Table of Contents
一次由于关归档的 ‘小事’ 引发的 ‘ 事故 ’。。。。。
Home Database Mysql Tutorial 一次由于关归档的‘小事’引发的‘事故’。。。。。

一次由于关归档的‘小事’引发的‘事故’。。。。。

Jun 07, 2016 pm 03:28 PM
accident Archive

一次由于关归档的 ‘小事’ 引发的 ‘ 事故 ’。。。。。 今天在一个客户那做巡检是碰到了一个有趣的问题,差点把自己给吓死。 因客户要求关掉一个库的归档,如此easy,但是,but,出了一个问题,差点吓死。。。 模拟故障报错如下: SQL startup mount ORACL

一次由于关归档的 ‘小事’ 引发的 ‘ 事故 ’。。。。。

今天在一个客户那做巡检是碰到了一个有趣的问题,差点把自己给吓死。

因客户要求关掉一个库的归档,如此easy,但是,but,出了一个问题,差点吓死。。。

模拟故障报错如下:

SQL> startup mount
ORACLE instance started.

Total System Global Area 234344448 bytes
Fixed Size 1335696 bytes
Variable Size 142610032 bytes
Database Buffers 88080384 bytes
Redo Buffers 2318336 bytes
Database mounted.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-01143: cannot disable media recovery - file 7 needs media recovery
ORA-01110: data file 7:
'/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf'

数据库没有备份策略,之前的归档空间就10g左右,客户在db hang住是手工去删归档,由于之前在登入数据前已发现此库应用压力有点大,但是当时也没多想。于是直接一个alter system checkpoint;先切了几次。然后再shutdown immediate: 但是半天没反应。客户又在催,于是乎,手工cancel 了,接着一个shutdown abort:再startup一把,然后再shutdown immediate:这是后台 alert。log 里没有任何报错,然后接着再startup mount: 开始关归档。就出现上述报错:

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-01143: cannot disable media recovery - file 7 needs media recovery
ORA-01110: data file 7:
'/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf'

这下彻底吓坏了,应为见过在搞压力下,由于shutdown abort 后数据库器不来的case,而且,更吓人的是这个库没有任何备份,最经24的归档都刚刚给删了,这是瞬间,bbed dul。。。全跳到我脑门,but 这个屠龙之技哪敢在客户的生产上用,。。。。。。。。各种忐忑中。。。。。。

!!!!!!!注意:现在回想起来,以后打死也不随便在业务高峰期间敲shutdown 命令了。需要根据当前数据库系统实际情况,及先查明系统负载情况,在提出合理建议,说明可能存在的风险情况,让客户在业务低峰期,再做某些操作。

后来,重新检查数据文件的情况信息,搞清楚了是什么原因。

以下为整个问题的模拟再现:

当数据库中有数据文件在数据库open是 offline 时,这时再去禁用归档是不允许的。以下为测试文档:

[root@oracle11g ~]# !su
su - oracle
![oracle@oracle11g ~]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 15 04:33:23 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)

SQL> select file_name, file_id ,status from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID STATUS
---------- ---------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6k9o20_.dbf
6 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_users_814jk5bg_.dbf
4 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_undotbs1_814jk569_.dbf
3 AVAILABLE
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_sysaux_814jk53d_.dbf
2 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_system_814jk4rs_.dbf
1 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_example_814jq4jj_.dbf
5 AVAILABLE
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf
7 AVAILABLE


7 rows selected.

先将数据文件7将数据离线:
SQL> alter database datafile 7 offline;

Database altered.


SQL> select file_name, file_id ,status from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID STATUS
---------- ---------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6k9o20_.dbf
6 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_users_814jk5bg_.dbf
4 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_undotbs1_814jk569_.dbf
3 AVAILABLE
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_sysaux_814jk53d_.dbf
2 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_system_814jk4rs_.dbf
1 AVAILABLE

/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_example_814jq4jj_.dbf
5 AVAILABLE
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf
7 AVAILABLE


7 rows selected.

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
7 OFFLINE OFFLINE
4111255
13-JAN-14


SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/test
Oldest online log sequence 45
Next log sequence to archive 47
Current log sequence 47
SQL>

