Skip to main content

Posts

Showing posts from April, 2014

11gR2 RAC recovery Practices

            I was working on a recovery guide for a RAC cluster I built so I thought I would share the recovery of the DATA diskgroup. The set-up is a 2 node RAC cluster running 11.2.0.3 standard edition ASM with 2 disk groups DATA and FLASH. The OCR and voting disk are in the DATA diskgroup along with the database datafiles. The test is to illustrate the failure in the +DATA diskgroup which will result in loss of the OCR, Voting disk and database files Pre-recovery Steps 1. OCR & Voting Disk backup As root root@dbrac01mgt)# . oraenv ORACLE_SID = [+ASM1] ? +ASM1 The Oracle base remains unchanged with value /u01/app/oracle root@dbrac01mgt)# ocrconfig -showbackup PROT-24: Auto backups for the Oracle Cluster Registry are not available PROT-25: Manual backups for the Oracle Cluster Registry are not available root@dbrac01mgt)# ocrconfig -manualbackup dbrac01mgt 2013/12/09 13:35:34 /u01/app/11.2.3/grid/cdata/...

Resolving Your Wait Events

The following are 10 of the most common causes for wait events, along with explanations and potential solutions: 1. DB File Scattered Read . This generally indicates waits related to full table scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing or suppressed indexes. Although it may be more efficient in your situation to perform a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list. 2. DB File Sequential Read . This event generally indicates a single block read (an index read, for example). A large number of waits here could indicate poor joining orders of tables, or unselective indexing. It is norma...

Performance sql

Top Recent Wait Events col EVENT format a60   select * from ( select active_session_history.event, sum(active_session_history.wait_time + active_session_history.time_waited) ttl_wait_time from v$active_session_history active_session_history where active_session_history.event is not null group by active_session_history.event order by 2 desc) where rownum < 6 / Top Wait Events Since Instance Startup col event format a60   select event, total_waits, time_waited from v$system_event e, v$event_name n where n.event_id = e.event_id and n.wait_class !='Idle' and n.wait_class = (select wait_class from v$session_wait_class   where wait_class !='Idle'   group by wait_class having sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class where wait_class !='Idle' group by (wait_class))) order by 3; List Of Users Currently Waiting col username format a12 col sid form...