kp data guard for rls

8/8/2019 KP Data Guard for RLS

1/31

1

Implementing Oracle Data

Guard for the RLS database

Kasia Pokorska

CERN, IT-DB30th March 2004


2/31

2

Agenda

A quick introduction to Data Guardconcepts

What choice for the RLS database?

Data Guard implementation for RLS

Tested Data Guard scenarios

Data Guard monitoring

Next steps

Q&A


3/31

3

What is Oracle Data Guard?

Oracle high availability, data protection, and disaster recovery solution forenterprise data

Maintains up to nine standby databases as transactionally consistentcopies of the production database

If the production database (primary) becomes unavailable because of aplanned or an unplanned outage, Data Guard can switch any standbydatabase to the production role

Feature of Oracle Database Enterprise Edition

Prerequisites: the same edition of Oracle Enterprise Edition, the sameoperating system (but the release does not have to be the same), the samehardware architecture on the primary and standby locations


4/31

4

Data Guard ArchitectureOracle Net

FALPrimary

Database

Transactions Physical/LogicalStandbyDatabase

Backup /Reports

LGWR

Online Redo Logs

Archived Redo Logs

ARCH

RFS

StandbyRedoLogs

Archived Redo Logs

ARCH

MRP/ LSP


5/31

5

Physical versus Logical StandbyPhysical Logical

Characteristics A physically identical copy ofthe primary database on ablock-for-block basis

Contains the same logical informationas the production database, althougha physical organization and structureof the data can be different.

Synchronizationmechanism

Redo Apply SQL Apply

Read-only

access

Yes, but only in a exclusivemode (no redo apply at thesame time)

Can be queried for reports while logsare being applied via SQL

Additional

benefits

- Can be used to offload

backups- Redo apply is fastest andmost efficient approach toapply changes

- Can create additional indexes and

materialized views for better queryperformance

Limitations - Uses more resources than physical

- Has some restrictions on datatypes,

types of tables, DDL and DML


6/31

6

Data Guard Protection Modes

Maximum Protection

Offers the highest level of data protection

Requires at least one physical standby database in the configuration

The standby redo logs have to be set up

Maximum Availability

Provides the highest level of data protection without compromising the availabilityof the primary database

Switches to maximum performance mode if a fault prevents it from writing itsredo stream to a remote standby redo log

Maximum Performance

The default protection mode

Offers slightly less data protection on the primary database

...however, provides the higher performance


7/31

7

Agenda



Data Guard implementation for the RLS



Next steps

Q&A


8/31

8

How to Determine the Proper Data

Guard Configuration for the RLS?Is data loss acceptable if the primary site fails? NO physical standby + maximumprotection/availability mode

How much data loss is tolerated if a site is lost? NONE physical standby +maximum protection/availability

modeIs potential data loss between the production andthe standby databases tolerated when a standbyhost or network connection is temporarilyunavailable?

NO physical standby + maximumprotection

How far away will the disaster recovery site befrom the primary site? What is the networkbandwidth and latency between sites?

If the network round trip time isgreater than 10 milliseconds physical standby + maximumperformance for applications that cannot tolerate a change in performanceor response time

How many standby databases will be configured? At the first time only one physicalstandby + maximum availability +

standby redo log


9/31

9

Agenda






Next steps

Q&A


10/31

10

The RLS Testing Environment

Two testbed nodes: the primary database on lxn4252and one physical standby on the lxn4253 (Linux ia32,CPU servers)

Oracle release 9.2.0.4

Identical directory structure on both nodes

The RLS database created and deployed with the lastversion of the schemas (2.2.5)


11/31

11

Data Guard Prerequisites for the

RLS (1/2)1. Implement FORCE LOGGING mode (PRI)SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR

---

NO

SQL> ALTER DATABASE FORCE LOGGING;

2. Archiving is already enabled (PRI)

SQL> ARCHIVE LOG LIST;

Database log mode Archive Mode

Automatic archival EnabledArchive destination /ORA/dbs02/oracle/admin/arch/

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4


12/31

12

3. But a local archiving destination should be defined (PRI)

Changes to initrls1.ora:

- removed

#rls1.log_archive_dest=/ORA/dbs02/oracle/admin/arch/

- and addedrls1.log_archive_dest_1='LOCATION=/ORA/dbs02/oracle/admin/arch/

MANDATORY

4. Set up other initialization parameters required for the data guard (PRI)

- Already existing in initrls1.ora

db_name=rls1

rls1.instance_name=rls1

- Added

rls1.remote_archive_enable=true

rls1.standby_file_management=auto

Data Guard Prerequisites for the

RLS (2/2)


13/31

13

Creating the Physical Standby for

the RLS (1/6

)1. Identify the primary database datafiles (PRI)SQL> SELECT NAME FROM V$DATAFILE;

NAME

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

/ORA/dbs03/oradata/rls1/rls1_system_01.dbf/ORA/dbs03/oradata/rls1/rls1_undo_01.dbf

/ORA/dbs03/oradata/rls1/rls1_data01_01.dbf

/ORA/dbs03/oradata/rls1/rls1_INDX01_01.dbf

And the online redo logs:-rw-r----- 1 oracle ci 104858112 Mar 12 12:00 rls1_redo_01.log-rw-r----- 1 oracle ci 104858112 Mar 12 11:40 rls1_redo_02.log

-rw-r----- 1 oracle ci 104858112 Mar 12 11:40 rls1_redo_03.log


14/31

14

2.Make a copy of the primary database (PRI)

3. Create a control file for the standby database (PRI)

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS

2 '/ORA/dbs03/oradata/rls1/rls1hs_control01';

4. Copy files from the primary system to the standby system (PRI -> SEC)

- backup datafiles

- online redo logs

- standby control file- all archived redo logs

- initialization parameter file initrls1.ora


the RLS (2/6

)


15/31

15

5. Set initialization parameters on a physical standby database (SEC) in

initrls1.ora

control_files='/ORA/dbs03/oradata/rls1/rls1hs_control01

rls1.standby_archive_dest='/ORA/dbs02/oracle/admin/arch/

6. Enable dead connection detection on the standby system (SEC + PRI)

Create the file sqlnet.ora in $ORACLE_BASE/admin/networkcontaining one line:

SQLNET.EXPIRE_TIME=2

and add two simlinks

in $ORACLE_BASE/product/rdbms9.2.0.4/network/admin

ln -s /ORA/dbs01/oracle/admin/network/sqlnet.ora sqlnet.ora

in $ORACLE_BASE/admin/rls1/network

ln -s /ORA/dbs01/oracle/admin/network/sqlnet.ora sqlnet.ora


the RLS (3/6

)


16/31

16

7. Create Oracle Net Service name for the standby database -$ORACLE_BASE/admin/network/tnsnames.ora (PRI + SEC)

rls1hs=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lxn5243.cern.ch)(PORT=1521))

(CONNECT_DATA=(SID=rls1))

8. Start the physical standby database (SEC)

SQL> STARTUP NOMOUNT;

SQL> alter database mount standby database;


the RLS (4/6

)


17/31

17

9. Create standby redo log files (PRI + SEC)

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 11('/ORA/dbs03/oradata/rls1/rls1_stdby_redo_01.log') size 100M;



SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 14

('/ORA/dbs03/oradata/rls1/rls1_stdby_redo_04.log') size 100M;


the RLS (5

/6

)


18/31


19/31


20/31

20

Agenda






Next steps

Q&A


21/31

21

Managing the RLS database

Starting Up the Physical Standby

for read-only access

the static parameteraudit_trailhas to be set to NONE (currentlyequals to DB)

the temporary tablespace has to be added on the standby database

in order to sort a huge amount of data for managed recovery operations

Shutting down the physical standby

defer the archive log destination 2 and perform a log switch on theprimary database (not possible in the maximum protection mode the

primary database have to be shut down first)

Switching between the Read-Only Access and Managed-RecoveryMode


22/31

22

Role Management Switchover

a role reversal between the primary database and one of its standby databasesin case of planned maintenance of the primary system.

Role Management Failover

an irreversible transition of a standby database to the primary role. Done in theevent of a catastrophic failure of the primary database

Consider if it would be faster to repair the primary database than to perform arole transition

Any archived redo log gaps have to be identified, resolved (if possible) andregistered before performing the failover operation

The standby database has to be recreated from the primary

Unexpected shutdown of the standby database

- all the primary redo logs were shipped automatically to the standby while itbecome available

Managing the RLS database


23/31

23

Agenda






Next steps

Q&A


24/31

24

Verifying the Physical StandbyDatabase (1/2)

1. Identify the existing archived redo logs (PRI + SEC)

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOGORDER BY SEQUENCE#;

SEQUENCE#

FIRST_TIM NEXT_TIME---------- --------- ---------

1 23-FEB-04 23-FEB-04

2 23-FEB-04 23-FEB-04

3 23-FEB-04 23-FEB-04

2. Archive the current log (PRI)

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;


25/31

25

3. Verify that the new archived redo log was received (SEC)

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOGORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME

---------- --------- ---------1 23-FEB-04 23-FEB-04

2 23-FEB-04 23-FEB-04

3 23-FEB-04 23-FEB-04

4 23-FEB-04 03-MAR-04

4. Verify that the new archived redo log was applied (SEC)

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BYSEQUENCE#;

SEQUENCE# APP

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

4 YES

Verifying the Physical StandbyDatabase (2/2)


26/31

26

Data Guard Monitoring

Alert log in/ORA/dbs00/oracle/admin/rls1/bdump/alert_rls1.log

Dynamic performance views can be query in managed recovery mode

V$MANAGED_STANDBY monitoring the log transport and applyactivities

V$ARCHIVE_DEST_STATUS determine on the primary the mostrecently archived redo log at the standby destination

V$ARCHIVE_DEST - confirm from the primary site that automaticarchiving to the standby is working

V$LOG_HISTORY - viewing the whole archive log history

V$STANDBY_LOG - verifying that the standby redo log groups are

created and running correctly

Trace files are located in the directory specified by the USER_DUMP_DESTparameter/ORA/dbs00/oracle/admin/rls1/udump.

possibility to adjust the trace level


27/31

27

Agenda






Next steps

Q&A


28/31

28

Stress testing in the pre-production

RLS environment (April 2003)

Evaluate the Data Guard performance

Validate the Data Guard implementation itself

If positive results, a first maximum performance Data

Guard configuration can be in production at the end ofApril, followed by the maximum availabilityimplementation mi-May


29/31

29

Evaluating Data Guard Broker

Data Guard Broker is distributed management framework that

automates and centralizes the creation, maintenance, and monitoring ofData Guard configurations

It offers also an easy-to-use interfaces:

Oracle9i Data Guard Manager, which is the Data Guard graphical userinterface (GUI) integrated in the Oracle Enterprise Manager tool

Data Guard command-line interface (CLI)


30/31

30

Data Guard CLI

Prerequisites:

requires the SPFILE and password file

the primary database and a standby database must already exist

A Data Guard Broker Configuration was created and enabled

Successful tests for

setting database properties

Failure for

changing the configuration protection mode performing a switchover


31/31

31

Oracle Data Guard and 10g

Online redo apply to enforce data protection

Rolling upgrade of Oracle database

more ?

kp data guard for rls

Documents