Create Physical standby database using RMAN -- ORACLE DATAGUARD

Prepare the Standby database:

1. Put the database is in Archivelog mode

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

Confirmation:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 17
Next log sequence to archive 19
Current log sequence 19

2. Add Listener details for Standby database:

DR01 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba.asteroid.com)(PORT = 1522))
)
)

3. Create init.ora for standby with just DB_NAME:

[oracle@dba dbs]$ echo 'DB_NAME=DR01' > $ORACLE_HOME/dbs/initDR01.ora

4. Create password file for Standby

orapwd file=$ORACLE_HOME/dbs/orapwdr01 password=0racle
Make sure to use the same password as the SYS user password of PRIMARY database.

5. Start the Standby DB in nomount state.

Prepare the Primary database:

1. Create SRL on Primary database; this is needed for standby creation RMAN will then create this for you on Standby.

SQL> select GROUP#, THREAD#, MEMBERS from v$log;

GROUP# THREAD# MEMBERS
---------- ---------- ----------
1 1 1
2 1 1
3 1 1

Create the Standby using RMAN:

Run the below command using RMAN, I’m running this from Standby database node since there is an issue with the listener if you try to connect this from Primary database. You may end up with the below issue:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-12154: TNS:could not resolve the connect identifier specified
This happens because the listener process gets registered only after the database is in the mount state, whereas our database is in Standby state.

Add the STATIC REGISTRATION DR init.ora:

LOCAL_LISTENER=DR01

Connect from Primary

[oracle@dba oradata]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 22 00:22:35 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (DBID=4049196380)
RMAN> connect auxiliary sys/0racle@DR01;
connected to auxiliary database: DR01 (not mounted)
RMAN> run {
Allocate channel prm01 type disk;
Allocate channel prm02 type disk;
Allocate channel prm03 type disk;
Allocate channel prm04 type disk;
Allocate channel prm05 type disk;
Allocate auxiliary channel stb01 type disk;
Duplicate target database for standby from active database
db_file_name_convert=('/u01/app/oracle/oradata/prim','/u01/app/oracle/oradata/DR01')
spfile
set 'db_unique_name'='DR01'
Set control_files='/u01/app/oracle/oradata/DR01/control01.ctl';
}

alter system set log_archive_dest_2 = 'service="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dba.asteroid.com)(PORT=1523))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DR01)))"' ;

using target database control file instead of recovery catalog
allocated channel: prm01
channel prm01: SID=44 device type=DISK

allocated channel: prm02
channel prm02: SID=41 device type=DISK

allocated channel: prm03
channel prm03: SID=40 device type=DISK

allocated channel: prm04
channel prm04: SID=42 device type=DISK

allocated channel: prm05
channel prm05: SID=36 device type=DISK

allocated channel: stb01
channel stb01: SID=19 device type=DISK

Starting Duplicate Db at 22-AUG-11

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprim' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwDR01' targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileprim.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDR01.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDR01.ora''";
}
executing Memory Script

Starting backup at 22-AUG-11
Finished backup at 22-AUG-11

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileDR01.ora''

contents of Memory Script:
{
sql clone "alter system set db_unique_name =
''DR01'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/DR01/control01.ctl'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_unique_name = ''DR01'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/u01/app/oracle/oradata/DR01/control01.ctl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 401743872 bytes

Fixed Size 1336820 bytes
Variable Size 247466508 bytes
Database Buffers 146800640 bytes
Redo Buffers 6139904 bytes
allocated channel: stb01
channel stb01: SID=19 device type=DISK

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/DR01/control01.ctl';
}
executing Memory Script

Starting backup at 22-AUG-11
channel prm01: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_prim.f tag=TAG20110822T060431 RECID=13 STAMP=759823472
channel prm01: datafile copy complete, elapsed time: 00:00:03
Finished backup at 22-AUG-11

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/DR01/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/DR01/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/DR01/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/DR01/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/DR01/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/DR01/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/DR01/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/DR01/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/DR01/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/DR01/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/DR01/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DR01/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 22-AUG-11
channel prm01: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/prim/system01.dbf
channel prm02: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
channel prm03: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
channel prm04: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
channel prm05: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
output file name=/u01/app/oracle/oradata/DR01/example01.dbf tag=TAG20110822T060445
channel prm04: datafile copy complete, elapsed time: 00:00:26
output file name=/u01/app/oracle/oradata/DR01/users01.dbf tag=TAG20110822T060445
channel prm05: datafile copy complete, elapsed time: 00:00:28
output file name=/u01/app/oracle/oradata/DR01/undotbs01.dbf tag=TAG20110822T060445
channel prm03: datafile copy complete, elapsed time: 00:00:46
output file name=/u01/app/oracle/oradata/DR01/system01.dbf tag=TAG20110822T060445
channel prm01: datafile copy complete, elapsed time: 00:01:36
output file name=/u01/app/oracle/oradata/DR01/sysaux01.dbf tag=TAG20110822T060445
channel prm02: datafile copy complete, elapsed time: 00:02:07
Finished backup at 22-AUG-11

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=13 STAMP=759823618 file name=/u01/app/oracle/oradata/DR01/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=759823618 file name=/u01/app/oracle/oradata/DR01/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=759823618 file name=/u01/app/oracle/oradata/DR01/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=759823618 file name=/u01/app/oracle/oradata/DR01/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=759823618 file name=/u01/app/oracle/oradata/DR01/example01.dbf
Finished Duplicate Db at 22-AUG-11
released channel: prm01
released channel: prm02
released channel: prm03
released channel: prm04
released channel: prm05
released channel: stb01
SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
DR01 MOUNTED

MANUALLY ADD THE STANDBY AND PRIMARY INITIALIZATION ROLE FOR THE PRIMARY:

SQL> alter system set FAL_SERVER=PRIM;
System altered.
SQL> alter system set FAL_CLIENT=DR01;
System altered.
SQL> alter system set log_archive_config='DG_CONFIG=(PRIM,DR01)';

System altered.
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='service=PRIM LGWR ASYNC VALID_FOR=(primary_role,online_logfile) DB_UNIQUE_NAME=DR01';
System altered.

START THE APPLY PROCESS:

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

CONFIGURE PRIMARY REDO TRANSPORT:

SQL> alter system set FAL_SERVER=DR01;

System altered.

SQL> alter system set FAL_CLIENT=PRIM;

System altered.

SQL> alter system set log_archive_config='DG_CONFIG=(PRIM,DR01)';

System altered.

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='service=DR01 LGWR ASYNC VALID_FOR=(primary_role,online_logfile) DB_UNIQUE_NAME=DR01';

System altered.