ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 主库不停做物理dg

主库不停做物理dg

原创 Oracle 作者：BogengWang 时间：2015-04-12 18:33:36 0 删除 编辑
一、环境（主备库环境相同）
   操作系统：Red Hat Enterprise Linux Server release 5.5
    oracle：11.2.0.1.0

主库/etc/hosts如下

点击(此处)折叠或打开

  1. [root@wbg1 ~]# cat /etc/hosts
  2. # Do not remove the following line, or various programs
  3. # that require network functionality will fail.
  4. 127.0.0.1 wbg1 localhost.localdomain localhost
  5. ::1 localhost6.localdomain6 localhost6
  7. 1.1.1.51 wbg1
  8. 1.1.1.52 wbg2
备库/etc/hosts如下  

点击(此处)折叠或打开

  1. [oracle@wbg2 dbs]$ cat /etc/hosts
  2. # Do not remove the following line, or various programs
  3. # that require network functionality will fail.
  4. 127.0.0.1 wbg2 localhost.localdomain localhost
  5. ::1 localhost6.localdomain6 localhost6

  7. 1.1.1.51 wbg1
  8. 1.1.1.52 wbg2
                      
二、搭建过程
1.网络配置，tnsname.ora（主备库相同）

点击(此处)折叠或打开

  1. primary=
  2. (DESCRIPTION=
  3.  (ADDRESS=(PROTOCOL=tcp)(HOST=wbg1)(PORT=1521))
  4.  (CONNECT_DATA=(SERVICE_NAME=wbg)))
  5. standby=
  6. (DESCRIPTION=
  7.  (ADDRESS=(PROTOCOL=tcp)(HOST=wbg2)(PORT=1521))
  8.  (CONNECT_DATA=(SERVICE_NAME=standby)))
2.从主库拷贝口令文件到备库

点击(此处)折叠或打开

  1. scp orapwprimary wbg2:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstandby                                                           
3.主库打开强制记录日志

点击(此处)折叠或打开

  1. SQL> ALTER DATABASE FORCE LOGGING;

  3. Database altered.
4.在不停库的前提下DB_UNIQUE_NAME是不能改的，那么就让主库的别名和DB_NAME相同好了                                                                         

点击(此处)折叠或打开

  1. SQL> alter system set DB_UNIQUE_NAME=chicago;
  2. alter system set DB_UNIQUE_NAME=chicago
  3.                  *
  4. ERROR at line 1:
  5. ORA-02095: specified initialization parameter cannot be modified
5.对主库的parameter进行配置

点击(此处)折叠或打开

  1. SQL> alter system set log_archive_config='DG_CONFIG=(wbg,standby)';
  3. System altered.
  5. SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/wbg/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=wbg';
  7. System altered.
  9. SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
  11. System altered.
  15. SQL> alter system set FAL_SERVER=standby;
  17. System altered.
  19. SQL> alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/wbg/' scope=spfile;
  21. System altered.
  23. SQL> alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/wbg/' scope=spfile;
  25. System altered
  27. SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
  29. System altered.
6.为主库添加standby redo

点击(此处)折叠或打开

  1. SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/wbg/std01.log' size 50m;

  3. Database altered.

  5. SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/wbg/std02.log' size 50m;

  7. Database altered.

  9. SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/wbg/std03.log' size 50m;

  11. Database altered.

  13. SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/wbg/std04.log' size 50m;

  15. Database altered.
7.通过主库生产备库的控制文件，并拷贝到备库

点击(此处)折叠或打开

  1. SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';

  3. Database altered.
8.在主库生产pfile，并拷贝到备库
9.在备库修改从主库拷贝过来的pfile，修改为

点击(此处)折叠或打开

  1. *.compatible='11.2.0.1.0'
  2. *.control_files='/u01/app/oracle/oradata/standby/control01.ctl'
  3. *.db_block_size=8192
  4. *.db_file_name_convert='/u01/app/oracle/oradata/wbg/','/u01/app/oracle/oradata/standby/'
  5. *.db_name='WBG'
  6. DB_UNIQUE_NAME='standby'
  7. *.diagnostic_dest='/u01/app/oracle'
  8. *.fal_server='primary'
  9. *.log_archive_config='DG_CONFIG=(wbg,standby)'
  10. *.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/standby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
  11. *.log_archive_dest_2='SERVICE=primary ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
  12. *.log_file_name_convert='/u01/app/oracle/oradata/wbg/','/u01/app/oracle/oradata/standby/'
  13. *.memory_target=662700032
  14. *.open_cursors=300
  15. *.processes=150
  16. *.remote_login_passwordfile='EXCLUSIVE'
  17. *.standby_file_management='AUTO'
  18. *.undo_tablespace='UNDOTBS1'
10.在主库通过rman进行备库，并拷贝到备库

点击(此处)折叠或打开

  1. [oracle@wbg1 ~]$ rman target /

  3. Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 12 15:30:14 2015

  5. Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

  7. connected to target database: WBG (DBID=1668536352)

  9. RMAN> backup database format '/u01/app/oracle/rman/primary/%U' plus archivelog format '/u01/app/oracle/rman/primary/%U';


  12. Starting backup at 12-APR-15
  13. current log archived
  14. using target database control file instead of recovery catalog
  15. allocated channel: ORA_DISK_1
  16. channel ORA_DISK_1: SID=37 device type=DISK
  17. channel ORA_DISK_1: starting archived log backup set
  18. channel ORA_DISK_1: specifying archived log(s) in backup set
  19. input archived log thread=1 sequence=3 RECID=1 STAMP=876840801
  20. input archived log thread=1 sequence=4 RECID=2 STAMP=876841838
  21. input archived log thread=1 sequence=5 RECID=3 STAMP=876842896
  22. input archived log thread=1 sequence=6 RECID=4 STAMP=876842961
  23. input archived log thread=1 sequence=7 RECID=5 STAMP=876843016
  24. channel ORA_DISK_1: starting piece 1 at 12-APR-15
  25. channel ORA_DISK_1: finished piece 1 at 12-APR-15
  26. piece handle=/u01/app/oracle/rman/primary/01q47409_1_1 tag=TAG20150412T153017 comment=NONE
  27. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
  28. Finished backup at 12-APR-15

  30. Starting backup at 12-APR-15
  31. using channel ORA_DISK_1
  32. channel ORA_DISK_1: starting full datafile backup set
  33. channel ORA_DISK_1: specifying datafile(s) in backup set
  34. input datafile file number=00001 name=/u01/app/oracle/oradata/wbg/system01.dbf
  35. input datafile file number=00002 name=/u01/app/oracle/oradata/wbg/sysaux01.dbf
  36. input datafile file number=00005 name=/u01/app/oracle/oradata/wbg/example01.dbf
  37. input datafile file number=00003 name=/u01/app/oracle/oradata/wbg/undotbs01.dbf
  38. input datafile file number=00004 name=/u01/app/oracle/oradata/wbg/users01.dbf
  39. channel ORA_DISK_1: starting piece 1 at 12-APR-15
  40. channel ORA_DISK_1: finished piece 1 at 12-APR-15
  41. piece handle=/u01/app/oracle/rman/primary/02q4740g_1_1 tag=TAG20150412T153024 comment=NONE
  42. channel ORA_DISK_1: backup set complete, elapsed time: 00:01:45
  43. channel ORA_DISK_1: starting full datafile backup set
  44. channel ORA_DISK_1: specifying datafile(s) in backup set
  45. including current control file in backup set
  46. including current SPFILE in backup set
  47. channel ORA_DISK_1: starting piece 1 at 12-APR-15
  48. channel ORA_DISK_1: finished piece 1 at 12-APR-15
  49. piece handle=/u01/app/oracle/rman/primary/03q4743p_1_1 tag=TAG20150412T153024 comment=NONE
  50. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  51. Finished backup at 12-APR-15

  53. Starting backup at 12-APR-15
  54. current log archived
  55. using channel ORA_DISK_1
  56. channel ORA_DISK_1: starting archived log backup set
  57. channel ORA_DISK_1: specifying archived log(s) in backup set
  58. input archived log thread=1 sequence=8 RECID=6 STAMP=876843134
  59. channel ORA_DISK_1: starting piece 1 at 12-APR-15
  60. channel ORA_DISK_1: finished piece 1 at 12-APR-15
  61. piece handle=/u01/app/oracle/rman/primary/04q4743u_1_1 tag=TAG20150412T153214 comment=NONE
  62. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
  63. Finished backup at 12-APR-15

  65. RMAN> quit


  68. Recovery Manager complete.
  69. [oracle@wbg1 ~]$
