Using Transportable Tablespace in Oracle Database 10g

Introduction:

Oracle’s transportable tablespace feature allows users to quickly move a user tablespace across Oracle databases.  It is the most efficient way to move bulk data between databases.

Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data. This is because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move both table and index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data

Prior to Oracle Database 10g, if you want to transport a tablespace, both source and target databases need to be on the same platform

Oracle Database 10g adds the cross platform support for transportable tablespaces. With the cross platform transportable tablespace, you can transport tablespaces across platforms.

Endian Formats:

Even if your source and target platforms are compatible or same, you wont be able to transport the tablespace from source platform to target platform directly. Before you start transporting or copying the datafile, you need to check the endian format of the data files in the source and target platform. Endian format refers to the byte ordering in the datafile. It is just the way bytes are stored in the datafile. There are 2 types of endian formats – Big and Small.

If your target platform is not compatible with your source platform in terms of endian format, then you have to make the endian format of all the datafiles belonging to the tablespace you want to transport same. You can accomplish this using RMAN. I will let you know later how to convert the endian format. For now, we will see the platform compatibility and other details.

Determining the supported platform

First step before transporting the tablespace from source platform to target platform, you need to first check if tablespace transport is compatible. You can check the list of supported platform using following query in the database.

SQL> select * from v$transportable_platform;

 

PLATFORM_ID PLATFORM_NAME                        ENDIAN_FORMAT

----------- ------------------------------------ --------------

          1 Solaris[tm] OE (32-bit)              Big

          2 Solaris[tm] OE (64-bit)              Big

          7 Microsoft Windows IA (32-bit)        Little

         10 Linux IA (32-bit)                    Little

          6 AIX-Based Systems (64-bit)           Big

          3 HP-UX (64-bit)                       Big

          5 HP Tru64 UNIX                        Little

          4 HP-UX IA (64-bit)                    Big

         11 Linux IA (64-bit)                    Little

         15 HP Open VMS                          Little

          8 Microsoft Windows IA (64-bit)        Little

 

PLATFORM_ID PLATFORM_NAME                        ENDIAN_FORMAT

----------- ------------------------------------ --------------

          9 IBM zSeries Based Linux              Big

         13 Linux 64-bit for AMD                 Little

         16 Apple Mac OS                         Big

         12 Microsoft Windows 64-bit for AMD     Little

         17 Solaris Operating System (x86)       Little

 

16 rows selected.

In our case we are going to transport a tablespace from HP Itanium 64 bit platform to Linux 32 bit platform. These platforms are made bold in the above query output. As you can see the endian format of these platforms are different (HP IA 64 has big endian format and Linux IA 32 has little endian format).

You can check your database server platform using following query.

SQL> select platform_name from v$database;
PLATFORM_NAME
-----------------------------------------------
HP-UX IA (64-bit)

General requirements for transporting tablespaces between 2 databases

  • Both platform should have same character set.

you can check the character set of both platform using following query

SQL> select * from nls_database_parameters where parameter like '%SET%';

PARAMETER                      VALUE

------------------------------ ----------------------------------------

NLS_CHARACTERSET               WE8ISO8859P1

NLS_NCHAR_CHARACTERSET         AL16UTF16

 
  • Both database must be using Oracle 8i or higher version, but database version need not be identical
  • You cannot transport SYSTEM tablespace or any object owned by SYS
  • If you want to transport partitioned table, all the partitions must be included in transportable table set. If you are transporting index, all the tablespace containing respective tables also needs to be transported
  • You can transport the tablespace to a target database only if it has same or higher compatibility settting

Following are the brief steps for transporting the tablespace.

1. Ensure that the tablespace is self-contained

You can check if the tablespace you are transporting is self-contained or not using TRANSPORT_SET_PROCEDURE in DBMS_TTS package. This is shown below

DBMS_TTS.TRANSPORT_SET_CHECK (

   ts_list          IN CLOB, 

   incl_constraints IN BOOLEAN DEFAULT FALSE,

   full_check       IN BOOLEAN DEFAULT FALSE);

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

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

If you see no rows selected, that mean the tablespace is self-contained.
2. Make the tablespace read-only
Alter the tablepace to make it read-only as shown below.
SQL> alter tablespace IAS_META read only; 
Tablespace altered.
You can make the tablespace read write once you export the metadata about this tablespace and convert the endian format of the tablespace (if needed).
3. Export metadata using TRANSPORTABLE_TABLESPACE data pump option
Once you make tablespace as readonly, export the metadata information about the tablespace using data pump export as shown below.

-bash-3.00$ expdp system/welcome1 TRANSPORT_TABLESPACES=IAS_META DUMPFILE=test_dir:test_exp.dmp LOGFILE=test_dir:exp_01.log
Export: Release 10.1.0.5.0 - 64bit Production on Tuesday, 22 December, 2009 17:5
Copyright (c) 2003, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** TRANSPORT_TABL
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/CLUSTER
Processing object type TRANSPORTABLE_EXPORT/TTE_CLU_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/TTE_TABLE_OWNER_OBJGRANT/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/TABLE_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/TTE_FUNC_BITMAP_INDEX/TTE_FBM_INDEX_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/TTE_FUNC_BITMAP_INDEX/STATISTICS/TTE_FBM_IND_STATS/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/RLS_POLICY
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/TTE_POSTINST/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /slot/ems6024/oracle/test_exp.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:56
4. Convert the datafile to match endian format
You can use RMAN to convert the data file to the endian format of the target. Following is the command for the same
-bash-3.00$ rman TARGET SYS/welcome1
Recovery Manager: Release 10.1.0.5.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle.  All rights reserved.
connected to target database: HPTSTPW1 (DBID=2764844932)
RMAN> convert tablespace IAS_META to platform 'Linux IA (32-bit)' format '/slot/ems6024/oracle/%U';
Starting backup at 22-DEC-09
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=378 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00009 name=/slot/ems6024/oracle/AS10g/oradata/hptstpw1/ias_meta01.dbf
converted datafile=/slot/ems6024/oracle/data_D-HPTSTPW1_I-2764844932_TS-IAS_META_FNO-9_01l1igm6
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:10
Finished backup at 22-DEC-09
It generated the file with name "data_D-HPTSTPW1_I-2764844932_TS-IAS_META_FNO-9_01l1igm6". If you want to preserve the name or give custom name to the datafile, you can use db_file_name_convert parameter while converting the endian format.
5. Copy the file to target database
Here you need to copy both the converted file from step 4. (tablespace datafile) as well as export dump file from step 3.
6. Run import command to import the transportable tablespace
impdp system/welcome1 TRANSPORT_DATAFILES='/slot/ems5918/oracle/data_D-HPTSTPW1_I-2764844932_TS-IAS_META_FNO-9_01l1igm6' DUMPFILE=test_dir:test_exp.dmp LOGFILE=test_dir:imp_01.log
You might hit the error "UDI-00011: parameter dumpfile is incompatible with parameter transport_tablespaces" in case you are using TRANSPORT_TABLESPACES parameter in above impdp command. Please DO NOT use TRANSPORT_TABLESPACES parameter in impdp. Refer to official release metalink note ID 444756.1
Hope this helps !!
Advertisements

Oracle Data Pump Technology – Oracle Database 10g

Introduction:

Oracle datapump technology refers to the new import and export functionality introduced in Oracle database 10g. New import/export technology is far superior then the traditional import/export tools available until Oracle database 9i. Data pump manages multiple, parallel stream of data to achieve maximum throughput.

Oracle claims that Data Pump allows you to decrease the total export time by two orders of magnitude in most data intensive export jobs. Imports are supposed to run 15-20 times faster then traditional method.

While Oracle continues to support the traditional import utility forever, the traditional export utility will get deprecated eventually.

Benefits of using Data Pump:

Following are some of the benefits of using Data Pump

  • Server based: Data Pump jobs are server based. Mean if you submit the job from the client and you close the client connection the job will continue to run on server.
  • Ability to restart data pump job: We can attach to existing job which is stalled or failed because of lack of space and start the job.
  • Parallel Execution capabilities: Data Pump provide PARALLEL parameter which enables you to provide number of parallel workers
  • Ability to attach to running job: Data Pump provide you capability to attach to running job and change parameter as required
  • Network mode of operation: Ability to import the database from some other database without having to create a dump file
  • Fine-grained data import capability: Provides finer control on which objects to import
  • Remapping Capabilities: Enhancement to traditional TOUSER parameter. You have the capability to remap tablespace, schemas and datafiles
  • Ability to estimate space requirement: You can estimate the space without even running the export job.

Data Pump Components

We have following data pump API available for use in the program

  • DBMS_DATAPUMP
  • DBMS_METADATA

DBMS_DATAPUMP is a package which has the actual data pump engine to export and import data from the database. DBMS_METADATA is used to extract and upload the metadata about data. These are the API packages and can be used directly in a program. Other then this the client binaries like expdp and impdp are also available.

DIRECTORY Object:

One of the benefit of using data pump is that the job runs on the server side unlike the traditional import/export programs. In the traditional import/export programs, Oracle used to make simple client connection to the database and extract the data. The process for import/export in the traditional method used to run on client machine. Meaning that if a job is started using the traditional export and import method, the dump file will get created at a place or machine on which you started the job.

Data pump is different. In that the job always runs on the server where the database resides and job does not make a simple client connection and use SQL to extract the data. The mechanism for data pump is different then traditional technology. The mechanism is explained in the next section of this post. For now the important thing to understand here is that the job runs on the sever side and the dump files also gets created on the server side. So how do we know the location for this dump file. Where exactly will it get created on the server?

To answer this we have a parameter called DIRECTORY. This is a new object introduced in Oracle database 10g. Using DIRECTORY parameter we can specify the location of dump file.

Before using DIRECTORY parameter in expdp or impdp, we need to first create this object in the database. Following is the way to create directory object in database.

SQL> Create directory test_dir as ‘/u01/app/oracle/exp_dump’ ;

Here the location /u01/app/oracle/exp_dump is the location on server and must exists. test_dir is the directory object that we created in the database.

In order for the user to use a directory object, you need to grant privilege to the user on that directory.

SQL> grant read, write on directory test_dir to avdeo;

While doing the export or import there are 3 ways to provide this directory object.

1) Using DIRECTORY parameter

You can specify the DIRECTORY parameters and all the files will get created or read from the that directory. Example

$ expdp hr/hr DIRECTORY=test_dir DUMPFILE=…

2) Using DIRECTORY:FILE notation

You use DIRECTORY:FILE notation if you want to specify different direct for dump file, log file etc. Example

$ expdp hr/hr DUMPFILE=test_dir:exp_file01.dmp LOGFILE=test_dir1:logfile01.log

3) Using DATA_DUMP_DIR environment variable

You can also set DATA_DUMP_DIR env variable to a directory object name and same will be used by the expdp and impdp tool. Example

$ export DATA_DUMP_DIR=test_dir

$ expdp hr/hr DUMPFILE=test.dmp …

Order of precedence for the above notation goes as follows. Oracle will first give the precedence to DIRECTORY:FILE notation (point #2). If you have submitted job giving DIRECTORY:FILE notation then oracle will ignore other notation. Second precedence is giving to DIRECTORY parameter. Oracle will look for DATA_DUMP_DIR environment variable only of you have not used DIRECTORY parameter or DIRECTORY:FILE notation.

Mechanics of Data Pump technology

Data Pump uses several processes to perform the job. This include the master process, the worker process, shadow process and the client process. Lets check out each of these processes

The master process

The master process or more accurately the Master Control Process (MCP) is the primary process for managing data pump job. MCP is a background process with a full name as <instance>_DMnn_<pid> and this process can be seen when a import or export job starts. There is 1 MCP process per job. If you are running 3 import or export job, you will see 3 such master process in that instance. The master process performs following tasks:

  • Create jobs and control them
  • Creates and manages worker process
  • Monitors the job and logs the progress
  • Maintains the job state and restart information
  • Manages necessary files, including the dump file set

When a job is submitted master process creates a table called mater table to log the location of various database objects in export dump file. Master table is the heart of every data pump export and import job. Master process maintains the job state and restart information in master table. Oracle creates master table in the schema of use who is running the data pump job at the beginning of export. Master table contains various set of information regarding the state of the running job, the location of various objects in export dump file, parameters of the job and status of all worker process. Master table has the same name as the name of the job. You can provide the name to the job you are submitting or by default the name of the job will be <schema>_<operation>_<mode>_<unique count>. Example SYS_EXPORT_SCHEMA_01

At the end of export, data pump export job will export the master table and drop it from the schema of the job is successful. If the job fails the master table will remain with all the information about the point of failure. Later on one can just attach to the job and restart the failed job after fixing the issue. Also master table will get deleted if you kill the job using KILL_JOB command.

Master process will also recreate the master table that gets saved at the end of export as the first thing while starting the import. The import process gets complete information about the dumps and objects its going to import using the master table. If the import is successful or forcefully killed by the user, the master table is dropped. If the import job fails or stopped by the user using STOP_JOB command, the master table remains in the database schema.

The worker process

Work process is the one who does the heavy duty work of actual loading and unloading operations. Worker process has the name of the process as <instance>_DWnn_<pid>. Number of worker process depends on the degree of parallelism as defined by the parameter PARALLEL. As the worker process exports and imports the objects they update the status of the same in master table. They update the master table with information about the status of various jobs: completed, pending or failed.

The Shadow process

When a client logs into the database server, the database creates an Oracle background foreground process to service data pump API request. The shadow process creates the job consisting of master table as well as master process. Once client detaches shadow process automatically disappears.

The Client Process

Client process is the one which calls data pump API. You have 2 client- expdp and impdp.

Interactive Data Pump

Running the export and import job in interactive mode is totally different concept in case of traditional import and export and data pump. In case of traditional import and export job when we give the export or import command without giving any parameters, the API will ask for some required mandatory parameters which we used to input and job used to start. But in case of data pump the meaning of interactive job is different. In case of data pump the API never ask you for any inputs of the parameters one by one. Only way you have to submit the job is to give all the parameters, either inline with job or in a parameter file.

Then what is interactive method in case of data pump?

Well the interactive method is used in data pump only when you decide to make some changes to the parameters of the already running job.

The way to get into the interactive mode is either by doing ctrl+c or using ATTACH parameter. Example

expdp hr/hr ATTACH=test_job_01

Data Pump Export Parameters

Following is the brief list of parameters that can be passed with data pump export job. For complete list you can do expdp help=y

DIRECTORY – Used to specify the location for dump file or logfile or SQL file.

DUMPFILE – Used to specify the name of dump file. And yes it replaces the FILE parameter in traditional export

FILESIZE – Used to specify the size of the dump file that it should create. If the size exceeds, then it will write the dump in another file if you have given multiple filenames in DUMPFILE parameter or used %U while giving the name of dump file.

PARFILE – Used to provide the name and location of parameter file which has all the required parameter for the export job to run

LOGFILE and NOLOGFILE – You can specify LOGFILE parameter to to create log file for export job. If you dont specify LOGFILE parameter, Oracle will still create the log file named export.log. Subsequent export jobs will overwrite it as this is the default name. You can specify NOLOGFILE parameter if you want to totally avoid creation of log file for your export job.

CONTENT – Using content parameter, you can filter what goes into the dump file. CONTENT parameter can take 3 values: ALL, DATA_ONLY, METADATA_ONLY

EXCLUDE and INCLUDE – Used for fine grained filtering of the objects that can go in export dump file

QUERY – Used to control the rows of the table that can go into the export dump file

ESTIMATE – Used to estimate the time and space required for export dump file in addition to doing export

ESTIMATE_ONLY – Used to only estimate the time and space requirement for the export job. This parameter will not export and data into the dump file.

NETWORK_LINK – Used to get the export dump from some other database into the current database server. This parameter expects a database network link. Example

expdp hr/hr DIRECTORY=test_dir NETWORK_LINK=finance@prod1 …

Here finance is the remote database on prod1 server from which you are exporting the data.

ATTACH – Used to connect to the currently running job.

JOBNAME – Used to provide custom name to the export jobs.

STATUS – Used to display the status of the job. This parameter takes the numeric values in seconds.

PARALLEL – Used to provide number of parallel workers who will perform the actual job

Data Pump Import Parameters

Some of the parameters in import job are same in export like PARFILE, DIRECTORY, DUMPFILE, LOGFILE, NOLOGFILE etc

Some of the other parameters in import are

SQLFILE – Used to extract DDL from the export dump file. If you specify the SQLFILE parameters it extracts the DDLs, no import takes place.

REUSE_DATAFILES – This parameter tells data pump whether it should existing datafile for creating tablespace during import. If REUSE_DATAFILES is set to Y then import utility will write over your existing datafiles.

CONTENT – Using content parameter, you can filter what goes into the dump file. CONTENT parameter can take 3 values: ALL, DATA_ONLY, METADATA_ONLY

TABLE_EXISTS_ACTION – In case the table already exist in the database which a import job is trying to import from the export dump file, you can use this parameter to tell import job what to do. The valid values are SKIP, APPEND, TRUNCATE, REPLACE

TRANSPORTABLE_TABLESPACE – used when you want to transport the tablespace from one database to another

REMAP_SCHEMA – Used to move objects from one user to another. Replacement of old TOUSER parameter.

REMAP_DATAFILE – Used to remap the data file names specially when transferring the data from one platform to another. Example from windows to UNIX

REMAP_TABLESPACE – Sometimes you want the tablespace in which you are importing the data to be different from the source database tablespace. This parameter comes handy for doing that.

NETWORK_LINK – This parameters is used to import the data directory into the database from some other database without having to create a dump file.

TRANSFORM – In case you don’t want to import the objects storage attributes but just the content, you can use this parameter. TRANSFORM parameter instruct the data pump import job to modify the DDL that creates the object during import. You can modify 2 basic attributes SEGMENT_ATTRIBUTE and STORAGE.

FLASHBACK_TIME – Enables you to import data consistent as of flashback time you specify in the import job. Replacement of old CONSISTENT parameter in traditional import utility.

Monitoring Data Pump Job

You can monitor data pump job using following views

DBA_DATAPUMP_JOBS
OWNER_NAME       VARCHAR2(30) User that initiated the job
JOB_NAME       VARCHAR2(30) Name of the job
OPERATION       VARCHAR2(30) Type of operation being performed
JOB_MODE       VARCHAR2(30) Mode of operation being performed
STATE             VARCHAR2(30) Current job state
DEGREE           NUMBER       Number of worker processes performing the operation
ATTACHED_SESSIONS NUMBER       Number of sessions attached to the job
DBA_DATAPUMP_SESSIONS OWNER_NAME VARCHAR2(30) User that initiated the job JOB_NAME VARCHAR2(30) Name of the job SADDR RAW(4) Address of the session attached to the job You can also use V$SESSION_LONGOPS to monitor long running export or import jobs. Hope this helps !!

Manual Cleanup of CRS installation – Oracle CRS 10g R2 (10.2.0.1)

Some times we have to do a maual cleanup of failed CRS installation or some old CRS installation that was done. We have a automated way of cleaning up CRS as show below

1) Run rootdelete.sh script present in $ORA_CRS_HOME/install directory

2) Run rootdeinstall.sh script present in $ORA_CRS_HOME/install directory

If you have unknowingly deleted the CRS HOME before running the above scripts then you can follow below procedure to manually clean up the CRS.

Manual Cleanup of CRS on Linux

1. Remove following files as root user on ALL NODES IN CLUSTER

rm /etc/oracle/*
rm -f /etc/init.d/init.cssd 
rm -f /etc/init.d/init.crs 
rm -f /etc/init.d/init.crsd 
rm -f /etc/init.d/init.evmd 
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -f /etc/inittab.crs 
cp /etc/inittab.orig /etc/inittab

2. If they are not already down then kill off EVM, CRS and CSS process as root user on ALL NODES

ps -ef | grep crs    
kill <crs pid>
ps -ef | grep evm
kill <evm pid>
ps -ef | grep css    
kill <css pid>

3. Remove the files in /var/tmp/.oracle or /tmp/.oracle on ALL NODES

rm -f /var/tmp/.oracle/*
or
rm -f /tmp/.oracle/*

4. Remove the ocr.loc at /etc/oracle on ALL NODES

5. De-install the CRS home in the Oracle Universal Installer
This is to remove the CRS home from inventory. Installer may not be able to remove the files in the slot as CRS home is already deleted, but it will clean up the inventory.

6. Remove the CRS install location on ALL NODES
In case you have not removed the CRS install directory, please do so.

7. Remove OCR and Voting disk from shared location from any 1 node

Once above manual steps are done, the hosts are cleaned up and ready for next CRS installation.

Hope this helps !!

References:

Metalink Note ID : 239998.1