背景：某客户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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select name, database_role, open_mode from gv$database;
NAME DATABASE_ROLE OPEN_MODE--------- ---------------- ----------JY PHYSICAL STANDBY MOUNTEDSQL> select recovery_mode from v$archive_dest_status;
RECOVERY_MODE-----------------------MANAGED REAL TIME APPLYIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLE11 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:20apply lag +00 00:00:12 day(2) to second(0) interval 05-MAY-2018 10:04:20estimated startup time 41 second 05-MAY-2018 10:04:20standby has been open N 05-MAY-2018 10:04:20transport 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.dbf11 rows selected.
3.2 备份相关数据文件副本：
编写脚本：
vi copy_datafile.sh echo "=======Begin at : `date`=======" >>/tmp/copy_datafile_`date +%Y%m%d`.logrman target / <<EOF >>/tmp/copy_datafile_`date +%Y%m%d`.logrun {
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;
}
EOFecho "=======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 2018Copyright (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 catalogallocated channel: c1channel c1: sid=152 devtype=DISKallocated channel: c2channel c2: sid=159 devtype=DISKallocated channel: c3channel c3: sid=144 devtype=DISKStarting backup at 05-MAY-18channel c1: starting datafile copyinput datafile fno=00009 name=/datafile/dbs_data9.dbfoutput filename=/oradata/jy/datafile/dbs_data9.dbf tag=TAG20180505T105125 recid=22 stamp=975322288channel c1: datafile copy complete, elapsed time: 00:00:03Finished backup at 05-MAY-18Starting backup at 05-MAY-18channel c1: starting datafile copyinput datafile fno=00010 name=/datafile/dbs_data10.dbfoutput filename=/oradata/jy/datafile/dbs_data10.dbf tag=TAG20180505T105129 recid=23 stamp=975322292channel c1: datafile copy complete, elapsed time: 00:00:07Finished backup at 05-MAY-18Starting backup at 05-MAY-18channel c1: starting datafile copyinput datafile fno=00011 name=/datafile/dbs_data11.dbfoutput filename=/oradata/jy/datafile/dbs_data11.dbf tag=TAG20180505T105136 recid=24 stamp=975322315channel c1: datafile copy complete, elapsed time: 00:00:25Finished backup at 05-MAY-18released channel: c1released channel: c2released channel: c3RMAN>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 catalogList of Datafile CopiesKey File S Completion Time Ckp SCN Ckp Time Name------- ---- - --------------- ---------- --------------- ----10 9 A 05-MAY-18 35303533 05-MAY-18 /oradata/jy/datafile/dbs_data9.dbf11 10 A 05-MAY-18 35303533 05-MAY-18 /oradata/jy/datafile/dbs_data10.dbf12 11 A 05-MAY-18 35303533 05-MAY-18 /oradata/jy/datafile/dbs_data11.dbfRMAN> 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 CopiesKey File S Completion Time Ckp SCN Ckp Time Name------- ---- - --------------- ---------- --------------- ----13 9 A 05-MAY-18 35309314 05-MAY-18 /datafile/data9.dbf14 10 A 05-MAY-18 35309314 05-MAY-18 /datafile/data10.dbf15 11 A 05-MAY-18 35309314 05-MAY-18 /datafile/datafile11.dbfRMAN> delete copy of datafile 9,10,11;
allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=146 devtype=DISKList of Datafile CopiesKey File S Completion Time Ckp SCN Ckp Time Name------- ---- - --------------- ---------- --------------- ----13 9 A 05-MAY-18 35309314 05-MAY-18 /datafile/data9.dbf14 10 A 05-MAY-18 35309314 05-MAY-18 /datafile/data10.dbf15 11 A 05-MAY-18 35309314 05-MAY-18 /datafile/datafile11.dbfDo you really want to delete the above objects (enter YES or NO)? yesdeleted datafile copydatafile copy filename=/datafile/data9.dbf recid=13 stamp=975320371deleted datafile copydatafile copy filename=/datafile/data10.dbf recid=14 stamp=975320371deleted datafile copydatafile copy filename=/datafile/datafile11.dbf recid=15 stamp=975320371Deleted 3 objects
4.2 开启日志应用：
SQL> --recover_std_realSQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.SQL> set lines 1000SQL> 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:56apply lag +00 00:02:00 day(2) to second(0) interval 05-MAY-2018 10:20:56estimated startup time 41 second 05-MAY-2018 10:20:56standby has been open N 05-MAY-2018 10:20:56transport lag +00 00:00:00 day(2) to second(0) interval 05-MAY-2018 10:20:56SQL> select recovery_mode from v$archive_dest_status;
RECOVERY_MODE-----------------------MANAGED REAL TIME APPLYIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLEIDLE11 rows selected.
至此，就完成了客户的需求，我们可以多思考一下，如果客户环境是11g的ADG环境呢？会有哪些不同呢？
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。
您可能感兴趣的文章:
- oracle数据迁移到db2数据库的实现方法(分享)
- oracle数据库迁移到MySQL的方法总结
- mysql数据迁移到Oracle的正确方法
- 直接拷贝数据文件实现Oracle数据迁移
- Oracle数据库迁移方案
- Oracle数据库升级或数据迁移方法研究
- 深入ORACLE迁移到MYSQL的总结分析
- oracle 数据库数据迁移解决方案
