physical standby database configuration for user managed failover end to end test

STANDBY DATABASE CONFIGURATION FOR MANUAL SWITCHOVEREND TO END TEST

Alejandro Vargas |Principal Support Consultant Oracle Advanced Customer Services

Index

Index ............................................................................................................................ 1 Summary ...................................................................................................................... 2 Important Configuration Tips ...................................................................................... 3 Implementation Summary ............................................................................................ 3

backup ...................................................................................................................... 3 Standby Controlfile .................................................................................................. 3 A copy of the password file generated on the primary database ............................. 3 An init.ora parameter file with specific standby database parameters set for both the primary and standby role .................................................................................... 4 Configure the network to provide connectivity to both primary and standby databases .................................................................................................................. 6 Configure the local_listener parameter for none standard ports .............................. 7 Create a set of standby redologs on both the primary and standby databases ......... 7

Enabling Recovery on the Standby Database .............................................................. 8 Enabling Dataguard broker on both Databases ............................................................ 9 Create the Broker Configuration ................................................................................ 10 Check the Broker Configuration ................................................................................ 11 Enable the Broker Configuration ............................................................................... 13 Check the Databases Transport Mode Setting ........................................................... 14 Test Manual Switchover ............................................................................................ 14 Check after Manual Switchover ................................................................................. 16 Test Manual Switchover Back to the Original Primary ............................................. 16 Test Failing over after a Primary Database Failure ................................................... 18 Reinstating the Primary Database after a Failure ...................................................... 20 Recreate the crashed primary after a failed reinstate ................................................. 23 Recreate the broker configuration .............................................................................. 26 Configure Flashback Database on the Standby Database .......................................... 28 Configure flashback database on the primary database ............................................. 28 Test switchover back and forth again ........................................................................ 29 Test Failing over after a Primary Database Failure (with Flashback Database set) . . 33 Reinstating the Primary Database after a Failure ...................................................... 36 Cleanup of Archived Logs on the Standby Location ................................................. 46

Script rman_clean_arch.bat ................................................................................... 46 Script rman_clean_arch.rmn .................................................................................. 47 Log File rman_clean_arch.log ............................................................................... 47

Tnsnames Configuration for the Applications ........................................................... 48

1/49


Summary

The test described on this document was implemented to demonstrate how to manually manage switchover and emergency failover of a standby database configured in maximum performance lgwr async noaffirm mode.

The maximum performance in Async No Affirm provide high level of protection but do not compromise the availability and performance of the primary database.

The main purpose of this configuration was defined as ‘Provide a quick way to restore service in case of severe outage on the primary site’.

It was decided not to use fast start failover but to put on the DBA team the decision of when to fail over to the standby site after assessing the crash situation.

Two failover situations were analyzed and are presented on this document: On the first scenario the databases were configured with flashback database enabled with a retention time of 60 minutes. On the second scenario flashback database was not configured.

Flashback database makes much easier to reinstate the failed database in case of requiring a failover to the standby. Once the failed server is restarted the old primary only requires to be mounted and the broker will flashback it to a previous SCN and then reinstate, recover and reconfigure it to have the standby role, automatically. That reduces the time where the main database is unprotected to the minimum.

When flashback database is not enabled, after failing over to the standby the old primary database will require to be rebuilt from a backup taken from the new primary. That makes the reinstate process more complex and it takes a much longer time.

The environment used for this test was based on a Windows 2003 R2 with Oracle RDBMS 10.2.0.4

This document does not contain Dataguard step by step implementation steps but provide a summary of them. For a step by step implementation document please check Dataguard Configuration for FSFO

2/49

http://blogs.oracle.com/AlejandroVargas/2009/01/data_guard_fast_start_failover_1.html


Important Configuration Tips

The implementation of a physical standby database, configured on Maximum Performance LGWR ASYNC NO AFFIRM transport mode does require the following:

1) A full backup of the primary database2) A standby controlfile for the standby database generated on the primary

database3) A copy of the password file generated on the primary database4) An init.ora parameter file with specific standby database parameters set

for both the primary and standby role5) A network configured to provide connectivity to both primary and standby

databases6) A local_listener parameter for none standard ports7) A set of standby redologs on both the primary and standby databases

Implementation Summary

backup

The full backup was made by copying over all database files, while the primary database was shutdown, to the standby server. The primary database may be copied over while backup mode is enabled.

Standby Controlfile

Was created on the primary database using the command:

> alter database create standby controlfile as ‘c:\standby-ctlfile.ctl’ ;

The standby controlfile was copied over to the standby server, and its location set on the parameter file of the standby database.

A copy of the password file generated on the primary database

The password file needs to be generated on the primary and copied over; it cannot be generated on the standby server

3/49


An init.ora parameter file with specific standby database parameters set for both the primary and standby role

First I do prepare the a pfile from the normal primary database spfile, then edit it for the primary and for the standby.

This is the version used for the primary database:

AVDB.__db_cache_size=2197815296AVDB.__java_pool_size=16777216AVDB.__large_pool_size=16777216AVDB.__shared_pool_size=268435456AVDB.__streams_pool_size=117440512*.audit_file_dest='C:\oracle\product\10.2.0\admin\AVDB\adump'*.background_dump_dest='E:\ORACLE\ADMIN\AVDB\BDUMP'*.compatible='10.2.0.3'*.control_files='E:\ORACLE\ORADATA\AVDB\CONTROL01.CTL','G:\ORACLE\ORADATA\AVDB\CONTROL02.CTL','D:\ORACLE\ORADATA\AVDB\CONTROL03.CTL'*.core_dump_dest='E:\ORACLE\ADMIN\AVDB\CDUMP'*.db_block_size=8192*.db_domain=''*.db_file_multiblock_read_count=16*.db_files=2048*.job_queue_processes=60*.nls_date_format='DD/MM/YYYY'*.open_cursors=300*.pga_aggregate_target=314572800*.processes=1000*.recovery_parallelism=4*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'*.resource_manager_plan='NIGHTTIME'*.session_cached_cursors=50*.sessions=555*.sga_max_size=2621440000*.sga_target=2621440000*.streams_pool_size=104857600*.undo_management='AUTO'*.undo_tablespace='UNDOTBS'*.user_dump_dest='E:\ORACLE\ADMIN\AVDB\UDUMP'*.utl_file_dir='F:\ORA_UTL_DIR','E:\ORACLE\admin\AVDB\udump'#### Standby relevant parameters#### db_file_name_convert and log_file_name_convert state in ## pairs the conversion that ## needs to be applied to the names registered on the \## standby controlfile. In this case the files located on

4/49


## the remote server under 'D:\D\ORACLE\ORADATA\AVDB' will ## be located on the local server on ## 'D:\ORACLE\ORADATA\AVDB'. The convertion continue for ## the next pair of paths and so on. ##*.db_file_name_convert='D:\D\ORACLE\ORADATA\AVDB','D:\ORACLE\ORADATA\AVDB','D:\E\ORACLE\ORADATA\AVDB','E:\ORACLE\ORADATA\AVDB','D:\F\ORACLE\ORADATA\AVDB','F:\ORACLE\ORADATA\AVDB','D:\G\ORACLE\ORADATA\AVDB','G:\ORACLE\ORADATA\AVDB'*.log_file_name_convert='D:\D\ORACLE\ORADATA\AVDB','D:\ORACLE\ORADATA\AVDB'#### Db_name is the same for all databases on the ## configuration## db_unique_name is specific for each instance##*.db_name='AVDB'*.db_unique_name='AVDB'*.instance_name='AVDB'#### fal server will be always the remote database and client ## the local. These parameters will be active only when the ## database is on the standby role##*.fal_client='AVDB'*.fal_server='AVDBSTD'#### log_archive_config list the names of the instances##*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(AVDB,AVDBSTD)'#### log_archive_dest_<n> is defined here for having the ## local database as primary and the destination 2 for the ## remote database as standby##*.LOG_ARCHIVE_DEST_1='LOCATION=L:\ORACLE\oradata\AVDB\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=AVDB'*.LOG_ARCHIVE_DEST_2='SERVICE=AVDBSTD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'*.log_archive_format='AVDB_%T_%s_%R.arc'*.LOG_ARCHIVE_MAX_PROCESSES=4 #### standby_file_management auto instruct the server to ## replicate all ddl’s like create tablespace or add ## datafiles##*.standby_file_management='auto'

5/49


Configure the network to provide connectivity to both primary and standby databases

The listener and tnsnames need to have this specific configuration when using dataguard:

# LISTENER.ORA

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = AVDB_DGMGRL) (SID_NAME = AVDB) (ORACLE_HOME = C:\oracle\product\10.2.0\db_1) ) (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = C:\oracle\product\10.2.0\db_1) (PROGRAM = extproc) ) )

INBOUND_CONNECTION_TIMEOUT_LISTENER = 0

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER01)(PORT = 2115)) ) )

# TNSNAMES.ORA

AVDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER01)(PORT = 2115)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = AVDB_DGMGRL) ) )

AVDBSTD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER02)(PORT = 2115)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = AVDBSTD_DGMGRL) ) )

6/49


## The listeners need to be registered on tnsnames.ora also

LISTENER_AVDB= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER01)(PORT = 2115)))

LISTENER_AVDBSTD= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER02)(PORT = 2115)))

Configure the local_listener parameter for none standard ports

The local_listener parameter needs to be configured to the listener registered on the tnsnames.ora in this case on the primary database:

> alter system set local_listener=’LISTENER_AVDB’ scope=both;

On the standby database:

> alter system set local_listener=’LISTENER_AVDBSTD’ scope=both;

Check that the listener respond to tnsping:

C:\Documents and Settings\db411>tnsping listener_AVDB

TNS Ping Utility for 64-bit Windows: Version 10.2.0.4.0 - Production on 19-JUL-2010 11:06:20

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER01)(PORT = 2115)))OK (20 msec)

Create a set of standby redologs on both the primary and standby databases

In order to enable LGWR ASYNC transport mode we need to create standby redologs on both the primary and standby databases:

The standby redologs need to be the same size as the online redologs and have the same number of online redo log groups +1 or more. In this case we have 8 online groups so we created 10 standby redologs on each database:

7/49


alter database add standby logfile group 08 'D:\ORACLE\ORADATA\AVDB\REDO08.LOG' SIZE 209715200;alter database add standby logfile group 09 'D:\ORACLE\ORADATA\AVDB\REDO09.LOG' SIZE 209715200;alter database add standby logfile group 10 'D:\ORACLE\ORADATA\AVDB\REDO10.LOG' SIZE 209715200;alter database add standby logfile group 11 'D:\ORACLE\ORADATA\AVDB\REDO11.LOG' SIZE 209715200;alter database add standby logfile group 12 'D:\ORACLE\ORADATA\AVDB\REDO12.LOG' SIZE 209715200;alter database add standby logfile group 13 'D:\ORACLE\ORADATA\AVDB\REDO13.LOG' SIZE 209715200;alter database add standby logfile group 14 'D:\ORACLE\ORADATA\AVDB\REDO14.LOG' SIZE 209715200;alter database add standby logfile group 15 'D:\ORACLE\ORADATA\AVDB\REDO15.LOG' SIZE 209715200;alter database add standby logfile group 16 'D:\ORACLE\ORADATA\AVDB\REDO16.LOG' SIZE 209715200;alter database add standby logfile group 17 'D:\ORACLE\ORADATA\AVDB\REDO17.LOG' SIZE 209715200;alter database add standby logfile group 18 'D:\ORACLE\ORADATA\AVDB\REDO18.LOG' SIZE 209715200;

Enabling Recovery on the Standby Database

Once all this configuration steps are ready we can check enabling recovery, for this we startup mount the standby database and execute the recover database command:

alter database add standby logfile group 10 'D:\ORACLE\ORADATA\AVDB\REDO10.LOG' SIZE 209715200;

Then we can do switch logs on the primary and check that the changes are applied to the standby

The Standby:Database log mode Archive ModeAutomatic archival EnabledArchive destination D:\L\ORACLE\oradata\AVDB\archiveOldest online log sequence 0Next log sequence to archive 0Current log sequence 1031

8/49


NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE--------- ------------------------------ ---------- ----------------AVDB AVDBSTD MOUNTED PHYSICAL STANDBY

The Primary:Database log mode Archive ModeAutomatic archival EnabledArchive destination L:\ORACLE\oradata\AVDB\archiveOldest online log sequence 1026Next log sequence to archive 1031Current log sequence 1031NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE--------- ------------------------------ ---------- ----------------AVDB AVDB READ WRITE PRIMARY

Enabling Dataguard broker on both Databases

Once the standby is receiving transactions and is kept in sync we can move over to the dataguard broker the responsibility of managing the environment, by setting both on the primary and standby databases the following parameter:

> alter system set dg_broker_start=true

This parameter will start the broker background process and will register the XPT service with the local listener. This service will be used for communications between the broker and the databases.

It is important to assure that this service was attached to the listener before configuring the borker, to do this use the lsnrctl status command: C:\Documents and Settings\db411>lsnrctl status

LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 19-JUL-2010 11:11:03


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER01)(PORT=2115)))STATUS of the LISTENER------------------------Alias LISTENER

9/49


Version TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - ProductionStart Date 19-JUL-2010 10:25:32Uptime 0 days 0 hr. 45 min. 36 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.oraListener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.logListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER01)(PORT=2115)))Services Summary...Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...Service "AVDB" has 1 instance(s). Instance "AVDB", status READY, has 1 handler(s) for this service...Service "AVDB_DGMGRL" has 1 instance(s). Instance "AVDB", status UNKNOWN, has 1 handler(s) for this service...Service "AVDB_XPT" has 1 instance(s). Instance "AVDB", status READY, has 1 handler(s) for this service...The command completed successfully

Create the Broker Configuration

Using the dataguard broker greatly simplify the administration of the standby configuration, it automates most of the administrative tasks.

C:\Documents and Settings\db411>dgmgrl sys/AVDBPWDGMGRL for 64-bit Windows: Version 10.2.0.4.0 - 64bit Production


Welcome to DGMGRL, type "help" for information.Connected.DGMGRL> create configuration dgAVDB as primary database is AVDB connect identifier is AVDB;

Configuration "dgAVDB" created with primary database "AVDB"

10/49


DGMGRL> add database AVDBSTD as connect identifier is AVDBSTD maintained as physical;

Database "AVDBSTD" added

Check the Broker Configuration

DGMGRL> show configuration;

Configuration Name: dgAVDB Enabled: NO Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: AVDB - Primary database AVDBSTD - Physical standby database

Current status for "dgAVDB":DISABLED

DGMGRL> show database verbose AVDB;

Database Name: AVDB Role: PRIMARY Enabled: NO Intended State: OFFLINE Instance(s): AVDB

Properties: InitialConnectIdentifier = 'AVDB' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' Dependency = '' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '180' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'auto' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1'

11/49


DbFileNameConvert = 'D:\D\ORACLE\ORADATA\AVDB, D:\ORACLE\ORADATA\AVDB, D:\E\ORACLE\ORADATA\AVDB, E:\ORACLE\ORADATA\AVDB, D:\F\ORACLE\ORADATA\AVDB, F:\ORACLE\ORADATA\AVDB, D:\G\ORACLE\ORADATA\AVDB, G:\ORACLE\ORADATA\AVDB' LogFileNameConvert = 'D:\D\ORACLE\ORADATA\AVDB, D:\ORACLE\ORADATA\AVDB' FastStartFailoverTarget = '' StatusReport = '(monitor)' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' HostName = 'SERVER01' SidName = 'AVDB' LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER01)(PORT=2115))' StandbyArchiveLocation = 'L:\ORACLE\oradata\AVDB\archive' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = 'AVDB_%T_%s_%R.arc' LatestLog = '(monitor)' TopWaitEvents = '(monitor)'

Current status for "AVDB":DISABLED

DGMGRL> show database verbose AVDBSTD;

Database Name: AVDBSTD Role: PHYSICAL STANDBY Enabled: NO Intended State: OFFLINE Instance(s): AVDBSTD

Properties: InitialConnectIdentifier = 'AVDBSTD' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' Dependency = '' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '180' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyParallel = 'AUTO' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4'

12/49


LogArchiveMinSucceedDest = '1' DbFileNameConvert = 'D:\ORACLE\ORADATA\AVDB, D:\D\ORACLE\ORADATA\AVDB, E:\ORACLE\ORADATA\AVDB, D:\E\ORACLE\ORADATA\AVDB, F:\ORACLE\ORADATA\AVDB, D:\F\ORACLE\ORADATA\AVDB, G:\ORACLE\ORADATA\AVDB, D:\G\ORACLE\ORADATA\AVDB' LogFileNameConvert = 'D:\ORACLE\ORADATA\AVDB, D:\D\ORACLE\ORADATA\AVDB' FastStartFailoverTarget = '' StatusReport = '(monitor)' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' HostName = 'SERVER02' SidName = 'AVDBSTD' LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER02)(PORT=2115))' StandbyArchiveLocation = 'D:\L\ORACLE\oradata\AVDB\archive' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = 'AVDB_%T_%s_%R.arc' LatestLog = '(monitor)' TopWaitEvents = '(monitor)'

Current status for "AVDBSTD":DISABLED

Enable the Broker Configuration

DGMGRL> enable configuration;Enabled.DGMGRL> show configuration;

Configuration Name: dgAVDB Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: AVDB - Primary database AVDBSTD - Physical standby database

Current status for "dgAVDB":SUCCESS

DGMGRL> enable database AVDBSTD;Enabled.DGMGRL> show configuration;

Configuration Name: dgAVDB Enabled: YES

13/49


Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: AVDB - Primary database AVDBSTD - Physical standby database


DGMGRL> show database AVDB

Database Name: AVDB Role: PRIMARY Enabled: YES Intended State: ONLINE Instance(s): AVDB

Current status for "AVDB":SUCCESS

DGMGRL> show database AVDBSTD

Database Name: AVDBSTD Role: PHYSICAL STANDBY Enabled: YES Intended State: ONLINE Instance(s): AVDBSTD

Current status for "AVDBSTD":SUCCESS

Check the Databases Transport Mode Setting

DGMGRL> show database AVDB logxptmode LogXptMode = 'ASYNC'DGMGRL> show database AVDBSTD logxptmode LogXptMode = 'ASYNC'

Test Manual Switchover

Before doing a manual switchover is always recommended to check that the configuration is healthy:


Configuration

14/49


Name: dgAVDB Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: AVDB - Primary database AVDBSTD - Physical standby database


Execute the switchover:

DGMGRL> switchover to AVDBSTD;Performing switchover NOW, please wait...Operation requires shutdown of instance "AVDB" on database "AVDB"Shutting down instance "AVDB"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires shutdown of instance "AVDBSTD" on database "AVDBSTD"Shutting down instance "AVDBSTD"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires startup of instance "AVDB" on database "AVDB"Starting instance "AVDB"...ORACLE instance started.Database mounted.Operation requires startup of instance "AVDBSTD" on database "AVDBSTD"Starting instance "AVDBSTD"...ORACLE instance started.Database mounted.Switchover succeeded, new primary is "AVDBSTD"

Check after the switchover succeeded:DGMGRL> show configuration

Configuration Name: dgAVDB Enabled: YES

15/49


Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: AVDB - Physical standby database AVDBSTD - Primary database


Check after Manual Switchover

On the new primary database AVDBSTD we can see that the broker automatically reconfigured the log_archive_dest_2 to match the transport mode for the standby.

SQL> show parameters log_archive_dest_2 log_archive_dest_2 got reconfigured, note the LGWR ASYNC NOAFFIRM property

NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string service="(DESCRIPTION=(ADDRESS _LIST=(ADDRESS=(PROTOCOL=TCP)( HOST= SERVER02 )(PORT=2115)))(CONNECT_D ATA=(SERVICE_NAME=AVDB_XPT)(IN STANCE_NAME=AVDB)(SERVER=dedic ated)))", LGWR ASYNC NOAFFI RM delay=0 OPTIONAL max_failur e=0 max_connections=1 reopen =300 db_unique_name="AVDB" reg ister net_timeout=180 valid_f or=(online_logfile,primary_rol e)

On the new standby log_archive_dest_2 got cleared:

SQL> show parameters log_archive_dest_2

NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string

Test Manual Switchover Back to the Original Primary

DGMGRL> switchover to AVDBPerforming switchover NOW, please wait...Operation requires shutdown of instance "AVDBSTD" on database "AVDBSTD"

16/49


Shutting down instance "AVDBSTD"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires shutdown of instance "AVDB" on database "AVDB"Shutting down instance "AVDB"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires startup of instance "AVDBSTD" on database "AVDBSTD"Starting instance "AVDBSTD"...ORACLE instance started.Database mounted.Operation requires startup of instance "AVDB" on database "AVDB"Starting instance "AVDB"...ORACLE instance started.Database mounted.Switchover succeeded, new primary is "AVDB"DGMGRL> show configuration;





