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

背景:某客户Oracle 10g 的DG由于空间不足,之前将部分数据文件迁移到其他目录,如今原目录扩容成功,要将之前迁移的数据文件再次迁移回来。

环境:Oracle 10.2.0.5 DG 单机

首先想到的是10gDG是在mount模式下应用的,在测试环境可以很容易的模拟下这个需求实现的过程:

1.查询当前DG的状态

2.停止DG应用

3.备份copy副本到新目录并切换

4.删除之前的目录并开启应用

1.查询当前DG的状态

查询当前DG的状态:

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应用:

alter database recover managed standby database cancel;

Database altered.

3.1 确认需要迁移的数据文件

查看当前的数据文件,确认将9,10,11三个文件迁移回原来的目录:

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 备份相关数据文件副本:

编写脚本:

>/tmp/copy_datafile_`date +%Y%m%d`.log

rman target / <>/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/copydatafiledate +%Y%m%d.log

后台执行脚本:nohup sh copy_datafile.sh &

记录的日志如下:

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副本:

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

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

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

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

总结

如果觉得编程之家网站内容还不错,欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值