Migrating Oracle 11g R2 To Oracle 19c

本文的环境都使用相同的操作系统：Oracle Linux 7.5。11g数据文件存放在文件系统上，而19c的数据文件存放在ASM上，中间使用NFS暂存数据文件，然后通过rman的可传输表空间特性进行迁移。

1、源端检查

由于rman不能自动迁移目录、外部表以及BFILEs，所以必须使用下面的命令进行检查，然后手工在目标端创建：

[[email?protected] ~]$ sqlplus "/as sysdba"
SQL> set serveroutput on;
SQL> declare x boolean;begin x:=dbms_tdb.check_external;end;
  2  /
The following directories exist in the database:
SYS.DMP,SYS.XMLDIR,SYS.ORACLE_OCM_CONFIG_DIR2,SYS.ORACLE_OCM_CONFIG_DIR,SYS.DATA_PUMP_DIR

PL/SQL procedure successfully completed.
SQL> set linesize 300
SQL> col directory_name for a25
SQL> col directory_path for a70
SQL> select directory_name,directory_path from dba_directories;

2、重启数据库至只读状态

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

Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size             738200976 bytes
Database Buffers         2449473536 bytes
Redo Buffers               16904192 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

3、DBMS_TDB.CHECK_DB检查数据库状态

SQL> set serveroutput on;
SQL> declare db_ready boolean;
  2  begin
  3  db_ready :=dbms_tdb.check_db(‘Linux x86 64-bit‘,dbms_tdb.skip_none);
  4  end;
  5  /

PL/SQL procedure successfully completed.

4、列出需要转换和不需要转换的数据文件

SQL> select file_name "Datafiles requiring Conversion" from dba_data_files where tablespace_name in (select distinct tablespace_name from dba_rollback_segs);

Datafiles requiring Conversion
------------------------------------------------------------
/u02/oradata/rhndb/undotbs01.dbf
/u02/oradata/rhndb/system01.dbf

SQL> select file_name "Files NOT requiring Conversion" from dba_data_files where tablespace_name  not in (select distinct tablespace_name from dba_rollback_segs);

Files NOT requiring Conversion
------------------------------------------------------------
/u02/oradata/rhndb/users01.dbf
/u02/oradata/rhndb/sysaux01.dbf
/u02/oradata/rhndb/spw01.dbf

5、复制源数据库的数据文件至目标端

这里的目标端使用了ASM，所以不能直接存放。因此使用了NFS文件系统临时存放源端数据文件。

[[email?protected] ~]$ cp /u02/oradata/rhndb/* /u03/orabak

6、创建目标库参数文件并启动至nomont

SQL> create pfile=‘/tmp/initrhndb.ora‘ from spfile;
[[email?protected] ~]$ scp /tmp/initrhndb.ora db02:/tmp
--修改参数文件中的audit_file_dest,control_files,db_name,db_recover_file_dest,diagnostic_dest
[[email?protected] ~]$ vi /tmp/initrhndb.ora
rhndb.__db_cache_size=2516582400
rhndb.__java_pool_size=16777216
rhndb.__large_pool_size=33554432
rhndb.__oracle_base=‘/u01/app/oracle‘#ORACLE_BASE set from environment
rhndb.__pga_aggregate_target=1073741824
rhndb.__sga_target=3221225472
rhndb.__shared_io_pool_size=0
rhndb.__shared_pool_size=620756992
rhndb.__streams_pool_size=0
*.audit_file_dest=‘/u01/app/oracle/admin/rhndb/adump‘
*.audit_trail=‘db‘
*.compatible=‘11.2.0.4.0‘
*.control_files=‘+DATA/rhndb/controlfile/control01.ctl‘,‘+FRA/rhndb/controlfile/control02.ctl‘
*.db_block_size=8192
*.db_domain=‘‘
*.db_name=‘rhndb‘
*.db_recovery_file_dest=‘+FRA‘
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest=‘/u01/app/oracle‘
*.dispatchers=‘(PROTOCOL=TCP) (SERVICE=rhndbXDB)‘
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=150
*.remote_login_passwordfile=‘EXCLUSIVE‘
*.sga_target=3221225472
*.undo_tablespace=‘UNDOTBS1‘
*._allow_resetlogs_corruption=true
--启动时nomount状态
[[email?protected] ~]$ sqlplus "/as sysdba"
SQL> startup nomount pfile=/tmp/initrhndb.ora
ORACLE instance started.

Total System Global Area 3221222464 bytes
Fixed Size                  8901696 bytes
Variable Size             671088640 bytes
Database Buffers         2533359616 bytes
Redo Buffers                7872512 bytes

7、数据文件转换

不论两个平台的endian format是否相同，都需要进行转换操作。根据第四步的信息，在rman中执行转换操作，如下：

[[email?protected] ~]$ rman target /
--转换操作
RMAN> convert from platform ‘Linux x86 64-bit‘ parallelism 2
2> datafile ‘/u03/orabak/system01.dbf‘ format ‘+data‘
3> datafile ‘/u03/orabak/undotbs01.dbf‘ format ‘+data‘;

Starting conversion at target at 28-APR-2019 19:03:38
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=135 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=198 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/u03/orabak/undotbs01.dbf
channel ORA_DISK_2: starting datafile conversion
input file name=/u03/orabak/system01.dbf
converted datafile=+DATA/RHNDB/DATAFILE/system.258.1006801423
channel ORA_DISK_2: datafile conversion complete,elapsed time: 00:01:35
converted datafile=+DATA/RHNDB/DATAFILE/undotbs1.257.1006801423
channel ORA_DISK_1: datafile conversion complete,elapsed time: 00:01:55
Finished conversion at target at 28-APR-2019 19:05:36

--通过rman复制数据文件至ASM磁盘组
RMAN> convert parallelism 3 
2> datafile ‘/u03/orabak/users01.dbf‘ format ‘+data‘
3> datafile ‘/u03/orabak/sysaux01.dbf‘ format ‘+data‘
4> datafile ‘/u03/orabak/spw01.dbf‘ format ‘+data‘;

Starting conversion at target at 28-APR-2019 19:07:32
using channel ORA_DISK_1
using channel ORA_DISK_2
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=2 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/u03/orabak/spw01.dbf
channel ORA_DISK_2: starting datafile conversion
input file name=/u03/orabak/sysaux01.dbf
channel ORA_DISK_3: starting datafile conversion
input file name=/u03/orabak/users01.dbf
converted datafile=+DATA/RHNDB/DATAFILE/users.261.1006801653
channel ORA_DISK_3: datafile conversion complete,elapsed time: 00:00:03
converted datafile=+DATA/RHNDB/DATAFILE/sysaux.260.1006801653
channel ORA_DISK_2: datafile conversion complete,elapsed time: 00:00:25
converted datafile=+DATA/RHNDB/DATAFILE/spacewalk.259.1006801653
channel ORA_DISK_1: datafile conversion complete,elapsed time: 00:01:35
Finished conversion at target at 28-APR-2019 19:09:08
RMAN> exit

8、创建目标端的控制文件

在源端使用下面的命令创建目标端的控制文件：

SQL> alter database backup controlfile to trace resetlogs;

（编辑：上饶站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!