active data guard hands on lab openworld 2009 - · pdf fileoracle active data guard page 3 of...

Active Data Guard Hands On Lab

Larry M. Carpenter Distinguished Product Manager

Oracle Active Data Guard

of 64


of 64

Active Data Guard Hands On Lab

Oracle Database 11g Release 2

MMMaaaxxxiiimmmuuummm AAAvvvaaaiiilllaaabbbiiillliiitttyyy AAArrrccchhhiiittteeeccctttuuurrreee (((MMMAAAAAA)))

OOOrrraaacccllleee BBBeeesssttt PPPrrraaaccctttiiiccceeesss FFFooorrr HHHiiiggghhh AAAvvvaaaiiilllaaabbbiiillliiitttyyy


of 64


of 64

INTRODUCTION.................................................................................................................................................... 7

OVERVIEW OF THE EXERCISES ..................................................................................................................... 9

SETUP AND CONFIGURATION ....................................................................................................................... 10

ENABLING AND USING ACTIVE DATA GUARD ........................................................................................ 11

ACTIVE DATA GUARD BENEFITS .......................................................................................................................... 12 Active Data Guard Reader Farms ................................................................................................................... 12

ENABLING ACTIVE DATA GUARD ......................................................................................................................... 13 READING FROM AN ACTIVE DATA GUARD STANDBY DATABASE ......................................................................... 14 MANAGING POTENTIAL APPLY LAGS.................................................................................................................... 16 WRITING DATA WITH AN ACTIVE DATA GUARD STANDBY ................................................................................... 21

Schema Redirecting and Active Data Guard ................................................................................................... 24

AVOIDING MEDIA CORRUPTION WITH ACTIVE DATA GUARD ............................................................................... 31

STANDBY STATSPACK AND ACTIVE DATA GUARD ............................................................................... 37

INSTALLING PRIMARY STATSPACK ....................................................................................................................... 38 INSTALLING STANDBY STATSPACK ....................................................................................................................... 40

USING ORACLE DATA PUMP WITH ACTIVE DATA GUARD ................................................................. 49

DIRECT EXPORT FROM ACTIVE DATA GUARD ...................................................................................................... 50

NETWORK EXPORT FROM ACTIVE DATA GUARD .................................................................................................. 51 NETWORK IMPORT FROM ACTIVE DATA GUARD .................................................................................................. 54

CONCLUSION ...................................................................................................................................................... 61

RESOURCES ......................................................................................................................................................... 63


of 64


of 64

Introduction

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. Data

Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby

databases to enable production Oracle databases to survive disasters and data corruptions. These standby

databases are maintained as transaction consistent copies of the production database. If the production database

becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database

to the production role, minimizing the downtime associated with the outage. Data Guard can be used with

traditional backup, restoration, and cluster techniques to provide a high level of data protection and data

availability.

With Oracle Database 11g Active Data Guard, administrators can improve production database performance by

offloading resource-intensive backup and query/reporting operations to Physical standby databases

A Data Guard configuration consists of one production database and one or more standby databases. The

databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically.

There are no restrictions on where the databases are located, provided they can communicate with each other.

For example, you can have a standby database on the same system as the production database, along with two

standby databases on other systems at remote locations.

A standby database can be:

Physical standby database

o Provides a physically identical copy of the primary database, with on disk database structures that

are identical to the primary database on a block-for-block basis. A physical standby database is

kept synchronized with the primary database, through Redo Apply, which recovers the redo data,

received from the primary database and applies the redo to the physical standby database.

Logical standby database

o Contains the same logical information as the production database, although the physical

organization and structure of the data can be different. The logical standby database is kept

synchronized with the primary database using SQL Apply, which transforms the data in the redo

received from the primary database into SQL statements and then executes the SQL statements on

the standby database. Some restrictions apply.

Active Data Guard standby database

o A Physical standby database that is open to read access with up-to-date data from the Primary

database. Ancillary writes are also possible but the writes must be redirected to a read write

database using database links.

Snapshot Standby database

o A fully read write standby database that is created by converting a physical standby database into a

Read write snapshot standby database.

o Receives and archives redo data from the primary database. A snapshot standby database does not

apply the redo data that it receives. The redo data is not applied until the snapshot standby is

converted back into a physical standby database, after first discarding any local updates made to

the snapshot standby database.

This handbook is your guide to the capabilities of Active Data Guard in Oracle Database 11g Release 2.


of 64


of 64

Overview of the Exercises

The exercises are set up to walk you through Oracle Database 11g Active Data Guard using SQL*Plus and the

Broker working with a Physical standby (Redo Apply) database using Active Data Guard to read your data as

redo is being applied while maintaining data protection. The exercises are designed from the top down so that

each step builds upon the previous exercise. It is essential that you follow the exercises in order.

The following is an outline of the exercises that you will perform in Oracle Database 11g.

Learn about Active Data Guard benefits

Learn how to enable Active Data Guard

Read data from an Active Data Guard standby database

Manage potential apply lags

Write data when using an Active Data Guard standby

Use Schema redirection with Active Data Guard

Avoid Primary database media corruption with Active Data Guard

Use Standby Statspack to examine Active Data Guard performance

Use Data Pump to extract data from an Active Data Guard standby database.

Note: While these exercises are run on a single system for simplicity, this is NOT the best practice when

implementing Data Guard.

As you go through these exercises you will find the commands that must be done for each step at each line

beginning with the word TASK.

The exercises assume that you have multiple terminal windows set up for SQL*Plus command, DGMGRL

commands or Linux commands. Each TASK line in the book list the commands (SQL*Plus, DGMGRL or

Linux) that are to be executed in one of the windows, depending on the type of command. Any DGMGRL

commands are to be executed in your DGMGRL terminal window, SQL commands in your SQL*Plus terminal

window, etc.

For the exercise, ‘Avoiding Media Corruption’, the Linux command required to introduce corruption into your

database is a precise command so be sure to enter it exactly as written. All of the tasks leading up to this step

have to be executed first.


of 64

Setup and Configuration

These exercises assume you have completed the Data Guard hands On Lab exercises and have set up your

network, tnsnames and listener files, created the primary SFO and the standby NYC.

As such you should have the following setup already.

A primary database called SFO

A standby database called NYC

A tnsnames.ora file with two connect identifiers

o SFO pointing to the SFO database using a service called SFO

o NYC pointing to the NYC standby database using a service called NYC

A running listener and listener.ora file on both systems (assuming two systems) with the Broker Static

entries defined.

o SFO.domain_DGMGRL for the SFO database

o NYC.domain_DGMGRL for the NYC database

A Broker configuration.

These exercises can be adapted to your environment.


of 64

Enabling and Using Active Data Guard

Oracle Active Data Guard – “The simplest solution to increase performance by offloading read-only workloads to

a synchronized replica of the production database as well as enhance protection from media failures.”

Oracle Active Data Guard is an option for Oracle Database 11g Enterprise Edition. It enhances Quality of Service

by offloading resource intensive workloads from your production database to one or more synchronized standby

databases. This is accomplished by enabling read-only access to a physical standby database for queries, real-

time reporting, web-based access, etc., while continuously applying changes received from the production

database. Active Data Guard also eliminates the overhead of performing backups on production systems by

enabling RMAN block-change tracking and fast incremental backups using a physical standby database and helps

your applications avoid media failures by enabling the automatic correction of corrupted blocks on your

Production and Active Data Guard standby databases.

An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database,

improving performance and scalability while preventing data loss or downtime due to data corruptions, database

and site failures, human error, or natural disaster. Active Data Guard is a Database Option for Oracle Enterprise

Edition that is separately licensed from your normal Oracle Database 11g Enterprise Edition license. An Active

Data Guard license is required when using :

Real-time Query

RMAN block-change tracking on a standby database

Active Data Guard is 100% compatible with new Data Guard functionality included with Oracle Database 11g

Enterprise Edition.

The following diagram presents a high level overview of Oracle Active Data Guard.


of 64

Active Data Guard Benefits

Increase performance of production database: Offload unpredictable workloads.

Simplify operations: Eliminate traditional replication management complexity.

Eliminate compromise: Reporting replica is up-to-date and online at all times.

Reduce cost: Provides disaster protection and high availability and can serve as a QA system.

Reduce backup time: Complete incremental backups on the standby up to 20x faster.

Automatic Block Media Recovery: Repair corrupted blocks without returning an error to the application

Active Data Guard Reader Farms

In the example of a Web-business, there is frequently a need to scale out performance to handle catalog queries,

order lookup, and other read-only activities that can vary widely depending upon the time of year or other special

circumstances that lead to sudden peaks in volume. Active Data Guard is uniquely suited for these situations,

because standby databases can easily be provisioned to handle peak periods. A single production database can

support direct connections to up to 30 standby databases (nine only prior to Oracle Database 11g Release 2),

creating what is referred to as a Reader Farm.

The figure below shows an example of a simple Reader Farm. In addition, because Active Data Guard is

compatible with Data Guard functionality - the Reader Farm pictured below has data protection and high

availability already built-in. If the production database fails, any of the standby databases in the configuration

can quickly transition to the production role - automatically keeping the remaining standby databases

synchronized with the latest transactions.


of 64

Enabling Active Data Guard

