connect internal fails - v8.0.4/NT/SAP 4.0B

peterob
peterob used Ask the Experts™
on
This is Oracle 8.0.4 on Windows NT4.0 with SAP R/3 4.0B - our quality-assurance system (QAS), that's been running fine for nearly four years. Suddenly, I'm no longer able to connect or open the database. No recent configuration changes have been made. Here's the session:

---
C:\users>SVRMGR30
Oracle Server Manager Release 3.0.4.0.0 - Production

(c) Copyright 1997, Oracle Corporation.  All Rights Reserved.

Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production
With the Partitioning option
PL/SQL Release 8.0.4.0.0 - Production

SVRMGR> connect internal
Password:
ORA-01017: invalid username/password; logon denied
SVRMGR>
---

The message is misleading because there is no password file (the normal setup for this version with SAP) and no password is need. I've referred to SAP notes, which tell me:

- Unlike normal database users, SYSDBA/SYSOPER/INTERNAL connections are not based on information in the Oracle data dictionary but authenticated via local operating-system groups, which allows connection when the database is stopped.

- Based on membership of these two groups (ORA_<sid>_DBA, ORA_<sid>_OPER), CONNECT INTERNAL automatically executes a SYSDBA or SYSOPER connect.

In our case, the administrative OS user is 'QASadm'; a second user 'SAPServiceQAS' runs SAP background processes. I've confirmed through NT's User Manager that both are members of local groups ORA_QAS_DBA and ORA_QAS_OPER.

I've also checked to ensure the NT service 'OracleServiceQAS' is running. Nevertheless, my
hunch is that this service could be the problem... but don't know what to try.

The last entries in the alert log indicate successful recovery from a stuck archive (but I doubt this is related).

I'd be very grateful for any assistance on this.

Thanks,
Pete
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Pete,

1. Can you establish connection on behalf of some other user?
2. Have you restarted the OracleServiceQAS service?

Author

Commented:
Hi hayrabedian,

Thanks for responding.

1. I've tried both SYSDBA and SYSOPER with their default passwords and both fail. Should I just pick another OS user and add to both groups to try?

2. Yes. I've stopped and restarted OracleServiceQAS several times to no effect -- even rebooted several times.

Thanks,
Peter
Peter,

My proposal was to test the usial connection (not OS based)with some Oracle user like 'SYS' or 'SYSTEM'.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I have to go :( If "normal" connection suceed then the problem is somewhere in the Windows administration...

Author

Commented:
hayrabedian,

Here's what I get:

SVRMGR> connect system/change_on_install
ORA-01034: ORACLE not available
SVRMGR> connect sys/manager
ORA-01034: ORACLE not available

Thanks,
Pete
Daniel StanleyDatabase engineer

Commented:
two other posibilities:

1) The SGA requires more space than was allocated for it.              
2) The operating-system variable pointing to the instance is improperly defined.

no 2 is more likely if you have multiple instances and multiple homes on the same box.

check your ORACLE_SID in the registry if you have more than one instance or home; if you have more than one home make sure your tnsnames.ora files are not conflicting with different connect information.

good luck,
daniels@asix.com  



 

Commented:
I think #2 above is the correct answer.  Rather than worrying about what's in the registry try explicitly setting your SID and starting the instance from dos:

set oracle_sid=your_sid

Then start svrmgr30 and try and start the database as normal.

If this doesn't work I'd try to use oradim80 and delete and recreate the services.

Your TNSNAMES.ora file isn't used when connecting to internal so this isn't the problem.

HTH
Steve...

Commented:
Do the following:
1. start up the oracle service in the winNT.
2. start a command prompt and type: svrmgr30
and then connect internal/pass
3. if you get the 'ORA-01017: invalid username/password; logon denied' then check out the file sqlnet.ora under the O_H/net80/admin - open it with notepad and look for the line: SQLNET.AUTHENTICATION.SERVICES=(NTS)
comment it out like:
#SQLNET.AUTHENTICATION.SERVICES=(NTS)
and try again
Guy

Author

Commented:
Thanks for the suggestions.

The ORA-01017 error persists after:

