一、迁移前数据库基本信息统计
查看数据库版本
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:APPORACLEORADATASIEBELDBSYSTEM01.DBF D:APPORACLEORADATASIEBELDBSYSAUX01.DBF D:APPORACLEORADATASIEBELDBUNDOTBS01.DBF D:APPORACLEORADATASIEBELDBUSERS01.DBF D:APPORACLEORADATASIEBELDBDATA01.DBF D:APPORACLEORADATASIEBELDBINDEX01.DBF D:APPORACLEORADATASIEBELDBSYSTEM02.DBF D:APPORACLEORADATASIEBELDBSYSAUX02.DBF D:APPORACLEORADATASIEBELDBEIM_IDX01.DBF
查看所有控制文件
SELECT NAME FROM V$CONTROLFILE; D:APPORACLEORADATASIEBELDBCONTROL01.CTL D:APPORACLEORADATASIEBELDBCONTROL02.CTL
查看所有日志文件
SELECT GROUP#, MEMBER FROM V$LOGFILE; D:APPORACLEORADATASIEBELDBREDO01.LOG D:APPORACLEORADATASIEBELDBREDO02.LOG D:APPORACLEORADATASIEBELDBREDO03.LOG
查看所有临时文件
SELECT NAME FROM V$TEMPFILE; D:APPORACLEORADATASIEBELDBTEMP01.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: rans_dir rans.sql' TO platform 'Linux x86 64-bit' db_file_name_convert 'D:APPORACLEORADATASIEBELDB' 'C: rans_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:apporacle" __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:APPORACLE"
五、在目标系统使用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