###acl merlyn:read,write All:read <> = 20190729 = == Create gzdt1v2bim Schema&Tablespace == === Create Tablespace === {{{ CREATE TABLESPACE gzdt1v2bim DATAFILE '+DATAFILE' SIZE 1G AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE LOGGING ONLINE SEGMENT SPACE MANAGEMENT AUTO; CREATE TEMPORARY TABLESPACE gzdt1v2bim_temp01 TEMPFILE '+DATAFILE' SIZE 10M AUTOEXTEND ON; }}} === Create USER gzdt1v2bim === {{{ CREATE USER gzdt1v2bim IDENTIFIED BY gzdt1v2bim DEFAULT TABLESPACE gzdt1v2bim TEMPORARY TABLESPACE gzdt1v2bim_temp01 QUOTA UNLIMITED ON gzdt1v2bim; }}} === Assign SYSTEM privileges to gzdt1v2bim in Oracle === {{{ GRANT resource TO gzdt1v2bim; GRANT connect TO gzdt1v2bim; GRANT create operator TO gzdt1v2bim; GRANT create session TO gzdt1v2bim; GRANT create table TO gzdt1v2bim; GRANT create view TO gzdt1v2bim; GRANT create any trigger TO gzdt1v2bim; GRANT create any procedure TO gzdt1v2bim; GRANT create sequence TO gzdt1v2bim; GRANT create synonym TO gzdt1v2bim; GRANT UNLIMITED TABLESPACE TO gzdt1v2bim; }}} {{{ GRANT CREATE SESSION, CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO hr; GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO hr; }}} {{{ SQL> select * from user_sys_privs; USERNAME PRIVILEGE ADMIN_ ------------------------------------------------------------ -------------------------------------------------------------------------------- ------ GZDT1V2BIM CREATE ANY PROCEDURE NO GZDT1V2BIM CREATE TABLE NO GZDT1V2BIM CREATE OPERATOR NO GZDT1V2BIM CREATE ANY TRIGGER NO GZDT1V2BIM CREATE VIEW NO GZDT1V2BIM CREATE SYNONYM NO GZDT1V2BIM UNLIMITED TABLESPACE NO GZDT1V2BIM CREATE SESSION NO GZDT1V2BIM CREATE SEQUENCE NO 9 rows selected. SQL> select * from user_role_privs; USERNAME GRANTED_ROLE ADMIN_ DEFAUL OS_GRA ------------------------------------------------------------ ------------------------------------------------------------ ------ ------ ------ GZDT1V2BIM CONNECT NO YES NO GZDT1V2BIM RESOURCE NO YES NO SQL> select name from v$datafile where name like '%gzdt1v2bim%'; NAME -------------------------------------------------------------------------------- +DATAFILE/dbn/datafile/gzdt1v2bim.268.1014932427 SQL> select name from v$tempfile where name like '%gzdt1v2bim%'; NAME -------------------------------------------------------------------------------- +DATAFILE/dbn/tempfile/gzdt1v2bim_temp01.269.1014932451 }}} == health_check.sh == {{{ # create file hostname_info, Collect system info touch /tmp/`hostname`_info_`date +%F`.txt uptime echo "========================================================================================" echo "* Network info" echo "========================================================================================" netstat -ni ip address show #> /tmp/`hostname`_info_`date +%F`.txt echo "========================================================================================" echo "* Machinfo info" echo "========================================================================================" cat /etc/hosts cat /etc/*-release #>> /tmp/`hostname`_info_`date +%F`.txt echo "========================================================================================" echo "* vmstat info" echo "========================================================================================" vmstat 1 10 echo "========================================================================================" echo "* iostat info" echo "========================================================================================" iostat 1 5 echo "========================================================================================" echo "* Listener info" echo "========================================================================================" lsnrctl status #>> /tmp/`hostname`_info_`date +%F`.txt tnsping dbn #>> /tmp/`hostname`_info_`date +%F`.txt # Oracle health check sqlplus / as sysdba @/home/oracle/scripts/check_oracle_v3.sql #>> /tmp/`hostname`_info_`date +%F`.txt echo "========================================================================================" echo "* spfile/pfile backup" echo "========================================================================================" sqlplus /nolog <= sysdate -7; prompt prompt List of Invalid objects of database: prompt -----------------------------------------------------------------------** set pagesize 50; Select owner "USERNAME", object_type, count(*) INVALID from dba_objects where status='INVALID' group by owner, object_type; set pagesize 50; SELECT dt.owner, dt.table_name "Table Change > 10%", ROUND ( (DELETES + UPDATES + INSERTS) / num_rows * 100) PERCENTAGE FROM dba_tables dt, all_tab_modifications atm WHERE dt.owner = atm.table_owner AND dt.table_name = atm.table_name AND num_rows > 0 AND ROUND ( (DELETES + UPDATES + INSERTS) / num_rows * 100) >= 10 ORDER BY 3 desc; prompt prompt Database Chained Rows Info: prompt -----------------------------------------------------------------------** col table_name format a25; select owner, table_name, pct_free, pct_used, avg_row_len, num_rows, chain_cnt, trunc(chain_cnt/num_rows*100, 2) as perc from dba_tables where owner not in ('SYS','SYSTEM') and table_name not in (select table_name from dba_tab_columns where data_type in ('RAW','LONG RAW') ) and chain_cnt > 0 order by chain_cnt desc; prompt prompt prompt**==================================================================================================** prompt** **RMAN Configuration and Backup** prompt**==================================================================================================** col "RMAN CONFIGURE PARAMETERS" format a100; select 'CONFIGURE '||name ||' '|| value "RMAN CONFIGURE PARAMETERS" from v$rman_configuration order by conf#; set line 300; col "DEVIC" format a6; col "L" format 9; col "FIN:SS" format 9999; SELECT DECODE(backup_type, 'L', 'Archived Logs', 'D', 'Datafile Full', 'I', 'Incremental') backup_type, bp.tag "RMAN_BACKUP_TAG", device_type "DEVIC", DECODE( bs.controlfile_included, 'NO', null, bs.controlfile_included) controlfile, (sp.spfile_included) spfile, sum(bs.incremental_level) "L", TO_CHAR(bs.start_time, 'dd/mm/yyyy HH24:MI:SS') start_time , TO_CHAR(bs.completion_time, 'dd/mm/yyyy HH24:MI:SS') completion_time, sum(bs.elapsed_seconds) "FIN:SS" FROM v$backup_set bs, (select distinct set_stamp, set_count, tag , device_type from v$backup_piece where status in ('A', 'X')) bp, (select distinct set_stamp , set_count , 'YES' spfile_included from v$backup_spfile) sp WHERE bs.start_time > sysdate - 1 AND bs.set_stamp = bp.set_stamp AND bs.set_count = bp.set_count AND bs.set_stamp = sp.set_stamp (+) AND bs.set_count = sp.set_count (+) group by backup_type, bp.tag, device_type, bs.controlfile_included, pieces, sp.spfile_included,start_time, bs.completion_time ORDER BY bs.start_time desc; set line 300; col "DBF_BACKUP_MB" format 999999.99; col "ARC_BACKUP_MB" format 9999.99; select trunc(completion_time) "BAK_DATE", sum(blocks*block_size)/1024/1024 "DBF_BACKUP_MB", (SELECT sum(blocks*block_size)/1024/1024 from v$backup_redolog WHERE first_time > sysdate-1) "ARC_BACKUP_MB" from v$backup_datafile WHERE completion_time > sysdate - 1 group by trunc(completion_time) order by 1 DESC; col "Datafiles backed up within 24h" format a40; col "Control backed up" format 999; col "SPFiles backed up" format 999; SELECT dbfiles||' out of '||numfiles||' datafiles backed up' "Datafiles backed up within 24h", cfiles "CFiles", spfiles "SPFiles" FROM (select count(*) numfiles from v$datafile), (select count(*) dbfiles from v$backup_datafile a, v$datafile b where a.file# = b.file# and a.completion_time > sysdate - 1), (select count(*) cfiles from v$backup_datafile where file# = 0 and completion_time > sysdate - 1), (select count(*) spfiles from v$backup_spfile where completion_time > sysdate - 1); prompt prompt prompt**===================================================================================================** prompt** "Workload and I/O Statistics** prompt**===================================================================================================** prompt prompt *** TOP SYSTEM Timed Events (Waits): prompt*-----------------------------------------------------------------------** COLUMN event FORMAT A40 HEADING "Wait Event" TRUNC COLUMN time_waited FORMAT 9999999999999 HEADING "Time|Waited" COLUMN wait_pct FORMAT 99.90 HEADING "Wait|(%)" SELECT w.event, w.time_waited, round(w.time_waited/tw.twt*100,2) wait_pct FROM gv$system_event w, (select inst_id, sum(time_waited) twt from gv$system_event where time_waited>0 AND event NOT IN ('Null event', 'client message', 'rdbms ipc reply', 'smon timer', 'rdbms ipc message', 'PX Idle Wait', 'PL/SQL lock timer', 'file open', 'pmon timer', 'WMON goes to sleep', 'virtual circuit status', 'dispatcher timer', 'SQL*Net message from client', 'parallel query dequeue wait', 'pipe get') group by inst_id ) tw WHERE w.inst_id = tw.inst_id and w.time_waited>0 and round(w.time_waited/tw.twt*100,2) > 1 and w.event NOT IN ('Null event', 'client message', 'rdbms ipc reply', 'smon timer', 'rdbms ipc message', 'PX Idle Wait', 'PL/SQL lock timer', 'file open', 'pmon timer', 'WMON goes to sleep', 'virtual circuit status', 'dispatcher timer', 'SQL*Net message from client', 'parallel query dequeue wait', 'pipe get') ORDER by 1; prompt prompt *** Most buffer gets (TOP 5): prompt*-----------------------------------------------------------------------** col object_type format a10; col object_name format a30; col statistic_name format a15; col value format 99999999999999; SELECT * from ( SELECT object_type, object_name, statistic_name,VALUE FROM v$segment_statistics WHERE statistic_name LIKE '%logi%' AND VALUE > 50 ORDER BY 4 DESC ) where rownum < 6; prompt prompt *** Most I/O operation (TOP 5): prompt*-----------------------------------------------------------------------** col object_type format a15 heading "Object Type" col object_name format a27 heading "Object Name" col statistic_name format a22 heading "Statistic Name" col value format 99999999999 heading "Value" SELECT * from ( SELECT object_type, object_name, statistic_name, VALUE FROM v$segment_statistics WHERE statistic_name LIKE '%phys%' AND VALUE > 50 ORDER BY 4 DESC ) where rownum < 6; prompt prompt *** Most I/O operation for particualr Query: prompt*-----------------------------------------------------------------------** col sql_text format a60; col reads_per_exe format 99999999 heading 'reads|per_exe'; col "exe" format 99999; col "sorts" format 99999; col buffer_gets heading 'buffer|gets'; col disk_reads heading 'disk|reads'; SELECT * FROM (SELECT Substr(a.sql_text,1,50) sql_text, Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_exe, a.buffer_gets, a.disk_reads, a.executions "exe", a.sorts "sorts", a.address "address" FROM v$sqlarea a ORDER BY 2 DESC) WHERE rownum <= 5; prompt prompt Monitoring Full Table Scan of Database: prompt*-----------------------------------------------------------------------** set line 300; col value format 99999999999; col "Full Table Scan" format a30; SELECT name "Full Table Scan", value FROM v$sysstat WHERE name LIKE '%table scans %' ORDER BY name; prompt Prompt* Review the query causing high amount of buffer_gets and create additional index to avoid full table scan. prompt prompt Monitor TOP CPU Usage and Logical I/O Process: prompt*-----------------------------------------------------------------------** col resource_name heading "Resource|Name"; col resource_name format a30; col current_utilization heading "current|utiliz"; col max_utilization heading "Max|utiliz"; col initial_allocation heading "Initial|Alloc"; col limit_value heading "Limit|Value"; col limit_value format a30; col MACHINE format a15; col OSUSER format a15; col USERNAME format a15; select resource_name, current_utilization, max_utilization, initial_allocation, limit_value from v$resource_limit where resource_name in ('processes','sessions', 'transactions', 'max_rollback_segments'); col name format a30; select * from (select a.sid, c.username, c.osuser, c.machine, logon_time, b.name, a.value from v$sesstat a, v$statname b, v$session c where a.STATISTIC# = b.STATISTIC# and a.sid = c.sid and b.name like '%CPU used by this session%' order by a.value desc) where rownum < 5; select 'top logical i/o process', sid, username, total_user_io amt_used, round(100 * total_user_io/total_io,2) pct_used from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_io from v$statname c, v$sesstat a, v$session b, v$bgprocess p where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid and c.name in ('consistent gets', 'db block gets') and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') group by b.sid, nvl(b.username, p.name) order by 3 desc), (select sum(value) total_io from v$statname c, v$sesstat a, v$session b, v$bgprocess p where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid and c.name in ('consistent gets', 'db block gets')) where rownum < 2 union all select 'top memory process', sid, username, total_user_mem, round(100 * total_user_mem/total_mem,2) from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_mem from v$statname c, v$sesstat a, v$session b, v$bgprocess p where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid and c.name in ('session pga memory', 'session uga memory') and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') group by b.sid, nvl(b.username, p.name) order by 3 desc), (select sum(value) total_mem from v$statname c, v$sesstat a where a.statistic# = c.statistic# and c.name in ('session pga memory', 'session uga memory')) where rownum < 2 union all select 'top cpu process', sid, username, total_user_cpu, round(100 * total_user_cpu/greatest(total_cpu,1),2) from (select b.sid sid, nvl(b.username, p.name) username, sum(value) total_user_cpu from v$statname c, v$sesstat a, v$session b, v$bgprocess p where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid and c.name = 'CPU used by this session' and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP') group by b.sid, nvl(b.username, p.name) order by 3 desc), (select sum(value) total_cpu from v$statname c, v$sesstat a, v$session b, v$bgprocess p where a.statistic# = c.statistic# and p.paddr (+) = b.paddr and b.sid = a.sid and c.name = 'CPU used by this session') where rownum < 2; prompt prompt Monitoring Current Running Long Job in Database: prompt*-----------------------------------------------------------------------** set line 300; col opname format a30; SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK order by 1; prompt prompt Monitoring Object locking: prompt*-----------------------------------------------------------------------** set line 300; col username format a15; col lock_type format a10; col osuser format a15; col owner format a12; col object_name format a30; col MACHINE format a15; SELECT s.sid, s. serial#, s.username, l.lock_type, s.osuser, s.machine, o.owner, o.object_name, ROUND(w.seconds_in_wait/60, 2) "Wait" FROM v$session s, dba_locks l, dba_objects o, v$session_wait w WHERE s.sid = l.session_id AND l.lock_type IN ('DML','DDL')AND l.lock_id1 = o.object_id AND l.session_id = w.sid ORDER BY s.sid; prompt prompt Monitor DB Corruption or Need of Recovery: prompt*-----------------------------------------------------------------------** set line 300; SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS, r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#; set linesize 300 col name format a45 heading "Datafile Name"; col "Read Time(ms)" heading 'Read|Time(ms)'; col "Write Time(ms)" heading 'write|Time(ms)'; col "Avg_Time" heading 'Avg|Time(ms)'; select name,PHYRDS,PHYWRTS,READTIM "Read Time(ms)",WRITETIM "Write Time(ms)",AVGIOTIM "Avg_Time" from v$filestat, v$datafile where v$filestat.file#=v$datafile.file#; set feedback on prompt rem ----------------------------------------------------------------------- rem Filename: DB_Health_Rep.sql rem Purpose: Database Statistics and Health Report rem ----------------------------------------------------------------------- prompt Recommendations: prompt ==================================================================================================== prompt* SQL Cache Hit rate ratio should be above 90%, if not then increase the Shared Pool Size. prompt* Dict Cache Hit rate ratio should be above 85%, if not then increase the Shared Pool Size. prompt* Buffer Cache Hit rate ratio should be above 90%, if not then increase the DB Block Buffer value. prompt* Redo Log space requests should be less than 0.5% of redo entries, if not then increase log buffer. prompt* Redo Log space wait time should be near to 0. prompt set serveroutput ON DECLARE libcac number(10,2); rowcac number(10,2); bufcac number(10,2); redlog number(10,2); redoent number; redowaittime number; BEGIN select value into redlog from v$sysstat where name = 'redo log space requests'; select value into redoent from v$sysstat where name = 'redo entries'; select value into redowaittime from v$sysstat where name = 'redo log space wait time'; select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache; select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache; select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph where cur.statistic# = ncu.statistic# and ncu.name = 'db block gets' and con.statistic# = nco.statistic# and nco.name = 'consistent gets' and phys.statistic# = nph.statistic# and nph.name = 'physical reads'; dbms_output.put_line('CACHE HIT RATE'); dbms_output.put_line('********************'); dbms_output.put_line('SQL Cache Hit rate = '||libcac); dbms_output.put_line('Dict Cache Hit rate = '||rowcac); dbms_output.put_line('Buffer Cache Hit rate = '||bufcac); dbms_output.put_line('Redo Log space requests = '||redlog); dbms_output.put_line('Redo Entries = '||redoent); dbms_output.put_line('Redo log space wait time = '||redowaittime); if libcac < 90 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.'); END IF; if rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.'); END IF; if bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.'); END IF; if redlog > 1000000 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!'); END IF; END; / exit }}} * sh health_check.sh | tee /tmp/`hostname`_info_`date +%F`.txt == oracle backup == SQL> CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/mnt/nfs/oracle-backup';