Archive for May 14th, 2011

#1 Recovery from Loss of Non system datafile(In Archive log Mode)

Posted by sriram on May 14, 2011

In  Order to Perform/Test this Scenario…..We should delete/Drop One Non system Datafile.

But This not possible to drop/Delete files when Database is Up and  running ……

“The process cannot access the file because it is being used by another process.”

here I `ll  copy/Replace User datafile with an Old datafile.

In shutdown mode I will manually  copy old file in using “cp” command,and try to Open DB.

C:\Windows\System32>sqlplus  sys as sysdba

SQL*Plus: Release Production on Thu May 12 09:36:36 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from emp_cp;
select count(*) from emp_cp
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database mount;
alter database mount
ERROR at line 1:
ORA-01100: database already mounted

SQL> alter database open;
alter database open
ERROR at line 1:
ORA-01190: control file or data file 4 is from before the last RESETLOGS

SQL> select * from v$recover_file;

---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
         4 ONLINE  ONLINE
UNKNOWN ERROR                                                         996306

SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database recover automatic datafile 4;

Database altered.

SQL> alter database datafile 4 online;

Database altered.

SQL> alter database open;

Database altered.

SQL> select count(*) from scott.emp_cp;


SQL> select * from v$recover_file;

no rows selected


If archive log not enabled the you may receive 

SQL> alter database datafile 4 offline;
alter database datafile 4 offline
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


Posted in Nothing But BackUp | 8 Comments »

%d bloggers like this: