DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

RMAN Catalog Housekeeping: how to purge the old incarnations

Posted on February 21, 2017 by Ludovico

First, let me apologize because every post in my blog starts with a disclaimer… but sometimes it is really necessary. 😉

Disclaimer: this blog post contains PL/SQL code that deletes incarnations from your RMAN recovery catalog. Please DON’T use it unless you deeply understand what you are doing, as it can compromise your backup and recovery strategy.

Small introduction

You may have a central RMAN catalog that stores all the backup metadata for your databases. If it is the case, you will have a database entry for each of your databases and a new incarnation entry for each duplicate, incomplete recovery or flashback (or whatever).

You should also have a delete strategy that deletes the obsolete backups from either your DISK or SBT_TAPE media. If you have old incarnations, however, after some time you will notice that their information never goes away from your catalog, and you may end up soon or later to do some housekeeping. But there is nothing more tedious than checking and deleting the incarnations one by one, especially if you have average big numbers like this catalog:

SQL> SELECT count(*) FROM db;

  COUNT(*)
----------
      1843

SQL> SELECT count(*) FROM dbinc;

  COUNT(*)
----------
      3870

SQL> SELECT count(*) FROM bdf;

  COUNT(*)
----------
   4130959

SQL> SELECT count(*) FROM brl;


  COUNT(*)
----------
  14876291

SQL> SELECT count(*) FROM db;

COUNT(*)

----------

1843

SQL> SELECT count(*) FROM dbinc;

COUNT(*)

----------

3870

SQL> SELECT count(*) FROM bdf;

COUNT(*)

----------

4130959

SQL> SELECT count(*) FROM brl;

COUNT(*)

----------

14876291

Where db, dbinc, bdf and brl contain reslectively the registered databases, incarnations, datafile backups and archivelog backups.

Different incarnations?

Consider the following query:

col dbinc_key_ for a60
set pages 100 lines 200
SELECT lpad(' ',2*(level-1))
  || TO_CHAR(DBINC_KEY) AS DBINC_KEY_,
  db_key,
  db_name,
  TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),
  dbinc_status
FROM rman.dbinc
  START WITH PARENT_DBINC_KEY IS NULL
  CONNECT BY prior DBINC_KEY   = PARENT_DBINC_KEY
ORDER BY db_name , db_key, level
;

col dbinc_key_ for a60

set pages 100 lines 200

SELECT lpad(' ',2*(level-1))

|| TO_CHAR(DBINC_KEY) AS DBINC_KEY_,

db_key,

db_name,

TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),

dbinc_status

FROM rman.dbinc

START WITH PARENT_DBINC_KEY IS NULL

CONNECT BY prior DBINC_KEY = PARENT_DBINC_KEY

ORDER BY db_name , db_key, level

;

You can run it safely: it returns the list of incarnations hierarchically connected to their parent, by database name, key and level.

Then you have several types of behaviors:

Normal databases (created once, never restored or flashed back) will have just one or two incarnations (it depends on how they are created):

DBINC_KEY                      DB_KEY DB_NAME  TO_CHAR(RESET_TIME, DBINC_ST
-------------------------- ---------- -------- ------------------- --------
104547357                   104546534 VxxxxxxP 2010-09-05 05:49:10 PARENT
  104546535                 104546534 VxxxxxxP 2012-01-18 09:31:01 CURRENT

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

-------------------------- ---------- -------- ------------------- --------

104547357 104546534 VxxxxxxP 2010-09-05 05:49:10 PARENT

104546535 104546534 VxxxxxxP 2012-01-18 09:31:01 CURRENT

They are usually the ones that you may want to keep in your catalog, unless the database no longer exist: in this case perhaps you omitted the deletion from the catalog when you have dropped your database?

Flashed back databases (flashed back multiple times) will have as many incarnations as the number of flashbacks, but all connected with the incarnation prior to the flashback:

DBINC_KEY                                                        DB_KEY DB_NAME  TO_CHAR(RESET_TIME, DBINC_ST
------------------------------------------------------------ ---------- -------- ------------------- --------
1164696351                                                   1164696336 VxxxxxxD 2014-07-07 05:38:47 PARENT
  1164696337                                                 1164696336 VxxxxxxD 2014-12-10 07:39:14 PARENT
    1328815631                                               1164696336 VxxxxxxD 2016-05-12 08:22:23 PARENT
      1329299866                                             1164696336 VxxxxxxD 2016-05-13 13:02:35 PARENT
        1329299867                                           1164696336 VxxxxxxD 2016-05-13 14:05:53 PARENT
          1329299833                                         1164696336 VxxxxxxD 2016-05-13 18:26:59 PARENT
            1331239226                                       1164696336 VxxxxxxD 2016-05-17 08:09:04 PARENT
              1331395102                                     1164696336 VxxxxxxD 2016-05-17 13:20:17 PARENT
                1331815030                                   1164696336 VxxxxxxD 2016-05-18 07:32:13 PARENT
                  1331814966                                 1164696336 VxxxxxxD 2016-05-18 10:57:45 PARENT
                    1387023006                               1164696336 VxxxxxxD 2016-07-13 09:33:05 PARENT
                      1407484366                             1164696336 VxxxxxxD 2016-09-09 13:25:31 PARENT
                        1419007163                           1164696336 VxxxxxxD 2016-10-17 14:32:59 PARENT
                          1436430179                         1164696336 VxxxxxxD 2016-12-12 15:13:55 PARENT
                            1436430034                       1164696336 VxxxxxxD 2016-12-12 16:28:57 PARENT
                              1437118959                     1164696336 VxxxxxxD 2016-12-14 14:48:53 PARENT
                                1437365509                   1164696336 VxxxxxxD 2016-12-15 09:45:00 PARENT
                                  1437365456                 1164696336 VxxxxxxD 2016-12-15 11:11:06 PARENT
                                    1437484026               1164696336 VxxxxxxD 2016-12-15 13:26:37 PARENT
                                      1437483983             1164696336 VxxxxxxD 2016-12-15 17:21:11 PARENT
                                        1437822754           1164696336 VxxxxxxD 2016-12-16 12:07:46 CURRENT

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

------------------------------------------------------------ ---------- -------- ------------------- --------

1164696351 1164696336 VxxxxxxD 2014-07-07 05:38:47 PARENT

1164696337 1164696336 VxxxxxxD 2014-12-10 07:39:14 PARENT

1328815631 1164696336 VxxxxxxD 2016-05-12 08:22:23 PARENT

1329299866 1164696336 VxxxxxxD 2016-05-13 13:02:35 PARENT

1329299867 1164696336 VxxxxxxD 2016-05-13 14:05:53 PARENT

1329299833 1164696336 VxxxxxxD 2016-05-13 18:26:59 PARENT

1331239226 1164696336 VxxxxxxD 2016-05-17 08:09:04 PARENT

1331395102 1164696336 VxxxxxxD 2016-05-17 13:20:17 PARENT

1331815030 1164696336 VxxxxxxD 2016-05-18 07:32:13 PARENT

1331814966 1164696336 VxxxxxxD 2016-05-18 10:57:45 PARENT

1387023006 1164696336 VxxxxxxD 2016-07-13 09:33:05 PARENT

1407484366 1164696336 VxxxxxxD 2016-09-09 13:25:31 PARENT

1419007163 1164696336 VxxxxxxD 2016-10-17 14:32:59 PARENT

1436430179 1164696336 VxxxxxxD 2016-12-12 15:13:55 PARENT

1436430034 1164696336 VxxxxxxD 2016-12-12 16:28:57 PARENT

1437118959 1164696336 VxxxxxxD 2016-12-14 14:48:53 PARENT

1437365509 1164696336 VxxxxxxD 2016-12-15 09:45:00 PARENT

1437365456 1164696336 VxxxxxxD 2016-12-15 11:11:06 PARENT

1437484026 1164696336 VxxxxxxD 2016-12-15 13:26:37 PARENT

1437483983 1164696336 VxxxxxxD 2016-12-15 17:21:11 PARENT

1437822754 1164696336 VxxxxxxD 2016-12-16 12:07:46 CURRENT

Here, despite you have several incarnations, they all belong to the same database (same DB_KEY and DBID), then you must also keep it inside the recovery catalog.

Non-production databases that are frequently refreshed from the production database (via duplicate) will have several incarnations with different DBIDs and DB_KEY:

DBINC_KEY                   DB_KEY DB_NAME  TO_CHAR(RESET_TIME, DBINC_ST
----------------------- ---------- -------- ------------------- --------
1173852671              1173852633 VxxxxxxV 2014-07-07 05:38:47 PARENT
  1173852635            1173852633 VxxxxxxV 2015-01-16 07:29:01 PARENT
    1188550385          1173852633 VxxxxxxV 2015-03-16 16:06:00 CURRENT
1220896058              1220896027 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1220896028            1220896027 VxxxxxxV 2015-07-17 08:06:00 CURRENT
1233975755              1233975724 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1233975725            1233975724 VxxxxxxV 2015-09-10 11:23:53 CURRENT
1244346785              1244346754 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1244346755            1244346754 VxxxxxxV 2015-10-23 07:46:34 CURRENT
1281775847              1281775816 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1281775817            1281775816 VxxxxxxV 2016-02-08 09:44:03 CURRENT
1317447322              1317447257 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1317447258            1317447257 VxxxxxxV 2016-04-07 12:20:56 CURRENT
1323527351              1323527316 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1323527317            1323527316 VxxxxxxV 2016-04-29 10:09:12 CURRENT
1437346753              1437346718 VxxxxxxV 2015-02-27 16:25:13 PARENT
  1437346719            1437346718 VxxxxxxV 2016-12-12 13:33:31 CURRENT

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

----------------------- ---------- -------- ------------------- --------

1173852671 1173852633 VxxxxxxV 2014-07-07 05:38:47 PARENT

1173852635 1173852633 VxxxxxxV 2015-01-16 07:29:01 PARENT

1188550385 1173852633 VxxxxxxV 2015-03-16 16:06:00 CURRENT

1220896058 1220896027 VxxxxxxV 2015-02-27 16:25:13 PARENT

1220896028 1220896027 VxxxxxxV 2015-07-17 08:06:00 CURRENT

1233975755 1233975724 VxxxxxxV 2015-02-27 16:25:13 PARENT

1233975725 1233975724 VxxxxxxV 2015-09-10 11:23:53 CURRENT

1244346785 1244346754 VxxxxxxV 2015-02-27 16:25:13 PARENT

1244346755 1244346754 VxxxxxxV 2015-10-23 07:46:34 CURRENT

1281775847 1281775816 VxxxxxxV 2015-02-27 16:25:13 PARENT

1281775817 1281775816 VxxxxxxV 2016-02-08 09:44:03 CURRENT

1317447322 1317447257 VxxxxxxV 2015-02-27 16:25:13 PARENT

1317447258 1317447257 VxxxxxxV 2016-04-07 12:20:56 CURRENT

1323527351 1323527316 VxxxxxxV 2015-02-27 16:25:13 PARENT

1323527317 1323527316 VxxxxxxV 2016-04-29 10:09:12 CURRENT

1437346753 1437346718 VxxxxxxV 2015-02-27 16:25:13 PARENT

1437346719 1437346718 VxxxxxxV 2016-12-12 13:33:31 CURRENT

This is usually the most frequent case: here you want to delete the old incarnations, but only as far as there are no backups attached to them that are still in the recovery window.

You may also have orphaned incarnations:

DBINC_KEY                                                        DB_KEY DB_NAME  TO_CHAR(RESET_TIME, DBINC_ST
------------------------------------------------------------ ---------- -------- ------------------- --------
1262827482                                                   1262827435 TxxxxxxT 2014-07-07 05:38:47 PARENT
  1262827436                                                 1262827435 TxxxxxxT 2016-01-05 12:15:22 PARENT
    1267262014                                               1262827435 TxxxxxxT 2016-01-19 09:15:47 PARENT
      1267290962                                             1262827435 TxxxxxxT 2016-01-19 11:09:05 PARENT
        1284933855                                           1262827435 TxxxxxxT 2016-02-11 11:18:52 PARENT
          1299685647                                         1262827435 TxxxxxxT 2016-02-23 13:40:18 ORPHAN
          1299837528                                         1262827435 TxxxxxxT 2016-02-23 17:08:36 CURRENT
          1299767977                                         1262827435 TxxxxxxT 2016-02-23 15:34:13 ORPHAN
          1298269640                                         1262827435 TxxxxxxT 2016-02-22 13:16:46 ORPHAN
            1299517249                                       1262827435 TxxxxxxT 2016-02-23 10:37:29 ORPHAN

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

------------------------------------------------------------ ---------- -------- ------------------- --------

1262827482 1262827435 TxxxxxxT 2014-07-07 05:38:47 PARENT

1262827436 1262827435 TxxxxxxT 2016-01-05 12:15:22 PARENT

1267262014 1262827435 TxxxxxxT 2016-01-19 09:15:47 PARENT

1267290962 1262827435 TxxxxxxT 2016-01-19 11:09:05 PARENT

1284933855 1262827435 TxxxxxxT 2016-02-11 11:18:52 PARENT

1299685647 1262827435 TxxxxxxT 2016-02-23 13:40:18 ORPHAN

1299837528 1262827435 TxxxxxxT 2016-02-23 17:08:36 CURRENT

1299767977 1262827435 TxxxxxxT 2016-02-23 15:34:13 ORPHAN

1298269640 1262827435 TxxxxxxT 2016-02-22 13:16:46 ORPHAN

1299517249 1262827435 TxxxxxxT 2016-02-23 10:37:29 ORPHAN

In this case, again, it depends whether the DBID and DB_KEY are the same as the current incarnation or not.

What do you need to delete?

Basically:

Incarnations of databases that no longer exist
Incarnations of existing databases where the database has a more recent current incarnation, only if there are no backups still in the retention window

How to do it?

In order to be sure 100% that you can delete an incarnation, you have to verify that there are no recent backups (for instance, no backups more rercent than the current recovery window for that database). If the database does not have a specified recovery window but rather a default “CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default”, it is a bit more problematic… in this case let’s assume that we consider “old” an incarnation that does not backup since 1 year (365 days), ok?

Getting the last backup of each database

Sadly, there is not a single table where you can verify that. You have to collect the information from several tables. I think bdf, al, cdf, bs would suffice in most cases.

When you delete an incarnation you specify a db_key: you have to get the last backup for each db_key, with queries like this:

SELECT dbinc_key
     ,max(completion_time) max_al_time
  FROM al
    GROUP by dbinc_key;

SELECT dbinc_key

,max(completion_time) max_al_time

FROM al

GROUP by dbinc_key;

Putting together all the tables:

WITH
   incs AS (
      SELECT lpad(' ',2*(level-1))|| to_char(dbinc_key) AS dbinc_key_
	     ,dbinc_key
         ,db_key
	     ,db_name
	     ,reset_time
	     ,dbinc_status
      FROM rman.dbinc
        START WITH parent_dbinc_key IS NULL
      CONNECT BY PRIOR dbinc_key   = parent_dbinc_key
        ORDER BY db_name, db_key, level
    ),
   mbdf AS (
      SELECT dbinc_key
	     ,max(completion_time) max_bdf_time
	  FROM bdf
	     GROUP by dbinc_key
   ),
   mbrl AS (
      SELECT dbinc_key
	     ,max(next_time) max_brl_time
	  FROM brl
	     GROUP by dbinc_key
   ),
   mal AS (
      SELECT dbinc_key
	     ,max(completion_time) max_al_time
	  FROM al
	     GROUP by dbinc_key
   ),
   mcdf AS (
      SELECT dbinc_key
	     ,max(completion_time) max_cdf_time
	  FROM cdf
	     GROUP by dbinc_key
   ),
   mbs AS (
      SELECT db_key
	     ,max(completion_time) max_bs_time
	  FROM bs
	     GROUP by db_key
   )
SELECT incs.db_key, db.db_id, db.REG_DB_UNIQUE_NAME AS db_uq_name , incs.db_name, dbinc_status
  ,greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
	 ) AS last_bck
FROM incs
  JOIN db ON (db.db_key=incs.db_key)
  LEFT OUTER JOIN mbdf ON (incs.dbinc_key=mbdf.dbinc_key)
  LEFT OUTER JOIN mcdf ON (incs.dbinc_key=mcdf.dbinc_key)
  LEFT OUTER JOIN mbrl ON (incs.dbinc_key=mbrl.dbinc_key)
  LEFT OUTER JOIN mal ON (incs.dbinc_key=mal.dbinc_key)
  LEFT OUTER JOIN mbs ON (incs.db_key=mbs.db_key)
;

WITH

incs AS (

SELECT lpad(' ',2*(level-1))|| to_char(dbinc_key) AS dbinc_key_

,dbinc_key

,db_key

,db_name

,reset_time

,dbinc_status

FROM rman.dbinc

START WITH parent_dbinc_key IS NULL

CONNECT BY PRIOR dbinc_key = parent_dbinc_key

ORDER BY db_name, db_key, level

mbdf AS (

SELECT dbinc_key

,max(completion_time) max_bdf_time

FROM bdf

GROUP by dbinc_key

mbrl AS (

SELECT dbinc_key

,max(next_time) max_brl_time

FROM brl

GROUP by dbinc_key

mal AS (

SELECT dbinc_key

,max(completion_time) max_al_time

FROM al

GROUP by dbinc_key

mcdf AS (

SELECT dbinc_key

,max(completion_time) max_cdf_time

FROM cdf

GROUP by dbinc_key

mbs AS (

SELECT db_key

,max(completion_time) max_bs_time

FROM bs

GROUP by db_key

)

SELECT incs.db_key, db.db_id, db.REG_DB_UNIQUE_NAME AS db_uq_name , incs.db_name, dbinc_status

,greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) AS last_bck

FROM incs

JOIN db ON (db.db_key=incs.db_key)

LEFT OUTER JOIN mbdf ON (incs.dbinc_key=mbdf.dbinc_key)

LEFT OUTER JOIN mcdf ON (incs.dbinc_key=mcdf.dbinc_key)

LEFT OUTER JOIN mbrl ON (incs.dbinc_key=mbrl.dbinc_key)

LEFT OUTER JOIN mal ON (incs.dbinc_key=mal.dbinc_key)

LEFT OUTER JOIN mbs ON (incs.db_key=mbs.db_key)

;

Getting the recovery window

The configuration information for each database is stored inside the conf table, but the retention information is stored in a VARCHAR2, either ‘TO RECOVERY WINDOW OF % DAYS’ or ‘TO REDUNDANCY %’

You need to convert it to a number when the retention policy is recovery windows, otherwise you default it to 365 days wher the redundancy is used. You can add a column and a join to the query:

-- new column in the projection
,nvl(to_number(substr(c.value,23,length(c.value)-27)),365) as days

-- new join in the "from"
LEFT OUTER JOIN conf c ON (c.db_key=incs.db_key AND c.NAME = 'RETENTION POLICY' AND value LIKE 'TO RECOVERY WINDOW OF %')

-- new column in the projection

,nvl(to_number(substr(c.value,23,length(c.value)-27)),365) as days

-- new join in the "from"

LEFT OUTER JOIN conf c ON (c.db_key=incs.db_key AND c.NAME = 'RETENTION POLICY' AND value LIKE 'TO RECOVERY WINDOW OF %')

and eventually, either display if it the incarnation is no more used or filter by usage:

-- display if the incarnation is still used
,CASE WHEN
     greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
	 ) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))
	 THEN 'OLD ONE!'
	 ELSE 'USED'
  END AS USED

-- or filter it
WHERE greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
	 nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
	 ) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))

-- display if the incarnation is still used

,CASE WHEN

greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))

