Thinking Out Loud

March 18, 2016

Migrate to 12c using Export/Import

Filed under: 12c,oracle,upgrade — mdinh @ 2:07 am

If you had success doing this, please share.

This took some troubleshooting and more than one pass.

Still not comfortable to guarantee target is identical to source.

Here are all the references, I have gathered if you want an adventure.

How to Perform a Full Database Export Import During Upgrade, Migrate, Copy or Move of a Database (Doc ID 286775.1)

Information On Installed Database Components and Schemas (Doc ID 472937.1)
Schemas CTXSYS MDSYS ORDSYS Are Not Exported (Doc ID 228482.1)

How to Install/Deinstall Oracle Workspace Manager (Doc ID 263428.1)
How to Determine if Workspace Manager is Being Used? (Doc ID 727765.1)

Data Pump: GRANTs On SYS Owned Objects Are Not Transferred With Data Pump And Are Missing In The Target Database (Doc ID 1911151.1)
After Full Import All Object Privileges Granted By SYS Are Lost (Doc ID 97902.1)

DataPump Export Fails With Error ORA-39095 (Doc ID 1071931.1)

++++++++++
How many components are installed?

*** dba_registry ***

ACTUAL SOURCE:
COMP_ID    COMP_NAME			       VERSION	       STATUS	    NAMESPACE	 SCHEMA
---------- ----------------------------------- --------------- ------------ ------------ ---------------
CATALOG    Oracle Database Catalog Views       10.2.0.4.0      VALID	    SERVER	 SYS
CATPROC    Oracle Database Packages and Types  10.2.0.4.0      VALID	    SERVER	 SYS
EM	   Oracle Enterprise Manager	       10.2.0.4.0      VALID	    SERVER	 SYSMAN
OWM	   Oracle Workspace Manager	       10.2.0.4.3      VALID	    SERVER	 WMSYS

ONE BAD SOURCE:
COMP_ID    COMP_NAME                           VERSION         STATUS       NAMESPACE    SCHEMA
---------- ----------------------------------- --------------- ------------ ------------ ---------------
APS        OLAP Analytic Workspace             11.2.0.4.0      VALID        SERVER       SYS
CATALOG    Oracle Database Catalog Views       11.2.0.4.0      VALID        SERVER       SYS
CATJAVA    Oracle Database Java Packages       11.2.0.4.0      VALID        SERVER       SYS
CATPROC    Oracle Database Packages and Types  11.2.0.4.0      VALID        SERVER       SYS
CONTEXT    Oracle Text                         11.2.0.4.0      VALID        SERVER       CTXSYS
EXF        Oracle Expression Filter            11.2.0.4.0      VALID        SERVER       EXFSYS
JAVAVM     JServer JAVA Virtual Machine        11.2.0.4.0      VALID        SERVER       SYS
ORDIM      Oracle Multimedia                   11.2.0.4.0      VALID        SERVER       ORDSYS
OWB        OWB                                 11.2.0.4.0      VALID        SERVER       OWBSYS
OWM        Oracle Workspace Manager            11.2.0.4.0      VALID        SERVER       WMSYS
RUL        Oracle Rules Manager                11.2.0.4.0      VALID        SERVER       EXFSYS
SDO        Spatial                             11.2.0.4.0      VALID        SERVER       MDSYS
XDB        Oracle XML Database                 11.2.0.4.0      VALID        SERVER       XDB
XML        Oracle XDK                          11.2.0.4.0      VALID        SERVER       SYS
XOQ        Oracle OLAP API                     11.2.0.4.0      VALID        SERVER       SYS

15 rows selected.

++++++++++
Export error using FLASHBACK_TIME

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
ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Took a some time but finally found a work around other than using FLASHBACK_SCN which is hokie.

expdp_full.par

USERID="/ as sysdba"
directory=DATA_PUMP_DIR
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
FLASHBACK_TIME="TO_TIMESTAMP(TO_CHAR(systimestamp,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS')"
EXCLUDE=statistics
exclude=schema:"IN ('WMSYS','SYSMAN')"
LOGFILE=exp_full.log

++++++++++
Create 12c non-PDB database.

whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
set echo on
shu abort;
create spfile from pfile;
startup force nomount;
CREATE DATABASE
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE 
GROUP 1 size 100M,GROUP 2 size 100M,GROUP 3 size 100M
EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 257M AUTOEXTEND ON NEXT 256M MAXSIZE 30721M
SYSAUX DATAFILE SIZE 257M AUTOEXTEND ON NEXT 256M MAXSIZE 30721M
DEFAULT TABLESPACE USERS DATAFILE SIZE 257M AUTOEXTEND ON NEXT 257M MAXSIZE 30721M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 30720M
UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 256M AUTOEXTEND ON NEXT 256M MAXSIZE 30720M
;
set echo off
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catoctk.sql;
@?/rdbms/admin/catio.sql
@?/rdbms/admin/dbmsiotc.sql
@?/rdbms/admin/dbmspool.sql
@?/rdbms/admin/userlock.sql
@?/rdbms/admin/utlrp.sql
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
connect system/oracle
@?/sqlplus/admin/pupbld.sql
exit

++++++++++
Check for errors.
grep '^ORA-' create_db_omf.tiger.out |cut -d ":" -f1|sort -u

ORA-00942
ORA-01432
ORA-01434
ORA-01921
ORA-02289
ORA-04043
ORA-29807

oerr ora 1432

01432, 00000, "public synonym to be dropped does not exist"
// *Cause:
// *Action:

