Oracle 启动报错处理

Oracle 日常维护处理

错误 ORA-03113

查看错误日志

1. Oracle启动时报如下错误

   SQL> startup
   ORACLE instance started.
   
   Total System Global Area 1.0289E+10 bytes
   Fixed Size                  2262168 bytes
   Variable Size            2080377704 bytes
   Database Buffers         8187281408 bytes
   Redo Buffers               18694144 bytes
   Database mounted.
   ORA-03113: end-of-file on communication channel
   Process ID: 2993
   Session ID: 771 Serial number: 3

2. 解决方法，查看orcle启动日志，确定具体是什么原因引起的错误。

   # 进入 trace 日志存放目录
   cd $ORACLE_HOME/diag/rdbms/实例名/实例名/trace/
   # 定位日志 alert
   ls -alcr | grep alert (c时间排序、r倒序) 定位启动日志。

3. 查看日志

   tail -f -n 500 alert_ncdbemu.log
   
   # 发现错误日志如下
   Errors in file /u01/app/oracle/diag/rdbms/qdmwms/qdmwms/trace/qdmwms_m000_4068.trc:
   ORA-19815: WARNING: db_recovery_file_dest_size of 4385144832 bytes is 99.40% used, and has 26523648 remaining bytes available.
   ************************************************************************
   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.
   ************************************************************************

4. 清理归档日志文件，双实例的 oracle 启动—注意：首先要export ORACLE_SID=要操作的实例名 保证自己操作的实例是需要删除归档日志文件的实例，然后进行删除步骤

清理归档日志

1. 以 mount 方式启动Oracle

   # 以 sysdba 身份登录 Oracle
   sqlplus  / as sysdba
   
   # 以 mount 方式启动数据库
   SQL> startup mount;
   ORACLE instance started.
   
   Total System Global Area 1.0289E+10 bytes
   Fixed Size                  2262168 bytes
   Variable Size            2080377704 bytes
   Database Buffers         8187281408 bytes
   Redo Buffers               18694144 bytes
   Database mounted.

2. 查看恢复分区(闪回区)的大小及位置

   SQL> show parameter db_recovery;
   
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   db_recovery_file_dest                string      /u01/app/oracle/flash_recovery_area
   db_recovery_file_dest_size           big integer 4182M

3. 查询当前的使用状态

   SQL> select * from v$flash_recovery_area_usage;
   
   FILE_TYPE            PERCENT_SPACE_USED NUMBER_OF_FILES
   -------------------- ------------------ ---------------
   CONTROL FILE                          0               0
   REDO LOG                              0               0
   ARCHIVED LOG                       99.4             107
   BACKUP PIECE                          0               0
   IMAGE COPY                            0               0
   FLASHBACK LOG                         0               0
   FOREIGN ARCHIVED LOG                  0               0
   
   7 rows selected.

4. 物理清除归档路径下的日志文件：

   • 退出 sql 命令窗口：
   • cd /u01/flash_recovery_area/DMDEV/archivelog —进入到对应的归档实例日志目录
   • 清理不需要的归档日志 —清理前请对需要的日子做好备份潮州：
   • 删除不需要的日子文件目录
   • 物理日志文件清理后，还需要在 ramn 管理中清理一次，不然还是显示的空间没有释放；

5. 进入rman命令行，通过rman管理工具清理。

   • 创建 rman 备份文件存放目录

   mkdir /backup/rman/qdmwms/ -p
   chown -R oracle:oinstall /backup/rman

   • 连接上数据库实例：rman
   • 执行以下语句

   RMAN> run {
   backup full tag 'qdmwms' database
   include current controlfile format '/backup/rman/qdmwms/full_%d_%T_%s'
   plus archivelog format '/backup/rman/qdmwms/arch_%d_%T_%s' delete all input;
   }

   • 退出 rman，重新连接数据库并启动数据库