Discussions
Categories
- 382.6K All Categories
- 2.1K Data
- 212 Big Data Appliance
- 1.9K Data Science
- 448.4K Databases
- 221.1K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 541 MySQL Community Space
- 469 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 499 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.3K SQL & PL/SQL
- 21.1K SQL Developer
- 293.8K Development
- 9 Developer Projects
- 130 Programming Languages
- 290.5K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.6K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 151 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 146 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 200 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 292 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 168 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 234 Portuguese
ORA-00214
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
Best 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.
Answers
-
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, Michelwhen 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.
-
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
-
Why did you start messing with control files to start with?
-
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
-
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.
-
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.
-
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
-
Which version, 1039 or 1042, is the correct value?
How did you end up with two different versions of the control file?
-
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.
-
1042 verison is the correct value.