delete from file$ where file#=5

最新推荐文章于 2021-05-20 23:19:11 发布

_Goolen

最新推荐文章于 2021-05-20 23:19:11 发布

阅读量1.1k

点赞数

本文链接：https://blog.csdn.net/goolenblog/article/details/23424517

版权

+++此次测试在归档模式下进行，通过delete file#来清除dbfile记录
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/arch
Oldest online log sequence 237
Next log sequence to archive 239
Current log sequence 239

SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
---------- -------------------------------------------------------
6 /opt/app/oracle/oradata/goolen/goolen01.dbf
3 /opt/app/oracle/oradata/goolen/undotbs01.dbf
2 /opt/app/oracle/oradata/goolen/sysaux01.dbf
1 /opt/app/oracle/oradata/goolen/system01.dbf
4 /opt/app/oracle/oradata/goolen/goolen02.dbf
5 /opt/app/oracle/oradata/goolen/goolen03.dbf

6 rows selected.

+++offline file 5 ，并且已经从操作系统删除
SQL> alter database datafile 5 offline;

Database altered.

SQL> ! rm -rf /opt/app/oracle/oradata/goolen/goolen03.dbf

SQL> select file#,online_status,error,change# from v$recover_file ;

FILE# ONLINE_ ERROR CHANGE#
---------- ------- ----------------------------------------------------------------- ----------
5 OFFLINE FILE NOT FOUND 0

SQL> select file#,status$,ts#,relfile# from file$ order by 1;

FILE# STATUS$ TS# RELFILE#
---------- ---------- ---------- ----------
1 2 0 1
2 2 1 2
3 2 2 3
4 2 6 4
5 2 6 5
6 2 6 6

6 rows selected.

+++直接从file$表删除file 5的信息，注意file 5不是file#编号最大的文件
SQL> delete from file$ where file#=5;

1 row deleted.

SQL> select file#,status$,ts#,relfile# from file$ order by 1;

FILE# STATUS$ TS# RELFILE#
---------- ---------- ---------- ----------
1 2 0 1
2 2 1 2
3 2 2 3
4 2 6 4
6 2 6 6

SQL> col name for a55
SQL> select * from v$dbfile ;

FILE# NAME
---------- -------------------------------------------------------
6 /opt/app/oracle/oradata/goolen/goolen01.dbf
3 /opt/app/oracle/oradata/goolen/undotbs01.dbf
2 /opt/app/oracle/oradata/goolen/sysaux01.dbf
1 /opt/app/oracle/oradata/goolen/system01.dbf
4 /opt/app/oracle/oradata/goolen/goolen02.dbf
5 /opt/app/oracle/oradata/goolen/goolen03.dbf

6 rows selected.

SQL> alter database backup controlfile to trace as '/home/oracle/xiaoming/1.trc';

Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size 2213696 bytes
Variable Size 1040189632 bytes
Database Buffers 520093696 bytes
Redo Buffers 7512064 bytes
Database mounted.

+++重建控制文件

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1570009088 bytes
Fixed Size 2213696 bytes
Variable Size 1040189632 bytes
Database Buffers 520093696 bytes
Redo Buffers 7512064 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "GOOLEN" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/opt/app/oracle/oradata/goolen/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/opt/app/oracle/oradata/goolen/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/opt/app/oracle/oradata/goolen/redo03.log' SIZE 50M BLOCKSIZE 512
11 DATAFILE
12 '/opt/app/oracle/oradata/goolen/system01.dbf',
13 '/opt/app/oracle/oradata/goolen/sysaux01.dbf',
14 '/opt/app/oracle/oradata/goolen/undotbs01.dbf',
15 '/opt/app/oracle/oradata/goolen/goolen02.dbf',
16 -- '/opt/app/oracle/oradata/goolen/goolen03.dbf', +++该文件已经被删除，重建控制文件的时候不需要该条记录
17 '/opt/app/oracle/oradata/goolen/goolen01.dbf'
18 CHARACTER SET AL32UTF8
19 ;

Control file created.

SQL> alter database open;

Database altered.

SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
---------- -------------------------------------------------------
4 /opt/app/oracle/oradata/goolen/goolen02.dbf
3 /opt/app/oracle/oradata/goolen/undotbs01.dbf
2 /opt/app/oracle/oradata/goolen/sysaux01.dbf
1 /opt/app/oracle/oradata/goolen/system01.dbf

SQL> select * from v$recover_file;

no rows selected

SQL> select file#,status$,ts#,relfile# from file$;

FILE# STATUS$ TS# RELFILE#
---------- ---------- ---------- ----------
1 2 0 1
2 2 1 2
3 2 2 3
4 2 6 4
6 2 6 6

SQL> select * from v$dbfile;

FILE# NAME
---------- -------------------------------------------------------
4 /opt/app/oracle/oradata/goolen/goolen02.dbf
3 /opt/app/oracle/oradata/goolen/undotbs01.dbf
2 /opt/app/oracle/oradata/goolen/sysaux01.dbf
1 /opt/app/oracle/oradata/goolen/system01.dbf

SQL> select text from dba_views where view_name='DBA_DATA_FILES';

TEXT
--------------------------------------------------------------------------------
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1,
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
and fe.fenum = f.file#
union all
select
v.name,f.file#, ts.name,
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#,
decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL),
decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NOT NULL
and v.file# = hc.ktfbhcafno
and hc.ktfbhctsn = ts.ts#
and fe.fenum = f.file#

SQL> select fenum,ferfn from x$kccfe;

FENUM FERFN
---------- ----------
1 1
2 2
3 3
4 4
5 0
6 6

6 rows selected.

SQL> select v.name, f.file#, ts.name,
2 ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
5 ts.blocksize * f.maxextend, f.maxextend, f.inc,
6 ts.blocksize * (f.blocks - 1), f.blocks - 1,
7 decode(fe.fetsn, 0, decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
8 decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER'))
9 from sys.file$ f, sys.ts$ ts, sys.v$dbfile v, x$kccfe fe
10 where v.file# = f.file#
11 and f.spare1 is NULL
12 and f.ts# = ts.ts#
13 and fe.fenum = f.file#;

no rows selected

SQL> select
v.name,f.file#, ts.name
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts, x$kccfe fe
where v.file# = f.file#
and f.spare1 is NOT NULL
and v.file# = hc.ktfbhcafno
and hc.ktfbhctsn = ts.ts#
and fe.fenum = f.file#
;

NAME FILE#
------------------------------------------------------- ----------
/opt/app/oracle/oradata/goolen/goolen02.dbf 4
/opt/app/oracle/oradata/goolen/undotbs01.dbf 3
/opt/app/oracle/oradata/goolen/sysaux01.dbf 2
/opt/app/oracle/oradata/goolen/system01.dbf 1

SQL> select view_definition from v$fixed_view_definition where view_name='GV$DBFILE';

VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------
select inst_id,fnfno,fnnam from x$kccfn where fnnam is not null and bitand(fnflg, 4) != 4 and fntyp=4

SQL> select inst_id,fnfno,fnnam from x$kccfn where fnnam is not null and bitand(fnflg, 4) != 4 and fntyp=4;

INST_ID FNFNO FNNAM
---------- ---------- -------------------------------------------------------
1 4 /opt/app/oracle/oradata/goolen/goolen02.dbf
1 3 /opt/app/oracle/oradata/goolen/undotbs01.dbf
1 2 /opt/app/oracle/oradata/goolen/sysaux01.dbf
1 1 /opt/app/oracle/oradata/goolen/system01.dbf

SQL> select inst_id,fnfno,fnnam,fnflg,fntyp from x$kccfn;

INST_ID FNFNO FNNAM FNFLG FNTYP
---------- ---------- ------------------------------------------------------- ---------- ----------
1 1 /opt/app/oracle/oradata/goolen/redo01.log 2 3
1 3 /opt/app/oracle/oradata/goolen/redo03.log 2 3
1 2 /opt/app/oracle/oradata/goolen/redo02.log 0 3
1 6 /opt/app/oracle/oradata/goolen/goolen01.dbf 4 4
1 4 /opt/app/oracle/oradata/goolen/goolen02.dbf 0 4
1 3 /opt/app/oracle/oradata/goolen/undotbs01.dbf 0 4
1 2 /opt/app/oracle/oradata/goolen/sysaux01.dbf 0 4
1 1 /opt/app/oracle/oradata/goolen/system01.dbf 0 4

8 rows selected.

+++如果file 5在数据库中的file#是最大的，那么直接从file$里删除file 5信息，不会影响其他数据文件
+++如果file 5后面还有file#更大的dbfile，那么直接删除file$里的记录，会导致后面的dbfile也无法读取，

file#不能断号，通过命令删除dbfile，只是把file$里该dbfile的status值为1(INVALID)

参考blog：
http://www.xifenfei.com/1660.html

_Goolen

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
3
评论
delete from file$ where file#=5

+++此次测试在归档模式下进行，通过delete file#来清除dbfile记录SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /opt/archOldest
复制链接

扫一扫