Shulou(Shulou.com)06/01 Report--

I. brief introduction

The biggest advantage of transferable tablespaces (and a set) is that they are much faster than export/import or unload/load. Because transferable tablespaces mainly copy data files to the target path, and then use applications such as export/import or Data Pump export/import to export / import only the metadata of tablespace objects to the new database.

(1) the transferable tablespace is mainly used for database-to-library tablespace replication, and the tablespace to be transferred must be placed in read-only mode. If the production library does not allow tablespaces to be set to read-only mode, there are ways to create transferable tablespace sets through RMAN backups.

(2) to use the transportable tablespace feature, oracle is at least 8i Enterprise Edition or later. If the same operating system platform is imported from each other, 8i or above can be supported, but if it is a different operating system platform, the database version is at least 10g.

(3) the transferred tablespace can be either dictionary-managed or locally managed. And since oracle9i, the block size of the transmitted tablespace can be different from the block size of the target database.

Some operating systems, including Windows, store multi-byte binary data in low-level memory addresses with the lowest significant bytes; therefore, such systems are called low-address low-byte order. In contrast, other operating systems, including Solaris, store the most significant bytes in low-order memory addresses, so this system is called low-address high-byte order. When a low-address, high-byte order system tries to read data from a low-address, low-byte order system, a conversion process is required-otherwise, the byte order will cause the read data to not be interpreted correctly. However, when transferring tablespaces between platforms in the same byte order, no conversion is required.

If the endian is different between platforms, you must first convert the tablespaces of the source platform to the target format before performing the import operation, and if the endian format is the same between platforms, you can skip the conversion step, even on different platforms. Of course, the premise is that the database version of each platform is not less than 10g.

Before the tablespace can be transferred to different platforms, the header of the data file must be able to identify the original platform to which it belongs. After setting the oracle database initialization parameters to 10.0.0 or higher, you must set the tablespace to read-write at least once, which is very important.

The tablespace transfer is exported and imported through the logical export command exp/imp of oracle to add the parameter transport_tablespace=y.

1.1. There are the following restrictions on performing tablespace transfer:

(1) the source and target databases must be on the same platform. After 10g, you can use the RMAN command to modify the data file to move the tablespace across platforms.

(2) for different versions of the source database and the target database, the version of the source database must be lower than that of the target database.

(3) the database character set and the national character set set on both sides must be the same.

(4) the target database cannot have tablespaces to be imported.

(5) the blocksize of the source database and the target database must be the same (before 9i).

Note:

Starting with oracle10gR2, you can transfer tablespaces with XMLTypes, but you must use imp/exp instead of selecting a data pump (impdp/expdp). And make sure that the CONSTRAINTS and TRIGGERS parameters of the imp/exp command are set to Y.

The following script lists which tablespaces in the database 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

1.2. Some restrictions on transport tablespaces:

(1) Advanced queuing (Advanced Queues): the transportable tablespace feature does not support 8.0 compatible advanced queues.

(2) system tablespaces (SYSTEM Tablespace Objects): objects owned by SYSTEM tablespaces or SYS users cannot be transferred.

(3) Mapping types (Opaque Types): types specified by the application and mapped to a database (such as RAW,BFILE, etc.) can be transferred, but they are not used as part of a cross-platform transformation. Only the application that specifies its actual type is clear, so you must make sure that the application solves the problem of reading various endian before considering transferring it to the new platform.

(4) floating point data (Floating-Point Numbers): BINARY_FLOAT and BINARY_DOUBLE types can be imported and exported using the data pump, but EXP is not supported.

Note:

For cross-platform transfers, query the V$TRANSPORTABLE_PLATFORM view to check the endian format of both platforms. If you are going to transfer tablespace sets to different endian platforms, you must first convert the tablespace set to the same endian as the target platform. As mentioned earlier, if the transformation of endian is involved, it can be done when the transferable tablespace set is generated (by the source platform) or before the tablespace set is imported (by the target platform). The conversion command is all one, but the parameters are slightly different, and the usage of the command is not mentioned for the time being, but there will be a special chapter to introduce and practice. Here, let's first delve into where this operation is appropriate. It is said that this operation can be performed anywhere, and there should be no dispute, but the attention of the value is for the database we operate, especially for the running production database. It is recommended that you use the conversion operation on the target platform for the following reasons:

(1) reduce downtime

Before creating a transportable tablespace set, you need to set the state of the tablespace to be transferred to read-only. You can quickly copy a mirror of the data file, generate an export file containing metadata, and then set the tablespace state to read-write. This arrangement makes it necessary to keep the tablespace as read-only as possible.

(2) improve execution performance

Production database is often dealing with multiple applications at the same time, but if it is used for conversion operation at this time, it may increase the load of the system, and the conversion efficiency is not the highest because the system performs more tasks at this time. Moving the conversion operation to the target platform usually acts as a backup role for the target platform, the system load is relatively low, and the efficiency of performing the conversion operation will be relatively higher.

(3) simplify replication operation

The operation of replication is simplified by directly converting the data file to the target directory by specifying the db_file_name_convert parameter when the conversion command is executed on the target platform.

Second, the steps of tablespace transmission:

2.1. The tablespace that needs to be moved must be self-contained.

If the data in the tablespace has a reference relationship with the data in other tablespaces, then when moving the tablespace, all the related tablespaces need to be moved together. 、

You can check whether it is a self-contained tablespace with the following command:

SQL > execute dbms_tts.transport_set_check (ts_list= >'', incl_constraints= > TRUE)

The parameter ts_list specifies the tablespace to be transferred, and incl_constraints specifies whether to check for integrity constraints. After execution, check the temporary table transport_set_violations. If no information is returned, the tablespace is self-contained.

2.2. Set the tablespace to be transferred to read-only. (if there is a way to generate a database, you can use the rman function.)

SQL > alter tablespace XXX read only

If the production library does not allow tablespaces to be read-only, you can choose to generate tablespace sets through RMAN backup.

Connect to the database as sysdba and perform exp export.

C: > exp userid='sys/sys@jiong as sysdba' file=d:\ testspace.dmp log=d:\ testspaceexp.log transport_tablespace=y tablespaces=testspace buffer=10240000

Only the directory structure information (metadata) of the exported table space to be transferred does not contain the actual data, so the export speed is very fast, and the file is also very small.

2.4. use RMAN to convert the data file header of the table space to be transferred to the target system file (when transferred on different platforms under 10g)

This transformation can be done in either the source database or the target database.

RMAN > convert tablespace "TESTSPACE" to platform 'Microsoft Windows IA (32-bit)' format'd:\ TESTSPACE01.DBF'

Remember to put the tablespace back to writable at the end of this step:

Alter tablespace TESTSPACE read write

The platform parameter can be obtained by querying v$db_transportable_platform.

(1) v$db_transportable_platform

V$DB_TRANSPORTABLE_PLATFORM displays all platforms to which the database can be transported using the RMAN CONVERT DATABASE command. The transportable database feature only supports transports of the same endian platform. Therefore, V$DB_TRANSPORTABLE_PLATFORM displays fewer rows than V$TRANSPORTABLE_PLATFORM.

(2) v$transportable_platform

V$TRANSPORTABLE_PLATFORM displays all platform names and their identifiers

2.5. Copy the tablespace converted data file and export the file to the target platform

2.6. import tablespaces using imp

It should be noted that in the target database, the user to which the object belongs in the original tablespace needs to be created first, but the tablespace with the same name cannot be used, so this user will be established on other tablespaces.

Also, if the transferred table space set block_size is different from the default block_size of the target library, then your first step is to set the initialization parameters (above 9i) of DB_nK_CACHE_SIZE in the target library.

C: > imp userid='sys/sys@mood as sysdba' file=d:\ testspace.dmp log=d:\ testspaceimp.log tablespaces=testspace datafiles=d:\ TESTSPACE01.DBF transport_tablespace=y

Change the user default tablespace to the imported tablespace:

Alter user TUSER default tablespace TESTSPACE

If necessary, make the table space imported into the target database writable because it is also in the read only state after import.

Alter tablespace TESTSPACE read write

# # #

Create a transport tablespace test

#

I. Test environment

Source side:

Operating system: OracleLinux 6.264 bit

Endianness format: little

Database version: 11.2.0.3

Target side:

Operating system: OracleLinux 6.264 bit

Endianness format: little

Database version: 11.2.0.3

2. Start testing

2.1. Create a test tablespace on the source side

SQL > select tablespace_name, status from dba_tablespaces TABLESPACE_NAME STATUS-SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMPTS1 ONLINE USERS ONLINE OUTLN ONLINE 6 rows selected. SQL > select file_name from dba_data_files FILE_NAME-/ u01/app/oracle/oradata/normal/system01.dbf / u01/app/oracle/oradata/normal/undotbs01.dbf / u01/app/oracle/oradata/normal/sysaux01.dbf / u01/app/oracle/oradata/normal/users01.dbf / u01/app/ Oracle/oradata/normal/undotbs02.dbf / u01/app/oracle/oradata/normal/system02.dbf / u01/app/oracle/oradata/normal/outln01.dbf 7 rows selected.

2.2.Create tablespace create tablespace tset

SQL > create tablespace tset datafile'/ u01 size Tablespace created.

Create a user source_test and specify a tablespace

On the source side SQL > create user source_test identified by oracle default tablespace tset temporary tablespace TEMPTS1; SQL > grant connect,resource to source_test; Grant succeeded. -- on the target side (only users are created first) SQL > create user target_test identified by oracle temporary tablespace TEMPTS1; SQL > grant connect,resource to target_test

2.4. Create a test table

SQL > conn source_test/oracle SQL > create table T1 (id number, name varchar2 (30)); SQL > insert into T1 values (1, 'AAAAA'); SQL > insert into T1 values (2,' BBBBB'); SQL > commit; Commit complete. SQL > select * from T1; ID NAME-1 AAAAA 2 BBBBB

3. Create backup directories on source side and target side

[oracle@normal] $mkdir-p / u01/backup [oracle@normal ~] $ls-l / U01 total 24 drwxr-xr-x 3 oracle oinstall 4096 Jul 28 12:31 app drwxr-xr-x 2 oracle oinstall 4096 Sep 14 16:21 backup SQL > show user USER is "SYS" SQL > create directory backup as'/ u01 lap backups; Directory created. SQL > col owner format A5 SQL > col directory_name format A25 SQL > col DIRECTORY_PATH format A50 SQL > select * from dba_directories OWNER DIRECTORY_NAME DIRECTORY_PATH-SYS BACKUP / u01/backup SYS OUTLN_DIR / home/oracle SYS DATA_PUMP_DIR / u01/app/oracle/product/11.2.0/db_1/rdbms/log/ SYS ORACLE_OCM_CONFIG_DIR / u01/app/oracle/product/11.2.0/db_1/ccr/state SQL > GRANT read Write ON DIRECTORY backup TO source_test Grant succeeded.

3.1, on the target side

[oracle@test] $mkdir-p / u01/backup [oracle@test ~] $ls-l / U01 total 24 drwxr-xr-x 3 oracle oinstall 4096 Aug 28 09:09 app drwxr-xr-x 2 oracle oinstall 4096 Sep 14 16:40 backup SQL > show user USER is "SYS" SQL > create directory backup as'/ u01 lap backups; Directory created. SQL > col owner format A5 SQL > col directory_name format A25 SQL > col DIRECTORY_PATH format A50 SQL > select * from dba_directories OWNER DIRECTORY_NAME DIRECTORY_PATH-SYS BACKUP / u01/backup SYS OUTLN_DIR / home/oracle SYS DATA_PUMP_DIR / u01/app/oracle/product/11.2.0/db_1/rdbms/log/ SYS ORACLE_OCM_CONFIG_DIR / u01/app/oracle/product/11.2.0/db_1/ccr/state SQL > GRANT read Write ON DIRECTORY backup TO target_test Grant succeeded.

4. Check the self-inclusion of the tablespace (that is, change the data in the tablespace to be not associated with other tablespace data, if the relevant association reports an error)

SQL > execute dbms_tts.transport_set_check ('TSET', TRUE); PL/SQL procedure successfully completed.

-- View the results of self-contained verification:

SQL > select * from transport_set_violations

-- No record means there's nothing wrong.

5. Set the tablespace TSET to read--only

SQL > alter tablespace TSET read only; SQL > select tablespace_name, status from dba_tablespaces TABLESPACE_NAME STATUS-SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMPTS1 ONLINE USERS ONLINE OUTLN ONLINE TSET READ ONLY 7 rows selected.

6. Generate: Transportable Tablespace Set

Transportable Tablespace Set has two parts:

Metadata of tablespace exported by 1.expdp

two。 There is also the data file corresponding to the tablespace.

6.1. metadata of table space exported by expdp

[oracle@normal normal] $pwd / u01/app/oracle/oradata/normal [oracle@normal normal] $ll total 2294664-rw-r- 1 oracle oinstall 9781248 Sep 14 16:46 control01.ctl drwx- 2 oracle oinstall 16384 Aug 22 12:44 lost+found-rw-r- 1 oracle oinstall 20979712 Sep 14 15:52 outln01.dbf-rw-r- 1 oracle oinstall 52429312 Sep 14 16:45 redo01a.log-rw- R-1 oracle oinstall 52429312 Sep 14 16:45 redo01b.log-rw-r- 1 oracle oinstall 52429312 Sep 14 15:52 redo02a.log-rw-r- 1 oracle oinstall 52429312 Sep 14 15:52 redo02b.log-rw-r- 1 oracle oinstall 52429312 Sep 14 15:52 redo03a.log-rw-r- 1 oracle oinstall 52429312 Sep 14 15:52 redo03b.log-rw-r--r-- 1 oracle oinstall 22633 Aug 22 17:00 su.lst-rw-r- 1 oracle oinstall 340795392 Sep 14 16:40 sysaux01.dbf-rw-r- 1 oracle oinstall 340795392 Sep 14 16:43 system01.dbf-rw-r- 1 oracle oinstall 314580992 Sep 14 16:43 system02.dbf-rw-r- 1 oracle oinstall 20979712 Sep 14 15:53 temp01.dbf-rw-r- 1 oracle oinstall 52436992 Sep 14 15:53 temp02.dbf -rw-r- 1 oracle oinstall 52436992 Sep 14 16:31 test01.dbf-rw-r- 1 oracle oinstall 209723392 Sep 14 16:43 undotbs01.dbf-rw-r- 1 oracle oinstall 209723392 Sep 14 16:40 undotbs02.dbf-rw-r- 1 oracle oinstall 524296192 Sep 14 15:52 users01.dbf [oracle@normal normal] $expdp dumpfile=test01.dmp directory=backup transport_tablespaces=TSET transport_full_check=y logfile=TSET.log Export: Release 11. 2.0.3.0-Production on Sun Sep 14 16:54:30 2014 Copyright (c) 1982 2011, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, OLAP Data Mining and Real Application Testing options Starting "SYS". "SYS_EXPORT_TRANSPORTABLE_01": / * / AS SYSDBA dumpfile=test01.dmp directory=backup transport_tablespaces=TSET transport_full_check=y logfile=TSET.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYS". "SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded * * * Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is: / u01/backup/test01.dmp * * * Datafiles required for transportable tablespace TSET: / u01/app/oracle/oradata/normal/test01.dbf Job "SYS". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:55:13 [oracle@normal normal] $ls-1 / u01/backup/ total 80-rw-r-- -1 oracle oinstall 77824 Sep 14 16:55 test01.dmp-rw-r--r-- 1 oracle oinstall 1160 Sep 14 16:55 TSET.log

7. Transfer Transportable Tablespace set to Target

1) copy the data file corresponding to the tablespace test to the ORADATA directory corresponding to Target.

2) copy the tablespace metadta data exported by expdp to the backup directory on the Target side

-- copy the data file corresponding to the tablespace test to the ORADATA directory corresponding to Target. [oracle@normal normal] $scp / u01/backup/test01.dmp 192.168.137.12:/u01/backup oracle@192.168.137.12 s password: test01.dmp 100% 76KB 76.0KB/s 00:00-- copy the tablespace metadta data exported by expdp to [oracle@normal normal] $scp test01.dbf 192.168 under the backup directory on the Target side. 137.12:/u01/app/oracle/oradata/normal/test01.dbf oracle@192.168.137.12 s password: test01.dbf 100% 50MB 16.7MB/s 00:03-check on the target side to see if the file has been transferred [oracle@test ~] $ll / u01/backup/ total 76-rw-r- 1 oracle oinstall 77824 Sep 14 17:03 test01.dmp [oracle@test ~] $ll $ORACLE_BASE/oradata/normal/test01.dbf-rw-r- 1 oracle oinstall 52436992 Sep 14 17:04 / u01/app/oracle/oradata/normal/test01.dbf

8. Metadata for Import tablespaces on Target systems (for target_test users, remap_schema is required)

[oracle@test] $impdp directory=backup dumpfile=test01.dmp transport_datafiles=/u01/app/oracle/oradata/normal/test01.dbf remap_schema=source_test:target_test logfile=test.log Import: Release 11.2.0.3.0-Production on Sun Sep 14 17:09:25 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production With the Partitioning, OLAP Data Mining and Real Application Testing options Master table "SYS". "SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYS". "SYS_IMPORT_TRANSPORTABLE_01": / * / AS SYSDBA directory=backup dumpfile=test01.dmp transport_datafiles=/u01/app/oracle/oradata/normal/test01.dbf remap_schema=source_test:target_test logfile=test.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYS". "SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 17:09:55 9, View and modify tablespace status SQL > select tablespace_name Status from dba_tablespaces TABLESPACE_NAME STATUS-SYSTEM ONLINE UNDOTBS1 ONLINE SYSAUX ONLINE TEMPTS1 ONLINE USERS ONLINE OUTLN ONLINE TSET READ ONLY 7 rows selected. SQL > alter tablespace TSET read write; Tablespace altered.

10. Verification

SQL > conn target_test/oracle

Connected.

SQL > select * from T1

ID NAME

1 AAAAA

2 BBBBB

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.