17/49






DGMGRL>

Test Failing over after a Primary Database Failure

In this case we will check the scenario this configuration is planned to be used on. The primary database crash and cannot be recovered in a short period of time, then we need to fail over to the standby database.

Before starting we will check status of both databases:

The Standby:Database log mode Archive ModeAutomatic archival EnabledArchive destination D:\L\ORACLE\oradata\AVDB\archiveOldest online log sequence 0Next log sequence to archive 0Current log sequence 1031

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE--------- ------------------------------ ---------- ----------------AVDB AVDBSTD MOUNTED PHYSICAL STANDBY

The Primary:Database log mode Archive Mode

18/49


Automatic archival EnabledArchive destination L:\ORACLE\oradata\AVDB\archiveOldest online log sequence 1026Next log sequence to archive 1031Current log sequence 1031

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE--------- ------------------------------ ---------- ----------------AVDB AVDB READ WRITE PRIMARY

Abort the primary database to simulate a failure:

SQL> shutdown abort;From alert log:

Mon Jul 19 13:24:52 2010LNS: Standby redo logfile selected for thread 1 sequence 1031 for destination LOG_ARCHIVE_DEST_2Mon Jul 19 13:25:16 2010Shutting down instance (abort)License high water mark = 5Instance terminated by USER, pid = 2204Mon Jul 19 13:29:39 2010WARNING: inbound connection timed out (ORA-3136)

Connect to DGMGRL and to the standby database:

DGMGRL> connect sys/[email protected]> show configuration


Current status for "dgAVDB":Error: ORA-16625: cannot reach the database

Execute the failover:

19/49


DGMGRL> failover to AVDBSTD;Performing failover NOW, please wait...Failover succeeded, new primary is "AVDBSTD"


Configuration Name: dgAVDB Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: AVDB - Physical standby database (disabled) AVDBSTD - Primary database


Check the new primary Note that the sequence was reset to be #1

Database log mode Archive ModeAutomatic archival EnabledArchive destination D:\L\ORACLE\oradata\AVDB\archiveOldest online log sequence 1Next log sequence to archive 1Current log sequence 1

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE--------- ------------------------------ ---------- ----------------AVDB AVDBSTD READ WRITE PRIMARY

Reinstating the Primary Database after a Failure

The next step is to reinstate the failed Primary database, here we may find one of two scenarios: 1) the primary can be reinstated, this is most feasible when there were no changes on the new primary yet, or 2) the primary needs to be recreated. We need to check the feedback the broker will provide and act accordingly.

To avoid being required to rebuild the primary it is convenient to enable flashback database that will permit to the broker to flashback the database to an SCN prior to the crash and then reinstate it.

20/49






Database Name: AVDBSTD Role: PRIMARY Enabled: YES Intended State: ONLINE Instance(s): AVDBSTD



Database Name: AVDB Role: PHYSICAL STANDBY Enabled: NO Intended State: ONLINE Instance(s): AVDB

Current status for "AVDB":Error: ORA-16661: the standby database needs to be reinstated

The message ORA-16661 means that the broker will be able to reinstate the database; otherwise we will need to recreate the old primary.

To reinstate we need to mount the failed database

21/49


C:\Documents and Settings\db411\Desktop\scripts>sqlplus sys/AVDBPW@AVDB as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 19 13:39:12 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;ORACLE instance started.

Total System Global Area 2634022912 bytesFixed Size 2068072 bytesVariable Size 419430808 bytesDatabase Buffers 2197815296 bytesRedo Buffers 14708736 bytesDatabase mounted.

Then connect to the DGMGRL

C:\Documents and Settings\db411>dgmgrl sys/AVDBPW@AVDBSTDDGMGRL for 64-bit Windows: Version 10.2.0.4.0 - 64bit Production


Welcome to DGMGRL, type "help" for information.Connected.DGMGRL> show configuration




22/49




DGMGRL> reinstate database AVDB;Reinstating database "AVDB", please wait...Error: ORA-16653: failed to reinstate database

Failed.Reinstatement of database "AVDB" failed



Current status for "AVDB":Error: ORA-16795: database resource guard detects that database re-creation is required

Recreate the crashed primary after a failed reinstate

If reinstatement fail then we need to recreate the failed primary database by following the exact same procedure we used to create the original standby

Once the old primary is ready as a standby database we will remove it from the broker and re-add it to the configuration, in addition we will configure flashback database to test again reinstantiation after a critical failure.

Once the backup finished I have edited the new pfile for the standby and create from it a new spfile

23/49


C:\Documents and Settings\db411>sqlplus SYS/AVDBPW@AVDB AS SYSDBAConnected to an idle instance.

SQL> CREATE SPFILE FROM PFILE='C:\oracle\product\10.2.0\db_1\database\init4standby.ora';

File created.

SQL> STARTUP MOUNT;ORACLE instance started.


On the primary database enable destination 2 and check the standby is receiving the changes:

SQL> show parameters log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string service="(DESCRIPTION=(ADDRESS _LIST=(ADDRESS=(PROTOCOL=TCP)( HOST=SERVER01 )(PORT=2115)))(CONNECT_D ATA=(SERVICE_NAME=AVDB_XPT)(IN STANCE_NAME=AVDB)(SERVER=dedic ated)))", LGWR ASYNC NOAFFI RM delay=0 OPTIONAL max_failur e=0 max_connections=1 reopen =300 db_unique_name="AVDB" reg ister net_timeout=180 valid_f or=(online_logfile,primary_rol e)

Check that Primary and Standby are in Sync


NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE

24/49


--------- ------------------------------ ---------- ----------------AVDB AVDBSTD READ WRITE PRIMARY

Database log mode Archive ModeAutomatic archival EnabledArchive destination L:\ORACLE\oradata\AVDB\archiveOldest online log sequence 9Next log sequence to archive 0Current log sequence 10

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE--------- ------------------------------ ---------- ----------------AVDB AVDB MOUNTED PHYSICAL STANDBY

After that I need to enable the broker configure the local listener and check that the XPT service was registered with the listener

SQL> alter system set dg_broker_start=true scope=both;

System altered.

SQL> alter system set local_listener='LISTENER_AVDB' scope=both;C:\Documents and Settings\db411\Desktop\scripts>lsnrctl status

LSNRCTL for 64-bit Windows: Version 10.2.0.4.0 - Production on 19-JUL-2010 17:34:27


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER01)(PORT=2115)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for 64-bit Windows: Version 10.2.0.4.0 - ProductionStart Date 19-JUL-2010 10:25:32Uptime 0 days 7 hr. 9 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.oraListener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.logListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER01)(PORT=2115)))Services Summary...Service "PLSExtProc" has 1 instance(s).

25/49


Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...Service "AVDB" has 1 instance(s). Instance "AVDB", status READY, has 1 handler(s) for this service...Service "AVDB_DGMGRL" has 1 instance(s). Instance "AVDB", status UNKNOWN, has 1 handler(s) for this service...Service "AVDB_XPT" has 1 instance(s). Instance "AVDB", status READY, has 1 handler(s) for this service...The command completed successfully

Recreate the broker configuration



Welcome to DGMGRL, type "help" for information.Connected.DGMGRL> create configuration dgAVDB> as primary database is AVDBSTD> connect identifier is AVDBSTD;Configuration "dgAVDB" created with primary database "AVDBSTD"DGMGRL> add database AVDB as> connect identifier is AVDB> maintained as physical;Database "AVDB" addedDGMGRL> show configuration;

Configuration Name: dgAVDB Enabled: NO Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: AVDBSTD - Primary database AVDB - Physical standby database

Current status for "dgAVDB":DISABLED

DGMGRL> enable configuration;Enabled.DGMGRL> enable database AVDB;Enabled.DGMGRL> show configuration

Configuration Name: dgAVDB Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases:

26/49


AVDBSTD - Primary database AVDB - Physical standby database



Database Name: AVDB Role: PHYSICAL STANDBY Enabled: YES Intended State: ONLINE Instance(s): AVDB





27/49


Configure Flashback Database on the Standby Database

First we will configure flashback on the standby database

SQL> select NAME,FLASHBACK_ON from v$database;

NAME FLASHBACK_ON--------- ------------------AVDB NO

SQL> alter system set db_recovery_file_dest_size=10000M scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='L:\ORACLE\oradata\AVDB\flashback' scope=both;

System altered.

SQL> alter system set db_flashback_retention_target=60 scope=both;

System altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY;

Database altered.

Configure flashback database on the primary database

SQL> alter system set db_recovery_file_dest_size=10000M scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='D:\L\ORACLE\oradata\AVDB\flashback' scope=both;

28/49


System altered.

SQL> alter system set db_flashback_retention_target=60 scope=both;

System altered.

SQL> SHUTDOWN IMMEDIATE;Database closed.Database dismounted.ORACLE instance shut down.

SQL> EXIT

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Documents and Settings\db411\Desktop\scripts>sqlplus sys/AVDBPW@AVDBSTD as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 19 18:52:24 2010



SQL> STARTUP MOUNT;ORACLE instance started.


SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

Test switchover back and forth again

Check that the environment is healthy before the switchover

DGMGRL> connect sys/AVDBPWConnected.DGMGRL> show configuration

29/49


Configuration Name: dgAVDB Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: AVDBSTD - Physical standby database AVDB - Primary database








Switchover

DGMGRL> switchover to AVDBSTDPerforming switchover NOW, please wait...Operation requires shutdown of instance "AVDB" on database "AVDB"Shutting down instance "AVDB"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires shutdown of instance "AVDBSTD" on database "AVDBSTD"Shutting down instance "AVDBSTD"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.

30/49


Operation requires startup of instance "AVDB" on database "AVDB"Starting instance "AVDB"...ORACLE instance started.Database mounted.Operation requires startup of instance "AVDBSTD" on database "AVDBSTD"Starting instance "AVDBSTD"...ORACLE instance started.Database mounted.Switchover succeeded, new primary is "AVDBSTD"

Switchover back

DGMGRL> show configuration

Configuration Name: dgAVDB Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: AVDBSTD - Primary database AVDB - Physical standby database



Database Name: AVDB Role: PHYSICAL STANDBY Enabled: YES Intended State: ONLINE Instance(s): AVDB





DGMGRL> switchover to AVDBPerforming switchover NOW, please wait...

31/49


Operation requires shutdown of instance "AVDBSTD" on database "AVDBSTD"Shutting down instance "AVDBSTD"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires shutdown of instance "AVDB" on database "AVDB"Shutting down instance "AVDB"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires startup of instance "AVDBSTD" on database "AVDBSTD"Starting instance "AVDBSTD"...ORACLE instance started.Database mounted.Operation requires startup of instance "AVDB" on database "AVDB"Starting instance "AVDB"...ORACLE instance started.Database mounted.Switchover succeeded, new primary is "AVDB"








Database Name: AVDBSTD Role: PHYSICAL STANDBY Enabled: YES

32/49


Intended State: ONLINE Instance(s): AVDBSTD


Test Failing over after a Primary Database Failure (with Flashback Database set)

Databases are in synch

Database log mode Archive ModeAutomatic archival EnabledArchive destination L:\ORACLE\oradata\AVDB\archiveOldest online log sequence 56Next log sequence to archive 61Current log sequence 61

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE--------- -------------------- ---------- ----------------AVDB AVDB READ WRITE PRIMARY

Database log mode Archive ModeAutomatic archival EnabledArchive destination dgsby_AVDBSTDOldest online log sequence 0Next log sequence to archive 0Current log sequence 61

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE--------- --------------------- ---------- ----------------AVDB AVDBSTD MOUNTED PHYSICAL STANDBY

The broker configuration is healthy




33/49








Check That Flashback Database is Configured On Primary and Standby

On AVDB

SQL> select name,db_unique_name,flashback_on from v$database;

NAME DB_UNIQUE_NAME FLASHBACK_ON--------- ------------------------------ ------------------AVDB AVDB YES

select estimated_flashback_size, retention_target, flashback_size from v$flashback_database_log ;

ESTIMATED_FLASHBACK_SIZE RETENTION_TARGET FLASHBACK_SIZE------------------------ ---------------- -------------- 9856000 60 79708160

select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log ;

OLDEST_FLASHBACK_SCN OLDEST_FL-------------------- ---------

34/49


12261282579 25-JUL-10

On AVDBSTD

SQL> select name,db_unique_name,flashback_on from v$database;

NAME DB_UNIQUE_NAME FLASHBACK_ON--------- ------------------------------ ------------------AVDB AVDBSTD YES

select estimated_flashback_size,retention_target, flashback_size from v$flashback_database_log ;

ESTIMATED_FLASHBACK_SIZE RETENTION_TARGET FLASHBACK_SIZE------------------------ ---------------- -------------- 11328512 60 79708160

select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log ;

OLDEST_FLASHBACK_SCN OLDEST_FL-------------------- --------- 12261282297 25-JUL-10

The primary database crashes

Sun Jul 25 09:00:12 2010LNS: Standby redo logfile selected for thread 1 sequence 61 for destination LOG_ARCHIVE_DEST_2Sun Jul 25 09:23:32 2010Shutting down instance (abort)License high water mark = 5Instance terminated by USER, pid = 1044

Connect to DGMGRL and to the standby database:



Welcome to DGMGRL, type "help" for information.Connected.DGMGRL> show configuration

35/49



Current status for "dgAVDB":Error: ORA-16625: cannot reach the database

Execute the failover:

DGMGRL> failover to AVDBSTD;Performing failover NOW, please wait...Failover succeeded, new primary is "AVDBSTD"


Configuration Name: dgAVDB Enabled: YES Protection Mode: MaxPerformance Fast-Start Failover: DISABLED Databases: AVDBSTD - Primary database AVDB - Physical standby database (disabled)


