KTEXPERTS – Page 76 – KNOWLEDGE SHARING PLATFORM

Generate Service and DB Details with one command for N DB’s on one cluster

8 years ago by Vinod SiramOracle DB Admin

for i in srvctl config; do echo $i; srvctl status database -d $i; done; for i in srvctl config; do echo $i; srvctl status service -d $i; done;

Start All DB’s using script

8 years ago by Vinod SiramOracle DB Admin

for ORA_SID in $(cat /var/opt/oracle/oratab |grep -vi asm |grep -v ‘#’|grep -vi agent |grep -vi orcl7 |awk -F”:” ‘{ print $1}’) do ORACLE_SID=$ORA_SID export ORACLE_SID sqlplus -s “/ as sysdba” <<! startup; exit; ! done

Simple Script to Delete Archive logs

8 years ago by Vinod SiramOracle DB Admin

$ cat /u01/app/oracle/admin/SEPQA01/scripts/archiveclean.sh #CURRDATETIME=date ‘+%Y%m%d-%H%M%S’ ORACLE_SID=ORCL1;export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/12102;export ORACLE_HOME LOGDIR=/var/opt/monitoring/oracle/logs LOGFILE=/var/opt/monitoring/oracle/logs/archiveclean.log $ORACLE_HOME/bin/rman <<EOF | tee -a /var/opt/monitoring/oracle/logs/archiveclean.log connect target delete archivelog until time ‘sysdate-1’ ; exit EOF ORACLE_SID=ORCL2;export ORACLE_SID $ORACLE_HOME/bin/rman <<EOF | tee -a /var/opt/monitoring/oracle/logs/archiveclean.log connect target delete archivelog until time ‘sysdate-1’ ; exit EOF… Read More

RMAN Full Backup

8 years ago by Vinod SiramOracle DB Admin

/u01/app/oracle/DBA/rmanbackup.sh #!/bin/ksh CURRDATETIME=date ‘+%Y%m%d-%H%M%S’a ORACLE_SID=ORCL1;export ORACLE_SID ORACLE_HOME=/u01/app/oracle/product/12.1.0.2;export ORACLE_HOME LOGDIR=/var/opt/oracle/logs LOGFILE=${LOGDIR}/${ORACLE_SID}_rman_${CURRDATETIME}.log RMANBACKUPDIR=/backup/ORCL1/jul16 $ORACLE_HOME/bin/sqlplus dbsnmp/password <<EOF | tee -a $LOGFILE SET LINESIZE 200 COL FILE_NAME FOR A70 SET FEEDBACK OFF SET PAGESIZE 100 COL MEMBER FOR A60 SELECT FILE_NAME,BYTES/1024/1024 MBYTES FROM DBA_DATA_FILES; SELECT FILE_NAME,BYTES/1024/1024 MBYTES FROM DBA_TEMP_FILES;… Read More

Load Test Monitoring

8 years ago by Vinod SiramLoad Test

$ cat run_any_sql_10sec.ksh 										
#!/bin/ksh                                                        
                                                                  
SQL_FILE=$1                                                       
TMP_FILE=/tmp/$$.out                                              
TMP_FILE_ALL=/tmp/$$.all.out                                      
                                                                  
for i in {1..10000}                                               
do                                                                
${ORACLE_HOME}/bin/sqlplus -s -l /nolog &lt;&lt; EOF &gt; ${TMP_FILE}      
Connect / as sysdba                                               
SET SERVEROUT ON ECHO ON                                          
COL SEGMENT_NAME FOR A30                                          
COL TABLE_NAME FOR A30                                            
COL COLUMN_NAME FOR A30                                           
COL OWNER FOR A10                                                 
COL SQL_TEXT FOR A45                                              
COL INDEX_NAME FOR A30                                            
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;       
start ${SQL_FILE};                                                
EXIT;                                                             
EOF                                                               
cat ${TMP_FILE}  &gt;&gt; ${TMP_FILE_ALL}                               
cat ${TMP_FILE}                                                   
sleep 10                                                          
done                                                              


chmod 754 run_any_sql 
./run_any_sql waits.sql  

##########
waits.sql

set linesize 300 
select inst_id, event, count(*), avg(Wait_time), max(wait_time) 
  from gv$session_wait  
  where event not in (  
'HS message to agent',  
'SQL*Net break/reset to client', 
'SQL*Net message from client', 
'SQL*Net message to client', 
'jobq slave wait', 
'pipe get', 
'pmon timer', 
'queue messages', 
'rdbms ipc message', 
'smon timer', 
'wait for unread message on broadcast channel', 
'wakeup time manager', 
'ASM background timer', 
'DIAG idle wait', 
'PX Deq: Execute Reply', 
'PX Deq: Execution Msg', 
'PX Deq: reap credit', 
'Streams AQ: qmn coordinator idle wait', 
'Streams AQ: qmn slave idle wait', 
'Streams AQ: waiting for messages in the queue', 
'Streams AQ: waiting for time management or cleanup tasks', 
'class slave wait' 
) 
and wait_class&lt;&gt;'Idle' 
group by inst_id, event 
order by 1,3;  

########

rowlocks.sql

col obj for a85 
col ses_cnt for 9999 
col info for a35 
set lines 160 
set trimspool on 

select  
obj,  
count(*) as ses_cnt,  
min(sql_hash_value) hash,  
cmd ||' : '|| module "INFO" 
 from 
  (select decode(sign(row_wait_obj#), 1, 
                (select object_name from dba_objects where object_id=row_wait_obj# and data_object_id is not null) 
                ,-1, 'UNKNOWN', 'UNKNOWN') || ':' || event || ':' || p2 || ':' || p3 || ':' || 
                chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1, 16711680)/65535) || ':''' || 
               decode(sign(row_wait_obj#), 1, dbms_rowid.rowid_create(1, (select data_object_id from dba_objects 
                                          where object_id = row_wait_obj# and data_object_id is not null), 
                                       row_wait_file#, row_wait_block#, row_wait_row#), 'No rowid') || '''' as obj, 
       decode(command, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', 182, 'UPDATE INDEXES', 3, 'SELECT FOR UPDATE', command) as cmd, 
       sql_hash_value, 
       module 
  from 
  v$session 
  where 
  status = 'ACTIVE' and 
  lockwait is not null 
  ) 
group by obj, cmd||' : '||module 
order by 2; 

#########

locktree.sql

set lines 199 
col sql_id for a13 
col sid for a18 
col status for a10 
col obj_info for a90 
col wait_sec for 99999 

select 
sid, sql_id, seconds_in_wait wait_sec, 
decode(sign(row_wait_obj#), 1, 
                (select object_name from dba_objects where object_id=row_wait_obj# and data_object_id is not null) 
               ,-1, 'Null', 'Null') ||':'|| cmd ||':'|| event#||':'||nvl(substr(event, 1, 15), 'No Event') ||':'|| 
               nvl(p1,0)||':'||nvl(p2, 0) ||':'|| nvl(p3, 0) ||':'|| info "OBJ_INFO" 
from 
( 
 select 
 sid, sql_id, status, seconds_in_wait, 
 sid2, sid3, 
 max(seconds_in_wait) over (partition by sid2) ctime, 
 count(*) over (partition by sid2) cnt, 
 row_wait_obj#, 
 cmd, event, event#, p1, p2, p3, "INFO" 
 from 
 ( 
  select 
  lpad(' ', level-1)||sid sid, decode(sql_id, null, prev_sql_id, sql_id) sql_id, status, 
  seconds_in_wait, 
  blocking_session, 
  blocking_session_status, 
  connect_by_root sid sid2, 
  sys_connect_by_path(sid,':') sid3, 
  row_wait_obj#, 
  decode(command, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', 182, 'UPDATE INDEXES', 3, 'SELECT FOR UPDATE', command) as cmd, 
  event, event#, 
  p1, 
  p2, 
  p3, 
  status||':'||machine||':'||substr(module, 1, 10) "INFO" 
  from v$session 
  where taddr is not null or blocking_session  &lt;&gt; 0 
  connect by prior sid=blocking_session 
  start with blocking_session is null 
 ) 
) 
where cnt &gt; 1 
order by ctime desc, sid2,  length(sid3), seconds_in_wait desc; 



##########

blocking_sessions.sql

set linesize 300 
select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('|
|W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from 
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION &gt;0) W, 
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B 
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID; 

############

active_sessions.sql

select 
(select 'Active Sessions in MY DB Inst 1 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=1 and sql_id is not null ) as Active_Sessions 
from dual                  
union                      
select                     
(select 'Active Sessions in MY DB Inst 2 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=2 and sql_id is not null ) as c2 
from dual                   
union                       
select                      
(select 'Active Sessions in MY DB Inst 3 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=3 and sql_id is not null ) as c2 
from dual                   
union                       
select                      
(select 'Active Sessions in MY DB Inst 4 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=4 and sql_id is not null ) as c2 
from dual; 

############

TOPSQL.sql

select sql_id,username,count(*) from v$session where type='USER' and status='ACTIVE' group by sql_id,username order by 3; 

##########

Userconnections.sql

 select USERNAME,status,count(*) from v$session where username not in ('SYSTEM','SYS') and username is not null group by USERNAME,status order by 2; 

##########

CPU ::

for ((;;)) do sar 2 30; sleep 10; done

##########

Memory 

free -m -s 5

#########

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

$ cat run_any_sql_10sec.ksh

#!/bin/ksh

SQL_FILE=$1

TMP_FILE=/tmp/$$.out

TMP_FILE_ALL=/tmp/$$.all.out

for i in {1..10000}

${ORACLE_HOME}/bin/sqlplus -s -l /nolog << EOF > ${TMP_FILE}

Connect / as sysdba

SET SERVEROUT ON ECHO ON

COL SEGMENT_NAME FOR A30

COL TABLE_NAME FOR A30

COL COLUMN_NAME FOR A30

COL OWNER FOR A10

COL SQL_TEXT FOR A45

COL INDEX_NAME FOR A30

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;

start ${SQL_FILE};

EXIT;

EOF

cat ${TMP_FILE} >> ${TMP_FILE_ALL}

cat ${TMP_FILE}

sleep 10

done

chmod 754 run_any_sql

./run_any_sql waits.sql

##########

waits.sql

set linesize 300

select inst_id, event, count(*), avg(Wait_time), max(wait_time)

from gv$session_wait

where event not in (

'HS message to agent',

'SQL*Net break/reset to client',

'SQL*Net message from client',

'SQL*Net message to client',

'jobq slave wait',

'pipe get',

'pmon timer',

'queue messages',

'rdbms ipc message',

'smon timer',

'wait for unread message on broadcast channel',

'wakeup time manager',

'ASM background timer',

'DIAG idle wait',

'PX Deq: Execute Reply',

'PX Deq: Execution Msg',

'PX Deq: reap credit',

'Streams AQ: qmn coordinator idle wait',

'Streams AQ: qmn slave idle wait',

'Streams AQ: waiting for messages in the queue',

'Streams AQ: waiting for time management or cleanup tasks',

'class slave wait'

)

and wait_class<>'Idle'

group by inst_id, event

order by 1,3;

########

rowlocks.sql

col obj for a85

col ses_cnt for 9999

col info for a35

set lines 160

set trimspool on

select

obj,

count(*) as ses_cnt,

min(sql_hash_value) hash,

cmd ||' : '|| module "INFO"

from

(select decode(sign(row_wait_obj#), 1,

(select object_name from dba_objects where object_id=row_wait_obj# and data_object_id is not null)

,-1, 'UNKNOWN', 'UNKNOWN') || ':' || event || ':' || p2 || ':' || p3 || ':' ||

chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1, 16711680)/65535) || ':''' ||

decode(sign(row_wait_obj#), 1, dbms_rowid.rowid_create(1, (select data_object_id from dba_objects

where object_id = row_wait_obj# and data_object_id is not null),

row_wait_file#, row_wait_block#, row_wait_row#), 'No rowid') || '''' as obj,

decode(command, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', 182, 'UPDATE INDEXES', 3, 'SELECT FOR UPDATE', command) as cmd,

sql_hash_value,

module

from

v$session

where

status = 'ACTIVE' and

lockwait is not null

)

group by obj, cmd||' : '||module

order by 2;

#########

locktree.sql

set lines 199

col sql_id for a13

col sid for a18

col status for a10

col obj_info for a90

col wait_sec for 99999

select

sid, sql_id, seconds_in_wait wait_sec,

decode(sign(row_wait_obj#), 1,

(select object_name from dba_objects where object_id=row_wait_obj# and data_object_id is not null)

,-1, 'Null', 'Null') ||':'|| cmd ||':'|| event#||':'||nvl(substr(event, 1, 15), 'No Event') ||':'||

nvl(p1,0)||':'||nvl(p2, 0) ||':'|| nvl(p3, 0) ||':'|| info "OBJ_INFO"

from

(

select

sid, sql_id, status, seconds_in_wait,

sid2, sid3,

max(seconds_in_wait) over (partition by sid2) ctime,

count(*) over (partition by sid2) cnt,

row_wait_obj#,

cmd, event, event#, p1, p2, p3, "INFO"

from

(

select

lpad(' ', level-1)||sid sid, decode(sql_id, null, prev_sql_id, sql_id) sql_id, status,

seconds_in_wait,

blocking_session,

blocking_session_status,

connect_by_root sid sid2,

sys_connect_by_path(sid,':') sid3,

row_wait_obj#,

decode(command, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', 182, 'UPDATE INDEXES', 3, 'SELECT FOR UPDATE', command) as cmd,

event, event#,

p1,

p2,

p3,

status||':'||machine||':'||substr(module, 1, 10) "INFO"

from v$session

where taddr is not null or blocking_session <> 0

connect by prior sid=blocking_session

start with blocking_session is null

)

where cnt > 1

order by ctime desc, sid2, length(sid3), seconds_in_wait desc;

##########

blocking_sessions.sql

set linesize 300

select B.USERNAME ||' ('||B.SID||','||B.SERIAL#||',@'||B.INST_ID||') is Currently '||B.STATUS||' for last '||B.LAST_CALL_ET||' Sec and it''s BLOCKING user '|| W.USERNAME|| ' ('|

|W.SID||','||W.SERIAL#||',@'||W.INST_ID||')' from

(select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W,

(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B

where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID;

############

active_sessions.sql

select

(select 'Active Sessions in MY DB Inst 1 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=1 and sql_id is not null ) as Active_Sessions

from dual

union

select

(select 'Active Sessions in MY DB Inst 2 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=2 and sql_id is not null ) as c2

from dual

union

select

(select 'Active Sessions in MY DB Inst 3 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=3 and sql_id is not null ) as c2

from dual

union

select

(select 'Active Sessions in MY DB Inst 4 : '||count(*) from gv$session where status='ACTIVE' and INST_ID=4 and sql_id is not null ) as c2

from dual;

############

TOPSQL.sql

select sql_id,username,count(*) from v$session where type='USER' and status='ACTIVE' group by sql_id,username order by 3;

##########

Userconnections.sql

select USERNAME,status,count(*) from v$session where username not in ('SYSTEM','SYS') and username is not null group by USERNAME,status order by 2;

##########

CPU ::

for ((;;)) do sar 2 30; sleep 10; done

##########

Memory

free -m -s 5

#########