| 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
|
|
|
|
| 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: 4579
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:
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!
|
|
|
|
| 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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 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! 
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
