Business continuity (Disaster Recovery) has become a very
critical factor for every business, especially in the financial sectors. Most
of the banks are tending to have their regular DR test to meet the central bank
regulation on DR testing capabilities.
Very recently, there was a request from one of the clients to perform a reverse replication and rollback (i.,e switchover & switchback) between the HO and DR for one of the business
critical databases. Similar activities performed with
easy on pre 12c databases. However, this was my first experience with Oracle 12c. After spending a bit of time to explore whats new in 12c Switchover, it was amazing to learn how 12c simplified the procedure. So, I decided to write a post on my experience.
This post demonstrates how Switchover and Switchback procedure is
simplified in Oracle 12c.
The following is used in the scenario:
·
2 instances Oracle 12c RAC primary database (IMMPRD)
·
Single instance Oracle 12c RAC Standby database
(IMMSDB)
Look at the current status of the both databases:
-- Primary
IMMPRD>
select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------
---------------- ----------------
OPEN IMMPRD1 PRIMARY
-- Standby
IMMSDB>
select status,instance_name,database_role from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE
------------
---------------- ----------------
OPEN IMMSDB1 PHYSICAL STANDBY
Before getting into the real action, validate the
following to avoid any failures during the course of role transition:
·
Ensure log_archive_dest_2 is configured on
PRIMARY and STANDBY databases
·
Media Recovery Process (MRP) is active on STANDBY
and in sync with PRIMARY database
· Create STANDBY REDO logs on PRIMARY, if not exists
·
FAL_CLIENT & FAL_SERVER parameters set on
both databases
·
Verify TEMP tablespaces on STANDBY, add them if
required, as TEMPFFILES created after STANDBY creation won’t be propagated to
STANDBY site.
Pre-Switchover in
12c
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
Let’s run the command on the primary database to validate
if the environments are ready for the role transition.
IMMPRD> alter database switchover to IMMSDB verify;
alter database switchover to IMSDB verify
*
ERROR
at line 1:
ORA-16475:
succeeded with warnings, check alert log for more details
When the command is executed, an ORA-16475 error was encountered.
For more details, lets walk through the PRIMARY and STANDBY databases alert.log
file, and pay attention to the SWITCHOVER VERIFY WARNING.
--primary
database alert.log
Fri
Oct 13 11:16:00 2017
SWITCHOVER
VERIFY: Send VERIFY request to switchover target IMSDB
SWITCHOVER
VERIFY COMPLETE
SWITCHOVER
VERIFY WARNING: switchover target has no standby database defined in
LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a
primary database, the new primary database will not be protected.
ORA-16475
signalled during: alter database
switchover to IMSDB verify...
The LOG_ARCHIVE_DEST_2 parameter was not set on the
STANDBY database and the VERIFY command produced the warning. After setting the
parameter on the STANDBY, the verify command was re-ran, and it went well this
time.
IMMPRD>
alter database switchover to IMMSDB verify;
Database
altered.
PRIMARY
database alert.log confirms no WARINGS
alter
database switchover to IMMSDB verify
Fri
Oct 13 08:49:20 2017
SWITCHOVER
VERIFY: Send VERIFY request to switchover target IMMSDB
SWITCHOVER
VERIFY COMPLETE
Completed:
alter database switchover to IMMSDB verify
Switchover in
12c
After successful validation and confirmation about the DBs
readiness for the role transition, execute the actual switchover command on the
primary database. (advised to view the alert.log files of PRIMARY and
STANDBY instances).
IMMPRD>
alter database switchover to IMMSDB;
Database
altered.
Let’s walk through the PRIMARY and STANDBY database
alert.log files to review what Oracle has internally done.
--primary
database alert.log
alter
database switchover to IMMSDB
Fri
Oct 13 08:50:21 2017
Starting
switchover [Process ID: 302592]
Fri
Oct 13 08:50:21 2017
ALTER DATABASE COMMIT TO
SWITCHOVER TO PHYSICAL STANDBY [Process Id: 302592] (IMMPRD1)
Waiting
for target standby to receive all redo
Fri
Oct 13 08:50:21 2017
Waiting
for all non-current ORLs to be archived...
Fri
Oct 13 08:50:21 2017
All
non-current ORLs have been archived.
Fri
Oct 13 08:50:21 2017
Waiting
for all FAL entries to be archived...
Fri
Oct 13 08:50:21 2017
All
FAL entries have been archived.
Fri
Oct 13 08:50:21 2017
Waiting for dest_id 2 to
become synchronized...
Fri
Oct 13 08:50:22 2017
Active, synchronized
Physical Standby switchover target has been identified
Preventing updates and
queries at the Primary
Generating and shipping
final logs to target standby
Switchover
End-Of-Redo Log thread 1 sequence 24469 has been fixed
Switchover
End-Of-Redo Log thread 2 sequence 23801 has been fixed
Switchover:
Primary highest seen SCN set to 0x960.0x8bcd0f48
ARCH:
Noswitch archival of thread 2, sequence 23801
ARCH:
End-Of-Redo Branch archival of thread 2 sequence 23801
ARCH:
LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
ARCH:
Standby redo logfile selected for thread 2 sequence 23801 for destination
LOG_ARCHIVE_DEST_2
ARCH:
Noswitch archival of thread 1, sequence 24469
ARCH:
End-Of-Redo Branch archival of thread 1 sequence 24469
ARCH:
LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
ARCH:
Standby redo logfile selected for thread 1 sequence 24469 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
Backup
controlfile written to trace file
/u01/app/oracle/diag/rdbms/imprd/IMPRD1/trace/IMPRD1_ora_302592.trc
Converting
the primary database to a new standby database
Clearing
standby activation ID 627850507 (0x256c3d0b)
The
primary database controlfile was created using the
'MAXLOGFILES
192' clause.
There
is space for up to 186 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 104857600;
ALTER
DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 104857600;
ALTER
DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 104857600;
ALTER
DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 104857600;
ALTER
DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 104857600;
ALTER
DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 104857600;
ALTER
DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 104857600;
Archivelog
for thread 1 sequence 24469 required for standby recovery
Archivelog
for thread 2 sequence 23801 required for standby recovery
Switchover:
Primary controlfile converted to standby controlfile succesfully.
Switchover complete.
Database shutdown required
USER
(ospid: 302592): terminating the instance
Fri
Oct 13 08:50:44 2017
Instance
terminated by USER, pid = 302592
Completed: alter database
switchover to IMMSDB
Shutting
down instance (abort)
--standby
database alert.log
SWITCHOVER: received request
'ALTER DTABASE COMMIT TO SWITCHOVER TO
PRIMARY' from primary database.
Fri
Oct 13 08:50:32 2017
ALTER
DATABASE SWITCHOVER TO PRIMARY (IMMSDB1)
Maximum
wait for role transition is 15 minutes.
Switchover:
Media recovery is still active
Role
Change: Canceling MRP - no more redo to apply
SMON:
disabling cache recovery
Fri
Oct 13 08:50:41 2017
Backup
controlfile written to trace file /u01/app/oracle/diag/rdbms/imsdb/IMMSDB1/trace/IMMSDB1_rmi_120912.trc
SwitchOver
after complete recovery through change 10310266982216
Online
logfile pre-clearing operation disabled by switchover
Online
log +DATAC1/IMMSDB/ONLINELOG/group_1.3018.922980623: Thread 1 Group 1 was
previously cleared
Standby
became primary SCN: 10310266982214
Switchover:
Complete - Database mounted as primary
SWITCHOVER: completed
request from primary database.
Fri
Oct 13 08:51:11 2017
At this point-in-time, the new PRIMARY database is in
MOUNT state, so you need to OPEN the database.
IMMSDB>
alter database open
And
startup the STANDBY database and enable MRP: (below is the active standby database command)
IMMPRD>
startup
IMMPRD>
recover managed standby database using current logfile disconnect from session;
Post Switchover,
run through the following:
IMMSDB>
alter system switch logfile;
IMMSDB>
select dest_id,error,status from v$archive_dest where dest_id=2;
IMMSDB>
select max(sequence#),thread# from v$log_history group by thread#;
IMMSDB> select
max(sequence#) from v$archived_log where applied='YES' and dest_id=2;
On Standby database
IMMPRD>
select thread#,sequence#,process,status from gv$managed_standby;
--
in 12.2, use gv$dataguard_status instead of gv$managed_standby view
IMMPRD>
select max(sequence#),thread# from v$archived_log group by thread#;
You can also enable the trace on primary and standby before
performing the role transition to analyze any failures during the procedure.
Use the below procedure on the PRIMARY database to enable the tracing:
SQL>
alter system set
log_archive_trace=8191; -- enabling trace
SQL> alter system set
log_archive_trace=0; --
disabling trace
Switchback
To revert (switch back) to the previous situation, perform the same action. Remember, now, your primary is your previous STANDBY and standby is previous PRIMARY.
References:
12c Data guard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1)
No comments:
Post a Comment