PRESENTOR:- KAMRAN SIDDIQUE SENIOR CONSULTANT/ARCHITECT (HA EXPERT) CGI
PRESENTOR:- KAMRAN SIDDIQUE SENIOR CONSULTANT/ARCHITECT (HA EXPERT) CGI DON’T WAIT OPPORTUNITY TO CREATE TECHNOLOGY…USE TECHNOLOGY TO CREATE OPPORTUNITY BASIC CONCEPTS Basic Steps for Creating a Physical Standby database MY TRICKS.. REFERENCES QUESTIONS 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. 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. DG configuration contains one Production database Functions as Primary Role. Mainly accessed by Production applications. Can be a Single-instance or RAC 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). 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. 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. 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. 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. 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. 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 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 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 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. Both primary and standby databases must be set at the same compatibility mode as the minimum release A standby database cannot be open readonly in any environment that has binarylevel PL/SQL-related incompatibilities between primary and standby databases 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. 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. 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). 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 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 Basic Steps for Creating a Physical Standby database 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 Enable Force Logging SQL> ALTER DATABASE FORCE LOGGING; Database altered. 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; 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; 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 Before After db_unique_name=CALGARY_DG db_unique_name=VANCOUVER_DG INSTANCE_NAME=CALGARY instance_name=VANCOUVER log_archive_config='dg_config=(VANCOUVER_DG,CALGARY_ DG) log_archive_dest_2='service=CALGARY valid_for=(online_logfiles,primary_role) db_unique_name=CALGARY_DG db_file_name_convert='+DATA/CALGARY','+DATA/VANCOUVER','+RECOVERY/CALGARY','+RECOVERY/V ANCOUVER' log_file_name_convert='+DATA/CALGARY','+DATA/VANCOUVER','+RECOVERY/CALGARY','+RECOVERY/V ANCOUVER' standby_file_management=auto fal_server='CALGARY' fal_client='VANCOUVER' service_names='VANCOUVER' 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/CALGAR Y/', ’+RECOVERY/VANCOUVER’,’+RECOVERY/CALGARY’ log_file_name_convert='+DATA/VANCOUVER/',’+DATA/CALGA RY/', ’+RECOVERY/VANCOUVER’,’+RECOVERY/CALGARY’ standby_file_management=auto fal_server=‘VANCOUVER' fal_client='CALGARY' service_names=CALGARY 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) ) ) Create password file for standby database orapwd file=orapwstandby password=xxxx Password must be same as Primary password file OR Copy primary password file over standby site. $ rman target sys/oracle@primary auxiliary / RMAN> DUPLICATE TARGET DATABASE FOR STANDBY; 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. Start managed recovery on the standby database: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 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; 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. 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; DGMGRL> show configuration; Configuration - DRSOLUTION Protection Mode: MaxPerformance Databases: PRIMARY - Primary database SECONDARY - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS 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. 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. Verify Managed Recovery is Running (non-broker) on the standby SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%'; SELECT DELAY_MINS FROM V$MANAGED_STANDBY WHERE 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 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; 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"... 1. Shutdown the new primary database If using RAC then set database to unclustered: SQL> alter system set cluster_database=false scope=spfile; SQL> shutdown immediate 2. Startup the new primary in UPGRADE mode SQL> startup upgrade 3. Invalidate existing PL/SQL modules in preparation to convert the format required by the new primary. SQL> spool utlirp.log SQL> @?/rdbms/admin/utlirp SQL> spool off 4. Restart the database If using RAC then set database back to clustered: SQL> alter system set cluster_database=true scope=spfile; SQL> shutdown immediate SQL> startup 5. Recompile all invalid PL/SQL modules to be in the format required by the new primary database. SQL> spool utlrp.log SQL> @?/rdbms/admin/utlrp SQL> spool off 6. 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'; 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. STEP 1 AIf not already configured , configure flash recovery area; Alter system set db_recovery_file_dest_size=<size> BSet Flash recovery area. Alter system set db_recovery_file_dest=<path> db_recovery_file_dest db_recovery_file_dest_size string +FLASH big integer 3882M 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. 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. 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 Step 1 Shutdown the snapshot standby database. Step 2 Bring the database to the mount stage. Step 3 Issue the command ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Step 3 Issue the command ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Step 4 :Shutdown the database and mount it . Step 5 Start the media recovery process. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount Database mounted. SQL> alter database convert to physical standby; Database altered. SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount Redo Buffers 2412544 bytes Database mounted. SQL> select open_mode,database_role from v$database; OPEN_MODE DATABASE_ROLE -------------------- ---------------MOUNTED PHYSICAL STANDBY SQL> alter database recover managed standby database disconnect; DGMGRL> CONVERT DATABASE "SECONDARY" TO SNAPSHOT STANDBY; Converting database "SECONDARY" to a Snapshot Standby database, please wait... Database "SECONDARY" converted successfully DGMGRL> CONVERT DATABASE "SECONDARY" TO PHYSICAL STANDBY; Converting database "SECONDARY" to a Physical Standby database, please wait... Operation requires shutdown of instance "secondary" on database "SECONDARY" Shutting down instance "secondary"... ORA-01109: database not open Database dismounted. ORACLE instance shut down. Operation requires startup of instance "secondary" on database "SECONDARY" Starting instance "secondary". Create Restore Point CREATE restore point before_rw guarantee flashback database; Failover the standby database: ALTER DATABASE ACTIVATE STANDBY DATABASE Open Database in Read Write Mode: Revert database back to standby mode: Shutdown the database; Startup database in mount mode; Flashback database; FLASH BACK DATABASE TO RESTORE POINT before_rw; ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Scenario 1: Creating a Configuration Scenario 2: Setting Database Properties Scenario 3: Enabling the Configuration and Databases Scenario 4: Setting the Configuration Protection Mode Scenario 5: Performing Routine Management Tasks Scenario 6: Performing a Switchover Operation Scenario 7: Performing a Failover Operation Scenario 8: Monitoring a Data Guard Configuration http://www.oracle.com/technetwork/database/fe atures/availability/s291923-2hughesallstatemaa-133334.pdf http://www.oracle.com/technetwork/database/fe atures/availability/s291923-2hughesallstatemaa-133334.pdf http://www.oracle.com/technetwork/database/fe atures/availability/data-guard-esg-casestudies-133297.pdf http://streaming.oracle.com/ebn/podcasts/db_i nsider/media/7548105_bank_of_aus_040709.mp 3 Metalink note 413484.1 For any further information contact me at Kamran.siddique@cgi.com
© Copyright 2024