Oracle 数据库迁移

将数据从Linux环境迁移至Windows 2012R2，并更改数据库实例名。

	源数据库	目标数据库
操作系统	Linux	Windows 2012R2
实例名称	TRN	TEST
模式	RAC	SINGLE
数据文件	/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile	D:\Oradata\Test
数据库版本	12.1.0.2	12.1.0.2

1. 检查环境
1.1 确认源数据库和目标数据库的endian format相同

SELECT tp.platform_name, 
       tp.endian_format
FROM v$transportable_platform tp, 
     v$database db
WHERE tp.platform_name = db.platform_name

2. 准备工作
2.1 创建转换文件存放目录

[oracle@pmsdb1 datafile]$ cd /u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN
[oracle@pmsdb1 TRN]$ mkdir transdb
[oracle@pmsdb1 TRN]$

2.2 将源数据库以单节点模式启动

[root@pmsdb1 ~]# su - oracle
[oracle@pmsdb1 ~]$ srvctl stop database -db trn
[oracle@pmsdb1 ~]$ export ORACLE_SID=TRN1
[oracle@pmsdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 6 12:42:22 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.7180E+10 bytes
Fixed Size            3731384 bytes
Variable Size         2617245768 bytes
Database Buffers     1.4496E+10 bytes
Redo Buffers           63377408 bytes
Database mounted.
SQL>

2.3 以只读方式打开数据库

SQL> alter database open read only;

Database altered.

SQL>

2.4 执行下面2个脚本检查数据库能否被传输以及列出外部表和DIRECTORY等无法传输的对象信息

SQL> set serveroutput on
SQL> declare
  db_ready boolean;
begin
  /* db_ready is ignored, but with SERVEROUTPUT set to ON any
   * conditions preventing transport will be output to console */
  db_ready := dbms_tdb.check_db('Microsoft Windows x86 64-bit', dbms_tdb.skip_none);
end;
/

PL/SQL procedure successfully completed.

注：如果没有出现警告信息则表示可以转换整个数据库。

SQL> 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 external tables exist in the database:
SYS.OPATCH_XML_INV, SYS.ALERT_LOG_VIEW1
The following directories exist in the database:
SYS.PREUPG_OUTPUT_DIR, SYS.TOAD_BDUMP_DIR_2, SYS.ALERT_DIR, SYS.EXP_DIR, SYS.BDUMP1, SYS.XSDDIR, SYS.DATA_PUMP_DIR, SYS.OPATCH_INST_DIR,
SYS.OPATCH_SCRIPT_DIR, SYS.OPATCH_LOG_DIR, SYS.ORACLE_BASE

PL/SQL procedure successfully completed.
SQL>

2.5 创建pfile文件

SQL> create pfile='/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/initTest.ora' from spfile;

File created.

SQL>

3. 数据库文件转换
3.1 登录RMAN

SQL> host rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jun 6 12:45:43 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TRN (DBID=372220850)

RMAN>

3.2 执行脚本

RMAN> convert database new database 'TEST'
transport script '/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/clonedb.sql'
to platform 'Microsoft transdb x86 64-bit'
db_file_name_convert '/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile','/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb';

Starting conversion at source at 06-JUN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=313 instance=TRN1 device type=DISK

External table SYS.OPATCH_XML_INV found in the database
External table SYS.ALERT_LOG_VIEW1 found in the database

Directory SYS.PREUPG_OUTPUT_DIR found in the database
Directory SYS.TOAD_BDUMP_DIR_2 found in the database
Directory SYS.ALERT_DIR found in the database
Directory SYS.EXP_DIR found in the database
Directory SYS.BDUMP1 found in the database
Directory SYS.XSDDIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.OPATCH_INST_DIR found in the database
Directory SYS.OPATCH_SCRIPT_DIR found in the database
Directory SYS.OPATCH_LOG_DIR found in the database
Directory SYS.ORACLE_BASE found in the database
Directory SYS.ORACLE_HOME found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
User SYSDG with SYSDG privilege found in password file
User SYSBACKUP with SYSBACKUP privilege found in password file
User SYSKM with SYSKM privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/system01.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00006 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/ifsapp_data01.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/ifsapp_data01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00012 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/ifsapp_lob.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/ifsapp_lob.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/undotbs01.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/sysaux01.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00007 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/ifsapp_index01.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/ifsapp_index01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/undotbs02.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/undotbs02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00008 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/ifsapp_archive_data01.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/ifsapp_archive_data01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00009 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/ifsapp_archive_index01.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/ifsapp_archive_index01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00010 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/ifsapp_report_data01.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/ifsapp_report_data01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00011 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/ifsapp_report_index01.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/ifsapp_report_index01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/TRN/datafile/users01.dbf
converted datafile=/u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Edit init.ora file /u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/init_00t4p2k9_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /u02/app/oracle/oradata/datastore/.ACFS/snaps/TRN/transdb/transdb.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 06-JUN-18

RMAN

4. 将transdb目录中所有文件传输至Windows系统目标数据库目录
5. 在Windows中创建数据库实例
5.1 修改initTest.ora参数，创建数据库实例

C:\Users\Administrator>cd /

C:\>oradim -new -sid Test -intpwd oracle -pfile D:\Oradata\Test\initTest.ora
Instance created.

C:\>

5.2 修改clonedb.sql参数如下

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "TEST" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\Oradata\Test\redo01.log'  SIZE 1024M BLOCKSIZE 512,
  GROUP 2 'D:\Oradata\Test\redo02.log'  SIZE 1024M BLOCKSIZE 512
DATAFILE
  'D:\Oradata\Test\system01.dbf',
  'D:\Oradata\Test\sysaux01.dbf',
  'D:\Oradata\Test\undotbs01.dbf',
  'D:\Oradata\Test\undotbs02.dbf',
  'D:\Oradata\Test\users01.dbf',
  'D:\Oradata\Test\ifsapp_data01.dbf',
  'D:\Oradata\Test\ifsapp_index01.dbf',
  'D:\Oradata\Test\ifsapp_archive_data01.dbf',
  'D:\Oradata\Test\ifsapp_archive_index01.dbf',
  'D:\Oradata\Test\ifsapp_report_data01.dbf',
  'D:\Oradata\Test\ifsapp_report_index01.dbf',
  'D:\Oradata\Test\ifsapp_lob.dbf'
CHARACTER SET AL32UTF8
;

ALTER DATABASE OPEN RESETLOGS UPGRADE;

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\Oradata\Test\TEMP01.DBF'
     SIZE 500M  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

5.3 执行clonedb.sql脚本

C:\>set oracle_sid=Test

C:\>sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 6 15:46:19 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> @D:\Oradata\Test\clone.sql
ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  3045360 bytes
Variable Size             721422352 bytes
Database Buffers          603979776 bytes
Redo Buffers               13729792 bytes

Control file created.


Database altered.


Tablespace altered.

SQL>

5.4 关闭数据库，启动至升级模式，执行升级脚本utlirp.sql

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  3045360 bytes
Variable Size             721422352 bytes
Database Buffers          603979776 bytes
Redo Buffers               13729792 bytes
Database mounted.
Database opened.
SQL> @D:\Oracle\product\12.1.0\dbhome_1\RDBMS\ADMIN\utlirp.sql

5.5 重启数据库，执行utlrp.sql 脚本，编译无效对象

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  3045360 bytes
Variable Size             721422352 bytes
Database Buffers          603979776 bytes
Redo Buffers               13729792 bytes
Database mounted.
Database opened.
SQL> @D:\Oracle\product\12.1.0\dbhome_1\RDBMS\ADMIN\utlrp.sql

6. 数据库迁移完成。

posted @ 2018-06-06 15:06 生有涯、知无涯阅读(977) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

他山之石

Oracle 数据库迁移

公告