Skip to main content

Some useful RMAN commands to check the status of backup

If we want to track the process of rman which is currently running, use the below sql query to find how much percentage (%) of rman backup has been completed & how much percentage is remaining to complete.

Current status of running RMAN process
============================
set lines 300
set pages 1000
col START_TIME for a20
col SID for 99999
select SID, to_char(START_TIME,'dd-mm-yy hh24:mi:ss') START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';


To find the total time elapsed for the RMAN backup in minutes, use the below sql query.
============================================================
set lines 300
set pages 500
set lines 300
set pages 1000
col STATUS for a10
col START_TIME for a20
col END_TIME for a20
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/60 Mins
from V$RMAN_BACKUP_JOB_DETAILS order by SESSION_KEY,START_TIME;


Use the following query to check backup is completed successfully or not.
==================================================
set lines 1000
set lines 500
col STATUS for a21
select SID,RECID,STAMP,OPERATION,OBJECT_TYPE,STATUS,to_char(START_TIME,'dd-mm-yy hh24:mi:ss'),to_char(END_TIME,'dd-mm-yy hh24:mi:ss')
from v$rman_status order by START_TIME;


Use the following query to check particular day backup is completed successfully or not.
===========================================================
set pages 1000
set lines 1000
select SID,RECID,STAMP,OPERATION,OBJECT_TYPE,STATUS,to_char(START_TIME,'dd-mm-yy hh24:mi:ss') START_TIME,
to_char(END_TIME,'dd-mm-yy hh24:mi:ss') END_TIME from v$rman_status where to_char(START_TIME,'dd-mm-yy hh24:mi:ss')
between '09-05-14 14:44:51' and '09-05-14 15:16:41' order by START_TIME;


To monitor the SBT events, you can run the following SQL query:
============================================
COLUMN EVENT FORMAT a10
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30
SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
       sw.STATE, CLIENT_INFO
FROM   V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE  sw.EVENT LIKE 's%bt%'
AND    s.SID=sw.SID
AND    s.PADDR=p.ADDR;

Comments

  1. AnonymousThursday, April 06, 2017 5:13:00 am

    How would you query v$rman_status (or other view) for daily backup jobs that did *not* run the previous night?

    ReplyDelete

Post a Comment

Popular posts from this blog

Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan

Deal all, Below post content is not my own. It is taken from MOSC document but the example shown below is taken from our environment. I got to know the below topic when we had experienced the bad SQL query performance in one of our production environment during daily batch running time. Hence we had implemented the same mentioned in the title "Pinning execution plan for a sql_id using 'SQL Profile' to consistently reproduce a good plan". Introduction The "cost-based optimizer (CBO or just optimizer) is the part of database software that determines the most efficient means of accessing the specified data (or at least what it "calculates" as the most efficient). The optimizer determines which execution plan is most efficient by considering several sources of information, including query conditions, available access paths, statistics gathered for the system, and hints. Some changes in those conditions may affect the calculations of the plan...
9 comments
Read more

Export and import multiple schema using expdp/impdp (Data Pump utility)

Use the below sql query to export and import multiple schema: expdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp exclude=statistics logfile=expdpschemas120514.log impdp schemas=schema1,schema2,schema3 directory=DATA_PUMP_DIR dumpfile=schemas120514bkp.dmp logfile=impdpschemas120514.log sql query to export and import a schema: expdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp exclude=statistics logfile=expdpschema120514.log impdp schemas=schema directory=DATA_PUMP_DIR dumpfile=schema120514bkp.dmp logfile=expdpschema120514.log Parameter STATISTICS=NONE can either be used in export or import. No need to use the parameter in both. To export meta data only to get ddl of the schemas: expdp schemas=schema1,schema2,schema3 directory=TEST_DIR dumpfile=content.dat content=METADATA_ONLY exclude=statistics To get the DDL in a text file: impdp directory=TEST_DIR sqlfile=sql.dat logfile=sql.log dumpfil...
3 comments
Read more

Cloud Control EM13c - Loader Throughput (rows per second) for Loader_D crossed the critical threshold and Total Loader Runtime in the Last Hour (seconds) for Loader_D crossed the critical threshold

Hello Guys, Here is an another post related to Cloud Control EM13c - Real-time Scenario. Suddenly We were receiving the following warning and critical alerts from EM13c Cloud Control during the time slot between 12 AM and 04:00 AM daily. 1)  Message= Loader Throughput (rows per second) for Loader_D crossed the critical threshold (xx). Current value: xx.xx 2)  Message= Total Loader Runtime in the Last Hour (seconds) for Loader_D crossed the critical threshold (x,xxx). Current value: xxxx.xx 3)  ORA-error stack (3,136) and ORA-error stack (609) were also logged in alert log 4)  Message= Incident (BEA-310003 [LowMemory]) detected in $OMS_HOME/gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/adr/diag/ofm/GCDomain/EMGC_OMS1/alert/log.xml at time/line number: When we analyzed the AWR reports of EM PDB repository database, we found there were few PL/SQL statements given below causing this issue with wait event  SQL*Net Break/reset To Client...
Post a Comment
Read more