Linux – Listener Status Blocked

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.

My procedures seem valid... I was able to test the logical DB, then bring it back into an exact copy Logical Standby and re-enable SQL Apply without issues.

However, there was a minor change to my procedures: flashback database requires that the DB is in mount state and that alter database open requires resetlogs or noresetlogs.

Therefore, the amended procedures are as follows:

PRIMARY
alter system set log_archive_dest_state_3=defer scope=both;

STANDBY
alter database stop logical standby apply;
alter database guard none;
alter database flashback on;
create restore point before_testing guarantee flashback database;

*AFTER TESTING IS DONE*

STANDBY
shutdown immediate;
startup mount;
flashback database to restore point 'before_testing';
alter database open resetlogs;
alter database guard all;
alter database start logical standby apply immediate;
drop restore point before_testing;
alter database flashback off;

PRIMARY
alter system set log_archive_dest_state_3=enable scope=both;