1 / 61

DATA GUARD

DATA GUARD. PRESENTOR:- KAMRAN SIDDIQUE SENIOR CONSULTANT/ARCHITECT (HA EXPERT) CGI DON’T WAIT OPPORTUNITY TO CREATE TECHNOLOGY…USE TECHNOLOGY TO CREATE OPPORTUNITY. AGENDA. BASIC CONCEPTS Basic Steps for Creating a Physical Standby database MY TRICKS.. REFERENCES QUESTIONS.

norm
Download Presentation

DATA GUARD

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DATA GUARD PRESENTOR:- KAMRAN SIDDIQUE SENIOR CONSULTANT/ARCHITECT (HA EXPERT) CGI DON’T WAIT OPPORTUNITY TO CREATE TECHNOLOGY…USE TECHNOLOGY TO CREATE OPPORTUNITY

  2. AGENDA • BASIC CONCEPTS • Basic Steps for Creating a Physical Standby database • MY TRICKS.. • REFERENCES • QUESTIONS

  3. WHAT IS DATA GUARD • A software • An extension to Oracle RDBMS • Protects DATA • Takes data and automatically puts it elsewhere. • Makes DATA available for Failover. • Maintain larger Distances. • Automatic resync with Production database. • High availability, data protection, and disaster recovery for enterprise data.

  4. What is a Standby Database ??? • Transactionally consistent copy of the primary database. • Using a backup copy of the primary database. • Up to 30 Standby Databases and integrations with DG . • Once created, Data Guard automatically maintains each standby database by transmitting redo data from the primary database and then applying the redo to the standby database. • Can be a SINGLE-INSTANCE OR RAC.

  5. Primary Database • DG configuration contains one Production database • Functions as Primary Role. • Mainly accessed by Production applications. • Can be a Single-instance or RAC

  6. TYPES OF STANDBY DATABASES 1-PHYSICAL STANDBY • A physical standby database is kept in sync with the primary database by using media recovery to apply redo that was generated on the primary database. • A block-for-block identical copy of the primary database. • Excellent choice for disaster recovery. • Data will be intact and consistent with data that existed on the primary. • Can receive and apply redo in read only-access (11.1.0).

  7. TYPES OF STANDBY DATABASESContinue… 2-LOGICAL STANDBY DATABASE • Contains the same logical information as the production database but physical organization and structure of the DATA can be different. • synchronization with the primary database through SQL Apply. • Transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database. • Can be used for Reporting. • Upgrade/Migration purpose with no downtime. • Patch sets with no downtime.

  8. Snapshot Standby Database11.1.0 • A fully updatable standby database. • Receives and archives redo data from a primary database (like physical/logical) • Does not apply the redo data that it receives (Differs from physical/logical) • Redo apply starts once standby is converted into physical standby. • Temporary/updatable snapshot of a physical standby. • Conversion to physical standby could be a lengthy process.

  9. My Trick…Different word sizes • Database Version = 10.2.0.4 Enterprise 64-bit • Primary platform = Linux 64-bit)Secondary platform= Linux 32-bit) • You can not use DG Broker tool to manage this environment. • This restriction is lifted from Data Guard 11g onward.

  10. Data Guard Configurations • one production database and one or more standby databases • connected by Oracle Net • may be dispersed geographically • No restriction on the distance. • SQL command-line • Data Guard Broker interface (DGMGRL) • GUI interface integrated with EM.

  11. What is Data Guard Broker • A distributed management framework that automates the creation, maintenance, and monitoring of Data Guard configurations. • Oracle Enterprise Manager graphical user interface (GUI) or the Data Guard command-line interface (DGMGRL) • Create and enable Data Guard configurations • Manage an entire Data Guard configuration from any system in the configuration • Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases • Simplify switchovers and failovers • Enable fast-start failover to fail over automatically when the primary database becomes unavailable.

  12. Data Guard Services 1-Redo Transport Services • Transmit redo data from the primary system to the standby systems . • Resolving any gaps in the archived redo log files due to a network failure. • Automatically detects missing or corrupted archived redo log files on a standby system . • Automatically retrieve replacement archived redo log files from the primary database or another standby database

  13. Data Guard Services 2-Apply Services • Automatically apply the redo data on the standby database to maintain consistency with the primary database. • Allows read-only access to the data

  14. Difference between physical and Logical Standby..with respect to services • Physical standby---Data Guard uses Redo Apply technology. • Physical standby-- applies redo data on the standby database using standard recovery techniques of an Oracle database. • Logical Standby--Data Guard uses SQL Apply technology • first transforms the received redo data into SQL statements • Then executes the generated SQL statements on the logical standby database

  15. Data Guard Services 3-Role Transitions Switchover --role reversal between the primary database and one of its standby databases. Switchover –No Data Loss Failover --failure of the primary database Failover --transition of a standby database to the primary role Failover –Can configure Data Guard to ensure no data loss.

  16. My Trick…Compatibility • Both primary and standby databases must be set at the same compatibility mode as the minimum release

  17. My Trick…Different binary-level • A standby database cannot be open read-only in any environment that has binary-level PL/SQL-related incompatibilities between primary and standby databases

  18. Data Guard Protection Modes 1-Maximum Protection • Redo data needed to recover a transaction must be written to both the online redo log and to the standby redo log on at least one synchronized standby database before the transaction commit • Zero Data Loss • Processing on primary is suspended if no standby is available.

  19. Data Guard Protection Modes 2-Maximum Availability • Minimal Data Loss • Similar to Maximum protection mode • If not standby database ,primary operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.

  20. Data Guard Protection Modes 3-Maximum Performance • Default protection mode. • Highest level of data protection that is possible without affecting the performance of a primary database • Asynchronous in terms of transaction commitment. • primary database performance is unaffected by delays in writing redo data to the standby database(s).

  21. My trick..Architectural Touch...RAC and DATA GUARD topologies • Symmetric configuration with RAC at all sites Same number of instances • Asymmetric configuration with RAC at all sites Different number of instances • Asymmetric configuration with mixture of RAC and single instance Some single-instance sites not running under Oracle Clusterware Metalink note 413484.1

  22. My Trick…Transient Logical Database Rolling Upgrades • Beginning with Oracle Database 11.1.0.7, a physical standby database can be used to execute a rolling database upgrade to a new Oracle Patch Set or database release by using the transient logical rolling database upgrade process

  23. Basic Steps for Creating a Physical Standby database

  24. HA VM • Two Vmwares (2.6.32-100.26.2.el5) • 11.2.0.3.0 • Primary ASM • Secondary ASM • Primary Database Name => Primary • Primary DB_UNIQUE_NAME => Primary • Standby DB_UNUIQUE_NAME =>Secondary • Primary Hostname =>primary.localdomain • Secondary Hostname => secondary.localdomain

  25. Step 1Primary Site • Enable Force Logging SQL> ALTER DATABASE FORCE LOGGING; Database altered.

  26. Step 2 • On the primary node, connect to the primary database and create a PFILE from the SPFILE • create pfile='/home/oracle/stage/secondary.ora' from spfile;

  27. Step 3 • On the primary node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory • RMAN> BACKUP DEVICE TYPE DISK FORMAT '/opt/oracle/stage/%U' DATABASE PLUS ARCHIVELOG; • RMAN> BACKUP DEVICE TYPE DISK FORMAT '/opt/oracle/stage/%U' CURRENT CONTROLFILE FOR STANDBY;

  28. Step 4:-Initialization parameters • DB_UNIQUE_NAME • LOG_ARCHIVE_CONFIG • LOG_ARCHIVE_DEST_2 • DB_FILE_NAME_CONVERT • LOG_FILE_NAME_CONVERT • STANDBY_FILE_MANAGEMENT • FAL_SERVER • FAL_CLIENT • SERVICE_NAMES • DB_RECOVERY_DEST • LOG_ARCHIVE_DEST_1

  29. Example

  30. CONFIGURE PRIMARY DATABASE FOR DG...Example • log_archive_config='dg_config=(VANCOUVER_DG,CALGARY_DG)' • log_archive_dest_2='service=VANCOUVER valid_for=(online_logfiles,primary_role) db_unique_name=VANCOUVER_DG' • db_file_name_convert='+DATA/VANCOUVER/',’+DATA/CALGARY/', ’+RECOVERY/VANCOUVER’,’+RECOVERY/CALGARY’ • log_file_name_convert='+DATA/VANCOUVER/',’+DATA/CALGARY/', ’+RECOVERY/VANCOUVER’,’+RECOVERY/CALGARY’ • standby_file_management=auto • fal_server=‘VANCOUVER' • fal_client='CALGARY' • service_names=CALGARY

  31. Step 6 :-CONFIGURE ORACLE NET SERVICES PRIMARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.105)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = primary) ) ) ================================================= SECONDARY = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.106)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = secondary) ) )

  32. Step 5 Create password file for standby database orapwdfile=orapwstandby password=xxxx Password must be same as Primary password file OR Copy primary password file over standby site.

  33. Step 7- From the standby host, duplicate the primary database • $ rman target sys/oracle@primary auxiliary / • RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;

  34. Step 8 Add standby redo logs to standby database • The recommended number of standby redo logs is: (maximum # of logfiles +1) * maximum # of threads • If uses two online log files for each thread. the number of standby redo logs should be (2 + 1) * 1 = 3. That is, one more standby redo log file for each thread.

  35. Step 9:- • Start managed recovery on the standby database: • SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

  36. CONFIGURE PRIMARY DATABASE FOR DG...Example • Create standby redo logs on the primary database to support the standby role • SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 SIZE 10M, GROUP 6 SIZE 10M, GROUP 7 SIZE 10M; • SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 SIZE 10M, GROUP 9 SIZE 10M, GROUP 10 SIZE 10M;

  37. My Trick… • Data Guard configuration includes a primary database and up to 30 standby databases. These standby databases may be a mix of physical and logical standby databases.

  38. Applying Redo Data to Physical Standby Databases • To start log apply services on a physical standby database ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; • If the real-time apply feature is enabled, apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

  39. END PRODUCT… • DGMGRL> show configuration; Configuration - DRSOLUTION Protection Mode: MaxPerformance Databases: PRIMARY - Primary database SECONDARY - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS

  40. My Trick… • It is not necessary that the primary and the standby both be Oracle RAC databases, or both use ASM. • For example, the primary database may be running Oracle RAC with or without ASM, and the standby database(s) may be single-instance, with or without ASM. • Furthermore, the versions of ASM and CRS do not need to be the same between the primary and standby systems.

  41. My Trick … • Exadata Database Machine: It is transparent to Data Guard whether primary and/or standby databases reside on an Exadata Database Machine or on other hardware, as long as the platform ID's of primary and standby systems within the same Data Guard configuration conform to the support requirements. • If Exadata Hybrid Columnar Compression (EHCC) is used, it is strongly recommended that both primary and standby databases reside on Exadata.

  42. Data Dictionary Verify Managed Recovery is Running (non-broker) on the standbySELECT PROCESSFROM V$MANAGED_STANDBYWHERE PROCESS LIKE 'MRP%'; SELECT DELAY_MINSFROM V$MANAGED_STANDBYWHERE PROCESS = 'MRP0'; SQL> select database_role,protection_level,fs_failover_status from v$database; V$DATAGUARD_STATUS V$LOG &V$STANDBY_LOG…REDO LOG CHANGED. V$MANAGED_STANDBY..RECOVERY PROGRESS Select max(sequence#),thread# from v$archived_log where applied=‘YES’ group by thread#; Secondary Select max(sequence#) from v$log;......primary

  43. Configuring Data Guard Broker • Dgmgrl / • Connect sys/password@primary/secondary • DGMGRL> CREATE CONFIGURATION 'DRSolution' AS PRIMARY DATABASE IS 'North_Sales' CONNECT IDENTIFIER IS North_Sales.foo.com; • DGMGRL> ADD DATABASE 'DR_Sales' AS CONNECT IDENTIFIER IS DR_Sales.foo.com MAINTAINED AS PHYSICAL;

  44. Switchover • DGMGRL> SWITCHOVER TO STANDBY; • Site "standby" was not found • DGMGRL> SWITCHOVER TO "SECONDARY"; • Performing switchover NOW, please wait... • New primary database "SECONDARY" is opening... • Operation requires shutdown of instance "primary" on database "PRIMARY" • Shutting down instance "primary"... • ORA-01109: database not open • Database dismounted. • ORACLE instance shut down. • Operation requires startup of instance "primary" on database "PRIMARY" • Starting instance "primary"...

  45. MY TRICK…………………Additional Role Transition Procedures when using mixed Oracle binaries: • 1. Shutdown the new primary databaseIf using RAC then set database to unclustered: SQL> alter system set cluster_database=false scope=spfile;SQL> shutdown immediate2. Startup the new primary in UPGRADE modeSQL> startup upgrade3. Invalidate existing PL/SQL modules in preparation to convert the format required by the new primary. • SQL> spool utlirp.logSQL> @?/rdbms/admin/utlirpSQL> spool off4. Restart the databaseIf using RAC then set database back to clustered: SQL> alter system set cluster_database=true scope=spfile;SQL> shutdown immediateSQL> startup 5. Recompile all invalid PL/SQL modules to be in the format required by the new primary database.SQL> spool utlrp.logSQL> @?/rdbms/admin/utlrpSQL> spool off6. Verify that all expected packages and classes are valid.SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';SQL> SELECT distinct object_name FROM dba_objects WHERE status='INVALID';

  46. Restore point • An implicit guaranteed restore point is created when a physical standby database is converted into a snapshot standby database and this restore point is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database.

  47. Steps to convert Physical Standby Database to the Snapshot Standby Database • STEP 1 A- If not already configured , configure flash recovery area; Alter system set db_recovery_file_dest_size=<size> B- Set Flash recovery area. Alter system set db_recovery_file_dest=<path> db_recovery_file_dest string +FLASH db_recovery_file_dest_size big integer 3882M

  48. Steps to convert Physical Standby Database to the Snapshot Standby DatabaseContinue... • Step 2 Bring the physical standby database to mount stage. • Step 3 Stop managed recovery if it is active. SQL> alter database recover managed standby database cancel; Database altered.

  49. Steps to convert Physical Standby Database to the Snapshot Standby DatabaseContinue... • Step 4 Convert physical standby database to snapshot standby database. ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; The database is dismounted during conversion and must be restarted.

  50. Steps to convert Physical Standby Database to the Snapshot Standby DatabaseContinue... • SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2217952 bytes Variable Size 507512864 bytes Database Buffers 322961408 bytes Redo Buffers 2412544 bytes Database mounted. Database opened. SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------- READ WRITE SNAPSHOT STANDBY

More Related