阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle 10g DG 数据文件迁移案例分析

175次阅读
没有评论

共计 7204 个字符,预计需要花费 19 分钟才能阅读完成。

背景:某客户 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 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 应用:
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.dbf

11 rows selected.

3.2 备份相关数据文件副本:
 编写脚本:
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 &

记录的日志如下:
=======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 副本:
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 删除之前的文件:
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 开启日志应用:
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 环境呢?会有哪些不同呢?

更多 Oracle 相关信息见 Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计7204字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中