Oracle 10g DG 数据文件迁移的实现
程序员文章站
2022-06-24 23:06:18
背景:某客户oracle 10g 的dg由于空间不足,之前将部分数据文件迁移到其他目录,如今原目录扩容成功,要将之前迁移的数据文件再次迁移回来。
环境:o...
背景:某客户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环境呢?会有哪些不同呢?
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持。