11.启动备库的instance，并可以看到，文件已经可以convert过来

点击(此处)折叠或打开

  1. [oracle@wbg2 dbs]$ !sql
  2. sqlplus / as sysdba

  4. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 12 16:07:39 2015

  6. Copyright (c) 1982, 2009, Oracle. All rights reserved.

  8. Connected to an idle instance.

  10. SQL> startup nomount
  11. ORACLE instance started.

  13. Total System Global Area 661209088 bytes
  14. Fixed Size 1338560 bytes
  15. Variable Size 394265408 bytes
  16. Database Buffers 260046848 bytes
  17. Redo Buffers 5558272 bytes
  18. SQL> !echo $ORACLE_SID
  19. standby

  21. SQL> alter database mount;

  23. Database altered.

  25. SQL> select name from v$datafile;

  27. NAME
  28. --------------------------------------------------------------------------------
  29. /u01/app/oracle/oradata/standby/system01.dbf
  30. /u01/app/oracle/oradata/standby/sysaux01.dbf
  31. /u01/app/oracle/oradata/standby/undotbs01.dbf
  32. /u01/app/oracle/oradata/standby/users01.dbf
  33. /u01/app/oracle/oradata/standby/example01.dbf
12.在备库用rman进行恢复

点击(此处)折叠或打开

  1. [oracle@wbg2 standby]$ rman target /

  3. Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 12 16:20:54 2015

  5. Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

  7. connected to target database: WBG (DBID=1668536352, not open)

  9. RMAN> catalog start with '/u01/app/oracle/rman/standby/';

  11. using target database control file instead of recovery catalog
  12. searching for all files that match the pattern /u01/app/oracle/rman/standby/

  14. List of Files Unknown to the Database
  15. =====================================
  16. File Name: /u01/app/oracle/rman/standby/01q47409_1_1
  17. File Name: /u01/app/oracle/rman/standby/03q4743p_1_1
  18. File Name: /u01/app/oracle/rman/standby/02q4740g_1_1
  19. File Name: /u01/app/oracle/rman/standby/04q4743u_1_1

  21. Do you really want to catalog the above files (enter YES or NO)? yes
  22. cataloging files...
  23. cataloging done

  25. List of Cataloged Files
  26. =======================
  27. File Name: /u01/app/oracle/rman/standby/01q47409_1_1
  28. File Name: /u01/app/oracle/rman/standby/03q4743p_1_1
  29. File Name: /u01/app/oracle/rman/standby/02q4740g_1_1
  30. File Name: /u01/app/oracle/rman/standby/04q4743u_1_1

  32. RMAN> restore database;

  34. Starting restore at 12-APR-15
  35. allocated channel: ORA_DISK_1
  36. channel ORA_DISK_1: SID=20 device type=DISK

  38. channel ORA_DISK_1: starting datafile backup set restore
  39. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  40. channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/standby/system01.dbf
  41. channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/standby/sysaux01.dbf
  42. channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/standby/undotbs01.dbf
  43. channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/standby/users01.dbf
  44. channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/standby/example01.dbf
  45. channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rman/standby/02q4740g_1_1
  46. channel ORA_DISK_1: piece handle=/u01/app/oracle/rman/standby/02q4740g_1_1 tag=TAG20150412T153024
  47. channel ORA_DISK_1: restored backup piece 1
  48. channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
  49. Finished restore at 12-APR-15

  51. RMAN> recover database;

  53. Starting recover at 12-APR-15
  54. using channel ORA_DISK_1

  56. starting media recovery

  58. channel ORA_DISK_1: starting archived log restore to default destination
  59. channel ORA_DISK_1: restoring archived log
  60. archived log thread=1 sequence=5
  61. channel ORA_DISK_1: restoring archived log
  62. archived log thread=1 sequence=6
  63. channel ORA_DISK_1: restoring archived log
  64. archived log thread=1 sequence=7
  65. channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rman/standby/01q47409_1_1
  66. channel ORA_DISK_1: piece handle=/u01/app/oracle/rman/standby/01q47409_1_1 tag=TAG20150412T153017
  67. channel ORA_DISK_1: restored backup piece 1
  68. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
  69. archived log file name=/u01/app/oracle/arch/standby/1_5_876836460.dbf thread=1 sequence=0
  70. archived log file name=/u01/app/oracle/arch/standby/1_6_876836460.dbf thread=1 sequence=6
  71. archived log file name=/u01/app/oracle/arch/standby/1_7_876836460.dbf thread=1 sequence=7
  72. channel ORA_DISK_1: starting archived log restore to default destination
  73. channel ORA_DISK_1: restoring archived log
  74. archived log thread=1 sequence=8
  75. channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rman/standby/04q4743u_1_1
  76. channel ORA_DISK_1: piece handle=/u01/app/oracle/rman/standby/04q4743u_1_1 tag=TAG20150412T153214
  77. channel ORA_DISK_1: restored backup piece 1
  78. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
  79. archived log file name=/u01/app/oracle/arch/standby/1_8_876836460.dbf thread=1 sequence=8
  80. unable to find archived log
  81. archived log thread=1 sequence=9
  82. RMAN-00571: ===========================================================
  83. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  84. RMAN-00571: ===========================================================
  85. RMAN-03002: failure of recover command at 04/12/2015 16:34:13
  86. RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9 and starting SCN of 892714

  88. RMAN>
13.在备库的sqlplus中clear日志文件

点击(此处)折叠或打开

  1. SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
  3. Database altered.
14.打开备库，备库开启recover

点击(此处)折叠或打开

  1. SQL> alter database open;
  3. Database altered.
  5. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
  7. Database altered.
15.查看主备库告警日志，可以看见主库在发送日志，备库在接收日志，至此dg完成                                                                               
主库：

点击(此处)折叠或打开

  1. Sun Apr 12 17:29:06 2015
  2. Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/arch/wbg/
  3. ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
  4. Sun Apr 12 17:29:08 2015
  5. ******************************************************************
  6. LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
  7. ******************************************************************
  8. Sun Apr 12 17:29:08 2015
  9. Thread 1 advanced to log sequence 33 (LGWR switch)
  10. Current log# 3 seq# 33 mem# 0: /u01/app/oracle/oradata/wbg/redo03.log
  11. Sun Apr 12 17:29:08 2015
  12. Archived Log entry 53 added for thread 1 sequence 32 ID 0xcb3dbb7c dest 1:
  13. LNS: Standby redo logfile selected for thread 1 sequence 32 for destination LOG_ARCHIVE_DEST_2
  14. LNS: Standby redo logfile selected for thread 1 sequence 33 for destination LOG_ARCHIVE_DEST_2
  15. Sun Apr 12 17:29:26 2015
  16. ALTER SYSTEM ARCHIVE LOG
  17. Sun Apr 12 17:29:26 2015
  18. Thread 1 advanced to log sequence 34 (LGWR switch)
  19. Current log# 1 seq# 34 mem# 0: /u01/app/oracle/oradata/wbg/redo01.log
  20. Archived Log entry 55 added for thread 1 sequence 33 ID 0xcb3dbb7c dest 1:
  21. Sun Apr 12 17:29:26 2015
  22. LNS: Standby redo logfile selected for thread 1 sequence 34 for destination LOG_ARCHIVE_DEST_2
  23. ALTER SYSTEM ARCHIVE LOG
  24. Thread 1 advanced to log sequence 35 (LGWR switch)
  25. Current log# 2 seq# 35 mem# 0: /u01/app/oracle/oradata/wbg/redo02.log
  26. Archived Log entry 57 added for thread 1 sequence 34 ID 0xcb3dbb7c dest 1:
  27. LNS: Standby redo logfile selected for thread 1 sequence 35 for destination LOG_ARCHIVE_DEST_2
  28. ALTER SYSTEM ARCHIVE LOG
  29. Thread 1 advanced to log sequence 36 (LGWR switch)
  30. Current log# 3 seq# 36 mem# 0: /u01/app/oracle/oradata/wbg/redo03.log
  31. Archived Log entry 59 added for thread 1 sequence 35 ID 0xcb3dbb7c dest 1:
  32. LNS: Standby redo logfile selected for thread 1 sequence 36 for destination LOG_ARCHIVE_DEST_2
