Bo's Oracle Station

查看: 3876|回复: 0

课程第56/57次(2018-11-18星期日上下午)使用保障前台业务0暂停的方式升级Oracle11gR2 MAA到Oracle12c MAA

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-11-17 20:58:13 | 显示全部楼层 |阅读模式
1. 环境概览
1.1升级前环境概览
1.2备库升级后环境概览
2. 通过两次Dataguard切换,升级所有GI
2.1主库Dataguard切换
2.2原主库(新备库)升级后环境概览
2.3主库Dataguard切换,原主库(新备库)重新成为主库
3. 通过TransientLogical Standby转换,升级所有DB
3.1MyOracle Support Bulletin 949322.1综述
3.2升级前的准备工作
3.2.1停用DataguradBroker
3.2.2确保主库和备库闪回区都存在,并都设置了正确的大小
3.2.3主库和备库都配置闪回数据库
3.2.4确保升级前,备库进行着健康的RecoverManaged Standby Database
3.3第一次执行physru.sh
3.4 在TransientLogical Standby上执行dbua
3.5第二次执行physru.sh
3.6手工重配置cluster01以便使用12.1.0.2数据库软件直接打开c01orcl到mount的状态
3.7最后一次执行physru.sh                                
3.8三次成功执行physru.sh后的善后工作                                
3.8.1打开c01orcl的所有实例
3.8.2打开c02orcl的所有实例
3.8.3验证在dbua过程中做的两个事务在主备库中都存在
3.8.4重新启用DataguradBroker
3.8.5主备库设置compatible参数为12.1.0.2.0




在1.2的过程中,
用户连主库就没影响,
备库实际上有重启过:
  1. [oracle@station23 ~]$ dgmgrl
  2. DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

  3. Copyright (c) 2000, 2009, Oracle. All rights reserved.

  4. Welcome to DGMGRL, type "help" for information.
  5. DGMGRL> connect sys/oracle_4U@c01orcl
  6. Connected.
  7. DGMGRL> show configuration verbose;

  8. Configuration - dg_config

  9.   Protection Mode: MaxPerformance
  10.   Databases:
  11.     c01orcl - Primary database
  12.     c02orcl - Physical standby database
  13.       Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

  14.   Properties:
  15.     FastStartFailoverThreshold      = '30'
  16.     OperationTimeout                = '30'
  17.     FastStartFailoverLagLimit       = '30'
  18.     CommunicationTimeout            = '180'
  19.     ObserverReconnect               = '0'
  20.     FastStartFailoverAutoReinstate  = 'TRUE'
  21.     FastStartFailoverPmyShutdown    = 'TRUE'
  22.     BystandersFollowRoleChange      = 'ALL'
  23.     ObserverOverride                = 'FALSE'
  24.     ExternalDestination1            = ''
  25.     ExternalDestination2            = ''
  26.     PrimaryLostWriteAction          = 'CONTINUE'

  27. Fast-Start Failover: DISABLED

  28. Configuration Status:
  29. WARNING

  30. DGMGRL>
复制代码
2.1接下来的switchover对所有人都会有影响。
2.3接下来的switchover对所有人都会有影响。
3.3
在使用physru.sh进行Rolling Upgrade过程中, Transient Logical Standby数据库可能会周期性地无法接收到来自主库的更新。如果此时万一主库发生故障,就没有备库应急,甚至会发生数据丢失。 因此在特别重要的生产环境中,要考虑到这种风险并采取相应手段比如准备备用的Logical Standby以应对。

还要通知用户,不能更新下面的表:
逻辑Standby有许多对象是不支持主备库同步的。在继续 physru.sh脚本前,请查看station11的/home/oracle/physru_unsupported.log(即调用physru.sh的位置)会列出这些对象。这些对象包括:sys拥有的对象、用以支持物化视图的表、全局临时表、带压缩的表和包含bfile,rowid,urowid等字段类型的表等等。


