Transcript Maximum Performance

Oracle Data Guard
Topics to be covered
What is Oracle Data Guard?
Oracle 9i Data Guard Features
New features in 10g
New features in 11g
Management Tools
What are we doing with Data Guard?
What is Oracle Data
Guard?
Oracle Data Guard is the management,
monitoring, and automation software
infrastructure that creates, maintains, and
monitors one or more standby databases to
protect data from failures, disasters, errors,
and corruption
Oracle 9i Data Guard
Types of Standby Databases
Creating Standby Databases
Log Transport Services
Log Apply Services
Switchover/Failover
Types of Standby
Databases
Physical Standby Database
Logical Standby Database
Snapshot Standby Database (11g)
Physical Standby
Database
A physical standby database is physically
identical to the primary database, with on-disk
database structures that are identical to the
primary database on a block-for-block basis.
The database schema, including indexes,
must be the same.
Physical Standby
Database
Managed Recovery- Physical standby is
maintained by applying archived redo logs
on the standby using Oracle recovery
operations. Recovery applies changes on a
block for block basis.
Open Read-Only- Physical standby
databases can only be queried in ReadOnly mode.
Physical Standby
benefits
Disaster Recovery/High AvailabilitySwitchover/failover allow for role reversal of
primary and standby, minimizing downtime of
primary database.
Data Protection- Data Guard can ensure no
data loss with physical standby.
Reduction in primary database workload- Can
take backups from physical standby.
Physical Standby
benefits (cont.)
Performance- Physical standby is more
efficient than Logical, in that it uses redo apply
technology rather than SQL apply. It bypasses
SQL level mechanisms, and applies redo
directly at block level.
Logical Standby
Database
Initially created as a copy of primary database.
Can later be changed.
Automatically applies archived redo log info by
transforming data in redo logs into SQL
statements and executing them on the logical
standby (must remain open).
Target tables on standby (ones being updated
from primary) are read only.
Logical Standby Benefits
Efficient use of standby hardware- Logical
standby can contain other schemas than those
being updated by Data Guard. those other
schemas are read/write capable, while those
being maintained by Data Guard are read only.
Reduction of Primary Database WorkloadSince standby is open, it’s available for query
(or reporting), offloading work from primary.
Snapshot Standby
Database (11g)
Physical standby database that can be
opened for read/write use. Redo logs are still
received from primary, just not applied until
snapshot standby is converted back into a
physical standby.
Can have similar functionality with 10g Data
Guard, however must be done manually with
flashback database.
Creating a Physical
Standby Database
Make copy of primary database (data files)
Create standby control file on primary database
Creating a Physical
Standby Database (cont.)
Prepare pfile for standby database
Standby Parameters
Defined
REMOTE_ARCHIVE_ENABLE- Permission
to write remote archive logs.
Values: True,False,Send
DB_FILE_NAME_CONVERT- Converts the
filename of a datafile on the primary to a
filename onthe standby database. (also
paths)
Standby Parameters
Defined
LOG_FILE_NAME_CONVERT- Converts the
filename of a log on the primary database to
the filename of a log on the standby database.
(Must use if paths are different between
primary and standby)
FAL_SERVER- Assigns the Oracle Net service
name that the standby will use to connect to
the fetch archive log server.
Standby Parameters
Defined
FAL_CLIENT- Assigns fetch archive log client
name to be used by the FAL server. This is the
Oracle Net service name that the FAL server
should use to refer to the standby database.
STANDBY_ARCHIVE_DEST- Location on
standby system where archived redo logs
received from the primary are stored.
Standby Parameters
Defined
STANDBY_FILE_MANAGEMENT- Automates
the creation and deletion of datafile filenames
on the standby site using the same filenames
as the primary site. Values:auto,manual
Creating a Physical
Standby Database (cont.)
Copy files from primary to standby system
On primary system set pfile parameters
Primary Parameters
Defined
LOG_ARCHIVE_DEST_2- Specify
‘SERVICE=XXXX’, XXXX being the service
name of the standby database listed in the
tnsnames.ora file on the primary system. This
is the location for the primary to write it’s redo.
LOG_ARCHIVE_DEST_STATE_2- Enables or
disables writing of logs to log_archive_dest_2.
Values: enable, defer, Alternate
Primary Parameters
Defined
REMOTE_ARCHIVE_ENABLE- Enables or
disables the sending of redo logs to remote
destinations and the receipt of remote redo
logs. Values: true,false,send,receive
(true=send + receive)
Creating a Physical
Standby Database (cont.)
Configure listeners for primary and standby
databases
Set SQLNET.EXPIRE_TIME=2 in sqlnet.ora
Modify tnsnames.ora on both primary and
standby
Create spfile on standby (optional)
Creating a Physical
Standby Database (cont.)
Start physical standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY
DATABASE;
Initiate log apply services
ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE DISCONNECT
FROM SESSION;
Creating a Physical
Standby Database (cont.)
Start remote archiving (from primary)
ALTER SYSTEM ARCHIVE LOG
CURRENT;
Physical standby database is now up and
running!
Log Transport Services
What are Log Transport services?
Log Transport Services control the
automated transfer of redo data within a
Data Guard configuration. They also
control the level of data protection for your
database.
Data Protection Modes
Maximum Protection- Highest level of
protection. A primary database transaction will
not commit until all redo data needed to
recover the transaction have been written to at
least one standby database, if it can’t write to
a standby, the primary will shutdown.
Guarantees no data loss, but has highest
impact on performance of the primary
database.
Data Protection Modes
(cont.)
Maximum Performance- This is the default
protection mode. A primary database
transaction will not wait to commit until redo
data needed to recover the transaction is
written to a standby database. This mode
provides the highest level of protection
available without affecting performance or
availability of the primary database. Does not
guarantee no data loss.
Data Protection Modes
(cont.)
Maximum Availability- Offers 2nd highest level
of protection. Same as Maximum Protection,
except if no standby database is available the
primary does not shutdown, it temporarily
goes into Maximum Performance mode until a
standby is available. This mode guarantees no
data loss except for when in Maximum
Performance mode. It doesn’t effect the
availability of the primary like Maximum
Protection Mode does.
Data Protection Modes
Maximum Protection
No Data Loss
Effects Performance
Effects Availability
Maximum Availability
Maximum Performance
No Data Loss (except when in
Max Perform)
Does Not Effect Availability
Does Not Effect Performance
Does Not Effect Availability
Effects Performance
Possible Data Loss
Transporting Redo Data
Maximum Protection and Maximum Availability
modes both require standby redo log files on
the standby system. Both use RFS (Remote
File Server) process to write to standby redo
logs on the standby database system.
Maximum Performance mode does not use
standby redo logs when using arch process to
write to standby.
Maximum Protection
Maximum Performance
Transmission and
Reception
of
Redo
Data
Specify the process to transmit redo, ARCH or
LGWR (ARCH is the default)
LOG_ARCHIVE_DEST_2=’SERVICE=stdb
y LGWR’
Choose SYNC or ASYNC network
transmission mode. Must use SYNC for zero
data loss.
LOG_ARCHIVE_DEST_2=’SERVICE=stdb
y ASYNC’
Data Protection Modes
Max
Protection
Max
Availability
Max
Performance
Redo Archival Process
LGWR
LGWR
LGWR OR ARCH
Network Transmission
Mode
SYNC
SYNC
ASYNC
Disk Write Option
AFFIRM
AFFIRM
NOAFFIRM
Standby Redo Logs
Required?
Yes
Required for
Physical Only
Only if using
LGWR
Database Types
Physical
Physical/Logical
Physical/Logical
Log Apply Services
Processes involved in Log apply services (for
physical standby)
Remote File Server (RFS)- Receives redo
data from primary.
Archiver (ARCn)- Archives standby redo
logs that are to be applied to standby.
Managed Recovery Process (MRP)Applies archived redo logs to standby.
Configuring Log Apply
Services
Start the Physical Standby:
To Stop Log Apply Services:
Log Apply Services
To Verify Managed Recovery:
To Determine if there is an Archive Gap:
Monitoring Log Apply
Services
The following views can be used to monitor
Log Apply Services:
V$MANAGED_STANDBY
V$ARCHIVED_LOG
V$LOG_HISTORY
V$DATAGUARD_STATUS
Switchover/Failover
Switchover- Role reversal between primary
and standby databases. Old primary is now a
standby. Used for hardware upgrades, OS
upgrades, etc.
Failover- Primary is down with little hope of
quick recovery (ie. hardware failure on primary
machine). Once failover has been initiated,
the old primary database is no longer useful.
How to Perform a
Switchover
On primary database:
Initiate switchover on primary database:
After the above statement completes the old
primary is now a standby database
How to Perform a
Switchover (cont.)
While still on old primary shutdown database
and restart as standby:
On the old standby system (new primary):
How to Perform a
Switchover (cont.)
On new primary complete switchover then
restart database:
On new standby start managed recovery:
On new primary begin redo transport:
Switchover Complete!
How to Perform a
Failover
Manually register any redo logs with the
standby that are available and that have not
been applied
If using standby redo logs, initiate failover with:
If not using standby redo logs:
How to Perform a
Failover (cont.)
Convert the Physical Standby to Primary
(modify pfile before restart):
Failover complete!
10g New Features
Fast Start Failover- Provides the ability to
automatically fail over to a designated standby
database when the primary goes down.
Automatically reconfigures old primary as a
standby when it re-enters the configuration.
10g New Features (cont.)
Asynchronous Redo Transmission- (LGWR
ASYNC) has been improved to reduce the
impact on the primary database (LSNn
process added to do redo log transmission).
Flashback Database across Data Guard
Switchovers- Now possible to flash back
primary and standby to an SCN or point in
time prior to switchover operation.
11g New Features
Snapshot Standby- Physical standby that can
be opened read-write, then revert back to
managed recovery.
Active Data Guard- Physical standby that can
be opened read-only while redo is still applied
to the standby. (SUNY is not currently
licensed for this feature)
11g New Features (cont.)
New advanced compression for redo
transport.
Transient Logical Standby allows a physical
standby to be changed to a logical standby
and then back to a physical. Used for rolling
database upgrades.
Management Tools
SQL - Data Guard can be managed and
monitored with SQL*PLUS. Mostly Manual.
Data Guard Broker- Special interface for Data
Guard. Has command line interface and Java
GUI. Less manual than SQL*PLUS.
Grid Control- Grid control has a web based
GUI for Data Guard Broker. Enables you to
use GC to schedule and monitor Data Guard
operations
What is ITEC doing with
Data Guard?
Currently ITEC is using Data Guard in it’s Data
Vaulting service. ITEC currently hosts
physical standby databases for the following
campuses:
Hudson Valley Community College
Niagara County Community College
SUNY Brockport
SUNY Fredonia
SUNY Oneonta
SUNY Oswego
What is ITEC doing with
Data Guard?
Currently we are using all Physical standby
databases. When we started the Data
Vaulting service, Banner had data types that
were incompatible with logical standby
databases. This may be re-evaluated in the
future (newer releases of Oracle are
compatible with more data types).
What is ITEC doing with
Data Guard?
ITEC currently manages all of it’s physical
standby databases using the SQL*Plus
interface. Our future intent is to use Grid
Control to manage and monitor all standby
databases. ITEC is currently in the process of
rolling out Grid Control to all systems ITEC
manages.
What is ITEC doing with
Data Guard?
In the Future, when the Dual Data Center
project is complete, ITEC will replicate the
production databases hosted at ITEC to the
Albany (Sys Admin) data center using either
storage level replication or Data Guard.
Questions???
Erik Snyder
[email protected]