Thinking Out Loud

February 19, 2017

Data Pump or Data Pain Part04 – The End

Filed under: 11g,DataPump — mdinh @ 7:10 pm

Please take a look at my notes below:
DataPump: Schema/DB Migration Notes

February 15, 2017

Data Pump or Data Pain Part03 – user/db object

Filed under: 11g,DataPump — mdinh @ 4:48 am

You might be thinking, why is it necessary to specify schemas versus perform full import or why create SQL file?

$ cat impdp_full02_user.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_user.log
schemas=GGS_ADMIN,DEMO
include=USER
sqlfile=user.sql

There are 5 users imported which correspond to EXU8USR view.
If users already exists in the new database, wouldn’t create user just fail?

From demo below #schemas=GGS_ADMIN,DEMO (commented) which will
import SYS/SYSTEM/OUTLN

$ impdp parfile=impdp_full02_user.par

Import: Release 11.2.0.4.0 - Production on Tue Feb 14 19:04:05 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
Startup took 0 seconds
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA parfile=impdp_full02_user.par
Processing object type DATABASE_EXPORT/SYS_USER/USER
     Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
     Completed 4 USER objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Feb 14 19:04:07 2017 elapsed 0 00:00:01

One good reason to create SQL file is to know what changes will be made to DB ahead of time.

$ cat /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/user.sql

-- new object type path: DATABASE_EXPORT/SYS_USER/USER
-- CONNECT SYSTEM
 ALTER USER "SYS" IDENTIFIED BY VALUES 'S:A1856CFD100792EB56F0432B20D3C4AAD57A48DD2C89A94E055FE37B2DD0;8A8F025737A9097A'
      TEMPORARY TABLESPACE "TEMP";
-- new object type path: DATABASE_EXPORT/SCHEMA/USER
 ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'S:0966E912AE33B8963B86F44E9731A9D0126DF07B0A8EADF85E248BBC18D5;2D594E86F93B17A1'
      TEMPORARY TABLESPACE "TEMP";
 CREATE USER "OUTLN" IDENTIFIED BY VALUES 'S:14BB7D86CDE99B2AF179EA19879DCB4A7DA651430A671FE8453ADB858B35;4A3BA55E08595C81'
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE
      ACCOUNT LOCK;
 CREATE USER "GGS_ADMIN" IDENTIFIED BY VALUES 'S:2AD4199BA9BF38A158B1181515FA385823EABDD9945B84F9698037BF319A;2E16F5C363B2AFF8'
      DEFAULT TABLESPACE "GGS_DATA"
      TEMPORARY TABLESPACE "TEMP";
 CREATE USER "DEMO" IDENTIFIED BY VALUES 'S:63BE233139FAE11FA97490DC8D7CABDAED282C89A7D343CE4D45972C8087;4646116A123897CF'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";

$ grep DATABASE_EXPORT /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/expdp_full.log
|grep -v SCHEMA|sort

Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

SYS_USER excluded; otherwise, SYS password will be changed via ALTER USER.

$ cat impdp_full03_dbobj.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_dbobj.log
include=AUDIT
include=CONTEXT
include=DB_LINK
include=DIRECTORY
include=GRANT
include=PASSWORD_VERIFY_FUNCTION
include=PROFILE
include=PUBLIC_SYNONYM/SYNONYM
include=RESOURCE_COST
include=ROLE
include=TRUSTED_DB_LINK
include=SYSTEM_PROCOBJACT
sqlfile=dbobj.sql

Next, import objects from DATABASE_EXPORT.

$ impdp parfile=impdp_full03_dbobj.par

Import: Release 11.2.0.4.0 - Production on Tue Feb 14 20:34:31 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
Startup took 0 seconds
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA parfile=impdp_full03_dbobj.par
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
     Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/ROLE
     Completed 16 ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 4 PROC_SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Completed 48 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
     Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
     Completed 2 DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
     Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
     Completed 2 DIRECTORY objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 6 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/CONTEXT
     Completed 3 CONTEXT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 6 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 3 PROCACT_SYSTEM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 17 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 4 PROCACT_SYSTEM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 24 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 4 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 3 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/AUDIT
     Completed 29 AUDIT objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Tue Feb 14 20:34:33 2017 elapsed 0 00:00:02

$ egrep ‘LINK|SYNONYM|DIRECTORY’ /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/dbobj.sql

-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK
CREATE DATABASE LINK "PRIVATE_DBLINK.LOCALDOMAIN"
CREATE PUBLIC DATABASE LINK "PUBLIC_DB_LINK.LOCALDOMAIN"
-- new object type path: DATABASE_EXPORT/TRUSTED_DB_LINK
-- new object type path: DATABASE_EXPORT/DIRECTORY/DIRECTORY
 CREATE DIRECTORY "DATA_PUMP_DIR" AS '/u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/';
 CREATE DIRECTORY "TEST" AS '/tmp/';
-- new object type path: DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "DEMO";
GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "EXP_FULL_DATABASE";
GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "IMP_FULL_DATABASE";
GRANT READ ON DIRECTORY "DATA_PUMP_DIR" TO "EXP_FULL_DATABASE";
GRANT READ ON DIRECTORY "DATA_PUMP_DIR" TO "IMP_FULL_DATABASE";
GRANT WRITE ON DIRECTORY "TEST" TO "GGS_ADMIN";
-- new object type path: DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
CREATE  PUBLIC SYNONYM "OL$" FOR "SYSTEM"."OL$";
CREATE  PUBLIC SYNONYM "OL$HINTS" FOR "SYSTEM"."OL$HINTS";
CREATE  PUBLIC SYNONYM "OL$NODES" FOR "SYSTEM"."OL$NODES";
CREATE  PUBLIC SYNONYM "PRODUCT_PROFILE" FOR "SYSTEM"."PRODUCT_PRIVS";
CREATE  PUBLIC SYNONYM "PRODUCT_USER_PROFILE" FOR "SYSTEM"."PRODUCT_PRIVS";
CREATE  PUBLIC SYNONYM "PUBLIC_HAWK" FOR "GGS_ADMIN"."OGG$Q_TAB_E_HAWK";
oracle@arrow1:HAWKA:/media/sf_working/datapump
$

++++++++++

-- CONNECT DEMO
CREATE DATABASE LINK "PRIVATE_DBLINK.LOCALDOMAIN"
   CONNECT TO CURRENT_USER
   USING 'sales';
-- CONNECT SYS
CREATE PUBLIC DATABASE LINK "PUBLIC_DB_LINK.LOCALDOMAIN"
   CONNECT TO "REMOTE_USERNAME" IDENTIFIED BY VALUES ':1'
   USING 'test';

February 14, 2017

Data Pump or Data Pain Part02 – tablespace/user

Filed under: 11g,DataPump — mdinh @ 3:38 am

This is still all WIP and if you stay with me, I will provide all the sequences for export and import.

Too much info put in one post.

EXP-10 Usernames Which Cannot Be Exported (Doc ID 217135.1)
Did you know from $ORACLE_HOME/rdbms/admin/catexp.sql there is view EXU8USR providing which schemas will not be in full export?

This looks rather similar to column oracle_maintained from dba_users for 12c database.

Schema bold red will not be exported. Trust but verify.

oracle@arrow1:HAWKA:/media/sf_working/datapump
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 13 18:28:51 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options

ARROW1:(SYS@HAWKA):PRIMARY> set lines 120 tab off trimsp on pages 1000
ARROW1:(SYS@HAWKA):PRIMARY> col name for a55
ARROW1:(SYS@HAWKA):PRIMARY> select username from dba_users order by 1;

USERNAME
------------------------------
APPQOSSYS
DBSNMP
DEMO
DIP
GGS_ADMIN
ORACLE_OCM
OUTLN
SYS
SYSTEM

9 rows selected.

ARROW1:(SYS@HAWKA):PRIMARY> select name from exu8usr order by 1;

NAME
-------------------------------------------------------
DEMO
GGS_ADMIN
OUTLN
SYS
SYSTEM

ARROW1:(SYS@HAWKA):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
oracle@arrow1:HAWKA:/media/sf_working/datapump
$

First step is to pre-create tablespaces.

$ cat impdp_full01_tbs.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_tbs.log
include=TABLESPACE
sqlfile=tbs.sql

$ impdp parfile=impdp_full01_tbs.par

Import: Release 11.2.0.4.0 - Production on Mon Feb 13 18:46:56 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
Startup took 0 seconds
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA parfile=impdp_full01_tbs.par
Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 4 TABLESPACE objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Mon Feb 13 18:46:57 2017 elapsed 0 00:00:01

$ cat /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/tbs.sql

See how the filename is hard coded even when OMF is being used.

-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/TABLESPACE
CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE
  SIZE 268435456
  AUTOEXTEND ON NEXT 268435456 MAXSIZE 8192M
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  SIZE 1610612736
  AUTOEXTEND ON NEXT 268435456 MAXSIZE 8192M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576;
CREATE TABLESPACE "USERS" DATAFILE
  SIZE 135266304
  AUTOEXTEND ON NEXT 134217728 MAXSIZE 8193M,
  SIZE 16777216,
  SIZE 10485760
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

   ALTER DATABASE DATAFILE
  '/oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf' RESIZE 16785408;

CREATE TABLESPACE "GGS_DATA" DATAFILE
  SIZE 269484032
  AUTOEXTEND ON NEXT 268435456 MAXSIZE 16385M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 DEFAULT
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;

Basically datafile 5 was created with 16777216 and resized to 16785408.
Why did datapump not use the current size vs original size?
I know what you are probably thinking, why not just create SQL script to do the work.
True but it’s like buying a Mercedes-Benz and having to roll down the windows by hand. (Dating myself).

ARROW1:(SYS@HAWKA):PRIMARY> select file#,name,bytes from v$datafile where name like '%user%';

     FILE# NAME                                                         BYTES
---------- ------------------------------------------------------- ----------
         4 /oradata/HAWKA/datafile/o1_mf_users_d3q5b4gw_.dbf        135266304
         5 /oradata/HAWKA/datafile/o1_mf_users_d4gohzod_.dbf         16785408
         7 /oradata/HAWKA/datafile/o1_mf_users_db4vw289_.dbf         10485760

ARROW1:(SYS@HAWKA):PRIMARY>

SYSTEM and SYSAUX tablespaces are not exported while UNDO and TEMP are.
Hopefully, the following was performed to get all the details from DB creation.
alter database backup controlfile to trace as ‘/tmp/cf_@.sql’ reuse resetlogs;
select property_name,property_value from DATABASE_PROPERTIES;

Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 4 TABLESPACE objects in 1 seconds

+++++++++
     
ARROW1:(SYS@HAWKA):PRIMARY> select name from v$tablespace order by 1;

NAME
-------------------------------------------------------
GGS_DATA
SYSAUX
SYSTEM
TEMP
UNDOTBS
USERS

6 rows selected.

ARROW1:(SYS@HAWKA):PRIMARY>     

Last but not least, did you know you can create database in achivelog mode to begin with versus having to enable ARCHIVELOG mode after the fact?

Take a look at my post below.
OTN Appreciation Day : Create Database Using SQL | Thinking Out Loud Blog

ARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
SET TIME_ZONE=’US/Mountain’

February 11, 2017

Data Pump or Data Pain

Filed under: 11g,DataPump — mdinh @ 7:30 pm

WARNING: Rants ahead.

Simple request migrate schema from one database to another, right?

Create new database, perform schema export and import; this only works if objects are self contained.

The following objects are missing from schema export to name a few.
DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM

Here’s what was done and hopefully did not missed anything. TBS was pre-created.

$ cat impdp_full_public.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_public.log
include=PASSWORD_VERIFY_FUNCTION
include=PROFILE
include=ROLE
include=PUBLIC_SYNONYM/SYNONYM

$ cat impdp_full_schema.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_full_schema.log
include=SCHEMA:"IN ('DEMO')"

DEMO

$ cat expdp_schema.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
flashback_time="TO_TIMESTAMP(TO_CHAR(systimestamp,'DD-MM-YYYY HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')"
metrics=Y
reuse_dumpfiles=Y
dumpfile=schema.dmp
logfile=exp_schema.log
SCHEMAS=ggs_admin,demo

$ expdp parfile=expdp_schema.par

Export: Release 11.2.0.4.0 - Production on Sat Feb 11 10:47:22 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=expdp_schema.par
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
     Estimated 12 TABLE_DATA objects in 0 seconds
Total estimation using BLOCKS method: 19 MB
Processing object type SCHEMA_EXPORT/USER
     Completed 2 USER objects in 0 seconds
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
     Completed 14 SYSTEM_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/ROLE_GRANT
     Completed 5 ROLE_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
     Completed 2 DEFAULT_ROLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
     Completed 1 TABLESPACE_QUOTA objects in 0 seconds
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
     Completed 3 PROCACT_SCHEMA objects in 1 seconds
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
     Completed 2 SYNONYM objects in 0 seconds
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
     Completed 2 TYPE objects in 0 seconds
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
     Completed 1 SEQUENCE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
     Completed 14 PROCACT_INSTANCE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 12 TABLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
     Completed 1 FUNCTION objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
     Completed 2 PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
     Completed 1 ALTER_FUNCTION objects in 0 seconds
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
     Completed 2 ALTER_PROCEDURE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 1 INDEX objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 7 INDEX_STATISTICS objects in 0 seconds
Processing object type SCHEMA_EXPORT/VIEW/VIEW
     Completed 5 VIEW objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 12 TABLE_STATISTICS objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
     Completed 8 PROCACT_INSTANCE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
     Completed 8 PROCDEPOBJ objects in 1 seconds
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
     Completed 11 PROCOBJ objects in 0 seconds
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
     Completed 1 PROCACT_SCHEMA objects in 0 seconds
. . exported "DEMO"."T"                                  6.087 MB   68540 rows
. . exported "GGS_ADMIN"."OGG$Q_TAB_E_HAWK"              16.98 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_C"        5.804 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_G"        13.16 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_H"        9.039 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_I"        9.007 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_L"        7.828 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_S"        11.31 KB       3 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_T"        6.218 KB       0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_SEED"             11.81 KB       1 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT"                      0 KB       0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_HISTORY"              0 KB       0 rows
     Completed 12 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 1 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/schema.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Feb 11 10:47:29 2017 elapsed 0 00:00:07

$ cat expdp_full.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
flashback_time=systimestamp
metrics=Y
exclude=statistics
reuse_dumpfiles=Y
dumpfile=full.dmp
#PARALLEL=2
#DUMPFILE=full%U.dmp
logfile=expdp_full.log
FULL=Y

$ expdp parfile=expdp_full.par

Export: Release 11.2.0.4.0 - Production on Sat Feb 11 10:59:36 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA parfile=expdp_full.par
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
     Estimated 69 TABLE_DATA objects in 3 seconds
Total estimation using BLOCKS method: 23.31 MB
Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 4 TABLESPACE objects in 1 seconds
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
     Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYS_USER/USER
     Completed 1 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/USER
     Completed 4 USER objects in 0 seconds
Processing object type DATABASE_EXPORT/ROLE
     Completed 16 ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 4 PROC_SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Completed 48 SYSTEM_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
     Completed 49 ROLE_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
     Completed 4 DEFAULT_ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
     Completed 1 TABLESPACE_QUOTA objects in 0 seconds
Processing object type DATABASE_EXPORT/RESOURCE_COST
     Completed 1 RESOURCE_COST objects in 0 seconds
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
     Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
     Completed 18 SEQUENCE objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
     Completed 1 DIRECTORY objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 4 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/CONTEXT
     Completed 3 CONTEXT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 6 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
     Completed 10 SYNONYM objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
     Completed 11 TYPE objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 3 PROCACT_SYSTEM objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 17 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
     Completed 4 PROCACT_SYSTEM objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
     Completed 7 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
     Completed 14 PROCACT_INSTANCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
     Completed 72 TABLE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
     Completed 6 PRE_TABLE_ACTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 24 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
     Completed 424 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
     Completed 1 PACKAGE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
     Completed 5 FUNCTION objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 4 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
     Completed 4 PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
     Completed 5 ALTER_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
     Completed 4 ALTER_PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
     Completed 106 INDEX objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
     Completed 89 CONSTRAINT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
     Completed 17 VIEW objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 3 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
     Completed 7 COMMENT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
     Completed 1 PACKAGE_BODY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 36 REF_CONSTRAINT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
     Completed 4 POST_TABLE_ACTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
     Completed 2 TRIGGER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
     Completed 8 PROCACT_INSTANCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
     Completed 8 PROCDEPOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
     Completed 11 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
     Completed 4 PROCACT_SCHEMA objects in 0 seconds
Processing object type DATABASE_EXPORT/AUDIT
     Completed 29 AUDIT objects in 1 seconds
