DataPump CMD - Database Level & Full=y

Goal	Changing DB Blocksize using Datapump FULL DB mode
Version	10gR2
Executable	expdp impdp
Log as	SYS SYSTEM

Datapump helps copying the old DB content into the newly created DB.

Tablespaces are created separately from Datapump ( with new Blocksize ).

We’re using 2 exports commands in order to

-       separate the biggest schema from the others

-       or if we need to set different Date format when importing ( DATE DEFAULT ).

FULL Mode Restrictions

·         The following system schemas are not exported as part of a Full : SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP, and WMSYS.

·         Grants on objects owned by the SYS schema are never exported.

Those Grants are taken manually , see 1.1.

1] Expdp FULL=y [ Source DB ]

1.1 Pre-requisite

1.1.1 to be done prior to IMPDP

• Create DP Directory, connected as SYSTEM

CREATE OR REPLACE DIRECTORY DP_DIR AS '<your_path>';

• Change Users missing DEFAULT Tablespace

select 'alter user '||username||' default tablespace USERS ; ' from dba_users a

where not exists ( select '1' from  dba_tablespaces b where a.default_tablespace = b.tablespace_name ) order by 1 ;

• Generate Tablespaces creation sql to be executed on target, 2.1

select 'create tablespace '||tablespace_name||' DATAFILE ''/<your_path>/'||lower(tablespace_name)||'.dbf'' SIZE 16M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED ;'

from dba_tablespaces where tablespace_name not in ('SYSTEM','UNDO','SYSAUX','TEMP','USERS','XDB') order by 1

1.1.2 to be done after IMPDP

• Take care manually of Grants on objects owned by SYS : it will create a sql script to be run on target DB, 2.3.2

select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||' ; '

from dba_tab_privs where owner in ('SYS','ORDSYS','EXFSYS','MDSYS','DMSYS','CTXSYS','ORDPLUGINS','LBACSYS','XDB','SI_INFORMTN_SCHEMA','DIP','DBSNMP','WMSYS') and not exists ( select '1' from dba_directories where table_name = directory_name )

and grantee not in (

'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'CTXSYS', 'DBA', 'DBSNMP', 'DELETE_CATALOG_ROLE', 'EXECUTE_CATALOG_ROLE', 'EXFSYS', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'HS_ADMIN_ROLE', 'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'MDSYS', 'OEM_MONITOR', 'ORACLE_OCM', 'ORDSYS', 'OUTLN', 'PUBLIC', 'SELECT_CATALOG_ROLE', 'SYSTEM', 'WMSYS', 'XDB', 'XDBADMIN' )

union

select 'grant '||privilege||' on directory '||owner||'.'||table_name||' to '||grantee||' ; '

from dba_tab_privs where owner = 'SYS' and exists ( select '1' from dba_directories where table_name = directory_name )

and grantee not in (

'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE', 'CTXSYS', 'DBA', 'DBSNMP', 'DELETE_CATALOG_ROLE', 'EXECUTE_CATALOG_ROLE', 'EXFSYS', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS', 'HS_ADMIN_ROLE', 'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'MDSYS', 'OEM_MONITOR', 'ORACLE_OCM', 'ORDSYS', 'OUTLN', 'PUBLIC', 'SELECT_CATALOG_ROLE', 'SYSTEM', 'WMSYS', 'XDB', 'XDBADMIN' )

order by 1 ;

1.2 Expdp

In <BASE> environment.

It’s wise to enable « restricted mode » before exporting : startup restrict

$ time expdp system FULL=y DUMPFILE=DP_DIR:<BASE>_1.dmp PARALLEL=2 LOGFILE=DP_DIR:e_<BASE>_1.log EXCLUDE=SCHEMA:"in\('RH'\)"

$ time expdp system FULL=y DUMPFILE=DP_DIR:<BASE>_2.dmp PARALLEL=2 LOGFILE=DP_DIR:e_<BASE>_2.log INCLUDE=SCHEMA:"in\('RH'\)"

Reminder : to get cross-tables Consistency - when not in restricted mode, it is important to set

flashback_time="to_timestamp(to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')"

even

-       If it is not well stated in Oracle documentation

-       the confusing message « FLASHBACK automatically enabled to preserve database integrity. » sometimes appears

see http://www.myoraclesupports.com/content/expdp-message-flashback-automatically-enabled-does-not-guarantee-export-consistency

2] Impdp [ Target DB ]

2.1 Pre-requisite

• Create DP Directory
• Create target tablespaces if needed ( in our example : DB Blocksize is changing )

2.2 Impdp

• Transfer dump files, then Import

$ time impdp system DUMPFILE=DP_DIR:<BASE>_1.dmp PARALLEL=4 LOGFILE=DP_DIR:i_<BASE>_1.log

export NLS_DATE_FORMAT='YYYY-MM-DD'

$ time impdp system DUMPFILE=DP_DIR:<BASE>_2.dmp PARALLEL=4 LOGFILE=DP_DIR:i_<BASE>_2.log

$ grep -i ora- i_<BASE>_*.log | egrep -v 'exist|ORA-29339|ORA-39083|ORA-39082' | more

2.3 Grants rights on SYS objects

2.3.1 APEX 4.0 pre-requisites to create WWV_* packages

Untar APEX binaries ; then connect as SYS, under « apex/core » directory :

SQL> @wwv_dbms_sql.sql

...wwv_dbms_sql

Package created.

No errors.

SQL> @wwv_dbms_sql.plb

...wwv_dbms_sql

Package body created.

No errors.

SQL> @wwv_flow_val.sql

Package created.

No errors.

Library created.

Grant succeeded.

SQL> @wwv_flow_create_key_package.plb

...wwv_flow_create_key_package

Procedure created.

No errors.

SQL> exec wwv_flow_create_key_package

PL/SQL procedure successfully completed.

SQL> drop procedure sys.wwv_flow_create_key_package

  2  /

Procedure dropped.

2.3.2 Run scripts created in 1.1 to grants rights on SYS objects

2.4 Recompile all objects & Gather DB stats

Connected as SYS, parallel mode

execute utl_recomp.recomp_parallel(4);

exec dbms_stats.gather_database_stats(estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, block_sample=>FALSE, method_opt=>'FOR ALL COLUMNS SIZE AUTO', degree=>4, granularity=>'ALL', cascade=>TRUE,options=>'GATHER', gather_sys=>TRUE) ;

2.5 Check for INVALID objects

To be compared to Source DB :

select * from dba_objects where status != 'VALID' order by 1,2

And that’s it.

2.6 Oracle Services

… Last Minute : take care of Oracle Services Management – see ** Services Startup post **

Create and start source Oracle Services.

$ grep service_ /data/<base>/spfile<BASE>.ora

*.service_names='SVC1,…SVCN'