11g Active Data Guard More than just DR

1
11g Active Data Guard More than just DR.

• Gavin Soorma
• Senior Oracle DBA,
• Bankwest

2
DR Sites of today

• Lots of spent on disaster recovery (DR)
  systems that sit idle waiting for a disaster to
  occur
• Investment on hardware, state of the art data
  centres, software licenses, maintenance costs
• DR sites very often have identical capacity as
  the production site idle and unused capacity in
  most cases
• Traditionally resource intensive activities like
  reporting and backups were not offloaded to the
  standby site
• Standby databases using Data Guard are
  synchronized replicas of production databases
  so why not use them?
• Oracle 11g Active Data Guard changes that
  enabling us to optimize Return On Investment from
  the DR or Standby sites

3
Life before 11g Active Data Guard

• Prior to Oracle 11g, read access to standby
  database required Redo Apply to be stopped.
• Queries on read only standby databases could
  potentially return stale results as changes
  occurring on the primary database are not
  applied.
• This backlog of unapplied redo data could
  potentially increase failover times
• Traditional reporting sites based on MV's
• MV's need administrative overhead for setup and
  maintenance.
• MV refresh times can be lengthy for complex
  queries - while complete refresh happens no rows
  returned for queries

4
Data Guard Overview
Data Guard Broker
Transactions
Oracle Net
MRP
LGWR
RFS
Standby Redo Logs
Online Redo Logs
Standby Database
Primary Database
ARCH
ARCH
Archived Redo Logs
Archived Redo Logs
5
What is Active Data Guard?

• Introduced in 11g Release 1
• Available in the database server Enterprise
  Edition as an additional license option
• Real time replica of a production database which
  is open in read only mode while changes
  transmitted from the primary database are being
  simultaneously applied to it.
• Applies to physical and not logical standby
  database
• Can offload reporting, queries and fast
  incremental backups from the primary site to the
  standby site improving the performance and adding
  to the scalability of the primary site
• Performs its primary Data Guard objective of
  preventing data loss and downtime due to data
  corruptions, database and site failures, human
  error, or natural disaster.

6
Active Data Guard Usage

• Redirect read only portions of application to
  Standby site - online order and shipping
  application will have a read only component where
  the catalogue is searched by customer before the
  shopping cart activity commences
• Read mostly applications can use redirection to
  Active DG site
• Redirect complex adhoc queries like year end or
  month end reports to DR site
• Redirect Business Intelligence tools like
  Discoverer to the standby site
• Use optimized incremental backups with RMAN block
  change tracking in 11g now on standby sites to
  make backups up to 20 times faster
• Extends basic Data guard functionality
  switchovers/failovers
• Data Guard is free - Active Data Guard is a
  licensed option

7
Active Data Guard Not just for protection

• Offload fast incremental backups to an Active
  Data Guard Standby
• Block change tracking eliminates full scans
• Incremental backups complete 20x faster (8.3 min
  vs 2.8 hrs)
• In 11g, block change tracking file can be created
  on physical standby
• Minimal overhead on standby database less than
  3

8
11g Active Data Guard
Read Only Users/Reporting Applications
Read/Write Users
Auto block Protection
Active Standby Database
Primary Database
Fast Incremental Backups
9
Increasing Read-Only Scalability
Active Data Guard Reader Farm
Primary Database (Two Node RAC Cluster)
10
Active Data Guard Operations Permitted

• What can we do?
• Issue SELECT statements
• Issue complex queries such as grouping SET
  queries and WITH clause queries
• Call stored procedures
• Use database links to write to remote databases
• Use stored procedures to call remote procedures
  via dblinks

11
Active Data Guard Operations Disallowed

• What we cannot do .
• Any DML excluding SELECT
• Any DDL
• So no additional indexes allowed
• AWR tool not supported for Active DG standby
• Read note 454848.1 for installing and using
  Standby statspack

12
What operations can be performed?

• SQLgt select getsal('KING') from dual
• GETSAL('KING')
• --------------
• 5000
• SQLgt select getsal_remote ('KING') from dual --
  getsal_remote is synonym for getsal_at_dblink
• GETSAL_REMOTE('KING')
• ---------------------
• 5000
• SQLgt create table mytab
• 2 (col_a number)
• create table mytab
• ERROR at line 1
• ORA-00604 error occurred at recursive SQL level
  1

13
What operations can be performed?

