Transcript Slide 1

11g Active Data Guard
More than just DR….
Gavin Soorma
Senior Oracle DBA,
Bankwest
AUSOUG National Conference Series 2009
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
AUSOUG National Conference Series 2009
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
AUSOUG National Conference Series 2009
Data Guard Overview
Data Guard
Broker
Transactions
Oracle Net
LGWR
Online
Redo
Logs
Primary
Database
MRP
RFS
Standby
Redo
Logs
Standby
Database
ARCH
ARCH
Archived
Redo Logs
Archived
Redo Logs
AUSOUG National Conference Series 2009
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.
AUSOUG National Conference Series 2009
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
AUSOUG National Conference Series 2009
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%
AUSOUG National Conference Series 2009
11g Active Data Guard
Read Only
Users/Reporting
Applications
Read/Write
Users
Auto block Protection
Active Standby Database
Primary Database
Fast Incremental
Backups
AUSOUG National Conference Series 2009
Increasing Read-Only Scalability
Active Data Guard
Reader Farm
Primary Database
(Two Node RAC Cluster)
AUSOUG National Conference Series 2009
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
AUSOUG National Conference Series 2009
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
AUSOUG National Conference Series 2009
What operations can be performed?
SQL> select getsal('KING') from dual;
GETSAL('KING')
-------------5000
SQL> select getsal_remote ('KING') from dual; -- getsal_remote is synonym for getsal@dblink
GETSAL_REMOTE('KING')
--------------------5000
SQL> create table mytab
2 (col_a number);
create table mytab
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
SQL> delete scott.emp;
delete scott.emp
*
ERROR at line 1:
ORA-16000: database open for read-only access
AUSOUG National Conference Series 2009
What operations can be performed?
SQL> select database_role from v$database;
DATABASE_ROLE
---------------PHYSICAL STANDBY
SQL> select open_mode from v$database;
OPEN_MODE
-------------------READ ONLY WITH APPLY
SQL> INSERT INTO dept@mylink
2 VALUES
3 (50,'I.T','HOUSTON');
--remote database
1 row created.
SQL> commit;
Commit complete.
SQL> UPDATE dept@mylink -- remote database
2 SET LOC='DALLAS'
3 WHERE LOC='HOUSTON';
1 row updated.
AUSOUG National Conference Series 2009
Enabling Active Data Guard
Via SQL*PLUS …..
If the standby database is not running
(note in 11g, STARTUP command will start standby in read-only mode)
SQL> STARTUP
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
If standby database is running and managed recovery in operation
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
AUSOUG National Conference Series 2009
Enabling Active Data Guard
Via the Data Guard Broker …
DGMGRL> startup
DGMGRL> edit database ‘STDBY’ set state=‘APPLY_ON’
In case standby database is already in managed recovery mode
Stop redo apply
DGMGRL> edit database ‘STDBY’ set state=‘APPLY-OFF’
Using SQL*PLUS open the database in read only
SQL> ALTER DATABASE OPEN READ ONLY;
Restart redo apply
DGMGRL> edit database ‘STDBY’ set state=‘APPLY-ON’
AUSOUG National Conference Series 2009
Don’t do this at home (or in the office)
SQL> 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_data01.dbf bs=8192 conv=notrunc seek=188 << EOF
CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt
EOF
apex:/u01/oracle/scripts> ./corrupt.sh
0+0 records in
0+0 records out
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from monitor.emp;
select * from monitor.emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 188)
ORA-01110: data file 5: '/u02/oradata/apex/monitor_data01.dbf'
AUSOUG National Conference Series 2009
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
AUSOUG National Conference Series 2009
Guaranteed SLA’s 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
AUSOUG National Conference Series 2009
Guaranteed SLA’s for read-only users
SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=0;
ERROR:
ORA-03174: STANDBY_MAX_DATA_DELAY does not apply to SYS users
SQL> conn sh/sh
Connected.
SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=0;
Session altered.
ON PRIMARY
SQL> update mysales set prod_id=2;
ON STANDBY
SQL> select distinct prod_id from mysales;
ERROR at line 1:
ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded
SQL> /
PROD_ID
---------1
>>>> QUERY REDIRECTED TO PRIMARY
AUSOUG National Conference Series 2009
Testing Active Data Guard
SQL> select database_role from v$database;
DATABASE_ROLE
---------------PRIMARY
SQL> update mysales set prod_id=1;
918843 rows updated.
SQL> commit;
Commit complete.
SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD-------------------04-NOV-2009 10:26:12
SQL> select distinct prod_id from mysales;
PROD_ID
---------1
AUSOUG National Conference Series 2009
Testing Active Data Guard
SQL> select database_role from v$database;
DATABASE_ROLE
---------------PHYSICAL STANDBY
SQL> conn sh/sh
Connected.
SQL>
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD-------------------04-NOV-2009 10:26:31
SQL> select distinct prod_id from mysales;
PROD_ID
---------1
AUSOUG National Conference Series 2009
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 V$DATAGUARD_STATS to monitor “apply lag” as well as “transport
lag”
AUSOUG National Conference Series 2009
Monitoring Active Data Guard
Query V$DATAGUARD_STATS view to determine both transport lag as
well as apply lag
Query V$DATABASE on both primary as well as standby database to obtain
CURRENT_SCN
SQL>
select value from v$dataguard_stats where name='apply lag';
VALUE
---------------------------------------------------------------+00 00:00:01
SQL> select value from v$dataguard_stats where name='transport lag';
VALUE
---------------------------------------------------------------+00 00:00:00
AUSOUG National Conference Series 2009
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
AUSOUG National Conference Series 2009
Snapshot Standby for Test environments
•
•
Physical Standby
–
Apply Logs
Open
Database
Back out •
Changes
Snapshot Standby
Perform Testing
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
Continuous Redo Shipping
while operating in Snapshot Standby mode
AUSOUG National Conference Series 2009
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
AUSOUG National Conference Series 2009
Combining with Real Application Testing
Archive Logs
Primary Database
Workload
Capture
Physical
Standby
Snapshot Standby
Workload
Replay
Workload
Pre-process
Analysis
& Reporting
AUSOUG National Conference Series 2009
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
AUSOUG National Conference Series 2009
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
AUSOUG National Conference Series 2009
Thanks for attending!!
http://gavinsoorma.wordpress.com
Tel: 0417713124
[email protected]
QUESTIONS
ANSWERS
AUSOUG National Conference Series 2009