physru.sh脚本的输出:
  1. WARN: c02orcl is a RAC database.  Before this script can continue, you
  2.       must manually reduce the RAC to a single instance, disable the RAC, and
  3.       restart instance c02orcl1 in mounted mode.  This can be accomplished
  4.       with the following steps:

  5.         1) Shutdown all instances other than instance c02orcl1.
  6.            eg: srvctl stop instance -d c02orcl -i c02orcl2 -o abort

  7.         2) On instance c02orcl1, set the cluster_database parameter to FALSE.
  8.            eg: SQL> alter system set cluster_database=false scope=spfile;

  9.         3) Shutdown instance c02orcl1.
  10.            eg: SQL> shutdown abort;

  11.         4) Startup instance c02orcl1 in mounted mode.
  12.            eg: SQL> startup mount;

  13.       Once these steps have been performed, enter 'y' to continue the script.
  14.       If desired, you may enter 'n' to exit the script to perform the required
  15.       steps, and recall the script to resume from this point.

  16. Are you ready to continue? (y/n): y

  17. Nov 18 11:04:40 2018 [2-1] continuing
  18. Nov 18 11:04:40 2018 [2-1] verifying RAC is disabled at c02orcl
  19. Nov 18 11:04:41 2018 [2-1] verifying database roles
  20. Nov 18 11:04:42 2018 [2-1] verifying physical standby is mounted
  21. Nov 18 11:04:42 2018 [2-1] verifying database protection mode
  22. Nov 18 11:04:42 2018 [2-1] verifying transient logical standby datatype support

  23. WARN: Objects have been identified on the primary database which will not be
  24.       replicated on the transient logical standby.  The complete list of
  25.       objects and their associated unsupported datatypes can be found in the
  26.       dba_logstdby_unsupported view.  For convenience, this script has written
  27.       the contents of this view to a file - physru_unsupported.log.

  28.       Various options exist to deal with these objects such as:
  29.         - disabling applications that modify these objects
  30.         - manually resolving these objects after the upgrade
  31.         - extending support to these objects (see metalink note: 559353.1)

  32.       If you need time to review these options, you should enter 'n' to exit
  33.       the script.  Otherwise, you should enter 'y' to continue with the
  34.       rolling upgrade.

  35. Are you ready to proceed with the rolling upgrade? (y/n): y

  36. Nov 18 11:18:58 2018 [2-1] continuing
  37. Nov 18 11:18:59 2018 [2-2] starting media recovery on c02orcl
  38. Nov 18 11:19:06 2018 [2-2] confirming media recovery is running
  39. Nov 18 11:19:08 2018 [2-2] waiting for apply lag to fall under 30 seconds
  40. Nov 18 11:19:12 2018 [2-2] apply lag measured at 3 seconds
  41. Nov 18 11:19:12 2018 [2-2] stopping media recovery on c02orcl
  42. Nov 18 11:19:14 2018 [2-2] executing dbms_logstdby.build on database c01orcl
  43. Nov 18 11:19:47 2018 [2-2] converting physical standby into transient logical standby
  44. Nov 18 11:19:57 2018 [2-3] opening database c02orcl
  45. ./physru.sh: line 3337: warning: here-document at line 3332 delimited by end-of-file (wanted `EOF')
  46. Nov 18 11:20:06 2018 [2-4] configuring transient logical standby parameters for rolling upgrade
  47. Nov 18 11:20:07 2018 [2-4] starting logical standby on database c02orcl
  48. Nov 18 11:20:14 2018 [2-4] waiting until logminer dictionary has fully loaded
  49. Nov 18 11:21:18 2018 [2-4] dictionary load 10% complete
  50. Nov 18 11:21:28 2018 [2-4] dictionary load 41% complete
  51. Nov 18 11:21:39 2018 [2-4] dictionary load 60% complete
  52. Nov 18 11:21:50 2018 [2-4] dictionary load 62% complete
  53. Nov 18 11:22:10 2018 [2-4] dictionary load 74% complete
  54. Nov 18 11:22:21 2018 [2-4] dictionary load 75% complete
  55. Nov 18 11:22:31 2018 [2-4] dictionary load is complete
  56. Nov 18 11:22:31 2018 [2-4] waiting for apply lag to fall under 30 seconds
  57. Nov 18 11:23:11 2018 [2-4] apply lag measured at 37 seconds

复制代码



同时备库第一个实例上的日志:
  1. Sun Nov 18 11:18:58 2018
  2. Created guaranteed restore point PRU_0201
  3. Sun Nov 18 11:18:59 2018
  4.   alter database recover managed standby database using current logfile through next switchover disconnect
  5. Attempt to start background Managed Standby Recovery process (c02orcl1)
  6. Sun Nov 18 11:18:59 2018
  7. MRP0 started with pid=46, OS id=20863
  8. MRP0: Background Managed Standby Recovery process started (c02orcl1)
  9. started logmerger process
  10. Sun Nov 18 11:19:04 2018
  11. Managed Standby Recovery starting Real Time Apply
  12. Parallel Media Recovery started with 4 slaves
  13. Waiting for all non-current ORLs to be archived...
  14. All non-current ORLs have been archived.
  15. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_2_seq_39.350.992517005
  16. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_1_seq_40.353.992517007
  17. Completed:   alter database recover managed standby database using current logfile through next switchover disconnect
  18. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_2_seq_40.349.992517003
  19. Sun Nov 18 11:19:08 2018
  20. Standby controlfile consistent with primary
  21. RFS[6]: Selected log 5 for thread 1 sequence 45 dbid 1035727207 branch 991429098
  22. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_1_seq_41.351.992517007
  23. Sun Nov 18 11:19:08 2018
  24. Archived Log entry 99 added for thread 1 sequence 44 ID 0x3dcb3c16 dest 1:
  25. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_1_seq_42.354.992517009
  26. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_2_seq_41.352.992517007
  27. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_2_seq_42.356.992517071
  28. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_1_seq_43.355.992517069
  29. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_1_seq_44.357.992517549
  30. Media Recovery Waiting for thread 2 sequence 43 (in transit)
  31. Recovery of Online Redo Log: Thread 2 Group 9 Seq 43 Reading mem 0
  32.   Mem# 0: +DATA/c02orcl/onlinelog/group_9.274.991772999
  33.   Mem# 1: +FRA/c02orcl/onlinelog/group_9.270.991773009
  34. Media Recovery Waiting for thread 1 sequence 45 (in transit)
  35. Recovery of Online Redo Log: Thread 1 Group 5 Seq 45 Reading mem 0
  36.   Mem# 0: +DATA/c02orcl/onlinelog/group_5.270.991772955
  37.   Mem# 1: +FRA/c02orcl/onlinelog/group_5.266.991772961
  38. Standby controlfile consistent with primary
  39. RFS[6]: Selected log 6 for thread 1 sequence 46 dbid 1035727207 branch 991429098
  40. Sun Nov 18 11:19:11 2018
  41. Archived Log entry 100 added for thread 1 sequence 45 ID 0x3dcb3c16 dest 1:
  42. Media Recovery Waiting for thread 1 sequence 46 (in transit)
  43. Sun Nov 18 11:19:12 2018
  44.   alter database recover managed standby database cancel
  45. Recovery of Online Redo Log: Thread 1 Group 6 Seq 46 Reading mem 0
  46.   Mem# 0: +DATA/c02orcl/onlinelog/group_6.271.991772965
  47.   Mem# 1: +FRA/c02orcl/onlinelog/group_6.267.991772973
  48. MRP0: Background Media Recovery cancelled with status 16037
  49. Errors in file /u01/app/oracle/diag/rdbms/c02orcl/c02orcl1/trace/c02orcl1_pr00_20877.trc:
  50. ORA-16037: user requested cancel of managed recovery operation
  51. Managed Standby Recovery not using Real Time Apply
  52. Recovery interrupted!
  53. Sun Nov 18 11:19:13 2018
  54. MRP0: Background Media Recovery process shutdown (c02orcl1)
  55. Managed Standby Recovery Canceled (c02orcl1)
  56. Completed:   alter database recover managed standby database cancel
  57. Sun Nov 18 11:19:19 2018
  58. Standby controlfile consistent with primary
  59. RFS[6]: Selected log 5 for thread 1 sequence 47 dbid 1035727207 branch 991429098
  60. Sun Nov 18 11:19:19 2018
  61. Archived Log entry 101 added for thread 1 sequence 46 ID 0x3dcb3c16 dest 1:
  62. Standby controlfile consistent with primary
  63. RFS[6]: Selected log 6 for thread 1 sequence 48 dbid 1035727207 branch 991429098
  64. Sun Nov 18 11:19:20 2018
  65. Archived Log entry 102 added for thread 1 sequence 47 ID 0x3dcb3c16 dest 1:
  66. Sun Nov 18 11:19:21 2018
  67. Standby controlfile consistent with primary
  68. RFS[3]: Selected log 8 for thread 2 sequence 44 dbid 1035727207 branch 991429098
  69. Archived Log entry 103 added for thread 2 sequence 43 ID 0x3dcb3c16 dest 1:
  70. Sun Nov 18 11:19:27 2018
  71. db_recovery_file_dest_size of 14655 MB is 50.47% used. This is a
  72. user-specified limit on the amount of space that will be used by this
  73. database for recovery-related files, and does not reflect the amount of
  74. space available in the underlying filesystem or ASM diskgroup.
  75. Sun Nov 18 11:19:36 2018
  76. Standby controlfile consistent with primary
  77. RFS[6]: Selected log 5 for thread 1 sequence 49 dbid 1035727207 branch 991429098
  78. Sun Nov 18 11:19:37 2018
  79. Archived Log entry 104 added for thread 1 sequence 48 ID 0x3dcb3c16 dest 1:
  80. Sun Nov 18 11:19:42 2018
  81. Standby controlfile consistent with primary
  82. RFS[3]: Selected log 9 for thread 2 sequence 45 dbid 1035727207 branch 991429098
  83. Sun Nov 18 11:19:42 2018
  84. Archived Log entry 105 added for thread 2 sequence 44 ID 0x3dcb3c16 dest 1:
  85. Standby controlfile consistent with primary
  86. RFS[6]: Selected log 6 for thread 1 sequence 50 dbid 1035727207 branch 991429098
  87. Sun Nov 18 11:19:44 2018
  88. Archived Log entry 106 added for thread 1 sequence 49 ID 0x3dcb3c16 dest 1:
  89. Standby controlfile consistent with primary
  90. RFS[3]: Selected log 8 for thread 2 sequence 46 dbid 1035727207 branch 991429098
  91. Archived Log entry 107 added for thread 2 sequence 45 ID 0x3dcb3c16 dest 1:
  92. Sun Nov 18 11:19:47 2018
  93. Standby controlfile consistent with primary
  94. RFS[6]: Selected log 5 for thread 1 sequence 51 dbid 1035727207 branch 991429098
  95. Archived Log entry 108 added for thread 1 sequence 50 ID 0x3dcb3c16 dest 1:
  96. Sun Nov 18 11:19:47 2018
  97. alter database recover to logical standby keep identity
  98. Media Recovery Start: Managed Standby Recovery (c02orcl1)
  99. started logmerger process
  100. Sun Nov 18 11:19:48 2018
  101. Managed Standby Recovery not using Real Time Apply
  102. Parallel Media Recovery started with 4 slaves
  103. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_1_seq_46.359.992517559
  104. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_2_seq_43.361.992517561
  105. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_1_seq_47.360.992517561
  106. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_1_seq_48.362.992517577
  107. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_2_seq_44.363.992517583
  108. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_1_seq_49.364.992517585
  109. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_2_seq_45.365.992517587
  110. Media Recovery Log +FRA/c02orcl/archivelog/2018_11_18/thread_1_seq_50.366.992517587
  111. Incomplete Recovery applied until change 1350596 time 11/18/2018 11:19:44
  112. Media Recovery Complete (c02orcl1)
  113. Sun Nov 18 11:19:53 2018
  114. Killing 7 processes with pids 17473,17479,17481,17500,17502,17504,17514 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 15414
  115. Begin: Standby Redo Logfile archival
  116. End: Standby Redo Logfile archival
  117. RESETLOGS after incomplete recovery UNTIL CHANGE 1350596
  118. Resetting resetlogs activation ID 1036729366 (0x3dcb3c16)
  119. Online log +DATA/c02orcl/onlinelog/group_1.266.991772911: Thread 1 Group 1 was previously cleared
  120. Online log +FRA/c02orcl/onlinelog/group_1.262.991772919: Thread 1 Group 1 was previously cleared
  121. Online log +DATA/c02orcl/onlinelog/group_2.267.991772921: Thread 1 Group 2 was previously cleared
  122. Online log +FRA/c02orcl/onlinelog/group_2.263.991772929: Thread 1 Group 2 was previously cleared
  123. Online log +DATA/c02orcl/onlinelog/group_3.268.991772931: Thread 2 Group 3 was previously cleared
  124. Online log +FRA/c02orcl/onlinelog/group_3.264.991772939: Thread 2 Group 3 was previously cleared
  125. Online log +DATA/c02orcl/onlinelog/group_4.269.991772945: Thread 2 Group 4 was previously cleared
  126. Online log +FRA/c02orcl/onlinelog/group_4.265.991772951: Thread 2 Group 4 was previously cleared
  127. Standby became primary SCN: 1350594
  128. Sun Nov 18 11:19:56 2018
  129. Setting recovery target incarnation to 3
  130. RECOVER TO LOGICAL STANDBY: Complete - Database mounted as logical standby
  131. Completed: alter database recover to logical standby keep identity
  132. Sun Nov 18 11:19:57 2018
  133. Created guaranteed restore point PRU_0202
  134. Sun Nov 18 11:19:57 2018
  135. alter database open
  136. Sun Nov 18 11:19:57 2018
  137. Assigning activation ID 1036776380 (0x3dcbf3bc)
  138. Thread 1 opened at log sequence 1
  139.   Current log# 1 seq# 1 mem# 0: +DATA/c02orcl/onlinelog/group_1.266.991772911
  140.   Current log# 1 seq# 1 mem# 1: +FRA/c02orcl/onlinelog/group_1.262.991772919
  141. Successful open of redo thread 1
  142. MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
  143. Sun Nov 18 11:19:57 2018
  144. SMON: enabling cache recovery
  145. Redo thread 2 internally disabled at seq 1 (CKPT)
  146. Sun Nov 18 11:19:59 2018
  147. ARC2: Archiving disabled thread 2 sequence 1
  148. [21177] Successfully onlined Undo Tablespace 2.
  149. Undo initialization finished serial:0 start:58040994 end:58041604 diff:610 (6 seconds)
  150. Dictionary check beginning
  151. Dictionary check complete
  152. Verifying file header compatibility for 11g tablespace encryption..
  153. Verifying 11g file header compatibility for tablespace encryption completed
  154. SMON: enabling tx recovery
  155. Database Characterset is AL32UTF8
  156. No Resource Manager plan active
  157. replication_dependency_tracking turned off (no async multimaster replication found)
  158. Archived Log entry 109 added for thread 2 sequence 1 ID 0x0 dest 1:
  159. Sun Nov 18 11:20:00 2018
  160. Primary database is in MAXIMUM AVAILABILITY mode
  161. Changing standby controlfile to RESYNCHRONIZATION level
  162. Archive log rejected (thread 1 sequence 53) by RFS clients
  163. Starting background process QMNC
  164. Sun Nov 18 11:20:01 2018
  165. QMNC started with pid=38, OS id=21202
  166. LOGSTDBY: Validating controlfile with logical metadata
  167. LOGSTDBY: Validation skipped -- detected logical instantiation
  168. LOGSTDBY: Validation complete
  169. LOGSTDBY: skipping logfile pre-registration due to in-progress instantiation
  170. Sun Nov 18 11:20:04 2018
  171. RFS LogMiner: RFS id [21181] assigned as thread [2] PING handler
  172. Sun Nov 18 11:20:05 2018
  173. Primary database is in MAXIMUM AVAILABILITY mode
  174. Standby controlfile consistent with primary
  175. Standby controlfile consistent with primary
  176. Completed: alter database open
  177. RFS[7]: Assigned to RFS process 21222
  178. RFS[7]: Selected log 9 for thread 2 sequence 48 dbid 1035727207 branch 991429098
  179. Sun Nov 18 11:20:05 2018
  180. Created guaranteed restore point PRU_0203
  181. Sun Nov 18 11:20:06 2018
  182. RFS[8]: Assigned to RFS process 21236
  183. RFS[8]: Selected log 10 for thread 2 sequence 47 dbid 1035727207 branch 991429098
  184. Sun Nov 18 11:20:06 2018
  185. Starting background process CJQ0
  186. Sun Nov 18 11:20:06 2018
  187. CJQ0 started with pid=47, OS id=21246
  188. Sun Nov 18 11:20:07 2018
  189. LOGSTDBY: APPLY_SET: LOG_AUTO_DELETE changed to FALSE
  190. LOGSTDBY: APPLY_SET: MAX_EVENTS_RECORDED changed to 2000000000
  191. LOGSTDBY: APPLY_SET: RECORD_UNSUPPORTED_OPERATIONS changed to TRUE
  192. LOGSTDBY: APPLY_SET: MAX_SERVERS changed to 15
  193. LOGSTDBY: APPLY_SET: MAX_SGA changed to 50
  194. Sun Nov 18 11:20:07 2018
  195.   alter database start logical standby apply immediate
  196. LOGSTDBY: Creating new session for dbid 1035727207 starting at scn 0x0000.00000000
  197. LOGSTDBY: Created session of id 1
  198. LOGSTDBY: Attempting to pre-register dictionary build logfiles
  199. LOGMINER: Error 308 encountered, failed to read  logfile 1_49_991429098.dbf
  200. LOGMINER: Encountered error 1291 while adding logfile 1_49_991429098.dbf to session 1
  201. LOGMINER: Error 308 encountered, failed to read  logfile 2_44_991429098.dbf
  202. LOGMINER: Encountered error 1291 while adding logfile 2_44_991429098.dbf to session 1
  203. LOGMINER: Error 308 encountered, failed to read  logfile 2_43_991429098.dbf
  204. LOGMINER: Encountered error 1291 while adding logfile 2_43_991429098.dbf to session 1
  205. LOGMINER: Error 308 encountered, failed to read  logfile 1_48_991429098.dbf
  206. LOGMINER: Encountered error 1291 while adding logfile 1_48_991429098.dbf to session 1
  207. LOGSTDBY: Unable to register recovery logfiles, will resend
  208. ALTER DATABASE START LOGICAL STANDBY APPLY (c02orcl1)
  209. with optional part
  210. IMMEDIATE
  211. Attempt to start background Logical Standby process
  212. LOGSTDBY parameters set by user:
  213. LOGSTDBY                        MAX_SGA = 50
  214. LOGSTDBY                    MAX_SERVERS = 15
  215. LOGSTDBY            MAX_EVENTS_RECORDED = 2000000000
  216. LOGSTDBY  RECORD_UNSUPPORTED_OPERATIONS = TRUE
  217. LOGSTDBY                LOG_AUTO_DELETE = FALSE
  218. Sun Nov 18 11:20:08 2018
  219. LSP0 started with pid=45, OS id=21256
  220. Completed:   alter database start logical standby apply immediate
  221. LOGMINER: Parameters summary for session# = 1
  222. LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
  223. LOGMINER: Memory Size = 50M, Checkpoint interval = 250M
  224. LOGMINER: SpillScn 0, ResetLogScn 0
  225. Sun Nov 18 11:21:05 2018
  226. RFS[8]: Opened log for thread 2 sequence 44 dbid 1035727207 branch 991429098
  227. Sun Nov 18 11:21:05 2018
  228. RFS[9]: Assigned to RFS process 21527
  229. RFS[9]: Opened log for thread 2 sequence 43 dbid 1035727207 branch 991429098
  230. RFS LogMiner: Registered logfile [+FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_2_seq_44.369.992517665] to LogMiner session id [1]
  231. RFS LogMiner: Registered logfile [+FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_2_seq_43.370.992517665] to LogMiner session id [1]
  232. Sun Nov 18 11:21:06 2018
  233. LOGMINER: summary for session# = 1
  234. LOGMINER: StartScn: 1348931 (0x0000.00149543)
  235. LOGMINER: EndScn: 0 (0x0000.00000000)
  236. LOGMINER: HighConsumedScn: 1350595 (0x0000.00149bc3)
  237. LOGMINER: session_flag: 0x1
  238. LOGMINER: Read buffers: 16
  239. LOGMINER: Memory LWM: limit 10M, LWM 40M, 80%
  240. LOGMINER: Memory Release Limit: 1M
  241. Sun Nov 18 11:21:06 2018
  242. RFS[10]: Assigned to RFS process 21181
  243. RFS[10]: Opened log for thread 1 sequence 48 dbid 1035727207 branch 991429098
  244. RFS[8]: Opened log for thread 1 sequence 49 dbid 1035727207 branch 991429098
  245. RFS[9]: Opened log for thread 2 sequence 45 dbid 1035727207 branch 991429098
  246. RFS LogMiner: Registered logfile [+FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_1_seq_49.372.992517667] to LogMiner session id [1]
  247. RFS LogMiner: Registered logfile [+FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_2_seq_45.373.992517667] to LogMiner session id [1]
  248. RFS[8]: Opened log for thread 1 sequence 50 dbid 1035727207 branch 991429098
  249. RFS[9]: Selected log 8 for thread 2 sequence 46 dbid 1035727207 branch 991429098
  250. RFS LogMiner: Registered logfile [+FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_1_seq_50.374.992517667] to LogMiner session id [1]
  251. Sun Nov 18 11:21:07 2018
  252. RFS LogMiner: Registered logfile [+FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_2_seq_46.375.992517667] to LogMiner session id [1]
  253. RFS[9]: Selected log 5 for thread 1 sequence 51 dbid 1035727207 branch 991429098
  254. RFS[8]: Opened log for thread 1 sequence 52 dbid 1035727207 branch 991429098
  255. RFS[9]: Opened log for thread 2 sequence 47 dbid 1035727207 branch 991429098
  256. RFS LogMiner: Registered logfile [+FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_1_seq_52.376.992517667] to LogMiner session id [1]
  257. Sun Nov 18 11:21:08 2018
  258. RFS LogMiner: Registered logfile [+FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_1_seq_51.377.992517669] to LogMiner session id [1]
  259. RFS LogMiner: Registered logfile [+FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_1_seq_48.371.992517667] to LogMiner session id [1]
  260. RFS LogMiner: Registered logfile [+FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_2_seq_47.378.992517669] to LogMiner session id [1]
  261. RFS LogMiner: RFS id [21181] assigned as thread [2] PING handler
  262. Sun Nov 18 11:21:09 2018
  263. LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=50 OS id=21546 sid=514 started
  264. Sun Nov 18 11:21:09 2018
  265. LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=51 OS id=21548 sid=767 started
  266. Sun Nov 18 11:21:09 2018
  267. LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=53 OS id=21550 sid=266 started
  268. LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 48, +FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_1_seq_48.371.992517667
  269. Sun Nov 18 11:21:15 2018
  270. Starting background process SMCO
  271. Sun Nov 18 11:21:15 2018
  272. SMCO started with pid=54, OS id=21568
  273. Sun Nov 18 11:21:43 2018
  274. LOGMINER: End   mining logfile during dictionary load for session 1 thread 1 sequence 48, +FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_1_seq_48.371.992517667
  275. LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 49, +FRA/c02orcl/foreign_archivelog/c01orcl/2018_11_18/thread_1_seq_49.372.992517667

复制代码
同时,主库对备库变成逻辑standby的反映:
  1. Archived Log entry 163 added for thread 1 sequence 49 ID 0x3dcb3c16 dest 1:
  2. Sun Nov 18 11:19:44 2018
  3. Logminer Bld: Done
  4. LOGMINER: Dictionary Build: Waiting for txns in-flight at scn 0x0000.00149543 [1348931] to complete
  5. LOGMINER: Dictionary Build: All in-flight txns at scn 0x0000.00149543 [1348931] completed
  6. Thread 1 cannot allocate new log, sequence 51
  7. Checkpoint not complete
  8.   Current log# 1 seq# 50 mem# 0: +DATA/c01orcl/onlinelog/group_1.261.991429099
  9.   Current log# 1 seq# 50 mem# 1: +FRA/c01orcl/onlinelog/group_1.257.991429105
复制代码
3.4 dbua过程中备库不可用

  1. 确保“+FRA/C02ORCL/foreign_archivelog/c01orcl/”有从c01orcl传送来的所有日志,确保 c02orcl上的Logical Standby Apply紧紧跟随主库,
复制代码

逻辑standby故障处理:
  1. SQL>  select APPLIED_SCN,LATEST_SCN, MINING_SCN from v$logstdby_progress;

  2. APPLIED_SCN LATEST_SCN MINING_SCN
  3. ----------- ---------- ----------
  4.     1352467    1353758

复制代码
  1. SQL> select  * from v$logstdby_process;
复制代码
  1. SQL> alter database start logical standby apply;

  2. Database altered.
复制代码

如果打开后,马上断掉(v$logstdby_process瞬间消失),查event, dbms_logstdby.skip跳过event:
  1. SQL> select event from dba_logstdby_events order by  event_time desc;
复制代码

逻辑standby的skip:
  1. select  to_char(e.EVENT), e.EVENT_TIME
  2. from dba_logstdby_events e order by 2 desc;


  3. select  * from dba_logstdby_skip;

  4. select APPLIED_SCN,LATEST_SCN, MINING_SCN from v$logstdby_progress;

  5. select  * from dba_logstdby_progress;
  6.   
  7.    
  8.    begin
  9.    dbms_logstdby.skip_error('DML','SH','CMP4$88063');
  10.    end;

  11. begin
  12.    dbms_logstdby.skip_error('SCHEMA_DDL','SH','CMP4$88063');
  13.    end;

  14. begin
  15.    dbms_logstdby.skip_error('TABLE','SH','CMP4$88063');
  16.    end;
复制代码
dbms_logstdby.skip_error的第一个参数的介绍:
Supported Values for the stmt Parameter
Keyword         Associated SQL Statements

NON_SCHEMA_DDL
        All DDL that does not pertain to a particular schema
Note: SCHEMA_NAME and OBJECT_NAME must be null

SCHEMA_DDL
        All DDL statements that create, modify, or drop schema objects (for example: tables, indexes, and columns)
Note: SCHEMA_NAME and OBJECT_NAME must not be null

DML
        Includes DML statements on a table (for example: INSERT, UPDATE, and DELETE)

CLUSTER
        AUDIT CLUSTER
CREATE CLUSTER
DROP CLUSTER
TRUNCATE CLUSTER

CONTEXT
        CREATE CONTEXT
DROP CONTEXT

DATABASE LINK
        CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
DROP DATABASE LINK
DROP PUBLIC DATABASE LINK

DIMENSION
        ALTER DIMENSION
CREATE DIMENSION
DROP DIMENSION

DIRECTORY
        CREATE DIRECTORY
DROP DIRECTORY

INDEX
        ALTER INDEX
CREATE INDEX
DROP INDEX

PROCEDURE
        ALTER FUNCTION
ALTER PACKAGE
ALTER PACKAGE BODY
ALTER PROCEDURE
CREATE FUNCTION
CREATE LIBRARY
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PROCEDURE
DROP FUNCTION
DROP LIBRARY
DROP PACKAGE
DROP PACKAGE BODY
DROP PROCEDURE

PROFILE
        ALTER PROFILE
CREATE PROFILE
DROP PROFILE

ROLE
        ALTER ROLE
CREATE ROLE
DROP ROLE
SET ROLE

ROLLBACK STATEMENT
        ALTER ROLLBACK SEGMENT
CREATE ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT

SEQUENCE
        ALTER SEQUENCE
CREATE SEQUENCE
DROP SEQUENCE

SYNONYM
        CREATE PUBLIC SYNONYM
CREATE SYNONYM
DROP PUBLIC SYNONYM
DROP SYNONYM

TABLE
        ALTER TABLE
CREATE TABLE
DROP TABLE

TABLESPACE
        CREATE TABLESPACE
DROP TABLESPACE
TRUNCATE TABLESPACE

TRIGGER
        ALTER TRIGGER
CREATE TRIGGER
DISABLE ALL TRIGGERS
DISABLE TRIGGER
DROP TRIGGER
ENABLE ALL TRIGGERS
ENABLE TRIGGER

TYPE
        ALTER TYPE
ALTER TYPE BODY
CREATE TYPE
CREATE TYPE BODY
DROP TYPE
DROP TYPE BODY

USER
        ALTER USER
CREATE USER
DROP USER

VIEW
        CREATE VIEW
DROP VIEW

3.4 dbua:
  1. 在进度条运行过程中,我们也可以到此目录下/u01/app/oracle/cfgtoollogs/dbua/c02orcl/upgrade1看日志。在截屏所示的“Pre Upgrade Steps”的这一步,c02orcl数据库会自动被关闭。然后只有一个实例启动起来以进行之后的升级。

  2. 在备库c02orcl升级过程中,主库c01orcl依然打开着支持前台业务,所有在主库上执行的事务将仍然能够在c02orcl升级后应用在它身上。为了证明这一点,就在此刻,我们在c01orcl上执行:
复制代码










回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 14:22 , Processed in 0.034403 second(s), 24 queries .

快速回复 返回顶部 返回列表