Moving data using transportable tablespaces can be faster than performing either a full export/import or unload/load of the same data. Reason is the datafiles containing all of the actual data are just copied to the destination location, and use the datapump export/import utility to transfer only the metadata of the tablespace objects to the new database.
If a tablespace already exists in the destination database then it cannot be imported in Target Database. i.e if you are trying to transport the ‘USERS’ tablespace from the source, and if ‘USERS’ already exists in the destination – you will get an error upon import.
Also need to ensure that the users, who currently own these objects in the source database, are created in the destination database prior to the tablespaces being moved. Alternatively use the ‘Remap Schema’ parameter to put the objects into a different schema.
1.. Verify endianness of Source and Target Oracle Database
Source Oracle Database 11203
Target Oracle Database 1930
| set lines 200; SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; |
2.. In source Oracle Database 11gR2
Create the list all of the tablespaces that are need to be transported and check tablespace block sizes and par files.
| select tablespace_name, block_size from dba_tablespaces where tablespace_name not in (‘SYSTEM’,’SYSAUX’,’USERS’) and contents = ‘PERMANENT’; |
Note: – Verify the block size for all tablespaces that will be Transported.If there are any using a non-standard block size,then need to add to the target database the init parameter i.e db_nK_cache_size (where ‘n’ is the desired block size) and it must be done prior to the TTS import.
2.1 Ensure there is a valid directory of datapump at both source and target Oracle Database
Source Oracle Database
Target Oracle Database
| set lines 200; set pagesize 200; column OWNER format a10 column DIRECTORY_NAME format a15 column DIRECTORY_PATH format a25 SELECT OWNER,DIRECTORY_NAME,DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME=’MIGRATION’; Create or replace directory DATA_PUMP_DIR as ‘<DIR_NAME>’; GRANT read, write on directory DATA_PUMP_DIR TO <user_name>; |
2.2 In source Oracle Database execute the script to create TTS export Data Pump parameter file
| REM REM Create TTS Data Pump export REM set feedback off trimspool on set serveroutput on size 1000000 REM REM Data Pump parameter file for TTS export REM spool /u001/MIGRATION/dp_ttsexp.par declare tsname varchar(30); i number := 0; begin dbms_output.put_line(‘directory=MIGRATION’); dbms_output.put_line(‘dumpfile=dp_tts.dmp’); dbms_output.put_line(‘logfile=dp_ttsexp.log’); dbms_output.put_line(‘transport_full_check=no’); dbms_output.put(‘transport_tablespaces=’); for ts in (select tablespace_name from dba_tablespaces where tablespace_name not in (‘SYSTEM’,’SYSAUX’,’USERS’) and contents = ‘PERMANENT’ order by tablespace_name) loop if (i!=0) then dbms_output.put_line(tsname||’,’); end if; i := 1; tsname := ts.tablespace_name; end loop; dbms_output.put_line(tsname); dbms_output.put_line(”); end; / spool off |
2.3 In source Oracle Database execute the script to create TTS import Data Pump parameter files
| REM set feedback off trimspool on set serveroutput on size 1000000 REM REM Data Pump parameter file for TTS import REM spool /u001/MIGRATION/dp_ttsimp.par declare fname varchar(513); i number := 0; begin dbms_output.put_line(‘directory=MIGRATION’); dbms_output.put_line(‘dumpfile=dp_tts.dmp’); dbms_output.put_line(‘logfile=dp_ttsimp.log’); dbms_output.put(‘transport_datafiles=’); for df in (select file_name from dba_tablespaces a, dba_data_files b where a.tablespace_name = b.tablespace_name and a.tablespace_name not in (‘SYSTEM’,’SYSAUX’,’USERS’) and contents = ‘PERMANENT’ order by a.tablespace_name) loop if (i!=0) then dbms_output.put_line(””||fname||”’,’); end if; i := 1; fname := df.file_name; end loop; dbms_output.put_line(””||fname||””); dbms_output.put_line(”); end; / spool off; |
2.4 Check for user-created objects in the system and sysaux tablespaces
| select owner, segment_name, segment_type from dba_segments where tablespace_name in (‘SYSTEM’, ‘SYSAUX’) and owner not in (select name from system.logstdby$skip_support where action=0); |
Note: –
As objects in the system and sysaux tablespaces are not transported, check for user-created objects that may be in these two tablespaces with given script.
If so then move the identified objects to a user tablespace prior to beginning the transport process so the objects can be transported by TTS. Alternatively, you can move the objects separately with Data Pump or you can manually re-create them after performing the database upgrade.
2.5. Create the ‘Create user script’ to generate a ‘create user’ script to be used at the destination database to create the users prior to the TTS import. A subsequent metadata import, run after the TTS, will reset these accounts to their original passwords and default tablespaces.
| spool /u001/MIGRATION/def_Tbs.sql select ‘CREATE USER ‘||username||’ IDENTIFIED BY test DEFAULT TABLESPACE SYSTEM;’ from dba_users where username not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’,’SYSMAN’,’OUTLN’,’MDSYS’,’ORDSYS’,’EXFSYS’,’DMSYS’,’WMSYS’,’CTXSYS’,’ANONYMOUS’,’XDB’,’ORDPLUGINS’,’OLAPSYS’,’PUBLIC’); spool off; |
2.6 In Source Oracle Database check containment Run this script for the DBMS_TTS.TRANSPORT_SET_CHECK function to perform the self-containment check for the list of tablespaces to be transported.
| declare checklist varchar2(4000); i number := 0; begin for ts in (select tablespace_name from dba_tablespaces where tablespace_name not in (‘SYSTEM’,’SYSAUX’) and contents = ‘PERMANENT’) loop if (i=0) then checklist := ts.tablespace_name; else checklist := checklist||’,’||ts.tablespace_name; end if; i := 1; end loop; dbms_tts.transport_set_check(checklist,TRUE,TRUE); end; / select * from transport_set_violations; |
2.7 Export source Oracle Database metadata
| nohup expdp system/oracle123@prod DIRECTORY=MIGRATION LOGFILE=dp_fullexp_meta.log DUMPFILE=dp_full.dmp FULL=y CONTENT=METADATA_ONLY & |
Export source metadata from the source database. After the tablespaces are transported, the metadata will be imported into the target database to create the remaining metadata that was not transported. Perform no DDL at the source database after this step as this will create a dump file for the metadata.
2.8 Check for tablespaces using a non-default block size.
These tablespaces will need a db_nK_cache_size parameter at the target database to support the tablespace
2.9 In Source Oracle Database Create tablespace scripts to change tablespace into read only mode.
This script creates the tts_tsro.sql script from the source database. Use this script to set all tablespaces to be transported to READ ONLY mode.
| set heading off set feedback off set trimspool on set linesize 500 spool /u001/MIGRATION/tts_tsromain.sql prompt /* =================================== */ prompt /* Make all user tablespaces READ ONLY */ prompt /* =================================== */ select ‘ALTER TABLESPACE ‘ || tablespace_name || ‘ READ ONLY;’ from dba_tablespaces where tablespace_name not in (‘SYSTEM’,’SYSAUX’,’USERS’) and contents = ‘PERMANENT’; spool off |
2.9.1 Set the tablespaces to be transported to read only mode
or verify the status of Tablespace
2.9.2 Similarly in Source Oracle Database create the tts_tsrw.sql script to set all tablespaces to READ WRITE mode after the transport tablespace import process
3.. Export the tablespaces and monitor the log
Review the log
Note: – As mentioned in the log file of export. We need to copy data files for Transportable tablespace
4.. Copy the files to a place that is accessible to the destination Oracle Database i.e DATA_PUMP_DIR
In Target Oracle Database Login to as sysdba
4.1 You can use secure copy to transfer files from Source to Target Oracle Database
Verify
5. In Target Oracle Database create users
Note :- Verify for user defined users only. Given below I dropped all users for testing and recreated again
6. Import the tablespaces
Before executing import data files which are in process of Transport Tablespaces need to be copied into location where Target Oracle Database datafiles resides.
Also, in case of different location of data file’s location in source and target then it has to be modified in parameter file of import as given below.
Execute impdp to load the data
Verify the import log file
6.1 Make tablespace read write.
6.2 Import source metadata
Review the log
| [oracle@oracledatabase19c MIGRATION]$ impdp system/oracle@proda DIRECTORY=MIGRATION LOGFILE=dp_fullimp_meta.log DUMPFILE=dp_full.dmp FULL=y CONTENT=METADATA_ONLY Import: Release 19.0.0.0.0 – Production on Mon Mar 25 22:23:43 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Master table “SYSTEM”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded import done in AL32UTF8 character set and AL16UTF16 NCHAR character set export done in UTF8 character set and AL16UTF16 NCHAR character set Starting “SYSTEM”.”SYS_IMPORT_FULL_01″: system/@proda DIRECTORY=MIGRATION LOGFILE=dp_fullimp_meta.log DUMPFILE=dp_full.dmp FULL=y CONTENT=METADATA_ONLY Processing object type DATABASE_EXPORT/TABLESPACE ORA-31684: Object type TABLESPACE:”TEMP” already exists ORA-31684: Object type TABLESPACE:”USERS” already exists ORA-39083: Object type TABLESPACE:”APPS_UNDO” failed to create with error: ORA-01119: error in creating database file ‘/u001/app/oracle/oradata/prod/APPS_UNDO_01.dbf’ ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 Failing sql is: CREATE UNDO TABLESPACE “APPS_UNDO” DATAFILE ‘/u001/app/oracle/oradata/prod/APPS_UNDO_01.dbf’ SIZE 1073741824 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE Create same UNDO tablespace as Source in Target before metadata import ORA-31684: Object type TABLESPACE:”RECOVERY” already exists ORA-31684: Object type TABLESPACE:”FLASH_DATA_ARCHIVE” already exists ORA-31684: Object type TABLESPACE:”APPS” already exists Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER ORA-31685: Object type USER:”SYS” failed due to insufficient privileges. Failing sql is: ALTER USER “SYS” IDENTIFIED BY VALUES ‘S:245436C8571C7AEE0839689E6F6748C54AF949BB51DB52606E8F74344106;8A8F025737A9097A’ TEMPORARY TABLESPACE “TEMP” Processing object type DATABASE_EXPORT/SCHEMA/USER IMPDP – ORA-31685 (Object Type USER:”” Failed Due To Insufficient Privileges) (Doc ID 1568333.1) ORA-31684: Object type ROLE:”OLAP_XS_ADMIN” already exists ORA-31684: Object type ROLE:”OLAP_DBA” already exists ORA-31684: Object type ROLE:”OLAP_USER” already exists ORA-39083: Object type PROCACT_SYSTEM failed to create with error: ORA-20000: Incompatible version of Workspace Manager Installed ORA-20000: Incompatible version of Workspace Manager Installed (Doc ID 2094940.1) Failing sql is: BEGIN declare ver varchar2(100) ; cnt integer ; dummy integer; vdummy varchar2(30) ; compile_exception EXCEPTION; PRAGMA EXCEPTION_INIT(compile_exception, -06550); invalid_table EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_table, -00942); procedure createErrorProc is begin execute immediate ‘create or replace function system.wm$_check_install return boolean is begin return true ; end;’ ; end ; begin select status into vdummy from dba_registry where comp_id = ‘OWM’ ; if (vdummy not in (‘VALID’, ‘UPGRADED’, ‘LOADED’)) then raise compile_exception ; end if ; select 1 into dummy from dual where exists (select 1 from all_users where username = ‘WMSYS’) ; execute immediate ‘create or replace function wmsys.wm$_old_version return varchar2 is begin return ”11.2.0.3.0” ; end;’ ; select count() into cnt from dba_procedures where object_name=’LT_EXPORT_PKG’ and procedure_name=’IMPORTEXPORTSUPPORT’; if (cnt=0) then raise_application_error(-20000, ‘Incompatible version of Workspace Manager Installed’); end if ; execute immediate ‘select value from wmsys.wm$env_vars where name=”OWM_VERSION”’ into ver ; if (0>0 and ver != ‘11.2.0.3.0’) then raise_application_error(-20000, ‘Unable to import due to one or more versioned tables not having a ”VERSIONED” status’); end if ; execute immediate ‘declare cnt integer ; begin select count() into cnt from (select workspace from wmsys.wm$version_hierarchy_table where version!=0 union all select table_name from wmsys.wm$versioned_tables) ; if (cnt>0) Failing sql is: BEGIN SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT(‘GRANT EXECUTE ON DBMS_DEFER_SYS TO DBA’);COMMIT; END; ORA-39083 And ORA-04042 Errors On DBMS_DEFER_SYS When Importing Into 12.2 Database (Doc ID 2335846.1) Failing sql is: ALTER TABLE “SYSMAN”.”MGMT_INV_COMPONENT” ADD CONSTRAINT “ENT_UK” UNIQUE (“COMPONENT_GUID”) USING INDEX (CREATE INDEX “SYSMAN”.”MGMT_INV_COMPONENT_IDX” ON “SYSMAN”.”MGMT_INV_COMPONENT” (“COMPONENT_GUID”, “CONTAINER_GUID”, “NAME”, “EXTERNAL_NAME”, “VERSION”) PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE “SYSAUX” ) ENABLE ORA-39083: Object type CONSTRAINT:”SYSMAN”.”MGMT_POLICY_ASSOC_CFG_PK” failed to create with error: ORA-00955: name is already used by an existing object Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW ORA-31685: Object type MATERIALIZED_VIEW:”SYSMAN”.”MGMT_ECM_MD_ALL_TBL_COLUMNS” failed due to insufficient privileges. Failing sql is: CREATE MATERIALIZED VIEW “SYSMAN”.”MGMT_ECM_MD_ALL_TBL_COLUMNS” (“METADATA_ID”, “TARGET_TYPE”, “SNAPSHOT_TYPE”, “TABLE_NAME”, “NAME”, “UI_NAME”, “TYPE”, “TYPE_FORMAT”, “UI_ON”, “COMPARE_ON”, “COMPARE_UI_ON”, “HISTORY_ON”, “HISTORY_UI_ON”, “IS_KEY”, “IS_CONTEXT”, “IS_SUMMARY”, “IS_CHILD_LINK”, “LINK_COLUMN_NAME”, “COL_ORDER”, “SOURCE_TABLE_NAME”) USING (“MGMT_ECM_MD_ALL_TBL_COLUMNS”, (10, ‘PROD’, 1, 0, 0, “SYSMAN”, “MGMT_ECM_SNAPSHOT_MD_COLUMNS”, ‘2011-09-17 10:13:09’, 0, 0, ‘2011-09-17 10:13:09’, ”, 0, 847261, 0, NULL), 2621760, 10, (‘2011-09-17 10:10:15’, 1, 0, 0, 847261, 0, 0, 0, 2, NULL, NULL)) REFRESH FORCE AS SELECT c.METADATA_ID, m.TARGET_TYPE, m.SNAPSHOT_TYPE, t.name as TABLE_NAME, c.NAME, c.UI_NAME, c.TYPE, c.TYPE_FORMAT, c.UI_ON, c.COMPARE_ON, c.COMPARE_UI_ON, c.HISTORY_ON, c.HISTORY_UI_ON, c.IS_KEY, c.IS_CONTEXT, c.IS_SUMMARY, c.IS_CHILD_LINK, c.LINK_COLUMN_NAME, rank() over (partition by c.metadata_id, t.name order by anc.tbl_order asc, c.col_order asc) as COL_ORDER, c.table_name as SOURCE_TABLE_NAME FROM mgmt_ecm_snapshot_metadata m, mgmt_ecm_snapshot_md_tables t, mgmt_ecm_snapshot_md_tables anc, mgmt_ecm_snapshot_md_columns c WHERE (anc.name, anc.metadata_id) in (SELECT anc_t.name, anc_t.metadata_id FROM mgmt_ecm_snapshot_md_tables anc_t START WITH anc_t.name = t.name AND anc_t.metadata_id = t.metadata_id CONNECT BY anc_t.name = PRIOR anc_t.parent_table_name AND anc_t.metadata_id = PRIOR anc_t.metadata_id) AND c.metadata_id = anc.metadata_id AND c.table_name = anc.name AND c.metadata_id = m.metadata_id AND m.kind = ‘P’ AND (( IMPDP – ORA-31685 (Insufficient Privileges) on Object type MATERIALIZED_VIEW:”SYSMAN”.”MGMT_ECM_MD_ALL_TBL_COLUMNS” (Doc ID 1563217.1) ORA-39082: Object type PACKAGE BODY:”APEX_030200″.”WWV_META_CLEANUP” created with compilation warnings ORA-39082: Object type PACKAGE BODY:”APEX_030200″.”WWV_MIG_ACC_LOAD” created with compilation warnings Job “SYSTEM”.”SYS_IMPORT_FULL_01″ completed with 634 error(s) at Mon Mar 25 22:28:42 2024 elapsed 0 00:04:58 Recompile the INVALID objects post migration |
Happy Learning !!!!
askmedawaa.wordpress.com 