主頁(yè) > 知識(shí)庫(kù) > Oracle 10g DG 數(shù)據(jù)文件遷移的實(shí)現(xiàn)

Oracle 10g DG 數(shù)據(jù)文件遷移的實(shí)現(xiàn)

熱門標(biāo)簽:呼倫貝爾智能手機(jī)地圖標(biāo)注 海南400電話哪里辦理 安陽(yáng)外呼系統(tǒng)免費(fèi) 分布式呼叫中心 貴陽(yáng)電話外呼系統(tǒng)哪家好 400電話是不是免費(fèi)申請(qǐng) 南寧人工智能電銷機(jī)器人費(fèi)用 濟(jì)南地圖標(biāo)注公司 圖像地圖標(biāo)注

背景:某客戶Oracle 10g 的DG由于空間不足,之前將部分?jǐn)?shù)據(jù)文件遷移到其他目錄,如今原目錄擴(kuò)容成功,要將之前遷移的數(shù)據(jù)文件再次遷移回來(lái)。

 環(huán)境:Oracle 10.2.0.5 DG 單機(jī)

首先想到的是10gDG是在mount模式下應(yīng)用的,在測(cè)試環(huán)境可以很容易的模擬下這個(gè)需求實(shí)現(xiàn)的過(guò)程:

  • 1.查詢當(dāng)前DG的狀態(tài)
  • 2.停止DG應(yīng)用
  • 3.備份copy副本到新目錄并切換
  • 4.刪除之前的目錄并開(kāi)啟應(yīng)用

1.查詢當(dāng)前DG的狀態(tài)

查詢當(dāng)前DG的狀態(tài):

 Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name, database_role, open_mode from gv$database;

NAME   DATABASE_ROLE  OPEN_MODE
--------- ---------------- ----------
JY    PHYSICAL STANDBY MOUNTED

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE

11 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:20
apply lag             +00 00:00:12                           day(2) to second(0) interval  05-MAY-2018 10:04:20
estimated startup time       41                                second             05-MAY-2018 10:04:20
standby has been open       N                                                05-MAY-2018 10:04:20
transport lag           +00 00:00:00                           day(2) to second(0) interval  05-MAY-2018 10:04:20 

可以看到DG處于正常應(yīng)用狀態(tài)。

2.停止DG應(yīng)用

停止DG應(yīng)用:

 SQL> alter database recover managed standby database cancel;

Database altered. 

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

3.1 確認(rèn)需要遷移的數(shù)據(jù)文件

查看當(dāng)前的數(shù)據(jù)文件,確認(rèn)將9,10,11三個(gè)文件遷移回原來(lái)的目錄:

 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.dbf

11 rows selected. 

3.2 備份相關(guān)數(shù)據(jù)文件副本:

編寫腳本:

 vi copy_datafile.sh
 echo "=======Begin at : `date`=======" >>/tmp/copy_datafile_`date +%Y%m%d`.log
rman target / EOF >>/tmp/copy_datafile_`date +%Y%m%d`.log
run {
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;
}
EOF
echo "=======End at : `date`=======" >>/tmp/copy_datafile_`date +%Y%m%d`.log 

后臺(tái)執(zhí)行腳本: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 2018

Copyright (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 catalog
allocated channel: c1
channel c1: sid=152 devtype=DISK

allocated channel: c2
channel c2: sid=159 devtype=DISK

allocated channel: c3
channel c3: sid=144 devtype=DISK

Starting backup at 05-MAY-18
channel c1: starting datafile copy
input datafile fno=00009 name=/datafile/dbs_data9.dbf
output filename=/oradata/jy/datafile/dbs_data9.dbf tag=TAG20180505T105125 recid=22 stamp=975322288
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 05-MAY-18

Starting backup at 05-MAY-18
channel c1: starting datafile copy
input datafile fno=00010 name=/datafile/dbs_data10.dbf
output filename=/oradata/jy/datafile/dbs_data10.dbf tag=TAG20180505T105129 recid=23 stamp=975322292
channel c1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 05-MAY-18

Starting backup at 05-MAY-18
channel c1: starting datafile copy
input datafile fno=00011 name=/datafile/dbs_data11.dbf
output filename=/oradata/jy/datafile/dbs_data11.dbf tag=TAG20180505T105136 recid=24 stamp=975322315
channel c1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 05-MAY-18

released channel: c1

released channel: c2

released channel: c3

RMAN>

Recovery Manager complete.
=======End at : Sat May 5 10:52:02 CST 2018======= 

3.3 切換數(shù)據(jù)文件到copy副本:

 RMAN> list copy of database;

using target database control file instead of recovery catalog

List of Datafile Copies
Key   File S Completion Time Ckp SCN  Ckp Time    Name
------- ---- - --------------- ---------- --------------- ----
10   9  A 05-MAY-18    35303533  05-MAY-18    /oradata/jy/datafile/dbs_data9.dbf
11   10  A 05-MAY-18    35303533  05-MAY-18    /oradata/jy/datafile/dbs_data10.dbf
12   11  A 05-MAY-18    35303533  05-MAY-18    /oradata/jy/datafile/dbs_data11.dbf

RMAN> 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.刪除之前的目錄并開(kāi)啟應(yīng)用

4.1 刪除之前的文件:

 RMAN> list copy of database;


List of Datafile Copies
Key   File S Completion Time Ckp SCN  Ckp Time    Name
------- ---- - --------------- ---------- --------------- ----
13   9  A 05-MAY-18    35309314  05-MAY-18    /datafile/data9.dbf
14   10  A 05-MAY-18    35309314  05-MAY-18    /datafile/data10.dbf
15   11  A 05-MAY-18    35309314  05-MAY-18    /datafile/datafile11.dbf

RMAN> delete copy of datafile 9,10,11;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK

List of Datafile Copies
Key   File S Completion Time Ckp SCN  Ckp Time    Name
------- ---- - --------------- ---------- --------------- ----
13   9  A 05-MAY-18    35309314  05-MAY-18    /datafile/data9.dbf
14   10  A 05-MAY-18    35309314  05-MAY-18    /datafile/data10.dbf
15   11  A 05-MAY-18    35309314  05-MAY-18    /datafile/datafile11.dbf

Do you really want to delete the above objects (enter YES or NO)? yes
deleted datafile copy
datafile copy filename=/datafile/data9.dbf recid=13 stamp=975320371
deleted datafile copy
datafile copy filename=/datafile/data10.dbf recid=14 stamp=975320371
deleted datafile copy
datafile copy filename=/datafile/datafile11.dbf recid=15 stamp=975320371
Deleted 3 objects 

4.2 開(kāi)啟日志應(yīng)用:

 SQL> --recover_std_real
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> set lines 1000
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:20:56
apply lag            +00 00:02:00                           day(2) to second(0) interval  05-MAY-2018 10:20:56
estimated startup time      41                                second             05-MAY-2018 10:20:56
standby has been open      N                                                05-MAY-2018 10:20:56
transport lag          +00 00:00:00                           day(2) to second(0) interval  05-MAY-2018 10:20:56

SQL> select recovery_mode from v$archive_dest_status;

RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE
IDLE

11 rows selected. 

至此,就完成了客戶的需求,我們可以多思考一下,如果客戶環(huán)境是11g的ADG環(huán)境呢?會(huì)有哪些不同呢?

以上就是本文的全部?jī)?nèi)容,希望對(duì)大家的學(xué)習(xí)有所幫助,也希望大家多多支持腳本之家。

您可能感興趣的文章:
  • DBCA命令行搭建Oracle ADG的流程
  • Oracle 給rac創(chuàng)建單實(shí)例dg并做主從切換功能
  • 詳解Oracle dg 三種模式切換
  • Oracle數(shù)據(jù)庫(kù) DGbroker三種保護(hù)模式的切換
  • win平臺(tái)oracle rman備份和刪除dg備庫(kù)歸檔日志腳本
  • Oracle區(qū)別ADG與DG案例詳解

標(biāo)簽:焦作 郴州 遼源 南充 許昌 滁州 涼山 合肥

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《Oracle 10g DG 數(shù)據(jù)文件遷移的實(shí)現(xiàn)》,本文關(guān)鍵詞  Oracle,10g,數(shù)據(jù),文件,遷移,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《Oracle 10g DG 數(shù)據(jù)文件遷移的實(shí)現(xiàn)》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于Oracle 10g DG 數(shù)據(jù)文件遷移的實(shí)現(xiàn)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章