Data backup using a portable tablespace in Oracle

Convertible tablespace: a set of self-contained and read-only tablespaces are used to export only metadata. At the operating system layer, the data files of these tablespaces are copied to the target platform and the metadata is imported into the data dictionary. In Oracle9i databases and earlier versions, table space that can be transferred is only useful when the target database and source database run on the same operating system platform. For example, you cannot transfer tablespaces between Solaris and the HP-UX platform. In the Oracle Database 10 Gb, this restriction disappears: as long as the operating system has the same byte order, you can transmit tablespaces between platforms.
There is an important concept for the table space that can be transferred: self-contained ).
During tablespace transmission, the tablespace set must be self-contained. The self-contained indicates that the internal tablespace set used for transmission does not reference the external tablespace set. There are two types of self-contained table space: generally self-contained table space set and completely (strictly) Self-contained table space set.
The following are common violations of the self-contained principle:
The index is in the internal tablespace set, while the table is in the external tablespace set (conversely, if the table is in the internal tablespace set and the index is in the external tablespace set, the self-contained principle is not violated ).
Partition tables are divided into partitions in the internal tablespace set, and some are in the external tablespace set (for partition tables, either all are included in the internal tablespace set or all are not included ).
If the constraints are transmitted when the tablespace is transmitted at the same time, the reference integrity constraints, the constraints point to the table in the external tablespace set, violation of the self-contained constraints; if the constraints are not transmitted, it is not related to the constraints.
The table is in the internal tablespace set, while the lob column is in the external tablespace set, which violates the self-contained constraints.
Generally, you can use the system package dbms_tts to check whether the space is self-contained. verification can be performed in two ways: Non-strict mode and strict mode.

The following is a simple verification process:
Perform a non-strict self-contained check (full_check = false) as the Sys User ):
Telnet 172.19.111.38
SQL> connect/As sysdba
SQL> DESC dbms_tts;
Procedure downgrade
Function isselfcontained returns Boolean
Argument name type in/out default?
-------------------------------------------------------------------
Ts_list clob in
Incl_constraints Boolean in
Full_check Boolean in
Procedure kfp_ckcmp
Procedure transport_set_check
Argument name type in/out default?
-------------------------------------------------------------------
Ts_list clob in
Incl_constraints Boolean in default
Full_check Boolean in default
SQL> exec dbms_tts.transport_set_check ('ipas _ acct_data ', true );
SQL> select * From transport_set_violations;
No rows selected

Perform a strict self-contained check (full_check = true ):
SQL> exec dbms_tts.transport_set_check ('ipas _ acct_data ', true, true );
SQL> select * From transport_set_violations;
Violations
Bytes ----------------------------------------------------------------------------------------
Index ipas_acct_data.ind_id in tablespace ipas_acct_data points to table utcore. ipas_acct_data in tablespace utsers
In turn, for the users tablespace, non-strict checks cannot pass:
SQL> exec dbms_tts.transport_set_check ('users', true );
SQL> select * From transport_set_violations;
Violations
Bytes ----------------------------------------------------------------------------------------
Index ipas_acct_data.ind_id in tablespace users points to table utcore. ipas_acct_data in tablespace ipas_acct_data
However, if multiple tablespaces can be transmitted at the same time, some self-contained issues can be solved:
SQL> exec dbms_tts.transport_set_check ('users, ipas_acct_data ', true, true );
SQL> select * From transport_set_violations;
No rows selected

After the tablespace self-contained is confirmed, it is very convenient to transmit the tablespace. Note that the export and import operations must be performed as sys as sysdba, which generally includes the following steps.

1. Make the tablespace read-only in the source database.

Telnet 172.19.111.38
$ Sqlplus '/As sysdba'
SQL> set lines 1000
SQL> Col file_name format A50
SQL> select tablespace_name, file_name from dba_data_files where tablespace_name = 'ipas _ acct_data ';
Tablespace_name file_name
--------------------------------------------------------------------------------
Ipas_acct_data/opt/Oracle/wacosdata/ipas_acct_data001.dbf
The source database and target database must be compatible with the compatible parameter:
SQL> show parameter compatible
Name type value
-----------------------------------------------------------------------------
Compatible string 9.2.0
SQL> alter tablespace ipas_acct_data read only;

2. Export the tablespace from the source database.

$ Exp tablespaces = ipas_acct_data transport_tablespace = y file = exp_ts_ipas_acct_data.dmp
Username: SYS as sysdba
Password: change_on_install
$ LS-OTR
...
-RW-r -- 1 Oracle 51200 Aug 20 exp_ts_ipas_acct_data.dmp
The DMP file only contains metadata (not the content of the tablespace), so it will be very small.