当前环境为归档模式,在数据库open状态下将数据文件7离线。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 234344448 bytes
Fixed Size 1335696 bytes
Variable Size 142610032 bytes
Database Buffers 88080384 bytes
Redo Buffers 2318336 bytes
Database mounted.

禁用数据库归档模式:
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-01143: cannot disable media recovery - file 7 needs media recovery
ORA-01110: data file 7:
'/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf'


对离线的数据文件进行恢复:
SQL> startup force
ORACLE instance started.

Total System Global Area 234344448 bytes
Fixed Size 1335696 bytes
Variable Size 142610032 bytes
Database Buffers 88080384 bytes
Redo Buffers 2318336 bytes
Database mounted.
Database opened.
SQL> recover datafile 7;
ORA-00279: change 4111255 generated at 01/13/2014 08:47:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/test/1_47_832034536.arc
ORA-00280: change 4111255 for thread 1 is in sequence #47


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.

这里可以看出需要一个归档日志文件来恢复数据文件7,因此,如果某个数据文件在过去某个时间点在数据库处于open模式下,且归档是开了的情况下,被offline了。如果这时没有备份,且历史归档都不完整存在的话。对于这个

数据文件只能采用非常规方式恢复了。而这时如果必须要关掉归档的话,只能drop掉相应的表空间了。


SQL> select * from v$recover_file;

no rows selected

SQL>

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 234344448 bytes
Fixed Size 1335696 bytes
Variable Size 142610032 bytes
Database Buffers 88080384 bytes
Redo Buffers 2318336 bytes
Database mounted.


SQL> alter database noarchivelog;

Database altered.

SQL> select * from v$recover_file;

no rows selected

SQL> select * from dba_data_files;
select * from dba_data_files
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> alter database open;

Database altered.

SQL> select * from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6k9o20_.dbf
6 ADMIN_TBS 104857600 12800 AVAILABLE
6 NO 0 0 0 103809024 12672
ONLINE


FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_users_814jk5bg_.dbf
4 USERS AVAILABLE
4
ONLINE


FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_undotbs1_814jk569_.dbf
3 UNDOTBS1 89128960 10880 AVAILABLE
3 YES 3.4360E+10 4194302 640 88080384 10752
ONLINE


FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_sysaux_814jk53d_.dbf
2 SYSAUX 660602880 80640 AVAILABLE
2 YES 3.4360E+10 4194302 1280 659554304 80512
ONLINE


FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_system_814jk4rs_.dbf
1 SYSTEM 786432000 96000 AVAILABLE
1 YES 3.4360E+10 4194302 1280 785383424 95872
SYSTEM


FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_example_814jq4jj_.dbf
5 EXAMPLE 104857600 12800 AVAILABLE
5 YES 3.4360E+10 4194302 80 103809024 12672
ONLINE


FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/u01/app/oracle/oradata/MYNEWDB/datafile/o1_mf_admin_tb_8y6kbmb4_.dbf
7 ADMIN_TBS2 AVAILABLE
7
OFFLINE


7 rows selected.

SQL> select * from v$recover_file;

no rows selected

SQL> alter database datafile 7 online;

Database altered.

SQL> select * from v$recover_file;

no rows selected

在shutdown abort 下,且非归档模式offline数据文件: 结论如下

SQL> shutdown abort
ORACLE instance shut down.
SQL> alter database datafile 7 offline;
alter database datafile 7 offline
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 12122
Session ID: 1 Serial number: 5


SQL> startup mount
ORACLE instance started.

Total System Global Area 234344448 bytes
Fixed Size 1335696 bytes
Variable Size 142610032 bytes
Database Buffers 88080384 bytes
Redo Buffers 2318336 bytes
Database mounted.
SQL> alter database datafile 7 offline;
alter database datafile 7 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/oracle/test
Oldest online log sequence 54
Current log sequence 56
SQL>

 
Statement of this Website
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

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to split or merge RAR files in Windows 11 How to split or merge RAR files in Windows 11 Feb 18, 2024 pm 05:48 PM

On Windows 11/10 PC, you can use various file compression/archiving software to split or merge RAR files. These software provide convenient features that can help you merge or split RAR files for better file management and organization. Not only can individual files or collections of files be combined into a compressed archive, but files and folders can also be manipulated within compressed archives. Therefore, using file archiving software allows you to manage compressed files more effectively and improve work efficiency. How to split or merge RAR files in Windows 11 To split or merge RAR files in Windows 11/10, you can use WinRAR, a popular Windows file compression/archiving software. Win

How to achieve hot and cold separation and archiving of data in MySQL? How to achieve hot and cold separation and archiving of data in MySQL? Jul 29, 2023 pm 08:18 PM

How to achieve hot and cold separation and archiving of data in MySQL? As the amount of data continues to grow and business needs change, the data in the database is also increasing. In order to improve the performance and efficiency of the database, we can manage and store data through hot and cold separation and data archiving. Hot and cold separation refers to dividing data into hot data and cold data according to the frequency of use. Hot data refers to data that is frequently accessed and queried, while cold data is rarely accessed or queried. By separating cold data from the main database, we can relieve the main

What does linux archive mean? What does linux archive mean? Mar 02, 2023 am 10:37 AM

Linux archiving is a process of aggregating many files and combining them into one large file. It is usually used as part of a system backup and is often used when old data is moved from a system to some long-term storage device. The tar command can create archives for Linux files and directories.

Common log file compression and archiving errors on Linux servers and how to fix them Common log file compression and archiving errors on Linux servers and how to fix them Jun 29, 2023 am 11:34 AM

Linux server is a widely used platform for hosting websites and providing services. While the server is running, log files are continuously logged to help administrators diagnose problems and monitor server activity. In order to maintain server performance and efficient use of storage space, log files need to be compressed and archived regularly. However, sometimes you encounter some common errors while performing these operations. This article describes several common log file compression and archiving errors and how to fix them. "PermissionDenied" error when trying to compress

Tesla FSD system test encounters accident: vehicle crashes into pond Tesla FSD system test encounters accident: vehicle crashes into pond Mar 01, 2024 pm 06:37 PM

Recently, a Tesla accident occurred near Mono, California. A Tesla Model 3 owner encountered an accident while using Tesla's assisted driving system "Full Self-Driving" (FSD). The incident attracted public attention, and the video of the accident sparked widespread discussion online. One early morning, a Tesla owner was testing the FSD system on the highway. He was confident in this system called "fully autonomous driving", but he did not expect that a yellow "flooding" sign would appear on the road not far ahead. Surprisingly, the FSD system completely ignored this sign and did not take any action to slow down or avoid it. The car owner mistakenly believed that the FSD system had fully taken over driving tasks, so he did not pay attention to the road signs.

An in-depth discussion of the definition and significance of Linux archiving An in-depth discussion of the definition and significance of Linux archiving Mar 21, 2024 am 08:21 AM

The Definition and Significance of Linux Archiving With the rapid development of information technology, data management and storage have become an indispensable part of all walks of life. In Linux systems, archiving is a very important function. It can help users organize and manage files effectively, save storage space, and improve work efficiency. This article will delve into the definition and significance of Linux archiving and provide specific code examples to help readers better understand and apply this function. 1. Definition of Linux Archiving In Linux system, archiving refers to

Learn to use Linux archiving to improve work efficiency Learn to use Linux archiving to improve work efficiency Mar 20, 2024 pm 06:12 PM

Linux system has always been the operating system of choice for programmers, system administrators and IT professionals. Its powerful functions and flexibility make it widely used in work and study. Among them, using the archiving function of Linux can help us better manage files and data and improve work efficiency. This article will introduce how to use Linux archiving tools to improve work efficiency and give specific code examples. 1. Basic introduction In Linux system, archiving refers to storing multiple files or objects

Solving Linux server log compression and archiving issues Solving Linux server log compression and archiving issues Jun 30, 2023 pm 12:28 PM

The log file of the Linux server is an important part of recording the running status of the server and various operation records. Over time, log files can grow and take up a lot of disk space. To solve this problem, compressing and archiving log files has become a common practice. This article describes some common log file compression and archiving issues and solutions. Problem 1: The log file is too large and takes up a lot of disk space. After the server has been running for a period of time, the log file will continue to grow, causing the problem of insufficient disk space. At this time, it is necessary

See all articles