Soumya's Database Blog

May 22, 2026

Oracle ADRCI Utility Explained with Practical Examples on Windows

Recently, while troubleshooting an Oracle issue on a Windows server, I realized how many DBAs still depend only on alert logs and trace file to find out issues in database.

Finding the right trace file manually can become painful, especially when multiple Oracle homes, listener logs, and incidents are involved.

This is where ADRCI (Automatic Diagnostic Repository Command Interpreter) becomes extremely useful.

I will show a handful commands which is very useful

1. To know the current base location:-

C:\Windows\system32>adrci

ADRCI: Release 19.0.0.0.0 - Production on Fri May 22 19:27:21 2026

ADR base = "D:\oracle"

adrci> show base

ADR base is "D:\oracle"

2. List out homes available

adrci> show homes

ADR Homes:

diag\clients\user_svc_concepts\host_3809360924_110

diag\clients\user_svc_concepts\RMAN_3809360924_110

diag\clients\user_SYSTEM\host_3809360924_110

diag\rdbms\orcl\orcl

diag\tnslsnr\VSCSDB\listener

3. To set a specific home

adrci> set home diag\rdbms\orcl\orcl

4. View alert log

adrci> show alert

ADR Home = D:\oracle\diag\rdbms\orcl\orcl:

*************************************************************************

Output the results to file: C:\Users\SVC_CO~1\AppData\Local\Temp\alert_16312_16036_orcl_1.ado

Following command will tail the alert log

adrci> show alert -tail 100 -f

2026-05-22 20:04:31.421000 +04:00

nt secondary err code: 0

nt OS err code: 0

Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.60.85)(PORT=57204))

WARNING: inbound connection timed out (ORA-3136)

2026-05-22 20:05:35.659000 +04:00

…..

adrci> show alert -p "message_text like '%ORA-%'"

ADR Home = D:\oracle\diag\rdbms\orcl\orcl:

*************************************************************************

Output the results to file: C:\Users\SVC_CO~1\AppData\Local\Temp\alert_11648_5432_orcl_4.ado

5. Purge alerts and trace files

adrci> purge -age 1200 -type ALERT (This purges all alerts older than 1200 minutes)

adrci> purge -age 43200 -type INCIDENT(This purges all INCIDENT older than 43200 minutes OR 30 days)

adrci> purge -age 21600 (This purges everything older than 21600 minutes OR 15 days)

adrci> purge -age 10080 -type cdump(This purges cdump older than 10800 minutes OR 7 days)

ADR automatically purges information from the repository. You can see the defaults using the SHOW CONTROL command.

adrci> show control

ADR Home = D:\oracle\diag\rdbms\orcl\orcl:

*************************************************************************

ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME SIZEP_POLICY PURGE_PERIOD FLAGS PURGE_THRESHOLD

-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- -------------------- -------------------- -------------------- --------------------

1335663986 720 8760 2022-11-01 13:32:30.314000 +04:00 2026-05-21 23:51:14.096000 +04:00 2026-05-22 20:18:33.106000 +04:00 1 2 110 1 2022-11-01 13:32:30.314000 +04:00 18446744073709551615 0 0 95

1 row fetched

The SHORTP_POLICY is of 720 hours (30 days) applies to:

Trace files, including those files stored in the cdmp_timestamp sub-directories
Core dump files
Packaging information

The LONGP_POLICY of 8760 hours (365 days) applies to:

Incident information
Incident dumps
Alert logs

We can alter the default value using set control command

adrci> set control (SHORTP_POLICY =240)

adrci> set control (LONGP_POLICY=4320)

6. Viewing Trace Files contents

adrci> show trace D:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_7860.trc

Output the results to file: C:\Users\SVC_CO~1\AppData\Local\Temp\utsout_11648_5432_7.ado

adrci>

7. Create incident package:

adrci> show incident

adrci>IPS CREATE PACKAGE INCIDENT

Step-by-Step Guide to Convert Physical Standby to Snapshot Standby in Oracle 19c

In many Oracle Data Guard environments, the standby database is usually seen only as a DR database. But I have seen several customers use their Snapshot Standby as a temporary sandbox for testing.

Before moving important changes to production whether it is a patch, program change, or database modification they first validate everything on snapshot standby. Since the standby stays very close to production data, testing becomes much more reliable than using an outdated DEV or TEST environment.

Recently, I had a similar requirement where the application team wanted to validate some changes. Instead of refreshing a separate database clone, we simply converted the physical standby into a Snapshot Standby and used it for testing.

In fact they had a batch script in place which used to run every night to keep the standby database in sync to make it very close to actual production environment.

Environment details: -

Env details	Primary DB	Standby DB
DB unique Name	Orcl	DVDB
DB name	Orcl	Orcl
DB role	Primary	Standby
DB Version	19.29	19.29
OS Version	Windows 2022	Windows 2022

Today I will show how to convert a physical standby database into snapshot standby database.

In primary:-

FRA must be configured

Sufficient FRA space should exist

Standby should be mounted and healthy

SQL> show parameter db_recovery

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string D:\oracle\fast_recovery_area

db_recovery_file_dest_size big integer 300G

Check for archive log gap status in Primary:-

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE

------------ ---------------- ---------------- --------------------

OPEN orcl PRIMARY READ WRITE

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)

--------------

87655

In standby:-

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string D:\oracle\fast_recovery_area

db_recovery_file_dest_size big integer 200G

SQL> select max(sequence#) from v$archived_log where archived='YES';

MAX(SEQUENCE#)

--------------

87655

Now login to DGMGRL utility to perform conversion to snapshot database

In standby:-

C:\Windows\system32>dgmgrl sys@dvdb as sysdba

DGMGRL for 64-bit Windows: Release 19.0.0.0.0 - Production on Fri May 22 13:32:26 2026

Version 19.29.0.0.0

Welcome to DGMGRL, type "help" for information.

Password:

Connected to "DVDB"

Connected as SYSDBA.

DGMGRL> show configuration

Configuration - my_dg_config

Protection Mode: MaxPerformance

Members:

orcl - Primary database

dvdb - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 39 seconds ago)

Lets start conversion (in standby site)

DGMGRL> CONVERT DATABASE dvdb to snapshot standby ;

Converting database "dvdb" to a Snapshot Standby database, please wait...

Database "dvdb" converted successfully

DGMGRL> show configuration

Configuration - my_dg_config

Protection Mode: MaxPerformance

Members:

orcl - Primary database

dvdb - Snapshot standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 50 seconds ago)

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE

------------ ---------------- ---------------- --------------------

OPEN orcl SNAPSHOT STANDBY READ WRITE

SQL>

So this concludes the conversion of physical standby database into standby database.

At this point we can start application and point to the standby database as the database is currently in read write mode.

Note: Any changes made into standby database is not permanent. They get removed whenever the database is converted back to physical standby database.

A Real-World Oracle EBS OCI Migration: Why We Picked WebLogic UCM

Recently, one of my long-time customers, who has been running Oracle E-Business Suite (EBS R12.2 with Oracle 19c) on-premises for many years, started discussing a long overdue infrastructure refresh. Their existing servers had aged considerably, and continuing with the same setup no longer looked practical.

Since I had worked with them for quite some time, they reached out to discuss possible options and get my thoughts on the best way forward.

One thing was very clear from the beginning that is although they wanted to modernize the environment, they were not keen on maintaining annual support costs (ATS) or investing heavily in upfront licensing again.

After understanding their requirements, I suggested a simpler approach: use Oracle DBCS for hosting the Oracle Database and consider an Oracle WebLogic Suite UCM image for the EBS application tier. This allowed them to move to OCI without the burden of managing separate WebLogic licensing upfront.

Here is how we launched the instance from oci console :-

In the Search field, type "Oracle WebLogic Suite UCM Image".

Click "Launch instance", and on the top right of screen, to launch the instance.

Choose the OS version on which the instance would be launched. Click on Launch instance

Select instance name and compartment under which the instance would be launched

If you wish to change the shape of the instance, you can do so by clicking on change shape option. And click on Next.

In the network section, provide vcn and subnet as per your environment. Ensure vcn and subnet is already created before launching the instance. and click on next.

Since our VM is associated with a private subnet and I chose the option “Automatically assign private ipv4 address”

In the Add SSH keys region, select Choose generate a pair for me and download the keys.

