OraFAQ Forum: Backup & Recovery » sql query for RMAN catalog database to report SID and last successfull backup

Home » RDBMS Server » Backup & Recovery » sql query for RMAN catalog database to report SID and last successfull backup

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql query for RMAN catalog database to report SID and last successfull backup [message #173835]

Wed, 24 May 2006 14:57

rajpura
Messages: 3
Registered: May 2006

Junior Member

I am trying to write a SQL query against my catalog database which has over 770+ databases registered to report SID, start_time,end_time,status of the last successfull completed backup and unable to get what i need.

1) First I tried to get it from rc_rman_status and did not get the 770+ list of databases. Then compared and ran another query against rc_databases and got back 770+. So what is the purpose of rc_rman_status view ANYONE??

2) Wrote another query and joined rc_database and rc_backup_set but get too many rows for one DBID and unable to group start_time and end_time. I thought rc_backup_set has single set info but returns rows as if it a piece. Try yourself

3) Oracle recommends to use centralized catalog repository but I have yet to see any good reporting tools or queries from RMAN catalog database. I don't want to use RMAN list, report commands because we have 770+ database

Apprciate if anyone can test his/her query against their current catalog and let me know if it works as below

SID START_TIME END_TIME STATUS
============================================
ABC 25-MAY-2006 26-MAY-2006 AVAILABLE
XYZ 23-MAY-2006 24-MAY-2006 NO BACKUP

Thanks in advance

Report message to a moderator

Re: sql query for RMAN catalog database to report SID and last successfull backup [message #176973 is a reply to message #173835]

Mon, 12 June 2006 04:58

Frank Naude
Messages: 4596
Registered: April 1998

Senior Member

1) According to the Recovery Manager Reference guide:

"This view contains information about the history of RMAN operations on all databases associated with this recovery catalog. It contains essentially the same information as V$RMAN_STATUS, except that it does not contain information about current sessions.

All RMAN operations such as backups, restores, deletion of backups, and so on are logged in this table. The table is organized to show the status of each RMAN session (the invocation of an RMAN client, including all actions taken until the RMAN client exits), operations executed during the session, and recursive operations."

2) The data looks right to me:

Code: [Select all] [Show/ hide]

SQL> SELECT db_name, row_type, start_time, end_time, operation, status, mbytes_processed
  2  FROM   rc_rman_status
  3  WHERE  db_name = 'ORCL'
  4    AND  start_time > SYSDATE - 1
  5  ORDER  BY END_TIME
  6  /

DB_NAME  ROW_TYP START_TIM END_TIME  OPERAT STATUS    MBYTES_PROCESSED
-------- ------- --------- --------- ------ --------- ----------------
...
ORCL     COMMAND 12-JUN-06 12-JUN-06 BACKUP COMPLETED       1.97998047
ORCL     SESSION 12-JUN-06 12-JUN-06 RMAN   COMPLETED               16
ORCL     COMMAND 12-JUN-06 12-JUN-06 BACKUP COMPLETED       16.5478516
ORCL     SESSION 12-JUN-06 12-JUN-06 RMAN   COMPLETED                0
ORCL     COMMAND 12-JUN-06 12-JUN-06 BACKUP COMPLETED       .468261719
ORCL     SESSION 12-JUN-06           RMAN   RUNNING                  0
ORCL     COMMAND 12-JUN-06           BACKUP RUNNING                  0

3) I fully agree - a better tool would be great!

Report message to a moderator

Re: sql query for RMAN catalog database to report SID and last successfull backup [message #177024 is a reply to message #176973]

Mon, 12 June 2006 10:27

rajpura
Messages: 3
Registered: May 2006

Junior Member

Pls explain the results below why i don't see 771 db_name from rc_rman_status when I have 771 db_name in rc_database??

SQL> select count(*) from rc_database;

COUNT(*)
----------
771

select db_name, count(*) from rc_rman_status group by db_name

DB_NAME COUNT(*)
-------- ----------
CARP1 1027
CBRUD1 3
FINARCD1 4
FINARCD3 2
FORMSP1 36
HRCUSDEV 78
HREPD1 80
HRSECDEV 82
MCHATP1 94
MCHATQ1 56
MGTRDRD2 52
OEBPERF 387
ORCHP 155
ORCHS 101
PERAC 465
REMEDYP5 477
REMEDYQ5 489

17 rows selected.

Report message to a moderator

Re: sql query for RMAN catalog database to report SID and last successfull backup [message #177135 is a reply to message #177024]

Tue, 13 June 2006 03:56

Frank Naude
Messages: 4596
Registered: April 1998

Senior Member

I don't see the same problem on my catalog. My guess is that you've registered 711 databases in your catalog, but only 17 databases use the catalog for backups.

Report message to a moderator

Re: sql query for RMAN catalog database to report SID and last successfull backup [message #177167 is a reply to message #177135]

Tue, 13 June 2006 05:55

rajpura
Messages: 3
Registered: May 2006

Junior Member

I have verified that 90% of the databases are backedup. Do you have any other verification check. I check backup_piece and backup_set and I do get information

Report message to a moderator

Re: sql query for RMAN catalog database to report SID and last successfull backup [message #332016 is a reply to message #177167]

Mon, 07 July 2008 04:39

ghenon
Messages: 1
Registered: July 2008
Location: Lyon, France

Junior Member

This view only gets populated starting with oracle version 10.2

Report message to a moderator

Re: sql query for RMAN catalog database to report SID and last successfull backup [message #351215 is a reply to message #173835]

Mon, 29 September 2008 23:50

jaysnaik82
Messages: 1
Registered: September 2008

Junior Member

This view only gets populated starting with oracle version 10.2 as target db.
for e.g if you connect from Oracle 10.2.0.3 and your Catalog db is Oracle 10.2.0.4 then your will not get the above entry.

Report message to a moderator

Re: sql query for RMAN catalog database to report SID and last successfull backup [message #351311 is a reply to message #351215]

Tue, 30 September 2008 05:03

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You mention:

jaysnaik82 wrote on Tue, 30 September 2008 00:50

This view only gets populated starting with oracle version 10.2 as target db.

Yet you contradict yourself by saying "if you connect from Oracle 10.2.0.3 and your Catalog db is Oracle 10.2.0.4 then your will not get the above entry." I would say a 10.2.0.3 database is a 10.2 database ?

Report message to a moderator

Re: sql query for RMAN catalog database to report SID and last successfull backup [message #393227 is a reply to message #351311]

Fri, 20 March 2009 15:20

deek102
Messages: 1
Registered: March 2009
Location: San Antonio, TX

Junior Member

I realize my post is months late, sorry, but I came across this thread and it's helping me clean out old databases from our recovery catalog, thanks! Smile

One of the basic queries I use is this:

select * from rc_database where db_key not in (select db_key from rc_rman_status)

RC_RMAN_STATUS: This view contains information about the history of RMAN operations on all databases associated with this recovery catalog.

Now I know which DBs have never been backed up by the catalog. From there I can refine it to weed out the DBs that haven't been backed up in the last 90 days (outside of our recovery window and probably not on the network anymore).

If your DBs aren't showing up in your catalog are you positive you're backing up your DBs to your catalog and not just to your control file? I've had that situation where we weren't properly syncing (or using) our recovery catalog with new or smaller DBs that we didn't pay much attention to.

Good Luck,
Rich

Report message to a moderator

Previous Topic:	recover to specific time
Next Topic:	rman backup in cif mount point

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 13 16:37:26 CDT 2025