Manually reinstate failed primary database

In my previous post I had initiated a manual fail-over.In this post I will reinstate my old primary database as my new standby database.

SRORCL - new primary database 

SQL> select name ,db_unique_name,open_mode,database_role from v$database;

NAME       DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL     SRORCL       READ WRITE           PRIMARY

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SRPDB                          READ WRITE NO

SQL> select to_char(standby_became_primary_scn) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
4152666

SQL>

TRORCL - old primary database 

SQL> select name ,db_unique_name,open_mode,database_role from v$database;

NAME       DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL     TRORCL       MOUNTED              PRIMARY

SQL> Flashback Database To Scn 4152666;

Flashback complete.

SQL> Alter Database Convert To Physical Standby;

Database altered.

SQL>

Below is the alert log entry -

Alter Database Convert To Physical Standby
2020-05-31 10:49:33.960000 +05:30
Clearing standby activation ID 3579079003 (0xd554695b)
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 92 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.
Network throttle feature is disabled as mount time
Physical Standby Database mounted.
In-memory operation on ADG is currently only supported on Engineered systems and PaaS.
inmemory_adg_enabled is turned off automatically.
Please contact our support team for EXADATA solutions
CONVERT TO PHYSICAL STANDBY: Complete - Database mounted as physical standby
Completed: Alter Database Convert To Physical Standby

SQL> select name ,db_unique_name,open_mode,database_role from v$database;

NAME       DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL     TRORCL       MOUNTED              PHYSICAL STANDBY

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         94      12288        559
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
RFS       IDLE                  1         95         32          1
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0

10 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
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         94      12288        559
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
RFS       RECEIVING             1         95         48          1
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
MRP0      APPLYING_LOG          1         92     133458     133458

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
DGRD      ALLOCATED             0          0          0          0

12 rows selected.

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> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         94      12288        559
ARCH      CONNECTED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
DGRD      ALLOCATED             0          0          0          0
RFS       IDLE                  1         95        926          1
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
MRP0      APPLYING_LOG          1         95        926     409600

11 rows selected.


SQL>

Dataguard fail-over using SQL plus

Make sure primary and standby database are in sync- 

Primary database-

SQL> select name ,db_unique_name,open_mode,database_role from v$database;

NAME       DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL     TRORCL       READ WRITE           PRIMARY

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

I want to verify flashback is enable at both databases, so that I can reinstate old-primary database later.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES
SQL>

Standby database-

SQL> select name ,db_unique_name,open_mode,database_role from v$database;

NAME       DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL     SRORCL       READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

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         91     131072       1239
DGRD      ALLOCATED             0          0          0          0
ARCH      CLOSING               1         88       8192        498
ARCH      CLOSING               1         89      96256        395
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  1         92      78114          1
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
MRP0      APPLYING_LOG          1         92      78114     409600

10 rows selected.

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# F
ROM 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                     91                    91          0

SQL>

Flush any unsent redo from the primary database to the target standby database.If the primary database can be mounted, it may be possible to flush any unsent archived and current redo from the primary database to the standby database. If this operation is successful, a zero data loss failover is possible even if the primary database is not in a zero data loss data protection mode.

Ensure that Redo Apply is active at the target standby database.

Mount, but do not open the primary database. If the primary database cannot be mounted, we need to identify the latest archive log files from the primary and then transfer and register them at standby database.

Issue the following SQL statement at the primary database:

ALTER SYSTEM FLUSH REDO TO target_db_name;

For the sake of testing,I will shutdown my primary and open in mount mode-

SQL> select name ,db_unique_name,open_mode,database_role from v$database;

NAME       DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL     TRORCL       READ WRITE           PRIMARY
SQL> shut abort ;
ORACLE instance shut down.
SQL>
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> ALTER SYSTEM FLUSH REDO TO SRORCL;

System altered.

SQL>

If above statement completes without any errors, proceed with below steps 

Stop Redo Apply-

Issue the following SQL statement on the target standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> 

Issue the following SQL statement on the target standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL>

Do the fail-over-

Issue the following SQL statement on the target standby database:

SQL> select name ,db_unique_name,open_mode,database_role from v$database;

NAME       DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE
---------- ------------ -------------------- ----------------

SRORCL     SRORCL       READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>  ALTER DATABASE FAILOVER TO SRORCL;

Database altered.

SQL> 

If above command fails try with force option, 

ALTER DATABASE FAILOVER TO SRORCL force;

Below is the alert log entry, 