. . exported "DEMO"."T"                                  6.087 MB   68540 rows
. . exported "GGS_ADMIN"."OGG$Q_TAB_E_HAWK"              16.98 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_C"        5.804 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_G"        13.16 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_H"        9.039 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_I"        9.007 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_L"        7.828 KB       0 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_S"        11.31 KB       3 rows
. . exported "GGS_ADMIN"."AQ$_OGG$Q_TAB_E_HAWK_T"        6.218 KB       0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_SEED"             11.81 KB       1 rows
. . exported "SYSTEM"."DEF$_LOB"                         6.664 KB       0 rows
. . exported "OUTLN"."OL$HINTS"                          13.12 KB       0 rows
. . exported "SYSTEM"."DEF$_AQCALL"                      14.73 KB       0 rows
. . exported "SYSTEM"."DEF$_AQERROR"                     14.73 KB       0 rows
. . exported "SYSTEM"."REPCAT$_EXCEPTIONS"               7.843 KB       0 rows
. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL"         6.25 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS"            5.859 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS"         10.72 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"           7.085 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"         6.257 KB       0 rows
. . exported "OUTLN"."OL$"                               10.17 KB       0 rows
. . exported "OUTLN"."OL$NODES"                          7.781 KB       0 rows
. . exported "SYSTEM"."DEF$_CALLDEST"                        7 KB       0 rows
. . exported "SYSTEM"."DEF$_DEFAULTDEST"                 5.007 KB       0 rows
. . exported "SYSTEM"."DEF$_DESTINATION"                 13.50 KB       0 rows
. . exported "SYSTEM"."DEF$_ERROR"                       8.210 KB       0 rows
. . exported "SYSTEM"."DEF$_ORIGIN"                      7.390 KB       0 rows
. . exported "SYSTEM"."DEF$_PROPAGATOR"                  5.796 KB       0 rows
. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS"         6.218 KB       0 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE"          6.328 KB       2 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN"             7.843 KB       0 rows
. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP"             6.210 KB       0 rows
. . exported "SYSTEM"."REPCAT$_CONFLICT"                 6.226 KB       0 rows
. . exported "SYSTEM"."REPCAT$_DDL"                      7.406 KB       0 rows
. . exported "SYSTEM"."REPCAT$_EXTENSION"                9.890 KB       0 rows
. . exported "SYSTEM"."REPCAT$_FLAVORS"                  7.390 KB       0 rows
. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS"           8.187 KB       0 rows
. . exported "SYSTEM"."REPCAT$_GENERATED"                8.640 KB       0 rows
. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN"           6.601 KB       0 rows
. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS"              6.203 KB       0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS"             5.429 KB       0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES"             6.882 KB      28 rows
. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN"         8.679 KB       0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY"                 9.070 KB       0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP"           6.656 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES"        10.69 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCAT"                   7.398 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCATLOG"                13.09 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCOLUMN"                13.79 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS"           7.390 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPOBJECT"                10.67 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPPROP"                  8.226 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPSCHEMA"                8.609 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION"                8.25 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD"        5.835 KB      19 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS"    8.265 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL"      7.835 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITES_NEW"                7.015 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS"             6.210 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP"                    7 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"       7.046 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"           9.062 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS"          5.484 KB       3 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"         6.648 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES"           6.289 KB       2 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"      5.828 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"          7.867 KB       0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT"                      0 KB       0 rows
. . exported "GGS_ADMIN"."GG_HEARTBEAT_HISTORY"              0 KB       0 rows
     Completed 69 DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA objects in 2 seconds
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /u01/app/oracle/product/11.2.0.4/db_1/rdbms/log/full.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Sat Feb 11 10:59:53 2017 elapsed 0 00:00:17
$ cat impdp_full_sql.par
directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=full.dmp
logfile=impdp_syn.log
include=PASSWORD_VERIFY_FUNCTION
include=PROFILE
include=ROLE
include=PUBLIC_SYNONYM/SYNONYM
sqlfile=impdp_full.sql

$ impdp parfile=impdp_full_sql.par

