snapshot controlfile and RMAN-08512

As RMAN needs a consistent view of the control file it takes a backup of the controlfile by creating a snapshot and during the backup RMAN uses the snapshot of the controlfile. By default the snapshot controlfile is created in $ORACLE_HOME/dbs/snapcf_ORACLE_SID.f which can be changed by setting the following parameter in RMAN

$ rman target /
– display parameter value
RMAN> show all;
..
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/10.2.0.4/TESTDB/snapcf_TESTDB.f'; # default
– set to new path for controlfile snapshot
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u02/TESTDB/snapcf_TESTDB.f';

When multiple RMAN are running one could run into an issue RMAN-08512 where RMAN is waiting for getting a lock on snapshot controlfile header. To find the session run the following SQL.

SELECT vs.sid, vs.username, vs.program, vs.module, TO_CHAR(vs.logon_time, 'DD-MON-YYYY HH24:MI:SS')
FROM v$session vs, v$enqueue_lock vel
WHERE vs.sid = vel.sid AND vel.type = 'CF' AND vel.id1 = 0 AND vel.id2 = 2

How to fix Target Database Incarnation Is Not Current In Recovery Catalog RMAN-20011?

When doing a incomplete recovery (i.e. database is open with resetlogs) and when running RMAN to backup that database after incomplete recovery you will get the following error.

RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog

To fix this issue by performing the following steps.

1) Connect to RMAN catalog via SQL*Plus
$ sqlplus @rmancatalog

SQL> SELECT name, DBID, RESETLOGS_TIME FROM rc_database WHERE dbid=9999999999;

NAME DBID RESETLOGS
——– ———- ———
TESTDB 9999999999 29-JUL-09

Check the latest incarnation
SQL> SELECT dbid, name, dbinc_key, resetlogs_change#, resetlogs_time FROM rc_database_incarnation WHERE dbid=9999999999 ORDER BY resetlogs_time;

SQL>SELECT db_key,DBID,name,current_incarnation FROM rc_database_incarnation WHERE dbid = 9999999999 order by 1;

DBID NAME DBINC_KEY RESETLOGS_CHANGE# TO_CHAR(RESETLOGS_TI
———- ——– ———- —————– ——————–
9999999999 TESTDB 473490 1 16-FEB-2006 08:59:37
9999999999 TESTDB 473484 565658 07-MAY-2007 10:15:58
9999999999 TESTDB 774712 52500357 29-JUL-2009 11:12:21

So then connect to the rman catalog
$ export NLS_DATE_FORMAT=’DD-MON-YYYY HH24:MI:SS’
$ rman catalog target /
RMAN> reset database to incarnation 774712; — From step 1 after running this step you should be able to run backup on the database on which incomplete recovery was done

RMAN> resync catalog;

– shows the current incarnation is the current one
RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
473483 473490 TESTDB 9999999999 PARENT 1 16-FEB-2006 08:59:37
473483 473484 TESTDB 9999999999 PARENT 565658 07-MAY-2007 10:15:58
473483 774712 TESTDB 9999999999 CURRENT 52500357 29-JUL-2009 11:12:21

How to delete archive logs already archived to backup device?

If one is keeping archive logs available on disk and also have them archived to tape and would like to delete only the ones that have been archived to tape in the archive directory through RMAN, you can use the following command.

connect target /
connect catalog rmancatalog/rmancatalog@catalog;
# For Tape
run {
DELETE ARCHIVELOG LIKE '%' BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE;
}

# For Disk
run {
DELETE ARCHIVELOG LIKE '%' BACKED UP 1 TIMES TO DEVICE TYPE DISK;
}

# Delete logs backed 2 times and more than 1/2 day old
run {
delete noprompt archivelog until time = 'sysdate-0.5' backed up 2 times to sbt ;
}

How to keep 2 days of archive log on disk and still have back to tape?

Using the following rman command one can setup to keep 2 days of archive log on disk and also archive them to tape/disk the ones that haven’t been sent to.

connect target /
connect rmancatalog/rmancatalog@catalog

run {
# tells to backup all archive logs that haven’t been backed up one time which prevents from backing up more than once to
backup archivelog all not backed up 1 times;
# deletes archive log more than 2 days old, as the 2nd command runs after the first one so if some reason first one fails the delete step won’t run
delete archivelog until time ‘SYSDATE-2′;
}

There is another way of doing which is:
# set the following parameter in the rman parameters
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1;
run {
delete archivelog all completed before ’sysdate-2′;
}

How to find objects that are performing unrecoverable/nologgging option?

Using the steps below one can find the object that is unrecoverable from UNRECOVERABLE_CHANGE# in the v$datafile, one would use this to find the object that is unrecoverable since the last backup.

In this scenario datafile# 5 had unrecoverable/nologging as when we query v$datafile it shows and UNRECOVERABLE_TIME was greater than when the last backup was done.

SQL> column UNRECOVERABLE_CHANGE# format 99999999999999

SQL> select file#, UNRECOVERABLE_CHANGE# from v$datafile where file# = 5;

FILE# UNRECOVERABLE_CHANGE#
———- ———————
5 37640948153

– find the archive log that contains the sequence#
SQL> select sequence#, name from v$archived_log where 37640948153 >= first_change# and 37640948153 < next_change#;

SEQUENCE# NAME
———- ——————————————————————————–
2108 /u01/TESTDB/arch/arch_1_2108.arc

