Datafile offline/online issue - Data Guard issue after failover/switchover
At a customer site (Oracle 10.2.0.3, AIX, CIO enabled), the following error occurred during some "datafile move" actions on the database . Note that this post only presents the solution on a real-life problem without knowing exactly if the issue was related to CIO, flashback or any Oracle or AIX “feature” :
- Database was previously in a Data Guard (DG) configuration
- Flashback is activated for the database
The performed actions were:
1. offline tablespace, test if the file can be copied (with a simple “cp” command). File was still locked (may be due to CIO).
2. online tablespace, an error occurs :
SQL> alter tablespace CONTRAINTESG2 online;
alter tablespace CONTRAINTESG2 online
*
ERROR at line 1:
ORA-01113: file 22 needs media recovery
ORA-01110: data file 22: '/export/oradata/G2DBR1/data01/contraintesg2_db_01.dbf'
Of course we directly tried to recover the datafile :
SQL> recover datafile 22;
ORA-00283: recovery session canceled due to errors
ORA-38727: FLASHBACK DATABASE requires a current control file.
No way ! Impossible to online the datafile. We also observed that the database datafiles aren't backed up since a while (we checked in the RMAN backup log file). However, the actual control file knows the datafiles (visible with "alter database backup controlfile to trace"). The controlfile is also in a correct state (CURRENT) :
SQL> select controlfile_type from v$database;
CONTROL
-------
CURRENT
It seems that during the “recover datafile”, Oracle tries to perform a flashback of the database and complains because it feels it is a BACKUP controlfile. The control file is however really a CURRENT controlfile. This problem is described in the note 438437.1. No restore point has been created :
SQL> select * from v$restore_point;
no rows selected
What we had to do to solve the problem ? :
1. Remove the flashback settings for the database.
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback off;
SQL> alter database open;
SQL> r
1* select file#,status from v$datafile
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
...
21 ONLINE
22 RECOVER
23 RECOVER
24 ONLINE
24 rows selected.
2. Try to recover the datafile, an ORA-600 appeared :
SQL> recover datafile 22;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [kccfhb_1], [4], [], [], [], [], [], []
3. Creation of a new controlfile with NORESETLOGS (statement coming from "alter database backup controlfile to trace")):
SQL> @/export/soft/oracle/admin/G2DBR1/udump/cr_ctl.sql
Control file created.
SYS> recover database;
SYS> alter database open;
All files are online :
SYS> select file#,status from v$datafile;
FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
...
22 ONLINE
23 ONLINE
24 ONLINE
24 rows selected.
The last step is of course to create the temporary tablespaces 
Last update 14.01.2008 :
After some additionnal tests with the customer we observed that the following scenery makes a Data Guard configuration in a very dangerous situation:
On a Data Guard configuration, perform a failover, then a switchover to get the initial situation (after reinstate, of course, from the old primary).
Afterwards, perform backups with a RMAN catalog
Add new datafiles on the database
These last added datafiles won't be backed up by RMAN anymore !
Only a backup without catalog will consider and save these datafiles.
Performing a RESYNC catalog doesn't solve the problem, the catalog doesn't know these new datafiles.
The only solution is to re-create the control file ! A SR has been opened by the customer.