Enabling a Physical standby database for Active Data Guard is so simple that you may be disappointed that this

exercise is so short and so easy. But that is the point, Active Data Guard is easy. To be able to read from your

Physical standby database all you have to do is stop Redo Apply, open the Physical standby database Read Only

and restart Redo Apply. If you were not using the Broker the steps would be as follows on your Physical

standby database.

Even if you are using the Data Guard Broker you could still do the above commands anyway and the Broker

would recognize that the apply has been restarted. But best practices when using the Data Guard Broker is to

perform Data Guard changes through the Broker, using DGMGRL or Grid Control (Active Data Guard is

configurable from Grid Control 10.2.0.5 onwards). To enable Active Data Guard with the Broker:

After this, your Physical standby is open and ready for readers all the while new redo is being applied. However,

as of Oracle Database 11g Release 2 the Broker will take care of the stopping and restarting of Redo Apply for

you whenever you execute an open command on a Physical Standby that is under Broker control.

TASK:

Execute the following command in the NYC SQL*Plus window

o connect sys/oracle@nyc as sysdba;

o alter database open;

SQL> alter database recover managed standby database cancel;

SQL> alter database open read only;

SQL> alter database recover managed standby database using

current logfile disconnect;

DO NOT EXECUTE THESE COMMANDS

DGMGRL> connect sys/oracle@nyc

DGMGRL> edit database nyc set state=’apply-off’;

SQL> connect sys/oracle@nyc

SQL> alter database open read only;

DGMGRL> connect sys/oracle@nyc

DGMGRL> edit database nyc set state=’apply-on’;


SQL> connect sys/oracle@nyc as sysdba;

Connected.

SQL> alter database open;

Database altered.


of 64

If you monitor the alert log from the standby NYC you will see that when the open is executed, Redo Apply is

canceled, the database opened in Read Only mode and Redo Apply restarted automatically. You can also verify

that the standby is open using Active Data Guard by executing a DGMGRL “show database nyc” command.

TASK:

Execute the following command in the DGMGRL window

o dgmgrl sys/oracle@nyc

o show database nyc;

Reading from an Active Data Guard Standby Database

To show that you can read the data on the Physical Standby database as it is being applied, you can add some

information to a table in the Primary database and read that data from the Physical Standby database. To begin,

log into the Physical standby NYC as HR (password oracle) and read the HR.REGIONS table.

TASK:


o connect hr/oracle@nyc

o select * from hr.regions;

SQL> connect hr/oracle@nyc

Connected.

SQL> select * from hr.regions;

REGION_ID REGION_NAME

---------- -------------------------

1 Europe

2 Americas

3 Asia

4 Middle East and Africa

SQL>

bash-3.2$ dgmgrl sys/oracle@NYC

Connected.

DGMGRL> show database nyc;

Database - nyc

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: ON

Instance(s):

NYC

Database Status:

SUCCESS


of 64

On the Primary database SFO, connect as HR and add a row to the HR.REGIONS table.

TASK:

Execute the following command in the SFO SQL*Plus window

o connect hr/oracle@sfo

o insert into HR.REGIONS values (30,'OpenWorld');

o commit;

To verify that the data has been applied at the standby and is visible return to your physical standby NYC and

rerun the select command.

TASK:




You have now used Active Data Guard to read up-to-date data from your Physical standby database.

SQL> connect hr/oracle@sfo

Connected.

SQL> insert into HR.REGIONS values (30,'OpenWorld');

1 row created.

SQL> commit;

Commit complete.

SQL>


Connected.



---------- -------------------------

1 Europe

2 Americas

3 Asia


30 OpenWorld

SQL>


of 64

Managing Potential Apply Lags

When you use real-time query to offload queries from a primary database to a physical standby database, you

may want to monitor the apply lag to ensure that it is within acceptable limits. The current apply lag is the

difference, in elapsed time, between when the last applied change became visible on the standby and when that

same change was first visible on the primary. This metric is computed to the nearest second. To obtain the apply

lag, query the V$DATAGUARD_STATS view.

TASK:


o connect sys/oracle@nyc as sysdba

o select name, value, datum_time, time_computed from v$dataguard_stats where name like ‘apply

lag’;

The apply lag metric is computed using data that is periodically received from the primary database.

DATUM_TIME contains a timestamp of when this data was last received by the standby database.

TIME_COMPUTED contains a timestamp taken when the apply lag metric was calculated.

An unchanging value of the DATUM_TIME column across multiple queries indicates that the standby database

is not receiving data from the primary database. To obtain a histogram that shows the history of apply lag values

since the standby instance was last started, query the V$STANDBY_EVENT_HISTOGRAM view.

TASK:


o select * from v$standby_event_histogram where name = 'apply lag' and count > 0;

SQL> connect sys/oracle@NYC as sysdba

Connected.

SQL> SELECT name, value, datum_time, time_computed

2 FROM V$DATAGUARD_STATS WHERE name like 'apply lag';

NAME VALUE DATUM_TIME TIME_COMPUTED

--------- ------------ -------------------- -------------------

apply lag +00 00:00:00 08/05/2010 13:14:11 08/05/2010 13:14:11

SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM

2 WHERE NAME = 'apply lag' AND COUNT > 0;

NAME TIME UNIT COUNT LAST_TIME_UPDATED

------------ ---------- ------ -------------------

apply lag 0 seconds 48612 08/05/2010 13:20:02





of 64

These two system views are useful for monitoring how up to date the data in an Active Data Guard standby is

compared to the data in the Primary database. The problem is how do your applications know how recent is the

data they are retrieving from an Active Data Guard standby when they may not have access to these views

directly? Active Data Guard now has a new session parameter called STANDBY_MAX_DATA_DELAY that can

be used to specify a session-specific apply lag tolerance, measured in seconds, for queries issued by non-

administrative users to a physical standby database that is in real-time query mode. This capability allows queries

to be safely offloaded from the primary database to a physical standby database, because it is possible to detect if

the standby database has become unacceptably stale.

You would use ALTER SESSION to set STANDBY_MAX_DATA_DELAY to a value of your choosing based on

the service level agreement (SLA) of the particular user. The rules for STANDBY_MAX_DATA_DELAY are as

follows.

If set to the default value of NONE, queries issued to a physical standby database will be executed

regardless of the apply lag on that database.

If set to a non-zero value, a query issued to a physical standby database will be executed only if the apply

lag is less than or equal to STANDBY_MAX_DATA_DELAY.

o Otherwise, an ORA-3172 error is returned to alert the client that the apply lag is too large.

If set to 0, a query issued to a physical standby database is guaranteed to return the exact same result as if

the query were issued on the primary database

o If the standby database is lagging behind the primary database an ORA-3172 error is returned.

o Note: Using zero has three requirements which if not met returns the ORA-3172 immediately:

1. The configuration must be in Maximum Availability or Maximum Protection

2. The Active Data Guard standby must be receiving the redo using SYNC.

3. Redo Apply must be active.

To show this functionality set an SLA of 2 seconds, which means that if the data returned from the Active Data

Guard standby is more than 2 seconds behind the Primary return an error rather than the stale data.


Connected.

SQL> ALTER SESSION SET STANDBY_MAX_DATA_DELAY=2;

Session altered.

SQL> select * from regions;


---------- -------------------------

1 Europe

2 Americas

3 Asia


30 OpenWorld

5 rows selected.


of 64

TASK:



o alter session set standby_max_data_delay=2;

o select * from regions;

To cause an artificial delay you can stop redo apply on your Standby NYC. Return to your DGMGRL window

and change the ‘state’ of NYC to ‘APPLY-OFF’ which will cancel Redo Apply.

TASK:


o edit database nyc set state=‘apply-off’;

Now return to your SQL*Plus window on NYC and re-execute the previous select statement again.

TASK:



To remove the artificial delay restart redo apply on your Standby NYC. Return to your DGMGRL window and

change the ‘state’ of NYC to ‘APPLY-ON’.

TASK:


o edit database nyc set state=‘apply-on’;

DGMGRL> edit database NYC set state='apply-off';

Succeeded.


select * from regions

*

ERROR at line 1:

ORA-03172: STANDBY_MAX_DATA_DELAY of 2 seconds exceeded

DGMGRL> edit database NYC set state='apply-on';

Succeeded.


of 64

Return to your SQL*Plus window on NYC and re-execute the previous select statement for the 3rd

time.

TASK:



From these simple examples you can see how your applications can control the amount of lag between what they

see in the Standby and where the Primary database actually is at every time they perform a query.

It is also possible for your application to be configured in a manner where it will not begin to allow queries to

occur until the standby is in sync with the Primary. To do this the application can execute a new session

statement “ALTER SESSION SYNC WITH PRIMARY”.

This statement will block until all redo data received by the standby database at the time that this command is

issued has been applied to the physical standby database. Note that an ORA-3173 error is returned immediately,

and synchronization will not occur, if the same three requirements as setting the session parameter

STANDBY_MAX_DATA_DELAY to ZERO are not met. (Maximum Protection/Availability, SYNC transport to

this standby and Redo Apply is active).

You can ensure that Redo Apply synchronization occurs in specific cases by using a logon trigger which contains

the SYS_CONTEXT('USERENV','DATABASE_ROLE') function to create a standby-only trigger. For example,

