SOURCE DATABASE ON WINDOWS
STEP 1 check compatible paramter value
SQL> show parameter compat
NAME TYPE VALUE
------------------------------------ ----------- -----------
compatible string 10.2.0.1.0
STEP 2 check Database Character Set value (this must same on target DB)
SQL> select * from database_properties where property_name like '%SET%';
PROPERTY_NAME PROPERTY_VALUE
----------------------------------------------
NLS_CHARACTERSET US7ASCII
NLS_NCHAR_CHARACTERSET AL16UTF16
STEP 3 check current Database platform value
SQL> select platform_name from v$database;
PLATFORM_NAME
--------------------------------------
Microsoft Windows IA (32-bit)
STEP 4 check platform value and ENDIAN_FORMAT value
SQL> column platform_name format A32
SQL> select * from v$transportable_platform where platform_name='Microsoft Windo
ws IA (32-bit)';
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------- --------------
7 Microsoft Windows IA (32-bit) Little
Step 5 Check referential integrity constraints taken into consideration
The following statement can be used to determine whether tablespaces USERS and EXAMPLE
are self-contained, with referential integrity constraints taken into consideration
(indicated by TRUE).
SQL> exec dbms_tts.TRANSPORT_SET_CHECK('USERS,EXAMPLE',TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
After invoking this PL/SQL package, you can see all violations by selecting from the
TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this
view is empty.
Step 6 Make required tablespaces in read-only.
SQL> ALTER TABLESPACE users READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE example READ ONLY;
Tablespace altered.
TEST STEP (I do this only for understanding, if you dont put tablespace in readonly you will get the error)
C:\>expdp system/oracle TRANSPORT_TABLESPACES=users,example DUMPFILE=DATA_PUMP_DIR:user_example.DMP LOGFILE=DATA_PUMP_DIR:user_example.LOG
Export: Release 10.2.0.1.0 - Production on Sunday, 18 September, 2011 1:27:15
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise
tion
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** TRANSPORT_TABL
ESPACES=users,example DUMPFILE=DATA_PUMP_DIR:user_example.DMP LOGFILE=DATA_PUMP_
DIR:user_example.LOG
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29335: tablespace 'EXAMPLE' is not read only
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 01:27:2
5
sqlplus / as sysdba
SQL> alter tablespace example read only;
Step 7 Invoke the Data Pump export utility and specify which tablespaces
are in the transportable set.
Note:
If any of the tablespaces have XMLTypes, you must use EXP instead of Data Pump. Ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).
C:\>expdp system/oracle TRANSPORT_TABLESPACES=users,example DUMPFILE=DATA_PUMP_DIR:user_example.DMP LOGFILE=DATA_PUMP_DIR:user_example.LOG
Export: Release 10.2.0.1.0 - Production on Sunday, 18 September, 2011 1:27:52
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise
tion
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** TRANSPORT_TABL
ESPACES=users,example DUMPFILE=DATA_PUMP_DIR:user_example.DMP LOGFILE=DATA_PUMP_
DIR:user_example.LOG
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITM
AP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
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 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL1\DPDUMP\USER_EXAMPLE.DMP
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:29:17
Step 8 copy the datafile belongs to tablespace to another location;
C:\>e:
E:\oracle\product\10.2.0\oradata\orcl1>copy EXAMPLE01.DBF c:\example01.dbf
1 file(s) copied.
E:\oracle\product\10.2.0\oradata\orcl1>copy users01.DBF c:\users01.dbf
1 file(s) copied.
Step 9 make tablespace back to read write mode
SQL> alter tablespace example read write;
Tablespace altered.
SQL> alter tablespace users read write;
Tablespace altered.
All step done here on source system. Now move datafiles and export dmp to target system
TARGET DATABASE
SQL> show parameter compat
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0
plsql_v2_compatibility boolean FALSE
SQL> alter system set compatible='10.2.0.1.0' scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 1218892 bytes
Variable Size 100665012 bytes
Database Buffers 159383552 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select * from database_properties where property_name like '%SET%';
PROPERTY_NAME PROPERTY_VALUE
-------------------------------------------------------------------------------
NLS_CHARACTERSET US7ASCII
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL> select platform_name from v$database;
PLATFORM_NAME
--------------------------------
SQL> column platform_name format A32
Note: IF ENDIAN_FORMAT is not same as source database then you need to convert the datafile by RMAN convert command. Other wise you just copy your files to target database.
SQL> select * from v$transportable_platform where platform_name like '%Linux IA
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------- --------------
10 Linux IA (32-bit) Little
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS
SYSAUX
TEMP
ZAMANTS
TEMP2
Create Desire user that have objects in both tablespace
create user scott identified by tiger ;
create user TSMSYS identified by tiger ;
create user BI identified by tiger ;
create user PM identified by tiger ;
create user MDDATA identified by tiger ;
create user IX identified by tiger ;
create user SH identified by tiger ;
create user DIP identified by tiger ;
create user HR identified by tiger ;
SQL> grant connect, resource to scott,tsmsys,bi,pm,mddata,IX,SH,DIP,OE,HR;
Grant succeeded.
Now check the datapump directory
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DATA_PUMP_DIR
/u01/oracle/product/10.2.0/db_1/rdbms/log/
SQL> create or replace directory data_pump_dir as '/u01/oracle/admin/orcl/dpdump';
Directory created.
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DATA_PUMP_DIR
/u01/oracle/admin/orcl/dpdump
SQL> grant read on directory data_pump_dir to system;
Grant succeeded.
IMPORT DP COMAND
[oracle@ocs orcl]$ impdp system/oracle directory=data_pump_dir dumpfile=USER_EXAMPLE.DMP transport_datafiles=/u04/orcl/USERS01.DBF,/u04/orcl/example01.dbf
Import: Release 10.2.0.1.0 - Production on Sunday, 18 September, 2011 2:49:48
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=USER_EXAMPLE.DMP transport_datafiles=/u04/orcl/USERS01.DBF,/u04/orcl/example01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39083: Object type TABLE:"PM"."ONLINE_MEDIA" failed to create with error:
ORA-39218: type check on object type "ORDSYS"."ORDIMAGE" failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 1915
ORA-01403: no data found
Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('ORDSYS','ORDIMAGE','1','21D055AB9B0A9AB4B1B44B1BE854B870BB',''); SYS.DBMS_METADATA.CHECK_TYPE('ORDSYS','ORDIMAGESIGNATURE','1','21434CBB7CFC39C0B4850F15D8A163B529',''); SYS.DBMS_METADATA.CHECK_TYPE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
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
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
ORA-06550: line 8, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored
ORA-06550: line 10, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored
ORA-06550: line 12, column 1:
PLS
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
ORA-31685: Object type MATERIALIZED_VIEW:"SH"."CAL_MONTH_SALES_MV" failed due to insufficient privileges. Failing sql is:
CREATE MATERIALIZED VIEW "SH"."CAL_MONTH_SALES_MV" USING ("CAL_MONTH_SALES_MV", (8, 'ORCL1.REGRESS.RDBMS.DEV.US.ORACLE.COM', 2, 0, 0, "SH", "TIMES", '2011-09-17 23:21:50', 8, 52100, '2011-09-17 23:21:51', '', 1, '0208', 561986, 0, NULL, 1, "SH", "SALES", '2011-09-17 23:21:50', 33032, 52042, '2011-09-17 23:21:51', '', 1, '88', 561986, 0, NULL), 1183809, 8, ('1950-01-01 12:00:00', 1, 0,
ORA-31685: Object type MATERIALIZED_VIEW:"SH"."FWEEK_PSCAT_SALES_MV" failed due to insufficient privileges. Failing sql is:
CREATE MATERIALIZED VIEW "SH"."FWEEK_PSCAT_SALES_MV" USING ("FWEEK_PSCAT_SALES_MV", (8, 'ORCL1.REGRESS.RDBMS.DEV.US.ORACLE.COM', 3, 0, 0, "SH", "PRODUCTS", '2011-09-17 23:21:51', 8, 52102, '2011-09-17 23:21:52', '', 1, '12', 562005, 0, NULL, 1, "SH", "TIMES", '2011-09-17 23:21:51', 8, 52100, '2011-09-17 23:21:52', '', 1, '82', 562005, 0, NULL, 2, "SH", "SALES", '2011-09-17 23:21:51',
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
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 8 error(s) at 02:51:26
DATA VERIFICATION
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS
SYSAUX
TEMP
ZAMANTS
TEMP2
EXAMPLE
USERS
8 rows selected.
SQL> select count(*) from hr.employees;
COUNT(*)
----------
107
No comments:
Post a Comment