ORA-03113: end-of-file on communication channel Serial number: 5

ORA-03113: end-of-file on communication channel  Process ID: 41880   Session ID: 762 Serial number: 5

---step1:  查看Alert 日志

1.1Alter 日志的路径查看

[oracle@ora11g ~]$ env | grep ORA

ORACLE_SID=CESDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2/db_1

[oracle@ora11g ~]$ cd $ORACLE_BASE

[oracle@ora11g oracle]$ cd diag

[oracle@ora11g diag]$ cd rdbms/cesdb/CESDB/trace/

[oracle@ora11g trace]$ tail -f alert*

ORA-16038: log 2 sequence# 2 cannot be archived

ORA-19504: failed to create file ""  ---（无法创建归档，要么归档日志满了，要么就是没有权限）

ORA-00312: online log 2 thread 1: '/oradata/CESDB/redo02.log'

Thu Nov 03 10:48:07 2022

ARC1 started with pid=21, OS id=62140

USER (ospid: 62136): terminating the instance due to error 16038

System state dump requested by (instance=1, osid=62136), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/cesdb/CESDB/trace/CESDB_diag_62005_20221103104808.trc

Dumping diagnostic data in directory=[cdmp_20221103104808], requested by (instance=1, osid=62136), summary=[abnormal instance termination].

Instance terminated by USER, pid = 62136

--step2 参考以下文章使用rman清理现在的归档日志，报错如下

​​https://blog.csdn.net/qq_41793064/article/details/108909124​​

RMAN> crosscheck archivelog all;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of crosscheck command at 11/03/2022 10:53:48

RMAN-12010: automatic channel allocation initialization failed

RMAN-06403: could not obtain a fully authorized session

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

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

可能是没有权限，查看权限, 再次查看Alert 日志

[oracle@ora11g ~]$ env | grep ORA

ORACLE_SID=CESDB

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2/db_1

[oracle@ora11g ~]$ cd $ORACLE_BASE

[oracle@ora11g oracle]$ cd diag

[oracle@ora11g diag]$ cd rdbms/cesdb/CESDB/trace/

[oracle@ora11g trace]$ tail -f alert*

ORA-16038: log 2 sequence# 2 cannot be archived

ORA-19504: failed to create file ""

ORA-00312: online log 2 thread 1: '/oradata/CESDB/redo02.log'

Thu Nov 03 10:48:07 2022

ARC1 started with pid=21, OS id=62140

USER (ospid: 62136): terminating the instance due to error 16038

System state dump requested by (instance=1, osid=62136), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/cesdb/CESDB/trace/CESDB_diag_62005_20221103104808.trc

Dumping diagnostic data in directory=[cdmp_20221103104808], requested by (instance=1, osid=62136), summary=[abnormal instance termination].

Instance terminated by USER, pid = 62136

^C

[oracle@ora11g trace]$ tail -100f alert*

  event                    = "43822 trace name context forever,level 1"

  event                    = "28401 trace name context forever, level 1"

  event                    = "10949 trace name context forever,level 1"

  large_pool_size          = 256M

  memory_target            = 1520M

  control_files            = "/oradata/CESDB/control01.ctl"

  control_files            = "/u01/app/oracle/fast_recovery_area/CESDB/control02.ctl"

  control_file_record_keep_time= 60

  db_block_size            = 8192

  compatible               = "11.2.0.4.0"

  log_archive_dest_1       = "location=/arch"

  db_files                 = 1000

  db_recovery_file_dest    = "/u01/app/oracle/fast_recovery_area"

  db_recovery_file_dest_size= 300G

  _gc_defer_time           = 0

  recovery_parallelism     = 10

  _datafile_write_errors_crash_instance= FALSE

  _cleanup_rollback_entries= 10000

  undo_tablespace          = "UNDOTBS1"

  _partition_large_extents = "FALSE"

  remote_login_passwordfile= "EXCLUSIVE"

  _sys_logon_delay         = 0

  db_domain                = ""

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=CESDBXDB)"

  session_cached_cursors   = 100

  parallel_max_servers     = 32

  _PX_use_large_pool       = TRUE

  result_cache_max_size    = 0

  audit_file_dest          = "/u01/app/oracle/admin/CESDB/adump"

  audit_trail              = "DB"

  db_name                  = "CESDB"

  open_cursors             = 500

  _optimizer_extended_cursor_sharing= "NONE"

  _optimizer_extended_cursor_sharing_rel= "NONE"

  _optimizer_adaptive_cursor_sharing= FALSE

  deferred_segment_creation= FALSE

  _optimizer_use_feedback  = FALSE

  diagnostic_dest          = "/u01/app/oracle"

  max_dump_file_size       = "1024M"

