OraFAQ Forum: Server Administration » Pulling data from a db that NO ONE has a password to

Home » RDBMS Server » Server Administration » Pulling data from a db that NO ONE has a password to (Oracle 9.2?)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Pulling data from a db that NO ONE has a password to [message #533600]

Wed, 30 November 2011 16:06

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

Ok, first off, i'm a SQL DBA. I know enough about Oracle to frustrate myself to the point of a coronary.

Here's the futile situation:
We have a client for whom we're trying to do a data conversion. Their data is on an Oracle machine, but they lost their IT staff and NO ONE has any of the passwords for the user accounts on that machine. I can log in with DBSNMP/DBSNMP but it appears to hold insufficient priv's for me to do any sort of export. I'm looking to export their data as an excel file or anyting else that i can pull into SQL.

Is there any way at all, for me to get into this system to pull the necessary data out in the manner i need? When i run the Management app, it offers me two choices. The first is to run "standalone", which i do with the above un/pw combo. At that point, i can't do an export unless i run the managment app against the OMS. No one in their agency knows anything about their OMS, so this prevents me from logging into the mgmt app with the other option, which connects it to the OMS which would, ostensibly, give me the ability to export the data.

Any suggestions or should i just hang myself now?

thanks
james

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533601 is a reply to message #533600]

Wed, 30 November 2011 16:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Any suggestions or should i just hang myself now?
OS name & version?

log onto the OS as the same OS user who owns the Oracle directory & software tree.
open a Command/Terminal window to get to OS command line prompt
Type the lines EXACTLY as below

sqlplus
/ as sysdba

COPY commands above & the results then PASTE all back here.
The expected outcome is that you just logged into the DB as "SYS";
which can do anything & everything within the DB.

HTH & YMMV!

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533602 is a reply to message #533601]

Wed, 30 November 2011 16:57

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

From the best info we have from the client regarding the appropriate user account, i've already tried that and get "insufficient priv's". Chances are, the user acct they gave is not elevated to the appropriate level and they don't know WHO's account would have the necessary access.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533603 is a reply to message #533602]

Wed, 30 November 2011 17:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

when you don't answer my questions, then you have a mystery & I have no clues.

what is OS name & version?

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533609 is a reply to message #533603]

Wed, 30 November 2011 17:57

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

Sorry dude, i didn't see that part.

Windows XP pro.

But mysteries are so much more FUN!

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533610 is a reply to message #533609]

Wed, 30 November 2011 18:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

use Windows SEARCH to find "sqlplus"

after sqlplus.exe is located post FULL directory listing of this file show which OS user owns it.
then you need to log onto the OS as that user (usually "oracle")

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533791 is a reply to message #533610]

Thu, 01 December 2011 05:38

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

Thanks. Ok, i will try that as soon as i get into the office this morning. But....This PC is on a domain. That user account won't be recognized on the domain. Should i be logging on to the local machine with that account?

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533829 is a reply to message #533791]

Thu, 01 December 2011 08:11

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

So i've found the local account owner's login name associated with the sqlplus.exe, but, again, our client does not even know WHO this person is and therefore does not have their password for that local account. So i'm still at square 1, i think.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533831 is a reply to message #533829]

Thu, 01 December 2011 08:18

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Well, you have to log into that machine with that XP user.

If the machine is in a domain, then *usually* the domain administrator is also a local administrator.
So the domain administrator of the domain should be able to change the password of that local XP user.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533832 is a reply to message #533829]

Thu, 01 December 2011 08:19

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Are you sure the DB is installed on that PC and not just the client software?

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533833 is a reply to message #533832]

Thu, 01 December 2011 08:21

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

And if it is on that PC surely you can log on as administrator and change the password?
Or have they lost their PC admin passwords as well?

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533834 is a reply to message #533600]

Thu, 01 December 2011 08:22

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Ok, we all must know...
What company fires all their IT staff or lets the last one leave before getting the information like passwords?
You may not know, but I am really interested.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533835 is a reply to message #533833]

Thu, 01 December 2011 08:23

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

I was able to change the password for that local account and login. However, when i'm logged in as that user and run the enterprise manager console and login there as DBSNMP/DBSNMP (the only account that i can log in with currently) i still get "insufficient privs" to login as sysdba. We're sure the database is on that machine because we can see it there but we don't yet know where the OMS is located, if we even need it at all.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533836 is a reply to message #533834]

Thu, 01 December 2011 08:29

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

I wish i could answer that question, but if i did, i might become one of those who walks out the door with the passwords! All i can say is, be worried, be VERY worried.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533838 is a reply to message #533836]

Thu, 01 December 2011 08:35

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

How are you seeing the DB on that machine?
How exactly are you trying to login as sysdba?

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533840 is a reply to message #533838]

Thu, 01 December 2011 08:37

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

i can see the db, it's tables and schemas, when i log in with DBSNMP/DBSNMP. However, with that account, i cannot do the export of that data which i need to do. I am trying to login / as sysdba via the sqlplus worksheet app.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533842 is a reply to message #533836]

Thu, 01 December 2011 08:37

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Don't run the enterprise manager console, that *always* connects via the network.

Do as BlackSwan said. IN the command prompt, start

sqlplus

and enter the user

/ as sysdba

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533843 is a reply to message #533842]

Thu, 01 December 2011 08:41

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

i immediately get denied with "insufficient privileges" when i try that.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533844 is a reply to message #533843]

Thu, 01 December 2011 08:43

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then you are not logged in as the owner of oracle.
Again are you sure it's actually on that pc? The fact that it has ssqlplus and you can connect to the DB from it doesn't necessarily mean the DB is there. Have a look for a tnsnames.ora file.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533845 is a reply to message #533843]

Thu, 01 December 2011 08:44

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Run the following two commands in the command prompt and post back ALL the output you get:

set 
sqlplus "/ as sysdba"

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533846 is a reply to message #533843]

Thu, 01 December 2011 08:47

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

I don't suppose the user you can use can query V$instance? Double check the host shown there is the physical machine you're on.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533847 is a reply to message #533845]

Thu, 01 December 2011 08:48

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Shawn>set sqlplus "/ as sysdba"
Environment variable sqlplus "/ as not defined

C:\Documents and Settings\Shawn>set
ALLUSERSPROFILE=C:\Documents and Settings\All Users
APPDATA=C:\Documents and Settings\Shawn\Application Data
CLIENTNAME=MCJSV04
CommonProgramFiles=C:\Program Files\Common Files
COMPUTERNAME=MCCWS158
ComSpec=C:\WINDOWS\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Documents and Settings\Shawn
JSERV=C:\oracle\ora92/Apache/Jserv/conf
LOGONSERVER=\\MCCWS158
NUMBER_OF_PROCESSORS=1
OS=Windows_NT
Path=C:\Program Files\Common Files\Microsoft Shared\Windows Live;C:\oracle\ora92
\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin
;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\PROGRA~1\IBM\CLIENT~
1;C:\PROGRA~1\IBM\CLIENT~1\Shared;C:\PROGRA~1\IBM\CLIENT~1\Emulator;
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 15 Model 4 Stepping 1, GenuineIntel
PROCESSOR_LEVEL=15
PROCESSOR_REVISION=0401
ProgramFiles=C:\Program Files
PROMPT=$P$G
SESSIONNAME=RDP-Tcp#17
SonicCentral=C:\Program Files\Common Files\Sonic Shared\Sonic Central\
SystemDrive=C:
SystemRoot=C:\WINDOWS
TEMP=C:\DOCUME~1\Shawn\LOCALS~1\Temp
TMP=C:\DOCUME~1\Shawn\LOCALS~1\Temp
USERDOMAIN=MCCWS158
USERNAME=Shawn
USERPROFILE=C:\Documents and Settings\Shawn
windir=C:\WINDOWS
WV_GATEWAY_CFG=C:\oracle\ora92\Apache\modplsql\cfg\wdbsvr.app

C:\Documents and Settings\Shawn>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Dec 1 08:46:02 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

ERROR:
ORA-01031: insufficient privileges

Enter user-name:

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533848 is a reply to message #533847]

Thu, 01 December 2011 08:50

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

that's a negative on being able to query V$instance.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533850 is a reply to message #533848]

Thu, 01 December 2011 08:52

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Run this in sqlplus as DBSNMP:

select * from session_privs;

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533851 is a reply to message #533848]

Thu, 01 December 2011 08:54

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

NUMBER_OF_PROCESSORS=1

I'm suspecting that's not the server, based purely on the above.

Try

select * from global_name

With that result, exit sql*plus and in the OS type:

tnsping <GLOBAL_NAME>

Hopefully get a host that way.

[Updated on: Thu, 01 December 2011 08:55]

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533852 is a reply to message #533850]

Thu, 01 December 2011 08:54

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

Argh. Any queries i try to run now result in "invalid username/password; logon denied. Warning: you are no longer connected to ORACLE.". This, after i WAS logged in with DBSNMP/DBSNMP.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533853 is a reply to message #533852]

Thu, 01 December 2011 08:55

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Try scott/tiger, you never know, they may have been lax.

Failing that, as suggested, go looking for a TNS names file.

[Updated on: Thu, 01 December 2011 08:56]

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533855 is a reply to message #533853]

Thu, 01 December 2011 08:56

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

tried scott/tiger already. no dice.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533857 is a reply to message #533852]

Thu, 01 December 2011 08:57

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

WJHamel wrote on Thu, 01 December 2011 14:54

Argh. Any queries i try to run now result in "invalid username/password; logon denied. Warning: you are no longer connected to ORACLE.". This, after i WAS logged in with DBSNMP/DBSNMP.

Somethings got confused, may have to try a reboot.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533858 is a reply to message #533857]

Thu, 01 December 2011 08:59

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Do you know the instance name?

If so, try the TNSPING I mentioned above, only reason for the query was in case you didnt know the name.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533859 is a reply to message #533858]

Thu, 01 December 2011 09:03

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

From the TNSNames.ora file:
# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

KAL10002 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = KAL10002)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533860 is a reply to message #533859]

