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;
No comments:
Post a Comment