Oracle DBA Lesson 16 — Choose the Right Database Tool

The job picks the tool. SQL*Plus does not create a database. DBCA does not apply redo. A listener can answer while the instance is down. The SQL*Plus version banner is the client you just started, not a database you have not connected to.

Oracle DBA Lesson 16 — Choose the Right Database Tool

Which executable actually runs

On Linux the shell picks a file from PATH. command -v prints that file. sqlplus -V prints the client release and exits. It opens no session, so it cannot know the release of a remote database.

command -v sqlplus
sqlplus -V
echo "$ORACLE_HOME"

Connect, then ask the instance. VERSION_FULL is the database release, including the RU.

SELECT instance_name, version_full, status
FROM   v$instance;

If command -v sqlplus and $ORACLE_HOME/bin/sqlplus are different files, the shell is not using the home you exported. Call the binary by full path, or fix PATH, before you create or recover anything.

TWO_TASK changes a local sqlplus / as sysdba into a network login. Unset it when you mean a bequeath connection to ORACLE_SID. A bequeath login also requires membership in the OS DBA group. A TNS login does not.

Query and script

SQL*Plus is installed with the database server and with the Oracle client. Use it when the work must run again: fixed settings, a query, a spool file.

SET PAGESIZE 200 LINESIZE 200 TRIMSPOOL ON FEEDBACK OFF
SPOOL /tmp/sessions.lst
SELECT sid, serial#, username, status, machine, program
FROM   v$session
WHERE  type = 'USER'
ORDER  BY username, sid;
SPOOL OFF

Read V$SESSION as a user that can see other sessions. Without that access the view returns only your own row.

SQLcl is a separate Java program. The command is sql, and current releases need Java 17 or 21. It runs existing SQL*Plus scripts. It also keeps statement history and supports SET SQLFORMAT csv (and json, ansiconsole). Check for it with command -v sql. A database home is not required to contain it.

SQL Developer is the graphical worksheet and the object browser. Use it to open a table and try a statement. A scheduled job stays in a script, run by SQL*Plus or SQLcl. SQL Developer stores connection definitions on the workstation, not in the database.

Create from the home you mean

DBCA lives in $ORACLE_HOME/bin. The database it creates runs on that home. Start the binary from the home that must own the database. The first dbca on PATH can be a different release.

echo "$ORACLE_HOME"
"$ORACLE_HOME/bin/dbca" -silent -createDatabase \
  -templateName General_Purpose.dbc \
  -gdbname oradb.example.com \
  -sid oradb \
  -createAsContainerDatabase true \
  -numberofPDBs 1 \
  -pdbName ORCLPDB \
  -characterSet AL32UTF8

DBCA prompts for the SYS and SYSTEM passwords. Leave them off the command line so they do not land in shell history. The same program deletes a database and changes options. It does not back up datafiles and it does not apply redo.

Backup, restore, and recover

RMAN takes the physical backup. Connect to the target with OS authentication when you are on the host:

rman target /

BACKUP DATABASE PLUS ARCHIVELOG;

RESTORE DATABASE copies files out of the backup and puts them back on disk. RECOVER DATABASE applies archived and online redo so those files reach a consistent SCN. A restore that stops there leaves the datafiles at the backup checkpoint. Complete recovery needs the redo generated after that backup.

RESTORE DATABASE;
RECOVER DATABASE;

Data Pump (expdp, impdp) unloads and loads logical data. It is not a physical backup of the datafiles, and it is not a recovery.

Connection problems start at the listener

STATUS shows whether the listener process answered, which addresses it uses, and a short services summary. SERVICES adds the handlers.

lsnrctl status
lsnrctl services

Read the instance status in that summary:

• READY — the instance registered itself and can accept connections.
• BLOCKED — registered, and not accepting connections.
• RESTRICTED — the instance is in restricted session. The listener refuses ordinary clients.
• UNKNOWN — the service is a static entry in listener.ora. The listener does not know if the instance is up. That line stays after a shutdown.

A successful lsnrctl status proves the listener process answered. It does not prove V$INSTANCE.STATUS is OPEN. Confirm that in a session, or read the alert log when you cannot connect.

Read the alert log in the right ADR home

ADRCI is the diagnostic command line for the Automatic Diagnostic Repository: alert log, incidents, and trace. A host usually has more than one ADR home (database, listener, ASM). Set the home first. With more than one home current, SHOW ALERT stops and asks you to choose.

adrci

show homes
set homepath diag/rdbms/oradb/oradb
show alert -tail 50 -term
show incident

SHOW ALERT with no flag opens an editor. -term prints in the terminal. The text alert log is also a file:

$ORACLE_BASE/diag/rdbms/<db_name>/<sid>/trace/alert_<sid>.log

SHOW INCIDENT lists incidents in the current home, open and closed, with the incident id and problem key. Use that when the alert log names an incident. Use the trace file when you already know the name.

Browser administration is a separate deployment

Enterprise Manager Database Express, on releases that still have it, is an HTTPS port in the database:

SELECT DBMS_XDB_CONFIG.GETHTTPSPORT() FROM dual;

0 means no HTTPS port is set, so Express is not listening. The URL is https://host:port/em. From Oracle Database 21c, EM Express is deprecated. Oracle Enterprise Manager Cloud Control is a different product: a management server and agents, installed and configured on their own. No Express port and no Cloud Control means you work from SQL*Plus, RMAN, Listener Control, and ADRCI.

Gotchas

• sqlplus -V identifies the client. V$INSTANCE.VERSION_FULL identifies the database, and only after you connect.
• command -v sqlplus can resolve an old Instant Client while ORACLE_HOME is a database home. Create and recover with the home you set.
• SQLcl is sql, not sqlplus. It needs its own install and Java 17 or 21.
• TWO_TASK turns sqlplus / as sysdba into a network connection. Unset it for a bequeath login.
• UNKNOWN in lsnrctl services is a static listener.ora entry. It does not mean the instance is open, and it does not disappear on shutdown.
• RESTORE and RECOVER are different RMAN commands. BACKUP is neither of them.
• Data Pump is not RMAN.
• EM Express is not Cloud Control. From 21c, EM Express is deprecated.

Quick quiz

1. Can the SQL*Plus version banner prove the remote database release?

Yes. The banner is negotiated with the listener and matches the instance. No. It identifies the client. Query V$INSTANCE.VERSION_FULL after you connect. Yes, but only when you pass the -V flag to a TNS alias. No. Listener Control status prints the database release.

2. lsnrctl services shows the instance status UNKNOWN. What do you know?

The instance is open and accepting connections. The instance is mounted and blocked. The service is a static listener.ora entry. The listener does not know whether the instance is up. Dynamic service registration failed, so the database is down.

3. The database must run on the 19c home, and command -v dbca prints a 21c path. What do you run?

Set ORACLE_HOME to the 19c home and run $ORACLE_HOME/bin/dbca. Run the 21c dbca. DBCA always builds the database in the newest home. Run RMAN. RMAN creates the database from the backup home. Run SQL Developer. The connection home becomes the database home.

4. The datafiles have been copied back from an RMAN backup and are still at the backup checkpoint. What applies the redo?

RESTORE DATABASE, run a second time lsnrctl services impdp RECOVER DATABASE

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro