Linux – How to access an Oracle database from linux

linuxoracleoracle-12c

We have a legacy Oracle database hosting all of the information for a now shutdown website, we need to keep the data on the DB available for a period of time due to GDPR etc. We have had some requests from previous users to have there information deleted.

The problem…

This database was last in use 5-6 years ago (for legal reasons we need to maintain access for 10 years), none of the staff still with the company have DB accounts capabale of performing delete actions, we can only read the data.

We have full root access to the linux server hosting the DB but otherwise we only have DB accounts capable of reading information. Old accounts which are able to perform deletes etc exist on the DB but the passwords for these are lost in time.

Is there any way by which we can create a new database user with suitable access? or to some how trigger a pwd reset of an old DB User (given our limited access to the DB itself)?

Edited for clarity:

oracle linux 6.5,
oracle db 12.1.0.2

After using su – oracle (and ensuring the machine is powered on) i can now run sqlplus!

is there something i need to do to get sqlplus to run using the os creds? I found some online info around this, which suggested some setting in the sqlnet.ora file, but the only instance of this file on the server is the sample file which i presume is not the correct one to be updating e.g.

/u01/app/oracle/product/12.1.0.2/network/admin/samples/sqlnet.ora

I tried:

sqlplus / as sysdba

which gives me the SQL prompt, but if i attempt to run:

conn /

Then it responds with invalid username/pwd – i assume conn is meant to connect you to an actual db (not sure, found it in some blog)

final edit:

fixed my final issue, needed to include the schema name when connecting! thanks all.

Best Answer

Since you have root access to the server, you can su to the Oracle instance owner (typically oracle), connect to the database locally using OS authentication (e.g. sqlplus / as sysdba), and create new (or modify existing) Oracle users or roles as needed.

In case you're not familiar how su and login shells work on Linux, you will need to run su - oracle to ensure the Oracle user profile gets executed, setting up the shell environment properly. If the database was installed by an experienced person, that would include configuring all the necessary Oracle environment variables, allowing you to call its utilities, such as sqlplus.

If an attempt to run sqlplus fails with "command not found" or something similar, try to set the Oracle environment yourself by running, as the oracle user, source /usr/local/bin/oraenv. Note that oraenv may be elsewhere, not necessarily in /usr/local/bin, depending on your Oracle version, Linux flavour, and on how Oracle was actually installed.

Thanks to Michael Kutz and EdStevens for valuable critique.

Related Question