• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Oracle 10g DG 数据文件迁移的实现

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

     环境:Oracle 10.2.0.5 DG 单机

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

    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环境呢?会有哪些不同呢?

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

    您可能感兴趣的文章:
    • DBCA命令行搭建Oracle ADG的流程
    • Oracle 给rac创建单实例dg并做主从切换功能
    • 详解Oracle dg 三种模式切换
    • Oracle数据库 DGbroker三种保护模式的切换
    • win平台oracle rman备份和删除dg备库归档日志脚本
    • Oracle区别ADG与DG案例详解
    上一篇:Oracle 配置远程访问教程
    下一篇:Oracle重建控制文件的实例教程
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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