Transcript Maximum availability

Oracle Data Guard 11g Release 2 with
Oracle Enterprise Manager 11g Grid
Control
Oracle Data Guard 11g Release 2
with Oracle Enterprise Manager 10g
Grid Control
Introduction to High Availability
 Data Guard Concepts
 Managing Data Guard with OEM
 Setup Data Guard without OEM

Introduction to High Availability

Availability is the degree of accessibility of an application, service or system. It is
measured as the percentage of uptime in a given year.
Availability %
90%
95%
98%
99%
99.50%
99.80%
99.9% ("three nines")
99.95%
99.99% ("four nines")
99.999% ("five nines")
99.9999% ("six nines")

Annual downtime Monthly downtime*
Weekly downtime
36.5 days
72 hours
16.8 hours
18.25 days
36 hours
8.4 hours
7.30 days
14.4 hours
3.36 hours
3.65 days
7.20 hours
1.68 hours
1.83 days
3.60 hours
50.4 minutes
17.52 hours
86.23 minutes 20.16 minutes
8.76 hours
43.2 minutes 10.1 minutes
4.38 hours
21.56 minutes 5.04 minutes
52.6 minutes
4.32 minutes 1.01 minutes
5.26 minutes
25.9 seconds 6.05 seconds
31.5 seconds
2.59 seconds 0.605 seconds
Characteristics of a highly available systems
o
o
o
o
o
o
Reliability
Resiliency
No single point of failure (SPOF)
Recoverability
Timely error detection
Continuous operation
Introduction to High Availability
Finalize with all stake holders prior to DR planning
and implementation
Service Level Agreement (SLA): agreement between
two parties specifying levels of availability,
performance or operation. In the contract the level of
each service should be clearly defined.
 Recovery Time Objective (RTO): maximum amount of
time that application, service or system can be down
before the organization starts suffering unacceptable
consequences.
 Recovery Point Objective (RPO): maximum amount
of data that can be lost without harm to the
organization as a result from failure of an application,
service or system.

Introduction to High Availability
Resiliency and protection from failures









Unplanned
Site failure
Cluster failure
Computer failure
System failure
Storage failure
Data corruption
Human error
Hang or slowdown
Natural disaster



Planned
System and database
changes
Data changes
Application changes
Introduction to High Availability
Present demand for high availability
 Oracle HA solutions as response






Oracle RAC
Oracle Data Guard
Oracle Flashback
Oracle Streams
Oracle Goldengate
How we benefit leveraging Oracle RAC, Oracle Data Guard
and Oracle Flashback implementing MAA?
 How Oracle RAC, Oracle Data Guard, Oracle Flashback
work in tandem in implementing MAA solutions.

 Data Guard Configurations
 Fast Start failover
 Flashback as a way to avoid restoring the primary from backup in case of
a failover, converting it to standby and synching it with the new primary.

OEM as a primary tool for managing MAA solutions.
Data Guard Concepts



Oracle Data Guard provides the managements,
monitoring and automation software to create
and maintain one or more standby databases to
protect Oracle data from failures, disasters,
human errors and data corruption
Data Guard configuration consist of one primary
database and one or more standby databases. The
databases in a Data Guard configuration may be
dispersed geographically.
Managing primary and standby databases can be
done using SQL command-line interfaces, Data
Guard broker interfaces or using a GUI interface
provided with OEM Grid Control.
Data Guard Concepts
Primary database: accessed by most
applications, can be a single instance or RAC
database
 Standby database: consistent copy of the
primary database. Maintained automatically
using redo data. Can be a single-instance or
RAC database.

Data Guard Concepts
•
•
•
1.
2.
3.
Physical Standby database
Provides a block-for-block physically identical
copy of the primary database.
Synchronized through Redo Apply. Redo
records are shipped and applied to the
standby database.
Benefits:
Switchover and failover
Standby database can be open in read only
mode while redo apply continues ( Active
data Guard). Can not be used when with
reporting tools requiring read-write access
such as repository creation.
Can be used as a robust DR
implementation.
•
•
•
•
•
1.
2.
3.
Logical Standby database
Provides a logically (transactionally) identical
copy of the primary database. Contains the
same logical information as the primary
database although the physical organization
and structure can be different.
Synchronized via SQL Apply. Redo records are
shipped to the standby database and mined
and applied as a SQL to the standby database.
Additional structures can be created to
optimize the reporting workload.
Some restrictions on data types, types of
tables and types of DDL and DML operations.
Benefits:
Switchover and failover
Standby database open in read-write
mode. Offload production when apps need
read-write access but do not modify
primary data such as when repositories
are created.
Can be used to perform upgrades to
primary database with near zero
downtime.
Data Guard Concepts


Switchover : the primary database transition to a
standby role and the standby database transition
to the primary role. It is used during the course
of a planned maintenance of the primary database
and ensure no data loss. Final EOR is applied on
the standby database.
Failover: Occurs when the primary database is
unavailable. Failover is performed only in case of a
failure of the primary database and the failover
results in a transition of a standby database to the
primary role. Final EOR is not applied on the
standby database.
Data Guard Concepts
Maximum availability: Archive destination is mandatory with log writer
sync and affirm. Set by ‘alter database set standby to maximize availability’.
Transactions do not commit until all redo data needed to recover the
transactions has been written to the online redo log and to the standby
redo log on at least one synchronized standby database. Ensures zero data
loss except in case of certain failures when standby can not receive redo
from primary.
 Maximum performance: Archive destination is either
mandatory/optional and transport is log writer or archive process and
synchronous or asynchronous. Set by ‘alter database set standby to
maximize performance’ .Is the default protection mode. Transactions
commit as soon as redo data generated by those transactions has been
written to the online log. Offers less data protection than maximum
availability mode.
 Maximum protection: Archive destination is mandatory with log writer
sync and affirm. Set by ‘alter database set standby to maximize protection’.
Redo data needed to recover as a transaction must be written to both the
online redo log and to the standby database redo log on at least one
synchronized standby database before the transaction commits. Ensures
zero data loss.

Data Guard Concepts
•
•
•
•
•
•
New features in 11gR1
Support up to 9 standby
databases.
Real time query capability of
physical standby database
(New Active Data Guard)
feature.
User configurable conditions
to initiate fast-start failover.
New Snapshot Data Guard
feature.
Transport of redo data using
SSL
Support of TDE with Data
Guard SQL Apply.
•
•
•
•
New features in 11gR2
Support up to 30 standby
databases.
Integrated support for
Application failover.
Compressed table support in
logical standby databases.
Automatic redo corruption
detection and repair.
Data Guard Concepts
Lets put it together…
Managing Data Guard with OEM

Add physical or logical standby database.



Crate a new physical or logical standby database
Add an existing standby database for management with Data Guard Broker.
Flexible options for creating standby database based on RMAN features for backup and cloning.
 Direct cloning using the primary database as a source.
 Using a staged backup
 Created with create standby database wizard
 Created as level 0 and level 1 regular backup

Monitor performance of a standby database









Redo Generation Rate
Redo Apply Rate
Transport Lag
Apply Lag
Monitor the archiving and application of redo log
Verify structure of a Data Guard
Perform Switchover or Failover
Set the type of a standby database (Active Data Guard & Snapshot Data
Guard)
Manage a Data Guard configuration and set fast-start failover.
Managing Data Guard with OEM

Add physical or logical standby database.
Managing Data Guard with OEM

Add physical standby database.
Managing Data Guard with OEM

Add physical standby database.
Managing Data Guard with OEM

Add physical standby database.
Managing Data Guard with OEM

Add physical standby database.
Managing Data Guard with OEM

Add physical standby database.
Managing Data Guard with OEM

Add physical standby database.
Managing Data Guard with OEM

Add physical standby database.
Managing Data Guard with OEM

Add physical standby database.
Managing Data Guard with OEM

Add physical standby database.
Managing Data Guard with OEM

Add physical standby database.
◦
◦
After adding a standby database new options under Data Guard hyperlink appears
What we gain?
Managing Data Guard with OEM

Verify structure of a Data Guard
Managing Data Guard with OEM
Verify structure of a Data Guard


What OEM do for us?
What we need to do if we do it ourselves?
Initializing
Connected to instance oel55.gj.com:D11P
Starting alert log monitor...
Updating Data Guard link on database homepage...
Skipping verification of fast-start failover static services check.
Data Protection Settings:
Protection mode : Maximum Performance
Redo Transport Mode settings:
D11P: ASYNC
D11P1: ASYNC
Checking standby redo log files.....OK
Checking Data Guard status
D11P : Normal
D11P1 : Normal
Checking inconsistent properties
Checking agent status
Checking applied log on D11P1...OK
Processing completed.
Managing Data Guard with OEM

Monitor performance of a standby database
Managing Data Guard with OEM

Monitor performance of a standby database
Managing Data Guard with OEM

Manage a Data Guard configuration
Managing Data Guard with OEM

Manage a Data Guard configuration
Managing Data Guard with OEM

Manage a Data Guard configuration
Managing Data Guard with OEM

Manage a Data Guard configuration
Managing Data Guard with OEM

Manage a Data Guard configuration
Managing Data Guard with OEM

Manage a Data Guard configuration – Perform a
switchover
Managing Data Guard with OEM

Manage a Data Guard configuration – Perform a
Failover
Managing Data Guard with OEM

Manage a Data Guard configuration – Perform a
Failover
Managing Data Guard with OEM

Manage a Data Guard configuration – Perform a
Failover & Reinstate the old physical database
Managing Data Guard with OEM

Manage a Data Guard configuration – Perform a
Failover & Reinstate the old physical database
Managing Data Guard with OEM

Manage a Data Guard configuration –
Configure fast-start failover
o
o
o
o
o
o
Select a standby database as a target for the fast-start
failover
Configure observer ( specify observer host and
observer ORACLE_HOME)
Set failover properties
Enable flashback logging
Enable observer
Fast start failover does NOT work in maximum
protection role. In 10g only max availability role was
supported for FSFO. Since 11g both maximum
performance and maximum availability are supported.
Managing Data Guard with OEM

Manage a Data Guard configuration –
Configure Observer
Managing Data Guard with OEM

Manage a Data Guard configuration –
Enable flashback logging
Managing Data Guard with OEM

Manage a Data Guard configuration –
complete fast-start failover configuration
Managing Data Guard with OEM

Manage a Data Guard configuration –
complete fast-start failover configuration
Managing Data Guard with OEM

Manage a Data Guard configuration – Create logical
standby database
•
•
•
•
•
•
Specify ‘Create new logical standby database’ and follow similar
steps as create a physical standby database.
SQL Apply Unsupported tables
Specify Backup options
Specify Database location
Specify File locations
Specify Configuration
Managing Data Guard with OEM

Manage a Data Guard configuration –
Create logical standby database
 SQL Apply Unsupported tables
Managing Data Guard with OEM

Manage a Data Guard configuration –
Create logical standby database
Managing Data Guard with OEM

Monitor a Data Guard configuration
Managing Data Guard with OEM

Monitor a Data Guard configuration
 Redo logs archived and applied
Managing Data Guard with OEM

Active Data Guard
 What is Active Data Guard?
 How to set Active Data Guard?
Managing Data Guard with OEM

Snapshot Data Guard
 What is Snapshot Data Guard?
 How to set Snapshot Data Guard?




Set Redo Transport Off
Enable Flash back Database
Set Redo Transport On
Use Convert to toggle between Snapshot Database and Physical Database
Managing Data Guard with OEM
Manage a Data Guard configuration – Add
existing Data Guard with OEM
•
Configuring Data Guard Broker with OEM
Grid Control to manage an existing Data
Guard by enabling Data Guard Broker
• Select ‘Add standby database screen’
• Specify the standby database to be enabled for
management with Data Guard Broker and specify the
additional properties (login credentials, archive
destination, FRA).
• Review the changes and confirm the creation of a Data
Guard Broker configuration enabling us to manage the
database via OEM or DGMGRL.
Managing Data Guard with OEM
Manage a Data Guard configuration –
Remove Data Guard configuration
• Removing a standby database from Data Guard
Broker.
• Select a standby database you want removed from the Data
Guard configuration
• Press REMOVE button.
• Acknowledge that you want to remove it at the confirmation
page (database remains on ASM or disk).
• Remove a Data Guard configuration
• Click at the ‘Remove Data Guard Configuration’ link
• Acknowledge that you want to remove the configuration at the
confirmation page.
• When complete you are returned to the Data Guard home
page.
Managing Data Guard without OEM
Setup Data Guard without OEM
 Configure primary database
 Configure Oracle NET
 Prepare standby site




Create directory structures on the standby site
Copy password file from primary database
Create init file
Set-up environment variables on the standby site
 Create the standby database using the new 11g RMAN
feature for duplicating a database using an active primary
database.
 Verify that the standby database is correct and redo data
is received and archived on the standby database.
 Create a data Guard Broker configuration using
DGMGRL utility
Managing Data Guard without OEM
Configure primary database
 Make sure the primary database has a spfile;
 Enable force logging on primary:
SQL> alter database force logging;
 Configure database to have standby redo logs on primary:
SQL> alter database add standby logfile;
 Configure database initialization parameters
LOG_ARCHIVE_CONFIG and LOG_ARCHIVE_DEST_n on
primary:
SQL>alter system set
log_archive_config=’dg_config=(d11p,d11s)’;
SQL>alter system set log_archive_dest_2=’service=d11s
ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE)
db_unique_name=d11s’;
 Configure the primary database in ARCHIVELOG mode
SQL> alter database arhivelog;
Managing Data Guard without OEM
Configure Oracle NET

Create an alias for the standby in tnsnames.ora
◦ D11S =
◦
(DESCRIPTION =
◦
(ADDRESS_LIST =
◦
(ADDRESS = (PROTOCOL = TCP)(HOST = oel55.gj.com)(PORT = 1521))
◦
)
◦
(CONNECT_DATA =
◦
(SERVER = DEDICATED)
◦
(SERVICE_NAME = D11S)
◦
)
◦
)


