Crosscheck Backup

The RMAN command "crosscheck backup" will verify whether the RMAN backuppieces are still on the tape. If the RMAN backuppieces are no longer available in the tape, then the status will be changed from "A" for available to "X" for expired. Meanwhile, the RMAN command "delete force expired backup" will permanently delete the RMAN backuppieces from the tape that have a status of "X".

I believe "crosscheck backup" only check the MMD (media management device) database. Even the tape that holds the backup piece is not physically on the tape drive, the command will still consider the backup piece is available if it has not been deleted by the "delete obsolete" command (and is within the retention period).

I ran "list backup" and "list backupset", and saw every backup piece from both commands is on the list of backup pieces found Available from "crosscheck backup" command.

What could make the list of obsolete backups and the list of expired backups different?

"delete force expired backup" will permanently delete the RMAN backups that have a status of "X". However, the "force" clause will delete the corresponding records in the RMAN data dictionary even if RMAN cannot find the corresponding RMAN backuppieces on tape. My job running "delete force expired backup" against Veritas NetBackup hanged when the tape was not on the tape drive, but the one without "force" clause worked fine. It seems that "delete expired backup" only deletes entries from the MMD database.

If you just want to see the list of expired backups, use "delete expired backup" and then answer "no" on confirmation, or query the STATUS column (with 'X') of view RC_BACKUP_PIECE.

Sometimes, you may see erros:

RMAN-06207: WARNING: 41 objects could not be deleted for SBT_TAPE channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: ----- --------------------------------
RMAN-06214: Backup Piece ukjamesq_1_1
RMAN-06214: Backup Piece uljamesr_1_1

The RMAN-06207 and RMAN-06208 errors indicate that the RMAN backuppieces are no longer in the tape. To avoid the two errors, run the following RMAN commands, as shown below.

RMAN> allocate channel for maintenance type 'SBT_TAPE';
RMAN> crosscheck backup;
RMAN> delete expired backup;

But, it seems "crosscheck backup" only looks back to a certain time frame. Fairly old backup pieces may not get the status checked by the command, and the two errors keep showing up in the RMAN log on them. In some occasions, I had to query the BS_KEY column from view RC_BACKUP_PIECE on the catalog database, and then run "RMAN> delete FORCE NOPROMPT BACKUPSET bs_key#;" to delete the backupset after maintenace CHANNEL allocation. Keyword "force" is necessary in below run:

RMAN> delete force NOPROMPT BACKUPSET 218128;
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
218353 218128 1 1 AVAILABLE SBT_TAPE 36j9fc7c_1_1

deleted backup piece
backup piece handle=36j9fc7c_1_1 recid=102 stamp=647475436
Deleted 1 objects

One strange error I got is that I could not use tape channle to run the command on an instance:

RMAN> run {
2> ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
3> crosscheck backup;
4> RELEASE CHANNEL ch01;
5> }

allocated channel: ch01
channel ch01: sid=79 devtype=SBT_TAPE
channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006110304)
released channel: ch01
RMAN-00571: ===================================
RMAN-00569: ==== ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ===================================
RMAN-03002: failure of crosscheck command at 09/15/2008 12:40:34
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

Find the Archivelog names by using the SCN

During database recovery, you may have a SCN number and need to know the archivelog names. Here is the SQL for the answer:

column first_change# format 9,999,999,999
column next_change# format 9,999,999,999

alter session set nls_date_format='DD-MON-RRRR HH24:MI:SS';

select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where 35297312527 between first_change# and next_change#;

If you see 'D' in the STATUS column, the archive log has been deleted from the disk. You may need to restore it from the tape.

SEQUENCE# number usually shows up on the archivelog name. You can use RMAN command to restore them:

restore archivelog from logseq=45164 until logseq=45179;

Or, use commands to check the backup status:

list backup of archivelog all completed after 'SYSDATE - 21';
list backup of archivelog from logseq=45164 until logseq=45179;

RMAN "delete noprompt obsolete;"

The "delete obsolete" or "delete noprompt obsolete" will delete the archivelog files past the retention from disk and will also delete any backups on disk or tape.

allocate channel for maintenance type 'SBT_TAPE';
delete noprompt obsolete;
release channel;

will list two parts

. The first part lists the obsolete backups and copies (including archive logs).
. The 2nd part confirms what have been really deleted on archive logs and backup piece, with statement "Deleted xx objects".

When flash recovery area (FRA) is used, Oracle will automatically remove archive logs when space pressure is seen in the FRA. "delete obsolete" command will not remove obsolete archivelog files from the FRA, and so it only reports the obsolete archive logs without "Deleted xx objects" under them.

