Primary Database Changes
- Create Standby Redo Log (SRL) Files
- Set Parameters
Create Standby Redo Log (SRL) Files
- The SIZE must be the same as your redo log files.
- The SRLs must have at least one more than the redo logs on the primary database. So if your Primary has 4 create 5.
- SRLs are used, even in Max Performance mode, as they reduce data loss to seconds (instead of minutes or hours).
Get Number of Redo Log Groups
COL member FORMAT a50 SELECT group#,member,status,type FROM v$logfile ORDER BY group#,member;
Get Redo Log File Size
SELECT bytes FROM v$log;
Add Standby Logfiles
Standalone & Oracle Restart Environment
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 6 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 7 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 8 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 9 SIZE 200M;
RAC Environment
Create a set of SRLs for each node allocating them via thread number. So for a 2-node RAC:
ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 5 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 6 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 7 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 8 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 1 group 9 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 10 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 11 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 12 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 13 SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE thread 2 group 14 SIZE 200M;
- Even if the Standby is not RAC you must create these as the Standby will need to manage all instances redo.
Verify SRL Files Created
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
Set Parameters
- If you prefer, edit and run this script: setDGParams.primary.sql
- If RAC & Oracle Restart, no need to specify log_archive_dest_1. The default will be used:
USE_DB_RECOVERY_FILE_DEST, VALID_FOR=(ALL_LOGFILES, ALL_ROLES).
ALTER DATABASE FORCE LOGGING; ALTER SYSTEM SET log_archive_config='DG_CONFIG=(oradb,oradb_sb)' SCOPE=both; ALTER SYSTEM SET log_archive_format='%t_%s_%r.arc' SCOPE=spfile; ALTER SYSTEM SET remote_login_passwordfile='EXCLUSIVE' SCOPE=spfile; ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=both; ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/oradata/ORADB/onlinelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb' SCOPE=both; ALTER SYSTEM SET log_archive_dest_2='SERVICE=oradb_sb ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb_sb' SCOPE=both; ALTER SYSTEM SET db_file_name_convert='ORADB_SB','ORADB' SCOPE=spfile; ALTER SYSTEM SET log_file_name_convert='ORADB_SB','ORADB' SCOPE=spfile; ALTER SYSTEM SET fal_server='oradb_sb' SCOPE=both; ALTER SYSTEM SET fal_client='oradb' SCOPE=both; ALTER SYSTEM SET sec_case_sensitive_logon=false SCOPE=both; ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED; ALTER PROFILE default LIMIT PASSWORD_REUSE_TIME UNLIMITED;
Property | Comments |
---|---|
STANDBY_FILE_MANAGEMENT | Sets OS file additions and deletions on the primary are replicated on the SB. |
LOG_ARCHIVE_CONFIG | Sets trans of redo with the SB. Also sets DB_UNIQUE_NAME for each DB. |
LOG_ARCHIVE_DEST_1 | Location where archive logs created. |
LOG_ARCHIVE_DEST_2 | SERVICE service name for Standby (as set in the tnsnames.ora). |
ASYNC NOAFFIRM sets for maximum performance. | |
*_FILE_NAME_CONVERT | Entries are required even when both systems file paths are identical. |
PASSWORD Props | These password changes help avoid common gotchas. |
Bounce Primary
Some changes above do not take effect until after a database bounce.
Standalone Database
SQLPlus> shutdown immediate SQLPlus> exit sqlplus / as sysdba SQLPlus> startup
RAC and Oracle Restart
oracle> srvctl stop database -d oradb oracle> srvctl start database -d oradb
APPENDIX
Delete Standby Log Files
ALTER DATABASE DROP STANDBY LOGFILE group 10; ALTER DATABASE DROP STANDBY LOGFILE group 11; ALTER DATABASE DROP STANDBY LOGFILE group 12; ALTER DATABASE DROP STANDBY LOGFILE group 13; ALTER DATABASE DROP STANDBY LOGFILE group 14;