Interview question - RMAN

why RMAN is keeping archive log backups older than retention.
List backup shows archives older than datafile backups not expiring.
Reason: RMAN will keep the archive files, not expire them, because these older archives are needed to recover a datafile which is older than the rest of the database.
This file may either be offline or a 'begin backup' was issued on the tablespace without the 'end backup'.


Cause for Excessive growth in Archive Logs:
Reason: 1 )could be generated by the Automatic Workload Repository (AWR) and Active session History (ASH).
Solution: These advisories collect statistic data even when there's no activity in the database. They perfom inserts in to some of these v$ tables and then use the data collected to automatically tune the system.You can try to set the statistics_level = basic, this will reduce the amount of redo that is generated.
2) Check the Auditing has been enabled.
=========================================================================
RMAN Interview questions:

Scenario 1 : Backup is configured using RMAN recovery catalog database and TSM.
rcv_cat = repdb
target_database=prodcrm
control_file_record_keep_time=15
Daily the backup kicked off at 18:00 hours,
During the backup, recovery catalog database(repdb) went down due to server crash.
a) Will my backup run completed successfully or not?
b) if yes,what is the reason?
c) and if No, what is the reason?

Answer : Backup will continue. It will update all the information in the target database controlfile.
======================================================================

Session lvl
------------

Scenario : 1 what is the difference between "disconnect session" and "kill session" ?

Disconnect Session : primarily for TAF (failing over)

Killing Session : the session is dead, killed, resources release.

Scenario 2 : Yesterday, In a database, archive generation is 2 archive for 20mins and after creating the Materialized view using ONCOMMIT, my archive generation has increased to 3 per minute. What is the reason behind this?

ONCOMMIT -it refreshes automatically on the next COMMIT performed on the MASTER TABLE.

So the redo generation is generating more and more.

RMAN - keeping a backup valid for long term

rdbms : 10.2.0.4
o/s : RHEL4
Step 1 : From the sql session
SQL > SHUTDOWN IMMEDIATE
SQL > startup mount;

Step 2: From RMAN session
RMAN> BACKUP DATABASE KEEP FOREVER NOLOGS TAG 'ARCHIVE_1'
#make long-term consistent backup
# mark backup as unavailable in the repository
# so that RMAN does not attempt to restore it unless explicitly specified on the restore.
RMAN> CHANGE BACKUP TAG 'ARCHIVE_1' unavailable
RMAN>sql 'alter database open';

Note : Nocatalog mode is not supported for keep forever.
RMAN 06522 : KEEP FOREVER option is not supported without the recovery catalog

LibraryCacheNotEmptyOnClose

o/s : IBM-AIX 5.3
rdbms version :10.2.0.4

ORA-00600 [LibraryCacheNotEmptyOnClose]

Cause :
can occur during shutdown.

Workaround:
Ignore the error as it just indicates that there are
some items in the library cache when closing down
the instance. The error itself occurs AFTER the database
close and dismount stages so only affects the instance
shutdown itself. Datafiles have been closed cleanly.

As per the metalink doc :466425.1

Known Issues:

  Bug# 4483084   See Note:4483084.8
     OERI[LibraryCacheNotEmptyOnClose] on shutdown
     Fixed: 11.1.0.6

Database got crashed - Needs recovery of undotbs

OS : IBM-AIX 5.3
RDBMS : 10.2.0.4
Scenario: Server abruptly crashed
Cause: corruption of block change tracking file and undotbs of our recovery catalog database.

Workaround:

Step 1 : After starting the server the database did not came up. We found the following error in the alert log file.

ORA-00600: internal error code, arguments: [krctcr_4], [137290073], [0],
[137309719], [0], [], [], []

Step 2 : We found the metalink doc related this issue: 553009.1

Step 3 : while performing the steps given in above metalink doc .

we faced
SQL> select name,open_mode,log_mode from v$database;

NAME OPEN_MODE LOG_MODE
--------- ---------- ------------
XXXXXXX MOUNTED ARCHIVELOG


SQL> alter database disable block change tracking;

Database altered.



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01172: recovery of thread 1 stuck at block 1033 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed


Step 4 : Since we have a cold backup.
We restored the files from the coldbackup and applied all the archive logs and opened
the database.


a) Shutdown the instance (which is running in mount state).

b) restored the file from the backup dir.( XXXXXX_full_27042009_20:30:00.tar.Z)

$ uncompress XXXXXX_full_27042009_20:30:00.tar.Z
$ tar -xvf /opt/oracle/backup/XXXXXX_full_21112009_17:30:00.tar.Z /opt/oradata/dbarep/XXXXX

c) This zip files contains all controlfile, datafiles etc...

Note : check all the archive logs are in place.
SQL>startup nomount
SQL>alter database mount
SQL>set autorecovery on
SQL>recover database using backup controlfile untill time '2009-11-22:02:09:00';
SQL>ALTER DATABASE OPEN RESETLOGS;



'2009-11-22:02:09:00';