Contents
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 <<EOF conn / as sysdba create pfile='/tmp/dbn.pfile' from spfile; exit EOF echo "========================================================================================" echo "* Package it" echo "========================================================================================" find $ORACLE_HOME/network/admin -name "*t*n*e?.ora" | xargs tar -cvf /tmp/`hostname`_Oracle_config_`date +%F`.tar find $ORACLE_HOME/dbs/ -name "init$ORACLE_SID.ora" | xargs tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar find /u01/app/oracle/ -iname "aler*`echo $ORACLE_SID`.log" -type f | xargs tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar /tmp/`hostname`_info_`date +%F`.txt tar -rvf /tmp/`hostname`_Oracle_config_`date +%F`.tar /tmp/dbn.pfile #scp /tmp/`hostname`_Oracle_config_`date +%F`.tar merlyn@172.16.34.252:~
check_oracle_v3.sql
prompt**==================================================================================================**
prompt** **Database Object Information**
prompt**==================================================================================================**
prompt
prompt List of Largest Object in Database:
prompt -----------------------------------------------------------------------**
set line 300;
col SEGMENT_NAME format a30;
col SEGMENT_TYPE format a10;
col BYTES format a15;
col TABLESPACE_NAME FORMAT A25;
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE TYPE, BYTES/1024/1024 SIZE_MB,
TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;
prompt
prompt Object Modified in last 7 days:
prompt -----------------------------------------------------------------------**
set line 300;
col owner format a15;
col object_name format a25;
col object_type format a15;
col last_modified format a20;
col created format a20;
col status format a10;
select owner, object_name, object_type, to_char(LAST_DDL_TIME,'MM/DD/YYYY HH24:MI:SS') last_modified,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created, status
from dba_objects
where (SYSDATE - LAST_DDL_TIME) < 7 and owner IN( 'HRMS', 'ORAFIN', 'HRTRAIN')
order by last_ddl_time DESC;
prompt
set pagesize 0;
SELECT 'Object Created in this Week: '|| count(1) from user_objects
where created >= 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;
/
exitsh health_check.sh | tee /tmp/hostname_info_date +%F.txt
oracle backup
SQL> CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/mnt/nfs/oracle-backup';