Scenario Preview:
We will do cross platform migration of tablespace using impdp from linux OS to window OS.
There are 2 type of endian format Big and little Below list we can find out endian format using table view V$TRANSPORTABLE_PLATFORM;
Database version : 12.2.0.1
Endian format : Little
Cross platform migration : Linux to Window operating system
Source Database : Linux (OEMDB database )
Destination Database : Window (ORCL database)
KEYWORDS:
View Name : V$TRANSPORTABLE_PLATFORM
Package : exec DBMS_TTS.TRANSPORT_SET_CHECK('MGMT_TABLESPACE', TRUE,TRUE);
Table Name : TRANSPORT_SET_VIOLATIONS
EXPDP: transport_tablespaces=MGMT_TABLESPACE,MGMT_ECM_DEPOT_TS,MGMT_AD4J_TS
RMAN:CONVERT DATAFILE
IMPDP : transport_datafiles
- Check tablespace name from sysman user
SQL> select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE from dba_users where username='SYSMAN';
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE
---------------------------------------- -------------------------------- ------------------------------
SYSMAN OPEN MGMT_TABLESPACE
- We are using transport tablespace from linux to window Endian platform from V$TRANSPORTABLE_PLATFORM
SQL> SELECT PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM WHERE UPPER(PLATFORM_NAME) LIKE '%LINUX%X86%64-BIT%' OR UPPER(PLATFORM_NAME) LIKE '% WINDOWS%X86%64-BIT%';
PLATFORM_NAME ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit Little
Microsoft Windows x86 64-bit Little
- Endian Platform check on destination window database
SQL> select name,open_mode,database_Role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ WRITE PRIMARY
SQL> SELECT PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM WHERE UPPER(PLATFORM_NAME) LIKE '%LINUX%X86%64-BIT%' OR UPPER(PLATFORM_NAME) LIKE '% WINDOWS%X86%64-BIT%';
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Linux x86 64-bit Little
Microsoft Windows x86 64-bit Little
- Check the tablespace transported is self contained Check violations it should return no rows. check user list of violations. Below examples we can see some multiple violations. Fix it before proceed.
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('MGMT_TABLESPACE', TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39907: Index SYSMAN.SYS_IL0000076482C00005$$ in tablespace MGMT_ECM_DEPOT_TS points to table SYSMAN.MGMT_URL_CACHE_E in tablespace MGMT_TABLESPACE.
ORA-39905: Table SYSMAN.MGMT_URL_CACHE_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB0000076482C00005$$ in tablespace MGMT_ECM_DEPOT_TS.
ORA-39907: Index SYSMAN.SYS_IL0000076619C00011$$ in tablespace MGMT_ECM_DEPOT_TS points to table SYSMAN.MGMT_BAM_ISESSION_DATASOURCE_E in tablespace MGMT_TABLESPACE.
ORA-39905: Table SYSMAN.MGMT_BAM_ISESSION_DATASOURCE_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB0000076619C00011$$ in tablespace MGMT_ECM_DEPOT_TS.
ORA-39907: Index SYSMAN.SYS_IL0000076635C00003$$ in tablespace MGMT_ECM_DEPOT_TS points to table SYSMAN.MGMT_LONG_TEXT_E in tablespace MGMT_TABLESPACE.
ORA-39905: Table SYSMAN.MGMT_LONG_TEXT_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB0000076635C00003$$ in tablespace MGMT_ECM_DEPOT_TS.
ORA-39907: Index SYSMAN.SYS_IL0000077198C00017$$ in tablespace MGMT_ECM_DEPOT_TS points to table SYSMAN.MGMT_ECM_PATCH_CACHE_E in tablespace MGMT_TABLESPACE.
ORA-39905: Table SYSMAN.MGMT_ECM_PATCH_CACHE_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB0000077198C00017$$ in tablespace MGMT_ECM_DEPOT_TS.
ORA-39915: Partition 2020-11-07 00:00 of table SYSMAN.MGMT_AUDIT_LOGS_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB_P1936 in tablespace MGMT_ECM_DEPOT_TS.
ORA-39910: Partitioned Global index SYSMAN.SYS_IL0000082706C00023$$ in tablespace MGMT_ECM_DEPOT_TS points to partition 2020-10-21 21:04 of table SYSMAN.MGMT_AUDIT_LOGS_E in tablespace MGMT_TABLESPACE
outside of transportable set.
VIOLATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39915: Partition 2020-11-05 00:00 of table SYSMAN.MGMT_AUDIT_LOGS_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB_P1735 in tablespace MGMT_ECM_DEPOT_TS.
142 rows selected.
- Executing procedure again mentioning violations tablespaces details.
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('MGMT_TABLESPACE,MGMT_ECM_DEPOT_TS,MGMT_AD4J_TS', TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM transport_set_violations;
no rows selected
- Make tablespace read only mode
SQL> ALTER TABLESPACE MGMT_TABLESPACE READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE MGMT_ECM_DEPOT_TS READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE MGMT_AD4J_TS READ ONLY;
Tablespace altered.
Check datafile details
SQL> col name for a60
SQL> SELECT NAME FROM V$DATAFILE WHERE TS#=(SELECT T.TS# FROM V$TABLESPACE T WHERE NAME='MGMT_ECM_DEPOT_TS');
NAME
------------------------------------------------------------
/u01/app/oracle/oradata/OEMDB/mgmt_depot.dbf
SQL> SELECT NAME FROM V$DATAFILE WHERE TS#=(SELECT T.TS# FROM V$TABLESPACE T WHERE NAME='MGMT_AD4J_TS');
NAME
------------------------------------------------------------
/u01/app/oracle/oradata/OEMDB/mgmt_ad4j.dbf
SQL> SELECT NAME FROM V$DATAFILE WHERE TS#=(SELECT T.TS# FROM V$TABLESPACE T WHERE NAME='MGMT_TABLESPACE');
NAME
------------------------------------------------------------
/u01/app/oracle/oradata/OEMDB/mgmt.dbf
- Copy datafile to window machine as tablespace already are in read only mode.
SQL>
host cp /u01/app/oracle/oradata/OEMDB/mgmt_depot.dbf /media/sf_varun_docs/backup/
host cp /u01/app/oracle/oradata/OEMDB/mgmt_ad4j.dbf /media/sf_varun_docs/backup/
host cp /u01/app/oracle/oradata/OEMDB/mgmt.dbf /media/sf_varun_docs/backup/
- EXPDP transport tablespace
[oracle@oem13c OEMDB]$ expdp system/system123 dumpfile=mgmt.dmp directory=expdpmgmt transport_tablespaces=MGMT_TABLESPACE,MGMT_ECM_DEPOT_TS,MGMT_AD4J_TS logfile=mgmt.log
Export: Release 12.2.0.1.0 - Production on Mon Nov 2 13:26:20 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=mgmt.dmp directory=expdpmgmt transport_tablespaces=MGMT_TABLESPACE,MGMT_ECM_DEPOT_TS,MGMT_AD4J_TS logfile=mgmt.log
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/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/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/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/RLS_POLICY/RLS_POLICY
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/rmanbkp/mgmt.dmp
******************************************************************************
Datafiles required for transportable tablespace MGMT_AD4J_TS:
/u01/app/oracle/oradata/OEMDB/mgmt_ad4j.dbf
Datafiles required for transportable tablespace MGMT_ECM_DEPOT_TS:
/u01/app/oracle/oradata/OEMDB/mgmt_depot.dbf
Datafiles required for transportable tablespace MGMT_TABLESPACE:
/u01/app/oracle/oradata/OEMDB/mgmt.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Nov 2 13:41:11 2020 elapsed 0 00:14:46
- Check datafile details of Destinations of window database ORCL.
SQL> SELECT NAME FROM V$DATAFILE;
NAME
--------------------------------------------------------------------------------
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\USERS01.DBF
- Using RMAN CONVERT DATAFILE command on datafile of target datafile directory.
C:\Users\varunyadav>rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Nov 2 14:22:17 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1583199703)
RMAN> CONVERT DATAFILE 'C:\varun_docs\backup\mgmt.dbf' FROM PLATFORM 'Linux x86 64-bit' FORMAT 'C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\%U';
Starting conversion at target at 02-NOV-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=C:\VARUN_DOCS\BACKUP\MGMT.DBF
converted datafile=C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\DATA_D-OEMDB_I-722581170_TS-MGMT_TABLESPACE_FNO-2_05VEH36I
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:05
Finished conversion at target at 02-NOV-20
Starting Control File and SPFILE Autobackup at 02-NOV-20
piece handle=C:\APP\VARUNYADAV\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\AUTOBACKUP\2020_11_02\O1_MF_S_1055427919_HSZLCRR8_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 02-NOV-20
RMAN> exit
Recovery Manager complete.
C:\Users\varunyadav>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 2 14:26:59 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\USERS01.DBF
- Check the privilages of schemas (optional)
SQL>
set long 100000
set pages 200
select dbms_metadata.get_ddl( 'USER', 'SYSMAN_STB' ) from dual;
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SYSMAN_STB' ) from dual;
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SYSMAN_STB' ) from dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SYSMAN_STB' ) from dual;
set long 100000
set pages 200
select dbms_metadata.get_ddl( 'USER', 'SYSMAN_OPSS' ) from dual;
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SYSMAN_OPSS' ) from dual;
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SYSMAN_OPSS' ) from dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SYSMAN_OPSS' ) from dual;
set long 100000
set pages 200
select dbms_metadata.get_ddl( 'USER', 'SYSMAN_TYPES' ) from dual;
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SYSMAN_TYPES' ) from dual;
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SYSMAN_TYPES' ) from dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SYSMAN_TYPES' ) from dual;
set long 100000
set pages 200
select dbms_metadata.get_ddl( 'USER', 'MGMT_VIEW' ) from dual;
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'MGMT_VIEW' ) from dual;
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'MGMT_VIEW' ) from dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'MGMT_VIEW' ) from dual;
- Create user on destination database
SQL> create user SYSMAN_STB identified by system123;
User created.
SQL> create user SYSMAN_OPSS identified by system123;
User created.
SQL> create user SYSMAN_TYPES identified by system123;
User created.
SQL> create user MGMT_VIEW identified by system123;
User created.
- IMPDP : Impdp transport datafiles
impdp system/system123 dumpfile=mgmt.dmp directory=expdp_window transport_datafiles=C:\varun_docs\backup\mgmt_depot.dbf transport_datafiles=C:\varun_docs\backup\mgmt_ad4j.dbf transport_datafiles=C:\varun_docs\backup\mgmt.dmp logfile=mgmt_window.log
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\USERS01.DBF
C:\VARUN_DOCS\BACKUP\MGMT_AD4J.DBF
C:\VARUN_DOCS\BACKUP\MGMT_DEPOT.DBF
C:\VARUN_DOCS\BACKUP\MGMT.DBF
7 rows selected.
- Grant schema unlimited tablespace privilages
SQL>
sqlplus / as sysdba
ALTER USER SYSMAN_STB DEFAULT TABLESPACE MGMT_TABLESPACE;
ALTER USER SYSMAN_STB QUOTA UNLIMITED ON MGMT_TABLESPACE;
ALTER USER SYSMAN_OPSS DEFAULT TABLESPACE MGMT_TABLESPACE;
ALTER USER SYSMAN_OPSS QUOTA UNLIMITED ON MGMT_TABLESPACE;
ALTER USER MGMT_VIEW DEFAULT TABLESPACE MGMT_ECM_DEPOT_TS;
ALTER USER MGMT_VIEW QUOTA UNLIMITED ON MGMT_ECM_DEPOT_TS;
ALTER USER SYSMAN_TYPES DEFAULT TABLESPACE MGMT_TABLESPACE;
ALTER USER SYSMAN_TYPES QUOTA UNLIMITED ON MGMT_TABLESPACE;
Select a sample data to verify that the transportation was successful.
SQL> select count(*) from tab;
COUNT(*)
----------
171
SQL> show user
USER is "SYSMAN_OPSS"
CONCLUSION:
Transportable tablespace from linux to window using impdp