Import: Release 11.2.0.4.0 - Production on Sat Feb 11 11:09:06 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
Startup took 0 seconds
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  /******** AS SYSDBA parfile=impdp_full_sql.par
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
     Completed 1 PASSWORD_VERIFY_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/PROFILE
     Completed 1 PROFILE objects in 0 seconds
Processing object type DATABASE_EXPORT/ROLE
     Completed 16 ROLE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 6 SYNONYM objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Sat Feb 11 11:09:09 2017 elapsed 0 00:00:02

$ cat $ORACLE_HOME/rdbms/log/impdp_full.sql

-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
CREATE FUNCTION verify_function_11G
(username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   db_name varchar2(40);
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);
   i_char varchar2(10);
   simple_password varchar2(10);
   reverse_user varchar2(32);

BEGIN
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

   -- Check for the minimum length of the password
   IF length(password) < 8 THEN
      raise_application_error(-20001, 'Password length less than 8');
   END IF;


   -- Check if the password is same as the username or username(1-100)
   IF NLS_LOWER(password) = NLS_LOWER(username) THEN
     raise_application_error(-20002, 'Password same as or similar to user');
   END IF;
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to user name ');
      END IF;
    END LOOP;

   -- Check if the password is same as the username reversed

   FOR i in REVERSE 1..length(username) LOOP
     reverse_user := reverse_user || substr(username, i, 1);
   END LOOP;
   IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
     raise_application_error(-20003, 'Password same as username reversed');
   END IF;

   -- Check if the password is the same as server name and or servername(1-100)
   select name into db_name from sys.v$database;
   if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
      raise_application_error(-20004, 'Password same as or similar to server name');
   END IF;
   FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to server name ');
      END IF;
    END LOOP;

   -- Check if the password is too simple. A dictionary of words may be
   -- maintained and a check may be made so as not to allow the words
   -- that are too simple for the password.
   IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN
      raise_application_error(-20006, 'Password too simple');
   END IF;

   -- Check if the password is the same as oracle (1-100)
    simple_password := 'oracle';
    FOR i IN 1..100 LOOP
      i_char := to_char(i);
      if simple_password || i_char = NLS_LOWER(password) THEN
        raise_application_error(-20007, 'Password too simple ');
      END IF;
    END LOOP;

   -- Check if the password contains at least one letter, one digit
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;

   IF isdigit = FALSE THEN
      raise_application_error(-20008, 'Password must contain at least one digit, one character');
   END IF;
   -- 2. Check for the character
   <>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP
      FOR j IN 1..m LOOP
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20009, 'Password must contain at least one \
              digit, and one character');
   END IF;


   <>
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);

     differ := abs(differ);
     IF differ < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;

       FOR i IN 1..m LOOP
         IF substr(password,i,1) != substr(old_password,i,1) THEN
           differ := differ + 1;
         END IF;
       END LOOP;

       IF differ < 3 THEN
         raise_application_error(-20011, 'Password should differ from the \
            old password by at least 3 characters');
       END IF;
     END IF;
   END IF;
   -- Everything is fine; return TRUE ;
   RETURN(TRUE);
END;
/
-- new object type path: DATABASE_EXPORT/PROFILE
 ALTER PROFILE "DEFAULT"
    LIMIT
         COMPOSITE_LIMIT UNLIMITED
         SESSIONS_PER_USER UNLIMITED
         CPU_PER_SESSION UNLIMITED
         CPU_PER_CALL UNLIMITED
         LOGICAL_READS_PER_SESSION UNLIMITED
         LOGICAL_READS_PER_CALL UNLIMITED
         IDLE_TIME UNLIMITED
         CONNECT_TIME UNLIMITED
         PRIVATE_SGA UNLIMITED
         FAILED_LOGIN_ATTEMPTS 10
         PASSWORD_LIFE_TIME 15552000/86400
         PASSWORD_REUSE_TIME UNLIMITED
         PASSWORD_REUSE_MAX UNLIMITED
         PASSWORD_VERIFY_FUNCTION "VERIFY_FUNCTION_11G"
         PASSWORD_LOCK_TIME 86400/86400
         PASSWORD_GRACE_TIME 604800/86400 ;
-- new object type path: DATABASE_EXPORT/ROLE
 CREATE ROLE "SELECT_CATALOG_ROLE";

 REVOKE "SELECT_CATALOG_ROLE" FROM SYS;
 CREATE ROLE "EXECUTE_CATALOG_ROLE";

 REVOKE "EXECUTE_CATALOG_ROLE" FROM SYS;
 CREATE ROLE "DELETE_CATALOG_ROLE";

 REVOKE "DELETE_CATALOG_ROLE" FROM SYS;
 CREATE ROLE "DBFS_ROLE";

 REVOKE "DBFS_ROLE" FROM SYS;
 CREATE ROLE "AQ_ADMINISTRATOR_ROLE";

 REVOKE "AQ_ADMINISTRATOR_ROLE" FROM SYS;
 CREATE ROLE "AQ_USER_ROLE";

 REVOKE "AQ_USER_ROLE" FROM SYS;
 CREATE ROLE "ADM_PARALLEL_EXECUTE_TASK";

 REVOKE "ADM_PARALLEL_EXECUTE_TASK" FROM SYS;
 CREATE ROLE "GATHER_SYSTEM_STATISTICS";

 REVOKE "GATHER_SYSTEM_STATISTICS" FROM SYS;
 CREATE ROLE "RECOVERY_CATALOG_OWNER";

 REVOKE "RECOVERY_CATALOG_OWNER" FROM SYS;
 CREATE ROLE "SCHEDULER_ADMIN";

 REVOKE "SCHEDULER_ADMIN" FROM SYS;
 CREATE ROLE "HS_ADMIN_SELECT_ROLE";

 REVOKE "HS_ADMIN_SELECT_ROLE" FROM SYS;
 CREATE ROLE "HS_ADMIN_EXECUTE_ROLE";

 REVOKE "HS_ADMIN_EXECUTE_ROLE" FROM SYS;
 CREATE ROLE "HS_ADMIN_ROLE";

 REVOKE "HS_ADMIN_ROLE" FROM SYS;
 CREATE ROLE "GLOBAL_AQ_USER_ROLE" IDENTIFIED GLOBALLY;
 CREATE ROLE "OEM_ADVISOR";

 REVOKE "OEM_ADVISOR" FROM SYS;
 CREATE ROLE "OEM_MONITOR";

 REVOKE "OEM_MONITOR" FROM SYS;
-- new object type path: DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
CREATE  PUBLIC SYNONYM "OL$" FOR "SYSTEM"."OL$";
CREATE  PUBLIC SYNONYM "OL$HINTS" FOR "SYSTEM"."OL$HINTS";
CREATE  PUBLIC SYNONYM "OL$NODES" FOR "SYSTEM"."OL$NODES";
CREATE  PUBLIC SYNONYM "PRODUCT_PROFILE" FOR "SYSTEM"."PRODUCT_PRIVS";
CREATE  PUBLIC SYNONYM "PRODUCT_USER_PROFILE" FOR "SYSTEM"."PRODUCT_PRIVS";
CREATE  PUBLIC SYNONYM "PUBLIC_HAWK" FOR "GGS_ADMIN"."OGG$Q_TAB_E_HAWK";

September 13, 2015

Scaling Export and Import Tables Residing in Different Schemas 10gR2

Filed under: 10g,DataPump,oracle — mdinh @ 2:40 pm

Our team was tasked to unpartion all partition tables.

Import: Release 11.2.0.4.0 has PARTITION_OPTIONS=DEPARTITION but we are on Release 10.2.0.4.0

The first step was to find all schemas with partition tables.

select owner, table_name, partitioning_type, subpartitioning_type, partition_count, status 
from dba_part_tables where owner not in ('SYS','SYSTEM') 
and (owner,table_name) not in (
 select owner,mview_name table_name 
 from dba_mviews 
 where owner not in ('SYS','SYSTEM') 
) 
order by 1,2
;

NOTE: SQL is not 100% fail proof as we ran into a scenario where the table and materialized view had the same name.

Export the metatadata for table from multiple schemas failed:

UDE-00012: table mode exports only allow objects from one schema

Export/Import DataPump Parameter TABLES – How to Export and Import Tables Residing in Different Schemas (Doc ID 277905.1)

Solution 1: Use combination of SCHEMAS and INCLUDE parameters.
Example:
File: expdp_tabs.par 
-------------------- 
DIRECTORY = my_dir  
DUMPFILE  = expdp_tabs.dmp  
LOGFILE   = expdp_tabs.log  
SCHEMAS   = scott,hr,oe   
INCLUDE   = TABLE:"IN ('EMP','EMPLOYEES','CATEGORIES_TAB')" 

Great solution but not scalable.

Dig to find – How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases
http://uralural.blogspot.com/2011/06/how-to-export-tables-from-multiple.html

I will let you read the post; however, the following was the key for me.

[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp '"/ as sysdba"' directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables schemas=U0001,U0002 
INCLUDE=TABLE:\"IN \(SELECT table_name FROM u0001.expdp_tables\)\"                                            

Database has 22 partition tables.

*** List partition tables, exluding MVIEW ***
22 rows selected.

There are 11 schemas with partition tables.

*** List partition tables count by owner, exluding MVIEW ***
11 rows selected.

Partition table SYSTEM_QUEUE resides in 7 different schemas and ACCOUNT_OBJECT_TRANSACTIONS resides in 2 different schemas.


*** List same table name across owner, exluding MVIEW ***
TABLE_NAME			 COUNT(*)
------------------------------ ----------
SYSTEM_QUEUE				7
ACCOUNT_OBJECT_TRANSACTIONS		2

Create tables to use for export.

create table OWNER01.expdp_tables (table_name varchar2(30))
;
insert into OWNER01.expdp_tables
select DISTINCT table_name
from dba_part_tables
where owner not in ('SYS','SYSTEM')
and (owner,table_name) not in (
  select owner,mview_name table_name from dba_mviews where owner not in ('SYS','SYSTEM')
)
; 

Create export parameter.

$ cat expdp_schema_TEST.par 
directory=EXP
userid="/ as sysdba"
metrics=Y
content=METADATA_ONLY
dumpfile=TEST.dmp
logfile=exp_TEST.log
schemas=OWNER01,OWNER02,OWNER03,OWNER04,OWNER05,OWNER06,OWNER07,OWNER08,OWNER09,OWNER10,OWNER11
INCLUDE=TABLE:"IN (SELECT table_name FROM OWNER01.expdp_tables)"

Perform export.

$ expdp parfile=expdp_schema_TEST.par 

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 September, 2015 14:04:34

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  parfile=expdp_schema_TEST.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 22 TABLE objects in 10 seconds
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 116 OBJECT_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 85 INDEX objects in 20 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 31 CONSTRAINT objects in 5 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 85 INDEX_STATISTICS objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 29 TRIGGER objects in 30 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 22 TABLE_STATISTICS objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
     Completed 1 POST_TABLE_ACTION objects in 0 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u02/oracle/exp/TEST.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:05:46

Create import parameter.

$ cat impdp_sqlfile_TEST.par 
directory=EXP
userid="/ as sysdba"
metrics=Y
exclude=STATISTICS
dumpfile=TEST.dmp
logfile=imp_TEST.log
sqlfile=create_TEST.sql

Perform import.

$ impdp parfile=impdp_sqlfile_TEST.par 

Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 September, 2015 14:07:36

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  parfile=impdp_sqlfile_TEST.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 22 TABLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 116 OBJECT_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 85 INDEX objects in 8 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 31 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 29 TRIGGER objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
     Completed 1 POST_TABLE_ACTION objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 14:07:48

BVT

$ grep -c "CREATE TABLE" /u02/oracle/exp/create_TEST.sql|sort
22

February 10, 2015

Goldengate – start replicat ATCSN or AFTERCSN?

Filed under: DataPump,GoldenGate,RMAN — mdinh @ 3:36 am

When using Goldengate to instantiate target database from an Oracle source database, replicat process can be started to coincide with extract based the method used for instantiation, e.g. RMAN or datapump.

ATCSN is used to start replicat if RMAN is used to instantiate target.
From Database Backup and Recovery Reference, UNTIL SCN specifies an SCN as an upper limit.
RMAN restore or recover up to but not including the specified SCN.

AFTERCSN is used to start replicat if datapump is used to instantiate target.
The export operation performed is consistent as of FLASHBACK_SCN.

Hope this helps to clear up when to use ATCSN versus AFTERCSN.

Referemce:
Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database –  Doc ID 1276058.1

March 5, 2014

Automating DataPump Export

Filed under: 11g,DataPump,oracle — mdinh @ 12:21 am

Please click link here to read my blog at Pythian.

August 17, 2013

Datapump REMAP_TABLE and Statistics Feature or Bug ???

Filed under: 11g,DataPump,oracle — mdinh @ 3:53 pm

Got a request from a friend:

One more thing , maybe for next post . I’ve noticed that during table_remap ie remap_table=gg.t:t1 there are no stats on t1 even if t has them and are included in dmp file . Bug or feature ?
Regards
Greg

What do you think? Is it a feature or a bug?

expdp_schema.par

directory=DATA_PUMP_DIR
userid='/ as sysdba'
metrics=Y
dumpfile=hr.dmp
tables=HR.EMPLOYEES
reuse_dumpfiles=Y

impdp_schema.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=hr.dmp
REMAP_SCHEMA=HR:DINH
REMAP_TABLE=EMPLOYEES:EMPS
TABLE_EXISTS_ACTION=REPLACE

expdp parfile=expdp_schema.par

Export: Release 11.2.0.3.0 - Production on Sat Aug 17 07:51:51 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA parfile=expdp_schema.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/COMMENT
     Completed 12 COMMENT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
     Completed 6 INDEX objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 6 INDEX_STATISTICS objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 3 REF_CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TRIGGER
     Completed 2 TRIGGER objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 1 TABLE_STATISTICS objects in 0 seconds
. . exported "HR"."EMPLOYEES"                            16.80 KB     107 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/pdb01/dpdump/hr.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 22:51:56

impdp parfile=impdp_schema.par

Import: Release 11.2.0.3.0 - Production on Sat Aug 17 07:53:02 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_schema.par
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DINH"."EMPS"                               16.80 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/COMMENT
     Completed 12 COMMENT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
     Completed 6 INDEX objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 6 INDEX_STATISTICS objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "DINH"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "DINH"."JOBS" ("JOB_ID") ENABLE
     Completed 3 REF_CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39083: Object type TRIGGER failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE TRIGGER update_job_history
  AFTER UPDATE OF job_id, department_id ON employees
  FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;
ORA-39083: Object type TRIGGER failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE TRIGGER secure_employees
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
     Completed 2 TRIGGER objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39083: Object type TABLE_STATISTICS failed to create with error:
ORA-20000: TABLE "DINH"."EMPLOYEES" does not exist or insufficient privileges
Failing sql is:
DECLARE   c varchar2(60);   nv varchar2(1);   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';   s varchar2(60) := 'DINH';   t varchar2(60) := 'EMPLOYEES';   p varchar2(1);   sp varchar2(1);   stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags,cl1) VALUES (:1,6,:2,:3
     Completed 1 TABLE_STATISTICS objects in 0 seconds
Job "SYS"."SYS_IMPORT_FULL_01" completed with 5 error(s) at 22:53:04

See the underlined error above? Looks like impdp is trying to import statistics to the original table.

Create table EMPLOYEES

SAN:(DINH@pdb01):PRIMARY> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMPS                           TABLE

SAN:(DINH@pdb01):PRIMARY> select count(*) from emps;

  COUNT(*)
----------
       107

SAN:(DINH@pdb01):PRIMARY> create table employees as select * from emps where 1=0;

Table created.

SAN:(DINH@pdb01):PRIMARY> exit

impdp parfile=impdp_schema.par

Import: Release 11.2.0.3.0 - Production on Sat Aug 17 07:54:05 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA parfile=impdp_schema.par
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 1 seconds
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DINH"."EMPS"                               16.80 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/COMMENT
     Completed 12 COMMENT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
     Completed 6 INDEX objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 4 CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 6 INDEX_STATISTICS objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "DINH"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "DINH"."EMPS" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "DINH"."JOBS" ("JOB_ID") ENABLE
     Completed 3 REF_CONSTRAINT objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"DINH"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"DINH"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"DINH"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"DINH"."SECURE_EMPLOYEES" created with compilation warnings
     Completed 2 TRIGGER objects in 0 seconds
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 1 TABLE_STATISTICS objects in 0 seconds
Job "SYS"."SYS_IMPORT_FULL_01" completed with 6 error(s) at 22:54:06

Statistics imported and where did it go?

SAN:(DINH@pdb01):PRIMARY> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMPLOYEES                      TABLE
EMPS                           TABLE

SAN:(DINH@pdb01):PRIMARY> select count(*) from emps;

  COUNT(*)
----------
       107

SAN:(DINH@pdb01):PRIMARY> select count(*) from employees;

  COUNT(*)
----------
         0

SAN:(DINH@pdb01):PRIMARY> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
EMPS
EMPLOYEES                             107

SAN:(DINH@pdb01):PRIMARY>

August 15, 2013

How to rebuild table when you don’t have TOAD

Filed under: 11g,DataPump,oracle — mdinh @ 5:24 am

I will demonstrate how to rebuild table DEPARTMENTS using datapump.

Find FK to DEPARTMENTS

select constraint_type, constraint_name ref_constraint, table_name fk_table from user_constraints
where r_constraint_name in (select constraint_name from user_constraints where table_name=’DEPARTMENTS’);

C REF_CONSTRAINT                 FK_TABLE
- ------------------------------ ------------------------------
R EMP_DEPT_FK                    EMPLOYEES
R JHIST_DEPT_FK                  JOB_HISTORY
Add DEPARTMENTS and FK_TABLE to expdp_metadata.par

$ cat expdp_metadata.par

directory=DATA_PUMP_DIR
userid='/ as sysdba'
metrics=Y
dumpfile=hr.dmp
content=METADATA_ONLY
schemas=HR
include=TABLE:"IN ('DEPARTMENTS','EMPLOYEES','JOB_HISTORY')"
reuse_dumpfiles=Y

Add FK_TABLE to impdp_rebuild_tab.par

$ cat impdp_rebuild_tab.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=hr.dmp
schemas=HR
exclude=TABLE:"IN ('EMPLOYEES','JOB_HISTORY')"
exclude=STATISTICS
sqlfile=rebuild_tab.sql

Since include and exclude cannot be used together, exclude is used to not import statistics.

Add REF_CONSTRAINT to impdp_rebuild_ref.par

$ cat impdp_rebuild_ref.par

directory=DATA_PUMP_DIR
userid="/ as sysdba"
metrics=Y
dumpfile=hr.dmp
schemas=HR
include=REF_CONSTRAINT:"IN ('EMP_DEPT_FK','JHIST_DEPT_FK')"
sqlfile=rebuild_ref.sql

Datapump it

$ cat rebuild.sh
expdp parfile=expdp_metadata.par
impdp parfile=impdp_rebuild_tab.par
impdp parfile=impdp_rebuild_ref.par

[oracle@lax:db01]/home/oracle
$ ./rebuild.sh

Export: Release 11.2.0.3.0 - Production on Wed Aug 14 21:23:56 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=expdp_metadata.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 3 TABLE objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
     Completed 23 COMMENT objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 12 INDEX objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 6 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 12 INDEX_STATISTICS objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 8 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 2 TRIGGER objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 3 TABLE_STATISTICS objects in 0 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/hr.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:24:03

Import: Release 11.2.0.3.0 - Production on Wed Aug 14 21:24:05 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01":  /******** AS SYSDBA parfile=impdp_rebuild_tab.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
     Completed 5 COMMENT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 2 INDEX objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 1 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 21:24:07

Import: Release 11.2.0.3.0 - Production on Wed Aug 14 21:24:08 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01":  /******** AS SYSDBA parfile=impdp_rebuild_ref.par
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 21:24:10

[oracle@lax:db01]/home/oracle
$
Edit and Run rebuild_tab.sql

Add: set echo on

Edit made to rebuild_tab.sql will be underlined in run output.

nohup sqlplus "/ as sysdba" @rebuild_tab.sql > rebuild_tab.log 2>&1 &

$ cat rebuild_tab.log

nohup: ignoring input

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 14 21:44:16 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.
LAX:(SYS@db01)> alter session set current_schema=hr;

Session altered.

LAX:(SYS@db01)> -- CONNECT SYS
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

Session altered.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/TABLE
LAX:(SYS@db01)> /*
LAX:(SYS@db01)> CREATE TABLE "HR"."DEPARTMENTS"
LAX:(SYS@db01)>    (    "DEPARTMENT_ID" NUMBER(4,0),
LAX:(SYS@db01)>         "DEPARTMENT_NAME" VARCHAR2(30 BYTE) CONSTRAINT "DEPT_NAME_NN" NOT NULL ENABLE,
LAX:(SYS@db01)>         "MANAGER_ID" NUMBER(6,0),
LAX:(SYS@db01)>         "LOCATION_ID" NUMBER(4,0),
LAX:(SYS@db01)>         "LAST_UPDATE" DATE DEFAULT sysdate NOT NULL ENABLE
LAX:(SYS@db01)>    ) SEGMENT CREATION IMMEDIATE
LAX:(SYS@db01)>   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
LAX:(SYS@db01)>  NOCOMPRESS LOGGING
LAX:(SYS@db01)>   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
LAX:(SYS@db01)>   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
LAX:(SYS@db01)>   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
LAX:(SYS@db01)>   TABLESPACE "USER_DATA" ;
LAX:(SYS@db01)> */
LAX:(SYS@db01)> -- MANUAL EDIT: drop and recreate table
LAX:(SYS@db01)> create table departments_old compress as select * from departments;