• SQLgt select database_role from vdatabase
• DATABASE_ROLE
• ----------------
• PHYSICAL STANDBY
• SQLgt select open_mode from vdatabase
• OPEN_MODE
• --------------------
• READ ONLY WITH APPLY
• SQLgt INSERT INTO dept_at_mylink --remote database
  
• 2 VALUES
• 3 (50,'I.T','HOUSTON')
• 1 row created.
• SQLgt commit

14
Enabling Active Data Guard

• Via SQLPLUS ..
• If the standby database is not running
• (note in 11g, STARTUP command will start standby
  in read-only mode)
• SQLgt STARTUP
• SQLgt RECOVER MANAGED STANDBY DATABASE USING
  CURRENT LOGFILE DISCONNECT
• If standby database is running and managed
  recovery in operation
• SQLgt RECOVER MANAGED STANDBY DATABASE CANCEL
• SQLgt ALTER DATABASE OPEN READ ONLY
• SQLgt RECOVER MANAGED STANDBY DATABASE USING
  CURRENT LOGFILE DISCONNECT

15
Enabling Active Data Guard

• Via the Data Guard Broker
• DGMGRLgt startup
• DGMGRLgt edit database STDBY set
  stateAPPLY_ON
• In case standby database is already in managed
  recovery mode
• Stop redo apply
• DGMGRLgt edit database STDBY set
  stateAPPLY-OFF
• Using SQLPLUS open the database in read only
• SQLgt ALTER DATABASE OPEN READ ONLY
• Restart redo apply
• DGMGRLgt edit database STDBY set
  stateAPPLY-ON

16
Dont do this at home (or in the office)

• SQLgt select dbms_rowid.ROWID_BLOCK_NUMBER(rowid)
  from emp
• DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
• ------------------------------------
• 188
• dd if/dev/null of/u02/oradata/apex/monitor_data
  01.dbf bs8192 convnotrunc seek188 ltlt EOF
• CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt
  CORRUPT corrupt CORRUPT corrupt
• EOF
• apex/u01/oracle/scriptsgt ./corrupt.sh
• 00 records in
• 00 records out
• SQLgt alter system flush buffer_cache
• System altered.

17
Active Data Guard Block Recovery

• Automatically repair block corruptions online
• Primary repaired from standby and vice-versa
• Corrupt block relative dba 0x018005da (file 5,
  block 188)
• Fractured block found during user buffer read
• Data in bad block
• type 6 format 2 rdba 0x018005da
• last change scn 0x0000.001b3ebf seq 0x2 flg
  0x04
• spare1 0x0 spare2 0x0 spare3 0x0
• consistency value in tail 0x00000000
• check value in block header 0x1b14
• computed block checksum 0xb2a8
• Reading datafile '/u01/oracle/testdr01.dbf' for
  corruption at rdba 0x018005da (file 5, block
  188)
• Reread (file 5, block 188) found same corrupt
  data
• Requesting Auto BMR for (file 5, block 188)
• Waiting Auto BMR response for (file 5, block
  188)
• Auto BMR successful

18
Guaranteed SLAs for read-only users

• Active Data Guard service level agreements (SLA)
  can be implemented using the session parameter,
  STANDBY_MAX_DATA_DELAY
• New with Data Guard 11g Release 2
• Specifies a limit for the amount of time (in
  seconds) allowed to elapse between when changes
  are committed on the primary and when they can be
  queried on an active standby database
• The active standby will return an ORA-3172 error
  code if the limit is exceeded.
• Applications can handle this error and redirect
  queries to the primary database

19
Guaranteed SLAs for read-only users

• SQLgt ALTER SESSION SET STANDBY_MAX_DATA_DELAY0
• ERROR
• ORA-03174 STANDBY_MAX_DATA_DELAY does not apply
  to SYS users
• SQLgt conn sh/sh
• Connected.
• SQLgt ALTER SESSION SET STANDBY_MAX_DATA_DELAY0
• Session altered.
• ON PRIMARY
• SQLgt update mysales set prod_id2
• ON STANDBY
• SQLgt select distinct prod_id from mysales

20
Testing Active Data Guard

