Potential of "import catalog" command

Since version 11.1 of Oracle database, there is very useful command available, allowing DBAs to easily move RMAN recovery catalog schemas between databases. Its functionality is even broader, as it also allows one catalog schema to be merged into another - either the whole schema or just the metadata of chosen databases. Command I'm writing about is of course import catalog, which I had a chance to use recently, to move our recovery catalog to the new database. It was moved from Oracle database to, with recovery catalog schema using below version:


SQL> select * from rcver;



The whole process is very straightforward. All you need is to create new database account, in the same way when configuring empty account for new recovery catalog. So apart from normal account creation, you have also grant proper role:

SQL> grant recovery_catalog_owner to dest_schema;


One should remember that the version of the source recovery catalog schema must be equal to the version of the destination recovery catalog schema. That's why you need to connect using proper version of Oracle client in order to create empty recovery catalog schema:

$ rman catalog dest_schema@dest_database
RMAN> create catalog;


As it is very good opportunity to do some cleanup, by importing only metadata for databases that are actively using source recovery catalog, I've found an easy way to list them on the basis of last resynchronization with the catalog:

SQL> select db_name, db_key, max(resync_time)
     from rc_resync
     group by db_name, db_key
     order by 3;


Using above query you could visually inspect the list, to double-check if there are any databases not resynchronized due to some other problems and not because they are already removed, using different recovery catalog, etc. If the check is successful, below query could be used to generate proper import command:

SQL> select 'import catalog source_schema@source_database dbid ' || 
       listagg(dbid,',') within group (order by dbid) || ' no unregister;'
     from (
       select rd.name, rd.dbid, max(rr.resync_time) resync_time
       from rc_resync rr, rc_database rd
       where rr.db_key = rd.db_key
       group by rd.name, rd.dbid)
     where resync_time > sysdate - 7;


Please adjust 'sysdate - 7' condition to your situation, not to omit the databases which should be added into the new recovery catalog. Afterwards, it is as easy as running:

$ rman catalog dest_schema@dest_database

Recovery Manager: Release - Production on Tue Feb 24 10:39:02 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

recovery catalog database Password:
connected to recovery catalog database

RMAN> import catalog source_schema@source_database dbid aaaaaaaa,bbbbbbbb,(...),zzzzzzzzz no unregister;

Starting import catalog at 24-FEB-2015 10:39:16
source recovery catalog database Password:
connected to source recovery catalog database
import validation complete
Finished import catalog at 24-FEB-2015 10:59:22

As you can see, in our case it took around 20 minutes, to import metadata of over 40 databases, occupying after the whole operation around 3.5 GB in the destination database. Of course if you have longer list of database IDs, you could probably hit a problem of too long command (I haven't tested it, though imagine that it could happen), but it is easy to modify SQL generating import command to divide it into smaller chunks.

Thanks to flexible options, import catalog command has potential to be used not only for simple migrations. For example we use it on a daily basis in our recovery system. As you already should know from my previous post regarding backups from standby, to restore and recover database which is backed up from its standby, you have to be connected to the catalog. After changing our recovery system to connect to the catalog when dealing with such cases, from time to time we were getting nasty errors. It was happening while backups were running (and connecting to the same catalog) at the same time as recoveries. In order to avoid such problems, I've implemented enhancement, using import catalog to populate temporary recovery catalog with the proper metadata, used then during recovery instead of production one.


Add new comment