- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: ORacle database
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-12-2006 01:47 AM
тАО12-12-2006 01:47 AM
ORacle database
ORA-01157: cannot identify/lock data file 21 - see DBWR trace file
ORA-01110: data file 21: '/u04/oradata/opsprd/radactdata42'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
I think may be if a create a new tablespace in the database and try to move the non existing datafiles and then drop the newly created tablespace. Please advise with the correct syntax
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-12-2006 01:52 AM
тАО12-12-2006 01:52 AM
Re: ORacle database
Check out the following link. I think it might help. You have to create a new empty datafile.
http://forums.oracle.com/forums/thread.jspa?threadID=180609
Here is waht is in there that I think might help you:
"What u have done should not have been done at any point of time. U should never delete a datafile from O/S, if u need the space at the O/S u should resize the datafile.
Now create a new, empty datafile to replace the deleted datafile
ALTER DATABASE CREATE DATAFILE '/usr/oracle/orahome/dbs/paging_O5.dbf' AS '/usr/oracle/orahome/dbs/paging_O5_new.dbf';
This statement creates an empty file that matches the deleted file. Oracle looks at information in the control file and the data dictionary to obtain size information. The old datafile is renamed as the new datafile.
And NEVER delete a datafile "
Hope this helps,
S
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-12-2006 02:02 AM
тАО12-12-2006 02:02 AM
Re: ORacle database
and welcome to the forums !
When you took the tablespaces (not files) offline the database was not told to remove them from its files, so when you restarted the DB it is looking for those files holding those tablespaces, but you have deleted them.
You could try looking at, but it depends on your setup:
http://www.csee.umbc.edu/help/oracle8/server.815/a67773/performi.htm#19041
Please also read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#33 on how to reward any useful answers given to your questions.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-12-2006 03:20 AM
тАО12-12-2006 03:20 AM
Re: ORacle database
alter database create datafile '/dev_db/oradata/opsdev/radius04.dbf' AS '/dev_db/oradata/opsdev/radius03.dbf'
it gives the following error:
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile
"/dev_db/oradata/opsdev/radius04.dbf"
i then touch radius04.dbf ( created the empty file manually ) and then tried to alter the database statement which given exactly the same error as above
Please advise
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-12-2006 04:24 AM
тАО12-12-2006 04:24 AM
Re: ORacle database
concluding from your posts, you deleted the files in production, so it does not make any sense to re-create a file in your dev DB.
With command CREATE DATAFILE you can only create a file that already belongs to the DB (it has to be visible in V$DATAFILE), which means it has to be created beforehand using CREATE TABLESPACE or ALTER TABLESPACE ADD DATAFILE.
Very strange is, that you get errors, after you took the files offline. That means most likely, that the space you provided for the tablespace in the new files has already been used, before you offlined the file, otherwise nobody would need to access the files. You can query dba_data_files for the file-id of the files deleted and crosscheck dba_extents with this id, if there are extents allocated for any segments (and what type of segments) in these datafiles.
Only way out seems to be to recover this, and that means you would either need a backup of the files and all the redologs from backup to the point, where you took the file offline. V$DATAFILE should give you information on this. Or you need to create the datafile and need all redologs from creation of the file to offline.
If you like to test this in DEV DB before, I'd advise to create a new, non-used tablespace before with a small datafile and play around with this one first.
Complete Recovery would be
1) restore the file or CREATE DATAFILE
2) recover datafile 'filename'
and give all the redologs requested.
3) alter datafile online
Other options, if redologs are not available all the way back:
- if the segments in the deleted files are indexes, they should be re-create-able.
- if the segments in the deleted files are rollback segments it may become very tricky
- if the segments in the deleted files belong to tables and do not contain data yet (means the entire stuff still is segment freespace) it may be possible to reorganize the table to re-gain access. If a
select * from table;
succeeds without error, there is a chance, that no data was in that file.
- if the segments in the deleted files belong to tables and already contain data you will loose exactly this data !
Here are some querys you should run:
select count(*) from v$datafile where status <> 'ONLINE';
select STATUS,NAME from v$datafile where status <> 'ONLINE';
are these the ones we are talking about ?
select distinct segment_name
from dba_extents where file_id in
( select file# from v$datafile where status <> 'ONLINE' );
Track the alert.log for the SCN when the files have been set "offline" or to "recover".
... and hopefully you have the redologs ???
Good hunting
Volker
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-12-2006 06:56 PM
тАО12-12-2006 06:56 PM
Re: ORacle database
Datafiles are not meant to be dropped once added. Attached is a support note with some methods you can use. (Doing an alter database OFFLINE DROP is not a method either).
hope this helps!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-12-2006 06:59 PM
тАО12-12-2006 06:59 PM
Re: ORacle database
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-12-2006 08:40 PM
тАО12-12-2006 08:40 PM
Re: ORacle database
If u have an export of tables in the tablespace
of which u have deleted datafile (Which is highly not recommended) OR if u don't have any important data in it then u can drop the tabelspace
itself
SQL> drop tablespace
then recreate the tablespace with same name,
then import tables if u have.
-Santosh
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-12-2006 08:43 PM
тАО12-12-2006 08:43 PM
Re: ORacle database
a correction of small mistake,
read 'including contents' instead of 'including containts'
-Santosh
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО12-12-2006 08:53 PM
тАО12-12-2006 08:53 PM
Re: ORacle database
I think we r moving away from the problem here. So i wil give yo more information
The datfile was created in error , the datafile was taken offline and the physical file from the unix file system was then
removed.
The database is still open and working but throwing error as mention earlier. It basically looking for the physical file which it cant find.
I dont wish to take the tablespace or database offline.
This is what i have in mind but not too sure how ,or if it will work.
step1: Create a new tablespace,mounted on a different location.
step2: Alter database rename file 'the file in error from old mounted point' to ' new location'
if this works ..then drop the new tablespace with its content.
Any comments on this please feel free to discuss.
Thank you
Jyoti