Online DBA

<<Back to Oracle Backup & Recovery Main Page LGWR: Minimum of 1 LGWR standby database required- RMAN Restore After database restore when tried to open , encountered below error. SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 17034 Session ID: 387 Serial number: 7 Checking at Alterlog LGWR: Minimum of 1 LGWR standby database required Errors in file /ora_log/diag/rdbms/shfus1pp/SHFUS1P/trace/SHFUS1P_lgwr_17009.trc: ORA-16072: a minimum of one standby database destination is required Sun Jan 17 11:14:29 2021 ARC1 started with pid=20, OS id=17046 LGWR (ospid: 17009): terminating the instance due to error 16072 Sun Jan 17 11:14:30 2021 System state dump requested by (instance=1, osid=17009 (LGWR)), summary=[abnormal instance termination]. System State dumped to trace file /ora_log/diag/rdbms/shfus1pp/SHFUS1P/trace/SHFUS1P_diag_16999_20210117111430.trc Dumping diagnostic data in directory=[cdmp_2021011...

<<Back to Oracle Data Guard Broker Page Enable Auto Reinstate of Crashed Primary In case of Failover Setting FastStartFailoverAutoReinstate  property value TRUE enables the feature of Reinstating the failed primary database in case of automatic failover. Valid value TRUE or FALSE Broker default TRUE DGMGRL> edit configuration set property FastStartFailoverAutoReinstate=TRUE; Property "FastStartFailoverAutoReinstate" updated NOTE: Auto Reinstate only succeed if flashback is enabled and necessary Archivelog/flshabcklogs are available

<<Back to Oracle Data Guard Broker Page Oracle Dataguard Broker ObserverReconnect  Property  This configuration property specifies how often the observer establishes a new connection to the primary database. Valid values >=0 Broker default 0 If ObserverReconnect=0 then the observer creates and maintains a connection to the primary database all the time. DGMGRL> edit configuration set property ObserverReconnect=60 ; Property "ObserverReconnect" updated

<<Back to Oracle Data Guard Broker Page Oracle Dataguard Broker CommunicationTimeout  Property  configuration property allows us to decide how many seconds the broker should wait before timing out its network communication between databases in the configuration. A value of zero indicates that a network communication should never be timed out. Valid values >= 0 Broker default 180 seconds DGMGRL> DGMGRL> edit configuration set property CommunicationTimeout=240; Property "communicationtimeout" updated

<<Back to Oracle DataGuard Main Page How to Synchronize Physical Standby Database Using Service Primary DB Unique Name: ORCL1PP Standby DB Unique Name: ORCL1PS Step1: Place the physical standby database in MOUNT mode If Active Dataguard is Used SQL>  SHUTDOWN IMMEDIATE; SQL>  STARTUP MOUNT; Step2: Stop Managed Recovery Process  SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Or if Broker is configured   DGMGRL> edit database 'ORCL1PS' set STATE='APPLY-OFF' ; Step3: Connect to Standby database over Service rman target SYS/<password>@ORCL1PS connected to target database: ORCL1PS (DBID=4165840403, not open) Step4: Recover From Primary Database Service  RMAN> recover database from service "ORCL1PP" noredo using compressed backupset; Starting recover at 06-JUL-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=318 device type=DISK allocated channel: ORA...

<<Back to Oracle Data Guard Broker Page How to Enable fast Start Failover in Maximum Performance Mode FSFO can be enabled even if the configuration is operating in Maximum Performance mode by setting configuration Property  FastStartFailoverLagLimit Configuration property FastStartFailoverLagLimit allows to occur failover to Physical standby database even if it is lagging behind Primary database. The value is defined in seconds. If the physical standby database is lagging behind Primary with in this limit and Primary is not available, Failover can occur. In this situation it is acceptable by business to have data loss. The value for FastStartFailoverLagLimit must not be less than RPO (recovery point objective) defined by business.The Property is used only if the configuration is operating in MaxPerformance mode and FSFO is enabled. B Broker Default value  is 30 seconds Minimum configurable value is 5 sec Value 0 means property is ignored and is the default value in case ...

<<Back to Oracle Data Guard Broker Page How to Define Dataguard Broker Tracing Level Configuration property TraceLevel is used to control the amount of tracing performed by broker for every member in the configuration Values : USER | SUPPORT Broker Default USER TraceLevel= USER := Traces only completed , warning and error messages of an operation TraceLevel= SUPPORT := Traces more detailed operation messages needed by oracle support DGMGRL> edit configuration set property TraceLevel=SUPPORT; Property "tracelevel" updated

<<Back to Oracle Data Guard Broker Page How to Define Oracle Dataguard Broker Health Check Timeout OperationTimeout specifies the maximum time period (in seconds ) the broker should wait for health check, get property and set property operation to complete. Value >=30 and <=3600 Broker Default 30 Seconds DGMGRL> edit configuration set property OperationTimeout=120; Property "operationtimeout" updated NOTE: if OperationTimeout is set too low for your environment or sometimes due to intermittent network issue , you will notice below error in broker log file. DMON: Operation CTL_GET_STATUS stalled during phase BEGIN. Error=ORA-16713

<<Back to Oracle DataGuard Main Page How to Configure Observer and Enable Fast Start Failover for Oracle Dataguard The article covers configuring observer on Windows Server only. Although setting up Observer on Linux to Monitor your DG setup for automatic Failover doesn't differ much from Windows, Installing Observer on Linux will be covered in different post. To install broker on Windows Server,  The first requirement is to find a way to run the  observer process in background. You can use one of following methods for the same.             1. HSTART ( Hidden Start ) software (keep in mind its not free)              2. SilentCMD (its free)              3. Windows task Scheduler. Lets pick up the easiest way and quickly configure Observer on Windows without wasting much time. I am not going to u...

<<Back to Oracle Data Guard Broker Page FastStartFailoverThreshold: Define Failover Time  Failover occurs if both observer and target standby database lose connection with Primary database for the period of time specified by FastStartFailoverThreshold property. The value of the property is in seconds. Default value is 30 seconds and lowest possible value is 6 seconds. DGMGRL> edit configuration set property FastStartFailoverThreshold=300; Property "faststartfailoverthreshold" updated The command above sets the FastStartFailoverThreshold to 5min. This means, the failover will occur after 5min after Observer and Target Standby lose the connection with Primary. If you want to wait for longer duration to initiate the failover, specify larger value for FastStartFailoverThreshold property. Here is list of other properties related to Fast Start Failover. In most of the cases the default value is sufficient. FastStartFailoverLagLimit FastStartFailoverPmyShutdown FastStartFai...

<<Back to Oracle DataGuard Main Page ORA-16905 Physical Standby Database is disabled DGMGRL> show configuration; Configuration - DG_ORCL1P   Protection Mode: MaxPerformance   Members:   ORCL1PP - Primary database     ORCL1PS - Physical standby database (disabled)       ORA-16905: The member was not enabled yet. Fast-Start Failover:  Disabled Configuration Status: SUCCESS   (status updated 58 seconds ago) DGMGRL> DGMGRL> enable database 'ORCL1PS'; Enabled. DGMGRL>  show configuration; Configuration - DG_ORCL1P   Protection Mode: MaxPerformance   Members:   ORCL1PP - Primary database     ORCL1PS - Physical standby database Fast-Start Failover:  Disabled Configuration Status: SUCCESS   (status updated 38 seconds ago)

<<Back to Oracle DataGuard Main Page Recover Standby Database from non Default Archive Location You can use below command to start recovering the standby database from non default archive location. The command is useful if you restored/copied the archive logs to a non default location due to space constraints    SQL> RECOVER FROM '/ora_arch_stage/ORCL1P/arc1' STANDBY DATABASE;

<<Back to Oracle Data Guard Broker Page How Oracle Dataguard Broker Works Oracle Dataguard Broker has server side and client side components. At client side it has OEM and DGMGRL. Both the utilities can be used to read and manipulate Broker Configuration data. At server Side it has got 2 components 1> Binary Configuration File 2> DMON process DMON processes is responsible to monitor the health of the broker configuration and ensures that every database has a consistent description of the configuration. To Ensure this DMON process persistently maintains information about all members of the broker configuration in a binary configuration file. In Configuration file Broker stores various properties associated with each database to control the database's behavior. Whenever you add databases to a broker configuration, or make a change to an existing database's properties, each DMON process records the new information in its copy of the configuration file. The param...

<<Back to Oracle Data Guard Broker Page Understanding  Oracle Data Guard Broker and DGMGRL Utility Oracle Data Guard Broker is an Utility which logically groups all the dataguard members in oracle dataguard configuration to centrally  manage and monitor them together as an integrated unit. Oracle Dataguard Broker stores the necessary information in a file locally on each dataguard member server. The file location is configured using following database parameters dg_broker_config_file1 dg_broker_config_file2 The content of above the file is accessed and managed by Dataguard Monitor  ( DMON ) Process. The DMON process is an Oracle background process that runs on every database instance that is managed by the broker. The start/stop of DMON process (which interns enables or disables) broker is managed by database  parameter  called dg_broker_start , the value of which is TRUE (enabled) or FALSE (disabled). The broker configuration can be queried and/o...

<<Back to Oracle DataGuard Main Page How to Resolve : ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added DGMGRL>  ADD DATABASE "ORCL3PS" AS CONNECT IDENTIFIER IS "ORCL3PS"; Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added Cause: One of log_archive_dest_n parameter is set for service  ORCL3PS in either primary or standby or both databases Solution: Identify the log_archive_dest_n parameter which is set to value  ORCL3PS  and rest it. SQL> show parameter dest_4 log_archive_dest_4                   string      SERVICE=ORCL3PS.EU.MERCKGROUP.C                                         ...

<<Back to Oracle DataGuard Main Page To use sqlplus instead broker follow the steps on this blog HOW TO CONVERT PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE USING SQLPLUS HOW TO CONVERT PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE USING DGMGRL- Dataguard Broker Utility Current Configuration Primary DB: ORCL1PP Standby DB: ORCL1PS Connect to primary database  over listener dgmgrl sys/xxxxx@ORCL1PP.ORACLE.COM Disable Fast Start Failover if enabled DGMGRL> DISABLE FAST_START FAILOVER; Change to maximum performance: DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE; Convert to snapshot standby DGMGRL> CONVERT DATABASE ORCL1PS TO SNAPSHOT STANDBY; CONVERING BACK THE SNAPSHOT STANDBY TO PHYSICAL STANDBY USING DGMGRL- Dataguard Broker Utility Connect to primary database  over listener dgmgrl sys/xxxxx@ORCL1PP.ORACLE.COM Convert to standby DGMGRL> CONVERT DATABASE ORCL1PS TO PHYSICAL STANDBY; Change back...

<<Back to Oracle DataGuard Main Page HOW TO CONVERT PHYSICAL STANDBY DATABASE TO SNAPSHOT STANDBY DATABASE MANUALLY USING SQLPLUS To open the physical standby database as snapshot standby, Flashback must be on. If not already set, set db_recovery_file_dest and db_recovery_file_dest_size parameter to enable the flashback. SQL> alter system set db_recovery_file_dest='/ora_data/orcl/flash'; System altered. SQL>alter system set db_recovery_file_dest_size=30G; System altered. SQL> recover managed standby database cancel; Media recovery complete. SQL> shutdown immediate; SQL> startup mount; SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; Database altered. SQL> Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database; NAME      OPEN_MODE     ...