- Explicitly setting ORACLE_SID=QAS from command prompt (there's only one instance)

- Deleting, then recreating 'OracleServiceQAS' with oradim80.

- Commenting out SLQNET.AUTHENTICATION.SERVICES=(NTS) and attempting several connections, including internal/pass(uncommented afterwards).

Commented:
SVRMGR> connect system/change_on_install
ORA-01034: ORACLE not available
SVRMGR> connect sys/manager
ORA-01034: ORACLE not available
>>

Actually it should be sys/change_on_install and system/manager. Did you just make a typo?


>>SVRMGR> connect internal
Password:
ORA-01017: invalid username/password; logon denied
SVRMGR>
The message is misleading because there is no password file

Ok, so the authentication is being done by the OS, and in this case you should be logged in as the Administrator (QSAdm). Verify he is *still* administrator. Re-Start service. Check if any errors encountered in this.
Verify in task-manager that the instance is actually started. Look at mempory consumption etc to verify this.

Once you are sure instance is started, try to logon using internal or sys and check.

Actually since you recreated the instance using ORADIM, you gave it a new password for internal. So it should have worked then.

>>The last entries in the alert log indicate successful recovery from a stuck archive

Maybe you should post these entries here.


Ali

Author

Commented:
Hi Ali,

- It was a typo; I actually tried sys/change_on_install and system/manager.

- User Manager shows that admin user, QASadm, is a member of Domain Admins, Administrators and the appropriate SAP global admin groups.

- I stopped and restarted service OracleServiceQAS and watched ORACLE80.EXE disappear, then reappear in Task Mangager's process list. Memory usage shows as 1372K. No errors were reported in NT event logs.

- I've copied the last entries in the alert log below:

Thanks for your help,
Peter

---
Mon Oct 28 08:03:57 2002
ARCH: Archival stopped, error occurred. Will continue retrying
ARCH:
 ORA-00255: error archiving log 12 of thread 1, sequence # 5299
ORA-00312: online log 12 thread 1: 'G:\ORACLE\QAS\ORIGLOGB\LOG_G12M1.DBF'
ORA-00312: online log 12 thread 1: 'F:\ORACLE\QAS\MIRRLOGB\LOG_G12M2.DBF'
ORA-00334: archived log: 'F:\ORACLE\QAS\SAPARCH\QASARCH\ARC5299.1'
ORA-19502: write error on file "F:\ORACLE\QAS\SAPARCH\QASARCH\ARC5299.1", blockno 17537 (blocksize=512)
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 112) There is not enough space on the disk.
ORA-00272: error writing archive log F:\ORACLE\QAS\SAPARCH\QASARCH\ARC5299.1
ORA-19502: write error on file "F:\ORACLE\QAS\SAPARCH\QASARCH\ARC5299.1", blockno 17537 (blocksize=512)
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 112) There is not enough space on the disk.

Archiver process freed from errors. No longer stopped.
Mon Oct 28 08:04:59 2002
Thread 1 advanced to log sequence 5303
  Current log# 12 seq# 5303 mem# 0: G:\ORACLE\QAS\ORIGLOGB\LOG_G12M1.DBF
  Current log# 12 seq# 5303 mem# 1: F:\ORACLE\QAS\MIRRLOGB\LOG_G12M2.DBF
Mon Oct 28 08:06:06 2002
Thread 1 advanced to log sequence 5304
  Current log# 13 seq# 5304 mem# 0: G:\ORACLE\QAS\ORIGLOGA\LOG_G13M1.DBF
  Current log# 13 seq# 5304 mem# 1: F:\ORACLE\QAS\MIRRLOGA\LOG_G13M2.DBF
Mon Oct 28 08:06:53 2002
Thread 1 advanced to log sequence 5305
  Current log# 14 seq# 5305 mem# 0: G:\ORACLE\QAS\ORIGLOGB\LOG_G14M1.DBF
  Current log# 14 seq# 5305 mem# 1: F:\ORACLE\QAS\MIRRLOGB\LOG_G14M2.DBF

Commented:
Idon't think it is related to the user/pass issue.
there was a diskspace problem and I guess you solved it.
now verify that in the init.ora file, there is an entry:
remote_login+passwordfile=exclusive
then recreate the password file: do the following:
start dos and type: ORAPWD80 file=<ORACLE_HOME>\Database\pwd<SID> password=oracle
     
      where <ORACLE_HOME> is where the oracle is installed
      and <SID> is the DB SID like pwdqas.ora

now try to connect again with user=internal and password=oracle
Guy

Author

Commented:
Hi Ali,

- It was a typo; I actually tried sys/change_on_install and system/manager.

- User Manager shows that admin user, QASadm, is a member of Domain Admins, Administrators and the appropriate SAP global admin groups.

