Wednesday, June 27, 2007

Duplicating a Database using RMAN

RMAN duplicate command can be used to create a test database by copying a production database. Production database can be online.

The following webpage describes the steps involved:

http://www.oraclenotes.com/dba/BackupRecovery/rman_duplicate.htm

However, in the step 6, it states:

STARTUP NOMOUNT the Target database.

I think here the term ‘target database’ could be misleading, it actually referred to the auxiliary instance.

In the context of RMAN and duplicate command, the terms should be understood as follows:

Target database – existing database which has RMAN backups and to be duplicated

Auxiliary database – the new database to be created.

I have difficulties to figure out how to deal with the following situation:

Target database – replicated, master definition site database

Auxiliary database – a test database, don’t want any replication stuff.

Previously I have worked out a procedure (see Tuesday, May 22, 2007 Post RMAN Refresh tasks -- Remove Replication ) , however, it seems have problems.

Monday, June 25, 2007

RMAN report shema

Just find that RMAN report schema command can be used to report a database’s structure in the past, for example:
RMAN> report schema at time '04-JUN-2007 01:00:00';
RMAN-03022: compiling command: report
Report of database schema
File K-bytes Tablespace RB segs Name
---- ---------- -------------------- ------- -------------------
1 1835008 SYSTEM YES /ora02/oradata/GENQA/genp_system01.dbf
2 1069056 TOOLS NO /ora01/oradata/GENQA/genp_tools01.dbf
3 2097152 RBS YES /ora02/oradata/GENQA/genp_rbs01.dbf
4 4194304 GEN_REP_DATA NO /ora02/oradata/GENXQA/gen_rep_data_05.dbf
5 131072 USERS NO /ora02/oradata/GENQA/genp_users01.dbf
6 491520 INDX NO /ora01/oradata/GENQA/genp_indx01.dbf
7 2097152 RBS YES /ora01/oradata/GENQA/genp_rbs02.dbf
8 4194304 RBS YES /ora02/oradata/GENXQA/genp_rbs03.dbf
9 2097152 GEN_DATA NO /ora01/oradata/GENXQA/gen_data_01.dbf
10 2097152 GEN_DATA NO /ora02/oradata/GENQA/gen_data_02.dbf
11 2097152 GEN_DATA NO /ora01/oradata/GENXQA/gen_data_03.dbf
12 2097152 GEN_DATA NO /ora01/orabkup/GENQA/oradata/gen_data_04.dbf
13 1024000 GEN_REP_DATA NO /ora01/oradata/GENQA/gen_rep_data_01.dbf
14 1024000 GEN_REP_DATA NO /ora02/oradata/GENQA/gen_rep_data_02.dbf
15 1024000 GEN_REP_DATA NO /ora01/oradata/GENQA/gen_rep_data_03.dbf
16 1048576 GEN_REP_DATA NO /ora02/oradata/GENQA/gen_rep_data_04.dbf
17 2097152 GEN_INDX NO /ora01/oradata/GENQA/gen_indx_01.dbf
18 1441792 GEN_INDX NO /ora02/oradata/GENQA/gen_indx_02.dbf
19 1966080 GEN_INDX NO /ora01/oradata/GENQA/gen_indx_03.dbf
20 1966080 GEN_INDX NO /ora02/oradata/GENQA/gen_indx_04.dbf
21 2097152 GEN_DATA NO /ora01/oradata/GENXQA/gen_data_05.dbf
22 2097152 GEN_DATA NO /ora02/oradata/GENXQA/gen_data_06.dbf
23 29696 AUDIT_DATA NO /ora02/oradata/GENXQA/GENP_audit_rdbs01.dbf
24 41984 REPOMAN_DATA NO /ora01/oradata/GENQA/genp_repoman_data_01.dbf
25 9216 REPOMAN_INDX NO /ora02/oradata/GENQA/genp_repoman_indx_01.dbf
26 1843200 GEN_DATA NO /ora01/oradata/GENXQA/gen_data_07.dbf
27 1536000 GEN_DATA NO /ora02/oradata/GENQA/gen_data_08.dbf
28 1843200 GEN_DATA NO /ora01/oradata/GENXQA/gen_data_09.dbf
29 2097152 GEN_DATA NO /ora02/oradata/GENQA/gen_data_10.dbf
30 1048576 GEN_DATA NO /ora01/oradata/GENQA/gen_data_11.dbf
31 2097152 GEN_DATA NO /ora01/oradata/GENQA/gen_data_12.dbf
32 917504 GEN_INDX NO /ora01/oradata/GENQA/gen_indx_05.dbf
33 917504 GEN_INDX NO /ora02/oradata/GENQA/gen_indx_06.dbf
34 1843200 GEN_INDX NO /ora01/oradata/GENQA/gen_indx_07.dbf
35 1048576 GEN_INDX NO /ora02/oradata/GENQA/gen_indx_08.dbf
36 32768 FOGLIGHT NO /ora02/oradata/GENXQA/foglight_01.dbf
37 2097152 GEN_DATA NO /ora01/orabkup/GENQA/oradata/gen_data_14.dbf
38 2097152 GEN_INDX NO /ora02/oradata/GENXQA/gen_indx_09.dbf
39 819200 GEN_AUDIT_DATA NO /ora01/oradata/GENXQA/GENP_audit_data.dbf
40 524288 GEN_AUDIT_NDX NO /ora01/oradata/GENXQA/GENP_audit_ndx.dbf
41 2097152 GEN_DATA NO /ora01/oradata/GENXQA/gen_data_15.dbf
42 2097152 GEN_DATA NO /ora01/oradata/GENXQA/gen_data_16.dbf
43 2998272 CONV_DATA NO /ora01/oradata/GENXQA/conv_data_01.dbf
44 1048576 CONV_INDX NO /ora01/oradata/GENXQA/conv_indx_01.dbf
45 2097152 GEN_INDX NO /ora01/orabkup/GENQA/oradata/gen_indx_10.dbf
46 2097152 SYSTEM YES /ora01/orabkup/GENQA/oradata/genp_system02.dbf
47 1048576 CONV_DATA NO /ora02/oradata/GENXQA/conv_data_02.dbf
RMAN>

Thursday, June 21, 2007

OCP Oracle 10g Admin II - Ch3 - Recovering From Non-Critical Losses

Chapter 3 - Recovering From Non-Critical Losses

Review Questions

1. Which of the following statements is true about non-critical losses?

A. Non-critical losses require media recovery.

B. Non-critical losses have a severe impact on database operations.

C. Non-critical losses can be resolved quickly with little impact to

database operations.

D. Non-critical losses require the use of the RECOVER command.

---

Non-critical losses have little impact on database operations if resolved properly.

Ans: C.

2. Which of the following statements is true about temporary tablespaces?

A. Temporary tablespaces most often contain some permanent objects.

B. Temporary tablespaces are responsible for storing temporary or sort

statements.

C. Temporary tablespaces must be recovered with the RECOVER command.

D. Temporary tablespaces cannot be managed locally.

----

Temporary tablespaces are responsible for storing temporary or sort segments.

These are used in the sorting of select statements or in building indexes.

Ans: B.

3. Why is a missing tempfile considered a non-critical recovery situation?

A. The tempfile is dictionary managed and can only contain some

permanent objects.

B. The tempfile is locally managed and can only contain some temporary

objects.

C. The tempfile is locally managed and can only contain temporary

objects.

D. The tempfile is dictionary managed and can only contain temporary

objects.

-----------

C

4. How can you resolve a missing temporary tablespace quickly? (Choose all that

apply.)

A. Recover the tablespace immediately because restoring is not needed.

B. Restore the tablespace from disk and not from tape.

C. Run CREATE TEMPORARY TABLESPACE and then ALTER DATABASE to

the new temporary tablespace.

D. If a temporary tablespace exists, then run ALTER DATABASE to the

existing temporary tablespace.

--

Ans C.

A missing tempfile can be re-created with the create tablespace command.

Because the tempfile is locally managed and contains no permanent data, no restoring

or recovering is needed. Therefore recovery can occur quickly with minimal impact

to database operations.

5. What must be done to recover a missing redo log file member?

A. First perform a ALTER DATABASE DROP LOGFILE MEMBER filename

and then ALTER DATABASE ADD LOGFILE MEMBER filename on the

missing logfile member.

B. Perform ALTER DATABASE ADD LOGFILE MEMBER filename on the

missing logfile.

C. Nothing is required if you have multiplexed redo logs.

D. Nothing is required if you do not have multiplexed redo logs.

-------

The missing redo log must first be dropped even though it doesn't exist physically

in the file system. This removes the redo log metadata from the data dictionary. Next

the log can be added back to database.

Ans: A.

6. How would you know if you have lost a mirrored redo log member?

A. The database would hang.

B. The archive process would stop working.

C. The alert log would display an error, and the database would hang.

D. The alert log would display an error, and the database would process

the archive logs.

-------

If your database has mirrored redo logs and a member is deleted, the database will

function as normal. The error signaling that a log member has been deleted would be

written to the alert log.

Ans: D

7. What happens if the current or active online redo log group has a new member

added?

A. Nothing, the redo log member will be added.

B. The redo log member will not be added because the log group is

actively recording transactions.

C. The redo log member will be added, but it will be out of sync until a

log switch occurs.

D. The redo log member will be added, but it will be empty.

----

The redo log member will not be added to the current or active redo log group.

Oracle will not allow this because transactions are actively being written to the redo

log group.

Ans: B.

8. What happens when you are recovering a temporary tablespace by switching to

another available tablespace? (Choose all that apply.)

A. The new temporary tablespace is made available if the tablespace is

permanent.

B. The new temporary tablespace is made available if the tablespace is

temporary.

C. You will receive an ORA-12904 error if the available tablespace is

temporary.

D. You will receive an ORA-12904 error if the available tablespace is

permanent.

-----

You must use the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE table

name command on a temporary tablespace or you will receive an ORA-12904 error.

Ans: B. D.

9. How can you rebuild the objects in the index tablespace most efficiently?

A. Recover the index from tape.

B. Rebuild the index with LOGGING.

C. Rebuild the index with NOLOGGING.

D. Rebuild the index in parallel with NOLOGGING.

----

Rebuilding an index in parallel with NOLOGGING is the most efficient method of

building an index to minimize the impact on database operations. You must be

cautious not to use extensive server resources when you don't use the parallel rebuild

option.

Ans: D.

10. What should be updated and readily accessible in case of a non-critical loss of

a tablespace?

A. Temporary segments

B. SELECT statements using sorting

C. Current index scripts

D. Create table scripts

----

The index scripts must be current and stored locally so they can be accessed

quickly and easily during an index tablespace rebuild.

Ans: C.

11. Which type of tablespace contains static data?

A. Read-only tablespace

B. Index tablespace

C. Read-write tablespace

D. Temporary tablespace

-----

The read-only tablespace contains only static or non-changing data.

Ans: A.

12. What is the result of starting an Oracle database with a missing read-only

tablespace? (Choose two.)

A. The database opens normally.

B. The database only mounts.

C. An ORA-01157 cannot identify datafile halts the database

from opening error occurs.

D. The database functions normally, and the read-only tablespace is static.

---

The database will stop at the mount stage. This is due to the ORA-01157 error.

Ans: B, C

13. When connecting to an Oracle database locally to perform administrative

functions, you are connecting with what access privilege?

A. SQL*Net

B. IPC

C. SYSDBA

D. SYSOPER

---

Ans: B. IPC is the method that the local administrative access uses to connect to the

database.

(??)

14. What is the proper way of creating a password supporting up to 10 users?

A. orapwd file=orapwORA101T password=syspass users=10

B. orapwd file=orapwORA101T password=syspass entry=10

C. orapwd file=orapwORA101T password=syspass entries=10

D. orapass file=orapwORA101T password=syspass entries=10

---

The correct command to create a password file that supports 10 users is orapwd

file=orapwORA101T password=syspass entries=10.

Ans: C.

15. Which initialization parameter is required for remote access to the database?

A. REMOTE_LOGIN_PASSWORDFILE

B. BREMOTE_LOGIN_PASSWORD_FILE

