背景&＃xff1a;某客户Oracle 10g 的DG由于空间不足&＃xff0c;之前将部分数据文件迁移到其他目录&＃xff0c;如今原目录扩容成功&＃xff0c;要将之前迁移的数据文件再次迁移回来。
环境&＃xff1a;Oracle 10.2.0.5 DG 单机
首先想到的是10gDG是在mount模式下应用的&＃xff0c;在测试环境可以很容易的模拟下这个需求实现的过程&＃xff1a;
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 &＃43;00 00:00:00.0 day(2) to second(1) interval 05-MAY-2018 10:04:20
apply lag &＃43;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 &＃43;00 00:00:00 day(2) to second(0) interval 05-MAY-2018 10:04:20
可以看到DG处于正常应用状态。
2.停止DG应用
停止DG应用&＃xff1a;
SQL> alter database recover managed standby database cancel;
Database altered.
3.备份copy副本到新目录并切换
3.1 确认需要迁移的数据文件
查看当前的数据文件&＃xff0c;确认将9,10,11三个文件迁移回原来的目录&＃xff1a;
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 备份相关数据文件副本&＃xff1a;
编写脚本&＃xff1a;
vi copy_datafile.sh
echo "&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;Begin at : &＃96;date&＃96;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;" >>/tmp/copy_datafile_&＃96;date &＃43;%Y%m%d&＃96;.log
rman target / <>/tmp/copy_datafile_&＃96;date &＃43;%Y%m%d&＃96;.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 &＃39;/oradata/jy/datafile/dbs_data9.dbf&＃39;;
backup as copy datafile 10 format &＃39;/oradata/jy/datafile/dbs_data10.dbf&＃39;;
backup as copy datafile 11 format &＃39;/oradata/jy/datafile/dbs_data11.dbf&＃39;;
release channel c1;
release channel c2;
release channel c3;
}
EOF
echo "&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;End at : &＃96;date&＃96;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;" >>/tmp/copy_datafile_&＃96;date &＃43;%Y%m%d&＃96;.log
后台执行脚本&＃xff1a;nohup sh copy_datafile.sh &
记录的日志如下&＃xff1a;
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;Begin at : Sat May 5 10:51:24 CST 2018&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
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&＃61;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&＃61;152 devtype&＃61;DISK
allocated channel: c2
channel c2: sid&＃61;159 devtype&＃61;DISK
allocated channel: c3
channel c3: sid&＃61;144 devtype&＃61;DISK
Starting backup at 05-MAY-18
channel c1: starting datafile copy
input datafile fno&＃61;00009 name&＃61;/datafile/dbs_data9.dbf
output filename&＃61;/oradata/jy/datafile/dbs_data9.dbf tag&＃61;TAG20180505T105125 recid&＃61;22 stamp&＃61;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&＃61;00010 name&＃61;/datafile/dbs_data10.dbf
output filename&＃61;/oradata/jy/datafile/dbs_data10.dbf tag&＃61;TAG20180505T105129 recid&＃61;23 stamp&＃61;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&＃61;00011 name&＃61;/datafile/dbs_data11.dbf
output filename&＃61;/oradata/jy/datafile/dbs_data11.dbf tag&＃61;TAG20180505T105136 recid&＃61;24 stamp&＃61;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.
&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;End at : Sat May 5 10:52:02 CST 2018&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;
3.3 切换数据文件到copy副本&＃xff1a;
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 删除之前的文件&＃xff1a;
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&＃61;146 devtype&＃61;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&＃61;/datafile/data9.dbf recid&＃61;13 stamp&＃61;975320371
deleted datafile copy
datafile copy filename&＃61;/datafile/data10.dbf recid&＃61;14 stamp&＃61;975320371
deleted datafile copy
datafile copy filename&＃61;/datafile/datafile11.dbf recid&＃61;15 stamp&＃61;975320371
Deleted 3 objects
4.2 开启日志应用&＃xff1a;
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 &＃43;00 00:00:00.0 day(2) to second(1) interval 05-MAY-2018 10:20:56
apply lag &＃43;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 &＃43;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.
至此&＃xff0c;就完成了客户的需求&＃xff0c;我们可以多思考一下&＃xff0c;如果客户环境是11g的ADG环境呢&＃xff1f;会有哪些不同呢&＃xff1f;
以上就是本文的全部内容&＃xff0c;希望对大家的学习有所帮助&＃xff0c;也希望大家多多支持脚本之家。
京公网安备 11010802041100号