Monday, November 17, 2014

Script: Display SGA Statistics in Oracle Database

Below script is useful for find database SGA statistics.

rem -----------------------------------------------------------------------
rem Filename:   sga_stat.sql
rem Purpose:    Display database SGA statistics
rem created by: http://mbc-dba.blogspot.com/
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('SGA CACHE STATISTICS');
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;
/

Sample Output:

SGA CACHE STATISTICS
********************
SQL Cache Hit rate = 99.99
Dict Cache Hit rate = 99.62
Buffer Cache Hit rate = 33.6
Redo Log space requests = 649618
Redo Entries = 18292985697
Redo log space wait time = 2153535
*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.


PL/SQL procedure successfully completed.

SQL>

Hope this will help you.. :)

Best Regards,
Chowdari


Sunday, November 16, 2014

Script: Check RMAN Backup job Status

Below script is very useful for checking RMAN backup job status.

set pagesize 1000
set lin 400
COL COMPRESSION format 99.9
COL Input_GB FORMAT 99999.9999
COL Output_GB FORMAT 99999.99
COL Time FORMAT a10
alter session set nls_date_format='YYYY-MM-DD HH24:MI';
SELECT
        STATUS,
        START_TIME,
        input_type Type,
       COMPRESSION_RATIO Compression,
       INPUT_BYTES/1024/1024/1024 Input_GB ,
       OUTPUT_BYTES/1024/1024/1024 Output_GB,
       TIME_TAKEN_DISPLAY Time
FROM   V$RMAN_BACKUP_JOB_DETAILS
ORDER BY START_TIME;

Sample Output:

STATUS                START_TIME                      TYPE          COMPRESSION    INPUT_GB   OUTPUT_GB    TIME
----------------------- -------------------------------------           -------------     ------------------------ ----------------   -------------------    ----------
COMPLETED      2014-11-13 22:27                    DB INCR         24.5                9366.1675    381.88            14:29:11
COMPLETED      2014-11-14 22:27                    DB INCR         35.1                8868.0660    252.77            15:46:57
RUNNING             2014-11-16 00:18                   DB INCR          5.6                  1918.7863    339.77            08:35:29

Hope this will help you.. :)

Best Regards,
Chowdari

Sunday, November 9, 2014

impdp - ORA-31640 ORA-31693 ORA-19505 ORA-27037

When I am trying to import schema, The impdp is failed to open dump file. see below script I am using.

$ cat parfile.par
USERID='/ as sysdba'
DIRECTORY=IMP_DP
DUMPFILE=SMARTSUPPY_SCHEMA.dmp
logfile=SMARTSUPPY_SCHEMA_09nov2014.log
parallel=10
SCHEMAS='SMARTSUPPY_SCHEMA'

I got below error while import..

ORA-31693: Table data object "SMARTSUPPY_SCHEMA"."BPM_TRANS_90000":"P297" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/export/APSDBSR-1351/SMARTSUPPY_SCHEMA.dmp" for read
ORA-19505: failed to identify file "/export/APSDBSR-1351/SMARTSUPPY_SCHEMA.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
. . imported "SMARTSUPPY_SCHEMA"."BPM_TRANS_90000":"P315"      265.3 MB 1484775 rows
. . imported "SMARTSUPPY_SCHEMA"."TRC_HOP_T_90000":"P139"      219.7 MB 1402774 rows

The issue is due to I am using PARALLEL=10, so the dump file directory not being accessible from all nodes in the RAC. When we use PARALLEL > 1 option the child thread will be started in other node to complete the job faster, when job started in other node the dump file can't access the file. Due to this we receive this error. To fix the issue either you place the dump file to be accessed from all the nodes in that RAC environment or use cluster=N option.

Added cluster=N parameter in parfile and started the import again.

$ cat parfile.par
USERID='/ as sysdba'
DIRECTORY=IMP_DP
DUMPFILE=SMARTSUPPY_SCHEMA.dmp
logfile=SMARTSUPPY_SCHEMA_09nov2014.log
parallel=10
SCHEMAS='SMARTSUPPY_SCHEMA'
CLUSTER=N
$

$ nohup impdp parfile=parfile.par &
[1] 27620
> nohup: ignoring input and appending output to `nohup.out'
$
$ jobs -l
[1]+ 27620 Running                 nohup impdp parfile=parfile.par &
$

$ tail -f SMARTSUPPY_SCHEMA_09nov2014.log
Master table "SYS"."SYS_IMPORT_SCHEMA_03" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_03":  /******** AS SYSDBA parfile=parfile.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P475"  1.956 GB 6711857 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P480"  1.965 GB 6794127 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P474"  1.958 GB 6727517 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P479"  1.921 GB 6616816 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P478"  1.903 GB 6512491 rows
. . imported "SMARTSUPPY_SCHEMA"."BPM_COMPONENTS_90000":"P481"  1.909 GB 6618578 rows

Hope this will help you.. :)

Best Regards,
Chowdari

Thursday, November 6, 2014

Script: Oracle Database Growth per Month

Here you can find two scripts those are database growth per month and tablespace level database growth.

The below script lists the details of database growth per month:

select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where to_char(creation_time,'RRRR')='2014'
group by to_char(creation_time, 'MM-RRRR')
order by  to_char(creation_time, 'MM-RRRR');

Sample output from the script:

Month                                                                       Growth in GB
------------------------------------------------------------      -----------------
05-2014                                                                       101.588867
06-2014                                                                       525.609375
07-2014                                                                             57.5
09-2014                                                                               10
10-2014                                                                       31.0976563
11-2014                                                                               52

Below script is useful for tablespace level database growth:

select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb  
from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# 
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 
ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;

Sample output from the script:

TABLESPACE_NAME                CUR_USED_SIZE_MB AVG_INCREAS_MB
---------------------------------         ---------------------------   -------------------------
DW_AGG                                       8150.31                                 46.94
DW_DAC_REP_DATA                    858.31                                   3.29
DW_DIM_DATA                             7078.81                                 6
DW_DIM_INDX                              3229.69                                3.89
DW_DIM_STG                                1202.38                                -9.61
DW_FACT_DATA                          304706.94                            894.51
DW_FACT_INDX                           32227.81                             183.04
DW_FACT_STG                             5483.81                                120.7
DW_INFA_DOMAIN_DATA              1                                      0
DW_INFA_REP_DATA                 3617.88                                2.43
DW_OTHER                                  14314.88                              83.68
DW_OTHER_INDX                        203.81                                  .71
PRD_BIPLATFORM                      7493                                     64.57
PRD_MDS                                        13.44                                    0
SYSAUX                                       7097.75                                 -3.94
SYSTEM                                       2408.31                                10.43
UNDOTBS1                                 62488.13                               7074.66
UNDOTBS2                                 13088.38                              -866.43
UNDOTBS3                                  81902                                   10326
USERS                                          2015.13                                  -2.68

Hope this scripts will help you. :)

Best Regards,
Chowdari

Some Most Popular Articles