0x-5-ORA-03113-ORA-01081-记一次删归档改spfile-开实例-CSDN博客

本文链接：https://blog.csdn.net/sharpwt/article/details/148649616

真是大大的回旋镖，小伙伴们还记得上一期为了测试23ai flashback log palcement，于是给11g和19c都给检查了db_recovery_file_dest的设置和大小，当时示例归档是默认已开启的状态。

直接给一个instance 19c的19.3单实例的主机给设置了db_recovery_file_dest大小.

于是发生了重启示例失败，重新检查参数和修改归档内容，重复自己写的步骤，大大的回旋。

一、重启示例（测试业务反馈卡）

shutdown immediate
--发现等待了很久很久都没有关闭，
--现场以为是Oracle Linux 8.10至少一年没重启了，有事务卡住了
--直接在终端reboot

二、实例开启失败

[root@test19 ~]# su - oracle
Last login: Fri Jun 13 18:09:33 CST 2025 on pts/1
[oracle@test19:/home/oracle]# lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 13-JUN-2025 19:05:02

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test19)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
[oracle@test19:/home/oracle]# sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 13 19:05:09 2025
Version 19.3.0.0.0

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

Connected to an idle instance.

SYS@test19> startup
ORACLE instance started.

Total System Global Area 8.5899E+10 bytes
Fixed Size                 30381736 bytes
Variable Size            1.0737E+10 bytes
Database Buffers         7.4893E+10 bytes
Redo Buffers              238051328 bytes
????????
ORA-03113: end-of-file on communication channel
?? ID: 3660
?? ID: 551 ???: 33456


SYS@test19> show pdbs;
ERROR:
ORA-03114: not connected to ORACLE


SP2-1545: ???????????
SYS@test19> exit

三、尝试startup mount ，alter database open

[oracle@test19:/home/oracle]# sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 13 19:05:56 2025
Version 19.3.0.0.0

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

Connected to an idle instance.

SYS@test19> startup mount;
ORACLE instance started.

Total System Global Area 8.5899E+10 bytes
Fixed Size                 30381736 bytes
Variable Size            1.0737E+10 bytes
Database Buffers         7.4893E+10 bytes
Redo Buffers              238051328 bytes
????????

SYS@test19> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDBRS6                         MOUNTED
SYS@test19> startup
ORA-01081: ????????? ORACLE - ??????


SYS@test19> alter database open;
alter database open
*
? 1 ?????:
ORA-03113: end-of-file on communication channel
?? ID: 24468
?? ID: 490 ???: 9507


SYS@test19> show pdbs;
ERROR:
ORA-03114: not connected to ORACLE


SP2-1545: ???????????
SYS@test19> exit

四、检查parameter和archive归档

[oracle@test19:/home/oracle]# sqlplus / as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 14 09:12:00 2025
Version 19.3.0.0.0

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

Connected to an idle instance.

SYS@test19> startup mount;
ORACLE instance started.

Total System Global Area 8.5899E+10 bytes
Fixed Size                 30381736 bytes
Variable Size            1.0737E+10 bytes
Database Buffers         7.4893E+10 bytes
Redo Buffers              238051328 bytes
????????
SYS@test19> show parameter db_recovery;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/recovery_area
db_recovery_file_dest_size           big integer 50G

五、修改parameter 后

删除7天前

delete archivelog all completed before 'sysdate-7';

Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/u01/app/oracle/recovery_area/TEST19/archivelog/2024_09_11/o1_mf_1_793_mg38m90v_.arc RECID=1 STAMP=1179439209
deleted archived log
archived log file name=/u01/app/oracle/recovery_area/TEST19/archivelog/2024_09_12/o1_mf_1_794_mg3n39cn_.arc RECID=2 STAMP=1179450985
deleted archived log
archived log file name=/u01/app/oracle/recovery_area/TEST19/archivelog/2024_09_12/o1_mf_1_795_mg4xbr3w_.arc RECID=3 STAMP=1179493208
……………………
archived log file name=/u01/app/oracle/recovery_area/TEST19/archivelog/2025_05_25/o1_mf_1_1280_n3653m50_.arc RECID=488 STAMP=1202072435
deleted archived log
archived log file name=/u01/app/oracle/recovery_area/TEST19/archivelog/2025_05_26/o1_mf_1_1281_n3717xh8_.arc RECID=489 STAMP=1202101245
Deleted 489 objects


RMAN> exit


Recovery Manager complete.

六、再次检查归档文件db_recovery_file_dest空间占用

[root@test19 recovery_area]# du -sh *
51G     TEST19
[root@test19 recovery_area]#
--删除后
[root@test19 recovery_area]# du -sh *
224M    TEST19
[root@test19 recovery_area]#

检查parameter参数

SYS@test19> startup mount
ORACLE ???????
Total System Global Area 8.5899E+10 bytes
Fixed Size                 30381736 bytes
Variable Size            1.0737E+10 bytes
Database Buffers         7.4893E+10 bytes
Redo Buffers              238051328 bytes
????????
SYS@test19> alter database open;
???????
SYS@test19> select INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS,ACTIVE_STATE from v$instance;
INSTANCE_NAME    HOST_NAME                             STARTUP_TI STATUS       ACTIVE_ST
test19           test19                                                           14-6? -25  OPEN         NORMAL
SYS@test19> SHOW PARAMETER DB_RECOVERY;
NAME                                 TYPE                   VALUE
db_recovery_file_dest                string      /u01/app/oracle/recovery_area
db_recovery_file_dest_size           big integer 100G

七、解决终端？乱码


[oracle@test19:/home/oracle]# vi .bash_profile

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_SID=test19
export ORACLE_UNQNAME=test19
export ORACLE_HOSTNAME=test19
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.3.0/dbhome_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export LANG=zh_CN.UTF-8
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
umask 022
alias sas='sqlplus / as sysdba'
export PS1="[`whoami`@`hostname`:\$PWD]# "
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias lsnrctl='rlwrap lsnrctl'
alias asmcmd='rlwrap asmcmd'
alias adrci='rlwrap adrci'

重新检测中文显示

[oracle@test19:/home/oracle]# source .bash_profile
[oracle@test19:/home/oracle]# sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on 星期六 6月 14 10:22:59 2025
Version 19.3.0.0.0

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


连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SYS@test19> select INSTANCE_NAME,HOST_NAME DATABASE_STATUS,ACTIVE_STATE, from v$instance;
select INSTANCE_NAME,HOST_NAME DATABASE_STATUS,ACTIVE_STATE, from v$instance
                                                             *
第 1 行出现错误:
ORA-00936: 缺失表达式


SYS@test19>