Home » RDBMS Server » Backup & Recovery » How to tell if a database has a recovery catalog (Oracle, all versions)
How to tell if a database has a recovery catalog [message #411138] Thu, 02 July 2009 02:30 Go to next message
CommVaultUser
Messages: 4
Registered: July 2009
Junior Member
Hello. I've just registered and this is my first post (so please be gentle!).

I'm a backup administrator, specialising in CommVault. I'm no Oracle DBA, but I know more or less enough to backup, restore and recover Oracle DBs. But there's one thing I just cannot find out: not with Google, online docco, or anywhere else it seems. So I thought I'd hit the forums!

I'm sometimes called upon to go to a server I've never seen before with this brief: "Get CommVault backing up the Oracle databases". Sounds simple enough. Often there is no-one that can give me any site-specific information, so I need to be able to get all of the information I need myself. I know how to find the databases, find out where ORACLE_HOME is, create a new username to use for the backups, how to grant it SYSDBA (and how to create a new password file, or change it from SHARED to EXCLUSIVE), how to stop and start databases, how to enable archive log mode, etc. It's all just fine. Except this: I don't know how to tell if a database uses a recovery catalog or not. In fact, I'm not sure how to tell for sure whether a database even contains a recovery catalog.

I'm hoping one of you fine folk can assist.

So here are my questions:

Firstly, I would like to be able to tell whether a database I've found contains recovery catalogs. It is not sufficient to look for a database called "rcat", "rman" or any other name. The database creator might have used any name so the name is not reliable. Is there a command or query I can run that positively identies whether or not there are any recovery catalogs in a database? Looking for users that have been granted the RECOVERY_CATALOG_OWNER privilege is also not adequate, since that privilege can be granted to anyone at any time and it does not prove the existance or otherwise of recovery catalogs. A command or (simple) query that (a) lists all recovery catalogs in the currently connected database and (b) works with any version of Oracle, would be just wonderful. If the query is version dependant, then fair enough, but hopefully someone can provide one that will work for the most recent couple of versions. Thanks in advance.

Secondly, how to identify whether a given database has a recovery catalog tucked away somewhere? Suppose I sniff around a server and find databases D1, D2, D3, and D4, and (thanks to some kind person that provides an answer to my first question), I identify 2 recovery catalogs in database D3. I cannot assume that they are for D1 and D2, or D1 and D4, or (heaven forbid) even D3 itself (a big no-no of course)! Is there a command or simple query that I can run against a database that will tell me whether or not it has a recovery catalog somewhere? I suspect that the answer to this is : no! But how about the reverse: is there a command or simple query that I can run against a recovery catalog database that will tell me what database each catalog belongs to?

Without this information, I can only connect to a target database for backups and cannot also connect to its recovery catalog, if it has one (and working this out is the point of this post)!

Thanks people!

A newbie from the UK.
Re: How to tell if a database has a recovery catalog [message #411188 is a reply to message #411138] Thu, 02 July 2009 05:59 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
There is no query you can run on a target database to determine if a recovery catalog exists, however you may be able to check the tnsnames.ora file to see if it references a database on a remote server that "may" be a recovery catalog database. In addition, if backup scripts exists, you can check the scripts to see if it makes a connection to a recovery catalog.

If you have access to the recovery catalog, you can query RC_DATABASE to obtain the DBID & NAME of the databases that have been registered with the recovery catalog.

HTH

Welcome to the forum.
Re: How to tell if a database has a recovery catalog [message #411194 is a reply to message #411138] Thu, 02 July 2009 06:58 Go to previous messageGo to next message
CommVaultUser
Messages: 4
Registered: July 2009
Junior Member
Many thanks.

> you can query RC_DATABASE...

Is this a table? I'm connected to a test database that I know holds two recovery catalogs. I tried this but got an error:

SQL> select * from rc_database;
select * from rc_database
*
ERROR at line 1:
ORA-00942: table or view does not exist

Can I please have more detail?

Thanks in advance.
Re: How to tell if a database has a recovery catalog [message #411204 is a reply to message #411194] Thu, 02 July 2009 07:50 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you know it is a recovery catalog database, then you can issue the following query to see who owns the recovery catalog:

select owner
from dba_objects
where object_name = 'RC_DATABASE';

You should be connected as this user in the recovery catalog.
Re: How to tell if a database has a recovery catalog [message #411215 is a reply to message #411204] Thu, 02 July 2009 08:39 Go to previous messageGo to next message
CommVaultUser
Messages: 4
Registered: July 2009
Junior Member
ebrian wrote on Thu, 02 July 2009 13:50
If you know it is a recovery catalog database, then you can issue the following query to see who owns the recovery catalog:

select owner
from dba_objects
where object_name = 'RC_DATABASE';

You should be connected as this user in the recovery catalog.


Thanks very much for this. Unfortunately it only lists usernames (catalog owners in this case) and not databases. There appear to be no indications as to which databases they apply to. It may (or may not) be possible to guess which databases from the usernames but this is only guesswork.

Is there a way to do this: interrogate the recovery catalog database to find out which catalogs are in it and, more importantly, which databases they apply to?

In fact, what happens if I use RMAN to connect to a target database and the WRONG recovery catalog? Does RMAN even care? Will it happily go on and scribble all over the wrong catalog?
Re: How to tell if a database has a recovery catalog [message #411221 is a reply to message #411215] Thu, 02 July 2009 08:57 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Once you know who owns the RC_DATABASE table(s), then you have to connect as each of those users and query RC_DATABASE to see the registered databases.

RMAN will error out if you try to connect to a recovery catalog for which it is not registered with and run a backup/restore/etc.
Re: How to tell if a database has a recovery catalog [message #411229 is a reply to message #411221] Thu, 02 July 2009 10:12 Go to previous message
CommVaultUser
Messages: 4
Registered: July 2009
Junior Member
ebrian: thanks very much for this. I've now been able to document how to do this and it works a treat.

Thanks again! Smile
Previous Topic: RMAN-04005 error
Next Topic: RMAN-06433: error installing recovery catalog
Goto Forum:
  


Current Time: Thu Mar 28 10:34:33 CDT 2024