迁移 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