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

    转载请说明出处 |QQ:327488733@qq.com
  • 相关阅读:
    Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging
    KDiff3使用指南
    Win10解决修改host没有权限问题(其他文件同理) 一步都不能少哦:先添加再授权
    微信小程序
    MyBatisSystemException 【exception】
    [LeetCode] Additive Number 加法数
    [CareerCup] 14.5 Object Reflection 对象反射
    C Memory Layout C语言中的内存布局
    strtol 函数用法
    [CareerCup] 14.4 Templates Java模板
  • 原文地址:https://www.cnblogs.com/zhenxing/p/5169108.html
Copyright © 2020-2023  润新知