上一篇文章中說到，由於主機中檔案系統的根目錄的空間被耗完了，導致不斷產生的日誌檔案不能歸正常歸檔。
最後的結果就是，不能正常開啟資料庫。如下是嘗試開啟資料庫並作恢復的過程。

--嘗試開啟資料庫：

[oracle@susu ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 7 20:38:59 2017

Connected to an idle instance.

PROD>

PROD>startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 507513936 bytes

Database Buffers 322961408 bytes

Redo Buffers 2371584 bytes

Database mounted.

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

Process ID: 27907

Session ID: 1 Serial number: 5
#產生ORA-03113報錯。

--檢視alert日誌：
[oracle@susu trace]$ tail -1000f alert_PROD.log
... ...

tarting up:

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/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: susu.oracle.com

Release: 2.6.32-100.26.2.el5

Version: #1 SMP Tue Jan 18 20:11:49 EST 2011

Machine: x86_64

VM name: VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilePROD.ora

System parameters with non-default values:

processes = 150

resource_limit = TRUE

memory_target = 800M

control_files = "/u01/app/oracle/oradata/PROD/control01.ctl"

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

db_block_size = 8192

compatible = "11.2.0.4.0"

db_recovery_file_dest = "/u01/app/FRA"

db_recovery_file_dest_size= 4182M

undo_tablespace = "UNDOTBS1"

undo_retention = 1200

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

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

local_listener = "LISTENER"

utl_file_dir = "/home/oracle/logmnr"

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

audit_trail = "DB"

db_name = "PROD"

open_cursors = 300

diagnostic_dest = "/u01/app/oracle"

Tue Feb 07 20:40:00 2017

PMON started with pid=2, OS id=27825

Tue Feb 07 20:40:00 2017

PSP0 started with pid=3, OS id=27829

Tue Feb 07 20:40:02 2017

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

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

Tue Feb 07 20:40:02 2017

GEN0 started with pid=5, OS id=27841

Tue Feb 07 20:40:02 2017

DIAG started with pid=6, OS id=27845

Tue Feb 07 20:40:02 2017

DBRM started with pid=7, OS id=27849

Tue Feb 07 20:40:02 2017

DIA0 started with pid=8, OS id=27853

Tue Feb 07 20:40:02 2017

MMAN started with pid=9, OS id=27857

Tue Feb 07 20:40:02 2017

DBW0 started with pid=10, OS id=27861

Tue Feb 07 20:40:02 2017

LGWR started with pid=11, OS id=27865

Tue Feb 07 20:40:02 2017

CKPT started with pid=12, OS id=27869

Tue Feb 07 20:40:02 2017

SMON started with pid=13, OS id=27873

Tue Feb 07 20:40:02 2017

RECO started with pid=14, OS id=27877

Tue Feb 07 20:40:02 2017

MMON started with pid=15, OS id=27881

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

Tue Feb 07 20:40:02 2017

MMNL started with pid=16, OS id=27885

starting up 1 shared server(s) ...

ORACLE_BASE from environment = /u01/app/oracle

Tue Feb 07 20:40:02 2017

ALTER DATABASE MOUNT

Successful mount of redo thread 1, with mount id 347220898

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Tue Feb 07 20:40:08 2017

ALTER DATABASE OPEN

Beginning crash recovery of 1 threads

Started redo scan

Completed redo scan

read 0 KB redo, 0 data blocks need recovery

Started redo application at

Thread 1: logseq 106, block 97495, scn 4318187

Recovery of Online Redo Log: Thread 1 Group 2 Seq 106 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/PROD/redo02.log

Mem# 1: /u01/app/oracle/oradata/PROD/redo02b.log

Completed redo application of 0.00MB

Completed crash recovery at

Thread 1: logseq 106, block 97495, scn 4338188

0 data blocks read, 0 data blocks written, 0 redo k-bytes read

LGWR: STARTING ARCH PROCESSES

Tue Feb 07 20:40:09 2017

ARC0 started with pid=20, OS id=27911

ARC0: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

ARC0: STARTING ARCH PROCESSES

Tue Feb 07 20:40:10 2017

ARC1 started with pid=21, OS id=27915

Tue Feb 07 20:40:10 2017

ARC2 started with pid=22, OS id=27919

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

ARC2: Becoming the heartbeat ARCH

Tue Feb 07 20:40:10 2017

ARC3 started with pid=23, OS id=27923

Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_arc1_27915.trc:

ORA-19815: WARNING: db_recovery_file_dest_size of 4385144832 bytes is 100.00% used, and has 0 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.

************************************************************************
ARCH: Error 19809 Creating archive log file to '/u01/app/FRA/PROD/archivelog/2017_02_07/o1_mf_1_104_%u_.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance PROD - Archival Error
ORA-16038: log 1 sequence# 105 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01b.log'
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_27907.trc:
ORA-16038: log 3 sequence# 104 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/redo03.log'
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/redo03b.log'
USER (ospid: 27907): terminating the instance due to error 16038
... ...

--把例項開啟到mount狀態：

[oracle@susu ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 7 21:21:26 2017

Connected to an idle instance.

PROD>startup mount

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 507513936 bytes

Database Buffers 322961408 bytes

Redo Buffers 2371584 bytes

Database mounted.

PROD>

--檢視日誌組的使用與歸檔情況：

PROD>select group#,archived,status from v$log;

GROUP# ARC STATUS

---------- --- ----------------

1 NO INACTIVE

3 NO INACTIVE

2 NO CURRENT

--檢視歸檔模式：

PROD>archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 104

Next log sequence to archive 104

Current log sequence 106

--嘗試清空未歸檔日誌：

PROD>alter database clear logfile group 1;

alter database clear logfile group 1

*

ERROR at line 1:

ORA-00350: log 1 of instance PROD (thread 1) needs to be archived

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01.log'

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD/redo01b.log'

--把資料庫更換開啟到非歸檔模式：

PROD>alter database noarchivelog;

Database altered.

PROD>alter database clear logfile group 1;

Database altered.

PROD>alter database clear logfile group 3;

Database altered.

PROD>alter database open;

Database altered.
#資料庫正常開啟。但如果在生成當中的資料庫，清空未被歸檔的日誌檔案，可能丟失部分資料。

兩個日誌組未能歸檔之後恢復資料庫

相關文章