Tuesday, July 7, 2015

Remove Data Guard Configuration Oracle 11g

Today I will show you how to remove DG configuration

DGMGRL> show configuration;  
 Configuration  
  Name:        DGMANAGER  
  Enabled:       YES  
  Protection Mode:   MaxAvailability  
  Databases:  
   PROD - Primary database  
   STAND - Physical standby database  
 Fast-Start Failover: DISABLED  
 Current status for "DGMANAGER":  
 SUCCESS  

 DGMGRL> remove configuration;  
 Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode  
 Failed.  

I tried to remove database from configuration

DGMGRL> remove database "STAND";  
 Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode  
 Failed.  

But result was same. Unsuccess.

I changed protection mode to MaxPerformance and then retied again

DGMGRL> edit configuration set protection mode as MaxPerformance;  
 Succeeded.  

DGMGRL> show configuration;  
 Configuration  
  Name:        DGMANAGER  
  Enabled:       YES  
  Protection Mode:   MaxPerformance  
  Databases:  
   PROD - Primary database  
   STAND - Physical standby database  
 Fast-Start Failover: DISABLED  
 Current status for "DGMANAGER":  
 SUCCESS  

DGMGRL> remove database "STAND"  
 Removed database "STAND" from the configuration  
 DGMGRL> remove configuration;  
 Removed configuration  
 DGMGRL> show configuration  
 Error: ORA-16532: Data Guard broker configuration does not exist  
 Configuration details cannot be determined by DGMGRL  
 DGMGRL>  

Dataguard configuration successfully removed. Now we have to disable some parameters.

SQL> set linesize 200  
 SQL> show parameter dg_broker  
 NAME                 TYPE               VALUE  
 ------------------------------------ --------------------------------- ------------------------------  
 dg_broker_config_file1        string              /u01/app/oracle/product/11.1.0  
                                     /db_1/dbs/dr1PROD.dat  
 dg_broker_config_file2        string              /u01/app/oracle/product/11.1.0  
                                     /db_1/dbs/dr2PROD.dat  
 dg_broker_start           boolean              TRUE  
 SQL>  


Change dg_broker_start`s value to FALSE on both side

alter system set dg_broker_start=FALSE;

At the end
remove dr*.dat files on both side

 -bash-3.00$ pwd  
 /u01/app/oracle/product/11.1.0/db_1/dbs  
 -bash-3.00$ ls -lrt dr*.dat  
 -rw-r-----  1 oracle  oinstall    1024 Jul 07 12:25 dr2PROD.dat  
 -rw-r-----  1 oracle  oinstall    1024 Jul 07 12:25 dr1PROD.dat  
 -bash-3.00$ rm dr*.dat  


 -bash-4.2$ pwd  
 /u01/app/oracle/product/11.0.1/db_1/dbs  
 -bash-4.2$ ls -lrt dr*.dat  
 -rw-r-----  1 oracle  oinstall    1024 Jul 07 12:24 dr2STAND.dat  
 -rw-r-----  1 oracle  oinstall    1024 Jul 07 12:24 dr1STAND.dat  
 -bash-4.2$ rm dr*.dat 

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...