======================================================================
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
