主库不停做物理dg

一、环境（主备库环境相同）
   操作系统：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/ host s如下  

点击(此处)折叠或打开

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
   
6. 
   
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;
   
2. 
   
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;
   
2. 
   
3. Database altered.
   
4. 
   
5. SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/wbg/std02.log' size 50m;
   
6. 
   
7. Database altered.
   
8. 
   
9. SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/wbg/std03.log' size 50m;
   
10. 
    
11. Database altered.
    
12. 
    
13. SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/wbg/std04.log' size 50m;
    
14. 
    
15. Database altered.

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

点击(此处)折叠或打开

1. SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
   
2. 
   
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 /
   
2. 
   
3. Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 12 15:30:14 2015
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. connected to target database: WBG (DBID=1668536352)
   
8. 
   
9. RMAN> backup database format '/u01/app/oracle/rman/primary/%U' plus archivelog format '/u01/app/oracle/rman/primary/%U';
   
10. 
    
11. 
    
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
    
29. 
    
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
    
52. 
    
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
    
64. 
    
65. RMAN> quit
    
66. 
    
67. 
    
68. Recovery Manager complete.
    
69. [oracle@wbg1 ~]$

11.启动备库的instance，并可以看到，文件已经可以convert过来

点击(此处)折叠或打开

1. [oracle@wbg2 dbs]$ !sql
   
2. sqlplus / as sysdba
   
3. 
   
4. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 12 16:07:39 2015
   
5. 
   
6. Copyright (c) 1982, 2009, Oracle. All rights reserved.
   
7. 
   
8. Connected to an idle instance.
   
9. 
   
10. SQL> startup nomount
    
11. ORACLE instance started.
    
12. 
    
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
    
20. 
    
21. SQL> alter database mount;
    
22. 
    
23. Database altered.
    
24. 
    
25. SQL> select name from v$datafile;
    
26. 
    
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 /
   
2. 
   
3. Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 12 16:20:54 2015
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
   
6. 
   
7. connected to target database: WBG (DBID=1668536352, not open)
   
8. 
   
9. RMAN> catalog start with '/u01/app/oracle/rman/standby/';
   
10. 
    
11. using target database control file instead of recovery catalog
    
12. searching for all files that match the pattern /u01/app/oracle/rman/standby/
    
13. 
    
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
    
20. 
    
21. Do you really want to catalog the above files (enter YES or NO)? yes
    
22. cataloging files...
    
23. cataloging done
    
24. 
    
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
    
31. 
    
32. RMAN> restore database;
    
33. 
    
34. Starting restore at 12-APR-15
    
35. allocated channel: ORA_DISK_1
    
36. channel ORA_DISK_1: SID=20 device type=DISK
    
37. 
    
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
    
50. 
    
51. RMAN> recover database;
    
52. 
    
53. Starting recover at 12-APR-15
    
54. using channel ORA_DISK_1
    
55. 
    
56. starting media recovery
    
57. 
    
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
    
87. 
    
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博客 ” ，链接：http://blog.itpub.net/30202921/viewspace-1570533/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30202921/viewspace-1570533/