ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier in Oracle Database

ORA-01092, ORA-00704, ORA-00604, ORA-00904 at Oracle Database Startup

This is what these errors codes meant in the first place.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 2

ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier

In normal scenarios, when we clone the database using Cold Backup Method as I have already mentioned in one of my previous post regarding - Duplicate Oracle Database using Cold Backup Method, a database gets duplicated within a few minutes or hours (depending upon the database size of course).

In a random scenario, I encountered an error (ORA-00704) which took me a while to understand why actually it happened. Although it wasn't supposed to come in the first place but it did. So I'll explain here why it happened and how to avoid the same.

So if you are familiar with the Cold Backup Method, I had already created the pfile and backed up control file as trace. The Oracle Service was also created for the new environment using ORADIM utility.

Now below are some steps from the same process and at the end, I faced this error.

SQL> startup nomount pfile=G:\Database_11g\savgst17\init_savmar15_17apr17.ora

ORACLE instance started.

Total System Global Area 4294967296 bytes

Fixed Size                  5413176 bytes

Variable Size            2399143624 bytes

Database Buffers         1879048192 bytes

Redo Buffers               11362304 bytes

SQL> CREATE CONTROLFILE SET DATABASE "SAVGST17" RESETLOGS  NOARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 1153

  7  LOGFILE

  8    GROUP 1 (

  9      'G:\DATABASE_11G\SAVGST17\REDO011.LOG',

 10      'G:\DATABASE_11G\SAVGST17\REDO012.LOG'

 11    ) SIZE 50M BLOCKSIZE 512,

 12    GROUP 2 (

 13      'G:\DATABASE_11G\SAVGST17\REDO021.LOG',

 14      'G:\DATABASE_11G\SAVGST17\REDO022.LOG'

 15    ) SIZE 50M BLOCKSIZE 512,

 16    GROUP 3 (

 17      'G:\DATABASE_11G\SAVGST17\REDO031.LOG',

 18      'G:\DATABASE_11G\SAVGST17\REDO032.LOG'

 19    ) SIZE 50M BLOCKSIZE 512

 20  -- STANDBY LOGFILE

 21  DATAFILE

 22    'G:\DATABASE_11G\SAVGST17\SYSTEM01.DBF',

 23    'G:\DATABASE_11G\SAVGST17\SYSAUX01.DBF',

 24    'G:\DATABASE_11G\SAVGST17\UNDOTBS01.DBF',

 25    'G:\DATABASE_11G\SAVGST17\DATA01.DBF',

 26    'G:\DATABASE_11G\SAVGST17\INDEX01.DBF',

 27    'G:\DATABASE_11G\SAVGST17\DATA02.DBF',

 28    'G:\DATABASE_11G\SAVGST17\INDEX02.DBF',

 29    'G:\DATABASE_11G\SAVGST17\LOB01.DBF',

 30    'G:\DATABASE_11G\SAVGST17\REPORT_DATA01.DBF',

 31    'G:\DATABASE_11G\SAVGST17\REPORT_INDEX01.DBF',

 32    'G:\DATABASE_11G\SAVGST17\USERS01.DBF'

 33  CHARACTER SET AL32UTF8

 34  ;

Control file created.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-00704: bootstrap process failure

ORA-00604: error occurred at recursive SQL level 2

ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier

Process ID: 17384

Session ID: 217 Serial number: 12183

And here it is....

It got me confused for a while but didn't took long to figure it out.

#######################################################################################


ORA-00704: bootstrap process failure 

Cause: An error occurred when processing bootstrap data. Refer to the accompanying messages for more information about the cause of the problem. 

Action: Correct the problems mentioned in the other messages. If the problem persists, contact customer support. 


#######################################################################################

Reason in my case:

Oracle Database and Oracle Home version different.

What I had in my system is that I had installed multiple Oracle Homes in a single system. I was taking a cold backup of Oracle 11g Database and when I tried to duplicate it using Cold Backup Method, I was in Oracle 12c Environment. If this sounds confusing, when you have multiple Oracle Environments on a single system, make sure you are connecting via the correct Environment in order to do such operations. When I opened the SQLPLUS in my command prompt, It was by default connecting to the default ORACLE_HOME which was Oracle 12c in my case but what I was duplicating was a Oracle 11g database. So I must be in Oracle 11g Environment in order to complete my process.

To those who don't have any idea how to accomplish that,

Either you can set all the required Environment Variables first as per your need like ORACLE_SID, ORACLE_BASE, ORACLE_HOME, TNS_ADMIN, PATH with proper values    OR

Simply work by going to the BIN directory of your required ORACLE_HOME

Open Command Prompt

SET ORACLE_HOME=Your_Oracle_Home

cd ORACLE_HOME\bin

Continue with your work now as you are in the correct Environment.