Check the new primary Note that the sequence was reset to be #1


NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE--------- ------------------------------ ---------- ----------------AVDB AVDBSTD READ WRITE PRIMARY

Reinstating the Primary Database after a Failure

36/49


The next step is to reinstate the failed Primary database, here we may find one of two scenarios: 1) the primary can be reinstated, this is most feasible when there were no changes on the new primary yet, or 2) the primary needs to be recreated. We need to check the feedback the broker will provide and act accordingly.

To avoid being required to rebuild the primary it is convenient to enable flashback database that will permit to the broker to flashback the database to an SCN prior to the crash and then reinstate it.




The message ORA-16661 means that the broker will be able to reinstate the database; otherwise we will need to recreate the old primary.

To reinstate we need to mount the failed database

C:\Documents and Settings\db411\Desktop\scripts>sqlplus sys/AVDBPW@AVDB as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Jul 25 09:32:24 2010



SQL> startup mountORACLE instance started.

Total System Global Area 2634022912 bytesFixed Size 2068072 bytesVariable Size 436208024 bytesDatabase Buffers 2181038080 bytesRedo Buffers 14708736 bytes

37/49


Database mounted.

From within DGMGRL reinstate the failed database

DGMGRL> reinstate database AVDB;Reinstating database "AVDB", please wait...Operation requires shutdown of instance "AVDB" on database "AVDB"Shutting down instance "AVDB"...ORA-01109: database not open

Database dismounted.ORACLE instance shut down.Operation requires startup of instance "AVDB" on database "AVDB"Starting instance "AVDB"...ORACLE instance started.Database mounted.Continuing to reinstate database "AVDB" ...Reinstatement of database "AVDB" succeeded

Check the reinstated database alert log

All the stages of the reinstate can be followed on the alert log of the reinstated database:

1) database is mounted

Database mounted in Exclusive ModeCompleted: ALTER DATABASE MOUNTSun Jul 25 09:32:41 2010

2) the broker process is started and it will drive the reinstatement

Starting Data Guard Broker (DMON)NSV0 started with pid=16, OS id=3868INSV started with pid=17, OS id=4064Sun Jul 25 09:32:48 2010

3) the old primary log_archive_dest_2 and log_archive_dest_state_2 are reset

ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;NSV0 started with pid=18, OS id=3676RSM0 started with pid=19, OS id=2900Sun Jul 25 09:34:56 2010ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;Sun Jul 25 09:34:56 2010

38/49


ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;Sun Jul 25 09:34:56 2010

4) database is flashed back to an SCN so that it can be resynchronized with the new primary

FLASHBACK DATABASE TO SCN 12261313471Flashback Restore StartSun Jul 25 09:35:11 2010Flashback Restore CompleteFlashback Media Recovery Start parallel recovery started with 7 processesSun Jul 25 09:35:19 2010Recovery of Online Redo Log: Thread 1 Group 3 Seq 58 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\AVDB\REDO3.LOGSun Jul 25 09:35:19 2010Recovery of Online Redo Log: Thread 1 Group 4 Seq 59 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\AVDB\REDO4.LOGSun Jul 25 09:35:20 2010Recovery of Online Redo Log: Thread 1 Group 5 Seq 60 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\AVDB\REDO5.LOGSun Jul 25 09:35:20 2010Recovery of Online Redo Log: Thread 1 Group 6 Seq 61 Reading mem 0 Mem# 0: D:\ORACLE\ORADATA\AVDB\REDO6.LOGSun Jul 25 09:35:21 2010Incomplete Recovery applied until change 12261313472Flashback Media Recovery CompleteCompleted: FLASHBACK DATABASE TO SCN 12261313471

5) The database is converted to a physical standby

Sun Jul 25 09:35:27 2010alter database convert to physical standbyClearing standby activation ID 1046488175 (0x3e60246f)The primary database controlfile was created using the'MAXLOGFILES 42' clause.There is space for up to 34 standby redo logfilesUse the following SQL commands on the standby database to createstandby 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;ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 209715200;

39/49


ALTER DATABASE ADD STANDBY LOGFILE 'srl8.f' SIZE 209715200;ALTER DATABASE ADD STANDBY LOGFILE 'srl9.f' SIZE 209715200;Completed: alter database convert to physical standby 6) The database is shutdown

Sun Jul 25 09:35:30 2010Shutting down instance: further logons disabledSun Jul 25 09:35:30 2010Stopping background process CJQ0Sun Jul 25 09:35:30 2010Stopping background process MMNLSun Jul 25 09:35:31 2010Stopping background process MMONSun Jul 25 09:35:32 2010Shutting down instance (immediate)License high water mark = 9Sun Jul 25 09:35:32 2010Stopping Job queue slave processes, flags = 7Sun Jul 25 09:35:32 2010Job queue slave processes stoppedSun Jul 25 09:35:32 2010alter database CLOSE NORMALORA-1109 signalled during: alter database CLOSE NORMAL...Sun Jul 25 09:35:32 2010alter database DISMOUNTCompleted: alter database DISMOUNTARCH: Archival disabled due to shutdown: 1089Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeShutting down Data Guard Broker processesSun Jul 25 09:35:34 2010Completed: Data Guard Broker shutdownSun Jul 25 09:35:36 2010ARCH: Archival disabled due to shutdown: 1089Shutting down archive processesArchiving is disabledArchive process shutdown avoided: 0 activeSun Jul 25 09:35:37 2010

7) The database is mounted

Starting ORACLE instance (normal)LICENSE_MAX_SESSION = 0LICENSE_SESSIONS_WARNING = 0Picked latch-free SCN scheme 3Autotune of undo retention is turned on. IMODE=BRILAT =121

40/49


LICENSE_MAX_USERS = 0SYS auditing is disabledksdpec: called for event 13740 prior to event group initializationStarting up ORACLE RDBMS Version: 10.2.0.4.0.System parameters with non-default values: processes = 1000 sessions = 1105 sga_max_size = 2634022912 __shared_pool_size = 285212672 __large_pool_size = 16777216 __java_pool_size = 16777216 __streams_pool_size = 117440512 streams_pool_size = 117440512 nls_date_format = DD/MM/YYYY resource_manager_plan = NIGHTTIME sga_target = 2634022912 control_files = G:\ORACLE\ORADATA\AVDB\AVDB_STANDBY_CTL.CTL db_file_name_convert = D:\D\ORACLE\ORADATA\AVDB, D:\ORACLE\ORADATA\AVDB, D:\E\ORACLE\ORADATA\AVDB, E:\ORACLE\ORADATA\AVDB, D:\F\ORACLE\ORADATA\AVDB, F:\ORACLE\ORADATA\AVDB, D:\G\ORACLE\ORADATA\AVDB, G:\ORACLE\ORADATA\AVDB log_file_name_convert = D:\D\ORACLE\ORADATA\AVDB, D:\ORACLE\ORADATA\AVDB db_block_size = 8192 __db_cache_size = 2181038080 compatible = 10.2.0.3 log_archive_config = dg_config=(AVDBSTD) log_archive_dest_1 = location="L:\ORACLE\oradata\AVDB\archive", valid_for=(ONLINE_LOGFILE,ALL_ROLES) log_archive_dest_2 = log_archive_dest_state_1 = ENABLE log_archive_dest_state_2 = ENABLE log_archive_max_processes= 4 log_archive_min_succeed_dest= 1 standby_archive_dest = log_archive_trace = 0 log_archive_format = AVDB_%T_%s_%R.arc fal_client = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER01)(PORT=2115)))(CONNECT_DATA=(SERVICE_NAME=AVDB_XPT)(INSTANCE_NAME=AVDB)(SERVER=dedicated))) fal_server = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER02)(PORT=2115)))(CONNECT_DATA=(SERVICE_NAME=AVDBSTD_XPT)(SERVER=dedicated))) archive_lag_target = 0

