Thursday 12 November 2020

RMAN - Cross Platform Migration using Transport Tablespace using Expdp | Impdp backup

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


Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)