Oracle 11g Logo

Query to find out progress of transaction recovery by SMON

Have you killed the long running query?

SMON is solely responsible for recovering transactions when you kill any large running query( truncate and delete ) by killing OS process or aborting database, SMON will take all possible CPU to rolling back previous state and its highly time-consuming task.
And frustrated DBA/Person will think bouncing database will resolve this problem completely, as obvious “shutdown immediate” will take equal amount of time to rollback and finally database being “aborted” by the user.
Kindly note: Shutting down or aborting database is not the solution, and won’t reduce the amount of work SMON need to be performed to complete rollback.

Following query will help you to identify the total amount of work to be rolled back by SMON, simply progress of transaction recovery. Run it multiple time.

SQL> SELECT usn, state, undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
DECODE(cputime,0,'unknown',SYSDATE+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Finish at"
FROM v$fast_start_transactions;

USN        STATE            Total      Done       ToDo       Finish at
---------- ---------------- ---------- ---------- ---------- --------------------
 724       RECOVERING       112623     4658       107965     09-MAR-2017 17:30:16
 737       RECOVERING       275333     1755       273578     09-MAR-2017 21:49:03

<<After some time>>

USN        STATE            Total      Done       ToDo       Finish at
---------- ---------------- ---------- ---------- ---------- --------------------
 724       RECOVERING       28647      9324       19323      09-MAR-2017 17:16:50
 737       RECOVERING       240190     2792       237398     09-MAR-2017 20:33:12

Note:

On the same time, your ADRCI database log will be flooded with following:

2017-03-09 17:17:25.063000 +05:30
SMON: Restarting fast_start parallel rollback

No action is required. No corruption applies unless the error is followed by other internal errors on the object.

Thanks

Stay tune. ūüôā

Script to find out total number of oracle schema objects and its size

Following script help you to find out available schema’s along with total number of objects and its size ( in MB )

SQL> set pages 999
SQL> col "size MB" format 999,999,999
SQL> col "Objects" format 999,999,999
SQL> select obj.owner "Owner", obj_cnt "Objects", decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1;

O/P:

Owner                          Objects      size MB
------------------------------ ------------ ------------
PRODAPP                        32,527       39,433
SYS                            30,924       5,521
SYSTEM                         604          4,468
AEX_030200                     2,406        157
XPRDB                          844          129
SYSMAN                         3,491        123
GHSYS                          1,019        29
TRXSYS                         366          19
...

Stay Tune. ūüôā

Script to find out datafiles with highest Input-Output activity

Following script will find out all datafiles OR first 5 datafiles with highest Input-Output in terms of physical reads and write on datafiles along with read/write time.

SQL> col name format a50
SQL> set linesize 200
SQL> select * from ( select name,phyrds, phywrts, readtim, writetim
from v$filestat a, v$datafile b where a.file#=b.file#
order by readtim desc) where rownum < 6;

O/P:

NAME                                    PHYRDS     PHYWRTS  READTIM   WRITETIM
--------------------------------------- ---------  -------- --------  ---------
/DATA/database/prod/prod_app_data.dbf   958846     231710   55197     12248
/DATA/database/prod/system01.dbf        1113495    69047    46798     1677
/DATA/database/prod/prod_app_lob.dbf    11226086   57018    44491     469
/DATA/database/prod/prod_app_index.dbf  389124     134121   44330     6719
/DATA/database/prod/sysaux01.dbf        396043     115628   29089     5735

Stay Tune ūüôā

Oracle 11g Logo

Script to extract size of Oracle tablespaces & associated datafiles

By issuing below script you will extract the total size of tablespces along with Total space available in MB, Free space available in MB & Free percentage(%)

Syntax:
COLU Free% FORMA A10
COLUMN tablespace_name FORMAT A17
SELECT t.tablespace_name, t.total_space_in_MB, f.free_space_in_MB,
TO_CHAR((f.free_space_in_MB*100/t.total_space_in_MB),'99990.000')
"Free%"

FROM (SELECT tablespace_name, SUM(bytes)/1024/1024 Total_space_in_MB FROM DBA_DATA_FILES GROUP BY tablespace_name) t,
(SELECT tablespace_name, SUM(bytes)/1024/1024 Free_space_in_MB FROM DBA_FREE_SPACE GROUP BY
tablespace_name) f WHERE t.tablespace_name= f.tablespace_name;

OR

 

select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by 1;

 

 

By issuing below script you will extract the size & path of datafiles along with tablespace name, allocated space in MB, used space in MB & free space in MB.

Syntax:
set linesize 200
set pagesize 2000
COLUMN tablespace_name format a10
COLUMN file_name format a45
COLUMN free% format a7
SELECT df.tablespace_name,SUBSTR (df.file_name, 1, 60) file_name, df.bytes / 1024 / 1024 allocated_mb,
round(((df.bytes / 1024 / 1024) ‚Äď NVL (SUM (dfs.bytes) / 1024 / 1024, 0)),1) used_mb,
round(NVL (SUM (dfs.bytes) / 1024 / 1024, 0),2) free_space_mb
FROM dba_data_files df, dba_free_space dfs
WHERE df.FILE_ID = dfs.file_id(+)
GROUP BY df.tablespace_name, dfs.file_id, df.file_id , df.bytes, df.file_NAME
ORDER BY df.tablespace_name;

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

Script to extract total objects & size of all Schemas

Script to extract total objects & size of all¬†Schema’s:

By issuing below script you will find out all schema’s along with size they have yet consumed, script will find out all users. ( system users + user defined users)
Also you can find out total number of objects contains that schema.

 

Issue following query as sys user:

select obj.owner "Owner", obj_cnt "Objects",
decode(seg_size, NULL, 0, seg_size) "size MB"
from ( select owner, count(*) obj_cnt from dba_objects group by owner) obj,
( select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) segment
where obj.owner = segment.owner(+)
order by 3 desc, 2 desc, 1;

Owner                          Objects     size MB
—————————— ¬† ¬† ———- ¬† ¬† ¬†———-
SYS                            30965       990
XDB                            842          128
APEX_030200             2406         78
SYSMAN                     3491         46

. .. …

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have a easy life ahead.

Oracle 11g Logo

Script to extract Up time of Oracle database

By issuing following script you will extract exact uptime of database cum Hostname, Instance Name & Database Start time:

Syntax:
 column hostname format a26
 column ‚ÄúInstance Name‚ÄĚ format a16
 column ‚ÄúStarted At‚ÄĚ format a26
 column ‚ÄúDatabase Uptime‚ÄĚ format a52
 SELECT
 host_name as Hostname,
 instance_name as ‚ÄúInstance Name‚ÄĚ,
 to_char(startup_time,‚ÄôDD-MON-YYYY HH24:MI:SS‚Äô) as ‚ÄúStarted_At‚ÄĚ,
 floor(sysdate ‚Äď startup_time) || ‚Äė days(s) ‚Äė ||
 trunc( 24*((sysdate-startup_time) -
 trunc(sysdate-startup_time))) || ‚Äė hour(s) ‚Äė ||
 mod(trunc(1440*((sysdate-startup_time) -
 trunc(sysdate-startup_time))), 60) ||‚Äô minute(s) ‚Äė ||
 mod(trunc(86400*((sysdate-startup_time) -
 trunc(sysdate-startup_time))), 60) ||‚Äô seconds‚Äô as ‚ÄúDatabase_Uptime‚ÄĚ
 FROM
 sys.v_$instance;

HOSTNAME

——–
Instance Name Started_At
——– —————
Database_Uptime
————————————–
localhost.localdomain
DemoDB 13-APR-2013 12:11:40
216 days(s) 22 hour(s) 1 minute(s) 52 seconds

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

Script to figure out total number of log switch occurred

Script to find out total number of archive logs generated for hourly, daily, monthly & yearly.

This will help you to figure out:
РThe transnational work load on your database.
– The total number of archive log files being generated.
РTo schedule or Re-schedule your RMAN transnational log backup policy.

// Script to find-out hourly log switch count:
set pages 1000
select to_char(COMPLETION_TIME,'dd mon yyyy hh24') as Hour,
thread#,
round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by to_char(COMPLETION_TIME,'dd mon yyyy hh24'),thread# order by 1;
// Script to find-out daily log switch count:
 SELECT to_char(first_time, 'dd-mon-yyyy') as "Date", 
 count(*) as "Daily log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'dd-mon-yyyy');

OR

SELECT A.*, Round(A.Count*B.AVG/1024/1024) Daily_average_MB
 FROM ( SELECT To_Char(First_Time,'YYYY-MM-DD') Day,
 Count(1) Count,
 Min(RECID) Min,
 Max(RECID) Max
 FROM v$log_history
 GROUP BY To_Char(First_Time,'YYYY-MM-DD')
 ORDER BY 1 DESC ) A,
 ( SELECT Avg(BYTES) AVG,
 Count(1) Count#,
 Max(BYTES) Max_Bytes,
 Min(BYTES) Min_Bytes
 FROM v$log ) B;

// Script to find-out monthly log switch count:

 SELECT to_char(first_time, 'yyyy-mon') as "Year:Month",
 count(*) as "Daily log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'yyyy-mon')
 ORDER BY 1;
// Script to find-out yearly log switch count:
 SELECT to_char(first_time, 'yyyy') as "Year", 
 count(*) as "Yearly log switch count"
 FROM V$log_history
 GROUP BY to_char(first_time, 'yyyy');

***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have a easy life ahead.

Oracle 11g Logo

Script to find fragmentation in Oracle tablespaces

By issuing following script you will extract fragmentation in tablespaces cum TABLESPACE NAME, FREE CHUNKS & LARGEST CHUNK of that tablespace:

Syntax:

 SELECT
 tablespace_name, 
 count(*) free_chunks,
 decode(round((max(bytes) / 1024000),2),
 null,0,
 round((max(bytes) / 1024000),2)) largest_chunk,
 nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
 FROM
 sys.dba_free_space 
 group by 
 tablespace_name
 order by 2 desc, 1;

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Oracle 11g Logo

How to check RMAN backup status and timings

This script will be run in the target( registered ) database, not in the catalog ( Repository ) database.

Login as sysdba and issue the following script:

This script will report on all currently running RMAN backups like full, incremental & archivelog backups:

SQL> col STATUS format a9
SQL> col hrs format 999.99
SQL> select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .00
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .01
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       COMPLETED 01/07/14 10:28 01/07/14 10:29 .03

Above script will give you RMAN backup status along with start and stop timing.

OR

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
 18        29         1          9115569    19258880   47.33

Above script will give you SID, Total Work, Sofar & % of completion.

You can also check historical backup status with the help of following script:

set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SESSION_KEY INPUT_TYPE    STATUS     RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME  HOURS
----------- ------------- ---------- ------------------- ------------------- --------
 137764     DB FULL       COMPLETED  06-06-2017 02:00:32 06-06-2017 04:12:13 2.19
 137770     ARCHIVELOG    COMPLETED  06-06-2017 04:00:29 06-06-2017 04:01:05 .01
 137778     ARCHIVELOG    COMPLETED  06-06-2017 06:00:27 06-06-2017 06:00:35 .00
 137782     ARCHIVELOG    COMPLETED  06-06-2017 08:00:32 06-06-2017 08:03:36 .05
 137786     ARCHIVELOG    COMPLETED  06-06-2017 10:00:30 06-06-2017 10:02:03 .03
 137790     ARCHIVELOG    COMPLETED  06-06-2017 12:00:30 06-06-2017 12:02:34 .03
 137794     ARCHIVELOG    COMPLETED  06-06-2017 14:00:30 06-06-2017 14:02:58 .04
 . .. ...

*****

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have an easy life ahead.