共计 8710 个字符，预计需要花费 22 分钟才能阅读完成。
本篇内容介绍了“Oracle 数据迁移的方法是什么”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
本文的环境都使用相同的操作系统：Oracle Linux 7.5。11g 数据文件存放在文件系统上，而 19c 的数据文件存放在 ASM 上，中间使用 NFS 暂存数据文件，然后通过 rman 的可传输表空间特性进行迁移。
1、源端检查
由于 rman 不能自动迁移目录、外部表以及 BFILEs，所以必须使用下面的命令进行检查，然后手工在目标端创建：
[oracle@rhndb ~]$ 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.dbf5、复制源数据库的数据文件至目标端
这里的目标端使用了 ASM，所以不能直接存放。因此使用了 NFS 文件系统临时存放源端数据文件。
[oracle@rhndb ~]$ cp /u02/oradata/rhndb/* /u03/orabak6、创建目标库参数文件并启动至 nomont
SQL create pfile= /tmp/initrhndb.ora from spfile;
[oracle@rhndb ~]$ scp /tmp/initrhndb.ora db02:$ORACLE_HOME/dbs
-- 修改参数文件中的 audit_file_dest,control_files,db_name,db_recover_file_dest,diagnostic_dest
[oracle@rhndb ~]$ vi $ORACLE_HOME/dbs/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 状态
[oracle@db02 ~]$ 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 bytes7、数据文件转换
不论两个平台的 endian format 是否相同，都需要进行转换操作。根据第四步的信息，在 rman 中执行转换操作，如下：
[oracle@db02 ~]$ 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 exit8、创建目标端的控制文件
在源端使用下面的命令创建目标端的控制文件：
SQL alter database backup controlfile to trace resetlogs;生成的 trace 文件路径可以通过 alter 日志进行查看，然后根据实际情况进行修改。修改完后在目标端执行，如下：
[oracle@db02 ~]$ sqlplus /as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 28 19:14:24 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL CREATE CONTROLFILE REUSE DATABASE RHNDB RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 2920
7 LOGFILE
8 GROUP 1 +DATA/rhndb/redo01.log SIZE 50M BLOCKSIZE 512,
9 GROUP 2 +DATA/rhndb/redo02.log SIZE 50M BLOCKSIZE 512,
10 GROUP 3 +DATA/rhndb/redo03.log SIZE 50M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 +DATA/RHNDB/DATAFILE/system.258.1006801423 ,
14 +DATA/RHNDB/DATAFILE/undotbs1.257.1006801423 ,
15 +DATA/RHNDB/DATAFILE/users.261.1006801653 ,
16 +DATA/RHNDB/DATAFILE/sysaux.260.1006801653 ,
17 +DATA/RHNDB/DATAFILE/spacewalk.259.1006801653
18 CHARACTER SET AL32UTF8;
Control file created.另外复制源库的密码文件至目标库的指定目录：
[oracle@rhndb dbs]$ scp orapwrhndb db02:/u01/app/oracle/product/19.0.0/db_1/dbs/9、以 resetlogs upgrade 方式打开数据库并创建临时表空间
SQL startup mount
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
Database mounted.
SQL alter database open resetlogs upgrade;
Database altered.
SQL alter tablespace temp add tempfile +data size 50M autoextend on next 100m maxsize unlimited;
Tablespace altered.10、创建 SPFILE
SQL create spfile= +data from pfile;
-- 新建的 spfile 名称可以在 asm 磁盘组中查看
[oracle@rhndb dbs]$ mv initrhndb.ora initrhndb.ora.old
[oracle@db02 dbs]$ echo SPFILE= +data/rhndb/parameterfile/spfile.267.1006905749 initrhndb.ora11、执行 upgrade
使用 dbupgrade 进行升级操作：
[oracle@db02 ~]$ dbupgrade -u sys在升级过程中，会遇到下面的错误：
ORA-02290: check constraint (SYS.JAVA_DEV_DISABLED) violateddbupgrade 会话结束后，使用下面的命令启用：
SQL exec dbms_java_dev.enable;此命令执行完后，会重启数据库至 migrate 模式，然后运行下面的命令即可完成升级。
SQL @?/rdbms/admin/utlirp.sql
SQL shutdown immediate
SQL startup
SQL @?/rdbms/admin/utlrp.sql如果还有 java 相关的 invalid 对象，请参考官方的 metalink（文档 ID 2262919.1）。
使用下面的命令检查组件信息：
SQL col comp_name for a40
SQL set wrap off
SQL set pagesize 999
SQL select comp_name,version, status from dba_registry;12、注册数据库资源（可选）
[oracle@db02 ~]$ srvctl add database -db rhndb -oraclehome /u01/app/oracle/product/19.0.0/db_1 -spfile +data/rhndb/parameterfile/spfile.267.1006814727 -pwfile /u01/app/oracle/product/19.0.0/db_1/dbs/orapwrhndb -role primary -dbname rhndb -diskgroup data,fra
[oracle@db02 ~]$ srvctl config database -db rhndb
Database unique name: rhndb
Database name: rhndb
Oracle home: /u01/app/oracle/product/19.0.0/db_1
Oracle user: oracle
Spfile: +data/rhndb/parameterfile/spfile.267.1006814727
Password file: /u01/app/oracle/product/19.0.0/db_1/dbs/orapwrhndb
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA,FRA
Services:
OSDBA group:
OSOPER group:
Database instance: rhndb“Oracle 数据迁移的方法是什么”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！
正文完
