How to access an Oracle database from linux

Question

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.

• Colin 't Hart: Which Oracle version? Please edit the question and tag as appropriate.
• mustaccio: Not sure what you're now asking; sqlplus / as sysdba is exactly running SQL*Plus with OS creds (of oracle). Are you asking how to create a user in Oracle? Do some basic research and post another question if you're stuck.
• William Robertson: @JohnThompson once connected with sysdba privileges you have the power of god over the entire database, including all tables in all schemas. You can delete any data and reset any password. You don't need any further connect step.
• John Thompson: hey, thanks, i resolved the issue, my problem was that when using the command sqlplus / as sysdba it would not allow me to execute any sql, when i swapped to sqlplus xyz as sysdba (where xyz is the schema name) it works fine, i now have control over the DB again! many thanks to all
• Vérace: @JohnThompson - please answer your own question - this may help those who have the same/similar issues!

Answer 1

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.

• Colin 't Hart: serverfault.com/questions/37628/…
• Michael Kutz: You forgot "setup environment variables". I do this via source /usr/local/bin/oraenv
• mustaccio: @MichaelKutz that would be automatically executed for user oracle in a login shell, if the system is correctly set up.
• EdStevens: @mustaccio - Michael brings up a good point. If you first log in as 'root', then 'su' to become oracle, it will depend on the exact syntax of your 'su' command. 'su oracle' will not invoke oracle's login shell, whereas 'su - oracle' will invoke it. And of course even that assumes that the system was set up correctly and that the database of interest is the one set in the login shell. The only sure way is to call 'oraenv' and respond to the prompt accordingly.
• mustaccio: Yes, and you also need to make sure your computer is plugged in and turned on, and your keyboard attached, etc. Please feel free to edit or downvote the answer as you see fit.