11.2 Data Guard Physical Standby Switchover Best Practices using SQL*Plus [ID 1304939.1]

Information Center: Oracle Data Guard Physical Standby Database [ID 1395508.2]

How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration [ID 278641.1]

Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration [ID 1302539.1]

New Parameter DB_ULTRA_SAFE Introduced In 11g [ID 465130.1]

Creating a Standby Database with Recovery Manager

This has been tested on Solaris 10 and Oracle 11.2.0.3

PRIMARY:

> cat initdbtest.ora

event='10298 trace name context forever, level 32'

*.audit_file_dest=/u01/app/oracle/admin/audit

*.audit_sys_operations=FALSE

*.audit_trail=NONE

*.compatible=11.2.0.3

*.control_management_pack_access=NONE

*.db_block_size=8192

*.db_block_checksum=TYPICAL

*.db_block_checking=MEDIUM

*.db_lost_write_protect=TYPICAL

*.db_create_file_dest=/oracle/oradata

*.db_recovery_file_dest=/oracle/oradata/flashrecovery

*.db_recovery_file_dest_size=100G

*.db_name=DBTEST

*.db_unique_name=SAN_DBTEST

*.deferred_segment_creation=FALSE

*.diagnostic_dest=/u01/app/oracle

*.fal_client=SAN_DBTEST

*.fal_server=LAX_DBTEST

*.fast_start_mttr_target=300

*.job_queue_processes=20

*.global_names=FALSE

*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1531))'

*.log_archive_config='DG_CONFIG=(SAN_DBTEST,LAX_DBTEST)'

*.log_archive_dest_2='SERVICE=LAX_DBTEST LGWR ASYNC COMPRESSION=ENABLE REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LAX_DBTEST'

*.log_archive_max_processes=2

*.pga_aggregate_target=1G

*.processes=300

*.remote_login_passwordfile=EXCLUSIVE

*.recyclebin=OFF

*.sec_case_sensitive_logon=FALSE

*.sga_target=2G

*.sga_max_size=2G

*.standby_file_management=AUTO

*.undo_management=AUTO

*.undo_retention=10800

*.undo_tablespace=UNDOTBS

STANDBY:

event='10298 trace name context forever, level 32'

*.db_keep_cache_size=0

*.db_recycle_cache_size=0

*.java_pool_size=0

*.large_pool_size=0

*.streams_pool_size=0

*.audit_file_dest=/u01/app/oracle/admin/audit

*.audit_sys_operations=FALSE

*.audit_trail=NONE

*.compatible=11.2.0.3

*.control_management_pack_access=NONE

*.db_block_size=8192

*.db_ultra_safe=DATA_AND_INDEX # New feature and is equivalent to the 3 parameters below

# *.db_block_checksum=FULL

# *.db_block_checking=FULL

# *.db_lost_write_protect=TYPICAL

*.db_cache_advice=OFF

*.db_cache_size=1024M

*.db_create_file_dest=/oracle/oradata

*.db_recovery_file_dest=/oracle/oradata/flashrecovery

*.db_recovery_file_dest_size=100G

*.db_name=DBTEST

*.db_unique_name=LAX_DBTEST

*.deferred_segment_creation=FALSE

*.diagnostic_dest=/u01/app/oracle

*.fal_client=LAX_DBTEST

*.fal_server=SAN_DBTEST

*.fast_start_mttr_target=300

*.job_queue_processes=20

*.global_names=FALSE

*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1531))'

*.log_archive_config='DG_CONFIG=(SAN_DBTEST,LAX_DBTEST)'

*.log_archive_dest_2='SERVICE=SAN_DBTEST LGWR ASYNC COMPRESSION=ENABLE REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SAN_DBTEST'

*.log_archive_max_processes=2

*.pga_aggregate_target=1G

*.processes=300

*.remote_login_passwordfile=EXCLUSIVE

*.recyclebin=OFF

*.sec_case_sensitive_logon=FALSE

*.sga_target=2G

*.sga_max_size=2G

*.standby_file_management=AUTO

*.undo_management=AUTO

*.undo_retention=10800

*.undo_tablespace=UNDOTBS

PRIMARY / STANDY: Important: create spfile from pfile when using OMF

PRIMARY: Create Database:

CREATE DATABASE

CHARACTER SET AL32UTF8

EXTENT MANAGEMENT LOCAL

DATAFILE SIZE 512M AUTOEXTEND ON NEXT 512M MAXSIZE 8G

sysaux DATAFILE SIZE 512M AUTOEXTEND ON NEXT 512M MAXSIZE 8G

LOGFILE GROUP 1 SIZE 200M,GROUP 2 SIZE 200M,GROUP 3 SIZE 200M,GROUP 4 SIZE 200M,GROUP 5 SIZE 200M

DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 512M AUTOEXTEND ON NEXT 512M MAXSIZE 8G

DEFAULT TABLESPACE users DATAFILE SIZE 512M AUTOEXTEND ON NEXT 512M MAXSIZE 8G

UNDO TABLESPACE undotbs DATAFILE SIZE 512M AUTOEXTEND ON NEXT 512M MAXSIZE 8G;

PRIMARY: Create Catalog:

Creating Additional Data Dictionary Structures

The above link provides descriptions and requirements for additional structures.

If not needed, then don’t create it.

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/catblock.sql

@?/rdbms/admin/catio.sql

