-
missing datafile
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
"There is a difference between knowing the path and walking the path."
-
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..?
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
-
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
The moment you try to hit the block on the missing data file it will throw an error.
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:
sys@ACME.WORLD> create tablespace drop_me datafile 'D:\ORACLE\ORADATA\ACME\drop_me.dbf' size 2M;
Tablespace created.
sys@ACME.WORLD> create table drop_me_tab (x int) tablespace drop_me;
Table created.
sys@ACME.WORLD> 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.
sys@ACME.WORLD> /
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.
sys@ACME.WORLD> /
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.
sys@ACME.WORLD> /
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.
sys@ACME.WORLD> /
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.
sys@ACME.WORLD> /
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.
sys@ACME.WORLD> commit;
Commit complete.
sys@ACME.WORLD> alter tablespace drop_me add datafile 'D:\ORACLE\ORADATA\ACME\drop_me01.dbf' size 2M;
Tablespace altered.
sys@ACME.WORLD> alter database datafile 'D:\ORACLE\ORADATA\ACME\drop_me01.dbf' offline drop;
Database altered.
sys@ACME.WORLD> select * from drop_me_tab;
X
----------
1
2
3
4
5
6
6 rows selected.
sys@ACME.WORLD> alter tablespace drop_me add datafile 'D:\ORACLE\ORADATA\ACME\drop_me02.dbf' size 2M;
sys@ACME.WORLD> 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
sys@ACME.WORLD> 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.
sys@ACME.WORLD> commit;
Commit complete.
sys@ACME.WORLD> 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.
sys@ACME.WORLD> alter database datafile 'D:\ORACLE\ORADATA\ACME\drop_me02.dbf' offline drop;
Database altered.
sys@ACME.WORLD> 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'
sys@ACME.WORLD> 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.
sys@ACME.WORLD> insert into drop_me_tab values(20);
1 row created.
sys@ACME.WORLD> commit;
Commit complete.
sys@ACME.WORLD>
Last edited by adewri; 08-12-2003 at 03:01 AM.
Amar
"There is a difference between knowing the path and walking the path."
-
Re: missing datafile
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.
How or why would you call that a successful backup? Forgetting a datafile during a backup is a "successful" backup? Wow.
-
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
-
Originally posted by yls177
9) smile
you will start crying after a while when data in that lost datafile is needed
-
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.
You should redesign your script to get the datafile list from the database.
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
Originally posted by yls177
a successful backup cos, we have a script which reads in the list of filesystems from a file.
Hard-coding... ekkkk. ...SQLBacktrack by any chance?
Last edited by grjohnson; 08-13-2003 at 07:46 AM.
OCP 8i, 9i DBA
Brisbane Australia
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|