How to find the last RMAN backup’s log?

set pagesize 2000

set linesize 2000

select

output

from

GV$RMAN_OUTPUT

where

session_recid =

(

select

session_recid

from

V$RMAN_BACKUP_JOB_DETAILS

where

session_key=(select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS)

);

OUTPUT

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

connected to target database: TEST (DBID=3221280546)

connected to recovery catalog database

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=2817 devtype=DISK

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1243_891780004.arc recid=2465 stamp=893720321

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1243_891780004.arc recid=2466 stamp=893720321

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1244_891780004.arc recid=2467 stamp=893720321

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1244_891780004.arc recid=2468 stamp=893720321

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1245_891780004.arc recid=2469 stamp=893720326

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1245_891780004.arc recid=2470 stamp=893720326

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1246_891780004.arc recid=2471 stamp=893720326

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1246_891780004.arc recid=2472 stamp=893720326

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1247_891780004.arc recid=2475 stamp=893720329

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1247_891780004.arc recid=2476 stamp=893720329

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1248_891780004.arc recid=2473 stamp=893720329

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1248_891780004.arc recid=2474 stamp=893720329

archive log filename=/backup/data/TEST/arch/TEST1_1251_891780004.arc recid=2482 stamp=893722157

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1252_891780004.arc recid=2483 stamp=893723957

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1252_891780004.arc recid=2484 stamp=893723957

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1253_891780004.arc recid=2485 stamp=893725758

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1253_891780004.arc recid=2486 stamp=893725758

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1254_891780004.arc recid=2487 stamp=893727558

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1254_891780004.arc recid=2488 stamp=893727558

.

.

.

input archive log thread=1 sequence=1305 recid=2589 stamp=893806765

channel CH1: starting piece 1 at 22-OCT-15

channel CH1: finished piece 1 at 22-OCT-15

piece handle=/backup/data/TEST/rman_backup/arch/FULL_TEST_1_5nqkcq6k_1_1 tag=TAG20151022T234001 comment=NONE

channel CH1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-OCT-15

released channel: CH1

released channel: CH2

released channel: CH3

967 rows selected.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

oracle script to check the database growth

SET LINESIZE 200

SET PAGESIZE 200

COL "Database Size" FORMAT a13

COL "Used Space" FORMAT a11

COL "Used in %" FORMAT a11

COL "Free in %" FORMAT a11

COL "Database Name" FORMAT a13

COL "Free Space" FORMAT a12

COL "Growth DAY" FORMAT a11

COL "Growth WEEK" FORMAT a12

COL "Growth DAY in %" FORMAT a16

COL "Growth WEEK in %" FORMAT a16

SELECT

(select min(creation_time) from v$datafile) "Create Time",

(select name from v$database) "Database Name",

ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",

ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",

ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",

ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"

FROM    (SELECT BYTES FROM V$DATAFILE

UNION ALL

SELECT BYTES FROM V$TEMPFILE

UNION ALL

SELECT BYTES FROM V$LOG) USED,

(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE

GROUP BY FREE.P;

Output:

SHUTDOWN: waiting for active calls to complete.

Issue:

Active call for process 1993 user ‘oraprod’ program ‘oracleprod@prod’

Active call for process 18672 user ‘oraprod’ program ‘oracleprod@prod’

Active call for process 19304 user ‘oraprod’ program ‘oracleprod@prod’

SHUTDOWN: waiting for active calls to complete.

Problem: When SHUTDOWN IMMEDIATE HUNG with above message means PMON process is unable to clear any uncommitted transaction and oracle hung for long time.

Solution:

1. Connect to SYS user with new terminal window

SQL> conn / sysdba
Connected to idle instance.

2. Shutdown database with ABORT option

SQL> shutdown abort;
ORACLE instance shut down.

3. Open database for normal shutdown with RESTRICT option

SQL> startup restrict
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 788496 bytes
Variable Size 149682160 bytes
Database Buffers 16777216 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

4. Now shutdown database with NORMAL option

SQL> shutdown normal;
Database closed.
Database dismounted.
ORACLE instance shut down.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!