========================================================================
STEP-1 : Verify the DR database
========================================================================
Connect to DR server : example01a
-- Verify the existing status in DR database which is open in R/W mode using "snapshot" standby:
SQL> select name, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TESTPRD testdr READ WRITE SNAPSHOT STANDBY
SQL>
-- Flashback status
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> select name,scn,time,guarantee_flashback_database,storage_size from v$restore_point;
NAME SCN TIME GUARA STORAGE_SIZE
-------------------------------------------------- ---------------- ----------------------------------- ----- -------------
SNAPSHOT_STANDBY_REQUIRED_09/11/2018 11:11:32 992874058 11-SEP-18 11.11.32.000000000 AM YES 3722444800
SQL> select instance_name,status,host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ --------------------------------------
testdr2 OPEN example01b.facedba.com
testdr1 OPEN example01a.facedba.com
testdr3 OPEN example01c.facedba.com
SQL>
========================================================================
STEP-2 : Verify LAG details
========================================================================
-- Verify archive logs pending to apply
-- connect to primary : examplerac1a
[oracle@examplerac1a ~]$ testprd
[oracle@examplerac1a ~]$ env | grep ORA
ORACLE_UNQNAME=TESTPRD
ORACLE_SID=TESTPRD1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@examplerac1a ~]$ !sql
sqlplus / as sysdba
SQL> set lines 222
SQL> select instance_name,status,host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ ----------------------------------------------------------------
TESTPRD1 OPEN examplerac1a
TESTPRD3 OPEN examplerac1c
TESTPRD2 OPEN examplerac1b
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TESTPRD READ WRITE
SQL>
SQL> select thread#,max(sequence#) from v$archived_log group by thread# order by 1;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 9393
2 8797
3 9209
SQL>
-- Connect to standby : example01a
-- Verify LAG details w.r.t. primary
-- Using DG broker
DGMGRL> connect testdr
Password:
Connected as SYSDG.
DGMGRL> show database testdr;
Database - testdr
Role: SNAPSHOT STANDBY
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 2 days 11 hours 55 minutes 6 seconds (computed 0 seconds ago)
Instance(s):
testdr1
testdr2
testdr3
Database Status:
SUCCESS
DGMGRL>
-- Using query
select * from v$dataguard_stats; SQL>
SOURCE_DBID SOURCE_DB_UN NAME VALUE UNIT TIME_COMPUTED DATUM_TIME CON_ID
----------- ------------ -------------------------------- -------------------- ------------------------------ -------------------------- --------------------- ----------
311374056 TESTPRD transport lag +00 00:00:00 day(2) to second(0) interval 09/13/2018 23:03:36 09/13/2018 23:03:35 0
311374056 TESTPRD apply lag +02 11:58:00 day(2) to second(0) interval 09/13/2018 23:03:36 09/13/2018 23:03:35 0
311374056 TESTPRD apply finish time +00 00:00:55.650 day(2) to second(3) interval 09/13/2018 23:03:36 0
0 estimated startup time 28 second 09/13/2018 23:03:36 0
SQL>
-- Verify the Archivelog Gap
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 9393 11 9382
2 8797 16 8781
3 9209 11 9198
SQL>
========================================================================
STEP-3 : Converting Snapshot Standby Database into Physical Standby Database
========================================================================
Step 1: Check for current database role
---------------------------------------
SQL> Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
testdr READ WRITE SNAPSHOT STANDBY
SQL>
SQL> select name, guarantee_flashback_database from v$restore_point;
NAME GUA
-------------------------------------------------------------------------------------------------------------------------------- ---
SNAPSHOT_STANDBY_REQUIRED_09/11/2018 11:11:32 YES
SQL>
Step 2: If the standby database is a RAC setup then we need to shutdown all the instances except one on which we will be using the conversion commands.
--------------------------------------------------------------------------------------------------------------------------
-- status
[oracle@example01a ~]$ srvctl status database -d testdr -verbose
Instance testdr1 is running on node example01a. Instance status: Open.
Instance testdr2 is running on node example01b. Instance status: Open.
Instance testdr3 is running on node example01c. Instance status: Open.
[oracle@example01a ~]$
-- Stop other two instances
[oracle@example01a ~]$ srvctl status instance -d testdr -i testdr2 -verbose
Instance testdr2 is running on node example01b. Instance status: Open.
[oracle@example01a ~]$ srvctl stop instance -d testdr -i testdr2
[oracle@example01a ~]$ srvctl status instance -d testdr -i testdr3 -verbose
Instance testdr3 is running on node example01c. Instance status: Open.
[oracle@example01a ~]$ srvctl stop instance -d testdr -i testdr3
[oracle@example01a ~]$ srvctl status instance -d testdr -i testdr2 -verbose
Instance testdr2 is not running on node example01b
[oracle@example01a ~]$
-- Cross-verify instance status in all the instances
SQL> select instance_name,status,host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ --------------------------------------
testdr1 OPEN example01a.facedba.com
Step 3: Ensure that the database is mounted.
---------------------------------------------
shut immediate;
startup mount
e.g.,
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
...
Database mounted.
SQL>
Step 4: Do the conversion of snapshot standby database to physical standby database.
------------------------------------------------------------------------------------
Using Manual method:
Issue the following SQL statement to convert the snapshot standby back to the physical standby:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL>
Note: Open the same instance alert log and see the status as well.
e.g.,
Archived Log entry 27095 added for thread 3 sequence 9210 ID 0x1292157e dest 1:
RFS[3]: Selected log 15 for thread 2 sequence 8799 dbid 311374056 branch 919823934
Thu Sep 13 23:26:46 2018
Archived Log entry 27096 added for thread 2 sequence 8798 ID 0x1292157e dest 1:
Thu Sep 13 23:26:54 2018
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='testdr1';
Thu Sep 13 23:26:54 2018
ALTER SYSTEM SET log_archive_format=' %t_%s_%r.dbf' SCOPE=SPFILE SID='testdr1';
Thu Sep 13 23:27:14 2018
Decreasing number of real time LMS from 2 to 0
Thu Sep 13 23:29:48 2018
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Thu Sep 13 23:29:48 2018
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (testdr1)
Thu Sep 13 23:29:48 2018
Killing 6 processes (PIDS:59198,59196,59192,59194,59188,59190) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 58293 on instance 1
Thu Sep 13 23:29:53 2018
Flashback Restore Start
Thu Sep 13 23:30:27 2018
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point dropped
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_274.5232.968804217
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_289.22000.970880737
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_125.12987.969976835
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_133.3707.969980407
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_144.22476.969983421
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_320.12499.975754841
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_149.3074.969985135
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_291.14763.970898411
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_150.16805.969989881
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_293.3098.970904117
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_161.4691.967916083
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_278.1284.968832017
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_199.3977.968839221
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_162.11578.970005681
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_163.6733.970005751
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_173.10531.970005901
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_12.1444.966125419
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_9.14265.968846697
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_174.8083.970013031
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_181.15345.970013051
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_164.7255.968850011
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_167.6290.970020023
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_206.13203.966125443
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_165.13970.968858533
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_182.15693.970033625
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_322.14307.975805261
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_22.20883.961207247
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_188.11958.970040197
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_26.22421.967959611
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_211.19980.966153929
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_4.8659.970048829
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_171.22135.968882689
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_196.2045.967075681
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_112.7933.968890729
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_30.19766.970059643
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_33.572.970061957
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_170.14302.970063873
Clearing standby activation ID 3291529591 (0xc430c177)
The primary database controlfile was created using the
'MAXLOGFILES 40' clause.
There is space for up to 31 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl8.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl9.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl10.f' SIZE 52428800;
Thu Sep 13 23:30:28 2018
Waiting for all non-current ORLs to be archived...
Thu Sep 13 23:30:28 2018
ARC0: Becoming the active heartbeat ARCH
ARC0: Becoming the active heartbeat ARCH
Thu Sep 13 23:30:28 2018
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DATA/testdr/ONLINELOG/group_1.1266.920900689
Clearing online log 1 of thread 1 sequence number 13
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/testdr/ONLINELOG/group_2.3177.920900689
Clearing online log 2 of thread 1 sequence number 11
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/testdr/ONLINELOG/group_3.7942.920900691
Clearing online log 3 of thread 1 sequence number 12
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 +DATA/testdr/ONLINELOG/group_4.5528.920900691
Clearing online log 4 of thread 2 sequence number 16
Clearing online redo logfile 4 complete
Clearing online redo logfile 5 +DATA/testdr/ONLINELOG/group_5.6487.920900691
Clearing online log 5 of thread 2 sequence number 17
Clearing online redo logfile 5 complete
Clearing online redo logfile 6 +DATA/testdr/ONLINELOG/group_6.7986.920900691
Clearing online log 6 of thread 2 sequence number 15
Clearing online redo logfile 6 complete
Clearing online redo logfile 7 +DATA/testdr/ONLINELOG/group_7.857.920900691
Clearing online log 7 of thread 3 sequence number 10
Clearing online redo logfile 7 complete
Clearing online redo logfile 8 +DATA/testdr/ONLINELOG/group_8.7774.920900691
Clearing online log 8 of thread 3 sequence number 11
Clearing online redo logfile 8 complete
Clearing online redo logfile 9 +DATA/testdr/ONLINELOG/group_9.1351.920900691
Clearing online log 9 of thread 3 sequence number 12
Clearing online redo logfile 9 complete
Physical Standby Database mounted.
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Thu Sep 13 23:30:38 2018
Network Resource Management enabled for Process (pid 70054) for Exadata I/O
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process (PID:70054)
RFS[4]: Selected log 19 for thread 3 sequence 9211 dbid 311374056 branch 919823934
Thu Sep 13 23:30:38 2018
Network Resource Management enabled for Process (pid 70046) for Exadata I/O
Primary database is in MAXIMUM PERFORMANCE mode
Thu Sep 13 23:30:38 2018
Network Resource Management enabled for Process (pid 70050) for Exadata I/O
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Assigned to RFS process (PID:70046)
RFS[5]: Selected log 15 for thread 2 sequence 8799 dbid 311374056 branch 919823934
RFS[6]: Assigned to RFS process (PID:70050)
RFS[6]: Selected log 10 for thread 1 sequence 9395 dbid 311374056 branch 919823934
RFS[4]: Selected log 18 for thread 3 sequence 9212 dbid 311374056 branch 919823934
Thu Sep 13 23:30:39 2018
Archived Log entry 27097 added for thread 3 sequence 9211 ID 0x1292157e dest 1:
RFS[6]: Selected log 11 for thread 1 sequence 9396 dbid 311374056 branch 919823934
Thu Sep 13 23:30:39 2018
Archived Log entry 27098 added for thread 1 sequence 9395 ID 0x1292157e dest 1:
Thu Sep 13 23:30:40 2018
Archived Log entry 27099 added for thread 2 sequence 8799 ID 0x1292157e dest 1:
RFS[5]: Selected log 14 for thread 2 sequence 8800 dbid 311374056 branch 919823934
....
Step 5: Start database in mount, if not already mounted.
-----------------------------------------------------------------------
SQL> select name, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TESTPRD testdr MOUNTED PHYSICAL STANDBY
SQL>
Note: You can see here now standby db status converted to "PHYSICAL STANDBY"
Step 6: Recover Physical Standby database
-------------------------------------------------
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
SQL>
-- Verify Lag
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1; 2 3 4 5 6
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 9395 9395 0
2 8799 8799 0
3 9211 9211 0
SQL>
Step-7 : Corect the DG Broker info:
-------------------------------------
-- before
DGMGRL> show database testdr;
Database - testdr
Role: SNAPSHOT STANDBY
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Instance(s):
testdr1
Warning: ORA-16782: instance not open for read and write access
testdr2
testdr3
Database Error(s):
ORA-16816: incorrect database role
Database Status:
ERROR
DGMGRL> quit
--- Now apply below commands
DGMGRL>
DGMGRL> connect testdr;
Password:
Connected as SYSDG.
DGMGRL>
DGMGRL> CONVERT DATABASE testdr TO PHYSICAL STANDBY;
Converting database "testdr" to a Physical Standby database, please wait...
Database "testdr" converted successfully
DGMGRL>
-- Post applied commands
DGMGRL> show database testdr;
Database - testdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 4.42 MByte/s
Real Time Query: OFF
Instance(s):
testdr1 (apply instance)
testdr2
testdr3
Database Status:
SUCCESS
DGMGRL>
Now all looks good.
Step 7: Check for database role:
-----------------------------------------
SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
testdr MOUNTED PHYSICAL STANDBY
SQL>
-- end of all the steps --
STEP-1 : Verify the DR database
========================================================================
Connect to DR server : example01a
-- Verify the existing status in DR database which is open in R/W mode using "snapshot" standby:
SQL> select name, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TESTPRD testdr READ WRITE SNAPSHOT STANDBY
SQL>
-- Flashback status
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL> select name,scn,time,guarantee_flashback_database,storage_size from v$restore_point;
NAME SCN TIME GUARA STORAGE_SIZE
-------------------------------------------------- ---------------- ----------------------------------- ----- -------------
SNAPSHOT_STANDBY_REQUIRED_09/11/2018 11:11:32 992874058 11-SEP-18 11.11.32.000000000 AM YES 3722444800
SQL> select instance_name,status,host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ --------------------------------------
testdr2 OPEN example01b.facedba.com
testdr1 OPEN example01a.facedba.com
testdr3 OPEN example01c.facedba.com
SQL>
========================================================================
STEP-2 : Verify LAG details
========================================================================
-- Verify archive logs pending to apply
-- connect to primary : examplerac1a
[oracle@examplerac1a ~]$ testprd
[oracle@examplerac1a ~]$ env | grep ORA
ORACLE_UNQNAME=TESTPRD
ORACLE_SID=TESTPRD1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
[oracle@examplerac1a ~]$ !sql
sqlplus / as sysdba
SQL> set lines 222
SQL> select instance_name,status,host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ ----------------------------------------------------------------
TESTPRD1 OPEN examplerac1a
TESTPRD3 OPEN examplerac1c
TESTPRD2 OPEN examplerac1b
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TESTPRD READ WRITE
SQL>
SQL> select thread#,max(sequence#) from v$archived_log group by thread# order by 1;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 9393
2 8797
3 9209
SQL>
-- Connect to standby : example01a
-- Verify LAG details w.r.t. primary
-- Using DG broker
DGMGRL> connect testdr
Password:
Connected as SYSDG.
DGMGRL> show database testdr;
Database - testdr
Role: SNAPSHOT STANDBY
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 2 days 11 hours 55 minutes 6 seconds (computed 0 seconds ago)
Instance(s):
testdr1
testdr2
testdr3
Database Status:
SUCCESS
DGMGRL>
-- Using query
select * from v$dataguard_stats; SQL>
SOURCE_DBID SOURCE_DB_UN NAME VALUE UNIT TIME_COMPUTED DATUM_TIME CON_ID
----------- ------------ -------------------------------- -------------------- ------------------------------ -------------------------- --------------------- ----------
311374056 TESTPRD transport lag +00 00:00:00 day(2) to second(0) interval 09/13/2018 23:03:36 09/13/2018 23:03:35 0
311374056 TESTPRD apply lag +02 11:58:00 day(2) to second(0) interval 09/13/2018 23:03:36 09/13/2018 23:03:35 0
311374056 TESTPRD apply finish time +00 00:00:55.650 day(2) to second(3) interval 09/13/2018 23:03:36 0
0 estimated startup time 28 second 09/13/2018 23:03:36 0
SQL>
-- Verify the Archivelog Gap
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 9393 11 9382
2 8797 16 8781
3 9209 11 9198
SQL>
========================================================================
STEP-3 : Converting Snapshot Standby Database into Physical Standby Database
========================================================================
Step 1: Check for current database role
---------------------------------------
SQL> Select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
testdr READ WRITE SNAPSHOT STANDBY
SQL>
SQL> select name, guarantee_flashback_database from v$restore_point;
NAME GUA
-------------------------------------------------------------------------------------------------------------------------------- ---
SNAPSHOT_STANDBY_REQUIRED_09/11/2018 11:11:32 YES
SQL>
Step 2: If the standby database is a RAC setup then we need to shutdown all the instances except one on which we will be using the conversion commands.
--------------------------------------------------------------------------------------------------------------------------
-- status
[oracle@example01a ~]$ srvctl status database -d testdr -verbose
Instance testdr1 is running on node example01a. Instance status: Open.
Instance testdr2 is running on node example01b. Instance status: Open.
Instance testdr3 is running on node example01c. Instance status: Open.
[oracle@example01a ~]$
-- Stop other two instances
[oracle@example01a ~]$ srvctl status instance -d testdr -i testdr2 -verbose
Instance testdr2 is running on node example01b. Instance status: Open.
[oracle@example01a ~]$ srvctl stop instance -d testdr -i testdr2
[oracle@example01a ~]$ srvctl status instance -d testdr -i testdr3 -verbose
Instance testdr3 is running on node example01c. Instance status: Open.
[oracle@example01a ~]$ srvctl stop instance -d testdr -i testdr3
[oracle@example01a ~]$ srvctl status instance -d testdr -i testdr2 -verbose
Instance testdr2 is not running on node example01b
[oracle@example01a ~]$
-- Cross-verify instance status in all the instances
SQL> select instance_name,status,host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ --------------------------------------
testdr1 OPEN example01a.facedba.com
Step 3: Ensure that the database is mounted.
---------------------------------------------
shut immediate;
startup mount
e.g.,
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
...
Database mounted.
SQL>
Step 4: Do the conversion of snapshot standby database to physical standby database.
------------------------------------------------------------------------------------
Using Manual method:
Issue the following SQL statement to convert the snapshot standby back to the physical standby:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL>
Note: Open the same instance alert log and see the status as well.
e.g.,
Archived Log entry 27095 added for thread 3 sequence 9210 ID 0x1292157e dest 1:
RFS[3]: Selected log 15 for thread 2 sequence 8799 dbid 311374056 branch 919823934
Thu Sep 13 23:26:46 2018
Archived Log entry 27096 added for thread 2 sequence 8798 ID 0x1292157e dest 1:
Thu Sep 13 23:26:54 2018
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='testdr1';
Thu Sep 13 23:26:54 2018
ALTER SYSTEM SET log_archive_format=' %t_%s_%r.dbf' SCOPE=SPFILE SID='testdr1';
Thu Sep 13 23:27:14 2018
Decreasing number of real time LMS from 2 to 0
Thu Sep 13 23:29:48 2018
ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Thu Sep 13 23:29:48 2018
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (testdr1)
Thu Sep 13 23:29:48 2018
Killing 6 processes (PIDS:59198,59196,59192,59194,59188,59190) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 58293 on instance 1
Thu Sep 13 23:29:53 2018
Flashback Restore Start
Thu Sep 13 23:30:27 2018
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point dropped
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_274.5232.968804217
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_289.22000.970880737
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_125.12987.969976835
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_133.3707.969980407
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_144.22476.969983421
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_320.12499.975754841
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_149.3074.969985135
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_291.14763.970898411
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_150.16805.969989881
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_293.3098.970904117
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_161.4691.967916083
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_278.1284.968832017
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_199.3977.968839221
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_162.11578.970005681
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_163.6733.970005751
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_173.10531.970005901
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_12.1444.966125419
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_9.14265.968846697
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_174.8083.970013031
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_181.15345.970013051
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_164.7255.968850011
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_167.6290.970020023
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_206.13203.966125443
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_165.13970.968858533
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_182.15693.970033625
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_322.14307.975805261
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_22.20883.961207247
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_188.11958.970040197
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_26.22421.967959611
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_211.19980.966153929
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_4.8659.970048829
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_171.22135.968882689
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_196.2045.967075681
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_112.7933.968890729
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_30.19766.970059643
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_33.572.970061957
Deleted Oracle managed file +DATA/testdr/FLASHBACK/log_170.14302.970063873
Clearing standby activation ID 3291529591 (0xc430c177)
The primary database controlfile was created using the
'MAXLOGFILES 40' clause.
There is space for up to 31 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl8.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl9.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl10.f' SIZE 52428800;
Thu Sep 13 23:30:28 2018
Waiting for all non-current ORLs to be archived...
Thu Sep 13 23:30:28 2018
ARC0: Becoming the active heartbeat ARCH
ARC0: Becoming the active heartbeat ARCH
Thu Sep 13 23:30:28 2018
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DATA/testdr/ONLINELOG/group_1.1266.920900689
Clearing online log 1 of thread 1 sequence number 13
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/testdr/ONLINELOG/group_2.3177.920900689
Clearing online log 2 of thread 1 sequence number 11
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/testdr/ONLINELOG/group_3.7942.920900691
Clearing online log 3 of thread 1 sequence number 12
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 +DATA/testdr/ONLINELOG/group_4.5528.920900691
Clearing online log 4 of thread 2 sequence number 16
Clearing online redo logfile 4 complete
Clearing online redo logfile 5 +DATA/testdr/ONLINELOG/group_5.6487.920900691
Clearing online log 5 of thread 2 sequence number 17
Clearing online redo logfile 5 complete
Clearing online redo logfile 6 +DATA/testdr/ONLINELOG/group_6.7986.920900691
Clearing online log 6 of thread 2 sequence number 15
Clearing online redo logfile 6 complete
Clearing online redo logfile 7 +DATA/testdr/ONLINELOG/group_7.857.920900691
Clearing online log 7 of thread 3 sequence number 10
Clearing online redo logfile 7 complete
Clearing online redo logfile 8 +DATA/testdr/ONLINELOG/group_8.7774.920900691
Clearing online log 8 of thread 3 sequence number 11
Clearing online redo logfile 8 complete
Clearing online redo logfile 9 +DATA/testdr/ONLINELOG/group_9.1351.920900691
Clearing online log 9 of thread 3 sequence number 12
Clearing online redo logfile 9 complete
Physical Standby Database mounted.
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY
Thu Sep 13 23:30:38 2018
Network Resource Management enabled for Process (pid 70054) for Exadata I/O
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process (PID:70054)
RFS[4]: Selected log 19 for thread 3 sequence 9211 dbid 311374056 branch 919823934
Thu Sep 13 23:30:38 2018
Network Resource Management enabled for Process (pid 70046) for Exadata I/O
Primary database is in MAXIMUM PERFORMANCE mode
Thu Sep 13 23:30:38 2018
Network Resource Management enabled for Process (pid 70050) for Exadata I/O
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Assigned to RFS process (PID:70046)
RFS[5]: Selected log 15 for thread 2 sequence 8799 dbid 311374056 branch 919823934
RFS[6]: Assigned to RFS process (PID:70050)
RFS[6]: Selected log 10 for thread 1 sequence 9395 dbid 311374056 branch 919823934
RFS[4]: Selected log 18 for thread 3 sequence 9212 dbid 311374056 branch 919823934
Thu Sep 13 23:30:39 2018
Archived Log entry 27097 added for thread 3 sequence 9211 ID 0x1292157e dest 1:
RFS[6]: Selected log 11 for thread 1 sequence 9396 dbid 311374056 branch 919823934
Thu Sep 13 23:30:39 2018
Archived Log entry 27098 added for thread 1 sequence 9395 ID 0x1292157e dest 1:
Thu Sep 13 23:30:40 2018
Archived Log entry 27099 added for thread 2 sequence 8799 ID 0x1292157e dest 1:
RFS[5]: Selected log 14 for thread 2 sequence 8800 dbid 311374056 branch 919823934
....
Step 5: Start database in mount, if not already mounted.
-----------------------------------------------------------------------
SQL> select name, DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
--------- ------------------------------ -------------------- ----------------
TESTPRD testdr MOUNTED PHYSICAL STANDBY
SQL>
Note: You can see here now standby db status converted to "PHYSICAL STANDBY"
Step 6: Recover Physical Standby database
-------------------------------------------------
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
SQL>
-- Verify Lag
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1; 2 3 4 5 6
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 9395 9395 0
2 8799 8799 0
3 9211 9211 0
SQL>
Step-7 : Corect the DG Broker info:
-------------------------------------
-- before
DGMGRL> show database testdr;
Database - testdr
Role: SNAPSHOT STANDBY
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Instance(s):
testdr1
Warning: ORA-16782: instance not open for read and write access
testdr2
testdr3
Database Error(s):
ORA-16816: incorrect database role
Database Status:
ERROR
DGMGRL> quit
--- Now apply below commands
DGMGRL>
DGMGRL> connect testdr;
Password:
Connected as SYSDG.
DGMGRL>
DGMGRL> CONVERT DATABASE testdr TO PHYSICAL STANDBY;
Converting database "testdr" to a Physical Standby database, please wait...
Database "testdr" converted successfully
DGMGRL>
-- Post applied commands
DGMGRL> show database testdr;
Database - testdr
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 4.42 MByte/s
Real Time Query: OFF
Instance(s):
testdr1 (apply instance)
testdr2
testdr3
Database Status:
SUCCESS
DGMGRL>
Now all looks good.
Step 7: Check for database role:
-----------------------------------------
SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database;
DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
------------------------------ -------------------- ----------------
testdr MOUNTED PHYSICAL STANDBY
SQL>
-- end of all the steps --
What I've seen is that though the images taken are very high-quality, all of them nonetheless look the identical. An image remains to be an image except one thing is finished to it to make it look out of the atypical, distinctive and private to its proprietor. If you want to learn more about this topic, then please visit here
ReplyDelete