Oracle Data Guard (Standby DB using DUPLICATE command)

Configuring standby database from primary database (Active Database)

Primary Database = rman

Standby Database = rman2

Important  Parameter

For primary db:

1. db_name = rman (use same name for all standby databases)

2. db_unique_name = rman (a unique db name to uniquely identify primary and standby db because db_name is same)       

3 *.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/rman' (destination for local archive)

4. *.log_archive_dest_2='service=rman2   ( destination for standby database) 

5.  Standby_file_management= auto (it automatically create file at standby db site)

Note : STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.

For Standby db:

initrman2.ora                                                                                        //this is my ora file for standby

*.db_name='rman'

*.db_unique_name='rman2'

*.db_file_name_convert='/u01/app/oracle/oradata/rman','/u01/app/oracle/oradata/rman2'

   //specify the path name and datafile location of primary database

    datafile followed by standby location

*.log_file_name_convert='/u01/app/oracle/oradata/rman','/u01/app/oracle/oradata/rman2'

//specify the path name and redo logfile location of primary

    database redo logfile followed by standby location

*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/rman2'

//location of archives coming from primary   database

*.fal_server=rman  //the value is tns service name, to fetch archived log from primary site when log gap is detected at standby site

*.fal_client=rman2   //the value is tns service name to copies missing archive to standby

   database.

 
               Steps for configuring standby database

Both side create listener and  Tns

Both side create password file with same password

Both side db_name must be same

At standby database side

Configure listerner on standby side and start the listener

start the standby database in nomount state

SQL> startup nomount

ORACLE instance started.

Total System Global Area  313860096 bytes

Fixed Size                               1336232 bytes

Variable Size                       197135448 bytes

Database Buffers               109051904 bytes

Redo Buffers                         6336512 bytes

Now on Primary Side ,

Do the Below command , to connect with RMAN

SQL> !rman target sys/oracle auxiliary sys/oracle@rman2

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 19 19:01:13 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RMAN (DBID=1792754794)

connected to auxiliary database: RMAN (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 19-MAR-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

contents of Memory Script:

{

   backup as copy reuse

   targetfile  '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwrman' auxiliary format

 '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwrman2'   ;

}

executing Memory Script

Starting backup at 19-MAR-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=47 device type=DISK

Finished backup at 19-MAR-16

contents of Memory Script:

{

   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/product/11.2.0/db_1/dbs/cntrlrman2.dbf';

}

executing Memory Script

Starting backup at 19-MAR-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_rman.f tag=TAG20160319T190131 RECID=5 STAMP=906922893

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 19-MAR-16

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/rman2/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/rman2/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/rman2/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/rman2/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/rman2/users01.dbf";

   set newname for datafile  6 to

 "/u01/app/oracle/oradata/rman2test01";

   backup as copy reuse

   datafile  1 auxiliary format

 "/u01/app/oracle/oradata/rman2/system01.dbf"   datafile

 2 auxiliary format

 "/u01/app/oracle/oradata/rman2/sysaux01.dbf"   datafile

 3 auxiliary format

 "/u01/app/oracle/oradata/rman2/undotbs01.dbf"   datafile

 4 auxiliary format

 "/u01/app/oracle/oradata/rman2/users01.dbf"   datafile

 6 auxiliary format

 "/u01/app/oracle/oradata/rman2test01"   ;

   sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/rman2/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 19-MAR-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/rman/system01.dbf

output file name=/u01/app/oracle/oradata/rman2/system01.dbf tag=TAG20160319T190141

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/rman/sysaux01.dbf

output file name=/u01/app/oracle/oradata/rman2/sysaux01.dbf tag=TAG20160319T190141

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/rman/undotbs01.dbf

output file name=/u01/app/oracle/oradata/rman2/undotbs01.dbf tag=TAG20160319T190141

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/u01/app/oracle/oradata/rmantest01

output file name=/u01/app/oracle/oradata/rman2test01 tag=TAG20160319T190141

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/rman/users01.dbf

output file name=/u01/app/oracle/oradata/rman2/users01.dbf tag=TAG20160319T190141

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 19-MAR-16

sql statement: alter system archive log current

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=906922992 file name=/u01/app/oracle/oradata/rman2/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=906922992 file name=/u01/app/oracle/oradata/rman2/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=906922992 file name=/u01/app/oracle/oradata/rman2/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=906922992 file name=/u01/app/oracle/oradata/rman2/users01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=9 STAMP=906922992 file name=/u01/app/oracle/oradata/rman2test01

RMAN-05535: WARNING: All redo log files were not defined properly.

RMAN-05535: WARNING: All redo log files were not defined properly.

RMAN-05535: WARNING: All redo log files were not defined properly.

Finished Duplicate Db at 19-MAR-16

Now on Standby Side , do the Below Command

SQL> alter database recover managed standby database disconnect;

// This is used to start MRP (media recovery process) process

Other Basic Commands

How to stop mrp process

SQL> alter database recover managed standby database cancel;

How to check mrp process is running or not

$ps –ef |grep mrp

How to Start the Standby db in read only mode

SQL> alter database recover managed standby database cancel ;  // stops the MRP process

SQL> alter database open ;    // opens in read only mode

Necessary Views, not to Forget :

1. v$archive_dest              - To check if any error in transferring archives
2. v$managed_standby    - Current status information for oracle database process

3  v$archived_log       - To check how many archives are applied at standby database                                                Side

Common Error I faced During Setup :

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04005: error from target database:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Solution  :

Add  (UR = A) in TnsNames.ora file , after your Service Name

Example :

..

RMAN2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracleasm1.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = rman2)

                (UR = A)

    )

  )