@?/rdbms/admin/catoctk.sql

@?/rdbms/admin/userlock.sql

@?/rdbms/admin/utldtree.sql

alter database archivelog;

alter database force logging;

STANDBY:

create spfile from pfile='initdbtest.ora';

startup nomount

PRIMARY:

RMAN> connect target;

connected to target database: DBTEST (DBID=1182508837)

RMAN> connect auxiliary sys@lax_dbtest;

auxiliary database Password:

connected to auxiliary database: DBTEST (not mounted)

RMAN> run {DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;}

PRIMARY alert.log:

Use the following SQL commands on the standby database to create

standby redo logfiles that match the primary database:

ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 209715200;

ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;

ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;

ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;

ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 209715200;

ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 209715200;

STANDBY:

Since OMF is being used, it’s easier to execute 6 times - ALTER DATABASE ADD STANDBY LOGFILE SIZE 209715200;

SYS@dbtest:drdbtest:PHYSICAL STANDBY> select count(*) from v$standby_log;

  COUNT(*)

----------

             6

SYS@dbtest:drdbtest:PHYSICAL STANDBY> select l.member from v$logfile l, v$standby_log s where l.group#=s.group#;

MEMBER

--------------------------------------------------------------------------------

/oracle/oradata/LAX_DBTEST/onlinelog/o1_mf_6_8160x1p3_.log

/oracle/oradata/flashrecovery/LAX_DBTEST/onlinelog/o1_mf_6_8160x76v_.log

/oracle/oradata/LAX_DBTEST/onlinelog/o1_mf_7_8160xnpo_.log

/oracle/oradata/flashrecovery/LAX_DBTEST/onlinelog/o1_mf_7_8160xv6x_.log

/oracle/oradata/LAX_DBTEST/onlinelog/o1_mf_8_8160yjpq_.log

/oracle/oradata/flashrecovery/LAX_DBTEST/onlinelog/o1_mf_8_8160yxmg_.log

/oracle/oradata/LAX_DBTEST/onlinelog/o1_mf_9_8160zm39_.log

/oracle/oradata/flashrecovery/LAX_DBTEST/onlinelog/o1_mf_9_8160zto9_.log

/oracle/oradata/LAX_DBTEST/onlinelog/o1_mf_10_81610fz0_.log

/oracle/oradata/flashrecovery/LAX_DBTEST/onlinelog/o1_mf_10_81610nqp_.log

/oracle/oradata/LAX_DBTEST/onlinelog/o1_mf_11_816116yj_.log

/oracle/oradata/flashrecovery/LAX_DBTEST/onlinelog/o1_mf_11_81611gph_.log

12 rows selected.

SYS@dbtest:drdbtest:PHYSICAL STANDBY> recover managed standby database nodelay using current logfile disconnect;

Media recovery complete.

SYS@dbtest:drdbtest:PHYSICAL STANDBY>

PRIMARY:

SYS@dbtest:proddbtest:PRIMARY> create tablespace newts datafile size 100m;

Tablespace created.

SYS@dbtest:proddbtest:PRIMARY> select name from v$datafile where name like '%newts%';

NAME

--------------------------------------------------------------------------------

/oracle/oradata/SAN_DBTEST/datafile/o1_mf_newts_81621dns_.dbf

STANDBY:

SYS@dbtest:drdbtest:PHYSICAL STANDBY> select name from v$datafile where name like '%newts%';

NAME

--------------------------------------------------------------------------------

/oracle/oradata/LAX_DBTEST/datafile/o1_mf_newts_81624kmp_.dbf

SYS@dbtest:drdbtest:PHYSICAL STANDBY>

STANDBY alert log:

oracle@drdbtest:dbtest:/u01/app/oracle/diag/rdbms/lax_dbtest/dbtest/trace

> tail -12 alert_dbtest.log

RFS[3]: Assigned to RFS process 2557

RFS[3]: Selected log 6 for thread 1 sequence 10 dbid 1182606150 branch 789743302

Successfully added datafile 5 to media recovery

Datafile #5: '/oracle/oradata/LAX_DBTEST/datafile/o1_mf_newts_81624kmp_.dbf'

Media Recovery Waiting for thread 1 sequence 10 (in transit)

Recovery of Online Redo Log: Thread 1 Group 6 Seq 10 Reading mem 0

  Mem# 0: /oracle/oradata/LAX_DBTEST/onlinelog/o1_mf_6_8160x1p3_.log

  Mem# 1: /oracle/oradata/flashrecovery/LAX_DBTEST/onlinelog/o1_mf_6_8160x76v_.log

Fri Jul 27 14:33:04 2012

Recovery deleting file #5:'/oracle/oradata/LAX_DBTEST/datafile/o1_mf_newts_81624kmp_.dbf' from controlfile.

Deleted Oracle managed file /oracle/oradata/LAX_DBTEST/datafile/o1_mf_newts_81624kmp_.dbf

Recovery dropped tablespace 'NEWTS'

SQL PROMPT:

-- set sqlpprompt

define _pr="&_USER'@'&_CONNECT_IDENTIFIER> "

column pr new_value _pr

select '&_USER@&_CONNECT_IDENTIFIER:'||SYS_CONTEXT('USERENV','SERVER_HOST')||':'||SYS_CONTEXT('USERENV','DATABASE_ROLE')||'> ' pr from dual;

set sqlprompt "&_pr"

column pr clear