see DBWR trace file

[Stephen Badgett]

I used the dbca program to delete a database drug and now I get weard message. I would like to just start over!

What does this message mean ( see DBWR trace file )?

Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 320301720 bytes
Fixed Size 735896 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/pcsi/app/oracle/oradata/orx/DRUG.dbf'

[Joseph Loo · Accepted solution]

hi,

may I know the owner and group, i.e. oracle:dba, of the file, DRUG.dbf as compare to the other data files?

it seems the owner or group of that file may have been change.

regards.

[Joseph Loo]

more info:

you may like to refer to Metalink Doc:212053.1, if not see the following:

==================================
fact: Oracle Server - Enterprise Edition
symptom: Alter database open fails
symptom: ORA-01110: data file %s: '%s'
symptom: ORA-01157: cannot identify datafile %s - file not found
change: File Was Physically Deleted From The System
cause: The database control file is corrupt because the file is physically
deleted from the system but not from the control file .



fix:

Find out which tablespace the datafile belongs to and perform the following
actions:

From SQL*Plus or Server Manager :

SQL> Startup mount
SQL> Alter database datafile offline drop;
SQL> Alter database open ;
SQL> Drop tablespace including contents;
( tablespace to which the datafile belonged, first check if any other datafiles
belong to the same tablespace )

Recreate the tablespace afterwards.

==================================

hope this help.

regards.

[twang]

ORA-01157:

Cause: The background process was either unable to find one of the data files or failed to lock it because the file was already in use. The database will prohibit access to this file but other files will be unaffected. However, the first instance to open the database will need to access all online data files. The accompanying error from the operating system describes why the file could not be identified.

Action: At the operating system level, make the file available to the database. Then either open the database or do an ALTER SYSTEM CHECK DATAFILES.

[Sanjay Kumar Suri]

1. Are you sure the DBCA, delete datafile operation was sucessfully over?

2. If yes, it seems that you are starting the Oracle instance with a old control file. Since this control has information for the deleted datafile, it is asking for the datafile and hence not able to open the database.

3. Else you may have to carry out the recovery using an online/offline backup and redo/archive log files.

sks

[Printaporn_1]

Hi,

if you want to delete database , don't need to use dbca , just delete all datafiles.
redolog and archivelog and initial parameter file.

shutdown abort
startup mount
select name from v$datafile (to list datafiles)
select member from v$logfile ( to check redolog).
shutdown abort.

then delete those listed above files.

HTH

[T G Manikandan]

What are you trying to do.

you have deleted the database.If you have deleted the database drug and you should definitely receive weird messages.

Please revert as what you exactly looking for,

Any problems with the database the first step is to check the alert.log for the database.
Please post the alert log and the trace file for the same

[T G Manikandan]

I got your question!

There might be some OS related problems.

First check the alertlog of the database located under bdump directory.
Do attach the trace file.

Revert

[Yogeeraj_1]

hi,

More details about the location of the DBWR trace file will be found in the alert.log.

note that it can also be that the kernel parameter for the number of concurrent locks is not high enough.

regards
Yogeeraj

[Stephen Badgett]

Thanks sks,

The DBCA process might of had a glitch at the end. I do not remember what it was but there did seem to be a problem at the end that I ignored, sorry

Thanks for your response. I will look into all the suggestions posted here and answer each.
Thank you all for your input.

Steve

[Stephen Badgett]

Hi Yogeeraj

The problem is fixed now.

The DBWR trace file gave me nothing as to what the problem was and the trace function did not seem to be turned on or is at a low setting. Is the trace adjusted in the init.ora file?

Steve

[Stephen Badgett]

Hi T G Manikandan,

Thank you for your input.

The issue has been fixed.

I also looked in the alert.log file and see this but this is in a past for now and I will keep an eye out for other clues

ORA-00202: controlfile: 'ora_control5'
ORA-27037: unable to obtain file status


Steve

[Stephen Badgett]

Hi Printaporn

Ok, I wont use DBCA.

Steve

[Stephen Badgett]

Hi Sanjay Kumar Suri,

The DBCA, delete datafile operation did have problems at the end and I did not get a screen print of them but I thought it was asking me if I wanted to continue anyway and I did, BAD MOVE!

Any way the problem is fixed :)

Steve

[Stephen Badgett]

Thank you twang for your help in understanding the processes better.
The issue has be fixed.

Steve

[Stephen Badgett]

Hi Joseph Loo

This was the anser, thank you


Steve