Thursday, February 28, 2013

ORACLE - Setup Data Guard 11gR2 - Step-By-Step

======================================================================
Step 1) Listener.ora, tnsnames.ora, password files :Both side (primary  and standby)
======================================================================
Listener.ora
Note: I used default listener and grid home for listener
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ATEST)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ATEST)
    )
  )
tnsnames.ora
ATEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hpmidb02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = ATEST)
    )
  )
ATEST_STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = npmidb02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = ATEST)
    )
  )

Create password file using like:
orapwd file=$ORACLE_HOME/dbs/orapwATEST password=<password>

On Primary side:
check :
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
show parameter db_unique_name
archive log list;
alter database force logging;
alter system set standby_file_management = 'AUTO';
alter system set log_archive_config='dg_config=(ATEST,ATEST_STBY)'
alter system set fal_server = 'ATEST_STBY'; # fal_client is deprecated in 11g
alter system set log_archive_dest_1 = 'location=/bkup/ATEST/arch valid_for=(all_logfiles, all_roles) DB_UNIQUE_NAME=ATEST' scope=both ;
alter system set log_archive_dest_2 = 'service=ATEST_STBY ASYNC valid_for=(online_logfile,primary_role) db_unique_name=ATEST_STBY';
# Add standby database like:
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 100m;

Best Practice for STandby logs:
1.Create standby redo logs.
2.Logs must be the same size as on-line redo logs and must have a least one extra group to accommodate lag.
3.Do not multiplex standby redo logs.

Optional: (db_recovery_file_dest, db_recovery_file_dest_size for FLASHBACK database)

========================================================================
Step 2) Create init.ora with ONLY with db_Name  And create a audit directory (if does not exists)
and STARTUP database with NOMOUNT Note:Do not use spfile
========================================================================
On the $ORACLE_HOME/dbs of the standby database, create a dummy init.ora and a password file
echo 'DB_NAME = ATEST' > $ORACLE_HOME/dbs/initATEST.ora
orapwd file=$ORACLE_HOME/dbs/orapwATEST password=<same as sys for Primary> 

Create dir ATEST at
/u01/app/oracle/admin/ATEST/adump
To avoid following error:
RMAN-04014: startup failed:
ORA-09925: Unable to create audit trail file

Check other directtories also.

STARTUP with NOMOUNT option
sqlplus / as sysdba
startup nomount
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 20 09:14:13 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area  329895936 bytes
Fixed Size                  2228024 bytes
Variable Size             255852744 bytes
Database Buffers           67108864 bytes
Redo Buffers                4706304 bytes

Verify:
show parameter db_name

========================================================================
Step 3) Duplicate target database for standby from active database
========================================================================
rman                 
connect target sys/pssword@ATEST
connect auxiliary sys/password@ATEST_STBY
run                  
{                    
   allocate channel p1 type disk;
   allocate channel p2 type disk;
   allocate auxiliary channel a1 type disk;
   duplicate target database for standby from active database
   spfile            
      parameter_value_convert 'ATEST','ATEST'
      set db_unique_name='ATEST_STBY'
      set control_files='+DG_HMWHIST_01'                       
      set db_create_file_dest='+DG_HMWHIST_01'
      set log_archive_config='dg_config=(ATEST,ATEST_STBY)'
      set log_archive_dest_1='LOCATION=/bkup/ATEST/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ATEST_STBY'
      set log_archive_dest_2='service=ATEST ASYNC db_unique_name=ATEST valid_for=(ONLINE_LOGFILES,PRIMARY_ROLES)'
      set fal_server = 'ATEST'
   nofilenamecheck;   
}

check:
SQL> SELECT OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE ROLE FROM V$DATABASE;
OPEN_MODE            PROTECTION_MODE      PROTECTION_LEVEL     ROLE
-------------------- -------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY


 ---- Done

No comments:

Post a Comment