grep '^ORA-' create_db_omf.tiger.out |cut -d ":" -f2|grep -v "not exist"|sort -u

 role name 'EXP_FULL_DATABASE' conflicts with another user or role
 role name 'IMP_FULL_DATABASE' conflicts with another user or role

++++++++++
Don’t use FILESIZE – I know, there are presentation showing usage of FILESIZE.

Example:
FILESIZE=8g
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

++++++++++
DATAFILE SIZE 2097152000 is larger than MAXSIZE 1048576000.

ORA-39083: Object type TABLESPACE:"USERS" failed to create with error:
ORA-02494: invalid or missing maximum file size in MAXSIZE clause
Failing sql is:
CREATE TABLESPACE "USERS" DATAFILE SIZE 2097152000 AUTOEXTEND ON NEXT 104857600 MAXSIZE 1048576000

Related error:

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE: failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'

++++++++++
SYS triggers need to be extracted from Source and created at Target.
Remember this one – OJVM?

SQL> CREATE OR REPLACE TRIGGER "SYS"."DBMS_JAVA_DEV_TRG" before create
2 on database
3 begin
4 if (ora_dict_obj_type='JAVA')
5 then
6 raise_application_error(-20031,'Java Development Disabled');
7 end if;
8 end;
9 /
Trigger created.

SQL> ALTER TRIGGER "SYS"."DBMS_JAVA_DEV_TRG" DISABLE;

Trigger altered.
SYS@SQL>

++++++++++
Tablespaces were pre-created since datafile locations changed.  Alternatively, use REMAP_DATAFILE.

set echo off head off verify off feedb off pages 0 long 10000 longchunk 10000 trimspool on lines 2000 timing off term off
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',TRUE);
define _filename=none
COL name NEW_VALUE name
select name from v$database;
define _filename='tbs_&name..sql'
spool &_filename
select dbms_metadata.get_ddl('TABLESPACE',TABLESPACE_NAME) from dba_tablespace
;
spool off
set term on
pro
pro *** Spool Output: &_filename
pro
pro
exit

++++++++++
Import done in 2 passes, impdp_full_user.par and impdp_full.par

impdp_full_user.par

directory=DATA_PUMP_DIR
USERID="/ as sysdba"
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
LOGFILE=imp_user.log
INCLUDE=user,role,default_role,tablespace_quota,role_grant,profile,password_verify_function,tablespace

Want to detect as many issues as possible before actual data import.
ORA-39083: Object type TABLESPACE:"USERS" failed to create with error

ORA-39083: Object type TABLE: failed to create with error:
ORA-01950: no privileges on tablespace 'USERS'

++++++++++
grep -v ORA-31684 imp_user.log | grep -A 1 Processing

Processing object type DATABASE_EXPORT/TABLESPACE
     Completed 26 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 7796 USER objects in 20 seconds
Processing object type DATABASE_EXPORT/ROLE
     Completed 321 ROLE objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
     Completed 21442 ROLE_GRANT objects in 129 seconds
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
     Completed 7796 DEFAULT_ROLE objects in 10 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
     Completed 7790 TABLESPACE_QUOTA objects in 24 seconds

++++++++++
impdp_full.par

directory=DATA_PUMP_DIR
USERID="/ as sysdba"
METRICS=Y
PARALLEL=4
FULL=Y
DUMPFILE=full%U.dmp
LOGFILE=imp_full.log
EXCLUDE=user,role,default_role,tablespace_quota,role_grant,profile,password_verify_function,tablespace
STREAMS_CONFIGURATION=NO

++++++++++
grep -v ORA-31684 imp_full.log | grep -A 1 Processing

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
     Completed 6 PROC_SYSTEM_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
     Completed 145 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/TRUSTED_DB_LINK
     Completed 1 TRUSTED_DB_LINK objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
     Completed 197 SEQUENCE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 205 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
     Completed 7 DIRECTORY objects in 0 seconds
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 12 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/CONTEXT
     Completed 7 CONTEXT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
     Completed 1139 SYNONYM objects in 2 seconds
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
     Completed 1014 SYNONYM objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
     Completed 180 TYPE objects in 3 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
--
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
     Completed 3 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
     Completed 1302 TABLE objects in 18 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Startup took 356 seconds
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 68094 OBJECT_GRANT objects in 91 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
     Completed 4 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
     Completed 238 CONSTRAINT objects in 86 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
     Completed 696 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/AUDIT_OBJ
     Completed 1 AUDIT_OBJ objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/RLS_POLICY
     Completed 68 RLS_POLICY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
     Completed 148 PACKAGE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 195 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
     Completed 42 FUNCTION objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 52 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
     Completed 16 PROCEDURE objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 2 OBJECT_GRANT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
     Completed 42 ALTER_FUNCTION objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
     Completed 16 ALTER_PROCEDURE objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
     Completed 72 VIEW objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 155 OBJECT_GRANT objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
     Completed 62 COMMENT objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/RLS_POLICY
     Completed 4 RLS_POLICY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
     Completed 148 PACKAGE_BODY objects in 4 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
     Completed 16 TYPE_BODY objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
ORA-39083: Object type INDEX: failed to create with error:
--
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
     Completed 68 TRIGGER objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
     Completed 3 TRIGGER objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
     Completed 1 MATERIALIZED_VIEW objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/JOB
     Completed 5 JOB objects in 1 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
     Completed 3 PROCOBJ objects in 0 seconds
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
--
Processing object type DATABASE_EXPORT/AUDIT
     Completed 5 AUDIT objects in 0 seconds
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: