ODG Convert to Standalone

Note that you may need to anonimise the data after creation of the new standalone. It is not good practice to use real customer data in a non-production environment.

Assumptions

The Primary has RMAN backups configured. (Add them before continuing if they are missing).
The Standby has RMAN backups configured. (Just skip the "Delete Backup" steps and add backups at the end).
You are not using an RMAN catalog (this is TODO).
You are not using ASM (this is TODO).
The database is on a UNIX (e.g. Linux, AIX etc) Operating System. (Windows needs additional steps and file locations are different).
Any automation has already been disabled (e.g. cron jobs to open as Snapshot Standby etc).
You are using the Data Guard Broker. Although some alternate steps have been included, there are other tasks that the Broker performs automatically that are not documented here.

Delete Backups

This is an optional step. It is much easier to delete backups at this stage but harder to rollback. I have also included a later step to delete backups after conversion to standalone is complete.

rman target /

DELETE BACKUP;

Remove Data Guard Configuration

dgmgrl /

EDIT CONFIGURATION SET PROTECION MODE AS MAXPERFORMANCE;

REMOVE CONFIGURATION;

Alternate step if you are not using the Data Guard Borker...sqlplus / as sysdbaALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Reset Database Parameters

On BOTH databases (Primary and Standby)..

SET LINESIZE 200

SET PAGESIZE 40

COLUMN name FORMAT a30

COLUMN value FORMAT a150

SELECT name,

value

FROM v$parameter

WHERE name IN ('log_archive_config',

'db_file_name_convert',

'log_file_name_convert',

'dg_broker_start',

'dg_broker_config_file1',

'dg_broker_config_file2',

'standby_archive_dest',

'standby_file_management',

'fal_server',

'fal_client')

OR name LIKE 'log_archive_dest%';

Copy the output in case you decide to backout the change.

You should also delete the files identified by dg_broker_config_file1 and dg_broker_config_file2 from the OS command line e.g.

rm /full/path/dr1MYSID.dat

rm /full/path/dr2MYSID.dat

On BOTH databases (Primary and Standby)..

ALTER SYSTEM RESET db_file_name_convert SCOPE=SPFILE;

ALTER SYSTEM RESET log_file_name_convert SCOPE=SPFILE;

ALTER SYSTEM SET dg_broker_start=FALSE SCOPE=BOTH;

ALTER SYSTEM RESET dg_broker_config_file1 SCOPE=SPFILE;

ALTER SYSTEM RESET dg_broker_config_file2 SCOPE=SPFILE;

ALTER SYSTEM RESET standby_archive_dest SCOPE=SPFILE;

ALTER SYSTEM RESET standby_file_management SCOPE=SPFILE;

ALTER SYSTEM RESET fal_server SCOPE=SPFILE;

ALTER SYSTEM RESET fal_client SCOPE=SPFILE;

Note that some parameters cannot be changed in memory and will only take effect when the database is restarted. However, there is no urgency to perform this restart.
log_archive_config is automatically set to NODG_CONFIG by the Brokerlog_archive_dest_n is automatically cleared by the Broker.
Note that standby_archive_dest is deprecated in 12.x and is not available in later versions.

Activate Standalone Database

On the old Standby (new Standalone)...

sqlplus / as sysdba

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

This may also work, but I have not tested...ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;SHUTDOWN IMMEDIATE;STARTUP

Remove Standby Redo Logs

On BOTH databases (Primary and Standby)..

SELECT GROUP# FROM V$STANDBY_LOG;

Drop all Standby Redo Logs returned from query above...

ALTER DATABASE DROP STANDBY LOGFILE GROUP &group;

ODG Standby Redo Logs

Verify Online Redo Logs Configuration

Online redo logs need to exist on the Standby...

@/u01/dba/oraredologs.sql

If you have ever performed a switchover then it is likely this has already been resolved.

Oracle REDO

Verify Temp Files

Verify tempfiles exist on the Standby. Add new if necessary.

@/u01/dba/oratemptscheck.sql

Tempfiles added after initial standby creation are not propagated to the standby.

Oracle TEMP Lifecycle

DROP dbconsole

TODO

Change DBID

sqlplus / as sysdba

SHUTDOWN IMMEDIATE

STARTUP MOUNT

exit

nid TARGET=SYS/password DBNAME=MYNEWDBNAME

