v$recover_file

ORA-01157: cannot identify/lock data file 68 - see DBWR trace file
ORA-01110: data file 68: '/u01/app/oracle/oradata/querydb/phs_data01.dbf'

[@more@]

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 994132128 bytes
Fixed Size 73888 bytes
Variable Size 364249088 bytes
Database Buffers 629145600 bytes
Redo Buffers 663552 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 68 - see DBWR trace file
ORA-01110: data file 68: '/u01/app/oracle/oradata/querydb/phs_data01.dbf'
SQL> select file# from v$recover_file;

FILE#
----------
68
69
70
250
251
252
253
254
260
261
262

FILE#
----------
263
264

13 rows selected.

SQL> run
1* select 'alter database datafile '||file#|| ' offline drop;' from v$recover_file

'ALTERDATABASEDATAFILE'||FILE#||'OFFLINEDROP;'
------------------------------------------------------------------------------
alter database datafile 68 offline drop;
alter database datafile 69 offline drop;
alter database datafile 70 offline drop;
alter database datafile 250 offline drop;
alter database datafile 251 offline drop;
alter database datafile 252 offline drop;
alter database datafile 253 offline drop;
alter database datafile 254 offline drop;
alter database datafile 260 offline drop;
alter database datafile 261 offline drop;
alter database datafile 262 offline drop;
alter database datafile 263 offline drop;
alter database datafile 264 offline drop;

13 rows selected.

SQL> alter database datafile 68 offline drop;

Database altered.

SQL> alter database datafile 69 offline drop;
alter database datafile 70 offline drop;
alter database datafile 250 offline drop;
alter database datafile 251 offline drop;
alter database datafile 252 offline drop;
alter database datafile 253 offline drop;
alter database datafile 254 offline drop;

Database altered.

SQL> alter database datafile 260 offline drop;
alter database datafile 261 offline drop;
alter database datafile 262 offline drop;
alter database datafile 263 offline drop;
alter database datafile 264 offline drop;
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL>

Database altered.

SQL>
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 196 failed verification check
ORA-01110: data file 196: '/u01/app/oracle/oradata/querydb/phs_data15.dbf'
ORA-01200: actual file size of 158213 is smaller than correct size of 256000
blocks

SQL> alter database datafile 196 offline drop;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 6 of thread 1
ORA-00312: online log 6 thread 1: '/u01/app/oracle/oradata/querydb/redo06a.log'
ORA-00312: online log 6 thread 1: '/u01/app/oracle/oradata/querydb/redo06b.log'

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database open;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9925929/viewspace-932009/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9925929/viewspace-932009/