After a osboleted backpiece has been deleted by the command, the LIST command will not be able to see it any more. For example, I see follwoings on backup piece 635938 in the log:

Backup Set 635930 12-SEP-08
Backup Piece 635938 12-SEP-08 vcjqcgn8_1_1

deleted backup piece
backup piece handle=vcjqcgn8_1_1 recid=2021 stamp=665207528

Now, LIST on them returns errors:

RMAN> list backupset 635930;
RMAN-00571: ==================================
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571:===================================
RMAN-03002: failure of list command at 09/12/2008 15:32:05
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20215: backup set not found
RMAN-06159: error while looking up backup set

RMAN> list backuppiece 635938;
RMAN-00571: ==================================
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ===
RMAN-00571: ==================================
RMAN-03002: failure of list command at 09/12/2008 15:32:34
RMAN-06004: ORACLE error from recovery catalog database:
RMAN-20260: backup piece not found in the recovery catalog
RMAN-06092: error while looking up backup piece

You can use "report obsolete" to find the obsolete backups. If you want to find what will become obsolete in next backup run, use "report obsolete redundancy 6" if the retention redundancy is 7.

Command line to compile invalid objects

You can have a script to compile invalid 11i database objects, and put the script in cron.

1. Script

#!/bin/ksh
APPSPASS="pwd1"
SYSTEMPASS="pwd2"

. /path/to/EBSSITE_servername.env

echo ${APPSPASS}
sqlplus -s APPS @${AD_TOP}/admin/sql/adutlrcmp.pls APPLSYS ${APPSPASS} APPS ${APPSPASS} ${SYSTEMPASS} 8 0 NONE FALSE

2. Log will look like:

Enter password:
Arguments are:
AOL_schema = APPLSYS, AOL_password = *****,Schema_to_compile = APPS, Schema_to_compile_pw = *****,SYSTEM_password = *****, Total_workers = 8, Logical_worker_num = 0
Object_type_to_not_compile = NONE
Use_stored_dependencies = FALSE

Connected.
Running utl_recomp.recomp_parallel(8), if it exists
OWNER NUM_INVALID
------------------ -----------
BOLINF 3
APPS 1
Elapsed: 00:00:00.68
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
STATUS_MESSAGE
------------------------------------------------------------------------------
Running UTL_RECOMP.RECOMP_PARALLEL...
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.65
STATUS_MESSAGE
------------------------------------------------------------------------------
Successfully ran UTL_RECOMP.RECOMP_PARALLEL
no rows selected
Elapsed: 00:00:00.28
Done running utl_recomp.recomp_parallel
Commit complete.
Elapsed: 00:00:00.00

3. Cron line (like below)
0 * * * * /path/scriptname.sh > /path/to_log.log 2>&1 &

UPDATE: ${AD_TOP}/admin/sql/adutlrcmp.pls does not exist in R12.2. Use adadmin to compile APPS schema.

Find the target on a host for Grid Control

One database disappeared from Grid Control. I executed "agentca -d" on the target server to re-run the agent configuration assistant and look for new targets. It did bring the database back to Grid Control.

/u01/app/oracle/product/agent10g/bin ORA$ ./emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.1.0
OMS Version : 10.2.0.1.0
Protocol Version : 10.2.0.0.0
Agent Home : /u01/app/oracle/product/agent10g
Agent binaries : /u01/app/oracle/product/agent10g
Agent Process ID : 585838
Parent Process ID : 569446
Agent URL : http://dssqa:3872/emd/main/
Repository URL : http://dssgrid:4889/em/upload/
Started at : 2008-08-15 19:44:26
Started by user : oracle
Last Reload : 2008-08-16 01:26:09
Last successful upload : 2008-08-16 13:42:01
Total Megabytes of XML files uploaded so far : 94.10
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 25.83%
Last successful heartbeat to OMS : 2008-08-16 13:41:51
---------------------------------------------------------------
Agent is Running and Ready

/u01/app/oracle/product/agent10g/bin ORA$ ./agentca -d
Stopping the agent using /u01/app/oracle/product/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
Running agentca using /u01/app/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/product/agent10g
ACTION=Configure MODE=Perform
RESPONSE_FILE=/u01/app/oracle/product/agent10g/response_file
RERUN=TRUE
INV_PTR_LOC=/etc/oraInst.loc

Perform - mode is starting for action: Configure
Perform - mode finished for action: Configure

You can see the log file: /u01/app/oracle/product/agent10g/cfgtoollogs/oui/confi gActions2008-08-16_01-43-06-PM.log

/u01/app/oracle/product/agent10g/bin ORA$ ls -al agentca
-rwxr-xr-x 1 oracle oinstall 657 Oct 19 2007 agentca