41/49


db_files = 2048 db_file_multiblock_read_count= 16 db_recovery_file_dest = L:\ORACLE\oradata\AVDB\flashback db_recovery_file_dest_size= 10485760000 standby_file_management = auto recovery_parallelism = 4 db_flashback_retention_target= 60 undo_management = AUTO undo_tablespace = UNDOTBS remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = AVDB local_listener = LISTENER_AVDB session_cached_cursors = 50 utl_file_dir = F:\ORA_UTL_DIR, E:\ORACLE\admin\AVDB\udump job_queue_processes = 60 audit_file_dest = C:\ORACLE\PRODUCT\10.2.0\ADMIN\AVDB\ADUMP background_dump_dest = E:\ORACLE\ADMIN\AVDB\BDUMP user_dump_dest = E:\ORACLE\ADMIN\AVDB\UDUMP db_name = AVDB db_unique_name = AVDB open_cursors = 300 pga_aggregate_target = 314572800 dg_broker_start = TRUEPMON started with pid=2, OS id=2532PSP0 started with pid=3, OS id=2404MMAN started with pid=4, OS id=496DBW0 started with pid=5, OS id=3088LGWR started with pid=6, OS id=4072CKPT started with pid=7, OS id=1072SMON started with pid=8, OS id=3644RECO started with pid=9, OS id=1996CJQ0 started with pid=10, OS id=3752MMON started with pid=11, OS id=1752MMNL started with pid=12, OS id=2136DMON started with pid=13, OS id=4076Sun Jul 25 09:35:38 2010alter database mountSun Jul 25 09:35:42 2010Setting recovery target incarnation to 6ARCH: STARTING ARCH PROCESSESARC0 started with pid=15, OS id=3900ARC1 started with pid=16, OS id=3916ARC2 started with pid=17, OS id=3660Sun Jul 25 09:35:42 2010ARC0: Archival startedARC1: Archival startedARC2: Archival started

42/49


ARC3: Archival startedARC3 started with pid=18, OS id=1560Sun Jul 25 09:35:43 2010ARCH: STARTING ARCH PROCESSES COMPLETESun Jul 25 09:35:43 2010ARC0: Becoming the 'no FAL' ARCHARC0: Becoming the 'no SRL' ARCHARC0: Thread not mountedSun Jul 25 09:35:43 2010ARC1: Becoming the heartbeat ARCHARC1: Thread not mountedSun Jul 25 09:35:43 2010ARC2: Thread not mountedSun Jul 25 09:35:43 2010Successful mount of redo thread 1, with mount id 1046492586Sun Jul 25 09:35:43 2010Allocated 15937344 bytes in shared pool for flashback generation bufferStarting background process RVWRRVWR started with pid=19, OS id=2464Sun Jul 25 09:35:43 2010Physical Standby Database mounted.Completed: alter database mountSun Jul 25 09:35:44 2010ARC3: Thread not mountedSun Jul 25 09:35:46 2010Starting Data Guard Broker (DMON)INSV started with pid=20, OS id=1068NSV0 started with pid=21, OS id=3340RSM0 started with pid=22, OS id=2896Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DESTSun Jul 25 09:35:58 2010ALTER SYSTEM SET log_archive_dest_1='location="L:\ORACLE\oradata\AVDB\archive"',' valid_for=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH SID='AVDB';Sun Jul 25 09:35:58 2010ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH SID='AVDB';Sun Jul 25 09:35:58 2010ALTER SYSTEM SET standby_archive_dest='L:\ORACLE\oradata\AVDB\archive' SCOPE=BOTH SID='AVDB';Sun Jul 25 09:35:58 2010ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='AVDB';Sun Jul 25 09:35:58 2010ALTER SYSTEM SET log_archive_format='AVDB_%T_%s_%R.arc' SCOPE=SPFILE SID='AVDB';Sun Jul 25 09:35:58 2010

43/49


ALTER SYSTEM SET standby_file_management='auto' SCOPE=BOTH SID='*';Sun Jul 25 09:35:58 2010ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';Sun Jul 25 09:35:58 2010ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';Sun Jul 25 09:35:58 2010ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';Sun Jul 25 09:35:58 2010ALTER SYSTEM SET db_file_name_convert='D:\D\ORACLE\ORADATA\AVDB','D:\ORACLE\ORADATA\AVDB','D:\E\ORACLE\ORADATA\AVDB','E:\ORACLE\ORADATA\AVDB','D:\F\ORACLE\ORADATA\AVDB','F:\ORACLE\ORADATA\AVDB','D:\G\ORACLE\ORADATA\AVDB','G:\ORACLE\ORADATA\AVDB' SCOPE=SPFILE;Sun Jul 25 09:35:58 2010ALTER SYSTEM SET log_file_name_convert='D:\D\ORACLE\ORADATA\AVDB','D:\ORACLE\ORADATA\AVDB' SCOPE=SPFILE;Sun Jul 25 09:35:59 2010ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER02)(PORT=2115)))(CONNECT_DATA=(SERVICE_NAME=AVDBSTD_XPT)(SERVER=dedicated)))' SCOPE=BOTH;Sun Jul 25 09:35:59 2010ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=SERVER01)(PORT=2115)))(CONNECT_DATA=(SERVICE_NAME=AVDB_XPT)(INSTANCE_NAME=AVDB)(SERVER=dedicated)))' SCOPE=BOTH;Sun Jul 25 09:35:59 2010

8) The database is ready to start receiving transactions from the primary

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILEMRP0 started with pid=23, OS id=2332Managed Standby Recovery starting Real Time Apply parallel recovery started with 7 processesSun Jul 25 09:36:10 2010Waiting for all non-current ORLs to be archived...Clearing online redo logfile 1 D:\ORACLE\ORADATA\AVDB\REDO1.LOGClearing online log 1 of thread 1 sequence number 56Sun Jul 25 09:36:11 2010Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILESun Jul 25 09:36:11 2010Clearing online redo logfile 1 completeClearing online redo logfile 2 D:\ORACLE\ORADATA\AVDB\REDO2.LOGClearing online log 2 of thread 1 sequence number 57

44/49


Clearing online redo logfile 2 completeClearing online redo logfile 3 D:\ORACLE\ORADATA\AVDB\REDO3.LOGClearing online log 3 of thread 1 sequence number 58Sun Jul 25 09:36:14 2010Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[1]: Assigned to RFS process 2296RFS[1]: Identified database type as 'physical standby'Primary database is in MAXIMUM PERFORMANCE modeSun Jul 25 09:36:14 2010RFS LogMiner: Client disabled from further notificationPrimary database is in MAXIMUM PERFORMANCE modeSun Jul 25 09:36:14 2010Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[2]: Assigned to RFS process 3848RFS[2]: Identified database type as 'physical standby'Sun Jul 25 09:36:14 2010RFS[1]: Successfully opened standby log 9: 'D:\ORACLE\ORADATA\AVDB\STDREDO09.LOG'Sun Jul 25 09:36:14 2010Clearing online redo logfile 3 completeClearing online redo logfile 4 D:\ORACLE\ORADATA\AVDB\REDO4.LOGClearing online log 4 of thread 1 sequence number 59Sun Jul 25 09:36:15 2010RFS[2]: Successfully opened standby log 10: 'D:\ORACLE\ORADATA\AVDB\STDREDO10.LOG'RFS[2]: Detected missing archivals for Branch(resetlogs_id): 724771929RFS[2]: Last archived SCN: 2:-623590551 Last change SCN: 2:-623588413RFS[2]: New Archival REDO Branch(resetlogs_id): 725275791 Prior: 724771929RFS[2]: Archival Activation ID: 0x3e60fcc1 Current: 0x0RFS[2]: Effect of primary database OPEN RESETLOGSRFS[2]: Managed Standby Recovery process is activeNew incarnation branch detected in ArchiveLog, filename D:\ORACLE\ORADATA\AVDB\STDREDO10.LOGInspection of file changed rdi from 6 to 7Setting recovery target incarnation to 7Sun Jul 25 09:36:16 2010Setting recovery target incarnation to 7Sun Jul 25 09:36:16 2010Clearing online redo logfile 4 completeClearing online redo logfile 5 D:\ORACLE\ORADATA\AVDB\REDO5.LOGClearing online log 5 of thread 1 sequence number 60Clearing online redo logfile 5 completeClearing online redo logfile 6 D:\ORACLE\ORADATA\AVDB\REDO6.LOGClearing online log 6 of thread 1 sequence number 61Clearing online redo logfile 6 complete

45/49


Media Recovery Waiting for thread 1 sequence 61MRP0: Incarnation has changed! Retry recovery...Sun Jul 25 09:36:25 2010Errors in file e:\oracle\admin\AVDB\bdump\AVDB_mrp0_2332.trc:ORA-19906: recovery target incarnation changed during recovery

Managed Standby Recovery not using Real Time ApplyRecovery interrupted!Sun Jul 25 09:36:26 2010Errors in file e:\oracle\admin\AVDB\bdump\AVDB_mrp0_2332.trc:ORA-19906: recovery target incarnation changed during recovery

Sun Jul 25 09:36:46 2010Managed Standby Recovery starting Real Time Apply parallel recovery started with 7 processesMedia Recovery start incarnation depth : 1, target inc# : 7, irscn : 12261313475Sun Jul 25 09:36:53 2010Waiting for all non-current ORLs to be archived...Media Recovery Waiting for thread 1 sequence 61 branch(resetlogs_id) 724771929Fetching gap sequence in thread 1 branch(resetlogs_id) 724771929, gap seq 61-61Sun Jul 25 09:36:54 2010Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[3]: Assigned to RFS process 3124RFS[3]: Identified database type as 'physical standby'RFS[3]: Archived Log: 'L:\ORACLE\ORADATA\AVDB\ARCHIVE\AVDB_001_61_0724771929.ARC'Sun Jul 25 09:37:02 2010Redo Shipping Client Connected as PUBLIC-- Connected User is ValidRFS[4]: Assigned to RFS process 3012RFS[4]: Identified database type as 'physical standby'

Cleanup of Archived Logs on the Standby Location

On this configuration all backups are configured and running on the primary database, so that we needed a job to cleanup the archived logs generated on the standby database, for this purpose we setup the following batch script:

Script rman_clean_arch.bat # This script is a batch file that invoke an rman script that # cleanup archived logs older than 3 hours# rman_clean_arch.batset echo on

46/49


set oracle_sid=AVDBSTDset oracle_home=C:\oracle\product\10.2.0\db_1cd L:\ORACLE\DG-Scriptsrman target / log=rman_clean_arch.log @rman_clean_arch.rmn# eof script rman_clean_arch.bat

Script rman_clean_arch.rmn# This script remove all archived logs that are older than 2 hours run { allocate channel d1 type disk;delete noprompt force archivelog until time 'sysdate -120/1440';release channel d1;}Exit# eof rman_clean_arch.rmn

Log File rman_clean_arch.log

This is the log file produced by the rman_clean_arch.rmn script:

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Aug 1 16:20:00 2010


connected to target database: AVDB (DBID=803764395, not open)

RMAN> run { allocate channel d1 type disk;2> delete noprompt force archivelog until time 'sysdate -120/1440';3> release channel d1;4> }5> exitusing target database control file instead of recovery catalogallocated channel: d1channel d1: sid=1091 devtype=DISK

deleted archive logarchive log filename=L:\ORACLE\ORADATA\AVDB\ARCHIVE\AVDB_001_64015_0702125008.ARC recid=889 stamp=725897700deleted archive logarchive log filename=L:\ORACLE\ORADATA\AVDB\ARCHIVE\AVDB_001_64016_0702125008.ARC recid=890 stamp=725897709deleted archive log

47/49


archive log filename=L:\ORACLE\ORADATA\AVDB\ARCHIVE\AVDB_001_64017_0702125008.ARC recid=891 stamp=725897725deleted archive logarchive log filename=L:\ORACLE\ORADATA\AVDB\ARCHIVE\AVDB_001_64018_0702125008.ARC recid=892 stamp=725897737deleted archive logarchive log filename=L:\ORACLE\ORADATA\AVDB\ARCHIVE\AVDB_001_64019_0702125008.ARC recid=893 stamp=725898425Deleted 5 objects

released channel: d1

Recovery Manager complete.

Tnsnames Configuration for the Applications

The tnsnames for the applications is configured to use a generic service that is started using an “on database open” trigger

AVSDR = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER01)(PORT = 2115)) (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER02)(PORT = 2115)) ) (CONNECT_DATA= (SERVICE_NAME=AVSDR) ) )

The service is created using the following code

exec DBMS_SERVICE.CREATE_SERVICE ( -service_name => 'AVSDR', -network_name => 'AVSDR', failover_method => 'BASIC', -failover_type => 'SELECT', -failover_retries => 180, -failover_delay => 1);

The trigger is created using the following code:

CREATE OR REPLACE TRIGGER manage_dgservice

48/49


after startup on databaseDECLARErole VARCHAR(30);BEGINSELECT DATABASE_ROLE INTO role FROM V$DATABASE;IF role = 'PRIMARY' THENDBMS_SERVICE.START_SERVICE('AVSDR');END IF;END;/

49/49