Table created.

LAX:(SYS@db01)> drop table departments cascade constraints purge;

Table dropped.

LAX:(SYS@db01)> create table departments as select * from departments_old;

Table created.

LAX:(SYS@db01)> -- MANUAL EDIT: add DEFAULT values
LAX:(SYS@db01)> alter table departments modify(last_update default sysdate);

Table altered.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/COMMENT
LAX:(SYS@db01)>  COMMENT ON COLUMN "HR"."DEPARTMENTS"."DEPARTMENT_ID" IS 'Primary key column of departments table.';

Comment created.

LAX:(SYS@db01)>  COMMENT ON COLUMN "HR"."DEPARTMENTS"."DEPARTMENT_NAME" IS 'A not null column that shows name of a department. Administration,
  2  Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
  3  Relations, Sales, Finance, and Accounting. ';

Comment created.

LAX:(SYS@db01)>  COMMENT ON COLUMN "HR"."DEPARTMENTS"."MANAGER_ID" IS 'Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.';

Comment created.

LAX:(SYS@db01)>  COMMENT ON COLUMN "HR"."DEPARTMENTS"."LOCATION_ID" IS 'Location id where a department is located. Foreign key to location_id column of locations table.';

Comment created.

LAX:(SYS@db01)>  COMMENT ON TABLE "HR"."DEPARTMENTS"  IS 'Departments table that shows details of departments where employees
  2  work. Contains 27 rows; references with locations, employees, and job_history tables.';

