11gR2 Active Dataguard creation steps

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 – gg01
Standby machine – gg02

Database Name – DEVDB

TNS alias for Primary – node1
TNS alias for standby – standbydb

Enable force logging on the Primary database
Pre – req :

Select * from v$database_block_corruption ;

SQL> alter database force logging;

Database altered.
SELECT STATUS FROM V$LOG WHERE GROUP#=2;
Note : If the status did not CURRENT then simply drop the log file by:
SQL>ALTER DATABASE DROP LOGFILE GROUP 2;
Create the Standby log files on the Primary database

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ‘/u02/oracle/oradata/11g/DEVDB/sredo04.log’ size 50m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ‘/u02/oracle/oradata/11g/DEVDB/sredo05.log’ size 50m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ‘/u02/oracle/oradata/11g/DEVDB/sredo06.log’ size 50m;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ‘/u02/oracle/oradata/11g/DEVDB/sredo07.log’ size 50m;

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=standbydb)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME=DEVDB)
)

Stop and Restart the listener on the standby site

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

standbydb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standbydb )
)
)

node1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gg01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB.bankwest.com)
)
)

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

DEVDB:/u01/app/oracle/product/11.2.0/db_1/dbs> cat initDEVDB.ora
db_name=DEVDB

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

DEVDB:/u01/oracle> rman target sys/oracle@node1 auxiliary sys/oracle@standbydb

rman target / auxiliary sys/oracle@standbydb

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: DEVDB (DBID=2860177231)
connected to auxiliary database: DEVDB (not mounted)

Run the command to create the Standby Database
run {
SET MAXCORRUPT FOR DATAFILE 76 to 10;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK
DORECOVER
SPFILE
SET DB_UNIQUE_NAME=”standbydb”
SET LOG_ARCHIVE_DEST_2=”service=node1 LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”
SET FAL_SERVER=”node1″
SET FAL_CLIENT=”standbydb”
SET
CONTROL_FILES=’/u02/oracle/oradata/11g/DEVDB/control01.ctl’,’/u02/oracle/oradata/11g/DEVDB/control02.ctl’,’/u02/oracle/oradat
a/11g/DEVDB/control03.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=standbydb scope=both;

System altered.

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

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=standbydb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standbydb’
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=DEVDB’;

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> alter database 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_DEVDB

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