Transportable Tablespaces in Oracle
Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles. In Oracle 8i one of the restrictions was that the block size of both databases must be the same. In Oracle 9i the introduction of multiple block sizes has removed this restriction. In this article I will run through a simple example of transporting a tablespace between two databases.
a) Setup
b) Using EXPORT & IMPORT utility
c) Cross-Platform Tablespace Conversions
d) Using RMAN TRANSPORT TABLESPACE: Basic Scenario
e) RMAN TRANSPORT TABLESPACE with UNTIL Time or SCN
f) Transport Tablespace with SET NEWNAME for Auxiliary Datafiles
a) Setup:
~~~~~~~~~
For this example I'm going to create a new tablespace, user and table to work with in the source database.
SQL> CONN / AS SYSDBA
CREATE TABLESPACE sample
DATAFILE '/u01/app/oradata/datafiles/sample01.dbf'
SIZE 1M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL;
CREATE USER sample_user IDENTIFIED BY sample_user
DEFAULT TABLESPACE sample
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON sample;
GRANT CREATE SESSION, CREATE TABLE TO sample_user;
CONN sample_user/sample_user;
CREATE TABLE sample_table (
sample_id NUMBER,
description VARCHAR2(50),
CONSTRAINT pk_sample_table PRIMARY KEY (sample_id)
);
INSERT /*+ APPEND */ INTO sample_table (sample_id, description)
SELECT level,
'Description for ' || level
FROM dual
CONNECT BY level <= 10000;
COMMIT;
b) Using EXPORT and IMPORT UTILITY
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A) Source Database:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure. The TS_LIST parameter accepts a comma separated list of tablespace names and the INCL_CONSTRAINTS parameter indicates if constraints should be included in the check.
SQL> CONN / AS SYSDBA
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'sample', incl_constraints => TRUE);
PL/SQL procedure successfully completed.
( if succeeded, then you can proceed)
The TRANSPORT_SET_VIOLATIONS view is used to check for any violations.
SELECT * FROM transport_set_violations;
no rows selected
Assuming no violations are produced we are ready to proceed by switching the tablespace to read only mode.
SQL> ALTER TABLESPACE sample READ ONLY;
Tablespace altered.
Next we export the tablespace metadata using the export (expdp or exp) utility. If you are using 10g or above you should use the expdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.
SQL> CONN / AS SYSDBA
SQL> CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
SQL> GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now export the tablespace metadata.
$ expdp userid=system/password
directory=temp_dir transport_tablespaces=sample
dumpfile=sample.dmp
logfile=sample_exp.log
If you are using a version prior to 10g/11g, you do not need the directory object and your command would look something like this.
$ exp userid='system/password as sysdba' transport_tablespace=y tablespaces=sample file=sample.dmp log=sample_exp.log
Copy the datafile to the appropriate location on the destination database server. Also copy the dump file to a suitable place on the destination database server. You may use binary FTP or SCP to perform this copy.
The source tablespace can now be switched back to read/write mode.
SQL> ALTER TABLESPACE sample READ WRITE;
Tablespace altered.
~~~~~~~~~~~~~~~~~~~~~~~~
B) Destination Database:
~~~~~~~~~~~~~~~~~~~~~~~~
Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.
SQL> CONN / AS SYSDBA
SQL> CREATE USER sample_user IDENTIFIED BY sample_user;
SQL> GRANT CREATE SESSION, CREATE TABLE TO sample_user;
Now we import the metadata into the destination database. If you are using 10g or above you should use the impdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.
SQL> CONN / AS SYSDBA
SQL> CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
SQL> GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now import the tablespace metadata.
$ impdp userid=system/password directory=temp_dir dumpfile=sample.dmp
logfile=sample_imp.log transport_datafiles='/u02/oradata/datafiles/sample01.dbf'
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.
$ imp usersample_id='system/password as sysdba' transport_tablespace=y datafiles='/u02/oradata/datafiles/sample01.dbf' tablespaces=sample file=sample.dmp log=sample_imp.log
Switch the new tablespace into read write mode.
SQL> ALTER TABLESPACE sample READ WRITE;
Tablespace altered.
SQL>The tablespace is now available in the destination database.
SELECT tablespace_name, plugged_in, status
FROM dba_tablespaces
WHERE tablespace_name = 'sample';
TABLESPACE_NAME PLU STATUS
--------------------- --- ---------
sample YES ONLINE
1 row selected.
SQL>
C) Cross-Platform Tablespace Conversions:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are transporting tablespaces between platforms you need to perform Cross-Platform Tablespace Conversions.
The CONVERT TABLESPACE allows tablespaces to be transported between platforms with different byte orders. The transportable tablespace mechanism is unchanged, this command merely converts the tablespace to allow the transport to work.
The platform of the source and destination platforms can be sample_identified using the V$TRANSPORTABLE_PLATFORM view. The platform of the local server is not listed as no conversion in necessary for a matching platform.
SQL> SELECT platform_name FROM v$transportable_platform;
PLATFORM_NAME
------------------------------------
Solaris[tm] OE (32-bit)
...
...
Microsoft Windows 64-bit for AMD
15 rows selected.The tablespace conversion can take place on either the source or the destination server. The following examples show how the command is used in each case.
# Conversion on a Solaris source host to a Linux destincation file.
CONVERT TABLESPACE my_tablespace
TO PLATFORM 'Linux IA (32-bit)'
FORMAT='/tmp/transport_linux/%U';
# Conversion on a Linux destination host from a Solaris source file.
CONVERT DATAFILE=
'/tmp/transport_solaris/my_ts_file01.dbf',
'/tmp/transport_solaris/my_ts_file02.dbf'
FROM PLATFORM 'Solaris[tm] OE (32-bit)'
DB_FILE_NAME_CONVERT
'/tmp/transport_solaris','/u01/oradata/MYDB';In the first example the converted files are placed in the directory specified by the FORMAT clause. In the second example the specified datafiles are converted to the local servers platform and placed in the correct directory specified by the DB_FILE_NAME_CONVERT clause.
d) Using RMAN TRANSPORT TABLESPACE: Basic Scenario
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using this scenario, the AUXILIARY DESTINATION clause to be used, which causes RMAN to use default values that work for most cases in managing the auxiliary instance. Only required options are specified.
Note : Oracle recommends that you use an auxiliary destination with TRANSPORT TABLESPACE to simplify management of auxiliary instance files.
1) Requirements:
>> The process described here is only one part of the process of transporting tablespaces. Before you use TRANSPORT TABLESPACE, you must meet the requirements.
>> Confirm that tablespace transport is supported between your source and destination platforms.
Use below view to verify:
SQL>select platform_name,endian_format from v$transportable_platform ;
"little" endian format allowed to transport into "big" endian format.
>> Identify a self-contained set of tablespaces to include in the transportable set
2) To use RMAN TRANSPORT TABLESPACE:
Start RMAN client, connect to the source database and, if used, the recovery catalog. Then enter the TRANSPORT TABLESPACE command, specifying the required arguments.
For example, to transport the tablespaces sample2 and sample3, use the TRANSPORT TABLESPACE command as follows:
RMAN> transport tablespace sample2, sample3
tablespace destination '/u01/app/oradata/datafiles'
auxiliary destination '/u02/oradata/datafiles';
Wait TRANSPORT TABLESPACE command to completes,
the following outputs result:
i) The transportable set datafiles are left in the location /u01/app/oradata/datafiles with their original names.
ii) The Data Pump Export dump file for the transportable set is named dmpfile.dmp, the export log is named explog.log and the sample import script is named impscrpt.sql. All are created in the tablespace destination /u02/oradata/datafiles.
Note:
If there is already a file under the name of the export dump file in the tablespace destination, then
>> TRANSPORT TABLESPACE fails when it calls Data Pump Export. If repeating a previous TRANSPORT TABLESPACE operation, make sure you delete the previous output files, including the export dump file.
>> The auxiliary set files are removed from /u02/oradata/datafiles.
>> You can now return to the process for transporting tablespaces again.
f) RMAN TRANSPORT TABLESPACE with UNTIL Time or SCN:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you specify a target time with the TRANSPORT TABLESPACE command, then during the tablespace transport operation, RMAN will restore the tablespace at the auxiliary instance using backups from prior to the target time and perform point-in-time recovery on the auxiliary database to the specified target time. (Backups and redo logs needed for this point-in-time recovery must be available.)
The target time can be specified using an SCN (in the current incarnation or its ancestors), log sequence number, restore point or time expression. For example:
TRANSPORT TABLESPACE sample
TABLESPACE DESTINATION '/u01/app/oradata/datafiles'
AUXILIARY DESTINATION '/u02/oradata/datafiles'
UNTIL SCN 25433932989;
TRANSPORT TABLESPACE sample
TABLESPACE DESTINATION '/u01/app/oradata/datafiles'
AUXILIARY DESTINATION '/u02/oradata/datafiles'
UNTIL RESTORE POINT 'last_upgrade';
TRANSPORT TABLESPACE sample
TABLESPACE DESTINATION '/u01/app/oradata/datafiles'
AUXILIARY DESTINATION '/u02/oradata/datafiles'
UNTIL TIME 'SYSDATE-1';
f) Transport Tablespace with SET NEWNAME for Auxiliary Datafiles
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SET NEWNAME command can be used in a RUN block to specify filenames for use in TRANSPORT TABLESPACE
Example:
RMAN>
RUN {
SET NEWNAME FOR DATAFILE '/u01/app/oradata/datafiles/sample01.dbf'
TO '/u02/oradata/datafiles/sample01.dbf';
SET NEWNAME FOR DATAFILE '/u01/app/oradata/datafiles/sample02.dbf'
TO '/u02/oradata/datafiles/sample02.dbf';
TRANSPORT TABLESPACE SAMPLE
TABLESPACE DESTINATION '/u01/app/oradata/datafiles'
AUXILIARY DESTINATION '/u02/oradata/datafiles';
}
The SET NEWNAME commands cause these auxiliary instance datafiles to be restored to the locations named instead of /u02/oradata/datafiles.
Note:
SET NEWNAME is best used with one-time operations. If you expect to create transportable tablespaces from backup regularly for a particular set of tablespaces, consider using CONFIGURE AUXNAME instead of SET NEWNAME in order to make persistent settings for the location of the auxiliary instance datafiles.
So many ways are there to move a tablespace within platform/ cross-platform.
...Please fee to post comment...
Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles. In Oracle 8i one of the restrictions was that the block size of both databases must be the same. In Oracle 9i the introduction of multiple block sizes has removed this restriction. In this article I will run through a simple example of transporting a tablespace between two databases.
a) Setup
b) Using EXPORT & IMPORT utility
c) Cross-Platform Tablespace Conversions
d) Using RMAN TRANSPORT TABLESPACE: Basic Scenario
e) RMAN TRANSPORT TABLESPACE with UNTIL Time or SCN
f) Transport Tablespace with SET NEWNAME for Auxiliary Datafiles
a) Setup:
~~~~~~~~~
For this example I'm going to create a new tablespace, user and table to work with in the source database.
SQL> CONN / AS SYSDBA
CREATE TABLESPACE sample
DATAFILE '/u01/app/oradata/datafiles/sample01.dbf'
SIZE 1M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL;
CREATE USER sample_user IDENTIFIED BY sample_user
DEFAULT TABLESPACE sample
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON sample;
GRANT CREATE SESSION, CREATE TABLE TO sample_user;
CONN sample_user/sample_user;
CREATE TABLE sample_table (
sample_id NUMBER,
description VARCHAR2(50),
CONSTRAINT pk_sample_table PRIMARY KEY (sample_id)
);
INSERT /*+ APPEND */ INTO sample_table (sample_id, description)
SELECT level,
'Description for ' || level
FROM dual
CONNECT BY level <= 10000;
COMMIT;
b) Using EXPORT and IMPORT UTILITY
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A) Source Database:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure. The TS_LIST parameter accepts a comma separated list of tablespace names and the INCL_CONSTRAINTS parameter indicates if constraints should be included in the check.
SQL> CONN / AS SYSDBA
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'sample', incl_constraints => TRUE);
PL/SQL procedure successfully completed.
( if succeeded, then you can proceed)
The TRANSPORT_SET_VIOLATIONS view is used to check for any violations.
SELECT * FROM transport_set_violations;
no rows selected
Assuming no violations are produced we are ready to proceed by switching the tablespace to read only mode.
SQL> ALTER TABLESPACE sample READ ONLY;
Tablespace altered.
Next we export the tablespace metadata using the export (expdp or exp) utility. If you are using 10g or above you should use the expdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.
SQL> CONN / AS SYSDBA
SQL> CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
SQL> GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now export the tablespace metadata.
$ expdp userid=system/password
directory=temp_dir transport_tablespaces=sample
dumpfile=sample.dmp
logfile=sample_exp.log
If you are using a version prior to 10g/11g, you do not need the directory object and your command would look something like this.
$ exp userid='system/password as sysdba' transport_tablespace=y tablespaces=sample file=sample.dmp log=sample_exp.log
Copy the datafile to the appropriate location on the destination database server. Also copy the dump file to a suitable place on the destination database server. You may use binary FTP or SCP to perform this copy.
The source tablespace can now be switched back to read/write mode.
SQL> ALTER TABLESPACE sample READ WRITE;
Tablespace altered.
~~~~~~~~~~~~~~~~~~~~~~~~
B) Destination Database:
~~~~~~~~~~~~~~~~~~~~~~~~
Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.
SQL> CONN / AS SYSDBA
SQL> CREATE USER sample_user IDENTIFIED BY sample_user;
SQL> GRANT CREATE SESSION, CREATE TABLE TO sample_user;
Now we import the metadata into the destination database. If you are using 10g or above you should use the impdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server.
SQL> CONN / AS SYSDBA
SQL> CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
SQL> GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
We can now import the tablespace metadata.
$ impdp userid=system/password directory=temp_dir dumpfile=sample.dmp
logfile=sample_imp.log transport_datafiles='/u02/oradata/datafiles/sample01.dbf'
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.
$ imp usersample_id='system/password as sysdba' transport_tablespace=y datafiles='/u02/oradata/datafiles/sample01.dbf' tablespaces=sample file=sample.dmp log=sample_imp.log
Switch the new tablespace into read write mode.
SQL> ALTER TABLESPACE sample READ WRITE;
Tablespace altered.
SQL>The tablespace is now available in the destination database.
SELECT tablespace_name, plugged_in, status
FROM dba_tablespaces
WHERE tablespace_name = 'sample';
TABLESPACE_NAME PLU STATUS
--------------------- --- ---------
sample YES ONLINE
1 row selected.
SQL>
C) Cross-Platform Tablespace Conversions:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you are transporting tablespaces between platforms you need to perform Cross-Platform Tablespace Conversions.
The CONVERT TABLESPACE allows tablespaces to be transported between platforms with different byte orders. The transportable tablespace mechanism is unchanged, this command merely converts the tablespace to allow the transport to work.
The platform of the source and destination platforms can be sample_identified using the V$TRANSPORTABLE_PLATFORM view. The platform of the local server is not listed as no conversion in necessary for a matching platform.
SQL> SELECT platform_name FROM v$transportable_platform;
PLATFORM_NAME
------------------------------------
Solaris[tm] OE (32-bit)
...
...
Microsoft Windows 64-bit for AMD
15 rows selected.The tablespace conversion can take place on either the source or the destination server. The following examples show how the command is used in each case.
# Conversion on a Solaris source host to a Linux destincation file.
CONVERT TABLESPACE my_tablespace
TO PLATFORM 'Linux IA (32-bit)'
FORMAT='/tmp/transport_linux/%U';
# Conversion on a Linux destination host from a Solaris source file.
CONVERT DATAFILE=
'/tmp/transport_solaris/my_ts_file01.dbf',
'/tmp/transport_solaris/my_ts_file02.dbf'
FROM PLATFORM 'Solaris[tm] OE (32-bit)'
DB_FILE_NAME_CONVERT
'/tmp/transport_solaris','/u01/oradata/MYDB';In the first example the converted files are placed in the directory specified by the FORMAT clause. In the second example the specified datafiles are converted to the local servers platform and placed in the correct directory specified by the DB_FILE_NAME_CONVERT clause.
d) Using RMAN TRANSPORT TABLESPACE: Basic Scenario
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using this scenario, the AUXILIARY DESTINATION clause to be used, which causes RMAN to use default values that work for most cases in managing the auxiliary instance. Only required options are specified.
Note : Oracle recommends that you use an auxiliary destination with TRANSPORT TABLESPACE to simplify management of auxiliary instance files.
1) Requirements:
>> The process described here is only one part of the process of transporting tablespaces. Before you use TRANSPORT TABLESPACE, you must meet the requirements.
>> Confirm that tablespace transport is supported between your source and destination platforms.
Use below view to verify:
SQL>select platform_name,endian_format from v$transportable_platform ;
"little" endian format allowed to transport into "big" endian format.
>> Identify a self-contained set of tablespaces to include in the transportable set
2) To use RMAN TRANSPORT TABLESPACE:
Start RMAN client, connect to the source database and, if used, the recovery catalog. Then enter the TRANSPORT TABLESPACE command, specifying the required arguments.
For example, to transport the tablespaces sample2 and sample3, use the TRANSPORT TABLESPACE command as follows:
RMAN> transport tablespace sample2, sample3
tablespace destination '/u01/app/oradata/datafiles'
auxiliary destination '/u02/oradata/datafiles';
Wait TRANSPORT TABLESPACE command to completes,
the following outputs result:
i) The transportable set datafiles are left in the location /u01/app/oradata/datafiles with their original names.
ii) The Data Pump Export dump file for the transportable set is named dmpfile.dmp, the export log is named explog.log and the sample import script is named impscrpt.sql. All are created in the tablespace destination /u02/oradata/datafiles.
Note:
If there is already a file under the name of the export dump file in the tablespace destination, then
>> TRANSPORT TABLESPACE fails when it calls Data Pump Export. If repeating a previous TRANSPORT TABLESPACE operation, make sure you delete the previous output files, including the export dump file.
>> The auxiliary set files are removed from /u02/oradata/datafiles.
>> You can now return to the process for transporting tablespaces again.
f) RMAN TRANSPORT TABLESPACE with UNTIL Time or SCN:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If you specify a target time with the TRANSPORT TABLESPACE command, then during the tablespace transport operation, RMAN will restore the tablespace at the auxiliary instance using backups from prior to the target time and perform point-in-time recovery on the auxiliary database to the specified target time. (Backups and redo logs needed for this point-in-time recovery must be available.)
The target time can be specified using an SCN (in the current incarnation or its ancestors), log sequence number, restore point or time expression. For example:
TRANSPORT TABLESPACE sample
TABLESPACE DESTINATION '/u01/app/oradata/datafiles'
AUXILIARY DESTINATION '/u02/oradata/datafiles'
UNTIL SCN 25433932989;
TRANSPORT TABLESPACE sample
TABLESPACE DESTINATION '/u01/app/oradata/datafiles'
AUXILIARY DESTINATION '/u02/oradata/datafiles'
UNTIL RESTORE POINT 'last_upgrade';
TRANSPORT TABLESPACE sample
TABLESPACE DESTINATION '/u01/app/oradata/datafiles'
AUXILIARY DESTINATION '/u02/oradata/datafiles'
UNTIL TIME 'SYSDATE-1';
f) Transport Tablespace with SET NEWNAME for Auxiliary Datafiles
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SET NEWNAME command can be used in a RUN block to specify filenames for use in TRANSPORT TABLESPACE
Example:
RMAN>
RUN {
SET NEWNAME FOR DATAFILE '/u01/app/oradata/datafiles/sample01.dbf'
TO '/u02/oradata/datafiles/sample01.dbf';
SET NEWNAME FOR DATAFILE '/u01/app/oradata/datafiles/sample02.dbf'
TO '/u02/oradata/datafiles/sample02.dbf';
TRANSPORT TABLESPACE SAMPLE
TABLESPACE DESTINATION '/u01/app/oradata/datafiles'
AUXILIARY DESTINATION '/u02/oradata/datafiles';
}
The SET NEWNAME commands cause these auxiliary instance datafiles to be restored to the locations named instead of /u02/oradata/datafiles.
Note:
SET NEWNAME is best used with one-time operations. If you expect to create transportable tablespaces from backup regularly for a particular set of tablespaces, consider using CONFIGURE AUXNAME instead of SET NEWNAME in order to make persistent settings for the location of the auxiliary instance datafiles.
So many ways are there to move a tablespace within platform/ cross-platform.
...Please fee to post comment...
No comments:
Post a Comment