Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand Oracle transportable table space". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to understand Oracle transportable table space".

We have many means of data backup and migration in Oracle environment, such as exp/imp, expdp/impdp, rman and so on. Among these methods, transferable table space (Transportable Tablespace) has always been the fastest technical means of data migration in the traditional sense. Ideally, Transportable Tablespace can achieve the rate characteristics similar to those of direct transmission over the network.

1. Overview of Transportable Tablespace

Other traditional backup migration methods mostly follow the "extract-transfer-restore" mode. Take expdp as an example. Oracle uses special internal scheduling jobs to convert the data to be exported (Used Block) into dmp format files for storage. After that, it is transferred to Target Envionment through the network, and then restored to the new environment. The accelerated optimization of this pattern is mainly reflected in extraction and reduction, such as the use of parallel and other means.

Transportable Tablespace is not such a concept at all. Metaphorically, it is similar to the idea of Portable/Plugin. The tablespace of the same platform and character set can copy the data file directly to Target Environment. At the Oracle level, you only need to let the data dictionary know the metadata of the data.

Compared with other methods, the biggest advantage of Transportable Tablespace is that it does not need complicated extraction and reduction process, and the space requirement of the intermediate environment is very small. The following is demonstrated through a series of experiments.

2. Environmental preparation and prerequisites

Oracle Transportable Tablespace (hereinafter referred to as TTS) appeared very early. Traditional TTS has three basic limitations, which are:

U table space content self contained. One or more table spaces that we export at one time cannot have dependent objects in other table spaces. For example, we often distribute the data and indexes of a data table on different table spaces, so that if we use Transportable Tablespace, we must export all of these table spaces at once.

The Character Set and National Character Set of ü Source and Target DB must be exactly the same

U operating system Source Target DB requires compatibility. Note: in versions above 10g, this restriction has been lifted

We will introduce these constraints and checking methods more intuitively below. Due to the limitation of the author's environment, some experiments can only be carried out on one server. Oracle 11gR2 was selected as the specific experiment.

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

CORE 11.2.0.1.0 Production

-- construct two experimental table spaces

SQL > create tablespace ttstbl datafile size 10m autoextend on

2 extent management local uniform. Size 1m

3 segment space management auto

Tablespace created

SQL > create tablespace ttsind datafile size 10m autoextend on

2 extent management local uniform. Size 1m

3 segment space management auto

Tablespace created

-- Test users

SQL > create user test identified by test default tablespace ttstbl

User created

SQL > grant resource, connect to test

Grant succeeded

SQL > grant select_catalog_role to test

Grant succeeded

Use test users to create some objects in the tablespace.

SQL > conn test/test@ora11gp

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as test

SQL > create table t tablespace ttstbl as select * from dba_objects

Table created

SQL > create index idx_t_id on t (object_id) tablespace ttsind

Index created

3 、 Pre-Condition Check

As a prerequisite, determine the character set information for the Source database first.

SQL > select value from nls_database_parameters where parameter='NLS_CHARACTERSET'

VALUE

-

AL32UTF8

SQL > select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET'

VALUE

-

AL16UTF16

You want to export the newly created table spaces ttsind and ttstbl.

SQL > select file_name, tablespace_name from dba_data_files where tablespace_name like 'TTS%'

FILE_NAME TABLESPACE_NAME

-

/ u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf TTSTBL

/ u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf TTSIND

[oracle@bsplinux datafile] $ls-l

Total 2095500

(for reasons of space, omit part of the content)

-rw-r- 1 oracle oinstall 10493952 Nov 19 18:04 o1_mf_ttsind_8bmyjz69_.dbf

-rw-r- 1 oracle oinstall 20979712 Nov 19 17:59 o1_mf_ttstbl_8bmyjf3w_.dbf

-rw-r- 1 oracle oinstall 267395072 Nov 19 18:13 o1_mf_undotbs1_7vpyc2py_.dbf

-rw-r- 1 oracle oinstall 11804672 Nov 19 17:29 o1_mf_users_7vpyc2xd_.dbf

Among the traditional constraints, the operating system platform OS is an unavoidable factor when using TTS. Oracle divides the supporting platform into two large types: Big and Little. Free tablespace migration can be carried out between the same platform, while migration between different platforms is not allowed. Note: this limit is broken after Oracle 10g. We will elaborate on the details later.

We can determine whether the systems are compatible by querying v$transportable_platform.

SQL > col platform_name for A30

SQL > select platform_name, ENDIAN_FORMAT from v$transportable_platform

PLATFORM_NAME ENDIAN_FORMAT

Solaris [tm] OE (32-bit) Big

Solaris [tm] OE (64-bit) Big

Microsoft Windows IA (32-bit) Little

Linux IA (32-bit) Little

AIX-Based Systems (64-bit) Big

HP-UX (64-bit) Big

HP Tru64 UNIX Little

HP-UX IA (64-bit) Big

Linux IA (64-bit) Little

HP Open VMS Little

Microsoft Windows IA (64-bit) Little

IBM zSeries Based Linux Big

Linux x86 64-bit Little

Apple Mac OS Big

Microsoft Windows x86 64-bit Little

Solaris Operating System (x86) Little

IBM Power Based Linux Big

HP IA Open VMS Little

Solaris Operating System (x86-Little

64)

Apple Mac OS (x86-64) Little

20 rows selected

For your own database, you can query your own platform information in the v$database view.

SQL > select PLATFORM_ID, PLATFORM_NAME from v$database

PLATFORM_ID PLATFORM_NAME

10 Linux IA (32-bit)

After determining the platform compatibility, we need to determine whether the migrated tablespaces are "Self Contained" or not. The dbms_tts package method provided by Oracle for verification.

SQL > exec dbms_tts.transport_set_check ('ttstbl, ttsind',true)

PL/SQL procedure successfully completed

SQL > select * from transport_set_violations

VIOLATIONS

If there is an error in the validation, we can query the prompt in transport_set_violations. Only when the self contained problem is resolved can you continue with the following steps.

4. Environmental data export

To control the change, we need to set the tablespace to read-only. The specific statement is: alter tablespace xxx read only.

SQL > select tablespace_name, status from dba_tablespaces where tablespace_name like 'TTS%'

TABLESPACE_NAME STATUS

TTSIND READ ONLY

TTSTBL READ ONLY

Oracle TTS needs to use exp/expdp to export the metadata Metadata information of the tablespace to an dmp file, which is used to describe the tablespace information. Note that this process takes a short time, and the resulting dmp file is usually very small.

[root@bsplinux ~] # cd /

[root@bsplinux /] # mkdir transtts

[root@bsplinux /] # chown-R oracle:oinstall transtts/

[root@bsplinux /] # ls-l | grep transtts

Drwxr-xr-x 2 oracle oinstall 4096 Nov 19 18:19 transtts

[root@bsplinux /] #

Exp supports the TTS working method.

[oracle@bsplinux transtts] $exp userid=\ "/ as sysdba\" transport_tablespace=y tablespaces=ttstbl,ttsind file=ttsdmp.dmp log=res.log statistics=none

Export: Release 11.2.0.1.0-Production on Mon Nov 19 19:32:14 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace TTSTBL...

. Exporting cluster definitions

. Exporting table definitions

. . Exporting table T

For tablespace TTSIND...

. Exporting cluster definitions

. Exporting table definitions

. Exporting referential integrity constraints

. Exporting triggers

. End transportable tablespace metadata export

Export terminated successfully without warnings.

After that, we just need to copy the data file directly.

SQL > select'cp'| | file_name | |'/ transtts' from dba_data_files where tablespace_name like 'TTS%'

'CP' | | FILE_NAME | |'/ TRANSTTS'

Cp / u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf / transtts

Cp / u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf / transtts

[oracle@bsplinux transtts] $cp / u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf / transtts

Cp / u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf / transtts

[oracle@bsplinux transtts] $cp / u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf / transtts

[oracle@bsplinux transtts] $

[oracle@bsplinux transtts] $ls-l

Total 30796

-rw-r- 1 oracle oinstall 10493952 Nov 19 19:35 o1_mf_ttsind_8bmyjz69_.dbf

-rw-r- 1 oracle oinstall 20979712 Nov 19 19:35 o1_mf_ttstbl_8bmyjf3w_.dbf

-rw-r--r-- 1 oracle oinstall 724 Nov 19 19:33 res.log

-rw-r--r-- 1 oracle oinstall 16384 Nov 19 19:33 ttsdmp.dmp

In the / transtts directory, all the information that needs to be restored to the database is saved.

5. Data environment recovery

We need to transfer the data file to the target database server via FTP/SFTP. The author first restores the data environment because the same database is used.

SQL > drop tablespace ttstbl including contents and datafiles

Tablespace dropped

SQL > drop tablespace ttsind including contents and datafiles

Tablespace dropped

SQL > select file_name from dba_data_files where tablespace_name like 'TTS%'

FILE_NAME

The Source database no longer has TTS tablespaces. Next, we copy the data file to the new location and import the Metadata information.

[oracle@bsplinux transtts] $cp o1_mf_ttstbl_8bmyjf3w_.dbf / u01/app/oradata/ORA11G/datafile/

[oracle@bsplinux transtts] $cp o1_mf_ttsind_8bmyjz69_.dbf / u01/app/oradata/ORA11G/datafile/

[oracle@bsplinux datafile] $ls-l

Total 2095500

-rw-r- 1 oracle oinstall 10493952 Jul 3 03:48 mytesttbl01.dbf

-rw-r- 1 oracle oinstall 10493952 Jul 3 03:48 mytesttbl02.dbf

-rw-r- 1 oracle oinstall 104865792 Nov 19 17:29 o1_mf_rman_ts_87bx5kcg_.dbf

-rw-r- 1 oracle oinstall 838868992 Nov 19 19:46 o1_mf_sysaux_7vpyc2hb_.dbf

-rw-r- 1 oracle oinstall 807411712 Nov 19 19:43 o1_mf_system_7vpyc1x7_.dbf

-rw-r- 1 oracle oinstall 60825600 Nov 19 18:02 o1_mf_temp_7vpz05do_.tmp

-rw-r- 1 oracle oinstall 10493952 Nov 19 19:47 o1_mf_ttsind_8bmyjz69_.dbf

-rw-r- 1 oracle oinstall 20979712 Nov 19 19:46 o1_mf_ttstbl_8bmyjf3w_.dbf

-rw-r- 1 oracle oinstall 267395072 Nov 19 19:47 o1_mf_undotbs1_7vpyc2py_.dbf

-rw-r- 1 oracle oinstall 11804672 Nov 19 17:29 o1_mf_users_7vpyc2xd_.dbf

The Imp command imports.

[oracle@bsplinux transtts] $imp userid=\'/ as sysdba\ 'file=ttsdmp.dmp transport_tablespace=y tablespaces=ttsind,ttstbl datafiles=/u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf,/u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf

Import: Release 11.2.0.1.0-Production on Mon Nov 19 19:51:25 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

About to import transportable tablespace (s) metadata...

Import done in AL32UTF8 character set and AL16UTF16 NCHAR character set

. Importing SYS's objects into SYS

. Importing SYS's objects into SYS

. Importing TEST's objects into TEST

. . Importing table "T"

. Importing SYS's objects into SYS

Import terminated successfully without warnings.

[oracle@bsplinux transtts] $

After the metadata is imported, the imported tablespaces can be determined in Oracle.

SQL > select file_name,tablespace_name from dba_data_files where tablespace_name like 'TTS%'

FILE_NAME TABLESPACE_NAME

-

/ u01/app/oradata/ORA11G/datafile/o1_mf_ttsind_8bmyjz69_.dbf TTSIND

/ u01/app/oradata/ORA11G/datafile/o1_mf_ttstbl_8bmyjf3w_.dbf TTSTBL

SQL > conn test/test@ora11gp

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as test

SQL > select count (*) from t

COUNT (*)

-

72348

Note that the imported tablespace is still in read only state and needs to be turned on.

SQL > alter tablespace ttsind read write

Tablespace altered

SQL > alter tablespace ttstbl read write

Tablespace altered

Thank you for reading, the above is the content of "how to understand Oracle transportable tablespaces". After the study of this article, I believe you have a deeper understanding of how to understand Oracle transportable tablespaces, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.