Thu Nov 03 10:48:01 2022

PMON started with pid=2, OS id=61995

Thu Nov 03 10:48:01 2022

PSP0 started with pid=3, OS id=61997

Thu Nov 03 10:48:01 2022

VKTM started with pid=4, OS id=61999 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Thu Nov 03 10:48:01 2022

GEN0 started with pid=5, OS id=62003

Thu Nov 03 10:48:01 2022

DIAG started with pid=6, OS id=62005

Thu Nov 03 10:48:01 2022

DBRM started with pid=7, OS id=62007

Thu Nov 03 10:48:01 2022

DIA0 started with pid=8, OS id=62009

Thu Nov 03 10:48:01 2022

MMAN started with pid=9, OS id=62011

Thu Nov 03 10:48:01 2022

DBW0 started with pid=10, OS id=62013

Thu Nov 03 10:48:01 2022

LGWR started with pid=11, OS id=62016

Thu Nov 03 10:48:01 2022

CKPT started with pid=12, OS id=62018

Thu Nov 03 10:48:02 2022

SMON started with pid=13, OS id=62020

Thu Nov 03 10:48:02 2022

RECO started with pid=14, OS id=62022

Thu Nov 03 10:48:02 2022

MMON started with pid=15, OS id=62024

Thu Nov 03 10:48:02 2022

MMNL started with pid=16, OS id=62026

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

Thu Nov 03 10:48:02 2022

ALTER DATABASE   MOUNT

Successful mount of redo thread 1, with mount id 4279919458

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE   MOUNT

Thu Nov 03 10:48:07 2022

ALTER DATABASE OPEN

LGWR: STARTING ARCH PROCESSES

Thu Nov 03 10:48:07 2022

ARC0 started with pid=20, OS id=62138

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Unable to create archive log file '/arch/1_2_1118362817.dbf'

ARCH: Error 19504 Creating archive log file to '/arch/1_2_1118362817.dbf'

Errors in file /u01/app/oracle/diag/rdbms/cesdb/CESDB/trace/CESDB_ora_62136.trc:

ORA-16038: log 2 sequence# 2 cannot be archived

ORA-19504: failed to create file ""

ORA-00312: online log 2 thread 1: '/oradata/CESDB/redo02.log'

Thu Nov 03 10:48:07 2022

ARC1 started with pid=21, OS id=62140

USER (ospid: 62136): terminating the instance due to error 16038

System state dump requested by (instance=1, osid=62136), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/cesdb/CESDB/trace/CESDB_diag_62005_20221103104808.trc

Dumping diagnostic data in directory=[cdmp_20221103104808], requested by (instance=1, osid=62136), summary=[abnormal instance termination].

Instance terminated by USER, pid = 62136

^C

[oracle@ora11g trace]$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Nov 3 10:51:28 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> delete archivelog until time 'sysdate -1';

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of delete command at 11/03/2022 10:53:12

RMAN-06403: could not obtain a fully authorized session

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

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

确定了archive log的位置，/archive, 然后查看权限

--step3: 查看权限

[oracle@ora11g /]$ ll

total 28

drwxr-xr-x.   2 root   root         6 Oct 17 23:23 arch

drwxr-xr-x.   2 root   root         6 Oct 17 23:23 backup

lrwxrwxrwx.   1 root   root         7 Sep 16 00:16 bin -> usr/bin

dr-xr-xr-x.   4 root   root      4096 Sep 16 00:27 boot

drwxr-xr-x   20 root   root      3320 Oct 24 18:47 dev

drwxr-xr-x. 140 root   root     12288 Nov  2 17:43 etc

drwxr-xr-x.  12 root   root       131 Nov  2 13:45 home

lrwxrwxrwx.   1 root   root         7 Sep 16 00:16 lib -> usr/lib

