Missing Example.dbf halts startup 1

[bobhagan]

I recently had a machine crash and wipe out my Oracle install. I have copies of the admin and oradata directories and was able to restore them. The example.dbf must be damaged. Startup goes as far as mounting, then errors when it tries to open.

ORA-01122 database file 5 failed verification check
ORA-01110 database file 5 c:....example.dbf
ORA-01251 unknown file header.

I would as soon remove this entirely. It has to be listed somewhere. How do I remove the name, so it isn't looked for?

Thanks
Bob Hagan

 

[SantaMufasa]

Bob,

That file certainly is not a necessary component of a working Oracle database. To get rid of it, you can follow the steps, below. I created a tablespace named "YADA" with a file named "...example.dbf" to most closely simulate your situation.

You cannot remove the file if your database is not in ARCHIVELOG mode. If your database is not in ARCHIVELOG mode, you must at least temporarily put the database in ARCHIVELOG mode, then you can (as I did), take the database out of ARCHIVELOG mode once the datafile is gone:

SQL> connect / as sysdba
Connected.

SQL> startup
ORACLE instance started.

Total System Global Area   76062212 bytes
Fixed Size                   454148 bytes
Variable Size              67108864 bytes
Database Buffers            8192000 bytes
Redo Buffers                 307200 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\EXAMPLE.DBF'

SQL> alter database archivelog;

Database altered.

SQL> alter database datafile 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\EXAMPLE.DBF' offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> drop tablespace yada;

Tablespace dropped.

SQL> alter database close;

Database altered.

SQL> alter database noarchivelog;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area   76062212 bytes
Fixed Size                   454148 bytes
Variable Size              67108864 bytes
Database Buffers            8192000 bytes
Redo Buffers                 307200 bytes
Database mounted.
Database opened.
SQL>

Let us know if this covers all the issues in your scenario.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[bobhagan]

Hi

Thanks for the advice. Unfortunately, I was not able to start SQLPLUS. I logged as sys; it thought the password was invalid. Using orapwd to reset made no difference.

I had also had messages saying that Intelligent Agent had encountered an error and would terminate, so I finally decided to reinstall, save the example.dbf and copy it in after restoring my files. The install and creating a new database always works, but I've tried copying in my files with example.dbf and without, and then get the Intelligent Agent error at the end of a reboot to restart the services. After that, in Enterprise Manager even the sys account has insufficient privileges to log on, and SQLPLUS always says the database is starting or shutting down.

I'm in worse shape than when I started. If I can't fix this, I'm going to have to recreate everything from scripts. No data lost, because this is a development machine, but I'd gotten creative and done lots of new or changed stored procedures just before the crash. Ugh

Bob Hagan

 

[SantaMufasa]

Bob,

Here are some issues to confirm:

1) To startup and shutdown the database, you must be able to do the following without a password:

sqlplus /nolog
SQL> connect / as sysdba

Logging in to SQL*Plus successfully as "SYS" will not give you the level of permission required to startup and shutdown the database. You must be able to successfully "connect / as sysdba" without a password.

If SQL*Plus's reaction to your attempt is either the error message, "insufficient privileges" or SQL*Plus asks you for a password, then you are not connected to the Oracle PC with an operating-system log-in that is a member of the highly privileged "ORA_DBA" group on the Oracle PC.

To confirm the PC log-ins that are members of the "ORA_DBA" group, connect to the PC as an administrator. As an administrator, you can check "Users and Passwords" (on Win2K, or something similar under other Windows versions). To navigate there on Win2K, Start..Settings..Control Panel..Users and Passwords. Under the "Users" tab, you should see (on the right side of the screen under the "Groups" column) one or more iterations of the group "ORA_DBA". Look to the left to see which users on that PC are members of the "ORA_DBA" group. You must then log-in to that PC as one of those users if you expect to startup or shutdown Oracle on that PC.

2) You cannot "copy in" any existing data files into any Oracle database, new or old. Oracle is the only mechanism that can create a data file, and there is no method to "retro-attach" an existing data file to any Oracle database/instance.

