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

This article shows you how to understand Oracle transferable tablespaces, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Finally, let's talk about the use of TTS under ASM. It should be said that ASM is not only an important component of Oracle at the storage level, but also a key solution to achieve redundancy and balanced IO at the software level. For 11gR2 RAC, ASM is an important component to replace bare devices.

An important feature of the application of ASM is that files no longer belong to the category of OS file system management, but belong to Oracle ASM Instance management. Data file redundancy, IO dispersion, load balancing and even operation are all coordinated by Database Server Instance and ASM Intance. We can't get into it from the operating system. What do we need to do if we want to use TTS?

8. ASM side is configured to use

The goal of this migration is to migrate between Linux platforms, with ASM storage on one side of Source and traditional file system storage on the other.

The first is the Source environment configuration information.

SQL > select * from v$version

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

SQL > select name, platform_name from v$database

NAME PLATFORM_NAME

-

ORA11GAS Linux IA (32-bit)

SQL > select file_name, tablespace_name from dba_data_files where tablespace_name='TTS_SIMPLE'

FILE_NAME TABLESPACE_NAME

-

+ DATA/ora11gasm/datafile/tts_simple.267.799910245 TTS_SIMPLE

The data file is saved in + DATA Disk Group stored in ASM. To experiment, create some objects in the tablespace.

SQL > create user tts_simple identified by tts_simple default tablespace tts_simple

User created

SQL > grant resource, connect to tts_simple

Grant succeeded

SQL > conn tts_simple/tts_simple@ora11gasm

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Connected as tts_simple

SQL > create table t as select * from all_objects

Table created

SQL > select count (*) from t

COUNT (*)

-

56289

Self Contained check.

SQL > exec dbms_tts.transport_set_check ('tts_simple',true)

PL/SQL procedure successfully completed

SQL > select * from transport_set_violations

VIOLATIONS

SQL > alter tablespace tts_simple read only

Tablespace altered

To export metadata using expdp, you first need to create a directory object.

[root@bspdev /] # ls-l | grep ttstest

Drwxr-xr-x 2 oracle oinstall 4096 Nov 21 05:24 ttstest

[root@bspdev /] #

SQL > create directory ttsdmp as'/ ttstest'

Directory created

Export the metadata dmp package.

[oracle@bspdev ttstest] $expdp userid=\'/ as sysdba\ 'transport_tablespaces=tts_simple dumpfile=tts_simple.dmp directory=ttsdmp transport_full_check=y

Export: Release 11.2.0.3.0-Production on Wed Nov 21 05:31:04 2012

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

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

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

Starting "SYS". "SYS_EXPORT_TRANSPORTABLE_01": userid= "/ * AS SYSDBA" transport_tablespaces=tts_simple dumpfile=tts_simple.dmp directory=ttsdmp transport_full_check=y

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:

/ ttstest/tts_simple.dmp

*

Datafiles required for transportable tablespace TTS_SIMPLE:

+ DATA/ora11gasm/datafile/tts_simple.267.799910245

Job "SYS". "SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 05:31:49

The following is the key step of an experiment, which is to copy out the data file. In a traditional file system, we can directly use FTP/SFTP to copy the data files to the target target side. But in the case of ASM, the commands of our operating system do not work.

The core of the problem is how to copy data files from ASM Diskgroup to the file system. At present, we have two ways:

One is the traditional method, which is to use the backup as copy method of RMAN to copy the data files to the file system intact.

In 11g, the ASM command line tool asmcmd provides cp commands to support copy operations between ASM and the file system

We chose RMAN method in our experiment.

[oracle@bspdev ttstest] $rman nocatalog

Recovery Manager: Release 11.2.0.3.0-Production on Wed Nov 21 05:32:23 2012

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

RMAN > connect target /

Connected to target database: ORA11GAS (DBID=2390045008)

Using target database control file instead of recovery catalog

RMAN > backup as copy tablespace tts_simple format'/ ttstest/%U'

Starting backup at 21-NOV-12

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00006 name=+DATA/ora11gasm/datafile/tts_simple.267.799910245

Output file name=/ttstest/data_D-ORA11GAS_I-2390045008_TS-TTS_SIMPLE_FNO-6_0bnqrccg tag=TAG20121121T055216 RECID=3 STAMP=799912337

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 21-NOV-12

Starting Control File and SPFILE Autobackup at 21-NOV-12

Piece handle=+RECO/ora11gasm/autobackup/2012_11_21/s_799912339.273.799912341 comment=NONE

Finished Control File and SPFILE Autobackup at 21-NOV-12

RMAN >

We can find backup files in the file system.

[oracle@bspdev ttstest] $ls-l

Total 10376

-rw-r- 1 oracle asmadmin 10493952 Nov 21 05:52 data_D-ORA11GAS_I-2390045008_TS-TTS_SIMPLE_FNO-6_0bnqrccg

-rw-r--r-- 1 oracle asmadmin 1201 Nov 21 05:31 export.log

-rw-r- 1 oracle asmadmin 106496 Nov 21 05:31 tts_simple.dmp

[oracle@bspdev ttstest] $

Use the mv command to rename.

[oracle@bspdev ttstest] $mv data_D-ORA11GAS_I-2390045008_TS-TTS_SIMPLE_FNO-6_0bnqrccg tts_simple01.dbf

[oracle@bspdev ttstest] $ls-l

Total 10376

-rw-r--r-- 1 oracle asmadmin 1201 Nov 21 05:31 export.log

-rw-r- 1 oracle asmadmin 10493952 Nov 21 05:52 tts_simple01.dbf

-rw-r- 1 oracle asmadmin 106496 Nov 21 05:31 tts_simple.dmp

[oracle@bspdev ttstest] $

9. Restore the target environment

We chose to restore TTS to a non-ASM Linux platform. It is preferred to use FTP/SFTP to copy dmp metadata and data files to the Target environment.

[root@bspdev ~] # cd /

[root@bspdev /] # mkdir ttstest

[root@bspdev /] # chown oracle:oinstall ttstest/

[root@bspdev /] # su-oracle

[oracle@bspdev ~] $cd / ttstest/

[oracle@bspdev ttstest] $ls-l

Total 0

[oracle@bspdev ttstest] $pwd

/ ttstest

[oracle@bspdev ttstest] $

-- end of copy

[oracle@bspdev ttstest] $ls-l

Total 10376

-rw-r--r-- 1 oracle oinstall 1201 Nov 21 06:14 export.log

-rw-r--r-- 1 oracle oinstall 10493952 Nov 21 06:14 tts_simple01.dbf

-rw-r--r-- 1 oracle oinstall 106496 Nov 21 06:14 tts_simple.dmp

Relocates the data file and creates a directory object for Metadata restore.

[oracle@bspdev ttstest] $cp tts_simple01.dbf / u01/oradata/WILSON/datafile/

[oracle@bspdev ttstest] $cd / u01/oradata/WILSON/datafile/

[oracle@bspdev datafile] $ls-l

Total 1930836

-rw-r- 1 oracle oinstall 104865792 Nov 21 06:11 o1_mf_example_7xt46m9x_.dbf

(the reason for the length of the space, omit the part. )

-rw-r- 1 oracle oinstall 110108672 Nov 21 06:17 o1_mf_undotbs1_7xt3yzl5_.dbf

-rw-r- 1 oracle oinstall 15736832 Nov 21 06:11 o1_mf_users_805nxydh_.dbf

-rw-r--r-- 1 oracle oinstall 10493952 Nov 21 06:17 tts_simple01.dbf

[oracle@bspdev datafile] $pwd

/ u01/oradata/WILSON/datafile

-- Sqlplus command

SQL > create directory ttsdmp as'/ ttstest'

Directory created

SQL > create user tts_simple identified by tts_simple

User created

SQL > grant connect, resource to tts_simple

Grant succeeded

Restore metadata information.

[oracle@bspdev ttstest] $impdp userid=\'/ as sysdba\ 'directory=ttsdmp dumpfile=tts_simple.dmp transport_datafiles='/u01/oradata/WILSON/datafile/tts_simple01.dbf'

Import: Release 11.2.0.1.0-Production on Wed Nov 21 06:22:43 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

Master table "SYS". "SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYS". "SYS_IMPORT_TRANSPORTABLE_01": userid= "/ * AS SYSDBA" directory=ttsdmp dumpfile=tts_simple.dmp transport_datafiles=/u01/oradata/WILSON/datafile/tts_simple01.dbf

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 06:23:01

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

FILE_NAME TABLESPACE_NAME

-

/ u01/oradata/WILSON/datafile/tts_simple01.dbf TTS_SIMPLE

SQL > alter tablespace tts_simple read write

Tablespace altered

SQL > select count (*) from tts_simple.t

COUNT (*)

-

56289

10 、 More Complex Cases

In the above series, we have introduced some basic scenarios and processing methods for using TTS. Of course, more complex scenarios can be encountered in practical work. For example, migrate an ASM-saved tablespace from a RAC environment to an ASM server in a different platform.

In the official solution, there are several details to pay attention to.

The first is metadata export. When using expdp export, the directory we created can be the ASM disk group directory. However, Expdp logs can only be stored in the file system, so additional directory objects are required.

The second is transmission. Oracle recommends using the dbms_file_transfer.put_file method to export files from one ASM to another ASM or file system.

If you are doing platform conversion, be careful not to use dbms_file_transfer for transmission. Because in the current Oracle version, this package method does not support the transfer of files between platforms.

TTS is called the fastest method of data migration, because we don't need to convert the data, we just need to consume the time it takes to transfer files on the network. However, TTS is not a good approach for those very empty tablespaces. For example, a tablespace consists of two files that occupy 2G space, but there is only 10m of valid data inside. Consuming 2G network traffic is obviously not a good way.

The above is how to understand Oracle transferable tablespaces. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.