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

2023-09-13 06:48:06 367

背景：某客户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环境呢？会有哪些不同呢？

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

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

热门推荐

随机推荐