3) Can you please confirm why you are focusing so heavily on the "...example.dbf" data file? What do you believe resides in that file that is important to you or your Oracle database?

4) Re-installing Oracle over a previously successful install does not buy you anything. You can CREATE a database entirely independently of your Oracle software install. (In fact, I never use Oracle's default-database creation upon software install since the default-database create carries with it so much crap unnecessary components that we don't need in our typical databases.)

5) If you still have the original database with which you experienced the problem with the "...example.dbf" data file, then you should be able to successfully bring up that database by using the commands I posted earlier.

If you continue to experience problems with any of those commands that I posted, then let's resolve those problems (one-by-one, if necessary) rather than reinstall anything or start over. You must be able to execute those commands in "SQL*Plus /nolog" if you ever expect to get your database up and running again.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[bobhagan]

1) I can sqlplus nolog,
However connect/ as sysdba gives

ORA-01031: insufficient privileges

User "bob" is in the ORA_DBA group, but logging in as bob
gives "Initialization or shutdown in progress"

3/4) I don't care about example.dbf at all. I thought that since that got trashed, and the startup was looking for it, I could copy a new version in, and "fool" the startup process. The only thing I really care about is the stored procedures I had created or modifed since my last backup.

I've really had experience with Foxpro and the SQL Server. There a teach-yourself seemed feasible. With Oracle there are so many things that can go wrong. I'm thankful there are guys like you around.

5) Because I can't log on as anything, Im not sure what to do next - Looks like an impass to me. That's why I reinstalled before.

Thanks again

Bob Hagan

 

[SantaMufasa]

Bob,

So, what you are saying is that Windows log-in, "BOB" does not give you the "insufficient privileges" error message, but instead gives you the "Initialization or shutdown in progress" message, right?

That actually is a good thing at this point as it confirms that you are connected as "sysdba" and are ready to follow the above code scenario. The reason why you were getting "Initialization or shutdown in progress" is because the Oracle database instance was thwarted by your faulty "...example.dbf" the last time you attempted to startup the database. As a result of that problem, Oracle was not able to get from "MOUNT" stage to "OPEN" stage. Since the database could not get "OPEN", you receive the message: "Initialization or shutdown in progress".

You can do the following at the prompt to get the database to a status that you can confirm. After you receive that message, type in the command:

shutdown abort

This is kinda like Indiana Jones, when confronted with the
7-foot Moroccan wielding the 6-foot sword, Indiana just pulled out his six-shooter and took care of business. That is what "shutdown abort" does...no formalities...it's just like pulling the plug on the Oracle instance.

Then, at that point, you can follow my code scenario above, beginning with the "startup" command. Follow those commands and let us know your outcomes.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[bobhagan]

I may not be understanting you. If I attempted to log on in SQLPLUS as sys, system or bob, I got "initialization in progress." With /nolog, at connect / as sysdba I got "insufficient privileges"

I tried shutdown abort anyway and that may have worked - I can also do abort inside enterprise manager. It then says it is shut down, and in SQLPLUS, I get
Oracle not available;
shared memory realm does not exist.

However when I go back to do your script, "connect ..." again goes "insufficient privileges"


Bob Hagan

 

[SantaMufasa]

Okay, then under the circumstances you describe, you must also ensure that your "sqlnet.ora" file (that resides in your ORACLE_HOME/network/admin path) contains this statement:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Make that enhancement and try again to "connect / as sysdba". If successful, then follow the code scenario, above and advise us of your findings.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[bobhagan]

I can connect and startup.

However alter database archivelog;

gives - ORA-00265: instance recovery required, cannot set archivelog mode

Progress.

Bob Hagan

 

[SantaMufasa]

Bob,

Logged in to SQL*Plus "as sysdba", please issue this command:

archive log list

and post the results here.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[bobhagan]

Database log mode No Archive Mode
Automatic archival Disabled
Archive destination C:\oracle\ora92\RDBMS
Oldest online log sequence 61
Current log sequence 63

Bob Hagan

 

[SantaMufasa]

Thanks for the post, Bob.

Here are the givens in your situation:

