Thursday, August 25, 2011

Find which media tapes have the latest backup

You can find which media tapes have the latest RMAN backup with the following query: 
SELECT RECID, STAMP, SET_STAMP, SET_COUNT, PIECE#, DEVICE_TYPE, HANDLE, MEDIA,
STATUS, DELETED, START_TIME,  COMPLETION_TIME, BYTES

FROM GV$BACKUP_PIECE

WHERE HANDLE LIKE '%ORACLE%SID%'

AND TRUNC(START_TIME) > TRUNC(SYSDATE-6)

ORDER BY START_TIME DESC;
Where SID is the database SID, for example A7







To find the distinct tapes use: 


SELECT DISTINCT(MEDIA) FROM GV$BACKUP_PIECE 

WHERE HANDLE LIKE 'ORACLE%A7%'

AND TRUNC(START_TIME) > TRUNC(SYSDATE-7);
Apps DBA Free Lance,
Sukhwinder Singh

Kill sessions in Unix - Linux while shutting down the database

When you shutdown the database with the immediate option you have to wait for all processes to terminate. Shutting down a database with 5000 sessions maybe will take some time!

If the database SID for example is DWH01 then you can execute in the Unix shell this command to kill all the oracle sessions to speed up the procedure. 

 
$ ps -ef|grep 'oracleDWH01 (LOCAL=NO)'|grep -v grep|awk '{print$2}'|xargs -i kill {}



OR



for i in `ps -ef|grep oracleDWH01|grep -v grep|awk '{print$2}'`

do

kill -9 $i

done
 It doesn't kill the smon and pmon processes, only the LOCAL=NO 


Apps DBA Free Lance
Sukhwinder Singh

Backup time

 You can find your backup time with the following query
