Oracle 10g DG 数据文件迁移的实现
背景：某客户Oracle10g的DG由于空间不足，之前将部分数据文件迁移到其他目录，如今原目录扩容成功，要将之前迁移的数据文件再次迁移回来。
环境：Oracle10.2.0.5DG单机
首先想到的是10gDG是在mount模式下应用的，在测试环境可以很容易的模拟下这个需求实现的过程：
- 1.查询当前DG的状态
- 2.停止DG应用
- 3.备份copy副本到新目录并切换
- 4.删除之前的目录并开启应用
1.查询当前DG的状态
查询当前DG的状态:
Connectedto: OracleDatabase10gEnterpriseEditionRelease10.2.0.5.0-64bitProduction WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions SQL>selectname,database_role,open_modefromgv$database; NAMEDATABASE_ROLEOPEN_MODE ----------------------------------- JYPHYSICALSTANDBYMOUNTED SQL>selectrecovery_modefromv$archive_dest_status; RECOVERY_MODE ----------------------- MANAGEDREALTIMEAPPLY IDLE IDLE IDLE IDLE IDLE IDLE IDLE IDLE IDLE IDLE 11rowsselected. SQL>select*fromv$dataguard_stats; NAMEVALUEUNITTIME_COMPUTED -------------------------------------------------------------------------------------------------------------------------------------------------------------- applyfinishtime+0000:00:00.0day(2)tosecond(1)interval05-MAY-201810:04:20 applylag+0000:00:12day(2)tosecond(0)interval05-MAY-201810:04:20 estimatedstartuptime41second05-MAY-201810:04:20 standbyhasbeenopenN05-MAY-201810:04:20 transportlag+0000:00:00day(2)tosecond(0)interval05-MAY-201810:04:20
可以看到DG处于正常应用状态。
2.停止DG应用
停止DG应用：
SQL>alterdatabaserecovermanagedstandbydatabasecancel; Databasealtered.
3.备份copy副本到新目录并切换
3.1确认需要迁移的数据文件
查看当前的数据文件，确认将9,10,11三个文件迁移回原来的目录：
SQL>selectfile#,namefromv$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 11rowsselected.
3.2备份相关数据文件副本：
编写脚本：
vicopy_datafile.sh echo"=======Beginat:`date`=======">>/tmp/copy_datafile_`date+%Y%m%d`.log rmantarget/<>/tmp/copy_datafile_`date+%Y%m%d`.log run{ allocatechannelc1devicetypedisk; allocatechannelc2devicetypedisk; allocatechannelc3devicetypedisk; backupascopydatafile9format'/oradata/jy/datafile/dbs_data9.dbf'; backupascopydatafile10format'/oradata/jy/datafile/dbs_data10.dbf'; backupascopydatafile11format'/oradata/jy/datafile/dbs_data11.dbf'; releasechannelc1; releasechannelc2; releasechannelc3; } EOF echo"=======Endat:`date`=======">>/tmp/copy_datafile_`date+%Y%m%d`.log
后台执行脚本：nohupshcopy_datafile.sh&
记录的日志如下：
=======Beginat:SatMay510:51:24CST2018======= RecoveryManager:Release10.2.0.5.0-ProductiononSatMay510:51:242018 Copyright(c)1982,2007,Oracle.Allrightsreserved. connectedtotargetdatabase:JY(DBID=857123342,notopen) RMAN>2>3>4>5>6>7>8>9>10>11>12>13> usingtargetdatabasecontrolfileinsteadofrecoverycatalog allocatedchannel:c1 channelc1:sid=152devtype=DISK allocatedchannel:c2 channelc2:sid=159devtype=DISK allocatedchannel:c3 channelc3:sid=144devtype=DISK Startingbackupat05-MAY-18 channelc1:startingdatafilecopy inputdatafilefno=00009name=/datafile/dbs_data9.dbf outputfilename=/oradata/jy/datafile/dbs_data9.dbftag=TAG20180505T105125recid=22stamp=975322288 channelc1:datafilecopycomplete,elapsedtime:00:00:03 Finishedbackupat05-MAY-18 Startingbackupat05-MAY-18 channelc1:startingdatafilecopy inputdatafilefno=00010name=/datafile/dbs_data10.dbf outputfilename=/oradata/jy/datafile/dbs_data10.dbftag=TAG20180505T105129recid=23stamp=975322292 channelc1:datafilecopycomplete,elapsedtime:00:00:07 Finishedbackupat05-MAY-18 Startingbackupat05-MAY-18 channelc1:startingdatafilecopy inputdatafilefno=00011name=/datafile/dbs_data11.dbf outputfilename=/oradata/jy/datafile/dbs_data11.dbftag=TAG20180505T105136recid=24stamp=975322315 channelc1:datafilecopycomplete,elapsedtime:00:00:25 Finishedbackupat05-MAY-18 releasedchannel:c1 releasedchannel:c2 releasedchannel:c3 RMAN> RecoveryManagercomplete. =======Endat:SatMay510:52:02CST2018=======
3.3切换数据文件到copy副本：
RMAN>listcopyofdatabase; usingtargetdatabasecontrolfileinsteadofrecoverycatalog ListofDatafileCopies KeyFileSCompletionTimeCkpSCNCkpTimeName -------------------------------------------------------- 109A05-MAY-183530353305-MAY-18/oradata/jy/datafile/dbs_data9.dbf 1110A05-MAY-183530353305-MAY-18/oradata/jy/datafile/dbs_data10.dbf 1211A05-MAY-183530353305-MAY-18/oradata/jy/datafile/dbs_data11.dbf RMAN>switchdatafile9,10,11tocopy; datafile9switchedtodatafilecopy"/oradata/jy/datafile/dbs_data9.dbf" datafile10switchedtodatafilecopy"/oradata/jy/datafile/dbs_data10.dbf" datafile11switchedtodatafilecopy"/oradata/jy/datafile/dbs_data11.dbf"
4.删除之前的目录并开启应用
4.1删除之前的文件：
RMAN>listcopyofdatabase; ListofDatafileCopies KeyFileSCompletionTimeCkpSCNCkpTimeName -------------------------------------------------------- 139A05-MAY-183530931405-MAY-18/datafile/data9.dbf 1410A05-MAY-183530931405-MAY-18/datafile/data10.dbf 1511A05-MAY-183530931405-MAY-18/datafile/datafile11.dbf RMAN>deletecopyofdatafile9,10,11; allocatedchannel:ORA_DISK_1 channelORA_DISK_1:sid=146devtype=DISK ListofDatafileCopies KeyFileSCompletionTimeCkpSCNCkpTimeName -------------------------------------------------------- 139A05-MAY-183530931405-MAY-18/datafile/data9.dbf 1410A05-MAY-183530931405-MAY-18/datafile/data10.dbf 1511A05-MAY-183530931405-MAY-18/datafile/datafile11.dbf Doyoureallywanttodeletetheaboveobjects(enterYESorNO)?yes deleteddatafilecopy datafilecopyfilename=/datafile/data9.dbfrecid=13stamp=975320371 deleteddatafilecopy datafilecopyfilename=/datafile/data10.dbfrecid=14stamp=975320371 deleteddatafilecopy datafilecopyfilename=/datafile/datafile11.dbfrecid=15stamp=975320371 Deleted3objects
4.2开启日志应用：
SQL>--recover_std_real SQL>alterdatabaserecovermanagedstandbydatabaseusingcurrentlogfiledisconnectfromsession; Databasealtered. SQL>setlines1000 SQL>select*fromv$dataguard_stats; NAMEVALUEUNITTIME_COMPUTED ------------------------------------------------------------------------------------------------------------------------------------------------------------ applyfinishtime+0000:00:00.0day(2)tosecond(1)interval05-MAY-201810:20:56 applylag+0000:02:00day(2)tosecond(0)interval05-MAY-201810:20:56 estimatedstartuptime41second05-MAY-201810:20:56 standbyhasbeenopenN05-MAY-201810:20:56 transportlag+0000:00:00day(2)tosecond(0)interval05-MAY-201810:20:56 SQL>selectrecovery_modefromv$archive_dest_status; RECOVERY_MODE ----------------------- MANAGEDREALTIMEAPPLY IDLE IDLE IDLE IDLE IDLE IDLE IDLE IDLE IDLE IDLE 11rowsselected.
至此，就完成了客户的需求，我们可以多思考一下，如果客户环境是11g的ADG环境呢？会有哪些不同呢？
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。