My environment details-
SRORCL is my primary CDB
TRORCL is my standby CDB
SQL> select name ,open_mode,database_role from v$database ;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
SRORCL READ WRITE PRIMARY
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(SRORCL,TRORCL)
For a smooth role transition, it is important to have everything in-place and in sync. Pre-Oracle 12c, a set of commands used on PRIMARY and STANDBY to validate the readiness of the systems. However, with Oracle 12c, this is simplified with the ALTER DATABASE SWITCHOVER VERIFY command. The command performs the following set of actions:
- Verifies minimum Oracle version, i.e, Oracle 12.1
- PRIMRY DB REDO SHIPPING
- Verify MRP status on Standby database
alter database switchover to TRORCL verify
*
ERROR at line 1:
ORA-16470: Redo Apply is not running on switchover target
Check the MRP status on standby-
SQL> select name,db_unique_name,PRIMARY_DB_UNIQUE_NAME,database_role from v$database ;
NAME DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ------------------------------ ----------------
SRORCL TRORCL SRORCL PHYSICAL STANDBY
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 74 1 742
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 73 1 4
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 75 126 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
10 rows selected.
Let's start MRP
SQL> recover managed standby database disconnect from session ;
Media recovery complete.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 74 1 742
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 73 1 4
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 75 145 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 75 145 409600
11 rows selected.
SQL>
Now do the verify again-
SQL> alter database switchover to TRORCL verify;
Database altered.
SQL>
Below messages are from standby alert log file -
2020-05-29 16:52:22.019000 +05:30
SWITCHOVER VERIFY BEGIN
2020-05-29 16:52:23.098000 +05:30
SWITCHOVER VERIFY COMPLETE
Now we can do the switchover using a simple command,
"alter database switchover to TRORCL".
SQL> select name,db_unique_name,database_role from v$database ;
NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
SRORCL SRORCL PRIMARY
SQL>
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL>
Check the same in standby-
SQL> select name,db_unique_name,PRIMARY_DB_UNIQUE_NAME,database_role from v$database ;
NAME DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ------------------------------ ----------------
SRORCL TRORCL SRORCL PHYSICAL STANDBY
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL>
Now do the switchover-
SQL> alter database switchover to TRORCL;
Database altered.
SQL>
Messages from prirmary alert log file-
alter database switchover to TRORCL
The Time Management Interface (TMI) is being enabled for role
transition information. This will result in messages being
output to the alert log file with the prefix 'TMI: '. This is
being enabled to make the timing of the various stages of the
role transition available for diagnostic purposes. This
output will end when the role transition is complete.
TMI: dbsdrv switchover to target BEGIN 2020-05-29 18:01:48.494721
Starting switchover [Process ID: 952]
TMI: kcv_switchover_to_target convert to physical BEGIN 2020-05-29 18:01:49.161760
Waiting for target standby to receive all redo
Waiting for all non-current ORLs to be archived
All non-current ORLs have been archived
Waiting for all FAL entries to be archived
All FAL entries have been archived
Waiting for LAD:2 to become synchronized
2020-05-29 18:01:50.226000 +05:30
Active, synchronized Physical Standby switchover target has been identified
Preventing updates and queries at the Primary
2020-05-29 18:01:52.265000 +05:30
Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 83 has been fixed
Switchover: Primary highest seen SCN set to 0x00000000003b8c48
ARCH: Noswitch archival of thread 1, sequence 83
ARCH: End-Of-Redo Branch archival of thread 1 sequence 83
ARCH: LGWR is scheduled to archive to LAD:2 after log switch
2020-05-29 18:01:53.753000 +05:30
ARCH: Standby redo logfile selected for thread 1 sequence 83 for destination LOG_ARCHIVE_DEST_2
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Waiting for target standby to apply all redo
2020-05-29 18:01:56.058000 +05:30
Backup controlfile written to trace file F:\APP\MAHESH\diag\rdbms\srorcl\srorcl\trace\srorcl_ora_952.trc
Converting the primary database to a new standby database
Clearing standby activation ID 3570474899 (0xd4d11f93)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 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 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
Archivelog for thread 1 sequence 83 required for standby recovery
Offline data file 2 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 4 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 6 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
TMI: kcv_switchover_to_target convert to physical END 2020-05-29 18:01:56.135158
Sending request(convert to primary database) to switchover target TRORCL
2020-05-29 18:02:12.553000 +05:30
Switchover complete. Database shutdown required
USER (ospid: 952): terminating the instance
2020-05-29 18:02:25.471000 +05:30
Instance terminated by USER, pid = 952
TMI: dbsdrv switchover to target END 2020-05-29 18:02:25.472634
Completed: alter database switchover to TRORCL
Shutting down instance (abort) (OS id: 952)
License high water mark = 4
Instance shutdown complete (OS id: 952)
Messages from standby alert log file-
2020-05-29 18:01:55.735000 +05:30
Resetting standby activation ID 3570474899 (0xd4d11f93)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Continuing
Archived Log entry 26 added for T-1.S-83 ID 0xd4d11f93 LAD:1
Media Recovery Waiting for thread 1 sequence 84
The Time Management Interface (TMI) is being enabled for role
transition information. This will result in messages being
output to the alert log file with the prefix 'TMI: '. This is
being enabled to make the timing of the various stages of the
role transition available for diagnostic purposes. This
output will end when the role transition is complete.
SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2020-05-29 18:01:56.389173
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply
MRP0: Background Media Recovery cancelled with status 16037
Errors in file F:\APP\MAHESH\diag\rdbms\trorcl\trorcl\trace\trorcl_pr00_1948.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
2020-05-29 18:01:57.732000 +05:30
Errors in file F:\APP\MAHESH\diag\rdbms\trorcl\trorcl\trace\trorcl_pr00_1948.trc:
ORA-16037: user requested cancel of managed recovery operation
Role Change: Canceled MRP
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2020-05-29 18:01:58.394288
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2020-05-29 18:01:58.397288
Killing 4 processes (PIDS:1532,5576,2264,5996) (all RFS) in order to disallow current and future RFS connections. Requested by
2020-05-29 18:02:10.251000 +05:30
Backup controlfile written to trace file F:\APP\MAHESH\diag\rdbms\trorcl\trorcl\trace\trorcl_rmi_5692.trc
SwitchOver after complete recovery through change 3902536
Online logfile pre-clearing operation disabled by switchover
2020-05-29 18:02:11.609000 +05:30
Online log F:\ORADATA\TRORCL\ONLINELOG\O1_MF_1_HF1O1K3C_.LOG: Thread 1 Group 1 was previously cleared
Online log F:\ORADATA\ORAFRA\TRORCL\ONLINELOG\O1_MF_1_HF1O1S9Z_.LOG: Thread 1 Group 1 was previously cleared
Online log F:\ORADATA\TRORCL\ONLINELOG\O1_MF_2_HF1O23HH_.LOG: Thread 1 Group 2 was previously cleared
Online log F:\ORADATA\ORAFRA\TRORCL\ONLINELOG\O1_MF_2_HF1O27QL_.LOG: Thread 1 Group 2 was previously cleared
Online log F:\ORADATA\TRORCL\ONLINELOG\O1_MF_3_HF1O2DRS_.LOG: Thread 1 Group 3 was previously cleared
Online log F:\ORADATA\ORAFRA\TRORCL\ONLINELOG\O1_MF_3_HF1O2HQ5_.LOG: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 3902534
The Time Management Interface (TMI) is being enabled for role
transition information. This will result in messages being
output to the alert log file with the prefix 'TMI: '. This is
being enabled to make the timing of the various stages of the
role transition available for diagnostic purposes. This
output will end when the role transition is complete.
Network throttle feature is disabled as mount time
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2020-05-29 18:02:12.546097
SWITCHOVER: completed request from primary database.
Open the new Primary database-
C:\Users\Mahesh>set ORACLE_SID=TRORCL
C:\Users\Mahesh>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 29 18:09:19 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> set lines 333
SQL> select name,db_unique_name,database_role,open_mode from v$database ;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
SRORCL TRORCL PRIMARY READ WRITE
SQL> alter database open;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB MOUNTED
SQL> alter pluggable database SRPDB open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ WRITE NO
SQL>
Start Standby instance-
C:\Windows\system32>set ORACLE_SID=SRORCL
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri May 29 18:16:25 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount ;
ORACLE instance started.
Total System Global Area 734003200 bytes
Fixed Size 8923240 bytes
Variable Size 348129176 bytes
Database Buffers 369098752 bytes
Redo Buffers 7852032 bytes
Database mounted.
SQL>
SQL> set lines 333
SQL> select name,db_unique_name,PRIMARY_DB_UNIQUE_NAME,database_role,open_mode from v$database ;
NAME DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ------------------------------ ---------------- --------------------
SRORCL SRORCL TRORCL PHYSICAL STANDBY MOUNTED
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 85 4096 488
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 86 91 1
RFS IDLE 0 0 0 0
9 rows selected.
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 85 4096 488
DGRD ALLOCATED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
RFS IDLE 0 0 0 0
RFS RECEIVING 1 86 108 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 83 1 252
DGRD ALLOCATED 0 0 0 0
11 rows selected.
SQL>
Let's open standby in read-only mode.
SQL> select name , value from v$dataguard_stats;
NAME VALUE
------------------------------ ----------------------------
transport lag +00 00:00:00
apply lag +00 00:00:00
apply finish time +00 00:00:00.000
estimated startup time 71
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB MOUNTED
SQL> alter pluggable database SRPDB open read only;
Pluggable database altered.
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRPDB READ ONLY NO
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 85 4096 488
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 86 1 354
ARCH CLOSING 1 87 1 5
ARCH CONNECTED 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 88 939 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 88 939 409600
10 rows selected.
SQL>
No comments:
Post a Comment