ORA-12528 and ORA-12505 error on Oracle database 11g

It's been one week since I last tried to run my final project that I worked on in my last internship at some banking company.

Suddenly, my webapps (Java project) in Eclipse can't start. In the log it says that it failed to make a connection to the Oracle database, with some error listener in it. So I try to connect the database manually from the SQL developer. It says


ORA-12528: TNS:Listener: All Appropriate instances are blocking new connections.

I've read some solutions, and I tried to restart the Oracle services including the orcl and listener. After I restarted it, the error becomes


ORA-12505: TNS:listener does not currently know of SID given in connect descriptor.

After a while, it becomes ORA-12528 again. Can someone help? My thesis defense is next week.

alert_ORCL.txt log (SID=ORCL):

```
*

Fatal NI connect error 12641, connecting to:
(LOCAL=NO)

VERSION INFORMATION:
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 11.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.1.0 - Production
Time: 05-MAR-2015 21:28:24
Tracing not turned on.
Tns error struct:
ns main err code: 12641

TNS-12641: Authentication service failed to initialize
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
opiodr aborting process unknown ospid (4204) as a result of ORA-609
Thu Mar 05 21:28:26 2015

*

Fatal NI connect error 12641, connecting to:
(LOCAL=NO)

VERSION INFORMATION:
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 11.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.2.0.1.0 - Production
Time: 05-MAR-2015 21

It's signalling a 'ORA-214' during instance startup, which is really bad.

$ oerr ORA 214
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.

In other words, one of your control files might be corrupted. They are supposed to be identical.

To fix this, we'll try each control file in turn to see if the database starts OK with it.

Follow this step-by-step, to the letter. MAKE SURE YOU BACK THEM UP, AS BELOW. You have been warned.

Make a backup of BOTH control files to a location of your choice.
EG:

copy C:\APP\NICO\ORADATA\ORCL\CONTROL01.CTL C:\TMP\CONTROL01.CTL
copy C:\APP\NICO\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL C:\TMP\CONTROL02.CTL

Next, make sure the database isn't up:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 19:14:49 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown abort;
ORACLE instance shut down.
SQL>

Now we'll overwrite the first control file with the second one, then attempt to start the database:

copy C:\TMP\CONTROL02.CTL C:\APP\NICO\ORADATA\ORCL\CONTROL01.CTL

Now try and start the database:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 19:15:54 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  822579200 bytes
Fixed Size                  2257720 bytes
Variable Size             255855816 bytes
Database Buffers          557842432 bytes
Redo Buffers                6623232 bytes
Database mounted.
Database opened.
SQL>

If it says Database opened and doesn't throw a ORA- error, it's fixed.

If it throws another ORA-214 or a message about a bad control file, we need to try the other control file. To do that, make sure the database is down (as above), then copy the other control file and try the startup again:

copy C:\TMP\CONTROL01.CTL C:\APP\NICO\ORADATA\ORCL\CONTROL01.CTL
copy C:\TMP\CONTROL01.CTL C:\APP\NICO\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL

Note that this would have been easier to fix with 3 control files, as you could have md5'ed each one to find out which was the bad apple.

If the above doesn't work, you have further problems - if so, edit your question with the error message presented by SQL*Plus, and from the end of the alert log.

I cannot reinforce how important it is that you backup the two control files somewhere safe before trying the above!

It's entirely possible that there's another problem with a data file or redo log file, but we'll try this first.

StackExchange Database Administrators Q#94739, answer score: 5