Online DBA

<<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_DI

<<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 of MaxAva

<<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 use any of the 3 listed above rather keeping it more easy. Advice :- There are lots of Parameter (Variables) and Values used in this post are specific to my Test Environment. Please Chan

<<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 Backup & Recovery Main Page ORA-19505: failed to identify file "": RMAN Duplicate Cause: Missing directory structure on auxiliary database server Solution: Identify all missing directories and create them. All the directory structures for data files on target systems must be available on auxiliary server as well. On Target  SQL> select name from v$datafile; data/ORCL1P/dbfile/SYSTEM_01.DBF data/ORCL1P/dbfile/pdbseed/ORCL1P/SYSTEM_01.DBF data/ORCL1P/dbfile/SYSAUX_01.DBF data/ORCL1P/dbfile/pdbseed/ORCL1P/SYSAUX_01.DBF data/ORCL1P/dbfile/UNDO_01.DBF data/ORCL1P/dbfile/pdbseed/ORCL1P/UNDO_01.DBF data/ORCL1P/dbfile/USERS_01.DBF data/ORCL1P/dbfile/PDB01/ORCL1P/SYSTEM_01.DBF data/ORCL1P/dbfile/PDB01/ORCL1P/SYSAUX_01.DBF data/ORCL1P/dbfile/PDB01/ORCL1P/UNDO_01.DBF .. .. On Auxiliary  mkdir -p data/ORCL1P/dbfile/ mkdir -p data/ORCL1P/dbfile/pdbseed/ORCL1P/ mkdir -p data/ORCL1P/dbfile/PDB01/ORCL1P/ ... .. Starting backup at 07-JUL-20 channel prmy1: startin

<<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 ASM Main Page Changing Failgroup Of an ASM Disk In many situations its required to alter the failgroup of a disk in an ASM diskgroup, for example while adding the disk you did typo while typing the failgroup name and you realized only after the disk is added to wrong failgroup. There is no other way but only drop and add the disk to right failgroup. Steps are here Step1: Identify the disks in wrong failgroup. set line 200 col name format a20 col path format a40 select path,failgroup,mount_status,mode_status,header_status,state from v$asm_disk where GROUP_NUMBER=1 order by failgroup, path; Step2: Drop the disks (identified disks) in wrong failgroup ALTER DISKGROUP DATA DROP DISK  DC1_DISK11 REBALANCE POWER 20; Step3: Wait for Rebalance Operation to complete select GROUP_NUMBER,OPERATION,STATE,EST_WORK,EST_MINUTES from gv$asm_operation; Step4: Add the disk in correct failgroup ALTER DISKGROUP DATA ADD FAILGROUP DC01 DISK 'ORCL:DC01_DISK11' NAME DC01_DISK11

<<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;