好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

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 0.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 0                           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 0.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 0                           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 DG 数据文件迁移的实现的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did227294
  阅读：27次

上一篇： Oracle重建控制文件的实例教程

下一篇：oracle sql语言模糊查询--通配符like的使用教程详解

相关资讯