Category Archives: Uncategorized

Start DG Database Automatically

In order to start a DG database automatically you can use DGMGRL. You must have parameter dg_broker_start=true,
You can set it to true using:
SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

When database is started from DGMGRL, it starts the database in mode according to database_role.

For Primary, it will start database in open read write stage.
For Physical Standby, it will start database in mount stage and also start the managed recovery.
For Logical Standby, it will start database in open stage and also start the SQL apply.

Steps for Windows:
1- Set ORA_SID_AUTOSTART to false in windows registry.

2- Create a batch job on the both primary and standby database:
start1.bat
=========
set ORACLE_SID=
dgmgrl / “startup”

start2.bat
======
set ORACLE_SID=
dgmgrl sys/ “startup”

++ Use either of 2 (start1.bat or start2.bat), only difference in these is OS and database authentication.

3- Now schedule this bat file to execute automatically at the system reboot to start the database automatically

Continue reading Start DG Database Automatically

All Datapump Options

Oracle 12c datapump have following clauses, which we would discuss one by one:-

EXCLUDE – This is used to filter the metadata from the export operation. This is more like a where clause which you can use to exclude any object or schema from the export job.

Example if you want a full database export except 1 schema (say TEST), then:-

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp FULL=YES EXCLUDE=SCHEMA:"='TEST'" LOGFILE=test.log

Similarly suppose you want to exclude all indexes from a schema export and the condition is to export only indexes that starts with TEST_%, then:-

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST EXCLUDE=INDEX:"LIKE 'TEST_%'" LOGFILE=test.log

So using this clause you can exclude any specific content from the dump, try and explore this option more. To get a list of available objects that can be excluded query the OBJECT_PATH column of DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS views.

INCLUDE – Exactly opposite to EXCLUDE clause, this is used when you explicitly want to export, say only some tables, indexes along with views, packages in a schema dump, so only the the object types explicitly mentioned in the include clause would be imported and nothing else.

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST INCLUDE=TABLE:"IN ('EMP', 'DEPT')" INCLUDE=PROCEDURE INCLUDE=INDEX:"LIKE 'TEST%'" LOGFILE=test.log

So the above expdp command would only export tables EMP & DEPT, PROCEDURE and all indexes starting with TEST% no other objects of TEST schema would be exported.

QUERY – This clause allows you to filter the data that gets exported, think of it as EXCLUDE & INCLUDE clause but with more control over filtering, thus this is more like a WHERE clause of the expdp command.

When the QUERY parameter is specified for a table, Data Pump uses external tables to unload the target table.

It accepts values in the form:- QUERY = [schema.][table_name:] query_clause

1
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST QUERY=TEST.EMP:"WHERE DEPT_ID > 90 AND SAL > 90000" LOGFILE=test.log

Above command would export all tables of TEST schema but only specific records of EMP table.

CLUSTER – This is used in RAC env. and used to spread the worker process on other oracle instances to utilize RAC resources for datapump jobs. Continue reading All Datapump Options