DG 备库切换为主库 HANG住

wkc168

DB:
SQL> select * from v$version
  2  /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

OS :win2003

在DG 中 我模拟主库当机（停掉所有服务）


我把备库切换为主库
备库操作：
alter database commit to switchover to primary with session shutdown

在这里HANG在这里

看了告警日志
Switchover: Media recovery is still active
If media recovery active, switchover will wait 900 seconds
Thu Feb 02 21:36:04 2012
Switchover: Media recovery is still active
If media recovery active, switchover will wait 900 seconds

Maclean Liu(刘相兵

跑一下这个脚本

1. SELECT DECODE(os_backup.backup + rman_backup.backup, 0, 'FALSE', 'TRUE') backup
   
2.   FROM (SELECT COUNT(*) backup FROM gv$backup WHERE status = 'ACTIVE') os_backup,
   
3.        (SELECT COUNT(*) backup
   
4.           FROM gv$session
   
5.          WHERE status = 'ACTIVE'
   
6.            AND client_info like '%rman%') rman_backup
   
7. /

复制代码

wkc168

SQL> SELECT DECODE(os_backup.backup + rman_backup.backup, 0, 'FALSE', 'TRUE') ba
ckup
  2  FROM (SELECT COUNT(*) backup FROM gv$backup WHERE status = 'ACTIVE') os_bac
kup,
  3  (SELECT COUNT(*) backup          FROM gv$session         WHERE status = 'AC
TIVE'           AND client_info like '%rman%') rman_backup
  4  /

BACKU
-----
FALSE

Maclean Liu(刘相兵

跑一下 physical standby 上

1. - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - -
   
2. 
   
3. -- NAME: DG_phy_stby_diag.sql
   
4. -- ------------------------------------------------------------------------
   
5. -- AUTHOR:
   
6. --    Michael Smith - Oracle Support Services - DataServer Group
   
7. --    Copyright 2002, Oracle Corporation
   
8. -- ------------------------------------------------------------------------
   
9. -- PURPOSE:
   
10. --    This script is to be used to assist in collection information to help
    
11. --    troubeshoot Data Guard issues.
    
12. -- ------------------------------------------------------------------------
    
13. -- DISCLAIMER:
    
14. --    This script is provided for educational purposes only. It is NOT
    
15. --    supported by Oracle World Wide Technical Support.
    
16. --    The script has been tested and appears to work as intended.
    
17. --    You should always run new scripts on a test instance initially.
    
18. -- ------------------------------------------------------------------------
    
19. -- Script output is as follows:
    
20. 
    
21. set echo off
    
22. set feedback off
    
23. column timecol new_value timestamp
    
24. column spool_extension new_value suffix
    
25. select to_char(sysdate,'Mondd_hhmi') timecol,
    
26. '.out' spool_extension from sys.dual;
    
27. column output new_value dbname
    
28. select value || '_' output
    
29. from v$parameter where name = 'db_name';
    
30. spool dgdiag_phystby_&&dbname&&timestamp&&suffix
    
31. set lines 200
    
32. set pagesize 35
    
33. set trim on
    
34. set trims on
    
35. alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
    
36. set feedback on
    
37. select to_char(sysdate) time from dual;
    
38. 
    
39. set echo on
    
40. 
    
41. --
    
42. -- ARCHIVER can be  (STOPPED | STARTED | FAILED) FAILED means that the archiver failed
    
43. -- to archive a -- log last time, but will try again within 5 minutes. LOG_SWITCH_WAIT
    
44. -- The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for. Note that
    
45. -- if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online
    
46. -- redo log, then value is NULL
    
47. 
    
48. column host_name format a20 tru
    
49. column version format a9 tru
    
50. select instance_name,host_name,version,archiver,log_switch_wait from v$instance;
    
51. 
    
52. -- The following select will give us the generic information about how this standby is
    
53. -- setup.  The database_role should be standby as that is what this script is intended
    
54. -- to be ran on.  If protection_level is different than protection_mode then for some
    
55. -- reason the mode listed in protection_mode experienced a need to downgrade.  Once the
    
56. -- error condition has been corrected the protection_level should match the protection_mode
    
57. -- after the next log switch.
    
58. 
    
59. column ROLE format a7 tru
    
60. select name,database_role,log_mode,controlfile_type,protection_mode,protection_level
    
61. from v$database;
    
62. 
    
63. -- Force logging is not mandatory but is recommended.  Supplemental logging should be enabled
    
64. -- on the standby if a logical standby is in the configuration. During normal
    
65. -- operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or NOT ALLOWED.
    
66. 
    
67. column force_logging format a13 tru
    
68. column remote_archive format a14 tru
    
69. column dataguard_broker format a16 tru
    
70. select force_logging,remote_archive,supplemental_log_data_pk,supplemental_log_data_ui,
    
71. switchover_status,dataguard_broker from v$database;
    
72. 
    
73. -- This query produces a list of all archive destinations and shows if they are enabled,
    
74. -- what process is servicing that destination, if the destination is local or remote,
    
75. -- and if remote what the current mount ID is. For a physical standby we should have at
    
76. -- least one remote destination that points the primary set but it should be deferred.
    
77. 
    
78. COLUMN destination FORMAT A35 WRAP
    
79. column process format a7
    
80. column archiver format a8
    
81. column ID format 99
    
82. 
    
83. select dest_id "ID",destination,status,target,
    
84. archiver,schedule,process,mountid
    
85. from v$archive_dest;
    
86. 
    
87. -- If the protection mode of the standby is set to anything higher than max performance
    
88. -- then we need to make sure the remote destination that points to the primary is set
    
89. -- with the correct options else we will have issues during switchover.
    
90. 
    
91. select dest_id,process,transmit_mode,async_blocks,
    
92. net_timeout,delay_mins,reopen_secs,register,binding
    
93. from v$archive_dest;
    
94. 
    
95. -- The following select will show any errors that occured the last time an attempt to
    
96. -- archive to the destination was attempted.  If ERROR is blank and status is VALID then
    
97. -- the archive completed correctly.
    
98. 
    
99. column error format a55 tru
    
100. select dest_id,status,error from v$archive_dest;
     
101. 
     
102. -- Determine if any error conditions have been reached by querying thev$dataguard_status
     
103. -- view (view only available in 9.2.0 and above):
     
104. 
     
105. column message format a80
     
106. select message, timestamp
     
107. from v$dataguard_status
     
108. where severity in ('Error','Fatal')
     
109. order by timestamp;
     
110. 
     
111. -- The following query is ran to get the status of the SRL's on the standby.  If the
     
112. -- primary is archiving with the LGWR process and SRL's are present (in the correct
     
113. -- number and size) then we should see a group# active.
     
114. 
     
115. select group#,sequence#,bytes,used,archived,status from v$standby_log;
     
116. 
     
117. -- The above SRL's should match in number and in size with the ORL's returned below:
     
118. 
     
119. select group#,thread#,sequence#,bytes,archived,status from v$log;
     
120. 
     
121. -- Query v$managed_standby to see the status of processes involved in the
     
122. -- configuration.
     
123. 
     
124. select process,status,client_process,sequence#,block#,active_agents,known_agents
     
125. from v$managed_standby;
     
126. 
     
127. -- Verify that the last sequence# received and the last sequence# applied to standby
     
128. -- database.
     
129. 
     
130. select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
     
131. from (select thread# thrd, max(sequence#) almax
     
132. from v$archived_log
     
133. where resetlogs_change#=(select resetlogs_change# from v$database)
     
134. group by thread#) al,
     
135. (select thread# thrd, max(sequence#) lhmax
     
136. from v$log_history
     
137. where first_time=(select max(first_time) from v$log_history)
     
138. group by thread#) lh
     
139. where al.thrd = lh.thrd;
     
140. 
     
141. -- The V$ARCHIVE_GAP fixed view on a physical standby database only returns the next
     
142. -- gap that is currently blocking redo apply from continuing. After resolving the
     
143. -- identified gap and starting redo apply, query the V$ARCHIVE_GAP fixed view again
     
144. -- on the physical standby database to determine the next gap sequence, if there is
     
145. -- one.
     
146. 
     
147. select * from v$archive_gap;
     
148. 
     
149. -- Non-default init parameters.
     
150. 
     
151. set numwidth 5
     
152. column name format a30 tru
     
153. column value format a50 wra
     
154. select name, value
     
155. from v$parameter
     
156. where isdefault = 'FALSE';
     
157. 
     
158. spool off
     
159. 
     
160. - - - - - - - - - - - - - - - -  Script ends here  - - - - - - - - - - - - - - - -

复制代码

tail -5000 alert*.log

把 mrp 进程的 trace 也上传

wkc168

告警信息

Thu Feb 02 21:04:35 2012
alter database recover managed standby database disconnect from session
MRP0 started with pid=18, OS id=3480
Managed Standby Recovery not using Real Time Apply
Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\LOG1_65_771764791.ARC
Thu Feb 02 21:04:41 2012
Completed: alter database recover managed standby database disconnect from session
Thu Feb 02 21:04:45 2012
Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ORCL02_ARCHIVE\LOG1_66_771764791.ARC
Media Recovery Waiting for thread 1 sequence 67
Thu Feb 02 21:06:04 2012
alter database commit to switchover to primary with session shutdown
Thu Feb 02 21:06:04 2012
If media recovery active, switchover will wait 900 seconds
Thu Feb 02 21:18:25 2012
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Feb 02 21:21:04 2012
Switchover: Media recovery is still active
If media recovery active, switchover will wait 900 seconds
Thu Feb 02 21:36:04 2012
Switchover: Media recovery is still active
If media recovery active, switchover will wait 900 seconds
Thu Feb 02 21:51:04 2012
Switchover: Media recovery is still active
If media recovery active, switchover will wait 900 seconds
Thu Feb 02 22:06:04 2012

Maclean Liu(刘相兵

ODM FIND:

1. SQL> select process,status,client_process,sequence#,block#,active_agents,known_agents
   
2.   2  from v$managed_standby;
   
3. 
   
4. PROCESS STATUS       CLIENT_P SEQUENCE# BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
   
5. ------- ------------ -------- --------- ------ ------------- ------------
   
6. ARCH    CONNECTED    ARCH             0      0             0            0
   
7. ARCH    CONNECTED    ARCH             0      0             0            0
   
8. MRP0    WAIT_FOR_LOG N/A             67      0             0            0
   
9. 
   
10. 
    
11. MRP 在等日志 67
    
12. 
    
13. SQL> -- The V$ARCHIVE_GAP fixed view on a physical standby database only returns the next
    
14. SQL> -- gap that is currently blocking redo apply from continuing. After resolving the
    
15. SQL> -- identified gap and starting redo apply, query the V$ARCHIVE_GAP fixed view again
    
16. SQL> -- on the physical standby database to determine the next gap sequence, if there is
    
17. SQL> -- one.
    
18. SQL>
    
19. SQL> select * from v$archive_gap;
    
20. 
    
21. 未选定行
    
22. 
    
23. 
    
24. 没有archive gap
    
25. 
    
26. SQL> select group#,sequence#,bytes,used,archived,status from v$standby_log;
    
27. 
    
28. GROUP# SEQUENCE# BYTES  USED ARC STATUS
    
29. ------ --------- ----- ----- --- ----------
    
30.      5         0 #####   512 NO  UNASSIGNED
    
31.      6         0 #####   512 NO  UNASSIGNED
    
32.      7         0 #####   512 NO  UNASSIGNED
    
33.      8         0 #####   512 YES UNASSIGNED
    
34. 
    
35. 
    
36. Standby Redo log 都是 UNASSIGNED ，而你用的是LGWR模式 这点很奇怪
    
37. 
    
38. 
    
39. db_file_name_convert           D:\oracle\product\10.2.0\oradata\orcl,
    
40.                                                     D:\oracle\product\10.2.0\oradata\orcl
    
41. 
    
42. log_file_name_convert          D:\oracle\product\10.2.0\oradata\orcl,
    
43.                                                     D:\oracle\product\10.2.0\oradata\orcl
    
44. 
    
45. LOG_FILE_NAME_CONVERT为啥要这样设置？

复制代码

给我的感觉是 primary上 online redo中的信息没传过来， 且你的 standby redo log从未被用过 这点很可疑

wkc168

db_file_name_convert           D:\oracle\product\10.2.0\oradata\orcl,
                                                    D:\oracle\product\10.2.0\oradata\orcl

log_file_name_convert          D:\oracle\product\10.2.0\oradata\orcl,
                                                    D:\oracle\product\10.2.0\oradata\orcl

LOG_FILE_NAME_CONVERT为啥要这样设置？


这个我明白是多余


有什么解决方法

Maclean Liu(刘相兵

SQL> set linesize 200 pagesize 1400


SQL>  select fnnam,fnonm from x$kccfn where fntyp=3;

wkc168

SQL> select fnnam,fnonm from x$kccfn where fntyp=3;

FNNAM
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
FNONM
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STANDREDO_7.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STANDREDO_7.LOG

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STANDREDO_6.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STANDREDO_6.LOG

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STANDREDO_5.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STANDREDO_5.LOG

D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STANDREDO_8.LOG
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\STANDREDO_8.LOG


已选择7行。

Maclean Liu(刘相兵

action plan:

1. 取消 switch over

2.  测试你的standby 日志是否被正常使用，

SQL> select group#,sequence#,bytes,used,archived,status from v$standby_log;

3. 确认 standby redo log位置正确

wkc168

SQL> select group#,sequence#,bytes,used,archived,status from v$standby_log;

GROUP# SEQUENCE# BYTES  USED ARC STATUS
------ --------- ----- ----- --- ----------
     5         0 #####   512 NO  UNASSIGNED
     6         0 #####   512 NO  UNASSIGNED
     7         0 #####   512 NO  UNASSIGNED
     8         0 #####   512 YES UNASSIGNED

已选择4行。