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

Every now and then, one needs to clone a database using Cold Backup Method as I have already shown in one of my previous post - Duplicate Oracle Database using Cold Backup Method, a database gets duplicated quickly, within a few minutes or hours (depending upon the database size of course).

On 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.

Solution:

In my case, Oracle Database and Oracle Home version were 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,

On Windows,

set ORACLE_HOME=C:\app\oracle\product\db_home
cd %ORACLE_HOME%\bin

On Linux,

export OR1ACLE_HOME=/u01/app/oracle/product/db_home

cd $ORACLE_HOME/bin

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

I hope this helps !!