C. REMOTE_PASSWORD_FILE

D. REMOTE_LOGIN_FILE

---

The initialization parameter required for remote access is

REMOTE_LOGIN_PASSWORDFILE.

Ans: A.

16. Which directory should the password file be stored in to function properly?

(Choose all that apply.)

A. $ORACLE_HOME

B. $ORACLE_HOME/dbs

C. C:\$ORACLE_HOME\database

D. $ORACLE_SID

---

The $ORACLE_HOME/dbs is the Unix location for the password file, and

C:\$ORACLE_ HOME\database is the Windows location for the password file.

Ans: B, C.

17. Before running the ORAPWD utility to generate a password file, what should

be done?

A. Start the database but make sure it isn't open.

B. Start up the database.

C. Start up the database in MOUNT mode.

D. Shut down the database.

-----

The database should be shut down before running the ORAPWD utility.

Ans: D.

18. What do local connections to an Oracle database rely on for security and

authentication of the user?

A. Password file

B. Database password

C. Operating system password

D. Listener password

---

Local connections are secured by the operating system password logging on to an

administrative account such as Oracle user in Unix and administrator in Windows.

Ans: C.

19. The main reason that you need to restore and use the RECOVER command on a

tablespace that was backed up read-write and converted to read-only is due to

what? (Choose all that apply.)

A. The checkpointing process has changed the control file.

B. There have been changes to the tablespace.

C. The tablespace file header has been changed.

D. Read-only tablespaces require the RECOVER command.

---

Ans: A, B, C. The scenario of read-write to read-only tablespace requires the use of the

RECOVER command, which will apply necessary redo changes to make the tablespace

consistent with the control file. The checkpointing operation of Oracle will change

the control file, and the header of the read-only tablespace will be modified, which

equates to changes in the tablespace.

20. Which situation is considered a non-critical loss to the database? (Choose all

that apply.)

A. Loss of redo log group before archived

B. Loss of current or active redo member

C. Loss of archive log

D. Loss of current or active redo group

--

Ans: B, C.

A non-critical loss should have limited impact on database operations. This

means that incomplete recovery or media failure scenarios need to be performed. A

loss of a redo group before archived will require incomplete recovery, which is the

same as the loss of current or active redo log group. The current or active member or

loss of archive will not significantly impact operations. A backup can be performed

to eliminate the need for the archive log, and the database will function normally with

the loss of a redo log member.

Using RMAN DUPLICATE DATABASE: Examples

Using RMAN DUPLICATE DATABASE: Examples

 

 

The following link is the Oracle 10gR2 official document about this:

 

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb006.htm

Triggers creation errors during imp

User found that some triggers are missing in a database just imp from production database exp dump. I looked at the imp log, identified the problem, and sent the following message:

 

======================

During imp, the public synonyms have been dropped. So dev triggers without qualified names to gen tables did not get created. I think it is probably better to re-create all triggers after refresh.

 

For example:

(a) Without qualified table name -- failed

IMP-00017: following statement failed with ORACLE error 942:

 "CREATE TRIGGER "DEV".ft_fee"

 " AFTER"

 "  INSERT OR DELETE OR UPDATE"

 " ON ft_fee"

 "REFERENCING NEW AS NEW OLD AS OLD"

 " FOR EACH ROW"

 ""

 "DECLARE"

 "    ctable_name                    CONSTANT ht_history_log.table_name%TYPE "

 ":= 'FT_FEE';"

 "    ceffective_based_on_date_code  CONSTANT ht_history_log.column_name%TYPE"

 " := 'EFFECTIVE_BASED_ON_DATE_CODE';"

 "    ccharge_based_on_date_code     CONSTANT ht_history_log.column_name%TYPE"

 " := 'CHARGE_BASED_ON_DATE_CODE';"

 "BEGIN"

 "    IF DBMS_REPUTIL.from_remote THEN"

 

IMP-00003: ORACLE error 942 encountered

ORA-00942: table or view does not exist

 

(b) With qualified name -- OK

 

IMP-00041: Warning: object created with compilation warnings

 "CREATE TRIGGER "DEV".ft_fee_effective_period"

 " AFTER"

 "  INSERT OR DELETE OR UPDATE"

 " ON gen.ft_fee_effective_period"

 "REFERENCING NEW AS NEW OLD AS OLD"

 " FOR EACH ROW"

 

=================================

 

Sunday, June 17, 2007

lk[SID] files

Tonight while waiting for the imp to finish, I am wondering what those lk<sid> files at $ORACLE_HOME/dbs are used for? As usual Google is my solution.

-rw-rw---- 1 oracle dba 24 Jun 7 03:43 lkGENARCHP

-rw-rw---- 1 oracle dba 24 May 26 23:14 lkGENP

-rw-rw---- 1 oracle dba 24 May 4 12:28 lkGENQA

-rw-rw---- 1 oracle dba 24 Jan 18 2004 lkNBDWSP

-rw-rw---- 1 oracle dba 24 Apr 9 15:04 lkTESTDB1

From : http://www.freelists.org/archives/oracle-l/09-2004/msg00372.html

It is a lock file.
When a database starts, one of the background processes locks the file 
(creating it first if necessary). An attempt to start up the database again 
will fail because the old background process holds the lock,
preventing the new background process from getting it.
 
Garry Gillies

Thursday, June 14, 2007

Oracle 10g OCP Admin II - Ch2 Using Recovery Manager

Oracle 10g OCP Admin II - Ch2 Using Recovery Manager

Review Questions
----------------
1. How is block change tracking enabled?
A. With ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
B. With ALTER SYSTEM ENABLE BLOCK CHANGE TRACKING
C. With a INIT.ORA parameter change
D. With a SPFILE parameter change
---
Block change tracking must be enabled with ALTER DATABASE ENABLE BLOCK
CHANGE TRACKING. The physical location and name of the block change
tracking file
must be supplied.
Ans: A.

2. What type of backup is stored in a proprietary RMAN format?
A. Backup set
B. Image copy
C. Backup section
D. Backup group
---
Ans: A.
The backup set is stored in a proprietary RMAN format, where only used
blocks
are backed up.

3. What method is used to improve backup performance when creating
backup
sets?

A. Multicopy
B. Multiplecopy
C. Multiplex
D. Multiply
---------
Multiplexing a backup is designed to improve the performance of the
backup sets
by copying multiple database files at the same time. Multiplexing can be
used with
image copies or backup sets.
Ans C.

4. Which command creates an image copy?
A. BACKUP AS COPY

Wednesday, June 13, 2007

Replication and LOB

ISSUE
=====
During conversion test on MYDBQA/QB the SYSTEM tablespace got extended to ~ 5G. Try to understand the reason.


RESEARCH
=========

1. issue following query against MYDBP and MYDBQA

col owner format a14
col segment_name format a27
col table_name format a18
col column_name format a20


select a.owner, a.segment_name, a.bytes/1024/1024 "size (M)",
b.table_name, b.column_name
from dba_segments a, dba_lobs b
where a.tablespace_name='SYSTEM'
and a.segment_type='LOBSEGMENT'
and a.segment_name=b.segment_name
order by 3


system@MYDBQA> /

OWNER SEGMENT_NAME size (M) TABLE_NAME
COLUMN_NAME
--------------- --------------------------- ----------
------------------ --------------------
SYS SYS_LOB0000000270C00002$$ .0234375 KOTTD$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000274C00002$$ .0234375 KOTTB$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000278C00002$$ .0234375 KOTAD$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000282C00002$$ .0234375 KOTMD$
SYS_NC_ROWINFO$
SYSTEM SYS_LOB0000002394C00012$$ .0625 AQ$_QUEUES
SUBSCRIBERS
SYSTEM SYS_LOB0000002590C00002$$ .0625 DEF$_TEMP$LOB
TEMP$CLOB
SYSTEM SYS_LOB0000002590C00003$$ .0625 DEF$_TEMP$LOB
TEMP$NCLOB
SYSTEM SYS_LOB0000002590C00001$$ .0625 DEF$_TEMP$LOB
TEMP$BLOB
SYSTEM SYS_LOB0000002581C00005$$ .0625 DEF$_LOB
NCLOB_COL
SYSTEM SYS_LOB0000002581C00004$$ 2.3125 DEF$_LOB
CLOB_COL
SYSTEM SYS_LOB0000002581C00003$$ 3241 DEF$_LOB
BLOB_COL