you could create the following trigger on the Primary database (which would be sent to the standby databases and

applied by Redo Apply) that would execute the ALTER SESSION SYNC WITH PRIMARY statement for a specific

user connection at logon.



---------- -------------------------

1 Europe

2 Americas

3 Asia


30 OpenWorld

5 rows selected.

SQL> CREATE OR REPLACE TRIGGER hr_logon_sync_trigger

2 AFTER LOGON ON hr.schema

3 BEGIN

4 IF (SYS_CONTEXT('USERENV','DATABASE_ROLE')

5 IN ('PHYSICAL STANDBY'))

6 THEN

7 execute immediate 'alter session sync with primary';

8 END IF;

9 END;

10 /



of 64

Now when you log into NYC as the user HR the trigger would attempt to synchronize the session with the

Primary, and in our case, return an error and disallow the logon since we do not meet the 3 requirements.

Note: There is no TASK for this exercise as you would no longer be allowed to login as HR!

Remember, as with STANDBY_MAX_DATA_DELAY set to zero, the ALTER SESSION SYNC WITH

PRIMARY statement has the same three requirements which, if not met, will return the ORA-3173 immediately:

The configuration must be in Maximum Availability or Maximum Protection

The Active Data Guard standby must be receiving the redo using SYNC.

Redo Apply must be active.

Since the configuration is running in Maximum Performance having the above trigger would cause the error and

you would no longer be allowed to log into the HR user since the standby is never considered ‘synchronized’

with the Primary database.


ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-03173: Standby may not be synced with primary

ORA-06512: at line 5


SP2-0640: Not connected



of 64

Writing data with an Active Data Guard standby

When connected to an Active Data Guard standby database, read-only applications can perform:

Selects

Alter session / system

Set role

Lock table

Call stored procedures

DBlinks to write to remote databases

Stored procedures to call remote procedures via DBlinks

SET TRANSACTION READ ONLY for transaction level read consistency

Complex queries e.g. grouping set queries and with clause queries

If an application has the capability of directing read operations to the Active Data Guard service running on the

Physical standby database and write operations to the Primary database then all you need to do is define the

services to start on the correct database and the application will automatically offload the read workload to the

Physical standby database.

However, as of Oracle Database 11g Release 1 with Active Data Guard, write operations can be performed on a

Physical Standby that is opened read only, through the use of Database Links. The links are created on the

Primary database and are applied to the Physical Standby through Redo Apply. They can point back to the

Primary or to another database where the application has read write access. In this manner a primarily read

application that has a need to make a few writes can make those writes on the standby and they will be

automatically redirected to the read write database. If the applications require access to the data that is updated

on the Active Data Guard standby then your database link must point to the Primary database so that the data is

available to all users.

To begin with log into the Primary SFO as SYS ( AS SYSDBA) again and create a database link that uses ‘sfo’

as the connect string. The redo generated by this DDL will be sent to the physical standby where Redo Apply

will apply the redo making the database link available to the application running on the physical standby.

TASK:

Execute the following command in the SFO SQL*Plus window

o connect sys/oracle@sfo as sysdba;

o create public database link write_ADG connect to HR identified by oracle using ‘SFO’;

SQL> connect sys/oracle@sfo as sysdba;

Connected.

SQL> create public database link write_ADG connect to HR

2 identified by oracle using 'SFO';

Database link created.

SQL>


of 64

Now, connecting back to the standby, select the rows in the HR.REGIONS table again where the data will be

displayed as it appears in the standby.

TASK:




This time we will insert a new row into the HR.REGIONS table using the database link while connected

to the Physical standby. The actual DML will be redirected to the Primary database and once the COMMIT is

executed redo will be generated and sent to the Physical standby. Redo Apply will apply the redo making the

new data available to the application.

TASK:


o insert into REGIONS@write_ADG values (99, ‘Data Guard HOL’);

o commit;


Connected.



---------- -------------------------

1 Europe

2 Americas

3 Asia


30 OpenWorld

SQL> insert into REGIONS@write_ADG values (99, 'Data Guard HOL');

1 row created.

SQL> commit;

Commit complete.


of 64

Finally, select the rows in the HR.REGIONS table again while connected to the Physical standby. The new data

will be displayed as it now appears in the standby.

TASK:



How fast the data appears in the Physical standby depends on the workload on your Primary database, how fast

the redo can be sent to the Physical standby and how well Redo Apply is performing. You can tune these factors

by following the MAA papers for tuning Redo Transport and Apply. Refer to the Resources section at the end of

this book for the links to the MAA papers.



---------- -------------------------

1 Europe

2 Americas

3 Asia


30 OpenWorld

99 Data Guard HOL

6 rows selected.


of 64

Schema Redirecting and Active Data Guard

You do have to be careful when setting up your database links and synonyms as it could be a performance

problem if normal read or write access to a table on the Primary goes out over the network and back again, or a

write on the standby goes over the link to the Primary but so do the reads, which defeats the purpose of having an

Active Data Guard standby database. It is now possible to use schema redirection to make it easier to code

applications so that reads are always local and writes only go out over the database link when you are connected

to an Active Data Guard standby.

This method does require changing your application to use different names for reading and writing to the tables

so that the reads can be done locally and the write redirected only when the application attaches to the Active

Data Guard standby.

To do this you will have to setup new users/schemas which your application will be switched to with a login

trigger whenever they connect to an Active Data Guard standby database. Then by using synonyms and database

links reads can be local and writes are transparently redirected to the Primary.

But, if the application connects to the Primary database you do not want it using any of the database links, for

reads or writes. So your trigger will have to ensure, like the previous logon trigger we discussed, that the schema

is only redirected when the application logs into the standby.

TASK:

Execute the following commands in the SFO SQL*Plus window

o connect sys/oracle@sfo as sysdba

o create or replace trigger hr_logon_switch_schema_trigger

after logon on hr.schema

begin

if (sys_context(‘userenv’,‘database_role’) in (‘PHYSICAL STANDBY’))

then

execute immediate

‘alter session set current_schema = hr_syn’;

end if;

end;

/

SQL> connect sys/oracle@sfo as sysdba

Connected.

SQL> CREATE OR REPLACE TRIGGER hr_logon_switch_schema_trigger

2 AFTER LOGON ON hr.schema

3 BEGIN

4 IF (SYS_CONTEXT('USERENV','DATABASE_ROLE')

5 IN ('PHYSICAL STANDBY'))

6 THEN

7 execute immediate

8 'alter session set current_schema = hr_syn';

9 END IF;

10 END;

11 /

Trigger created.


of 64

Now when your application logs into HR on the Primary you will be running in the HR schema but when it logs

into HR on the standby your context will be switched to the HR_SYN schema.

That takes care of the schema redirect. But now you need to create the user, grant the necessary privileges, and

create appropriate synonyms in the schemas that your application will use when reading or writing to the tables.

In our case we will do this for the HR_SYN and HR and only for the HR.REGIONS table.

TASK:


o connect sys/oracle@sfo as sysdba

o create user hr_syn identified by oracle account unlock;

o grant connect, resource, create session, create synonym to hr_syn;

o create public database link hr_primary using ‘sfo’;

With the user created and the trigger in place you would now see a different schema depending on the role of the

database as follows:

TASK:



o select sys_context(‘USERENV’,’CURRENT_SCHEMA’) from dual;

Execute the following commands in the NYC SQL*Plus window


o select sys_context(‘USERENV’,’CURRENT_SCHEMA’) from dual;

SQL> connect sys/oracle@sfo as sysdba

Connected.

SQL> create user hr_syn identified by oracle account unlock;

User created.

SQL> grant connect, resource, create session, create synonym to hr_syn;

Grant succeeded.

SQL> create public database link hr_primary using 'sfo';



SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;

HR


SQL> select sys_context('USERENV','CURRENT_SCHEMA') from dual;

HR_SYN


of 64

The next step is to allow our new user to create synonyms in the HR schema and create the same two synonyms

that the application will be changed to use regardless of which database they are using.

TASK:



o grant all on regions to hr_syn;

o create synonym r_regions for hr.regions;

o create synonym w_regions for hr.regions;

If these two synonyms are not created for the normal HR user, your modified application will not be able to

function if it has to be attached to the Primary database. You will see in a moment that when you read and write

data with your ‘application’ you will be modifying the ‘application’ (our SQL*Plus queries) to use these two

synonyms. But, remember, the trigger you defined will point your application to the HR_SYN schema when it

logs into the standby, and the synonyms would be visible. But when your application logs into the Primary the

schema ‘redirect’ would not take place and these synonyms would not be available unless you define them ahead

of time. In this case the reads and writes will take place on the REGIONS table directly since you would be

attached to the Primary database.

Now when our application connects to the Primary database, all activity with the HR.REGIONS table will be

local to the Primary.


Connected.

SQL> grant all on regions to hr_syn;

Grant succeeded.

SQL> create synonym r_regions for hr.regions;

Synonym created.

SQL> create synonym w_regions for hr.regions;

Synonym created.


of 64

The last step is to do the same for the HR_SYN schema. But in this case any reads that are directed to the

r_regions synonym will be read from the standby while any updates to the w_regions synonym will

automatically be sent to the Primary database using our database link.

TASK:


o connect hr_syn/oracle@sfo

o create synonym r_regions for hr.regions;

o create synonym w_regions for hr.regions@hr_primary;

Finally we are ready to point our application (which we have changed to use our new synonyms) at our Active

Data Guard standby and begin reading and writing data. Reconnect to the HR user on your standby and try to

read the REGIONS table again. You will see that it is no longer visible because your default schema is now

HR_SYN because of the logon trigger. But if you use the synonym you created, r_regions, you will see the

REGIONS data directly from the standby database.

TASK:



o select * from regions where region_name=‘OpenWorld’;

o select * from r_regions where region_name=‘OpenWorld’;

SQL> connect hr_syn/oracle@sfo

Connected.

SQL> create synonym r_regions for hr.regions;

Synonym created.

SQL> create synonym w_regions for hr.regions@hr_primary;

Synonym created.

SQL> connect hr/oracle@nyc;

Connected.

SQL> select * from regions where region_name='OpenWorld';

select * from regions

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select * from r_regions where region_name='OpenWorld';


---------- -------------------------

30 OpenWorld


of 64

Trying to update the data using the r_regions synonym ends up with an ORA-16000 error as you can see below.

TASK:


o update r_regions set region_id=88 where region_name=‘OpenWorld’;

When you retry the update command using the ‘write’ synonym w_regions, you will be able to update the data

because the DML is actually being transparently sent to the Primary database via the database link and will be

visible in the standby once the redo has been transmitted and applied to the physical standby.

TASK:


o update w_regions set region_id=88 where region_name=‘OpenWorld’;

o commit;


While this process requires some setup and configuration and modifications to your application, it does provide a

method to make an application that requires some updating capability function normally even when attached to a

Read Only Active Data Guard standby database.

SQL> update w_regions set region_id=88 where region_name='OpenWorld';

1 row updated.

SQL> commit;

Commit complete.



---------- -------------------------

88 OpenWorld

SQL> update r_regions set region_id=88 where region_name='OpenWorld';

update r_regions set region_id=88 where region_name='OpenWorld'

*

ERROR at line 1:

ORA-16000: database open for read-only access


of 64

But what happens if your ‘application’ connects directly to the Primary? Or perhaps you did a failover or

switchover and what was your Active Data Guard standby is now the Primary database. How will your

application function now? It will work as advertised and not use any of the database links you have defined as it

will no longer be switched to the HR_SYN schema. Logging back into the current primary you would see the

following.

TASK:



o select sys_context(‘USERENV’,‘CURRENT_SCHEMA’) "Who Am I?" from dual;

So now when we rerun all of the ‘application’ commands you will see somewhat different results as shown below

now that we are connected to the Primary database.

TASK:





Connected.

SQL> select sys_context('USERENV','CURRENT_SCHEMA') "Who Am I?"

2 from dual;

Who Am I?

--------------------------------

HR



---------- -------------------------

88 OpenWorld



---------- -------------------------

88 OpenWorld


of 64

As well as all the update commands since we are on the Primary and can update both the r_regions and

w_regions as they both point to the local base table in a read write database.

TASK:


o update r_regions set region_id=77 where region_name=‘OpenWorld’;

o commit;


o update w_regions set region_id=66 where region_name=‘OpenWorld’;

o commit;


Not only does the table REGIONS exist again but you can read AND write to R_REGIONS as well as

W_REGIONS. You can do this because you are connected to a Read Write database in the HR schema.

SQL> update r_regions set region_id=77 where region_name='OpenWorld';

1 row updated.

SQL> commit;

Commit complete.



---------- -------------------------

77 OpenWorld

SQL> update w_regions set region_id=66 where region_name='OpenWorld';

1 row updated.

SQL> commit;

Commit complete.



---------- -------------------------

66 OpenWorld


of 64

Avoiding Media Corruption with Active Data Guard

Oracle Database 11g Release 2 now has the capability to automatically repair corrupt data blocks in your

production database as soon as the corruption is detected by using your Active Data Guard standby database to

retrieve good copies of the corrupted blocks. Automatic Block Media Recovery will also automatically repair

corrupted blocks that are discovered in your physical standby databases. This feature reduces the amount of time

that data is inaccessible due to block corruption and will avoid returning errors to your application.

This reduces block recovery time by using up-to-date good blocks in real-time, as opposed to retrieving blocks

from disk or tape backups, or from Flashback logs. The process is as follows:

If a corrupt data block is discovered on a Primary database.

o If you have a physical standby database open read only with Active Data Guard then the standby

can be used to repair corrupt data blocks in the primary database.

If possible, any corrupt data block encountered when a primary database is accessed is

automatically replaced with an uncorrupted copy of that block from the Active Data Guard

physical standby database.

An ORA-1578 error is returned when automatic repair is not possible which would be the

case if the corrupted block was the header record for example.

If a corrupt data block is discovered on a Physical standby database.

o The server attempts to automatically repair the corruption by obtaining a copy of the block from

the primary database. This requires that the following database initialization parameters are

configured on the standby database.

LOG_ARCHIVE_CONFIG parameter with a DG_CONFIG list

LOG_ARCHIVE_DEST_n parameter for the primary database

o In any standby database configuration these parameters should always be defined. Since your

configuration is being managed by the Broker these parameters are defined.

You can also manually repair a corrupted data block by using the RMAN RECOVER BLOCK command. This

command searches several locations for an uncorrupted copy of the data block. By default, one of the locations is

any available physical standby database that is open with Active Data Guard.

You could then use RMAN to recover just that one block.

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 139)

ORA-01110: data file 5:

'/hol/oracle/oradata/SFO/datafile/o1_mf_example_5bmrk40x_.dbf'


RMAN> recover datafile ‘…/o1_mf_example_5bmrk40x_.dbf' block 139;

Starting recover at 05-Aug-10

using target database control file instead of recovery catalog

finished standby search, restored 1 blocks

…

Finished recover at 05-Aug-10



of 64

To show how this feature works we need to disable Active Data Guard so that Automatic Block Media Recovery

cannot happen at first. In this manner your ‘application’ does get the error because the corruption cannot

automatically be recovered. Stopping Redo Apply is sufficient to take the standby NYC out of Active Data

Guard mode.

TASK:

Execute the following commands in the DGMGRL window

o edit database nyc set state=‘apply-off’;

At this point you will corrupt a block in the REGIONS table of the Primary database. You will do this by

executing a ‘dd’ command that copies the first data block (skipping the header block) of the REGIONS table over

the next block in the table. (We know where the REGIONS table is by looking at DBA_SEGMENTS).

TASK:

Execute the following commands at the Linux prompt

o dd if=/u01/app/oradata/SFO/example01.dbf of=/u01/app/oradata/SFO/example01.dbf count=1

bs=8192 skip=141 seek=140 conv=notrunc

To ensure that the database is forced to reread the block from disk we will flush the buffer cache of the Primary

database SFO.

TASK:


o alter system flush buffer_cache;

That will have corrupted the 2nd

block in the REGIONS table so when you go back to the Primary SFO and try to

select the rows from the REGIONS table as the HR user, you will get an error since the Standby is not open in

Active Data Guard at the moment.

DGMGRL> edit database nyc set state=apply-off;

Succeeded.

bash-3.1$ dd if=/u01/app/oradata/SFO/example01.dbf

of=/u01/app/oradata/SFO/example01.dbf count=1 bs=8192 skip=141

seek=140 conv=notrunc

1+0 records in

1+0 records out

8192 bytes (8.2 kB) copied, 9.7e-05 seconds, 84.5 MB/s

SQL> alter system flush buffer_cache;

System Altered

SQL>


of 64

TASK:




Since we have the apply services stopped at the standby, effectively turning off Active Data Guard, the block

cannot be repaired. Returning to the NYC window and restarting the redo apply services will put our standby

back into Active Data Guard mode.

TASK:

Execute the following commands in the DGMGRL window

o edit database nyc set state=‘apply-on’;

At this point the corrupted block on the Primary database has still not been repaired because it is not being

accessed at present. But we can read the REGIONS table in the standby database without any errors.

TASK:





Connected.


select * from hr.regions

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 140)

ORA-01110: data file 5: '/u01/app/oradata/SFO/example01.dbf '

DGMGRL> edit database nyc set state=apply-on;

Succeeded.


Connected.



---------- -------------------------

1 Europe

2 Americas

3 Asia


66 OpenWorld

99 Data Guard HOL


of 64

You can read the REGIONS table in the standby without any errors because Data Guard does NOT propagate

these kinds of media failures to its standby databases.

Monitor the apply status of your standby NYC and make sure that Redo Apply has caught up before continuing.

TASK:


o connect sys/oracle@nyc as sysdba;

o select status from v$managed_standby where process like ‘MRP%’;

When the ‘STATUS’ returned is ‘APPLYING_LOG’ you can continue. Continue to execute the select statement

until it matches the output above.

Now that Active Data Guard is fully enabled again you can return to your Primary where you originally executed

the select command on REGIONS and got the error. Re-execute the command again and you will see that the

read will succeed.

TASK:





---------- -------------------------

1 Europe

2 Americas

3 Asia


66 OpenWorld

99 Data Guard HOL

6 rows selected.

SQL> connect sys/oracle@nyc as sysdba;

SQL> select status from v$managed_standby

2 where process like ‘MRP%’;

