Transportable tablespaces are now portable across platforms, making data publication quicker and easier. Plus, external table downloads make the task of data movement with transformation simpler and faster.
How do you move data from one database to another? Of the several methods, one in particular stands out: transportable tablespaces. In this approach, you take a set of self-contained, read-only tablespaces, export only the metadata, copy the datafiles of those tablespaces at the OS level to the target platform, and import the metadata into the data dictionarya process known as plugging.OS file copy is generally much faster than other traditional means of data movement such as export/import or SQL*Loader. However, in Oracle9i Database and below, a restriction limits its usefulness to only a few cases in which both the target and source database run on the same OS platformyou can't transport tablespaces between Solaris and HP-UX, for example. In Oracle Database 10g, this restriction has disappeared: you can now transport tablespaces between platforms as long as the OS byte orders are identical. A lengthy discussion of byte order is beyond our boundaries here, but suffice it to say that some operating systems, including Windows, store multi-byte binary data with the least significant byte in the lowest memory address; therefore, the system is called little endian. Conversely, other OSs, including Solaris, store the most significant byte in the lowest memory address, hence the term big endian. When a big-endian system tries to read data from a little-endian one, a conversion process is requiredotherwise, the byte order will lead to an incorrect interpretation of the read data. (For a detailed explanation of byte order, read the excellent article "Introduction to Endianness" from the Jan. 2002 issue of Embedded Systems Programming.) When transporting tablespaces between platforms of same endianess, however, no conversion is required. How do you know which operating systems follow which byte order? Instead of guessing or having to search the internet, simply issue the 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 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 16 Apple Mac OS BigSuppose you want to transport a tablespace USERS from a host machine SRC1, running Linux on Intel Architecture to machine TGT1, running Microsoft Windows. Both the source and target platforms are of little endian type. The datafile for the tablespace USERS is users_01.dbf. You would follow an approach similar to the following.
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-04This step produces a file in the standard RMAN file format <tablespace_name>_<absolute_datafile_no> in the directory /home/oracle/rman_bkups. Note that the datafile for tablespace USERS is not touched; rather, a new file is created for HP-UX. Now this file can be copied over to the target system, and the rest of the steps are easy. This RMAN conversion command is quit powerful. In the form given above, it can create the datafiles in sequence. For a tablespace containing multiple datafiles, you can instruct conversion to run in parallel. To do so, you would add a clause to the above command:
parallelism = 4which creates four RMAN channels, with each one working on a datafile. However, a more useful approach is to convert a large number of tablespaces in one step, which is where parallelism can really help. Here we are converting two tablespaces, USERS and MAINTS, to HP-UX:
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 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-04In the above examples, the converted file names are difficult to decipher and tie to the original files (for instance, file users01.dbf becomes USERS_4). Instead, you can use the other format for naming data files. This process is similar to that for renaming data files in Data Guard. You could use:
RMAN> convert tablespace users 2> to platform 'HP-UX (64-bit)' 3> db_file_name_convert '/usr/oradata/dw10/dw10','/home/oracle/rman_bkups' 4> ; 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/dw10/dw10/users01.dbf converted datafile=/home/oracle/rman_bkups/users01.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile conversion input datafile fno=00005 name=/usr/oradata/dw10/dw10/users02.dbf converted datafile=/home/oracle/rman_bkups/users02.dbf channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01 Finished backup at 14-MAR-04which preserves the file names after conversion. If you change to directory /home/oracle/rman_bkups, you will see the files users01.dbf and users02.dbf, corresponding to the original files in the same names. In the above cases, we converted the files on the source platform. However, you can do that on the target platform as well. For example, you can copy file users01.dbf to host TGT1 running HP-UX and then convert the file to HP-UX format with:
RMAN> convert 2> datafile '/usr/oradata/dw10/dw10/users01.dbf' 3> format '/home/oracle/rman_bkups/%N_%f' 4> ;This approach will create a file in the format specified in the directory. But why would you want to convert the datafiles on the target platform, exactly? One reason could be shorter downtime, which requires the tablespaces to be READ ONLY state only for the duration of the copy to the target host. You could triple-mirror the datafile, make the tablespace read only, break the third mirror, and immediately make the tablespace read/write. This third mirror could then be mounted on the target system and converted at leisure. This arrangement minimizes the duration for which the tablespace must remain read only. Another reason could be performance. The OLTP database may be under a constant load and using the RMAN convert operation may strain the system more than desired. Instead, the conversion can be offloaded to the data warehouse server, where more CPUs are usually available for parallel operations. Using External Tables as a Data Transfer Mechanism Oracle9i Database introduced external tables, which allow a formatted plain text file to be visible to the database as a table that can be selected by regular SQL. Suppose you have to move the contents of the table named TRANS from the OLTP database to the data warehouse database using this external table approach. Here are the steps to accomplish that.
create directory dump_dir as '/home/oracle/dump_dir'; create table trans_dump organization external ( type oracle_datapump default directory dump_dir location ('trans_dump.dmp') ) as select * from trans /This command creates a file named trans_dump.dmp in the directory /home/oracle/dump_dir. This file is not exactly ASCII text; the metadata is plain text but the actual data is in raw format. However, this file is portable across operating systems, similar to the export dump filebut unlike export, the download of the data is extremely fast. You would copy this file to the data warehouse server and create the external table in the same manner as before, but this time substituting this file as the source. So what are the differences between older data transfer mechanisms and this one? There are several. First, you can create a portable file extremely quickly without writing any complex SQL, selecting columns of the table, and so on. Second, you can use this file as an input for the external table, making it possible to view the data as a regular table and load that data into other tables after data manipulation. You can also enhance the performance of the data download to this external table as shown below.
create table trans_dump organization external ( type oracle_datapump default directory dump_dir location ('trans_dump.dmp') ) parallel 2 as select * from trans /This command creates the same file, only in parallel. You should do that to take advantage of multiple host CPUs, if available. In addition to going parallel, you can also download the table to multiple external files as shown below.
create table trans_dump organization external ( type oracle_datapump default directory dump_dir location ('trans_dump_1.dmp','trans_dump_2.dmp') ) parallel 4 as select * from trans /This command creates two files trans_dump_1.dmp and trans_dump_2.dmp, instead of only one. This approach is helpful in spreading files across many physical devices or controllers to reduce I/O-related waits. Conclusion By allowing tablespaces to be transportable across platforms, 10g offers a powerful solution for data warehouse data movements. Coupled with External Table download, this feature bridges the gap between source and target databases for data publicationwhether it's an OLTP, data warehouse, or data mart databaseand allows you to make appropriate platform choices for particular types of applications. Furthermore, by making transportable tablespaces viable, 10g makes data refreshes quicker and more frequent so that analyzed data is available to end users sooner. This capability can also be used to publish data via offline media to different databases, regardless of their host systems. Using external table downloads the utility to move large quantities of data as an ETL tool is finally available to the end user. For more information about transporting tablespaces in 10g, see the "Transporting Tablespaces Between Databases" section in Chapter 8 of the Oracle Database Administrator's Guide .
Next Week: Automatic Memory Management