Comment created.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
LAX:(SYS@db01)> -- CONNECT HR
LAX:(SYS@db01)> CREATE UNIQUE INDEX "HR"."DEPT_ID_PK" ON "HR"."DEPARTMENTS" ("DEPARTMENT_ID")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "USER_DATA" PARALLEL 1 ;

Index created.

LAX:(SYS@db01)>
LAX:(SYS@db01)>   ALTER INDEX "HR"."DEPT_ID_PK" NOPARALLEL;

Index altered.

LAX:(SYS@db01)> CREATE INDEX "HR"."DEPT_LOCATION_IX" ON "HR"."DEPARTMENTS" ("LOCATION_ID")
  2    PCTFREE 10 INITRANS 2 MAXTRANS 255
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "USER_DATA" PARALLEL 1 ;

Index created.

LAX:(SYS@db01)>
LAX:(SYS@db01)>   ALTER INDEX "HR"."DEPT_LOCATION_IX" NOPARALLEL;

Index altered.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
LAX:(SYS@db01)> -- CONNECT SYS
LAX:(SYS@db01)> ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_ID_PK" PRIMARY KEY ("DEPARTMENT_ID")
  2    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  3    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  5    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  6    TABLESPACE "USER_DATA"  ENABLE;

Table altered.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
LAX:(SYS@db01)> ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID")
  2            REFERENCES "HR"."LOCATIONS" ("LOCATION_ID") ENABLE;

