Monday, 11 April 2016

Oracle Create scott schema ....

create oracle scott user

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 10 17:49:19 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @?/rdbms/admin/utlsampl.sql -----!!!!!!!!!!!!!!! run this script to create scott schema
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

[oracle@ora12c ~]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Apr 10 17:49:51 2016

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> SELECT username, account_status FROM dba_users WHERE username = 'SCOTT';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT OPEN

SQL> connect scott/tiger
Connected.

SQL> select * from tab ;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

====

with insert command

create table scott.emptest as select * from scott.emp where 1=2 ;

alter session set nls_date_format='dd-mon-yy';

set linesize 300

col x for a200

select 'insert into scott.emptest( EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values ('

||EMPNO||','''||ENAME||''','||''''||JOB||''','||MGR||','''||HIREDATE||''','||SAL||','||nvl( to_char(comm),'null')||','||DEPTNO||' );' X from scott.emp ;

HR data

https://github.com/oracle-samples/db-sample-schemas/tree/main/human_resources

https://github.com/oracle-samples/db-sample-schemas/releases

Monday, 1 February 2016

Oracle Wait last 5 Min

Oracle Wait last 5 Min ..

-----------------------------------------
--
-- Top 10 CPU consumers in last 5 minutes
--
-----------------------------------------
select * from
(
select inst_id,session_id, session_serial#, count(*) from gv$active_session_history
where session_state= 'ON CPU'
and sample_time > sysdate - interval '5' minute
-- and USER_ID not in ( select user_id from dba_users where username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
-- )
group by inst_id,session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;

--------------------------------------------
--
-- Top 10 waiting sessions in last 5 minutes
--
--------------------------------------------

select * from
(
select inst_id ,session_id, session_serial#,count(*) from gv$active_session_history
where session_state='WAITING'
and sample_time > sysdate - interval '5' minute
-- and USER_ID not in ( select user_id from dba_users where username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' ,'PUBLIC','WWV_FLOW_PLATFORM' )
-- )
group by inst_id,session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;

set lines 200 pagesize 200
col username for a10
col osuser for a10
col machine for a10
col program for a20
col client_info for a10

col resource_consumer_group for a10

select

inst_id ,
serial#,
username,
osuser,
machine,
program,
resource_consumer_group,
client_info,
EVENT,
sql_id
from gv$session
where sid=&sid;

=======

set pagesize 200 linesize 200

col sql_id format a15

col inst_id format '9'

col sql_text format a50

col module format a10

col blocker_ses format '999999'

col blocker_ser format '999999'

col kill for a17

SELECT distinct

'''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''' kill ,

a.sql_id ,

--a.inst_id,

a.blocking_session blocker_ses,

a.blocking_session_serial# blocker_ser,

a.user_id,

s.sql_text,

a.module,a.sample_time

FROM GV$ACTIVE_SESSION_HISTORY a,gv$sql s

where a.sql_id=s.sql_id

and blocking_session is not null

and a.user_id <> 0 -- exclude SYS user

and sample_time > sysdate - interval '5' minute

--and a.sample_time >= SYSDATE - 1/24

;

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set pagesize 400 linesize 400

col sql_id format a15

col inst_id format '9'

col sql_text format a50 wrap

col module format a10

col blocker_ses format '999999'

col blocker_ser format '999999'

col kill for a17

SELECT distinct

'''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''' kill ,

a.con_id,

a.event,

a.sql_id ,

--a.inst_id,

a.blocking_session blocker_ses,

a.blocking_session_serial# blocker_ser,

a.user_id,

a.module,

a.sample_time,

s.sql_text

from gv$active_session_history a,gv$sqlstats s

where a.sql_id=s.sql_id

--and blocking_session is not null

and a.user_id <> 0 -- exclude SYS user

and sample_time > sysdate - interval '50' minute

and a.inst_id=s.inst_id

--and a.sample_time >= SYSDATE - 1/24

;

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD FROM gv$active_session_history

WHERE 1=1

--and sample_time > SYSDATE - 1/24

and sample_time > sysdate - interval '5' minute

AND session_type = 'BACKGROUND'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD FROM gv$active_session_history

WHERE 1=1

--and sample_time > SYSDATE - 1/24

and sample_time > sysdate - interval '5' minute

AND session_type = 'FOREGROUND'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

SELECT sql_id, COUNT(*) FROM gv$active_session_history ash, gv$event_name evt

WHERE 1=1

--and ash.sample_time > SYSDATE - 1/24

and sample_time > sysdate - interval '5' minute

AND ash.session_state = 'WAITING'

AND ash.event_id = evt.event_id

AND evt.wait_class = 'User I/O'

GROUP BY sql_id

ORDER BY COUNT(*) DESC;

select * from (

select SQL_ID ,

sum(decode(session_state,'ON CPU',1,0)) as CPU,

sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,

sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,

sum(decode(session_state,'ON CPU',1,1)) as TOTAL

from gv$active_session_history

where SQL_ID is not NULL

and sample_time > sysdate - interval '5' minute

group by sql_id

order by sum(decode(session_state,'ON CPU',1,1)) desc

)

where rownum <11

===

prompt Top Sessions

set linesize 200

col MACHINE for a40

col USERNAME for a20

col kill for a16

select * from (

select

'''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''' kill ,

u.username,

a.machine,

--sum the session states

sum(decode(a.session_state,'ON CPU',1,0)) CPU,

sum(decode(a.session_state,'WAITING',1,0)) - sum(decode(a.session_state,'WAITING',decode(wait_class,'User I/O',1, 0 ), 0)) WAIT ,

sum(decode(a.session_state,'WAITING',decode(wait_class,'User I/O',1, 0 ), 0)) IO,

sum(decode(session_state,'ON CPU',1,1)) TOTAL

from gv$active_session_history a, dba_users u

where a.user_id = u.user_id

and a.sample_time > sysdate - 5/24/60 --< in the last n minutes

and u.username not in ('DBVISIT7', 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )

--group by inst_id,session_id,username,session_serial#,machine

group by '''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''',username,machine

order by sum(decode(session_state,'ON CPU',1,1)) desc

) where rownum < 10

;

prompt Top SQL

set linesize 200

select * from (

select

--sql details

'''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''' kill ,

a.sql_id ,

a.sql_plan_hash_value phv,

--sum the session states

sum(decode(a.session_state,'ON CPU',1,0)) CPU,

sum(decode(a.session_state,'WAITING',1,0)) - sum(decode(a.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) WAIT,

sum(decode(a.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) IO,

sum(decode(a.session_state,'ON CPU',1,1)) TOTAL

from gv$active_session_history a

where sql_id is not null

and a.sample_time > sysdate - 5/24/60 --< in the last n minutes

and user_id not in (select user_id from dba_users where username in ('SYS','SYSTEMS', 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ))

group by '''' || a.SESSION_ID || ',' || a.session_serial# ||',@'|| a.inst_id||'''',sql_id, sql_plan_hash_value

order by sum(decode(session_state,'ON CPU',1,1)) desc

) where rownum < 10

;

col type for a15

col usernm for a16

col event for a40

col program for a30

col totales for a 10

col info_sesion FOR A20 HEAD '(Inst,SID,SERIAL)'

col totales noprint

select inst_id||' - ('||session_id||'/'||session_serial#||')' as info_sesion,

sql_id,

(select username from dba_users where user_id = a.user_id) usernm,

PROGRAM,

type,

cpu,

wait,

io,

sum(total) over (partition by session_id, session_serial#, sql_id) totales,

event

from (select ash.inst_id,

ash.session_id,

ash.session_serial#,

ash.user_id,

ash.SQL_ID,

substr(ash.program,1,30) as program,

aud.name type,

sum(decode(ash.session_state, 'ON CPU', 1, 0)) cpu,

sum(decode(ash.session_state, 'WAITING', 1, 0)) - sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O', 1, 0),0)) wait,

sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O', 1, 0),0)) io,

sum(decode(ash.session_state, 'ON CPU', 1, 1)) total,

decode(ash.event, NULL, 'db_cpu', ash.event) event

from gv$active_session_history ash, audit_actions aud

where SQL_ID is not NULL

and ash.sql_opcode = aud.action

and ash.sample_time > sysdate - 5 / (60 * 24)

and ash.user_id not in (select user_id from dba_users where username in ('SYS','SYSTEMS', 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' ))

group by inst_id, ash.session_id, ash.session_serial#, user_id, sql_id, PROGRAM, aud.name, event

order by sql_id ) a

where rownum <= 50

order by totales desc

;

set linesize 500 pagesize 300

col kill for a17

select

'''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''' KILL,

ash.con_id,

ash.sql_id,

ash.user_id,

ash.program,

sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",

sum(decode(ash.session_state,'WAITING',1,0)) -

sum(decode(ash.session_state,'WAITING',

decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,

sum(decode(ash.session_state,'WAITING',

decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,

sum(decode(session_state,'ON CPU',1,1)) "TOTAL",

event

from gv$active_session_history ash, v$event_name en

where en.event# = ash.event#

-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second

-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1' DAY

and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '5' minute

group by '''' || ash.SESSION_ID || ',' || ash.session_serial# ||',@'|| ash.inst_id||'''',ash.con_id,ash.sql_id,user_id,program,event

order by sum(decode(session_state,'ON CPU',1,1));

col DB_NAME for A40

col BEGIN_INTERVAL_TIME for a22

col idle for 999

col sys for 999

col user1 for 999

col nice for 999

set lin 1000 pages 1000

col db_name for a40

col cpus1 for 9999

select (select instance_name||'@'||substr(host_name,-(length(host_name))) from v$instance)db_name ,cpus1,sess,date1, idle,sys,user1,nice,sql.cnt ,sql.CPU,sql.ELAPSED,sql.iowait,sql.exec

from

(

select count(1)sess,os.snap_ID ,cpus1, to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi')date1, idle,sys,user1,nice

from

(

select

SNAP_ID,

cpus1,round(idle1/(idle1+user1+sys1+iowait1+nice1),2)*100 idle,

round(sys1/(idle1+user1+sys1+iowait1+nice1),2)*100 sys,

round(user1/(idle1+user1+sys1+iowait1+nice1),2)*100 user1,

round(nice1/(idle1+user1+sys1+iowait1+nice1),2)*100 nice

from

(SELECT SNAP_ID,

sum(decode(stat_name,'NUM_CPUS',value,0)) cpus1,

sum(decode(stat_name,'IDLE_TIME',value,0)) idle1,

sum(decode(stat_name,'USER_TIME',value,0)) user1,

sum(decode(stat_name,'SYS_TIME',value,0)) sys1,

sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait1,

sum(decode(stat_name,'NICE_TIME',value,0)) nice1

FROM DBA_HIST_OSSTAT group by SNAP_ID))os

, DBA_HIST_snapshot sn

, (select * from dba_hist_active_sess_history where user_id in (select user_id from dba_users where username in ('SYS','SYSTEMS'))) ash

where os.SNAP_ID=sn.SNAP_ID

and sn.SNAP_ID=ash.SNAP_ID

and ash.snap_id=os.snap_id

group by os.SNAP_ID,cpus1,to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi'), idle,sys,user1,nice

)aa, (select snap_id,count(1)cnt,round(sum(CPU_TIME_TOTAL/1000000),0)CPU,round(sum(ELAPSED_TIME_TOTAL/1000000),0)ELAPSED

,round(sum(IOWAIT_TOTAL/1000000),0)IOWAIT,round(sum(EXECUTIONS_TOTAL),0)exec from DBA_HIST_SQLSTAT group by snap_id) sql

where sql.snap_id=aa.snap_id order by 3;

============

-------------------------
--
-- What did that SID do?
--
-------------------------

select distinct inst_id, sql_id, session_serial#,sql_id from gv$active_session_history
where sample_time > sysdate - interval '5' minute
and session_id=&sid;

----------------------------------------------
--
-- Retrieve the SQL from the Library Cache:
--
----------------------------------------------

col sql_text for a80
select sql_text from gv$sql where sql_id='&sqlid';

Oracle Table Info ..

accept owner prompt 'Enter table owner: '
accept table_name prompt 'Enter table name: '
REM
REM Table Definition
REM
describe &owner..&table_name

REM
REM Table and Indexes Info .....
REM
set wrap off linesize 200 pages 200 numf 99999999999999999999999999999999999999
col COLUMN_NAME for a20
col TABLE_NAME for a25
col TABLESPACE_NAME for a15
col Table for a25
col type for a8
col Ind for a25
col Col for a20
col Pos for 990
col Tbs for a20
col unq for a3
col deg for a8
select t.table_name "Table" ,decode(t.index_type,'NORMAL','BTree','BITMAP','Bitmap','FUNCTION-BASED NORMAL','Function-Based BTree',t.index_type) "Type" ,t.status ,t.index_name "Ind" ,c.column_name "Col" ,c.column_position "Pos" ,decode(t.uniqueness,'UNIQUE','UNQ',NULL) "unq" ,
t.partitioned "Prt" ,t.degree "deg" ,t.NUM_ROWS,SAMPLE_SIZE,t.CLUSTERING_FACTOR,t.TABLESPACE_NAME,t.LAST_ANALYZED
from dba_indexes t , dba_ind_columns c
where t.table_name = c.table_name
and t.index_name = c.index_name
and t.owner = upper('&owner')
and t.table_name = upper('&table_name')
and t.index_type not in ('IOT - TOP','LOB')
order by t.table_name, t.index_name, c.column_position;
/

REM
REM Column Definitions
REM
select column_name, num_distinct, num_nulls, num_buckets, density, sample_size from dba_tab_columns
WHERE upper(owner) = upper('&owner')
AND upper(table_name) = upper('&table_name')
order by column_name
/

REM
REM Existing Histograms
REM
SELECT column_name, endpoint_number, endpoint_value FROM dba_histograms
WHERE upper(table_name) = upper('&table_name')
AND upper(owner) = upper('&owner')
ORDER BY column_name, endpoint_number
/

REM
REM Row Counts
REM
SELECT table_name, num_rows, degree, last_analyzed FROM dba_tables
WHERE upper(owner) = upper('&owner')
AND upper(table_name) = upper('&table_name')
/

REM
REM Table and Indexes - Segment Sizes
REM
column segment_name format a50
SELECT segment_name, segment_type, SUM(bytes)/1024/1024 size_mb FROM dba_segments
WHERE upper(owner) = upper('&owner')
AND upper(segment_name) = upper('&table_name')
OR segment_name in (select index_name from dba_indexes
where upper(table_name) = upper('&table_name')
and upper(table_owner) = upper('&owner'))
GROUP BY segment_name, segment_type
/

REM uncomment this line if you want "live" row counts
REM - for large tables this could run for a while and cause performance problems
REM
REM select count(*) from "&owner"."&table_name"
REM /

Sunday, 3 January 2016

Oracle RAC session info

Oracle Session Per Hour for Each Instance ..

sessions per hour for EACH INSTANCE in a RAC

Oracle sessions per hour for EACH INSTANCE in a RAC

How to find number of sessions per hour node wise .

set linesize 200 pagesize 200
SELECT to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') snap_begin, r.instance_number instance,r.current_utilization sessions FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE ( TRUNC(s.begin_interval_time,'HH24'),s.snap_id ) IN
(
--Select the Maximum of the Snapshot IDs within an hour if all of the snapshot IDs
--have the same number of sessions
SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id) FROM dba_hist_resource_limit rl,dba_hist_snapshot sn
WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-1)
AND rl.snap_id = sn.snap_id
AND rl.resource_name = 'sessions'
AND rl.instance_number = sn.instance_number
AND ( TRUNC(sn.begin_interval_time,'HH24'),rl.CURRENT_UTILIZATION ) IN
(
--Select the Maximum no.of sessions for a given begin interval time
SELECT TRUNC(s.begin_interval_time,'HH24'),MAX(r.CURRENT_UTILIZATION) "no_of_sess" FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE r.snap_id = s.snap_id
AND TRUNC(s.begin_interval_time) >= TRUNC(sysdate-1)
AND r.instance_number=s.instance_number
AND r.resource_name = 'sessions'
GROUP BY TRUNC(s.begin_interval_time,'HH24')
)
GROUP BY TRUNC(sn.begin_interval_time,'HH24'),CURRENT_UTILIZATION
)
AND r.snap_id = s.snap_id
AND r.instance_number = s.instance_number
AND r.resource_name = 'sessions'
ORDER BY snap_begin,instance

SNAP_BEGIN INSTANCE SESSIONS
----------------------------- ---------- ----------
10-APR-2016 00:00:00 1 81
10-APR-2016 00:00:00 2 79
10-APR-2016 01:00:00 1 85
10-APR-2016 01:00:00 2 82
10-APR-2016 02:00:00 1 84
10-APR-2016 02:00:00 2 84
10-APR-2016 03:00:00 1 83
10-APR-2016 03:00:00 2 81
10-APR-2016 04:00:00 1 83
10-APR-2016 04:00:00 2 81
10-APR-2016 05:00:00 1 84
10-APR-2016 05:00:00 2 86
10-APR-2016 06:00:00 1 83
10-APR-2016 06:00:00 2 80
10-APR-2016 07:00:00 1 83
10-APR-2016 07:00:00 2 77

How to find number of sessions per hour

set linesize 200 pagesize 200
SELECT
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') snap_begin,
sum(r.current_utilization) sessions
FROM
dba_hist_resource_limit r,
dba_hist_snapshot s
WHERE ( TRUNC(s.begin_interval_time,'HH24'),s.snap_id ) IN
(
--Select the Maximum of the Snapshot IDs within an hour if more than one snapshot IDs
--have the same number of sessions within that hour , so then picking one of the snapIds
SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id)
FROM dba_hist_resource_limit rl,dba_hist_snapshot sn
WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-1)
AND rl.snap_id = sn.snap_id
AND rl.resource_name = 'sessions'
AND rl.instance_number = sn.instance_number
AND ( TRUNC(sn.begin_interval_time,'HH24'),rl.CURRENT_UTILIZATION ) IN
(
--Select the Maximum no.of sessions for a given begin interval time
-- All the snapshots within a given hour will have the same begin interval time when TRUNC is used
-- for HH24 and we are selecting the Maximum sessions for a given one hour
SELECT TRUNC(s.begin_interval_time,'HH24'),MAX(r.CURRENT_UTILIZATION) "no_of_sess"
FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE r.snap_id = s.snap_id
AND TRUNC(s.begin_interval_time) >= TRUNC(sysdate-1)
AND r.instance_number=s.instance_number
AND r.resource_name = 'sessions'
GROUP BY TRUNC(s.begin_interval_time,'HH24')
)
GROUP BY TRUNC(sn.begin_interval_time,'HH24'),CURRENT_UTILIZATION
)
AND r.snap_id = s.snap_id
AND r.instance_number = s.instance_number
AND r.resource_name = 'sessions'
GROUP BY
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS')
ORDER BY snap_begin ;

SNAP_BEGIN SESSIONS
----------------------------- ----------
10-APR-2016 00:00:00 160
10-APR-2016 01:00:00 167
10-APR-2016 02:00:00 168
10-APR-2016 03:00:00 164
10-APR-2016 04:00:00 164
10-APR-2016 05:00:00 170
10-APR-2016 06:00:00 163
10-APR-2016 07:00:00 160
10-APR-2016 08:00:00 157
10-APR-2016 09:00:00 157
10-APR-2016 10:00:00 157
10-APR-2016 11:00:00 162
10-APR-2016 12:00:00 162
10-APR-2016 13:00:00 163
10-APR-2016 14:00:00 168
10-APR-2016 15:00:00 160
10-APR-2016 16:00:00 160
10-APR-2016 17:00:00 157
10-APR-2016 18:00:00 154
10-APR-2016 19:00:00 154
10-APR-2016 20:00:00 159
10-APR-2016 21:00:00 160
10-APR-2016 22:00:00 160
10-APR-2016 23:00:00 160
11-APR-2016 00:00:00 164
11-APR-2016 01:00:00 164
11-APR-2016 02:00:00 162
11-APR-2016 03:00:00 157
11-APR-2016 04:00:00 155

29 rows selected.

Saturday, 19 December 2015

Oracle Standby out of Sync ..

Oracle Standby out of Sync ...

. Standby roll forward using RMAN incremental backup

How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary?

On standby

Identify the lowest SCN on the Standby..

select MIN(scn) Min_Scn from (SELECT to_char(CURRENT_SCN) scn FROM V$DATABASE

union

select to_char(min(fhscn)) from x$kcvfh

union

select to_char(min(f.fhscn)) from x$kcvfh f, v$datafile d

where f.hxfil =d.file#

and d.enabled != 'READ ONLY'

);

MIN_SCN

----------------------------------------

12066937215

Use this scn no to take backup on primary .

RMAN> BACKUP INCREMENTAL FROM SCN 12066937215 DATABASE FORMAT '/u01/rman_backup/stdby20140828/ForStandby_%U' tag 'FORSTANDBY';

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u01/rman_backup/stdby20140828/ForStandbyCTRL.bck';

cd /u01/rman_backup/

create scp script for copy file

ls -1 *For* |awk '{print " scp -q " $1 " " "akl0std:/u02/DISK_BACKUP/ &" }'

after copy the file

On Standby ...

cancel the recovery ..

alter database recover managed standby database disconnectfrom session;

Database altered.

check for MRP0 process ..

IMP This process should not be running .

break on INST_ID

set pages 1000 lines 150

COL GROUP# for a15

col status for a20

select INST_ID,PROCESS,STATUS,SEQUENCE#,THREAD#,GROUP#,PID from gV$MANAGED_STANDBY

order by INST_ID,STATUS;

on OS

ps -ef|grep -i mrp

RMAN> SHUTDOWN IMMEDIATE ;

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE STANDBY CONTROLFILE FROM '/u02/DISK_BACKUP/ForStandbyCTRL.bck';

Starting restore at 25-NOV-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=345 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

output file name=+DATA/prod/control01.ctl

output file name=+DATA/prod/control02.ctl

Finished restore at 25-NOV-15

RMAN> shutdown;

database closed

database dismounted

Oracle instance shut down

RMAN> startup mount

RMAN> CATALOG START WITH '/u02/DISK_BACKUP';

RMAN> RECOVER DATABASE NOREDO;

If there are datafiles created on the primary database after the last SCN on standby

SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 12066937215;

Perform the switch so that the controlfile points to the datafiles under the standby directory.

RMAN> SWITCH DATABASE TO COPY;

To start recovery On standby !!!!

sqlplus

alter database recover managed standby database using current logfile disconnect;

On Standby:

MRP identifier

break on INST_ID

set pages 1000 lines 150

COL GROUP# for a15

col status for a20

select INST_ID,PROCESS,STATUS,SEQUENCE#,THREAD#,GROUP#,PID from GV$MANAGED_STANDBY

order by INST_ID,STATUS;

Metalink Note ..

standby database using RMAN Incremental Backup. (Doc ID 836986.1)

standby database using RMAN incremental backup when datafile is added to primary (Doc ID 1531031.1) <<<<<<<

Imp part

8) Rename the datafiles in new standby controlfile !!!! if location different on Standby

Since the controlfile is restored from PRIMARY the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.

Perform the below step in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.

on standby

RMAN> CATALOG START WITH '+DATA/<db_unqiue_name>/datafile/';

RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE NOREDO;

=======

Copy database file directly across network using RMAN in 11g including Primary and Standby (Doc ID 1909235.1)

rman target sys/<password>@<prmy_tns> auxiliary sys/<password>@<stby_tns>

run {

allocate channel d1 type disk ;

allocate channel d2 type disk ;

backup as copy reuse

datafile 4 auxiliary format '/u01/app/oracle/oradata/stby/users01.dbf'

datafile 5 auxiliary format '/u01/app/oracle/oradata/stby/example01.dbf'

;

}

Thursday, 17 December 2015

Oracle Database Info ... from v$database

PROMPT ---- Oracle 9i

PROMPT *********************************************

PROMPT * D A T A B A S E I N F O R M A T I O N

PROMPT *********************************************

PROMPT

COLUMN column1 FORMAT a70

COLUMN column2 FORMAT a60

set pages 0 linesize 200

SELECT /* First Column */

'[DB Info]' || chr(10)

|| TRIM(SUBSTR('DB ID : ' || d.dbid ,1,50)) || chr(10)

|| TRIM(SUBSTR('DB Name : ' || d.name ,1,50)) || chr(10)

|| TRIM(SUBSTR('DB Created : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Open Mode : ' || d.open_mode ,1,50)) || chr(10)

|| TRIM(SUBSTR('Open Resetlogs : ' || d.open_resetlogs ,1,50)) || chr(10)

|| TRIM(SUBSTR('ArchiveLog Mode : ' || d.log_mode ,1,50)) || chr(10)

|| TRIM(SUBSTR('Force Logging : ' || d.force_logging ,1,50)) || chr(10)

|| TRIM(SUBSTR('Remote Archive : ' || d.remote_archive ,1,50)) || chr(10)

|| chr(10)

|| '[Timestamps]' || chr(10)

|| TRIM(SUBSTR('DB Created : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile Created : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile Time : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Version Time : ' || TO_CHAR(d.version_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Resetlogs Time : ' || TO_CHAR(d.resetlogs_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Prior Resetlogs Time : ' || TO_CHAR(d.prior_resetlogs_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| chr(10)

|| '[System Change Number]' || chr(10)

|| TRIM(SUBSTR('Resetlogs SCN : ' || d.resetlogs_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Prior Resetlogs SCN : ' || d.prior_resetlogs_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Checkpoint SCN : ' || d.checkpoint_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile SCN : ' || d.controlfile_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Archivelog Highest NextSCN : ' || d.archivelog_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Force Archivelog SCN : ' || d.archive_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Archivelog SCN : ' || d.archive_change# ,1,50)) || chr(10)

|| chr(10)

|| '[Controlfile Info]' || chr(10)

|| TRIM(SUBSTR('Controlfile Type : ' || d.controlfile_type ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile Created : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile SCN : ' || d.controlfile_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile Sequence# : ' || d.controlfile_sequence# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile Time : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

column1

FROM v$database d ;

PROMPT ---- Oracle 10g

PROMPT *********************************************

PROMPT * D A T A B A S E I N F O R M A T I O N

PROMPT *********************************************

PROMPT

COLUMN column1 FORMAT a70

COLUMN column2 FORMAT a60

set pages 0 linesize 200

SELECT /* First Column */

'[DB Info]' || chr(10)

|| TRIM(SUBSTR('DB ID : ' || d.dbid ,1,50)) || chr(10)

|| TRIM(SUBSTR('DB Name : ' || d.name ,1,50)) || chr(10)

|| TRIM(SUBSTR('DB Unique Name : ' || d.db_unique_name ,1,50)) || chr(10)

|| TRIM(SUBSTR('Platform : ' || d.platform_name ,1,50)) || chr(10)

|| TRIM(SUBSTR('DB Created : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Open Mode : ' || d.open_mode ,1,50)) || chr(10)

|| TRIM(SUBSTR('Open Resetlogs : ' || d.open_resetlogs ,1,50)) || chr(10)

|| TRIM(SUBSTR('Flashback ON : ' || d.flashback_on ,1,50)) || chr(10)

|| TRIM(SUBSTR('ArchiveLog Mode : ' || d.log_mode ,1,50)) || chr(10)

|| TRIM(SUBSTR('ArchiveLog Compression : ' || d.archivelog_compression ,1,50)) || chr(10)

|| TRIM(SUBSTR('Force Logging : ' || d.force_logging ,1,50)) || chr(10)

|| TRIM(SUBSTR('Remote Archive : ' || d.remote_archive ,1,50)) || chr(10)

|| TRIM(SUBSTR('Last Open Incarnation# : ' || d.last_open_incarnation# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Recovery Target Inc# : ' || d.recovery_target_incarnation# ,1,50)) || chr(10)

|| chr(10)

|| '[Timestamps]' || chr(10)

|| TRIM(SUBSTR('DB Created : ' || TO_CHAR(d.created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile Created : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile Time : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Version Time : ' || TO_CHAR(d.version_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Resetlogs Time : ' || TO_CHAR(d.resetlogs_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| TRIM(SUBSTR('Prior Resetlogs Time : ' || TO_CHAR(d.prior_resetlogs_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

|| chr(10)

|| '[System Change Number]' || chr(10)

|| TRIM(SUBSTR('Current SCN : ' || d.current_scn ,1,50)) || chr(10)

|| TRIM(SUBSTR('Resetlogs SCN : ' || d.resetlogs_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Prior Resetlogs SCN : ' || d.prior_resetlogs_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Checkpoint SCN : ' || d.checkpoint_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile SCN : ' || d.controlfile_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Archivelog Highest NextSCN : ' || d.archivelog_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Force Archivelog SCN : ' || d.archive_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Archivelog SCN : ' || d.archive_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Standby Became Primary SCN : ' || d.standby_became_primary_scn ,1,50)) || chr(10)

|| chr(10)

|| '[Controlfile Info]' || chr(10)

|| TRIM(SUBSTR('Controlfile Type : ' || d.controlfile_type ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile Created : ' || TO_CHAR(d.controlfile_created,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

-- || TRIM(SUBSTR('Controlfile Converted : ' || d.controlfile_converted ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile SCN : ' || d.controlfile_change# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile Sequence# : ' || d.controlfile_sequence# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Controlfile Time : ' || TO_CHAR(d.controlfile_time,'DD-MON-YYYY HH24:MI:SS') ,1,50)) || chr(10)

column1

'[DataGuard Information]' || chr(10)

--|| TRIM(SUBSTR('Primary DB Unique Name : ' || d.primary_db_unique_name ,1,50)) || chr(10)

|| TRIM(SUBSTR('DataGuard Role : ' || d.database_role ,1,50)) || chr(10)

|| TRIM(SUBSTR('Protection Mode : ' || d.protection_mode ,1,50)) || chr(10)

|| TRIM(SUBSTR('Protection Level : ' || d.protection_level ,1,50)) || chr(10)

-- || TRIM(SUBSTR('DataGuard Broker : ' || d.dataguard_broker ,1,50)) || chr(10)

|| TRIM(SUBSTR('DataGuard Status : ' || d.guard_status ,1,50)) || chr(10)

|| TRIM(SUBSTR('SwitchOver Status : ' || d.switchover_status ,1,50)) || chr(10)

|| TRIM(SUBSTR('Activation SCN : ' || d.activation# ,1,50)) || chr(10)

|| TRIM(SUBSTR('SwitchOver SCN : ' || d.switchover# ,1,50)) || chr(10)

|| TRIM(SUBSTR('Standby Became Primary SCN : ' || d.standby_became_primary_scn ,1,50)) || chr(10)

|| TRIM(SUBSTR('Supplemental Log Data MIN : ' || d.supplemental_log_data_min ,1,50)) || chr(10)

|| TRIM(SUBSTR('Supplemental Log Data PK : ' || d.supplemental_log_data_pk ,1,50)) || chr(10)

|| TRIM(SUBSTR('Supplemental Log Data UI : ' || d.supplemental_log_data_ui ,1,50)) || chr(10)

-- || TRIM(SUBSTR('Supplemental Log Data PL : ' || d.supplemental_log_data_pl ,1,50)) || chr(10)

|| chr(10)

|| '[Fast Start Failover Info]' || chr(10)

|| TRIM(SUBSTR('FS Failover Status : ' || d.fs_failover_status ,1,50)) || chr(10)

|| TRIM(SUBSTR('FS Failover Current Target : ' || d.fs_failover_current_target ,1,50)) || chr(10)

|| TRIM(SUBSTR('FS Failover Threshold : ' || d.fs_failover_threshold ,1,50)) || chr(10)

|| TRIM(SUBSTR('FS Failover Observer Present: ' || d.fs_failover_observer_present ,1,50)) || chr(10)

--|| TRIM(SUBSTR('FS Failover Observer Host : ' || d.fs_failover_observer_host ,1,50)) || chr(10)

column2

FROM v$database d ;

PROMPT ----oracle 11gr2 and Oracle 12c<<<<<<<<<<<<<<<