备库：

点击(此处)折叠或打开

  1. Sun Apr 12 18:10:43 2015
  2. RFS[6]: Assigned to RFS process 1957
  3. RFS[6]: Identified database type as 'physical standby': Client is ARCH pid 3004
  4. RFS[6]: Opened log for thread 1 sequence 29 dbid 1668536352 branch 876836460
  5. Sun Apr 12 18:10:43 2015
  6. RFS[7]: Assigned to RFS process 1959
  7. RFS[7]: Identified database type as 'physical standby': Client is ARCH pid 3008
  8. Archived Log entry 25 added for thread 1 sequence 29 rlc 876836460 ID 0xcb3dbb7c dest 2:
  9. RFS[7]: Opened log for thread 1 sequence 30 dbid 1668536352 branch 876836460
  10. Archived Log entry 26 added for thread 1 sequence 30 rlc 876836460 ID 0xcb3dbb7c dest 2:
  11. Sun Apr 12 18:10:43 2015
  12. RFS[8]: Assigned to RFS process 1961
  13. RFS[8]: Identified database type as 'physical standby': Client is ARCH pid 3011
  14. RFS[8]: Opened log for thread 1 sequence 31 dbid 1668536352 branch 876836460
  15. Archived Log entry 27 added for thread 1 sequence 31 rlc 876836460 ID 0xcb3dbb7c dest 2:
  16. Sun Apr 12 18:10:45 2015
  17. RFS[9]: Assigned to RFS process 1963
  18. RFS[9]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 3173
  19. Primary database is in MAXIMUM PERFORMANCE mode
  20. RFS[9]: Selected log 4 for thread 1 sequence 32 dbid 1668536352 branch 876836460
  21. Sun Apr 12 18:10:45 2015
  22. Archived Log entry 28 added for thread 1 sequence 32 ID 0xcb3dbb7c dest 1:
  23. RFS[9]: Selected log 4 for thread 1 sequence 33 dbid 1668536352 branch 876836460
  24. Sun Apr 12 18:11:03 2015
  25. RFS[9]: Selected log 5 for thread 1 sequence 34 dbid 1668536352 branch 876836460
  26. Sun Apr 12 18:11:03 2015
  27. Archived Log entry 29 added for thread 1 sequence 33 ID 0xcb3dbb7c dest 1:
  28. Sun Apr 12 18:11:04 2015
  29. Archived Log entry 30 added for thread 1 sequence 34 ID 0xcb3dbb7c dest 1:
  30. RFS[9]: Selected log 4 for thread 1 sequence 35 dbid 1668536352 branch 876836460
  31. Sun Apr 12 18:11:05 2015
  32. Archived Log entry 31 added for thread 1 sequence 35 ID 0xcb3dbb7c dest 1:
  33. RFS[9]: Selected log 4 for thread 1 sequence 36 dbid 1668536352 branch 876836460
  34. Sun Apr 12 18:11:37 2015
  35. RFS[10]: Assigned to RFS process 1968
  36. RFS[10]: Identified database type as 'physical standby': Client is ARCH pid 3004
三、总结
1.用这种方法搭建物理dg，可以保持业务正常使用
2.备库恢复的方法，类似于rman的异机恢复，而且更加简单
3.修改主库的parameter，只能修改spfile                                                                                                                                                                                

                    

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30202921/viewspace-1570533/ ，如需转载，请注明出处，否则将追究法律责任。

1
0
上一篇： 没有了~
下一篇： 主库rac，备库单实例，偶遇trouble
请登录后发表评论 登录
全部评论
BogengWang

注册时间：2015-04-11

  • 博文量
    22
  • 访问量
    70513

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593