Oracle DBA Daily Checklist
发布时间：2023-02-17 09:22:34 所属栏目：Oracle 来源：互联网
导读：There are several routine checklist and tasks to do in Oracle database by DBA ( Database Administrator ). This checklist and tasks are as follows. All scripts are valid for Single and RAC Database and Exadata. If you dont use RAC,then you
|
09-03-2020 18:39:21 DB INCR COMPLETED 5.9 1281.08734 1202.46387 SBT_TAPE 08-03-2020 18:00:30 DB INCR COMPLETED 5.7 1279.00639 1200.3584 SBT_TAPE 08-03-2020 17:27:04 ARCHIVELOG COMPLETED 0 .60765028 .608642578 SBT_TAPE 07-03-2020 18:00:23 DB INCR COMPLETED 7.3 1279.00921 1200.35254 SBT_TAPE 07-03-2020 15:22:58 ARCHIVELOG COMPLETED 0 .625060558 .626220703 SBT_TAPE 73 rows selected. sql> 8- Check any session blocking the other session ( blocking session and Lock control ) Use the following script to check Blocking session state. select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1,gv$session s1,gv$lock l2,gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ; 9- Check the DBMS jobs running or not and check the status of the Jobs Use the following script to check Scheduler jobs state. -- Failed Scheduled Jobs SELECT owner,job_name,LOG_DATE,ERROR#,( EXTRACT (SECOND FROM run_duration) /60 + EXTRACT (MINUTE FROM run_duration) + EXTRACT (HOUR FROM run_duration) * 60 + EXTRACT (DAY FROM run_duration) * 60 * 24) MINUTES,ADDITIONAL_INFO FROM dba_scheduler_job_run_details WHERE LOG_DATE > SYSDATE - 1 AND status != 'SUCCEEDED' ORDER BY 1 ASC,4 DESC; -- Running and Succeeded Scheduled Jobs SELECT OWNER,JOB_NAME,LAST_START_DATE,STATE FROM DBA_SCHEDULER_JOBS WHERE LAST_START_DATE > SYSDATE - 1 AND STATE <> 'SCHEDULED'; 10- Check the Dataguard is synchronized or not. Use the following script to check Dataguard status select process,client_process,thread#,sequence#,status from v$managed_standby where process like '%MRP%'; select name,value from v$dataguard_stats; set lines 1000 select name,value from v$dataguard_stats; NAME VALUE -------------------------------- ------------------------- transport lag +00 00:03:52 apply lag +00 00:03:54 apply finish time +00 00:00:00.001 estimated startup time 16 second 11- Check the Performance Page of Enterprise Manager or Enterprise Manager Cloud Control Open Performance Page of Enterprise manager Cloud Control as follows to check Performance. 12- Check the TOP session and TOP activity of database. Open TOP Activity Page of Enterprise manager Cloud Control as follows to check TOP Activity. 13- Detect lock objects Use the following script to check Lock objects and tables. SELECT B.Owner,B.Object_Name,A.Oracle_Username,A.OS_User_Name FROM gv$Locked_Object A,All_Objects B WHERE A.Object_ID = B.Object_ID; 14- Check the sql query consuming lot of resources ( cpu and disk Resources ) Use the following script to check TOP cpu and disk sql Statements. select * from ( select ss.sql_text,a.sql_ID,sum(cpu_TIME_DELTA),sum(disK_READS_DELTA),count(*) from DBA_HIST_sqlSTAT a,dba_hist_snapshot s,v$sql ss where s.snap_id = a.snap_id and a.sql_id=ss.sql_id and s.begin_interval_time > sysdate -1 group by ss.sql_text,a.sql_ID order by sum(cpu_TIME_DELTA) desc) where rownum<20; 15- Check the usage of physical RAM and SGA – Paging or Swapping exist or not. [oracle@msddbadm01 ~]$ free -m [oracle@msddbadm01 ~]$ free -m total used free shared buffers cached Mem: 772257 739605 32652 6174 491 397834 -/+ buffers/cache: 341279 430978 Swap: 24575 8035 16540 sql> set linesize 150 sql> set pagesize 150 sql> select * from v$sgainfo; NAME BYTES RES CON_ID -------------------------------- ---------- --- ---------- Fixed SGA Size 29906520 No 0 Redo Buffers 207720448 No 0 Buffer Cache Size 1.1543E+11 Yes 0 In-Memory Area Size 2.1475E+10 No 0 Shared Pool Size 4.5634E+10 Yes 0 Large Pool Size 3758096384 Yes 0 Java Pool Size 536870912 Yes 0 （编辑：甘南站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
