Thinking Out Loud

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';
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: