Blog

How To Relocate Data Guard Broker Configuration File Onto ASM

Just after the database migrated onto ASM, it is noticed that DG broker configuration files are still sitting on the default locations as following:

SQL>  show parameter dg_broker_config

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u02/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/dr1TESTDB.dat
dg_broker_config_file2               string      /u02/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/dr2TESTDB.dat

Stop the broker 

SQL> alter system set dg_broker_start = FALSE;

Set the dg_broker_config_file1 & 2 parameters to new location

SQL> alter system set dg_broker_config_file1 = '+DATAC1/TESTDB/dr1TESTDB.dat';
SQL> alter system set dg_broker_config_file2 = '+DATAC1/TESTDB/dr2TESTDB.dat'

Restart the broker 

SQL> alter system set dg_broker_start = TRUE;

Recreate the Data Guard Configuration

DGMGRL>  CREATE CONFIGURATION 'TESTDB' AS PRIMARY DATABASE IS 'TESTDB' CONNECT IDENTIFIER IS 'TESTDB' ;
Configuration "TESTDB" created with primary database "TESTDB"

DGMGRL>ADD DATABASE 'TESTBYDB' AS CONNECT IDENTIFIER IS 'TESTBYDB' MAINTAINED AS PHYSICAL;
Database "TESTBYDB" added

DGMGRL> enable configuration;
Enabled.

Verify the results

ASMCMD> pwd
+DATAC1/TESTDB
ASMCMD>  ls -lt
Type             Redund  Striped  Time             Sys  Name
                                                   Y    TEMPFILE/
                                                   Y    PASSWORD/
                                                   Y    PARAMETERFILE/
                                                   Y    ONLINELOG/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    DATAFILE/
                                                   Y    CONTROLFILE/
                                                   Y    2347F41E951336CFE053200F030A8127/
                                                   Y    2346D55DBF2B64B2E053200F030A299D/
DATAGUARDCONFIG  HIGH    COARSE   OCT 04 17:00:00  N    dr2testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.290.1085073589
DATAGUARDCONFIG  HIGH    COARSE   OCT 04 17:00:00  N    dr1testdb.dat => +DATAC1/TESTDB/DATAGUARDCONFIG/TESTDB.561.1085073589

How to Configure Multiple Standby Databases in Data Guard

This post demonstrates how to configure multiple standby databases. The following environment and namings are involved in this post:

Primary Database :
db_name : PRIMDB
db_unique_name : PRIMDB
Standby Database 1 :
db_name : PRIMDB
db_unique_name : STDBY1DB
Standby Database 2:
db_name : PRIMDB
db_unique_name : STDBY2DB
Standby Database 3 :
db_name : PRIMDB
db_unique_name : STDBY3DB

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

ORA-01033 When Adding Standby Database into Data Guard Configuration

The following errors occur when adding a standby database to DG: 

DGMGRL> add database 'stdby_db'  as connect identifier is 'stdby_db'  maintained as physical;
Error: ORA-01033: ORACLE initialization or shutdown in progress

Failed.

SOLUTION

Make sure the password file is copied from primary database, and configured properly in RAC:

On standby database server:

$ srvctl config database -d stdby_db
...
..
Password file:             <======= password file is not on ASM and will be read from $ORACLE_HOME/dbs instead

Copy the correct password file from Primary’s ASM to the Standby’s ASM and modify the password file path in OCR:


$ srvctl modify database -d stdby_db -pwfile <ASM path for the password file>

dbms_metadata to Generate DDL

The following command is generally used to extract object DDL:

SQL>SELECT DBMS_METADATA.GET_DDL('object_type','object_name','object owner') FROM DUAL;

Generate a Table DDL

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','OWNER') FROM DUAL;

Generate All Tables DDL

SQL> select dbms_metadata.get_ddl('TABLE',t.table_name, t.owner) from dba_tables t where owner='USER_NAME' ;

Generate an Index DDL

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','INDEX_NAME','OWNER') FROM DUAL;

Generate All Indexes DDL

SQL> select dbms_metadata.get_ddl('INDEX',i.index_name, i.owner) from dba_indexes i where table_owner='USER_NAME' ;

Generate a View DDL

SQL> Select Dbms_metadata.get_ddl ('VIEW', 'VIEW_NAME', 'OWNER') from dual;

Generate All Views DDL

SQL> SELECT dbms_metadata.GET_DDL ('VIEW', v.view_name,v.owner) from dba_views v;

Generate a Materialized View DDL

SQL> SELECT query FROM  DBA_MVIEWS WHERE  MVIEW_NAME='MV_NAME';

SQL> Select Dbms_metadata.get_ddl ('MATERIALIZED_VIEW', 'MVIEW_NAME', 'MV_OWNER') from dual;

Generate All Materialized Views DDL

SQL> SELECT dbms_metadata.GET_DDL ('MATERIALIZED_VIEW', mv.mview_name,mv.owner) from dba_mviews mv;

Generate a Procedure DDL

SQL>select dbms_metadata.get_ddl ('PROCEDURE','PROCEDURE_NAME','OWNER') from dual;

Generate All Procedures DDL

SQL>select dbms_metadata.get_ddl ('PROCEDURE', o.object_name, o.owner,) from dba_objects o where o.object_type = 'PROCEDURE' and o.owner='USER';

Generate a Function DDL

SQL>select dbms_metadata.get_ddl ('FUNCTION', 'FUNCTION_NAME', 'OWNER') from dual;

Generate All Functions DDL

SQL>select dbms_metadata.get_ddl ('FUNCTION', f.object_name, f.owner,) from dba_objects f where f.object_type = 'FUNCTION' and f.owner='USER';

Generate a Package DDL

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','PACKAGE_NAME','OWNER') FROM DUAL;

Generate a Package Body DDL

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PACKAGE_NAME','OWNER') FROM DUAL;

Generate a Tablespace DDL

SQL>  SELECT DBMS_METADATA.GET_DDL('TABLESPACE','TABLESPACE_NAME') FROM dual;

Generate All Tablespace DDL

SQL> SELECT dbms_metadata.GET_DDL('TABLESPACE', ts.tablespace_name) from dba_tablespaces ts;

Generate Foreign Key Constraints DDL

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','TABLE_NAME','OWNER') from dual;

System Privileges Granted to User

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','USER') from dual;

Generate Roles Granted DDL

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','USER') from dual;

Generate Objects Granted to User DDL

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','USER') from dual;