Error 2 :

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04005: error from target database:

ORA-12154: TNS:could not resolve the connect identifier specified

OR

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04005: error from target database:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Solution :

Check Yout TnsNames.ora  & also Listener.ora File.

Check the opening brackests and Closing Brackets.

Also Check Database SID is Properly specified or Not.

Ooho.. ..  So Big Task. . . Right  ???  :D

 So Guys , Now Lets Learn ROLE - TRANSFER …  How to Perform SWITCHOVER

STEP 1

On Primary Site :

SQL> select database_role , switchover_status from v$database;

DATABASE_ROLE              SWITCHOVER_STATUS

-----------------------          -----------------------------

PRIMARY                             TO STANDBY

Note : Primary DATABASE should be in ‘Open Mode’

SQL> alter database commit to switchover to physical standby;

Database altered.

OR          You can Use Below Query , in case Above Query Gives Error

SQL> alter database commit to switchover to PHYSICAL STANDBY with SESSION SHUTDOWN;

Primary > Shutdown Immediate

//Standby is Still either  ’Mounted’ in redo apply mode or open mode for ‘read only ‘access.

STEP 2

Now, Lets Go  to  Standby Site

You will see Pending Status , see

SQL> select database_role , switchover_status from v$database;

DATABASE_ROLE              SWITCHOVER_STATUS

---------------- --------------------------------------------

PHYSICAL STANDBY         SWITCHOVER PENDING

So now, we will switch Standby site to primary, Lets do it with below command

Standby site :

SQL>  alter database commit to switchover to primary;

 alter database commit to switchover to primary

*

ERROR at line 1:

ORA-16139: media recovery required

Oops , media Recovery is Recovered here..

So I will do

SQL> recover managed standby database finish;

Primary Site - - -  >SQL> startup mount                           //Mount the Primary Site

Done, Now check the Status Using Below Query Standby Site

SQL> select database_role , switchover_status from v$database;

DATABASE_ROLE              SWITCHOVER_STATUS

---------------- ----------------------------------------------

PRIMARY                            TO STANDBY

Check on Primary Site - - -

SQL> select database_role , switchover_status from v$database;

DATABASE_ROLE              SWITCHOVER_STATUS

---------------- -----------------------------------------------

PHYSICAL STANDBY         TO PRIMARY

ERROR FACED during SWITCHOVER

SQL>  alter database commit to switchover to primary;

 alter database commit to switchover to primary

*

ERROR at line 1:

ORA-16139: media recovery required

SQL> recover managed standby database finish;

ORA-00283: recovery session canceled due to errors

ORA-01111: name for data file 5 is unknown - rename to correct file

ORA-01110: data file 5: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005'

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01111: name for data file 5 is unknown - rename to correct file

ORA-01110: data file 5: '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005'

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00005' as '/u01/app/oracle/oradata/rman2/demots.dbf';

Database altered.

SQL> recover managed standby database finish;

Media recovery complete.

SQL>  alter database commit to switchover to primary;

Database altered.

SQL>