• SQLgt select database_role from vdatabase
• DATABASE_ROLE
• ----------------
• PRIMARY
• SQLgt update mysales set prod_id1
• 918843 rows updated.
• SQLgt commit
• Commit complete.
• SQLgt select to_char(sysdate,'DD-MON-YYYY
  HH24MISS') from dual
• TO_CHAR(SYSDATE,'DD-
• --------------------
• 04-NOV-2009 102612

21
Testing Active Data Guard

• SQLgt select database_role from vdatabase
• DATABASE_ROLE
• ----------------
• PHYSICAL STANDBY
• SQLgt conn sh/sh
• Connected.
• SQLgt select to_char(sysdate,'DD-MON-YYYY
  HH24MISS') from dual
• TO_CHAR(SYSDATE,'DD-
• --------------------
• 04-NOV-2009 102631
• SQLgt select distinct prod_id from mysales
• PROD_ID
• ----------

22
Monitoring Active Data Guard

• Goal - How far behind does data lag on Standby
  relative to the primary database?
• Focus areas
• Determine Transport lag
• Determine Apply lag
• Determine Query SCN or time compare
  CURRENT_SCN on Primary and Standby databases
• Guarantees a transactionally consistent view of
  data
• Query SCN on Standby is equivalent to CURRENT SCN
  on primary
• Redo Transport considerations synchronous vs.
  asynchronous
• View VDATAGUARD_STATS to monitor apply lag as
  well as transport lag

23
Monitoring Active Data Guard
Query VDATAGUARD_STATS view to determine both
transport lag as well as apply lag Query
VDATABASE on both primary as well as standby
database to obtain CURRENT_SCN

• SQLgt select value from vdataguard_stats where
  name'apply lag'
• VALUE
• --------------------------------------------------
  --------------
• 00 000001
• SQLgt select value from vdataguard_stats where
  name'transport lag'
• VALUE
• --------------------------------------------------
  --------------
• 00 000000

24
11g Snapshot Standby

• Active Dataguard
• Read Only - redo from primary received and
  applied
• Snapshot Standby
• Read Write - redo from primary received but not
  applied
• Redo applied when snapshot standby database is
  converted back to physical standby
• Note
• Both Active Data Guard and Snapshot standby
  require a physical standby database in place as
  opposed to a logical standby database

25
Snapshot Standby for Test environments

• Two steps vs. 15 in 10g Release 2
• Convert Physical Standby to Snapshot Standby and
  open for read/writes by testing applications
• ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
• Discard testing writes and resync with primary by
  applying logs
• ALTER DATABASE CONVERT TO PHYSICAL STANDBY
• Preserves zero data loss as well
• But no real time query (Active Data Guard) or
  fast failover

Physical Standby Apply Logs
Open Database
Back out Changes
Snapshot Standby Perform Testing
Continuous Redo Shipping while operating in
Snapshot Standby mode
26
Snapshot Standby and Change Control

• Combined with Real Application Testing to provide
  a simple way to test and maintain protection at
  the same time
• Eliminate guesswork as performance test results
  are realistic and reliable since using a replica
  of production data
• Understand the impact as well as extent of system
  resource consumption on production data during
  performance testing
• Change assurance - Introduce changes with
  confidence
• Snapshot standby provides simultaneous disaster
  recovery capabilities as well as QUALITY
  ASSURANCE capabilities

27
Combining with Real Application Testing
Physical Standby
Archive Logs
Primary Database
Snapshot Standby
Workload Pre-process
Workload Replay
Workload Capture
Analysis Reporting
28
Snapshot Standby some considerations

• Can only be used with a physical standby database
  and not logical standby database
• Needs flashback logging to be enabled - creates a
  implicit guaranteed restore point internally to
  which the snapshot standby is flashed back to
  convert back to physical standby
• The name of this guaranteed restore points begins
  with SNAPSHOT_STANDBY_REQUIRED_
• Need to consider how long the standby database
  operates in snapshot standby mode
• First have to rewind the database to the
  guaranteed restore point using flashback logs -
  then changes generated on the primary when
  standby was in snapshot standby mode have to be
  applied by rolling forward and applying archive
  logs
• Note switchover or failover cannot be performed
  to a snapshot standby database

29
Final Thoughts .

• Organisations are not fully utilising the
  investment in hardware and resources on disaster
  recovery sites
• Now in Oracle 11g, it is possible to increase the
  return on such investment by employing new
  features like Active Data Guard and Snapshot
  Standby database
• Standby databases are not just for disaster
  recovery
• Offload resource intensive operations like
  reporting and backups to standby sites
• Use the standby database to perform testing and
  QA via the Snapshot Database technology
• Need to weigh benefits vs. perceived additional
  licensing costs

30
Thanks for attending!!
http//gavinsoorma.wordpress.com Tel
0417713124 gavin.soorma_at_bankwest.com.au
A