In the Boot volume section, you can either accept the default volume size or change it if desired.

And Click Create to create your Compute instance.

Once the instance is launched, log in to the instance as the opc user via SSH and perform the following steps: Remove the oracle user by entering the following commands:

Remove the oracle user by entering the following commands:

$ sudo userdel -rf oracle

$ sudo usermod --shell /bin/bash root

Install the Oracle E-Business Suite RPM using the following command: For Oracle Linux 8 installation, use the following yum command:

$ dnf install oracle-ebs-server-R12-preinstall.x86_64

Create the required soft link for the motif library:

sudo ln -s /usr/lib/libXm.so.4 /usr/lib/libXm.so.2

Run the following commands to perform required cleanup

$ sudo dnf history redo last

$ sudo dnf remove --duplicates

$ sudo dnf update

Use the following commands to remove the content of the

/u01 directory:

[opc@ebs_ucm u01]$

pwd

/u01

[opc@ebs_ucm u01]$

ls -lrt

total 0

drwxrwxr-x. 5 oracle oracle 65 May 22 10:57 zips

drwxrwxr-x. 2 oracle oracle 6 May 22 10:57 logs

$ sudo rm -rf

Now our ucm instance is ready for EBS installation or migration. The path is straightforward and same as we perform for any EBS application migration.

Reference: Doc id : 2766854.1

Oracle 19c FSFO Observe-Only Mode – A Safe Way to Test Automatic Failover

Recently for an enterprise customer, we had a requirement of enabling Fast start fail over. Customer had 1 primary and one standby database configured and they wanted to configure fast start fail over on top of it.

Now before we start the configuration, customer had a concern about “What if Oracle triggers failover when it shouldn’t?”

This is where Observe-Only Mode becomes useful.

Lets understand what is Observe-Only Mode?

Observe-Only Mode lets the observer monitor the Data Guard configuration exactly like normal FSFO, but without actually failing over the database.

In simple words, Oracle checks:

Primary database availability
Standby health
Observer connectivity
FSFO conditions

But even if all failover conditions are met, role reversal does not happen between primary and standby database.

Recommendation:-

In real environments, nobody wants surprises during DR .

You may have questions like:

Is the observer stable?
Will temporary network issues trigger failover?
Is standby lag acceptable?
Are timeout values configured properly?

Observe-Only Mode helps answer these questions before enabling actual automatic failover.

Personally, I feel this is a good way to build confidence in the setup.

How to Enable It

Connect using DGMGRL and run:

DGMGRL> ENABLE FAST_START FAILOVER OBSERVE ONLY;

Enabled in Observe-Only Mode.

Once enabled, verify the configuration:

DGMGRL> SHOW CONFIGURATION;

You should see something like:

Fast-Start Failover: Enabled in Observe-Only Mode

To check complete FSFO details:

DGMGRL> SHOW FAST_START FAILOVER;

This shows observer information, target standby, failover threshold, lag limits, and related settings.

What Actually Happens During Failure?

Suppose your primary database becomes unreachable.

Normally, FSFO may promote the standby automatically.

In Observe-Only Mode, Oracle behaves differently.

The observer still evaluates everything, but no role transition happens. The standby remains standby.

You can later review logs and confirm whether failover would have been triggered.

When Should You Use It?

I would recommend using Observe-Only Mode in these situations:

Before enabling FSFO in production
After network or infrastructure changes
During DR testing
After observer relocation

Basically, anytime you want to test behavior without taking risk.

Moving to Actual FSFO

Once you are satisfied with the behavior, disable observe-only mode and enable real FSFO.

DGMGRL> DISABLE FAST_START FAILOVER;

DGMGRL> ENABLE FAST_START FAILOVER;

Final Thoughts

FSFO is powerful, but enabling it blindly is not always a good idea.

Observe-Only Mode gives you a safe way to understand how Oracle Data Guard will react during failures without impacting production.

Think of it as a practice run before switching to full automation.

May 21, 2026

Fast-Start failover(FSFO) in Oracle 19c

Fast-Start failover(FSFO) in Oracle 19c Dataguard