lrwxrwxrwx.   1 root   root         9 Sep 16 00:16 lib64 -> usr/lib64

drwxr-xr-x.   2 root   root         6 Mar 10  2016 media

drwxr-xr-x.   3 root   root        19 Nov  1 15:02 mnt

drwxr-xr-x.   4 root   root        32 Oct 18 00:06 opt

drwxrwxr-x.   3 oracle oinstall    19 Oct 18 00:19 oradata

dr-xr-xr-x  319 root   root         0 Oct 24 18:46 proc

dr-xr-x---.  16 root   root      4096 Nov  2 10:59 root

drwxr-xr-x   39 root   root      1200 Nov  3 09:09 run

lrwxrwxrwx.   1 root   root         8 Sep 16 00:16 sbin -> usr/sbin

drwxr-xr-x.   2 root   root         6 Mar 10  2016 srv

drwxrwxr-x    2 oracle oinstall    47 Oct 19 04:34 stage

dr-xr-xr-x   13 root   root         0 Oct 24 18:47 sys

drwxr-xr-x    2 root   root        33 Nov  1 16:22 test

drwxrwxrwt.  21 root   root      4096 Nov  3 10:50 tmp

drwxrwxr-x.   4 oracle oinstall    30 Oct 17 23:32 u01

drwxr-xr-x.  13 root   root       155 Sep 16 00:16 usr

drwxr-xr-x.  20 root   root       282 Sep 16 00:27 var

drwx------    5 wjx    wjx        128 Nov  2 10:47 wjx

--step4:修改权限

[root@ora11g ~]# cd /

[root@ora11g /]# chown oracle:oinstall -R arch/

[root@ora11g /]# ll

total 28

drwxr-xr-x.   2 oracle oinstall     6 Oct 17 23:23 arch

drwxr-xr-x.   2 root   root         6 Oct 17 23:23 backup

lrwxrwxrwx.   1 root   root         7 Sep 16 00:16 bin -> usr/bin

dr-xr-xr-x.   4 root   root      4096 Sep 16 00:27 boot

drwxr-xr-x   20 root   root      3320 Oct 24 18:47 dev

drwxr-xr-x. 140 root   root     12288 Nov  2 17:43 etc

drwxr-xr-x.  12 root   root       131 Nov  2 13:45 home

lrwxrwxrwx.   1 root   root         7 Sep 16 00:16 lib -> usr/lib

lrwxrwxrwx.   1 root   root         9 Sep 16 00:16 lib64 -> usr/lib64

drwxr-xr-x.   2 root   root         6 Mar 10  2016 media

drwxr-xr-x.   3 root   root        19 Nov  1 15:02 mnt

drwxr-xr-x.   4 root   root        32 Oct 18 00:06 opt

drwxrwxr-x.   3 oracle oinstall    19 Oct 18 00:19 oradata

dr-xr-xr-x  318 root   root         0 Oct 24 18:46 proc

dr-xr-x---.  16 root   root      4096 Nov  2 10:59 root

drwxr-xr-x   39 root   root      1200 Nov  3 09:09 run

lrwxrwxrwx.   1 root   root         8 Sep 16 00:16 sbin -> usr/sbin

drwxr-xr-x.   2 root   root         6 Mar 10  2016 srv

drwxrwxr-x    2 oracle oinstall    47 Oct 19 04:34 stage

dr-xr-xr-x   13 root   root         0 Oct 24 18:47 sys

drwxr-xr-x    2 root   root        33 Nov  1 16:22 test

drwxrwxrwt.  21 root   root      4096 Nov  3 10:50 tmp

drwxrwxr-x.   4 oracle oinstall    30 Oct 17 23:32 u01

drwxr-xr-x.  13 root   root       155 Sep 16 00:16 usr

drwxr-xr-x.  20 root   root       282 Sep 16 00:27 var

drwx------    5 wjx    wjx        128 Nov  2 10:47 wjx

--step5: 再次测试，可以成功启动打开数据库

[oracle@ora11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 3 10:58:48 2022

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

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area 1586708480 bytes

Fixed Size                  2253624 bytes

Variable Size            1258294472 bytes

Database Buffers          318767104 bytes

Redo Buffers                7393280 bytes

Database mounted.

Database opened.