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

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

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

[oracle@rhndb~]$sqlplus"/assysdba"SQL>setserveroutputon;SQL>declarexboolean;beginx:=dbms_tdb.check_external;end;2/Thefollowingdirectoriesexistinthedatabase:SYS.DMP,SYS.XMLDIR,SYS.ORACLE_OCM_CONFIG_DIR2,SYS.ORACLE_OCM_CONFIG_DIR,SYS.DATA_PUMP_DIRPL/SQLproceduresuccessfullycompleted.SQL>setlinesize300SQL>coldirectory_namefora25SQL>coldirectory_pathfora70SQL>selectdirectory_name,directory_pathfromdba_directories;2、重启数据库至只读状态

SQL>shutdownimmediateDatabaseclosed.Databasedismounted.ORACLEinstanceshutdown.SQL>startupmountORACLEinstancestarted.TotalSystemGlobalArea3206836224bytesFixedSize2257520bytesVariableSize738200976bytesDatabaseBuffers2449473536bytesRedoBuffers16904192bytesDatabasemounted.SQL>alterdatabaseopenreadonly;Databasealtered.3、DBMS_TDB.CHECK_DB检查数据库状态

SQL>setserveroutputon;SQL>declaredb_readyboolean;2begin3db_ready:=dbms_tdb.check_db('Linuxx8664-bit',dbms_tdb.skip_none);4end;5/PL/SQLproceduresuccessfullycompleted.4、列出需要转换和不需要转换的数据文件

SQL>selectfile_name"DatafilesrequiringConversion"fromdba_data_fileswheretablespace_namein(selectdistincttablespace_namefromdba_rollback_segs);DatafilesrequiringConversion------------------------------------------------------------/u02/oradata/rhndb/undotbs01.dbf/u02/oradata/rhndb/system01.dbfSQL>selectfile_name"FilesNOTrequiringConversion"fromdba_data_fileswheretablespace_namenotin(selectdistincttablespace_namefromdba_rollback_segs);FilesNOTrequiringConversion------------------------------------------------------------/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>createpfile='/tmp/initrhndb.ora'fromspfile;[oracle@rhndb~]$scp/tmp/initrhndb.oradb02:$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_BASEsetfromenvironmentrhndb.__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"/assysdba"SQL>startupnomountpfile=/tmp/initrhndb.oraORACLEinstancestarted.TotalSystemGlobalArea3221222464bytesFixedSize8901696bytesVariableSize671088640bytesDatabaseBuffers2533359616bytesRedoBuffers7872512bytes7、数据文件转换

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

[oracle@db02~]$rmantarget/--转换操作RMAN>convertfromplatform'Linuxx8664-bit'parallelism22>datafile'/u03/orabak/system01.dbf'format'+data'3>datafile'/u03/orabak/undotbs01.dbf'format'+data';Startingconversionattargetat28-APR-201919:03:38usingtargetdatabasecontrolfileinsteadofrecoverycatalogallocatedchannel:ORA_DISK_1channelORA_DISK_1:SID=135devicetype=DISKallocatedchannel:ORA_DISK_2channelORA_DISK_2:SID=198devicetype=DISKchannelORA_DISK_1:startingdatafileconversioninputfilename=/u03/orabak/undotbs01.dbfchannelORA_DISK_2:startingdatafileconversioninputfilename=/u03/orabak/system01.dbfconverteddatafile=+DATA/RHNDB/DATAFILE/system.258.1006801423channelORA_DISK_2:datafileconversioncomplete,elapsedtime:00:01:35converteddatafile=+DATA/RHNDB/DATAFILE/undotbs1.257.1006801423channelORA_DISK_1:datafileconversioncomplete,elapsedtime:00:01:55Finishedconversionattargetat28-APR-201919:05:36--通过rman复制数据文件至ASM磁盘组RMAN>convertparallelism32>datafile'/u03/orabak/users01.dbf'format'+data'3>datafile'/u03/orabak/sysaux01.dbf'format'+data'4>datafile'/u03/orabak/spw01.dbf'format'+data';Startingconversionattargetat28-APR-201919:07:32usingchannelORA_DISK_1usingchannelORA_DISK_2allocatedchannel:ORA_DISK_3channelORA_DISK_3:SID=2devicetype=DISKchannelORA_DISK_1:startingdatafileconversioninputfilename=/u03/orabak/spw01.dbfchannelORA_DISK_2:startingdatafileconversioninputfilename=/u03/orabak/sysaux01.dbfchannelORA_DISK_3:startingdatafileconversioninputfilename=/u03/orabak/users01.dbfconverteddatafile=+DATA/RHNDB/DATAFILE/users.261.1006801653channelORA_DISK_3:datafileconversioncomplete,elapsedtime:00:00:03converteddatafile=+DATA/RHNDB/DATAFILE/sysaux.260.1006801653channelORA_DISK_2:datafileconversioncomplete,elapsedtime:00:00:25converteddatafile=+DATA/RHNDB/DATAFILE/spacewalk.259.1006801653channelORA_DISK_1:datafileconversioncomplete,elapsedtime:00:01:35Finishedconversionattargetat28-APR-201919:09:08RMAN>exit8、创建目标端的控制文件

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

