1 2 Previous Next 26 Replies Latest reply on May 20, 2004 9:37 PM by 403551 Go to original post
      • 15. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
        403551
        Step 11: Apply this command to see all the options of the import utility.
        C:\>SET ORACLE_SID=BASEY
        
        C:\>IMP HELP=Y
        
        Import: Release 10.1.0.2.0 - Production on Fri Apr 23 19:51:33 2004
        
        Copyright (c) 1982, 2004, Oracle.  All rights reserved.
        
        
        
        You can let Import prompt you for parameters by entering the IMP
        command followed by your username/password:
        
             Example: IMP SCOTT/TIGER
        
        Or, you can control how Import runs by entering the IMP command followed
        by various arguments. To specify parameters, you use keywords:
        
             Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
             Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
                       or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
        
        USERID must be the first parameter on the command line.
        
        Keyword  Description (Default)       Keyword      Description (Default)
        --------------------------------------------------------------------------
        USERID   username/password           FULL         import entire file (N)
        BUFFER   size of data buffer         FROMUSER     list of owner usernames
        FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames
        SHOW     just list file contents (N) TABLES       list of table names
        IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
        GRANTS   import grants (Y)           INCTYPE      incremental import type
        INDEXES  import indexes (Y)          COMMIT       commit array insert (N)
        ROWS     import data rows (Y)        PARFILE      parameter filename
        LOG      log file of screen output   CONSTRAINTS  import constraints (Y)
        DESTROY                overwrite tablespace data file (N)
        INDEXFILE              write table/index info to specified file
        SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N)
        FEEDBACK               display progress every x rows(0)
        TOID_NOVALIDATE        skip validation of specified type ids
        FILESIZE               maximum size of each dump file
        STATISTICS             import precomputed statistics (always)
        RESUMABLE              suspend when a space related error is encountered(N)
        RESUMABLE_NAME         text string used to identify resumable statement
        RESUMABLE_TIMEOUT      wait time for RESUMABLE
        COMPILE                compile procedures, packages, and functions (Y)
        STREAMS_CONFIGURATION  import streams general metadata (Y)
        STREAMS_INSTANTIATION  import streams instantiation metadata (N)
        
        The following keywords only apply to transportable tablespaces
        TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
        TABLESPACES tablespaces to be transported into database
        DATAFILES datafiles to be transported into database
        TTS_OWNERS users that own data in the transportable tablespace set
        
        Import terminated successfully without warnings.
        Joel Pérez
        http://otn.oracle.com/experts

        • 16. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
          403551
          Step 12: Let's got import the metadata in the database "BASEY"
          C:\>IMP FILE=joel.dmp TRANSPORT_TABLESPACE=Y TABLESPACES=JOEL_TS DATAFILES=('C:\
          oracle\product\10.1.0\oradata\BASEY\JOEL_TS_01.DBF') USERID='SYS/VZLA AS SYSDBA'
          
          
          Import: Release 10.1.0.2.0 - Production on Fri Apr 23 19:53:40 2004
          
          Copyright (c) 1982, 2004, Oracle.  All rights reserved.
          
          
          Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
          tion
          With the OLAP and Data Mining options
          
          IMP-00002: failed to open joel.dmp for read
          Import file: EXPDAT.DMP > EXPDAT.DMP
          
          Export file created by EXPORT:V10.01.00 via conventional path
          About to import transportable tablespace(s) metadata...
          import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
          import server uses WE8ISO8859P1 character set (possible charset conversion)
          . importing SYS's objects into SYS
          IMP-00017: following statement failed with ORACLE error 29342:
           "BEGIN   sys.dbms_plugts.checkUser('JOEL'); END;"
          IMP-00003: ORACLE error 29342 encountered
          ORA-29342: user JOEL does not exist in the database
          ORA-06512: at "SYS.DBMS_PLUGTS", line 1822
          ORA-06512: at line 1
          IMP-00000: Import terminated unsuccessfully
          As you can see, the user "JOEL" must be created in the database "BASEY"

          Joel Pérez
          http://otn.oracle.com/experts

          • 17. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
            403551
            Step 13: Creating a user called "JOEL" in the database "BASEY" and granting 2 basic priviliges to him to create tables
            and other some objects
            SQL> create user joel identified by joel
              2  default tablespace joel_ts
              3  quota unlimited on joel_ts;
            
            User created.
            
            SQL>
            
            SQL> grant connect, resource to joel;
            
            Grant succeeded.
            
            SQL>
            Joel Pérez
            http://otn.oracle.com/experts

            • 18. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
              403551
              Step 14: Let's go to try to carry out the importation again. There is a special detail
              that I wanted to include in this example. When I exported the metadata of the tablespace
              "JOEL_TS" I did not include the parameter "FILE" and the export utility created a default
              file called : "EXPDAT.DMP" and that is the file with which I am working in this example.
              C:\>
              C:\>
              C:\>IMP FILE=joel.dmp TRANSPORT_TABLESPACE=Y TABLESPACES=JOEL_TS DATAFILES=('C:\
              oracle\product\10.1.0\oradata\BASEY\JOEL_TS_01.DBF') USERID='SYS/VZLA AS SYSDBA'
              
              
              Import: Release 10.1.0.2.0 - Production on Fri Apr 23 19:54:51 2004
              
              Copyright (c) 1982, 2004, Oracle.  All rights reserved.
              
              
              Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
              tion
              With the OLAP and Data Mining options
              
              IMP-00002: failed to open joel.dmp for read
              Import file: EXPDAT.DMP >
              
              Export file created by EXPORT:V10.01.00 via conventional path
              About to import transportable tablespace(s) metadata...
              import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
              import server uses WE8ISO8859P1 character set (possible charset conversion)
              . importing SYS's objects into SYS
              . importing JOEL's objects into JOEL
              . . importing table                   "JOEL_TABLE"
              . importing SYS's objects into SYS
              Import terminated successfully with warnings.
              
              C:\>
              When you get this stage you can simle because the task is done!

              Joel Pérez
              http://otn.oracle.com/experts

              • 19. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
                403551
                Step 14: Let's go to try to carry out the importation again. There is a special detail
                that I wanted to include in this example. When I exported the metadata of the tablespace
                "JOEL_TS" I did not include the parameter "FILE" and the export utility created a default
                file called : "EXPDAT.DMP" and that is the file with which I am working in this example.
                C:\>
                C:\>
                C:\>IMP FILE=joel.dmp TRANSPORT_TABLESPACE=Y TABLESPACES=JOEL_TS DATAFILES=('C:\
                oracle\product\10.1.0\oradata\BASEY\JOEL_TS_01.DBF') USERID='SYS/VZLA AS SYSDBA'
                
                
                Import: Release 10.1.0.2.0 - Production on Fri Apr 23 19:54:51 2004
                
                Copyright (c) 1982, 2004, Oracle.  All rights reserved.
                
                
                Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
                tion
                With the OLAP and Data Mining options
                
                IMP-00002: failed to open joel.dmp for read
                Import file: EXPDAT.DMP >
                
                Export file created by EXPORT:V10.01.00 via conventional path
                About to import transportable tablespace(s) metadata...
                import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
                import server uses WE8ISO8859P1 character set (possible charset conversion)
                . importing SYS's objects into SYS
                . importing JOEL's objects into JOEL
                . . importing table                   "JOEL_TABLE"
                . importing SYS's objects into SYS
                Import terminated successfully with warnings.
                
                C:\>
                When you get this stage you can smile because the task is done!

                Joel Pérez
                http://otn.oracle.com/experts

                • 20. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
                  403551
                  Step 15: Now, set in "read write" mode the tablespace "JOEL_TS" in the
                  database "BASEX" to have all as was before.
                  C:\>sqlplus /nolog
                  
                  SQL*Plus: Release 10.1.0.2.0 - Production on Fri Apr 23 20:00:06 2004
                  
                  Copyright (c) 1982, 2004, Oracle.  All rights reserved.
                  
                  SQL> conn system/vzla@basex
                  Connected.
                  SQL>
                  SQL> alter tablespace joel_ts read write;
                  
                  Tablespace altered.
                  
                  SQL>
                  Joel Pérez
                  http://otn.oracle.com/experts

                  • 21. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
                    403551
                    Step 16: If you got trouble carrying out these steps, one of the main causes can
                    be that the database and national character set between the databases are different.

                    Note : when you copy the datafile from one location to another location, remember
                    do it in binary mode. With the export/import utilities you can not do a transportation
                    of tablespaces between different OS but with the Oracle Data Pump you could do it without
                    any problem.

                    As you can see in these queries, my nls parameters are equal and because of that I did not
                    get any problem to do the task.

                    C:\>sqlplus /nolog
                    
                    SQL*Plus: Release 10.1.0.2.0 - Production on Fri Apr 23 20:00:06 2004
                    
                    Copyright (c) 1982, 2004, Oracle.  All rights reserved.
                    
                    SQL> conn system/vzla
                    Connected.
                    SQL>
                    
                    SQL>
                    SQL> show parameters db_name
                    
                    NAME                                 TYPE        VALUE
                    ------------------------------------ ----------- ------------------------------
                    db_name                              string      BASEY
                    SQL>
                    SQL> select * from nls_database_parameters;
                    
                    PARAMETER                      VALUE
                    ------------------------------ ----------------------------------------
                    NLS_LANGUAGE                   AMERICAN
                    NLS_TERRITORY                  AMERICA
                    NLS_CURRENCY                   $
                    NLS_ISO_CURRENCY               AMERICA
                    NLS_NUMERIC_CHARACTERS         .,
                    NLS_CHARACTERSET               WE8ISO8859P1
                    NLS_CALENDAR                   GREGORIAN
                    NLS_DATE_FORMAT                DD-MON-RR
                    NLS_DATE_LANGUAGE              AMERICAN
                    NLS_SORT                       BINARY
                    NLS_TIME_FORMAT                HH.MI.SSXFF AM
                    
                    PARAMETER                      VALUE
                    ------------------------------ ----------------------------------------
                    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
                    NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
                    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
                    NLS_DUAL_CURRENCY              $
                    NLS_COMP                       BINARY
                    NLS_LENGTH_SEMANTICS           BYTE
                    NLS_NCHAR_CONV_EXCP            FALSE
                    NLS_NCHAR_CHARACTERSET         AL16UTF16
                    NLS_RDBMS_VERSION              10.1.0.2.0
                    
                    20 rows selected.
                    
                    SQL>
                    Joel Pérez
                    http://otn.oracle.com/experts

                    • 22. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
                      403551
                      Step 17: the same query in the another database
                      SQL> conn system/vzla@127.0.0.1:1521/basex
                      Connected.
                      SQL>
                      SQL> show parameters db_name
                      
                      NAME                                 TYPE        VALUE
                      ------------------------------------ ----------- ------------------------------
                      db_name                              string      BASEX
                      SQL>
                      SQL> select * from nls_database_parameters;
                      
                      PARAMETER                      VALUE
                      ------------------------------ ----------------------------------------
                      NLS_LANGUAGE                   AMERICAN
                      NLS_TERRITORY                  AMERICA
                      NLS_CURRENCY                   $
                      NLS_ISO_CURRENCY               AMERICA
                      NLS_NUMERIC_CHARACTERS         .,
                      NLS_CHARACTERSET               WE8ISO8859P1
                      NLS_CALENDAR                   GREGORIAN
                      NLS_DATE_FORMAT                DD-MON-RR
                      NLS_DATE_LANGUAGE              AMERICAN
                      NLS_SORT                       BINARY
                      NLS_TIME_FORMAT                HH.MI.SSXFF AM
                      
                      PARAMETER                      VALUE
                      ------------------------------ ----------------------------------------
                      NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
                      NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
                      NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
                      NLS_DUAL_CURRENCY              $
                      NLS_COMP                       BINARY
                      NLS_LENGTH_SEMANTICS           BYTE
                      NLS_NCHAR_CONV_EXCP            FALSE
                      NLS_NCHAR_CHARACTERSET         AL16UTF16
                      NLS_RDBMS_VERSION              10.1.0.2.0
                      
                      20 rows selected.
                      
                      SQL>
                      Joel Pérez
                      http://otn.oracle.com/experts

                      • 23. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
                        403551
                        Step 18: Checking that Our task was done successfully.
                        Microsoft Windows 2000 [Version 5.00.2195]
                        (C) Copyright 1985-2000 Microsoft Corp.
                        
                        C:\>SET ORACLE_SID=BASEY
                        
                        C:\>sqlplus /nolog
                        
                        SQL*Plus: Release 10.1.0.2.0 - Production on Fri Apr 23 19:41:32 2004
                        
                        Copyright (c) 1982, 2004, Oracle.  All rights reserved.
                        
                        SQL> conn joel/joel@basey
                        Connected.
                        SQL>
                        SQL> select table_name from user_tables;
                        
                        TABLE_NAME
                        ------------------------------
                        JOEL_TABLE
                        
                        SQL>
                        SQL> select * from joel_table;
                        
                                C1
                        ----------
                                 1
                                 1
                                 1
                        
                        SQL>
                        Joel Pérez
                        http://otn.oracle.com/experts

                        • 24. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
                          403551
                          Step 19: To complement this task you can query the information from the
                          data dictionary to see the new tablespace and consequently the datafile added.
                          SQL> conn sys/vzla as sysdba
                          Connected.
                          SQL>
                          SQL> desc dba_tablespaces
                           Name                                      Null?    Type
                           ----------------------------------------- -------- ----------------------------
                          
                           TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
                           BLOCK_SIZE                                NOT NULL NUMBER
                           INITIAL_EXTENT                                     NUMBER
                           NEXT_EXTENT                                        NUMBER
                           MIN_EXTENTS                               NOT NULL NUMBER
                           MAX_EXTENTS                                        NUMBER
                           PCT_INCREASE                                       NUMBER
                           MIN_EXTLEN                                         NUMBER
                           STATUS                                             VARCHAR2(9)
                           CONTENTS                                           VARCHAR2(9)
                           LOGGING                                            VARCHAR2(9)
                           FORCE_LOGGING                                      VARCHAR2(3)
                           EXTENT_MANAGEMENT                                  VARCHAR2(10)
                           ALLOCATION_TYPE                                    VARCHAR2(9)
                           PLUGGED_IN                                         VARCHAR2(3)
                           SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
                           DEF_TAB_COMPRESSION                                VARCHAR2(8)
                           RETENTION                                          VARCHAR2(11)
                           BIGFILE                                            VARCHAR2(3)
                          
                          SQL>
                          SQL> select tablespace_name from dba_tablespaces;
                          
                          TABLESPACE_NAME
                          ------------------------------
                          SYSTEM
                          UNDOTBS1
                          SYSAUX
                          TEMP
                          USERS
                          JOEL_TS
                          
                          6 rows selected.
                          
                          SQL> desc dba_data_files
                           Name                                      Null?    Type
                           ----------------------------------------- -------- ----------------------------
                          
                           FILE_NAME                                          VARCHAR2(513)
                           FILE_ID                                            NUMBER
                           TABLESPACE_NAME                                    VARCHAR2(30)
                           BYTES                                              NUMBER
                           BLOCKS                                             NUMBER
                           STATUS                                             VARCHAR2(9)
                           RELATIVE_FNO                                       NUMBER
                           AUTOEXTENSIBLE                                     VARCHAR2(3)
                           MAXBYTES                                           NUMBER
                           MAXBLOCKS                                          NUMBER
                           INCREMENT_BY                                       NUMBER
                           USER_BYTES                                         NUMBER
                           USER_BLOCKS                                        NUMBER
                          
                          SQL>
                          SQL> select file_name from dba_data_files;
                          
                          FILE_NAME
                          --------------------------------------------------------------------------------
                          
                          C:\ORACLE\PRODUCT\10.1.0\ORADATA\BASEY\USERS01.DBF
                          C:\ORACLE\PRODUCT\10.1.0\ORADATA\BASEY\SYSAUX01.DBF
                          C:\ORACLE\PRODUCT\10.1.0\ORADATA\BASEY\UNDOTBS01.DBF
                          C:\ORACLE\PRODUCT\10.1.0\ORADATA\BASEY\SYSTEM01.DBF
                          C:\ORACLE\PRODUCT\10.1.0\ORADATA\BASEY\JOEL_TS_01.DBF
                          
                          SQL>
                          Joel Pérez
                          http://otn.oracle.com/experts

                          • 25. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
                            403551
                            I hope this has helped so that you learn this new functionality with few lines of demonstration and text without reading extensive guides or books.

                            For any comments, you could write to me directly to : ing_joelperez@hotmail.com

                            General Source of the Oracle10g Database documentation:
                            http://otn.oracle.com/pls/db10g/portal.portal_demo3?selected=1

                            Joel Pérez
                            http://otn.oracle.com/experts
                            • 26. Re: TIP 03: Transportable Tablespaces in 10g by Joel Pèrez
                              403551
                              I invite you to read as well this excellent article written by Arup Nanda.

                              Oracle Database 10g: The Top 20 Features for DBAs
                              Join Oracle Magazine's 2003 "DBA of the Year" Arup Nanda as he presents his list of the top Oracle Database 10g features for database administrators

                              Week 16
                              Transportable Tablespaces

                              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 dictionary—a 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 platform—you 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 required—otherwise, 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 Big

                              Suppose 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.
                              Make the tablespace READ ONLY:
                              alter tablespace users read only;
                              Export the tablespace. From the OS prompt, issue:
                              exp tablespaces=users transport_tablespace=y file=exp_ts_users.dmp
                              The file exp_ts_users.dmp contains only metadata—not the contents of the tablespace USERS—so it will be very small.

                              Copy the files exp_ts_users.dmp and users_01.dbf to the host TGT1. If you were using FTP, you would specify the binary option.

                              Plug the tablespace into the database. From the OS command prompt, you would issue:
                              imp tablespaces=users transport_tablespace=y file=exp_ts_users.dmp datafiles='users_01.dbf'
                              After Step 4, the target database will have a tablespace named USERS and the contents of the tablespace will be available.

                              Remember, the systems SRC1 and TGT1 are Linux and Windows respectively. As of Oracle9i, databases running on TGT1 will not recognize the datafile users_01.dbf in Step 4, rendering this whole process useless. You would have to resort to some other approach such as regular export and import, creating a flat file and loading via SQL*Loader, or direct load insert across database links.

                              In 10g, these alternatives are unnecessary because the target database will recognize a datafile from another platform. In our example, the byte order of the OSs on which the source and target hosts run are the same (little endian), so no conversion is needed.

                              This capability is particularly useful in data warehouses where smaller, subject-oriented data marts are often populated from the warehouse after a refresh. With 10g, these data marts can now be placed in smaller, cheaper machines, such as Intel boxes running Linux, with the data warehouse server on a larger enterprise-class machine. In essence, with transportable tablespaces, you can now make better use of various hardware and OS mixes.

                              Across Differing Endianness of Platforms

                              If the platforms are of different endianness, how will you achieve transferability? As I explained earlier, the byte order of the target machine, if different than the source, will read the data file incorrectly, making the mere copying of the data files impossible. But don't lose heart; help is available from the Oracle 10g RMAN utility, which supports the conversion of datafiles from one byte order to another.

                              In the above example, if the host SRC1 runs on Linux (little endian) and the target host TGT1 runs on HP-UX (big endian), you need to introduce another step between Steps 3 and 4 for conversion. Using RMAN, you would convert the datafile from Linux to HP-UX format on the source machine SRC1 (assuming you have made the tablespace read only):
                              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

                              This 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 = 4

                              which 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-04

                              In 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-04


                              which 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.
                              From the OLTP database, create a plain text file with the contents of the table TRANS. The file can be called trans_flat.txt in the directory /home/oracle/dump_dir. Usually this file is created with this SQL:
                              spool trans_flat.txt
                              select <column_1> ||','|| <column_2> ||','|| ...
                              from trans;
                              spool off
                              Copy the file over to the data warehouse server using ftp, rcp, or some other mechanism. The file exists in the directory /home/oracle/dump_dir.

                              On the data warehouse database, create a directory object named dump_dir as:
                              create directory dump_dir as '/home/oracle/dump_dir';
                              Create an external table:
                              create table trans_ext
                              (
                              ... <columns of the table> ...
                              )
                              organization external
                              (
                              type oracle_loader
                              default directory admin
                              access parameters
                              (
                              records delimited by newline
                              badfile 'trans_ext.bad'
                              discardfile 'trans_ext.dis'
                              logfile 'trans_ext.log'
                              fields terminated by "," optionally enclosed by '"'
                              (
                              ... <columns> ...
                              )
                              )
                              location ('trans_flat.txt')
                              )
                              reject limit unlimited;
                              Now load the external table into the regular tables using any common method such as direct load insert and merge.
                              The most time-consuming step here is Step 1, in which the plain text file is created. You could create this file using plain SQL and spooling to a file—a simple yet lengthy process. You can make the process somewhat faster by using a Pro*C or OCI program instead of SQL*Plus to offload the records to a flat file, but it will still take a while. The other "speed bump" is the need to specify the columns manually—another time-consuming process.

                              Both these problems have been addressed in 10g. Now you can unload a table to a portable format quickly using the external table creation process. Step 1 above becomes this simple SQL:
                              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 file—but 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 publication—whether it's an OLTP, data warehouse, or data mart database—and 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.

                              http://otn.oracle.com/pub/articles/10gdba/week16_10gdba.html

                              Joel Pérez
                              http://otn.oracle.com/experts
                              1 2 Previous Next