标签云
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,664)
- DB2 (22)
- MySQL (72)
- Oracle (1,527)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (558)
- Oracle安装升级 (90)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (77)
- 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)
-
最近发表
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
- 如何修改集群的公网信息(包括 VIP) (Doc ID 1674442.1)
- 如何在 oracle 集群环境下修改私网信息 (Doc ID 2103317.1)
- ORA-600 [kcvfdb_pdb_set_clean_scn: cleanckpt] 相关bug
- ORA-600 krhpfh_03-1210故障处理
- 19c库启动报ORA-600 kcbzib_kcrsds_1
- DBMS_SESSION.set_context提示ORA-01031问题解决
标签归档:File N in the controlfile not found in data dictionary
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之后,插入被删除的记录,重建控制文件,数据会被恢复回来.如果数据库无法启动,需要通过其他方式进行恢复被删除记录