Wednesday, June 17, 2009

10g Cross Platform TTS example

Cross Platform Transportable Tablespace Case Study


Source Platform – Windows 32 Bit, Oracle 10.2.0.4 – CBDORCA261

Target Platform – AIX 64 Bit, Oracle 10.2.0.4 – DEVU007

This case study demonstrates the cross platform tablespace transport of two tablespaces – USERS and EXAMPLE from the source GENUA2 database.

These tablespaces contain objects from the demo schemas SH,OE,IX,PM,HR,SCOTT with a number of different data types to test the case – as noted, issues were observed with few objects of certain XML data type and some objects did not get transported. This will have to be investigated further.

DATA_TYPE
--------------------------------------------------------------------------------

AQ$_SIG_PROP
ADHEADER_TYP
XMLTYPE
TEXTDOC_TAB
TIMESTAMP(6) WITH LOCAL TIME ZONE
INTERVAL YEAR(2) TO MONTH
NVARCHAR2
BFILE
TIMESTAMP(6)
ORDER_EVENT_TYP
WAREHOUSE_TYP
CHAR
NUMBER
CLOB
ORDDOC
PHONE_LIST_TYP
CUST_ADDRESS_TYP
ORDIMAGE
ORDIMAGESIGNATURE
INVENTORY_LIST_TYP
DATE
RAW
TIMESTAMP(9)
ORDER_LIST_TYP
ORDER_ITEM_LIST_TYP
CUSTOMER_TYP
ORDVIDEO
NCLOB
ANYDATA
ORDAUDIO
VARCHAR2
BLOB
ROWID
SDO_GEOMETRY

Determine the platform and endian compatability

If we are transporting the tablespace set to a platform different from the source platform, then determine if cross-platform tablespace transport is supported for both the source and target platforms, and determine the endianness of each platform. If both platforms have the same endianness, no conversion is necessary. Otherwise you must do a conversion of the tablespace set either at the source or target database.

Run the following SQL on both Source and Target.

SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;


Source:

PLATFORM_NAME ENDIAN_FORMAT
------------------------------------------------------------ --------------
Microsoft Windows IA (32-bit) Little


Target:

PLATFORM_NAME ENDIAN_FORMAT
-------------------------------------------------------------------------------- --------------
AIX-Based Systems (64-bit) Big

Since the endian formats are different, thus a conversion is necessary for transporting the tablespace set.

Ensure that the tablespaces to be transported are self contained

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('EXAMPLE,USERS',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected


Limitations to be noted for TTS (Transportable Tablespaces)

1. The source and target database must use the same character set and national character set.

2. You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the
tablespace to be transported or the destination tablespace before the transport operation.

3. Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of
the underlying or contained objects are in the tablespace set.

4. Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes, but you must use the IMP and EXP utilities,
not Data Pump. When using EXP, ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).

The following query returns a list of tablespaces that contain XMLTypes:

select distinct p.tablespace_name
from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t
where t.table_name=x.table_name and
t.tablespace_name=p.tablespace_name and
x.owner=u.username

Transporting tablespaces with XMLTypes has the following limitations:

a.The target database must have XML DB installed.
b.Schemas referenced by XMLType tables cannot be the XML DB standard schemas.
c.Schemas referenced by XMLType tables cannot have cyclic dependencies.
d.Any row level security on XMLType tables is lost upon import.
e.If the schema for a transported XMLType table is not present in the target database, it is imported and registered.
If the schema already exists in the target databasean error is returned unless the ignore=y option is set.

5. Advanced Queues Transportable tablespaces do not support 8.0-compatible
advanced queues with multiple recipients.

6. You cannot transport the SYSTEM tablespace or objects owned by the user SYS.

7. Opaque Types Types(such as RAW, BFILE, and the AnyTypes) can be transported, but they are not converted as part of the cross-platform transport operation.
Their actual structure is known only to the application, so the application must address any endianness issues after these types are moved to the new platform.

8. Floating-Point Numbers BINARY_FLOAT and BINARY_DOUBLE types are transportable using Data Pump but not the original export utility, EXP.


Export the database metadata from the source

Note – Objects in SYS and SYSAUX tablespaces are not transported

E:\oracle\rman\GENUA2>expdp directory=ttsdir logfile=db_meta_exp.log dumpfile=db_meta.dmp full=y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE, content=METADATA_ONLY

Export: Release 10.2.0.4.0 - Production on Tuesday, 12 May, 2009 14:23:57

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: system/oracle

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produc
tion
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=ttsdir logfil
e=db_meta_exp.log dumpfile=db_meta.dmp full=y INCLUDE=USER,ROLE,ROLE_GRANT,PROFI
LE, content=METADATA_ONLY
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
E:\ORACLE\RMAN\GENUA2\DB_META.DMP
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at 14:24:08



Restrict Access to the database and make tablespaces to be transported as READ ONLY


E:\oracle\rman\GENUA2>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 12 14:26:11 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter system enable restricted session;

System altered.

SQL> alter tablespace example read only;

Tablespace altered.

SQL> alter tablespace users read only;

Tablespace altered.


Make a note of any directories which exist on the source database – these need to be created on the target database as well before plugging in the tablespaces as objects may refer to these directories

SQL> set serveroutput on
SQL> declare x boolean;
2 begin x := dbms_tdb.check_external; end;
3
4 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR, SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.
WORK_DIR, SYS.ADMIN_DIR,
SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA


Export the Tablespace Metadata

Parfile dp_ttsexp.par:

directory=ttsdir
dumpfile=ts_meta.dmp
logfile=dp_ttsexp.log
transport_full_check=no
transport_tablespaces=EXAMPLE, USERS


Note: Data Pump does not handle XMLSCHEMA data type

SQL> desc oe.purchaseorder
Name Null? Type
----------------------------------------- -------- ----------------------------

TABLE of XMLTYPE(XMLSchema "http://localhost:8080/source/schemas/poSource/xsd/pu
rchaseOrder.xsd" Element "PurchaseOrder") STORAGE Object-relational TYPE "PURCHASEORDER_T"
E:\oracle\rman\GENUA2>expdp parfile=ts_meta_exp.par

Export: Release 10.2.0.4.0 - Production on Tuesday, 12 May, 2009 14:38:43

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Username: system
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Produc
tion
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=ts_met
a_exp.par
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/TABLE
ORA-39139: Data Pump does not support XMLSchema objects. TABLE:"OE"."PURCHASEORD
ER" will be skipped.
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/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/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
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\RMAN\GENUA2\TS_META.DMP
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" completed with 1 error(s) at 14:39:10



Perform the Conversion

Note: Conversion can be performed either at source or target – a number of factors need to be considered for this


E:\oracle\rman\GENUA2>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue May 12 14:59:19 2009

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

connected to target database: GENUA2 (DBID=3824459639)

RMAN> convert tablespace users,example to
2> platform 'AIX-Based Systems (64-bit)'
3> format 'E:\oracle\rman\genua2\%U';

Starting backup at 12/MAY/09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=129 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=+DATA/genua2/datafile/example.444.686236815
converted datafile=E:\ORACLE\RMAN\GENUA2\DATA_D-GENUA2_I-3824459639_TS-EXAMPLE_F
NO-5_08KERKQM
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=+DATA/genua2/datafile/users.375.686236713
converted datafile=E:\ORACLE\RMAN\GENUA2\DATA_D-GENUA2_I-3824459639_TS-USERS_FNO
-4_09KERKQU
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 12/MAY/09



E:\oracle\rman\GENUA2>dir

12/05/2009 02:59 PM 104,865,792 DATA_D-GENUA2_I-3824459639_TS-EXAMPLE_FNO
-5_08KERKQM
12/05/2009 02:59 PM 5,251,072 DATA_D-GENUA2_I-3824459639_TS-USERS_FNO-4
_09KERKQU
12/05/2009 02:24 PM 212,992 DB_META.DMP
12/05/2009 02:24 PM 1,093 db_meta_exp.log
12/05/2009 02:39 PM 2,398 dp_ttsexp.log
12/05/2009 02:39 PM 1,351,680 TS_META.DMP
12/05/2009 02:37 PM 306 ts_meta_exp.par
7 File(s) 111,685,333 bytes
2 Dir(s) 46,828,691,456 bytes free


Rename the converted files to users01.dbf and example01.dbf

E:\oracle\rman\GENUA2>dir *.dbf
Volume in drive E is ORACLE
Volume Serial Number is 289A-13DE

Directory of E:\oracle\rman\GENUA2

12/05/2009 02:59 PM 104,865,792 example01.dbf
12/05/2009 02:59 PM 5,251,072 users01.dbf
2 File(s) 110,116,864 bytes
0 Dir(s) 46,828,691,456 bytes free



Create the GENUA2 database on the target platform

Note: ensure the same character set is used as the source database


scp or FTP the dumpfiles from the Windows source machine to the AIX Target machine

location on target machine is /u01/oracle/tts

Create the required directories on the target – in case any directores are created on the source, the same need to be created on the target database

SQL> create directory ttsdir as '/u01/oracle/tts';

Directory created.


Note: We need to create some placeholder tablespaces which are created so that the meta data dumpfile which will create the users can be imported. These tablespaces are those that are designated as the DEFAULT tablespace for the particular user in the source database.

Note: these placeholder tablespaces have to be dropped as they will be plugged in at a later stage.TTS will fail if a tablespace which is being plugged in already exists in the target database.


Scp or FTP all the Data Pump dumpfiles and RMAN converted datafiles to the target machine required locations

/u01/oracle/tts location for all the meta data dump files

/u02/oradata/genua2 location for the datafiles


Import the user meta data

genua2:/u01/oracle/tts> impdp system/oracle dumpfile=db_meta.dmp logfile=dp_fullimp.log full=y directory=ttsdir

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/******** dumpfile=GAVIN.DMP logfile=dp_fullimp.log full=y directory=ttsdir
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"MONITORING_PROFILE" already exists
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"TSMSYS" already exists
ORA-31684: Object type USER:"ANONYMOUS" already exists
ORA-31684: Object type USER:"OLAPSYS" already exists
ORA-31684: Object type USER:"MDDATA" already exists
ORA-31684: Object type USER:"SYSMAN" already exists
ORA-31684: Object type USER:"MGMT_VIEW" already exists
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type DATABASE_EXPORT/ROLE
ORA-31684: Object type ROLE:"SELECT_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"EXECUTE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"DELETE_CATALOG_ROLE" already exists
ORA-31684: Object type ROLE:"RECOVERY_CATALOG_OWNER" already exists
ORA-31684: Object type ROLE:"GATHER_SYSTEM_STATISTICS" already exists
ORA-31684: Object type ROLE:"LOGSTDBY_ADMINISTRATOR" already exists
ORA-31684: Object type ROLE:"AQ_ADMINISTRATOR_ROLE" already exists
ORA-31684: Object type ROLE:"AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"GLOBAL_AQ_USER_ROLE" already exists
ORA-31684: Object type ROLE:"SCHEDULER_ADMIN" already exists
ORA-31684: Object type ROLE:"HS_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"OEM_ADVISOR" already exists
ORA-31684: Object type ROLE:"OEM_MONITOR" already exists
ORA-31684: Object type ROLE:"WM_ADMIN_ROLE" already exists
ORA-31684: Object type ROLE:"JAVAUSERPRIV" already exists
ORA-31684: Object type ROLE:"JAVAIDPRIV" already exists
ORA-31684: Object type ROLE:"JAVASYSPRIV" already exists
ORA-31684: Object type ROLE:"JAVADEBUGPRIV" already exists
ORA-31684: Object type ROLE:"EJBCLIENT" already exists
ORA-31684: Object type ROLE:"JAVA_ADMIN" already exists
ORA-31684: Object type ROLE:"JAVA_DEPLOY" already exists
ORA-31684: Object type ROLE:"CTXAPP" already exists
ORA-31684: Object type ROLE:"XDBADMIN" already exists
ORA-31684: Object type ROLE:"AUTHENTICATEDUSER" already exists
ORA-31684: Object type ROLE:"XDBWEBSERVICES" already exists
ORA-31684: Object type ROLE:"OLAPI_TRACE_USER" already exists
ORA-31684: Object type ROLE:"OLAP_DBA" already exists
ORA-31684: Object type ROLE:"CWM_USER" already exists
ORA-31684: Object type ROLE:"OLAP_USER" already exists

……..

………

Import the tablespace meta data and plug in the USERS and EXAMPLE tablespaces


genua2:/u01/oracle/tts> impdp dumpfile=TS_META.DMP directory=ttsdir transport_datafiles=/u02/oradata/genua2/users01.dbf,/u02/oradata/genua2/example01.dbf

The following errors were observed while the tablespace import took place

ORA-31684: Object type TYPE:"XDB"."XDB$RAW_LIST_T" already exists

ORA-39139: Data Pump does not support XMLSchema objects. "OE"."PURCHASEORDER" will be skipped.

ORA-39132: object type "OE"."PART_T" already exists with different hashcode




Note: users like SH, OE. HRetc have now been created


SQL> select username,to_char(created,'HH24:MI:SS') from dba_users order by 2;

USERNAME TO_CHAR(
------------------------------ --------
BI 15:33:06
IX 15:33:06
PM 15:33:06
HR 15:33:06
OE 15:33:06
SH 15:33:06
SYSTEM 21:24:50
SYS 21:24:50
OUTLN 21:24:59
DIP 21:30:04
TSMSYS 21:38:48
DBSNMP 21:42:38
ORACLE_OCM 21:42:45
WMSYS 21:44:20
EXFSYS 21:53:45
DMSYS 21:54:08
CTXSYS 21:54:47
ANONYMOUS 21:55:38
XDB 21:55:38
ORDPLUGINS 21:58:21
SI_INFORMTN_SCHEMA 21:58:21
ORDSYS 21:58:21
MDSYS 21:58:21
OLAPSYS 22:10:18
MDDATA 22:11:52
SYSMAN 22:18:33
MGMT_VIEW 22:20:52
SCOTT 22:22:00

Check the objects that have been imported


HR 28
HR INDEX 19
HR TABLE 7
HR TRIGGER 2
IX 53
IX LOB 3
IX TYPE 1
IX VIEW 8
IX INDEX 14
IX QUEUE 4
IX TABLE 15
IX RULE SET 4
IX SEQUENCE 2
IX EVALUATION CONTEXT 2
OE 57
OE LOB 5
OE TYPE 11
OE INDEX 26
OE TABLE 11
OE TRIGGER 1
OE TYPE BODY 3
PM 26
PM LOB 17
PM TYPE 3
PM INDEX 3
PM TABLE 3
SH 297
SH LOB 2
SH INDEX 27
SH TABLE 16
SH INDEX PARTITION 196
SH TABLE PARTITION 56
SCOTT 6
SCOTT INDEX 2
SCOTT TABLE 4

Note: Tablespaces that have been imported will have the PLUGGED_IN column set to YES

SQL> select plugged_in from dba_tablespaces where tablespace_name in ('USERS','EXAMPLE');

PLU
---
YES
YES

Flashback - Normal vs Guaranteed Restore Points

Normal Restore Points

If the db_recovery_dest_file_size has been allocated less space then what is required for the flashback transaction logs as dictated by the db_flashback_retention_target parameter, then the flashback logs are deleted to free up space for the additional logs that are being generated.

Alert log will show also that flashback logs are being deleted

Deleted Oracle managed file /u02/oradata/testdb/TESTDB/flashback/o1_mf_51nc8494_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB/flashback/o1_mf_51nc86n3_.flb
Deleted Oracle managed file /u02/oradata/testdb/TESTDB/flashback/o1_mf_51nc8963_.flb


Flashback then MAY fail

SQL> flashback database to scn 1580000;
flashback database to scn 1580000
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.

Guaranteed Restore Points

With guaranteed restore points, logs are not deleted, but if the space allocated for the flashback logs is not adequate, the database may hang until more space is allocated.

The parameter db_recovery_file_dest_size is dynamic and can be increased on the fly to allocate more space for the flashback logs.

This query should be run when we have enabled a guaranteed restore point - the STORAGE_SIZE column will show the disk space that is currently being used by the flashback logs and this should be compared with the db_recovery_file_dest_size value so as to ensure that we have allocated sufficient space for the flashback logs.

select name,scn,time,database_incarnation#,guarantee_flashback_database,storage_size
from v$restore_point
where guarantee_flashback_database = 'YES';

Kill all UNIX processes with one command



$ ps -ef |grep opmn|grep -v grep| awk '{print $2}' | xargs -i kill -9 {}