标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,683)
- DB2 (22)
- MySQL (73)
- Oracle (1,545)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (68)
- Oracle Bug (8)
- Oracle RAC (53)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (565)
- Oracle安装升级 (92)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (79)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- ORA-15411: Failure groups in disk group DATA have different number of disks.
- 断电引起的ORA-08102: 未找到索引关键字, 对象号 39故障处理
- ORA-00227: corrupt block detected in control file
- 手工删除19c rac
- 解决oracle数据文件路径有回车故障
- .wstop扩展名勒索数据库恢复
- Oracle Recovery Tools工具一键解决ORA-00376 ORA-01110故障(文件offline)
- OGG-02771 Input trail file format RELEASE 19.1 is different from previous trail file form at RELEASE 11.2.
- OGG-02246 Source redo compatibility level 19.0.0 requires trail FORMAT 12.2 or higher
- GoldenGate 19安装和打patch
- dd破坏asm磁盘头恢复
- 删除asmlib磁盘导致磁盘组故障恢复
- Kylin Linux 安装19c
- ORA-600 krse_arc_complete.4
- Oracle 19c 202410补丁(RUs+OJVM)
- ntfs MFT损坏(ntfs文件系统故障)导致oracle异常恢复
- .mkp扩展名oracle数据文件加密恢复
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
标签归档:删除file$
file$ 删除记录恢复(delete file$ recovery)
最近遭遇几次有人因为对oracle不太理解,由于各种情况下,删除了file$中的部分记录,从而使得该文件之后的文件都丢失,使得数据库出现各种异常情况。这里演示了可以重启数据库的情况下两种常见的删除file$中记录的恢复
创建表空间和表
SQL> startup ORACLE instance started. Total System Global Area 2421825536 bytes Fixed Size 2215744 bytes Variable Size 1828716736 bytes Database Buffers 570425344 bytes Redo Buffers 20467712 bytes Database mounted. Database opened. SQL> create tablespace tbs_delete_file datafile '/home/oracle/oradata/xifenfei/file01.dbf' size 128M; Tablespace created. SQL> alter tablespace tbs_delete_file add datafile '/home/oracle/oradata/xifenfei/file02.dbf' size 128M; Tablespace altered. SQL> alter tablespace tbs_delete_file add datafile '/home/oracle/oradata/xifenfei/file03.dbf' size 128M; Tablespace altered. SQL> create table t_xifenfei tablespace tbs_delete_file 2 as select * from dba_objects; Table created. SQL> insert into t_xifenfei select * from dba_objects; 71895 rows created. SQL> / 71895 rows created. SQL> / 71895 rows created. SQL> / 71895 rows created. SQL> commit; Commit complete. SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 359475
删除file$中记录
SQL> select ts#,file# from file$; TS# FILE# ---------- ---------- 0 1 1 2 2 3 4 4 6 5 6 6 6 7 7 rows selected. [oracle@localhost ~]$ ss SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 2 23:30:57 2017 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> delete from file$ where file#=5; 1 row deleted. SQL> commit; Commit complete. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
不重建控制文件,重启数据库
[oracle@localhost tmp]$ ss SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 2 23:46:33 2017 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2421825536 bytes Fixed Size 2215744 bytes Variable Size 1828716736 bytes Database Buffers 570425344 bytes Redo Buffers 20467712 bytes Database mounted. Database opened. SQL> select file# from file$; FILE# ---------- 1 2 3 4 6 7 6 rows selected. SQL> select file# from v$datafile; FILE# ---------- 1 2 3 4 5 6 7 7 rows selected. SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 359475
数据库启动正常,而且文件也未从控制文件中删除,而且记录查询正常,考虑通过逻辑方式迁移数据。
测试重建控制文件
SQL> startup nomount ORACLE instance started. Total System Global Area 2421825536 bytes Fixed Size 2215744 bytes Variable Size 1828716736 bytes Database Buffers 570425344 bytes Redo Buffers 20467712 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 4 5 6 7 LOGFILE 8 GROUP 1 '/home/oracle/oradata/xifenfei/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/home/oracle/oradata/xifenfei/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/home/oracle/oradata/xifenfei/redo03.log' SIZE 50M BLOCKSIZE 512 9 10 11 DATAFILE 12 '/home/oracle/oradata/xifenfei/system01.dbf', '/home/oracle/oradata/xifenfei/sysaux01.dbf', '/home/oracle/oradata/xifenfei/undotbs01.dbf', 13 14 15 '/home/oracle/oradata/xifenfei/users01.dbf', '/home/oracle/oradata/xifenfei/file01.dbf', 16 17 '/home/oracle/oradata/xifenfei/file02.dbf', '/home/oracle/oradata/xifenfei/file03.dbf' CHARACTER SET AL32UTF8 18 19 20 ; Control file created. SQL> alter database open; Database altered. SQL> select file# from v$datafile; FILE# ---------- 1 2 3 4 SQL> select file# from file$; FILE# ---------- 1 2 3 4 6 7 6 rows selected. SQL> select count(*) from t_xifenfei; select count(*) from t_xifenfei * ERROR at line 1: ORA-00600: internal error code, arguments: [25029], [6], [], [], [], [], [],[], [], [], [], []
删除file$中记录,然后重启库之后,大于删除的file#之后的数据文件全部丢失.
alert日志报错
Fri Jun 02 23:49:42 2017 alter database open Fri Jun 02 23:49:42 2017 Thread 1 advanced to log sequence 9 (thread open) Thread 1 opened at log sequence 9 Current log# 3 seq# 9 mem# 0: /home/oracle/oradata/xifenfei/redo03.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Fri Jun 02 23:49:42 2017 SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Dictionary check beginning Tablespace 'TEMP' #3 found in data dictionary, but not in the controlfile. Adding to controlfile. File #5 in the controlfile not found in data dictionary. Removing file from controlfile. data file 5: '/home/oracle/oradata/xifenfei/file01.dbf' File #6 in the controlfile not found in data dictionary. Removing file from controlfile. data file 6: '/home/oracle/oradata/xifenfei/file02.dbf' File #7 in the controlfile not found in data dictionary. Removing file from controlfile. data file 7: '/home/oracle/oradata/xifenfei/file03.dbf' Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery ********************************************************************* WARNING: The following temporary tablespaces contain no files. This condition can occur when a backup controlfile has been restored. It may be necessary to add files to these tablespaces. That can be done using the SQL statement: ALTER TABLESPACE <tablespace_name> ADD TEMPFILE Alternatively, if these temporary tablespaces are no longer needed, then they can be dropped. Empty temporary tablespace: TEMP ********************************************************************* Database Characterset is AL32UTF8 No Resource Manager plan active ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. If files cannot be cataloged, then manually delete them using OS command. One of the following events caused this: 1. A backup controlfile was restored. 2. A standby controlfile was restored. 3. The controlfile was re-created. 4. db_recovery_file_dest had previously been enabled and then disabled. ********************************************************** replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Fri Jun 02 23:49:43 2017 QMNC started with pid=20, OS id=11886 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Completed: alter database open Fri Jun 02 23:49:44 2017 db_recovery_file_dest_size of 3882 MB is 0.00% used. This is a 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. Fri Jun 02 23:49:44 2017 Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_m001_11890.trc (incident=84344): ORA-00600: internal error code, arguments: [25029], [6], [], [], [], [], [], [], [], [], [], [] Incident details in: /opt/oracle/diag/rdbms/test/test/incident/incdir_84344/test_m001_11890_i84344.trc Errors in file /opt/oracle/diag/rdbms/test/test/trace/test_m001_11890.trc: ORA-00600: internal error code, arguments: [25029], [6], [], [], [], [], [], [], [], [], [], [] Fri Jun 02 23:49:45 2017 Trace dumping is performing id=[cdmp_20170602234945] Fri Jun 02 23:49:46 2017 Starting background process CJQ0 Fri Jun 02 23:49:46 2017 CJQ0 started with pid=21, OS id=11902
这里报错明显,由于file$.file#=5被删除,重建控制文件之后,file#在5之后的数据文件全部从控制文件中删除,类似提示File #5 in the controlfile not found in data dictionary. Removing file from controlfile.
插入file$记录恢复
--通过dump文件头和相关block,找出来对应记录 插入被file$删除记录 SQL> insert into file$ values(5,2,16384,6,5,0,0,0,993135,null,20971522,null,null,null); 1 row created. SQL> commit; Commit complete. SQL> select file# from file$; FILE# ---------- 1 2 3 4 5 6 7 7 rows selected. --重启数据库,创建控制文件 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup nomount ORACLE instance started. Total System Global Area 2421825536 bytes Fixed Size 2215744 bytes Variable Size 1828716736 bytes Database Buffers 570425344 bytes Redo Buffers 20467712 bytes SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 4 5 6 MAXLOGHISTORY 292 7 LOGFILE GROUP 1 '/home/oracle/oradata/xifenfei/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/home/oracle/oradata/xifenfei/redo02.log' SIZE 50M BLOCKSIZE 512, 8 9 10 GROUP 3 '/home/oracle/oradata/xifenfei/redo03.log' SIZE 50M BLOCKSIZE 512 DATAFILE 11 12 '/home/oracle/oradata/xifenfei/system01.dbf', '/home/oracle/oradata/xifenfei/sysaux01.dbf', 13 14 '/home/oracle/oradata/xifenfei/undotbs01.dbf', 15 '/home/oracle/oradata/xifenfei/users01.dbf', '/home/oracle/oradata/xifenfei/file01.dbf', '/home/oracle/oradata/xifenfei/file02.dbf', 16 17 18 '/home/oracle/oradata/xifenfei/file03.dbf' 19 CHARACTER SET AL32UTF8 ; 20 Control file created. SQL> alter database open; alter database open * ERROR at line 1: ORA-01113: file 5 needs media recovery ORA-01110: data file 5: '/home/oracle/oradata/xifenfei/file01.dbf' SQL> recover database; Media recovery complete. SQL> alter database open; Database altered. SQL> select file# from file$; FILE# ---------- 1 2 3 4 5 6 7 7 rows selected. SQL> select file# from v$datafile; FILE# ---------- 1 2 3 4 5 6 7 7 rows selected. SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 359475
alert日志正常,未提示删除控制文件中数据文件
alter database open Fri Jun 02 23:56:52 2017 Thread 1 advanced to log sequence 10 (thread open) Thread 1 opened at log sequence 10 Current log# 1 seq# 10 mem# 0: /home/oracle/oradata/xifenfei/redo01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Fri Jun 02 23:56:52 2017 SMON: enabling cache recovery Successfully onlined Undo Tablespace 2. Dictionary check beginning Tablespace 'TEMP' #3 found in data dictionary, but not in the controlfile. Adding to controlfile. Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery ********************************************************************* WARNING: The following temporary tablespaces contain no files. This condition can occur when a backup controlfile has been restored. It may be necessary to add files to these tablespaces. That can be done using the SQL statement: ALTER TABLESPACE <tablespace_name> ADD TEMPFILE Alternatively, if these temporary tablespaces are no longer needed, then they can be dropped. Empty temporary tablespace: TEMP ********************************************************************* Database Characterset is AL32UTF8 No Resource Manager plan active ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. If files cannot be cataloged, then manually delete them using OS command. One of the following events caused this: 1. A backup controlfile was restored. 2. A standby controlfile was restored. 3. The controlfile was re-created. 4. db_recovery_file_dest had previously been enabled and then disabled. ********************************************************** replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Fri Jun 02 23:56:53 2017 QMNC started with pid=20, OS id=12127 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Completed: alter database open
通过插入删除记录,重建控制文件,数据库恢复正常,而且相关记录也可以查询。
结论总结
1. 如果删除file$中的记录,而且控制文件未重建,数据库可以正常启动,而且可以查询数据
2. 如果删除file$中的记录,而且控制文件被重建,在数据库启动过程中,从被删除文件之后的所有文件记录从控制文件中删除(类似:File N in the controlfile not found in data dictionary. Removing file from controlfile.).可以在数据库open之后,插入被删除的记录,重建控制文件,数据会被恢复回来.如果数据库无法启动,需要通过其他方式进行恢复被删除记录