Enable Data Guard Broker in an Existing DG Envrionrment

There are several advantages to using the data guard broker to configure and manage your data guard environment.  Don't get me wrong, I believe in command line and the power of understanding what tools, and GUIs provide.  The broker provides a single integrated view of a data guard configuration.  Configuration changes, monitoring and switchover/failover are all available through the broker providing efficient management of a the environment.  When you are working with several environments that include logical standbys an efficient method of managing all aspects is not only a time saving but a necessities.  Performing these tasks through command line is more of luxury. 

So I already have a standby database (or multiple standbys in some cases) how do I set up the broker to make my life easier?

The broker requires a special static listener in order for the failover/switchover to work.  The broker will use this listener to connect as SYSDBA remotely and perform the startup.  The key here is the global name will be the db_unique_name appended with DGMGRL and the domain the database resides.  This must be done for both the primary and the standby.   No TNS entry is required because the broker knows this is the listener to utilize.

On the primary server:

SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
         (GLOBAL_DBNAME = PRIMARY_DGMGRL)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
         (SID_NAME = PRIMARY)
    )
)

On the standby server:

SID_LIST_LISTENER =
   (SID_LIST =
      (SID_DESC =
         (GLOBAL_DBNAME = STANDBY_DGMGRL)
         (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3)
         (SID_NAME = PRIMARY)
    )
)

Enable data guard broker by setting the DG_BROKER_START parameter to TRUE:

ALTER SYSTEM SET DG_BROKER_START=TRUE;

There are two configuration files for the broker the default values:

$ORACLE_HOME/dbs/dr1.dat
$ORACLE_HOME/dbs/dr2.dat

For RAC it is suggested that these are changed to a shared file system.

Start the data guard broker command line utility and connect as SYS.  It is assumed by the broker that sysdba is the privilege and therefore you don't need to specify it.

dgmgrl
DGMGRL> CONNECT SYS
Password:

Next we create the configuration by specifying the primary database name, and the configuration name.

DGMGRL> CREATE CONFIGURATION DG1' AS
PRIMARY DATABASE IS 'PRIMARY'
CONNECT IDENTIFIER IS 'PRIMARY' ;

Configuration "DG1" created with primary database "PRIMARY"

Add the standby databases to the configuration.

DGMGRL> ADD DATABASE 'STANDBY' AS
CONNECT IDENTIFIER IS   'STANDBY';

Database 'STANDBY' added

The data guard configuration must then be enabled.

DGMGRL> ENABLE CONFIGURATION;
Enabled


 







 

Data Pump Export Schema within Pl/SQL

I have one client where I don't have access to the database server, but still need to perform exports to move metadata.  This can easily be accomplished using the Data Pump APIs.  Here's an example of the script that I use, its the same script from The Data Pump API Utilities manual from Oracle.

DECLARE
  ind NUMBER;              
  h1 NUMBER;               
  percent_done NUMBER;     
  job_state VARCHAR2(30);  
  le ku$_LogEntry;         
  js ku$_JobStatus;        
  jd ku$_JobDesc;          
  sts ku$_Status;          
BEGIN

 h1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'MYJOB','LATEST');

 DBMS_DATAPUMP.ADD_FILE(h1,'myexport.dmp','EXPORT_DIR');

 DBMS_DATAPUMP.ADD_FILE(handle=>h1,filename=>'myexport.log',directory=>'EXPORT_DIR',

    filetype=>DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); 

 DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR','IN (''MY'')');

  DBMS_DATAPUMP.START_JOB(h1);

  percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
    js := sts.job_status;

    if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;

   if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;

  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
END;
/

For a table level export simply replace the lines as follows:

h1 := DBMS_DATAPUMP.OPEN('EXPORT','SCHEMA',NULL,'MYJOB','LATEST');

   h1 := DBMS_DATAPUMP.OPEN('EXPORT','TABLE',NULL,'MYJOB',LATEST'); 

 

DBMS_DATAPUMP.METADATA_FILTER(h1,'SCHEMA_EXPR','IN (''MY'')');

    DBMS_DATAPUMP.METADATA_FILTER(h1,'NAME_EXPR',IN (''TABLE1'',''TABLE2''...)');