SQL>alterdatabasebackupcontrolfiletotraceresetlogs;

生成的trace文件路径可以通过alter日志进行查看，然后根据实际情况进行修改。修改完后在目标端执行，如下：

[oracle@db02~]$sqlplus"/assysdba"SQL*Plus:Release19.0.0.0.0-ProductiononSunApr2819:14:242019Version19.3.0.0.0Copyright(c)1982,2019,Oracle.Allrightsreserved.Connectedto:OracleDatabase19cEnterpriseEditionRelease19.0.0.0.0-ProductionVersion19.3.0.0.0SQL>CREATECONTROLFILEREUSEDATABASE"RHNDB"RESETLOGSNOARCHIVELOG2MAXLOGFILES163MAXLOGMEMBERS34MAXDATAFILES1005MAXINSTANCES86MAXLOGHISTORY29207LOGFILE8GROUP1'+DATA/rhndb/redo01.log'SIZE50MBLOCKSIZE512,9GROUP2'+DATA/rhndb/redo02.log'SIZE50MBLOCKSIZE512,10GROUP3'+DATA/rhndb/redo03.log'SIZE50MBLOCKSIZE51211--STANDBYLOGFILE12DATAFILE13'+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'18CHARACTERSETAL32UTF8;Controlfilecreated.

另外复制源库的密码文件至目标库的指定目录：

[oracle@rhndbdbs]$scporapwrhndbdb02:/u01/app/oracle/product/19.0.0/db_1/dbs/9、以 resetlogs upgrade 方式打开数据库并创建临时表空间

SQL>startupmountORACLEinstancestarted.TotalSystemGlobalArea3221222464bytesFixedSize8901696bytesVariableSize671088640bytesDatabaseBuffers2533359616bytesRedoBuffers7872512bytesDatabasemounted.SQL>alterdatabaseopenresetlogsupgrade;Databasealtered.SQL>altertablespacetempaddtempfile'+data'size50Mautoextendonnext100mmaxsizeunlimited;Tablespacealtered.10、创建SPFILE

SQL>createspfile='+data'frompfile;--新建的spfile名称可以在asm磁盘组中查看[oracle@rhndbdbs]$mvinitrhndb.orainitrhndb.ora.old[oracle@db02dbs]$echo'SPFILE='+data/rhndb/parameterfile/spfile.267.1006905749''>initrhndb.ora11、执行upgrade

使用dbupgrade进行升级操作：

[oracle@db02~]$dbupgrade-usys

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

ORA-02290:checkconstraint(SYS.JAVA_DEV_DISABLED)violated

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

SQL>execdbms_java_dev.enable;

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

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

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

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

SQL>colcomp_namefora40SQL>setwrapoffSQL>setpagesize999SQL>selectcomp_name,version,statusfromdba_registry;12、注册数据库资源（可选）

[oracle@db02~]$srvctladddatabase-dbrhndb-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-roleprimary-dbnamerhndb-diskgroup'data,fra'[oracle@db02~]$srvctlconfigdatabase-dbrhndbDatabaseuniquename:rhndbDatabasename:rhndbOraclehome:/u01/app/oracle/product/19.0.0/db_1Oracleuser:oracleSpfile:+data/rhndb/parameterfile/spfile.267.1006814727Passwordfile:/u01/app/oracle/product/19.0.0/db_1/dbs/orapwrhndbDomain:Startoptions:openStopoptions:immediateDatabaserole:PRIMARYManagementpolicy:AUTOMATICDiskGroups:DATA,FRAServices:OSDBAgroup:OSOPERgroup:Databaseinstance:rhndb

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