Oracle数据库还原恢复后，执行alter database open resetlogs时遇到下面错误。如下所示： 刚开始有点懵的，第一次遇到这种情形。不过搜索了一下metalink相关资料后，大致了解到是因为环境变量设置有误导致这些错误出现的。这里先介绍一下这个案例的复杂背景： 当前机器为一个测试服务器（UAT环境），操作系统版本为HP-UX，数据库版本Oracle 19c，然后因为Support人员要查找历史数据，需要还原一个2020年时间点的数据库备份。但是生产环境（PROD）在2020年是Oracle 11g，然后在2021年升级为了Oracle 19c，现在测试服务器（UAT环境）的数据库版本也是Oracle 19c，于是从其它测试服务器拷贝了一个Oracle 11g版本的挂载带点/opt/oracle11g到当前测试服务器（为了方便省事，如果重新安装搭建Oracle 11g环境还麻烦一些），然后设置了一下/etc/oratab,如下所示：（xxx表示ORACLE_SID） 退出当前SecureCRT窗口，重新登陆，提示修改成功，如下所示 由于忙着检查/修改pfile文件中的参数，当时没有仔细检查环境变量，匆匆忙忙就开始了数据库实例的还原恢复，但是当前的ORACLE_HOME环境变量确实还是Oracle 19c的环境变量 修改环境变量~/.profile后，执行. ~/.profile设置生效。然后重新登陆SecureCRT后，重新还原数据库就不会遇到这个问题。另外，如果不重新还原，关闭数据库后，重新OPEN数据库亦可以，测试没有什么问题。 官方文档Database Startup Failure After RMAN Restore with ORA-00704, ORA-00604, ORA-00904 (Doc ID 2540757.1)的具体内容如下：SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
Process ID: 19288
Session ID: 2 Serial number: 4441#xxx:/opt/oracle19c/product/19.3.0/db_1:N
xxx:/opt/oracle11g/product/11.2:NORACLE_SID = [xxx] ? xxx
Oracle SID = xxx
Oracle BASE = /opt/oracle11g
Oracle HOME = /opt/oracle11g/product/11.2
SQL*Net TNS = /etc
ORA_NLS10 = /opt/oracle11g/product/11.2/nls/data>echo $ORACLE_BASE
/opt/oracle19c
>echo $ORACLE_HOME
/opt/oracle19c/product/19.3.0/db_1
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.
SYMPTOMS
SQL> alter database open;
alter database open
*
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: 23346
Session ID: 680 Serial number: 51933
CHANGES
After restoring the database to another server by RMAN, it is failed to startup.
After upgrade or post upgrade
CAUSE
>>>>>ORA-00904: "I"."UNUSABLEBEGINNING#": invalid identifier
This error appears due to using the incorrect sqlplus environment. For example., By default SQLPLUS will connect to the default ORACLE_HOME, say 12C Home but if you are duplicating the 11g database on same host the it must be Oracle 11g Environment in order to complete the process.
1 - This issue happens when we have installed multiple Oracle Homes in a single system.
2 - We need to make sure that we are in the right environment when we are restoring it.
SOLUTION
You need to 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
SET ORACLE_HOME=Your_Oracle_Home
CD ORACLE_HOME\bin
Even if you set all the environment parameter properly, then need to investigate further to verify the rman backup set which used to restore or verify the upgrade logs, component, dba_registry etc.
Provide the details requested in below Notes based on the post restore or post upgrade
Note 1905616.1 SRDC - Startup Issues: Checklist of Evidence to Supply (Doc ID 1905616.1)
Note 1906468.1 SRDC - Startup Shutdown - Oracle Binary and OS Resources: Checklist of Evidence to Supply (Doc ID 1906468.1)
Note:1672387.1 SRDC - Data Collection for Upgrade Issues
Note 753041.1 How to Diagnose Components with NON VALID Status in DBA_REGISTRY after an Upgrade (Doc ID 753041.1)
Note 1965956.1 SRDC - Data Collection for Datapatch issues (Doc ID 1965956.1)
Note 1671416.1 SRDC - Required diagnostic data collection for RMAN Restore and Recover Using TFA Collector (Recommended) or Manual Steps (Doc ID 1671416.1)