Converting a Physical Standby Database into a Snapshot Standby Database

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Before starting into the steps it is worth to know what is snapshot standby database. 

A snapshot standby database is created from physical standby database. Once we create a snapshot standby database it continues to receive redo log from primary database and archive the redo logs but it does not apply redo log or archived redo log to the snapshot standby database.

Redo data received from the primary database is applied when a snapshot standby database is converted back into a physical standby database, after discarding all local updates to the snapshot standby database.

A snapshot standby database typically diverges from its primary database over time because redo data from the primary database is not applied as it is received. As once we create snapshot standby database in READ WRITE mode then we can update the database locally, so local updates to the snapshot standby database will cause additional divergence. The data in the primary database is fully protected however, because a snapshot standby can be converted back into a physical standby database at any time, and the redo data received from the primary will then be applied.

The following are the steps to convert a physical standby database into a snapshot standby database in RAC environment.

Step 01: Stop Redo Apply in Physical Standby Database, if it is active.
Login to any database instance of the Physical standby database if it is RAC. In case of Non-RAC you have only once instance/one database.

- Check status by,

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;  OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME -------------------- ---------------- --------- ------------------------------ MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS 

- I have also checked the instance to know in which instance I am performing stop redo log.

SQL> select instance_name from v$instance;  INSTANCE_NAME ---------------- bddipdc1 

- Issue stop redo log apply command.

SQL> alter database recover managed standby database cancel;  Database altered. 

Step 02: In case of RAC database, shut down all but one instance.
If you are non RAC environment then simply you can ignore this step. In case of RAC environment just you need to shutdown other instances except one. In this case I have 2 node RAC and I am just shutting down bddipdc2 instance.

$ sqlplus sys/oracle@bddipdrs2 as sysdba  SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 8 12:48:48 2011  Copyright (c) 1982, 2009, Oracle.  All rights reserved.  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options  SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;  OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME -------------------- ---------------- --------- ------------------------------ MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS  SQL>  select instance_name from v$instance;  INSTANCE_NAME ---------------- bddipdc2  SQL> shutdown immediate; ORA-01109: database not open   Database dismounted. ORACLE instance shut down. 

Step 03: Ensure that database is mounted and not open. Also ensure that a fast recovery areahas been configured. 
It is not necessary for flashback database to be enabled but you should enable FLASHBACK database if you want to convert a snapshot standby database back into a physical standby database.

Checking fast recovery area by,

SQL> archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     1 Next log sequence to archive   1 Current log sequence           1  SQL> show parameter db_reco  NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest                string      +RECOVERY db_recovery_file_dest_size           big integer 800G 

which means fast recovery area is configured as we are using DB_RECOVERY_FILE_DEST as archival destination. 

You can check it is mounted but not opened by,

SQL> select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;  OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME -------------------- ---------------- --------- ------------------------------ MOUNTED              PHYSICAL STANDBY BDDIPDC   BDDIPDRS 

You can check flashback database option by,

SQL> select flashback_on from v$database;  FLASHBACK_ON ------------------ YES 

Step 04: Issue the "CONVERT TO SNAPSHOT STANDBY" command.

SQL> alter database convert to snapshot standby;  Database altered. 

Step 05: Open the database and check the database_role and open_mode.

SQL> alter database open;  Database altered.  SQL>  select open_mode , database_role, name, DB_UNIQUE_NAME  from v$database;  OPEN_MODE            DATABASE_ROLE    NAME      DB_UNIQUE_NAME -------------------- ---------------- --------- ------------------------------ READ WRITE           SNAPSHOT STANDBY BDDIPDC   BDDIPDRS 

Now let's see in the alert log to know what happens in the backend.

alter database recover managed standby database cancel Tue Mar 08 12:17:24 2011 MRP0: Background Media Recovery cancelled with status 16037 Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc: ORA-16037: user requested cancel of managed recovery operation Recovery interrupted! Waiting for MRP0 pid 17366 to terminate Errors in file /u01/app/oracle/diag/rdbms/bddipdrs/bddipdc1/trace/bddipdc1_pr00_17371.trc: ORA-16037: user requested cancel of managed recovery operation Tue Mar 08 12:17:24 2011 MRP0: Background Media Recovery process shutdown (bddipdc1) Managed Standby Recovery Canceled (bddipdc1) Completed: alter database recover managed standby database cancel Tue Mar 08 12:17:42 2011 Reconfiguration started (old inc 8, new inc 10) List of instances:  1 (myinst: 1)  Global Resource Directory frozen  * dead instance detected - domain 0 invalid = TRUE  Communication channels reestablished  Master broadcasted resource hash value bitmaps  Non-local Process blocks cleaned out Tue Mar 08 12:17:42 2011  LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Tue Mar 08 12:17:42 2011  LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived  Set master node info  Submitted all remote-enqueue requests  Dwn-cvts replayed, VALBLKs dubious  All grantable enqueues granted  Post SMON to start 1st pass IR  Submitted all GCS remote-cache requests  Post SMON to start 1st pass IR  Fix write in gcs resources Reconfiguration complete Tue Mar 08 12:17:53 2011 alter database convert to snapshot standby Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_03/08/2011 12:17:53 Tue Mar 08 12:17:53 2011 krsv_proc_kill: Killing 219043332100 processes (all RFS) Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival RESETLOGS after incomplete recovery UNTIL CHANGE 449582938 Resetting resetlogs activation ID 1523683415 (0x5ad19057) Online log +DATA/bddipdrs/onlinelog/group_1.351.728933147: Thread 1 Group 1 was previously cleared Online log +DATA/bddipdrs/onlinelog/group_1.256.728933147: Thread 1 Group 1 was previously cleared Online log +DATA/bddipdrs/onlinelog/group_2.356.728933149: Thread 1 Group 2 was previously cleared Online log +DATA/bddipdrs/onlinelog/group_2.326.728933149: Thread 1 Group 2 was previously cleared Online log +DATA/bddipdrs/onlinelog/group_4.379.728933149: Thread 2 Group 4 was previously cleared Online log +DATA/bddipdrs/onlinelog/group_4.366.728933149: Thread 2 Group 4 was previously cleared Online log +DATA/bddipdrs/onlinelog/group_14.374.728933149: Thread 2 Group 14 was previously cleared Online log +DATA/bddipdrs/onlinelog/group_14.354.728933149: Thread 2 Group 14 was previously cleared Standby became primary SCN: 449582936 Tue Mar 08 12:17:55 2011 Setting recovery target incarnation to 6 CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby Completed: alter database convert to snapshot standby alter database open This instance was first to open Picked broadcast on commit scheme to generate SCNs Tue Mar 08 12:18:00 2011 Assigning activation ID 1523975741 (0x5ad6063d) Thread 1 opened at log sequence 1   Current log# 1 seq# 1 mem# 0: +DATA/bddipdrs/onlinelog/group_1.351.728933147   Current log# 1 seq# 1 mem# 1: +DATA/bddipdrs/onlinelog/group_1.256.728933147 Successful open of redo thread 1 Tue Mar 08 12:18:01 2011 ARC4: Becoming the 'no SRL' ARCH MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Mar 08 12:18:01 2011 ARC0: Becoming the 'no SRL' ARCH Tue Mar 08 12:18:01 2011 SMON: enabling cache recovery Instance recovery: looking for dead threads Instance recovery: lock domain invalid but no dead threads Successfully onlined Undo Tablespace 2. Dictionary check beginning Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is AL32UTF8 No Resource Manager plan active Starting background process GTX0 Tue Mar 08 12:18:02 2011 GTX0 started with pid=43, OS id=24612 Starting background process RCBG Tue Mar 08 12:18:02 2011 RCBG started with pid=44, OS id=24614 replication_dependency_tracking turned off (no async multimaster replication found) Redo thread 2 internally disabled at seq 1 (CKPT) Tue Mar 08 12:18:02 2011 ARC3: Becoming the 'no SRL' ARCH ARC3: Archiving disabled thread 2 sequence 1 Archived Log entry 48206 added for thread 2 sequence 1 ID 0x0 dest 1: Tue Mar 08 12:18:03 2011 Starting background process QMNC Tue Mar 08 12:18:03 2011 QMNC started with pid=46, OS id=24618 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Completed: alter database open 

