Oracle Database Admin Notes

Submitted by code_admin on Mon, 07/23/2018 - 09:35

ODI VM Database location:
/u01/app/oracle/product/11.2.0/orcl

Start/stop database
cd /u01/app/oracle/product/11.2.0/orcl
login prompt: type "/ as sysdba"

Stop:
SHUTDOWN IMMEDIATE

Start:
STARTUP

Restart listner:

  1. lsnrctl

status, stop and start

Process to change port

Stop the listener

Alter /u01/app/oracle/product/11.2.0/orcl/network/admin/listener.ora

  2. LISTENER =
  3.   (DESCRIPTION_LIST =
  4.     (DESCRIPTION =
  5.       (ADDRESS = (PROTOCOL = TCP)(HOST = ODIGettingStarted)(PORT = 3621))
  6.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  7.     )
  8.   )
  9.  
  10. DEFAULT_SERVICE_LISTENER = (ORCL)

Alter /u01/app/oracle/product/11.2.0/orcl/network/admin/tnsnames.ora

  1. ORCL =
  2.   (DESCRIPTION =
  3.     (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 3621))
  4.     (CONNECT_DATA =
  5.       (SERVER = DEDICATED)
  6.       (SERVICE_NAME = orcl)
  7.     )
  8.   )

Start the listener.
You will notice "The listener supports no services"

Start SQL and run: (Chaning the address part to match what is in listener.ora)

  1. ALTER SYSTEM SET LOCAL_LISTENER = "(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 3621))";
  2.  
  3. ALTER SYSTEM REGISTER;
  4.  
  5. commit;

Debugging listner

Find process ID

  1. ps -fu oracle | grep lsnr | grep -v grep

List listening ports

  1. lsof -p **PROCESS ID** | grep IP

View parameter

  1. select value from v$parameter where name like '%list%';

Tags

RJM Article Type
Quick Reference