Oracle 10g DG 数据文件迁移的实现

背景：某客户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 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应用：

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

11 rows selected. 

3.2 备份相关数据文件副本：

编写脚本：

 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 &

记录的日志如下：

 =======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副本：

 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 删除之前的文件：

 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 开启日志应用：

 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环境呢？会有哪些不同呢？

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。