https://gavinsoorma.com.au/knowledge-base/goldengate-integrated-capture-using-downstream-mining-database/
How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy) (Doc ID 438580.1)
https://ioweb.oneadr.net/itssp/#requests/list
\\Dcd03hh-evs10\cmcc-dk-db-oracle\Projects\TNSnames
export ORACLE_HOME=/u01/oem13_4
./sqlplus dbsnmp/Pony2last4ever@'(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=dbz-amhap-scan)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME =amhp1a)))'
it help
https://ninaa.oneadr.net/portal/internalportal/appmanager/nordeaportal/desktop/Content/web/s-134246/w/1526358?language=en
3-30146982481
default password for SOPSETUP user is cmio4you
Non-prd - root :- NdbExaMaster2last4ever
prod - root :- PdbExaMaster2last4ever
https://ioweb.oneadr.net/heimdal/#home
Arsenal2021 excel file pass
\\Dcd03hh-evs10\cmcc-dk-db-oracle\Projects tns entry globaly
rman target "'/@rman_s0094t1o as sysbackup'" catalog [S0094T]@rmanproxy
select sys.database_name from dual; to find db name
nohup sqlplus "/ as sysdba" @/me01/oracle/part.sql >> /me01/oracle/part.log &
/me01/drift/backup/log backup
tablespace growth
https://blog.pythian.com/how-to-retrieve-growth-history-for-oracle-tablespaces/
https://rcirogers-my.sharepoint.com/personal/deepak_kumar2_rci_rogers_ca/_layouts/15/onedrive.aspx?view=3
clouds console
https://console.us-ashburn-1.oraclecloud.com/dbaas/dbsystems
ocirogers
nmdb
du -sh * | grep -i G
oracle---Welcome@123 47.150.56.204
mdcodhelxdb33-mg.rci.rogers.com
Deepak@123
Rogers@12345
needs to run under root user
/u01/oracle/oraclegrid/11.2.0.4/bin/srvctl start filesystem -d /dev/asm/rfsprodtemp-53 -n elxdb020
srvctl config filesystem -device /dev/asm/diag_vol-330
send TESTASM status
/opt/oracle/rssdba/scripts
sudo su - edworaclegg
set long 1000000
set longchunksize 1000000
set lines 1000
set pages 80
set trim on
set trimspool on
set echo off
set feedback off
spool /tmp/report_sql_monitor.htm
select
dbms_sqltune.report_sql_monitor(
sql_id => '847ahztscj4xw',
type => 'html',
report_level => 'all') as report
from
dual;
spool off
DK10966282 identified by Dkrogers123; s
/u01/app/oracle/rssdba/scripts/bcv/CRMBCV1_CRMBCV2_start_refresh.sh
OUTPUT of command listener_scan
genesis tool access link
srvctl status service -db
opatch lspatches last patch appliyed
HCLRogersSSManagers@rci.rogers.com
https://outlook.office.com/owa/
RSO-Enterprise Monitoring
dkumar5/Rogers@123
http://ehpap151:8080/amc_ehpap151/servlet/AmcGateway
DBSNMP/btoempass
rcirs420.rogers.com
./crsctl stat res -t | grep -i 327 | awk '{print $1}'
nohup expdp \'/ as sysdba\'
pbrun rights
pbsu - dbprdabp
tcsh
dpath
conn HFTREP/HFTREP_PRD1@ABPPRD
Connected.
SQL> set timing on
SQL> exec dbms_refresh.refresh('HFTREPABPPRD_GRP');
SELECT '/RBT/wadm/hotfix/HOTFIX/'
|| product || '/Release_' || release || '/HF_' || unique_id
FROM hotfix_mng
WHERE unique_id = &HF_ID;
select sys_context('userenv','current_schema') "user" from dual;
system/Prdmanag3R
/u01/data/ogg/
uuencode ashrpt_1.html ashrpt_1.html |mail deepak.kumar2@rci.rogers.com
/usr/local/bin/pbsu - gg
/opt/gg
ggsci
*************************************
/*+ opt_param('optimizer_features_enable','11.2.0.4')*
select /*+ OPT_PARAM('_OPTIMIZER_USE_FEEDBACK' 'FALSE') */
ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
ALTER SESSION SET optimizer_mode = FIRST_ROWS_10
ALTER SESSION SET optimizer_index_cost_adj = 5
ALTER SESSION SET optimizer_index_caching = 95
cursor_sharing = FORCE
session_cached_cursors = 50
to put hint /*+ parallel(a,8) */
to explain
Alter session set current_schema=okm_im;
explain plan for
VAR b1 NUMBER;
EXEC :b1 := 10;
**************************
Dl in snow - prod dba
RSO-PROD APP DBA
----to wait event
select p.limit
from dba_profiles p, dba_users u
where p.resource_name = 'IDLE_TIME' and p.profile = u.profile and u.username = '...';
select event, state, count(*) from v$session_wait group by event, state order by 3 desc;
----to see wait and no of session and which is taking cup-----
select
count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM
v$session_wait
GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY
1 DESC, 2 DESC
/
----to taking cpu
select
count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM
gv$session
WHERE
type = 'USER'
AND status = 'ACTIVE'
GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY
1 DESC, 2 DESC
/
-- to find sql_hash_value
select sql_hash_value, count(*) from v$session
where status = 'ACTIVE' group by sql_hash_value order by 2 desc;
select sql_text,users_executing from gv$sql where sql_hash_value=
select sql_text,users_executing from v$sql where sql_id ='240h675gxhw01';
select sql_text,users_executing from v$sql where sql_id ='22js197kafd26';
set pages 100
set lines 160
col status for a20
col WRL_PARAMETER for a40
col WRL_TYPE for a7
select * from gv$encryption_wallet
#################session list ###############
SELECT OSUSER,SID,PREV_SQL_ID,sql_id,status,to_char(LOGON_TIME,'HH24:MI DD-MON-YY') "login" from gv$session WHERE type <>'BACKGROUND' and osuser='prdarm'
order by 6
****************************************
wait ----s
select a.sid,b.osuser,b.username,b.SQL_HASH_VALUE,
decode(command,0,'None',2,'Insert',3,'Select',
6,'Update',7,'Delete',10,'Drop Index',12,'Drop Table',
45,'Rollback',47,'PL/SQL',command) command,
a.event,a.p1,a.p2,a.p3,a.p1raw,b.state,b.wait_time
from gv$session_wait a,gV$session b
where b.sid=a.sid
and (a.sid>10 and a.event not in('SQL*Net message from client',
'SQL*Net message to client')
or (a.sid<=10 and a.event not in ('rdbms ipc message','smon timer',
'pmon timer','SQL*Net message from client','SQL*Net break/reset to client','rdbms ipc message','gcs remote message')))
order by decode(event,'pipe get','A',event),p1,p2
Run the following query to find out the p1,p2 and p3 of a session causing buffer busy wait
sql> select p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait where event = 'buffer busy waits';
After that running the following query to find the segment causing buffer busy wait:-
sql> select owner,segment_name,segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1;
**************************
--long session
select * from v$session_longops where totalwork<> sofar
*******************new query for wait event ***********************
select /*+ ordered */
substr(n.name, 1, 29) event,
t0,
t1,
t2,
t3,
t4,
t5,
t6,
t7,
t8,
t9
from
sys.v_$event_name n,
(select event e0, count(*) t0 from sys.v_$session_wait group by event),
(select event e1, count(*) t1 from sys.v_$session_wait group by event),
(select event e2, count(*) t2 from sys.v_$session_wait group by event),
(select event e3, count(*) t3 from sys.v_$session_wait group by event),
(select event e4, count(*) t4 from sys.v_$session_wait group by event),
(select event e5, count(*) t5 from sys.v_$session_wait group by event),
(select event e6, count(*) t6 from sys.v_$session_wait group by event),
(select event e7, count(*) t7 from sys.v_$session_wait group by event),
(select event e8, count(*) t8 from sys.v_$session_wait group by event),
(select event e9, count(*) t9 from sys.v_$session_wait group by event)
where
n.name != 'Null event' and
n.name != 'null event' and
n.name != 'rdbms ipc message' and
n.name != 'pipe get' and
n.name != 'virtual circuit status' and
n.name not like '%timer%' and
n.name not like '%slave wait' and
n.name not like 'SQL*Net message from %' and
n.name not like 'io done' and
n.name != 'queue messages' and
e0 (+) = n.name and
e1 (+) = n.name and
e2 (+) = n.name and
e3 (+) = n.name and
e4 (+) = n.name and
e5 (+) = n.name and
e6 (+) = n.name and
e7 (+) = n.name and
e8 (+) = n.name and
e9 (+) = n.name and
nvl(t0, 0) + nvl(t1, 0) + nvl(t2, 0) + nvl(t3, 0) + nvl(t4, 0) +
nvl(t5, 0) + nvl(t6, 0) + nvl(t7, 0) + nvl(t8, 0) + nvl(t9, 0) > 0
order by
nvl(t0, 0) + nvl(t1, 0) + nvl(t2, 0) + nvl(t3, 0) + nvl(t4, 0) +
nvl(t5, 0) + nvl(t6, 0) + nvl(t7, 0) + nvl(t8, 0) + nvl(t9, 0)
/
select event, state, count(*) from gv$session_wait group by event, state order by 3 desc;
WAIT EVENT
SELECT w.sid, w.event, w.seconds_in_wait
FROM GV$SESSION s, DBA_DATAPUMP_SESSIONS d, GV$SESSION_WAIT w
WHERE s.saddr = d.saddr AND s.sid = w.sid;
****************************
****************************
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','0hvuj9yz5g17p')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
see last hash value
UNDEFINE 1 2 3 original_sql_id modified_sql_id plan_hash_value profile_name
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('0hvuj9yz5g17p ' )
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('0hvuj9yz5g17p ')
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs,sum(buffer_gets)/sum(executions) avg_bu_ex,sum(executions) ex
FROM gv$sql
WHERE sql_id = TRIM('0hvuj9yz5g17p ')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs,sum(buffer_gets_total)/sum(executions_total) avg_bu_ex,sum(executions_total) ex
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('0hvuj9yz5g17p ')
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs,round(nvl(m.avg_bu_ex,a.avg_bu_ex)) avg_bu_ex,nvl(m.ex,a.ex) ex
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
avg_et_secs NULLS LAST
/
UNDEFINE 1 2 3 original_sql_id modified_sql_id plan_hash_value profile_name
select sid, SERIAL#,OPNAME,SOFAR,TOTALWORK,USERNAME,TIME_REMAINING/(60*60) FROM gv$session_longops where sofar<totalwork and sql_id='cpzv5su9rzs2h'
##################################################
SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_194255';
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_194255',task_owner => 'SYS', replace => TRUE) -- WITH force_match=>true);
— Drop the profile:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_03663155293b0000');
Fatch data from
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('1k9jmy68yb9g9','1k9jmy68yb9g9')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3;
prompt enter start and end times in format DD-MON-YYYY [HH24:MI]
column sample_end format a21
select to_char(min(s.end_interval_time),'DD-MON-YYYY DY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),1) msec_exec
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.plan_hash_value=trim('&plan_hash_value.')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
and s.end_interval_time >= to_date(trim('&start_time.'),'dd-mon-yyyy hh24:mi')
and s.begin_interval_time <= to_date(trim('&end_time.'),'dd-mon-yyyy hh24:mi')
and substr(to_char(s.end_interval_time,'DD-MON-YYYY DY HH24:MI'),13,2) like '%&hr24_filter.%'
group by s.snap_id
, q.sql_id
, q.plan_hash_value
order by s.snap_id, q.sql_id, q.plan_hash_value
#########################################
lock
SELECT inst_id,blocking_session,FINAL_BLOCKING_SESSION,serial#,SQL_ID,seconds_in_wait,event FROM gv$session WHERE blocking_session IS NOT NULL and seconds_in_wait>=360;
row level locks
set linesize 150;
set head on;
col sid_serial form a13
col ora_user for a15;
col object_name for a35;
col object_type for a10;
col lock_mode for a15;
col last_ddl for a25;
col status for a10
SELECT l.session_id||','||v.serial# sid_serial,
l.ORACLE_USERNAME ora_user,
o.object_name,
o.object_type,
DECODE(l.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR(l.locked_mode)
) lock_mode,
o.status,
to_char(o.last_ddl_time,'DD-MON-YYYY HH:MI:SS AM') last_ddl
FROM dba_objects o, gv$locked_object l, gv$session v
WHERE o.object_id = l.object_id and o.object_name='ARRE_CHARGES_ADJUSTMENTS'
and l.SESSION_ID=v.sid
order by 2,3;
SELECT s1.username
|| '@'
|| s1.machine
|| ' ( SID='
|| s1.SID
|| ' ) is blocking '
|| s2.username
|| '@'
|| s2.machine
|| ' ( SID='
|| s2.SID
|| ' ) ' AS blocking_status
FROM gv$lock l1,
gv$session s1,
gv$lock l2,
gv$session s2
WHERE s1.SID = l1.SID
AND s2.SID = l2.SID
AND l1.BLOCK = 1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2;
select username,INST_ID,status,type,machine,to_char(logon_time,'dd-mon-yyyy hh12:mi:ssam')login,osuser,module from gv$session where OSUSER='orarom';
##
open curosr
select stat.value, sess.username, sess.sid, sess.serial# from
v$sesstat stat, v$statname b, v$session sess where stat.statistic# = b.statistic# and
sess.sid=stat.sid and b.name = 'opened cursors current';
select inst_id, resource_name, current_utilization, max_utilization,limit_value from gv$resource_limit where resource_name in ( 'sessions', 'processes');
SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM gv$sesstat a, gv$statname b, gv$parameter p WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;
lock
select inst_id,decode(request,0,'Holder: ',' Waiter: ')||sid sess,id1,id2, lmode, request, type
from gv$lock where (id1, id2, type) in (select id1, id2, type from gv$lock where request>0) order by id1,request;
spid
select a.username,a.sid, a.logon_time, a.serial#,a.process,b.pid,a.program,b.spid, a.status,a.last_call_et, a.sql_id
from v$session a , v$process b where a.paddr(+)=b.addr and a.sid=&sid;
SELECT s.sid ,s.username,s.paddr,s.LOGON_TIME,round((s.LAST_CALL_ET/1200),2),s.program,s.osuser,s.process,
s.machine,s.terminal,p.spid,s.STATUS FROM gv$session s, gv$process p
WHERE s.username IS NOT NULL AND (LAST_CALL_ET / 60) > 720
AND s.STATUS = 'INACTIVE'
and s.paddr=p.addr
order by s.LAST_CALL_ET desc
SELECT s.sid ,s.LOGON_TIME,round((s.LAST_CALL_ET/1200),2),s.osuser,s.process,
s.machine,p.spid,s.STATUS FROM gv$session s, gv$process p
WHERE s.username IS NOT NULL AND (LAST_CALL_ET / 60) > 720
AND s.STATUS = 'INACTIVE'
and s.paddr=p.addr
order by s.LAST_CALL_ET desc
SELECT '!kill -9 '||p.spid FROM gv$session s, gv$process p
WHERE s.username IS NOT NULL AND (LAST_CALL_ET / 60) > 360
AND s.STATUS = 'INACTIVE'
and s.paddr=p.addr
order by s.LAST_CALL_ET desc
********************************************************
COLUMN username FORMAT A30
COLUMN sid FORMAT 999999999
COLUMN serial# FORMAT 999999999
COLUMN "cpu usage (seconds)" FORMAT 999999999.0000
COLUMN sql_id FORMAT 999999999
SELECT
s.username,program,
t.sid,sql_id,
s.serial#,
SUM(VALUE/100) as "cpu usage (seconds)"
FROM
gv$session s,
gv$sesstat t,
gv$statname n
WHERE
t.STATISTIC# = n.STATISTIC#
AND
NAME like '%CPU used by this session%'
AND
t.SID = s.SID
AND
s.status='ACTIVE' and VALUE >9000
AND
s.username is not null and s.username not in ('SYS','SYSTEM')
GROUP BY username,t.sid,s.serial#,program,sql_id
/
***************New *************************
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
col program format a40
COLUMN "cpu usage (seconds)" FORMAT 999,999,999.0000
SELECT
s.username,
t.sid,
s.serial#,
s.osuser,
s.program,
s.sql_id,
SUM(VALUE/100) as "cpu usage (seconds)"
FROM
Gv$session s,
Gv$sesstat t,
Gv$statname n
WHERE
t.STATISTIC# = n.STATISTIC#
AND
NAME like '%CPU used by this session%'
AND
t.SID = s.SID
AND
s.status='ACTIVE'
AND
s.username is not null
GROUP BY username,t.sid,s.serial#,s.osuser,s.program,s.sql_id
order by 7 desc;
#############################################
9 iiiiii
select * from (
select opname, target, sofar, totalwork,
units, elapsed_seconds, message
from v$session_longops order by start_time desc)
where rownum <=1;
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
60 secound long running
select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from gv$session s
join gv$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece;
####################################
free shared pool
Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB
From V$sgastat
Where Name Like '%free memory%';
memory is allocated to the SGA components in the database you can use:
COLUMN component FORMAT A30
SELECT component,
ROUND(current_size/1024/1024) AS current_size_mb,
ROUND(min_size/1024/1024) AS min_size_mb,
ROUND(max_size/1024/1024) AS max_size_mb
FROM v$sga_dynamic_components
WHERE current_size != 0
ORDER BY component;
##############################################CPU used by sql query ############
SELECT s.username, t.sid, s.serial#, s.osuser,s.program,s.sql_id, SUM(VALUE/100) as "cpu usage (seconds)" FROM Gv$session s,
Gv$sesstat t, Gv$statname n WHERE t.STATISTIC# = n.STATISTIC# AND NAME like '%CPU used by this session%' AND
t.SID = s.SID AND s.status='ACTIVE' AND s.username is not null GROUP BY username,t.sid,s.serial#,s.osuser,
s.program,s.sql_id order by 7 desc
####without back ground process
SELECT s.username, t.sid, s.serial#, s.osuser,s.program,s.sql_id, SUM(VALUE/100) as "cpu usage (seconds)" FROM Gv$session s,
Gv$sesstat t, Gv$statname n WHERE t.STATISTIC# = n.STATISTIC# AND NAME like '%CPU used by this session%' AND
t.SID = s.SID AND s.status='ACTIVE' AND s.username is not null and type <> 'BACKGROUND'
GROUP BY username,t.sid,s.serial#,s.osuser,s.program,s.sql_id order by 7 desc
last day session details
select PROGRAM ,module,machine, count(*) from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time > to_date('2018-11-24:12:45:00','yyyy-mm-dd:hh24:mi:ss') and sample_time < to_date('2018-11-24:16:00:00','yyyy-mm-dd:hh24:mi:ss') group by PROGRAM,module,session_id,machine,USER_ID order by 2;
5 min longer
SELECT
S.INST_ID, S.SID, S.SERIAL#, S.STATUS, P.SPID, S.PROCESS, RTRIM(S.USERNAME) AS USERNAME, RTRIM(S.MACHINE) AS MACHINE, RTRIM(S.OSUSER) as OSUSER, RTRIM(S.PROGRAM) AS PROGRAM,
S.SQL_ID, S.LOGON_TIME, ROUND((SYSDATE-S.SQL_EXEC_START)*24,2) HOURS_RUNNING, S.SQL_EXEC_START
from gv$session s , gv$process p
where s.paddr = p.addr and (SYSDATE-s.sql_exec_start)*24 > (5/60)
order by s.SID, s.sql_exec_start desc;
#############################################
query to show how long each execution ran:
select
SQL_EXEC_ID,
to_char(SQL_EXEC_START,'YYYY-MM-DD HH24:MI:SS') sql_start,
to_char(min(sample_time),'YYYY-MM-DD HH24:MI:SS') first_sample,
to_char(max(sample_time),'YYYY-MM-DD HH24:MI:SS') last_sample,
max(sample_time)-min(sample_time) elapsed_seconds
from V$ACTIVE_SESSION_HISTORY a
where
sample_time
between
to_date('20-OCT-2016 17:00:00','DD-MON-YYYY HH24:MI:SS')
and
to_date('20-OCT-2016 17:30:00','DD-MON-YYYY HH24:MI:SS') and
sql_id='0gt3cjptk68vw'
group by SQL_EXEC_ID,SQL_EXEC_START
order by SQL_EXEC_START,SQL_EXEC_ID;
select ss.sql_id,
ss.plan_hash_value,
sn.END_INTERVAL_TIME,
ss.executions_delta,
ELAPSED_TIME_DELTA/(executions_delta*1000) "Elapsed Average ms",
CPU_TIME_DELTA/(executions_delta*1000) "CPU Average ms",
IOWAIT_DELTA/(executions_delta*1000) "IO Average ms",
CLWAIT_DELTA/(executions_delta*1000) "Cluster Average ms",
APWAIT_DELTA/(executions_delta*1000) "Application Average ms",
CCWAIT_DELTA/(executions_delta*1000) "Concurrency Average ms",
BUFFER_GETS_DELTA/executions_delta "Average buffer gets",
DISK_READS_DELTA/executions_delta "Average disk reads",
ROWS_PROCESSED_DELTA/executions_delta "Average rows processed"
from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn
where ss.sql_id ='3d65d8hbr6mvu'
and ss.snap_id=sn.snap_id
and executions_delta > 0
and ss.INSTANCE_NUMBER=sn.INSTANCE_NUMBER
order by ss.snap_id,ss.sql_id;
###############Hidden parameter
relocate service
srvctl relocate service -d USMPRD -s USMPRD_APP -i USMPRD2 -t USMPRD1
select nam.ksppinm NAME,nam.ksppdesc DESCRIPTION, val.KSPPSTVL from
x$ksppi nam, x$ksppsv val
where nam.indx = val.indx and nam.ksppinm like '%resource%'
#########################
select local_tran_id, global_tran_id, state, mixed, advice from dba_2pc_pending order BY local_tran_id
set pagesize 999;
set feedback off;
set wrap on;
column local_tran_id format a22 heading 'Local Txn Id'
column global_tran_id format a50 heading 'Global Txn Id'
column state format a16 heading 'State'
column mixed format a5 heading 'Mixed'
column advice format a5 heading 'Advice'
select
local_tran_id,
global_tran_id,
state,mixed,advice
from
dba_2pc_pending
order
by local_tran_id;
select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared'
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.1.116925')
****************************
gg dml count
select TABLE_NAME, sum(DELETES), to_char(TIMESTAMP,'DD-MON-YYYY') from DBA_TAB_MODIFICATIONS
where TABLE_NAME IN
('AR1_INVOICE','BL1_CUSTOMER','BL1_CYCLE_CONTROL','BL1_BILL_STATEMENT','AR1_ACCOUNT','AR1_PAYMENT_DETAILS','AR1_PAYMENT_ACTIVITY','AR1_PAYMENT','AR1_PAY_CHANNEL') and to_char(TIMESTAMP,'DD-MON-RRRR')='31-OCT-2019' group by to_char(TIMESTAMP,'DD-MON-YYYY'),TABLE_NAME;
col Object format a25
col osuser format a20
set linesize 999
col OSUSER format a10
col login_time format a25
SELECT SUBSTR(b.owner, 1, 8)||'.'||SUBSTR(b.object_name, 1, 18) "Object"
, b.object_type object_type,c.program , DECODE(a.locked_mode , 0, 'NONE'
, 1, 'NULL'
, 2, 'ROW SHARE'
, 3, 'ROW EXCLUSIVE'
, 4, 'SHARE'
, 5, 'SHARE ROW EXCLUSIVE'
, 6, 'EXCLUSIVE') locked_mode
, a.session_id "DB Sid"
, c.serial# "Serial#"
,c.sql_id "SQL_ID"
,c.status
,a.oracle_username "Locker"
, a.os_user_name osuser
, TO_CHAR(c.logon_time,'YYYY/MM/DD HH24:MI:SS') logon_time
FROM gv$locked_object a , dba_objects b , gv$session c
WHERE a.object_id = b.object_id
AND a.session_id = c.sid and object_name=' STN_BILLING_ACCOUNT_1205'
ORDER BY b.owner , b.object_type , b.object_name
/
APP_V21.STG_GL_DETAILS
Remove trace file
find /opt/oracle/product/asm/rdbms/audit -type f -name "*" -mtime +30 -exec rm {} \;
find /u01/app/oracle/diag/rdbms/bdwprod/BDWPROD3/trace -type f -name "*trc" -mtime +15 -exec ls -ltr {} \;
***************** find out session login time************
select p.PID,p.SPID,s.SID,s.sql_id , to_char(LOGON_TIME,'HH24:MI DD-MON-YY') "login",event,status from gv$process p,gv$session s where s.paddr = p.addr and p.spid = &PID
To Find PID with sql_id and sql text
select ses.sid SID,sqa.SQL_TEXT SQL from gv$session ses, gv$sqlarea sqa, gv$process proc where ses.paddr=proc.addr and ses.sql_hash_value=sqa.hash_value
and proc.spid =&pid;
######################
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
DEGREE "Degree", REQ_DEGREE "Req Degree"
FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;
SELECT s.osuser,s.username ,s.module,p.spid ,p.pid,s.status,to_char(s.LOGON_TIME,'HH24:MI DD-MON-YY') "login" from gv$session s ,gv$process p WHERE
s.status='INACTIVE' and s.username not in ('SYS','GGS') and s.paddr = p.addr and s.osuser in ('Prdabp4','prdabp8','prdabp9','prdabp10')
select sql_id,sid,to_char(logon_time,'DD-MON-YY HH24:MI:SS') FROM gv$session WHERE SQL_ID IN('as4m1g1kpmuu0')order by logon_time DESC
SELECT OSUSER,SID,PREV_SQL_ID,sql_id,status,to_char(LOGON_TIME,'HH24:MI DD-MON-YY') "login" from gv$session WHERE
sql_id='ftf2kfqctkack' and to_char(LOGON_TIME,'HH24:MI DD-MON-YY')>='12:00 13-JUN-17'
select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID
SELECT OSUSER,SID,PREV_SQL_ID,sql_id,status,to_char(LOGON_TIME,'HH24:MI DD-MON-YY') "login" from gv$session WHERE sid=1173 and
sql_id='ftf2kfqctkack' and to_char(LOGON_TIME,'HH24:MI DD-MON-YY')>='11:47 10-FEB-17'
*************************Active session history ******* output
select count(*),EVENT from v$session where status='INACTIVE' and EVENT in ('cursor: pin X','SQL*Net message from client') group by EVENT;
select sample_time, session_state, event, sql_id
from v$active_session_history where sample_time between to_date('09-NOV-16 4.15.02 PM','dd-MON-yy hh:mi:ss PM') and to_date('09-NOV-16 06.15.02 PM','dd-MON-yy hh:mi:ss PM') and sql_id ='2vrum6mxvz67b' order by 1;
SELECT sql_id,executions, buffer_gets/executions "Gets/Exec",sql_id FROM gV$SQLAREA WHERE sql_id in('fnzavy7b82gy2'),'g8bgjq6rgr2w3')
col SQL_FULLTEXT format a80
set linesize 999
select inst_id, sql_fulltext,sql_id,loaded_versions, executions, first_load_time,rows_processed, hash_value,cpu_time,elapsed_time,to_char(last_active_time,'dd-mon-yyyy hh24:mi:ss') from gv$sql where sql_id in ('30bv8hzw7ntjw');
*******************************
select inst_id, resource_name, current_utilization, max_utilization,limit_value from gv$resource_limit where resource_name in ( 'sessions', 'processes');
how to find trace file name with spid
select spid ,pid ,osuser from gv$process , gv$session where gv$process.ADDR=gv$session.paddr and gv$session.sid=428 and gv$session.serial#=11584;
select p.PID,p.SPID,s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = &SESSION_ID
/ ..
command se pid nikalna
############################tracing enable
then oradebug setospid 47307
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug tracefile_name
cat listener_scan3.log |grep unknown |grep 04-JUN-2019
top 5 high cpu use
UNIX95= ps -e -o pcpu,pid | sort -n -r |grep -v "%CPU"|head -5
ps aux | grep -i XBS|awk '{print $6/1024 " MB\t\t" $1, $11}' |grep -v grep
col OSUSER for a10
col username for a20
col machine for a20
SELECT s.username ,s.module,status,to_char(s.LOGON_TIME,'HH24:MI DD-MON-YY') "login" from gv$session s WHERE
s.status='INACTIVE' and s.username not in ('SYS','GGS') and s.osuser in ('Prdabp4','prdabp8','prdabp9','prdabp10')
select p.PID,p.SPID,s.SID,s.serial# ,s.inst_id,status from gv$process p,gv$session s where s.paddr = p.addr and sql_id='
select p.PID,p.SPID,s.SID from gv$process p,gv$session s where s.paddr = p.addr and s.sid = &SESSION_ID
select p.PID,p.SPID,s.SID,s.serial# ,s.inst_id,status from gv$process p,gv$session s where s.paddr = p.addr and p.SPID='20089
2
col SQL_FULLTEXT format a80
set linesize 999
select sql_fulltext,sql_id, executions, first_load_time,rows_processed, hash_value,cpu_time,elapsed_time,last_active_time
from gv$sql where sql_id in ('2m59ycy5h9xwp');
select inst_id,sql_id,loaded_versions, executions,rows_processed,cpu_time,elapsed_time,last_active_time
from gv$sql where sql_id in ('22js197kafd26')
col SQL_FULLTEXT format a80
set linesize 999
select sql_id, executions, first_load_time,rows_processed from gv$sql where sql_id in ('5c6yhra6ztr76');
SELECT DISTINCT sql_id,plan_hash_value, TIMESTAMP, to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI') FROM dba_hist_sql_plan WHERE sql_id='&sql_id' ORDER BY TIMESTAMP;
select CPU_TIME,ELAPSED_TIME,DISK_READS,BUFFER_GETS from gv$sql where sql_id='d7wc3snmcku7t';
******************************
logical and physical reads
set linesize 132
rem
ttitle 'User Session Statistics'
rem
col pid format 9999 heading 'PID'
col spid format a6 heading 'SERVER|PID'
col sid format 9999 heading 'SID'
col serial# format 99999 heading 'SERIAL'
col process format a6 heading 'CLIENT|PID'
col osuser format a8 heading 'OS|USERNAME'
col username format a9 heading 'ORACLE|USERNAME'
col log_per_sec format 999999 heading 'LOG|PER|SEC'
col logical format b9999999999 heading 'LOGICAL|READS'
col phy_per_sec format b9999 heading 'PHY|PER|SEC'
col physical_reads format b999999999 heading 'PHYSICAL|READS'
col audsid format b9999999 heading 'AUDIT|SESSION'
col program format a32 heading 'PROGRAM NAME'
col module format a32 heading 'MODULE'
col logon_time format a8 heading 'LOGON|TIME'
col duration format a8 heading 'DURATION'
col last_call_min format 9999 heading 'LAST|CALL|MIN'
col status format a1 heading 'S'
col hide_sql_hash_value noprint new_value _sql_hash_value
rem
set pages 500
select s.process,
p.spid,
/*
p.pid,
*/
s.sid,
s.serial#,
s.osuser,
s.username,
( i.block_gets + i.consistent_gets ) /
( ( sysdate - s.logon_time ) * 86400 ) log_per_sec,
i.block_gets + i.consistent_gets logical,
physical_reads /
( ( sysdate - s.logon_time ) * 86400 ) phy_per_sec,
i.physical_reads,
/*
s.audsid,
*/
to_char( trunc(sysdate) + ( sysdate - s.logon_time ), 'hh24:mi:ss' ) duration,
s.last_call_et/60 last_call_min,
decode( s.status, 'ACTIVE', '*', 'INACTIVE', null, 'KILLED', 'K', '?' ) status,
decode( s.action, 'Concurrent Request', 'CM/' || s.module, s.module ) module,
s.sql_hash_value hide_sql_hash_value
from gv$process p, gv$session s, gv$sess_io i
where i.sid = s.sid
and type <> 'BACKGROUND'
and s.paddr = p.addr
and s.sid = &sid;
********************************************************************
to create script of kill session
set lines 200 pages 1000
col OBJECT_NAME for a50
every 30 min
select 'alter system kill session '||''''||sid||','||serial#||''''||' immediate ;'
from v$session where username ='CRAMER_COMMON' and osuser='prdarm' and LOGON_TIME < SYSDATE - 30/1440 AND type <>'BACKGROUND' ;
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||'''immediate;' from gv$session where sql_id='b96v1m08hnppz'
select 'alter system kill session '''|| vs.sid|| ',' || vs.SERIAL# ||''' immediate;',d_o.object_name from dba_objects d_o
inner join v$session vs on d_o.object_id = vs.plsql_entry_object_id
union all select 'alter system kill session '''|| vs.sid|| ',' || vs.SERIAL# ||''' immediate;',d_o.object_name from dba_objects d_o
inner join gv$session vs on d_o.object_id = vs.plsql_object_id and d_o.object_name like 'VW_USAGE_BILLED %';
*************************
Code Listing 7: Getting row lock information from the Active Session History archive
select sample_time, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,
dbms_ROWID.ROWID_create ( 1,o.data_object_id, current_file#,
current_block#
from dba_hist_active_sess_history s, dba_objects o
where sample_time between
to_date('05-APR-17 13.30.02 AM','dd-MON-yy hh:mi:ss PM')
and to_date('05-APR-17 14.30.02 AM','dd-MON-yy hh:mi:ss PM')
and event = 'enq: TX - row lock contention'
and o.data_object_id = s.current_obj#
order by 1,2;
SELECT C.SQL_TEXT,
B.NAME,
COUNT(*),
SUM(TIME_WAITED)
FROM v$ACTIVE_SESSION_HISTORY A,
v$EVENT_NAME B,
v$SQLAREA C
WHERE A.SAMPLE_TIME BETWEEN '05-APR-17 03:30:00 AM' AND
'17-APR-16 04:59:00 AM' AND
A.EVENT# = B.EVENT# AND
A.SQL_ID = C.SQL_ID
GROUP BY C.SQL_TEXT, B.NAME
******************************Sandeep query****************************
set lines 200 pages 1000
col OBJECT_NAME for a50
select 'CALLED PLSQL', vs.username, d_o.object_name,vs.sid,vs.SERIAL# from dba_objects d_o
inner join
gv$session vs on d_o.DATA_OBJECT_ID = vs.plsql_entry_object_id
union all select 'CURRENT PLSQL', vs.username, d_o.object_name,vs.sid,vs.SERIAL# from dba_objects d_o
inner join gv$session vs on d_o.DATA_OBJECT_ID = vs.plsql_object_id;
********************
gather schema stat
SELECT *
FROM gv$session_longops
WHERE opname LIKE '%gather%' AND time_remaining != 0
ORDER BY Sid
**************
table last analyzed status RCUSGO4"."USAGE_22_B
select table_name,to_char(LAST_ANALYZED,'DD/MM/YYYY HH24:MI:SS') from dba_tables where owner='APP_CHATR' and table_name='USAGE'
and trunc(LAST_ANALYZED) = trunc(sysdate) and table_name
******************
ODS.CS_ACCT_SEGMENT_MONTH_END
APP_HW_IBRO.FACTTRANSACTION_COA_INT1_STG
ODS.CS_ACCT_SEGMENT
****************************************************
log running query
COLUMN sid FORMAT 9999999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial# and sl.sql_id='0bdf0xdvy4fv9'
###########query more than 5 mniutes
select sid, serial#, username, status, machine, osuser,
to_char(sysdate-last_call_et/24/60/60,'hh24:mi:ss') started,
trunc(last_call_et/60) || ' mins, ' || mod(last_call_et,60) ||
' secs' dur,
(select distinct sql_text from v$sql where address = sql_address ) sql_text
from v$session
where username is not null
and last_call_et > 1*300
and status = 'ACTIVE' and username <> 'SYS';
*****************************************
working
set lines 200 pages 1000
col OBJECT_NAME for a50
select 'CALLED PLSQL', vs.username,vs.inst_id, d_o.object_name,vs.sid,vs.SERIAL# from dba_objects d_o
inner join gv$session vs on d_o.object_id = vs.plsql_entry_object_id
union all select 'CURRENT PLSQL', vs.username,vs.inst_id, d_o.object_name,vs.sid,vs.SERIAL# from dba_objects d_o
inner join gv$session vs on d_o.object_id = vs.plsql_object_id;
set lines 200 pages 1000
col OBJECT_NAME for a50
select 'alter system kill session '''|| vs.sid|| ',' || vs.SERIAL# ||''' immediate;',d_o.object_name from dba_objects d_o
inner join v$session vs on d_o.object_id = vs.plsql_entry_object_id
union all select 'alter system kill session '''|| vs.sid|| ',' || vs.SERIAL# ||''' immediate;',d_o.object_name from dba_objects d_o
inner join gv$session vs on d_o.object_id = vs.plsql_object_id and d_o.object_name like 'GCDM_PERMISSIONS_CSTONE_POST%';
#######################
AQ & MGW Health Check and Configuration Script (Doc ID 1193854.1)
****************Instance up time
SELECT TO_CHAR(logon_time, 'DD-MM-YYYY HH24:MI:SS')
FROM gv$session
WHERE program LIKE '%PMON%'
SELECT host_name,
instance_name,
TO_CHAR(startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time,
FLOOR(sysdate-startup_time) days
FROM sys.v_$instance;
********************************************
cdb and pdb
select name, open_mode from v$pdbs;
show con_name;
ALTER SESSION SET CONTAINER = PDB1
Script to find sql child count
select inst_id,sql_id,round(sum(S.SHARABLE_MEM)/(1024*1024),1) as Memory_MB,count(1) as CHILDCOUNT
from gv$sql s
group by inst_id,sql_id
order by 3 desc;
########################
file not moving
Script Need to run on :-ehpdb320 RMAF:-
select /*+ rule */ s.sid, s.serial#, s.program, s.machine, s.osuser, s.username, sql.sql_text, sql.rows_processed
from gv$session s, gv$access b, gv$sql sql
where b.owner ='RCMAFC' and
b.object like '%AC_PROCESSING_ACCOUN%' and
s.sid=b.sid AND
sql.address=s.sql_address and
s.machine='ehpap313'
order by object;
******************
select a.sid,a.serial#,b.sql_text,machine,username
from Gv$session a ,v$sqlarea b where a.sql_address=b.address and a.machine='ehpap313'
and a.username='RCMAFC' and b.sql_text like 'DECLARE%m%';
*********************fragmantion********************
col owner for a30
col table_name for a30
set linesize 200
set pages 300
select owner,table_name,round((blocks*8),2) "size (kb)" ,
round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and owner not in ('SYS','SYSTEM','OUTLN','SQLAB','REPADMIN','PERFSTAT','BACKUPADMIN','TOAD','DBSOL','DBA_TOOLS',
'DSPSUP','APPQOSSYS','DIP','ORACLE_OCM','TSMSYS','DRMUSER','ANONYMOUS','APPQOSSYS','DDSOPS','XDB','DRMDMO','DBSNMP')
order by 4 desc;
******
Table partition
COLUMN high_value FORMAT A20
SELECT table_name,
partition_name,
high_value,
num_rows
FROM user_tab_partitions WHERE TABLE_NAME='USAGE'
ORDER BY table_name, partition_name;
COLUMN high_value FORMAT A20
SELECT index_name,
partition_name,
high_value,
num_rows
FROM dba_ind_partitions where index_owner='NFDB'
ORDER BY index_name, partition_name;
set lines 300
col PARTITION_NAME format a20
col table_owner format a50
select table_owner||' - '||partition_name "Table Owner - Partition Name" from dba_tab_partitions where table_owner in ('CRCOSBTDV_PRD')
order by table_owner,partition_name
*************** alter sequence *****************
select LOCAL_TRAN_ID||' '||GLOBAL_TRAN_ID||' '||STATE||' '||MIXED||' '||ADVICE||' '||TRAN_COMMENT||' '||FAIL_TIME||' '||FORCE_TIME||' '||RETRY_TIME||' '||OS_USER||
' '||OS_TERMINAL||' '||HOST||' '||DB_USER||' '||COMMIT# from dba_2pc_pending where local_tran_ID='151.8.1182873'
select MARQUEE_OWNER.PRODUCT_KEY_SEQ.currval from dual to see current value
alter SEQUENCE MARQUEE_OWNER.PRODUCT_KEY_SEQ INCREMENT by 526; this is target value we set as it 12831+526 and we need to set 13357
alter SEQUENCE MARQUEE_OWNER.PRODUCT_KEY_SEQ INCREMENT by 1;
select MARQUEE_OWNER.PRODUCT_KEY_SEQ.nextval from dual; to generate next value manually
**************gahter table schema stat************
APP_MAESTRO_STG.INTRVNFCT
exec sys.dbms_stats.gather_table_stats (OWNNAME=>'ELAU_USAGE', TABNAME=>'USAGE_BILLED', PARTNAME=>'P_CC_23', estimate_percent=>SYS.DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>72,cascade=>true,no_invalidate=>false);
execute dbms_stats.gather_table_stats(ownname => 'APP_HW_IBRO', tabname =>'FACTTRANSACTION_COR_STG' , estimate_percent =>2,degree=>36);
execute dbms_stats.gather_table_stats(ownname => 'APP_MAESTRO_STG', tabname =>'INTRVNFCT' , estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree=>32);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS (ownname=>'SASRTDM',tabname=>'DIC_LOB',degree=>6, estimate_percent=>100,method_opt=>'for all columns size auto');
exec dbms_stats.gather_table_stats('ELA_SS','AR_HISTORY',partname=>'SYS_P1151',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, granularity=>'PARTITION',DEGREE=>32);
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'APP_CHATR',tabname=>'USAGE_DATA_ACCUMULATOR',granularity => 'SUBPARTITION',partname =>'USAGE_DATA_ACCUMULATOR_201704',method_opt=>'FOR ALL COLUMNS SIZE AUTO',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE=>TRUE,DEGREE=>32)
exec dbms_stats.gather_table_stats(ownname => 'APP_CHATR', tabname => 'USAGE_DATA_ACCUMULATOR', partname => 'USAGE_DATA_ACCUMULATOR_201705', estimate_percent => 2,method_opt=>'for all indexed columns size 1', granularity => 'ALL', degree => 24);
SELECT ' EXEC DBMS_STATS.unlock_table_stats((ownname=>'''||owner||''',tabname=>'''||table_name||''');' from deep_tab ;
APP_MAESTRO.LGCYPRODTXNGRFCT
exec sys.dbms_stats.gather_table_stats (ownname=>'MARQUEE',tabname=>'BIS_COD01_SUMMARY', estimate_percent=>SYS.DBMS_STATS.AUTO_SAMPLE_SIZE, options=>'GATHER AUTO', granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 1',degree=>72,cascade=>true,no_invalidate=>false);
TO SEE LOCK TABLE STAT
SELECT OWNER,TABLE_NAME,STALE_STATS,LAST_ANALYZED,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE
OWNER IN ('NIM_TRANS_PRD','NIM_EBS_PRD') AND STATTYPE_LOCKED ='ALL' ORDER BY 1,4
SELECT ' EXEC DBMS_STATS.unlock_table_stats((ownname=>'''||owner||''',tabname=>'''||table_name||''');' from deep_tab ;
EXEC DBMS_MVIEW.REFRESH ('MARQUEE_OWNER.ORDER_TASK_CHANGE_MV','C',parallelism=>32);
EXEC DBMS_MVIEW.REFRESH ('MARQUEE_OWNER.ORDER_VALID_SO_MV','C',parallelism=>32);
######################
col table_name for a50
col last_analyzed for 99999999
col STATUS for a10
select table_name,last_analyzed,num_rows,STATUS from dba_tables
where table_NAME in('DEFERRED_STG$','USER$','SEG$','OBJ$','TAB$')
and owner='SMTGT1CM'
select OWNER,index_name,last_analyzed,num_rows,STATUS from dba_indexes
where index_NAME in('IDX_DT_CAL_DATE','IX_PS_AGG_AGI_30M_011','PS_AGG_AGENT_IXN_30M_TBL','IDX_AGR_RESOURCE_NAME','PK_AG2_I_SESS_STATE_SUBHR','PK_DATE_TIME,DIM_AGENT_GROUP_CATEGORY','RESOURCE_STATE','PK_RESOURCE_STATE','PK_RSRC_ST_RSN','RESOURCE_STATE_REASON')
and owner='APP_CUST_PROFILE'
col index_name for a50
col last_analyzed for 99999999
col STATUS for a10
select index_name,last_analyzed,STATUS,table_name from dba_indexes
where table_NAME in('AR1_AGED_TRIAL_BALANCE')
and owner='RMSAPPO'
"CMP"."JRNY_RESI_ONBDG_INTRXN_STG
APP_V21.STG_GL_DETAILS
"ODS"."MV_PHYSICAL_ESN"
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,STALE_STATS from dba_TAB_STATISTICS where owner IN('USAGE_OUTCOLLECT'),'ODS_ARM','ODS_SOM','ODS_ABP','ODS_OMS','ODS_BMRT','ODS_CRM')
OWNER='ODS'and TABLE_NAME='BIS_COD01_BALANCES';
STALE_STATS='YES'
and OWNER='ODS'and TABLE_NAME='ADDRESS_NAME_LINK_CURRENT';
MARQUEE_OWNER.SALES_WORK_ORDER
select TABLE_NAME,STALE_STATS,LAST_ANALYZED,PARTITION_NAME,SUBPARTITION_NAME from dba_TAB_STATISTICS where OWNER='MARQUEE_OWNER' and TABLE_NAME in ('SALES_WORK_ORDER') AND STALE_STATS='YES';
***************to see partition
col owner for a4
col object_name for a15
col subobject_name for a20
col to_char(created,'DD.MM.YYYY HH24:MI:SS') for 99999999999999999999999999
select owner,object_name,subobject_name,to_char(created,'DD.MM.YYYY HH24:MI:SS') from dba_objects where object_type='TABLE PARTITION' and OWNER='SA' and object_name='TABLE_TOPIC'
select owner,object_name,subobject_name,to_char(created,'DD.MM.YYYY HH24:MI:SS') from dba_objects where object_type='TABLE PARTITION' and trunc(created) = trunc(sysdate);
select table_owner||' - '||partition_name "Table Owner - Partition Name"
from dba_tab_partitions where table_name in('LNP_LAND_CPC_MANAGEMENT_HIST')
and table_owner in ('ODS') AND partition_name IN('LNP_LLCMH_PQ062017','LNP_LLCMH_PQ062017','LNP_LLPR_PQ062017')
order by table_owner,partition_name
select table_owner||' - '||partition_name "Table Owner - Partition Name"
from dba_tab_partitions where table_owner in ('SA') AND partition_name IN('LNP_LLCMH_PQ062017','LNP_LLAPR_PM062017','LNP_LLPR_PQ062017')
order by table_owner,partition_name;
set lines 300
col PARTITION_NAME format a20
col table_owner format a50
select table_owner||' - '||partition_name "Table Owner - Partition Name"
from dba_tab_partitions
where table_owner in ('CRCOSBTDV_PRD')
order by table_owner,partition_name
set linesize 999
col PARTITION_NAME format a30
col table_owner format a15
col table_name format a30
select table_owner,table_name,PARTITION_NAME from DBA_TAB_PARTITIONS where table_owner='ORS_CRM' and table_name='&1' order by partition_name;
delete force archivelog all completed before 'sysdate -3' backed up 1 times to device type 'SBT_TAPE';
select owner, table_name, partition_name, object_type, stale_stats from dba_tab_statistics
WHERE owner='APP_MAESTRO'
and stale_stats='YES' and table_name='PNTRDLY'
####################
to check subpartition status
##################
select index_name name,partition_name partition,subpartition_name Subpartition,status
from all_ind_subpartitions i where I.INDEX_NAME='X_SERVICE_AGREEMENT_PK' and status ='UNUSABLE';
**********************
select index_name, partition_name, object_type, sample_size, last_analyzed, global_stats, STALE_STATS
from dba_ind_statistics where table_name in('JRNY_HUP_TRX_STG')
select table_name, partition_name, object_type, sample_size, last_analyzed, global_stats, STALE_STATS
from dba_tab_statistics where table_name in ('ENTERPRISE_ID_FULL) and owner='ODS_EBIZ';
BDWPROD1
set linesize 999
col PARTITION_NAME format a30
col table_owner format a15
col table_name format a30
select table_owner,table_name,PARTITION_NAME,GLOBAL_STATS from DBA_TAB_PARTITIONS where table_owner='ODS_CRM'
and table_name='TABLE_CLOSE_CASE ' order by partition_name;
To find local indexes use dba_part_indexes. The "locality" field from this view should be queried.
col owner for a20
col table_name for a30
col index_name for a30
col locality for a30
select owner, table_name, index_name, locality from dba_part_indexes;
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE;
crosscheck archivelog all;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
SET PAUSE ON
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
COLUMN scn FOR 999,999,999,999,999
COLUMN Incar FOR 99
COLUMN name FOR A25
COLUMN storage_size FOR 999,999,999,999
COLUMN guarantee_flashback_database FOR A3
SELECT
database_incarnation# as Incar,
scn,
name,
time,
storage_size,
guarantee_flashback_database
FROM
v$restore_point
ORDER BY 4
to see restore point time
db_flashback_retention_target
CREATE RESTORE POINT INC000003129995 GUARANTEE FLASHBACK DATABASE;
flashback database to restore point INC000003129995
select INSTANCE_NAME,HOST_NAME,STARTUP_TIME,NAME,OPEN_MODE from gv$database a,gv$instance b where a.INST_ID=b.INST_ID;
#################profile
ps -eo pcpu,%mem,cmd|sort -k2 -r
It gives the %cpu,%memory utilization of the processes in order of memory usage from highest to lowest.
##########################HP -UX
echo " Memusage PID PPID RUSER COMMAND" ;UNIX95=1 ps -e -o vsz,pid,ppid,ruser,args | sort -rn | head -50
##################################
select name from dba_sql_profiles where
exec dbms_sqltune.drop_sql_profile('coe_dkadu9h5ks56c_3709441712')
*************************
select INDEX_NAME,PARTITION_NAME,STATUS from DBA_ind_partitions where INDEX_NAME='TABLE_CLOSE_CASE_PK';
ALTER INDEX ODS_CRM.TABLE_CLOSE_CASE_PK REBUILD ONLINE ParaLLeL 8;
*********************
for job status
SELECT owner, job_name, enabled FROM dba_scheduler_jobs where job_name='DORMANT_IP_LIST_JOB'
Query showing UTL_RECOMP jobs that are running
SELECT job_name FROM dba_scheduler_running_jobs
WHERE job_name like 'UTL_RECOMP_SLAVE_%';
#######audit ############
select count(1) from DBA_OBJ_AUDIT_OPTS;
******************************************
password reuse in 12c
set long 2000
select dbms_metadata.get_ddl('USER','SMP_JASPER_DBUSER') from dual;
alter user dddd profile default;
ALTER USER "SYS" IDENTIFIED BY VALUES 'S:ED9B4E5F05E4923A86E05DC2EFF661252D22
EFEA6F46A84EF6CC7244FE3B;H:BF88CD4A0DDE69B7337AC58D7B41E304;T:2C5FD8E5AE19BF831D
C21FE164E9D63926CFD310D5391B3DE0C06D322FD2982EB082FA2A4E569890471F899F5DB1EBE639
287AADAA203F3FD6E4A5EE04ABB17B3C9BDE9DF9C3334D04B28D26A4AFCE1E;75A64BC4F424A695'
TEMPORARY TABLESPACE "TEMP"
SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='APP_VOD_MSTR';
*******************
crsctl relocate resource ora.ehpdb305-va2d10.vip -s ehpdb305 -n ehpdb306
############################
export status check
col username format a10
set linesize 150
col job_name format a20
col program format a25
SELECT TO_CHAR (SYSDATE,'YYYY-MM-DD HH24:MI:SS') "DATE",
s.program,s.sid,s.status,s.username,
d.job_name, p.spid,
s.serial#, p.pid
FROM gV$SESSION s, gV$PROCESS p, DBA_DATAPUMP_SESSIONS d
WHERE p.addr = s.paddr AND s.saddr = d.saddr;
###################
set pages 2000 lines 200
col STATUS format a20
col INPUT_TYPE for a15
col hrs format 999.99
col status format a15
col start_time for a20
col end_time for a20
col "Send DB Size" for 99,999,999.9
col "Write Size" for 99,999,999.9
col "Send MB/Sec." for 99,999,999.9
col "Write MB/Sec." for 99,999,999.9
select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'dd/Mon/yy hh24:mi:ss') start_time, to_char(END_TIME,'dd/Mon/yy hh24:mi:ss') end_time,
elapsed_seconds/3600 hrs, INPUT_BYTES/1024/1024/1024 "Send DB Size", OUTPUT_BYTES/1024/1024/1024 "Write Size",
INPUT_BYTES_PER_SEC/1024/1024 "Send MB/Sec.", OUTPUT_BYTES_PER_SEC/1024/1024 "Write MB/Sec." , COMPRESSION_RATIO
from V$RMAN_BACKUP_JOB_DETAILS where trunc(start_time)>=trunc(sysdate-60) and INPUT_TYPE like '%FULL%'
--and STATUS<>'FAILED'
order by session_key;
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;
________________________________________________________
col STATUS format a9
col hrs format 999.99
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
--where input_type like 'DB %'
order by session_key;
------
select b.INST_ID, b.sid, b.serial#, a.spid, b.client_info from gv$process a, gv$session b
where a.addr=b.paddr and client_info like 'rman%';
________________________________________________
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;
______________________________
SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%'
/
____________________________________________
select sid,
start_time,
totalwork
sofar,
(sofar/totalwork) * 100 pct_done
from v$session_longops
where
totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'MERG%';
################## Table insert and update delete
select
table_owner,
table_name,
SUM(inserts),
SUM(updates),
SUM(deletes),
TO_CHAR(TIMESTAMP,'DD-MON-RRRR HH24:MI:SS')
from
dba_tab_modifications
where
timestamp > SYSDATE-1
AND TABLE_OWNER <> 'SYS'
GROUP BY TABLE_OWNER, TABLE_NAME,to_char(timestamp,'DD-MON-RRRR HH24')
HAVING SUM(inserts)>0 OR SUM(updates) >0 OR SUM(deletes)>0
ORDER BY 6,1,2
table_name= 'HUP_OFFER' and table_owner='RCAPPO'
############ archive deletion steps
set line 300;
col name format A50
col space_limit format A20
col space_used format A20
select name, (space_limit/1024/1024/1024) ||'GB' as Space_Limit,(space_used/1024/1024/1024)||'GB' as Space_Used from v$recovery_file_dest;
col start_time format a20
col STATUS format a9
col hrs format 999.99
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 where start_time > SYSDATE - 1 order by session_key;
delete archivelog all completed before 'sysdate - 2' backed up 1 times to device type 'SBT_TAPE';
ASM QUERY
set line 400
set pagesize 400
cocl NAME for a40
col PATH for a60
col NAME for a40
SELECT name, header_status, path FROM V$ASM_DISK where header_status='CANDIDATE'
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
alter diskgroup dg1 rebalance power 8;
select * from v$asm_operation;
ALTER DISKGROUP CUSTDATADG01 ADD DISK '/dev/rac/ASM_disk8033_0038F';
*********************************************Advanced query *********************
Checking the IS_REOPTIMIZABLE column of the V$SQL view shows the optimizer has detected the incorrect cardinality estimates and has marked the statement to be reoptimized.
COLUMN sql_text FORMAT A50
COLUMN is_reoptimizable FORMAT A16
SELECT sql_text, is_reoptimizable
FROM gv$sql
WHERE sql_id='9xmatqqvfj5hh'
AND sql_text NOT LIKE '%v$sql%';
**************Rerun query ************
SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM TABLE(get_tab_ptf(10)); ---- this table need to put table name
SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last'));
******************
EXEC DBMS_SPD.flush_sql_plan_directive; --- flush flush_sql_plan_directive
SET LINESIZE 200
COLUMN dir_id FORMAT A20
COLUMN owner FORMAT A10
COLUMN object_name FORMAT A10
COLUMN col_name FORMAT A10
SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name,
o.subobject_name col_name, o.object_type, d.type, d.state, d.reason
FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE d.directive_id=o.directive_id
AND o.owner = 'TEST'
ORDER BY 1,2,3,4,5;
**********************
SELECT dbms_metadata.get_ddl('SYNONYM','USST02','RCSNP4_15') from dual;
***********************************
select mview_name,to_char(LAST_REFRESH_DATE,'dd-mm-yyyy hh:mi:ss'),round(increfreshtim/60) as "Minutes to Refresh" from user_MVIEW_ANALYSIS where owner in('RCREFSNP1','RCREFSNP2') ORDER BY 2;
select DBMS_METADATA.get_ddl ('MATERIALIZED_VIEW','V21_ACT_PORT_REQUEST','PORTALSNP1') ||'/' from dual
enable job
exec dbms_job.broken(166,FALSE);
******************
aix to check which process taking resource ps -e -o ruser,group,pid,vsz,comm > virt_mem_size.txt
SELECT OWNER, TABLE_NAME, STALE_STATS, PARTITION_NAME FROM ALL_TAB_STATISTICS WHERE OWNER = 'MARSP' AND STALE_STATS = 'YES'
GAHTER STATS OF MORE THEN ONE TABLES
SELECT 'Exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'''||owner||''',tabname=>'''||table_name||''',method_opt=>''FOR ALL COLUMNS SIZE AUTO'',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE=>TRUE,DEGREE=>6);' from dba_tables where owner='APP_MAESTRO'
EXECUTE DBMS_STATS.GATHER_TABLE_STATS (ownname=>'MARQUEE',tabname=>'BIS_PENT_DAILY_SS',partname=>'BIS_PENT_DAILY_SS_201706',degree=>6, estimate_percent=>10);
EXECUTE DBMS_STATS.GATHER_TABLE_STATS (ownname=>'SASRTDM',tabname=>'CMT_PERS_LOG_UNAGGR_122017',degree=>6, estimate_percent=>100,method_opt=>'for all columns size auto');
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SMPTGT1CM',tabname=>'SUB_SVC_PARM',method_opt=>'FOR ALL COLUMNS SIZE AUTO',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,CASCADE=>TRUE,DEGREE=>6)
******************************************
Someone needs to watch the alerts and act if it goes beyond 90%.
Archive deletion policy is setup to backed up 1 times on RMAN.
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO 'SBT_TAPE';
You need to change to
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
Do the purge and change
DELETE NOPROMPT force ARCHIVELOG UNTIL TIME 'SYSDATE - 7';
Change back
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO 'SBT_TAPE';
nohup sqlplus "/ as sysdba" @/me01/oracle/deepak.sql >> /me01/oracle/deepak.log &
****************PGA USED BY USER ***********
col LOGON for a25
--col OSUSER for a50
--col MACHINE for a30
--col MODULE for a30
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
SID, v$process.SPID , ROUND(v$process.pga_used_mem/(1024*1024), 2) PGA_MB_USED,
v$session.USERNAME, STATUS
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
--and status = 'ACTIVE'
--and v$session.sid = 97
and v$session.username not in('SYS','SYSTEM') AND v$session.STATUS='INACTIVE'
--and v$process.spid = 24301
ORDER BY pga_used_mem DESC;
select USERNAME from dba_users where username in (select distinct owner from dba_tables where TABLESPACE_NAME='BDW_USER_DATA');
**************************
-rwxr-xr-x 1 oracle oinstall 470 Apr 19 16:11 /u01/app/oracle/rssdba/scripts/cleanup_adrci.sh
Servers: BDWPROD and ELAPROD servers
********************
NMDB
SELECT 'analyze table '||owner||'.'||table_name||' '||
decode(sign(10485760 - initial_extent),1,'compute statistics;',
'estimate statistics sample 50 percent;')
FROM sys.dba_tables
WHERE owner != 'SYS'
minus
SELECT 'analyze table '||owner||'.'||table_name||' '||
decode(sign(10485760 - initial_extent),1,'compute statistics;',
'estimate statistics sample 50 percent;')
FROM sys.dba_tables
WHERE owner != 'SYS' and TRUNC(LAST_ANALYZED)>='01-APR-17';
SELECT 'analyze index '||owner||'.'||index_name||' validate structure;'
FROM dba_indexes
WHERE owner != 'SYS'
minus
SELECT 'analyze index '||owner||'.'||index_name||' validate structure;'
FROM dba_indexes
WHERE owner != 'SYS' and TRUNC(LAST_ANALYZED)>='01-APR-17';
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','ITEM_QUANTITY','RNPDATA') from dual
SELECT DBMS_METADATA.GET_DDL('VIEW','VWACCTCLCTNRLTD','CRDTOPSVWS') FROM DUAL;
set pagesize 0
set long 90000
select dbms_metadata.get_ddl('USER','AMH') from dual;
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('CONSTRAINT', constraint_name, owner)
FROM DBA_constraints
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), table_name, UPPER('&2'))
--AND constraint_type IN ('U', 'P');
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
set pagesize 0
set long 90000
select dbms_metadata.get_ddl('FUNCATION','VERIFY_FUNCTION','SYS') from dual;
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','EMP_PKG','SCOTT') FROM DUAL;
SELECT DBMS_METADATA.GET_DDL('PACKAGE','EMP_PKG','SCOTT') FROM DUAL;
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('VIEW', view_name, owner)
FROM all_views
WHERE owner = UPPER('APP_SALES_INSIGHT')
AND view_name = DECODE(UPPER('VW_PRICEPLANCHANGES'), 'ALL', view_name, UPPER('VW_PRICEPLANCHANGES'));
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
SELECT DBMS_METADATA.GET_DDL('FUNCTION','VERIFY_FUNCTION') FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION';
************************************
temp tablespace size check
select TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024/1024 "TOTAL SPACE(GB)", ALLOCATED_SPACE/1024/1024/1024 "ALLOCATED SPACE(GB)", FREE_SPACE/1024/1024/1024 "FREE SPACE(GB)" from DBA_TEMP_FREE_SPACE;
#########################
Redo log size for current statemnt
select sid,name,value from v$statname a,v$mystat b where a.STATISTIC#=b.STATISTIC# and a.name like '%redo size%';
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(<sid>,<serial#>,waits=>TRUE,binds=>TRUE);
#########################
set pagesize 0
set echo on
select 'alter database datafile '''||file_name||'''' || ' resize 11g ;' from dba_data_files
************************
/export/home/oracle/exp_OM_PROVISIONING.par
nohup expdp \'/ as sysdba \' parfile=exp_OM_PROVISIONING.par &
/home/oracle/scripts/
***************** script for nohup ###################################
$ more dpk_dict.sh
ORACLE_SID=FMSPROD1
export ORACLE_SIDG
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
#LOG_DIR=/home/oracle/scripts/log
sqlplus / as sysdba <<EOF
spool dpk_dict.LOG
---Set time on
--SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY DD:MI:SS') FROM DUAL;
----exec dbms_stats.gather_dictionary_stats(degree=>16);
select count(*),status from dba_objects group by status;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY DD:MI:SS') FROM DUAL;
spool off
exit
EOF
#######################################
exec dbms_system.set_ev(1131,19398,10046,0,'');
ALTER SESSION SET EVENTS '10046 trace name context off'; off the trcing
ALTER SESSION SET tracefile_identifier='MYTRACE'; tracing looks like eystest_ora_4333_MYTRACE.trc
eystest_ora_25000.trc
You are not limited to trace your own sessions, you can trace any sessions if you have permission to execute DBMS_MONITOR package. You enable tracing with issuing the following command:
Oracle PL/SQL
DBMS_MONITOR.SESSION_TRACE_ENABLE( session_id => x, serial_num => y, waits=>(TRUE|FALSE), binds=>(TRUE|FALSE) );
DBMS_MONITOR.SESSION_TRACE_ENABLE( session_id => x, serial_num => y, waits=>(TRUE|FALSE), binds=>(TRUE|FALSE) );
The default of waits is TRUE and the default of binds is FALSE. You can disable tracing by issuing the following command:
Oracle PL/SQL
DBMS_MONITOR.SESSION_TRACE_DISABLE( session_id => x, serial_num => y );
DBMS_MONITOR.SESSION_TRACE_DISABLE( session_id => x, serial_num => y );
You can also use oradebug to trace a session. First, you need to identify the OS process ID of the session:
Oracle PL/SQL
SELECT p.PID, p.SPID, s.SID
FROM v$process p, v$session s
WHERE s.paddr = p.addr AND s.SID = &session_id;
PID SPID SID
---------- ------------ ----------
115 29716 1202
After the OS process id has been determined then the trace can be started:
Oracle PL/SQL
CONNECT / AS SYSDBA
ORADEBUG SETOSPID 85005
ORADEBUG UNLIMIT
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER,LEVEL 12
Oracle pid: 115, Unix process pid: 29716, image: oracle@xyzserver
6
CONNECT / AS SYSDBA
ORADEBUG SETOSPID 29716
ORADEBUG UNLIMIT
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER,LEVEL 12
Oracle pid: 115, Unix process pid: 29716, image: oracle@xyzserver
We can use SETOSPID or SETORAPID. Please note that 29716 is a sample value. If we’ll use SETOSPID, then we should type value of p.SPID. If we’ll use SETORAPID, then we need to type the value of p.PID (i.e. 115).
You can issue the following command to disable oradebug tracing:
racle PL/SQL
ORADEBUG EVENT 10046 TRACE NAME CONTEXT ---- disable
oradebug event 10046 trace name context off --- disable
###################################
See suprenatural logging enabled
select owner, log_group_name, table_name, log_group_type, always, generated
from dba_log_groups
where log_group_name like 'GGS%' and table_name like'MTL_%';
##########################Role ############################
select GRANTED_ROLE,GRANTEE from dba_role_privs where grantee ='MG970001' and GRANTED_ROLE in('R_FINTAXATIONVWS_RO');
List of role user has
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
*************************
To see resource window scheduled
select * from V$RSRC_PLAN_HISTORY order by SEQUENCE#;
set echo on;
set linesize 300;
set pages 1000;
set numwidth 10;
set trimspool on;
col VALUE for a30;
col ATTRIBUTE for a15;
col GRANTEE for a25;
col CPU_METHOD for a15;
col COMMENTS for a30;
col MGMT_METHOD for a15;
col STATUS for a10;
--alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
--set markup html on
spool resource_manager_details.html
show parameter resource
SELECT SYSDATE FROM DUAL;
SELECT * FROM DBA_RSRC_MANAGER_SYSTEM_PRIVS;
SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;
SELECT * FROM DBA_RSRC_CONSUMER_GROUPS;
SELECT * FROM DBA_RSRC_PLANS;
SELECT * FROM DBA_RSRC_PLAN_DIRECTIVES;
SELECT * FROM V$RSRC_CONSUMER_GROUP;
select * from dba_rsrc_group_mappings;
select * from dba_rsrc_mapping_priority;
select username, resource_consumer_group, service_name from v$session;
SELECT * FROM V$RSRC_CONSUMER_GROUP_CPU_MTH;
SELECT * FROM V$RSRC_PLAN;
SELECT * FROM V$RSRC_PLAN_CPU_MTH;
SELECT * FROM V$RSRC_CONS_GROUP_HISTORY;
SELECT * FROM V$RSRC_PLAN_HISTORY;
SELECT * FROM V$RSRC_SESSION_INFO;
spool off
set markup html off
Stop Resource Manager
1. Set the current resource manager plan to null (or another plan that is not restrictive):
alter system set resource_manager_plan='' scope=both;
2. Change the active windows to use the null resource manager plan (or other nonrestrictive plan) using:
execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
Since in 11g there are more Maintenance Windows, we should add them too:
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:
execute dbms_scheduler.set_attribute('<window name>','RESOURCE_PLAN','');
profile
##########
select RESOURCE_NAME ,LIMIT from dba_profiles where PROFILE='RF_ROG_PERSONAL_PROFILE';
alter profile RF_ROG_PERSONAL_PROFILE limit PASSWORD_LIFE_TIME 90;
###########3
CREATE BIGFILE TABLESPACE "ELA_BMRT_MEDIA_DATA" DATAFILE '+DATAC1' size 5 g;
SELECT b.GRANTEE "username",B.GRANTED_ROLE "role",A.OWNER,A.TABLE_NAME FROM DBA_TAB_PRIVS
A JOIN DBA_ROLE_PRIVS B ON (A.GRANTEE=B.GRANTED_ROLE)
AND B.GRANTEE='APP_MRKT_AF_ETL' AND A.TABLE_NAME='PMS_AF' AND A.OWNER='APP_MRKT_AF' and B.GRANTED_ROLE='R_APP_MRKT_AF_RW';
select GRANTEE,GRANTED_ROLE from dba_role_privs where GRANTEE='APP_MRKT_AF_ETL';
GRANTEE GRANTED_ROLE
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
APP_MRKT_AF_ETL R_APP_MRKT_AF_RW
APP_MRKT_AF_ETL CONNECT
SASCIAPP.LND_DIM_CMPGN_CYCLE_KEYCODE
###############
SELECT b.GRANTEE "username",B.GRANTED_ROLE "role",A.OWNER,A.TABLE_NAME FROM DBA_TAB_PRIVS
A JOIN DBA_ROLE_PRIVS B ON (A.GRANTEE=B.GRANTED_ROLE)
AND B.GRANTEE='AT843253' AND A.TABLE_NAME='BELL_TECH_FTTH' AND A.OWNER='ODS_BMRT' and B.GRANTED_ROLE='R_ODS_BMRT_RO';
SELECT b.GRANTEE "username",B.GRANTED_ROLE "role",A.OWNER,A.TABLE_NAME FROM DBA_TAB_PRIVS
A JOIN DBA_ROLE_PRIVS B ON (A.GRANTEE=B.GRANTED_ROLE)
AND B.GRANTEE='AT843253' AND A.TABLE_NAME='BELL_TECH_FTTH' AND A.OWNER='ODS_BMRT' and B.GRANTED_ROLE='R_ODS_BMRT_RO';
select GRANTEE,GRANTOR,PRIVILEGE,TABLE_NAME from dba_tab_privs where TABLE_NAME='LND_DIM_CMPGN_CYCLE_KEYCODE' and PRIVILEGE='SELECT'
and GRANTEE='R_ODIN_ALL_SCHEMAS_RO' and GRANTOR in (select GRANTEE from dba_role_privs where grantee ='SL817875' and GRANTED_ROLE in('R_ODIN_ALL_SCHEMAS_RO'));
########
select GRANTEE,GRANTOR,PRIVILEGE,TABLE_NAME from dba_tab_privs where GRANTOR='MARQUEE_COS' AND TABLE_NAME='PEN_MON_ADDR_SUBS' and PRIVILEGE='SELECT'
and GRANTEE='R_MARQUEE_ALL_SCHEMAS_RO'
####################
table access
select GRANTEE,GRANTOR,PRIVILEGE,TABLE_NAME from dba_tab_privs where TABLE_NAME='MTEND_CUSTOMER_ORIG' ;
###########3 to see table level privis
SELECT t.table_name object_name, t.privilege, t.grantee table_grantee, r.grantee role_grantee
FROM dba_tab_privs t join dba_role_privs r ON t.grantee = r.granted_role WHERE t.owner = 'VAS' AND r.grantee='BDW_HADOOP_APPUSR'
ORDER BY table_grantee, role_grantee
###########table privis
SELECT t.owner || '.' || t.table_name object_name,
t.privilege,
t.grantable,
t.TYPE object_type,
t.grantee table_grantee,
r.grantee role_grantee
FROM dba_tab_privs t
join dba_role_privs r ON t.grantee = r.granted_role
WHERE t.table_name ='TABLE_X_CUST_PORT_OUT_ROLE' AND
t.owner = 'ODS_CRM'
ORDER BY table_grantee, role_grantee
ALTER DATABASE DATAFILE '+DATAC1/BDWPROD/DATAFILE/app_cmt_fido_data.1858.948630143' AUTOEXTEND ON MAXSIZE 60G;
I confirm no users currently have RW privileges on BDWPROD.
15:10:37 sys@BDWPROD1(235)> SELECT granted_role
FROM dba_role_privs
WHERE granted_role LIKE '%\_RW%' ESCAPE '\' AND
REGEXP_LIKE(grantee, '[A-Z]{2}[0-9]{4}') AND
granted_role NOT LIKE 'R_ODS_RWI%RO'
select 'grant select on '||owner||'.'||OBJECT_NAME||' to R_ODS_APTITUDE_RO;' from dba_objects where owner='ODS_APTITUDE' and OBJECT_TYPE='TABLE';
***********************
ps -ef | grep veridata
ps -ef | grep oggvd
col MAX_VALUE for 99999999999999999999999999999999999
col SEQUENCE_LEFT for 99999999999999999999999999999
set lin 180
SELECT sequence_owner owner ,sequence_name, increment_by,last_number curr_val,last_number + Increment_by next_val,MAX_VALUE,MAX_VALUE - last_number SEQUENCE_LEFT FROM dba_sequences order by 1
---To get the % useage of all available open cursor
SELECT 'session_cached_cursors' parameter, LPAD(value, 5) value,
DECODE(value, 0, ' n/a', to_char(100 * used / value, '990') || '%' ) usage
FROM ( SELECT MAX(s.value) used
FROM gv$statname n, gv$sesstat s
WHERE
n.name = 'session cursor cache count' and
s.statistic# = n.statistic# ),
(SELECT value
FROM gv$parameter
WHERE
name = 'session_cached_cursors' )
UNION ALL
SELECT 'open_cursors', LPAD(value, 5), to_char(100 * used / value, '990') || '%'
FROM
(SELECT MAX(sum(s.value)) used
FROM gv$statname n, gv$sesstat s
WHERE
n.name in ('opened cursors current', 'session cursor cache count') and s.statistic#=n.statistic#
GROUP BY
s.sid
),
(SELECT value
FROM gv$parameter
WHERE
name = 'open_cursors' )
/
#####
Open cirsor value
SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM gv$sesstat a, gv$statname b, gv$parameter p WHERE a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;
####################### bind value
SELECT NAME, POSITION, DATATYPE_STRING, VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='
############################gg columns add
select 'alter table '||owner||'.'||table_name||'
add (GG_SRC_DBNAME VARCHAR2(20 CHAR), GG_OP_TYPE VARCHAR2(1 CHAR), GG_COMMIT_TS TIMESTAMP(6), GG_REPLICAT_NAME VARCHAR2(20 CHAR), GG_LOAD_TO_TDAT_TS TIMESTAMP(6), GG_LOAD_SEQUENCE NUMBER(18), GG_LOG_RBA VARCHAR2(20 CHAR), GG_SOURCE_TS TIMESTAMP(6), GG_BIGDATA_SCN VARCHAR2(20 CHAR), GG_BIGDATA_OP_TYPE VARCHAR2(1 CHAR), GG_BIGDATA_LOG_POSITION VARCHAR2(30 CHAR), GG_DATALAKE_LOAD_TS TIMESTAMP(6));' from dba_tables where table_name in (‘<ADD Table_name>’) and owner='ELA_CRM';
###################
SELECT DISTINCT (GROUP_NAME ) ,LAST_UPDATE_TS FROM GGREPUSR.GGS_CHECKPOINT where group_name in ('RTOOLK', 'TOOLKIT') ;
SELECT GROUP_NAME, TO_TIMESTAMP(AUDIT_TS,'YYYY-MM-DD HH24:MI:SS.FF') AS AUDIT_TS
FROM GGREPUSR.GGS_CHECKPOINT;
####################
Here is what needs to be done, every night on ehpdb353 server where RCUST standby database is running.
1.Before stopping managed recovery, go to ggsci
2.Stop testasm
3.Check status by running, info all and ensure status of TESTASM extract shows STOPPED.
4.Stop managed recovery and do the steps required for BCV cut.
5.After managed recovery is started
6.Goto ggsci and start the TESTASM extract and confirm it does not have a lag (initially it might show you little lag).
7.Once lag is gone (3, 4 seconds lag is okay). Let it run.
#############
SELECT 'DROP INDEX '||OWNER||'.'||INDEX_NAME||';' FROM DBA_INDEXES WHERE TABLE_NAME IN('DIM_AGENT_GROUP_CATEGORY','PS_AGG_AGENT_IXN_30M_TBL','PS_AGG_AGENT_IXN_DAY_TBL','PS_AGG_IXN_30M_TBL','PS_AGG_IXN_DAY_TBL','PS_AGG_TRANSFER_30M_TBL','PS_IXN_TRANSFER_DTL_TBL')
and UNIQUENESS='NONUNIQUE'
###
schema object drop
select 'drop '||object_type||' '||owner||'.'||object_name||decode(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') from dba_objects where owner in ('DRM_MEDIA') and object_type not in ('DATABASE LINK','JOB','LOB') order by object_type,object_name;
#################
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from dba_tab_privs
where owner='APP_IFRS' and table_name in ('PENDING_CHARGE_STG','PENDING_ADJUSTMENT_STG') order by grantee,owner,table_name,privilege;
expdp \'/ as sysdba\' directory=expDMARTP schemas=BIUSER EXCLUDE=TABLE:\"IN \(\'RCI_VST_PEN_MON_CLICC_P2\', \'VST_PENETRATION_MONT_ADDR_SUBS\',\'RCI_CCPS_HTRY_PENMON_DATA_P2\'\)\" dumpfile=BIUSER_11Nov_%U logfile=BIUSER_11Nov.log filesize=10g parallel=8
select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in ('processes','sessions');
####################
alter session set current_schema=EDA_TENANT1;
SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') "CURRENT_SCHEMA" from dual;
select grantee,privilege from dba_sys_privs
where grantee in
(select p.USERNAME from dba_users p,dba_role_privs q
where p.USERNAME = q.GRANTEE (+)
and p.oracle_maintained='N'
and q.granted_role is null))
select a.USERNAME,a.ACCOUNT_STATUS,b.GRANTED_ROLE,c.privilege ,a.DEFAULT_TABLESPACE, a.CREATED, to_char(a.last_login,'YY-MM-DD, hh24:mi:ss') LAST_LOGIN from dba_users a
,
dba_role_privs b,privilege c
where a.USERNAME = b.GRANTEE (+)
and a.oracle_maintained='N'
and a.username=c.grantee (+)
order by a.account_status,a.last_login,a.username,b.granted_role,c.privilege,a.default_tablespace
/
set pagesize0
set line 200
select 'grant select on '||owner||'.'||OBJECT_NAME||' to R_BDWPROD_ALL_SCHEMAS_RO;'
from dba_objects where OBJECT_TYPE IN('TABLE','VIEW','MATERIALIZED VIEW')
and object_name not in('PEN_MON_ADDR','PEN_MON_ADDR_SUBS','PEN_MON_ADDR_SUBS_DUPS')
and object_name not like '%SYS%'
and object_name not like'FIN%' and owner not in ('CFERREIRA','AS891687','WL815963','SZ966285','SS932089','EV962646','SQLTXPLAIN','OUTLN' ,'SYSTEM','SYS','ANONYMOUS','DBSNMP','WMSYS','XDB','APPQOSSYS','GSMADMIN_INTERNAL','GSMCATUSER','SYSBACKUP','DIP','SYSDG','ORACLE_OCM','OJVMSYS','SYSKM','XS$NULL','XS$NULL','GSMUSER', 'GSMUSER','AUDSYS')
and object_name not like 'BIN$%' ;
set pagesize 100
set linesize 200
break on report
compute sum of free_mb on report
compute sum of total_mb on report
col free_mb format 999,999,999.99
col total_mb format 999,999,999.99
col USABLE_FILE_MB format 999,999,999.99
col NAME for a25
select GROUP_NUMBER,name,total_mb,free_mb,USABLE_FILE_MB from v$asm_diskgroup
/
###########################
SELECT t.owner,
t.table_name,
sp.partition_name,
--sp.subpartition_name,
ROUND(SUM(NVL(bytes, 0)) / 1024 / 1024) size_mb
FROM dba_tables t
--join dba_tab_partitions p on t.owner = p.table_owner and t.table_name = p.table_name
JOIN dba_tab_subpartitions sp
ON t.owner = sp.table_owner AND
t.table_name = sp.table_name
LEFT JOIN dba_segments s
ON t.owner = s.owner AND
s.segment_name = t.table_name AND
s.partition_name = sp.subpartition_name
WHERE t.owner = 'ODS' AND
t.table_name = 'TOS_SUBSCRIBER_ACTIVITY' and sp.partition_name in('PARTITION_201710','PARTITION_201711','PARTITION_201712')
GROUP BY t.owner, t.table_name, sp.partition_name
ORDER BY t.owner, t.table_name, sp.partition_name
### awr report setting
exec dbms_workload_repository.modify_snapshot_settings( retention => 129600); --- 24*90*60 --- this is for 90 days retention periods
set lines 200
col SNAP_INTERVAL format a20
col RETENTION format a20
select * from dba_hist_wr_control;
set lines 100 pages 999
select snap_id,
snap_level,
to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
from
dba_hist_snapshot
order by 1;
##################
user not login last 45 days
SELECT username,CREATED,last_login FROM dba_users WHERE CREATED <trunc(sysdate)-45 and oracle_maintained='N' AND last_login IS NULL and username not in('SQLTXADMIN')
order by 1
ALTER DATABASE DATAFILE ‘<file_name>’ AUTOEXTEND ON MAXSIZE 50g;
#########ASM DISK TOP Space ocupied ######
SELECT name, enqueue, dequeue FROM USER_QUEUES WHERE name = 'Q_FILES';
SELECT name,ENQUEUE_ENABLED,DEQUEUE_ENABLED FROM dba_QUEUES WHERE name ='Q_FILES';
NAME ENQUEUE DEQUEUE
------------------------------ ------- -------
Q_FILES YES YES
EXECUTE DBMS_AQADM.STOP_QUEUE (
queue_name => 'Q_FILES');
EXECUTE DBMS_AQADM.START_QUEUE (
queue_name => 'Q_FILES');
SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V$RECOVERY_FILE_DEST A,
V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;
SELECT name, free_mb/1024 "FREE_GB" ,total_mb/1024 "TOTAL",nvl( free_mb/total_mb*100,0) as "free%" FROM v$asm_diskgroup;
####### STANDBY
SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
;
set linesize 500
col value format a100
col name format a50
col sid format a10
select NAME,VALUE from v$parameter where NAME in ('db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1','log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes','fal_server','db_file_name_convert','log_file_name_convert','standby_file_management','dg_broker_config_file1','dg_broker_config_file2','dg_broker_start');
Primary:
======
select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
PhyStdby:
=======
select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;
select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;
select process,thread#,sequence#,status from gv$managed_standby;
delete archivelog until sequence=464240 thread=2
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
#########################
EAR
select 'BDWPROD',USERNAME "USER Account",substr(username,3,8) "employee id",
GRANTED_ROLE||'@mdcocdmelxdb37-m,mdcocdmelxdb38-m,mdcocdmelxdb39-m,mdcocdmelxdb40-m,mdcocdmelxdb70,mdcocdmelxdb71-M' "rOLE ID",
GRANTED_ROLE||'@mdcocdmelxdb37-m,mdcocdmelxdb38-m,mdcocdmelxdb39-m,mdcocdmelxdb40-m,mdcocdmelxdb70,mdcocdmelxdb71-M' ROLE,
granted_role||'@mdcocdmelxdb37-m,mdcocdmelxdb38-m,mdcocdmelxdb39-m,mdcocdmelxdb40-m,mdcocdmelxdb70,mdcocdmelxdb71-M' "Role Description"
from dba_role_privs a,dba_users b where a.grantee=b.username and granted_role in ('DBA','APP_DBA') AND USERNAME NOT IN ('SYS','SYSTEM')
select USERNAME,to_char(logoff_time,'DD-MON-YYYY HH24:MI:SS') logoff from dba_audit_session a where username ='ABC987' and timestamp = (select max(timestamp) from dba_audit_session where username=a.username );
#####################3
DBA_2PC_PENDING
select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared'
select 'rollback force '''||local_tran_id ||''' ; ' from dba_2pc_pending where STATE='prepared';
select 'execute dbms_transaction.purge_lost_db_entry( '''||local_tran_id ||''') ; ' from dba_2pc_pending
######## dba_recyclebin
select owner,original_name,to_date(droptime, 'YYYY-MM-DD:HH24:MI:SS'),ts_name from dba_recyclebin where TS_NAME in('ODS_APTITUDE_DATA','APP_IBRO_DATA','GCDM_IA_STAGE_DATA','ODIN_ODS_DATA','GCDM_IA_STAGE_IDX','ODIN_HUP_DATA','MARQUEE_ODMART_DATA',
'APP_RECOVERY_DATA','BDW_USER_DATA','ODIN_BPR_APP_DATA','MARQUEE_OWNER_DATA','ODIN_V21TGT_DATA')
select owner,original_name, 'purge table "' || owner || '"."' || object_name || '";'
from dba_recyclebin where TS_NAME='ODS_APTITUDE_DATA'
select owner,original_name,to_date(droptime, 'YYYY-MM-DD:HH24:MI:SS'),ts_name from dba_recyclebin where TS_NAME='ODIN_V21TGT_DATA'
select owner,original_name,'purge table "' || owner || '"."' || object_name || '";' from dba_recyclebin where ts_name='ODIN_V21TGT_DATA'
and to_date(droptime, 'YYYY-MM-DD:HH24')='2018-JAN-11'
###########################
user details expires
set pagesize 5000
set linesize 999
set trimspool on
column "Expire Date" format a20
column "Locked Date" format a20
select
gn.GLOBAL_NAME as "Instance",
du.username,
du.expiry_date as "Expire Date",
du.account_status,
du.lock_date as "Locked Date",
du.profile,
dp.limit as "Profile Password Expiration"
from dba_users du, global_name gn, dba_profiles dp
where (du.account_status IN ( 'OPEN', 'LOCKED' ) OR du.account_status like '%EXPIRE%')
and du.profile = dp.profile
and dp.resource_name = 'PASSWORD_LIFE_TIME'
order by du.account_status, du.expiry_date, du.username
set pagesize 500
set linesize 200
set trimspool on
--set feedback off
--set heading off
column "EXPIRE DATE" format a20
select TO_CHAR(STARTUP_TIME, 'Month dd, YYYY HH24:MI:SS') "StartUp" from v$instance;
select username as "USER NAME",
TO_CHAR(expiry_date,'DD-MON-YYYY') as "EXPIRE DATE",
account_status
from dba_users
where expiry_date < sysdate+180
and account_status IN ( 'OPEN', 'EXPIRED(GRACE)' ) and username like'%ETL';
/
set pagesize 500
set linesize 200
set trimspool on
set feedback off
set heading off
column "EXPIRE DATE" format a20
select TO_CHAR(STARTUP_TIME, 'Month dd, YYYY HH24:MI:SS') "StartUp" from v$instance;
select username as "USER NAME",
TO_CHAR(expiry_date,'DD-MON-YYYY') as "EXPIRE DATE",
account_status
from dba_users
where account_status IN ( 'OPEN', 'EXPIRED(GRACE)' ) and username like'%ETL';
/
select username as "USER NAME",
account_status
from dba_users
where
account_status LIKE ( '%LOCKED%' )
order by username;
#########3
set heading off
set echo off
set flush off
set pagesize 50000
set linesize 32767
set long 99999999
spool PACKAGE_MARQ2000.pks
select dbms_metadata.get_ddl('PACKAGE','MARQ2000','MARQUEE') from dual ;
spool off;
spool PACKAGE_BODY_MARQ2000.Pks
select dbms_metadata.get_ddl('PACKAGE_BODY','MARQ2000','MARQUEE') from dual ;
spool off
select text from dba_source where owner='MARQUEE' and name='MARQ2000' and type='PACKAGE BODY' order by line
spool off
######################
listener entry
alter system set local_listener='dm06clu3-scan:61521','dm06clu401-vip.corp.pep.pvt:61026' scope=both sid='FLNR121D1';
alter system set local_listener='LISTENER_IFRSDEV4','exa002ldccs09.rci.rogers.com:1551' scope=both ;
alter system set local_listener='LISTENER_IFRSDEV4','exa002ldccs09.rci.rogers.com:1551','exa002ldcs:1528' scope=both sid='*';
System altered.
SQL> alter system register;
exa002ldcs.rci.rogers.com
##########################
pwd copy in asm
srvctl config database -d REBPRDR
pwcopy --dbuniquename REBPRDR '/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/orapwREBPRDR' '+DATAC1/REBPRDR/PASSWORD/pwdrebprdr'
#####################
col DB_LINK for a30
col USERNAME for a30
col HOST for a30
col CREATED for 999999999
set lin 120
SELECT * FROM USER_DB_LINKS
col OWNER for a20
col DB_LINK for a30
col HOST for a30
set lin 200
select OWNER,DB_LINK,HOST from dba_db_links
This query will tell you recommendations for errors encountered on DB
Set pages 1000
Set lines 75
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message : '||c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,
Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%' and a.status='COMPLETED'
Order by b.impact, d.rank;
#########
Data too long error can be sorted by the below query. Change the owner and table_name.
select 'alter table '||owner||'.'||table_name||' modify ('||column_name||' '||data_type||' ('||char_length||' CHAR ));'
from dba_tab_columns where owner in ('ELA_V21') and data_type in ('CHAR','VARCHAR2') and table_name in ('SERVICE_ELIGIBILITY_GG');
cp --port 1531 sys@10.18.116.200.+ASM1:+RECOC1/ELAPROS/ARCHIVELOG/2018_04_25/thread_1_seq_152468.3779.974386917 +RECOC1/ELAPROD/ARCHIVELOG/2018_04_25/thread_1_seq_152468.3779
###sParallel
SELECT /*+ PARALLEL(vpfp,16) PARALLEL(vpfp1,16) PARALLEL(vpfp2,16) PARALLEL(vpfp3,16) PARALLEL(contract,16) PARALLEL(contract_history,16)*/
PGA Advisor
select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice;
SGA Advisor
select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;
MEMORY TARGET Advisor
select memory_size,memory_size_factor,estd_db_time from v$memory_target_advice;
Asm disk group size
select name,total_mb/1024 "Total (Gb)", usable_File_mb/1024 "Free (Gb)" from v$asm_diskgroup;
###################
SELECT 'CREATE OR REPLACE SYNONYM RMSPC.'||SYNONYM_NAME||' FOR '||REPLACE(TABLE_OWNER,'1OWN','2OWN')||'.'||TABLE_NAME||';'
FROM dba_synonyms where TABLE_OWNER like '%1OWN' and owner='RMSPC' ORDER BY TABLE_NAME;
#########
SET SERVEROUTPUT ON
DECLARE
v_table_name VARCHAR2(100);
BEGIN
v_table_name:= app_recovery.tab_recovery_pkg.create_backup_table(i_source_owner => 'MARQUEE_OWNER',
i_source_name => 'DAILY_WORK_ORDER_PRODUCTS',
i_ticket_number => 'INC1234567',
i_degree => '16',
i_query => 'ROWNUM < 20000',
i_compress => 'YES',
i_comments => 'This is a test');
dbms_output.put_line('TABLE: ' || v_table_name);
END;
/
SET SERVEROUTPUT ON
DECLARE
v_table_name VARCHAR2(100);
BEGIN
v_table_name:= app_recovery.tab_recovery_pkg.create_backup_table(i_source_owner => 'APP_IFRS',
i_source_name => 'GL_V21_RRE_RECON_SEQ_NO',
i_ticket_number => 'INC1592608',
i_degree => '16',
-- i_query => '',
i_compress => 'YES',
i_comments => 'This is a test');
dbms_output.put_line('TABLE: ' || v_table_name);
END;
/
Example 2 – creating backup table on based of query
SET SERVEROUTPUT ON
DECLARE
v_table_name VARCHAR2(100);
v_query VARCHAR2(4000);
BEGIN
v_query := 'SELECT CNTCTSROGTKEY.CONTACT_KEY
FROM ODS_CRM.TABLE_CONTACT_NRT
INNER JOIN APP_MAESTRO.CNTCTSROGTKEY CNTCTSROGTKEY
ON TABLE_CONTACT_NRT.OBJID=CNTCTSROGTKEY.OBJ_ID
AND CNTCTSROGTKEY.SRC_SYS_NM=''CRM''
LEFT OUTER JOIN APP_MAESTRO.CNTCTDIM CNTCTDIM
ON CNTCTSROGTKEY.contact_key=CNTCTDIM.contact_key
and CNTCTDIM.crnt_f=''Y''
WHERE TABLE_CONTACT_NRT.CONTACT_ID IS NOT NULL
AND TABLE_CONTACT_NRT.GG_COMMIT_TS >= TO_DATE(''2018-06-14 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')
AND TABLE_CONTACT_NRT.GG_COMMIT_TS < TO_DATE (''2018-06-15 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')
AND CNTCTDIM.contact_key is null';
v_table_name :=
app_recovery.tab_recovery_pkg.create_backup_table(i_query => v_query,
i_ticket_number => 'INC1515066',
i_degree => '16',
i_compress => 'YES',
i_comments => 'This is a test');
DBMS_OUTPUT.put_line('TABLE: ' || v_table_name);
END;
/
space used alst in 7 days
select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, sum(space_used_delta) / (1024 * 1024 * 1024) "Space used (GB)", avg(c.bytes) / (1024 * 1024 * 1024) "Total Object Size (GB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) - 7
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name = 'HUP_OFFER'
group by to_char(end_interval_time, 'MM/DD/YY'))
order by to_date(mydate, 'MM/DD/YY');
MYDATE Space used (GB) Total Object Size (GB) Percent of Total Disk Usage
#######AWR REPORT
@?/rdbms/admin/awrrpt.sql -- basic AWR report
@?/rdbms/admin/awrsqrpt.sql -- Standard SQL statement Report
@?/rdbms/admin/awrddrpt.sql -- Period diff on current instance awrsqrpi.sql
@?/rdbms/admin/awrrpti.sql -- Workload Repository Report Instance (RAC)
@?/rdbms/admin/awrgrpt.sql -- AWR Global Report (RAC)
@?/rdbms/admin/awrgdrpt.sql -- AWR Global Diff Report (RAC)
@?/rdbms/admin/awrinfo.sql -- Script to output general AWR information
@?/rdbms/admin/ashrpt.sql
To run HANG ANALYSIS and Systemstate we have 2 ways:
1-Form of Execution for Single Instance Environments:
sqlplus / as sysdba
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
oradebug dump systemstate 258
2 - Form of execution for RAC ambitions:
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
Procudure of gather stat
Begin
dbms_stats.gather_table_stats(OwnName => 'RDR',
tabname => 'RJLE_SLR_JOURNAL_LINE_EXT_TEST',
estimate_percent => 25,
granularity => 'ALL',
DEGREE => 32,
CASCADE => TRUE,
method_opt => 'for all indexed columns size 1',
No_Invalidate => FALSE);
end;
/
vg status
vgdisplay |grep 'VG Name'|awk '{print $3}'|grep -v vg[0-9][0-9]| grep -v vgtest
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
scheduler
SELECT log_id,to_char(log_date,'DD-MON-YY HH24:MM:SS') timestamp, window_name, operation FROM DBA_SCHEDULER_WINDOW_LOG
select * from DBA_AUTOTASK_WINDOW_CLIENTS where WINDOW_NAME='SUNDAY_WINDOW';
Database name - CMS03PRD1 and CMS03PRD2
Server name - exa001mdccs03 and exa001mdccs04
Issue - CWA and Webops teams are unable to login to Weblogic console. <[Security:090759]A SQLException occurred while retrieving password information
java.sql.SQLException: ORA-28365: wallet is not open
Fix – CMS03PRD1 and CMS03PRD2 nodes bounced in order to restore the console
DBA – Restarted both the Nodes and open wallet
Status - Resolved
Disable crs on elxdb020
crsctl disable crs
crsctl config crs
crsctl stop crs
enable crs on elxdb020
crsctl start crs
crsctl enable crs
crsctl config crs
############IFRSPROD
GG Processes has been stopped
EXTRACT STOPPED EIFDWPR 00:00:08 00:00:57
EXTRACT STOPPED EIFELE 00:00:02 00:01:32
EXTRACT STOPPED PIFDWPR 00:00:00 00:00:26
EXTRACT STOPPED PIFELEDW 00:00:00 00:00:05
SQL> select current_scn from v$database;
########################
srvctl start instance -d RFSPROD -i RFSPROD1
srvctl stop instance -d RFSPROD -i RFSPROD1
srvctl start instance -d RFSDBPRD -i RFSDBPRD1
srvctl stop instance -d RFSDBPRD -i RFSDBPRD1
srvctl stop asm -n rcirs292 -i +asm1
crs process is running
ps -fea |grep crsd.bin
crsctl stat res -t
How to start crs stack on local node?
#crsctl start crs
How to start all services in a cluster on all nodes
#crsctl start cluster -all
How to start services only on particular node
#crsctl start cluster -n node2, node3
nmdb
oracle/Weclome@123
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
opatch apply OPatch.SKIP_VERIFY_SPACE=true
As part of proactive tuning exercise, we have identified queries/SQLID which can be candidate of proactive performance tuning exercise. We have found below SQLID which is currently not running with optimal plan. We have shared our recommendation which will boost the performance of the query. Kindly check and let us know if the above suggestion suits and fulfill the requirement
SELECT USERNAME,CREATED FROM DBA_USERS WHERE USERNAME in (select GRANTEE FROM Dba_role_privs WHERE GRANTED_ROLE='DBA') order by 1
https://rogers2.service-now.com/nav_to.do?uri=%2Fhome.do
uid link
https://rogers2.service-now.com/nav_to.do?uri=%2Fcom.glideapp.servicecatalog_cat_item_view.do%3Fv%3D1%26sysparm_id%3Dfdb7d3964f2a71002c7f926ca310c7f0%26sysparm_link_parent%3Dfdd4d25e4f9002002c7f926ca310c700%26sysparm_catalog%3De0d08b13c3330100c8b837659bba8fb4%26sysparm_catalog_view%3Dcatalog_default_view%26sys_id%3Dfdb7d3964f2a71002c7f926ca310c7f0
cloud
https://rcirogers.sharepoint.com/teams/ITSSLT-Infra/IT-Infra-Service/Shared%20Documents/Forms/AllItems.aspx?RootFolder=%2Fteams%2FITSSLT%2DInfra%2FIT%2DInfra%2DService%2FShared%20Documents%2FLogical%2DDBA%2FProjects%2FHRT&FolderCTID=0x012000F36C3229F4900B43A1E33AD7F3C29DC7&View=%7BF0431D7E%2DBBCB%2D4178%2D8D22%2D07A9AE36FAB7%7D
lagecy id creation link
https://rogers2.service-now.com/itportal/cat_doc.do?uri=com.glideapp.servicecatalog_cat_item_view.do?sysparm_id=9861db1a6ff9210095eb2981be3ee4a2
https://rogers2.service-now.com/itportal/cat_doc.do?uri=com.glideapp.servicecatalog_cat_item_view.do?sysparm_id=8aab20364ffcaa00de114bff9310c751
HCL-Infra-BACKUP
/usr/bin/uuencode /opt/app/oracle/DESKTOP/RSCA_20190409060702.csv /opt/app/oracle/DESKTOP/RSCA_20190409060702.csv | mailx -m -s "HEALTH CHECK OF RSCA." infraDBmAlert@rci.rogers.com
--- pre check has been compelted for this below db
's please consider this mail as approval for cmdb update
pleaes confrim once cmbd will be updated to complatere handover of the ss team
srvctl status database -d CHATRPRD
srvctl modify instance -d CHATRPRD -i CHATRPRD1 -n exa001mdccs07
srvctl status database -d CHATRPRD
srvctl start instance -d CHATRPRD -i CHATRPRD1
srvctl status database -d CHATRPRD
srvctl status service -d CHATRPRD
srvctl config service -d CHATRPRD | egrep "Preferred|Service name"
srvctl modify service -d CHATRPRD -s CHATRPRD_SN -n -i CHATRPRD1,CHATRPRD2
srvctl modify service -d CHATRPRD -s PCISPRD_SN -n -i CHATRPRD1,CHATRPRD2
srvctl modify service -d CHATRPRD -s PCOLHPRD_SN -n -i CHATRPRD1,CHATRPRD2
srvctl modify service -d CHATRPRD -s PCOLPRD_SN -n -i CHATRPRD1,CHATRPRD2
srvctl modify service -d CHATRPRD -s PCOLRPRD_SN -n -i CHATRPRD1,CHATRPRD2
srvctl modify service -d CHATRPRD -s PMOBHPRD_SN -n -i CHATRPRD1,CHATRPRD2
srvctl start service -d CHATRPRD
srvctl status service -d CHATRPRD
run {
allocate channel c1
type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_ehpdb060_ABPPRD1.opt)';
allocate channel c2
type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_ehpdb060_ABPPRD1.opt)';
restore archivelog from logseq = 57674 until logseq = 57719 thread=1;
release channel c1;
release channel c2;
}
profile
col sql_profile_name for a40
col CREATED for 9999999
select distinct
p.name sql_profile_name,CREATED,
s.sql_id
from
dba_sql_profiles p,
DBA_HIST_SQLSTAT s
where
p.name=s.sql_profile and s.sql_id in ('03had3665dzmg')
ora.rfsprod_acfs.rfsprodtemp.acfs
OFFLINE OFFLINE elxdb020 volume /u01/oracle/
oracleprod/temp offl
ine
ONLINE ONLINE elxdb021 mounted on /u01/ora
cle/oracleprod/temp
ONLINE ONLINE elxdb022 mounted on /u01/ora
cle/oracleprod/temp
vb. If temp space allocation between instances has become imbalanced, it might be necessary to manually drop temporary segments from an instance. The following command is used for this:
alter session set events 'immediate trace name drop_segments level <TS number + 1>';
For details see Oracle Support Document 47400.1 (EVENT: DROP_SEGMENTS - Forcing cleanup of TEMPORARY segments)
12c Data guard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1)
Displaying PT_QUERY.txt.
No comments:
Post a Comment