服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Oracle - Oracle 10g DG 数据文件迁移的实现

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

2020-03-26 16:12AlfredZhao Oracle

我们常常需要对数据进行迁移，这篇文章主要介绍了Oracle 10g DG 数据文件迁移的实现，小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编过来看看吧

背景：某客户Oracle 10g 的DG由于空间不足，之前将部分数据文件迁移到其他目录，如今原目录扩容成功，要将之前迁移的数据文件再次迁移回来。

 环境：Oracle 10.2.0.5 DG 单机

首先想到的是10gDG是在mount模式下应用的，在测试环境可以很容易的模拟下这个需求实现的过程：

  • 1.查询当前DG的状态
  • 2.停止DG应用
  • 3.备份copy副本到新目录并切换
  • 4.删除之前的目录并开启应用

1.查询当前DG的状态

查询当前DG的状态:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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应用：

?
1
2
3
SQL> alter database recover managed standby database cancel;
 
Database altered.

3.备份copy副本到新目录并切换

3.1 确认需要迁移的数据文件

查看当前的数据文件，确认将9,10,11三个文件迁移回原来的目录：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 备份相关数据文件副本：

编写脚本：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 &

记录的日志如下：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
=======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副本：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 删除之前的文件：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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 开启日志应用：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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环境呢？会有哪些不同呢？

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

原文链接：http://www.cnblogs.com/jyzhao/p/8994065.html

  • Oracle 10g
  • 数据文件迁移

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    1033 　 1455 　 12154 　 10231 　 12514 　 54013 　 数据库系统 　 快照 　 分区策略 　 数据库集 　 异常关闭 　 Oracle9iPL 　 SQL编程 　 日期函数 　 SQL性能优化 　 并行查询 　 紧急故障 　 完全卸载 　 Win2k 　 IMP 　 分区管理 　 密码文件 　 AutoUpgrade 　 异地备份 　 交叉表 　 加载数据库错误 　 areasQueries 　 逻辑备份 　 SH文件 　 数据文件 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图