1) You have a corrupted "...example.dbf" file.
2) Oracle thinks that it needs that file to startup the database.
3) Since Oracle (and you) don't need that file, we want to just get rid of it.
4) One way to get rid of the file is by following my SQL code scenario, above.
5) The above code scenario depends upon being in ARCHIVELOG mode.
6) We cannot get into ARCHIVELOG mode because Oracle says that the database first needs recovery.
7) If we attempt a recovery of the database under the current situation, we need:
a) a pristine backup copy of the "...example.dbf" datafile.
b) a complete set of on-line and archived redo log files that pre-date your backup copy of "example.dbf".
8) Since your database is not in ARCHIVELOG mode, that means that your backup copy of "example.dbf" must be more recent than your oldest on-line redo log file. (If this is the case, then please advise me with a reply to this post. If this is not the case, then proceed to item 9.)
9) If the stars are not aligned for you to do a RECOVER DATABASE as outlined in item 8, above, then you must re-create your database control files. Proceed to the explanation of "How to Re-created Database Control Files," also known as "The Other Method for Getting Rid of '...example.dbf'."

How to Re-created Database Control Files:
-----------------------------------------
1) From the SQL*Plus prompt, determine the User-Dump Destination for your database:

select value from v$parameter where name = 'user_dump_dest';

2) Generate a text file that contains the SQL commands to reconstruct your control files. From the SQL*Plus prompt (with the database at least at MOUNT state):

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

3) Edit the resulting SQL text file.
a) Using your favourite text editor, open the most recently generated trace file (".trc") in the directory you identified in Step 1.
b) Locate the line that begins, "STARTUP NOMOUNT", followed by the command, "CREATE CONTROLFILE REUSE...".
c) Remove all lines prior to "STARTUP NOMOUNT".
d) Most importantly, remove the line of code under the "DATAFILE" section that specifies the "...example.dbf" file.
e) Save the file As "RecreateControlFiles.sql", in your favourite SQL scripts path.

4) From the SQL*Prompt:

SQL> SHUTDOWN ABORT
SQL> @<scripts-path>\RecreateControlFiles

5) Once this script runs successfully, your database should be intact. You can confirm this by doing the follwoing:

SQL> shutdown immediate
SQL> startup

If any of this results in problems (or questions), repost here.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[bobhagan]

Item 9 on:
CREATE CONTROL FILE ...

I get
ORA-01503 CREATE CONTOLFILE failed
ORA-01565 error in identifying file
'%ORACLE_HOME%\DATABASE\DBS1%ORACLE_SID%.ORA'

there is nothing in the Oracle\Ora92\Database\ starting DB1. There is a PWD....ORA, SPFILE....ORA, sqlnet.log, OraDim.Log, and oradba.exe.

After that there are 4 errors saying it doesn't recognize the command 'C\Oracle\oradata\...

and then after the other commands, messages that the database was not mounted and opened.

Progress or not?
Bob Hagan

 

[SantaMufasa]

I'll need to see the contents of the script your are trying to execute. If you can post it here without disclosing any proprietary information, that would be nice. Otherwise, you can e-mail it to me at "dave at dasages dot com".

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[bobhagan]

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PSAAFPR" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'C:\ORACLE\ORADATA\BLAH\REDO01.LOG' SIZE 100M,
GROUP 2 'C:\ORACLE\ORADATA\BLAH\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\ORACLE\ORADATA\BLAH\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'C:\ORACLE\ORADATA\BLAH\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\BLAH\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\BLAH\CWMLITE01.DBF',
'C:\ORACLE\ORADATA\BLAH\DRSYS01.DBF',
'C:\ORACLE\ORADATA\BLAH\INDX01.DBF',
'C:\ORACLE\ORADATA\BLAH\ODM01.DBF',
'C:\ORACLE\ORADATA\BLAH\TOOLS01.DBF',
'C:\ORACLE\ORADATA\BLAH\USERS01.DBF',
'C:\ORACLE\ORADATA\BLAH\XDB01.DBF'
CHARACTER SET WE8MSWIN1252
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\BLAH\TEMP01.DBF' REUSE;

This is Set 2 - resetlog
The first error is on CREATE CONTROLFILE, and that seems to break everything that follows

