Transcript Oracle 11g Dataguard Enhancements - oracle-info

11g(R1/R2) Data guard Enhancements
Suresh Gandhi
}
Agenda
}
}
}
}
}
}
}
}
}
2
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
Q&A
}
Data Guard Overview
Data Guard
Standby Plus more
High Availability & Disaster
Recovery Solution
3
}
4
Provides ….
}
5
Data guard Databases
}
11G R1 Enhancements
Release 1
New
Features
6
}
R2 - Enhancements
Release 2
New
Features
7
}
R2- New Features
Release 2
New
Features
8
}
Active and Snapshot Standby
Traditional
Functionality
11g
Standbyuntil 10g
11gSnapshot
Active
DataGuard
9
}
10
Active Data Guard Benefits
}
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 (SRL’s)
Status changed to
read only apply
11
}
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
12
}
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
<SCN>
Additional License
13
•
•
•
•
•
•
•
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
1. Configure the Flash Recovery Area
2. Set the Retention Target
3. Enable Flashback Database
}
10g Equivalent Snapshot commands
Prepare the physical standby database to be activated
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G;
SQL> 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.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
Cancel Redo Apply and create a guaranteed restore point.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> CREATE RESTORE POINT restore_pt GUARANTEE FLASHBACK DATABASE;
Activate the physical standby database.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
SQL> STARTUP MOUNT FORCE;
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL> ALTER DATABASE OPEN
Revert the ac4vated database back to a physical standby database.
SQL> STARTUP MOUNT FORCE;
SQL> FLASHBACK DATABASE TO RESTORE POINT restore_pt;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> STARTUP MOUNT FORCE;
14
}
15
DATGUARD – Still More Features
}
More Features – RMAN Active database Duplication
}Steps
–Create a pfile with only db_name=stby
–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 
16
RMAN> 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_convert 'emrep','repstby'
set db_unique_name='repstby'
set db_file_name_convert='C:\','F:\'
set log_file_name_convert='C:\','F:\'
;
}
}
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_2=‘Service=stby Async compression=ENABLE’
17
}
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
18
}
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
19
}
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
20
}
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 <target DB Name>
21
}
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
22
}
23
More Features: STANDBY_MAX_DATA_DELAY
}
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
24
}
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
}
Finally, Monitoring Views
} GAP_STATUS , additional column in v$archive_dest – GAP or NO
GAP
} V$dataguard_stats for transport/apply lag
} V$REDO_DEST_RESP_HISTOGRAM
– Useful when need to analyse network latencies by checking transport lag
} V$STANDBY_EVENT_HISTOGRAM
26
Thank you!
Q&A
Internal
27