Oracle active Data Guard for standby database : RMAN

You can use Oracle active Data Guard to create a physical standby database by using RMAN backups.
For my example, the primary database is STATTAR (host=hots1), and the standby is STATSTDB (host=hots2).
The OS is linux x86_64 and the oracle version is 11.2.0.1.

I. Set up Standby database

1. Ensure that  STATTAR  is in archivelog mode, otherwise convert it.

2. Enable the logging mode

SQL> ALTER DATABASE FORCE LOGGING;

3. do a complete backup of STATTAR
export ORACLE_SID=STATTAR
rman target /
RMAN> backup database plus archivelog;


4. tnsnames
STATSTDB  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STATSTDB)
    )
  )

5. listner (host2)
(SID_DESC =
       (GLOBAL_DBNAME =  STATSTDB )
       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
       (SID_NAME      =  STATSTDB )
     )
 
Check with the SQL*Net configuration using the following commands on the Primary AND Standby


from host1 server, check the communication beetween the 2 databases :

tnsping  STATSTDB
tnsping STATTAR

6. on host2, create the directories  for data, controlfile and redo log files
 mkdir -p /u01/app/oracle/admin/ STATSTDB /adump
 mkdir -p /u01/app/oracle/admin/ STATSTDB /dpdump
 mkdir -p /u01/app/oracle/admin/ STATSTDB /pfile
 mkdir -p /u01/app/oracle/admin/ STATSTDB /scripts
 mkdir -p /u02/oracle/oradata/ STATSTDB /controlfile/
 mkdir -p /u03/oracle/oradata/ STATSTDB /controlfile/
 mkdir -p /u04/oracle/oradata/ STATSTDB /controlfile/
 mkdir -p /u02/oracle/oradata/ STATSTDB /data_system
 mkdir -p /u02/oracle/oradata/ STATSTDB /data
 mkdir -p /u02/oracle/oradata/ STATSTDB /data
 mkdir -p /u03/oracle/oradata/ STATSTDB /data
 mkdir -p /u04/oracle/oradata/ STATSTDB /data
 mkdir -p /u02/oracle/oradata/ STATSTDB /redo
 mkdir -p /u03/oracle/oradata/ STATSTDB /redo
 mkdir -p /u04/oracle/oradata/ STATSTDB /redo


7. create pfile for standby (host2)
db_name=STATTAR
db_unique_name=STATSTDB
REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
db_block_size=8192
memory_max_target=1500M
memory_target=1500M

8. create pwdfile : the same as the target DB (host2)
orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapw STATSTDB  password=<syspwd>



9. startup  STATSTDB  in nomount mode
  export ORACLE_SID=STATSTDB
  sqlplus / as sysdba
  SQL> startup nomount pfile='/u01/app/oracle/admin/STATSTDB/pfile/STATSTDB_init.ora';

10. Create stanby redo log on the on primary db STATTAR

SQL> alter database add standby logfile group 4 ('/u02/oracle/oradata/ STATTAR /redostdb.ora') size 50M;

11. Modify the primary initialization parameter for dataguard on primary db STATTAR
 -- primary DB

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(STATTAR,STATSTDB)';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u06/oracle/oradata/STATTAR/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STATTAR';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=STATSTDB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STATSTDB';
System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.

SQL> alter system set FAL_SERVER=STATSTDB;
System altered.

SQL> alter system set FAL_CLIENT=STATTAR;
System altered.

SQL> alter system set DB_FILE_NAME_CONVERT= '/u02/oracle/oradata/STATSTDB/redo',
                          '/u02/oracle/oradata/STATTAR/redo',
                          '/u03/oracle/oradata/STATSTDB/redo',
                          '/u03/oracle/oradata/STATTAR/redo',
'/u04/oracle/oradata/STATSTDB/redo',
                          '/u04/oracle/oradata/STATTAR/redo' scope=spfile;
System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT= '/u02/oracle/oradata/STATSTDB/data_system',
                        '/u02/oracle/oradata/STATTAR/data_system',
'/u02/oracle/oradata/STATSTDB/data',
          '/u02/oracle/oradata/STATTAR/data',
'/u03/oracle/oradata/STATSTDB/data',
        '/u03/oracle/oradata/STATTAR/data',
'/u04/oracle/oradata/STATSTDB/data',
          '/u04/oracle/oradata/STATTAR/data' scope=spfile;
System altered.


12. on primary host (host1) invoke RMAN :
 rman target sys/<password>@STATTAR

RMAN>
connect auxiliary sys/<password>@STATSTDB
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;

duplicate target database for standby from active database
spfile
  parameter_value_convert 'STATTAR','STATSTDB'
  set db_unique_name='STATSTDB'
  set db_file_name_convert='/STATTAR/','/STATSTDB/'
  set log_file_name_convert='/STATTAR/','/STATSTDB/'
  set control_files='/u02/oracle/oradata/STATSTDB/controlfile/control01.ctl','/u03/oracle/oradata/STATSTDB/controlfile/control02.ctl','/u04/oracle/oradata/STATSTDB/controlfile/control03.ctl'
  set log_archive_max_processes='5'
  set fal_client='STATSTDB'
  set fal_server='STATTAR'
  set standby_file_management='AUTO'
  set log_archive_config='dg_config=(STATTAR,STATSTDB)'
  set log_archive_dest_1='service=STATTAR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=STATTAR'
;
}

...

 ''STATMAC'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config =
 ''dg_config=(STATMAC,STATSTDB)'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''service=STATMAC ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=                                                                                                                                                              STATMAC'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  audit_file_dest =  ''/u01/app/oracle/admin/STAT                                                                                                                                                              STDB/adump'' comment= '''' scope=spfile

sql statement: alter system set  dispatchers =  ''(PROTOCOL=TCP) (SERVICE=STATST                                                                                                                                                              DBXDB)'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''STATSTDB'' comment= '''' sc                                                                                                                                                              ope=spfile

sql statement: alter system set  db_file_name_convert =  ''/STATMAC/'', ''/STATS                                                                                                                                                              TDB/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/STATMAC/'', ''/STAT                                                                                                                                                              STDB/'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u02/oracle/oradata/STATSTDB                                                                                                                                                              /controlfile/control01.ctl'', ''/u03/oracle/oradata/STATSTDB/controlfile/control                                                                                                                                                              02.ctl'', ''/u04/oracle/oradata/STATSTDB/controlfile/control03.ctl'' comment= ''                                                                                                                                                              '' scope=spfile

sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''STATSTDB'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''STATMAC'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= ''                                                                                                                                                              '' scope=spfile

sql statement: alter system set  log_archive_config =  ''dg_config=(STATMAC,STAT                                                                                                                                                              STDB)'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''service=STATMAC ASYNC v                                                                                                                                                              alid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=STATMAC'' comment= '''' sc                                                                                                                                                              ope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1570009088 bytes

Fixed Size                     2213696 bytes
Variable Size                905971904 bytes
Database Buffers             637534208 bytes
Redo Buffers                  24289280 bytes
allocated channel: stby
channel stby: SID=63 device type=DISK

....

input datafile copy RECID=156 STAMP=778006506 file name=/u03/oracle/oradata/STATSTDB/data/TBS_FMA_DATA_01.ora

datafile 49 switched to datafile copy

input datafile copy RECID=157 STAMP=778006506 file name=/u03/oracle/oradata/STATSTDB/data/TBS_FMA_DATA_02.ora

Finished Duplicate Db at 15-MAR-12

released channel: prmy1

released channel: prmy2

released channel: prmy3

released channel: prmy4

released channel: stby

exit;

II. Manage Standby database

1. Stop the managed recovery process and open STATSTDB: 
export ORACLE_SID=STATSTDB
  sqlplus / as sysdba
SQL>  select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
              1 STATSTDB
pprod_db.local
11.2.0.1.0        15-MAR-12 MOUNTED      NO           1 STARTED
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> SQL> alter database open;
Database altered.
SQL>

2. Restart the managed recovery process

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

3. Test the active data guard:
on the primary database for user SCOTT:

create table my_test (id number);
insert into my_test(id) vlaues (1);
commit;
witth sys user : alter system switch logfile;

on the auxiliary database :
the table my_test is created with one record.