Oracle Database in Oracle Linux Cannot Start / Open

Today I mess around with the Oracle database that can not start and open. I get some error messages from various places such as alert.log, sqlplus, rman.

These errors are,

ORA-03110: end-of-file on communication channel
ORA-03135: connection lost contact
ORA-00845: MEMORY_TARGET not supported on this system
ORA-16038: log 3 sequence# 400 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/ITCAPPS/redo03.log'
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-00257: archiver error. Connect internal only, until freed.

I have do this step :
1. replace the  current spfile with the old one
2. startup with pfile
3. restore and recover database using rman

But that no help at all.

And the real solution is to enlarge the capacity of the folder where the archivelog is

ORA-01122: database file 1 failed verification check

When database startup, it send this message to the console,

ORA-01122: database file 1 failed verification check
ORA-01110: data file 1:
‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\ITCAPPS2\SYSTEM01.DBF’
ORA-01207: file is more recent than control file – old control file

To fix this, try

1. alter database backup controlfile to trace;
2. startup nomount;
3. recover database using backup controlfile;
4. alter database open;

Hope this will work ^_^

ORA-30371: column cannot define a level in more than one dimension

Cause

A column was used in the definition of a level after it had already been used to define a level in a different dimension.

Action

Reorganize dimension levels and hierarchies into a single dimension such that no column is used to define levels in different dimensions. There is no limit on the number of levels or hierarchies you can place in a dimension. A column can be used to define any number of levels provided all such levels are in the same dimension and provided no two levels contain identical sets of columns.

ORA-06550 – DBMS_SHARED_POOL

when i run DBMS_SHARED_POOL.KEEP, i got this error message

ORA-06550: line 2, column 4:
PLS-00201: identifier ‘DBMS_SHARED_POOL.KEEP’ must be declared

after i search net, the solution is to run the script dbmspool.sql located in ORACLE_HOME\RDBMS\admin as sysdba

ORA-14400: inserted partition key does not map to any partition

Cause:
An attempt was made to insert a record into, a Range or Composite Range object, with a concatenated partition key that is beyond the concatenated partition bound list of the last partition -OR- An attempt was made to insert a record into a List object with a partition key that did not match the literal values specified for any of the partitions.

Action:
Do not insert the key. Or, add a partition capable of accepting the key, Or add values matching the key to a partition specification.

ORA-01110: data file 1: ‘SYSTEM01.DBF’

The database is suddenly can not connect. I’ve tried using the command,
shutdown immediate;
startup mount;
alter database open resetlogs;

Then this error appears
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘\SYSTEM01.DBF’

Second try,
SQL> alter database recover datafile ‘\SYSTEM01.DBF’
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 302423545 generated at 01/14/2009 09:25:14 needed for thread 1
ORA-00289: suggestion : G:\ARCHIVELOGS\672825376_1_49174.ARC
ORA-00280: change 302423545 for thread 1 is in sequence #49174

Specify log: {=suggested | filename | AUTO | CANCEL}

The more I want to find the solution, the more errors occurred. So I took the decision to continue to seek solutions from the first error only (“ORA-01110: data file 1: ‘\SYSTEM01.DBF’”).

Finally, after googling everywhere, I found the command in RMAN,
RMAN TARGET USR/PW
RMAN > RESTORE DATABASE
RMAN > RECOVER DATABASE

Back to SQLPLUS, I run again alter database open, and error is gone. ^_^

ORA-08102: index key not found

I got this error message, ORA-08102: index key not found, obj# 211154, file 21, block 1194413 (2)

when i run

DELETE FROM STATEMENTS
WHERE ID = ’9646′
AND ORG_ID =’204′
AND TYPE NOT IN (‘PLA’, ‘TRK’, ‘RO’, ‘WO’, ‘CLM’);

To fix this error, i rebuild all of the index.

ORA-00600: internal error code, arguments: [kksfbc-reparse-infinite-loop]

Hey i got this error code when execute query on java form

ORA-00600: internal error code, arguments: [kksfbc-reparse-infinite-loop], [0x290EB6C18], [], [], [], [], [], []
The solution is just simply re-compile source of the view that used.

ORA-09314: sltln: error translating logical name

Do anyone know how can this error means, how to prevent it ?

This is the result if i search over internet,