Table altered.

LAX:(SYS@db01)> ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID")
  2            REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") DISABLE;

Table altered.

LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$
Edit and Run rebuild_ref.sql

Add: set echo on

nohup sqlplus "/ as sysdba" @rebuild_ref.sql > rebuild_ref.log 2>&1 &

$ cat rebuild_ref.log

nohup: ignoring input

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 14 21:55:51 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

LAX:(SYS@db01)> -- CONNECT SYS
LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

LAX:(SYS@db01)> ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

Session altered.

LAX:(SYS@db01)> -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
LAX:(SYS@db01)> ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
  2            REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE;

Table altered.

LAX:(SYS@db01)> ALTER TABLE "HR"."JOB_HISTORY" ADD CONSTRAINT "JHIST_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
  2            REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE;

Table altered.

LAX:(SYS@db01)> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lax:db01]/home/oracle
$

Don’t forget to gather statistics.

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'DEPARTMENTS',method_opt=>'FOR ALL COLUMNS SIZE 1',estimate_percent=>dbms_stats.auto_sample_size,degree=>4);

September 30, 2012

Cross Platform Transportable Tablespace (XTTS) and Data Pump

Filed under: 11g,DataPump,oracle — mdinh @ 12:00 am

There’s a requirement for cross platform database migration from Solaris Operating System (SPARC) (64-bit) to Solaris Operating System (x86-64) and ideas started flying around.

CONVERT DATABASE cannot be used because of endian change.

Dataguard was considered and there are limitations as well.

Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration [ID 1085687.1]
Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]

Because of source and target, neither options were available.

Two other options were explored: Cross Platform Transportable Tablespace (XTTS) and Data Pump.

Here’s a brief overview for XTTS:

  1. Verify tablespace is self-contained.
  2. Set the tablespace to READ ONLY.
  3. Export metadata at source
  4. Endian Conversion – Convert at SOURCE: convert tablespace / Convert at TARGET: convert datafile
  5. Transfer datafiles and export dump file to target.
  6. Set the source tablespace to READ WRITE.
  7. Import metadata at target
  8. Set the imported tablespace to READ WRITE

For the test case, endian conversion was performed at source using convert tablespace because it’s much simpler with 3 tablespaces and 45 data files.

The platform name can be determined using the SQL:

SELECT tp.platform_id, tp.PLATFORM_NAME, ENDIAN_FORMAT,d.name
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME(+)
ORDER BY name, endian_format, platform_id;

Using RMAN for Conversion:

configure device type disk parallelism 6 backup type to backupset;
configure archivelog deletion policy to none;
convert tablespace tbs1,tbs2,tbs3
to platform="Solaris Operating System (x86-64)"
db_file_name_convert '/oracle/oradata/source','/oracle/oradata/target';

Caveat – TRANSPORT_TABLESPACES do not contain: db_link,function,package,procedure,sequence,synonym,view, to name a few.

Summary of results:

XTTS took 593 minutes and Data Pump took 663 minutes.

However, REF_CONSTRAINT took 337 minutes.

Is it necessary to enable REF_CONSTRAINT if the tablesspace is in READ ONLY or the export is consistent???

Without enabling REF_CONSTRAINT, the total Data Pump time is reduced to 326 minutes.

RMAN CONVERT tablespace total 704GB for all data files and total USED tablespace is 692GB.

Of interest, the total USED tablespace is 445GB when imported at target.

That’s a difference 0f 247GB !!!

Action

Mins

expdp schema parallel(18) 68
impdp table parallel(21) 68
index parallel(16) 159
constraint 15
stats parallel(24) 16
subtotal 326
ref_constraint 337
total 663
expdp_tts 183
dbf conversion parallel(6) 274
impdp_tts 136
total 593

References:

Export/Import DataPump: The Minimum Requirements to Use Export DataPump and Import DataPump (System Privileges) [ID 351598.1]
Best Practices for Using Transportable Tablespaces (TTS) [ID 1457876.1]
Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backups [ID 1389592.1]

Next Page »

Create a free website or blog at WordPress.com.