Note that, when the physical standby is converted to a snapshot standby database a guaranteed restore point is created. In this case we see the name is SNAPSHOT_STANDBY_REQUIRED_03/08/2011. When the snapshot standby is converted back into a physical standby this restore point will be used to flashback the standby to its original state prior to the conversion. 

If you want to convert the snapshot standby database into physical standby database then you simply need to do,

SQL> shutdown immediate SQL> startup mount SQL> alter database convert to physical standby; 

In case of RAC you just need to shut down all but one instance.

Get Oracle Certifications for all Exams
Free Online Exams.com

Failed to create a peer profile for Oracle Cluster GPnP. gpnptool rc=256

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description
During the installation of Oracle Grid Infrastructure on Linux, while running root.sh it fails with error. From the logfile we see the following error./u01/app/11.2.0/grid/cfgtoollogs/crsconfig/rootcrs_vis.log:

[root@rac1 grid]# ./root.sh  Running Oracle 11g root.sh script...   The following environment variables are set as:  ORACLE_OWNER= oracle  ORACLE_HOME= /u01/app/11.2.0/grid   Enter the full pathname of the local bin directory: [/usr/local/bin]:  Copying dbhome to /usr/local/bin ...  Copying oraenv to /usr/local/bin ...  Copying coraenv to /usr/local/bin ...    Creating /etc/oratab file...  Entries will be added to the /etc/oratab file as needed by  Database Configuration Assistant when a database is created  Finished running generic part of root.sh script.  Now product-specific root actions will be performed.  2009-11-17 09:38:12: Parsing the host name  2009-11-17 09:38:12: Checking for super user privileges  2009-11-17 09:38:12: User has super user privileges  Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params  Creating trace directory  LOCAL ADD MODE  Creating OCR keys for user 'root', privgrp 'root'..  Operation successful.  root wallet  root wallet cert  root cert export  peer wallet  profile reader wallet  pa wallet  peer wallet keys  pa wallet keys  peer cert request  pa cert request  peer cert  pa cert  peer root cert TP  profile reader root cert TP  pa root cert TP  peer pa cert TP  pa peer cert TP  profile reader pa cert TP  profile reader peer cert TP  peer user cert  pa user cert  Failed to create a peer profile for Oracle Cluster GPnP. gpnptool rc=256  Creation of Oracle GPnP peer profile failed for linux1 at /u01/app/11.2.0/grid/crs/install/crsconfig_lib.pm line 4138.  

Cause of the Problem
This problem happened if on the host machine Firewall is enabled or Security Enhanced linux(SELinux) is enabled.

Solution of the Problem
Step 01: Login as root user on the node which you have ran root.sh. Check whether SELinux is enabled or not.
You can check by, 
# /usr/sbin/sestatus 
command.

Step 02: If enabled you need to disable it permanently as well as temporarily unless you want to restart your server. Temporarily disable is very good workaround because you no longer need to restart your OS and also no need to reinstall everything.

Step 03: You can permanently disable SELinux by editing file /etc/selinux/config and set SELINUX =disabled. 
# vi /etc/selinux/config
SELINUX=disabled

Temporarily disabled SELinux by setting setenforce to 0.
# /usr/sbin/setenforce 0 (to disable which is permissive, meaning SELinux is running and logging events but not controlling permissions)

If you want to know detail about the procedure have a look at How to disable SELinux.

Step 04: Deconfig crs on the nodes which it is failed.
# $GRID_HOME/crs/install/roothas.pl -delete -force -verbose
In order to know detail information about what to do after failure of Oracle 11gR2 Grid Infrastructure (CRS) Installation have a look at http://arjudba.blogspot.com/2010/03/what-to-do-after-failure-of-oracle.html.

Step 05: Run root.sh again.
[root@rac1 grid]# ./root.sh

Get Oracle Certifications for all Exams
Free Online Exams.com

root.sh fails with Fatal error: failed to initialize XDK. XDK error XMLERR_URL_INIT (54)

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description
During installation of Oracle 11.2g infrastructure running root.sh fails with the following error:

Fatal error: failed to initialize XDK. XDK error XMLERR_URL_INIT (54).  Fatal error: cannot initialize GPnP, CLSGPNP_XML_ERR (Generic XML error).  Fatal: GPnP init failed. Make sure GPnP client setup is correct (wallets, etc.) Failed to create a peer profile for Oracle Cluster GPnP. gpnptool rc=256 

Cause of the Problem
This is in fact Oracle bug. This bug fires if environment variable http_proxy is set. 

Solution of the Problem
Unset Oracle environmental variable http_proxy and run the root.sh file again.

Get Oracle Certifications for all Exams
Free Online Exams.com

Startup of RAC database fails with ORA-01105 & ORA-01677

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description
Oracle Data Guard was setup between two RAC databases and after switch-over operation is performed one node of primary database can be opened but another node can't be mounted. Following is the output while starting the instance using srvctl command. 

$ srvctl start database -d orcl1
PRCR-1079 : Failed to start resource ora.orcl1.db
ORA-01105: mount is incompatible with mounts by other instances
ORA-01677: standby file name convert parameters differ from other instance
CRS-2674: Start of 'ora.orcl1.db' on 'rac1' failed
CRS-2632: There are no more servers to try to place resource 'ora.orcl1.db' on that would satisfy its placement policy

If we try to start the instance using SQL*Plus it also fails with error message ORA-01105 and ORA-01677 while mounting the database. 

Cause of the Problem
The 'ORA-01105: mount is incompatible with mounts by other instances' error indicates that the parameter file does not match with the parameter file of another instance. So, you must verify the list of initialization parameters of one node with the list of initialization parameters on another node. The 'ORA-01677: standby file name convert parameters differ from other instance' error message gives you a hint which specific initialization parameter mismatch with another instance. It means the log_file_name_convert initialization parameter differs between two nodes. 

Solution of the Problem
1) The best way to solve the problem is to use shared storage for your database initialization parameter (spfile) and keep same spfile for both instances so that if you perform any modification from database using ALTER SYSTEM command both instance are affected. So, check the spfile location from one instance and use same spfile for another instance. 

2) If you use different spfile/pfile to start your database instance then set log_file_name_convert parameter to the same as it is used in another instance so that both instance has same value. 

3) Once you ensure same parameter value in both database try to mount and start the instance.

Get Oracle Certifications for all Exams
Free Online Exams.com

ORA-600 or Data Corruption during database shutdown of RAC instance

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description
Today I want to introduce one terrible Oracle bug that fires in Oracle RAC environment, cause data corruption while shutting down RAC instances. The issue can occur when shutting down Oracle RAC instances using the shutdown mode "normal, transactional, or immediate" and the instance(s) are shutdown in a rolling fashion; i.e. other instances remain to operate normally. The issue does not occur, if the instances are shutdown using "shutdown abort" or the database is shutdown as a whole as a workaround. 

Cause of the Problem
Data corruption happens due to Oracle bug. Oracle named this bug as 10205230. The following symbols found in the alert log when you hit this bug. 
- ORA-600 [kclchkblk_3]
- ORA-600 [kclwcrs_6]
- ORA-600 [ktubko_1]
- ORA-600 [kcratr_scan_lostwrt]
- ORA-600[3020] on the standby database
And data corruption happens in your database. When one RAC instance is working normally and you shutdown another instance either by any of following commands then this corruption happens.
So avoid these commands. 
- shutdown normal
- shutdown transactional
- shutdown immediate

Solution of the Problem
1)Shutdown abort whole database instead of shut down one to avoid corruption:

SQL> alter system checkpoint;

$ srvctl stop database -d -o abort -f

The first command from SQL*plus writes out dirty buffers for all instances to minimize instance recovery; The srvctl command shutdown all instances with abort option and its dependent resources. Shutdown abort completely by-passes the vulnerable code path of the bug.

2) If you need shutdown only one instance or in rolling fashion then instead of shutdown normal/transactional/immediate, you should shutdown each instance with the following 2 commands:
SQL> alter system checkpoint local;
SQL> shutdown abort;

The first command writes out dirty buffers for this instance to minimize instance recovery. Shutdown abort completely by-passes the vulnerable code path of the bug. Instance can also be shutdown abort using "srvctl stop instance -d db_unique_name -i instance_name -o abort". 

What to do if corruption already happens
As this bug may cause logical corruption to the redo stream so we need to recover corrupt blocks. I have written a topic regarding Handling block corruption in oracle. Also I have written another topic about how torecover data from corrupt blocks. 

The solution can be followings at the end.
- Drop and recreate the corrupt object. This may work for objects like indexes.

- Use DBMS_REPAIR package to repair and skip the corrupted blocks, and recreate the object with create table as select. This may work for objects like tables and partition tables, but may result in data loss.

- Failover to a standby database that has not been affected by the data corruption. This may result in data loss. Flashback or perform point-in-time recovery. This may also result in data loss.

- If a standby database encounters ORA-600[3020] stuck recovery, you can invoke trial recovery to see if there are other corrupt blocks:

SQL> recover automatic standby database allow n corruption test;

Here n can be 1 or any other integer.

One can also use the following command to allow standby recovery to continue by marking the problematic block as corrupt:

SQL> recover automatic standby database allow n corruption;

Get Oracle Certifications for all Exams
Free Online Exams.com

ORA-600 [4194] or ORA-600 [4193]

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem Description
ORA-600[4194] or ORA-600[4193] occurs when there is mismatch detected between Redo records and rollback (Undo) records. ORA-600[4194] / ORA-600[4193] error comes with two more arguments within bracket. 
Arg [a] indicates Maximum Undo record number in Undo block
Arg [b] indicates Undo record number from Redo block
Sample error is below:
ORA-00600: internal error code, arguments: [4194], [19], [33], [], [], []

Solution of the Problem - Drop the undo tablespace
This solution does not deal with Oracle unsupported parameter like _offline_rollback_segments and @_corrupted_rollback_segments to resolve ora-00600[4193]/[4194]. 

Step 01: 
Startup the database in nomount stage using spfile.
SQL> Startup nomount; 

In this case ensure you have started your database using spfile. "show parameter spfile" will display the location of spfile.

Create pfile from spfile.
SQL> Create pfile='/tmp/pfile.ora' from spfile ;

Shutdown the database.
SQL> Shutdown immediate; 

Step 02: 
Modify the pfile.ora and set Undo_managment=Manual
$vi /tmp/pfile.ora
undo_managment=manual

Start the database in mount mode using modified pfile.
SQL> Startup mount pfile='/tmp/pfile.ora'

Ensure that you have undo_managment initialization parameter set to manual.
SQL> Show parameter undo

SQL> Alter database open ;

Once it open, create a rollback segment.

SQL> Create rollback segment r01 ;

SQL> Alter rollback segment r01 online ;

Create a new undo tablespace

SQL> Create undo tablespace undotbs_new datafile 'datafile_name.dbf' size 100M ;

Drop the old undo tablespace

SQL> Drop tablespace undotbs_old including contents and datafiles

Step 03:

Shutdown the database.
SQL> Shutdown immediate;

Start the database using spfile where it had undo_managment initialization parameter set to AUTO.

SQL> Startup nomount ; 

Change the undo_tablespace parameter to use new tablespace.

SQL>Alter system set undo_tablespace=undotbs_new scope=spfile;

SQL> Shutdown immediate ;

SQL> Startup

Check if error is reported.

In case of RAC database instances from Instance which is up and running do the following:

SQL> Create undo tablespace undo_new datafile 'undotbs_new' size 100m ;

SQL> Alter system set undo_tablespace=undo_new sid={instance which has corrupt undo tablespace and is down} ;

Now Startup the Instance which is down

SQL> Startup mount

SQL> Show parameter undo

Should show the new undo tablespace created above

SQL>Alter database open ;

SQL>Drop tablespace undotbs_old including contents and datafiles


If all the Instance is down in the RAC due to this error then following the instruction given for Single instance and create new undo tablespace.

Get Oracle Certifications for all Exams
Free Online Exams.com

DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects

With the export/import new technology data pump we can filter to load/unload certain objects. This so-called 'Metadata filtering' is implemented through the EXCLUDE and INCLUDE parameters.
Incorrect usage of metadata filters, can result in errors such as:

ORA-39001: invalid argument value 
ORA-31655: no data or metadata objects selected for job
UDE-00011: parameter include is incompatible with parameter exclude 

How metadata can be filtered with the EXCLUDE and INCLUDE parameters. 

1. Syntax of the INCLUDE and EXCLUDE Data Pump parameters.


EXCLUDE = object_type[:name_clause] [, ...]
INCLUDE = object_type[:name_clause] [, ...] 

EXCLUDE=SEQUENCE, TABLE:"IN ('EMP', 'DEPT')"
INCLUDE=FUNCTION, PACKAGE, TABLE:"= 'EMP'" 

2. SQL Operator usage. 

EXCLUDE=TABLE:"IN ('EMP', 'DEPT')"

or:

EXCLUDE=INDEX:"= 'MY_INDX'"

or:

INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"

or:

INCLUDE=TABLE:"> 'E'" 

3.Double quotes and single quotes usage. 

The name clause is separated from the object type with a colon. The name clause must be enclosed in double quotation marks. The single-quotation marks are required to delimit the name strings. Using the INCLUDE or EXCLUDE parameter in a parameter file is the preferred method.
Parameter file: exp.par
-------------------------
DIRECTORY = my_dir
DUMPFILE = exp_tab.dmp
LOGFILE = exp_tab.log
SCHEMAS = scott
INCLUDE = TABLE:"IN ('EMP', 'DEPT')"

> expdp system/manager parfile=exp.par

To run this job without a parameter file, you need to escape the special characters. Incorrect escaping can result in errors such as: ksh: syntax error: '(' unexpected.
expdp system/manager DIRECTORY=my_dir \
DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"

4. Using the same filter name for an object type more than once. 

If multiple filters are specified for an object type, an implicit AND operation is applied to them. That is, the objects that are exported or imported during the job have passed all of the filters applied to their object types.

5. The EXCLUDE and INCLUDE parameters are mutually exclusive. 

It is not possible to specify both the INCLUDE parameter and the EXCLUDE parameter in the same job.
Parameter file - incorrect syntax (error: UDE-00011):

INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
EXCLUDE=INDEX:"= 'PK_EMP'"

Parameter file - correct syntax:

INCLUDE=TABLE:"IN ('EMP', 'DEPT')" 


6. Specified object types depend on the export/import Data Pump mode. 
During a TABLE level export/import, certain object types that are directly related to SCHEMA or DATABASE level jobs, cannot be specified. The same applies to a SCHEMA level export/import where no DATABASE level object types can be specified.

To determine the name of the object types can be specified with EXCLUDE and INCLUDE,

database_export_objects
schema_export_objects
table_export_objects

7. Only specific object types can be named with a Name clause.

