Pages

Tuesday 27 March 2012


ORA-00202: control file: ''
ORA-27036: translation error, unable to expand file name
ORA-205 signalled during: ALTER DATABASE   MOUNT...


I have an issue in my development database today. Suddenly client ping me and told that he cannot able to access the database.  He got the below error.




I checked the database is in archive log mode and the archive log destination is used 100%(Disk space). This database  was created using the RMAN duplication technique. To make the database in noarchive log mode I shutdown the database and tried to up database in mount state.

SQL>startup nomount;

ORA-205 signalled during: ALTER DATABASE   MOUNT...

SQL>select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
RCKDEV1         STARTED



I checked the alert_log file of the database. In that I observed that the control_files parameter is set to nothing. i.e.

Control_files=


And I observed the command ALTER SYSTEM SET control_files='' SCOPE=SPFILE; that was done by the previous DBA. And the database is using the omf feature(ORACLE MANAGED FILES)

The database is using spfile. So, I created a pfile from the spfile:

SQL>create pfile from spfile;


Added the control­_files parameter in the pfile and started the database.


control_files=’/u02/ RCKDEV1/controlfile/o1_mf_7mpsghnh_.ctl’,’ /u01/ RCKDEV1
                           /controlfile/o1_mf_7mpsghln_.c tl’

SQL>startup mount;
SQL>alter database noarchivelog;
SQL>alter database open;
SQL>select  instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
RCKDEV1         OPEN


1 comment:

  1. Check your DB_RECOVERY_FILE_DEST_SIZE.

    select TO_CHAR(ROUND( 100*ceil(space_used / 1024 / 1024) /floor(space_limit / 1024 / 1024),0)) from v$recovery_file_dest order by name;

    If over 100% then
    ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G;
    where 100G is an example, just to get rid of error.

    Do not forget to tell your controlfile than old archives are not available anymore.
    Some RMAN Purge is needed.

    ReplyDelete