system@MYDBP> /

OWNER SEGMENT_NAME size (M) TABLE_NAME
COLUMN_NAME
---------- --------------------------- ---------- ------------------
--------------------
SYS SYS_LOB0000000270C00002$$ .0234375 KOTTD$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000274C00002$$ .0234375 KOTTB$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000278C00002$$ .0234375 KOTAD$
SYS_NC_ROWINFO$
SYS SYS_LOB0000000282C00002$$ .0234375 KOTMD$
SYS_NC_ROWINFO$
SYSTEM SYS_LOB0000002394C00012$$ .0625 AQ$_QUEUES
SUBSCRIBERS
SYSTEM SYS_LOB0000002590C00002$$ .0625 DEF$_TEMP$LOB
TEMP$CLOB
SYSTEM SYS_LOB0000002590C00003$$ .0625 DEF$_TEMP$LOB
TEMP$NCLOB
SYSTEM SYS_LOB0000002590C00001$$ .0625 DEF$_TEMP$LOB
TEMP$BLOB
SYSTEM SYS_LOB0000002581C00005$$ .0625 DEF$_LOB
NCLOB_COL
SYSTEM SYS_LOB0000002581C00004$$ 2.3125 DEF$_LOB
CLOB_COL
SYSTEM SYS_LOB0000002581C00003$$ 640.625 DEF$_LOB
BLOB_COL


Note: the last LOGSEGMENT has size of 3.2G in MYDBQA versus 640 M in MYDBP


CONCLUSIONS
===========
It can be inferred that there are significant DMLs on LOB type columns
during conversion