The name clause applies only to object types whose instances have names (for example, it is applicable to TABLE, but not to GRANT).
To determine which object types can be named, you can use,database_export_objects and ....

EXCLUDE = TRIGGER:"IN ('TRIG1', 'TRIG2')", INDEX:"= 'INDX1'", REF_CONSTRAINT 

8. Excluding/Including an object, will also exclude/include it's dependent objects.

Dependent objects of an identified object are processed along with the identified object. For example, if a filter specifies that an index is to be included in an operation, then statistics from that index will also be included. Likewise, if a table is excluded by a filter, then indexes, constraints, grants, and triggers upon the table will also be excluded by the filter. 

To determine which objects are dependent, e.g. for a TABLE, you can run the following query,
SELECT named, object_path, comments 
FROM database_export_objects 
WHERE object_path LIKE 'TABLE/%';

9. Exporting or Importing a large number of objects.


If a large number of objects needs to be exported (or imported), it is possible that an internal buffer limit is exceeded (e.g. for the parameters INCLUDE or TABLES). If that happens it will cause the Data Pump job to abort with an error such as: ORA-06502 (PL/SQL: numeric or value error: character string buffer too small). This happened especially in Oracle10g Release 1 where the value for the internal buffer was set to 3000 bytes. With the fix for Bug 4053129 "EXPDP fails with ORA-39125 ORA-6502 on large list of table names" (not a public bug; fixed in 10.1.0.5.0 and 10.2.0.x), this value was increased to 4000 bytes.
When exporting a large number of objects, we recommend to make use of a table inside the database that contains the names of the objects.
Example:
-- create a table that contains the names of the objects:

CONNECT scott/tiger
CREATE TABLE expdp_table (owner VARCHAR2(30),
object_name VARCHAR2(128), object_type VARCHAR2(19));
INSERT INTO expdp_table VALUES ('SCOTT','EMP','TABLE');
INSERT INTO expdp_table VALUES ('SCOTT','DEPT','TABLE');
INSERT INTO expdp_table VALUES ('SCOTT','BONUS','TABLE');
...
COMMIT;
-- run export DataPump job:

expdp system/manager DIRECTORY=my_dir \
DUMPFILE=expdp_scott.dmp LOGFILE=expdp_scott.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(SELECT object_name FROM scott.expdp_table WHERE \
owner=\'SCOTT\' AND object_type=\'TABLE\'\)\"

On windows in one line following is valid.
expdp system/a SCHEMAS=aRJU INCLUDE=TABLE:\"IN \(SELECT object_name FROM dba_objects WHERE owner=\'ARJU\' AND object_type=\'TABLE\'\)\"

10. Other issues when excluding objects at a Data Pump job.

10.1. Excluding Constraints.
The following constraints cannot be excluded:
- NOT NULL constraints.
- Constraints needed for the table to be created and loaded successfully (for example, primary key constraints for index-organized tables or REF SCOPE and WITH ROWID constraints for tables with REF columns).

10.2. Excluding Grants.
Specifying EXCLUDE=GRANT excludes object grants on all object types and system privilege grants.

10.3. Excluding Users.
Specifying EXCLUDE=USER excludes only the definitions of users, not the objects contained within users' schemas. To exclude a specific user and all objects of that user, specify a filter such as the following (where SCOTT is the schema name of the user you want to exclude):
EXCLUDE=SCHEMA:"='SCOTT'"

If you try to exclude a user by using a statement such as EXCLUDE=USER:"= 'SCOTT'", only the CREATE USER scott DDL statement will be excluded, and you may not get the results you expect.
11. Other issues when including objects at a Data Pump job.

When specifying the INCLUDE parameter for en Export Data Pump or Import Data Pump job, only object types explicitly specified in INCLUDE statements (and their dependent objects) are exported/imported. No other object types, such as the schema definition information that is normally part of a schema-mode export when you have the EXP_FULL_DATABASE role, are exported/imported.

Get Oracle Certifications for all Exams
Free Online Exams.com