– This will create file in user_dump
SQL> alter system dump logfile '/u01/TESTDB/arch/arch_1_2108.arc' layer 19;

In 8i the object id is not included but in the 10g trace file the object id is included in the trace file
/u01/admin/TESTDB/udump $ grep DBA ora_168168_testdb.trc

…
CHANGE #1 INVLD AFN:5 DBA:0×01401e0a BLKS:0×0020 SCN:0×0008.c39349b9 SEQ: 1 OP:19.2
…

# to find the file# and block
SET SERVEROUTPUT ON
DECLARE
l_dba NUMBER := TO_NUMBER (‘01401e0a’,'XXXXXXXX’);
l_file NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba);
l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba);
BEGIN
DBMS_OUTPUT.PUT_LINE (‘File : ‘||l_file);
DBMS_OUTPUT.PUT_LINE (‘Block : ‘||l_block);
END;
/

Output:
File : 5
Block : 7690

SQL> SELECT owner, segment_name FROM dba_extents where file_id = 5 and block_id = 7690;

OWNER SEGMENT_NAME
—————————— —————-
SCOTT TEST_TABLE

How to restore archive logs from backup piece?

If one needs to restore archive logs to disk and not apply them, for example in case when cloning a database manually and one is missing the archive logs from the source database and you need to restore the archive logs on the source machine. So using the following command one can restore the archive logs.

connect target /
connect rcvcat rmancatalog/rmancatalog@catdb

run {
restore archivelog from logseq=36747 until logseq=36753 thread=1;
}
from logseq – is the starting log sequence #
until logseq – is the end log sequence #

How to fix when backup piece is not found when deleting obsolete backup piece?

As part of the RMAN script during backup to disk for example you may have a statement to delete the obsolete backups. And in cases when backup piece is not found the RMAN fails with the error shown below when it can’t delete the obsolete backup.

Example (portion of the script to delete obsolete backup):
run {
….
DELETE NOPROMPT OBSOLETE REDUNDANCY = 7;
}

Error message displayed when backup piece is not found.
RMAN-06207: WARNING: 2 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status

This error can be fixed if the FORCE option is used when deleting obsolete backup, it will update the RMAN repository that the backup has been deleted regardless it was found or not.

run {
DELETE FORCE NOPROMPT OBSOLETE REDUNDANCY = 7;
}

How to enable/disable block tracking for RMAN?

Using block tracking one can improve performance of the backup time of incremental backup from hours to minutes depending on size of database as it no longer needs to scan blocks in datafiles to find blocks changed it can use the block tracking file to identify changed blocks for incremental backup. Enabling this starts a new background process called Change Tracking Writer (CTWR) which manages this file.

Here are the steps to enable block tracking:
– Note if the file already exists the command will fail unless the REUSE option is used so first check if block tracking is not enabled before overwriting the file.

– if it returns DISABLED it means block tracking is off
SQL> SELECT distinct status from V$BLOCK_CHANGE_TRACKING;

STATUS
———-
DISABLED

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘/u04/oradata/TEST/rman_block_tracking.f’;

Database altered.

– indicating block tracking is enabled and the find the block tracking file
SQL> select status, filename from V$BLOCK_CHANGE_TRACKING;

STATUS
———-
FILENAME
——————————————————————————–
ENABLED
/u04/oradata/TEST/rman_block_tracking.f’

After block tracking file is created you would need to do level 0 backup to ensure the next incremental backup uses the block tracking file.

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

The subsequent incremental backup (differential/cumulative) would use the block change tracking file to identify the changes that need to be backed up.
– For differential incremental backup, this will backup blocks since last last level 0 or level 1 which ever is latter.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

– For cumulative incremental backups, this will backup all blocks changed since level 0 backup
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

To disable block tracking:
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Shows the process that are using the file, and when RMAN is running it will have handles on the file when running incremental backup.
$ fuser /u04/oradata/TEST/rman_block_tracking.f
/u04/oradata/TEST/rman_block_tracking.f: 9597302
oracle 9597302 1 0 13:29:41 – 0:31 ora_ctwr_TEST

How to check syntax of rman commands/script?

One can check syntax for RMAN commands without running the RMAN.

Example: Checking syntax of commands on the command line.
$ rman checksyntax

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

RMAN> backup database;

The command has no syntax errors

RMAN> exit

Example: Checking syntax of commands on the command script.
$ cat backup.txt
connect target /
connect catalog rmancatalog/rmancatlog@catalog

run {
backup database;
}

$ rman checksyntax @backup.txt

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 30 09:31:51 2008

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

RMAN> connect target *
2> connect catalog *
3>
4> run {
5> backup database;
6> }
7>
The cmdfile has no syntax errors

Recovery Manager complete.

How to fix ORA-19571 during running of RMAN?

Received ORA-19571 error when running of archive log process, and found the the parameter control_file_record_keep_time was set to 0 so we changed to higher value which fixed the issue. Note this parameter can be changed without restart if using spfile, click on this link to find how to find if you are using spfile or not.

Log file:
archive log filename=/u01/oradata/TEST/arch/TEST_173673.arc recid=173475 stamp=668792442
Crosschecked 3146 objects

starting full resync of recovery catalog
full resync complete

sql statement: alter system archive log current

Starting backup at 22-SEP-08
channel t1: starting archive log backupset
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 09/22/2008
ORA-19571: archived-log recid 173477 stamp 668795179 not found in controlfile