ORA-09314: sltln: error translating logical name

Cause: Internal buffer may have overflowed

Action: See OSD error accompanying this message

After all, i restart oracle service to fix this. But that is not a solution before i know what the cause of that.

database not open – redo log fail

database couldn’t open because i got the error,

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: ‘E:\REDO\REDO01_01.ILG’

ORA-00312: online log 1 thread 1: ‘H:\REDO\REDO01_02.ILG’

i search some explanation about the error,

ora-00312: online log %s thread %s: ‘%s’

*Cause:  This message reports the filename for details of another message.

*Action: Other messages will accompany this message. See the

associated messages for the appropriate action to take.

ora-00313: open failed for members of log group %s of thread %s

*Cause:  The online log cannot be opened. May not be able to find file.

*Action: See accompanying errors and make log available.

so to open database, follow the step,

1)Using V$LOG, got the status of each group

In mount mode,

2) Clear the log groups which are not current,

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1

3) RECOVER DATABASE UNTIL CANCEL (This is what i missed and looking for the help)

4) ALTER DATABASE OPEN RESETLOGS;

OPEN RESETLOGS is valid whenever you make an incomplete recovery

ORA-06553: PLS-382: expression is of wrong type

CREATE OR REPLACE FUNCTION GET_BOOL
RETURN BOOLEAN
IS
BEGIN
RETURN TRUE;
END;

SELECT GET_BOOL() FROM DUAL;

ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

Cause :
There is BOOLEAN datatype is supported by PL/SQL and NOT supported by SQL
So you cannot use this function in a straight select statement.

However, this function work in another PL/SQL block

DECLARE
VBOOL BOOLEAN;
BEGIN
VBOOL := GET_BOOL();
IF( VBOOL = TRUE ) THEN
DBMS_OUTPUT.PUT_LINE( ‘TRUE’ );
END IF;
END;

So, we can change the function to return number(FALSE = 0, TRUE = 1)

CREATE OR REPLACE FUNCTION GET_BOOL
RETURN NUMBER
IS
BEGIN
IF TRUE THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

ORA-00257: archiver error. Connect internal only, until freed.

ORA-00257: archiver error. Connect internal only, until freed.
Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

———————————-

I clear some archivelog that have fill up until server harddisk has no space anymore. After clear some archivelog, then automatically the message not display anymore.

ORA-00600: internal error code, arguments: [19004], [], [], [], [], []

ORA-00600: internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string]
Cause: This is the generic internal error number for Oracle program exceptions. This indicates that a process has encountered an exceptional condition.
Action: Report as a bug – the first argument is the internal error number

—————————————————————————

ORA-00600 internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string]

Cause: This is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition. Causes of this message include:

timeouts

file corruption

failed data checks in memory

hardware, memory, or I/O errors

incorrectly restored files

The first argument is the internal message number. Other arguments are various numbers, names, and character strings. The numbers may change meanings between different versions of Oracle.

Action: Report this error to Oracle Support Services after gathering the following information:

events that led up to the error

the operations that were attempted that led to the error

the conditions of the operating system and databases at the time of the error

any unusual circumstances that occurred before receiving the ORA-00600 message

contents of any trace files generated by the error

the relevant portions of the Alter files

Note: The cause of this message may manifest itself as different errors at different times. Be aware of the history of errors that occurred before this internal error.
—————————————————————————

If you have receive this error, you could try to :

- analyze table,

analyze table mhs_suppliers delete statistics;

- rebuild table
If anybody have other solution, please leave a comment. ^_^

ORA-27101: shared memory realm does not exist

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

————————————————

Cause: Unable to locate shared memory realm
Action: Verify that the realm is accessible

Explanation:

you could be receive ORA-27101 because you tried to modify the database parameters such as SGA_MAX_SIZE size. You can try to bring back the original parameters.

C:\Documents and Settings\Administrator>sqplus /nolog

- SQL> conn / as sysdba;

- SQL> shutdown immediate;

- SQL> startup pfile=’D:\oracle\product\10.2.0\admin\wiapps\pfile\init.ora.112320071736′;

- SQL> create spfile from pfile;

- SQL> shutdown immediate;

- SQL> startup;

This logic is startup with pfile and copy oracle parameter in pfile to spfile, then startup with new spfile.