11g(R1/R2) Data guard Enhancements Suresh Gandhi

1
11g(R1/R2) Data guard EnhancementsSuresh Gandhi
2
Agenda

• What is DATA GUARD ?
• Types of Data Guard Databases
• 11g Release 1 Enhancements
• 11g Release 2 Enhancements
• Active Data guard
• Snapshot Standby Database
• More Features
• Rolling Upgrade and Physical Standby
• QA

3
Data Guard Overview

• Data Guard
• Standby Plus more
• High Availability Disaster Recovery Solution

4
Provides .
5
Data guard Databases
6
11G R1 Enhancements

• Release 1
• New
• Features

7
R2 - Enhancements

• Release 2
• New
• Features

8
R2- New Features

• Release 2
• New
• Features

9
Active and Snapshot Standby
Traditional Functionality until 10g
11g Snapshot Standby
11g Active DataGuard
10
Active Data Guard Benefits
11
Turn on Physical Standby to Active Standby
Stop redo apply
Database in standby mount mode
Open readonly
Open mode changed to read only
Start the redo apply again using log file (SRLs)
Status changed to read only apply
12
Turn on Physical Standby to Snapshot Standby
Once sync check, convert standby to snapshot
Open database
Read write mode
Need to close database
Switched back to physical standby
13
Additional Info Active / Snapshot

• WHAT OPERATION ALLOWED IN ACTIVE DATA GUARD ?
• SELECT statements
• Complex queries
• Calling of stored procedures
• Use of database links to redirect DML to Primary
  
• Use of stored procedures to call remote stored
  procedures via database links
• Use of SET ROLE
• Use of ALTER SESSION and ALTER SYSTEM
• No Datatypes restrictions like Logical Standby
• Additional TEMPFILES can be created for Sorts
• Flashback Query SELECT AS OF ltSCNgt
• Additional License

• SOME IMPORTANT POINTS FOR SNAPSHOT STANDBY
  DATABASE
• PHYSICAL STANDBY DATABASE SHOULD BE IN FLASHBACK
  MODE
• NO REAL TIME QUERY OR DATA
• NO FAST START FAILOVER IN THIS MODE
• NO SWITCHOVER/FAILOVER UNTIL CONVERTED TO
  PHYSICAL STANDBY
• Free of Licence
• POINTS
• Configure the Flash Recovery Area
• Set the Retention Target
• Enable Flashback Database

14
10g Equivalent Snapshot commands
Prepare the physical standby database to be
activated SQLgt ALTER SYSTEM SET
DB_RECOVERY_FILE_DEST_SIZE20G SQLgt ALTER SYSTEM
SET DB_RECOVERY_FILE_DEST'/arch/oradata' Prepare
the primary database to have the physical
standby be diverged. Archive the current log
file. SQLgt ALTER SYSTEM ARCHIVE LOG
CURRENT Cancel Redo Apply and create a
guaranteed restore point. SQLgt ALTER DATABASE
RECOVER MANAGED STANDBY DATABASE CANCEL SQLgt
CREATE RESTORE POINT restore_pt GUARANTEE
FLASHBACK DATABASE Activate the physical
standby database. SQLgt ALTER DATABASE ACTIVATE
STANDBY DATABASE SQLgt STARTUP MOUNT FORCE SQLgt
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE
PERFORMANCE SQLgt ALTER DATABASE OPEN Revert the
ac4vated database back to a physical standby
database. SQLgt STARTUP MOUNT FORCE SQLgt
FLASHBACK DATABASE TO RESTORE POINT
restore_pt SQLgt ALTER DATABASE CONVERT TO
PHYSICAL STANDBY SQLgt STARTUP MOUNT FORCE
15
DATGUARD Still More Features
16
More Features RMAN Active database Duplication
RMANgt run allocate channel prmy1 type
disk allocate channel prmy2 type disk allocate
channel prmy3 type disk allocate channel prmy4
type disk allocate auxiliary channel repstby
type disk duplicate target database for standby
from active database spfile parameter_value_conve
rt 'emrep','repstby' set db_unique_name'repstby'
set db_file_name_convert'C\','F\' set
log_file_name_convert'C\','F\'