Failover is the process of transitioning a standby database to the primary role when the original primary database becomes unavailable due to planned or unplanned outages. In Oracle Data Guard, failover can be manual or automatic (Fast-Start Failover). Depending on the protection mode and redo synchronization status, failover may occur with zero data loss or minimal data loss.

The following conditions must be met before you can use the broker:

■ Primary and standby DB’s must be on same version
■ You must use a SPFILE to ensure the broker can persistently reconcile values between broker properties.
■ DG_BROKER_START parameter must be set to TRUE.
■ DG_BROKER_CONFIG_FILE file should be place in the shared area for RAC.
■ Oracle Net Services network files must be set up on the primary database and on the standby database.
■ To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance.
■ Ensure the COMPATIBLE initialization parameter is set to the same value on all systems.
■ Flashback database should be enable for fast start failover.

Introduction of FSFO observer:-
The Dataguard Broker Observer (Observer) is used when fast start failover is required. Fast start failover is simply an automatic failover to the standby database.This is not often desired as most often even the most urgent failover/switchover scenarios require configuration of the Application once the database has been activated at the DR site.
The observer will initiate the failover under the following conditions automatically:
By default, the observer will initiate failover to the target standby if and only if ALL of the following are true:
• observer is running
• observer and the standby both lose contact with the primary

Note: if the observer loses contact with the primary, but the standby does not, the observer can determine that the primary is still up via the standby.
• observer is still in contact with the standby
• durability constraints are met
• failover threshold timeout has elapsed

Health conditions
Broker can be configured to initiate failover on any of the following conditions. Conditions shown in blue are enabled by default.
• Datafile Offline (due to IO errors)
• Corrupted Controlfile
• Corrupted Dictionary
• Inaccessible Logfile (due to IO errors)
• Stuck Archiver

Primary database Server : primary.example.com
Standby database Server : standby.example.com
Observer database server: observer.example.com

Primary database: prim
Standby database: stand
Observer database: observer

Here I have already done a physical standby server setup and its working properly.So I will only show how to configure DGMRL and use Fast-Start Failover using it.

Here is host file entry for for all server , make sure its present in each of the server.

# cat /etc/hosts
[oracle@standby ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.16.10.1 primary.example.com primary
172.16.10.2 standby.example.com standby
172.16.10.3 observer.example.com observer

Step 1:- Check parameter DG_BROKER_START on primary and standby:
SQL> show parameter DG_BROKER_START

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE

Step 2:- Set DG_BROKER_START parameter to true on primary:

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

SQL> show parameter DG_BROKER_START

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE

Step 3:- Define a service for the listener that includes the db_unique_name_DGMGRL.db_domain values for the GLOBAL_DBNAME
Update the listener.ora file on primary and standby database

In primary database :-

[oracle@primary ~]$ cat /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = prim.example.com)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
(SID_NAME = prim)
)
)

(SID_DESC =
(GLOBAL_DBNAME = prim_DGMGRL.example.com)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
(SID_NAME = prim)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.example.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
INBOUND_CONNECT_TIMEOUT_LISTENER=120

-- save and quit (:wq)

In standby database:-

[oracle@standby ~]$ cat /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stand.example.com)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
(SID_NAME = stand)
)
)

(SID_DESC =
(GLOBAL_DBNAME = stand_DGMGRL.example.com)
(ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
(SID_NAME = stand)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.example.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
INBOUND_CONNECT_TIMEOUT_LISTENER=120

-- save and quit (:wq)

Tnsnames.ora entry for all server:-

[oracle@ ~]$cat /u01/app/oracle/product/19.3.0/db_1/network/admin/tnsnames.ora
prim =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIM)
)
)

stand =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = STAND)
)
)
observer
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = observer.example.com )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = observer)
)
)

Once changes are done , restart the listener.

Step 4: Now lets create the data guard broker configurations:-

[oracle@primary ~]$ dgmgrl

DGMGRL> connect sys/redhat@prim
Connected.
DGMGRL> create configuration 'DBTEST'
> as primary database is 'prim'
> connect identifier is prim;
Configuration "DBTEST" created with primary database "prim"

DGMGRL> show configuration

Configuration - DBTEST

Protection Mode: MaxPerformance
Databases:
prim - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Step 7 :- Now add standby database in data guard broker configuration:-