THEN 'OLD ONE!'

ELSE 'USED'

END AS USED

-- or filter it

WHERE greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))

Delete the incarnations!

You can delete the incarnations with this procedure:

BEGIN
  dbms_rcvcat.unregisterdatabase(DB_KEY=>:db_key, DB_ID=>:db_id);
END;

BEGIN

dbms_rcvcat.unregisterdatabase(DB_KEY=>:db_key, DB_ID=>:db_id);

END;

This procedure will raise an exception (-20001, ‘Database not found’) when a database does not exist anymore (either already deleted by this procedure or by another session), so you need to handle it.

Putting all together:

col db_uq_name for a12
col dbinc_key_ for a30
set pages 100 lines 200
set serveroutput on
DECLARE

  e_dbatabase_not_found EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_dbatabase_not_found, -20001);

  CURSOR c_old_incarnations IS
  WITH
   incs AS (
      SELECT lpad(' ',2*(level-1))|| to_char(dbinc_key) AS dbinc_key_
             ,dbinc_key
         ,db_key
             ,db_name
             ,reset_time
             ,dbinc_status
      FROM rman.dbinc
        START WITH parent_dbinc_key IS NULL
      CONNECT BY PRIOR dbinc_key   = parent_dbinc_key
        ORDER BY db_name, db_key, level
    ),
   mbdf AS (
      SELECT dbinc_key
             ,max(completion_time) max_bdf_time
          FROM bdf
             GROUP by dbinc_key
   ),
   mbrl AS (
      SELECT dbinc_key
             ,max(next_time) max_brl_time
          FROM brl
             GROUP by dbinc_key
   ),
   mal AS (
      SELECT dbinc_key
             ,max(completion_time) max_al_time
          FROM al
             GROUP by dbinc_key
   ),
   mcdf AS (
      SELECT dbinc_key
             ,max(completion_time) max_cdf_time
          FROM cdf
             GROUP by dbinc_key
   ),
   mbs AS (
      SELECT db_key
             ,max(completion_time) max_bs_time
          FROM bs
             GROUP by db_key
   )
  SELECT distinct incs.db_key, db.db_id, db.REG_DB_UNIQUE_NAME AS db_uq_name , incs.db_name
  ,greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
         ) AS last_bck
  ,CASE WHEN
     greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
         ) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))
         THEN 'OLD ONE!'
         ELSE 'USED'
  END AS USED
FROM incs
  JOIN db ON (db.db_key=incs.db_key)
  LEFT OUTER JOIN mbdf ON (incs.dbinc_key=mbdf.dbinc_key)
  LEFT OUTER JOIN mcdf ON (incs.dbinc_key=mcdf.dbinc_key)
  LEFT OUTER JOIN mbrl ON (incs.dbinc_key=mbrl.dbinc_key)
  LEFT OUTER JOIN mal ON (incs.dbinc_key=mal.dbinc_key)
  LEFT OUTER JOIN mbs ON (incs.db_key=mbs.db_key)
  LEFT OUTER JOIN conf c ON (c.db_key=incs.db_key AND c.NAME = 'RETENTION POLICY' AND value LIKE 'TO RECOVERY WINDOW OF %')
 WHERE 1=1
 AND greatest(
     nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_al_time ,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),
         nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))
         ) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))
  order by 4,3, 5
  ;

   r_old_incarnation    c_old_incarnations%ROWTYPE;

   BEGIN
        OPEN c_old_incarnations;
        LOOP
                FETCH c_old_incarnations INTO r_old_incarnation;
                EXIT WHEN  c_old_incarnations%NOTFOUND;

                dbms_output.put('Purging db: ' || r_old_incarnation.db_name);
                dbms_output.put('       IncKey: ' || r_old_incarnation.db_key);
                dbms_output.put('       DBID: ' || r_old_incarnation.db_id);
                dbms_output.put_line('  Last BCK: ' || to_char(r_old_incarnation.last_bck,'YYYY-MM-DD'));
                BEGIN
                   dbms_rcvcat.unregisterdatabase(DB_KEY => r_old_incarnation.db_key, DB_ID => r_old_incarnation.db_id);
                EXCEPTION
                    WHEN e_dbatabase_not_found THEN
                    dbms_output.put_line('Database already unregistered');
                END;
        END LOOP;

        CLOSE c_old_incarnations;
	
END;
/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

col db_uq_name for a12

col dbinc_key_ for a30

set pages 100 lines 200

set serveroutput on

DECLARE

e_dbatabase_not_found EXCEPTION;

PRAGMA EXCEPTION_INIT (e_dbatabase_not_found, -20001);

CURSOR c_old_incarnations IS

