DR test

[Roshan]

Hello Erman,

grateful if you can advise if below steps are ok for a DR drill?

Data guard switchover

Database/Host Details:
Primary database details:
Two Node RAC
IP: 10.242.1.146/48
SCAN: scan-db-dell.mytmoney
SID: PRODDB
Listener port: 1521

Secondary (Standby) database details:
Standalone DR
IP: 10.242.2.37
SID: PRODDBDR
Listener port: 1521
Objective: Perform switchover between primary and secondary so that the roles are reversed. The replication then occurs in the reverse direction.

• Backup database before starting activity (DC)

Rman> run
{
ALLOCATE CHANNEL C1 TYPE DISK '/localbackup/rmanBKP/Db_Full_bkp_%t_%s_%p.bkp';
ALLOCATE CHANNEL C2 TYPE DISK '/localbackup/rmanBKP/Db_Full_bkp_%t_%s_%p.bkp';
ALLOCATE CHANNEL C3 TYPE DISK '/localbackup/rmanBKP/Db_Full_bkp_%t_%s_%p.bkp';
backup database plus archivelog;
backup current controlfile format ‘/localbackup/rmanBKP/proddb_control.bkp’;
}
• Check the max archive log sequence number on DC:

SQL> select max(sequence#),thread# from v$archived_log group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
217 2
422 1

• Check the max applied archive log sequence number on DR:

SQL> select max(sequence#),thread# from v$archived_log where applied='YES' group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
422 1
217 2
The sequence number from DC and DR must be same.

• Check the archive log destination 2 status on both DC and DR:

SQL> select dest_name, status,error from v$archive_dest where dest_name ='LOG_ARCHIVE_DEST_2';
DEST_NAME STATUS ERROR
------------------------------ --------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_2 VALID
The STATUS must be VALID and there must not be any error in ERROR columns.

• Check the switchover status

DC :
SQL> select name,open_mode,database_role,inst_id,switchover_status from gv$database;
NAME OPEN_MODE DATABASE_ROLE INST_ID SWITCHOVER_STATUS
--------- -------------------- ---------------- ---------- --------------------
PRODDB READ WRITE PRIMARY 1 TO STANDBY
PRODDB READ WRITE PRIMARY 2 TO STANDBY
DR:
SQL> select name,open_mode,database_role,inst_id,switchover_status from gv$database;
NAME OPEN_MODE DATABASE_ROLE INST_ID SWITCHOVER_STATUS
--------- -------------------- ---------------- ---------- --------------------
PRODDB MOUNTED PHYSICAL STANDBY 1 NOT ALLOWED
SQL>
Note: On DC, SWITCHOVER_STATUS must be “TO STANDBY” or “NOT ALLOWED”.
On DR, SWITCHOVER_STATUS must be “TO PRIMARY” or “NOT ALLOWED”.

• Convert the primary database to standby:

Execute below on any one node in DC:
SQL> alter database commit to switchover to standby with session shutdown;
Database altered.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.0972E+10 bytes
Fixed Size 12152768 bytes
Variable Size 2214596672 bytes
Database Buffers 8724152320 bytes
Redo Buffers 21397504 bytes
Database mounted.

• Start the second node in mount state.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1.0972E+10 bytes
Fixed Size 12152768 bytes
Variable Size 2214596672 bytes
Database Buffers 8724152320 bytes
Redo Buffers 21397504 bytes
SQL> select name,open_mode,database_role,inst_id,switchover_status from gv$database;
NAME OPEN_MODE DATABASE_ROLE INST_ID SWITCHOVER_STATUS
--------- -------------------- ---------------- ---------- --------------------
PRODDB MOUNTED PHYSICAL STANDBY 1 RECOVERY NEEDED
PRODDB MOUNTED PHYSICAL STANDBY 2 RECOVERY NEEDED
The database role must be physical standby.

• Start the standby media recovery process.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
On standby :

• Check the archive destination 2 status:

SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
DEST_NAME STATUS ERROR
-------------------- --------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_2
The STATUS must be VALID and there must not be any error in ERROR columns.

• Stop the media recovery on standby and switchover to primary::

SQL>alter database recover managed standby database cancel;
Database altered.
SQL>alter database commit to switchover to primary;
Database altered.
SQL>Alter database open;
SQL> select name,open_mode,database_role,inst_id,switchover_status from gv$database;
NAME OPEN_MODE DATABASE_ROLE INST_ID SWITCHOVER_STATUS
--------- -------------------- ---------------- ---------- --------------------
PRODDB READ WRITE PRIMARY 1 RESOLVABLE GAP
Note: For switchback, please follow the same steps from beginning.

Is it easier to use the flasback method instead of the above?
http://ermanarslan.blogspot.com/2016/05/ebs-dr-dataguard-switchover-tests.html

Thanks,
Roshan

[ErmanArslansOracleBlog]

Just follow -> Business Continuity for Oracle E-Business Suite Release 12.2 Using Oracle 12c (12.1.0.2) Physical Standby Database (Doc ID 1963472.1)
-Section 6: Performing Role Transitions, but! Yes.. Guaranteed Restore Point-based DR test method is easier.

[Roshan]

Thanks Erman.

[Roshan]

Hello Erman,

I checked the document for dr test.

http://ermanarslan.blogspot.com/2016/05/ebs-dr-dataguard-switchover-tests.html

When you defer archivelog on the primary database, then start replication on standby, can there be any possibility of data loss?

After you defer the primary log archive destination and open the standby database, this means both primary and standby are open? Normally primary should be open and standby can mounted or readonly.

Suppose I created the checkpoint on standby at 10 am and then opened the database. Then at 11 am I did the fail/switchback. Will there be data loss from 10 am to 11 am? I assume the old primary will have the updated data from 10 am to 11 am.

Regards,

Roshan

[ErmanArslansOracleBlog]

Please revise your questions.

I can't understand.

You defer archivelog destination, and do the replication? How will you do that. There is a logic problem in this question.
You open the database and then what? If you open the standby then yes it will be in open read write status, and it will not be a standby anymore.
What do you mean by switchback/failback? Failback to what? To the standby snapshot? If you fail back to the standby snapshot/guaranteed restore point, then you can sync your standby from primary, no problem with that.

[Roshan]

I revise my question.

On standby database, after you create the restore point and open it read only to make it new primary, the old primary database does it remain open read write or we mount it? I see only log_archive_dest is defered on old primary.

Also, should we set the new archivelog destination on new primary to ship to old primary before open database to read write?

I assume below steps will make standby new primary.

On Standby DB tier:

alter database recover managed standby database cancel;

alter database flashback on;

create restore point OPEN_STANDBY guarantee flashback database;

alter database activate standby database;
<set log archive dest>?

alter database open;

Thanks,

Roshan

[ErmanArslansOracleBlog]

You can always recover your  standby database unless you open it read write. -- as long as you will have the necessary archives in place.
You can also create a restore point on standby and then open your standby database read write, and then you can restore your standby to that restore point and recover / sync your standby.