Author | JiekeXu
Source | Public account JiekeXu DBA road (ID: JiekeXu_IT)
If you need to reprint, please contact us for authorization | (Personal WeChat ID: JiekeXu_DBA)
Hello everyone, I am JiekeXu. I am very happy to meet you again. Today I will let you know if it is possible to directly rm the dbf data file and restart the database in Oracle archive mode. Is there any way to save it? Welcome to click on the blue words “JiekeXu DBA Road” above to follow my public account, mark it with a star or pin it to the top, and more useful information will arrive as soon as possible!
Is it possible to directly rm the data.dbf data file and restart the database in Oracle archive mode? Why there is such a problem can be traced back to last week when a friend consulted. He had an Oracle stand-alone environment because the table space was insufficient and the table space needed to be expanded. He used the command to add the table space data file. A data file has been added. After successful execution, you can continue to write data.
After a while, he found that the path of the data file he added was wrong. He added it to the root file system instead of the directory where the data is stored uniformly. He thought this was the data file he had just added. When he found that the path was wrong, he directly used something like rm data. The dbf command was deleted, which caused the database to crash [PS: I don’t know why this database crashed. Logically speaking, deleting a data file from the operating system will not cause the Oracle database to crash]. He must not have read the case I wrote before | RAC adding table space and mistakenly placing data files locally, otherwise such a problem would not have occurred.
1. Simulation issues
Because it is not something I have personally experienced and I do not have all the materials, here I use my Oracle stand-alone 19.12 version test environment to simulate the general process.
Simulate business table spaces and business users, and data may be written.
--Simulate business table space
create tablespace jiekexu_data datafile '/u01/app/oracle/oradata/TESTOGG/jiekexu_data01.dbf' size 10m;
--The jiekexu user has been created in the users table space in advance, and it is modified to the new table space JIEKEXU_DATA here.
alter user jiekexu quota unlimited on jiekexu_data;
alter user jiekexu default tablespace jiekexu_data;
--Connect to the new user to view the table and simulate data writing to the new table
connjiekexu/jiekexu;
select count(*) from JiekeXu.T1;
COUNT(*)
----------
12
create table t2 as select * from t1;
The following simulates the operation of expanding the table space due to insufficient table space and possibly the operation of writing business data.
select file_id,file_name,bytes/1024/1024/1024 Gb from dba_data_files where tablespace_name='JIEKEXU_DATA';
alter tablespace jiekexu_data add datafile '/u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf' size 1m;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
commit;
select count(*) from t2;
COUNT(*)
----------
12288
Next, I found that the path was wrong, and the operating system layer directly deleted the newly added data files. I used mv instead here to develop good habits and ensure data security. He directly used the rm -rf jiekexu_data02.dbf command to delete the file, and then found that it was down when he logged in to the database.
$ mv /u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf /u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf_bak $ sqlplus/as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 23 17:13:26 2023 Version 19.12.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to an idle instance. 17:13:27 SYS@test>
What I simulate here is that data may still be written after deleting the file, because under normal circumstances the Oracle instance will not go down.
--Enter the database again to insert data connjiekexu/jiekexu; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; insert into t2 select * from t2; commit;
But when executing the above insertion, I got an error and could not open the deleted file. It seems that the data was already written to the data file when inserting data, but the OS layer was deleted. I could not find the error directly. Here comes the file. The first two inserts were successfully inserted, but the next three inserts failed. This is in line with expectations. Let’s read on.
14:25:25 SYS@testogg> conn jiekexu/jiekexu;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;
Connected.
14:25:27 JIEKEXU@testogg>
12288 rows created.
Elapsed: 00:00:00.03
14:25:27 JIEKEXU@testogg>
24576 rows created.
14:25:27 JIEKEXU@testogg> insert into t2 select * from t2
*
ERROR at line 1:
ORA-01116: error in opening database file 31
ORA-01110: data file 31: '/u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
14:25:27 JIEKEXU@testogg> insert into t2 select * from t2
*
ERROR at line 1:
ORA-01116: error in opening database file 31
ORA-01110: data file 31: '/u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
14:25:27 JIEKEXU@testogg> insert into t2 select * from t2
*
ERROR at line 1:
ORA-01116: error in opening database file 31
ORA-01110: data file 31: '/u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
14:25:27 JIEKEXU@testoggcommit;
14:25:34 2
14:25:37 JIEKEXU@testogg> select count(*) from t2;
COUNT(*)
----------
49152
14:26:01 JIEKEXU@testogg> commit;
Commit complete.
Looking at the table space usage, it was only 19.32%. However, the above insert operation reported an error and the newly added data file could not be found.
14:26:18 SYS@testogg> SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g", 14:27:39 2 round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%" 14:27:39 3 FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE 14:27:39 4 GROUP BY tablespace_name ) a, 14:27:39 5 (SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES 14:27:39 6 GROUP BY tablespace_name) b 14:27:39 7 WHERE a.tablespace_name=b.tablespace_name and b.tablespace_name='JIEKEXU_DATA'; TABLESPACE_NAME Total g Free g USED% ------------------------------- ---------- ---------- ---------- JIEKEXU_DATA 0 0 19.32
Looking at the alert log, it was found that the file could not be found, and it was prompted that file No. 31 could not be found. But in his library, he not only saw that the data files could not be found, but also the database was directly down.
2023-10-20T14:25:27.476828 + 08:00 Errors in file /u01/app/oracle/diag/rdbms/testogg/testogg/trace/testogg_mz00_14146.trc: ORA-01110: data file 31: '/u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf' ORA-01565: error in identifying file '/u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 Checker run found 1 new persistent data failures 2023-10-20T14:26:55.856636 + 08:00 Control autobackup written to DISK device handle '/u01/app/oracle/flash_recovery_area/TESTOGG/autobackup/2023_10_20/o1_mf_s_1150727215_lm47fhqr_.bkp'
Close the database
At this time, let’s simulate closing the database manually.
Normally shu immediate cannot close the database. Here we can only use shutdown abort to close the database.
2. Solving problems
mount database offline data files
At this time, we start the database to mount, then offline the data file first and then open the database.
Note: There is no difference between alter database datafile file_name’ offline and offline drop in archive mode, because after offline, you need to recover before you can go online.
alter database datafile 'file_name' offline; or alter database datafile 'file_name' offline drop;
The offline drop command does not delete the data file, but only changes the status of the data file to recover. The offline drop command is equivalent to putting a data file offline and needs to be restored, rather than deleting the data file. Relevant information about the data files will also be stored in the data dictionary and control files.
The background alarm log contains the following information
user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. 2023-10-20T15:01:45.036618 + 08:00 Errors in file /u01/app/oracle/diag/rdbms/testogg/testogg/trace/testogg_mz01_18870.trc: ORA-01110: data file 31: '/u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf' ORA-01565: error in identifying file '/u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7
***Delete table space data files***
So how to delete the data files in the table space? Note: Use this command with caution in production environment!!!
alter tablesapce 'tablespace_name' drop datafile 'datafile_name';
This statement will delete the file on the disk and update the information in the control file and data dictionary. The serial number of the original data file after deletion can be reused.
Note that this statement can only be used when datafile online is used. If the corresponding data file is already offline for drop, it is only available for dictionary managed tablespaces.
If any of the following conditions is violated, the datafile cannot be dropped:
1) It must be empty, otherwise it will report: ORA-03262: the file is non-empty. It is worth noting that non-empty means that extent is assigned to the table, not whether there are rows in the table. At this time, if you use drop table xxx, it will not work. You must use drop table xxx purge; or if you have already used In the case of drop table xxx, use purge table “the name of table xxx in the recycle bin” to purge the table. Otherwise, the space will not be released and the datafile will still not be dropped.
2) It cannot be the first data file of the table space it belongs to.
The above two can be achieved by drop tablespace.
3) Cannot be in a read-only table space. —-After testing, the read-only table space is OK
4) It cannot be offline, otherwise it will report: ORA-03264: cannot drop offline datafile of locally managed tablespace
5) System tablespace Cannot be a datafile that is part of the system tablespace, even if it is not the first datafile of the system tablespace —-This article comes from How to Drop a Datafile From a Tablespace (Document ID 111316.1)
Next, we have opened the database, but data file No. 31 is still offline. If new data is written to this data file, it may be lost if the archive is not opened and there is no backup. Fortunately, the database is in archive mode and archive logs are retained for two or three days. You can recover the data files from the archive logs and then bring them online to return to normal.
Syntax for rebuilding data files
alter database create datafile '/u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf' [as '/data/oradata/jiekexu_data02.dbf'];
This syntax is usually used when the data file is damaged, but there is no backup (data file), but there is a complete archive log, and the data file can be restored through the archive log! That is to say, a new data file is generated, which can be the same as the original path, or it can be different. The usage scenario is generally when the disk where the data file is located is damaged and cannot be restored to the original file (or it is accidentally deleted by rm -rf data01.dbf) , and generate a new data file in the original directory or other path! Another possibility is that the standby database disk is full and the data files added by the primary database cannot be transferred to the standby database. You can also use this syntax.
alter database create datafile 31;
The role of alter database create datafile
Creates a new empty datafile in place of an old one–useful to re-create a datafile that was lost with no backup.
The premise is that the database turns on the archive mode and the archive log since the original data file was created is available.
This command extracts the DDL command to create the file by viewing the control file, initializes the creation of the file (the file content is empty at this moment), and then uses the archive log to redo all redo. It can be used when there is no data file backup.
recover recover data files
At this time, we use the archive log to restore the deleted data file. Note that the archive log should retain the log from the time the data file was deleted to this moment, and cannot be interrupted or deleted.
recover datafile 31;
It is possible that errors ORA-00279, ORA-00289, and ORA-00280 will be reported during recovery. Then we select AUTO to automatically apply the archive log to complete the entire recovery operation.
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
online data files
After the previous step is completed, we can put the deleted data online.
alter database datafile 31 online;
select file#,status,name,online_time from v$datafile where status='RECOVER'; select file#,status from v$datafile_header where file#=31;
15:23:17 SYS@testogg> select file#,status from v$datafile_header where file#=31;
FILE#STATUS
---------- -------
31 ONLINE
15:23:19 SYS@testogg> c/datafile_header/datafile
1* select file#,status from v$datafile where file#=31
15:23:35 SYS@testogg> /
FILE#STATUS
---------- -------
31 ONLINE
3. Check data integrity
Cut archive and checkpoint
alter system switch logfile; alter system switch logfile; alter system checkpoint; alter system switch logfile; alter system checkpoint;
View the physical location of the data files, start and stop the database (optional)
15:38:07 JIEKEXU@testogg> !ls -l /u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf -rw-r----- 1 oracle oinstall 1056768 Oct 20 15:33 /u01/app/oracle/oradata/TESTOGG/jiekexu_data02.dbf 15:39:38 JIEKEXU@testogg> conn / as sysdba Connected. 15:39:46 SYS@testogg> shu immediate Database closed. Database dismounted. ORACLE instance shut down. 15:40:16 SYS@testogg> startup ORACLE instance started. Total System Global Area 6878657840 bytes Fixed Size 8912176 bytes Variable Size 4143972352 bytes Database Buffers 2717908992 bytes Redo Buffers 7864320 bytes Database mounted. Database opened. 15:41:32 SYS@testogg> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0 19coggtest:/u01/app/oracle/oradata/TESTOGG(testogg)$ ll jiekexu* -rw-r----- 1 oracle oinstall 10493952 Oct 20 15:41 jiekexu_data01.dbf -rw-r----- 1 oracle oinstall 1056768 Oct 20 15:41 jiekexu_data02.dbf -rw-r----- 1 oracle oinstall 1056768 Oct 20 14:30 jiekexu_data02.dbf_bak --The restored file is the same size as the original mv
Final reminder:
1. When a database failure occurs, protect the site as much as possible, and consider the consequences before performing operations. Baidu’s indiscriminate direct operations will lead to irreversible damage, and data may be permanently lost.
2. Before using the alter database create datafile command, you need to be careful and evaluate whether all archive logs exist
Reference links
HOW TO RECOVER OFFLINE DROPPED DATAFILE IN ARCHIVELOG MODE (Document ID 286355.1) https://docs.oracle.com/en/database/oracle/oracle-database/23/admin/managing-tablespaces.html#GUID-F9840816-246E-46DF-9B85-A0BFB3E0E0D8 https://www.xifenfei.com/2016/10/alter-database-create-datafile-recovery.html http://blog.itpub.net/26736162/viewspace-2124605 https://www.modb.pro/db/47441
The full text is complete. I hope it can help you who are reading this. If you think this article is helpful to you, you can share it with your friends and colleagues. Share it with anyone you care about, and learn and make progress together~~~
Welcome to follow my public account [JiekeXu DBA Road] and learn new knowledge together as soon as possible! You can find me at the following three addresses. The other addresses are all pirated and infringing articles that have been crawled from me, and the code formats, pictures, etc. are all messed up, making it inconvenient to read. Welcome to my official account or Mo Tianlun address to follow me as soon as possible. Get the latest news.
————————–
Public account: JiekeXu DBA road
CSDN: https://blog.csdn.net/JiekeXu
Mo Tianlun: https://www.modb.pro/u/4347
Tencent Cloud: https://cloud.tencent.com/developer/user/5645107————————–
Share several database backup scripts
Oracle table fragmentation check and defragmentation solution
OGG|Oracle GoldenGate Basics Collection of historical articles on public accounts in 2022
Several problems encountered by Oracle 19c RAC
OGG|Compare consistency after Oracle data migration
OGG|Oracle GoldenGate Microservice Architecture
A problem with extremely slow Oracle query table space usage
Some issues you need to pay attention to when upgrading Oracle 11g to 19c
Domestic database|TiDB 5.4 stand-alone quick installation first experience
Oracle ADG standby database shutdown, maintenance process and incremental recovery
Linux environment to build MySQL8.0.28 master-slave synchronization environment
What information can you learn from the domestic database research report and my summary suggestions
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. MySQL entry-level skills treeHomepageOverview 77275 people are learning the system