oracledba.help

<- DataGuard

Primary Database Changes

  1. Create Standby Redo Log (SRL) Files
  2. 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;
PropertyComments
STANDBY_FILE_MANAGEMENTSets OS file additions and deletions on the primary are replicated on the SB.
LOG_ARCHIVE_CONFIGSets trans of redo with the SB. Also sets DB_UNIQUE_NAME for each DB.
LOG_ARCHIVE_DEST_1Location where archive logs created.
LOG_ARCHIVE_DEST_2SERVICE service name for Standby (as set in the tnsnames.ora).
 ASYNC NOAFFIRM sets for maximum performance.
*_FILE_NAME_CONVERTEntries are required even when both systems file paths are identical.
PASSWORD PropsThese 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;

<- DataGuard