Post date: 12-Jan-2011 12:08:09
1. Enable Forced Logging on Primary
2. Create a Password File for standby on Standby site
3. Create Standby Controlfile from Primary db.
alter database create standby controlfile as '/tmp/stb.ctl';
Copy this stb.ctl to the same location as RMAN will check for it's availability during the controlfile restore for Standby duplication through RMAN.
4. Set Primary Database Initialization Parameters
PRIMARY DB PARAMETERS
------------------------
*.__db_cache_size=922746880
*.__java_pool_size=16777216
*.__large_pool_size=16777216
*.__shared_pool_size=285212672
*.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/prim/adump'
*.background_dump_dest='/oracle/admin/prim/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/oradata/prim/control01.ctl','/oracle/oradata/prim/control02.ctl','/oracle/oradata/prim/control03.ctl'
*.core_dump_dest='/oracle/admin/prim/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prim'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=6291456000
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=418381824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1256194048
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/prim/udump'
db_unique_name='prim'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stb)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'
LOG_ARCHIVE_DEST_2='service=stb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stb'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=2
FAL_SERVER=STB
FAL_CLIENT=PRIM
db_file_name_convert='prim','stb','PRIM','STB'
log_file_name_convert='prim','stb','PRIM','STB'
STANDBY DB PARAMETERS
------------------------
*.__db_cache_size=922746880
*.__java_pool_size=16777216
*.__large_pool_size=16777216
*.__shared_pool_size=285212672
*.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/stb/adump'
*.background_dump_dest='/oracle/admin/stb/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/oradata/stb/control01.ctl','/oracle/oradata/stb/control02.ctl','/oracle/oradata/stb/control03.ctl'
*.core_dump_dest='/oracle/admin/stb/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prim'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=6291456000
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=418381824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1256194048
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/stb/udump'
db_unique_name='stb'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stb)'
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stb'
LOG_ARCHIVE_DEST_2='service=prim VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=2
FAL_SERVER=PRIM
FAL_CLIENT=STB
db_file_name_convert='prim','stb','PRIM','STB'
log_file_name_convert='prim','stb','PRIM','STB'
5. Create Standby Controlfile from primary db.
sql> alter database create standby controlfile as '/tmp/stb.ctl'
Move this copy of standby controlfile to the same /tmp location on the Standby site.
6. Configure the tnsnames and listener.ora
tnsnames.ora (Same on Primary and Standby site)
===================================================
PRIM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Audible)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prim)
)
)
STB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Audible)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stb)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
listener.ora (on Primary Site)
===================================================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/Db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = prim)
(ORACLE_HOME = /oracle/product/10.2.0/Db_1)
(SID_NAME = orcldev)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Audible)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
listener.ora (on Standby Site)
===================================================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/Db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = stb)
(ORACLE_HOME = /oracle/product/10.2.0/Db_1)
(SID_NAME = stb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Audible)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
6. Start the Listener
7. Take RMAN backup of Primary database.
8. Start standby db in nomount mode.
9 From Target site run the following
rman target / auxiliary sys/sys@stb
duplicate target database for standby;
10. Configure Stanby Redo logs on Standby Site.
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
10 Start Recovery on Standby Site.
alter database recover managed standby database disconnect from session.
Monitor Standby Database
==========================
SELECT DEST_ID,VALID_TYPE,VALID_ROLE,VALID_NOW
FROM V$ARCHIVE_DEST;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ#
FROM V$ARCHIVE_DEST_STATUS;
SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME
FROM V$ARCHIVED_LOG;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;