Printable View
we did a offline backup. backup was successful. however, in our script, we forget to include a newly added datafile into the backups. therefore, the successful backup is short of one missing data file. my question is that is it possible to restored this offline backup in another box considering that a datafile is not present.
you will have to offline that datafile and drop the tablespace it was part of
Yes you can. Only problem would be, you will need to drop that datafile (using alter database datafile 'name' offline drop) after doing the database mount. Then you can open it normally.
Amar, what will happen to the tablespace and the objects in it..?Quote:
Yes you can. Only problem would be, you will need to drop that datafile (using alter database datafile 'name' offline drop) after doing the database mount. Then you can open it normally.
Say table A got 3 extents. The latest extent is in the new data file which we are droping.? So what will happened to the table? And the tablespace..?
Regards,
Thomas
The moment you try to hit the block on the missing data file it will throw an error.Quote:
Originally posted by Thomasps
Amar, what will happen to the tablespace and the objects in it..?
Say table A got 3 extents. The latest extent is in the new data file which we are droping.? So what will happened to the table? And the tablespace..?
Regards,
Thomas
Thats one way to bring up the database, but yes if its the only datafile of that tablespace then you need to drop that tablespace as well.
If more than one datafiles belong to that tablespace then you can perform selects and inserts on those tables which do not belong to that dropped datafile. Only those blocks will not be accessible which were on that droppped datafile.
Code:[email protected]D> create tablespace drop_me datafile 'D:\ORACLE\ORADATA\ACME\drop_me.dbf' size 2M;
Tablespace created.
[email protected]D> create table drop_me_tab (x int) tablespace drop_me;
Table created.
[email protected]D> insert into drop_me_tab values(&number);
Enter value for number: 1
old 1: insert into drop_me_tab values(&number)
new 1: insert into drop_me_tab values(1)
1 row created.
[email protected]D> /
Enter value for number: 2
old 1: insert into drop_me_tab values(&number)
new 1: insert into drop_me_tab values(2)
1 row created.
[email protected]D> /
Enter value for number: 3
old 1: insert into drop_me_tab values(&number)
new 1: insert into drop_me_tab values(3)
1 row created.
[email protected]D> /
Enter value for number: 4
old 1: insert into drop_me_tab values(&number)
new 1: insert into drop_me_tab values(4)
1 row created.
[email protected]D> /
Enter value for number: 5
old 1: insert into drop_me_tab values(&number)
new 1: insert into drop_me_tab values(5)
1 row created.
[email protected]D> /
Enter value for number: 6
old 1: insert into drop_me_tab values(&number)
new 1: insert into drop_me_tab values(6)
1 row created.
[email protected]D> commit;
Commit complete.
[email protected]D> alter tablespace drop_me add datafile 'D:\ORACLE\ORADATA\ACME\drop_me01.dbf' size 2M;
Tablespace altered.
[email protected]D> alter database datafile 'D:\ORACLE\ORADATA\ACME\drop_me01.dbf' offline drop;
Database altered.
[email protected]D> select * from drop_me_tab;
X
----------
1
2
3
4
5
6
6 rows selected.
[email protected]D> alter tablespace drop_me add datafile 'D:\ORACLE\ORADATA\ACME\drop_me02.dbf' size 2M;
[email protected]D> select name,file# from v$datafile where name like '%DROP_ME%';
NAME FILE#
---------------------------------------- ----------
D:\ORACLE\ORADATA\ACME\DROP_ME.DBF 30
D:\ORACLE\ORADATA\ACME\DROP_ME01.DBF 31
D:\ORACLE\ORADATA\ACME\DROP_ME02.DBF 32
[email protected]D> declare
2 x number;
3 begin
4 for x in 1..10000 loop
5 insert into drop_me_tab values (x);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
[email protected]D> commit;
Commit complete.
[email protected]D> select segment_name,tablespace_name,file_id from dba_extents where segment_name like 'DROP_ME%';
SEGMENT_NAME TABLESPACE_NAME FILE_ID
---------------------------------------- ------------------------------ ----------
DROP_ME_TAB DROP_ME 30
.
.
.
DROP_ME_TAB DROP_ME 30
DROP_ME_TAB DROP_ME 32
17 rows selected.
[email protected]D> alter database datafile 'D:\ORACLE\ORADATA\ACME\drop_me02.dbf' offline drop;
Database altered.
[email protected]D> select count(*) from drop_me_tab;
select count(*) from drop_me_tab
*
ERROR at line 1:
ORA-00376: file 32 cannot be read at this time
ORA-01110: data file 32: 'D:\ORACLE\ORADATA\ACME\DROP_ME02.DBF'
[email protected]D> select * from drop_me_tab where rownum < 20;
X
----------
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
9
10
11
19 rows selected.
[email protected]D> insert into drop_me_tab values(20);
1 row created.
[email protected]D> commit;
Commit complete.
[email protected]D>
How or why would you call that a successful backup? Forgetting a datafile during a backup is a "successful" backup? Wow.Quote:
Originally posted by yls177
we did a offline backup. backup was successful. however, in our script, we forget to include a newly added datafile into the backups. therefore, the successful backup is short of one missing data file.
a successful backup cos, we have a script which reads in the list of filesystems from a file. what happened is that this file was not updated to reflect the latest filesystem. so what happens is that the backup automatiaclly will not backup it since the filesystem file did not register the new filesystem.
anyway....
the good news is we managed to get it up and running without offline any datafile.
steps
1) copy the datafile to the restored database
2) startup
3) of course, there will be errros
4) re-create control file
5) recover database using backup controlfile until cancel
6) will prompt for which online log file
7) alter dataabse open resetlogs;
8) and it works
9) smile
you will start crying after a while when data in that lost datafile is neededQuote:
Originally posted by yls177
9) smile
You should redesign your script to get the datafile list from the database.Quote:
Originally posted by yls177
a successful backup cos, we have a script which reads in the list of filesystems from a file. what happened is that this file was not updated to reflect the latest filesystem. so what happens is that the backup automatiaclly will not backup it since the filesystem file did not register the new filesystem.
Hard-coding... ekkkk. ...SQLBacktrack by any chance?Quote:
Originally posted by yls177
a successful backup cos, we have a script which reads in the list of filesystems from a file.
that is what i suspected too... but we copy the data over... so shouldnt have any lost data... hmmm, am i right?Quote:
Originally posted by pando
you will start crying after a while when data in that lost datafile is needed
Copy from where, u said you had lost the data file ?? where do you get the lost data from ??Quote:
Originally posted by yls177
that is what i suspected too... but we copy the data over... so shouldnt have any lost data... hmmm, am i right?
Well, how about using RMAN? Live and learn, right :-)Quote:
Originally posted by yls177
a successful backup cos, we have a script which reads in the list of filesystems from a file.
select * from v$recover_file;
select file#, status from v$datafile;
and look the output :D
Quote:
Originally posted by pando
you will start crying after a while when data in that lost datafile is needed
u are right... i started to cry.. as my functional team has some problems with the new restoration. however, they couldnt pinpoint to the loss of data. my colleague has to restore once again..
my question is that when we do the below
1) copy the datafile to the restored database
2) startup
3) of course, there will be errros
4) re-create control file
5) recover database using backup controlfile until cancel
6) will prompt for which online log file
7) alter dataabse open resetlogs;
though , the database can be started, but we will surely lose some data?
also-> no rows are selectedQuote:
select * from v$recover_file;
no file# with offline statusQuote:
select file#, status from v$datafile;
does that mean that the database is fine?