Backup is one of the most important topics for any Oracle DBA. It is our primary responsibility to make sure that at any point in time we can recover a database. This is why I can’t stop thinking about this topic and hope to release more related blog posts in the near future.
Some time ago, I created a survey (my very first one): “Why do you use RMAN catalog DB for your Oracle DB backups?” As I am unsure of whether or not we should use RMAN catalog database and what should be the important input information for a decision-taking process. In this blog post, I will share the survey’s results. You can find my short comments on the results at the end of this blog post. Please feel free to leave your option in the comments sections.
1.Are you using RMAN catalog DB for your Oracle DB backups?
| Answer | Response Percent | Response Count |
| I (organization I am working for) use RMAN for backing up Oracle DBs and USE catalog database | 70.8% | 17 |
| I (organization I am working for) use RMAN for backing up Oracle DBs but I DON’T USE catalog database | 25.0% | 6 |
| I (organization I am working for) DON’T USE RMAN for performing Oracle DBs backups (if yes don’t answer rest of the questions) | 4.2% | 1 |
| answered question | 24 |
2.What was the primary purpose to build and use RMAN catalog in your organization?
| Answer | Response Percent | Response Count |
| Additional control files’ metadata backup | 47.4% | 9 |
| Keep backup history longer than control file can keep it | 47.4% | 9 |
| Centralized reporting purposes | 36.8% | 7 |
| Primary & Standby databases backups synchronization | 21.1% | 4 |
| Store backup scripts to be executed an several databases | 21.1% | 4 |
| We don’t use RMAN or RMAN Catalog | 21.1% | 4 |
| answered question | 19 | |
| skipped question | 5 |
| Other reasons | Using a catalogue is a defined business standard. |
| Duplicates | |
| Catalog backups stored using MML library |
3.What REALLY you are using RMAN catalog database for?
| Answer | Response Percent | Response Count |
| Centralized reporting purposes (please specify reports you are running on regular basis in other filed) | 31.3% | 5 |
| Additional control files’ metadata backup (please specify use cases from your experience) | 31.3% | 5 |
| Keep backup history longer than control file can keep it (please specify use cases from your experience) | 31.3% | 5 |
| Primary & Standby databases backups synchronization | 25.0% | 4 |
| We don’t use RMAN or RMAN Catalog | 25.0% | 4 |
| Store backup scripts to be executed an several databases (please tick this option if you REALLY use RMAN scripts stored in the catalog) | 12.5% | 2 |
| answered question | 16 | |
| skipped question | 8 |
| Other reasons | easy clone database |
| Ah well, I suspect the real reason is to keep controlfile sizes to a minimum and still have the ability to keep details about old(er) backups. | |
| Report on databases needing backups and trends in backup timings and sizes for capacity planning. Cases where control_file_record_keep_time wasn’t changed from default (7 days) and information about older backups needed. | |
| * repository: we use hand made reports on RMAN repository e.g. to ‘crosscheck’ backup-pieces RMAN knows about with backup-pieces our Symantec NETBACKUP knows about. They are not 100% in sync all the time. Also APEX reports about backup quality per DB is in use. (management style reports) * RMAN repo is protected by DAtaGuard on 2nd location – therefore it’s part of disaster recovery: RMAN repo protected by DG; backup pieces protected by NETBACKUP duplication to ‘other side’ * control file history: some backups are kept for ‘history’ snapshots – nothing to keep in controlfile | |
| Duplicates | |
| Catalog backups stored using MML library | |
| Query RMAN catalog and report on the backup status of every database in the catalog. If you have more than one, this is very usefull |
Yury’s comments
I’m planning to continue blogging on the topic. And my working name for the next related blog post is “You probably don’t need RMAN catalog database” where I am going to give more analysis. As of now, let me highlight some points:
- One of the good reasons to have catalog I missed initial is “DUPLICATE … FROM ACTIVE DATABASE”. This option requires RMAN catalog database connection. (2012.07.10 Yury: I stand corrected. Please see comments. Thanks Mdinh and Anand.)
- ~25% of the people who participated in the survey don’t use RMAN catalog database.
- It looks like some DBAs initially planned to use a catalog database for storing RMAN scripts. However, some of them didn’t implement it.
- Additional control files’ metadata backup is one of the most popular reasons to use catalog database.
- Other popular reasons are “keep backup history longer” and “centralized reporting purposes”.
- Some other interesting reasons to use catalog database are “defined business standard” and “catalog backups stored using MML library”.
Feel free to comment,
Yury
6 Comments. Leave new
Hi Yury,
So far i have not used catalog database to backup databases. Had a small shell which calls RMAN takes backup and emails when completed or error.
Thank You,
Baskar.l
Hey Baskar,
Thx for sharing your experience. Just wonder how big is the infrastructure you are looking for? How many databases?
i tend to think that after some number of databases it may be justifiable introducing a catalog database.
Yury
Hello Yury,
It’s me again :=)
I have only done active database duplication for standby and it does not require a catalog. Not sure if this is the same thing as what you had mentioned in your comments.
https://mdinh.wordpress.com/2011/10/08/create-standby-database-from-active-database/
Hi Yury,
Waiting for the upcoming blog “You most probably don’t need RMAN catalog database”.
I have also never used RMAN Catalog.As far as,”DUPLICATE … FROM ACTIVE DATABASE option requires RMAN catalog database connection”,is concerned i don’t think its true. I have tested many time creating duplicate database using from active database without using RMAN Catalog.
Regards,
Anand
Hey Anand,
Thak you for pointing this out. I am sure I picked it up from somewhere at the time I needed to run a DUPLICATE from ACTIVE DATABASE. I just made a search and can’t find a reference. On the other hand I found the refrence that list “Prerequisites Specific to Active Database Duplication” and it doesn’t have catalog database. I stand corrected. And I appriciate it :)
Yury
REF: https://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta020.htm#RCMRF126
Oracle® Database Backup and Recovery Reference
11g Release 2 (11.2)
Part Number E10643-06
DUPLICATE
Prerequisites Specific to Active Database Duplication
PS Just need a bit more time to write a good blog post on “You most probably don’t need RMAN catalog database” :)
Hi Yury
Can u plz explain me in detail wat is catalog and y rman catalog ??