Monday, December 15, 2014

Rebuilding Standby Database Manually (11g and up)

One of my remote customers has been facing severe network outages between Production and DR sites to reasons unknown and challenges with certain geographic limitations, the bandwidth between sites is limited to 2MBPS and the replication between sites is aching slow. So, Customer has had tentative requirements to rebuild Standby more often between sites until the network issue is fully resolved.

This paper outlines the procedure to rebuild the STANDBY manually provided we have following things in place already between/on sites. And this procedure has a special case for One node RAC, Oracle dynamically applies SID naming convention in case of an online relocation. Due to this fact, I have to mention which SID to export before logging to the Database via SQLPLUS/RMAN/ASMCMD
(PR refers to - Production/Primary and PR1/PR2 are two nodes in RAC,  DR refers to - Disaster Recovery/Standby and DR1/DR2 are two nodes in RAC at DR Site)

  • A working Dataguard Configuration between Sites (All Parameters on both PR and DR)
  • A working Oracle Networking Configuration between sites (including Listener  and TNS)
  • A working RMAN backup
  • A RAC/One Node RAC/Single Node Oracle setup in place
  • ASM
  • Level 1 Skillset Oracle DBA Resource

In case if you would like to know how to configure Dataguard, Please refer to my post


On Production/Primary/PR1/PR2 (oracle os user prompt)

Find which SID is running
$ps -ef|grep pmon

Get the SID name and export it
$export ORACLE_SID=PRDB_1/PRDB_2

Create a directory (create this on DR too and create this on all nodes PR2/DR1/DR2)
$mkdir -p /backup/rman/4standby

Login to RMAN and take the copy of controlfile for STANDBY
$rman target /

Do log switches
RMAN>sql 'alter system switch logfile';
RMAN> sql 'alter system switch logfile';

Take the backup of controlfile for standby and full Database
RMAN>configure device type disk parallelism 10 backup type to backupset;
RMAN>configure channel device type disk format '/backup/rman/4standby/%d_% s_%p_%t';
RMAN>copy current controlfile for standby to '/backup/rman/4standby/standby_PR.ctl';
the above command dumps the controlfile to /backup/rman/4standby, SCP it to DR site to the same location '/backup/rman/4standby/'
RMAN>backup section size 500m as compressed backupset database;
RMAN>exit;

Disable the destination if it's running
$sqlplus / as sysdba
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SID='*';

Now take a copy of this backup to DR and keep it in the same location '/backup/rman/4standby'


Now we are on DR (DR/Standby/DR1/DR2)(oracle os user prompt)

Start the DR instance (on DR1/DR2)
$srvctl start database -d PRDB -o nomount

Find which SID is running
$ps -ef|grep pmon

Get the SID name and export it
$export ORACLE_SID=PRDBDR_1/PRDBDR_2

Login to RMAN and restore the controlfile and backup
$rman target /
RMAN>restore controlfile from '/backup/rman/4standby/standby_PR.ctl';
RMAN>sql 'alter database mount standby database';
RMAN>catalog start with '/backup/rman/4standby' noprompt';
RMAN>restore database;
RMAN>exit;


On PR at this time(oracle os user prompt)

Enable the destination to ship archivelogs
$sqlplus / as sysdba
SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SID='*';


On DR at this time (grid os user prompt)

Remove existing STANDBY Logfiles
Login as grid user
Find which ASM instance is running
$ps -ef|grep pmon

Get the ASM instance name and export it
export ORACLE_SID=+ASM1/+ASM2

Login to asmcmd” prompt
asmcmd>cd +PRREDO1/PRDB
asmcmd>rm sr* (Choose “y” on prompt)
asmcmd>cd +PRREDO2/PRDB
asmcmd>rm sr* (Choose “y” on prompt)
asmcmd>exit;

Back to oracle user prompt(oracle os user prompt)
Login to SQLPLUS prompt as SYSDBA
$sqlplus / as sysdba

Recreate Standby Logfiles
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO1/PRDB/srl1.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO2/PRDB/srl2.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO1/PRDB/srl3.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO2/PRDB/srl4.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO1/PRDB/srl5.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO2/PRDB/srl6.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO1/PRDB/srl7.f' SIZE 536870912;
SQL>ALTER DATABASE ADD STANDBY LOGFILE '+PRREDO2/PRDB/srl8.f' SIZE 536870912;

Open the Database in Managed Recovery mode
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


 Monitor the shipping and logs apply status
SQL>SELECT SEQUENCE#,BLOCK#,PROCESS,STATUS FROM V$MANAGED_STANDBY;


On PR at this time(oracle os user prompt)

Keep monitoring the logs apply status until the the previous sequence to current sequence updated with status "YES" under applied column from the below query
$sqlplus / as sysdba
SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG WHERE SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG);

The updated status "YES" for the current log sequence at PR confirms the synchronisation with its DR peer.


On DR at the time(oracle os user prompt)

Enable Active Dataguard mode
$sqlplus / as sysdba
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>ALTER DATABASE OPEN READ ONLY;
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL><Query any application table to monitor the Active transactional SYNC which should match with PR transaction output>

If this has helped you, please do not forget share it and help others.

Good Luck!

Tuesday, February 11, 2014

Oracle Database 12c - Far Sync Instance



A New Standby destination type is allowed in 12c Active Dataguard environment that ensures zero data loss failover and zero performance impact on Primary Database by a new concept called "Far Sync Instance". 


What is Far Sync Instance?
As we knew so far (until 11g), A SYNCHRONOUS redo transport Dataguard configuration impacts the availability and performance of Primary Database in order to protect data loss that happens due to network outages with its Standby Site. To avoid such issues and give benefit of the same zero Data loss to customers at additional cost, Oracle has introduced a new type of Instance called Far Sync Instance.  A Far Sync Instance usually is very close in distance to its Primary Database Network receives the redo synchronously from its Primary partner and then pushes it to its remote Standby Partner, plays a dummy physical standby role that actually fakes its Primary Partner to forget network outages as it takes the responsibility of managing redo to actual Standby targets. As a result of it, It minimises the unplanned downtime and performance issues due to Wider Area Network latencies or simply Network latencies. 


What does it contain?
This destination contains parameterfile, controlfile, and standby redo logs (SRLs), it receives the redo from Primary Database, and archives it to local destination through Standby Redo Logs. There are no Datafiles here. As a result of it, it consumes very little disk and processing resources, yet provides the ability to failover to a terminal destination with zero data loss, as well as offload the primary database of other types of overhead (for example, redo transport).

How to configure?
(Same as configuring Dataguard except backup/restore operations of the Primary Database)
1)Create pfile from spfile of the Primary, and copy to to Far Sync Server, and start the Instance
2)Add the Far Sync Destination, EX: "CDBFS"(Net Service Name/DB_UNIQUE_NAME), to Dataguard configuration (under LOG_ARCHVE_CONFIG=DG_CONFIG)
3)Set a new destination parameter to enable Log Transport Services, EX: LOG_ARCHIVE_DEST_3 & LOG_ARCHIVE_DEST_STATE_3
4)Create the Far Sync Instance Control file, copy it to FSI Server, and mount
SYS@CDB AS SYSDBA 28-OCT-13> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/fra/fsi_control01.ctl';
Database altered.

5)Enable the destination to FSI on Primary

FSI now receives the redo by establishing RFS connections to its primary

Hope it helps, your feedback is appreciated, and thanks for reading..