跨作業系統平臺移動資料庫（相同尾數格式）

hooca發表於2015-01-20
作業系統資料庫
當Oracle資料庫有相同的尾數格式時，可以跨平臺傳輸資料庫。尾數格式有大尾數和小尾數。

10g以後的版本可以支援。

檢視尾數格式：


點選(此處)摺疊或開啟

  1. Select endian_format
  2. From v$transportable_platform tp, v$database d
  3. Where tp.platform_name=d.platform_name;

ENDIAN_FORMAT
----------------------------
Little
如果兩個系統返回相同的值，則表示有相容的資料檔案格式，正常方式傳輸表空間即可；
否則需要使用RMAN的convert命令轉換格式。

轉換方法：
a. 檢視支援的作業系統平臺名：

點選(此處)摺疊或開啟

  1. select platform_name from v$transportable_platform;

PLATFORM_NAME
--------------------------------------------------------------------------------
Solaris[tm] OE (32-bit)
Solaris[tm] OE (64-bit)
Microsoft Windows IA (32-bit)
Linux IA (32-bit)
AIX-Based Systems (64-bit)
HP-UX (64-bit)
HP Tru64 UNIX
HP-UX IA (64-bit)
Linux IA (64-bit)
HP Open VMS
Microsoft Windows IA (64-bit)


PLATFORM_NAME
--------------------------------------------------------------------------------
IBM zSeries Based Linux
Linux x86 64-bit
Apple Mac OS
Microsoft Windows x86 64-bit
Solaris Operating System (x86)
IBM Power Based Linux
HP IA Open VMS
Solaris Operating System (x86-64)
Apple Mac OS (x86-64)


20 rows selected.
移動資料庫：
1. 以只讀模式開啟資料庫

點選(此處)摺疊或開啟

  1. startup mount;
  2. alter database open read only;
2. 驗證當前資料庫是否能傳送到指定平臺

點選(此處)摺疊或開啟

  1. set serveroutput on
  2. declare
  3.        db_ready boolean;
  4.      begin
  5.        db_ready := dbms_tdb.check_db
  6.        ('Microsoft Windows x86 64-bit',dbms_tdb.skip_readonly);
  7.      end;
  8.      /
3. 檢查外部物件，是否有依賴性

點選(此處)摺疊或開啟

  1. set serveroutput on
  2. Declare
  3.      external boolean;
  4. begin
  5.     external := dbms_tdb.check_external;
  6. end;
  7. /
4. 建立傳送所需指令碼

點選(此處)摺疊或開啟

  1. CONVERT DATABASE NEW DATABASE 'copydb'
            transport script '/home/oracle/trans.sql'
            to platform 'Microsoft Windows IA (32-bit)'
            db_file_name_convert 'oltp','olap'
  2.         --如果使用了OMF，還需要再加一行
  3.         --format 'olap_%U.dbf'
  4. ;
上面一個指令碼，將會生成/home/oracle/trans.sql指令碼，以及在/oradata/olap路徑下（假如源資料庫資料檔案在/oradata/oltp）生成轉換後的資料檔案，一會我們將這兩部分，連同引數檔案（trans.sql中顯示的）一同複製到目的伺服器，根據提示生成資料庫。

/home/oracle/trans.sql需要做相應修改。

引數檔案節選：

點選(此處)摺疊或開啟

  1. # Please change the values of the following parameters:


      control_files            = "E:\oradata\olap\CONTROL01.CTL"


      db_recovery_file_dest    = "E:\app\oracle\fast_recovery_area"


      db_recovery_file_dest_size= 42949672960


      audit_file_dest          = "E:\app\oracle\admin\olap\adump"


      db_name                  = "OLAP"

trans.sql指令碼

點選(此處)摺疊或開啟

  1. -- The following commands will create a new control file and use it
    -- to open the database.
    -- Data used by Recovery Manager will be lost.
    -- The contents of online logs will be lost and all backups will
    -- be invalidated. Use this only if online logs are damaged.


    -- After mounting the created controlfile, the following SQL
    -- statement will place the database in the appropriate
    -- protection mode:
    --  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE


    STARTUP NOMOUNT PFILE='E:\trans\initOLAP.ora'
    CREATE CONTROLFILE REUSE SET DATABASE "OLAP" RESETLOGS  ARCHIVELOG
        MAXLOGFILES 16
        MAXLOGMEMBERS 3
        MAXDATAFILES 100
        MAXINSTANCES 8
        MAXLOGHISTORY 292
    LOGFILE
      GROUP 1 'E:\oradata\olap\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
      GROUP 2 'E:\oradata\olap\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
      GROUP 3 'E:\oradata\olap\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
    DATAFILE
      'E:\oradata\olap\system01.dbf',
      'E:\oradata\olap\sysaux01.dbf',
      'E:\oradata\olap\undotbs01.dbf',
      'E:\oradata\olap\users01.dbf',
      'E:\oradata\olap\soe01.dbf'
    CHARACTER SET AL32UTF8
    ;


    -- Database can now be opened zeroing the online logs.
    ALTER DATABASE OPEN RESETLOGS UPGRADE;


    -- Commands to add tempfiles to temporary tablespaces.
    -- Online tempfiles have complete space information.
    -- Other tempfiles may require adjustment.
    ALTER TABLESPACE TEMP ADD TEMPFILE 'E:\oradata\olap\TEMP01.DBF'
         SIZE 30408704  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
    -- End of tempfile additions.
    --


    set echo off
    prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    prompt * Your database has been created successfully!
    prompt * There are many things to think about for the new database. Here
    prompt * is a checklist to help you stay on track:
    prompt * 1. You may want to redefine the location of the directory objects.
    prompt * 2. You may want to change the internal database identifier (DBID) 
    prompt *    or the global database name for this database. Use the 
    prompt *    NEWDBID Utility (nid).
    prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


    SHUTDOWN IMMEDIATE 
    STARTUP UPGRADE PFILE='E:\trans\initOLAP.ora'
    @@ ?/rdbms/admin/utlirp.sql 
    SHUTDOWN IMMEDIATE 
    STARTUP PFILE='E:\trans\initOLAP.ora'
    -- The following step will recompile all PL/SQL modules.
    -- It may take serveral hours to complete.
    @@ ?/rdbms/admin/utlrp.sql 
    set feedback 6;
最後，還需要重置DBID號
在目的端：

點選(此處)摺疊或開啟

  1. startup mount;
  2. execute sys.dbms_backup_restore.zerodbid(0);

  4. shutdown immediate;
  5. startup nomount;
然後，參考trans.sql中的相關語句，create controlfile建立控制檔案

最後resetlogs開啟資料庫

點選(此處)摺疊或開啟

  1. alter database open resetlogs;




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22621861/viewspace-1407496/，如需轉載，請註明出處，否則將追究法律責任。

相關文章