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;
2. Remove below parameters from spfile of primary database:
FAL_SERVER
FAL_CLIENT
LOG_ARCHIVE_CONFIG
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n – pointing to standby database like _2 or _3
LOG_ARCHIVE_DEST_STATE_n
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT
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)