Implementing Oracle Data Guard for the RLS database
Implementing Oracle Data Guard for the RLS database Kasia Pokorska CERN, IT-DB 30 th March 2004 1
Agenda • A quick introduction to Data Guard concepts • What choice for the RLS database? • Data Guard implementation for RLS • Tested Data Guard scenarios • Data Guard monitoring • Next steps • Q&A 2
What is Oracle Data Guard? • Oracle high availability, data protection, and disaster recovery solution for enterprise data • Maintains up to nine standby databases as transactionally consistent copies of the production database • If the production database (primary) becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the production role • Feature of Oracle Database Enterprise Edition • Prerequisites: the same edition of Oracle Enterprise Edition, the same operating system (but the release does not have to be the same), the same hardware architecture on the primary and standby locations 3
Data Guard Architecture Physical/Logical Standby Database Oracle Net Transactions LGWR MRP/ LSP RFS Online Redo Logs FAL Primary Database Standby Redo Logs Backup / Reports ARCH Archived Redo Logs 4
Physical versus Logical Standby Physical Logical Characteristics A physically identical copy of the primary database on a block-for-block basis Contains the same logical information as the production database, although a physical organization and structure of the data can be different. Synchronization mechanism Redo Apply SQL Apply Read-only access Yes, but only in a exclusive mode (no redo apply at the same time) Can be queried for reports while logs are being applied via SQL Additional benefits - Can be used to offload backups - Redo apply is fastest and most efficient approach to apply changes - Can create additional indexes and materialized views for better query performance Limitations - Uses more resources than physical - Has some restrictions on datatypes, 5 types of tables, DDL and DML
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 availability of the primary database – Switches to maximum performance mode if a fault prevents it from writing its redo 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 6
Agenda • A quick introduction to Data Guard concepts • What choice for the RLS database? • Data Guard implementation for the RLS • Tested Data Guard scenarios • Data Guard monitoring • Next steps • Q&A 7
How to Determine the Proper Data Guard Configuration for the RLS? Is data loss acceptable if the primary site fails? NO → physical standby + maximum protection/availability mode How much data loss is tolerated if a site is lost? NONE → physical standby + maximum protection/availability mode Is potential data loss between the production and the standby databases tolerated when a standby host or network connection is temporarily unavailable? NO → physical standby + maximum protection How far away will the disaster recovery site be from the primary site? What is the network bandwidth and latency between sites? If the network round trip time is greater than 10 milliseconds → physical standby + maximum performance for applications that can not tolerate a change in performance or response time How many standby databases will be configured? At the first time only one → physical standby + maximum availability + 8 standby redo log
Agenda • A quick introduction to Data Guard concepts • What choice for the RLS database? • Data Guard implementation for the RLS • Tested Data Guard scenarios • Data Guard monitoring • Next steps • Q&A 9
The RLS Testing Environment • Two testbed nodes: the primary database on lxn 4252 and one physical standby on the lxn 4253 (Linux ia 32, CPU servers) • Oracle release 9. 2. 0. 4 • Identical directory structure on both nodes • The RLS database created and deployed with the last version of the schemas (2. 2. 5) 10
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 Automatic archival Archive destination Oldest online log sequence Next log sequence to archive Current log sequence Archive Mode Enabled /ORA/dbs 02/oracle/admin/arch/ 2 4 4 11
Data Guard Prerequisites for the RLS (2/2) 3. …But a local archiving destination should be defined (PRI) Changes to initrls 1. ora: - removed #rls 1. log_archive_dest=/ORA/dbs 02/oracle/admin/arch/ - and added rls 1. log_archive_dest_1='LOCATION=/ORA/dbs 02/oracle/admin/arch/ MANDATORY‘ 4. Set up other initialization parameters required for the data guard (PRI) - Already existing in initrls 1. ora db_name=rls 1. instance_name=rls 1 - Added rls 1. remote_archive_enable=true rls 1. standby_file_management=auto 12
Creating the Physical Standby for the RLS (1/6) 1. Identify the primary database datafiles (PRI) SQL> SELECT NAME FROM V$DATAFILE; NAME --------------------------------/ORA/dbs 03/oradata/rls 1_system_01. dbf /ORA/dbs 03/oradata/rls 1_undo_01. dbf /ORA/dbs 03/oradata/rls 1_data 01_01. dbf /ORA/dbs 03/oradata/rls 1_INDX 01_01. dbf … And the online redo logs: -rw-r-----rw-r----- 1 oracle ci ci ci 104858112 Mar 12 12: 00 rls 1_redo_01. log 104858112 Mar 12 11: 40 rls 1_redo_02. log 104858112 Mar 12 11: 40 rls 1_redo_03. log 13
Creating the Physical Standby for the RLS (2/6) 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/dbs 03/oradata/rls 1 hs_control 01'; 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 initrls 1. ora 14
Creating the Physical Standby for the RLS (3/6) 5. Set initialization parameters on a physical standby database (SEC) – in initrls 1. ora control_files='/ORA/dbs 03/oradata/rls 1 hs_control 01‘ rls 1. standby_archive_dest='/ORA/dbs 02/oracle/admin/arch/‘ 6. Enable dead connection detection on the standby system (SEC + PRI) Create the file sqlnet. ora in $ORACLE_BASE/admin/network containing one line: SQLNET. EXPIRE_TIME=2 and add two simlinks in $ORACLE_BASE/product/rdbms 9. 2. 0. 4/network/admin ln -s /ORA/dbs 01/oracle/admin/network/sqlnet. ora in $ORACLE_BASE/admin/rls 1/network ln -s /ORA/dbs 01/oracle/admin/network/sqlnet. ora 15
Creating the Physical Standby for the RLS (4/6) 7. Create Oracle Net Service name for the standby database $ORACLE_BASE/admin/network/tnsnames. ora (PRI + SEC) rls 1 hs=(DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=lxn 5243. cern. ch)(PORT=1521)) (CONNECT_DATA=(SID=rls 1)) 8. Start the physical standby database (SEC) SQL> STARTUP NOMOUNT; SQL> alter database mount standby database; 16
Creating the Physical Standby for the RLS (5/6) 9. Create standby redo log files (PRI + SEC) SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 ('/ORA/dbs 03/oradata/rls 1_stdby_redo_01. log') size 100 M; SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 ('/ORA/dbs 03/oradata/rls 1_stdby_redo_02. log') size 100 M; SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 ('/ORA/dbs 03/oradata/rls 1_stdby_redo_03. log') size 100 M; SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 ('/ORA/dbs 03/oradata/rls 1_stdby_redo_04. log') size 100 M; 17
Creating the Physical Standby for the RLS (6/6) 10. Initiate Log Apply Services (SEC) SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 11. Enable archiving to the physical standby database (PRI) SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=rls 1 hs LGWR SYNC=NOPARALLEL AFFIRM'; SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 18
Setting the Data Protection Mode for the RLS database SQL> SELECT PROTECTION_MODE FROM V$DATABASE; SQL> shutdown immediate SQL> startup mount exclusive; SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE} SQL> alter database open; 19
Agenda • A quick introduction to Data Guard concepts • What choice for the RLS database? • Data Guard implementation for the RLS • Tested Data Guard scenarios • Data Guard monitoring • Next steps • Q&A 20
Managing the RLS database • Starting Up the Physical Standby – for read-only access • the static parameter audit_trail has to be set to „NONE” (currently equals 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 the primary 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-Recovery Mode 21
Managing the RLS database • Role Management – Switchover – a role reversal between the primary database and one of its standby databases in case of planned maintenance of the primary system. • Role Management – Failover – an irreversible transition of a standby database to the primary role. Done in the event of a catastrophic failure of the primary database – Consider if it would be faster to repair the primary database than to perform a role transition – Any archived redo log gaps have to be identified, resolved (if possible) and registered 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 it become available 22
Agenda • A quick introduction to Data Guard concepts • What choice for the RLS database? • Data Guard implementation for the RLS • Tested Data Guard scenarios • Data Guard monitoring • Next steps • Q&A 23
Verifying the Physical Standby Database (1/2) 1. Identify the existing archived redo logs (PRI + SEC) SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# -----1 2 3 FIRST_TIM ----23 -FEB-04 NEXT_TIME ----23 -FEB-04 2. Archive the current log (PRI) SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; 24
Verifying the Physical Standby Database (2/2) 3. Verify that the new archived redo log was received (SEC) SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; SEQUENCE# -----1 2 3 4 FIRST_TIM ----23 -FEB-04 NEXT_TIME ----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 BY SEQUENCE#; SEQUENCE# APP ----- --4 YES 25
Data Guard Monitoring • Alert log in /ORA/dbs 00/oracle/admin/rls 1/bdump/alert_rls 1. log • Dynamic performance views – can be query in managed recovery mode – V$MANAGED_STANDBY – monitoring the log transport and apply activities – V$ARCHIVE_DEST_STATUS – determine on the primary the most recently archived redo log at the standby destination – V$ARCHIVE_DEST - confirm from the primary site that automatic archiving 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_DEST parameter /ORA/dbs 00/oracle/admin/rls 1/udump. – possibility to adjust the trace level 26
Agenda • A quick introduction to Data Guard concepts • What choice for the RLS database? • Data Guard implementation for the RLS • Tested Data Guard scenarios • Data Guard monitoring • Next steps • Q&A 27
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 of April, followed by the maximum availability implementation mi-May 28
Evaluating Data Guard Broker • Data Guard Broker is distributed management framework that – automates and centralizes the creation, maintenance, and monitoring of Data Guard configurations • It offers also an easy-to-use interfaces: – Oracle 9 i Data Guard Manager, which is the Data Guard graphical user interface (GUI) integrated in the Oracle Enterprise Manager tool – Data Guard command-line interface (CLI) 29
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 30
Oracle Data Guard and 10 g • Online redo apply to enforce data protection • Rolling upgrade of Oracle database • more ? 31
- Slides: 31