Thu, 01 December 2011 09:07

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

And your OS user is a member of the oracle group in the OS?

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533861 is a reply to message #533859]

Thu, 01 December 2011 09:07

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

OK. So it looks like it's on that PC.
Which user owns tnsnames.ora?

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533862 is a reply to message #533860]

Thu, 01 December 2011 09:07

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

checking that now....

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533863 is a reply to message #533857]

Thu, 01 December 2011 09:08

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

And I'm also suspecting that it's not the server (or the Shawn user is not the normal DBA or a completely set up DBA) because there is no ORACLE_SID variable set in the environment.

Can you look in the services control panel (start -> run -> services.msc) which user the Oracle services are running under? (and if there are none, then we also know that that isn't the server)

edit:

Just read the tnsnames reply.

There are two databases it seems?

So try:

set ORACLE_SID=KAL10002
sqlplus "/ as sysdba"

or

set ORACLE_SID=MODOSE
sqlplus "/ as sysdba"

depending on which instance is the one you want to get into.

[Updated on: Thu, 01 December 2011 09:11]

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533864 is a reply to message #533860]

Thu, 01 December 2011 09:09

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

Roachcoach wrote on Thu, 01 December 2011 09:07

And your OS user is a member of the oracle group in the OS?

That's a big fat negative ghost-rider.

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533865 is a reply to message #533864]

Thu, 01 December 2011 09:10

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Get yourself added and then

sqlplus / as sysba will work Smile

Smile

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533866 is a reply to message #533863]

Thu, 01 December 2011 09:11

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

ThomasG wrote on Thu, 01 December 2011 09:08

And I'm also suspecting that it's not the server (or the Shawn user is not the normal DBA or a completely set up DBA) because there is no ORACLE_SID variable set in the environment.

Can you look in the services control panel (start -> run -> services.msc) which user the Oracle services are running under? (and if there are none, then we also know that that isn't the server)

It shows they are running under the local system account. no username

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533867 is a reply to message #533865]

Thu, 01 December 2011 09:12

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

Roachcoach wrote on Thu, 01 December 2011 09:10

Get yourself added and then

sqlplus / as sysba will work

So you're saying i should add a new local user account, set as admin, login as such, and then try to get into sqlplus /as sysdba?

Report message to a moderator

Re: Pulling data from a db that NO ONE has a password to [message #533868 is a reply to message #533867]

Thu, 01 December 2011 09:12

WJHamel
Messages: 27
Registered: November 2011
Location: Pensacola, FL

Junior Member

By the way, this is all mad-crazy-FUN.

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	EXP_FULL_DATABASE role
Next Topic:	dbam.tablespace_mon(2,TRUE);

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 22:38:17 CDT 2024