跨操作系统平台移动数据库（相同尾数格式）

当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);
   
3. 
   
4. shutdown immediate;
   
5. startup nomount;

然后，参考trans.sql中的相关语句，create controlfile创建控制文件

最后resetlogs打开数据库

点击(此处)折叠或打开

1. alter database open resetlogs;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22621861/viewspace-1407496/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-1407496/