Dear Readers, In this article, we will see Auditing using Percona XDB plugin. Database auditing is the monitoring of selected actions of database users. It doesn’t protect the database in case privileges are set incorrectly, but it can help the administrator detect mistakes Audits are… Read More
Dear Readers, In this article, we will see the following Free Buffer Wait . Buffer Cache Management : 👉 Locating free blocks (Data to write) 👉 Finding data blocks (Data to read) 👉 Managing LRU lists (Flushing least recently used blocks) 👉 Cleaning dirty buffers… Read More
Dear Readers, In this article, we will see the following Transfer Files from One Server to Another Server Using SFTP. Login as root and install sshpass package :
|
1 2 |
[oracle@db2 u01]$ su - root [root@db2 ~]# yum install sshpass |
Create text file and Create .sh file Create text file :
|
1 |
[oracle@db2 u01]$ vi demo.txt |
Create .sh file :… Read More
Dear Readers, In this article, we will see the following Internal mechanism of Buffer Cache. Buffer cache management : 👉 Locating free blocks (Data to write) 👉 Finding data blocks (Data to read) 👉 Managing LRU lists (Flushing least recently used blocks) 👉 Cleaning dirty… Read More
Dear Readers, In this article, we will see the following Check Partition Summary More than N Days (60 Days). Script :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
cat parti_last_60days.sh #!/bin/bash #Script to Check Partition In last 60 Days #./Partition Report.sh DEMO ## Environment variables TODAY=`date +%d-%b-%Y-%H:%M:%S` rm /tmp/part1.log /tmp/part.log touch /tmp/part1.log export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2/db_1' export ORACLE_SID='ORCL11' export ORACLE_BASE='/u01/app/oracle' export PATH=$PATH:${ORACLE_HOME}/bin export MAILIST='ktexpert@ktexperts.com' export LOG='/tmp' _OWNER=$1 function sql_script { echo " " echo "Started To Run Partition Check Sql Query" echo " " sqlplus vinod/vinod <<EOF > /tmp/t.log SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0 spool /tmp/schema.log select table_name from dba_tables where owner='$_OWNER'; spool off; EOF sed -i '1d' /tmp/schema.log sed -i '$d' /tmp/schema.log wc=`wc -l /tmp/schema.log |awk '{ print $1 }'` for (( c=1; c<=$wc; c++ )) do _tbls=`sed ''$c'!d' /tmp/schema.log` _tbls1=` echo -n "${_tbls//[[:space:]]/}"` if [ "$_tbls1" ]; then echo "Checking Partition details on $_tbls1@$_OWNER" sqlplus vinod/vinod <<EOF > /tmp/t.log set line 2000 pages 2000 SET HEADING ON FEEDBACK OFF ECHO OFF PAGESIZE 0 trimspool on col OBJECT_NAME for a40 col SUBOBJECT_NAME for a40 col OWNER for a30 col CREATED for a30 spool /tmp/part.log; SELECT object_name,subobject_name,owner,created FROM dba_objects where owner='$_OWNER' and OBJECT_NAME='$_tbls1' and object_type='TABLE PARTITION' and trunc(created) <= trunc(sysdate-60) order by 4; spool off EOF fi sed -i '1d' /tmp/part.log sed -i '$d' /tmp/part.log #echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log if [ ! -s /tmp/part.log ]; then #echo " " >> /tmp/part1.log #echo "Checked and No Partition Found 60 Days Back for $_tbls1 table " >> /tmp/part1.log #echo " " >> /tmp/part1.log _var='FALSE' else echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log echo " " >> /tmp/part1.log echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log echo "TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE" >> /tmp/part1.log echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log cat /tmp/part.log >> /tmp/part1.log fi done echo " " echo "----- End Of Query Execution logs can be check at /tmp/part1.log ------" } function send_mail { cat /tmp/part1.log | mailx -s "${TODAY} - Partition Check Summary" ${MAILIST} } #MAIN sql_script send_mail |
Out Put :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
################ Partition Check on DEPART table of DEMO schema 60 Days Back ################# ------------------------------------------------------------------------------------------------------------------------------------------- TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE ------------------------------------------------------------------------------------------------------------------------------------------- DEPART SYS_P21641 DEMO 11-JUN-20 DEPART SYS_P13521 DEMO 27-JUL-20 DEPART SYS_P13530 DEMO 28-JUL-20 DEPART SYS_P13587 DEMO 29-JUL-20 DEPART SYS_P13631 DEMO 04-AUG-20 DEPART SYS_P13668 DEMO 05-AUG-20 DEPART SYS_P13768 DEMO 06-AUG-20 DEPART SYS_P13856 DEMO 19-AUG-20 DEPART SYS_P13894 DEMO 20-AUG-20 DEPART SYS_P13928 DEMO 11-SEP-20 DEPART SYS_P14004 DEMO 12-SEP-20 DEPART SYS_P14067 DEMO 14-SEP-20 DEPART SYS_P14072 DEMO 15-SEP-20 ################ Partition Check on EMPBKP table of DEMO schema 60 Days Back ################# ------------------------------------------------------------------------------------------------------------------------------------------- TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE ------------------------------------------------------------------------------------------------------------------------------------------- EMPBKP SYS_P21661 DEMO 11-JUN-20 EMPBKP SYS_P13561 DEMO 28-JUL-20 EMPBKP SYS_P13632 DEMO 04-AUG-20 EMPBKP SYS_P13672 DEMO 05-AUG-20 EMPBKP SYS_P13772 DEMO 06-AUG-20 EMPBKP SYS_P13857 DEMO 19-AUG-20 EMPBKP SYS_P13903 DEMO 20-AUG-20 EMPBKP SYS_P13949 DEMO 11-SEP-20 EMPBKP SYS_P14009 DEMO 12-SEP-20 |
Thank you for giving your valuable time to read the above information. Follow us on Website … Read More
Dear Readers, In this article, we will see TableSpace Menu Bar in SecureCRT . TBS_FREE :
|
1 2 3 |
set linesize 130 set pagesize 1000 col tablespace format a30 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
select tablespace_name tablespace, round(sum(a.total1)/1048576, 0) Total, round(sum(a.total1)/1048576, 0)-round(sum(a.sum1)/1048576, 0) used, round(sum(a.sum1)/1048576, 0) free, round(((sum(a.sum1)/1048576)/(sum(a.total1)/1048576))*100, 0) pct_free, round(sum(a.maxb)/1048576, 0) largest, max(a.cnt) fragment from (select tablespace_name, 0 total1, sum(bytes) sum1, max(bytes) MAXB, count(bytes) cnt from dba_free_space group by tablespace_name union select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files group by tablespace_name) a group by a.tablespace_name order by 5 desc; |
Out Put :
|
1 2 3 4 5 6 |
TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENT ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- USERS 5 2 3 65 3 1 UNDOTBS1 60 24 36 59 25 13 SYSAUX 730 690 40 5 38 14 SYSTEM 800 792 8 1 8 2 |
TBS_FILE :
|
1 2 3 4 |
set linesize 130 set pagesize 1000 set echo on col file_name format a80 |
|
1 2 3 4 |
select file_id,file_name, bytes/1048576 mb,AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAX_BYTES_GB from dba_data_files where tablespace_name='&tbs' order by substr(file_name,length(file_name)-5,6); |
SEGMENTBYNAME :
|
1 2 3 4 5 6 7 8 9 10 11 |
set ver off set trimspool on set lines 199 col owner for a15 col segment_name for a30 col segment_type for a25 col tablespace_name for a22 set pagesize 150 col part_name for a30 compu sum of size_mb on report break on report skip 2 |
|
1 2 3 4 5 |
select owner, segment_name,partition_name part_name, buffer_pool, segment_type, tablespace_name, extents, round(bytes/1024/1024) size_mb from dba_segments where segment_name like upper('&segname'); |
DBSIZE :
|
1 2 3 4 |
select sum(bytes)/1024/1024/1024 GB from dba_segments; GB ---------- 1.46954346 |
|
1 2 3 4 |
select sum(bytes)/1024/1024/1024 GB from dba_data_files; GB ---------- 1.55761719 |
|
1 2 3 4 |
select sum(bytes)/1024/1024/1024 GB from dba_temp_files; GB ---------- .05859375 |
Seg_With_Highest_IO_Reads :
|
1 2 3 4 |
set pagesize 200 set linesize 300 col segment_name format a40 col owner format a10 |
|
1 2 3 4 5 6 7 |
select segment_name,object_type,total_physical_reads from ( select owner||'.'||object_name as segment_name,object_type, value as total_physical_reads from v$segment_statistics where statistic_name in ('physical reads') order by total_physical_reads desc) where rownum <=10; |
Out Put :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SEGMENT_NAME OBJECT_TYPE TOTAL_PHYSICAL_READS ---------------------------------------- ------------------ -------------------- SYS.REFCON$ TABLE 6665 SYS.OBJ$ TABLE 3057 SYS.TYPE$ TABLE 2728 SYS.SEG$ TABLE 2694 SYS.IDL_UB1$ TABLE 1803 SYS.I_OBJ5 INDEX 962 SYS.HISTGRM$ TABLE 958 SYS.I_OBJ1 INDEX 615 SYS.IDL_UB2$ TABLE 542 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY TABLE PARTITION 462 |
Seg_With_Highest_IO_Writes :… Read More
Dear Readers, In this article, we will see the following Log Miner Configuration for Oracle Database. We can easily track history of activity on a database from archived redolog files using log miner. 1. ENABLE SUPPLEMENTAL LOGGING FOR THE DATABASE
|
1 |
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; |
|
1 2 3 4 |
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE; SUPPLEME -------- YES |
2. CHECK THE… Read More
Dear Readers, In this article, we will see GoldenGate 12c setup for Oracle Container Database. Separate user for Extract and Replication need to be created. User for Extract should be common user connected to Root Container in order to interact with the logmining server. For… Read More
Dear Readers, In this article, we will see UNDO segments not Releasing Space . UNDERSTANDING: There are three status of UNDO segments: ACTIVE: Currently in Used EXPIRED: Extend cannot be Used UNEXPIRED: unexpired extents were reused The UNDO records are not deleted when they are… Read More
Dear Readers, In this article, we will see Performance Investigation(Database / SQL) . UNDERSTANDING: Database running slow or application running slow. These are the basic and practical issue come in day today activity. Before Investigation have below answers from application or client or users 👉… Read More