ALTER DATABASE FAILOVER TO SRORCL
TMI: dbsdrv failover to target BEGIN 2020-05-31 10:20:05.386307
Terminal Recovery requested in process 172
TMI: adbdrv termRecovery BEGIN 2020-05-31 10:20:05.390307
TMI: adbdrv termRecovery END 2020-05-31 10:20:05.412308
 Started logmerger process
Managed Standby Recovery not using Real Time Apply
2020-05-31 10:20:06.703000 +05:30
Parallel Media Recovery started with 4 slaves
Media Recovery Waiting for thread 1 sequence 93
2020-05-31 10:20:08.443000 +05:30
Already in limbo. Terminal Recovery finished with No-Data-Loss.
RECOVER FINISH applied through switchover EOR logs and stopped.
2020-05-31 10:20:09.475000 +05:30
Attempt to set limbo arscn 0x00000000003f5d5c irscn 0x00000000003f5d5c
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2020-05-31 10:20:09.938567
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2020-05-31 10:20:09.939567
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2020-05-31 10:20:09.994570
Killing 1 processes (PIDS:7536) (all RFS) in order to disallow current and future RFS connections. Requested by OS proc
2020-05-31 10:20:16.413000 +05:30
Stopping Emon pool
Dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 6832 user 'SYSTEM' program 'ORACLE.EXE (SVCB)', waiting for 'wait for unread message on broadcast channe
Active process 6832 user 'SYSTEM' program 'ORACLE.EXE (SVCB)', waiting for 'wait for unread message on broadcast channe
Active process 6832 user 'SYSTEM' program 'ORACLE.EXE (SVCB)', waiting for 'wait for unread message on broadcast channe
Active process 6832 user 'SYSTEM' program 'ORACLE.EXE (SVCB)', waiting for 'wait for unread message on broadcast channe
CLOSE: all sessions shutdown successfully.
alter pluggable database all close
2020-05-31 10:20:17.775000 +05:30
JIT: pid 4060 requesting stop
Pluggable database SRPDB closed
Completed: alter pluggable database all close
JIT: pid 4060 requesting stop
Stopping Emon pool
2020-05-31 10:20:19.187000 +05:30
Backup controlfile written to trace file F:\APP\MAHESH\diag\rdbms\srorcl\srorcl\trace\srorcl_ora_172.trc
2020-05-31 10:20:20.263000 +05:30
SwitchOver after complete recovery through change 4152668
Online logfile pre-clearing operation disabled by switchover
Online log F:\ORADATA\SRORCL\ONLINELOG\O1_MF_1_H5544RTJ_.LOG: Thread 1 Group 1 was previously cleared
Online log F:\ORADATA\ORAFRA\SRORCL\ONLINELOG\O1_MF_1_H5545F40_.LOG: Thread 1 Group 1 was previously cleared
Online log F:\ORADATA\SRORCL\ONLINELOG\O1_MF_2_H5544RWG_.LOG: Thread 1 Group 2 was previously cleared
Online log F:\ORADATA\ORAFRA\SRORCL\ONLINELOG\O1_MF_2_H5545FBT_.LOG: Thread 1 Group 2 was previously cleared
Online log F:\ORADATA\SRORCL\ONLINELOG\O1_MF_3_H5544RXG_.LOG: Thread 1 Group 3 was previously cleared
Online log F:\ORADATA\ORAFRA\SRORCL\ONLINELOG\O1_MF_3_H5545FQ0_.LOG: Thread 1 Group 3 was previously cleared
2020-05-31 10:20:21.825000 +05:30
Standby became primary SCN: 4152666
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
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2020-05-31 10:20:22.011258
TMI: dbsdrv failover to target END 2020-05-31 10:20:22.011258
Failover completed with No-Data-Loss.
Completed:  ALTER DATABASE FAILOVER TO SRORCL

SQL> select name ,db_unique_name,open_mode,database_role from v$database;

NAME       DB_UNIQUE_NA OPEN_MODE            DATABASE_ROLE
---------- ------------ -------------------- ----------------
SRORCL     SRORCL       MOUNTED              PRIMARY

SQL> alter database open;

Database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SRPDB                          READ WRITE NO
SQL>

If both of the below command fails, we need to perform a data-loss failover.

ALTER DATABASE FAILOVER TO SRORC;
ALTER DATABASE FAILOVER TO SRORC force;

Perform a data-loss fail-over-

If an error condition cannot be resolved, a failover can still be performed (with some data loss) by issuing the following SQL statement on the target standby database:

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Verifying 12c Switchover Verify feature

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

SQL> alter database switchover to TRORCL verify;
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>