本篇內容介紹了“Oracle數據遷移的方法是什么”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
本文的環境都使用相同的操作系統：Oracle Linux 7.5。11g數據文件存放在文件系統上，而19c的數據文件存放在ASM上，中間使用NFS暫存數據文件，然后通過rman的可傳輸表空間特性進行遷移。
由于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;
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.
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.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
這里的目標端使用了ASM，所以不能直接存放。因此使用了NFS文件系統臨時存放源端數據文件。
[oracle@rhndb ~]$ cp /u02/oradata/rhndb/* /u03/orabak
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 bytes
不論兩個平臺的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> exit
在源端使用下面的命令創建目標端的控制文件：
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/
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.
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
使用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.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;
[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數據遷移的方法是什么”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
