ORACLE--归档

今天准备做一下实验，结果启动该数据库时候报错了。

ORA-03113: 通信通道的文件结尾 进程 ID:
22038
会话 ID: 1 序列号: 5

[oracle@ORA-RAC1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期五 3月 19 22:10:00 2021

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

已连接到空闲例程。

SYS@ prod>startup
ORACLE 例程已经启动。

Total System Global Area  768294912 bytes
Fixed Size                  2257192 bytes
Variable Size             465571544 bytes
Database Buffers          297795584 bytes
Redo Buffers                2670592 bytes
数据库装载完毕。
ORA-03113: 通信通道的文件结尾 进程 ID:
22038
会话 ID: 1 序列号: 5

根据提示我找到trace文件

提示一下哈trace文件的路径在$ORACLE_BASE/diag/rdbms/prod/prod/trace/prod_ora_22038.trc

内容如下：  有用信息我标记了红色

Trace file /u01/diag/rdbms/prod/prod/trace/prod_ora_22142.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/oracle
System name:    Linux
Node name:      ORA-RAC1
Release:        3.10.0-514.el7.x86_64
Version:        #1 SMP Wed Oct 19 11:24:13 EDT 2016
Machine:        x86_64
Instance name: prod
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 22142, image: oracle@ORA-RAC1 (TNS V1-V3)

*** 2021-03-19 22:14:39.318
*** SESSION ID:(1.5) 2021-03-19 22:14:39.318
*** CLIENT ID:() 2021-03-19 22:14:39.318
*** SERVICE NAME:() 2021-03-19 22:14:39.318
*** MODULE NAME:(sqlplus@ORA-RAC1 (TNS V1-V3)) 2021-03-19 22:14:39.318
*** ACTION NAME:() 2021-03-19 22:14:39.318
 
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
ORA-19815: : db_recovery_file_dest_size  (097152 ) 00.00%,  0 ************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
*** 2021-03-19 22:14:39.381 4329 krsh.c
ARCH: Error 19809 Creating archive log file to '/u01/oradata/prod/flash_recovery_area/PROD/archivelog/2021_03_19/o1_mf_1_6_%u_.arc'
*** 2021-03-19 22:14:39.381 2917 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
DDE rules only execution for: ORA 312
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
ORA-16038:  3 sequence# 6 

                           ORA-19809: ORA-00312:  3  1: '/u01/oradata/prod/redo03.log'
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+465<-kjzdssdmp()+267<-kjzduptcctx()+232<-kjzdicrshnfy()+63<-ksuitm()+5570<-kcfopd()+5962<-adbdrv()+52169<-opiexe()+18724<-opiosq0()+3932<-kpooprx()+274<-kpoal8()+842<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570
<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265 
----- End of Abridged Call Stack Trace -----

*** 2021-03-19 22:14:39.401
USER (ospid: 22142): terminating the instance due to error 16038
ksuitm: waiting up to [5] seconds before killing DIAG(22112)

 

结果分析：

开启归档模式导致归档空间不足造成数据库异常

本例子中我采取使用rman方法清理过期的归档日志

并设置了自动清理3天前的归档日志

[oracle@ORA-RAC1 ~]$ rman target /

恢复管理器: Release 11.2.0.4.0 - Production on 星期五 3月 19 22:18:33 2021

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

已连接到目标数据库 (未启动)

RMAN> startup mount

Oracle 实例已启动
数据库已装载

系统全局区域总计     768294912 字节

Fixed Size                     2257192 字节
Variable Size                465571544 字节
Database Buffers             297795584 字节
Redo Buffers                   2670592 字节

RMAN> crosscheck archivelog all;

使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=18 设备类型=DISK
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_25_1067014507.dbf RECID=1 STAMP=1067264251
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_26_1067014507.dbf RECID=2 STAMP=1067264275
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_27_1067014507.dbf RECID=3 STAMP=1067340107
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_28_1067014507.dbf RECID=4 STAMP=1067342204
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_29_1067014507.dbf RECID=6 STAMP=1067354808
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_30_1067014507.dbf RECID=5 STAMP=1067353910
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_32_1067014507.dbf RECID=7 STAMP=1067354814
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_33_1067014507.dbf RECID=8 STAMP=1067355744
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_1_1067355744.dbf RECID=10 STAMP=1067510870
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_2_1067355744.dbf RECID=9 STAMP=1067510870
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_3_1067355744.dbf RECID=11 STAMP=1067510871
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_4_1067355744.dbf RECID=12 STAMP=1067532153
对归档日志的验证成功
归档日志文件名=/u01/oracle/dbs/arch1_5_1067355744.dbf RECID=13 STAMP=1067551232
已交叉检验的 13 对象


RMAN> delete expired archivelog all;

释放的通道: ORA_DISK_1
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=18 设备类型=DISK
说明与资料档案库中的任何归档日志都不匹配

RMAN> delete noprompt archivelog until time "sysdate -3";

释放的通道: ORA_DISK_1
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=18 设备类型=DISK
db_unique_name 为 PROD 的数据库的归档日志副本列表
=====================================================================

关键字     线程序列     S 时间下限           
------- ---- ------- - -------------------
1       1    25      A 2021-03-15 13:23:23
        名称: /u01/oracle/dbs/arch1_25_1067014507.dbf

2       1    26      A 2021-03-15 14:17:31
        名称: /u01/oracle/dbs/arch1_26_1067014507.dbf

3       1    27      A 2021-03-15 14:17:55
        名称: /u01/oracle/dbs/arch1_27_1067014507.dbf

4       1    28      A 2021-03-16 11:21:46
        名称: /u01/oracle/dbs/arch1_28_1067014507.dbf

6       1    29      A 2021-03-16 11:56:43
        名称: /u01/oracle/dbs/arch1_29_1067014507.dbf

5       1    30      A 2021-03-16 14:38:23
        名称: /u01/oracle/dbs/arch1_30_1067014507.dbf

7       1    32      A 2021-03-16 15:26:48
        名称: /u01/oracle/dbs/arch1_32_1067014507.dbf

8       1    33      A 2021-03-16 15:26:54
        名称: /u01/oracle/dbs/arch1_33_1067014507.dbf

10      1    1       A 2021-03-16 15:42:24
        名称: /u01/oracle/dbs/arch1_1_1067355744.dbf

9       1    2       A 2021-03-16 22:00:29
        名称: /u01/oracle/dbs/arch1_2_1067355744.dbf

已删除的归档日志
归档日志文件名=/u01/oracle/dbs/arch1_25_1067014507.dbf RECID=1 STAMP=1067264251
已删除的归档日志
归档日志文件名=/u01/oracle/dbs/arch1_26_1067014507.dbf RECID=2 STAMP=1067264275
已删除的归档日志
归档日志文件名=/u01/oracle/dbs/arch1_27_1067014507.dbf RECID=3 STAMP=1067340107
已删除的归档日志
归档日志文件名=/u01/oracle/dbs/arch1_28_1067014507.dbf RECID=4 STAMP=1067342204
已删除的归档日志
归档日志文件名=/u01/oracle/dbs/arch1_29_1067014507.dbf RECID=6 STAMP=1067354808
已删除的归档日志
归档日志文件名=/u01/oracle/dbs/arch1_30_1067014507.dbf RECID=5 STAMP=1067353910
已删除的归档日志
归档日志文件名=/u01/oracle/dbs/arch1_32_1067014507.dbf RECID=7 STAMP=1067354814
已删除的归档日志
归档日志文件名=/u01/oracle/dbs/arch1_33_1067014507.dbf RECID=8 STAMP=1067355744
已删除的归档日志
归档日志文件名=/u01/oracle/dbs/arch1_1_1067355744.dbf RECID=10 STAMP=1067510870
已删除的归档日志
归档日志文件名=/u01/oracle/dbs/arch1_2_1067355744.dbf RECID=9 STAMP=1067510870
10 对象已删除


RMAN> 

暂时不需要用归档，磁盘空间不够 我先关闭归档

[oracle@ORA-RAC1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期五 3月 19 22:20:35 2021

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



连接到: 
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ prod>SYS@ prod>archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     6
下一个存档日志序列   6
当前日志序列           8

SYS@ prod>alter  database noarchivelog ;

数据库已更改。

SYS@ prod>
SYS@ prod>archive log list;
数据库日志模式             非存档模式
自动存档             禁用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     6
当前日志序列           8
SYS@ prod>
SYS@ prod>
SYS@ prod>alter database open;

数据库已更改。

SYS@ prod>
SYS@ prod>
SYS@ prod>
SYS@ prod>
SYS@ prod>select instance_name,status from v$instance;

INSTANCE_NAME                                    STATUS
------------------------------------------------ ------------------------------------
prod                                             OPEN

SYS@ prod>
SYS@ prod>exit

 

 

Jrojyun

2021-03-22