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 ========