Search My Oracle Blog

Custom Search

15 January, 2017

Copying a Tablespace from NonCDB to a PDB (using TTS)

A Tablespace can be "transported"  from a NonCDB to a PDB as a way of copying the Tablespace.  Here I work with ASM as well.

First in the NonCDB :

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [oracle] ? NONCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:03:43 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  402653184 bytes
Fixed Size      2924928 bytes
Variable Size    260050560 bytes
Database Buffers   134217728 bytes
Redo Buffers      5459968 bytes
Database mounted.
Database opened.
SQL> select file_name, bytes/1048576                    
  2  from dba_data_files
  3  where tablespace_name = 'EXAMPLE'
  4  /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
+DATA/NONCDB/DATAFILE/example.266.896482777
      1243.75


SQL> 
[oracle@ora12102 Desktop]$ expdp hemant/hemant \
> directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp \
> transport_tablespaces=EXAMPLE transport_full_check=Y

Export: Release 12.1.0.2.0 - Production on Sun Jan 15 16:08:27 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01":  hemant/******** directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp transport_tablespaces=EXAMPLE transport_full_check=Y 
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'EXAMPLE' is not read only
Job "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at Sun Jan 15 16:08:41 2017 elapsed 0 00:00:06

[oracle@ora12102 Desktop]$ 


The tablespace has to be set READ ONLY before we can use export to transport it (also, it should be READ ONLY while the data files are being copied.

[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:09:10 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter tablespace example read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ expdp hemant/hemant \
> directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp \
> transport_tablespaces=EXAMPLE transport_full_check=Y

Export: Release 12.1.0.2.0 - Production on Sun Jan 15 16:09:58 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01":  hemant/******** directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp transport_tablespaces=EXAMPLE transport_full_check=Y 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HEMANT.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/NONCDB/dpdump/EXAMPLE_TTS.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
  +DATA/NONCDB/DATAFILE/example.266.896482777
Job "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Jan 15 16:12:48 2017 elapsed 0 00:02:46

[oracle@ora12102 Desktop]$ 


Now, I need to copy the datafile (while the tablespace is READ ONLY).

[oracle@ora12102 Desktop]$ su - grid
Password: 
[grid@ora12102 ~]$ asmcmd
ASMCMD> cp +DATA/NONCDB/DATAFILE/example.266.896482777 /tmp/example.dbf
copying +DATA/NONCDB/DATAFILE/example.266.896482777 -> /tmp/example.dbf
ASMCMD> 
ASMCMD> exit
[grid@ora12102 ~]$ exit
logout
[oracle@ora12102 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:16:24 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter tablespace example read write ;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ 


I must now identify the target location for the datafile in the CDB database.

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [NONCDB] ? CDB1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:17:44 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size      2925024 bytes
Variable Size    973082144 bytes
Database Buffers   654311424 bytes
Redo Buffers     13848576 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>        
SQL> alter session set container=PDB1;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/system.284.914408541
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/sysaux.285.914408541
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/users.287.914408663
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/hemant.288.914713623

SQL> 


Now that I have identiied the default location for all PDB1 files, I need to use ASMCMD to copy the datafile.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ su - grid
Password: 
[grid@ora12102 ~]$ 
[grid@ora12102 ~]$ asmcmd
ASMCMD> cp /tmp/example.dbf +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/
copying /tmp/example.dbf -> +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf
ASMCMD> cd +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE
ASMCMD> ls
HEMANT.288.914713623
SYSAUX.285.914408541
SYSTEM.284.914408541
USERS.287.914408663
example.dbf
ASMCMD> exit
[grid@ora12102 ~]$ 


Now, I need to import the tablespace with the datafile. Before that, I need to setup the user that will do the import and all the users of the target tablespace.

[grid@ora12102 ~]$ exit
logout
[oracle@ora12102 Desktop]$ 
[oracle@ora12102 Desktop]$ sqlplus system/oracle@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:28:16 2017

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

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sun Jan 15 2017 16:27:18 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant select_catalog_role, imp_full_database to hemant;

Grant succeeded.

SQL> select privilege from dba_sys_privs where grantee = 'HEMANT';

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SESSION

SQL> select granted_role from dba_role_privs where grantee = 'HEMANT';

GRANTED_ROLE
--------------------------------------------------------------------------------
SELECT_CATALOG_ROLE
IMP_FULL_DATABASE

SQL> 


As with the Export, I am using a non-DBA user for the import.  I also have to setup the users and their grants.

[oracle@ora12102 Desktop]$ sqlplus sys/oracle@PDB1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:33:17 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create directory imp_from_noncdb as '/u01/app/oracle/admin/NONCDB/dpdump';

Directory created.

SQL> grant read, write on directory imp_from_noncdb to hemant;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ 
[oracle@ora12102 Desktop]$ sqlplus system/oracle@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:41:36 2017

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

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sun Jan 15 2017 16:28:16 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create user HR identified by  HR ;
create user IX identified by  IX ;
create user OE identified by  OE ;
create user PM identified by  PM ;
create user SH identified by  SH ;

User created.

SQL> 
User created.

SQL> 
User created.

SQL> 
User created.

SQL> 
User created.

SQL> SQL> 
SQL> @grants_to_EXAMPLE
SQL> spool grants_to_EXAMPLE
SQL> 
SQL> grant  ALTER SESSION  to  HR ;

Grant succeeded.

SQL> grant  ALTER SESSION  to  IX ;

Grant succeeded.

SQL> grant  ALTER SESSION  to  SH ;

Grant succeeded.

SQL> grant  CREATE CLUSTER  to IX ;

Grant succeeded.

SQL> grant  CREATE CLUSTER  to SH ;

Grant succeeded.

SQL> grant  CREATE DATABASE LINK  to  HR ;

Grant succeeded.

SQL> grant  CREATE DATABASE LINK  to  IX ;

Grant succeeded.

SQL> grant  CREATE DATABASE LINK  to  OE ;

Grant succeeded.

SQL> grant  CREATE DATABASE LINK  to  SH ;

Grant succeeded.

SQL> grant  CREATE DIMENSION  to  SH ;

Grant succeeded.

SQL> grant  CREATE INDEXTYPE  to  IX ;

Grant succeeded.

SQL> grant  CREATE MATERIALIZED VIEW  to  OE ;

Grant succeeded.

SQL> grant  CREATE MATERIALIZED VIEW  to  SH ;

Grant succeeded.

SQL> grant  CREATE OPERATOR to  IX ;

Grant succeeded.

SQL> grant  CREATE PROCEDURE  to  HR ;

Grant succeeded.

SQL> grant  CREATE PROCEDURE  to  IX ;

Grant succeeded.

SQL> grant  CREATE RULE  to IX ;

Grant succeeded.

SQL> grant  CREATE RULE SET to  IX ;

Grant succeeded.

SQL> grant  CREATE SEQUENCE to  HR ;

Grant succeeded.

SQL> grant  CREATE SEQUENCE to  IX ;

Grant succeeded.

SQL> grant  CREATE SEQUENCE to  SH ;

Grant succeeded.

SQL> grant  CREATE SESSION  to HR ;

Grant succeeded.

SQL> grant  CREATE SESSION  to IX ;

Grant succeeded.

SQL> grant  CREATE SESSION  to OE ;

Grant succeeded.

SQL> grant  CREATE SESSION  to SH ;

Grant succeeded.

SQL> grant  CREATE SYNONYM  to HR ;

Grant succeeded.

SQL> grant  CREATE SYNONYM  to IX ;

Grant succeeded.

SQL> grant  CREATE SYNONYM  to OE ;

Grant succeeded.

SQL> grant  CREATE SYNONYM  to SH ;

Grant succeeded.

SQL> grant  CREATE TABLE  to  IX ;

Grant succeeded.

SQL> grant  CREATE TABLE  to  SH ;

Grant succeeded.

SQL> grant  CREATE TRIGGER  to IX ;

Grant succeeded.

SQL> grant  CREATE TYPE  to IX ;

Grant succeeded.

SQL> grant  CREATE VIEW  to HR ;

Grant succeeded.

SQL> grant  CREATE VIEW  to IX ;

Grant succeeded.

SQL> grant  CREATE VIEW  to OE ;

Grant succeeded.

SQL> grant  CREATE VIEW  to SH ;

Grant succeeded.

SQL> grant  QUERY REWRITE  to  OE ;

Grant succeeded.

SQL> grant  QUERY REWRITE  to  SH ;

Grant succeeded.

SQL> grant  SELECT ANY DICTIONARY  to  IX ;

Grant succeeded.

SQL> grant  UNLIMITED TABLESPACE  to  HR ;

Grant succeeded.

SQL> grant  UNLIMITED TABLESPACE  to  IX ;

Grant succeeded.

SQL> grant  UNLIMITED TABLESPACE  to  OE ;

Grant succeeded.

SQL> grant  UNLIMITED TABLESPACE  to  PM ;

Grant succeeded.

SQL> grant  UNLIMITED TABLESPACE  to  SH ;

Grant succeeded.

SQL> 
SQL> spool off
SQL> @roles_to_EXAMPLE
SQL> set echo on
SQL> spool roles_to_EXAMPLE
SQL> 
SQL> grant  AQ_ADMINISTRATOR_ROLE  to  IX ;

Grant succeeded.

SQL> grant  AQ_USER_ROLE  to  IX ;

Grant succeeded.

SQL> grant  CONNECT to  IX ;

Grant succeeded.

SQL> grant  CONNECT to  PM ;

Grant succeeded.

SQL> grant  RESOURCE  to  HR ;

Grant succeeded.

SQL> grant  RESOURCE  to  IX ;

Grant succeeded.

SQL> grant  RESOURCE  to  OE ;

Grant succeeded.

SQL> grant  RESOURCE  to  PM ;

Grant succeeded.

SQL> grant  RESOURCE  to  SH ;

Grant succeeded.

SQL> grant  SELECT_CATALOG_ROLE  to IX ;

Grant succeeded.

SQL> grant  SELECT_CATALOG_ROLE  to SH ;

Grant succeeded.

SQL> grant  XDBADMIN  to  OE ;

Grant succeeded.

SQL> 
SQL> spool off
SQL> 


I am now ready to import the tablespace and datafile.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ impdp hemant/hemant@PDB1 \
> dumpfile=EXAMPLE_TTS.dmp directory=imp_from_noncdb \
> transport_datafiles=+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf

Import: Release 12.1.0.2.0 - Production on Sun Jan 15 16:50:16 2017

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

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone is +00:00 and target time zone is -07:00.
Starting "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01":  hemant/********@PDB1 dumpfile=EXAMPLE_TTS.dmp directory=imp_from_noncdb transport_datafiles=+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39360: Table "OE"."ORDERS" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
ORA-39151: Table "OE"."PURCHASEORDER" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PROMOTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PRODUCT_DESCRIPTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PRODUCT_INFORMATION" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."INVENTORIES" TO "BI"
ORA-39112: Dependent object type OBJECT_GRANT:"OE" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"OE" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."ORDER_ITEMS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."WAREHOUSES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."CUSTOMERS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."COSTS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."SALES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."COUNTRIES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CUSTOMERS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."PROMOTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CHANNELS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."PRODUCTS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."TIMES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."FWEEK_PSCAT_SALES_MV" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CAL_MONTH_SALES_MV" TO "BI"
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"OE"."ORD_SALES_REP_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORD_ORDER_DATE_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORD_CUSTOMER_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORDER_PK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_MODE_LOV" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_TOTAL_MIN" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_PK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"OE"."ORDER_ITEMS_ORDER_ID_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "OE"."ORDER_ITEMS" ADD CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID") REFERENCES "OE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE
ORA-39112: Dependent object type REF_CONSTRAINT:"OE"."ORDERS_SALES_REP_FK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"OE"."ORDERS_CUSTOMER_ID_FK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
Job "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01" completed with 41 error(s) at Sun Jan 15 16:51:40 2017 elapsed 0 00:01:23

[oracle@ora12102 Desktop]$ 


The key error is the failure on the ORDERS table creation because of a TimeZone mismatch !   So, there is a lesson to be learnt !

.
.
.

1 comment:

oracle said...

Nice blog and a very good explanation about the topic, Good points were stated in the blog.

thanks for sharing

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com