3. copy the file exp_ts_ipas_acct_data.dmp and/opt/Oracle/wacosdata/ipas_acct_data001.dbf to the target host. If you use ftp, You need to specify the binary option.
$ FTP 172.19.111.34
Ftp> bi
Ftp> HA
Ftp> prompt off
Ftp> put exp_ts_ipas_acct_data.dmp
Ftp> put/opt/Oracle/wacosdata/ipas_acct_data001.dbf
226 transfer complete.
Local:/opt/Oracle/wacosdata/ipas_acct_data001.dbf remote:/opt/Oracle/wacosdata/ipas_acct_data001.dbf
Ftp>
After transmission, you can set the source database tablespace to readable:
$ Sqlplus '/As sysdba'
SQL> alter tablespace ipas_acct_data read write;

4. Insert the tablespace into the database on the target host.

Telnet 172.19.111.34
$ Pwd
/Opt/Oracle
$ LS-OTR
-RW-r -- 1 Oracle 51200 Aug 21 exp_ts_ipas_acct_data.dmp
$ LS-L/opt/Oracle/wacosdata/ipas_acct_data001.dbf
-RW-r ----- 1 Oracle DBA 1073750016 Aug 20/opt/Oracle/wacosdata/ipas_acct_data001.dbf
$ Imp tablespaces = ipas_acct_data transport_tablespace = y file = exp_ts_ipas_acct_data.dmp datafiles = '/opt/Oracle/wacosdata/ipas_acct_data001.dbf'
Username: SYS as sysdba
Password: change_on_install
. Importing sys's objects into sys
. Importing wacos's objects into wacos
.. Importing table "pimbalancelogdetail"
...
Import terminated successfully with warnings.
The import is successful and the time is fast.

Finally, set the tablespace to writable, and the target database can read and write data in the tablespace:
SQL> alter tablespace ipas_acct_data read write;

 

However, in Oracle9i databases and earlier versions, the tablespace that can be transferred is only useful when the target database and source database run on the same operating system platform? For example, you cannot transfer tablespaces between Solaris and the HP-UX platform.
　　
In the Oracle Database 10 Gb, this restriction disappears: as long as the operating system has the same byte order, you can transmit tablespaces between platforms. This article will not discuss the byte sequence in length, but it is enough to mention a few words here: some operating systems (including Windows systems) this system stores multi-byte binary data with the lowest valid bytes in the low memory address. Therefore, this system is called the low address and low byte order. On the contrary, other operating systems (including Solaris) store the highest valid bytes in the low memory address, so this system is called the low address high byte order. When a low-address, high-byte system tries to read data from a low-address, low-byte system, a conversion process is required? Otherwise, the byte order will cause the read data to be incorrectly interpreted. (For a detailed description of the byte sequence, please read an excellent article "byte sequence Introduction" in the January 2002 issue of embedded system programming ".) However, when the tablespace is transferred between platforms in the same byte sequence, no conversion is required.

China Network Management Alliance
　　
How do you know which operating system adopts the byte sequence? You do not need to guess or search for Internet connections, but simply execute the following query:
SQL> select * from V $ transportable_platform order by platform_id;
　　
Platform_id platform_name endian_format
------------------------------------------------------------
1 Solaris [Tm] OE (32-bit) Big
2 Solaris [Tm] OE (64-bit) Big
3 HP-UX (64-bit) Big
4 HP-UX Ia (64-bit) Big
5 HP Tru64 UNIX little
6 Aix-based systems (64-bit) Big
7 Microsoft Windows System Ia (32-bit) Little
8 Microsoft Windows Ia (64-bit) Little
9 IBM zseries based Linux big
10 Linux Ia (32-bit) Little
11 Linux Ia (64-bit) Little
12 Microsoft Windows 64-bit for AMD little
13 Linux 64-bit for AMD little
15 HP Open VMS little

Bbs.bitscn.com Network Management Forum
16 Apple Mac OS big
　　
Suppose you want to transmit a tablespace users to the computer tgt1 running the Microsoft Windows operating system from the host src1 running the Linux operating system on the intel architecture. Both the source and target platforms are low-address and Low-byte-order. The data file of the tablespace users is users_01.dbf. You will perform operations in a way similar to the following.
　　
Make the tablespace read-only:
Alter tablespace users read only;
Export the tablespace. Run the following command at the operating system prompt:
Exp tablespaces = users transport_tablespace = y file = exp_ts_users.dmp
The exp_ts_users.dmp file only contains metadata (not the contents of the tablespace users), so it will be very small.
　　
Copy the exp_ts_users.dmp and users_01.dbf files to the host tgt1. If you use ftp, You need to specify the binary option.
　　
Insert the tablespace into the database. Run the following statement at the operating system command prompt:
IMP tablespaces = users transport_tablespace = y file = exp_ts_users.dmp datafiles = 'users _ 01. dbf'
After step 2, the target database will have a tablespace named users and the contents of the tablespace will be provided.
　　
Remember that the system src1 and tgt1 are Linux and Windows systems respectively. As of Oracle9i, databases running on tgt1 cannot identify the data file users_01.dbf in step 1, making the entire process useless. You will have to seek help with other methods (such as conventional export and import, creating plain text files, loading through SQL * loader, or directly loading and inserting between different databases ).

China Network Management Forum
　　
These alternative methods are no longer needed in the 10 Gb because the target database can identify data files from another platform. In our example, the operating system run on the source and target hosts in the same byte order (low address and low byte order), so no conversion is required.
　　
This function is particularly useful in Data Warehouses. Smaller Object-oriented Data marketplaces are often filled in from warehouses after refreshing. With 10 Gbit/s, these data marketplaces can now be placed on smaller, cheaper computers (such as Intel boxes running Linux), while data warehouse servers can be placed on larger enterprise-level computers. Essentially, with the ability to transmit tablespaces, you can now make better use of a combination of hardware and operating systems.
　　
　　 Platforms with different byte Sequences
If the platform is in different byte sequences, how can you implement transport? As I have mentioned earlier, if the byte sequence of the target computer is different from that of the source computer, the data file cannot be read correctly, so it is impossible to simply copy the data file. But don't be discouraged. The Oracle 10g RMAN utility provides help to convert data files from one byte to another.
　　
In the above example, if the host src1 runs on Linux (low address low byte order) while the target host tgt1 runs on the HP-UX (low address high byte order ), then you need to introduce another step between Step 1 and Step 2 for conversion. With rman, you will convert the data file from Linux to the HP-UX format on the source computer src1 (assuming you have changed the tablespace to read-only): bitscn. Net China Network Administrator blog
RMAN> convert tablespace users
2> to platform 'hp-UX (64-bit )'
3> Format = '/home/Oracle/rman_bkups/% N _ % F ';
　　
Starting backup at 14-Mar-04
Using channel ora_disk_1
Channel ora_disk_1: Starting datafile Conversion
Input datafile fno = 00004 name =/usr/oradata/DW/starz10/users01.dbf
Converted datafile =/home/Oracle/rman_bkups/users_4
Channel ora_disk_1: datafile Conversion complete, elapsed time: 00:00:07
Finished backup at 14-Mar-04
　　
In the/home/Oracle/rman_bkups directory, create a file in the standard RMAN file format <tablespace_name >_< absolute_datafile_no>. Note that we did not touch the data file for the tablespace users; instead, we created a new file for the HP-UX. Currently, this file can be copied to the target system. The remaining steps are very simple.
　　
This RMAN conversion command is very powerful. In the preceding format, a data file can be created in sequence. For tablespaces that contain multiple data files, you can run the command to convert and run multiple data files at the same time. To achieve this, you need to add a sub-statement in the preceding command:
Parallelism = 4
　　
This clause creates four RMAN channels, each of which processes one data file. However, a more useful method is to use a step to convert a large number of tablespaces. In this case, parallel conversions can be very helpful. Here we will convert the two tablespaces users and maints to the HP-UX:

Bbs.bitscn.com Network Management Forum

RMAN> convert tablespace users, maints
2> to platform 'hp-UX (64-bit )'
3> Format = '/home/Oracle/rman_bkups/% N _ % F'
4> parallelism = 5;
　　
Starting backup at 14-Mar-04
Using target database controlfile instead of recovery catalog
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 244 devtype = Disk
Allocated channel: ora_disk_2
Channel ora_disk_2: SID = 243 devtype = Disk
Allocated channel: ora_disk_3
Channel ora_disk_3: SID = 245 devtype = Disk
Allocated channel: ora_disk_4
Channel ora_disk_4: SID = 272 devtype = Disk
Allocated channel: ora_disk_5
Channel ora_disk_5: SID = 253 devtype = Disk
Channel ora_disk_1: Starting datafile Conversion
Input datafile fno = 00004 name =/usr/oradata/dw10/dw10/users01.dbf
Channel ora_disk_2: Starting datafile Conversion
Input datafile fno = 00005 name =/usr/oradata/dw10/dw10/users02.dbf
Channel ora_disk_3: Starting datafile Conversion
Input datafile fno = 00006 name =/usr/oradata/dw10/dw10/maints01.dbf

Bitscn. com
Channel ora_disk_4: Starting datafile Conversion
Input datafile fno = 00007 name =/usr/oradata/dw10/dw10/maints02.dbf
Converted datafile =/home/Oracle/rman_bkups/users_4
Channel ora_disk_1: datafile Conversion complete, elapsed time: 00:00:03
Converted datafile =/home/Oracle/rman_bkups/users_5
Channel ora_disk_2: datafile Conversion complete, elapsed time: 00:00:00
Converted datafile =/home/Oracle/rman_bkups/maints_6
Channel ora_disk_3: datafile Conversion complete, elapsed time: 00:00:01
Converted datafile =/home/Oracle/rman_bkups/maints_7
Channel ora_disk_4: datafile Conversion complete, elapsed time: 00:00:01
Finished backup at 14-Mar-04