Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

  • Translate

  • It’s Me






  • My Certificates

  • Links

    My Acclaim Certification : Credly Profile
    My Oracle ACE Pro Profile

  • Achievements

    Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
  • VISITORS COUNT

  • Verified International Academic Qualification from World Education Service (WES)

    Verified International Academic Qualification from World Education Service (WES)

  • Jobs

Script to verify the database for Primary & standby

Posted by Mir Sayeed Hassan on June 17th, 2018

Script to verify the database for Primary & standby

[oracle@Prim-DB ~]$ cd /home/oracle/scripts/
[oracle@Prim-DB scripts]$ vi dblogsummary.sql
 !df -h
 !free
 !uptime
 !last reboot

set linesize 500
 col host_name format a40;
 select INST_ID,instance_name,STARTUP_TIME,status,archiver from gv$instance;
 select name,log_mode,protection_mode, protection_level,force_logging from gv$database;

set linesize 1200
 set pagesize 30

select INST_ID,name,state,round(total_mb/1024+.5) as total_gb,
 round(free_mb/1024+.5) as free_gb,
 round((1-free_mb/total_mb)*100+.5) as "use %"
 from gv$ASM_DISKGROUP
 where STATE='CONNECTED';

SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,(SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

set linesize 300
 col error format a20
 col DESTINATION format a20
 select dest_id,status,target,error,destination from v$archive_dest;

select index_name,status from user_indexes where status='UNUSABLE';

set lines 220
 set pages 1000
 col cf for 9,999
 col df for 9,999
 col elapsed_seconds heading "ELAPSED|SECONDS"
 col i0 for 9,999
 col i1 for 9,999
 col l for 9,999
 col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
 col session_recid for 999999 heading "SESSION|RECID"
 col session_stamp for 99999999999 heading "SESSION|STAMP"
 col status for a10 trunc
 col time_taken_display for a10 heading "TIME|TAKEN"
 col output_instance for 9999 heading "OUT|INST"

select
 j.session_recid, j.session_stamp,
 to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
 to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
 (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
 decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
 3, 'Tuesday', 4, 'Wednesday',
 5, 'Thursday', 6, 'Friday',
 7, 'Saturday') dow,
 j.elapsed_seconds, j.time_taken_display,
 x.cf, x.df, x.i0, x.i1, x.l,
 ro.inst_id output_instance
 from V$RMAN_BACKUP_JOB_DETAILS j
 left outer join (select
 d.session_recid, d.session_stamp,
 sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
 sum(case when d.controlfile_included = 'NO'
 and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
 sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
 sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
 sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
 from
 V$BACKUP_SET_DETAILS d
 join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
 where s.input_file_scan_only = 'NO'
 group by d.session_recid, d.session_stamp) x
 on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
 left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
 from GV$RMAN_OUTPUT o
 group by o.session_recid, o.session_stamp)
 ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
 where j.start_time > trunc(sysdate)-7
 order by j.start_time;



set lines 1220
 col CLIENT_NAME format a21
 col WINDOW_NAME format a13
 col WINDOW_START_TIME format a20
 col WINDOW_DURATION format a20
 col JOB_NAME format a24
 col JOB_STATUS format a10
 col JOB_START_TIME format a20
 col JOB_DURATION format a15
 col JOB_ERROR format 99
 col JOB_INFO format a10
 select * from DBA_AUTOTASK_JOB_HISTORY
 where trunc(window_start_time)=to_date(sysdate ,'dd/mm/YY')
 or trunc(window_start_time)=to_date(sysdate-1 ,'dd/mm/YY');



set linesize 180
 set pagesize 30

select
 fs.tablespace_name "Tablespace",
 (df.totalspace - fs.freespace) "Used MB",
 fs.freespace "Free MB",
 df.totalspace "Total MB",
 round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
 from
 (select
 tablespace_name,
 round(sum(bytes) / 1048576) TotalSpace
 from
 dba_data_files
 group by
 tablespace_name
 ) df,
 (select
 tablespace_name,
 round(sum(bytes) / 1048576) FreeSpace
 from
 dba_free_space
 group by
 tablespace_name
 ) fs
 where
 df.tablespace_name = fs.tablespace_name;

set linesize 180
 set pagesize 30
 col filename format a50;

select
 a.tablespace_name,
 round(a.bytes/1073741824,3) as used_gb,
 round(b.bytes/1073741824,3) free_gb,
 round(b.bytes/1073741824,3) max_possibale_gb,
 a.exsize exten_size_mb,
 a.cnt data_files,
 c.bigfile "is bigfile",
 round(((a.bytes-b.bytes)/a.bytes)*100,2) "use%",
 a.fname filename
 from
 (select tablespace_name,sum(bytes) bytes,count(*) cnt,max(file_name) fname,sum(maxbytes) maxsize,max(relative_fno) exsize from dba_data_files group by tablespace_name) a,
 (select tablespace_name,sum(bytes) bytes from dba_free_space group by tablespace_name) b,dba_tablespaces c
 where
 a.tablespace_name=b.tablespace_name and c.tablespace_name=b.tablespace_name
 order by "use%" desc;

SET PAUSE ON
 SET PAGESIZE 60
 SET LINESIZE 300
 COLUMN "Tablespace Name" FORMAT A20
 COLUMN "File Name" FORMAT A80

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
 Substr(df.file_name,1,80) "File Name",
 Round(df.bytes/1024/1024,0) "Size (M)",
 decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
 decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
 decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
 FROM DBA_DATA_FILES DF,
 (SELECT file_id,
 sum(bytes) used_bytes
 FROM dba_extents
 GROUP by file_id) E,
 (SELECT sum(bytes) free_bytes,
 file_id
 FROM dba_free_space
 GROUP BY file_id) f
 WHERE e.file_id (+) = df.file_id
 AND df.file_id = f.file_id (+)
 ORDER BY df.tablespace_name,
 df.file_name;

select e.owner,
 e.segment_type,
 e.segment_name,
 e.partition_name,
 c.file#,
 GREATEST (e.block_id, c.block#) s_blk#,
 LEAST (e.block_id + e.blocks - 1, c.block# + c.blocks - 1) e_dblk#,
 LEAST (e.block_id + e.blocks - 1, c.block# + c.blocks - 1)
 - GREATEST (e.block_id, c.block#)
 + 1
 blk_corrupt,
 NULL description
 FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
 AND e.block_id <= c.block# + c.blocks - 1
 AND e.block_id + e.blocks - 1 >= c.block#
 UNION
 SELECT s.owner,
 s.segment_type,
 s.segment_name,
 s.partition_name,
 c.file#,
 header_block s_blk#,
 header_block e_blk#,
 1 blk_corrupt,
 'Segment Header' description
 FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
 AND s.header_block BETWEEN c.block# AND c.block# + c.blocks - 1
 UNION
 SELECT NULL owner,
 NULL segment_type,
 NULL segment_name,
 NULL partition_name,
 c.file#,
 GREATEST (f.block_id, c.block#) s_blk#,
 LEAST (f.block_id + f.blocks - 1, c.block# + c.blocks - 1) e_blk#,
 LEAST (f.block_id + f.blocks - 1, c.block# + c.blocks - 1) - GREATEST (f.block_id, c.block#) + 1
 blk_corrupt,
 'Free Block' description
 FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
 AND f.block_id <= c.block# + c.blocks - 1
 AND f.block_id + f.blocks - 1 >= c.block#
 ORDER BY file#, s_blk#
 /
 exit;

Verify:

[oracle@Prim-DB scripts]$ !sq
 sqlplus / as sysdba
 SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 17 16:03:10 2018
 Copyright (c) 1982, 2013, Oracle. All rights reserved.
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/home/oracle/scripts/dblogsummary.sql;
 

Filesystem Size Used Avail Use% Mounted on
 /dev/sda3 518G 77G 415G 16% /
 tmpfs 16G 8.1G 8.0G 51% /dev/shm
 /dev/sda1 1.9G 34M 1.8G 2% /boot
 /dev/sdb1 1.1T 172G 873G 17% /u01
 total used free shared buffers cached
 Mem: 32972356 29865424 3106932 6840120 280208 25879040
 -/+ buffers/cache: 3706176 29266180
 Swap: 32767996 2495372 30272624
 16:03:21 up 408 days, 4:55, 1 user, load average: 0.54, 0.57, 0.45
 reboot system boot 2.6.32-504.el6.x Fri Aug 15 10:38 - 10:32 (8+23:54)
 reboot system boot 2.6.32-504.el6.x Sun Aug 10 08:58 - 10:32 (14+01:33)
 reboot system boot 2.6.32-504.el6.x Fri Aug 8 04:06 - 10:32 (16+06:26)
 reboot system boot 2.6.32-504.el6.x Thu Aug 7 01:18 - 04:03 (1+02:44)
 reboot system boot 2.6.32-504.el6.x Mon Aug 4 06:44 - 06:44 (00:00)
 reboot system boot 2.6.32-504.el6.x Mon Aug 4 06:42 - 06:44 (00:02)
 reboot system boot 2.6.32-504.el6.x Mon Aug 4 06:17 - 06:39 (00:21)
 wtmp begins Mon Aug 4 06:17:22 2014
 INST_ID INSTANCE_NAME STARTUP_T STATUS ARCHIVE
 ---------- ---------------- --------- ------------ -------
 1 primdb 05-MAY-17 OPEN STARTED
 NAME LOG_MODE PROTECTION_MODE PROTECTION_LEVEL FOR
 --------- ------------ -------------------- -------------------- ---
 PRIMDB ARCHIVELOG MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE YES

no rows selected
 THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_ ARC_DIFF
 ---------- ---------- ----------- --------- ----------
 1 59144 59143 17-JUN-18 1
 DEST_ID STATUS TARGET ERROR DESTINATION
 ---------- --------- ------- -------------------- --------------------
 1 VALID PRIMARY /u01/app/oracle/orad
 ata/primdb/
 2 VALID STANDBY stdprimdb
 3 INACTIVE PRIMARY
 4 INACTIVE PRIMARY
 5 INACTIVE PRIMARY
 6 INACTIVE PRIMARY
 7 INACTIVE PRIMARY
 8 INACTIVE PRIMARY
 9 INACTIVE PRIMARY
 10 INACTIVE PRIMARY
 11 INACTIVE PRIMARY
 12 INACTIVE PRIMARY
 13 INACTIVE PRIMARY
 14 INACTIVE PRIMARY
 15 INACTIVE PRIMARY
 16 INACTIVE PRIMARY
 17 INACTIVE PRIMARY
 18 INACTIVE PRIMARY
 19 INACTIVE PRIMARY
 20 INACTIVE PRIMARY
 21 INACTIVE PRIMARY
 22 INACTIVE PRIMARY
 23 INACTIVE PRIMARY
 24 INACTIVE PRIMARY
 25 INACTIVE PRIMARY

DEST_ID STATUS TARGET ERROR DESTINATION
 ---------- --------- ------- -------------------- --------------------
 26 INACTIVE PRIMARY
 27 INACTIVE PRIMARY
 28 INACTIVE PRIMARY
 29 INACTIVE PRIMARY
 30 INACTIVE PRIMARY
 31 INACTIVE PRIMARY
 31 rows selected.

no rows selected

SESSION SESSION OUTPUT ELAPSED TIME OUT
 RECID STAMP START_TIME END_TIME MBYTES STATUS INPUT_TYPE DOW SECONDS TAKEN CF DF I0 I1 L INST
 ------- ------------ ------------------- ------------------- ---------- ---------- ------------- --------- ---------- ---------- ------ ------ ------ ------ ------ -----
 6878 978395402 2018-06-10 00:30:07 2018-06-10 00:33:10 1,160 COMPLETED DB INCR Sunday 183 00:03:03 0 0 0 1 2 1
 6885 978481802 2018-06-11 00:30:07 2018-06-11 00:33:41 1,238 COMPLETED DB INCR Monday 214 00:03:34 0 0 0 1 2 1
 6892 978568202 2018-06-12 00:30:07 2018-06-12 00:34:03 1,369 COMPLETED DB INCR Tuesday 236 00:03:56 0 0 0 1 2 1
 6899 978654602 2018-06-13 00:30:07 2018-06-13 00:34:11 1,330 COMPLETED DB INCR Wednesday 244 00:04:04 0 0 0 1 2 1
 6906 978741002 2018-06-14 00:30:08 2018-06-14 00:34:12 1,396 COMPLETED DB INCR Thursday 244 00:04:04 0 0 0 1 2 1
 6913 978827402 2018-06-15 00:30:07 2018-06-15 01:01:42 13,832 COMPLETED DB INCR Friday 1895 00:31:35 0 0 1 0 2 1
 6920 978913803 2018-06-16 00:30:08 2018-06-16 00:32:50 1,006 COMPLETED DB INCR Saturday 162 00:02:42 1 0 0 1 2 1
 6927 979000202 2018-06-17 00:30:07 2018-06-17 00:32:48 936 COMPLETED DB INCR Sunday 161 00:02:41 2 1 0 1 2 1
 8 rows selected.
 CLIENT_NAME WINDOW_NAME WINDOW_START_TIME WINDOW_DURATION JOB_NAME JOB_STATUS JOB_START_TIME JOB_DURATION JOB_ERROR JOB_INFO
 --------------------- ------------- -------------------- -------------------- ------------------------ ---------- -------------------- --------------- --------- ----------
 auto optimizer stats SATURDAY_WIND 16-JUN-18 10.00.00.2 +000000000 07:59:59. ORA$AT_OS_OPT_SY_4304 SUCCEEDED 16-JUN-18 10.00.02.3 +000 00:00:33 0
 collection OW 47816 PM +04:30 853314 25679 PM ASIA/TEHRAN
 auto optimizer stats SATURDAY_WIND 16-JUN-18 10.00.00.2 +000000000 07:59:59. ORA$AT_OS_OPT_SY_4307 SUCCEEDED 17-JUN-18 02.01.21.2 +000 00:00:07 0
 collection OW 47816 PM +04:30 853314 12920 AM ASIA/TEHRAN
 auto space advisor SATURDAY_WIND 16-JUN-18 10.00.00.2 +000000000 07:59:59. ORA$AT_SA_SPC_SY_4308 SUCCEEDED 17-JUN-18 02.01.21.3 +000 00:00:06 0
 OW 47816 PM +04:30 853314 14955 AM ASIA/TEHRAN
 auto space advisor SATURDAY_WIND 16-JUN-18 10.00.00.2 +000000000 07:59:59. ORA$AT_SA_SPC_SY_4305 SUCCEEDED 16-JUN-18 10.00.02.3 +000 00:00:49 0
 OW 47816 PM +04:30 853314 36923 PM ASIA/TEHRAN
 sql tuning advisor SATURDAY_WIND 16-JUN-18 10.00.00.2 +000000000 07:59:59. ORA$AT_SQ_SQL_SW_4306 SUCCEEDED 16-JUN-18 10.00.02.3 +000 00:00:31 0
 OW 47816 PM +04:30 853314 51251 PM ASIA/TEHRAN

Tablespace Used MB Free MB Total MB Pct. Free
 ------------------------------ ---------- ---------- ---------- ----------
 SYSAUX 10087 1177 11264 10
 UNDOTBS1 52 538 590 91
 USERS 30916 30524 61440 50
 SYSTEM 9410 11070 20480 54
 KERMAN_TBS 1106 9134 10240 89

TABLESPACE_NAME USED_GB FREE_GB MAX_POSSIBALE_GB EXTEN_SIZE_MB DATA_FILES is use% FILENAME
 ------------------------------ ---------- ---------- ---------------- ------------- ---------- --- ---------- --------------------------------------------------
 SYSAUX 11 1.149 1.149 2 1 NO 89.55 /u01/app/oracle/oradata/primdb/sysaux01.dbf
 USERS 60 29.809 29.809 6 2 NO 50.32 /u01/app/oracle/oradata/primdb/users02.dbf
 SYSTEM 20 10.811 10.811 1 1 NO 45.95 /u01/app/oracle/oradata/primdb/system01.dbf
 KERMAN_TBS 10 8.92 8.92 5 1 NO 10.8 /u01/app/oracle/oradata/primdb/kermanDF01.dbf
 UNDOTBS1 .576 .526 .526 3 1 NO 8.76 /u01/app/oracle/oradata/primdb/undotbs01.dbf

Tablespace Name File Name Size (M) Used (M) Free (M) % Used
 -------------------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
 KERMAN_TBS /u01/app/oracle/oradata/primdb/kermanDF01.dbf 10240 1105 9134 11
 SYSAUX /u01/app/oracle/oradata/primdb/sysaux01.dbf 11264 10086 1177 90
 SYSTEM /u01/app/oracle/oradata/primdb/system01.dbf 20480 9409 11070 46
 UNDOTBS1 /u01/app/oracle/oradata/primdb/undotbs01.dbf 590 51 538 9
 USERS /u01/app/oracle/oradata/primdb/users01.dbf 30720 29653 1066 97
 USERS /u01/app/oracle/oradata/primdb/users02.dbf 30720 1261 29458 4

6 rows selected.

no rows selected
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

======== Hence tested & verified in our production database ========