Although v$diag_alert_ext works for a single instance, there is no corresponding gv$ view for RAC.
However, the gv$ function can be used to execute a cursor across all instances. Here I query for the alert log in the two nodes srv1 and srv2 and order the results by timestamp to capture messages in the last 15 minutes : Note all the activity that happens when I CLOSE and later OPEN a PDB in one node, resulting in the other node also responding to maintain Global Resources.
SQL> l 1 select * from table(gv$(cursor 2 (select originating_timestamp, host_id, con_id, message_text 3 from v$diag_alert_ext 4 where originating_timestamp > sysdate - 0.25/24 -- time filter 5 and message_text like '%' -- message filter 6 ) 7 ) 8 ) 9* order by 1,2,3 SQL> / ORIGINATING_TIMESTAMP --------------------------------------------------------------------------- HOST_ID CON_ID ------------------------------------------------------------------- ---------- MESSAGE_TEXT ------------------------------------------------------------------------------------------------------------------------------------ 07-JAN-25 02.23.37.941000000 PM +00:00 srv2 1 Thread 2 cannot allocate new log, sequence 278 07-JAN-25 02.23.37.941000000 PM +00:00 srv2 1 Checkpoint not complete 07-JAN-25 02.23.37.941000000 PM +00:00 srv2 1 Current log# 3 seq# 277 mem# 0: +DATA/RACDB/ONLINELOG/group_3.270.1164520171 07-JAN-25 02.23.37.941000000 PM +00:00 srv2 1 Current log# 3 seq# 277 mem# 1: +FRA/RACDB/ONLINELOG/group_3.259.1164520171 07-JAN-25 02.23.38.255000000 PM +00:00 srv1 1 Thread 1 cannot allocate new log, sequence 322 07-JAN-25 02.23.38.255000000 PM +00:00 srv1 1 Current log# 1 seq# 321 mem# 1: +FRA/RACDB/ONLINELOG/group_1.257.1164519549 07-JAN-25 02.23.38.255000000 PM +00:00 srv1 1 Current log# 1 seq# 321 mem# 0: +DATA/RACDB/ONLINELOG/group_1.263.1164519549 07-JAN-25 02.23.38.255000000 PM +00:00 srv1 1 Checkpoint not complete 07-JAN-25 02.23.40.951000000 PM +00:00 srv2 1 Thread 2 advanced to log sequence 278 (LGWR switch), current SCN: 10841502 07-JAN-25 02.23.40.951000000 PM +00:00 srv2 1 Current log# 4 seq# 278 mem# 0: +DATA/RACDB/ONLINELOG/group_4.271.1164520171 07-JAN-25 02.23.40.951000000 PM +00:00 srv2 1 Current log# 4 seq# 278 mem# 1: +FRA/RACDB/ONLINELOG/group_4.260.1164520173 07-JAN-25 02.23.40.976000000 PM +00:00 srv2 1 Deleted Oracle managed file +FRA/RACDB/ARCHIVELOG/2024_11_02/thread_1_seq_240.500.1183975081 07-JAN-25 02.23.40.977000000 PM +00:00 srv2 1 ARC2 (PID:17940): Archived Log entry 1023 added for B-1164519547.T-2.S-277 LOS:0x0000000000a568cc NXS:0x0000000000a56d9e NAB:372 ID 0x46c5be03 LAD:1 07-JAN-25 02.23.41.048000000 PM +00:00 srv1 1 Thread 1 advanced to log sequence 322 (LGWR switch), current SCN: 10841505 07-JAN-25 02.23.41.048000000 PM +00:00 srv1 1 Current log# 2 seq# 322 mem# 1: +FRA/RACDB/ONLINELOG/group_2.258.1164519549 07-JAN-25 02.23.41.048000000 PM +00:00 srv1 1 Current log# 2 seq# 322 mem# 0: +DATA/RACDB/ONLINELOG/group_2.262.1164519549 07-JAN-25 02.23.41.117000000 PM +00:00 srv2 1 Deleted Oracle managed file +FRA/RACDB/ARCHIVELOG/2024_11_02/thread_2_seq_221.499.1183975181 07-JAN-25 02.23.41.138000000 PM +00:00 srv1 1 Deleted Oracle managed file +FRA/RACDB/ARCHIVELOG/2024_11_02/thread_2_seq_222.498.1183975205 07-JAN-25 02.23.41.139000000 PM +00:00 srv1 1 NET (PID:15641): Archived Log entry 1025 added for B-1164519547.T-1.S-321 LOS:0x0000000000a568c9 NXS:0x0000000000a56da1 NAB:369 ID 0x46c5be03 LAD:1 07-JAN-25 02.23.41.209000000 PM +00:00 srv1 1 Deleted Oracle managed file +FRA/RACDB/ARCHIVELOG/2024_11_02/thread_1_seq_241.497.1183975551 07-JAN-25 02.27.41.986000000 PM +00:00 srv1 1 alter pluggable database pdb1 close 07-JAN-25 02.27.41.997000000 PM +00:00 srv1 3 Pluggable database PDB1 closing 07-JAN-25 02.27.42.004000000 PM +00:00 srv1 3 Increasing priority of 2 RS 07-JAN-25 02.27.42.011000000 PM +00:00 srv1 3 JIT: pid 19878 requesting stop 07-JAN-25 02.27.42.044000000 PM +00:00 srv1 3 Stopped service mypdb 07-JAN-25 02.27.42.208000000 PM +00:00 srv1 3 Closing sequence subsystem (3300983922). 07-JAN-25 02.27.42.332000000 PM +00:00 srv1 3 Buffer Cache flush started: 3 07-JAN-25 02.27.42.345000000 PM +00:00 srv1 3 Buffer Cache flush finished: 3 07-JAN-25 02.27.42.347000000 PM +00:00 srv2 1 Increasing priority of 2 RS 07-JAN-25 02.27.42.350000000 PM +00:00 srv1 3 queued detach DA request 0x934eafc8 for pdb 3, ospid 19878 07-JAN-25 02.27.42.351000000 PM +00:00 srv1 1 Domain Action Reconfiguration started (domid 3, new da inc 3, cluster inc 8) 07-JAN-25 02.27.42.351000000 PM +00:00 srv1 1 Instance 1 is detaching from domain 3 (lazy abort? 0, recovery member? 0) 07-JAN-25 02.27.42.352000000 PM +00:00 srv1 1 Global Resource Directory partially frozen for domain action 07-JAN-25 02.27.42.352000000 PM +00:00 srv1 1 * domain detach - domain 3 valid ? 1 07-JAN-25 02.27.42.430000000 PM +00:00 srv2 1 Domain Action Reconfiguration started (domid 3, new da inc 3, cluster inc 8) 07-JAN-25 02.27.42.430000000 PM +00:00 srv2 1 Instance 1 is detaching from domain 3 (lazy abort? 0, recovery member? 0) 07-JAN-25 02.27.42.430000000 PM +00:00 srv2 1 Global Resource Directory partially frozen for domain action 07-JAN-25 02.27.42.430000000 PM +00:00 srv2 1 * domain detach - domain 3 valid ? 1 07-JAN-25 02.27.42.432000000 PM +00:00 srv2 1 Non-local Process blocks cleaned out 07-JAN-25 02.27.42.434000000 PM +00:00 srv2 1 Set master node info 07-JAN-25 02.27.42.435000000 PM +00:00 srv2 1 Dwn-cvts replayed, VALBLKs dubious 07-JAN-25 02.27.42.435000000 PM +00:00 srv2 1 All grantable enqueues granted 07-JAN-25 02.27.42.437000000 PM +00:00 srv1 1 Non-local Process blocks cleaned out 07-JAN-25 02.27.42.438000000 PM +00:00 srv1 1 Set master node info 07-JAN-25 02.27.42.439000000 PM +00:00 srv1 1 Dwn-cvts replayed, VALBLKs dubious 07-JAN-25 02.27.42.440000000 PM +00:00 srv1 1 All grantable enqueues granted 07-JAN-25 02.27.42.440000000 PM +00:00 srv2 1 Domain Action Reconfiguration complete (total time 0.0 secs) 07-JAN-25 02.27.42.440000000 PM +00:00 srv2 1 Decreasing priority of 2 RS 07-JAN-25 02.27.42.444000000 PM +00:00 srv1 1 freeing the fusion rht of pdb 3 07-JAN-25 02.27.42.445000000 PM +00:00 srv1 1 freeing the pdb enqueue rht 07-JAN-25 02.27.42.445000000 PM +00:00 srv1 1 Decreasing priority of 2 RS 07-JAN-25 02.27.42.445000000 PM +00:00 srv1 1 Domain Action Reconfiguration complete (total time 0.1 secs) 07-JAN-25 02.27.42.542000000 PM +00:00 srv1 1 Pluggable database PDB1 closed 07-JAN-25 02.27.42.571000000 PM +00:00 srv1 1 Completed: alter pluggable database pdb1 close 07-JAN-25 02.28.10.713000000 PM +00:00 srv1 1 alter pluggable database pdb1 open 07-JAN-25 02.28.10.715000000 PM +00:00 srv1 3 Pluggable database PDB1 opening in read write 07-JAN-25 02.28.10.892000000 PM +00:00 srv1 3 Increasing priority of 2 RS 07-JAN-25 02.28.10.892000000 PM +00:00 srv1 3 Autotune of undo retention is turned on. 07-JAN-25 02.28.10.892000000 PM +00:00 srv1 3 SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18 07-JAN-25 02.28.10.929000000 PM +00:00 srv1 3 queued attach DA request 0x934eaf60 for pdb 3, ospid 19878 07-JAN-25 02.28.10.929000000 PM +00:00 srv2 1 Increasing priority of 2 RS 07-JAN-25 02.28.10.933000000 PM +00:00 srv1 1 Domain Action Reconfiguration started (domid 3, new da inc 4, cluster inc 8) 07-JAN-25 02.28.10.933000000 PM +00:00 srv1 1 Global Resource Directory partially frozen for domain action 07-JAN-25 02.28.10.933000000 PM +00:00 srv1 1 Instance 1 is attaching to domain 3 07-JAN-25 02.28.10.940000000 PM +00:00 srv2 1 Domain Action Reconfiguration started (domid 3, new da inc 4, cluster inc 8) 07-JAN-25 02.28.10.940000000 PM +00:00 srv2 1 Instance 1 is attaching to domain 3 07-JAN-25 02.28.10.941000000 PM +00:00 srv2 1 Global Resource Directory partially frozen for domain action 07-JAN-25 02.28.10.943000000 PM +00:00 srv2 1 Non-local Process blocks cleaned out 07-JAN-25 02.28.10.946000000 PM +00:00 srv2 1 Set master node info 07-JAN-25 02.28.10.947000000 PM +00:00 srv1 1 Non-local Process blocks cleaned out 07-JAN-25 02.28.10.947000000 PM +00:00 srv2 1 Dwn-cvts replayed, VALBLKs dubious 07-JAN-25 02.28.10.948000000 PM +00:00 srv1 1 Set master node info 07-JAN-25 02.28.10.949000000 PM +00:00 srv2 1 All grantable enqueues granted 07-JAN-25 02.28.10.952000000 PM +00:00 srv1 1 Dwn-cvts replayed, VALBLKs dubious 07-JAN-25 02.28.10.953000000 PM +00:00 srv1 1 All grantable enqueues granted 07-JAN-25 02.28.11.034000000 PM +00:00 srv2 1 Domain Action Reconfiguration complete (total time 0.1 secs) 07-JAN-25 02.28.11.034000000 PM +00:00 srv2 1 Decreasing priority of 2 RS 07-JAN-25 02.28.11.038000000 PM +00:00 srv1 1 Domain Action Reconfiguration complete (total time 0.1 secs) 07-JAN-25 02.28.11.038000000 PM +00:00 srv1 1 Decreasing priority of 2 RS 07-JAN-25 02.28.11.135000000 PM +00:00 srv1 3 Endian type of dictionary set to little 07-JAN-25 02.28.11.213000000 PM +00:00 srv1 3 Undo initialization recovery: err:0 start: 3329988 end: 3329989 diff: 1 ms (0.0 seconds) 07-JAN-25 02.28.11.352000000 PM +00:00 srv1 3 [19878] Successfully onlined Undo Tablespace 2. 07-JAN-25 02.28.11.352000000 PM +00:00 srv1 3 Undo initialization online undo segments: err:0 start: 3329989 end: 3330129 diff: 140 ms (0.1 seconds) 07-JAN-25 02.28.11.357000000 PM +00:00 srv1 3 Undo initialization finished serial:0 start:3329988 end:3330134 diff:146 ms (0.1 seconds) 07-JAN-25 02.28.11.361000000 PM +00:00 srv1 3 Database Characterset for PDB1 is AL32UTF8 07-JAN-25 02.28.11.627000000 PM +00:00 srv1 3 SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog) 07-JAN-25 02.28.11.891000000 PM +00:00 srv1 3 Started service mypdb/mypdb/mypdb 07-JAN-25 02.28.11.925000000 PM +00:00 srv1 3 Opening pdb with no Resource Manager plan active 07-JAN-25 02.28.12.097000000 PM +00:00 srv1 3 joxcsys_required_dirobj_exists: directory object exists with required path /u02/app/oracle/product/19.0.0/dbhome_1/javavm/admin/, pi d 19878 cid 3 07-JAN-25 02.28.12.118000000 PM +00:00 srv1 1 Pluggable database PDB1 opened read write 07-JAN-25 02.28.12.122000000 PM +00:00 srv1 1 Completed: alter pluggable database pdb1 open 07-JAN-25 02.33.26.501000000 PM +00:00 srv1 1 Control autobackup written to DISK device 07-JAN-25 02.33.26.502000000 PM +00:00 srv1 1 handle '+FRA/RACDB/AUTOBACKUP/2025_01_07/s_1189780406.497.1189780407' 93 rows selected. SQL>At 07-JAN-25 02.27.41.986000000 PM +00:00 : I issued a command to close PDB1 (CON_ID=3) on host srv1
You can also see the "Stopped service mypdb" message on srv1 at 07-JAN-25 02.27.42
Host srv2 started writing it's messages at 07-JAN-25 02.27.42.347000000 PM +00:00 from which you can see Domain Action Reconfiguration (07-JAN-25 02.27.42.351000000 PM +00:00), Global Resource Directory partially frozen (07-JAN-25 02.27.42.430000000 PM +00:00)/
Finally, srv1 logged "Completed: alter pluggable database pdb1 close" at 07-JAN-25 02.27.42.571000000 PM +00:00 (under CON_ID=1).
When I issued the "alter pluggable database pdb1 open" on srv1 at 07-JAN-25 02.28.10.713000000 PM +00:00, Domain Actions and Global Resource Directory actions were executed again on srv2
Finally, srv1 logged "Completed: alter pluggable database pdb1 open" at 07-JAN-25 02.28.12.122000000 PM +00:00
So, it is obvious that closing and opening a PDB in RAC also results in a number of steps to maintain consistency (Resources etc) across the instances.
No comments:
Post a Comment