oracle v$recover_file,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

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