The Dataguard Broker makes it very easy to manage role changes. The following exercise is an attempt to understand what really goes on behind the scenes, by managing the role changes from the SQL prompt.
Disclaimer: One or more statements in this blog might be incorrect, including this one.
Introduction
Environment Description
A. Physical Machines:
B. Oracle Instances
Prerequisite Checks
Switchover steps
Additional Prerequisites
Steps to switchover
1.
Check the switchover status of both
DB's.
2.
Initiate switchover at primary Site(DG2)
3.
Mount the former primary (DG2)
4.
check switchover status of the older standby(DG1)
5.
Initiate switchover at the older standby site(DG1)
6.
Open the new primary(DG1)
7.
start redo apply on the new standby
Failover steps
Additional Prerequisites
Steps to Failover
1.
Identify and resolve any gaps
2.
Repeat until all gaps are resolved
3.
Copy any other missing archived logfiles
4.
Flush any unsent redo to the standby if
possible
5.
Stop Redo Apply at standby (DG2)
6.
Finish redo apply
7.
check switchover status of the standby(DG2)
8.
Switch physical standby to primary role
9.
Reinstate the failed primary back as a
physical standby
i. Determine
SCN at which old standby became new primary
ii. Flashback
failed primary
iii. Convert
primary to failed physical standby
iv. Check
the status of the archive destinations at the primary site (DG2)
v. Start
redo apply at the reinstated database
Introduction
A Data guard environment consists of one Primary and one
or more standby databases.
The standby databases could be logical standby or
physical standby.
In this article covers the role changes for a data guard
environment consisting of one primary and one physical standby database.
Role change can occur in 2 ways:
1.
Switchover:
It is reversal of role between a standby database and a primary database. The
primary database assumes role of standby database and the standby database
assumes role of primary database after the switchover. There is no data loss in
a switchover. This is generally a planned activity.
2.
Failover:
If the Primary database fails, one of the standby databases can be made to
assume the Primary role. This role change of standby database to primary, in
case of failure of existing primary, is called failover. There may or may not
be data loss. This is not a planned activity and is performed only in
catastrophic cases where the Primary database fails.
Environment Description
A. Physical Machines:
Two
nodes DG1 and DG2 were configured with following IP addresses:
DG1:
192.168.4.1
DG2:
192.168.4.2
B. Oracle Instances
Below
are the details of the RDBMS instances
RDBMS
instance @DG1: ORCL1
RDBMS instance @DG2: ORCL2
At the outset, ORCL2 is the primary database
instance and ORCL1 is the standby database instance.
Prerequisite Checks
The following requirements must be checked before any
role change:
1.
Verify that each DB is configured properly for
the role it is about to assume.
This would include checking the
instance parameters for both databases are set appropriately, the network
services are configured properly etc.
2.
Verify that there are no redo errors/gaps by
querying V$ARCHIVE_DEST_STATUS view at Primary (DG2)
SQL> select status,
gap_status , db_unique_name from v$archive_dest_status where dest_id=2;
STATUS GAP_STATUS DB_UNIQUE_NAME
---------
------------------------ ------------------------------
VALID NO GAP orcl1
3.
Ensure that temporary files created at standby
site match those at the primary site
4.
Remove any delay if present, configure MRP such
that delay is ignored using NODELAY option
5.
Before performing switchover from RAC primary to
standby, shutdown all but one instances of the primary
6.
If the standby is in Active data guard mode
(real-time query), bring all instances to mounted but not open state
Switchover steps
Additional Prerequisites
·
Verify that the primary DB is open and redo
apply active on standby
Steps to switchover
1.
Check the switchover status of both DB's.
The switchover status of the
standby would be 'NOT ALLOWED' since there is already an active Primary
database.
- PRIMARY (DG2)
Proceed only if it shows 'TO STANDBY' or
'SESSIONS ACTIVE'
SQL> select
instance_name, name, primary_db_unique_name, switchover_status, open_mode,
flashback_on, force_logging, database_role from v$database, v$instance;
INSTANCE_NAME
NAME
PRIMARY_DB_UNIQUE_NAME
SWITCHOVER_STATUS OPEN_MODE FLASHBACK_ON FOR DATABASE_ROLE
---------------- --------- ------------------------------
-------------------- -------------------- ------------------ ---
----------------
orcl2
ORCL1 orcl1 TO STANDBY READ WRITE YES YES PRIMARY
- STANDBY (DG1)
SQL> select
instance_name, name, primary_db_unique_name, switchover_status, open_mode,
flashback_on, force_logging, database_role from v$database, v$instance;
INSTANCE_NAME
NAME PRIMARY_DB_UNIQUE_NAME SWITCHOVER_STATUS OPEN_MODE FLASHBACK_ON FOR DATABASE_ROLE
---------------- --------- ------------------------------
-------------------- -------------------- ------------------ ---
----------------
orcl1 ORCL1 orcl2 NOT ALLOWED MOUNTED YES YES PHYSICAL STANDBY
2.
Initiate switchover at primary Site(DG2)
alter database commit
to switchover to physical standby with session shutdown;
- At this stage the there is no
primary DB, but all physical standbys
- This also leads to change in
'SWITCHOVER_STATUS' of physical standby from
'NOT ALLOWED' to 'TO_PRIMARY'
-
This statement takes a backup of the current controlfile to trace
-
closes the database
-
archives the current logfile
-
marks the End-of-redo marker to the end of the last logfile
-
shuts down the MRP process at the standby
A
closer look at the logfiles, details what actions are performed by this
statement
Primary Logfile (DG2):
alter database commit to switchover to
physical standby with session shutdown
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 4738] (orcl2)
Sat Mar 29 20:00:13 2014
LGWR: Standby redo logfile selected to archive thread 1
sequence 165
LGWR: Standby redo logfile selected for thread 1 sequence 165
for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 165 (LGWR switch)
Current log# 3 seq#
165 mem# 0: /u01/app/oracle/oradata/orcl2/redo03.log
Waiting for
all non-current ORLs to be archived...
Waiting for the ORL for thread 1 sequence 164 to be
archived...
Sat Mar 29 20:00:13 2014
Archived Log entry 207 added for thread 1 sequence 164 ID
0x4919b14e dest 1:
Sat Mar 29 20:00:23 2014
ORL for thread 1 sequence 164 has been archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for potential switchover target to become
synchronized...
Active, synchronized Physical Standby switchover target has been identified
Sat Mar 29 20:00:24 2014
********************************************************************
LGWR:
Resetting 'active' archival for destination LOG_ARCHIVE_DEST_2
********************************************************************
Destination LOG_ARCHIVE_DEST_2 no longer supports
SYNCHRONIZATION
Thread 1 advanced to log sequence 166 (LGWR switch)
Current log# 1 seq#
166 mem# 0: /u01/app/oracle/oradata/orcl2/redo01.log
Archived Log entry 209 added for thread 1 sequence 165 ID
0x4919b14e dest 1:
Sat Mar 29 20:00:25 2014
Stopping background process CJQ0
Sat Mar 29 20:00:25 2014
SMON: disabling tx recovery
Stopping background process QMNC
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Stopping background process SMCO
SMON: disabling cache recovery
Shutting down archive processes
Archiving is disabled
Sat Mar 29 20:00:28 2014
ARCH shutting downSat Mar 29 20:00:28 2014
ARCH shutting down
ARC2: Archival stoppedARC3: Archival stopped
Sat Mar 29 20:00:28 2014
ARCH shutting down
ARC1: Archival stopped
Sat Mar 29 20:00:28 2014
ARCH shutting down
ARC0: Archival stopped
Thread 1 closed at log sequence 166
Successful close of redo thread 1
ARCH: Noswitch archival of thread 1, sequence 166
ARCH: End-Of-Redo Branch archival of thread 1 sequence 166
Expanded controlfile section 11 from 114 to 228 records
Requested to grow by 114 records; added 4 blocks of records
Archived Log entry 210 added for thread 1 sequence 166 ID
0x4919b14e dest 1:
ARCH: Archiving is disabled due to current logfile archival
Using STANDBY_ARCHIVE_DEST parameter default value as
USE_DB_RECOVERY_FILE_DEST
Primary will
check for some target standby to have received all redo
Final check for
a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also applied all redo
Backup
controlfile written to trace file
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_4738.trc
Clearing standby activation ID 1226420558 (0x4919b14e)
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 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;
Archivelog for thread 1 sequence 166 required for standby
recovery
Switchover: Primary controlfile converted to standby
controlfile succesfully.
Sat Mar 29 20:00:29 2014
MRP0 started
with pid=17, OS id=5176
MRP0: Background Managed Standby Recovery process started
(orcl2)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Online logfile pre-clearing operation disabled by switchover
Media
Recovery Log
/u01/app/oracle/flash_recovery_area/ORCL2/archivelog/2014_03_29/o1_mf_1_166_9mfp85jm_.arc
Identified
End-Of-Redo for thread 1 sequence 166
Resetting standby activation ID 0 (0x0)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied until change 1128261
MRP0: Media
Recovery Complete: End-Of-REDO (orcl2)
MRP0:
Background Media Recovery process shutdown (orcl2)
Sat Mar 29 20:00:35 2014
idle dispatcher 'D000' terminated, pid = (17, 1)
Sat Mar 29 20:00:35 2014
Switchover: Complete - Database shutdown required (orcl2)
Completed: alter database commit to switchover to physical
standby with session shutdown
Sat Mar 29 20:04:40 2014
Shutting down instance (immediate)
.
.
ALTER DATABASE CLOSE NORMAL
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
.
.
Instance shutdown complete
Standby Logfile (DG1):
Media
Recovery Waiting for thread 1 sequence 165 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 165 Reading
mem 0
Mem# 0:
/u01/app/oracle/oradata/orcl1/srl05.log
Sat Mar 29 20:00:23 2014
Archived Log entry 23 added for thread 1 sequence 165 ID
0x4919b14e dest 1:
Sat Mar 29 20:00:23 2014
RFS[5]: Assigned to RFS process 4965
RFS[5]: Identified database type as 'physical standby': Client
is ARCH pid 4754
Sat Mar 29 20:00:23 2014
Media Recovery Waiting for thread 1 sequence 166
Sat Mar 29 20:00:29 2014
RFS[6]: Assigned to RFS process 4969
RFS[6]: Identified database type as 'physical standby': Client
is Foreground pid 4738
RFS[6]: Opened log for thread 1 sequence 166 dbid 1224670660
branch 840221191
Archived Log entry 24 added for thread 1 sequence 166 rlc
840221191 ID 0x4919b14e dest 2:
Sat Mar 29 20:00:29 2014
RFS[7]: Assigned to RFS process 4973
RFS[7]: Identified database type as 'physical standby': Client
is Foreground pid 4738
Media Recovery Log
/u01/app/oracle/flash_recovery_area/ORCL1/archivelog/2014_03_29/o1_mf_1_166_9mfp85jg_.arc
Identified End-Of-Redo for thread 1 sequence 166
Resetting standby activation ID 1226420558 (0x4919b14e)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied until change 1128261
Sat Mar 29 20:00:30 2014
MRP0: Media Recovery Complete: End-Of-REDO (orcl1)
MRP0: Background Media Recovery process shutdown (orcl1)
3.
Mount the former primary (DG2)
The former primary needs to be
mounted, since it was dismounted by the switchover statement.
shutdown
immediate;
startup mount;
SQL> select instance_name,
name, primary_db_unique_name, switchover_status, open_mode, flashback_on,
force_logging, database_role from v$database, v$instance;
INSTANCE_NAME
NAME
PRIMARY_DB_UNIQUE_NAME
SWITCHOVER_STATUS OPEN_MODE FLASHBACK_ON FOR DATABASE_ROLE
---------------- --------- ------------------------------
-------------------- -------------------- ------------------ ---
----------------
orcl2
ORCL1 orcl1 TO PRIMARY MOUNTED YES YES PHYSICAL STANDBY
4.
check switchover status of the older standby(DG1)
At this point, we have 2
physical standby databases.
SQL> select
instance_name, name, primary_db_unique_name, switchover_status, open_mode, flashback_on,
force_logging, database_role from v$database, v$instance;
INSTANCE_NAME
NAME
PRIMARY_DB_UNIQUE_NAME
SWITCHOVER_STATUS OPEN_MODE FLASHBACK_ON FOR DATABASE_ROLE
---------------- --------- ------------------------------
-------------------- -------------------- ------------------ ---
----------------
orcl1
ORCL1 orcl2 TO PRIMARY MOUNTED YES YES PHYSICAL STANDBY
5.
Initiate switchover at the older standby site(DG1)
The following statement will
switchover the ORCL1 database from ‘Physical Standby’ to ‘Primary’.
SQL> alter database
commit to switchover to primary with session shutdown;
Database altered.
SQL> select
instance_name, name, primary_db_unique_name, switchover_status, open_mode,
flashback_on, force_logging, database_role from v$database, v$instance;
INSTANCE_NAME
NAME
PRIMARY_DB_UNIQUE_NAME
SWITCHOVER_STATUS OPEN_MODE FLASHBACK_ON FOR DATABASE_ROLE
---------------- --------- ------------------------------
-------------------- -------------------- ------------------ ---
----------------
orcl1
ORCL1 orcl2 NOT ALLOWED MOUNTED YES YES PRIMARY
New Primary
Log (DG1):
ALTER
DATABASE SWITCHOVER TO PRIMARY (orcl1)
Maximum wait for role transition is 15 minutes.
Backup
controlfile written to trace file
/u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_ora_4733.trc
SwitchOver
after complete recovery through change 1128261
Online log
/u01/app/oracle/oradata/orcl1/redo01.log: Thread 1 Group 1 was previously
cleared
Online log
/u01/app/oracle/oradata/orcl1/redo02.log: Thread 1 Group 2 was previously cleared
Online log
/u01/app/oracle/oradata/orcl1/redo03.log: Thread 1 Group 3 was previously
cleared
Standby
became primary SCN: 1128259
Switchover:
Complete - Database mounted as primary
Completed: alter database commit to switchover to primary with
session shutdown
Sat Mar 29 20:25:29 2014
ARC1: Becoming the 'no SRL' ARCH
6.
Open the new primary(DG1)
alter
database open;
7.
start redo apply on the new standby
alter
database recover managed standby database disconnect;
- The standby database ORL's are
cleared when we start redo apply at the
just “switched over to Standby” database
New Standby log(DG2):
Attempt
to start background Managed Standby Recovery process (orcl2)
Sat Mar 29 20:44:57 2014
MRP0 started with pid=29, OS id=5425
MRP0: Background Managed Standby Recovery process started
(orcl2)
started logmerger
process
Sat Mar 29 20:45:02 2014
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for
all non-current ORLs to be archived...
All non-current
ORLs have been archived.
Clearing online redo logfile 1
/u01/app/oracle/oradata/orcl2/redo01.log
Clearing
online log 1 of thread 1 sequence number 166
Completed: alter database recover managed standby database
disconnect
Sat Mar 29 20:45:07 2014
Clearing online redo logfile 1 complete
Clearing online redo logfile 2
/u01/app/oracle/oradata/orcl2/redo02.log
Clearing
online log 2 of thread 1 sequence number 164
Clearing online redo logfile 2 complete
Clearing online redo logfile 3
/u01/app/oracle/oradata/orcl2/redo03.log
Clearing
online log 3 of thread 1 sequence number 165
Clearing online redo logfile 3 complete
Sat Mar 29 20:45:16 2014
Media Recovery Waiting for thread 1 sequence 167
Failover steps
Additional Prerequisites
·
If standby is running in Max Protection mode,
first place it in Max Performance mode
alter
database set standby database to maximize performance;
This is required because one cannot
failover to a standby in Max Protection mode.
Steps to Failover
After the switchover operation detailed in previous
section, the ORCL1@DG1 is now the Primary DB instance and ORCL2@DG2 is the
standby database instance.
We have rebooted the DG1 server, and mounted the
ORCL1(db_unq_name) database.
1.
Identify and resolve any gaps
select
* from v$archive_gap;
If any gaps found, manually copy
the archived log over to the standby and
register it.
alter
database register physical logfile '/path/of/archive.log';
2.
Repeat until all gaps are resolved
3.
Copy any other missing archived logfiles
This is basically for the archive
log sequences which were not transferred to
standby owing to the connection failure, that
is the cause of the need to failover.
Check the V$ARCHIVED_LOG view
select
unique thread#, max(sequence#) over (partition by thread#) as last_seq from
v$archived_log;
Register these logfiles at the
standby site.
alter
database register physical logfile '/path/of/archive.log';
4.
Flush any unsent redo to the standby if
possible
If the failed primary can be
mounted and the standby can be reached, flush the
redo available in the non-archived redo log to the
standby database.
alter
system flush redo to 'orcl2';
Failed
Primary log (DG1):
ALTER
SYSTEM FLUSH REDO TO 'orcl2' CONFIRM APPLY
ALTER SYSTEM FLUSH REDO TO orcl2 CONFIRM APPLY [Process Id:
4829] (orcl1)
Waiting for
all FAL entries to be archived...
All FAL entries have been archived.
Waiting for dest_id 2 to become synchronized...
Active,
synchronized Physical Standby
synchronized target has been identified
Managed recovery
running at physical standby 'LOG_ARCHIVE_DEST_2'
Flush
End-Of-Redo Log thread 1 sequence 178 has
been fixed
Flush Redo: Primary highest seen SCN set to 1162012:0
ARCH: Noswitch archival of thread 1, sequence 178
ARCH: End-Of-Redo Branch archival of thread 1 sequence 178
Identified End-Of-Redo for thread 1 sequence 178
ARCH: LGWR is scheduled to archive destination
LOG_ARCHIVE_DEST_2 after log switch
Flush End-Of-Redo Log thread 1 sequence 178
Archived Log entry 45 added for thread 1 sequence 178 ID
0x4930caf9 dest 1:
ARCH:
Archiving is disabled due to current logfile archival
Primary will wait for orcl2 standby to have applied all redo
Final check for a target standby that has recovered all redo.
Check will be made a few times.
LOG_ARCHIVE_DEST_2 is a potential synchronized target
LOG_ARCHIVE_DEST_2 is a potential synchronized target
LOG_ARCHIVE_DEST_2 is a potential synchronized target
LOG_ARCHIVE_DEST_2 is a potential synchronized target
LOG_ARCHIVE_DEST_2
has also applied all redo from primary
Active,
synchronized target has been identified that has applied all the redo from the
primary.
Flush
Redo: Primary redo moved to standby
Flush Redo: Complete - Database shutdown required (orcl1)
SQL> archive log list
Database log mode Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 176
Next log sequence to archive 0
Current log sequence 178
Standby Log:
Completed:
alter database register physical logfile
'/u01/app/oracle/flash_recovery_area/ORCL2/archivelog/2014_03_30/o1_mf_1_177_9mhsg19n_.arc'
Sun Mar 30 15:17:30 2014
Media Recovery Log
/u01/app/oracle/flash_recovery_area/ORCL2/archivelog/2014_03_30/o1_mf_1_177_9mhsg19n_.arc
Media
Recovery Waiting for thread 1 sequence 178
Sun Mar 30 15:27:08 2014
RFS[5]: Assigned to RFS process 5716
RFS[5]: Identified database type as 'physical standby': Client
is Foreground pid 4829
Standby switchover readiness check: Checking whether
recoveryapplied all redo..
Database not available for switchover
End-Of-REDO archived
log file has not been recovered
Physical Standby did not apply all the redo from the primary.
Sun Mar 30 15:27:08 2014
RFS[6]: Assigned to RFS process 5718
RFS[6]: Identified database type as 'physical standby': Client
is ARCH pid 4869
Sun Mar 30 15:27:08 2014
RFS[7]: Assigned to RFS process 5724
RFS[7]: Identified database type as 'physical standby': Client
is Foreground pid 4829
RFS[7]: Opened log for thread 1 sequence 178 dbid 1224670660
branch 840221191
Archived Log entry 223 added for thread 1 sequence 178 rlc
840221191 ID 0x4930caf9 dest 2:
Sun Mar 30 15:27:08 2014
RFS[8]: Assigned to RFS process 5728
RFS[8]: Identified database type as 'physical standby': Client
is Foreground pid 4829
Standby switchover readiness check: Checking whether
recoveryapplied all redo..
Database not available for switchover
End-Of-REDO archived
log file has been received
End-Of-REDO archived
log file has not been recovered
Archived log files
detected beyond End-Of-REDO
Incomplete recovery
SCN:0:1141681 archive SCN:0:1162012
Physical Standby did not apply all the redo from the primary.
Sun Mar 30 15:27:10 2014
RFS[9]: Assigned to RFS process 5732
RFS[9]: Identified database type as 'physical standby': Client
is Foreground pid 4829
Standby switchover readiness check: Checking whether
recoveryapplied all redo..
Database not available for switchover
End-Of-REDO archived
log file has been received
End-Of-REDO archived
log file has not been recovered
Archived log files
detected beyond End-Of-REDO
Incomplete recovery SCN:0:1141681
archive SCN:0:1162012
Physical Standby did not apply all the redo from the primary.
Sun Mar 30 15:27:11 2014
RFS[10]: Assigned to RFS process 5736
RFS[10]: Identified database type as 'physical standby':
Client is Foreground pid 4829
Standby switchover readiness check: Checking whether
recoveryapplied all redo..
Database not available for switchover
End-Of-REDO archived
log file has been received
End-Of-REDO archived
log file has not been recovered
Archived log files
detected beyond End-Of-REDO
Incomplete recovery
SCN:0:1141681 archive SCN:0:1162012
Physical Standby did not apply all the redo from the primary.
Sun Mar 30 15:27:11 2014
Media Recovery Log
/u01/app/oracle/flash_recovery_area/ORCL2/archivelog/2014_03_30/o1_mf_1_178_9mhtmnn2_.arc
Identified
End-Of-Redo for thread 1 sequence 178
Resetting
standby activation ID 1227934457 (0x4930caf9)
Resetting standby activation ID 1227934457 (0x4930caf9)
Media Recovery Waiting for thread 1 sequence 179
Sun Mar 30 15:27:12 2014
RFS[11]: Assigned to RFS process 5740
RFS[11]: Identified database type as 'physical standby':
Client is Foreground pid 4829
Standby switchover readiness check: Checking whether
recoveryapplied all redo..
Physical
Standby applied all the redo from the primary.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 174
Next log sequence to archive 0
Current log sequence 178
5.
Stop Redo Apply at standby (DG2)
alter
database recover managed standby database cancel;
6.
Finish redo apply
alter
database recover managed standby database finish force;
FORCE option stops the RFS process
on the standby database so that failover
can proceed immediately without waiting for
network connections to timeout.
Standby
Log:
Attempt
to do a Terminal Recovery (orcl2)
Media Recovery Start: Managed Standby Recovery (orcl2)
started logmerger process
Sun Mar 30 15:46:40 2014
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
Media Recovery Waiting for thread 1 sequence 179
RECOVER
FINISH applied through switchover EOR logs and stopped.
Media Recovery
Complete: End-Of-REDO (orcl2)
select
recid, first_change#, next_change# , sequence#, blocks, status, applied, END_OF_REDO, END_OF_REDO_TYPE,
fal from v$archived_log;
RECID FIRST_CHANGE#
NEXT_CHANGE# SEQUENCE# BLOCKS S APPLIED END END_OF_RED FAL
---------- ------------- ------------ ---------- ---------- -
--------- --- ---------- ---
210 1128234 1128261 166 34 A YES YES SWITCHOVER NO
211 1128234 1128261 166 34 A NO YES SWITCHOVER NO
212 1130130 1130134 169 4 A YES NO YES
213 1128261 1130111 167 2783 A YES NO YES
214 1130111 1130130
168 1 A YES NO YES
215 1131085 1134447 171
17515 A YES NO NO
216 1130134 1131085 170 2365 A YES NO YES
217 1134447 1134454 172 5 A YES NO NO
218 1134454 1134488 173 33 A YES NO NO
219 1134488 1141238 174 8378 A YES NO NO
220
1141238 1141351 175 141 A YES NO NO
221 1141351 1141354 176 1 A YES NO NO
222 1141354 1141681 177 175 A YES NO
NO
223 1141681 1162012 178 454 A YES YES SWITCHOVER NO
If REDO APPLY FINISH is successful, goto step 7.
If errors, then
-
activate standby database | alter
database activate physical standby database;
-
goto step 9
7.
check switchover status of the standby(DG2)
SQL>
select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
8.
Switch physical standby to primary role
alter
database commit to switchover to primary with session shutdown;
Standby Logfile:
ALTER
DATABASE SWITCHOVER TO PRIMARY (orcl2)
Maximum wait for role transition is 15 minutes.
Backup controlfile written to trace file
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_4676.trc
SwitchOver after complete recovery through change 1162012
Online log /u01/app/oracle/oradata/orcl2/redo01.log: Thread 1
Group 1 was previously cleared
Online log /u01/app/oracle/oradata/orcl2/redo02.log: Thread 1
Group 2 was previously cleared
Online log /u01/app/oracle/oradata/orcl2/redo03.log: Thread 1
Group 3 was previously cleared
Standby
became primary SCN: 1162010
Switchover:
Complete - Database mounted as primary
Completed: alter database commit to switchover to primary with
session shutdown
SQL>
select instance_name, name, primary_db_unique_name, switchover_status,
open_mode, flashback_on, force_logging, database_role from v$database,
v$instance;
INSTANCE_NAME
NAME
PRIMARY_DB_UNIQUE_NAME
SWITCHOVER_STATUS OPEN_MODE FLASHBACK_ON FOR DATABASE_ROLE
---------------- --------- ------------------------------
-------------------- -------------------- ------------------ ---
----------------
orcl2
ORCL1 orcl1 FAILED
DESTINATION READ WRITE YES YES PRIMARY
9.
Reinstate the failed primary back as a
physical standby
The failed primary still assumes
the Primary Role, and we will reinstate it as standby.
SQL> select instance_name, name, primary_db_unique_name, switchover_status,
open_mode, flashback_on, force_logging, database_role from v$database,
v$instance;
INSTANCE_NAME
NAME
PRIMARY_DB_UNIQUE_NAME
SWITCHOVER_STATUS OPEN_MODE FLASHBACK_ON FOR DATABASE_ROLE
---------------- ------- ------------------------------
-------------------- -------------------- ------------------ ---
----------------
orcl1 ORCL1 orcl2 NOT ALLOWED MOUNTED YES YES PRIMARY
i.
Determine
SCN at which old standby became new primary
SQL>
select standby_became_primary_scn from v$database;
STANDBY_BECAME_PRIMARY_SCN
--------------------------
1162010
ii.
Flashback
failed primary
SQL>
flashback database to SCN 1162010;
Flashback complete.
iii.
Convert
primary to failed physical standby
alter
database convert to physical standby;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (orcl1)
Clearing standby activation ID 1227934457 (0x4930caf9)
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
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;
Archivelog for thread 1 sequence 178 required for
standby recovery
Completed: alter database convert to physical standby
This
statement dismounts the DB, so we need to mount it again.
shutdown
immediate;
startup mount;
SQL>
select instance_name, name, primary_db_unique_name, switchover_status,
open_mode, flashback_on, force_logging, database_role from v$database,
v$instance;
INSTANCE_NAME NAME
PRIMARY_DB_UNIQUE_NAME
SWITCHOVER_STATUS OPEN_MODE FLASHBACK_ON FOR DATABASE_ROLE
---------------- ------- ------------------------------
-------------------- -------------------- ------------------ ---
----------------
orcl1 ORCL1 orcl2 TO PRIMARY MOUNTED YES YES PHYSICAL STANDBY
iv.
Check
the status of the archive destinations at the primary site (DG2)
SQL>
select dest_id, dest_name, status, protection_mode, destination, error,srl from
v$archive_dest_status where dest_id=2;
DEST_ID DEST_NAME STATUS PROTECTION_MODE DESTINATION ERROR SRL
---------- -------------------- --------- --------------------
------------------------------ ------------------------------ ---
2 LOG_ARCHIVE_DEST_2 VALID
MAXIMUM AVAILABILITY orcl1
YES
v.
Start
redo apply at the reinstated database
alter database recover
managed standby database using current logfile disconnect;
Excelente Post !!!! felicidades
ReplyDelete