Sunday, January 2, 2011

Recovering 10g AS Infra-Tier | Corrupt controlfile

During my daily health check i found this:
F:\OracleAs10g\Infra102\opmn\bin>opmnctl status

Processes in Instance: infra102.orapps3.velox-tech.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | N/A | Down
OC4J | OC4J_SECURITY | N/A | Down
HTTP_Server | HTTP_Server | 7344 | Alive
OID | OID | 0 | NONE

Note : As the status shows the OC4J is down and in turn the Infra Tier is down as well.We will be unable to start or stop the processes using OPMNCTL command at this point.
e.g
F:\OracleAs10g\Infra102\opmn\bin>opmnctl startproc ias_component=OC4J
opmnctl: starting opmn managed processes...

opmn id=orapps3:6200
Unknown parameters for this request.

========Error Message from error.log ( IAS_ORACLE_HOME or APACHE_HOME)======
[Mon Oct 26 16:47:01 2010] [warn] [client 150.1.15.4] [ecid: 1256561221:150.1.15.4:2996:3176:331,0] MOD_OC4J_0184: Failed to find an oc4j process for destination: OC4J_SECURITY
[Mon Oct 26 16:47:01 2010] [error] [client 150.1.15.4] [ecid: 1256561221:150.1.15.4:2996:3176:331,0] MOD_OC4J_0145: There is no oc4j process (for destination: OC4J_SECURITY) available to service request.
[Mon Oct 26 16:47:01 2010] [error] [client 150.1.15.4] [ecid: 1256561221:150.1.15.4:2996:3176:331,0] MOD_OC4J_0119: Failed to get an oc4j process for destination: OC4J_SECURITY
[Mon Oct 26 16:47:01 2010] [error] [client 150.1.15.4] [ecid: 1256561221:150.1.15.4:2996:3176:331,0] MOD_OC4J_0013: Failed to call destination: OC4J_SECURITY's service() to service the request.
========Error Message from error.log ( IAS_ORACLE_HOME or APACHE_HOME)======


C:\Documents and Settings\dbadmin>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.1.0.4.2 - Production on 28-OCT-2009 12:24
:10

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.1.0.4.2 - Produ
ction
Start Date 27-OCT-2009 10:37:13
Uptime 1 days 1 hr. 46 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File F:\OracleAs10g\Infra102\network\admin\listener.ora
Listener Log File F:\OracleAs10g\Infra102\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orapps3.velox-tech.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "asdb.velox-tech.com" has 1 instance(s).
Instance "asdb", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

==============Error Messages from listener.log==================

26-OCT-2010 20:22:23 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=asdb.velox-tech.com)) * (ADDRESS=(PROTOCOL=tcp)(HOST=150.1.15.4)(PORT=3372)) * establish * asdb.velox-tech.com * 12528
TNS-12528: TNS:listener: all appropriate instances are blocking new connections
==============Error Messages from listener.log==================


PLEASE NOTE : The Status of Listener says BLOCKED.

Now we try to login to the Metadata Repository Database for the 10g Application Server.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 789000 bytes
Variable Size 228849144 bytes
Database Buffers 50331648 bytes
Redo Buffers 1048576 bytes
ORA-00214: controlfile 'F:\ORACLEAS10G\INFRA102\ORADATA\ASDB\CONTROL03.CTL'
version 36164 inconsistent with file
'F:\ORACLEAS10G\INFRA102\ORADATA\ASDB\CONTROL01.CTL' version 36139

NOTE: We find that one of the control files have been corrupt .

The control file with highest sequence number is to be chosen
e.g 'F:\ORACLEAS10G\INFRA102\ORADATA\ASDB\CONTROL03.CTL'




RECOVERY PROCEDURE

1>
Create Pfile from Spfile for editing the controlfile parameter

SQL> create pfile from spfile;

File created.

SQL> select status from v$instance;

STATUS
------------
STARTED


2>
shut down the database

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.


3>
Remove the entries from pfile of all control files except the control file that we will use to startup the database.

e.g 'F:\ORACLEAS10G\INFRA102\ORADATA\ASDB\CONTROL03.CTL'


4>
startup the database using pfile ( where the change is made )


SQL> startup pfile='F:\OracleAs10g\Infra102\database\INITasdb.ORA'
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 789000 bytes
Variable Size 228849144 bytes
Database Buffers 50331648 bytes
Redo Buffers 1048576 bytes
Database mounted.
Database opened.
SQL>


F:\OracleAs10g\Infra102\opmn\bin>opmnctl stopall
opmnctl: stopping opmn and all managed processes...

F:\OracleAs10g\Infra102\opmn\bin>opmnctl startall
opmnctl: starting opmn and all managed processes...

F:\OracleAs10g\Infra102\opmn\bin>opmnctl status

Processes in Instance: infra102.orapps3.velox-tech.com
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
DSA | DSA | N/A | Down
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | N/A | Down
OC4J | OC4J_SECURITY | 7348 | Alive
HTTP_Server | HTTP_Server | 4020 | Alive
OID | OID | 4232 | Alive


ROOT CAUSE
--------------------
Due to abnormal power shutdown in DR site:

The Controlfile was corrupt.

No comments:

Post a Comment