STANDBY Single Instance11g DATABASE CREATION - Duplicate from Active

11G STANDBY DATABASE CREATION WITHOUT ANY RMAN BACKUPS

• 11g RMAN, active data guard, duplicate database from active, standby database creation

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