Thursday, June 25, 2009

LOG APPLY SERVICES (LAS)

LOG APPLY SERVICE:

i. Applying redo immediately

ii. Time delay for redo apply

Applying redo data to Physical Standby Database

  1. start redo apply
  2. stop redo apply
  3. monitor redo apply

Applying redo data to Logical Standby Database

iii. start sql apply

iv. stop sql apply

v. monitor sql apply

LOG APPLY SERVICES (LAS): Process is Automatic

1. Redo Apply (Physical Standby Database only)

· Uses Media Recovery to keep Primary Database & Standby Database synchronized.

· Kept in mounted state & can be open for reporting.

2. SQL Apply (Logical Standby Database only)

· Reconstructs the SQL statements form redo data received from Primary Database & applies it to Logical Standby Database.

· Can be opened in R/W mode.

Redo Transport Service Process on the Standby Database receives the redo data and applies it to standby redolog files or archived redolog files.

RFS - Redo file server process

MRP- Managed recovery process (performs recovery i.e... starts apply redo data)

LSP - Logical Standby Process.

FIG 6-1: Oracle Dataguard (B14239-04)

1. Applying Redo Data Immediately: (Real-Time Apply)

In this process the redo data is applied immediately as it is received without waiting for the current standby redolog file to be archived

Enabling real-Time Apply for Physical Standby Database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE

Enabling real-Time Apply for Logical Standby Database

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE

2. Specifying a time delay for applying Redologs:

Paramter used: log_archive_dest_n

Attribute: delay

System: Delay in minutes

Default Value: 30 mins

Delay is used to protect the corrupted data getting applied to Standby Database.

Delay time starts after redo is received and completely archived

If real time is applied & delay is specified then delay is ignored

Cancel delay using nodelay

Ex:

Physical Standby Database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

Logical Standby Database

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NODELAY;

Alternate option for delaying:

Using flash back on standby database.

Applying Redo data to Physical Standby Database:

· By default redo is always applied from archived redo logs of standby db.

· In case of real-time apply; redo applied directly from standby redo log files before they are archived.

· Redo data cannot be applied if Physical Standby Database is open in read only mode. There fore start the Physical Standby Database and keep it in mounted state to apply the redo.

Apply redo is foreground process ( control is not returned)

Sql> Alter database recover managed standby database;

Applying redo as background process(control is returned)

Sql> Alter database recover managed standby database disconnect;

Using Real-time Apply:

Sql> Alter database recover managed standby database using current logfile;

Cancel Real-time Apply:

Sql> Alter database recover managed standby database cancel;

Monitoring:

Use OEM for monitoring log apply services.

Applying redo data to Logical Standby Database:

SQL Apply converts the data from archived redo log files or standby redolog ifles on Logical Standby Database into sql statements and then these sql statement are applied to Logical Standby Database.

Logical Standby Database always remain open as sql statements has to be executed.

Used for reporting, summations and quering purpose.

Starting sql apply

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

Realtime:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Stopping:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Note: This command is delayed as sql apply will wait to apply all the commited transactions.

For stopping immediately used

SQL> ALTER DATABASE ABORT LOGICAL STANDBY APPLY;

Monitoring:

Use OEM for monitoring log apply servies.

REDO TRANSPORT SERVICES (RTS)

Redo Transport Service:

§ Automates the transfer of redo to one or more destinations

§ Resolve gaps in Redo Transport in case of network failures

FIG 5-1: Oracle Dataguard (B14239-04)

Destinations types for Redo Transport Service:

§ Oracle Data Guard Standby Database

§ Archived redo log repository

§ Oracle Streams real-time downstream capture database

§ Oracle Change Data Capture staging database

LOG_ARCHIVE_DEST_n parameter:

§ Number of destinations maximum (10)

§ Number of Standby Database configured max (10)

Attributes:

§ Location= specifies the local destinations

§ Service= specifies remote destinations

LOG_ARCHIVE_DEST_n is used along with LOG_ARCHIVE_DEST_STATE_n parameter.

Attributes: of LOG_ARCHIVE_DEST_STATE_n parameter

ENABLE:

Redo transport services can transmit redo data to this destination. This is the default.

DEFER:

This is a valid but unused destination (Redo transport services will not transmit redo data to this destination.)

ALTERNATE:

This destination is not enabled, but it will become enabled if communication to its associated destination fails.

RESET:

Functions the same as DEFER

Example 5–1 Specifying a Local Archiving Destination

LOG_ARCHIVE_DEST_1=’LOCATION=/arch1/chicago/’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

Example 5–2 Specifying a Remote Archiving Destination

LOG_ARCHIVE_DEST_1=’LOCATION=/arch1/chicago/’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_2=’SERVICE=boston

LOG_ARCHIVE_DEST_STATE_2=ENABLE

We can change the destination attributes

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=boston

VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE)’;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

(This commands defers the Redo Transport Service)

§ The modifications take effect after the next log switch on the primary database.

