COLD RMAN backup fails: Ora-19602 even after SHUTDOWN IMMEDIATE and STARTUP MOUNT

SYMPTOMS

Symptom 1 :- Database was Shutdown Clean using Shutdown Immediate.

Attempt to do a COLD backup via RMAN in mount stage fails:

RMAN-03009: failure of backup command on c1 channel at 06/15/2006 22:21:45
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

The failure occurs despite the fact that the database has been previously shutdown with IMMEDIATE option and restarted using STARTUP MOUNT.

The database is running in NOARCHIVELOG mode.

Symptom 2:- Database was Aborted because Shutdown immediate got terminated in Initial Phase/OS got restarted.

Attempt to do a COLD backup via RMAN in mount stage fails:

RMAN-03009: failure of backup command on c1 channel at 06/15/2006 22:21:45
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode.

A Cold backup can be taken against a MOUNTED database only when the datafiles/database does need recovery and are consistent.

The database is running in NOARCHIVELOG mode.

CAUSE

There is at least one file that is in need of recovery.
A COLD backup for a database running in NOARCHIVELOG mode has to be a CONSISTENT backup as it cannot be recovered after restore.

To confirm, mount the database and run the following SQL:

SQL> select distinct checkpoint_change# from v$datafile_header;

This will return >1 row, indicating that files are at different points in time.

Run

Select * from v$recover_file ;

This give information about file which are offline and in need of Recovery.
.

SOLUTION

You will not be able to take a cold backup until the database is consistent.

Solution for symptom 1

Recover the datafile(s) or, if the redo has been lost (due to the online redologs having recycled) and the file is no longer needed the tablespace can be dropped.

1. To recover the datafile:

SQL> recover datafile n;
SQL> alter database datafile n online;

2. To drop the datafile:

SQL> alter database datafile n offline drop;
SQL> alter database open;
SQL> drop tablespace X including contents;

If you cannot recover the datafile and need the data content:

a. Use Note 223543.1 How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN
to restore the tablespace from its most recent backup to an auxiliary instance where the data can be exported

b. If you do not have a backup then your only option is to request PRM-DUL consultancy to extract the data prior to drop and recreation of the tablespace.

If you cannot recover the data by yourself, ask Parnassusdata, the professional ORACLE database recovery team for help.

Parnassusdata Software Database Recovery Team

Service Hotline: +86 13764045638

E-mail: [email protected]

Solutions for symptom 2

Select controlfile_type from v$database ;

It would show controlfile_type as Current

Run the below query to find the current redo log information.

Query1

SQL>Select b.member,a.group#,a.sequence# from v$log a ,v$logfile b where a.group#=b.group#

Check if the datafiles are in Fuzzy status by querying v$datafile_header

SQL> Select status,file#,fuzzy,checkpoint_change# from v$datafile_header;

Example :-

STATUS       FILE# FUZ CHECKPOINT_CHANGE#
——- ———- — ——————
ONLINE           1 YES            2083769
ONLINE           2 YES            2083769
ONLINE           3 YES            2083769
ONLINE           4 YES            2083769

Example :-

SQL> Select count(*),fuzzy from v$datafile_header group by fuzzy;

COUNT(*) FUZ
———- —
4 YES

YES value indicates files are fuzzy and need recovery.

If redo log files(Current/Active shown query 1) are available

SQL>Recover database ;
Re-run

SQL> Select count(*),fuzzy from v$datafile_header group by fuzzy;

COUNT(*) FUZ
———- —
4 NO

Now take the backup.