美圖欣賞 | 設為首頁 | 加入收藏 | 網站地圖

當前位置：新錦江平臺：www.xjj7.com-電腦中國 > 數據庫 > oracel >

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

2018-07-10 11:35|來源:未知 |作者:dnzg |點擊: 次

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

 環境：Oracle 10.2.0.5 DG 單機

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

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

1.查詢當前DG的狀態

查詢當前DG的狀態:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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處于正常應用狀態。

2.停止DG應用

停止DG應用：

?
1
2
3
SQL> alter database recover managed standby database cancel;
 
Database altered.

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 備份相關數據文件副本：

編寫腳本：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

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

記錄的日志如下：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
=======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 切換數據文件到copy副本：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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.刪除之前的目錄并開啟應用

4.1 刪除之前的文件：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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 開啟日志應用：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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.

至此，就完成了客戶的需求，我們可以多思考一下，如果客戶環境是11g的ADG環境呢？會有哪些不同呢？
（責任編輯：dnzg）
相關閱讀
編輯推薦

CorelDRAW12技巧:導出透明背景的gif圖片

CorelDRAW12技巧:導出透明背景的gif圖片 第一步：CTRL+E 打開導出窗口�；蛘呶募�-導出，選擇保存位置以及...[詳細]
熱門文章
圖文資訊

關于網站 | 版權聲明 | Tag標簽 | RSS訂閱 | 友情鏈接 | 聯系我們 | 服務收費

Copyright © 2009-2018 電腦中國 www.03173333.buzz Power by DNZG.CN

電腦中國 版權所有 www.justoa69.com業務QQ：1239601100

森林龙江麻将精华版 (*^▽^*)MG海豚礁客户端下载 福建快3今天推荐号 (★^O^★)MG夢幻邂逅游戏 30选5每个礼拜几开奖 河内五分彩官网开奖走势 (＾ω＾)MG好多寿司试玩网站 靠谱十大网赚平台排名 (★^O^★)MG黄金武士新手攻略 黑龙江36选711月7日 (*^▽^*)MG丧尸来袭免费下载 有没什么好的网赚项目 走势图东方6+1 海南41开奖时间 (*^▽^*)MG马戏团app (*^▽^*)MG花花公子怎么玩容易爆分 18选7今天开奖结果查询