1) First Check PING from PRIMARY to STANDBY Server and from STANDBY to PRIMARY Server
--- It should work fine. If any issue ask your network team to check .
( A ) All below needs to be done on PRIMARY Server :
*********************************************************
2) Database should be in archivelog mode , most of the production system always in archivelog mode.
--- Check using command "archive log list" , if disable then check the downtime when you can do this.
3) PRIMARY should be in force logging.
--- alter database force logging;
--- select FORCE_LOGGING from v$database; (Should return YES )
4) On PRIMARY check if password file is being used.
--- select * from v$pwfile_users;
--- Check orapw file in location $ORACLE_HOME/dbs
5) Network configuration on PRIMARY server
PRIMARY NETWORK FILES:
[oracle@primary admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u04/app/oracle/11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD11DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stand.ggn.com)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod11dg)
)
)
PROD11 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.ggn.com)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod11)
)
)
[oracle@primary admin]$ cat listener.ora
# listener.ora Network Configuration File: /u04/app/oracle/11g/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= prod11)
(SID_NAME = prod11)
(ORACLE_HOME= /u04/app/oracle/11g)
)
(SID_DESC =
(SID_NAME = prod11dg)
(ORACLE_HOME = /u04/app/oracle/11g)
(GLOBAL_DBNAME=prod11dg)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stand.ggn.com)(PORT = 1525))
)
ADR_BASE_LISTENER = /u04/app/oracle
[oracle@primary admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u04/app/oracle/11g/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u04/app/oracle
6) Primary side parameters which need to check :
db_name='prod11'
db_unique_name='prod11'
log_archive_config='DG_CONFIG=(PROD11,PROD11DG)
log_archive_dest_1=location=/u04/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=prod11
log_archive_dest_2=service=prod11dg valid_for=(online_logfiles,primary_role) db_unique_name=prod11dg
fal_client=PROD11
fal_server=PROD11DG
remote_login_passwordfile=EXCLUSIVE
standby_file_management=AUTO
alter system set log_archive_dest_state_2='defer' scope=both; >>> for time being , once configuration completed we will enable it.
alter system set log_archive_dest_1='location=/u04/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=prod11' scope=both;
alter system set log_archive_dest_2='service=prod11dg valid_for=(online_logfiles,primary_role) db_unique_name=prod11dg' scope=both;
alter system set db_file_name_convert='/oradata/prod11','/oradata/prod11dg' scope=spfile;
alter system set log_file_name_convert='/oradata/prod11','/oradata/prod11dg' scope=spfile;
Add standby logfile on Primary:
alter database add standby logfile group 4 ('/u04/app/oracle/oradata/prod11/standredo1.log') size 50m;
alter database add standby logfile group 5 ('/u04/app/oracle/oradata/prod11/standredo2.log') size 50m;
alter database add standby logfile group 6 ('/u04/app/oracle/oradata/prod11/standredo3.log') size 50m;
alter database add standby logfile group 7 ('/u04/app/oracle/oradata/prod11/standredo4.log') size 50m;
select member from v$logfile;
select member from v$logfile where type='STANDBY';
7) Create pfile from spfile:
create pfile='/home/oracle/initPROD11DG.ora' from spfile;
SAMPLE init file on PRIMARY:
prod11.__db_cache_size=58720256
prod11.__java_pool_size=4194304
prod11.__large_pool_size=8388608
prod11.__oracle_base='/u04/app/oracle'#ORACLE_BASE set from environment
prod11.__pga_aggregate_target=171966464
prod11.__sga_target=306184192
prod11.__shared_io_pool_size=25165824
prod11.__shared_pool_size=197132288
prod11.__streams_pool_size=4194304
*.audit_file_dest='/u04/app/oracle/admin/prod11/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/u04/app/oracle/oradata/prod11/control01.ctl','/u04/app/oracle/flash_recovery_area/prod11/control02.ctl'
*.db_block_size=8192
*.db_domain='ggn.com'
*.db_file_name_convert='/oradata/prod11','/oradata/prod11dg'
*.db_name='prod11'
*.db_recovery_file_dest='/u04/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=107374182400
*.db_unique_name='prod11'
*.diagnostic_dest='/u04/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod11XDB)'
*.fal_client='PROD11'
*.fal_server='PROD11DG'
*.log_archive_config='DG_CONFIG=(PROD11,PROD11DG)'
*.log_archive_dest_1='location=/u04/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=prod11'
*.log_archive_dest_2='service=prod11dg valid_for=(online_logfiles,primary_role) db_unique_name=prod11dg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='DEFER'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.log_file_name_convert='/oradata/prod11','/oradata/prod11dg'
*.memory_target=478150656
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='prod11'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
8) Take the full backup using RMAN
backup database plus archivelog;
backup current controlfile for standby;
( B ) All below needs to be done on STANDBY Server :
*********************************************************
9) COPY backup pieces, password file ( rename it ) , init file , and all the archivelogs .
10) Setup Network on Standby server
STANDBY NETWORK FILES:
[oracle@stand admin]$ cat listener.ora
# listener.ora Network Configuration File: /u04/app/oracle/11g/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME= prod11)
(SID_NAME = prod11)
(ORACLE_HOME= /u04/app/oracle/11g)
)
(SID_DESC =
(SID_NAME = prod11dg)
(ORACLE_HOME = /u04/app/oracle/11g)
(GLOBAL_DBNAME=prod11dg)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stand.ggn.com)(PORT = 1525))
)
ADR_BASE_LISTENER = /u04/app/oracle
[oracle@stand admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u04/app/oracle/11g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PROD11DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stand.ggn.com)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod11dg)
)
)
PROD11 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.ggn.com)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prod11)
)
)
[oracle@stand admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u04/app/oracle/11g/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ADR_BASE = /u04/app/oracle
--- If Network files not resolve your connection issues then register using NETCA
Check TNSPING of PRIMARY and STANDBY from both ends it should be working else you will face issue.
11) Nomount the database using below init file which already modified for standby:
prod11dg.__db_cache_size=58720256
prod11dg.__java_pool_size=4194304
prod11dg.__large_pool_size=8388608
prod11dg.__oracle_base='/u04/app/oracle'#ORACLE_BASE set from environment
prod11dg.__pga_aggregate_target=171966464
prod11dg.__sga_target=306184192
prod11dg.__shared_io_pool_size=25165824
prod11dg.__shared_pool_size=197132288
prod11dg.__streams_pool_size=4194304
*.audit_file_dest='/u04/app/oracle/admin/prod11dg/adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/u04/app/oracle/oradata/prod11dg/control01.ctl','/u04/app/oracle/flash_recovery_area/prod11dg/control02.ctl'
*.db_block_size=8192
*.db_domain='ggn.com'
*.db_file_name_convert='/oradata/prod11','/oradata/prod11dg'
*.db_name='prod11'
*.db_recovery_file_dest='/u04/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=107374182400
*.db_unique_name='prod11dg'
*.diagnostic_dest='/u04/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prod11dgXDB)'
*.fal_client='PROD11DG'
*.fal_server='PROD11'
*.log_archive_config='DG_CONFIG=(PROD11,PROD11DG)'
*.log_archive_dest_1='location=/u04/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=prod11dg'
*.log_archive_dest_2='service=prod11 valid_for=(online_logfiles,primary_role) db_unique_name=prod11'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='defer'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=30
*.log_file_name_convert='/oradata/prod11','/oradata/prod11dg'
*.memory_target=478150656
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='prod11dg'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
startup nomount;
11) Recover standby
rman
connect auxiliary /
connect target sys/oracle123@prod11
connected to auxiliary database: PROD11 (not mounted)
connected to target database: PROD11 (DBID=4134858347)
RMAN> run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate auxiliary channel a1 device type disk;
allocate auxiliary channel a2 device type disk;
allocate auxiliary channel a3 device type disk;
allocate auxiliary channel a4 device type disk;
duplicate target database for standby nofilenamecheck dorecover;
}
Once Recovery finished, check Standby status:
SQL> select name, open_mode , database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
PROD11 MOUNTED PHYSICAL STANDBY
Check if any alerts in standby alert logs.
12) Enable MRP process.
alter database recover managed standby database disconnect;
13) Enable Dest 2 on PRIMARY database which we kept earlier false
alter system set log_archive_dest_state_2='enable' scope=both;
14) Now check SYNC:
On Physical Standby:
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Check if any archive gap :
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
On Primary:
select thread#,max(sequence#) from v$archived_log group by thread#;
SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"
FROM V$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 1;