§ Parameter for configuring Flash Recovery Area is DB_RECOVERY_FILE_DEST = ‘/……’

§ If no destinations for local archiving are specified then LOG_ARCHIVE_DEST_10 is implicitly mapped to DB_RECOVERY_FILE_DEST location by Oracle Data Guard.

§ A Primary Database cannot write the redo data to the Flash Recovery Area of Logical Standby Database

Note: Flash Recovery Area is the directory to stores the files related to recovery.

§ To configure Flash Recovery Area to any other destinations other then LOG_ARCHIVE_DEST_10 use

§ LOG_ARCHIVE_DEST_9=’LOATION=USE_ DB_RECOVERY_FILE_DEST ARCH MANDATORY REOPEN 5

§ Specifying Flash Recovery Area as Physical Standby Database

STANDBY_ARCHIVE_DEST = ‘LOCATION=USE_ DB_RECOVERY_FILE_DEST’

§ Sharing a Flash Recovery Area between Physical Standby Database and Primary Database DB_UNIQUE_NAME should be specified to each database and it should have a unique name.

Example 5–3 Primary Database Initialization Parameters for a Shared Recovery Area

DB_NAME=PAYROLL

LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST’

DB_RECOVERY_FILE_DEST=’/arch/oradata’

DB_RECOVERY_FILE_DEST_SIZE=20G

Example 5–4 Standby Database Initialization Parameters for a Shared Recovery Area

DB_NAME=PAYROLL

DB_UNIQUE_NAME=boston

LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST’

STANDBY_ARCHIVE_DEST=’LOCATION=USE_DB_RECOVERY_FILE_DEST’

DB_RECOVERY_FILE_DEST=’/arch/oradata’

DB_RECOVERY_FILE_DEST_SIZE=5G

Sending Redo:

Redo can be transmitted by archiver process (Arcn) and log writer process (lgwr). But both cannot be used for the same destinations i.e... arcn can send redo to one destinations and lgwr to other.

Using ARCn to send redo

§ Default method & 4 processes are used by default

§ Supports only Maximum Performance level of data protection

§ Specify LOCATION attribute for local archiving and SERVICE attribute for remote archiving.

EX:

LOG_ARCHIVE_DEST_1=’LOCATION=/arch1/chicago/’

LOG_ARCHIVE_DEST_2=’SERVICE=boston

Another parameter

LOG_ARCHIVE_MAX_PROCESSES (Dynamic parameter; Maximum is 30 process)

Archival Processing:

FIG 5-3: Oracle Dataguard (B14239-04)

Note: use v$archive_log to verify the redo data is received on Standby Database

Minimum 2 Arch Process are required default is 4 & maximum is 30

RFS: On the remote destination, the remote file server process (RFS) will, in turn, write the redo data to an archived redo log file from a standby redo log file. Log apply services use Redo Apply (MRP process1) or SQL Apply (LSP process2) to apply the redo to the standby database.

MRP: The managed recovery process applies archived redo log files to the physical standby database, and automatically determines the optimal number of parallel recovery processes at the time it starts. The number of parallel recovery slaves spawned is based on the number of CPUs available on the standby server.

LSP: The logical standby process uses parallel execution (Pnnn) processes to apply archived redo log files to the logical standby database, using SQL interfaces.

Using LGRW to Send Redo

- LGWR SYNC

- LGWR ASYNC

LGWR SYNC archival processing:

Parameter: LOG_ARCHIVE_DEST_n

Attributes: LGWR, SYNC,SERVICE

Example 5–5 Initialization Parameters for LGWR Synchronous Archival

LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago'

LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR SYNC NET_TIMEOUT=30'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

SYNC: Network I/O is synchronous (default)

Waits until each write operation is completed

Note: if LGWR process does not work for some reason then redo transport will automatically shift to ARCn process.

NET_TIMEOUT: waits for specified seconds over the network & give error if write operation does not complete

LGWR ASYNC archival processing:

Ex: Same as above without SYNC & NET_TIMEOUT attribute

Use ASYNC instead of SYNC

NET_TIMEOUT is not necessary in ora10.2

Diagram showing SYNC & ASYNC LGWR archival process:


FIG 5-4: Oracle Dataguard (B14239-04)

FIG 5-5: Oracle Dataguard (B14239-04)

Note: LOG_ARCHIVE_DEST & LOG_ARCHIVE_DUPLEX_DEST should not be used for configuring Flash Recovery Area.

Providing security while transmitting redo:

Sql> Orapwd file=orapw password=xyz entries=10

Note: Make sys user passwore identical for all db’s in Oracle Data Guard. Also set remote_login_password_file=exclusive/shared.

VALID_FOR attribute of LOG_ARCHIVE_DEST_n parameter

VALID_FOR=(redo_log_type,database_role)

redo_log_type: ONLINE_LOGFILE, STANDBY_LOGFILE, or ALL_LOGFILES

database_role: PRIMARY_ROLE, STANDBY_ROLE, or ALL_ROLES.

VALID_FOR attribute is required for role transtition

- configures destination attrivutes for both Primary Database and Standby Database in one SPFILE

- If VaLID_FOR is not used then we need to user two spfiles each time we do the role transitions

- This attribute makes the switch-over and Fail-over easy.

Ex

LOG_ARCHIVE_DEST_1='LOCATION=/ARCH1/CHICAGO/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'

DB_UNIQUE_NAME: Specified unique database name in Oracle Data Guard conf.

Used along with LOG_ARCHIVE_CONFIG.

Ex:

DB_NAME=chicago

DB_UNIQUE_NAME=chicago

LOG_ARCHIVE_CONFIG='DG_CONFIG= (chicago, boston)'

LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/ VALID_FOR= (ALL_LOGFILES, ALL_ROLES)

LOG_ARCHIVE_DEST_2= 'SERVICE=boston LGWR ASYNCVALID_FOR= (ONLINE_LOGFILES, PRIMARY_ROLE)

DB_UNIQUE_NAME=boston'

The LOG_ARCHIVE_CONFIG parameter also has SEND, NOSEND, RECEIVE, and NORECEIVE attributes:

- SEND enables a database to send redo data to remote destinations

- RECEIVE enables the standby database to receive redo from another database

To disable these settings, use the NOSEND and NORECEIVE keywords

Ex: LOG_ARCHIVE_CONFIG='NORECEIVE, DG_CONFIG= (chicago,boston)'

Use of these parameters can effect the role transition. Therefore trys to remove them before doing any role transitions

Handling Errors while transmitting redo:

Options when archiving fails

Retry the archival operations (control the number of retry operations)

Use an Alternate destinations

Ex: LOG_ARCHIVE_DEST_1='LOCATION=/arc_dest REOPEN=60 MAX_FAILURE=3's

Other parameters used:

REOPEN: default value is 300 seconds, 0 turns off this option

MAXIMUM _FAILURE: Maximum number of failures

ALTERNATE: Alternate Destinations

Note: Alternate take precedence over mandatory attribute; i.e.. even if the archiving destinations is mandatory and if it fails; the archiving automatically moves to alternate destinations.

DATA PROTECTION MODES:

MAXIMUX PROTECTION

MAXIMUM AVAILIBLITY

MAXIMUX PERFOMANCE (Default)

- No Data loss if Primary Database fails

-Redo data needed for recovery has to be written both to an online redo log files and standby redo log files before commit

- Atleast one Standby Database should be available

- If any fault happens Primary Database will shutdown

- Configure LGWR, SYNC & AFFIRM attributes of LOG_ARCHIVE_DEST_n parameter on Standby Database

-Provides highest level of data protection without compromising availability of Primary Database

-Primary Database doesn’t shutdown and continues to work in maximum performance mode until the fault is corrected

-all gaps in redolog files are resolved and then it goes back to maximum availability mode.

-Alteast on Standby Database should be available

- Configure LGWR, SYNC & AFFIRM attributes of LOG_ARCHIVE_DEST_n parameter on Standby Database

-Provides highest level of data protection

-does not effect the performance

- As soon as the redo data is writted to the online redo log file the transacation is committed.

-redo is also written to alteast one Standby Database asynchronously

- Use network links with sufficient bandwith to get maximum availablitiy with minimal input on performance on pdb

- Set LGWR, SYNC & AFFIRM attributes of LOG_ARCHIVE_DEST_n parameter on alteast Standby Database

Setting the Data Protection Mode of a Data Guard Configuration

Atleast one db should meet the following minimum requirements:


MAXIMUM PROTECTION

MAXIMUM AVAILIBLITY

MAXIMUM PERFOMANCE

Redo Archival Process

LGWR

LGWR

LGWR OR ARCH

Network transmission mode

SYNC

SYNC

SYNC or ASYNC when using

LGWR process. SYNC if using

ARCH process

Disk write option

AFFIRM

AFFIRM

AFFIRM OR NO AFFIRM

Standby redo log required?

YES

YES

NO BUT RECOMMENDED

Note: oracle recommends that on Oracle Data Guard configurations that is running on maximum protection mode contains atleast two Standby Database meeting the above requirements so that the Primary Database continue processing without shutting down if one of the Standby Database cannot receive redo data from Primary Database.

Managing log files:

1. Specify alternate directory for archived redologs.

- Redo received from Primary Database is identified by location attribute of the parameter LOG_ARCHIVE_DEST_n.

- Alternate directory can be specified by using parameter STANDBY_ARCHIVE_DEST.

- If both parameters are specified then STANDBY_ARCHIVE_DEST overrides LOG_ARCHIVE_DEST_n parameter.

- query v$arvhive_dest to check the value of STANDBY_ARCHIVE_DEST parameter

SQL> SELECT DEST_NAME, DESTINATION FROM V$ARCHIVE_DEST WHERE DEST_NAME='STANDBY_ARCHIVE_DEST';

- Filesnames are generated in the format specified LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

Note: Redo Transport Service stores the fully qualified domain name of these files in Standby Database control file and redo apply uses this information to perform recovery.

- view v$archived_log

- checking archived redo’s log files that are on the standby system

SQL> SELECT NAME FROM V$ARCHIVED_LOG;

2. Reusing Online Redo Log Files

1. Specify alternate directory for archived redolog files

- redo received from Primary Database is identified by location attribute of the parameter LOG_ARCHIVE_DEST_n

- Alternate directory can be specified by using parameter STANDBY_ARCHIVE_DEST

- If both parameters are specified than STANDBY_ARCHIVE_DEST overrides LOG_ARCHIVE_DEST_n parameter

- Query v$arhive_dest to check the value of STANDBY_ARCHIVE_DEST parameter

SQL> SELECT DEST_NAME, DESTINATION FROM V$ARCHIVE_DEST WHERE DEST_NAME= 'STANDBY_ARCHIVE_DEST';

- Files name are generated in the format specified by LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

Note: Redo Transport Service stores fully qualified domain name of these files in Standby Database control file & redo apply uses this information to perform recovery.

2. Reusing Online Redolog file:

For reusing the online redolog files we have to set optional or mandatory option with LOG_ARCHIVE_DEST_n parameter

Ex: LOG_ARCHIVE_DEST_3=’LOCATION=/arch_dest MANDATORY’

Note: By Default remote destinations are set to optional.

By Default one local destination is mandatory.

§ If mandatory is specified the archive log files are not overwritten until the failed archive log is applied

§ If optional is specified; even if the redo is not applied the files are over written.

3. Managing Standy Redo log files:

Check the RFS process trace file or database alert log file to determine we have adequate standby redo log files or not.

i.e... If these files indicate RFS process has to wait frequently for a group as archiving is not getting completed than add more log file groups to standby redo log.

Note: when ever an ORL file group is added to Primary Database than we must add the corresponding standby redo log file group to the Standby Database.

If the no. of standby redo log file groups are inadequate then Primary Database will shutdown if it is in maximum protection mode or switch to maximum performance mode if it is in maximum availability mode

Ex: Adding a member to the standby redo log group

Sql> Alter database add standby logfile member ‘/disk1/oracle/dbs/log2b.rdo’ to group 2;

4. Planning for growth & reuse of control files:

§ The maximum control file size is 20,000 database blocks

§ If 8k is the block size (8124) then the maximum control file size will be 156 MB.

§ As long as the archived redo logs are generated or RMAN backups are taken records are added to the control file. If control file reaches its maximum size then these records are reused.

§ Parameter used to specify the time for keeping control file records is control_file_record_keep_time value ranges from 0-365 days (default is 7 days)

§ Note: Keep the control_file_record_keep_time value atleast upto last 2 full backup’s period.

§ In case if redo is planned to apply with delay then set this value to more no. of days.

5. Sharing a log file destinations among multiple Standby Databases:

Ex:

LOG_ARCHIVE_DEST_1=‘LOCATION=disk1 MANDATORY’

LOG_ARCHIVE_DEST_2 =‘SERCIVE=standby1 optional’

LOG_ARCHIVE_DEST_3 =‘SERCIVE=standby2 optional dependency LOG_ARCHIVE_DEST_2’

In this case DEPENDENCY attribute is set to second standby db which takes the redo data from LOG_ARCHIVE_DEST_2.

This kind of setup can be used if

§ Primary Database & Standby Database resides on the same system.

§ Physical Standby Database & Logical Standby Database resides on the same system.

§ When clustered file system is used.

§ When network file system is used

MANAGING ARCHIVE GAPS:

§ Oracle Data Guard resolves the gaps automatically

§ Gaps can happen due to network failure or archiving problem on Primary Database.

§ Primary Database polls Standby Database every minute to detect the gaps [polling mechanism]

§ In case Primary Database is not available then we have to resolve the gaps manually by applying redo from one of the Standby Database.

§ No extra configurations are required to resolve the gaps automatically.

1. Using FAL [fetch archive logs mechanism] to resolve gaps.

Set the parameters FAL_SERVER = net_service_name

FAL_SERVER=standby2_db, standby3_db

FAL_CLIENT=stadnby1_db

2. Manually resolving archive gaps

We have to resolve the gaps manually if Primary Database is not available and if we are using Logical Standby Database (case 1); Also application for some other cases.

Resolving gaps on a Physical Standby Database:

  1. Query the gap on Physical Standby Database:

SQL> SELECT * FROM V$ARCHIVE_GAP;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#

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

1 7 10

  1. Find the missing logs on Primary Database and copy them to the Physical Standby Database.

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 7 AND 10;

NAME

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

/primary/thread1_dest/arcr_1_7.arc

/primary/thread1_dest/arcr_1_8.arc

/primary/thread1_dest/arcr_1_9.arc

  1. Once these log files are copied on Physical Standby Database then we have to register them with Physical Standby Database

SQL> ALTER DATABASE REGISTER LOGFILE

'/physical_standby1/thread1_dest/arcr_1_7.arc';