STATUS

------------

APPLYING_LOG


of 64

The error is not returned the 2nd

time because Automatic BMR was initiated in the background to recover the

corrupted block with a good copy from the Active Data Guard Standby. You can see this happening by

examining the alert log of the Primary SFO.

TASK:

Execute the following in the SFO Alert log window

o Look for the first ‘Automatic block media recovery’ line.

Transparent to your ‘application’ the corrupted block was fixed and the results of your query are returned. If the

physical standby had already been opened in Active Data Guard when you first executed the select statement you

would never have seen the ORA-01578 error as the corrupted block would have been fixed immediately.

Tue Sep 06 16:48:49 2011

ABMR started with pid=33, OS id=16571

Automatic block media recovery service is active.

Automatic block media recovery requested for (file# 5, block# 140)

Tue Sep 06 16:48:51 2011

Automatic block media recovery successful for (file# 5, block# 140)

Automatic block media recovery successful for (file# 5, block# 140)

WARNING: AutoBMR fixed mismatched on-disk block 140008d

with in-mem rdba 140008c.


of 64


of 64

Standby Statspack and Active Data Guard

There are two areas that may require tuning exercises on an Active Data Guard standby database, Redo Apply

performance and Query performance. Normally the Automatic Workload Repository (AWR) would be used to

gather information about the performance on a database but as AWR writes its information back to the database it

cannot be used on an Active Data Guard standby which is open read-only. In order to gather performance

information on an Active Data Guard standby you need to configure ‘Standby Statspack’. With Active Data

Guard, you can now use statspack from the primary database to collect data from a standby database that is

opened read-only and performing recovery and queries. Like AWR, Statspack cannot be executed directly on a

physical standby due to its read-only nature. Therefore tuning the performance of the apply process and SQL

queries involves manually collecting statistics.

The standby statspack is installed in a separate schema on the Primary database, called STDBYPERF which is

then propagated to the standby via the normal Data Guard mechanism. This new user does not require (nor have)

DBA privileges and has no access to local V$ tables. For the purposes of this exercise you will install standby

statspack and examine the statistics for Redo Apply on your standby database.

This lab exercise is not designed to go into depth on performing these tuning procedures. Extensive tuning

procedures for Redo Apply and Query performance are covered in detail in the MAA best practices paper

“Active Data Guard 11g Best Practices” in Appendices C and D.

Statspack has several procedures most of which have been duplicated for Standby Statspack. The ones we are

interested in are as follows.

spcreate - Creates the STATSPACK user PERFSTAT, tables and package

sbcreate - Creates the STATSPACK user STDBYPERF, tables and package

sbaddins - Adds Active Data Guard instances to be monitored

sbreport - Produces the Standby Statspack report

There are three other procedures that you may want to use that are not discussed nor used in this exercise.

However their use is self explanatory. They are:

sbpurge - Purge a range of Snapshots

sbdelins - Removes a standby database instance for performance data collection

sbdrop - Drops the STATSPACK user STDBYPERF, tables and package

Note: All scripts executed during the installation and configuration of Statspack and Standby Statspack are in the

directory $ORACLE_HOME/rdbms/admin/. Before you start make sure the SFO SQL*Plus window is logged in

as SYS first.

TASK:


o connect sys/oracle@SFO as sysdba

SQL> connect sys/oracle@SFO as sysdba

Connected

http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr1-activedataguard-1-128199.pdf


of 64

Installing Primary Statspack

Before you can install Standby Statspack your Primary database must first have Statspack installed and

configured. The Primary database ‘SFO’ does not yet have Statspack installed so you must first run the standard

Statspack installation script which creates the ‘perfstat’ user for performance gathering of the Primary database.

This is done by logging into the Primary database as SYS and executing the ‘spcreate.sql’ script.

TASK:


o @$ORACLE_HOME/rdbms/admin/spcreate

o Enter oracle as the password and press the Enter key

The script will then ask for tablespace names for the Statspack user and packages as well as a temporary

tablespace. The defaults are SYSAUX and TEMPORARY which is what we will choose. The output has been

edited to save space.

TASK:

Execute the following in the SFO SQL*Plus window

o Press the Enter key to use the default

SQL> @$ORACLE_HOME/rdbms/admin/spcreate

Choose the PERFSTAT user's password

-----------------------------------

Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: oracle

oracle

Choose the Default tablespace for the PERFSTAT user

---------------------------------------------------

Choose the PERFSTAT users's default tablespace. This is the

tablespace

in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE

------------------------------ --------- ----------------------------

EXAMPLE PERMANENT

SYSAUX PERMANENT *

USERS PERMANENT

Pressing <return> will result in STATSPACK's recommended default

tablespace (identified by *) being used.

Enter value for default_tablespace:

Using tablespace SYSAUX as PERFSTAT default tablespace.


of 64

TASK:



When Statspack has been successfully configured you will see a completion message in the output for each of the

three packages that ‘spcreate’ executes.

TASK:

Examine the list files identified if any errors were encountered

Statspack has now been configured on your Primary database ‘SFO’.

Choose the Temporary tablespace for the PERFSTAT user

-----------------------------------------------------

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE

------------------------------ --------- --------------------------

TEMP TEMPORARY *

Pressing <return> will result in the database's default Temporary


Enter value for temporary_tablespace:

Using tablespace TEMP as PERFSTAT temporary tablespace ...

NOTE:

SPCUSR complete. Please check spcusr.lis for any errors.

...

NOTE:

SPCTAB complete. Please check spctab.lis for any errors.

...

NOTE:

SPCPKG complete. Please check spcpkg.lis for any errors.

SQL>


of 64

Installing Standby Statspack

The procedure for installing Standby Statspack is almost identical to the standard Statspack installation but using

a different unique user for the Standby Statspack. In addition the procedure asks for at least one standby to

include as a monitoring target. You can add additional Active Data Guard standby databases that you want to

monitor by running the end script multiple times. The Standby Statspack procedures all start with ‘sb’ instead of

the ‘sp’ as in normal Statspack. Make sure you are logged in a SYS again first.

TASK:



o @$ORACLE_HOME/rdbms/admin/sbcreate

o Enter oracle as the password and press the Enter key

As with the normal Statspack installation, the script will then ask for tablespace names for the Standby Statspack

user and packages as well as a temporary tablespace. The defaults are SYSAUX and TEMPORARY which is

what we will choose. The output has been edited to save space.

TASK:




Connected

SQL> @$ORACLE_HOME/rdbms/admin/sbcreate

Choose the STDBYPERF user's password

-----------------------------------

Not specifying a password will result in the installation FAILING

Enter value for stdbyuser_password: oracle

oracle

Choose the Default tablespace for the STDBYPERF user

---------------------------------------------------

Choose the STDBYPERF users's default tablespace ...

TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE

------------------------------ --------- ----------------------------

EXAMPLE PERMANENT

SYSAUX PERMANENT *

USERS PERMANENT

Pressing <return> will result in STATSPACK's recommended default


Enter value for default_tablespace:

Using tablespace SYSAUX as STDBYPERF default tablespace.


of 64

TASK:



The script will continue as it did for standard Statspack and you will see similar messages in the output as before

for the first 2 procedures, ‘sbcusr’ and ‘sbctab’.

TASK:


The 3rd

procedure, ‘sbcpkg’, has an added procedure call ‘sbaddins’ (Standby Statspack Add Instance) where it

will ask for at least one Active Data Guard standby instance for Standby Statspack to monitor. The procedure

will first display any previously configured standby instances and then ask if you want to add a new Active Data

Guard standby database to Standby Statspack. In the initial run there are no configured instances.

Choose the Temporary tablespace for the STDBYPERF user

-----------------------------------------------------

Choose the STDBYPERF user's Temporary tablespace.

TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE

------------------------------ --------- --------------------------

TEMP TEMPORARY *

Pressing <return> will result in the database's default Temporary


Enter value for temporary_tablespace:

Using tablespace TEMP as STDBYPERF temporary tablespace.

NOTE:

SBCUSR complete. Please check sbcusr.lis for any errors.

...

NOTE:

SBCTAB complete. Please check sbctab.lis for any errors.

...

The following standby instances (TNS_NAME alias) have been configured

for data collection

=== END OF LIST ===


of 64

You will be asked if you want to add a new instance. In our case since this is the first time the procedure has

been run there are no monitored Active Data Guard standby databases so we must enter the first one.

TASK:


o Enter ‘y’ and press the Enter key

TASK:


o Enter ‘NYC’ and press the Enter key

After identifying the Active Data Guard standby you want to monitor, you have to enter the password for the

standard Statspack user “PERFSTAT” that you created previously. In your case it should be ‘oracle’.

TASK:


o Enter ‘oracle’ and press the Enter key

THE INSTANCE YOU ARE GOING TO ADD MUST BE ACCESSIBLE AND OPEN READ ONLY

Do you want to continue (y/n) ?

Enter value for key: y

You entered: y

Enter the TNS ALIAS that connects to the standby database instance

-----------------------------------------------------------------

Make sure the alias connects to only one instance (without load

balancing).

Enter value for tns_alias: NYC

You entered: NYC

Enter the PERFSTAT user's password of the standby database

---------------------------------------------------------

Performance data will be fetched from the standby database via

database link. We will connect to user PERFSTAT.

Enter value for perfstat_password: oracle

You entered: oracle


of 64

The ‘sbaddins’ procedure will now call the ‘sbcpkg’ and create specific packages for the Active Data Guard

standby called ‘NYC’. When complete you will see the normal end message.

TASK:


To add more Active Data Guard standby databases to your Standby Statspack configuration you would execute

the ‘sbaddins’ script multiple time, once for each additional standby. The procedure will also display what

standby databases you have already configured, in your case ‘NYC’.

TASK:


o @$ORACLE_HOME/rdbms/admin/sbaddins

o Enter ‘n’ and press the Enter key

o Note that the error is expected and you will have to log back into SQL*Plus

...

NOTE:

SBCPKG complete. Please check sbcpkg.lis for any errors.

...

SQL> @$ORACLE_HOME/rdbms/admin/sbaddins

...

The following standby instances (TNS_NAME alias) have been configured

for data collection

DATABASE INSTANCE DB LINK PACKAGE

-------- ------------ --------------- -----------------------------

NYC NYC STDBY_LINK_NYC STATSPACK_NYC_NYC

=== END OF LIST ===

THE INSTANCE YOU ARE GOING TO ADD MUST BE ACCESSIBLE AND OPEN READ ONLY

Do you want to continue (y/n) ?

Enter value for key: n

You entered: n

begin

*

ERROR at line 1:

ORA-20101: Install failed - Aborted by user



of 64

Note that the ‘Package’ listed in the above output is the PLSql procedure that will be executed to monitor the

target Active Data Guard instance. If you are only going to use Standby Statspack to monitor redo apply then

you only need to add the standby instance where redo apply will be running. However, if the standby is a RAC

and you wish to also us Standby Statspack to monitor queries you would want to add the other instances of each

standby so that you can capture snapshots from each standby instance. In our case we only have one standby and

it is a single instance so we have finished configuring Standby Statspack.

Now you can capture snapshots of the standby as redo apply and queries are running. In the final part of this

exercise you will do the following steps.

Start a snapshot of the standby NYC from the Primary SFO as the user “STDBYPERF”

Run a simple workload on SFO as the user “HR”

Cancel the workload

Stop the snapshot on NYC as the user “STDBYPERF”

Run the Standby Statspack Report procedure “sbreport”.

Examine the report file for the relevant media recovery information.

You can use the same SQL window for all of the above steps or open a new terminal window. Connect as the

“STDBYPERF” user and start the Statspack snap.

TASK:


o connect stdbyperf/oracle@SFO

o exec statspack_NYC_NYC.snap

SQL> connect stdbyperf/oracle@SFO

Connected.

SQL> exec statspack_NYC_NYC.snap

PL/SQL procedure successfully completed.


of 64

Start up a workload as the “HR” user and let it run for a period of time.

TASK:


o connect hr/oracle@SFO

o create table t1 (a int);

o begin

while 1>0 loop

insert into t1 values (1);

delete from t1;

commit;

end loop;

end;

/

Let this very simple work load run for a period of time and then cancel it using <Ctrl>C (^C).

TASK:


o Enter Control C (Hold down the <Ctrl> key and press C at the same time)

SQL> connect hr/oracle@SFO

Connected.

SQL> create table t1 (a int);

Table created.

SQL> begin

while 1>0 loop

insert into t1 values (1);

delete from t1;

commit;

end loop;

end;

/

SQL> 2 3 4 5 6 7 8

2 3 4 5 6 7 8 ^C begin

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation



of 64

This routine has generated a series of transactions whose redo has been sent to and applied at the standby.

During this time with the Statspack snap running the performance information has been written back to the

Primary database in the “STDBYPERF” schema. Stop the snap by executing the snap package for a second time

as the “STADBYPERF” user.

TASK:


o connect stdbyperf/oracle@SFO

o exec statspack_NYC_NYC.snap

Like any performance monitor exercise the last thing to do is to generate a report of the performance information

captured during the snap period. Since this is a Standby Statspack report you need to run the ‘sbreport’ script

instead of the normal Statspack ‘spreport’ script and you must run the report as the ‘STDBYPERF” user.

TASK:


o @$ORACLE_HOME/rdbms/admin/sbreport

o Enter ‘NYC’ for the db_unique_name

o Enter ‘NYC’ for the inst_name

SQL> connect stdbyperf/oracle@SFO

Connected.

SQL> exec statspack_NYC_NYC.snap

PL/SQL procedure successfully completed.

SQL> @$ORACLE_HOME/rdbms/admin/sbreport

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Unique Name Instance Name

------------------------------ ----------------

NYC NYC

Enter the DATABASE UNIQUE NAME of the standby database to report

Enter value for db_unique_name: NYC

You entered: NYC

Enter the INSTANCE NAME of the standby database instance to report

Enter value for inst_name: NYC

You entered: NYC


of 64

Once you have identified which Active Data Guard standby instance you wish to generate the report from you

need to choose the snapshots you want to use. In our case there should only be the 2 entries.

TASK:


o Enter 1 for the begin_snap

o Enter 2 for the end_snap

o Press the Enter key to accept the default report name

NOTE: If you ran the snapshots more than just once, the begin and end snap numbers may be different.

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing <return> without

specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap

Instance Snap Id Snap Started Level Comment

------------ --------- ----------------- ----- --------------------

NYC 1 22 Sep 2011 17:05 5

2 22 Sep 2011 17:08 5

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1

Enter value for end_snap: 2

End Snapshot Id specified: 2

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sb_NYC_NYC_1_2. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name sb_NYC_NYC_1_2

STATSPACK Statistics Report for Physical Standby


of 64

When the report has completed displaying onto your screen you will see the final outline of the report script and

you can edit the report file to examine the performance statistics..

TASK:


o ! vi sb_NYC_NYC_1_2.lst

Search for ‘Recovery Progress Stats’ to position at the recovery statistics in the report.

TASK:

Type the following in the editor

o /Recovery Progress Stats

o :q!

You now have access to performance information for the recovery process on any Active Data Guard standby. In

addition the Statspack information can also be used to analyze query performance. Remember, extensive tuning

procedures for Redo Apply and Query performance are covered in detail in the MAA best practices paper

“Active Data Guard 11g Best Practices” in Appendices C and D.

End of Report ( sb_NYC_NYC_1_2.lst )

SQL> ! vi sb_NYC_NYC_1_2.lst

Recovery Progress Stats DB/Inst: NYC/NYC End Snap: 6

-> End Snapshot Time: 22-Sep-11 17:08:20

-> ordered by Recovery Start Time desc, Units, Item asc

Recovery Start Time Item Sofar Units Redo Timestamp

------------------- ------------------------- ------- ---------------

31-Aug-11 14:36:17 Log Files 36 Files

31-Aug-11 14:36:17 Active Apply Rate 884 KB/sec

31-Aug-11 14:36:17 Average Apply Rat 1 KB/sec

31-Aug-11 14:36:17 Maximum Apply Rat 963 KB/sec

31-Aug-11 14:36:17 Redo Applied 940 Megabyt

31-Aug-11 14:36:17 Last Applied Redo 0 SCN+Tim 06-Sep-11 16:43:48

31-Aug-11 14:36:17 Active Time 1,949 Seconds

31-Aug-11 14:36:17 Apply Time per Lo 54 Seconds

31-Aug-11 14:36:17 Checkpoint Time p 1 Seconds

31-Aug-11 14:36:17 Elapsed Time 526,052 Seconds

31-Aug-11 14:19:39 Log Files 6 Files

31-Aug-11 14:19:39 Active Apply Rate 53 KB/sec

31-Aug-11 14:19:39 Average Apply Rat 5 KB/sec

31-Aug-11 14:19:39 Maximum Apply Rat 53 KB/sec

31-Aug-11 14:19:39 Redo Applied 4 Megabyt

31-Aug-11 14:19:39 Last Applied Redo 0 SCN+Tim 31-Aug-11 14:35:29

...

http://www.oracle.com/technetwork/database/features/availability/maa-wp-11gr1-activedataguard-1-128199.pdf


of 64

Using Oracle Data Pump with Active Data Guard

Since a physical standby is a byte for byte copy of your production database, all the data that you might want to

distribute en-masse to another database is available there. The question is, How to get it out? Most extract

utilities requiring writing to the database, something you cannot do on a read-only Data Guard standby database.

With the arrival of Active Data Guard though, things have changed. Since Active Data Guard allows you to read

and write over database links it is now possible to use Oracle Data Pump and the network option to extract data

from a physical standby that is in Active Data Guard mode.

Oracle Data Pump enables high-speed movement of data and metadata from one database to another. This

technology is the basis for the following Oracle Database data movement utilities:

Data Pump Export (Export) - Export is a utility for unloading data and metadata into a set of operating

system files called a dump file set. The dump file set is made up of one or more binary files that contain

table data, database object metadata, and control information.

Data Pump Import (Import) - Import is a utility for loading an export dump file set into a database. You

can also use Import to load a destination database directly from a source database with no intervening

files, which allows export and import operations to run concurrently, minimizing total elapsed time.

Being able to extract data from an Active Data Guard standby allows you to offload the Data Pump processing to

the standby database. This enables the ability to not only extract and move data to other databases; it also allows

you to repair the Primary database selectively without necessarily affecting the entire database. An example of

this would be as follows:

Your Primary database has multiple applications that run in different schemas.

At some point, due to user error, one application‘s schema is damaged.

o A batch job runs twice by mistake, etc.

During the 2nd

batch job run, the other applications continue on and are taking new transactions.

When the error is discovered you could use flashback database and return the Primary database to the point just

prior to the 2nd batch job run and open reset logs. But this will cause an entire application suite outage and also

return all the other schemas to the same point in time losing all of their good data that was generated during the

2nd

batch job run.

You could just perform the same flashback on the Primary, extract a good copy of the schema and then reverse

the flashback and replace the damaged schema. You would not lose any data from the other applications but this

would require that all the applications are down during the entire process.

Instead, you can flashback the Active Data Guard standby to the appropriate point in time and extract a good

copy of the schema using Oracle Data Pump and repair the Primary database by replacing the damaged schema

with this good copy of the data. All the while the other applications which do not have a problem keep running

and do not lose any data and your application outage is limited only to the schema that was damaged.

This exercise will take you through the steps of extracting the ‘HR’ schema from your Active Data Guard

standby and using the data to replace the ‘HR’ schema in the Primary database.


of 64

Direct Export from Active Data Guard

Oracle Data Pump uses an external directory for the dump file (if created) and the log file into which it writes

informational messages. So the first task is to create a directory structure in the Primary database ‘SFO’ that will

be used for this purpose.

TASK:



o create directory dmpdir as '/tmp';

o grant read, write on directory dmpdir to public;

This creates the directory metadata in the Primary database and the same thing happens on the standby database

through the redo. Since the directory ‘/tmp’ also exists at the standby (the two databases are on the same system

but ‘/tmp’ would also exist on a remote system as well if it exists on the Primary system) the directory will point

to a real location. But this still does not allow you to export directly from the Active Data Guard standby.

TASK:


o expdp system/oracle@NYC directory=dmpdir dumpfile=system_sby.dmp


Connected.

SQL> create directory dmpdir as '/tmp';

Directory created.

SQL> grant read, write on directory dmpdir to public;

Grant succeeded.

SQL>

bash-3.2$ expdp system/oracle@NYC directory=dmpdir dumpfile=system_sby.dmp

Export: Release 11.2.0.2.0 - Production on Fri Sep 23 16:56:12 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0

With the Partitioning, OLAP, Data Mining and Real Application Testing

ORA-31626: job does not exist

ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_SCHEMA_05"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPV$FT", line 1020

ORA-16000: database open for read-only access


of 64

Network Export from Active Data Guard

As you can see from the previous exercise, even though the directory metadata and the system directory exist at

the standby, you cannot export directly from a read-only database because Oracle Data Pump needs to write to its

master table. To extract data from the standby you must run Data Pump from a read-write database using the

NETWORK_LINK qualifier in the export or import command.

The read-write database can be the Primary database or some other target database for the data you are going to

extract. If you are only creating export dump files then the read-write database could also be a small ‘stub’

database that resides on the same system as the Active Data Guard standby database. In this exercise you will

be using the Primary database as the read-write database for Data Pump’s information.

The first exercise is to extract the ‘HR’ schema from the Active Data Guard standby and put it in a dump file in

the ‘/tmp’ directory. To do this you first have to create a database link that will be used by Oracle Data Pump to

connect from the read-write database (the Primary SFO) to the read-only database (the Active Data Guard

standby NYC). This will be used to connect to the standby and extract the required data.

TASK:


o create public database link adg_sby using 'NYC';

Now you can begin to extract data from the standby into dump files that will be placed in the ‘/tmp’ directory.

TASK:


o expdp system/oracle@SFO directory=dmpdir dumpfile=hr.dmp network_link=adg_sby

schemas=hr

o ls -l /tmp/hr.dmp

The dump file ‘/tmp/hr.dmp’ can now be imported into any read write database and a record of the export is

contained in the export log file ‘/tmp/export.log’. As an example you will drop the ‘HR’ schema in the Primary

SQL> create public database link adg_sby using 'NYC';


bash-3.2$ expdp system/oracle@SFO directory=dmpdir dumpfile=hr.dmp

network_link=adg_sby schemas=hr

... <output edited for space>

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

***********************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/tmp/hr.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:11:25

bash-3.2$ ls -l /tmp/hr.dmp

-rw-r----- 1 oracle oinstall 434176 Sep 25 18:11 /tmp/hr.dmp


of 64

database, which will cause it to also be removed from the standby database, and then using the dump file you just

created, recreate the ‘HR’ schema in the Primary database by importing it.

TASK:


o drop user HR cascade;

This drops the entire schema related to the user ‘HR’ and, since we are running in Real-Time Apply mode at the

standby, the HR schema has also been removed. Now we can use import to replace the ‘HR’ user.

TASK:


o impdp system/oracle@SFO DIRECTORY=dmpdir dumpfile=hr.dmp schemas=hr

SQL> drop user HR cascade;

User dropped.

SQL>

bash-3.2$ impdp system/oracle@SFO DIRECTORY=dmpdir dumpfile=hr.dmp

schemas=hr

Import: Release 11.2.0.2.0 - Production on Mon Sep 26 10:48:57 2011

Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@SFO

DIRECTORY=dmpdir dumpfile=hr.dmp schemas=hr

... <Output edited for space>

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 10:49:04


of 64

You can verify that the data has been returned by connecting to both the Standby and the Primary databases as

the ‘HR’ user again and examine the ‘EMPLOYEES’ table.

TASK:


o connect hr/oracle@NYC

o select count(*) from employees;


o select count(*) from employees;

SQL> connect hr/oracle@NYC

Connected.

SQL> select count(*) from employees;

COUNT(*)

----------

107


Connected.

SQL> select count(*) from employees;

COUNT(*)

----------

107

SQL>


of 64

Network Import from Active Data Guard

In the previous exercise you extracted the ‘HR’ schema from the Active Data Guard standby and then replaced

the schema in your Primary database by dropping the ‘HR’ user and then importing the schema from the dump

file. It is also possible to export data from an Active Data Guard standby database and import it directly into your

read-write target database. This eliminates the need for the export dump file but the directory is still required for

the log file of the export/import operation. This exercise will combine the use of Oracle Data Pump and

Flashback Database to repair a ‘user damaged’ Primary database.

The steps you would take to repair a damaged primary database include a flashback of the standby before

extracting the ‘good’ schema copy. This flashback operation would normally be done to some time that is

determined by the DBA for a point in the database where the affected data (the ‘HR’ schema in our case) was

good and could be extracted. In addition, since you want to use the IMPORT directly into the Primary database

method, the standby must be at a point where the good copy of the data is available even when we drop the

‘damaged’ schema at the Primary. As you saw in the last exercise, the ‘HR’ schema was dropped from the

Primary, which would have also dropped it from the standby since Redo Apply was active the whole time.

The steps for this final exercise are as follows:

1. Stop Redo Apply with DGMGRL

2. Create a guaranteed restore point on the standby

a. This is to simplify the flashback for the purposes of this exercise. Normally you would use some

timestamp relevant to your situation for step 7 below.

3. Restart Redo Apply with DGMGRL

4. ‘Damage’ the HR schema with some simple SQL on the Primary

5. Verify that the data is damaged on the standby

6. Stop Redo Apply with DGMGRL

7. Flashback the standby database to the restore point

8. Reopen the standby in read-only mode (without Redo Apply running)

9. Drop the HR schema at the Primary

10. Import the good copy of the HR schema from the Active Data Guard standby.

11. Restart Redo Apply with DGMGRL

12. Verify that the good data is available at the standby

Stopping the apply is required to create the restore point and is also required when using FLASHBACK

DATABASE later on.

TASK:

Execute the following in the DGMGRL window

o edit database nyc set state='apply-off';

DGMGRL> edit database nyc set state='apply-off';

Succeeded.

DGMGRL>


of 64

Once Redo Apply has stopped you can create your guaranteed restore point.

TASK:

Execute the following in the NYC SQL*Plus window

o connect sys/oracle@nyc as sysdba

o restore point before_drop guarantee flashback database;

And then restart Redo Apply again so that the Standby is in SYNC with the Primary again.

TASK:


o edit database nyc set state='apply-on';

This sets up our standby for the rest of this exercise without having to figure out when the ‘damage’ to the ‘HR’

schema was done. The next thing to do is to ‘damage’ some data in the ‘HR’ schema. You will run a simple

SQL*Plus DML statements to emulate a more serious problem.

TASK:



o update employees set email=phone_number, hire_date=sysdate, salary=commission_pct;

o commit;

DGMGRL> edit database nyc set state='apply-on';

Succeeded.

DGMGRL>

SQL> connect sys/oracle@nyc as sysdba

Connected.

SQL> create restore point before_drop guarantee flashback database;

Restore point created.

SQL>


Connected

SQL> update employees set email=phone_number, hire_date=sysdate,

salary=commission_pct;

107 rows updated.

SQL> commit;

Commit complete.


of 64

Connect to the standby again as the ‘HR’ user and verify the damaged data.

TASK:



o select last_name,email,hire_date,salary from employees where employee_id<105;

At this point in real life, the ‘HR’ application would have to be stopped due to the ’damage’, but other

applications could continue running. Of course there are simpler ways to correct this kind of damage with

FLASHBACK TABLE. So you try that method first.

TASK:


o flashback table hr.employees to timestamp sysdate-1;

The production database was not prepared for this kind of damage. You can fix that for the future but what do

you do now? This is where your Active Data Guard standby comes to the rescue! As you saw in the first

exercise, Oracle Data Pump can extract the data from the standby and import it back into your Primary, replacing

the damaged data. For the purposes of this exercise we are going to assume that it really is the entire ‘HR’

schema has been damaged and extract and import the ‘HR’ schema.


Connected

SQL> select last_name,email,hire_date,salary from employees where

employee_id<105;

LAST_NAME EMAIL HIRE_DATE SALARY

--------------------- ------------------------- --------- ----------

King 515.123.4567 26-SEP-11

Kochhar 515.123.4568 26-SEP-11

De Haan 515.123.4569 26-SEP-11

Hunold 590.423.4567 26-SEP-11

Ernst 590.423.4568 26-SEP-11

SQL>

SQL> flashback table hr.employees to timestamp sysdate-1;

flashback table hr.employees to timestamp sysdate-1

*

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not

enabled

SQL>


of 64

The first step is to stop Redo Apply since you cannot flash a database back if media recovery is running. As

before, you will do this with DGMGRL.

TASK:


o edit database nyc set state='apply-off';

At this point you would flash the standby back to a time or SCN before the damage was done. Since you created

the Guaranteed Restore Point (GRP) previously, you can flashback to that point.

TASK:


o connect sys/oracle@NYC as sysdba

o flashback standby database to restore point before_drop;

The standby database is now at a point before you ran the ‘damage’ SQL statement earlier. You can verify the

data by connecting as the HR user and examining the ‘EMPLOYEES table. However, you must first open the

standby again as the flashback operation does an implicit close and returns the standby database to a mount state.

TASK:


o select open_mode from v$database;

o alter database open;

DGMGRL> edit database nyc set state='apply-off';

Succeeded.

DGMGRL>

SQL> connect sys/oracle@NYC as sysdba

Connected.

SQL> flashback standby database to restore point before_drop;

Flashback complete.

SQL>

SQL> select open_mode from v$database;

OPEN_MODE

----------

MOUNTED

SQL> alter database open;

Database altered.


of 64

Now verify that the EMPLOYEES table has been restored to its original state using a select statement.

TASK:




You are ready to extract the good data and repair the Primary database. Just like the last exercise, you need to

remove the ‘HR’ user again, after reconnecting as SYS of course!

TASK:



o drop user HR cascade;

Because Redo Apply is not running at the standby, the redo for the drop user will be sent to the standby but not

applied. This way we can still extract the good data. Note that if you restarted Redo Apply at this point all of the

redo that occurred after the GRP creation (including our damaging SQL) but even then you would be OK as you

could just start again repeating the flashback operation. Bear in mind that without the GRP you are subject to the

rules of flashback and your flashback retention time (which is set to the default of 24 hours on your databases).


Connected.

SQL> drop user HR cascade;

User dropped.

SQL>


Connected.


employee_id<105;


---------------------- ------------------------- --------- ----------

King SKING 17-JUN-03 24000

Kochhar NKOCHHAR 21-SEP-05 17000

De Haan LDEHAAN 13-JAN-01 17000

Hunold AHUNOLD 03-JAN-06 9000

Ernst BERNST 21-MAY-07 6000

SQL>


of 64

When you do the import, to prove that the data really is coming across the network, remove all the dump and log

files in ‘/tmp’ first.

TASK:


o rm /tmp/hr.dmp /tmp/export.log /tmp/import.log

The next to last step is to use Oracle Data Pump and import the ‘HR’ schema back into the Primary database

using the database link you created in the first Data Pump exercise.

TASK:


o impdp system/oracle@SFO DIRECTORY=dmpdir NETWORK_LINK=adg_sby schemas=hr

Check the ‘/tmp’ directory and see what files were (or were not) created there.

TASK:


o ls -ltrh /tmpr

bash-3.2$ rm /tmp/hr.dmp /tmp/export.log /tmp/import.log

rm: remove regular file `/tmp/hr.dmp'? y

rm: remove regular file `/tmp/export.log'? y

rm: remove regular file `/tmp/import.log'? y

bash-3.2$

bash-3.2$ impdp system/oracle@SFO DIRECTORY=dmpdir NETWORK_LINK=adg_sby

schemas=hr

Import: Release 11.2.0.2.0 - Production on Mon Sep 26 17:04:39 2011

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@SFO

DIRECTORY=dmpdir NETWORK_LINK=adg_sby schemas=hr

... <Output edited for space>

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 17:05:36

bash-3.2$

bash-3.2$ ls -ltrh /tmp

...

-rw-r--r-- 1 oracle oinstall 2.2K Sep 26 17:05 import.log

bash-3.2$


of 64

Now you will be able to verify that the data has been returned to its previous state and the ‘HR’ application can

be restarted. Log back into the Primary database ‘SFO’ and check the EMPLOYEES table.

TASK:




Your last task is to restart Redo Apply on the standby so that it will become synchronized again with the Primary

(which means that the standby will apply everything since the guaranteed restore point including the damaging

SQL, the drop of the ‘HR’ user and the import of the ‘HR’ user). Once caught up you are ready for the next

event!

TASK:


o edit database nyc set state='apply-on';

During the catch up period the ‘HR’ user and tables will be returned to their damaged state, disappear and then

reappear in the correct state. You could monitor this with repeated select statements as SYS on your ‘NYC’

standby.

Note: You do not need to do this for this hands on lab but in general if you use GRP’s do not forget to drop them

when they are no longer needed otherwise your standby database will continue to fill up the flashback logs.


Connected.


employee_id<105;


---------------------- ------------------------- --------- ----------

King SKING 17-JUN-03 24000

Kochhar NKOCHHAR 21-SEP-05 17000

De Haan LDEHAAN 13-JAN-01 17000

Hunold AHUNOLD 03-JAN-06 9000

Ernst BERNST 21-MAY-07 6000

SQL>

DGMGRL> edit database nyc set state='apply-on';

Succeeded.

DGMGRL>


of 64

Conclusion

This concludes the exercises for the Oracle Database 11g Active Data Guard Hands On Lab. During this session

you have explored and exercised Active Data Guard’s unique capabilities while protecting your data. You were

able to read and write the Standby Database while changes are being applied from the Production database.

You have done the following exercises.

Enabled Active Data Guard

Read live data from your Physical Standby database

Wrote data through your Active Data Guard standby

Used Schema redirection to write data through your Active Data Guard standby

Corrupted data blocks and watched Automatic Block Media Recovery in action

Used Statspack to monitor the performance of Redo Apply and ultimately Queries

Used Oracle Data Pump to extract data from your standby and repair the Primary database.

It is our sincere hope that these exercises have helped to guide you on your path to using Data Guard and

understanding how to use the features that it brings to the Oracle Database world to provide you with Disaster

Protection for your Oracle data.

Thank you for your participation.

Larry M. Carpenter


of 64


of 64

Resources

The following documentation is available for Data Guard

Oracle® Data Guard Concepts and Administration 11g Release 1 (11.1)

o http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/toc.htm

Oracle® Data Guard Broker 11g Release 1 (11.1)

o http://download.oracle.com/docs/cd/B28359_01/server.111/b28295/toc.htm

Oracle® Data Guard Concepts and Administration 11g Release 2 (11.2)

o http://download.oracle.com/docs/cd/E11882_01/server.112/e10700/toc.htm

Oracle® Data Guard Broker 11g Release 2 (11.2)

o http://download.oracle.com/docs/cd/E11882_01/server.112/e10702/toc.htm

The Maximum Availability Architecture Best Practices papers are on Oracle OTN.

Best Practices for High Availability -- Maximum Availability Architecture (MAA)

o http://www.oracle.com/goto /maa

Best Practices for Data Guard

o http://www.oracle.com/goto /dataguard

http://download.oracle.com/docs/cd/B28359_01/server.111/b28294/toc.htm

http://download.oracle.com/docs/cd/B28359_01/server.111/b28295/toc.htm

http://download.oracle.com/docs/cd/E11882_01/server.112/e10700/toc.htm

http://download.oracle.com/docs/cd/E11882_01/server.112/e10702/toc.htm

http://www.oracle.com/goto%20/maa

http://www.oracle.com/goto%20/dataguard


of 64

Active Data Guard Hands on Lab

January 2013

Authors: Larry M. Carpenter

Oracle USA

World Headquarters

500 Oracle Parkway

Redwood Shores, CA 94065

U.S.A.

Worldwide Inquiries:

Phone: +1.650.506.7000

Fax: +1.650.506.7200

oracle.com

Copyright © 2010, Oracle. All rights reserved.

This document is provided for information purposes only and the contents hereof are subject to change without

notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether

expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a

particular purpose. We specifically disclaim any liability with respect to this document and no contractual

obligations are formed either directly or indirectly by this document. This document may not be reproduced or

transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written

permission. Oracle, JD Edwards, and PeopleSoft are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

active data guard hands on lab openworld 2009 - · pdf fileoracle active data guard page 3 of...

Documents