Oracle9i Data Guard

1. Oracle9i Data Guard Darl Kuhn Sun Microsystems RMOUG Training Days 2003

2. Oracle9i Data Guard Agenda • Business Requirements • What is Data Guard? • Data Guard Architecture • Implementing Data Guard • Failover when Disaster Strikes • 8i Data Guard New Features • 9i Data Guard New Features

3. Business Requirements Business Goals: • Keep the data 7x24 highly available • High Availability • Minimize unscheduled downtime • Minimize scheduled downtime • Disaster Recovery protection • Minimize Cost

4. Business Requirements Minimize Costs: • Hardware • Personnel (DBA, SA, etc.) • Special skills • Training • Licensing • Implementation • Maintenance

5. Business Requirements Minimize Complexity: • Number of moving parts • Ease of implementation • Ease of maintenance • More complex usually means more cost • Higher availability usually means more cost Choose a solution that: • Meets business requirements • Minimizes cost and complexity

6. Business Solutions Available • Backup the database, restore from tape • Operating System failover • Remote Mirroring • Quest Software SharePlex • Oracle Advanced Replication (OAR) • Oracle Real Application Clusters (RAC) • Oracle Data Guard (Standby)

7. We Chose Data Guard • Needed 7x24 Disaster Recovery protection • Simple to implement • Requires DBA with B&R skills • Didn’t need special SA skills or consultants • Low maintenance (do more w/less DBAs) • We don’t have to baby-sit it • No extra licensing (built into Oracle9i) • Not an add-on, part of Enterprise Edition • Main cost was extra hardware

8. Data Guard History • Previously known as the Standby Option • First developed by Oracle Consulting in the late 80s early 90s • Became part of Oracle database in 7.3 • Renamed to Oracle Data Guard in 8.1.7 • Greatly enhanced in Oracle9i

9. What is Data Guard? Built upon a simple concept: • Take a backup of your database • Move backup files to standby box • Mount standby database in recovery mode • Apply archived redo to standby database as it becomes available from primary database

10. Copy and Apply Archive Redo • Oracle 7.3 • Copy and apply of archive redo was manual • Oracle8i • Automated copy and apply of archived redo • Uses ARCn and Net8 for copy • Configured via init.ora parameters • Oracle9i • Advanced archive gap detection • Uses Fetch Archive Log (FAL) process

11. Basic Physical Standby Architecture (picture for managers) Network Primary Database Site (production hardware) Standby Database Site (standby box) Damn Users (archived transactions in an OS file) Local Archive Redo Standby Database Managed Recovery Process Copied Archive Redo Primary Database ARCn (a background process) Network

12. Basic Physical Standby Architecture . Primary Database Production Site Standby Database Server Copied Archive Redo Copied Archive Redo Users Remote File Server (RFS) Fetch ArchiveLog (FAL) Oracle Net Primary Database LGWR Managed Recovery Process (MRP) ARCn On-line Redo Online Redo Standby Database Local Archive Redo Local Archive Redo

13. Implementing Data Guard in 10 Steps Assumptions • Two different servers • Identical mount points on both boxes • Both primary and standby will have same name • Using ARCn process in this example (not LGWR) • http://otn.oracle.com/oramag/oracle/02-nov/o62avail.html 1. Ensure primary database is in archive log mode 2. Take backup of primary database datafiles SQL> select * from v$datafile; • Cold (easiest) • Hot • RMAN • Do not backup controlfiles • Do not backup on-line redo logs

14. Implementation (continued) 3. Copy backup datafiles to standby server • If you use an online backup, ensure that you copy over any archive redo generated during the backup 4. Create a standby controlfile SQL> alter database create standby controlfile as ‘stbycf.ctl’; • This controlfile will be used by the standby database 5. Copy the standby controlfile to the standby box

15. Implementation (continued) 6. Configure primary init.ora or spfile # location of primary archive redo log_archive_dest_1=‘LOCATION=/ora01/oradata/BRDSTN/’ # location of archive redo on standby log_archive_dest_2=‘SERVICE=standby1 optional’ log_archive_dest_state_2=enable 7. Copy primary database init.ora file to standby box and make modifications for standby database # Ensure standby database is pointing at standby controlfile control_files=‘/ora01/oradata/BRDSTN/stbycf.ctl’ # Location of where archive redo logs are being written in standby environment standby_archive_dest=/ora01/oradata/BRDSTN # Enable archive gap management fal_client=standby1 fal_server=primary1

16. Implementation (continued) 8. Configure Oracle Net • Need Oracle Net connectivity from Primary to Standby and • Need Oracle Net connectivity from Standby to Primary • Primary database needs: • Listener • TNS entry for standby1 service • Standby database needs: • Listener • TNS entry for primary1 service

17. TNS File primary1 = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (PORT=1521) (HOST=primary_host)) (CONNECT_DATA=(SERVICE_NAME=BRDSTN))) standby1 = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (PORT=1521) (HOST=standby_host)) (CONNECT_DATA=(SERVICE_NAME=BRDSTN)))

18. Implementation (continued) 9. Startup and mount standby database SQL> startup nomount; SQL> alter database mount standby database; 10. Enable managed-recovery mode on standby SQL> alter database recover managed standby database disconnect;

19. Troubleshooting Implementation • Continuously view bottom of primary and standby alert.log files $ tail –f alert_BRDSTN.log • Most of problems are with getting Oracle Net setup correctly (fat fingering a TNS entry)

20. When Disaster Strikes • Ensure you apply all applicable redo to standby • Make the standby the primary • On standby 8i example: SQL> shutdown immediate; SQL> alter database mount standby database; SQL> alter database activate standby database; 9i many more options: SQL> alter database recover managed standby database finish;

21. When Disaster Strikes • Applications must change TNS to point to new database • Or change Oracle Names entry • Or use Oracle Net load balance feature brdstn= (DESCRIPTION = (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=tcp)(HOST=l3srv1)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=l3srv2)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=brdstn)) )

22. When Disaster Strikes • Java Thin Driver Example String connectInfo = "jdbc:oracle:thin:@" + "(DESCRIPTION =" + "(LOAD_BALANCE=on)" + "(ADDRESS=(PROTOCOL=tcp)(HOST=l3srv1)(PORT=1521))" + "(ADDRESS=(PROTOCOL=tcp)(HOST=l3srv2)(PORT=1521))" + "(CONNECT_DATA=(SERVICE_NAME=brdstn))" + ")";

23. Data Guard 8i New Features • Auto copy of redo to standby • Net8 • Initialization parameters • Auto apply of redo on standby • Enabled in standby database via SQL> alter database recover managed standby database;

24. Data Guard 8i New Features Read Only Mode: • Standby was mainly a disaster recovery and high availability solution • Some limited scalability features • Can now open database in read-only mode SQL> alter database open read only; • And then back to recovery mode SQL> alter database close; SQL> alter database recover managed standby database;

25. Data Guard 8i New Features • Read Only mode allows for SELECT statements to run against standby database • No DDL allowed in read-only mode • Applying of archive redo and read-only mode mutually exclusive • Can either be in read-only mode or recovery mode, but not both

26. Data Guard 9i New Features • Archive gap management • Advanced data protection modes • Automated propagation of datafile operations • User error protection • Database switchover/switchback • Cascading standby • Archive_lag_target parameter • Logical Standby (version 9.2 only) • Data Guard Broker

27. Archive Gap Management • What if network is down? • What if standby is down? • Primary continues to generate archive redo • Those unapplied redo logs make up an archive gap • When failure is resolved how is the standby caught up?

28. Archive Gap Management • In version 8, DBA has to figure out which archive redo logs make up the archive gap • Copy over archive redo logs • Issue recover command on standby SQL> alter database recover standby database; • Then automatic ship and apply can restart

29. Archive Gap Management • Gap detection crucial to high availability • 9i has new methods for gap resolution • FAL_CLIENT and FAL_SERVER initialization parameters initiate Fetch Archive Log (FAL) processes • If network or standby is down • FAL process waits until communication is re-established • Detects gap • Transmits and applies archive redo log

30. Data Protection Modes • Maximum protection • Guaranteed no-data-loss • Uses LGWR to transfer redo SQL> alter system set log_archive_dest_2=‘SERVICE=standby1 LGWR SYNC AFFIRM’; • Standby configured with its own redo logs SQL> alter database add standby logfile (‘/ora01/oradata/BRDSTN/sb_redo_01.log’) size 10m; • Maximum availability • Uses LGWR • Allows for temporary unavailability of standby • Maximum performance • Default mode • Uses either ARCn or LGWR for transferring redo

31. On-line Redo Online Redo Maximum Protection Architecture . Standby Database Server Primary Database Production Site Users Synch/Asynch writes Remote File Server (RFS) LGWR Oracle Net Primary Database Standby Online Redo ARCn Standby Database Managed Recovery Process Archive Redo Archive Redo Local Archive Redo Local Archive Redo ARCn

32. Propagation of Datafile Operations • Physical standbys have always supported all data types • Supported all insert/update/delete statements • Supported most DDL • Create table/view/index/sequence • Create user • Create package/procedure/function/trigger • Create role/synonym • Grants • Etc.

33. Propagation of Datafile Operations • One weakness of 8i was that add/drop tablespace/datafile commands were not automatically propagated • DBA had to intervene • Now automated in 9i • In standby initialization file standby_file_management = auto

34. Propagation of Datafile Operations Example: SQL> drop tablespace SHOLAY including contents and datafiles; • On standby the tablespace SHOLAY will be automatically dropped and all datafiles will automatically be deleted from disk • Note: If you want to rename a datafile, and have those changes applied to standby, DBA must intervene

35. User Error Protection • In 8i, DBAs often implemented a manual delay in application of redo • Create a shell script that • Copy archive redo • Wait 60 minutes before applying • Idea being that if user accidentally dropped/deleted/truncated data, the DBA could intervene before it was applied to standby

36. User Error Protection • 9i you get more robust methods to manage user errors • On standby SQL> recover managed standby database delay 60 disconnect; • Archive redo copied to standby when generated but not applied until 60 minutes later • Disable the delay via SQL> recover managed standby database nodelay;

37. Database Switchover • Database can be in one of two roles • Primary or standby SQL> select database_role from v$database; • A switchover allows you to reverse roles of primary and standby

38. Database Switchover • Database switchover is not a database failover • A failover is permanently making a standby database the primary database • Database switchovers would be ideal for rolling OS upgrades • Switchovers cannot be used for database upgrades

39. Cascading Redo Logs • Version 9.2 only • Standby can receive redo from another standby • Reduce load on primary • Can receive redo from a physical or logical standby • Setup standby redo log files • Ensure archiving enabled on physical standby

40. Archive_lag_target • Use archive_lag_target init parameter to ensure online redo is switching often enough • Specifies in seconds how often you want an online redo log switch to occur • Value in seconds • Valid values 0, or 60 through 7200 • A value of 0 (default) disables this feature • 1800 (30 minutes) seems reasonable

41. Logical Standby • Prior to 9.2 you could only have a physical standby • Primary and physical identical down to the block level • With 9.2 you can now create a logical standby with the Data Guard SQL Apply tool • Logical standby is available for SELECT access while applying transactions from the primary via SQL statements generated using LogMiner technology • Ideal for 7x24 reporting databases • Logical updates one or more log files behind the primary

42. Logical Standby Architecture . Primary Database Production Site Logical Database Standby Site Network User Enters Data User runs reports Logical Standby Process (LSP) Transforms Redo into SQL Production Primary Database SQL Apply Logical Database LGWR

43. Logical Standby • In event of disaster, logical standby can be turned into a primary database • Can create indexes and MVs to tune it • Doesn’t support longs and raws SQL> select * from dba_logstdby_unsupported; • Many steps (20+) involved with setting up logical standby • See MetaLink note: 186150.1

44. Data Guard Broker • Two components • Command line tool • Data Guard Manager OEM GUI tool • With Data Guard Manager, point and click your way through setting up standby environment • Create physical and logical standby databases • Perform switchovers and failovers • Monitor log transport and apply • Configure event driven reporting/e-mailing/paging

45. Data Guard Summary • Relies on tried-and-true standby technology • Ideal for disaster recovery and high availability • Fairly low cost • DBA needs to have backup and recovery skills to create and maintain • Many new Data Guard features in Oracle9i • Now has some scalability features