oracledba.help

<- DataGuard

Standby Database Changes: OMF

ORACLE_SID
Ensure oracle user .bashrc has ORACLE_SID=oradb_sb.

Pfile Actions 

 cp /u01/rman/oradb_sb.pfile /tmp/oradb_sb.pfile
 gedit /tmp/oradb_sb.pfile

 -- Remove
   oradb1.__ header entries.
 -- Add
  *.db_unique_name=oradb_sb
 -- Change
  *.audit_file_dest=/u01/app/oracle/admin/oradb_sb/adump  
  *.control_files=
   '/u01/oradata/ORADB_SB/controlfile/o1_mf_dxt66yjq_.ctl', 
   '/u02/oradata/ORADB_SB/controlfile/o1_mf_dxt66ykb_.ctl
   *.local_listener=''
  *.log_archive_dest_1='LOCATION=/u01/oradata/ORADB_SB/onlinelog/ 
     VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb_sb'
  *.log_archive_dest_2='SERVICE=oradb ASYNC NOAFFIRM 
     VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb'
  *.fal_client=oradb_sb
  *.fal_server=oradb
  *.db_file_name_convert='ORADB','ORADB_SB'
  *.log_file_name_convert='ORADB','ORADB_SB'

Example Pfile if Primary is RAC\ASM and Standby is a Standalone database.

If Primary is a RAC and SB will be a Standalone, remove these type entries: 

 *family:dw_helper.instance_mode='read-only'
 *.cluster_database=true
 oradb1.instance_number=1
 oradb2.instance_number=2
 oradb2.thread=2
 oradb1.thread=1
 oradb1.undo_tablespace='UNDOTBS1'
 oradb2.undo_tablespace='UNDOTBS2'

Copy Password File 

 cp /u01/rman/orapworadb $ORACLE_HOME/dbs/
 cp /u01/rman/orapworadb $ORACLE_HOME/dbs/orapworadb_sb
  • Note the destination name is changed to the SB's DB_UNIQUE_NAME for 2nd copy.

Startup Nomount SB
With ORACLE_SID set to primary SID: 

 sqlplus / as sysdba
 SQLPlus> startup nomount pfile='/tmp/oradb_sb.pfile';

Start Listener on SB 

 oracle> lsnrctl start

Test Connectivity 

sqlplus sys/go@oradb as sysdba
SQL> SELECT instance_name FROM v$instance;

sqlplus sys/go@oradb_sb as sysdba
SQL> SELECT instance_name FROM v$instance;

RMAN Restore to Create Standby DB 

 rman target=sys/go@oradb auxiliary=/

 connected to target database: ORADB (DBID=nnnnnnnnnn)
 connected to auxiliary database: ORADB (not mounted)

 RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

 sqlplus sys/go@oradb_sb as sysdba
 SQLPlus> alter database recover managed standby database disconnect from session;

Mount DB 

 create spfile from pfile='/tmp/oradb_sb.pfile';
 startup mount force;
 alter database recover managed standby database disconnect from session;

The recover managed standby command will have to be issued manually each SB database start\mount until you have configured the broker.

Enable Log Transmission 

 Primary
 alter system set log_archive_dest_state_2=ENABLE;

QC 

 SB
 tail -f /u01/app/oracle/diag/rdbms/oradb_sb/oradb_sb/trace/alert_oradb_sb.log

 Both
 COL database_role     FORMAT a20
 COL db_unique_name    FORMAT a15 
 COL instance          FORMAT a15 
 COL open_mode         FORMAT a15
 COL protection_level  FORMAT a25
 COL switchover_status FORMAT a15
 SELECT database_role, db_unique_name Instance, 
        open_mode, protection_level, switchover_status 
 FROM V$DATABASE;

 Primary
 archive log list

 SB
 SELECT process,thread#,sequence#,status 
 FROM v$managed_standby WHERE process='MRP0';

 PROCESS      THREAD#  SEQUENCE# STATUS
 --------- ---------- ---------- ------------
 MRP0               1         233 WAIT_FOR_LOG

 Issue Cmds from Primary but Monitor log on SB
 alter system switch logfile;
 archive log list

 Both (MAX(SEQUENCE#)) Should Match
 SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG 
 WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) 
 GROUP BY THREAD#;

 If you do not see headings\values for SEQUENCE# & THREAD# your password file on SB may not be right.

APPENDIX

Pfile Example for SB: Primary RAC\ASM & SB Standalone Database

Some lines split to make them easier to read.

  1. cp /u01/rman/oradb_sb.pfile /tmp/oradb_sb.pfile
  2. gedit /tmp/oradb_sb.pfile 
*.audit_file_dest='/u01/app/oracle/admin/oradb_sb/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files=
 '/u01/oradata/oradb_sb/controlfile/current.257.979813477',
 '/u02/oradata/oradb_sb/controlfile/current.258.979813477'
*.db_block_size=8192
*.db_domain=''
*.db_name='oradb'
*.db_recovery_file_dest_size=10g
*.db_unique_name=oradb_sb
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)'
*.fal_client='oradb_sb'
*.fal_server='oradb'
*.local_listener=''
*.log_archive_config='DG_CONFIG=(oradb,oradb_sb)'
*.log_archive_dest_1='LOCATION=/u01/oradata/ORADB_SB/onlinelog/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradb_sb'
*.log_archive_dest_2='SERVICE=oradb ASYNC NOAFFIRM 
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1580m
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
#
*.db_create_file_dest='/u01/oradata/'
*.db_recovery_file_dest='/u02/oradata/' 
*.db_file_name_convert='+DATA/ORADB/DATAFILE/','/u01/oradata/ORADB_SB/datafile/',
                       '+DATA/ORADB/TEMPFILE/','/u01/oradata/ORADB_SB/datafile/'
*.log_file_name_convert=
 '+FRA/ORADB/ONLINELOG/','/u02/oradata/ORADB_SB/onlinelog/'

Sweep $ORACLE_HOME/dbs

 rm -f $ORACLE_HOME/dbs/*.dat