{"id":1277,"date":"2016-02-15T11:27:30","date_gmt":"2016-02-15T09:27:30","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1277"},"modified":"2020-08-18T16:26:45","modified_gmt":"2020-08-18T14:26:45","slug":"getting-the-dbid-and-incarnation-from-the-rman-catalog","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/getting-the-dbid-and-incarnation-from-the-rman-catalog\/","title":{"rendered":"Getting the DBID and Incarnation from the RMAN Catalog"},"content":{"rendered":"

Using the RMAN catalog is an option. There is a long discussion between DBAs on whether should you use the catalog or not.<\/p>\n

But because I like (a lot) the RMAN catalog and I generally use it, I assume that most of you do it \ud83d\ude09<\/p>\n

When you want to restore from the RMAN catalog, you need to get the DBID of the database you want to restore and, sometimes, also the incarnation key.<\/p>\n

The DBID<\/strong> is used to identify the database you want to restore. The DBID is different for every newly created \/ duplicated database, but beware that if you duplicate your database manually (using restore\/recover), you actually need to change your DBID using the nid<\/em> tool, otherwise you will end up by having more than one database registered in the catalog with the very same DBID. This is evil! The DB_NAME is also something that you may want to make sure is unique within your database farm.<\/p>\n

The Incarnation Key<\/strong> changes whenever you do an “open resetlogs”, following for example a flashback database, an incomplete recovery, or just a “open resetlogs” without any specific need.<\/p>\n

\"2016-02-15<\/a><\/p>\n

In the image, you can see that you may want to restore to a point in time after the open resetlogs (blue incarnation) or before it (red incarnation). Depending on which one you need to restore, you may need to use the command RESET DATABASE TO INCARNATION.<\/p>\n

https:\/\/docs.oracle.com\/database\/121\/RCMRF\/rcmsynta2007.htm#RCMRF148<\/a><\/p>\n

If you have a dynamic and big environment, you probably script your restores procedures, that’s why getting the DBID and incarnation key using the RMAN commands may be more complex than just querying the catalog using sqlplus.<\/p>\n

How do I get the history of my database incarnations?<\/strong><\/p>\n

You can get it easily for all your databases using the handy hierarchical queries on the RMAN catalog (db names and ids are obfuscated for obvious reasons):<\/p>\n

SQL> SELECT lpad(' ',2*(level-1))\r\n  || TO_CHAR(DBINC_KEY) AS DBINC_KEY,\r\n  db_key,\r\n  db_name,\r\n  TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),\r\n  dbinc_status\r\nFROM rman.dbinc\r\n  START WITH PARENT_DBINC_KEY IS NULL\r\n  CONNECT BY prior DBINC_KEY   = PARENT_DBINC_KEY ;\r\n\r\nDBINC_KEY                     DB_KEY DB_NAME    TO_CHAR(RESET_TIME, DBINC_ST\r\n------------------------- ---------- ---------- ------------------- --------\r\n356247416                  356247380 A9EE272A   2011-09-24 18:22:58 PARENT\r\n  356247387                356247380 A9EE272A   2012-10-24 08:41:41 PARENT\r\n    1149458631             356247380 A9EE272A   2014-10-10 08:30:57 CURRENT\r\n360319357                  360319322 F5FD787F   2011-10-14 15:39:19 PARENT\r\n  360319323                360319322 F5FD787F   2012-11-08 18:57:26 PARENT\r\n    547928008              360319322 F5FD787F   2013-09-10 10:57:44 PARENT\r\n      576592237            360319322 F5FD787F   2013-11-20 14:54:05 ORPHAN\r\n      576613820            360319322 F5FD787F   2013-11-20 15:57:03 ORPHAN\r\n      584503796            360319322 F5FD787F   2013-11-27 13:57:53 CURRENT\r\n364099232                  364099231 25E64A7F   2012-11-20 08:01:49 PARENT\r\n  415031968                364099231 25E64A7F   2013-02-15 12:16:15 PARENT\r\n    456099512              364099231 25E64A7F   2013-05-03 12:19:52 CURRENT\r\n366065362                  366065336 3AE45141   2011-09-24 18:22:58 PARENT\r\n  366065337                366065336 3AE45141   2012-11-26 17:14:14 CURRENT\r\n394067322                  394067321 C34FFA7E   2013-01-10 17:18:11 CURRENT\r\n402469086                  402469073 D164DDB8   2011-09-24 18:22:58 PARENT\r\n  402469074                402469073 D164DDB8   2013-01-29 11:20:19 CURRENT\r\n410147332                  410147283 27984513   2011-09-24 18:22:58 PARENT\r\n  410147284                410147283 27984513   2013-02-08 11:12:38 CURRENT\r\n...\r\n...\r\n<\/pre>\n
What about getting the correct DBID\/DBINC_KEY pair for a specific database\/time?<\/p>\n
You can get the time windows for each incarnation using the lead<\/strong>() analytical function:<\/p>\n
SQL> WITH dbids AS\r\n  (SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,\r\n    dbinc.db_key,\r\n    dbinc.db_name,\r\n    dbinc.reset_time,\r\n    dbinc.dbinc_status,\r\n    db.db_id\r\n  FROM rman.dbinc dbinc\r\n  JOIN rman.db db\r\n  ON ( \r\n  dbinc.db_key   =db.db_key)\r\n  )\r\nselect * from (\r\nSELECT DBINC_KEY,\r\n  db_name,\r\n  db_id,\r\n  reset_time,\r\n  nvl(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset\r\nFROM dbids\r\n)\r\nORDER BY db_name ,\r\n  reset_time ;  \r\n\r\nDBINC_KEY                 DBNAME          DB_ID RESET_TIME          NEXTRESET\r\n------------------------- ---------- ---------- ------------------- -------------------\r\n1173852671                1DF63C30   2507085371 2014-07-07 05:38:47 2015-01-16 07:29:01\r\n1173852635                1DF63C30   2507085371 2015-01-16 07:29:01 2015-02-27 16:25:13\r\n1244346785                1DF63C30   2531796824 2015-02-27 16:25:13 2015-02-27 16:25:13\r\n1281775847                1DF63C30   2541221473 2015-02-27 16:25:13 2015-02-27 16:25:13\r\n1233975755                1DF63C30   2528008262 2015-02-27 16:25:13 2015-02-27 16:25:13\r\n1220896058                1DF63C30   2523244390 2015-02-27 16:25:13 2015-03-16 16:06:00\r\n1188550385                1DF63C30   2507085371 2015-03-16 16:06:00 2015-07-17 08:06:00\r\n1220896028                1DF63C30   2523244390 2015-07-17 08:06:00 2015-09-10 11:23:53\r\n1233975725                1DF63C30   2528008262 2015-09-10 11:23:53 2015-10-23 07:46:34\r\n1244346755                1DF63C30   2531796824 2015-10-23 07:46:34 2016-02-08 09:44:03\r\n1281775817                1DF63C30   2541221473 2016-02-08 09:44:03 2016-02-15 10:13:49\r\n1201139592                1D0776F6   2025503263 2014-07-07 05:38:47 2015-05-04 17:08:50\r\n1201139578                1D0776F6   2025503263 2015-05-04 17:08:50 2015-06-02 08:48:07\r\n1213295265                1D0776F6   2029287211 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1256000477                1D0776F6   2044568865 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1235940868                1D0776F6   2037421528 2015-06-02 08:48:07 2015-06-17 12:14:38\r\n1213295230                1D0776F6   2029287211 2015-06-17 12:14:38 2015-09-18 15:46:34\r\n1235940852                1D0776F6   2037421528 2015-09-18 15:46:34 2015-12-08 09:08:52\r\n1256000461                1D0776F6   2044568865 2015-12-08 09:08:52 2016-02-15 10:13:49\r\n1173653066                2D828C2C   1656607497 2014-07-07 05:38:47 2015-01-15 14:06:04\r\n1173653052                2D828C2C   1656607497 2015-01-15 14:06:04 2015-06-02 08:48:07\r\n1247872446                2D828C2C   1682603029 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1218354231                2D828C2C   1671898993 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1278227063                2D828C2C   1690479985 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1219084145                2D828C2C   1672155073 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1228714578                2D828C2C   1675699280 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1211451469                2D828C2C   1669565762 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1235422982                2D828C2C   1678113471 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1228713810                2D828C2C   1675697673 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1240749487                2D828C2C   1680107003 2015-06-02 08:48:07 2015-06-02 08:48:07\r\n1255743496                2D828C2C   1685361979 2015-06-02 08:48:07 2015-06-10 13:37:08\r\n1211451453                2D828C2C   1669565762 2015-06-10 13:37:08 2015-07-06 13:44:20\r\n1218354215                2D828C2C   1671898993 2015-07-06 13:44:20 2015-07-09 12:52:19\r\n1219084129                2D828C2C   1672155073 2015-07-09 12:52:19 2015-08-19 12:55:40\r\n1228713794                2D828C2C   1675697673 2015-08-19 12:55:40 2015-08-19 13:22:27\r\n1228714562                2D828C2C   1675699280 2015-08-19 13:22:27 2015-09-16 11:58:58\r\n1235422966                2D828C2C   1678113471 2015-09-16 11:58:58 2015-10-08 13:44:29\r\n1240749471                2D828C2C   1680107003 2015-10-08 13:44:29 2015-11-06 11:04:55\r\n1247872430                2D828C2C   1682603029 2015-11-06 11:04:55 2015-12-07 09:27:27\r\n1255743480                2D828C2C   1685361979 2015-12-07 09:27:27 2016-02-04 15:07:29\r\n1278227047                2D828C2C   1690479985 2016-02-04 15:07:29 2016-02-15 10:13:49\r\n<\/pre>\n
With this query, you can see that every incarnation has a reset time and a “next reset time”.<\/p>\n
It’s easy then to get exactly what you need by adding a couple of where clauses:<\/p>\n
SQL> WITH dbids AS\r\n  (SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,\r\n    dbinc.db_key,\r\n    dbinc.db_name,\r\n    dbinc.reset_time,\r\n    dbinc.dbinc_status,\r\n    db.db_id\r\n  FROM rman.dbinc dbinc\r\n  JOIN rman.db db\r\n  ON ( --dbinc.dbinc_key=db.CURR_DBINC_KEY\r\n    --AND\r\n    dbinc.db_key =db.db_key)\r\n  )\r\nSELECT *\r\nFROM\r\n  (SELECT DBINC_KEY,\r\n    db_name,\r\n    db_id,\r\n    reset_time,\r\n    NVL(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset\r\n  FROM dbids\r\n  )\r\nWHERE TO_DATE ('2016-01-20 00:00:00','YYYY-MM-DD HH24:MI;SS') BETWEEN reset_time AND next_reset\r\nAND db_name='1465419F'\r\nORDER BY db_name ,\r\n  reset_time ; \r\n\r\nDBINC_KEY                 DB_NAME         DB_ID RESET_TIME          NEXT_RESET\r\n------------------------- ---------- ---------- ------------------- -------------------\r\n1256014297                1465419F   1048383773 2015-12-08 11:03:55 2016-02-08 07:55:05<\/pre>\n
So, if I need to restore the database 1465419F until time 2016-01-20 00:00:00, i need to set DBID=1048383773 and reset the database to incarnation 1256014297.<\/p>\n
Cheers<\/p>\n
—<\/p>\n
Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"
Using the RMAN catalog is an option. There is a long discussion between DBAs on whether should you use the catalog or not. But because I like (a lot) the RMAN catalog and I generally use it, I assume that … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[326,3,52,330,132],"tags":[257,256,160,44,258],"class_list":["post-1277","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracledb","category-12c","category-oracle-inst-upg","category-triblog","tag-dbid","tag-incarnation","tag-restore","tag-rman","tag-rman-catalog"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1277","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1277"}],"version-history":[{"count":6,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1277\/revisions"}],"predecessor-version":[{"id":1284,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1277\/revisions\/1284"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1277"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1277"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1277"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}