迁移 Windows 上 Oracle 11.2.0.3.0 到 Linux 上 Oracle 11.2.0.3.0

一、迁移前数据库基本信息统计

查看数据库版本

SELECT * FROM V$VERSION;

/*

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

"CORE 11.2.0.3.0 Production"

TNS for 64-bit Windows: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;

查看所有数据文件

SELECT NAME FROM V$DATAFILE;

D:\APP\ORACLE\ORADATA\SIEBELDB\SYSTEM01.DBF

D:\APP\ORACLE\ORADATA\SIEBELDB\SYSAUX01.DBF

D:\APP\ORACLE\ORADATA\SIEBELDB\UNDOTBS01.DBF

D:\APP\ORACLE\ORADATA\SIEBELDB\USERS01.DBF

D:\APP\ORACLE\ORADATA\SIEBELDB\DATA01.DBF

D:\APP\ORACLE\ORADATA\SIEBELDB\INDEX01.DBF

D:\APP\ORACLE\ORADATA\SIEBELDB\SYSTEM02.DBF

D:\APP\ORACLE\ORADATA\SIEBELDB\SYSAUX02.DBF

D:\APP\ORACLE\ORADATA\SIEBELDB\EIM_IDX01.DBF

查看所有控制文件

SELECT NAME FROM V$CONTROLFILE;

D:\APP\ORACLE\ORADATA\SIEBELDB\CONTROL01.CTL

D:\APP\ORACLE\ORADATA\SIEBELDB\CONTROL02.CTL

查看所有日志文件

SELECT GROUP#, MEMBER FROM V$LOGFILE;

D:\APP\ORACLE\ORADATA\SIEBELDB\REDO01.LOG

D:\APP\ORACLE\ORADATA\SIEBELDB\REDO02.LOG

D:\APP\ORACLE\ORADATA\SIEBELDB\REDO03.LOG

查看所有临时文件

SELECT NAME FROM V$TEMPFILE;

D:\APP\ORACLE\ORADATA\SIEBELDB\TEMP01.DBF

查看当前的操作系统平台

SELECT D.PLATFORM_NAME, ENDIAN_FORMAT

  FROM V$TRANSPORTABLE_PLATFORM TP, V$DATABASE D

 WHERE TP.PLATFORM_NAME = D.PLATFORM_NAME;

PLATFORM_NAME  　　　　　　　　　　ENDIAN_FORMAT
Microsoft Windows x86 64-bit    Little

查看可以迁移的平台

SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY 3;

PLATFORM_ID  PLATFORM_NAME                     ENDIAN_FORMAT

12           Microsoft Windows x86 64-bit      Little

13           Linux x86 64-bit                  Little

查看数据库字符集

select * from nls_database_parameters;

PARAMETER    VALUE

NLS_LANGUAGE    AMERICAN

NLS_TERRITORY    AMERICA

NLS_CURRENCY    $

NLS_ISO_CURRENCY    AMERICA

NLS_NUMERIC_CHARACTERS    .,

NLS_CHARACTERSET    AL32UTF8

NLS_CALENDAR    GREGORIAN

NLS_DATE_FORMAT    DD-MON-RR

NLS_DATE_LANGUAGE    AMERICAN

NLS_SORT    BINARY

NLS_TIME_FORMAT    HH.MI.SSXFF AM

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    UTF8

NLS_RDBMS_VERSION    11.2.0.3.0

将数据库开启到read only模式后运行校验包

SHUTDOWN IMMEDIATE;

STARTUP MOUNT

ALTER DATABASE OPEN READ ONLY;

--返回TRUE或成功运行(无报错,表示正常)

--If no warnings appear, or if DBMS_TDB.CHECK_DB returns TRUE, then you can transport the database.

SET SERVEROUTPUT ON

DECLARE

  db_ready BOOLEAN;

BEGIN

  db_ready :=DBMS_TDB.CHECK_DB('Microsoft Windows x86 64-bit',DBMS_TDB.SKIP_READONLY);

END

校验是否存在RMAN不能自动转换的文件

--使用DBMS_TDB.CHECK_EXTERNAL函数检查是否存在任何外部表、目录或BFILES数据,使用RMAN的方式不能传输这些文件,需要手动重建或手动拷贝到目标库.

--Execute the DBMS_TDB.CHECK_EXTERNAL function to identify any external tables, directories, or BFILEs. RMAN cannot automate the transport of these

--files, so you must copy the files manually and re-create the database directories

DECLARE

external BOOLEAN;

BEGIN

/* value of external is ignored, but with SERVEROUTPUT set to ON

* dbms_tdb.check_external displays report of external objects

* on console */

external := DBMS_TDB.CHECK_EXTERNAL;

END;

/

The following directories exist in the database:
SYS.TRAN_TBS_DIR, SYS.TEST_DIR, SYS.XMLDIR, SYS.ORACLE_OCM_CONFIG_DIR2, SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR

--查看不能转换的目录

COL OWNER FOR A10

COL DIRECTORY_NAME FOR A40

COL DIRECTORY_PATH FOR A60

SELECT * FROM DBA_DIRECTORIES;

二、使用RMAN生成迁移文件

--启动数据库到Read Only模式

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN READ ONLY;

--启动RMAN

RMAN target /

CONVERT DATABASE

NEW DATABASE 'siebeldb'

transport script 'C:\trans_dir\trans.sql'

TO platform 'Linux x86 64-bit'

db_file_name_convert 'D:\APP\ORACLE\ORADATA\SIEBELDB' 'C:\trans_dir';

三、修改对应转换文件参数

在linux上设置ORACLE_BASE,ORACLE_HOME

在源系统上会生成好转换好的数据库dbf文件和转换文件脚本trans.sql

需要编辑trans.sql文件将其中数据文件参数文件等信息改为Linux相应的目录

-- 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='/data/oracle/app/database/11.2.0.3/dbhome_1/dbs/initsiebeldb.ora'

CREATE CONTROLFILE REUSE SET DATABASE "SIEBELDB" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/data/oracle/oradata/siebeldb/redolog01.log'  SIZE 500M BLOCKSIZE 512,

  GROUP 2 '/data/oracle/oradata/siebeldb/redolog02.log'  SIZE 500M BLOCKSIZE 512,

  GROUP 3 '/data/oracle/oradata/siebeldb/redolog03.log'  SIZE 500M BLOCKSIZE 512

DATAFILE

  '/data/oracle/oradata/siebeldb/SYSTEM01.DBF',

  '/data/oracle/oradata/siebeldb/SYSAUX01.DBF',

  '/data/oracle/oradata/siebeldb/UNDOTBS01.DBF',

  '/data/oracle/oradata/siebeldb/USERS01.DBF',

  '/data/oracle/oradata/siebeldb/DATA01.DBF',

  '/data/oracle/oradata/siebeldb/INDEX01.DBF',

  '/data/oracle/oradata/siebeldb/SYSTEM02.DBF',

  '/data/oracle/oradata/siebeldb/SYSAUX02.DBF',

  '/data/oracle/oradata/siebeldb/EIM_IDX01.DBF'

CHARACTER SET AL32UTF8

;

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE '/data/oracle/oradata/siebeldb/temp01.dbf'

     SIZE 3167M 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='/data/oracle/app/database/11.2.0.3/dbhome_1/dbs/initsiebeldb.ora'

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE='/data/oracle/app/database/11.2.0.3/dbhome_1/dbs/initsiebeldb.ora'

-- The following step will recompile all PL/SQL modules.

-- It may take serveral hours to complete.

@@ ?/rdbms/admin/utlrp.sql

set feedback 6;

四、修改生成的INIT参数文件

在RMAN转换完后也会生成init的参数文件，在windows的 database目录下

将转换用的init文件重命名为initSID.ora，并修改其中的参数路径对应到linux的路径下

# Please change the values of the following parameters:

control_files            = '/data/oracle/oradata/siebeldb/control01.ctl'

audit_file_dest          = '/data/oracle/admin/siebeldb/adump'

db_name                  = 'SIEBELDB'

# Please review the values of the following parameters:

# __oracle_base            = "D:\app\oracle"

__shared_pool_size       = 788529152

__large_pool_size        = 16777216

__java_pool_size         = 16777216

__streams_pool_size      = 16777216

__sga_target             = 2634022912

__db_cache_size          = 1761607680

__shared_io_pool_size    = 0

remote_login_passwordfile= "EXCLUSIVE"

db_domain                = ""

dispatchers              = "(PROTOCOL=TCP) (SERVICE=siebeldbXDB)"

__pga_aggregate_target   = 536870912

# The values of the following parameters are from source database:

processes                = 1500

sessions                 = 2272

sga_target               = 2634022912

db_block_size            = 8192

compatible               = "11.2.0.0.0"

undo_tablespace          = "UNDOTBS1"

audit_trail              = "OS"

open_cursors             = 300

optimizer_index_cost_adj = 1

pga_aggregate_target     = 524288000

# diagnostic_dest          = "D:\APP\ORACLE"

五、在目标系统使用DBCA建库

1、建议使用DBCA建库,避免手动建库出现部分目录没创建的问题

2、建库完毕后关闭数据库,并把所有的数据文件、日志文件、控制文件都删除(也就是/.../oradata/目录下都删除)

六、复制源系统的RMAN迁移文件到目标系统指定目录

1、将Windows上RMAN生成的转换完成的数据文件等使用FTP工具传输到Linux上的$ORACLE_BASE/oradata/$SID目录下
2、将生成的init参数文件复制到Linux下$ORACLE_HOME/dbs目录下

七、执行迁移脚本

sqlplus / as sysdba

@/data/oracle/oradata/siebeldb/trans.sql

--脚本会跑一段时间，只需要等待完成即可

八、注意事项

1、Oracle的版本在Windows上与Linux上要一样，不然会存在问题.

2、建议用DBCA建库，防止人为错误.

3、修改init和trans.sql转换文件时，对应的文件夹及路径不要写错.

4、用RMAN的convert功能比较强大,不用担心其它问题,也是oracle的建议事项.

九、参考文档

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmxplat.htm#BRADV89977