On the standby site configure an entry for the standby database in listener.ora
◦ SID_LIST_LISTENER =
◦
(SID_LIST =
◦
(SID_DESC =
◦
(GLOBAL_DBNAME = D11S)
◦
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 )
◦
(SID_NAME = D11S)
◦
)
◦ )
Managing Data Guard without OEM
Prepare Standby site for D11S standby
database
 Create the directories required for the standby
database D11S.
 Copy the orapw from the primary site and rename
it to orapwD11S to match the name of the standby
instance.
 Create initD11S.ora with a line db_name=D11S
 Set environment variables.
 Start the standby database in nomount using the
initD11S.ora
Managing Data Guard without OEM
Run RMAN to create the standby database D11S from
D11P


On the primary site:
Run RMAN for creating the standby database
rman target / auxiliary sys@D11S

execute the script:
run {
allocate channel d11gp1 type disk;
allocate channel d111p2 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert ‘d11p,d11s’
set db_unique_name=’d11’
set db_file_name_convert=’/d11p/’,’/d11s/’
set log_file_name_convert=’/d11p/’,’/d11s/’
set log_archive_max_processe=’5’
set fal_client=’d11s’
set fal_server=’d11p’
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(d11p,d11s)’
set log_archive_dest_2=’service=d11p SYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=d11p’;
}
Managing Data Guard without OEM
Verify the standby database creation. Make
sure redo is received and archived on
standby



On the standby database check the sequence# of the
last received log by issuing:
SQL> select sequence#, first_time, next_time, applied
from v$archive_log order by sequence#;
Perform a log switch on the primary
SQL> alter system switch logfile;
Check if the log is received and archived on the standby
site by verifying that the sequence is increments by one.
SQL> select sequence#, first_time, next_time, applied
from v$archive_log order by sequence#;
Managing Data Guard without OEM
Create a data Guard Broker configuration using
DGMGRL utility

Set the DG_BROKER_START=TRUE on both primary and standby database
SQL> alter system set dg_broker_start=true;

Modify the listener to work with Data Guard Broker by adding a static entry for DGMGRL to avoid ORA-12154 errors. Errors can be
observed on startup of the standby database after performing a switchover. Reload the listener.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = D11P_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 )
(SID_NAME = D11P)
)
(SID_DESC =
(GLOBAL_DBNAME = D11S_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 )
(SID_NAME = D11S)
)
)

Invoke the DGMGRL and connect to the primary database. Create the Data Guard Broker configuration that includes a profile for the
primary database.
DGMGRL>create configuration ‘DR1’ as primary database is ‘D11P’ connect identifier is D11P;

Add the physical standby database to the broker configuration.
DGMGRL>add database ‘D11S’ as connect identifier is D11S;

Enable the configuration.
DGMGRL> enable configuration;

Verify the configuration.
DGMGRL> show configuration;
Managing Data Guard without OEM
Create a data Guard Broker configuration using
DGMGRL utility


How to setup Active Data Guard?
On the standby database perform:
DGMGRL> edit database ‘D11S’ set state=’apply-off’;
SQL>alter database open read only;
DGMGRL>edit database ‘D11S’ set state=’apply-on’;
Managing Data Guard without OEM
Create a data Guard Broker configuration using
DGMGRL utility

How to enable Fast start failover?
 Enable flashback on the primary and standby databases
 Verify we configured standby redo log files
 Ensure the LogXptMode Property is set to SYNC. These
commands will succeed only if database is configured with
standby redo logs.
DGMGRL> EDIT DATABASE D11P SET PROPERTY
'LogXptMode'='SYNC';
DGMGRL> EDIT DATABASE D11S SET PROPERTY
'LogXptMode'='SYNC';
 Specify the FastStartFailoverTarget property
DGMGRL> EDIT DATABASE D11P SET PROPERTY
FastStartFailoverTarget='D11S';
DGMGRL> EDIT DATABASE D11S SET PROPERTY
FastStartFailoverTarget='D11P';
Managing Data Guard without OEM
Create a data Guard Broker configuration using DGMGRL
utility

How to enable Fast start failover? (cont.)
 Make sure maximum protection is not set.
 Enable Fast Start Failover
DGMGRL>enable fast_start failover
 Verify configuration
DGMGRL>show fast_start failover
DGMGRL>show database D11P
DGMGRL>show database D11S
 Perform switchover to the standby database
DGMGRL>switchover to D11S;
 Check changes to the configuration
DGMGRL>show configuration;
SQL> select database_role from v$database;
 Perform switchover to the original primary
DGMGRL>switchover to D11P;
 Check changes to the configuration
DGMGRL>show configuration;
SQL> select database_role from v$database;
Oracle Data Guard 11g Release 2 with
Oracle Enterprise Manager 11g Grid
Control
Questions?