<- DataGuard

Standby Database Changes: ASM

Create Audit Directory

 mkdir -p /u01/app/oracle/admin/oradb_sb/adump

Configure Pfile

1. cp /u01/rman/oradb_sb.pfile /tmp/oradb_sb.pfile
2. Edit /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='+DATA/ORADB_SB/CONTROLFILE/current.257.1004013489',
                  '+FRA/ORADB_SB/CONTROLFILE/current.256.1004013491'
  *.local_listener=''
  *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST',
                       'VALID_FOR=(ALL_LOGFILES, ALL_ROLES)'
  *.log_archive_dest_2='SERVICE=oradb ASYNC NOAFFIRM 
                        VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
                        DB_UNIQUE_NAME=oradb'
  *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradb,oradb_sb)'

  *.fal_client=oradb_sb
  *.fal_server=oradb

  *.db_create_file_dest='+DATA'
  *.db_recovery_file_dest='+FRA'

  *.db_file_name_convert='ORADB','ORADB_SB'
  *.log_file_name_convert='ORADB','ORADB_SB'

Example Pfile if Primary is RAC\ASM and Standby is using Oracle-Restart.

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/orapworadb_sb
 cp /u01/rman/orapworadb $ORACLE_HOME/dbs/

• Note the destination name is changed to the SB's DB_UNIQUE_NAME for 2nd copy.

Start SB Using PFile

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

Test Connectivity

Make these connectivity tests from both Primary and SB.

 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;

Enable ASM Disk Access

 su -
 chmod 666 /dev/sd?1
 ls -rlt /dev/sd?1

• Allows all required accounts to access ASM resources during RMAN.

Udev Changes
If using Udev:

 1. Set MODE="0666" in your rules file.
    vi /etc/udev/rules.d/99-oracle-asmdevices.rules
 2. Restart Udev Service.
    udevadm control --reload-rules
 3. Check privs.
    ls -rlt /dev/sd?1

If privs not changed reload partition tables:

 /sbin/partprobe /dev/sdb1
 /sbin/partprobe /dev/sdc1
 ...

RMAN Restore to Create Standby DB

 su - oracle
 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;

Update control_files Value

 1. Get the SB control_file names from ASM:
    su - grid
    asmcmd -p
    ls +data/ORADB_SB/CONTROLFILE/*
      current.275.1005734491
    ls +fra/ORADB_SB/CONTROLFILE/*
      current.270.1005734493
 2. Set the control_file names in the pfile as they exist in ASM.
    su - oracle
    vi /tmp/oradb_sb.pfile

Mount DB

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

• This step ensures the database will be started using an spfile from this point on.
• 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

Takes a few minutes for MRP0 to be active. May show: 'no rows selected' until then.

 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#;

APPENDIX

Pfile Example for SB:

 Primary =  RAC\ASM
 Standby = Oracle-Restart (single instance using ASM)

Some lines split to make them easier to read.

*.audit_file_dest='/u01/app/oracle/admin/oradb_sb/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='+DATA/ORADB_SB/CONTROLFILE/current.277.1006610845',
                '+FRA/ORADB_SB/CONTROLFILE/current.278.1006610845'
*.db_block_size=8192

*.db_domain=''
*.db_name='oradb'
*.db_unique_name=oradb_sb

*.db_recovery_file_dest_size=6g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)'

*.log_archive_config='DG_CONFIG=(oradb,oradb_sb)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST',
                     'VALID_FOR=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2='SERVICE=oradb_sb ASYNC NOAFFIRM 
                      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradb_sb'
*.log_archive_format='%t_%s_%r.arc'

*.open_cursors=300
*.pga_aggregate_target=512m
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sga_target=1512m
*.standby_file_management='AUTO'

*.fal_client='oradb_sb'
*.fal_server='oradb'

*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+FRA'

*.db_file_name_convert='ORADB','ORADB_SB'
*.log_file_name_convert='ORADB','ORADB_SB'

Sweep $ORACLE_HOME/dbs

 rm -f $ORACLE_HOME/dbs/*.dat

Create Password File Examples

 oracle> orapwd file=$ORACLE_HOME/dbs/orapworadb_sb password=go entries=10 force=yignorecase=y
 oracle> orapwd file=$ORACLE_HOME/dbs/orapworadb password=go entries=10 force=y ignorecase=y

Use same password (for SYS etc.) as used on Primary.

<- DataGuard