SQL> ALTER DATABASE REGISTER LOGFILE

'/physical_standby1/thread1_dest/arcr_1_8.arc';

  1. Restart Redo Apply.

Resolving gaps on a Logical Standby Database:

Same procedure as Physical Standby Database but the view used is dba_logstdby_log instead of v$archive_gap

Steps:

a.

SQL> COLUMN FILE_NAME FORMAT a55

SQL> SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L

WHERE NEXT_CHANGE# NOT IN

(SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#) ORDER BY THREAD#,SEQUENCE#;

THREAD# SEQUENCE# FILE_NAME

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

1 6 /disk1/oracle/dbs/log-1292880008_6.arc

1 10 /disk1/oracle/dbs/log-1292880008_10.arc

Note: If there is a gap then only one file is hsown for each thread. Otherwise it shows two files for each thread

In the above examples missing files are 7,8,9.

b. copy these file on Logical Standby Database location.

c. register these files with Logical Standby Database

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE 'file_name';

d. Restart SQL Apply

Verification:

1. Check the status of online redofile on Primary Database

SQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;

2. determine is the most recent archive file on Primary Database

SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

3. Use the following query on Primary Database to check which is the most recently transmitted archive log file to each destination

SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#

FROM V$ARCHIVE_DEST_STATUS

WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

4. Use the following query on Primary Database to find out the archive redolog files not received at each destination

SQL> SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM

(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1)

LOCAL WHERE LOCAL.SEQUENCE# NOT IN

(SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND

THREAD# = LOCAL.THREAD#);

5. Set log_archive_trace parameter in Primary Database & Standby Database to see the transmission of redo data

Monitoring the Performance of Redo Transport Services

View: v$system_event

Parameter: log_archive_dest_n

Attributes: ARCH

LGWR ( SYNC/ASYNC)

Waits Events to monitor:

  1. ARCn Wait Events
  2. LGWR SYNC wait events
  3. LGWR ASYNC Wait Events

Note: Use OEM to Monitor in GUI for Oracle Data Guard

ORACLE 10g DATAGUARD


 Agenda
Ø  Physical vs. Logical Standby
Ø  Standby Protection Modes
Ø  Log Transport Attributes
Ø  Standby Redo Logs
Ø  Setup Physical Standby step-by-step
Ø  Managing and Monitoring standby
Ø  Role Transition: Switchover/Failover

Purpose
To provide an efficient disaster recovery solution by maintaining transactionally consistent copies of the production database at a remote site.

Physical Standby
Ø  Kept in sync with the primary by using media recovery to apply redo generated on primary
Ø  Used for BCP
Ø  Can be opened in read-only mode but redo won’t be applied for that time
Ø   
Logical Standby
Ø  Kept in sync with the primary by transforming redo data received from primary into logical SQL statements and then executing those SQLs against the standby database.
Ø  Used to offload reporting from the primary database
Ø  Can be opened in read-only mode while the changes are being applied

Protection Modes
Decide on Standby Protection Mode before setting it up:
  1. MAXIMUM PROTECTION
Pre-requisites
Ø  Using LGWR SYNC AFFIRM, transport of redo logs to be done in a synchronous fashion.
Ø  Standby redo logs (SRLs) need to be created on standby site.
Ø  At least one standby must be available for the primary database to function.
Ø  Need high speed network.
Pros
Ø  Zero data loss
Cons
Ø  Primary shuts down if in case of network issues unable to commit on standby at the same time.

2. MAXIMUM AVAILABILITY
Pre-requisites
Ø  Using LGWR SYNC AFFIRM, transport of redo logs to be done in a synchronous fashion.
Ø  Standby redo logs (SRLs) need to be created on standby site.
Features
Ø  If network issues, switches to maximum performance and when the fault corrects switches back to maximum availability.
Ø  Data loss only if primary loses it’s redo logs.
SQL> alter database set standby to maximize availability;

3. MAXIMUM PERFORMANCE
Ø  Asynchronous redo shipping using ARC or LGWR ASYNC.
Ø  No impact on primary’s performance even if network issues.
Ø  No need to create SRLs unless real-time apply is needed on the standby site.

Log Transport Services
Log Transport Service Attributes are defined on primary in log_archive_dest_2
ARC(default)
Ø  ARC will first archive the online redo log to local destination on primary. Then second ARC process spawns and writes the archive to remote standby.
Ø  By default, log_archive_local_first=true in init.ora on primary. DO NOT CHANGE IT.
LGWR
Ø  In contrast to ARC, which transmits redo to standby only at log switch time, LGWR attribute instructs LGWR process to transmit redo to standby at the same time while the redo is writing to the online redo logs.
Ø  Transmission of redo can be done synchronously (SYNC) or asynchronously (ASYNC)
AFFIRM
All Disk I/O at standby to be performed synchronously
SYNC
Ø  By default, LGWR archives synchronously. Once I/O is initiated, archiving must wait for I/O to complete. This means transaction is not committed on primary database until redo data necessary to recover that transaction is received by the destination.
ASYNC
Ø  LGWR does not wait for the I/O to complete. LGWR network server process(LNS) performs actual network I/O.
Ø  User-configurable buffer used to accept outbound redo data from LGWR. ASYNC=20480 indicates a 10MB buffer. Maximum can be upto 50MB.
MAX_FAILURE
Ø  Defines number of times to retry a destination that has been closed due to a failure
NET_TIMEOUT
Ø  Used with LGWR ASYNC.
Ø  Defines how many seconds to wait before giving up on a network connection.
REOPEN
Ø  Determines how long the primary waits before retrying a connection

DATAGUARD SETUP

Creating a physical standby
                Both primary and standby systems must be identical in configuration with regards to operating system, platform architecture and database version. H/W config may differ.

1. Enable archiving on primary
log_archive_dest_1=‘LOCATION=
log_archive_format=%t_%s_%r.dbf
log_archive_start=true( As of 10g  release, its deprecated )
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

2. Enable force logging on primary
SQL> alter database force logging;
This is required as any nologging operations would not be logged within redo stream.
In this mode, nologging operations are permitted to run, but changes are placed into redo.

3. Creating password file on primary and standby
Create a password file( if not created yet )
orapwd file=orapw password=
remote_login_passwordfile=exclusive
SYS password must be identical on both primary and standby for log transport services to function.

4. Creating standby controlfile on primary
SQL> alter database create standby controlfile as ‘<../path/standby.ctl>’;

5. Take hotbackup of primary  and copy datafiles,archivelogs and standby controlfile to standby
(NOTE: do not copy redo logs since standyb will create it’s own)

6. Create tnsnames.ora aliases for primary and standby on both primary and standby

7. Prepare init.ora on primary
db_name=‘TEST’
db_unique_name=‘PRI’
service_names=‘PRI_SERVICE’
log_archive_config=‘DG_CONFIG=(PRI,STDBY)’
log_archive_dest_1=‘LOCATION=
log_archive_dest_state_1=enable
log_archive_dest_2=‘SERVICE= ARCH ASYNC reopen=300 max_failure=0
net_timeout=60 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDBY’
log_archive_dest_state_2=enable
log_archive_min_succeed_dest=1
log_archive_max_processes=2
standby_file_management=auto
fal_server=
fal_client=

8. Prepare init.ora on standby
db_name=‘TEST’
db_unique_name=‘STDBY’
service_names=‘STDBY_SERVICE’
log_archive_config=‘DG_CONFIG=(PRI,STDBY)’
log_archive_dest_1=‘LOCATION=
log_archive_dest_state_1=enable
log_archive_dest_2=‘SERVICE= ARCH ASYNC reopen=300 max_failure=0
net_timeout=60 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRI’
log_archive_dest_state_2=enable
log_archive_min_succeed_dest=1
log_archive_max_processes=2
standby_file_management=auto
fal_server=
fal_client=

9. Mount the standby and start applying the changes
SQL> startup mount;
SQL> alter database recover managed standby database disconnect;
To put standby in read-write mode
SQL> alter database activate standby database;
To stop the apply:
SQL> alter database recover managed standby database cancel immediate;
To start real time apply:
SQL> alter database recover managed standby database using current logfile disconnect;
This needs creation on SRLs (standby redo logs)
To put standby in read-only mode
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> alter database open read only;
Note: Once the standby is made primary (read-write), verify redo logs and tempfiles.

Monitoring
Ø  On primary check if archive logs are getting copied to standby:
SQL> select status from v$archive_dest where dest_id=2;
Ø  On Standby monitor MRP process:
SQL> select status from v$managed_standby where process like ‘%MRP%’;
Status must be “APPLYING_LOG” or “WAIT_FOR_LOG”
ps –ef|grep mrp
Ø  On standby detect archive gap
SQL> select * from v$archive_gap;
This will return records if MRP status is “WAIT_FOR_GAP”
Ø  With 10gR2, v$dataguard_stats is introduced to monitor redo transport/apply progress;
SQL> select value from v$dataguard_stats where name=‘apply lag’;
SQL> select value from v$dataguard_stats where name=‘transport lag’;
Ø  Note: In case Dataguard is RAC, MRP process would be applying on one of the node. If this node   crashes, MRP must be started on that surviving node to which VIP of the crashed node has failed over.

STANDBY REDO LOGS
Guidelines when creating standby redo logs:
Ø  Number of standby redo logs should be the same number as online redo logs plus one.
Ø  Standby redo logs should be exactly the same size as the online redo logs.
Ø  SRLs should be created on both primary and standby to facilitate seamless role changes.
Ø  In a RAC environment, all SRLs should be on a shared disk and may be thread specific.
Ø  Used with maximum protection modes and when real-time apply is used.
How SRLs work?
  1. LGWR process on primary initiates a connection with standby.
  2. Standby listener responds by spawning a process called RFS(remote file server)
  3. RFS process creates n/w conn with processes on primary and waits for data to arrive.
  4. Once data comes, RFS places it into standby redo logs.
  5. When log switch occurs on primary, standby redo logs are switched and RFS will go to next available standby redo log.

SWITCHOVER
Ø  Switchover allows a primary and standby to reverse roles without any data loss.
Ø  No need to re-create the old primary. Performed for planned maintenance.
Steps:
1. Verify if primary can be switched over to standby
SQL> select switchover_status from v$database;
If value returns “TO_STANDBY”, its alright to switch the primary to standby role.
2. Convert primary to standby
SQL> alter database commit to switchover to physical standby;
If value is “SESSIONS ACTIVE” from step 1, then
SQL> alter database commit to switchover to physical standby with session shutdown;
3. Shutdown the restart the old primary as standby
SQL> shutdown immediate;
SQL> startup mount;
At this point, we now have both databases as standby.
4. On target standby database, verify switchover status. If value is “TO_PRIMARY” then
SQL> alter database commit to switchover to primary;
If value is “SESSIONS ACTIVE”, then append “WITH SESSION SHUTDOWN” to above command.
5. Shutdown and restart the new primary database
SQL> shutdown immediate;startup;


FAILOVER
Ø  Failover implies data loss and can result in the need to re-create old primary.
Steps:
1. Identify and resolve any gaps that may exist on standby.
SQL> select * from v$archive_gap;
Copy missing archives from primary to standby and register them to standby controlfile.
SQL> alter database register physical logfile ‘’;
2. If standby redo logs are configured and active,
SQL> alter database recover managed standby database finish;
If NO SRLs or they are not active,
SQL> alter database recover managed standby database skip standby logfile;
3. Convert standby to primary;
SQL> alter database commit to switchover to primary;
4. Restart new primary
SQL> shutdown immediate;startup;
Note: Once the standby is made primary (read-write), verify redo logs and tempfiles.


DATA GUARD BROKER

Agenda
Ø  DG Broker Concepts & Advantages
Ø  Setup using CLI(DGMGRL)
Ø  Useful broker commands
Ø  Switchover

Concepts
Ø  Any Data Guard configuration consists of one primary database and up to nine standby databases.
Ø  The Data Guard broker logically groups these primary and standby databases into a broker configuration so as to manage and monitor them together as an integrated unit.
Ø  Data Guard broker is a centralized framework to manage entire Data Guard configuration through a client connection to any database in the configuration.
Ø  Accessed either locally or remotely using either of the two clients: CLI interface(DGMGRL) or the Data Guard page from GUI( OEM Grid Control )
Ø  DGMGRL does not have the ability to create standby (GUI can do it). CLI is used mostly for configuration and management.
Ø  Easy switchover/failover with one command thereby minimizing overall downtime associated with the planned/unplanned outage
Ø  Integrated with CRS so that database role changes occur smoothly and seamlessly.
Ø  Instead of managing primary and standby databases with various SQL*Plus statements, broker provides a single unified configuration

DMON
Ø  Data Guard monitor process (DMON) runs for every database instance that is managed by the broker and maintains the broker configuration in a binary configuration file
Whenever a broker command is issued, DMON process:
Ø  Carries out the request on primary database
Ø  Coordinates with DMON process for each of the other databases
Ø  Updates its local configuration file
Ø  Communicates with DMON process for each of the other databases to update their copies of the configuration file

Prerequisites
Set up following parameters on primary and standby:
DG_BROKER_START=TRUE
DG_BROKER_CONFIG_FILE1=‘
DG_BROKER_CONFIG_FILE2=‘
LOCAL_LISTENER
GLOBAL_DBNAME in listener.ora as db_unique_name_DGMGRL.db_domain
Ø  To enable DGMGRL to restart instances, a service with a specific name must be statically registered with the local listener of each instance.
Ø  For RAC, ensure dg_broker_config_files are on shared storage and accessible to all instances.
Ø  START_OPTIONS for RAC database must be set to MOUNT in OCR using SRVCTL (For switchover/Failover operations for broker and CRS to coordinate while restarting instances and database role reversal)
Ø  SPFILE must be used

Switchover
Once SWITCHOVER is issued, the broker does the following:
  1. Verifies state of primary and standby database are enabled and in ONLINE state
  2. Shuts down all RAC instances except one
  3. Switches roles between the primary and standby databases.
  4. Updates the  broker configuration file to record the changes in roles
  5. Restarts the new standby database and any RAC instances that were shutdown prior to switchover.
  6. Restarts the new primary database, opens it in read-write mode, and starts log transport services transmitting redo data to the archived redo log files for standby database
  7. After switchover completes, the overall Data Guard protection mode remains at the same protection level as it was before the switchover.
  8. For DGMGRL to restart instances automatically, you must connect to the database as SYSDBA using the username and password specified in the remote password file before beginning the switchover.

Dataguard Broker setup
1. Set up init parameters on primary to enable broker
Note: For RAC, ensure dg_broker_config_files are on shared storage and accessible to all the instances.
Note: Broker config files are named as dr1<<db_unique_name>>.dat anddr2<<db_unique_name>>.dat
SQL> alter system set dg_broker_start=false sid='*';
System altered.

SQL> alter system set dg_broker_config_file1='/n01/dg_broker_config_files/dr1TESTPRI.dat' sid='*';
System altered.

SQL> alter system set dg_broker_config_file2='/n01/dg_broker_config_files/dr2TESTPRI.dat' sid='*';
System altered.

SQL> alter system set dg_broker_start=true  sid='*';
System altered.
2. Verify if DMON process has started on all the instances of primary. Example:
$ ps -ef|grep dmon|grep -v grep
oracle   16190     1  0 08:53 ?        00:00:00 ora_dmon_TESTPRIR1

$ ps -ef|grep dmon|grep -v grep
oracle   29723     1  0 08:53 ?        00:00:00 ora_dmon_TESTPRIR2
3. Set up init parameters on standby
SQL> alter system set dg_broker_start=false sid='*';
System altered.

SQL> alter system set dg_broker_config_file1='/export/crawlspace/dg_broker_config_files/dr1TESTDG.dat' sid='*';
System altered.

SQL> alter system set dg_broker_config_file2='/export/crawlspace/dg_broker_config_files/dr2TESTDG.dat' sid='*';
System altered.

SQL> alter system set dg_broker_start=true  sid='*';
System altered.
4. GLOBAL_DBNAME should be set to <<db_unique_name>>_DGMGRL.<<db_domain>>in listener.ora on all instances of both primary and standby.
This is important otherwise you'll have TNS-12154 error during switchover operation.
Example:
SID_LIST_LISTENER_TESTPRI =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /apps/oracle/product/10g/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = TESTPRIR1)
      (GLOBAL_DBNAME = TESTPRI_DGMGRL)
      (ORACLE_HOME = /apps/oracle/product/10g/db)
    )
  )
5. DGMGRL Configuration
5.1Connect
DGMGRL> CONNECT sys/sys
Connected.

5.2Create Configuration
DGMGRL> CREATE CONFIGURATION 'DG_TEST' AS PRIMARY DATABASE IS 'TESTPRI' CONNECT IDENTIFIER IS TESTPRI;
Configuration "DG_TEST" created with primary database "TESTPRI".

5.3Verify configuration
DGMGRL> SHOW CONFIGURATION;
Configuration
  Name:            DG_TEST
  Enabled:         NO
  Protection Mode: MaxPerformance
  Databases:
    TESTPRI - Primary database

Current status for "DG_TEST":
DISABLED

5.4Verify database; if RAC verify if all instances are validated
DGMGRL> show database 'TESTPRI';
Database
  Name:            TESTPRI
  Role:            PRIMARY
  Enabled:         NO
  Intended State:  ONLINE
  Instance(s):
    TESTPRIR1
    TESTPRIR2

Current status for "TESTPRI":
DISABLED

5.5Add standby database to the configuration
DGMGRL> ADD DATABASE 'TESTDG' AS CONNECT IDENTIFIER IS TESTDG MAINTAINED AS PHYSICAL;
Database "TESTDG" added.

5.6Enable the broker
DGMGRL> ENABLE CONFIGURATION;
Enabled.

5.7Verfying again
DGMGRL> SHOW CONFIGURATION;
Configuration
  Name:            DG_TEST
  Enabled:         YES
  Protection Mode: MaxPerformance
  Databases:
    TESTPRI - Primary database
    TESTDG  - Physical standby database

Current status for "DG_TEST":
SUCCESS

6. Troubleshooting
Let us see some sample issues and their fix
Issue
DGMGRL> CONNECT sys/sys
ORA-16525: the Data Guard broker is not yet available

Fix
Set dg_broker_start=true

Issue
After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes 
Warning: ORA-16608: one or more sites have warnings

Fix
To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.

Few Monitorable properties to troubleshoot
DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';

Issue
DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
          TESTPRIR2    WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting.
          TESTPRIR2    WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.

Issue
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE 
      TESTPRIR2     ArchiveLagTarget                    0                                         0 
      TESTPRIR2 LogArchiveMaxProcesses                    4                    2                    4 

Example
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses';
  LogArchiveMaxProcesses = '4'

Fix
DGMGRL> EDIT DATABASE 'TESTPRI' SET PROPERTY 'LogArchiveMaxProcesses'=2;

or

SQL> alter system set log_archive_max_processes=4 scope=spfile sid='*';
System altered.

DGMGRL> SHOW DATABASE 'TESTPRI' 'LogArchiveMaxProcesses';
  LogArchiveMaxProcesses = '4'

More commands
DGMGRL> SHOW DATABASE VERBOSE 'dbname';
This will show all property values in detail

DGMGRL> HELP;
List of all broker commands with usage help
Equivalent Broker Commands to 'ALTER SYSTEM'
SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'stby_dbname' set state='ONLINE';

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;

SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';

SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';

DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases