Creating standby database in oracle 11g without 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 – PROD01
Standby machine – STAND01

Database Name – PROD

TNS alias for Primary – prod_ps
TNS alias for standby – prod_ss

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=prod_ss)
(ORACLE_HOME=/p01/oracle/product/11.1.0/db_1)
(SID_NAME=prod)
)

Stop and Restart the listener on the standby site

Update tnsnames.ora on Standby as well as Primary site with the alias ‘prod_ss’ and ‘prod_ps’
prod_ss =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stand01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod_ss )
)
)

prod_ps =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = PROD01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod01.domain.com)
)
)

Create an init.ora on the Standby machine with just a single line which is the db_name parameter
[oracle@stand01 dbs]$ cat initprod.ora
db_name=prod

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
[oracle@prod01 ~]$ rman target / auxiliary sys/xxx@prod_ss

Recovery Manager: Release 11.1.0.7.0 – Production on Fri JAN 24 10:28:51 2013

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

connected to target database: PROD (DBID=2860177231)
connected to auxiliary database: PROD (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=”prod_ss”
SET LOG_ARCHIVE_DEST_2=”service=prod_ps LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”
SET FAL_SERVER=”prod_ps”
SET FAL_CLIENT=”prod_ss”
SET CONTROL_FILES=’/p02/oradata/prod/control01.ctl’,'/p03/oradata/prod/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=prod_ss scope=both;

System altered.

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

System altered.

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

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=prod’;

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_prod

TEST
--------------------
On Primary
SQL> conn system/xxx
Connected.
SQL> create table test123(mydate date);

Table created.

SQL> insert into test123 values(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 test123;

TO_CHAR(MYDATE,’DD-MON-YYHH
—————————
24-JAN-13 11:15:49