DGMGRL> add database 'stand' as
> connect identifier is stand
> maintained as physical;
Database "stand" added
DGMGRL>
DGMGRL> show configuration

Configuration - DBTEST

Protection Mode: MaxPerformance
Databases:
prim - Primary database
stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Step 8:-Now enable the configurations

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - DBTEST

Protection Mode: MaxPerformance
Databases:
prim - Primary database
stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database verbose stand

Database - stand

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
stand

Properties:
DGConnectIdentifier = 'stand'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'prim, stand'
LogFileNameConvert = 'prim, stand'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'standby.example.com'
SidName = 'stand'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stand_DGMGRL)(INSTANCE_NAME=stand)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show database verbose prim;

Database - prim

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prim

Properties:
DGConnectIdentifier = 'prim'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'stand, prim'
LogFileNameConvert = 'stand, prim'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'primary.example.com'
SidName = 'prim'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prim_DGMGRL)(INSTANCE_NAME=prim)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - DBTEST

Protection Mode: MaxPerformance
Databases:
prim - Primary database
stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> enable database stand;
Enabled.
DGMGRL> SHOW DATABASE VERBOSE stand;

Database - stand

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
stand

Properties:
DGConnectIdentifier = 'stand'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'auto'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'prim, stand'
LogFileNameConvert = 'prim, stand'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'standby.example.com'
SidName = 'stand'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=stand_DGMGRL)(INSTANCE_NAME=stand)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'

Database Status:
SUCCESS

Step 8 :-Ensure standby redologs are configured on primary & standby databases.

on primary Database:
SQL> set lines 200
SQL> col member for a50
SQL>
SQL> SELECT TYPE,MEMBER FROM V$LOGFILE;

TYPE MEMBER
------- --------------------------------------------------
ONLINE /u01/app/oracle/oradata/prim/redo03.log
ONLINE /u01/app/oracle/oradata/prim/redo02.log
ONLINE /u01/app/oracle/oradata/prim/redo01.log
STANDBY /u01/app/oracle/oradata/prim/redos4A.log
STANDBY /u01/app/oracle/oradata/prim/redos5A.log
STANDBY /u01/app/oracle/oradata/prim/redos6A.log
STANDBY /u01/app/oracle/oradata/prim/redos7A.log

7 rows selected.

On Standby Database:-
SQL> set lines 200
SQL> col member for a50
SQL> SELECT TYPE,MEMBER FROM V$LOGFILE;

TYPE MEMBER
------- --------------------------------------------------
ONLINE /u01/app/oracle/oradata/stand/redo03.log
ONLINE /u01/app/oracle/oradata/stand/redo02.log
ONLINE /u01/app/oracle/oradata/stand/redo01.log
STANDBY /u01/app/oracle/oradata/stand/redos4A.log
STANDBY /u01/app/oracle/oradata/stand/redos5A.log
STANDBY /u01/app/oracle/oradata/stand/redos6A.log
STANDBY /u01/app/oracle/oradata/stand/redos7A.log

7 rows selected.

Step 9:- Configure the LogXptMode Property is set to SYNC

DGMGRL> EDIT DATABASE prim SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

DGMGRL> EDIT DATABASE stand SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

Step 10:-Configure the FastStartFailoverTarget property

DGMGRL> EDIT DATABASE prim SET PROPERTY
> FastStartFailoverTarget='stand';
Property "faststartfailovertarget" updated

DGMGRL> EDIT DATABASE stand SET PROPERTY
> FastStartFailoverTarget='prim';
Property "faststartfailovertarget" updated

Step 11:- Upgrade the protection mode to MAXAVAILABILITY, if required.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

Step 12:-To enable the Flashback Mode run below queries on primary & standby database.

SQL> alter system set db_recovery_file_dest_size=1G;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flash_recovery_area';

System altered.

SQL> ALTER SYSTEM SET UNDO_RETENTION=3600 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET UNDO_MANAGEMENT='AUTO' SCOPE=SPFILE;

System altered.

Step 13:- Now restart the database one by one. and start the database in mount stage. But NOT TOGETHER. First on primary.

on Primary:-
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 314574840 bytes
Database Buffers 92274688 bytes
Redo Buffers 4308992 bytes
Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> alter database open;

