Oracle 10g DG 數據文件遷移的實現

2024-08-29 14:01:04

字體：大中小

來源：轉載

供稿：網友

背景：某客戶oracle/198144.html">Oracle 10g 的DG由于空間不足，之前將部分數據文件遷移到其他目錄，如今原目錄擴容成功，要將之前遷移的數據文件再次遷移回來。

環境：Oracle 10.2.0.5 DG 單機

首先想到的是10gDG是在mount模式下應用的，在測試環境可以很容易的模擬下這個需求實現的過程：

1.查詢當前DG的狀態
2.停止DG應用
3.備份copy副本到新目錄并切換
4.刪除之前的目錄并開啟應用

1.查詢當前DG的狀態

查詢當前DG的狀態:

 Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select name, database_role, open_mode from gv$database;NAME   DATABASE_ROLE  OPEN_MODE--------- ---------------- ----------JY    PHYSICAL STANDBY MOUNTEDSQL> select recovery_mode from v$archive_dest_status;RECOVERY_MODE-----------------------MANAGED REAL TIME APPLYIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLE11 rows selected.SQL> select * from v$dataguard_stats;NAME                VALUE                              UNIT              TIME_COMPUTED---------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------apply finish time         +00 00:00:00.0                          day(2) to second(1) interval  05-MAY-2018 10:04:20apply lag             +00 00:00:12                           day(2) to second(0) interval  05-MAY-2018 10:04:20estimated startup time       41                                second             05-MAY-2018 10:04:20standby has been open       N                                                05-MAY-2018 10:04:20transport lag           +00 00:00:00                           day(2) to second(0) interval  05-MAY-2018 10:04:20

可以看到DG處于正常應用狀態。

2.停止DG應用

停止DG應用：

 SQL> alter database recover managed standby database cancel;Database altered.

3.備份copy副本到新目錄并切換

3.1 確認需要遷移的數據文件

查看當前的數據文件，確認將9,10,11三個文件遷移回原來的目錄：

 SQL> select file#, name from v$datafile;   FILE# NAME---------- -------------------------------------------------------     1 /oradata/jy/datafile/system.256.839673875     2 /oradata/jy/datafile/undotbs1.258.839673877     3 /oradata/jy/datafile/sysaux.257.839673877     4 /oradata/jy/datafile/users.259.839673877     5 /oradata/jy/datafile/example.267.839673961     6 /oradata/jy/datafile/undotbs2.268.839674103     7 /oradata/jy/datafile/dbs_d_school.276.840618437     8 /oradata/jy/datafile/dbs_cssf_gt.289.848228741     9 /datafile/dbs_data9.dbf    10 /datafile/dbs_data10.dbf    11 /datafile/dbs_data11.dbf11 rows selected.

3.2 備份相關數據文件副本：

編寫腳本：

 vi copy_datafile.sh echo "=======Begin at : `date`=======" >>/tmp/copy_datafile_`date +%Y%m%d`.logrman target / <<EOF >>/tmp/copy_datafile_`date +%Y%m%d`.logrun {allocate channel c1 device type disk;allocate channel c2 device type disk;allocate channel c3 device type disk;backup as copy datafile 9 format '/oradata/jy/datafile/dbs_data9.dbf';backup as copy datafile 10 format '/oradata/jy/datafile/dbs_data10.dbf';backup as copy datafile 11 format '/oradata/jy/datafile/dbs_data11.dbf';release channel c1;release channel c2;release channel c3;}EOFecho "=======End at : `date`=======" >>/tmp/copy_datafile_`date +%Y%m%d`.log

后臺執行腳本：nohup sh copy_datafile.sh &

