nactive恢复mysql删除数据_file$ 删除记录恢复(delete file$ recovery)

最新推荐文章于 2024-01-01 22:51:57 发布

Nicholas Qin

最新推荐文章于 2024-01-01 22:51:57 发布

阅读量595

点赞数

文章标签： nactive恢复mysql删除数据

本文链接：https://blog.csdn.net/weixin_42116058/article/details/113468336

版权

最近遭遇几次有人因为对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

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

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#

----------

6 rows selected.

SQL> select file# from v$datafile;

FILE#

----------

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#

----------

SQL> select file# from file$;

FILE#

----------

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 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#

----------

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#

----------

7 rows selected.

SQL> select file# from v$datafile;

FILE#

----------

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 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之后,插入被删除的记录,重建控制文件,数据会被恢复回来.如果数据库无法启动,需要通过其他方式进行恢复被删除记录

Nicholas Qin

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
nactive恢复mysql删除数据_file$ 删除记录恢复(delete file$ recovery)

最近遭遇几次有人因为对oracle不太理解，由于各种情况下，删除了file$中的部分记录，从而使得该文件之后的文件都丢失，使得数据库出现各种异常情况。这里演示了可以重启数据库的情况下两种常见的删除file$中记录的恢复创建表空间和表SQL> startupORACLE instance started.Total System Global Area 2421825536 bytesFixe...
复制链接

扫一扫

nactive恢复mysql删除数据_file$ 删除记录恢复(delete file$ recovery)

“相关推荐”对你有帮助么？