Thanks again

Bob Hagan

 

[SantaMufasa]

Okay, Bob, here are the next steps we follow. (I regret that the solution, so far, has been elusive, but sometimes troubleshooting problems such as this are a bit dicey and do not have an immediate answer without some initial detective work.)

Your error message causes me to wonder what your settings are for %ORACLE_HOME% and for %ORACLE_SID% since the message did not explicitly list those values...only the variables that represent those values.

To identify the contents of those variables (and to define them if they do not exist) we must go to the registry. Now, I know that many people will say, "Oooo, stay away from the registry...going there can only cause trouble." And I concur, you don't want to mess with the registry unless it is absolutely necessary. And in this case, it is the best place to find these values and fix them if needed.

At a command prompt, access the registry with the command, "regedit". Once the "Registry Editor" appears on your screen, navigate: HKEY_LOCAL_MACHINE...SOFTWARE...ORACLE...HOME0. At that elementary level, look for two variables, "ORACLE_HOME" and "ORACLE_SID". Please post their values here if they exist. If they do not exist, then I believe we are honing in on your problem.

If either of those two variables are missing, we can add them in this fashion:

Place your cursor in the list of HOME0's list of key values, then <right-click> anywhere amongst the existing keys. This produces an action option, "New", upon which you click and choose the "String Value" option. That choice adds a new key at the bottom of the existing list. The default key name is "New Value #1". You change it to either "ORACLE_HOME" or "ORACLE_SID", whichever is missing. To change the name, right click on "New Value #1", and choose "Rename".

Following the rename, place the appropriate value in that key by right clicking the new name and choosing "Modify". In the pop-up window, place the appropriate value in the "Value data" entry, followed by [OK]. Repeat these steps, if necessary, for the second variable, as well.

Close the "Registry Editor", then retry your script that failed earlier.

Let us know if this gets us any closer.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[bobhagan]

The registry entries are:

ORACLE_HOME = C:\Oracle\ora92
ORACLE_SID = BLAH

Both of these appear to be correct.

I'm still getting "Oracle Intelligent Agent has encountered a problem. Do you want to send MS a report?" when I reboot.

The service is not running. Could that have anything to do with this problem, or is that a separate issue?

Thank you for your persistence.
Bob Hagan

 

[SantaMufasa]

Bob, I believe at this point that if you need to pursue a solution to this, that the best avenue presently is via Oracle Technical Support. They can actually "see" your screen over the Internet via their Oracle Collaboration Suite software.

Since you are in the U.S., their toll-free number is 800-223-1711. You will need your Customer Support Identifier (CSI) number when you make the call. Alternately, you can log a Technical Assistance Request (TAR) via their MetaLink site:

http://metalink.oracle.com/,

set up your own personal account, and log a TAR (provided you have a valid CSI number).

I'm sorry that I have no other ideas at this point (especially after you kindly complimented my persistence).

Please do return and update this thread with the solution once OTS helps you with a solution.

Reluctantly throwing in the towel,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[bobhagan]

Ok, I'll explore that.

This problem was the result, I think, of an Internet virus. I've had trouble getting XP to be stable, even after a reformat, so I'm also considering putting in a new drive, and starting over from scratch. It may take me less time to rewrite the stored procedures, than to get the old version recoved. Fortunately this was strictly a development machine so there is no problem with data loss.

Message to self - develop a better backup strategy.

Thanks again for your persistence.

Bob Hagan

 

[bobhagan]

Santa

I installed a new drive, and still wasn't able to get my backup to work. Intelligent Agent stopped crashing, but when I followed your steps, I got exactly the same errors.

I found pieces of the stored procs that I thought I had lost. After that, it just wasn't worth trying to recover the backup.

A remaining question is what is a good setup and backup strategy. I really am doing only development and need to preserve data designs and stored procedures, and sometimes set a new machine. You mentioned that you never set an new database using the Oracle wizards. And for backup I was using TOAD to copy tables and sps as scripts. Every change means remembering to overwrite the previous script. Is there a way to create all in one pass?

No need for a detailed walk-through. If somebody could just point me to something brief to read....