SELECT * FROM (SELECT END.dt, 'Incremental' BACKUP, STAR.TIME started, END.TIME END,  

DECODE(TRUNC(END.TIME - star.TIME), 0, NULL, TRUNC(END.TIME - star.TIME) || ' Days' || ' + ') ||

TO_CHAR(TO_DATE(TRUNC(MOD(END.TIME - star.TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') TIME

FROM

(SELECT TRUNC(start_time) dt , MAX(COMPLETION_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='I'

GROUP BY TRUNC(start_time)) END,

(SELECT TRUNC(start_time) dt, MIN(START_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='I'

GROUP BY TRUNC(start_time)) STAR

WHERE END.dt = STAR.dt

UNION ALL

SELECT END.dt, 'Full Database' BACKUP, STAR.TIME started, END.TIME END,

DECODE(TRUNC(END.TIME - star.TIME), 0, NULL, TRUNC(END.TIME - star.TIME) || ' Days' || ' + ') ||

TO_CHAR(TO_DATE(TRUNC(MOD(END.TIME - star.TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') TIME

FROM

(SELECT TRUNC(start_time) dt , MAX(COMPLETION_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='D'

GROUP BY TRUNC(start_time)) END,

(SELECT TRUNC(start_time) dt, MIN(START_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='D'

GROUP BY TRUNC(start_time)) STAR

WHERE END.dt = STAR.dt)

ORDER BY dt ASC;
Apps DBA Free Lance
Sukhwinder Singh

Backup calendar

If you have set online RMAN backup for the archives and database files, 
you can see how thing going on with tha backup with the script : - 
 
SELECT * FROM 

(SELECT dates.dt, TO_CHAR(dates.dt, 'DAY') DAY,

DECODE(archives."00", NULL, NULL, archives."00" || 'L') || ' ' || DECODE(data_files."00", NULL, NULL, data_files."00" || 'D')

|| ' ' || DECODE(INCREMENTAL."00", NULL, NULL, INCREMENTAL."00" || 'I') "00",

DECODE(archives."01", NULL, NULL, archives."01" || 'L') || ' ' || DECODE(data_files."01", NULL, NULL, data_files."01" || 'D')

|| ' ' || DECODE(INCREMENTAL."01", NULL, NULL, INCREMENTAL."01" || 'I') "01",

DECODE(archives."02", NULL, NULL, archives."02" || 'L') || ' ' || DECODE(data_files."02", NULL, NULL, data_files."02" || 'D')

|| ' ' || DECODE(INCREMENTAL."02", NULL, NULL, INCREMENTAL."02" || 'I') "02",

DECODE(archives."03", NULL, NULL, archives."03" || 'L') || ' ' || DECODE(data_files."03", NULL, NULL, data_files."03" || 'D')

|| ' ' || DECODE(INCREMENTAL."03", NULL, NULL, INCREMENTAL."03" || 'I') "03",

DECODE(archives."04", NULL, NULL, archives."04" || 'L') || ' ' || DECODE(data_files."04", NULL, NULL, data_files."04" || 'D')

|| ' ' || DECODE(INCREMENTAL."04", NULL, NULL, INCREMENTAL."04" || 'I') "04",

DECODE(archives."05", NULL, NULL, archives."05" || 'L') || ' ' || DECODE(data_files."05", NULL, NULL, data_files."05" || 'D')

|| ' ' || DECODE(INCREMENTAL."05", NULL, NULL, INCREMENTAL."05" || 'I') "05",

DECODE(archives."06", NULL, NULL, archives."06" || 'L') || ' ' || DECODE(data_files."06", NULL, NULL, data_files."06" || 'D')

|| ' ' || DECODE(INCREMENTAL."06", NULL, NULL, INCREMENTAL."06" || 'I') "06",

DECODE(archives."07", NULL, NULL, archives."07" || 'L') || ' ' || DECODE(data_files."07", NULL, NULL, data_files."07" || 'D')

|| ' ' || DECODE(INCREMENTAL."07", NULL, NULL, INCREMENTAL."07" || 'I') "07",

DECODE(archives."08", NULL, NULL, archives."08" || 'L') || ' ' || DECODE(data_files."08", NULL, NULL, data_files."08" || 'D')

|| ' ' || DECODE(INCREMENTAL."08", NULL, NULL, INCREMENTAL."08" || 'I') "08",

DECODE(archives."09", NULL, NULL, archives."09" || 'L') || ' ' || DECODE(data_files."09", NULL, NULL, data_files."09" || 'D')

|| ' ' || DECODE(INCREMENTAL."09", NULL, NULL, INCREMENTAL."09" || 'I') "09",

DECODE(archives."10", NULL, NULL, archives."10" || 'L') || ' ' || DECODE(data_files."10", NULL, NULL, data_files."10" || 'D')

|| ' ' || DECODE(INCREMENTAL."10", NULL, NULL, INCREMENTAL."10" || 'I') "10",

DECODE(archives."11", NULL, NULL, archives."11" || 'L') || ' ' || DECODE(data_files."11", NULL, NULL, data_files."11" || 'D')

|| ' ' || DECODE(INCREMENTAL."11", NULL, NULL, INCREMENTAL."11" || 'I') "11",

DECODE(archives."12", NULL, NULL, archives."12" || 'L') || ' ' || DECODE(data_files."12", NULL, NULL, data_files."12" || 'D')

|| ' ' || DECODE(INCREMENTAL."12", NULL, NULL, INCREMENTAL."12" || 'I') "12",

DECODE(archives."13", NULL, NULL, archives."13" || 'L') || ' ' || DECODE(data_files."13", NULL, NULL, data_files."13" || 'D')

|| ' ' || DECODE(INCREMENTAL."13", NULL, NULL, INCREMENTAL."13" || 'I') "13",

DECODE(archives."14", NULL, NULL, archives."14" || 'L') || ' ' || DECODE(data_files."14", NULL, NULL, data_files."14" || 'D')

|| ' ' || DECODE(INCREMENTAL."14", NULL, NULL, INCREMENTAL."14" || 'I') "14",

DECODE(archives."15", NULL, NULL, archives."15" || 'L') || ' ' || DECODE(data_files."15", NULL, NULL, data_files."15" || 'D')

|| ' ' || DECODE(INCREMENTAL."15", NULL, NULL, INCREMENTAL."15" || 'I') "15",

DECODE(archives."16", NULL, NULL, archives."16" || 'L') || ' ' || DECODE(data_files."16", NULL, NULL, data_files."16" || 'D')

|| ' ' || DECODE(INCREMENTAL."16", NULL, NULL, INCREMENTAL."16" || 'I') "16",

DECODE(archives."17", NULL, NULL, archives."17" || 'L') || ' ' || DECODE(data_files."17", NULL, NULL, data_files."17" || 'D')

|| ' ' || DECODE(INCREMENTAL."17", NULL, NULL, INCREMENTAL."17" || 'I') "17",

DECODE(archives."18", NULL, NULL, archives."18" || 'L') || ' ' || DECODE(data_files."18", NULL, NULL, data_files."18" || 'D')

|| ' ' || DECODE(INCREMENTAL."18", NULL, NULL, INCREMENTAL."18" || 'I') "18",

DECODE(archives."19", NULL, NULL, archives."19" || 'L') || ' ' || DECODE(data_files."19", NULL, NULL, data_files."19" || 'D')

|| ' ' || DECODE(INCREMENTAL."19", NULL, NULL, INCREMENTAL."19" || 'I') "19",

DECODE(archives."20", NULL, NULL, archives."20" || 'L') || ' ' || DECODE(data_files."20", NULL, NULL, data_files."20" || 'D')

|| ' ' || DECODE(INCREMENTAL."20", NULL, NULL, INCREMENTAL."20" || 'I') "20",

DECODE(archives."21", NULL, NULL, archives."21" || 'L') || ' ' || DECODE(data_files."21", NULL, NULL, data_files."21" || 'D')

|| ' ' || DECODE(INCREMENTAL."21", NULL, NULL, INCREMENTAL."21" || 'I') "21",

DECODE(archives."22", NULL, NULL, archives."22" || 'L') || ' ' || DECODE(data_files."22", NULL, NULL, data_files."22" || 'D')

|| ' ' || DECODE(INCREMENTAL."22", NULL, NULL, INCREMENTAL."22" || 'I') "22",

DECODE(archives."23", NULL, NULL, archives."23" || 'L') || ' ' || DECODE(data_files."23", NULL, NULL, data_files."23" || 'D')

|| ' ' || DECODE(INCREMENTAL."23", NULL, NULL, INCREMENTAL."23" || 'I') "23"

FROM

(SELECT DISTINCT(TRUNC(COMPLETION_TIME)) dt FROM V$BACKUP_SET WHERE BACKUP_TYPE='L'

UNION

SELECT DISTINCT(TRUNC(COMPLETION_TIME)) dt FROM V$BACKUP_SET WHERE BACKUP_TYPE='D'

UNION

SELECT DISTINCT(TRUNC(COMPLETION_TIME)) dt FROM V$BACKUP_SET WHERE BACKUP_TYPE='I'

ORDER BY dt DESC) dates,

(SELECT * FROM (SELECT TO_DATE(date_time, 'DD/MM/YYYY') DT, TO_CHAR(TO_DATE(date_time, 'DD/MM/YYYY'), 'DAY') DAY,

"00", "01", "02", "03", "04", "05", "06", "07", "08", "09",

"10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23"

FROM

(

SELECT DATE_TIME,

SUM(DECODE(HOUR,'00',1,NULL)) "00", SUM(DECODE(HOUR,'01',1,NULL)) "01", SUM(DECODE(HOUR,'02',1,NULL)) "02",

SUM(DECODE(HOUR,'03',1,NULL)) "03", SUM(DECODE(HOUR,'04',1,NULL)) "04", SUM(DECODE(HOUR,'05',1,NULL)) "05",

SUM(DECODE(HOUR,'06',1,NULL)) "06", SUM(DECODE(HOUR,'07',1,NULL)) "07", SUM(DECODE(HOUR,'08',1,NULL)) "08",

SUM(DECODE(HOUR,'09',1,NULL)) "09", SUM(DECODE(HOUR,'10',1,NULL)) "10", SUM(DECODE(HOUR,'11',1,NULL)) "11",

SUM(DECODE(HOUR,'12',1,NULL)) "12", SUM(DECODE(HOUR,'13',1,NULL)) "13", SUM(DECODE(HOUR,'14',1,NULL)) "14",

SUM(DECODE(HOUR,'15',1,NULL)) "15", SUM(DECODE(HOUR,'16',1,NULL)) "16", SUM(DECODE(HOUR,'17',1,NULL)) "17",

SUM(DECODE(HOUR,'18',1,NULL)) "18", SUM(DECODE(HOUR,'19',1,NULL)) "19", SUM(DECODE(HOUR,'20',1,NULL)) "20",

SUM(DECODE(HOUR,'21',1,NULL)) "21", SUM(DECODE(HOUR,'22',1,NULL)) "22", SUM(DECODE(HOUR,'23',1,NULL)) "23", COUNT(*) TOTAL

FROM (SELECT TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY') DATE_TIME,

SUBSTR(TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY HH24:MI:SS'),12,2) HOUR FROM V$BACKUP_SET WHERE BACKUP_TYPE='D')

GROUP BY date_time

) ORDER BY dt DESC)) data_files,

(SELECT TO_DATE(date_time, 'DD/MM/YYYY') DT, TO_CHAR(TO_DATE(date_time, 'DD/MM/YYYY'), 'DAY') DAY,

"00", "01", "02", "03", "04", "05", "06", "07", "08", "09",

"10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23"

FROM

(

SELECT DATE_TIME,

SUM(DECODE(HOUR,'00',1,NULL)) "00", SUM(DECODE(HOUR,'01',1,NULL)) "01", SUM(DECODE(HOUR,'02',1,NULL)) "02",

SUM(DECODE(HOUR,'03',1,NULL)) "03", SUM(DECODE(HOUR,'04',1,NULL)) "04", SUM(DECODE(HOUR,'05',1,NULL)) "05",

SUM(DECODE(HOUR,'06',1,NULL)) "06", SUM(DECODE(HOUR,'07',1,NULL)) "07", SUM(DECODE(HOUR,'08',1,NULL)) "08",

SUM(DECODE(HOUR,'09',1,NULL)) "09", SUM(DECODE(HOUR,'10',1,NULL)) "10", SUM(DECODE(HOUR,'11',1,NULL)) "11",

SUM(DECODE(HOUR,'12',1,NULL)) "12", SUM(DECODE(HOUR,'13',1,NULL)) "13", SUM(DECODE(HOUR,'14',1,NULL)) "14",

SUM(DECODE(HOUR,'15',1,NULL)) "15", SUM(DECODE(HOUR,'16',1,NULL)) "16", SUM(DECODE(HOUR,'17',1,NULL)) "17",

SUM(DECODE(HOUR,'18',1,NULL)) "18", SUM(DECODE(HOUR,'19',1,NULL)) "19", SUM(DECODE(HOUR,'20',1,NULL)) "20",

SUM(DECODE(HOUR,'21',1,NULL)) "21", SUM(DECODE(HOUR,'22',1,NULL)) "22", SUM(DECODE(HOUR,'23',1,NULL)) "23", COUNT(*) TOTAL

FROM (SELECT TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY') DATE_TIME,

SUBSTR(TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY HH24:MI:SS'),12,2) HOUR FROM V$BACKUP_SET WHERE BACKUP_TYPE='L')

GROUP BY date_time

) ORDER BY dt DESC) archives,

(SELECT TO_DATE(date_time, 'DD/MM/YYYY') DT, TO_CHAR(TO_DATE(date_time, 'DD/MM/YYYY'), 'DAY') DAY,

"00", "01", "02", "03", "04", "05", "06", "07", "08", "09",

"10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23"

FROM

(

SELECT DATE_TIME,

SUM(DECODE(HOUR,'00',1,NULL)) "00", SUM(DECODE(HOUR,'01',1,NULL)) "01", SUM(DECODE(HOUR,'02',1,NULL)) "02",

SUM(DECODE(HOUR,'03',1,NULL)) "03", SUM(DECODE(HOUR,'04',1,NULL)) "04", SUM(DECODE(HOUR,'05',1,NULL)) "05",

SUM(DECODE(HOUR,'06',1,NULL)) "06", SUM(DECODE(HOUR,'07',1,NULL)) "07", SUM(DECODE(HOUR,'08',1,NULL)) "08",

SUM(DECODE(HOUR,'09',1,NULL)) "09", SUM(DECODE(HOUR,'10',1,NULL)) "10", SUM(DECODE(HOUR,'11',1,NULL)) "11",

SUM(DECODE(HOUR,'12',1,NULL)) "12", SUM(DECODE(HOUR,'13',1,NULL)) "13", SUM(DECODE(HOUR,'14',1,NULL)) "14",

SUM(DECODE(HOUR,'15',1,NULL)) "15", SUM(DECODE(HOUR,'16',1,NULL)) "16", SUM(DECODE(HOUR,'17',1,NULL)) "17",

SUM(DECODE(HOUR,'18',1,NULL)) "18", SUM(DECODE(HOUR,'19',1,NULL)) "19", SUM(DECODE(HOUR,'20',1,NULL)) "20",

SUM(DECODE(HOUR,'21',1,NULL)) "21", SUM(DECODE(HOUR,'22',1,NULL)) "22", SUM(DECODE(HOUR,'23',1,NULL)) "23", COUNT(*) TOTAL

FROM (SELECT TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY') DATE_TIME,

SUBSTR(TO_CHAR(COMPLETION_TIME, 'DD/MM/YYYY HH24:MI:SS'),12,2) HOUR FROM V$BACKUP_SET WHERE BACKUP_TYPE='I')

GROUP BY date_time

) ORDER BY dt DESC) INCREMENTAL

WHERE dates.dt = data_files.dt(+) AND

dates.dt = archives.dt (+)

AND dates.dt = INCREMENTAL.dt(+)

)

ORDER BY dt DESC;
Apps DBA Free Lance
Sukhwinder Singh

Unsuccessful logins for the running day.

If you have set up auditing in Oracle E-Business Suite then you can monitor the unsuccessful logins from the applsys.FND_UNSUCCESSFUL_LOGINS table.

For example, the unsuccessful logins for the running day.
 
SELECT B.USER_NAME, U.ATTEMPT_TIME

FROM applsys.FND_UNSUCCESSFUL_LOGINS U, applsys.FND_USER B

WHERE U.USER_ID = B.USER_ID

--AND B.USER_NAME LIKE NVL(:USER_NAME, '%')

AND TRUNC(U.ATTEMPT_TIME) > TRUNC(SYSDATE-1)

ORDER BY U.ATTEMPT_TIME;
Apps DBA Free Lance
Sukhwinder Singh