記錄的日志如下：

 =======Begin at : Sat May 5 10:51:24 CST 2018=======Recovery Manager: Release 10.2.0.5.0 - Production on Sat May 5 10:51:24 2018Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: JY (DBID=857123342, not open)RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> using target database control file instead of recovery catalogallocated channel: c1channel c1: sid=152 devtype=DISKallocated channel: c2channel c2: sid=159 devtype=DISKallocated channel: c3channel c3: sid=144 devtype=DISKStarting backup at 05-MAY-18channel c1: starting datafile copyinput datafile fno=00009 name=/datafile/dbs_data9.dbfoutput filename=/oradata/jy/datafile/dbs_data9.dbf tag=TAG20180505T105125 recid=22 stamp=975322288channel c1: datafile copy complete, elapsed time: 00:00:03Finished backup at 05-MAY-18Starting backup at 05-MAY-18channel c1: starting datafile copyinput datafile fno=00010 name=/datafile/dbs_data10.dbfoutput filename=/oradata/jy/datafile/dbs_data10.dbf tag=TAG20180505T105129 recid=23 stamp=975322292channel c1: datafile copy complete, elapsed time: 00:00:07Finished backup at 05-MAY-18Starting backup at 05-MAY-18channel c1: starting datafile copyinput datafile fno=00011 name=/datafile/dbs_data11.dbfoutput filename=/oradata/jy/datafile/dbs_data11.dbf tag=TAG20180505T105136 recid=24 stamp=975322315channel c1: datafile copy complete, elapsed time: 00:00:25Finished backup at 05-MAY-18released channel: c1released channel: c2released channel: c3RMAN>Recovery Manager complete.=======End at : Sat May 5 10:52:02 CST 2018=======

3.3 切換數據文件到copy副本：

 RMAN> list copy of database;using target database control file instead of recovery catalogList of Datafile CopiesKey   File S Completion Time Ckp SCN  Ckp Time    Name------- ---- - --------------- ---------- --------------- ----10   9  A 05-MAY-18    35303533  05-MAY-18    /oradata/jy/datafile/dbs_data9.dbf11   10  A 05-MAY-18    35303533  05-MAY-18    /oradata/jy/datafile/dbs_data10.dbf12   11  A 05-MAY-18    35303533  05-MAY-18    /oradata/jy/datafile/dbs_data11.dbfRMAN> switch datafile 9,10,11 to copy;datafile 9 switched to datafile copy "/oradata/jy/datafile/dbs_data9.dbf"datafile 10 switched to datafile copy "/oradata/jy/datafile/dbs_data10.dbf"datafile 11 switched to datafile copy "/oradata/jy/datafile/dbs_data11.dbf"

4.刪除之前的目錄并開啟應用

4.1 刪除之前的文件：

 RMAN> list copy of database;List of Datafile CopiesKey   File S Completion Time Ckp SCN  Ckp Time    Name------- ---- - --------------- ---------- --------------- ----13   9  A 05-MAY-18    35309314  05-MAY-18    /datafile/data9.dbf14   10  A 05-MAY-18    35309314  05-MAY-18    /datafile/data10.dbf15   11  A 05-MAY-18    35309314  05-MAY-18    /datafile/datafile11.dbfRMAN> delete copy of datafile 9,10,11;allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=146 devtype=DISKList of Datafile CopiesKey   File S Completion Time Ckp SCN  Ckp Time    Name------- ---- - --------------- ---------- --------------- ----13   9  A 05-MAY-18    35309314  05-MAY-18    /datafile/data9.dbf14   10  A 05-MAY-18    35309314  05-MAY-18    /datafile/data10.dbf15   11  A 05-MAY-18    35309314  05-MAY-18    /datafile/datafile11.dbfDo you really want to delete the above objects (enter YES or NO)? yesdeleted datafile copydatafile copy filename=/datafile/data9.dbf recid=13 stamp=975320371deleted datafile copydatafile copy filename=/datafile/data10.dbf recid=14 stamp=975320371deleted datafile copydatafile copy filename=/datafile/datafile11.dbf recid=15 stamp=975320371Deleted 3 objects

4.2 開啟日志應用：

 SQL> --recover_std_realSQL> alter database recover managed standby database using current logfile disconnect from session;Database altered.SQL> set lines 1000SQL> select * from v$dataguard_stats;NAME               VALUE                              UNIT              TIME_COMPUTED-------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------apply finish time        +00 00:00:00.0                          day(2) to second(1) interval  05-MAY-2018 10:20:56apply lag            +00 00:02:00                           day(2) to second(0) interval  05-MAY-2018 10:20:56estimated startup time      41                                second             05-MAY-2018 10:20:56standby has been open      N                                                05-MAY-2018 10:20:56transport lag          +00 00:00:00                           day(2) to second(0) interval  05-MAY-2018 10:20:56SQL> select recovery_mode from v$archive_dest_status;RECOVERY_MODE-----------------------MANAGED REAL TIME APPLYIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLE11 rows selected.

至此，就完成了客戶的需求，我們可以多思考一下，如果客戶環境是11g的ADG環境呢？會有哪些不同呢？

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持VeVb武林網。

注：相關教程知識閱讀請移步到oracle教程頻道。

上一篇：Oracle數據庫自帶表空間的詳細說明

下一篇：Oracle重建控制文件的實例教程