- I stopped and restarted service OracleServiceQAS and watched ORACLE80.EXE disappear, then reappear in Task Mangager's process list. Memory usage shows as 1372K. No errors were reported in NT event logs.

- I've copied the last entries in the alert log below:

Thanks for your help,
Peter

---
Mon Oct 28 08:03:57 2002
ARCH: Archival stopped, error occurred. Will continue retrying
ARCH:
 ORA-00255: error archiving log 12 of thread 1, sequence # 5299
ORA-00312: online log 12 thread 1: 'G:\ORACLE\QAS\ORIGLOGB\LOG_G12M1.DBF'
ORA-00312: online log 12 thread 1: 'F:\ORACLE\QAS\MIRRLOGB\LOG_G12M2.DBF'
ORA-00334: archived log: 'F:\ORACLE\QAS\SAPARCH\QASARCH\ARC5299.1'
ORA-19502: write error on file "F:\ORACLE\QAS\SAPARCH\QASARCH\ARC5299.1", blockno 17537 (blocksize=512)
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 112) There is not enough space on the disk.
ORA-00272: error writing archive log F:\ORACLE\QAS\SAPARCH\QASARCH\ARC5299.1
ORA-19502: write error on file "F:\ORACLE\QAS\SAPARCH\QASARCH\ARC5299.1", blockno 17537 (blocksize=512)
ORA-27072: skgfdisp: I/O error
OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 112) There is not enough space on the disk.

Archiver process freed from errors. No longer stopped.
Mon Oct 28 08:04:59 2002
Thread 1 advanced to log sequence 5303
  Current log# 12 seq# 5303 mem# 0: G:\ORACLE\QAS\ORIGLOGB\LOG_G12M1.DBF
  Current log# 12 seq# 5303 mem# 1: F:\ORACLE\QAS\MIRRLOGB\LOG_G12M2.DBF
Mon Oct 28 08:06:06 2002
Thread 1 advanced to log sequence 5304
  Current log# 13 seq# 5304 mem# 0: G:\ORACLE\QAS\ORIGLOGA\LOG_G13M1.DBF
  Current log# 13 seq# 5304 mem# 1: F:\ORACLE\QAS\MIRRLOGA\LOG_G13M2.DBF
Mon Oct 28 08:06:53 2002
Thread 1 advanced to log sequence 5305
  Current log# 14 seq# 5305 mem# 0: G:\ORACLE\QAS\ORIGLOGB\LOG_G14M1.DBF
  Current log# 14 seq# 5305 mem# 1: F:\ORACLE\QAS\MIRRLOGB\LOG_G14M2.DBF

Author

Commented:
Guy,

A password file has never existed for this system. Indeed, none of our four SAP/Oracle/NT systems use Oracle password files for administrative authentication; it's done by the operating system.

Until now, there has never been a problem connecting with internal, sys or system. The puzzling thing is why this is no longer possible when nothing seems to have changed (the stuck archive - probably unrelated - is the only recent event of note).

Thanks,
Peter

Author

Commented:
Guy,

A password file has never existed for this system. Indeed, none of our four SAP/Oracle/NT systems use Oracle password files for administrative authentication; it's done by the operating system.

Until now, there has never been a problem connecting with internal, sys or system. The puzzling thing is why this is no longer possible when nothing seems to have changed (the stuck archive - probably unrelated - is the only recent event of note).

Thanks,
Peter

Commented:
You tried recreating the service using oradim right ?
therefore the only solution I can think of is creating the
password file. Is it possible that the password file existed without anyone knowing about is and somehow it got deleted/corrupted ?
try search the HDD for pwd<SID>.ora.
that is the only thing I can think of right now....
Guy

Author

Commented:
Guy,

A password file has never existed for this system. Indeed, none of our four SAP/Oracle/NT systems use Oracle password files for administrative authentication; it's done by the operating system.

Until now, there has never been a problem connecting with internal, sys or system. The puzzling thing is why this is no longer possible when nothing seems to have changed (the stuck archive - probably unrelated - is the only recent event of note).

Thanks,
Peter

Author

Commented:
Guy,

A password file has never existed for this system. Indeed, none of our four SAP/Oracle/NT systems use Oracle password files for administrative authentication; it's done by the operating system.

Until now, there has never been a problem connecting with internal, sys or system. The puzzling thing is why this is no longer possible when nothing seems to have changed (the stuck archive - probably unrelated - is the only recent event of note).

Thanks,
Peter

Author

Commented:
Yes, Guy, I recreated OracleServiceQAS with oradim80. And a search of the drive for pwd<SID>.ora comes up empty (as expected). I'm quite sure it has never existed and isn't necessary.
Thanks,
Pete

Commented:
When using the oradim utility and you specify a password a password file is automatically created.  It should be in the same directory where you state your pfile is located when running the oradim80 command.

I would do like gsalman said and set

remote_login_passwordfile=exclusive

In your init.ora file then delete and recreate the service using oradim80 specifying a password which will reset the internal password.

This should allow you to connect to internal and bring the database up.  Then you can figure out what changed.

Steve...

Author

Commented:
Yes, Guy, I recreated OracleServiceQAS with oradim80. And a search of the drive for pwd<SID>.ora comes up empty (as expected). I'm quite sure it has never existed and isn't necessary.
Thanks,
Pete

Author

Commented:
Thanks Steve, here's what I've done:

Added remote_login_passwordfile=exclusive to initQAS.ora

D:\orant\BIN>oradim80 -delete -sid QAS -srvc OracleServiceQAS

D:\orant\BIN>oradim80 -new QAS -srvc OracleServiceQAS -intpwd sap -maxusers 10 -startmode auto -pfile d:\orant\database\initQAS.ora
(Is this syntax correct? It created the service OK, but I was expecting to find a password file in d:\orant\database. It didn't create one.)

Started service OracleServiceQAS

SVRMGR> connect internal/sap
ORA-01017: invalid username/password; logon denied
SVRMGR>

Pete

Commented:
I am curious to know if you tried to CONNECT / AS SYSDBA? First make sure you have REMOTE_LOGIN_PASSWORDFILE = NONE in the init*.ora and SQLNET.AUTHENTICATION_SERVICES = (NTS) in the SQLNET.ORA and then try connect / as sysdba


You can also try to recreate the service as
oradim80 -new QAS -srvc OracleServiceQAS -intpwd / -maxusers 10 ....


I am not sure of this, but I think 8.0 used a file called start*.cmd file to actually open the database. This file contained the internal password (again not sure of this, but have a look anyways).



Ali

Author

Commented:
Ali,
initQAS.ora now has REMOTE_LOGIN_PASSWORDFILE = NONE and sqlnet.ora shows SQLNET.AUTHENTICATION_SERVICES = (NTS).

I deleted and recreated the service as you suggested with -intpwd / (I presume this gives the default: no password) and started it.

SVRMGR> connect/as sysdba
ORA-01031: insufficient privileges

The only start* program is see is STARTUP.EXE, which I think is a SAP executable.

MORE INFO (relevant?): Two stuck archives occurred in quick succession just before the problem occurred (backup problems prevented their clearing). After the first, the database resumed when space was cleared; but it did not after the second (a few days later). Attempts to restart it using SAPDBA, the SAP utility that calls SVRMGR30, failed. At that point I shut down and restarted the server and couldn't connect.

Thanks again for your help...Pete

Author

Commented:
All,

I'm off on vacation and may not be able to check in here again till Nov 24. Thanks for your help, and I'd be grateful for any more suggestions.

Pete

Author

Commented:
All,

I'm off on vacation and may not be able to check in here again till Nov 24. Thanks for your help, and I'd be grateful for any more suggestions.

Pete

Commented:
>>The only start* program is see is STARTUP.EXE, which I think is a SAP executable.

Sorry meant strt*.cmd.

Also, I think since it is an os-authenticated system, I think you do not need to specify the internal password. So suggest create instance w/o the intpwd parameter like:

oradim80 -new QAS -srvc OracleServiceQAS -maxusers 10 ....



Ali
Commented:

Check your services and ensure the that listener that is running is 'listener80' and not just 'listener'.

Both are often installed, but 'connect internal' will not work on 8.0.4 unless the 'listener80' listener is running.

Author

Commented:
Ali,

- There's strtdb80.exe under \orant\bin. I tried running it from the command line (without really knowing what it does) while watching Task Manager. Nothing seemed to happen.

- I recreated OracleServiceQAS without the -intpwd param, but the problem persists.

Thanks again... Pete

Author

Commented:
jkstill,
Yes, the listener80 is running.
Thanks... Pete


No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
accept jkstill's comment as an answer
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial