Forum Stats

  • 3,769,466 Users
  • 2,252,967 Discussions
  • 7,875,038 Comments

Discussions

ORA-00214

1021200
1021200 Member Posts: 125
edited Aug 12, 2013 2:00AM in General Database Discussions

Hello All,

Could anyone please help me to get my control file back. While opening the database I am getting below error.

SQL> startup

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size               2217952 bytes

Variable Size          562038816 bytes

Database Buffers       268435456 bytes

Redo Buffers             2412544 bytes

ORA-00214: control file '/u01/app/oracle/oradata/orcl/control01.ctl' version

1042 inconsistent with file '/u01/app/oracle/oradata/orcl/control03.ctl'

version 1039

I have my control file to belwo trace file.


/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4428.trc


Please guide me.



Thank You,

Michel

1021200

Best Answer

  • Alvaro
    Alvaro Member Posts: 709
    edited Aug 11, 2013 5:27PM Accepted Answer

    Hello there,

    What you did wrong was that you made a copiy of the control file while the database was open and running. Therefore, the control file was being written to as it was copied, and so the origin file does not match the copy.

    1) You copied a control file that was being written to by the database.

    2) Copy was frozen in time but DB kept open and writing to the original control file.

    3) You shut down the database and try to start it. Oracle now tells you that the two control files are not consistent with each other, which is true.

    As SB has pointed out, this is expect behaviour and it's not a problem.

    What you should do is

    1) Revert the change you made to the control_file parameter in the SPFILE. Put it pointing to the original control file, which is: '/u01/app/oracle/oradata/orcl/control01.ctl' . That should open the database again.

    2) Read documentation further to make sure you know how to multiplex control files manually.

    Regards,

    Álvaro.

    1021200
