Oracle数据迁移的方法是什么

本篇内容介绍了"Oracle数据迁移的方法是什么"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

本文的环境都使用相同的操作系统：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_DIRPL/SQL procedure successfully completed.SQL> set linesize 300SQL> col directory_name for a25SQL> col directory_path for a70SQL> select directory_name,directory_path from dba_directories;

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

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 3206836224 bytesFixed Size                  2257520 bytesVariable Size             738200976 bytesDatabase Buffers         2449473536 bytesRedo Buffers               16904192 bytesDatabase 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.dbfSQL> 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文件系统临时存放源端数据文件。

[oracle@rhndb ~]$ cp /u02/oradata/rhndb/* /u03/orabak

6、创建目标库参数文件并启动至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.orarhndb.__db_cache_size=2516582400rhndb.__java_pool_size=16777216rhndb.__large_pool_size=33554432rhndb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environmentrhndb.__pga_aggregate_target=1073741824rhndb.__sga_target=3221225472rhndb.__shared_io_pool_size=0rhndb.__shared_pool_size=620756992rhndb.__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.oraORACLE instance started.Total System Global Area 3221222464 bytesFixed Size                  8901696 bytesVariable Size             671088640 bytesDatabase Buffers         2533359616 bytesRedo Buffers                7872512 bytes

7、数据文件转换

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

[oracle@db02 ~]$ rman target /--转换操作RMAN> convert from platform 'Linux x86 64-bit' parallelism 22> 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:38using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=135 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=198 device type=DISKchannel ORA_DISK_1: starting datafile conversioninput file name=/u03/orabak/undotbs01.dbfchannel ORA_DISK_2: starting datafile conversioninput file name=/u03/orabak/system01.dbfconverted datafile=+DATA/RHNDB/DATAFILE/system.258.1006801423channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:01:35converted datafile=+DATA/RHNDB/DATAFILE/undotbs1.257.1006801423channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:55Finished 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:32using channel ORA_DISK_1using channel ORA_DISK_2allocated channel: ORA_DISK_3channel ORA_DISK_3: SID=2 device type=DISKchannel ORA_DISK_1: starting datafile conversioninput file name=/u03/orabak/spw01.dbfchannel ORA_DISK_2: starting datafile conversioninput file name=/u03/orabak/sysaux01.dbfchannel ORA_DISK_3: starting datafile conversioninput file name=/u03/orabak/users01.dbfconverted datafile=+DATA/RHNDB/DATAFILE/users.261.1006801653channel ORA_DISK_3: datafile conversion complete, elapsed time: 00:00:03converted datafile=+DATA/RHNDB/DATAFILE/sysaux.260.1006801653channel ORA_DISK_2: datafile conversion complete, elapsed time: 00:00:25converted datafile=+DATA/RHNDB/DATAFILE/spacewalk.259.1006801653channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:35Finished conversion at target at 28-APR-2019 19:09:08RMAN> exit

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

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

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 2019Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle.  All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL> 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 mountORACLE instance started.Total System Global Area 3221222464 bytesFixed Size                  8901696 bytesVariable Size             671088640 bytesDatabase Buffers         2533359616 bytesRedo Buffers                7872512 bytesDatabase 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.ora

11、执行upgrade

使用dbupgrade进行升级操作：

[oracle@db02 ~]$ dbupgrade -u sys

在升级过程中，会遇到下面的错误：

ORA-02290: check constraint (SYS.JAVA_DEV_DISABLED) violated

dbupgrade会话结束后，使用下面的命令启用：

SQL> exec dbms_java_dev.enable;

此命令执行完后，会重启数据库至migrate模式，然后运行下面的命令即可完成升级。

SQL> @?/rdbms/admin/utlirp.sqlSQL> shutdown immediateSQL> startupSQL> @?/rdbms/admin/utlrp.sql

如果还有java相关的invalid对象，请参考官方的metalink（文档 ID 2262919.1）。

使用下面的命令检查组件信息：

SQL> col comp_name for a40SQL> set wrap offSQL> set pagesize 999SQL> 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 rhndbDatabase unique name: rhndbDatabase name: rhndbOracle home: /u01/app/oracle/product/19.0.0/db_1Oracle user: oracleSpfile: +data/rhndb/parameterfile/spfile.267.1006814727Password file: /u01/app/oracle/product/19.0.0/db_1/dbs/orapwrhndbDomain: Start options: openStop options: immediateDatabase role: PRIMARYManagement policy: AUTOMATICDisk Groups: DATA,FRAServices: OSDBA group: OSOPER group: Database instance: rhndb

"Oracle数据迁移的方法是什么"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！