Search My Oracle Blog

Custom Search

30 August, 2015

RMAN -- 8 : Using a Recovery Catalog Schema

Besides retaining information about backups in the controlfile, Oracle allows the use of an (external) Recovery Catalog schema.  This schema is queryable via SQL in the same manner as querying any user / application schema.

Let's start with a database that already has backups present but created without a Recovery Catalog Schema.

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 30 19:48:30 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select to_char(completion_time,'DD-MON HH24:MI') Completed_at, datafile_blocks, blocks_read       
  2  from v$backup_datafile
  3  where file#=1
  4  order by completion_time;

COMPLETED_AT          DATAFILE_BLOCKS BLOCKS_READ
--------------------- --------------- -----------
01-AUG 22:10                   107648      107648
10-AUG 15:14                   107648      107648
10-AUG 19:58                   107648      107648
30-AUG 16:59                   107648      107648

SYS>

I now create a Catalog Schema and register this database into that schema.  There are 4 steps to this.  The first is to create the database that will hold t he Catalog Schema (here, we presume that the database has been created before today's steps).  The next is to create the database account for the Catalog Schema (I create the account RCAT_OWNER).  The third step is to login to the Catalog Schema with RMAN and run the CREATE CATALOG command.  The final step is to create an RMAN connection from the TARGET database to the Catalog and REGISTER the database.

[oracle@localhost ~]$ sqlplus system/oracle@rcat

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 30 19:52:15 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYSTEM>create user rcat_owner identified by rcat_owner
  2  default tablespace users quota unlimited on users;

User created.

SYSTEM>grant create session to rcat_owner;

Grant succeeded.

SYSTEM>grant recovery_catalog_owner to rcat_owner;

Grant succeeded.

SYSTEM>
SYSTEM>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ rman catalog rcat_owner@rcat

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Aug 30 19:53:26 2015

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

recovery catalog database Password: 
connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ rman target / catalog rcat_owner/rcat_owner@rcat

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Aug 30 19:55:59 2015

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

connected to target database: ORCL (DBID=1229390655)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> 

When my (TARGET) database ("ORCL") is registered, Oracle automatically does a RESYNC CATALOG. Can I now see my backups in the Catalog ?

RMAN> list backup of datafile 1;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
381     Full    863.89M    DISK        00:03:38     10-AUG-15      
        BP Key: 391   Status: AVAILABLE  Compressed: YES  Tag: TAG20150810T195515
        Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/o1_mf_nnndf_TAG20150810T195515_bwk493jn_.bkp
  List of Datafiles in backup set 381
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14186110   10-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
385     Full    238.12M    DISK        00:01:09     30-AUG-15      
        BP Key: 395   Status: AVAILABLE  Compressed: YES  Tag: TAG20150830T165804
        Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_30/o1_mf_nnndf_TAG20150830T165804_by5kdwrb_.bkp
  List of Datafiles in backup set 385
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14198051   30-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN> 

Why does it show only the latest two backups ? What about the preceding two backups that I could see in V$BACKUP_DATAFILE ? Let me check those backups without a CATALOG connection.

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Aug 30 19:59:59 2015

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

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
254     Full    733.27M    DISK        00:04:51     01-AUG-15      
        BP Key: 266   Status: AVAILABLE  Compressed: YES  Tag: TAG20150801T220612
        Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_01/o1_mf_nnndf_TAG20150801T220612_bvsnlnpn_.bkp
  List of Datafiles in backup set 254
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14157609   01-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
262     Full    733.23M    DISK        00:03:17     10-AUG-15      
        BP Key: 274   Status: AVAILABLE  Compressed: YES  Tag: TAG20150810T151144
        Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/o1_mf_nnndf_TAG20150810T151144_bwjmojs6_.bkp
  List of Datafiles in backup set 262
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14158847   10-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
271     Full    863.89M    DISK        00:03:38     10-AUG-15      
        BP Key: 283   Status: AVAILABLE  Compressed: YES  Tag: TAG20150810T195515
        Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/o1_mf_nnndf_TAG20150810T195515_bwk493jn_.bkp
  List of Datafiles in backup set 271
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14186110   10-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
275     Full    238.12M    DISK        00:01:09     30-AUG-15      
        BP Key: 287   Status: AVAILABLE  Compressed: YES  Tag: TAG20150830T165804
        Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_30/o1_mf_nnndf_TAG20150830T165804_by5kdwrb_.bkp
  List of Datafiles in backup set 275
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14198051   30-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN> 

When I disconnect from the Catalog Schema and do a local only (TARGET) connection, I can see 4 backups of the datafile.  So, what gives ?  Let me try an SQL query on V$BACKUP_DATAFILE.
(As an aside : Note above how there an be discrepancy in the listings showed by LIST BACKUP in the two scenarios (a) without a Recovery Catalog connection  and  (b) with a Recovery Catalog that was created after the last RESETLOGS).  This is something to remember.

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 30 20:01:54 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS>select to_char(completion_time,'DD-MON HH24:MI') Completed_at, datafile_blocks, blocks_read,                   
  2  resetlogs_change#, resetlogs_time
  3  from v$backup_datafile
  4  where file#=1
  5  order by completion_time
  6  /

COMPLETED_AT          DATAFILE_BLOCKS BLOCKS_READ RESETLOGS_CHANGE# RESETLOGS
--------------------- --------------- ----------- ----------------- ---------
01-AUG 22:10                   107648      107648          14082620 04-JUL-15
10-AUG 15:14                   107648      107648          14082620 04-JUL-15
10-AUG 19:58                   107648      107648          14185666 10-AUG-15
30-AUG 16:59                   107648      107648          14185666 10-AUG-15

SYS>

Notice that I have added two columns (RESETLOGS_CHANGE# and RESETLOGS_TIME) in the query.  Now, I see that the two older backups were from an *older* incarnation of the database.  They have a different RESETLOGS_CHANGE# / RESETLOGS_TIME.  So, those backups of the older incarnation are not cataloged into the Catalog Schema !

Can I do anything about this ?  It seems that the full RESYNC doesn't resync for backups of previous incarnations.   Can I reset my RETENTION POLICY and then do a RESYNC ?

SYS>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ rman target / catalog rcat_owner/rcat_owner@rcat

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Aug 30 20:08:06 2015

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

connected to target database: ORCL (DBID=1229390655)
connected to recovery catalog database

RMAN> show all;

RMAN configuration parameters for database with db_unique_name HEMANTDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/snapcf_orcl.f'; # default

RMAN> configure retention policy to recovery window of 36 days;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 36 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete

RMAN> list backup of datafile 1;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
381     Full    863.89M    DISK        00:03:38     10-AUG-15      
        BP Key: 391   Status: AVAILABLE  Compressed: YES  Tag: TAG20150810T195515
        Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/o1_mf_nnndf_TAG20150810T195515_bwk493jn_.bkp
  List of Datafiles in backup set 381
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14186110   10-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
385     Full    238.12M    DISK        00:01:09     30-AUG-15      
        BP Key: 395   Status: AVAILABLE  Compressed: YES  Tag: TAG20150830T165804
        Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_30/o1_mf_nnndf_TAG20150830T165804_by5kdwrb_.bkp
  List of Datafiles in backup set 385
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14198051   30-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN> 

No, extending the Recovery Window still doesn't help.  Can I try something else ?  What about the CATALOG command ?

[oracle@localhost ~]$ ls -l /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_01/
total 894280
-rw-rw---- 1 oracle oracle   7786496 Aug  1 22:06 o1_mf_annnn_TAG20150801T220605_bvsnlfrc_.bkp
-rw-rw---- 1 oracle oracle   2421248 Aug  1 22:06 o1_mf_annnn_TAG20150801T220605_bvsnlk3q_.bkp
-rw-rw---- 1 oracle oracle     56320 Aug  1 22:06 o1_mf_annnn_TAG20150801T220605_bvsnll9n_.bkp
-rw-rw---- 1 oracle oracle   3595776 Aug  1 22:06 o1_mf_annnn_TAG20150801T220605_bvsnlmdx_.bkp
-rw-rw---- 1 oracle oracle    165888 Aug  1 22:12 o1_mf_annnn_TAG20150801T221209_bvsnxs75_.bkp
-rw-rw---- 1 oracle oracle     16896 Aug  1 22:14 o1_mf_annnn_TAG20150801T221404_bvso1f28_.bkp
-rw-rw---- 1 oracle oracle 768901120 Aug  1 22:11 o1_mf_nnndf_TAG20150801T220612_bvsnlnpn_.bkp
-rw-rw---- 1 oracle oracle 110075904 Aug  1 22:11 o1_mf_nnndf_TAG20150801T220612_bvsnvwjj_.bkp
-rw-rw---- 1 oracle oracle  21782528 Aug  1 22:12 o1_mf_nnndf_TAG20150801T220612_bvsnx9v4_.bkp
[oracle@localhost ~]$ ls -l /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/
total 1771844
-rw-rw---- 1 oracle oracle    786944 Aug 10 15:11 o1_mf_annnn_TAG20150810T151143_bwjmohc7_.bkp
-rw-rw---- 1 oracle oracle     28672 Aug 10 15:16 o1_mf_annnn_TAG20150810T151601_bwjmxk5v_.bkp
-rw-rw---- 1 oracle oracle   4140032 Aug 10 19:55 o1_mf_annnn_TAG20150810T195509_bwk48xxv_.bkp
-rw-rw---- 1 oracle oracle    526336 Aug 10 19:55 o1_mf_annnn_TAG20150810T195509_bwk4911j_.bkp
-rw-rw---- 1 oracle oracle    311296 Aug 10 19:55 o1_mf_annnn_TAG20150810T195509_bwk4923s_.bkp
-rw-rw---- 1 oracle oracle     32256 Aug 10 19:59 o1_mf_annnn_TAG20150810T195901_bwk4j5lt_.bkp
-rw-rw---- 1 oracle oracle 768851968 Aug 10 15:15 o1_mf_nnndf_TAG20150810T151144_bwjmojs6_.bkp
-rw-rw---- 1 oracle oracle 110075904 Aug 10 15:15 o1_mf_nnndf_TAG20150810T151144_bwjmvycy_.bkp
-rw-rw---- 1 oracle oracle  21938176 Aug 10 15:15 o1_mf_nnndf_TAG20150810T151144_bwjmx1sv_.bkp
-rw-rw---- 1 oracle oracle 905863168 Aug 10 19:58 o1_mf_nnndf_TAG20150810T195515_bwk493jn_.bkp
[oracle@localhost ~]$ 


RMAN> catalog start with '/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_01/';

searching for all files that match the pattern /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_01/
no files found to be unknown to the database

RMAN> list backup of datafile 1;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
381     Full    863.89M    DISK        00:03:38     10-AUG-15      
        BP Key: 391   Status: AVAILABLE  Compressed: YES  Tag: TAG20150810T195515
        Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/o1_mf_nnndf_TAG20150810T195515_bwk493jn_.bkp
  List of Datafiles in backup set 381
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14186110   10-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
385     Full    238.12M    DISK        00:01:09     30-AUG-15      
        BP Key: 395   Status: AVAILABLE  Compressed: YES  Tag: TAG20150830T165804
        Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_30/o1_mf_nnndf_TAG20150830T165804_by5kdwrb_.bkp
  List of Datafiles in backup set 385
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 14198051   30-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN> 
RMAN> catalog start with '/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/';

searching for all files that match the pattern /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/
no files found to be unknown to the database

Well, apparently, even the CATALOG command refuses to catalog backup pieces from an older incarnation !

So, it seems that :
1.  If you've done a RESETLOGS recently and even though the controlfile may show previous backups, if you create a Catalog Schema after the RESETLOGS, previous backups (i.e. of the preceding incarnation)  are not visible in the Catalog  (disconnecting from the Catalog does allow you to view the previous backups in RMAN !)
2. The CATALOG command also will not include the previous backups, it will only accept backups of the current incarnation.

This testing has been done with 11.2.0.2    Has the behaviour changed in 11.2.0.4 / 12.1.0.1 / 12.1.0.2 ?

.
.
.


10 August, 2015

RMAN -- 7 : Recovery Through RESETLOGS -- how are the ArchiveLogs identified ?

When Oracle introduced "official" support for Recovery through RESETLOGS in 10g, it introduced the "%r" component of the log_archive_format parameter.  (This was not present in 9.2)

Therefore, in 10.2, we religiously included "%r" in the log_archive_format parameter.

However, if you use the  FRA via USE_DB_RECOVERY_FILE_DEST, the resetlogs_id is not present and  Oracle uses OMF naming rules for the archivelogs.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     154
Next log sequence to archive   156
Current log sequence           156
SQL> show parameter log_archive_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf
SQL> select name from v$archived_log where first_time > trunc(sysdate);

NAME
--------------------------------------------------------------------------------
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_152_bwjmnzt5_.arc
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_153_bwjmo4yp_.arc
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_154_bwjmoh29_.arc
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_155_bwjmxjyb_.arc

SQL> 

Thus, you can see in the above listing the archivelog filenames, the RESETLOGS_ID is *not* part of the archivelog filename.  (THREAD# and SEQUENCE# do still appear)

How, then, does Oracle (or do we ??) distinguish between archivelogs from one resetlogs and another ?  The archivelog sequence numbers do get reset to 1.
What matters  is that RESETLOGS_ID, RESETLOGS_CHANGE# and RESETLOGS_TIME are also incorporated into V$ARCHIVED_LOG.

SQL> select * from v$database_incarnation order by incarnation#;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
           1                 1 13-AUG-09                       0
PARENT     694825248                  0 NO

           2            754488 30-OCT-09                       1 13-AUG-09
PARENT     701609923                  1 NO

           3          14082620 04-JUL-15                  754488 30-OCT-09
CURRENT    884179148                  2 NO


SQL> select resetlogs_id, count(*) from v$archived_log group by resetlogs_id;

RESETLOGS_ID   COUNT(*)
------------ ----------
   884179148        153

SQL> 

My database's current Incarnation is 3 and all the archivelogs are for this Incarnation.  What if I were to do a RESETLOGS and go to a new Incarnation ?

First, I generate some more archivelogs in the current incarnation.

SQL> create table hemant.test_recovery_thru (id number);

Table created.

SQL> insert into hemant.test_recovery_thru select rownum from dual connect by level < 100001;

100000 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     156
Next log sequence to archive   158
Current log sequence           158
SQL>               

Then I shutdown the database and do a RESETLOGS on OPEN.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     159
Next log sequence to archive   161
Current log sequence           161
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             394267320 bytes
Database Buffers           54525952 bytes
Redo Buffers                6008832 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> 
SQL> select * from v$database_incarnation order by 1;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
           1                 1 13-AUG-09                       0
PARENT     694825248                  0 NO

           2            754488 30-OCT-09                       1 13-AUG-09
PARENT     701609923                  1 NO

           3          14082620 04-JUL-15                  754488 30-OCT-09
PARENT     884179148                  2 NO


INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
           4          14184835 10-AUG-15                14082620 04-JUL-15
CURRENT    887387582                  3 NO


SQL> 

I now have a new incarnation (4) created today. I next generate a couple of archivelogs and check the archivelogs

SQL> alter system archive log current;

System altered.

SQL> alter system archivelog current;
alter system archivelog current
             *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SQL> alter system archive log current;

System altered.

SQL> select resetlogs_id, sequence# 
  2  from v$archived_log
  3  where first_time > trunc(sysdate)
  4  order by 1,2;

RESETLOGS_ID  SEQUENCE#
------------ ----------
   884179148        152
   884179148        153
   884179148        154
   884179148        155
   884179148        156
   884179148        157
   884179148        158
   884179148        159
   884179148        160
   884179148        161
   887387582          1
   887387582          2

12 rows selected.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3
SQL> 
SQL> select resetlogs_id, name from v$archived_log where first_time > trunc(sysdate) order by first_time;

RESETLOGS_ID
------------
NAME
--------------------------------------------------------------------------------
   884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_152_bwjmnzt5_.arc

   884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_153_bwjmo4yp_.arc

   884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_154_bwjmoh29_.arc

   884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_155_bwjmxjyb_.arc

   884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_156_bwjr2nmj_.arc

   884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_157_bwjr2s1s_.arc

   884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_158_bwjr9pg5_.arc

   884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_159_bwjrb06z_.arc

   884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_160_bwjrb582_.arc

   884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_161_bwjrfz1j_.arc

   887387582
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_1_bwjrhogp_.arc

   887387582
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_2_bwjrj0gf_.arc


12 rows selected.

SQL> 

Notice the first 10 archivelogs are from the RESETLOGS_ID corresponding to Incarnation 3. The Thread# and SEQUENCE# do appear to be part of the file names. But NOT the RESETLOGS_ID.
The last two files now have SEQUENCE# 1 and 2 but no indication of the RESETLOGS_ID.  The RESETLOGS_ID is in V$ARCHIVED_LOG, not in the actual filename.

.
.
.



01 August, 2015

RMAN -- 6 : RETENTION POLICY and CONTROL_FILE_RECORD_KEEP_TIME

Most people read the documentation on CONTROL_FILE_RECORD_KEEP_TIME and believe that this parameter *guarantees* that Oracle will retain backup records for that long.  (Some do understand that backup records may be retained longer, depending on the availability of slots (or "records") for the various types of metadata in the controlfile).

However, .... as you should know from real-world experience ... there is always a "BUT".

Please read Oracle Support Note "How to ensure that backup metadata is retained in the controlfile when setting a retention policy and an RMAN catalog is NOT used. (Doc ID 461125.1)" and Bug 6458068

Oracle may need to "grow" the controlfile when adding information about ArchiveLogs or BackupSets / BackupPieces.
An example is this set of entries that occurred when I had created very many archivelogs and backuppieces for them :
Trying to expand controlfile section 13 for Oracle Managed Files
Expanded controlfile section 13 from 200 to 400 records
Requested to grow by 200 records; added 9 blocks of records


To understand the contents of the controlfile see how this listing shows that I have space for 400 records of Backup Pieces and am currently using 232 records.  :

SQL> select * from v$controlfile_record_section where type like '%BACKUP%' order by 1;

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
BACKUP CORRUPTION                     44           371            0           0          0          0
BACKUP DATAFILE                      200           245          159           1        159        159
BACKUP PIECE                         736           400          232         230         61        261
BACKUP REDOLOG                        76           215          173           1        173        173
BACKUP SET                            40           409          249           1        249        249
BACKUP SPFILE                        124           131           36           1         36         36

6 rows selected.

SQL> 


However, if I start creating new Backup Pieces without deleting older ones (without Oracle auto-deleting older ones) and Oracle hits the allocation of 400 records, it may try to add new records.  Oracle then prints a message (as shown above) into the alert.log.  Oracle may overwrite records older than control_file_record_keep_time.  If necesssary, it tries to expand the controlfile. If, however, there is not enough filesystem space (or space in the raw device or ASM DiskGroup) to expand the controlfile, it may have to ovewrite some records from the controlfile.  If it has to overwrite records that are older than control_file_record_keep_time, it provides no warning.  However, if it has to overwrite records that are not older than the control_file_record_keep_time, it *does* write a warning to the alert.log

I don't want to violate the Oracle Support policy and quote from the Note and the Bug but I urge you to read both very carefully.  The Note has a particular line about whether there is a relationship between the setting of the control_file_record_time and the Retention Policy.  In the Bug, there is one particularly line about whether the algorithm to extend / reuse / purge records in the controlfile is or is not related to the Retention Policy.  So it IS important to ensure that you have enough space for the controlfile to grow in case it needs to expand space for these records.

Also, remember that not all Retention Policies are defined in terms of days.  Some may be defined in terms of REDUNDANCY (the *number* of Full / L0 backups that are not to be obsoleted).  This does NOT relate to the number of days because Oracle can't predict how many backups you run in a day / in a week / in a month.  Take an organisation with a small database and runs 3 Full / L0 backups per day versus another with a very large database that runs Full / L0 backup only once a fortnight !  How many days of Full / L0 backups would each have to retain if the REDUNDANCY is set to, say, 3 ?

.
.
.




Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com