From Oracle Doc:
=================
( http://www.csee.umbc.edu/help/oracle8/server.815/a67791/ch9.htm#1420 )


DEFLOB View of Storage for RPC

Oracle stores internal LOB parameters to deferred RPCs in a side table that is referenced only by way of a synonym. This gives the you flexibility for storage parameters and the containing schema. The following shows the default storage table for LOB parameters.

CREATE TABLE system.def$_lob(
id RAW(16) CONSTRAINT def$_lob_primary PRIMARY KEY,
deferred_tran_db VARCHAR2(128), -- origin db
deferred_tran_id VARCHAR2(22), -- transaction id
blob_col BLOB,
clob_col CLOB
nclob_col NCLOB)
/
-- make deletes fast
CREATE INDEX system.def$_lob_n1 ON system.def$_lob(
deferred_tran_db,
deferred_tran_id)
/
-- use a synonym in case underlying table is moved
CREATE SYNONYM sys.def$_lob FOR system.def$_lob
/
CREATE OR REPLACE VIEW DefLOB AS SELECT * FROM sys.def$_lob
/
CREATE PUBLIC SYNONYM DefLOB FOR DefLOB
/

Tuesday, June 12, 2007

Oracle 10 RMAN new feature -- Block change tracking

Block change tracking
======================

1. Enable block change tracking

sqlplus /nolog
connect / as sysdba
alter database enable block change tracking using file
'/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/toy10g_block_track
.log';

2. To verify the status of block change tracking, use this SELECT
command:
SQL> select filename, status, bytes from v$block_change_tracking;

system@TOY10G> select filename, status, bytes from
v$block_change_tracking;

FILENAME
------------------------------------------------------------------------
----------------------------
STATUS BYTES
---------- ----------
/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/toy10g_block_track.
log
ENABLED 11599872

3. To disable block change tracking, enter this command:
SQL> alter database disable block change tracking;

system@TOY10G> select filename, status, bytes from
v$block_change_tracking;

FILENAME
------------------------------------------------------------------------
----------------------------
STATUS BYTES
---------- ----------

DISABLED

Friday, June 01, 2007

RMAN -- delete expired and crosscheck notes

1. Issue: delete expired backup; -- has errors
================================================

Note: mush allocate channel first

allocate channel for maintenance type disk;
delete expired backup;
release channel;

RMAN> delete expired backup;

RMAN-03022: compiling command: delete expired
RMAN-03023: executing command: delete expired

RMAN-03022: compiling command: delete expired
RMAN-03026: error recovery releasing channel resources
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: delete expired
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242:
specification does not match any archivelog in the recovery catalog


2. delete expired backup of datafile xx is ok
==============================================

delete expired backup of datafile 1;
delete expired backup of datafile 2;
delete expired backup of datafile 3;
delete expired backup of datafile 4;
delete expired backup of datafile 5;
delete expired backup of datafile 6;
delete expired backup of datafile 7;
delete expired backup of datafile 8;
delete expired backup of datafile 9;
delete expired backup of datafile 10;
delete expired backup of datafile 11;
delete expired backup of datafile 12;
delete expired backup of datafile 13;
delete expired backup of datafile 14;
delete expired backup of datafile 15;
delete expired backup of datafile 16;
delete expired backup of datafile 17;
delete expired backup of datafile 18;
delete expired backup of datafile 19;
delete expired backup of datafile 20;
delete expired backup of datafile 21;
delete expired backup of datafile 22;
delete expired backup of datafile 23;
delete expired backup of datafile 24;
delete expired backup of datafile 25;
delete expired backup of datafile 26;
delete expired backup of datafile 27;
delete expired backup of datafile 28;
delete expired backup of datafile 29;
delete expired backup of datafile 30;
delete expired backup of datafile 31;
delete expired backup of datafile 32;
delete expired backup of datafile 33;
delete expired backup of datafile 34;
delete expired backup of datafile 35;
delete expired backup of datafile 36;
delete expired backup of datafile 37;
delete expired backup of datafile 38;
===


3. tablespace level --this one is ok too
=======================

delete expired backup of tablespace gen_data;
delete expired backup of tablespace system;
delete expired backup of tablespace audit_data;
delete expired backup of tablespace gen_indx;
delete expired backup of tablespace indx;
delete expired backup of tablespace rbs;
delete expired backup of tablespace tools
delete expired backup of tablespace users;
delete expired backup of tablespace gen_rep_data;
delete expired backup of tablespace fog_light;


File K-bytes Tablespace RB segs Name
---- ---------- -------------------- ------- -------------------
1 583680 SYSTEM YES
/ora02/oradata/GEN3S/gen_system01.dbf
2 29696 AUDIT_DATA NO
/ora02/oradata/GEN3S/gen_audit_rdbs01.dbf
3 2097152 GEN_DATA NO
/ora01/oradata/GEN3S/gen_data_01.dbf
4 2097152 GEN_DATA NO
/ora02/oradata/GEN3S/gen_data_02.dbf
5 2097152 GEN_DATA NO
/ora01/oradata/GEN3S/gen_data_03.dbf
6 2097152 GEN_DATA NO
/ora02/oradata/GEN3S/gen_data_04.dbf
7 2097152 GEN_DATA NO
/ora01/oradata/GEN3S/gen_data_05.dbf
8 2097152 GEN_DATA NO
/ora02/oradata/GEN3S/gen_data_06.dbf
9 2096136 GEN_DATA NO
/ora01/oradata/GEN3S/gen_data_07.dbf
10 2097152 GEN_DATA NO
/ora02/oradata/GEN3S/gen_data_08.dbf
11 2074728 GEN_DATA NO
/ora01/oradata/GEN3S/gen_data_09.dbf
12 3211264 GEN_DATA NO
/ora02/oradata/GEN3S/gen_data_10.dbf
13 3211264 GEN_DATA NO
/ora01/oradata/GEN3S/gen_data_11.dbf
14 3211264 GEN_DATA NO
/ora01/oradata/GEN3S/gen_data_12.dbf
15 1835008 GEN_INDX NO
/ora01/oradata/GEN3S/gen_indx_01.dbf
16 1835008 GEN_INDX NO
/ora02/oradata/GEN3S/gen_indx_02.dbf
17 1703936 GEN_INDX NO
/ora01/oradata/GEN3S/gen_indx_03.dbf
18 1835008 GEN_INDX NO
/ora02/oradata/GEN3S/gen_indx_04.dbf
19 1048576 GEN_INDX NO
/ora01/oradata/GEN3S/gen_indx_05.dbf
20 655360 GEN_INDX NO
/ora02/oradata/GEN3S/gen_indx_06.dbf
21 2228224 GEN_INDX NO
/ora01/oradata/GEN3S/gen_indx_07.dbf
22 2232320 GEN_INDX NO
/ora02/oradata/GEN3S/gen_indx_08.dbf
23 65536 INDX NO
/ora01/oradata/GEN3S/gen_indx01.dbf
24 2097152 RBS YES
/ora02/oradata/GEN3S/gen_rbs01.dbf
25 2097152 RBS YES
/ora01/oradata/GEN3S/gen_rbs02.dbf
28 872448 TOOLS NO
/ora01/oradata/GEN3S/gen_tools01.dbf
29 131072 USERS NO
/ora02/oradata/GEN3S/gen_users01.dbf
30 469504 GEN_REP_DATA NO
/ora01/oradata/GEN3S/gen_rep_data_01.dbf
31 468736 GEN_REP_DATA NO
/ora02/oradata/GEN3S/gen_rep_data_02.dbf
32 465152 GEN_REP_DATA NO
/ora01/oradata/GEN3S/gen_rep_data_03.dbf
33 466944 GEN_REP_DATA NO
/ora02/oradata/GEN3S/gen_rep_data_04.dbf
34 32768 FOGLIGHT NO
/ora02/oradata/GEN3S/foglight_01.dbf


4. crosscheck backup has problem too
========================================

RMAN> crosscheck backup;

RMAN-03022: compiling command: XCHECK
RMAN-03026: error recovery releasing channel resources
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: XCHECK
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242:
specification does not match any archivelog in the recovery catalog


5. crosscheck backup of tablespace system;

RMAN> crosscheck backup of tablespace system;

RMAN-03022: compiling command: XCHECK
RMAN-03023: executing command: XCHECK
RMAN-08074: crosschecked backup piece: found to be 'EXPIRED'
RMAN-08517: backup piece
handle=/oracle/admin/GEN3S/backup/May11bkup_cold_GEN3S_1_14.dbf recid=12
stamp=622309903
RMAN-08074: crosschecked backup piece: found to be 'EXPIRED'
RMAN-08517: backup piece
handle=/oracle/admin/GEN3S/backup/May15bkup_cold_GEN3S_1_25.dbf recid=23
stamp=622644884
RMAN-08074: crosschecked backup piece: found to be 'EXPIRED'
RMAN-08517: backup piece
handle=/oracle/admin/GEN3S/backup/May17bkup_cold_GEN3S_1_27.dbf recid=23
stamp=622832261
RMAN-08074: crosschecked backup piece: found to be 'AVAILABLE'
RMAN-08517: backup piece
handle=/oracle/admin/GEN3S/backup/May21Task38_cold_GEN3S_1_38.dbf
recid=34 stamp=623150216
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete

crosscheck backup of tablespace SYSTEM ;
crosscheck backup of tablespace AUDIT_DATA ;
crosscheck backup of tablespace GEN_DATA ;
crosscheck backup of tablespace GEN_INDX ;
crosscheck backup of tablespace INDX ;
crosscheck backup of tablespace RBS ;
crosscheck backup of tablespace TOOLS ;
crosscheck backup of tablespace USERS ;
crosscheck backup of tablespace GEN_REP_DATA ;
crosscheck backup of tablespace FOGLIGHT ;

delete expired backup of tablespace SYSTEM ;
delete expired backup of tablespace AUDIT_DATA ;
delete expired backup of tablespace GEN_DATA ;
delete expired backup of tablespace GEN_INDX ;
delete expired backup of tablespace INDX ;
delete expired backup of tablespace RBS ;
delete expired backup of tablespace TOOLS ;
delete expired backup of tablespace USERS ;
delete expired backup of tablespace GEN_REP_DATA ;
delete expired backup of tablespace FOGLIGHT ;