Common fault handling for Oracle databases

First, locate the database failure reason.
The reason for positioning can be divided into three steps:
1, if there is an Oracle error number or alert log detailed error information, you can locate the reasons for the database failure;
2, if not, you can run AWR tools or Statspack tools to generate reports, according to the report to locate database failure reasons;
3, if there is no such tool, you can view the relevant database dictionary, to locate the database failure reasons.

Script 1: The following statement retrieves the database overhead and CPU latency with the v$sysstat view, thus determining whether your database has a resource wait event.
Select
Round (100*cpu_sec/available_time,2) "ORACLE CPU time as% avail."
, round (100* (db_sec-cpu_sec)/available_time,2) "Non-idle WAITS as% avail."
, case
Sign (AVAILABLE_TIME-DB_SEC)
When 1 then round (100* (available_time-db_sec)/Available_time, 2)
else 0
End "ORACLE IDLE as% avail."
From
(
Select
(sysdate-i.startup_time) * 86400 * C.cpus available_time
, t.db_sec
, t.cpu_sec
From V$instance I
, (select Value CPUs
From V$parameter
where name = ' Cpu_count ') c
, (select
SUM (case name
When ' DB time ' then round (value/100)
else 0
End) Db_sec
, SUM (case name
When ' DB time ' then 0
Else Round (value/100)
End) Cpu_sec
From V$sysstat
where name in ("DB Time", ' CPU used by this session ')) t
where I.instance_number = Userenv (' instance ')
);

This is some output:
ORACLE CPU time as% avail. Non-idle WAITS as% avail. ORACLE IDLE as% avail.
--------------------------- -------------------------- -----------------------
31.97 129.59
Script 2: The above query result (Non-idle WAITS) shows a large number of waiting events in the database, and the following statements can identify specific wait events.
Col wait_class format A15
Col Event format A35 trunc
Col "class as% of WHOLE" format 990.00 Head "class as|% of WHOLE"
Col "event as% of class" like "class as% of WHOLE" Head "event as|% of Class"
Col ' event as% of WHOLE ' like ' CLASS as% of WHOLE ' head ' event as|% of WHOLE '
Set PageSize 30
Break on Wait_class on "class as% of WHOLE"
--
Select
Wait_class,
Round (* time_class/total_waits, 2) "class as% of WHOLE",
Event
Round (+ * Time_waited/time_class, 2) "EVENT as% of class",
Round (Time_waited/total_waits, 2) "EVENT as% of WHOLE"
From
(SELECT
Wait_class
, event
, time_waited
, SUM (time_waited) over (partition by Wait_class) Time_class
, Rank () over (partition by Wait_class ORDER BY time_waited Desc) rank_within_class
, SUM (time_waited) over () total_waits
From V$system_event
where Wait_class <> ' Idle ')
where Rank_within_class <= 3
ORDER BY time_class Desc, Rank_within_class;

Third, to determine whether the online redo log size is appropriate.
Problem: If a log file switch (checkpoint incomplete) waits for an event, the frequency of online redo log exchange needs to be detected.
Script 1: This script can look at the number of online log exchanges per hour, generally, if the log exchange times are too frequent, you can adjust the size of the online log properly.
Select COUNT (*), To_char (First_time, ' YYYY:MM:DD:HH24 ')
From V$log_history
Group BY To_char (First_time, ' YYYY:MM:DD:HH24 ')
Order by 2;
Script 2: View related online redo log files.
Select a.group#, A.member, B.status, b.bytes/1024/1024 meg_bytes
From V$logfile A, V$log b
where a.group# = b.group#
Order BY a.group#;

To determine whether the undo table space size is appropriate.
Problem: When running a long SQL statement, the ORA-01555 "snapshot too old" error occurs, you need to check the undo table space size is appropriate.
Script:
Select
To_char (Begin_time, ' mm-dd-yyyy hh24:mi ') begin_time
, ssolderrcnt ora_01555_cnt
, nospaceerrcnt no_space_cnt
, Txncount Max_num_txns
, Maxquerylen Max_query_len
, Expiredblks blck_in_expired
From V$undostat
where Begin_time > Sysdate-1
Order BY Begin_time;
Note:
1. The ora_01555_cnt column indicates whether a ORA-01555 error has occurred, and if so, the following steps are judged:
*ensure that code does not contain COMMIT statements within cursor.
*tune The SQL statement throwing the error so this it runs faster.
*ensure that you have good statistics (so your SQL runs efficiently).
*increase the Undo_retention initialization parameter.
2, if the no_space_cnt column is not 0, you need to increase the undo table space size.
3, now undo table space Size and Oracle recommended size.
Select sum (bytes)/1024/1024 cur_mb_size,
Dbms_undo_adv.required_undo_size (900) req_mb_size
From Dba_data_files
where tablespace_name like ' undo% ';

V. Determine whether the temporary table space is appropriate.
Question: ora-01652:unable to extend temp segment by 128 in Tablespace temp
Select Tablespace_name, SUM (bytes_used)/1024/1024 mb_used
From V$temp_extent_pool
Group BY Tablespace_name;
Select name, bytes/1024/1024 mb_alloc from V$tempfile;
ALTER DATABASE tempfile '/ORA03/DWREP/TEMP03.DBF ' resize 12g; Or
Alter tablespace temp add tempfile '/ora04/dwrep/temp04.dbf ' size 2g;

Check the use of table space.
Select A.tablespace_name,
(f.bytes/a.bytes) * Pct_free,
f.bytes/1024/1024 Mb_free,
a.bytes/1024/1024 mb_allocated
From (select Nvl (sum (bytes), 0) bytes, x.tablespace_name
From Dba_free_space y, dba_tablespaces x
where X.tablespace_name = Y.tablespace_name (+)
and x.contents!= ' temporary '
and X.status!= ' READ only '
and x.tablespace_name not like ' undo% '
GROUP by X.tablespace_name) F,
(select sum (bytes) bytes, tablespace_name
From Dba_data_files
Group by Tablespace_name) a
where a.tablespace_name = F.tablespace_name
Order by 1;

REM *****************************************************
REM * File:freesp.sql
REM *****************************************************
SET PAGESIZE LINES 132 ECHO off VERIFY off feedb off spaces 1 Trimsp on
COMPUTE SUM of A_byt t_byt f_byt on
Break on the Tablespace_name on PF
COL tablespace_name for A17 TRU head ' tablespace| Name '
COL file_name for A40 TRU head ' Filename '
COL a_byt for 9,990.999 head ' allocated| GB '
COL t_byt for 9,990.999 head ' current| Used GB '
COL f_byt for 9,990.999 head ' current| Free GB '
COL Pct_free for 990.0 head ' File%|free '
COL PF for 990.0 head ' tbsp%|free '
COL seq Noprint
DEFINE b_div=1073741824
--
COL db_name new_value h_db_name noprint
COL db_date new_value h_db_date noprint
SELECT name Db_name, To_char (sysdate, ' Yyyy_mm_dd ') db_date from V$database;
--
SPO &&h_db_name._&&h_db_date. Lis
PROMPT Database: &&h_db_name, Date: &&h_db_date
--
SELECT 1 seq,
B.tablespace_name,
NVL (X.FS, 0)/Y.ap * PF,
B.file_name file_name,
B.bytes/&&b_div A_byt,
NVL ((B.bytes-sum (f.bytes))/&&b_div, b.bytes/&&b_div) T_byt,
NVL (SUM (f.bytes)/&&b_div, 0) F_byt,
NVL (SUM (f.bytes)/b.bytes * 0) Pct_free
From Dba_free_space F,
Dba_data_files B,
(SELECT y.tablespace_name, SUM (y.bytes) FS
From Dba_free_space y
GROUP by Y.tablespace_name) x,
(SELECT x.tablespace_name, SUM (X.bytes) AP
From Dba_data_files X
GROUP by X.tablespace_name) y
WHERE f.file_id (+) = b.file_id
and x.tablespace_name (+) = Y.tablespace_name
and y.tablespace_name = B.tablespace_name
and f.tablespace_name (+) = B.tablespace_name
GROUP by B.tablespace_name,
NVL (X.FS, 0)/Y.ap * 100,
B.file_name,
B.bytes
UNION
SELECT 2 seq,
Tablespace_name,
J.bf/k.bb PF,
B.name file_name,
B.bytes/&&b_div A_byt,
a.bytes_used/&&b_div T_byt,
A.bytes_free/&&b_div F_byt,
A.bytes_free/b.bytes * Pct_free
From V$temp_space_header A,
V$tempfile B,
(SELECT SUM (bytes_free) BF from V$temp_space_header) J,
(SELECT SUM (bytes) bb from V$tempfile) k
WHERE a.file_id = b.file#
Order by 1, 2, 4, 3;
--
COLUMN name FORMAT A60 Head ' control Files '
SELECT name from V$controlfile;
--
COL member FORMAT A50 head ' Redo log Files '
COL status FORMAT A9 head ' status '
COL archived FORMAT A10 Head ' archived '
--
SELECT
a.group#, A.member, B.status,
B.archived, SUM (b.bytes)/1024/1024 MB
From V$logfile A, V$log b
WHERE a.group# = b.group#
GROUP by a.group#, A.member, B.status, b.archived
Order by 1, 2;
--
Seven, check the index usage.
1, ALTER index F_DOWN_DOM_FK9 monitoring usage;
2, select * from V$object_usage;
3. Select ' Alter index ' | | index_name | | ' monitoring usage; ' from user_indexes;