WITH

incs AS (

SELECT lpad(' ',2*(level-1))|| to_char(dbinc_key) AS dbinc_key_

,dbinc_key

,db_key

,db_name

,reset_time

,dbinc_status

FROM rman.dbinc

START WITH parent_dbinc_key IS NULL

CONNECT BY PRIOR dbinc_key = parent_dbinc_key

ORDER BY db_name, db_key, level

mbdf AS (

SELECT dbinc_key

,max(completion_time) max_bdf_time

FROM bdf

GROUP by dbinc_key

mbrl AS (

SELECT dbinc_key

,max(next_time) max_brl_time

FROM brl

GROUP by dbinc_key

mal AS (

SELECT dbinc_key

,max(completion_time) max_al_time

FROM al

GROUP by dbinc_key

mcdf AS (

SELECT dbinc_key

,max(completion_time) max_cdf_time

FROM cdf

GROUP by dbinc_key

mbs AS (

SELECT db_key

,max(completion_time) max_bs_time

FROM bs

GROUP by db_key

)

SELECT distinct incs.db_key, db.db_id, db.REG_DB_UNIQUE_NAME AS db_uq_name , incs.db_name

,greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) AS last_bck

,CASE WHEN

greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))

THEN 'OLD ONE!'

ELSE 'USED'

END AS USED

FROM incs

JOIN db ON (db.db_key=incs.db_key)

LEFT OUTER JOIN mbdf ON (incs.dbinc_key=mbdf.dbinc_key)

LEFT OUTER JOIN mcdf ON (incs.dbinc_key=mcdf.dbinc_key)

LEFT OUTER JOIN mbrl ON (incs.dbinc_key=mbrl.dbinc_key)

LEFT OUTER JOIN mal ON (incs.dbinc_key=mal.dbinc_key)

LEFT OUTER JOIN mbs ON (incs.db_key=mbs.db_key)

LEFT OUTER JOIN conf c ON (c.db_key=incs.db_key AND c.NAME = 'RETENTION POLICY' AND value LIKE 'TO RECOVERY WINDOW OF %')

WHERE 1=1

AND greatest(

nvl(max_bdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_brl_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_al_time ,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_cdf_time,to_date('1970-01-01','YYYY-MM-DD')),

nvl(max_bs_time,to_date('1970-01-01','YYYY-MM-DD'))

) < (sysdate - nvl(to_number(substr(c.value,23,length(c.value)-27)),365))

order by 4,3, 5

;

r_old_incarnation c_old_incarnations%ROWTYPE;

BEGIN

OPEN c_old_incarnations;

LOOP

FETCH c_old_incarnations INTO r_old_incarnation;

EXIT WHEN c_old_incarnations%NOTFOUND;

dbms_output.put('Purging db: ' || r_old_incarnation.db_name);

dbms_output.put(' IncKey: ' || r_old_incarnation.db_key);

dbms_output.put(' DBID: ' || r_old_incarnation.db_id);

dbms_output.put_line(' Last BCK: ' || to_char(r_old_incarnation.last_bck,'YYYY-MM-DD'));

BEGIN

dbms_rcvcat.unregisterdatabase(DB_KEY => r_old_incarnation.db_key, DB_ID => r_old_incarnation.db_id);

EXCEPTION

WHEN e_dbatabase_not_found THEN

dbms_output.put_line('Database already unregistered');

END;

END LOOP;

CLOSE c_old_incarnations;

END;

I have used this procedure today for the first time and it worked like a charm.

However, if you have any adjustment or suggestion, don’t hesitate to comment it 🙂

HTH

Getting the DBID and Incarnation from the RMAN Catalog

Posted on February 15, 2016 by Ludovico

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 most of you do it 😉

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.

The DBID 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 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.

The Incarnation Key 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.

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.

https://docs.oracle.com/database/121/RCMRF/rcmsynta2007.htm#RCMRF148

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.

How do I get the history of my database incarnations?

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):

SQL> SELECT lpad(' ',2*(level-1))
  || TO_CHAR(DBINC_KEY) AS DBINC_KEY,
  db_key,
  db_name,
  TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),
  dbinc_status
FROM rman.dbinc
  START WITH PARENT_DBINC_KEY IS NULL
  CONNECT BY prior DBINC_KEY   = PARENT_DBINC_KEY ;