DBNEWID: Release 12.1.0.2.0 - Production on Tue Jun 3 16:18:19 2025
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to database MYDBNAME (DBID=1187776927)
Connected to server version 12.1.0
Control Files in database: /u01/oradata/MYSID/control01.ctl /u02/oradata/MYSID/control02.ctl
Change database ID and database name MYDBNAME to MYNEWDBNAME? (Y/[N]) => Y
Proceeding with operationChanging database ID from 1187776927 to 1199949196Changing database name from MYDBNAME to MYNEWDBNAME Control File /u01/oradata/MYSID/control01.ctl - modified Control File /u02/oradata/MYSID/control02.ctl - modified Datafile /u02/oradata/MYSID/data_D-MYDBNAME_I-1188986920_TS-SYSTEM_FNO-1_ufo9ac8 - dbid changed, wrote new name Datafile /u02/oradata/MYSID/data_D-MYDBNAME_I-1188986920_TS-SYSAUX_FNO-2_uco9ac6 - dbid changed, wrote new name Datafile /u02/oradata/MYSID/data_D-MYDBNAME_I-1188986920_TS-UNDOTBS1_FNO-3_udo9ac6 - dbid changed, wrote new name Datafile /u02/oradata/MYSID/data_D-MYDBNAME_I-1188986920_TS-USERS_FNO-4_ugo9ac9 - dbid changed, wrote new name Datafile /u02/oradata/MYSID/o1_mf_temp_knv03h3m_.tm - dbid changed, wrote new name Control File /u01/oradata/MYSID/control01.ctl - dbid changed, wrote new name Control File /u02/oradata/MYSID/control02.ctl - dbid changed, wrote new name Instance shut down
Database name changed to MYNEWDBNAME.Modify parameter file and generate a new password file before restarting.Database ID for database MYNEWDBNAME changed to 1199949196.All previous backups and archived redo logs for this database are unusable.Database is not aware of previous backups and archived logs in Recovery Area.Database has been shutdown, open database with RESETLOGS option.Succesfully changed database name and ID.DBNEWID - Completed succesfully.In this example the OMF generated datafilenames containing the old DBNAME/ORACLE_SID remained the same.Although MYSID and MYNEWDBNAME are the same, I identified them with different names above to more clearly show which names change and which remain the same.

sqlplus / as sysdba

CREATE PFILE FROM SPFILE;

Update DBNAME in $ORACLE_HOME/dbs/initMYSID.ora

sqlplus / as sysdba

CREATE PFILE FROM SPFILE;

STARTUP MOUNT

ALTER DATABASE OPEN RESETLOGS;

exit

Recreate Password File

orapwd file=$ORACLE_HOME/dbs/orapwMYNEWDBNAME force=y

SQL*Net Configuration

Check TNSNAMES.ORA and LISTENER.ORA. Update as necessary.

e.g. In my testing I removed the Data Guard Broker rleated stanza from listener.ora

lsnrctl stop listener

lsnrctl start listener

You only need to restart if you made any changes to listener.ora

LISTENER.ORA

TNSNAMES.ORA

Delete Backups

If you were backing up the Physical Standby on this server before converting to standalone, you are likely to have some backups in the FRA (unless you deleted them in the earlier optional step) that you now can't delete from RMAN (due to the change in incarnation caused by the open resetlogs). Assuming RMAN was configured to autobackup the controlfile, you can do this...

export ORACLE_SID=DUMMY

vi $ORACLE_HOME/dbs/initDUMMY.ora

db_name=MYDBNAME

db_unique_name=DUMMY

control_files='/u02/oradata/DUMMY/controlfile/ctlDUMMY1.ctl'

*.compatible='11.2.0.3.0'

Note that the db_name needs to be the db_name of the database before conversion (use show parameter db_name on the old Primary if you don't remember it).
control_files can be any path (except the path of the controlfiles for the standalone database i.e. we don't want to overwrite anything belonging to our new standalone database instance).

sqlplus / as sysdba

startup nomount

Identify the full path of the most recent controlfile autobackup of the previous incarnation of the database. If you pick an autobackup from the new incarnation you won't be able to see the old backups. If you pick an older controlfile autobackup then some autobackups may not be visible.

rman target /

restore controlfile from '/fra/MYDBNAME/autobackup/2025_06_02/o1_mf_s_1202213625_n3thy526_.bkp';

Starting restore at 2025-JUN-09 15:24:01using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=3 device type=DISK
channel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:01output file name=/u02/oradata/DUMMY/controlfile/ctlDUMMY1.ctlFinished restore at 2025-JUN-09 15:24:03

alter database mount;

list backup summary

RMAN> list backup summary;

List of Backups===============Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag------- -- -- - ----------- -------------------- ------- ------- ---------- ---23652 B A A DISK 2025-MAY-28 21:01:57 1 1 YES TAG20250528T21000623653 B 0 A DISK 2025-MAY-28 23:42:47 1 1 YES TAG20250528T21020223654 B A A DISK 2025-MAY-28 23:42:52 1 1 YES TAG20250528T23424923657 B A A DISK 2025-MAY-29 21:00:32 1 1 YES TAG20250529T21000523658 B A A DISK 2025-MAY-29 21:01:01 1 1 YES TAG20250529T21000523659 B A A DISK 2025-MAY-29 21:01:23 1 1 YES TAG20250529T21000523660 B 1 A DISK 2025-MAY-29 21:06:33 1 1 YES TAG20250529T21013023661 B A A DISK 2025-MAY-29 21:06:41 1 1 YES TAG20250529T21063623664 B A A DISK 2025-MAY-30 21:00:41 1 1 YES TAG20250530T21000623665 B A A DISK 2025-MAY-30 21:01:08 1 1 YES TAG20250530T21000623666 B A A DISK 2025-MAY-30 21:01:37 1 1 YES TAG20250530T21000623667 B 1 A DISK 2025-MAY-30 21:09:03 1 1 YES TAG20250530T21014223668 B A A DISK 2025-MAY-30 21:09:11 1 1 YES TAG20250530T21090823670 B A A DISK 2025-MAY-31 21:00:33 1 1 YES TAG20250531T21000523671 B 1 A DISK 2025-MAY-31 21:08:28 1 1 YES TAG20250531T21004123672 B A A DISK 2025-MAY-31 21:09:02 1 1 YES TAG20250531T21083723673 B F A DISK 2025-MAY-31 21:09:12 1 1 NO TAG20250531T21091223674 B A A DISK 2025-JUN-01 21:00:32 1 1 YES TAG20250601T21000623675 B 1 A DISK 2025-JUN-01 21:07:33 1 1 YES TAG20250601T21004123676 B A A DISK 2025-JUN-01 21:07:52 1 1 YES TAG20250601T21073723677 B F A DISK 2025-JUN-01 21:07:53 1 1 NO TAG20250601T210753

DELETE BACKUP;

allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=3 device type=DISK
List of Backup PiecesBP Key BS Key Pc# Cp# Status Device Type Piece Name------- ------- --- --- ----------- ----------- ----------23654 23652 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_28/o1_mf_annnn_TAG20250528T210006_n3gtwq8p_.bkp23655 23653 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_28/o1_mf_nnnd0_TAG20250528T210202_n3gtxv2h_.bkp23656 23654 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_28/o1_mf_annnn_TAG20250528T234249_n3h4c9xh_.bkp23659 23657 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_29/o1_mf_annnn_TAG20250529T210005_n3kh65cz_.bkp23660 23658 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_29/o1_mf_annnn_TAG20250529T210005_n3kh78bw_.bkp23661 23659 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_29/o1_mf_annnn_TAG20250529T210005_n3kh81gl_.bkp23662 23660 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_29/o1_mf_nnnd1_TAG20250529T210130_n3kh8v9p_.bkp23663 23661 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_29/o1_mf_annnn_TAG20250529T210636_n3khlf4d_.bkp23666 23664 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_30/o1_mf_annnn_TAG20250530T210006_n3n3l6x1_.bkp23667 23665 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_30/o1_mf_annnn_TAG20250530T210006_n3n3mb06_.bkp23668 23666 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_30/o1_mf_annnn_TAG20250530T210006_n3n3nf3z_.bkp23669 23667 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_30/o1_mf_nnnd1_TAG20250530T210142_n3n3o727_.bkp23670 23668 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_30/o1_mf_annnn_TAG20250530T210908_n3n434wb_.bkp23672 23670 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_31/o1_mf_annnn_TAG20250531T210005_n3pqy682_.bkp23673 23671 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_31/o1_mf_nnnd1_TAG20250531T210041_n3pqz9kl_.bkp23674 23672 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_05_31/o1_mf_annnn_TAG20250531T210837_n3prg5d1_.bkp23675 23673 1 1 AVAILABLE DISK /fra/MYDBNAME/autobackup/2025_05_31/o1_mf_s_1202546668_n3prh8tj_.bkp23676 23674 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_06_01/o1_mf_annnn_TAG20250601T210006_n3sdb6kz_.bkp23677 23675 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_06_01/o1_mf_nnnd1_TAG20250601T210041_n3sdcb09_.bkp23678 23676 1 1 AVAILABLE DISK /fra/MYDBNAME/backupset/2025_06_01/o1_mf_annnn_TAG20250601T210737_n3sdr9wb_.bkp23679 23677 1 1 AVAILABLE DISK /fra/MYDBNAME/autobackup/2025_06_01/o1_mf_s_1202642253_n3sdrsb6_.bkp
Do you really want to delete the above objects (enter YES or NO)? YESdeleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_28/o1_mf_annnn_TAG20250528T210006_n3gtwq8p_.bkp RECID=23654 STAMP=1202331687deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_28/o1_mf_nnnd0_TAG20250528T210202_n3gtxv2h_.bkp RECID=23655 STAMP=1202331723deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_28/o1_mf_annnn_TAG20250528T234249_n3h4c9xh_.bkp RECID=23656 STAMP=1202341369deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_29/o1_mf_annnn_TAG20250529T210005_n3kh65cz_.bkp RECID=23659 STAMP=1202418005deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_29/o1_mf_annnn_TAG20250529T210005_n3kh78bw_.bkp RECID=23660 STAMP=1202418040deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_29/o1_mf_annnn_TAG20250529T210005_n3kh81gl_.bkp RECID=23661 STAMP=1202418065deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_29/o1_mf_nnnd1_TAG20250529T210130_n3kh8v9p_.bkp RECID=23662 STAMP=1202418091deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_29/o1_mf_annnn_TAG20250529T210636_n3khlf4d_.bkp RECID=23663 STAMP=1202418397deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_30/o1_mf_annnn_TAG20250530T210006_n3n3l6x1_.bkp RECID=23666 STAMP=1202504406deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_30/o1_mf_annnn_TAG20250530T210006_n3n3mb06_.bkp RECID=23667 STAMP=1202504442deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_30/o1_mf_annnn_TAG20250530T210006_n3n3nf3z_.bkp RECID=23668 STAMP=1202504477deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_30/o1_mf_nnnd1_TAG20250530T210142_n3n3o727_.bkp RECID=23669 STAMP=1202504503deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_30/o1_mf_annnn_TAG20250530T210908_n3n434wb_.bkp RECID=23670 STAMP=1202504948deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_31/o1_mf_annnn_TAG20250531T210005_n3pqy682_.bkp RECID=23672 STAMP=1202590806deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_31/o1_mf_nnnd1_TAG20250531T210041_n3pqz9kl_.bkp RECID=23673 STAMP=1202590841deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_05_31/o1_mf_annnn_TAG20250531T210837_n3prg5d1_.bkp RECID=23674 STAMP=1202591317deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_06_01/o1_mf_annnn_TAG20250601T210006_n3sdb6kz_.bkp RECID=23676 STAMP=1202677206deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_06_01/o1_mf_nnnd1_TAG20250601T210041_n3sdcb09_.bkp RECID=23677 STAMP=1202677242deleted backup piecebackup piece handle=/fra/MYDBNAME/backupset/2025_06_01/o1_mf_annnn_TAG20250601T210737_n3sdr9wb_.bkp RECID=23678 STAMP=1202677657deleted backup piecebackup piece handle=/fra/MYDBNAME/autobackup/2025_06_01/o1_mf_s_1202642253_n3sdrsb6_.bkp RECID=23679 STAMP=1202677673Deleted 20 objects
RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) dueRMAN-06208: to mismatched status. Use CROSSCHECK command to fix statusRMAN-06210: List of Mismatched objectsRMAN-06211: ==========================RMAN-06212: Object Type Filename/HandleRMAN-06213: --------------- ---------------------------------------------------RMAN-06214: Backup Piece /fra/MYDBNAME/autobackup/2025_05_31/o1_mf_s_1202546668_n3prh8tj_.bkp

Take a Backup

This is critical as opening the database with RESETLOGS will hamper recovery from existing backups.

RMAN Backup

Bibliography

https://support.oracle.com/How To Remove Standby Database And Convert It to Standalone Database (Doc ID 2074686.1)How to remove a Data Guard Configuration from Primary Database (Doc ID 733794.1)How to Change the DBID, DBNAME Using NID Utility (Doc ID 863800.1)How to Rename Database/Change DB_NAME or ORACLE_SID/Instance Name (Doc ID 15390.1)How To Drop, Create And Recreate Database Control (dbconsole) Web Site in Releases 10g and 11g (Doc ID 278100.1)How to rename/move a datafile in the same ASM diskgroup (Doc ID 564993.1)How To Restore Controlfile From A Backupset Without A Catalog Or Autobackup (Doc ID 403883.1)How to delete backups of an inaccessible database. (Doc ID 1394002.1)Using RMAN to Restore and Recover a Database When the Repository and Spfile/Init.ora Files Are Also Lost (Doc ID 372996.1)RMAN - How to restore the controlfile using RMAN (Doc ID 580414.1)
https://stackoverflow.com/questions/24512154/convert-physical-standby-to-standalone-databasehttps://orahow.com/remove-parameter-from-spfile-in-oracle/https://serhatcelik.wordpress.com/2021/12/28/how-to-remove-a-parameter-from-spfile-in-oracle-database/https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-SYSTEM.htm