一、迁移前数据库基本信息统计
查看数据库版本
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