Urgent - Corrupt datafile or Invalid PWD file

[THoey]

Oracle 8.1.7 on Win NT

I was doing a SQL*Loader load and for some reason, my DB went down. This has caused a corrupted datafile. If I try to open my DB from DBA Studio, I get the following:

ORA-0113: File 13 needs media recovery
ORA-0110: Data file 13: "D:\Oracle\Oradata\SATR\Eventlog_IDX.ora"

If I hit the OK button, the DB is only in the mounted phase.

If I go through SQL*Plus as INTERNAL and issue the startup command, it looks like it is going to startup, but then gives me an invalid password file:

Total System Global Area 238319644 bytes
Fixed Size 75804 bytes
Variable Size 80879616 bytes
Database Buffers 157286400 bytes
Redo Buffers 77824 bytes
ORA-01991: invalid password file 'D:\oracle\ora817\DATABASE\PWDsatr.ORA'

I have looked at doing recovery, but the error comes back that Oracle is not available.

Any ideas? Sorry, but as with everything, this is urgent... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[spenglerr]

sorry for the late answer,

1) this is a very strange behaviour, since ORA-113 normally is &quot;protocol name <string> is too long&quot;.

2) Is the password-file still existing or has it vanished?
did you change the REMOTE_LOGIN_PASSWORDFILE in init.ora before?

3) I think you should look for it with the use of Oracle Metalink

If 3 is not possible:

------------------------

Doc ID: Note:19277.1
Subject: OERR: ORA 1991 invalid password file
Type: REFERENCE
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 26-SEP-1997
Last Revision Date: 10-JUN-1999




Error: ORA 1991
Text: invalid password file <name>
-------------------------------------------------------------------------------
Cause: The specified password file name is invalid.
Action: Specify a correct password file name.

*** Important: The notes below are for experienced users - See [NOTE:22080.1]


Diagnosis:

- Is REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE and trying to recreate
the database/controlfile (Workaround in bug 254667)

- set remote_login_passwordfile = false & bounce instance
- create control file
- create password file
- reset remote_login_passwordfile to original value & bounce instance

OR see Workaround in [BUG:254667]
.

------------------------------------

Another one:

Doc ID: Note:114384.1
Subject: NT: Password File and connect internal password issues
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 04-JUL-2000
Last Revision Date: 18-MAR-2002


Purpose:
~~~~~~~~
This article is intended to document some frequently encountered issues with
password files and the ORAPWD-utility on NT


Scope & Application:
~~~~~~~~~~~~~~~~~~~~
The scope of this is article is the general use of password file use on NT

Password file and internal password issues on NT
------------------------------------------------
Versions: 7.3.x - 8.0.x - 8.1.x - 9.0.x
7.3.x ORAPWD73 ORADIM73
8.0.x ORAPWD80 ORADIM80
8.1.x ORAPWD ORADIM
9.0.x ORAPWD ORADIM

in general ORAPWDxx ORADIMxxx

**Note: You MUST be logged into the Windows NT system as the user Administrator
-OR- a user within the Windows NT Administrative Group with full
administrative rights to perform the following steps

I) Usage
a) Connecting Without a Password
b) Password file
c) ORAPWDxx
d) Adding & Removing users to the password file
e) Listing users recorded in the password file
f) Resetting/Changing the password of the user internal
g) Updating Windows NT registry when password file moved or renamed
h) Finding back the internal password (7.3.4 - 8.0.x)

II) Most Common Problems
1) OPW-00005: File with same name exists - please delete or rename
2) ORA-1017 or ORA-1031 when trying to connect as internal
3) ORA-1996 when trying to grant sysdba or sysoper to user
4) By default in 8.1.x, a new database will use Windows NT Native
Authentication by having the following already set in the sqlnet.ora file.

Usage:
~~~~~~
Connecting as internal or sysdba/sysoper can be setup in 2 different ways:

- to connect without the necessity to provide a password
- to connect with the necessity to provide a password


a) Connecting Without a Password:
---------------------------------
Note 77665.1: NT Connecting Without a Password as a Privileged Database User


b) Password file:
-----------------
When the &quot;init.ora file&quot; parameter remote_login_passwordfile is set to EXCLUSIVE
or SHARED, Oracle can have a password file to maintain

- the password of internal
- the list of users and their passwords that have been granted administrator privileges

The name of the password file has the format: &quot;pwd<SID>.ora&quot; and is situated by
default in the <ORACLE_HOME>\Database-directory.

The password is automatically created in the <ORACLE_HOME>\Database directory
when an instance is created with the instance manager ORADIMxx.

For example (8.1):
D:\> ORADIM -new -sid ORCL -intpwd ORACLE -startmode auto -pfile d:\orant\database\initorcl.ora

will automatically create a file D:\ORANT\DATABASE\PWDORCL.ORA


c) ORAPWDxx:
------------
ORAPWDxx is a specific command-line utility for password file creation.
ORAPWDxx is automatically installed with the RDBMS utilities


Syntax:
-------
When you type ORAPWDxx at the DOS-prompt, it will enlist the syntax to use it

D:\> ORAPWDxx
Usage: orapwd file=<fname> password=<password> entries=<users>

where
file - name of password file (mand),
password - password for SYS and INTERNAL (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.

File:
this option lets you specify the name of the password file. It should
containt the full path, <ORACLE_HOME>\DATABASE, and be of this format
PWD<SID>.ORA. Its content is encrypted.

Password:
sets the password for internal.

Entries:
specifies the maximum number of distinct SYSDBA and SYSOPER USERS

Example (8.1):
D:\> ORAPWD file=D:\ORANT\DATABASE\PWDORCL.ORA password=ORACLE


d) Adding & Removing users to the password file:
------------------------------------------------
Adding users to password file is done by granting them the sysdba or sysoper
system privilege.

SVRMGR> grant sysdba to system;
SVRMGR> grant sysoper to test_user;
SVRMGR> grant sysoper to test_user2;

Removing users from the password file is done by revoking the corresponding privileges
from the specific users;

SVRMGR> revoke sysoper from test_user2;


e) Listing users recorded in the password file:
-----------------------------------------------
There is a view defined in the database that will give you the list of the users
recorded in the password file: v$pwfile_users.


SVRMGR> describe v$pwfile_users;
Column Name Null? Type
------------------------------ -------- ----
USERNAME VARCHAR2(30)
SYSDBA VARCHAR2(5)
SYSOPER VARCHAR2(5)

SVRMGR> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
INTERNAL TRUE TRUE
SYSTEM TRUE FALSE
TEST_USER FALSE TRUE



f) Resetting/changing the password of the user internal:
--------------------------------------------------------
There are 3 common ways to change to password of internal:

1) Recreate the passwordfile (Read Warning 1 & 2):

You can recreate the password file using the ORAPWDxx command by which you can
specify the password for internal.
Before running this command to create the new password file, make sure to delete
the old password file.
Otherwise, the ORAPWDxx utility will give an error message.

D:\> ORAPWD file=D:\ORANT\DATABASE\PWDORCL.ORA password=<password> entries=5

Verify if the new file has been created.

The passwordfile might be hidden. Hidden files can be seen:
on the DOS-prompt by type:
c:\> dir /A:H

in Windows NT explorer:
use the Windows Explorer View/Option 'show all files' to see the file.


Remark:
-------
If the value of the &quot;remote_login_passwordfile&quot; parameter in the &quot;init.ora&quot; is
EXCLUSIVE then you must shutdown your database before recreating the password file

Failure to do so will result in being unable to connect as internal as long as
you don't stop the database by for example stopping services.

2) Alter the password of the user 'SYS' (Read Warning 1)

a) Verify the values of the &quot;remote_login_passwordfile&quot; in the
&quot;init.ora&quot; parameter file.

If its values is exclusive continue with e) if Shared continue with b)

b) Stop the database if it is started

c) Change the &quot;remote_login_passwordfile&quot; in the Init<SID>.ora to
&quot;exclusive&quot;.

d) Startup the database.

e) Alter the user SYS to a new password, now also the password for internal.

3) Use ORADIMxx (Read Warning 2)

You could drop the instance and recreate it with a new password.

Suppose the SID in question is ORCL:

a) You shutdown the database and stop the services.
b) D:\> ORADIMxx -delete -sid ORCL
c) D:\> ORADIMxx -new -sid ORCL -intpwd <new password> -startmode
auto -pfile D:\ORANT\DATABASE\INITORCL.ORA


Warning:
~~~~~~~~
1) (Methods 1 and 2) Changing the password of the internal might prevent
your database from starting up automatically in Oracle7 7.3.x and Oracle8
8.0.x.
When you create your instance with the instance manager and configure it to
startup automatically.

(ORADIMxx -new -sid ORCL -intpwd ORACLE -startmode AUTO -pfile
D:\ORANT\DATABASE\INITORCL.ORA),

a file <ORACLE_HOME>\DATABASE\strt<sid>.cmd is generated.
That file contains (hardcoded) the password of internal.
If the password in there does not match the actual password of internal, the
instance will fail to startup automatically

When you change the password of internal in one of the above described ways and
if you still want your database to start up automatically, you need to adjust
the password manually.

2) Methods 1 and 3 can result in loosing all previously registered sysdba and
sysoper users.
If you had granted specific users those privileges again, you will need to grant it
again.


g) Updating Windows NT registry when password file moved or renamed:
--------------------------------------------------------------------
You either renamed or moved your password file.
The Registry has an entry to denote the password file.
When the file is moved or renamed the Registry must be updated.


Solution Description:
~~~~~~~~~~~~~~~~~~~~~

Caution:
--------
Editing the REGISTRY could cause severe damage to Windows NT.
If you are not familiar with the Registry, please consult with your System
Administrator.
A damaged registry could require a complete reload of the NT Operating System.

1. Run REGEDT32.EXE to open the Registry.
2. Click on the HKEY_LOCAL_MACHINE on Local Machine hive.
3. Click on the SOFTWARE folder.
4. Click on the ORACLE folder.

If the database is created in the default home, skipt step 5.

5. If the database is not created in the default home, but in HomeXX
(XX is a number from 1 to the number of homes installed on your Server)
Click on the HomeXX folder.

6. Update or Create ORA_<SID>_PWFILE (where <SID> is the database SID).
String with the correct name and path of the password file.

After changing the registry entry, the OracleService<SID> service will need to
be stopped and restarted for the change to take affect.


Solution Explanation:
~~~~~~~~~~~~~~~~~~~~~
The HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORA_<SID>_PWFILE or
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HomeXX\ORA_<SID>_PWFILE
registry entry is used to store the name and location of the password file.


h) Finding back the internal password (7.3.4 - 8.0.x)
-----------------------------------------------------
When you're using Oracle 7.3.4 or 8.0.x, the ORADIMxx command creates the
password-file: pwd<SID>.ora that contains the encrypted internal password.
If you're using the automatic startup option (-startmode auto), there is also a file
strt<SID>.cmd created that contains the not-encrypted password of internal.



II) Most Common Problems
------------------------
1) You're trying to recreate the Password file with ORAPWDxx and you get the
following error:

D:\> ORADIMxx file=D:\ORANT\DATABASE\PWDORCL.ORA password=ORACLE

OPW-00005: File with same name exists - please delete or rename

Solution:
There is already a file with the same name in the specified directory.
Remove or rename that specific file, before creating the new one.

The passwordfile might be hidden.
Hidden files can be seen:

on the DOS-prompt by type:
c:\> dir /A:H

in Windows NT explorer :
use the Windows Explorer View/Option 'show all files' to see the file.

2)ORA-1017 or ORA-1031 when trying to connect as internal

Note 114399.1: WINNT: ORA-1017 WHEN CONNECTING AS INTERNAL ON NT
Note 114401.1: WINNT: ORA-1031 WHEN CONNECTING AS INTERNAL ON NT

3) ORA-1996 when trying to grant sysdba or sysoper to user
Note 19282.1 : OERR ORA 1996 GRANT failed password file is full
Note 1032464.6: ORA-1994 WHEN GRANTING SYSDBA TO A USER

You must create a larger password file to be able to contain more entries and
re-grant the privileges to all of the users.

4) By default in 8.1.x, a new database will use Windows NT Native Authentication
by having the following already set in the &quot;sqlnet.ora&quot; file:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

This will allow any privileged user defined in the NT user group (named
ORA_<sid>_DBA) to login to database without providing a password.
To disable this feature and force all users to provide a password, simply
comment out the above in the sqlnet.ora file:

#SQLNET.AUTHENTICATION_SERVICES = (NTS)

Note 77665.1: NT Connecting Without a Password as a Privileged Database User

Oracle8i Administrator's Guide Release 8.1 Chapter 1


Related Documents:
~~~~~~~~~~~~~~~~~~
Note 19282.1 : OERR ORA 1996 GRANT failed password file is full
Note 30796.1 : PARAMETER: INIT.ORA: REMOTE_LOGIN_PASSWORDFILE
Note 50508.1 : ALERT &quot;CONNECT INTERNAL&quot; Syntax to be DeSupported
Note 77665.1 : NT: Connecting Without a Password as a Privileged Database User
Note 114399.1 : NT: ORA-1017 WHEN CONNECTING AS INTERNAL ON NT
Note 114401.1 : NT: ORA-1031 WHEN CONNECTING AS INTERNAL ON NT
Note 1022992.6: NT: FORGOT PASSWORD FOR CONNECT INTERNAL
Note 100867.1 : Troubleshooting failures/password problems within Oracle FailSafe
Note 1027964.6: LCC-00161 AND ORA-01031 ON STARTUP
Note 1032464.6: ORA-1994 WHEN GRANTING SYSDBA TO A USER

Oracle8 Administrator's Guide Release 8.0 - Chapter 1 The Oracle Database Administrator
Oracle8i Administrator's Guide Release 8.1 - Chapter 1 The Oracle Database Administrator
.

-------------------------------------------

and last but not least:


Doc ID: Note:1036675.6
Subject: ORA-00603 -00376 -01110 -01501 -01991 TRYING TO START NEW DATABASE
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 06-OCT-1997
Last Revision Date: 19-APR-2001


Problem Description:
====================

When trying to start a newly created database you receive the following errors:

ORA-00603: Oracle server session terminated by fatal error
Cause: An Oracle Server session is in an unrecoverable state.
Action: Log in to Oracle again so a new server session will be
created automatically. Examine the session trace file
for more information

ORA-00376: file 1 cannot be read at this time
Cause: An attempt was made to read from a file that is not
readable. The most likely cause is that the file is
off line.
Action: Check the state of the file. Bring the file online,
if necessary.

ORA-01110: data file 1: %s
Cause: This message reports the filename involved with other
messages.
Action: See the associated messages for a description of the
problem.

ORA-01501: CREATE DATABASE failed
Cause: An error occurred while executing the CREATE DATABASE
statement.
Action: Refer to the diagnostic information in the accompanying
message stack.

ORA-01991: invalid password file %s
Cause: The specified password file name is invalid.
Action: Specify a correct password file name.


Solution Description:
=====================

You need to either recreate the password file, or set
remote_login_password_file equal to shared.


Recreate the password file:
---------------------------

1. Change to the &quot;$ORACLE_HOME/dbs&quot; directory

% cd $ORACLE_HOME/dbs

2. Create the password file using the ORAPWD command

% orapwd file=orapw<SID> password=<actual_password> entries=<number>


Note: This command will create a file named orapw<SID> in the
&quot;$ORACLE_HOME/dbs/orapw<SID>&quot; directory.



Set remote_login_password_file=shared:
--------------------------------------

1. Change to the &quot;$ORACLE_HOME/dbs&quot; directory

% cd $ORACLE_HOME/dbs

2. Edit the &quot;init<SID>.ora&quot; file and set REMOTE_LOGIN_PASSWORDFILE=SHARED


Explanation:
============

The reason this happens is because you are using an old password file
(previous database) for the new database. This cannot be done if
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE in your init.ora. In exclusive mode each
password file is associated with one instance (control file) only. When you
create a new database a new control file is created that has a different time
stamp (the time stamp encoded in the password file is old). The error is
caused by rerunning the create database statement while in exclusive mode.


References:
===========

Search Words:
=============

ORA-603 ORA-376 ORA-1110 ORA-1501 ORA-1991