Database altered.
SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
YES

On standby:-

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 314574840 bytes
Database Buffers 92274688 bytes
Redo Buffers 4308992 bytes
Database mounted.

SQL> alter database recover managed standby database cancel;

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL> alter database open;

SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON
------------------
YES

Step 14:-Enable fast start failover
[oracle@primary ~]$ dgmgrl
DGMGRL> connect sys/redhat@prim
Connected.
DGMGRL> show configuration verbose;

Configuration - DBTEST

Protection Mode: MaxAvailability
Databases:
prim - Primary database
stand - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database stand;

Database - stand

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
stand

Database Status:
SUCCESS

DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.

Step 15:- Start the observer
At Oberserver server(172.16.10.3):-
[oracle@observer ~]$ mkdir -p /u01/FSFO
[oracle@observer ~]$ cd /u01/FSFO
[oracle@observer FSFO] ~]$ vi startobserver.sh
#!/bin/bash
# startobserver
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db_1
export BASE_PATH=/u01/FSFO:/opt/CTEact/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/bin:/etc:/usr/local/maint/oracle:/usr/ccs/bin:/usr/openwin/bin:/usr/dt/bin:/usr/local/bin:.
export PATH=$ORACLE_HOME/bin:$BASE_PATH
dgmgrl << eof
connect sys/redhat@prim
START OBSERVER;
eof

-- save & exit (:wq)

[oracle@observer FSFO] ~]$chmod 775 startobserver.sh

Now start the observer:-
[oracle@observer FSFO] ~]$sh startobserver.sh
DGMGRL for Linux: Version 19.3.0.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected.
DGMGRL> Observer started

Step 16:-Verify the fast-start failover configuration
DGMGRL> SHOW CONFIGURATION VERBOSE;

Configuration - DBTEST

Protection Mode: MaxAvailability
Databases:
prim - Primary database
stand - (*) Physical standby database

(*) Fast-Start Failover target

Fast-Start Failover: ENABLED

Threshold: 30 seconds
Target: stand
Observer: observer.example.com
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE

Configuration Status:
SUCCESS

DGMGRL> show database prim

Database - prim

Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
prim

Database Status:
SUCCESS

DGMGRL> show database stand

Database - stand

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
stand

Database Status:
SUCCESS

Step 17:- EXECUTE THE SWITCHOVER

DGMGRL> switchover to stand;
Performing switchover NOW, please wait...
New primary database "stand" is opening...
Operation requires shutdown of instance "prim" on database "prim"
Shutting down instance "prim"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "prim" on database "prim"
Starting instance "prim"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
start up instance "prim" of database "prim"

Start the database at primary server(172.16.10.1)
SQL> startup
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 360712184 bytes
Database Buffers 46137344 bytes
Redo Buffers 4308992 bytes
Database mounted.
Database opened.

[oracle@primary ~]$ dgmgrl
DGMGRL> connect sys/redhat@prim
Connected.
DGMGRL> show configuration verbose

Configuration - DBTEST

Protection Mode: MaxAvailability
Databases:
stand - Primary database
prim - (*) Physical standby database

(*) Fast-Start Failover target

Fast-Start Failover: ENABLED

Threshold: 30 seconds
Target: prim
Observer: observer.example.com
Lag Limit: 30 seconds (not in use)
Shutdown Primary: TRUE
Auto-reinstate: TRUE

Configuration Status:
SUCCESS

DGMGRL> show database prim

Database - prim

Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
prim

Database Status:
SUCCESS

Step 18:-Failover to standby(old primary)
Note:- We will need to run this from the new standby database
[oracle@primary ~]$ dgmgrl sys/redhat@prim

DGMGRL for Linux: Version 19.3.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> failover to prim;
Performing failover NOW, please wait...
Failover succeeded, new primary is "prim"
DGMGRL> exit

Now once failover is completed, we will find that our old primary(prim) is back again in as primary database.
[oracle@primary ~]$ sqlplus / as sysdba

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
prim

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

But we will need to start the standby database as it has been shutdown by dataguard broker process.

At standby server(172.16.10.2):-

SQL> startup mount
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 352323576 bytes
Database Buffers 54525952 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Done...