«1

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    fb767351-a660-4b18-9a9f-73bc141f6a0c wrote:
    
    Hello All,
    
    Could anyone please help me to get my control file back. While opening the database I am getting below error.
    
    SQL> startup
    ORACLE instance started.
    
    
    Total System Global Area  835104768 bytes
    Fixed Size               2217952 bytes
    Variable Size          562038816 bytes
    Database Buffers       268435456 bytes
    Redo Buffers             2412544 bytes
    ORA-00214: control file '/u01/app/oracle/oradata/orcl/control01.ctl' version
    1042 inconsistent with file '/u01/app/oracle/oradata/orcl/control03.ctl'
    version 1039
    
    I have my control file to belwo trace file.
    
    /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4428.trc
    Please guide me.

    Thank You, Michel

    when did Oracle last start without error?

    What changed since then?

    00214, 00000, "control file '%s' version %s inconsistent with file '%s' version %s"

    // *Cause: An inconsistent set of control files, datafiles/logfiles, and redo

    //         files was used.

    // *Action: Use a consistant set of control files, datafiles/logfiles, and redo

    //         log files. That is, all the files must be for the same database

    //         and from the same time period.

  • 1021200
    1021200 Member Posts: 125

    Below task I did. This is my bad:

    [[email protected] ~]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl

    [[email protected] ~]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control03.ctl

    [[email protected] ~]$ exit

    exit

    SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/oradata/orcl/control03.ctl' scope=spfile;

    System altered.

    SQL> show parameter control_files

    NAME                      TYPE   VALUE

    ------------------------------------ ----------- ------------------------------

    control_files                string      /u01/app/oracle/oradata/orcl/c

                                     ontrol01.ctl, /u01/app/oracle/

                                     flash_recovery_area/orcl/contr

                                     ol02.ctl

    SQL> shutdown immediate;

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL> startup

    ORACLE instance started.

    Total System Global Area  835104768 bytes

    Fixed Size               2217952 bytes

    Variable Size          562038816 bytes

    Database Buffers       268435456 bytes

    Redo Buffers             2412544 bytes

    ORA-00214: control file '/u01/app/oracle/oradata/orcl/control01.ctl' version

    1042 inconsistent with file '/u01/app/oracle/oradata/orcl/control03.ctl'

    version 1039

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond

    Why did you start messing with control files to start with?

  • 1021200
    1021200 Member Posts: 125

    Before Doing the above task I have taken my control file backup to trace. Please see below

    SQL> oradebug setmypid

    Statement processed.

    SQL> alter database backup controlfile to trace noresetlogs;

    Database altered.

    SQL> oradebug tracefile_name

    /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4428.trc

  • 1021200
    1021200 Member Posts: 125

    Yes Sir, this was my bad. I was trying to add one more control file. I should have changed the parameter file first than should have used OS cp command.

    Now struggling to get my control file back.

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    fb767351-a660-4b18-9a9f-73bc141f6a0c wrote:
    
    Before Doing the above task I have taken my control file backup to trace. Please see below
    
    SQL> oradebug setmypid
    Statement processed.
    SQL> alter database backup controlfile to trace noresetlogs;
    
    
    Database altered.
    
    
    SQL> oradebug tracefile_name
    /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4428.trc
    

    doing above does not change any existing control file.

    Why did you start COPYING files between locations.

    What did you actually do, that you have not posted here?

    I hope that you have a good backup for this DB.

  • 1021200
    1021200 Member Posts: 125

    SQL> oradebug setmypid

    Statement processed.

    SQL> alter database backup controlfile to trace noresetlogs;

    Database altered.

    SQL> oradebug tracefile_name

    /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4428.trc

    Above thing, I did for taken the control file backup to trace.

    After that I have tried to add one more control file but it got messed up there.

    When my database was working fine my control_files parameter was like this:

    SQL> show parameter control_files

    NAME                      TYPE   VALUE

    ------------------------------------ ----------- ------------------------------

    control_files                string      /u01/app/oracle/oradata/orcl/c

                                     ontrol01.ctl, /u01/app/oracle/

                                     flash_recovery_area/orcl/contr

                                     ol02.ctl

    after that I did the below task :

    SQL> show parameter pfile

    NAME                      TYPE   VALUE

    ------------------------------------ ----------- ------------------------------

    spfile                       string      /u01/app/oracle/product/11.2.0

                                     /db_1/dbs/spfileorcl.ora

    SQL> !

    [[email protected] ~]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl

    [[email protected] ~]$ cp /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control03.ctl

    [[email protected] ~]$ exit

    exit

    SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/oradata/orcl/control03.ctl' scope=spfile;

    System altered.

    SQL> show parameter control_files

    NAME                      TYPE   VALUE

    ------------------------------------ ----------- ------------------------------

    control_files                string      /u01/app/oracle/oradata/orcl/c

                                     ontrol01.ctl, /u01/app/oracle/

                                     flash_recovery_area/orcl/contr

                                     ol02.ctl

    SQL> shutdown immediate;

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL> startup

    ORACLE instance started.

    Total System Global Area  835104768 bytes

    Fixed Size               2217952 bytes

    Variable Size          562038816 bytes

    Database Buffers       268435456 bytes

    Redo Buffers             2412544 bytes

    ORA-00214: control file '/u01/app/oracle/oradata/orcl/control01.ctl' version

    1042 inconsistent with file '/u01/app/oracle/oradata/orcl/control03.ctl'

    version 1039

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond

    Which version, 1039 or 1042, is the correct value?

    How did you end up with two different versions of the control file?

  • Alvaro
    Alvaro Member Posts: 709
    edited Aug 11, 2013 5:27PM Accepted Answer

    Hello there,

    What you did wrong was that you made a copiy of the control file while the database was open and running. Therefore, the control file was being written to as it was copied, and so the origin file does not match the copy.

    1) You copied a control file that was being written to by the database.

    2) Copy was frozen in time but DB kept open and writing to the original control file.

    3) You shut down the database and try to start it. Oracle now tells you that the two control files are not consistent with each other, which is true.

    As SB has pointed out, this is expect behaviour and it's not a problem.

    What you should do is

    1) Revert the change you made to the control_file parameter in the SPFILE. Put it pointing to the original control file, which is: '/u01/app/oracle/oradata/orcl/control01.ctl' . That should open the database again.

    2) Read documentation further to make sure you know how to multiplex control files manually.

    Regards,

    Álvaro.

    1021200
  • 1021200
    1021200 Member Posts: 125

    1042 verison is the correct value.

This discussion has been closed.