Oracle单实例数据库启动ORA-01078、ORA-00205、ORA-01157、ORA-01110处理

 

数据库启动报错，显示参数文件无法打开，应该是参数文件损坏

[oracle@qyy ~]$ dba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 15 10:22:49 2021

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora'

SQL> exit

Disconnected

 

 

查找备份的参数文件

[oracle@qyy ~]$ find $ORACLE_BASE -name "init.ora*"

/u01/app/oracle/product/11.2.0/db_1/dbs/init.ora

/u01/app/oracle/product/11.2.0/db_1/srvm/admin/init.ora

/u01/app/oracle/admin/orcl/pfile/init.ora.3282021175947

 

将动态参数文件复制到参数文件目录下替代原有的参数文件        

[oracle@qyy ~]$ cp /u01/app/oracle/admin/orcl/pfile/init.ora.3282021175947 /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora

 

 

数据库实例启动了，但还是报错

[oracle@qyy ~]$ dba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 15 10:25:20 2021

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 1653518336 bytes

Fixed Size     2253784 bytes

Variable Size  1023413288 bytes

Database Buffers   620756992 bytes

Redo Buffers     7094272 bytes

ORA-00205: error in identifying control file, check alert log for more info

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 

查看告警日志，以下只截取了部分日志，日志显示在数据库mount阶段，无法打开控制文件control02.ctl

[oracle@qyy ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

[oracle@qyy trace]$ tail -100 alert_orcl.log

ALTER DATABASE   MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/orcl/control02.ctl'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE   MOUNT...

Sat May 15 10:25:28 2021

Checker run found 1 new persistent data failures

 

 

 

进入相关目录，查看控制文件，发现控制文件名变成了control01.ctl

[oracle@qyy trace]$ cd /u01/app/oracle/fast_recovery_area/orcl/

[oracle@qyy orcl]$ ll

total 9520

-rw-r-----. 1 oracle oinstall 9748480 May 14 17:06 control01.ctl

 

复制控制文件命名为control02.ctl

[oracle@qyy orcl]$ cp control01.ctl control02.ctl

 

 

 

将数据库mount成功，open的时候报错，显示数据文件example01.dbf无法识别

[oracle@qyy orcl]$ dba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 15 10:27:40 2021

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter database mount;

 

Database altered.

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/example01.dbf'

 

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 

 

查看数据文件example01.dbf状态，发现名称被更改

[oracle@qyy orcl]$ cd /u01/app/oracle/oradata/orcl/

[oracle@qyy orcl]$ ll

total 2179108

-rw-r-----. 1 oracle oinstall   9748480 May 15 10:28 control01.ctl

-rw-r-----. 1 oracle oinstall 363077632 May 14 17:06 exampleo1.dbf

-rw-r-----. 1 oracle oinstall  52429312 May 14 17:06 red002.log0

-rw-r-----. 1 oracle oinstall  52429312 May 13 15:17 redo01.log

-rw-r-----. 1 oracle oinstall  52429312 May 13 15:17 redo03.log

-rw-r-----. 1 oracle oinstall 608182272 May 14 17:06 sysaux01.dbf

-rw-r-----. 1 oracle oinstall 796925952 May 14 17:06 system01.dbf

-rw-r-----. 1 oracle oinstall  30416896 May 14 15:06 temp01.dbf

-rw-r-----. 1 oracle oinstall 288366592 May 14 17:06 undotbs01.dbf

-rw-r-----. 1 oracle oinstall   5251072 May 14 17:06 users01.dbf

 

 

将exampleo1.dbf复制一份，命名为example01.dbf

[oracle@qyy orcl]$ cp exampleo1.dbf example01.dbf

 

 

 

open数据库时还是报错

[oracle@qyy orcl]$ dba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 15 10:32:26 2021

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 14872

Session ID: 191 Serial number: 5

 

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 

查询告警日志，以下只截取了部分日志，发现open数据库时报错第二组日志打开失败

[oracle@qyy orcl]$cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace/

[oracle@qyy trace]$ tail -200 alert_orcl.log

alter database open

Sat May 15 10:32:33 2021

LGWR: STARTING ARCH PROCESSES

Sat May 15 10:32:33 2021

ARC0 started with pid=20, OS id=14880

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_13664.trc:

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

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_lgwr_13664.trc:

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

ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

 

 

 

查看日志文件redo02.log状态，发现名称被更改

[oracle@qyy trace]$ cd /u01/app/oracle/oradata/orcl/

[oracle@qyy orcl]$ ll

total 2585104

-rw-r-----. 1 oracle oinstall   9748480 May 15 11:04 control01.ctl

-rw-r-----. 1 oracle oinstall 363077632 May 15 10:39 example01.dbf

-rw-r-----. 1 oracle oinstall 363077632 May 14 17:06 exampleo1.dbf

-rw-r-----. 1 oracle oinstall  52429312 May 14 17:06 red002.log0

-rw-r-----. 1 oracle oinstall  52429312 May 15 10:34 redo01.log

-rw-r-----. 1 oracle oinstall  52429312 May 15 11:04 redo03.log

-rw-r-----. 1 oracle oinstall 608182272 May 15 11:03 sysaux01.dbf

-rw-r-----. 1 oracle oinstall 796925952 May 15 11:03 system01.dbf

-rw-r-----. 1 oracle oinstall  30416896 May 15 10:34 temp01.dbf

-rw-r-----. 1 oracle oinstall 288366592 May 15 11:02 undotbs01.dbf

-rw-r-----. 1 oracle oinstall   5251072 May 15 10:39 users01.dbf

 

 

将red002.log0复制一份，命名为redo02.log

[oracle@qyy orcl]$ cp red002.log0 redo02.log

 

 

将数据库open过程还是报错，后来将数据库shutdown abort再启动就正常了。

[oracle@qyy orcl]$ dba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 15 10:33:26 2021

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 0

Session ID: 0 Serial number: 0

 

 

SQL> shutdown immediate;

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

SQL> shutdown abort

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area 1653518336 bytes

Fixed Size     2253784 bytes

Variable Size  1023413288 bytes

Database Buffers   620756992 bytes

Redo Buffers     7094272 bytes

Database mounted.

Database opened.

 

 

 

以上方式通过两次操作均可启动数据库。