Database
Creation Script:
STARTUP NOMOUNT
To
know the table information at a particular point of time:
select count(*) from scott.dept as
of timestamp to_timestamp('10-Mar-2015 03:22','DD-MM-YY HH24:MM');
Modifying
the Column Size:
Alter table
<owner>.<table_name> MODIFY(<Cououmn_name> datatype(size));
Script_Prelim
Connection:
[oracle@orcdb01 ~]$ sqlplus -prelim
/nolog
SQL*Plus: Release 11.2.0.2.0
Production on Mon Nov 3 20:53:35 2014
Copyright (c) 1982, 2010, Oracle.
All rights reserved.
SQL> connect / as sysdba
Shut immediate won't work, We have
to use shut abort
Retrieving
DDL of the objects:
DDL of
user:
select
dbms_metadata.get_ddl('USER','&username') as DDL from dual;
DDL of
tablespace:
select dbms_metadata.get_ddl('TABLESPACE','&tablespace_name')
as DDL from dual;
DDL of
synonym:
select
dbms_metadata.get_ddl('SYNONYM','&synonym_name') as DDL from dual;
DDL of
View:
select
dbms_metadata.get_ddl('VIEW','&view_name') as DDL from dual;
DDL of Job:
select dbms_metadata.get_ddl('PROCOBJ','&JOB-NAME','&OWNER')
as DDL from dual;
Note: DDL of jobs owned by syscan not be
retrieved using the above query
DDL of
Package and Package body:
set
heading off;
set
longchunksize 100000
col
object_ddl format A32000;
spool
ddl_extracted_package.sql;
select
dbms_metadata.get_ddl('PACKAGE','&PACKAGE_NAME','&OWNER') from dual ;
spool
off;
set
heading off;
set
longchunksize 100000
col
object_ddl format A32000;
spool
ddl_extracted_package_body.sql;
select
dbms_metadata.get_ddl('PACKAGE_BODY','&PACKAGE_NAME','&OWNER') from
dual ;
spool
off;
DDL of
Procedure:
set
heading off;
set
longchunksize 100000
col
object_ddl format A32000;
spool
ddl_extracted_procedure.sql;
select
dbms_metadata.get_ddl('PROCEDURE','&PROCEDURE_NAME','&OWNER') from dual
;
spool
off;
Tablespace
usage:
col autoex for a10 head
'Auto|Extend'
SELECT TabSpaceName,
select file_id,file_name,autoextensible,ceil(maxbytes/(1024*1024))
maxsize_mb, ceil(bytes/(1024*1024)) cursize_mb,status from dba_data_files where
tablespace_name=’&TABLESPACE_NAME’;
Script_Retrieving contents
from sysaux:
set lines 300 pages 300
SQL> select
OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE,TS_NAME,CREATETIME,DROPTIME,CAN_PURGE
from DBA_RECYCLEBIN where TS_NAME='SYSAUX' order by CREATETIME,DROPTIME;
no rows selected
TEMP
tablespace usage:
SQL> select
tablespace_name,current_users,round (USED_BLOCKS*8/1024/1024,2) "GB in
Use",
SQL> SELECT s.sid,
s.username,s.status, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value,
u.segtype, u.contents, u.blocks
select
sid,serial#,USERNAME,STATUS,PROGRAM,SQL_ADDRESS,SQL_HASH_VALUE,SQL_ID,CLIENT_INFO,SECONDS_IN_WAIT,STATE
from v$session where sid=&sid;
Query
to find the ongoing transactions :
select start_time, username,
r.name,
Undo Tablespace Usage by each user:
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)
sid_serial,NVL(s.username, 'None') orauser,
select start_time, username,
r.name,
order by 1;
Script
Tablespace Fragmentation:
Reorganization ensures
that all tables and indexes do not have row fragmentation, and that they reside
in a single extent, with all free space in a tablespace in a single, contiguous
chunk. How a tablespace may become fragmented. At initial load time, all Oracle
tables within the tablespace are contiguous--that is, only one chunk of free
space resides at the end of the tablespace. As tables extend and new
extents are added to the tablespace, the free space becomes smaller but it
still remains contiguous.
Basically, a table can
fragment in two ways:
* A table
extends (without row chaining)--Contrary to popular belief, this is not a
problem and performance will not suffer.
* Rows
fragment within the tablespace (due to SQL UPDATES)--This causes a serious
performance problem, and the offending tables must be exported, dropped, and
re-imported.
set linesize 150
Script_Finding
SPID:
set line 200
Find
Report User SID, PID, and User Name:
Script to Report User
SID, PID, and User Name
This script provides a
list of SID's, System PID's and usernames of current users.
Sample Output
Copy Script to Clipboard
REM LOCATION:
System Monitoring\Reports
COLUMN sid
format 9999 heading "SID"
SID PID
User Name
OS
User
Script_Find
SQL being executed by a OS Process ID (PID)_SPID:
Script – Find SQL being executed by
a OS Process ID (PID)
Script_Locks
on a table:
column oracle_username format a18
Script_long_running_query:
SELECT SID,
Script_PGA
Usage Script:
set head off
COLD
backup using RMAN Script:
run{
RMAN
Backup Failure:
select
operation,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,to_char(END_TIME,'mm/dd/yy
hh24:mi') end_time,status,object_type from v$rman_status order by 2;
col STATUS format a9
Archive
logs generated per every one hour:
SELECT TO_CHAR (COMPLETION_TIME,
'DD/MM/YYYY') DAY,
SUM
(DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
DAY
00-01
01-02 02-03 03-04
04-05 05-06 06-07
07-08 08-09
Number
of log switches per day and Size of Archive logs:
set pagesize 10000 linesize 10000
date
size_gb number_of_switches_per_day
select trunc(COMPLETION_TIME) TIME,
SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc
(COMPLETION_TIME) order by 1;
TIME SIZE_MB
Number of log switches per week and total Size of Archive logs:
select round(sum((blocks + 1)
* block_size)
weekly_size_gb
number_of_switches_per_week
Calculating
Estimated Restoration completion time:
Estimated time of Backup / Restore
via RMAN
Below, a query to be executed on
target database or database auxiliary ( in the case of duplicate) to estimate
the time of restore and the completed percentage .
col OPNAME for a30
Output example of a duplicate:
OPNAME PCT MIN_RESTANTES
MIN_ATEAGORA
Reading the Output:
The line ( RMAN: aggregate input )
is the sum of total and it is showing that was executed 67,60% of total of
restore, and took until now, 368 minutes, and estimates that rest 176 minutes.
The other lines are of partials (
RMAN: full datafile restore ), we have many completed ( pct=100) , and two
restore of pieces in execution, that will still take 26 minutes, the first, and
25 minutes the second.
Script_Retriving
Information from Listener.log:
timestamp*connect info* protocol
tcp/ip , spx, port * sid * return value
grep "08-FEB"
listener.log | awk '{ if ( $NF != 0 ) print $0 }'
https://martincarstenbach.wordpress.com/2010/02/09/check-for-non-successful-connection-attemps-in-listener-log/
grep "05-SEP" listener.log
| awk '{ if ( $NF != 0) print $0}'
Oracle
Management Service (OMS) Commands:
emctl list oms // The command will
prompt for the SYSMAN user's password and list the Server where OMS is running.
emctl stop oms -all
The above command will stop all the
OMS services Web Tier, Oracle Management Server and Admin Server.
emctl stop oms
-all
emctl start oms
emctl start oms
emctl status oms
~]$ emctl status oms
Scheduler
Job Queries:
job_schedules.sql:
SET LINESIZE 250
COLUMN owner FORMAT A20
SELECT owner,
job_classes:
SET LINESIZE 200
COLUMN service FORMAT A20
SELECT job_class_name,
job_programs:
SET LINESIZE 250
COLUMN owner FORMAT A20
SELECT owner,
jobs_running:
SET LINESIZE 200
COLUMN owner FORMAT A20
SELECT owner,
Disabling
Scheduler Jobs:
exec
dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
exec
dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');
select name from v$database;
select JOB_NAME,STate from
ALL_SCHEDULER_JOBS where owner='ORACLE_OCM';
JOB_NAME
STATE
Partitioning
Commands:
set lines 300
set lines 300
select TABLE_NAME,PARTITION_COUNT,PARTITIONING_TYPE,PARTITIONING_KEY_COUNT,STATUS
from dba_part_tables where TABLE_NAME='&TABLE_NAME';
select view_name from dba_views
where view_name like '%PARTITION%';
Script_to
find dumpfile version:
Database
Creation Script:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE
"<DB_NAME>" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1
'<PATH>/redo01.log' SIZE 50M,
GROUP 2
'<PATH>/redo02.log' SIZE 50M,
GROUP 3
'<PATH>/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'<PATH>/system01.dbf',
'<PATH>/sysaux01.dbf',
'<PATH>/undotbs01.dbf',
'<PATH>/users01.dbf',
CHARACTER SET AL32UTF8;
To
know the table information at a particular point of time:
select count(*) from scott.dept as
of timestamp to_timestamp('10-Mar-2015 03:22','DD-MM-YY HH24:MM');
Modifying
the Column Size:
Alter table
<owner>.<table_name> MODIFY(<Cououmn_name> datatype(size));
Script_Prelim
Connection:
[oracle@orcdb01 ~]$ sqlplus -prelim
/nolog
SQL*Plus: Release 11.2.0.2.0
Production on Mon Nov 3 20:53:35 2014
Copyright (c) 1982, 2010, Oracle.
All rights reserved.
SQL> connect / as sysdba
Prelim connection established
Shut immediate won't work, We have
to use shut abort
Retrieving
DDL of the objects:
DDL of
user:
select
dbms_metadata.get_ddl('USER','&username') as DDL from dual;
DDL of
tablespace:
select dbms_metadata.get_ddl('TABLESPACE','&tablespace_name')
as DDL from dual;
DDL of
synonym:
select
dbms_metadata.get_ddl('SYNONYM','&synonym_name') as DDL from dual;
DDL of
View:
select
dbms_metadata.get_ddl('VIEW','&view_name') as DDL from dual;
DDL of Job:
select dbms_metadata.get_ddl('PROCOBJ','&JOB-NAME','&OWNER')
as DDL from dual;
Note: DDL of jobs owned by syscan not be
retrieved using the above query
DDL of
Package and Package body:
set
heading off;
set
echo off;
set
pagesize 0;
set
serveroutput on
set
verify off lines 132
set
longchunksize 100000
set
long 10000000;
set
linesize 32767;
set
trimspool on;
col
object_ddl format A32000;
spool
ddl_extracted_package.sql;
select
dbms_metadata.get_ddl('PACKAGE','&PACKAGE_NAME','&OWNER') from dual ;
spool
off;
set
heading off;
set
echo off;
set
pagesize 0;
set
serveroutput on
set
verify off lines 132
set
longchunksize 100000
set
long 10000000;
set
linesize 32767;
set
trimspool on;
col
object_ddl format A32000;
spool
ddl_extracted_package_body.sql;
select
dbms_metadata.get_ddl('PACKAGE_BODY','&PACKAGE_NAME','&OWNER') from
dual ;
spool
off;
DDL of
Procedure:
set
heading off;
set
echo off;
set
pagesize 0;
set
serveroutput on
set
verify off lines 132
set
longchunksize 100000
set
long 10000000;
set
linesize 32767;
set
trimspool on;
col
object_ddl format A32000;
spool
ddl_extracted_procedure.sql;
select
dbms_metadata.get_ddl('PROCEDURE','&PROCEDURE_NAME','&OWNER') from dual
;
spool
off;
Tablespace
usage:
Script_Retrieving contents
from sysaux:
set lines 300 pages 300
col OWNER for a10
col OBJECT_NAME for a25
col ORIGINAL_NAME for a25
col TYPE for a10
col TS_NAME for a8
col PARTITION_NAME for a25
select
OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE,TS_NAME,CREATETIME,DROPTIME,CAN_PURGE
from DBA_RECYCLEBIN where TS_NAME='SYSAUX' order by CREATETIME,DROPTIME
SQL> select
OWNER,OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE,TS_NAME,CREATETIME,DROPTIME,CAN_PURGE
from DBA_RECYCLEBIN where TS_NAME='SYSAUX' order by CREATETIME,DROPTIME;
no rows selected
TEMP
tablespace usage:
SQL> select
tablespace_name,current_users,round (USED_BLOCKS*8/1024/1024,2) "GB in
Use",
round(FREE_BLOCKS*8/1024/1024,2)
"Free GB",round(MAX_SIZE/1024,2) "Msize GB",
round(MAX_USED_BLOCKS*8/1024/1024,2)
"Max used GB"
from v$sort_segment;
SQL> SELECT s.sid,
s.username,s.status, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value,
u.segtype, u.contents, u.blocks
FROM v$session s,
v$tempseg_usage u
WHERE
s.saddr=u.session_addr
order by u.blocks;
Query
to find the ongoing transactions :
select start_time, username,
r.name,
ubafil, ubablk, t.status,
(used_ublk*p.value)/1024 blk, used_urec
from v$transaction t, v$rollname r,
v$session s, v$parameter p
where xidusn=usn
and s.saddr=t.ses_addr
and p.name='db_block_size'
order by 1;
Undo Tablespace Usage by each user:
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)
sid_serial,NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk *
TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';
select start_time, username,
r.name,
ubafil, ubablk, t.status,
(used_ublk*p.value)/1024 blk, used_urec
from v$transaction t, v$rollname r,
v$session s, v$parameter p
where xidusn=usn
and s.saddr=t.ses_addr
and p.name='db_block_size'
order by 1;
Script
Tablespace Fragmentation:
Reorganization ensures
that all tables and indexes do not have row fragmentation, and that they reside
in a single extent, with all free space in a tablespace in a single, contiguous
chunk. How a tablespace may become fragmented. At initial load time, all Oracle
tables within the tablespace are contiguous--that is, only one chunk of free
space resides at the end of the tablespace. As tables extend and new
extents are added to the tablespace, the free space becomes smaller but it
still remains contiguous.
Basically, a table can
fragment in two ways:
* A table
extends (without row chaining)--Contrary to popular belief, this is not a
problem and performance will not suffer.
* Rows
fragment within the tablespace (due to SQL UPDATES)--This causes a serious
performance problem, and the offending tables must be exported, dropped, and
re-imported.
set linesize 150
column tablespace_name format a20 heading 'Tablespace'
column sumb format 999,999,999
column
extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999,999 Heading 'Total| Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(MB)'
column Pct_Free format 999.99 heading '% Free'
column Chunks_Free format 99999999 heading 'No Of Ext.'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
set
echo off
PROMPT FREE SPACE AVAILABLE IN TABLESPACES
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1048576) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from
dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by pct_free;
Script_Finding
SPID:
set line 200
col PROGRAM for a45
col username for a12
col machine for a50
select
sid,s.serial#,spid,s.username,s.machine,s.program,status from v$process
p,v$session s where addr=paddr and sid in(880);
Find
Report User SID, PID, and User Name:
Script to Report User
SID, PID, and User Name
This script provides a
list of SID's, System PID's and usernames of current users.
Sample Output
Copy Script to Clipboard
REM LOCATION:
System Monitoring\Reports
REM FUNCTION:
Provide list of SID, System PID and usernames of
REM
current users
REM TESTED ON:
7.3.3.5, 8.0.4.1, 8.1.5, 8.1.7, 9.0.1, 10.2.0.3, 11.1.0.6
REM PLATFORM:
non-specific
REM REQUIRES:
v$session a, v$process b
REM
REM This is a part
of the Knowledge Xpert for Oracle Administration library.
REM Copyright (C)
2008 Quest Software
REM All rights
reserved.
REM
REM********************
Knowledge Xpert for Oracle Administration ********************
COLUMN sid
format 9999 heading "SID"
COLUMN spid
format 9999 heading
"PID"
COLUMN username
format a30 heading "User
Name"
COLUMN osuser
format a30 heading "OS
User"
SET FEEDBACK OFF VERIFY
OFF
SET lines 132 pages 59
TTITLE left _date center
'Oracle System Users' skip 2
SELECT a.sid, b.spid,
a.username, a.osuser
FROM v$session a,
v$process b
WHERE a.paddr =
b.addr(+)
/
TTITLE off
CLEAR columns
SID PID
User Name
OS
User
-----
------------------------ ------------------------------
------------------------------
262 29728
RRSPICER
rrspicer
Script_Find
SQL being executed by a OS Process ID (PID)_SPID:
Script – Find SQL being executed by
a OS Process ID (PID)
prompt "Please Enter The UNIX
Process ID"
set pagesize 50000
set linesize 30000
set long 500000
set head off
select
s.username su,
substr(sa.sql_text,1,540) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and
s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;
Script_Locks
on a table:
column oracle_username format a18
column os_user_name format a15
column object_name format a28
column object_type format a25
select
a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT
OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID,
ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner,
object_name,object_type from dba_objects) b
where a.object_id=b.object_id;
Script_long_running_query:
SELECT SID,
ROUND(TIME_REMAINING
/60,2)"time remaining(min)",
ROUND(ELAPSED_SECONDS/60,2)"elapsed_mins",
SOFAR,
TOTALWORK-SOFAR "Work
Remaining" ,
SQL_PLAN_OPTIONS||'
'||SQL_PLAN_OPERATION "PLAN OPERATION",
MESSAGE
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING>0;
Script_PGA
Usage Script:
set head off
set head on
column name format a60
column value/1024/1024 as MB format
9,999,999,999,999,999
select * from gv$pgastat order by
inst_id,name;
COLD
backup using RMAN Script:
run{
sql 'alter system checkpoint';
shutdown immediate;
startup mount;
sql “create pfile=”/u01/app/oracle/backup/pfile`date
+%d%m%Y`.ora” from spfile”;
RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE
DISK FORMAT ‘/u01/app/oracle/backup/%U’;
ALLOCATE CHANNEL disk2 DEVICE TYPE
DISK FORMAT ‘/u01/app/oracle/backup/%U’;
ALLOCATE CHANNEL disk3 DEVICE TYPE
DISK FORMAT ‘/u01/app/oracle/backup/%U’;
BACKUP AS COMPRESSED BACKUPSET
DATABASE;
BACKUP CURRENT CONTROLFILE FORMAT
‘/u01/app/oracle/backup/cntrl_%s_%p_%t’;
RELEASE CHANNEL disk1;
RELEASE CHANNEL disk2;
RELEASE CHANNEL disk3;
}
RMAN
Backup Failure:
select
operation,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,to_char(END_TIME,'mm/dd/yy
hh24:mi') end_time,status,object_type from v$rman_status order by 2;
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
order by session_key;
Archive
logs generated per every one hour:
SELECT TO_CHAR (COMPLETION_TIME,
'DD/MM/YYYY') DAY,
SUM
(DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '00', 1, NULL))
"00-01",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '01', 1, NULL))
"01-02",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '02', 1, NULL))
"02-03",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '03', 1, NULL))
"03-04",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '04', 1, NULL))
"04-05",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '05', 1, NULL))
"05-06",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '06', 1, NULL))
"06-07",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '07', 1, NULL))
"07-08",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '08', 1, NULL))
"08-09",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '09', 1, NULL))
"09-10",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '10', 1, NULL))
"10-11",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '11', 1, NULL))
"11-12",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '12', 1, NULL))
"12-13",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '13', 1, NULL))
"13-14",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '14', 1, NULL))
"14-15",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '15', 1, NULL))
"15-16",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '16', 1, NULL))
"16-17",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '17', 1, NULL))
"17-18",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '18', 1, NULL))
"18-19",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '19', 1, NULL))
"19-20",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '20', 1, NULL))
"20-21",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '21', 1, NULL))
"21-22",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '22', 1, NULL))
"22-23",
SUM (DECODE (TO_CHAR (COMPLETION_TIME, 'HH24'), '23', 1, NULL))
"23-00",
COUNT (*) TOTAL
FROM V$ARCHIVED_LOG
WHERE ARCHIVED='YES'
GROUP BY TO_CHAR (COMPLETION_TIME,
'DD/MM/YYYY')
ORDER BY TO_DATE (DAY,
'DD/MM/YYYY');
DAY
00-01
01-02 02-03 03-04
04-05 05-06 06-07
07-08 08-09
---------- ---------- ----------
---------- ---------- ---------- ---------- ---------- ---------- ----------
09-10
10-11 11-12 12-13
13-14 14-15 15-16
16-17 17-18 18-19
19-20
---------- ---------- ----------
---------- ---------- ---------- ---------- ---------- ---------- ----------
----------
20-21
21-22 22-23 23-00
TOTAL
---------- ---------- ----------
---------- ----------
05/05/2015
16 22
12 31
109 95 117
107 103
78
80 36
6 4
35 71 38
10
970
Number
of log switches per day and Size of Archive logs:
set pagesize 10000 linesize 10000
set feedback 0
column date format a18
select to_char(first_time,
'YYYY-MM-DD Dy') as "date",
round( sum((blocks + 1) *
block_size)
/ 1024 / 1024 / 1024) as "size_gb",
count (*) as
"number_of_switches_per_day"
from v$archived_log
where dest_id = 1
group by
to_char(first_time,'YYYY-MM-DD Dy')
order by
to_char(first_time,'YYYY-MM-DD Dy');
date
size_gb number_of_switches_per_day
------------------ ----------
--------------------------
2015-07-12 Sun
2
23
select trunc(COMPLETION_TIME) TIME,
SUM(BLOCKS * BLOCK_SIZE)/1024/1024 SIZE_MB from V$ARCHIVED_LOG group by trunc
(COMPLETION_TIME) order by 1;
TIME SIZE_MB
---------- ----------
2011-02-12 71797.87
2011-02-13 75880.52
2011-02-14 73569.37
2011-02-15 76776.81
Number of log switches per week and total Size of Archive logs:
select round(sum((blocks + 1)
* block_size)
/ 1024 / 1024 / 1024) as
"weekly_size_gb"
count (*) as
"number_of_switches_per_week"
from v$archived_log
where dest_id = 1
and first_time >= sysdate - 7;
weekly_size_gb
number_of_switches_per_week
--------------
---------------------------
6
88
Calculating
Estimated Restoration completion time:
Estimated time of Backup / Restore
via RMAN
Query to estimate the time of Backup
/ Restore via RMAN
Below, a query to be executed on
target database or database auxiliary ( in the case of duplicate) to estimate
the time of restore and the completed percentage .
col OPNAME for a30
select OPNAME,SOFAR/TOTALWORK*100
PCT, trunc(TIME_REMAINING/60) MIN_RESTANTES,
trunc(ELAPSED_SECONDS/60)
MIN_ATEAGORA
from v$session_longops where
TOTALWORK>0 and OPNAME like '%RMAN%';
Output example of a duplicate:
OPNAME PCT MIN_RESTANTES
MIN_ATEAGORA
------------------------------
---------- ------------- ------------
RMAN: aggregate input 67.6037414 176
368
RMAN: full datafile restore 100 0 26
RMAN: full datafile restore 100 0 10
RMAN: full datafile restore 100 0 21
RMAN: full datafile restore 100 0 22
RMAN: full datafile restore 100 0 20
RMAN: full datafile restore 100 0 51
RMAN: full datafile restore
49.1620893 26 25
RMAN: full datafile restore 100 0 32
RMAN: full datafile restore 100 0 26
RMAN: full datafile restore 100 0 20
RMAN: full datafile restore 100 0 27
RMAN: full datafile restore 100 0 26
RMAN: full datafile restore 100 0 19
RMAN: full datafile restore 100 0 52
RMAN: full datafile restore
49.4441805 25 24
Reading the Output:
The line ( RMAN: aggregate input )
is the sum of total and it is showing that was executed 67,60% of total of
restore, and took until now, 368 minutes, and estimates that rest 176 minutes.
The other lines are of partials (
RMAN: full datafile restore ), we have many completed ( pct=100) , and two
restore of pieces in execution, that will still take 26 minutes, the first, and
25 minutes the second.
Script_Retriving
Information from Listener.log:
timestamp*connect info* protocol
tcp/ip , spx, port * sid * return value
grep "08-FEB"
listener.log | awk '{ if ( $NF != 0 ) print $0 }'
https://martincarstenbach.wordpress.com/2010/02/09/check-for-non-successful-connection-attemps-in-listener-log/
grep "05-SEP" listener.log
| awk '{ if ( $NF != 0) print $0}'
Oracle
Management Service (OMS) Commands:
emctl list oms // The command will
prompt for the SYSMAN user's password and list the Server where OMS is running.
emctl stop oms -all
The above command will stop all the
OMS services Web Tier, Oracle Management Server and Admin Server.
emctl stop oms
-all
Oracle Enterprise Manager Cloud
Control 12c Release 5
Copyright (c) 1996, 2015 Oracle
Corporation. All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Already
Stopped
AdminServer Successfully Stopped
Oracle Management Server is Dow
emctl start oms
emctl start oms
Oracle Enterprise Manager Cloud
Control 12c Release 5
Copyright (c) 1996, 2015 Oracle
Corporation. All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier
Successfully Started
Oracle Management
Server Successfully Started
Oracle Management
Server is Up
emctl status oms
~]$ emctl status oms
Oracle Enterprise Manager Cloud
Control 12c Release 5
Copyright (c) 1996, 2015 Oracle
Corporation. All rights reserved.
WebTier is Up
Oracle Management Server is Up
Scheduler
Job Queries:
job_schedules.sql:
SET LINESIZE 250
COLUMN owner FORMAT A20
COLUMN schedule_name FORMAT A30
COLUMN start_date FORMAT A35
COLUMN repeat_interval FORMAT A50
COLUMN end_date FORMAT A35
COLUMN comments FORMAT A40
SELECT owner,
schedule_name,
start_date,
repeat_interval,
end_date,
comments
FROM dba_scheduler_schedules
ORDER BY owner, schedule_name;
job_classes:
SET LINESIZE 200
COLUMN service FORMAT A20
COLUMN comments FORMAT A40
SELECT job_class_name,
resource_consumer_group,
service,
logging_level,
log_history,
comments
FROM dba_scheduler_job_classes
ORDER BY job_class_name;
job_programs:
SET LINESIZE 250
COLUMN owner FORMAT A20
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A50
COLUMN comments FORMAT A40
SELECT owner,
program_name,
program_type,
program_action,
number_of_arguments,
enabled,
comments
FROM dba_scheduler_programs
ORDER BY owner, program_name;
jobs_running:
SET LINESIZE 200
COLUMN owner FORMAT A20
SELECT owner,
job_name,
running_instance,
elapsed_time
FROM dba_scheduler_running_jobs
ORDER BY owner, job_name;
Disabling
Scheduler Jobs:
exec
dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
PL/SQL procedure successfully completed.
exec
dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');
PL/SQL procedure successfully
completed.
select name from v$database;
NAME
---------
<DB_NAME>
select JOB_NAME,STate from
ALL_SCHEDULER_JOBS where owner='ORACLE_OCM';
JOB_NAME
STATE
------------------------------
---------------
MGMT_CONFIG_JOB
DISABLED
MGMT_STATS_CONFIG_JOB
DISABLED
Partitioning
Commands:
set lines 300
col OWNER for a10
col TABLE_NAME for a10
select
OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,PCT_FREE,NUM_ROWS,PARTITIONED from
dba_tables where TABLE_NAME='&TABLE_NAME';
set lines 300
col PARTITION_NAME for a16
col TABLESPACE_NAME for a10
col HIGH_VALUE for a50
select table_name
,PARTITION_NAME,PARTITION_POSITION,TABLESPACE_NAME,COMPOSITE,HIGH_VALUE,PCT_FREE,LAST_ANALYZED
from dba_tab_partitions where TABLE_NAME='&TABLE_NAME';
select TABLE_NAME,PARTITION_COUNT,PARTITIONING_TYPE,PARTITIONING_KEY_COUNT,STATUS
from dba_part_tables where TABLE_NAME='&TABLE_NAME';
select view_name from dba_views
where view_name like '%PARTITION%';
Script_to find dumpfile version:
Helps us in identifying
if it is a classic export dumpfile or datapump dumpfile
set verify off
set define on
accept a_filename char
prompt ‘Filename: ‘
accept a_directory char
prompt ‘Directory: ‘
declare
fn
varchar2(256) := ‘&&a_filename';
dir
varchar2(30) := ‘&&a_directory';
info
ku$_dumpfile_info;
ft
number;
h number;
js
dba_datapump_jobs.state%type;
begin
sys.dbms_datapump.get_dumpfile_info(
fn,
dir,
info,
ft
);
if ft = 0 then
dbms_output.put_line(‘File not recognized.’);
elsif ft = 1 then
–dbms_output.put_line(‘File is DataPump export file.’);
/* can we
attach it to a DP job? */
begin
h
:= dbms_datapump.open (
‘SQL_FILE’,
‘FULL’
);
dbms_datapump.add_file (h,
‘examine_’||fn||’.log’,
dir,
null, — job name
sys.dbms_datapump.ku$_file_type_log_file
);
dbms_datapump.add_file (h,
fn,
dir,
null, — job name
sys.dbms_datapump.ku$_file_type_dump_file
);
dbms_datapump.stop_job(h);
dbms_output.put_line(‘File is DataPump export file.’);
exception
when dbms_datapump.INVALID_ARGVAL then
dbms_output.put_line(‘File is ORACLE_DATAPUMP External table file.’);
dbms_datapump.stop_job(h);
when others then
dbms_output.put_line(sqlerrm);
dbms_datapump.stop_job(h);
end;
elsif ft = 2 then
dbms_output.put_line(‘File is Classic export file.’);
else
dbms_output.put_line(‘Undocumented, file type is: ‘||to_char(ft));
end if;
/* appears
that external tables are recognized as DataPump files but cannot be imported:
ORA-39000:
bad dump file specification
ORA-31619:
invalid dump file “C:\temp\DW_INSTR_CMPNT.DP”
*/
end;
SUM
of CPU usage during the day:
SELECT TRUNC(dhsn.end_interval_time)
AS day,
MAX(dhsy.value) - MIN(dhsy.value) AS value
FROM dba_hist_sysstat dhsy
JOIN dba_hist_snapshot
dhsn ON dhsy.snap_id = dhsn.snap_id
WHERE dhsn.end_interval_time
>= TRUNC(SYSTIMESTAMP) - 30
AND
UPPER(dhsy.stat_name) LIKE '%CPU%'
GROUP BY
TRUNC(dhsn.end_interval_time)
ORDER BY
TRUNC(dhsn.end_interval_time);
SQL Statements executing and executed in the database
How can I see (with SQL*plus) that is the last SQL statement that a specific user has
given....
and we said...
A script such as follows works well. It shows you who's logged in and active -- and if
active, the statement they are executing (and the last et text shows you how long that
statement has been executing). Currently, it shows only SQL that is executing right now,
just change the predicate from "where status = 'ACTIVE'" to "where status = status" if
you want to see the currently executing as well as LAST executed (in which case the last
et column text shows you how long they've been idle -- not how long that statement took
to execute):
column status format a10
set feedback off
set serveroutput on
select username, sid, serial#, process, status
from v$session
where username is not null
/
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped
select username||'('||sid||','||serial#||')' username,
module,
action,
client_info
from v$session
where module||action||client_info is not null;
Retrieving repentance of sql statements executed in last one day based on SQL_ID:
col parsed format a6
col sql_text format a40
set lines 200
set pages 300
select
sql_text,
parsing_schema_name as parsed,
elapsed_time_delta/1000/1000 as elapsed_sec,
stat.snap_id,
to_char(snap.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
txt.sql_id
from
dba_hist_sqlstat stat,
dba_hist_sqltext txt,
dba_hist_snapshot snap
where
stat.sql_id=txt.sql_id and
stat.snap_id=snap.snap_id and
snap.begin_interval_time>=sysdate-1 and
txt.sql_id='9gg0mp8n2brdh' and
parsing_schema_name not in ('SYS','SYSMAN','MDSYS','WKSYS')
order by SNAP_ID;
Thank you for share this sql's!!!
ReplyDelete