DBINC_KEY                     DB_KEY DB_NAME    TO_CHAR(RESET_TIME, DBINC_ST
------------------------- ---------- ---------- ------------------- --------
356247416                  356247380 A9EE272A   2011-09-24 18:22:58 PARENT
  356247387                356247380 A9EE272A   2012-10-24 08:41:41 PARENT
    1149458631             356247380 A9EE272A   2014-10-10 08:30:57 CURRENT
360319357                  360319322 F5FD787F   2011-10-14 15:39:19 PARENT
  360319323                360319322 F5FD787F   2012-11-08 18:57:26 PARENT
    547928008              360319322 F5FD787F   2013-09-10 10:57:44 PARENT
      576592237            360319322 F5FD787F   2013-11-20 14:54:05 ORPHAN
      576613820            360319322 F5FD787F   2013-11-20 15:57:03 ORPHAN
      584503796            360319322 F5FD787F   2013-11-27 13:57:53 CURRENT
364099232                  364099231 25E64A7F   2012-11-20 08:01:49 PARENT
  415031968                364099231 25E64A7F   2013-02-15 12:16:15 PARENT
    456099512              364099231 25E64A7F   2013-05-03 12:19:52 CURRENT
366065362                  366065336 3AE45141   2011-09-24 18:22:58 PARENT
  366065337                366065336 3AE45141   2012-11-26 17:14:14 CURRENT
394067322                  394067321 C34FFA7E   2013-01-10 17:18:11 CURRENT
402469086                  402469073 D164DDB8   2011-09-24 18:22:58 PARENT
  402469074                402469073 D164DDB8   2013-01-29 11:20:19 CURRENT
410147332                  410147283 27984513   2011-09-24 18:22:58 PARENT
  410147284                410147283 27984513   2013-02-08 11:12:38 CURRENT
...
...

SQL> SELECT lpad(' ',2*(level-1))

|| TO_CHAR(DBINC_KEY) AS DBINC_KEY,

db_key,

db_name,

TO_CHAR(reset_time,'YYYY-MM-DD HH24:MI:SS'),

dbinc_status

FROM rman.dbinc

START WITH PARENT_DBINC_KEY IS NULL

CONNECT BY prior DBINC_KEY = PARENT_DBINC_KEY ;

DBINC_KEY DB_KEY DB_NAME TO_CHAR(RESET_TIME, DBINC_ST

------------------------- ---------- ---------- ------------------- --------

356247416 356247380 A9EE272A 2011-09-24 18:22:58 PARENT

356247387 356247380 A9EE272A 2012-10-24 08:41:41 PARENT

1149458631 356247380 A9EE272A 2014-10-10 08:30:57 CURRENT

360319357 360319322 F5FD787F 2011-10-14 15:39:19 PARENT

360319323 360319322 F5FD787F 2012-11-08 18:57:26 PARENT

547928008 360319322 F5FD787F 2013-09-10 10:57:44 PARENT

576592237 360319322 F5FD787F 2013-11-20 14:54:05 ORPHAN

576613820 360319322 F5FD787F 2013-11-20 15:57:03 ORPHAN

584503796 360319322 F5FD787F 2013-11-27 13:57:53 CURRENT

364099232 364099231 25E64A7F 2012-11-20 08:01:49 PARENT

415031968 364099231 25E64A7F 2013-02-15 12:16:15 PARENT

456099512 364099231 25E64A7F 2013-05-03 12:19:52 CURRENT

366065362 366065336 3AE45141 2011-09-24 18:22:58 PARENT

366065337 366065336 3AE45141 2012-11-26 17:14:14 CURRENT

394067322 394067321 C34FFA7E 2013-01-10 17:18:11 CURRENT

402469086 402469073 D164DDB8 2011-09-24 18:22:58 PARENT

402469074 402469073 D164DDB8 2013-01-29 11:20:19 CURRENT

410147332 410147283 27984513 2011-09-24 18:22:58 PARENT

410147284 410147283 27984513 2013-02-08 11:12:38 CURRENT

...

What about getting the correct DBID/DBINC_KEY pair for a specific database/time?

You can get the time windows for each incarnation using the lead() analytical function:

SQL> WITH dbids AS
  (SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,
    dbinc.db_key,
    dbinc.db_name,
    dbinc.reset_time,
    dbinc.dbinc_status,
    db.db_id
  FROM rman.dbinc dbinc
  JOIN rman.db db
  ON ( 
  dbinc.db_key   =db.db_key)
  )
select * from (
SELECT DBINC_KEY,
  db_name,
  db_id,
  reset_time,
  nvl(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset
FROM dbids
)
ORDER BY db_name ,
  reset_time ;  

DBINC_KEY                 DBNAME          DB_ID RESET_TIME          NEXTRESET
------------------------- ---------- ---------- ------------------- -------------------
1173852671                1DF63C30   2507085371 2014-07-07 05:38:47 2015-01-16 07:29:01
1173852635                1DF63C30   2507085371 2015-01-16 07:29:01 2015-02-27 16:25:13
1244346785                1DF63C30   2531796824 2015-02-27 16:25:13 2015-02-27 16:25:13
1281775847                1DF63C30   2541221473 2015-02-27 16:25:13 2015-02-27 16:25:13
1233975755                1DF63C30   2528008262 2015-02-27 16:25:13 2015-02-27 16:25:13
1220896058                1DF63C30   2523244390 2015-02-27 16:25:13 2015-03-16 16:06:00
1188550385                1DF63C30   2507085371 2015-03-16 16:06:00 2015-07-17 08:06:00
1220896028                1DF63C30   2523244390 2015-07-17 08:06:00 2015-09-10 11:23:53
1233975725                1DF63C30   2528008262 2015-09-10 11:23:53 2015-10-23 07:46:34
1244346755                1DF63C30   2531796824 2015-10-23 07:46:34 2016-02-08 09:44:03
1281775817                1DF63C30   2541221473 2016-02-08 09:44:03 2016-02-15 10:13:49
1201139592                1D0776F6   2025503263 2014-07-07 05:38:47 2015-05-04 17:08:50
1201139578                1D0776F6   2025503263 2015-05-04 17:08:50 2015-06-02 08:48:07
1213295265                1D0776F6   2029287211 2015-06-02 08:48:07 2015-06-02 08:48:07
1256000477                1D0776F6   2044568865 2015-06-02 08:48:07 2015-06-02 08:48:07
1235940868                1D0776F6   2037421528 2015-06-02 08:48:07 2015-06-17 12:14:38
1213295230                1D0776F6   2029287211 2015-06-17 12:14:38 2015-09-18 15:46:34
1235940852                1D0776F6   2037421528 2015-09-18 15:46:34 2015-12-08 09:08:52
1256000461                1D0776F6   2044568865 2015-12-08 09:08:52 2016-02-15 10:13:49
1173653066                2D828C2C   1656607497 2014-07-07 05:38:47 2015-01-15 14:06:04
1173653052                2D828C2C   1656607497 2015-01-15 14:06:04 2015-06-02 08:48:07
1247872446                2D828C2C   1682603029 2015-06-02 08:48:07 2015-06-02 08:48:07
1218354231                2D828C2C   1671898993 2015-06-02 08:48:07 2015-06-02 08:48:07
1278227063                2D828C2C   1690479985 2015-06-02 08:48:07 2015-06-02 08:48:07
1219084145                2D828C2C   1672155073 2015-06-02 08:48:07 2015-06-02 08:48:07
1228714578                2D828C2C   1675699280 2015-06-02 08:48:07 2015-06-02 08:48:07
1211451469                2D828C2C   1669565762 2015-06-02 08:48:07 2015-06-02 08:48:07
1235422982                2D828C2C   1678113471 2015-06-02 08:48:07 2015-06-02 08:48:07
1228713810                2D828C2C   1675697673 2015-06-02 08:48:07 2015-06-02 08:48:07
1240749487                2D828C2C   1680107003 2015-06-02 08:48:07 2015-06-02 08:48:07
1255743496                2D828C2C   1685361979 2015-06-02 08:48:07 2015-06-10 13:37:08
1211451453                2D828C2C   1669565762 2015-06-10 13:37:08 2015-07-06 13:44:20
1218354215                2D828C2C   1671898993 2015-07-06 13:44:20 2015-07-09 12:52:19
1219084129                2D828C2C   1672155073 2015-07-09 12:52:19 2015-08-19 12:55:40
1228713794                2D828C2C   1675697673 2015-08-19 12:55:40 2015-08-19 13:22:27
1228714562                2D828C2C   1675699280 2015-08-19 13:22:27 2015-09-16 11:58:58
1235422966                2D828C2C   1678113471 2015-09-16 11:58:58 2015-10-08 13:44:29
1240749471                2D828C2C   1680107003 2015-10-08 13:44:29 2015-11-06 11:04:55
1247872430                2D828C2C   1682603029 2015-11-06 11:04:55 2015-12-07 09:27:27
1255743480                2D828C2C   1685361979 2015-12-07 09:27:27 2016-02-04 15:07:29
1278227047                2D828C2C   1690479985 2016-02-04 15:07:29 2016-02-15 10:13:49

SQL> WITH dbids AS

(SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,

dbinc.db_key,

dbinc.db_name,

dbinc.reset_time,

dbinc.dbinc_status,

db.db_id

FROM rman.dbinc dbinc

JOIN rman.db db

ON (

dbinc.db_key =db.db_key)

)

select * from (

SELECT DBINC_KEY,

db_name,

db_id,

reset_time,

nvl(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset

FROM dbids

)

ORDER BY db_name ,

reset_time ;

DBINC_KEY DBNAME DB_ID RESET_TIME NEXTRESET

------------------------- ---------- ---------- ------------------- -------------------

1173852671 1DF63C30 2507085371 2014-07-07 05:38:47 2015-01-16 07:29:01

1173852635 1DF63C30 2507085371 2015-01-16 07:29:01 2015-02-27 16:25:13

1244346785 1DF63C30 2531796824 2015-02-27 16:25:13 2015-02-27 16:25:13

1281775847 1DF63C30 2541221473 2015-02-27 16:25:13 2015-02-27 16:25:13

1233975755 1DF63C30 2528008262 2015-02-27 16:25:13 2015-02-27 16:25:13

1220896058 1DF63C30 2523244390 2015-02-27 16:25:13 2015-03-16 16:06:00

1188550385 1DF63C30 2507085371 2015-03-16 16:06:00 2015-07-17 08:06:00

1220896028 1DF63C30 2523244390 2015-07-17 08:06:00 2015-09-10 11:23:53

1233975725 1DF63C30 2528008262 2015-09-10 11:23:53 2015-10-23 07:46:34

1244346755 1DF63C30 2531796824 2015-10-23 07:46:34 2016-02-08 09:44:03

1281775817 1DF63C30 2541221473 2016-02-08 09:44:03 2016-02-15 10:13:49

1201139592 1D0776F6 2025503263 2014-07-07 05:38:47 2015-05-04 17:08:50

1201139578 1D0776F6 2025503263 2015-05-04 17:08:50 2015-06-02 08:48:07

1213295265 1D0776F6 2029287211 2015-06-02 08:48:07 2015-06-02 08:48:07

1256000477 1D0776F6 2044568865 2015-06-02 08:48:07 2015-06-02 08:48:07

1235940868 1D0776F6 2037421528 2015-06-02 08:48:07 2015-06-17 12:14:38

1213295230 1D0776F6 2029287211 2015-06-17 12:14:38 2015-09-18 15:46:34

1235940852 1D0776F6 2037421528 2015-09-18 15:46:34 2015-12-08 09:08:52

1256000461 1D0776F6 2044568865 2015-12-08 09:08:52 2016-02-15 10:13:49

1173653066 2D828C2C 1656607497 2014-07-07 05:38:47 2015-01-15 14:06:04

1173653052 2D828C2C 1656607497 2015-01-15 14:06:04 2015-06-02 08:48:07

1247872446 2D828C2C 1682603029 2015-06-02 08:48:07 2015-06-02 08:48:07

1218354231 2D828C2C 1671898993 2015-06-02 08:48:07 2015-06-02 08:48:07

1278227063 2D828C2C 1690479985 2015-06-02 08:48:07 2015-06-02 08:48:07

1219084145 2D828C2C 1672155073 2015-06-02 08:48:07 2015-06-02 08:48:07

1228714578 2D828C2C 1675699280 2015-06-02 08:48:07 2015-06-02 08:48:07

1211451469 2D828C2C 1669565762 2015-06-02 08:48:07 2015-06-02 08:48:07

1235422982 2D828C2C 1678113471 2015-06-02 08:48:07 2015-06-02 08:48:07

1228713810 2D828C2C 1675697673 2015-06-02 08:48:07 2015-06-02 08:48:07

1240749487 2D828C2C 1680107003 2015-06-02 08:48:07 2015-06-02 08:48:07

1255743496 2D828C2C 1685361979 2015-06-02 08:48:07 2015-06-10 13:37:08

1211451453 2D828C2C 1669565762 2015-06-10 13:37:08 2015-07-06 13:44:20

1218354215 2D828C2C 1671898993 2015-07-06 13:44:20 2015-07-09 12:52:19

1219084129 2D828C2C 1672155073 2015-07-09 12:52:19 2015-08-19 12:55:40

1228713794 2D828C2C 1675697673 2015-08-19 12:55:40 2015-08-19 13:22:27

1228714562 2D828C2C 1675699280 2015-08-19 13:22:27 2015-09-16 11:58:58

1235422966 2D828C2C 1678113471 2015-09-16 11:58:58 2015-10-08 13:44:29

1240749471 2D828C2C 1680107003 2015-10-08 13:44:29 2015-11-06 11:04:55

1247872430 2D828C2C 1682603029 2015-11-06 11:04:55 2015-12-07 09:27:27

1255743480 2D828C2C 1685361979 2015-12-07 09:27:27 2016-02-04 15:07:29

1278227047 2D828C2C 1690479985 2016-02-04 15:07:29 2016-02-15 10:13:49

With this query, you can see that every incarnation has a reset time and a “next reset time”.

It’s easy then to get exactly what you need by adding a couple of where clauses:

SQL> WITH dbids AS
  (SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,
    dbinc.db_key,
    dbinc.db_name,
    dbinc.reset_time,
    dbinc.dbinc_status,
    db.db_id
  FROM rman.dbinc dbinc
  JOIN rman.db db
  ON ( --dbinc.dbinc_key=db.CURR_DBINC_KEY
    --AND
    dbinc.db_key =db.db_key)
  )
SELECT *
FROM
  (SELECT DBINC_KEY,
    db_name,
    db_id,
    reset_time,
    NVL(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset
  FROM dbids
  )
WHERE TO_DATE ('2016-01-20 00:00:00','YYYY-MM-DD HH24:MI;SS') BETWEEN reset_time AND next_reset
AND db_name='1465419F'
ORDER BY db_name ,
  reset_time ; 

DBINC_KEY                 DB_NAME         DB_ID RESET_TIME          NEXT_RESET
------------------------- ---------- ---------- ------------------- -------------------
1256014297                1465419F   1048383773 2015-12-08 11:03:55 2016-02-08 07:55:05

SQL> WITH dbids AS

(SELECT TO_CHAR(dbinc.DBINC_KEY) AS DBINC_KEY,

dbinc.db_key,

dbinc.db_name,

dbinc.reset_time,

dbinc.dbinc_status,

db.db_id

FROM rman.dbinc dbinc

JOIN rman.db db

ON ( --dbinc.dbinc_key=db.CURR_DBINC_KEY

--AND

dbinc.db_key =db.db_key)

)

SELECT *

FROM

(SELECT DBINC_KEY,

db_name,

db_id,

reset_time,

NVL(lead (reset_time) over (partition BY db_name order by reset_time),sysdate) AS next_reset

FROM dbids

)

WHERE TO_DATE ('2016-01-20 00:00:00','YYYY-MM-DD HH24:MI;SS') BETWEEN reset_time AND next_reset

AND db_name='1465419F'

ORDER BY db_name ,

reset_time ;

DBINC_KEY DB_NAME DB_ID RESET_TIME NEXT_RESET

------------------------- ---------- ---------- ------------------- -------------------

1256014297 1465419F 1048383773 2015-12-08 11:03:55 2016-02-08 07:55:05

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.

Cheers

—

Ludo

Oracle Database 12c: RMAN recover at table level

Posted on June 27, 2013 by Ludovico

Oracle Database 12c comes with a new feature named “RMAN table level recovery”.

After a quick try it’s easy to understand that we are talking about Tablespace Point-in-Time Recovery (TSPITR) with some automation to have it near-transparent.

How to launch it

The syntax is quite trivial. Suppose you’ve dropped a table ludovico.reco and then purged it (damn!) then you can’t flashback it to before drop and don’t want to flashback the entire database.

SQL> create table reco (field1 varchar2(50) primary key);

Table created.

SQL> insert into reco values ('test');

1 row created.

SQL> insert into reco values ('foo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from reco;

FIELD1
--------------------------------------------------
foo 
test

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
803916

SQL> drop table reco;

Table dropped.

SQL> purge table reco;

Table purged.

SQL> flashback table reco to before drop;
flashback table reco to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

SQL> create table reco (field1 varchar2(50) primary key);

Table created.

SQL> insert into reco values ('test');

1 row created.

SQL> insert into reco values ('foo');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from reco;

FIELD1

--------------------------------------------------

foo

test

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

803916

SQL> drop table reco;

Table dropped.

SQL> purge table reco;

Table purged.

SQL> flashback table reco to before drop;

flashback table reco to before drop

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

You can recover the table with:

RMAN> recover table ludovico.reco until scn 803916 auxiliary destination '/tmp/recover';

1	RMAN> recover table ludovico.reco until scn 803916 auxiliary destination '/tmp/recover';

You identify the schema.table:partition to restore, optionally you can pass the pluggable database containing the table to recover, the time definition as usual (scn, seq# or timestamp) and an auxiliary destination.

This Auxiliary destination is well-known to be mandatory for TSPITR. You can pass other options like table renaming or tablespace remapping.

Off course, the database must be open in read-write, in archivelog mode and at least one successful backup must be taken.

How it works

Oracle prepare an auxiliary instance by restoring the SYSTEM, UNDO and SYSAUX tablespaces.

Starting recover at 10-JUN-13
using channel ORA_DISK_1 
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM 
Tablespace UNDOTBS1

Creating automatic instance, with SID='kCoq'

initialization parameters used for automatic instance:
db_name=CLASSIC 
db_unique_name=kCoq_pitr_CLASSIC 
compatible=12.0.0.0.0 
db_block_size=8192 
db_files=200 
sga_target=1G 
processes=80 
diagnostic_dest=/u01/app/oracle 
db_create_file_dest=/tmp/recover 
log_archive_dest_1='location=/tmp/recover' 
#No auxiliary parameter file used

starting up automatic instance CLASSIC

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes
Variable Size 281020272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5480448 bytes
Automatic instance created

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# restore the controlfile 
restore clone controlfile; 
# mount the controlfile 
sql clone 'alter database mount clone database';
# archive current online log 
sql 'alter system archive log current'; 
} 
executing Memory Script

executing command: SET until clause

Starting restore at 10-JUN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07 
output file name=/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl 
Finished restore at 10-JUN-13

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new; 
set newname for clone datafile 3 to new; 
set newname for clone datafile 2 to new; 
set newname for clone tempfile 1 to new; 
# switch all tempfiles 
switch clone tempfile all; 
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2; 
switch clone datafile all; 
} 
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/recover/CLASSIC/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 10-JUN-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/recover/CLASSIC/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_%u_.dbf 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:56 
Finished restore at 10-JUN-13

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf
datafile 3 switched to datafile copy 
input datafile copy RECID=5 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf
datafile 2 switched to datafile copy 
input datafile copy RECID=6 STAMP=817751487 file name=/tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

Starting recover at 10-JUN-13

using channel ORA_DISK_1

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='kCoq'

initialization parameters used for automatic instance:

db_name=CLASSIC

db_unique_name=kCoq_pitr_CLASSIC

compatible=12.0.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/u01/app/oracle

db_create_file_dest=/tmp/recover

log_archive_dest_1='location=/tmp/recover'

#No auxiliary parameter file used

starting up automatic instance CLASSIC

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes

Variable Size 281020272 bytes

Database Buffers 780140544 bytes

Redo Buffers 5480448 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 10-JUN-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=19 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/ncsnf0_tag20130610t165249_0.270.817750463 tag=TAG20130610T165249

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

output file name=/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl

Finished restore at 10-JUN-13

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 3, 2;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/recover/CLASSIC/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 10-JUN-13

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/recover/CLASSIC/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:56

Finished restore at 10-JUN-13

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=817751486 file name=/tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=817751487 file name=/tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf

Then it opens in READ-ONLY mode the partial database.

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
# recover and open database read only 
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only'; 
} 
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

Starting recover at 10-JUN-13
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=54 
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=55 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 
archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54 
archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55 
media recovery complete, elapsed time: 00:00:10 
Finished recover at 10-JUN-13

sql statement: alter database open read only

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 3 online";

sql clone "alter database datafile 2 online";

# recover and open database read only

recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 2 online

Starting recover at 10-JUN-13

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=54

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=55

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54

archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55

media recovery complete, elapsed time: 00:00:10

Finished recover at 10-JUN-13

sql statement: alter database open read only

It uses then the read-only dictionary to take the tablespace that was containing the table before the data loss. This tablespace (users in my example) is restored and recovered, and the database is opened.

contents of Memory Script:

{ 
sql clone "create spfile from memory";
shutdown clone immediate; 
startup clone nomount; 
sql clone "alter system set control_files = 
''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment=
''RMAN set'' scope=spfile"; 
shutdown clone immediate; 
startup clone nomount; 
# mount database 
sql clone 'alter database mount clone database'; 
} 
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes
Variable Size 285214576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes

sql statement: alter system set control_files = ''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes
Variable Size 285214576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 4 to new; 
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 4; 
switch clone datafile all; 
} 
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 10-JUN-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=26 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 
Finished restore at 10-JUN-13

datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=817751583 file name=/tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf

contents of Memory Script:
{ 
# set requested point in time
set until scn 803916; 
# online the datafiles restored or switched
sql clone "alter database datafile 4 online";
# recover and open resetlogs 
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs; 
} 
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 4 online

Starting recover at 10-JUN-13
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=54 
channel ORA_AUX_DISK_1: restoring archived log 
archived log thread=1 sequence=55 
channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931
channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210
channel ORA_AUX_DISK_1: restored backup piece 1 
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03 
archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54 
channel clone_default: deleting archived log(s) 
archived log file name=/tmp/recover/1_54_814717091.dbf RECID=4 STAMP=817751585 
archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55 
channel clone_default: deleting archived log(s) 
archived log file name=/tmp/recover/1_55_814717091.dbf RECID=5 STAMP=817751587 
media recovery complete, elapsed time: 00:00:01 
Finished recover at 10-JUN-13

database opened

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

contents of Memory Script:

{

sql clone "create spfile from memory";

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set control_files =

''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment=

''RMAN set'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes

Variable Size 285214576 bytes

Database Buffers 775946240 bytes

Redo Buffers 5480448 bytes

sql statement: alter system set control_files = ''/tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2295952 bytes

Variable Size 285214576 bytes

Database Buffers 775946240 bytes

Redo Buffers 5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile 4 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 4;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 10-JUN-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=26 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/nnndf0_tag20130610t165249_0.269.817750371 tag=TAG20130610T165249

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

Finished restore at 10-JUN-13

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=817751583 file name=/tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf

contents of Memory Script:

{

# set requested point in time

set until scn 803916;

# online the datafiles restored or switched

sql clone "alter database datafile 4 online";

# recover and open resetlogs

recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 4 online

Starting recover at 10-JUN-13

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=54

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=55

channel ORA_AUX_DISK_1: reading from backup piece +FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931

channel ORA_AUX_DISK_1: piece handle=+FRA/CLASSIC/BACKUPSET/2013_06_10/annnf0_tag20130610t170210_0.277.817750931 tag=TAG20130610T170210

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

archived log file name=/tmp/recover/1_54_814717091.dbf thread=1 sequence=54

channel clone_default: deleting archived log(s)

archived log file name=/tmp/recover/1_54_814717091.dbf RECID=4 STAMP=817751585

archived log file name=/tmp/recover/1_55_814717091.dbf thread=1 sequence=55

channel clone_default: deleting archived log(s)

archived log file name=/tmp/recover/1_55_814717091.dbf RECID=5 STAMP=817751587

media recovery complete, elapsed time: 00:00:01

Finished recover at 10-JUN-13

database opened

At this point, RMAN starts an export/import with datapump to move the table from the auxiliary database back to the target database:

contents of Memory Script:
{ 
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/recover''"; 
# create directory for datapump export 
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/tmp/recover''"; 
} 
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover''

Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_kCoq_fqic": 
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB 
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE 
EXPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER 
EXPDP> . . exported "LUDOVICO"."RECO" 5.054 KB 2 rows
EXPDP> Master table "SYS"."TSPITR_EXP_kCoq_fqic" successfully loaded/unloaded 
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_kCoq_fqic is: 
EXPDP> /tmp/recover/tspitr_kCoq_82218.dmp 
EXPDP> Job "SYS"."TSPITR_EXP_kCoq_fqic" successfully completed at Mon Jun 10 17:14:44 2013 elapsed 0 00:00:41
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_kCoq_Fbti" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_kCoq_Fbti":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "LUDOVICO"."RECO" 5.054 KB 2 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_kCoq_Fbti" successfully completed at Mon Jun 10 17:15:12 2013 elapsed 0 00:00:07
Import completed

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/tmp/recover''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/tmp/recover''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/recover''

Performing export of tables...

EXPDP> Starting "SYS"."TSPITR_EXP_kCoq_fqic":

EXPDP> Estimate in progress using BLOCKS method...

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP> Total estimation using BLOCKS method: 64 KB

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

EXPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN

EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

EXPDP> . . exported "LUDOVICO"."RECO" 5.054 KB 2 rows

EXPDP> Master table "SYS"."TSPITR_EXP_kCoq_fqic" successfully loaded/unloaded

EXPDP> ******************************************************************************

EXPDP> Dump file set for SYS.TSPITR_EXP_kCoq_fqic is:

EXPDP> /tmp/recover/tspitr_kCoq_82218.dmp

EXPDP> Job "SYS"."TSPITR_EXP_kCoq_fqic" successfully completed at Mon Jun 10 17:14:44 2013 elapsed 0 00:00:41

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

IMPDP> Master table "SYS"."TSPITR_IMP_kCoq_Fbti" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_kCoq_Fbti":

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP> . . imported "LUDOVICO"."RECO" 5.054 KB 2 rows

IMPDP> Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN

IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

IMPDP> Job "SYS"."TSPITR_IMP_kCoq_Fbti" successfully completed at Mon Jun 10 17:15:12 2013 elapsed 0 00:00:07

Import completed

Finally, the auxiliary instance is cleaned:

Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_temp_8vcv5mrt_.tmp deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_3_8vcv8nfh_.log deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_2_8vcv8fqb_.log deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_1_8vcv86fv_.log deleted
auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf deleted
auxiliary instance file /tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl deleted
auxiliary instance file tspitr_kCoq_82218.dmp deleted
Finished recover at 10-JUN-13

Removing automatic instance

Automatic instance removed

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_temp_8vcv5mrt_.tmp deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_3_8vcv8nfh_.log deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_2_8vcv8fqb_.log deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/onlinelog/o1_mf_1_8vcv86fv_.log deleted

auxiliary instance file /tmp/recover/KCOQ_PITR_CLASSIC/datafile/o1_mf_users_8vcv7wh0_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_sysaux_8vctzf32_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_undotbs1_8vctzfc9_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/datafile/o1_mf_system_8vctzf86_.dbf deleted

auxiliary instance file /tmp/recover/CLASSIC/controlfile/o1_mf_8vctyxcy_.ctl deleted

auxiliary instance file tspitr_kCoq_82218.dmp deleted

Finished recover at 10-JUN-13

We can check if our table is ok:

RMAN> select * from ludovico.reco;

FIELD1
--------------------------------------------------
foo
test

RMAN>

RMAN> select * from ludovico.reco;

FIELD1

--------------------------------------------------

foo

test

RMAN>

Oh, and yes, now we can select directly from RMAN! 🙂

My opinion

It still needs the amount of space needed to recover the auxiliary instance (system, sysaux, temp and the user tablespace containing the missing data), so it has all the defeats of the typical TSPITR, but it’s automatic so is an improvement for the real life.
Restoring the user tablespace separately from the system tablespaces can be an issue if you’re saving backupsets over tape: you can end up by reading twice the same backupset that could be read once instead.

Cheers

Ludovico

Script that duplicates a database using a physical standby RAC as source

Posted on March 18, 2012 by Ludovico

It’s possibile to duplicate a database for testing purposes (it’s an example) using a standby database as source. This allows you to off-load the production environment.

This is a simple script that makes use of ASM and classic duplicate, although I guess it’s possible to use the standby DB for a duplicate from active database.
You can launch it everyday to align your test env at a point in time.

#!/bin/bash
if [ $USER != 'oracle' ] ; then
        echo "need to be oracle"
        exit 0
fi

. $HOME/set11
export ORACLE_SID=test1

srvctl stop database -d test -o immediate

## this is supposed to be a script that erase your ASM  from your old test dbfiles:
## it's as simple as running with the CORRECT ENV:
## asmcmd rm -rf \
##   +DATA/TEST/ONLINELOG
##   +DATA/TEST/DATAFILE
##   +DATA/TEST/CONTROLFILE
##   +DATA/TEST/TEMPFILE
##   +FRA/TEST/ONLINELOG
##   +FRA/TEST/CONTROLFILE

ssh grid@testsrv /shared/refresh_test/remove_test_files.sh

sqlplus / as sysdba < <EOF
        set echo on
        startup nomount
        alter system set cluster_database=false scope=spfile;
        shutdown immediate
        startup nomount
EOF

rman <<EOF
connect target sys/mystrongpassword@stdby
connect auxiliary /

duplicate database to 'test' until time "trunc(sysdate)+6/24";

EOF


sqlplus / as sysdba <<EOF
        set echo on
        alter system set cluster_database=true scope=spfile;
        shutdown immediate
        startup mount
        alter database noarchivelog;
        shutdown immediate
EOF


srvctl start database -d test

#!/bin/bash

if [ $USER != 'oracle' ] ; then

echo "need to be oracle"

exit 0

. $HOME/set11

export ORACLE_SID=test1

srvctl stop database -d test -o immediate

## this is supposed to be a script that erase your ASM from your old test dbfiles:

## it's as simple as running with the CORRECT ENV:

## asmcmd rm -rf \

## +DATA/TEST/ONLINELOG

## +DATA/TEST/DATAFILE

## +DATA/TEST/CONTROLFILE

## +DATA/TEST/TEMPFILE

## +FRA/TEST/ONLINELOG

## +FRA/TEST/CONTROLFILE

ssh grid@testsrv /shared/refresh_test/remove_test_files.sh

sqlplus / as sysdba < <EOF

set echo on

startup nomount

alter system set cluster_database=false scope=spfile;

shutdown immediate

startup nomount

EOF

rman <<EOF

connect target sys/mystrongpassword@stdby

connect auxiliary /

duplicate database to 'test' until time "trunc(sysdate)+6/24";

EOF

sqlplus / as sysdba <<EOF

set echo on

alter system set cluster_database=true scope=spfile;

shutdown immediate

startup mount

alter database noarchivelog;

shutdown immediate

EOF

srvctl start database -d test

Dog eat Dog… Oracle deletes itself by mistake!

Posted on July 21, 2011 by Ludovico

While implementing the backup on a new DB inherited from a customer, I scheduled our standard backup “type disk” procedure through rman, on Windows.
The morning after I saw that the “delete obsolete” tried to delete ALL CURRENT DATAFILES!!

i criteri di conservazione RMAN verranno applicati al comando i criteri di conservazione RMAN sono impostati su una ridondanza 1 canale allocato: ORA_DISK_1 canale ORA_DISK_1: sid=29 devtype=DISK Eliminazione dei seguenti backup e copie obsoleti: Tipo Chiave Ora fine Nome file/Handle -------------------- ------ ------------------ -------------------- Set di backup 917 28-GIU-11 ... Set di backup 927 29-GIU-11 Backup piece 1005 29-GIU-11 H:\ORACLE\BACKUP\ORAPERSP\RMAN\SPFILEBCK_20110629 Copia file di dati 14 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF Copia file di dati 16 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TOOLS01.DBF Copia file di dati 17 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\USERS01.DBF Copia file di dati 18 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\DRSYS01.DBF Copia file di dati 19 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\EXAMPLE01.DBF Copia file di dati 20 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\ODM01.DBF Copia file di dati 21 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\XDB01.DBF Copia file di dati 22 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\CWMLITE01.DBF Copia file di dati 23 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLDATI01.ORA Copia file di dati 24 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLINDEX01.ORA Copia file di dati 25 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\OEM_REPOSITORY1.ORA Copia file di dati 26 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\SYSTEM01.DBF Copia file di dati 27 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\UNDOTBS01.DBF backup piece eliminata ... backup piece eliminata handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\C-2220366420-20110628-02 recid=990 stamp=755031582 backup piece eliminata handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\C-2220366420-20110629-00 recid=1002 stamp=755130872 backup piece eliminata handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\CTL_20110629 recid=1004 stamp=755130883 backup piece eliminata handle di backup piece=H:\ORACLE\BACKUP\ORAPERSP\RMAN\SPFILEBCK_20110629 recid=1005 stamp=755130885 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of delete command on ORA_DISK_1 channel at 06/29/2011 22:34:55 ORA-19584: file E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF già in usoRecovery Manager ha terminato.

That’s because all current datafiles were registered into recovery catalog as backup copy. With a recovery redundancy of 1, all datafiles were set as obsolete! But since it’s windows, a delete command doesn’t delete datafiles if they are already in use. What it was on unix? We had just luck!

Then we had to uncatalog all copies.

RMAN> list copy;


la specifica non corrisponde a nessuno dei log di archivio del Recovery Catalog

Lista di copie del file di dati Chiave SCN Ckp file S Ora di completamento Nome Ora ckp ------- ---- - -------------------- ---------- -------------------- ---- 26 1 X 29-NOV-10 18535127593 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\SYSTEM01.DBF 27 2 X 29-NOV-10 18535127762 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\UNDOTBS01.DBF 14 3 X 29-NOV-10 18535122625 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF 16 4 X 29-NOV-10 18535123721 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TOOLS01.DBF 17 5 X 29-NOV-10 18535124423 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\USERS01.DBF 18 6 X 29-NOV-10 18535124439 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\DRSYS01.DBF 19 7 X 29-NOV-10 18535124453 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\EXAMPLE01.DBF 20 8 X 29-NOV-10 18535124554 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\ODM01.DBF 21 9 X 29-NOV-10 18535125790 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\XDB01.DBF 22 10 X 29-NOV-10 18535125874 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\CWMLITE01.DBF 23 11 X 29-NOV-10 18535125887 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLDATI01.ORA 24 12 X 29-NOV-10 18535126750 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\TBLINDEX01.ORA 25 13 X 29-NOV-10 18535127211 29-NOV-10 E:\ORACLE\ORADATA\ORAPERSP\OEM_REPOSITORY1.ORA

RMAN> change copy of datafile 1..N uncatalog;

copia non catalogata del file di dati filename di copia del file di dati=E:\ORACLE\ORADATA\ORAPERSP\INDX01.DBF recid=14 stamp=736336991 Oggetti 1 non catalogati ...

until no “obsolete” current datafile were reported!

RMAN> report obsolete;

i criteri di conservazione RMAN verranno applicati al comando i criteri di conservazione RMAN sono impostati su una ridondanza 1 non sono stati trovati backup obsoleti

Lesson learned: never schedule delete obsolete without actually checking what could be deleted!