• Steps
• Create a pfile with only db_namestby
• Create directory structure for datafile locations
• Create directory structure for dump locations
• Create a password file (must match with target)
• Listener Tnsnames entries for new standby
  database
• Startup nomount with pfile
• Run the rman command in other box
• Log File ?

17
More Features Redo Compression

• Requires Advanced Compression features
• Compress redo as it is transmitted data
• Automatically compress the data as it only
  resolves gaps
• Helpful and useful in bandwidth constrained
  locations
• Syntax-
• LOG_ARCHIVE_DEST_2Servicestby Async
  compressionENABLE

18
More Features Heterogenous database support

• Oracle 10g Dataguard support mixed environments
  32bit and 64 bit configuration
• Oracle 11G Dataguard supports mixed Windows/Linux
  in same configuration
• Only Physical Standby
• Same Endianess required on all platforms
• Metalink 413484.1

19
More Features Lost Write Detection

• Due to hardware or storage issues lost writes can
  happen while transmission and blocks may be
  stale.
• Very hard to diagnose in such of case of
  corruption
• Feature-
• Compares version of Blocks
• Between Standby blocks and redo stream
• Version discrepancy can be either standby or
  primary
• If Primary Database block corruption is detected
  ? Resolved
• Using standby failover and
• Restore data consistency
• New Initialization parameter
• ALTER System set db_lost_write_protect
  NONE/TYPICAL/FULL
• 5 impact but increase protection

20
More Features Automatic Block recovery

• 11g R2 automatically recovers corrupted blocks
  from standby copies of blocks
• Automatic Block Media Recovery also automatically
  repair corrupted blocks discovered in the
  physical standby databases
• Reduce recovery time in block corruption cases
  instead of restoring from tape or disks
• RMAN command RECOVER BLOCK can be used for manual
  recovery and its check for all suitable or
  consistent blocks across several copies of
  backups or
• Clause can be added RECOVER BLOCK EXCLUDE
  STANDBY then wont check in standby
• Automatic block recovery is applicable only in
  physical corruptions like
• When checksum is invalid
• Block contains all zeros
• Block header is fractured

21
More Features Redo Flush

• Yes, Redo can be flushed
• Can be done when the primary is in mount mode
• To avoid minimal loss of data and allowed
  failover to be performed without data loss
• Syntax-
• ALTER System FLUSH Redo to lttarget DB Namegt

22
More Features Sync with Primary

• New command
• ALTER SESSION SYNC WITH PRIMARY
• WAIT FOR STANDBY TO BE SYNCHRONIZED WITH PRIMARY
• FAILS IF REDO APPLY OR REDO TRANSPORT IS OFF
  REPORT TRANSPORT AND APPLY LAGS

23
More Features STANDBY_MAX_DATA_DELAY
24
More Features ROLE Based services

• Services can be created and start based on the
  database ROLE, viz a new addition sys_context
  (Database ROLE)
• In R2 services can be attached via srvctl
• Service can failover when using Cluster or Oracle
  restart
• 11gR1 to use triggers based on Database role
• Example Trigger

25
Rolling Upgrade Transient Logical Standby

• Physical standby must be converted to Logical
  Standby and uses Flashback as fall out plan
• Downtime for upgrade is only the SWITCHOVER TIME

26
Finally, Monitoring Views

• GAP_STATUS , additional column in varchive_dest
  GAP or NO GAP
• Vdataguard_stats for transport/apply lag
• VREDO_DEST_RESP_HISTOGRAM
• Useful when need to analyse network latencies by
  checking transport lag
• VSTANDBY_EVENT_HISTOGRAM

27
Thank you!Q A