11g Standby database creation without any RMAN backups

Quick steps to set up a 11g Standby database with Active Data Guard using the Active Duplication feature available in 11g where we can create a standby database without having to take a backup on the primary database. Datafiles are copied over the network.

Primary machine – OATU036
Standby machine – DROU036

Database Name – SID1O

TNS alias for Primary – sid1o_fc
TNS alias for standby – sid1o_js

Enable force logging on the Primary database

SQL> alter database force logging;

Database altered.

Create the Standby log files on the Primary database

Copy the password file from the $ORACLE_HOME/dbs directory on primary server to $ORACLE_HOME/dbs on the standby server

Update listener.ora on Standby machine

 

(SID_DESC=
(GLOBAL_DBNAME=sid1o_js)
(ORACLE_HOME=/u01/oracle/product/11.1.0/db_1)
(SID_NAME=sid1o)
)

Stop and Restart the listener on the standby site

Update tnsnames.ora on Standby as well as Primary site with the alias ‘sid1o_js’ and ‘sid1o_fc’

sid1o_js =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = drou036)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sid1o_js )
)
)

SID1O_FC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oatu036)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sid1o.bankwest.com)
)
)

Create an init.ora on the Standby machine with just a single line which is the db_name parameter

sid1o:/u01/oracle/product/11.1.0/db_1/dbs> cat initsid1o.ora
db_name=sid1o

Startup the Standby instance in nomount state

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2152328 bytes
Variable Size 159385720 bytes
Database Buffers 50331648 bytes
Redo Buffers 5287936 bytes

On the Primary launch RMAN and establish an auxiliary connection to the standby instance

sid1o:/u01/oracle> rman target / auxiliary sys/xxx@sid1o_js

Recovery Manager: Release 11.1.0.7.0 – Production on Fri Dec 4 10:28:51 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: SID1O (DBID=2860177231)
connected to auxiliary database: SID1O (not mounted)

Run the command to create the Standby Database

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK
DORECOVER
SPFILE
SET DB_UNIQUE_NAME=”sid1o_js”
SET LOG_ARCHIVE_DEST_2=”service=sid1o_fc LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”
SET FAL_SERVER=”sid1o_fc”
SET FAL_CLIENT=”sid1o_js”
SET CONTROL_FILES=’/u02/oradata/sid1o/control01.ctl’,'/u03/oradata/sid1o/control02.ctl’;

Change the init.ora parameters related to redo transport and redo apply

On standby and primary

SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

On Primary

SQL> alter system set fal_server=sid1o_js scope=both;

System altered.

SQL> alter system set fal_client=sid1o_fc scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=sid1o_js LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sid1o_js’
scope=both; 2

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sid1o’;

System altered.

Shutdown the Standby and enable managed recovery (active standby mode)

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size 2160352 bytes
Variable Size 775948576 bytes
Database Buffers 260046848 bytes
Redo Buffers 5730304 bytes
Database mounted.
Database opened.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>

Check if the MRP process is running

SQL> !ps -ef |grep mrp
oracle 446526 1 0 10:59:01 – 0:00 ora_mrp0_sid1o

TEST

On Primary

SQL> conn system/xxx
Connected.
SQL> create table test_dr
2 (mydate date);

Table created.

SQL> insert into test_dr
2 values
3 (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

On Standby

SQL> conn system/xxx
Connected.
SQL> select to_char(mydate,’DD-MON-YY HH24:MI:SS’) from test_dr;

TO_CHAR(MYDATE,’DD-MON-YYHH
—————————
04-DEC-09 11:15:49