Remove Dataguard Configuration From Primary Database

 💡 If you wish to remove the dataguard/standby setup from primary database i.e to make the primary database as standalone database without any standby database, then follow below steps.

1.Put primary database on maximum performance mode:

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
  1. Remove below parameters from spfile of primary database:
alter system reset LOG_ARCHIVE_CONFIG scope=spfile;
alter system reset DB_FILE_NAME_CONVERT scope=spfile;
alter system reset LOG_FILE_NAME_CONVERT scope=spfile;
alter system reset STANDBY_FILE_MANAGEMENT scope=spfile;
alter system reset FAL_SERVER scope=spfile;
alter system reset FAL_CLIENT scope=spfile;
alter system reset LOG_ARCHIVE_DEST_STATE_2 scope=spfile;
alter system reset LOG_ARCHIVE_DEST_2 scope=spfile; --- destination pointing to standby
alter system reset STANDBY_ARCHIVE_DEST scope=spfile;

3. Drop all standby log files from primary:

SELECT GROUP# FROM V$STANDBY_LOG;

alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
alter database drop standby logfile group 8;
alter database drop standby logfile group 9;

4. Restart the database to reflect the parameters

shutdown immediate;
startup

Now the standby database is completely separated from primary.

If you want to use the standby database as a standalone database, then follow below steps on standby

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
SQL>STARTUP

You can change dbid using NID utility( as dbid would be same as primary)

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle