db_recovery_file_dest_size满导致数据库启动失败-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

db_recovery_file_dest_size满导致数据库启动失败

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:10 PM

filerecoverysizelead to

启动数据库时： SYS@orcl11gstartupORACLE instance started.Total System Global Area 417546240 bytesFixed Size 2213936 bytesVariable Size 327157712 bytesDatabase Buffers 83886080 bytesRedo Buffers 4288512 bytesDatabase mounted.ORA-03113: end-o

启动数据库时：

SYS@orcl11g>startup
ORACLE instance started.
Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             327157712 bytes
Database Buffers           83886080 bytes
Redo Buffers                4288512 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 1935
Session ID: 1 Serial number: 5

利用10046 事件跟踪数据库打开过程：

SYS@orcl11g>startup mount
ORACLE instance started.
Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             327157712 bytes
Database Buffers           83886080 bytes
Redo Buffers                4288512 bytes
Database mounted.
SYS@orcl11g>oradebug setmypid
Statement processed.
SYS@orcl11g>
SYS@orcl11g>
SYS@orcl11g>oradebug event 10046 trace name context forever,level 12;
Statement processed.
SYS@orcl11g>
SYS@orcl11g>
SYS@orcl11g>oradebug tracefile_name
/opt/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_1727.trc
SYS@orcl11g>alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 1727
Session ID: 1 Serial number: 5

查看trace文件：

ORA-19815: WARNING: db_recovery_file_dest_size of 4070572032 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.
************************************************************************
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 43927552 bytes disk space from 4070572032 limit
*** 2014-11-15 21:20:56.648 4132 krsh.c
ARCH: Error 19809 Creating archive log file to &#39;/opt/oracle/flash_recovery_area/ORCL11G/archivelog/2014_11_15/o1_mf_1_52_%u_.arc&#39;
*** 2014-11-15 21:20:56.648 2747 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
WAIT #1: nam=&#39;log file sequential read&#39; ela= 5 log#=0 block#=0 blocks=0 obj#=-1 tim=1416104456648722
WAIT #1: nam=&#39;Disk file operations I/O&#39; ela= 15 FileOperation=5 fileno=0 filetype=3 obj#=-1 tim=1416104456648774
WAIT #1: nam=&#39;control file sequential read&#39; ela= 14 file#=0 block#=1 blocks=1 obj#=-1 tim=1416104456648863
WAIT #1: nam=&#39;control file sequential read&#39; ela= 11 file#=0 block#=15 blocks=1 obj#=-1 tim=1416104456648969
WAIT #1: nam=&#39;control file sequential read&#39; ela= 10 file#=0 block#=17 blocks=1 obj#=-1 tim=1416104456649013
WAIT #1: nam=&#39;control file sequential read&#39; ela= 10 file#=0 block#=22 blocks=1 obj#=-1 tim=1416104456649075
WAIT #1: nam=&#39;control file parallel write&#39; ela= 4671 files=1 block#=21 requests=1 obj#=-1 tim=1416104456653795
WAIT #1: nam=&#39;control file parallel write&#39; ela= 956 files=1 block#=18 requests=1 obj#=-1 tim=1416104456654862
WAIT #1: nam=&#39;control file parallel write&#39; ela= 1817 files=1 block#=16 requests=1 obj#=-1 tim=1416104456656679
WAIT #1: nam=&#39;control file parallel write&#39; ela= 1295 files=1 block#=1 requests=1 obj#=-1 tim=1416104456658021
WAIT #1: nam=&#39;control file sequential read&#39; ela= 14 file#=0 block#=1 blocks=1 obj#=-1 tim=1416104456658082
WAIT #1: nam=&#39;control file sequential read&#39; ela= 12 file#=0 block#=32 blocks=1 obj#=-1 tim=1416104456658162
DDE: Problem Key &#39;ORA 312&#39; was flood controlled (0x1) (no incident)
ORA-00312: online log 1 thread 1: &#39;/opt/oracle/oradata/orcl11g/redo01.log&#39;
ORA-16038: log 1 sequence# 52 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: &#39;/opt/oracle/oradata/orcl11g/redo01.log&#39;
www.zbdba.com
*** 2014-11-15 21:20:56.660
USER (ospid: 2247): terminating the instance due to error 16038

原来是flash_recovery_area满了，无法进行归档。增加db_recovery_file_dest_size：

SYS@orcl11g>alter system set db_recovery_file_dest_size=8G scope=spfile;

System altered.
SYS@orcl11g>startup
ORACLE instance started.
Total System Global Area  417546240 bytes
Fixed Size                  2213936 bytes
Variable Size             327157712 bytes
Database Buffers           83886080 bytes
Redo Buffers                4288512 bytes
Database mounted.
Database opened.
SYS@orcl11g>show parameter recover
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest                string                 /opt/oracle/flash_recovery_are
                                                            a
db_recovery_file_dest_size           big integer            8G
recovery_parallelism                 integer                0

至此已经打开数据库，所以数据库启动失败，我们利用10046事件跟踪就能发现具体的原因。

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles