Listener / Tnsname.ora

 1# Check if listner is present
 2ps -edf | grep lsn
 3
 4# Prompt Listner
 5lsnrctl
 6LSNRCTL> help
 7The following operations are available
 8An asterisk (*) denotes a modifier or extended command:
 9
10start           stop            status          services
11version         reload          save_config     trace
12spawn           quit            exit            set*
13show*
14
15lsnrctl status
16lsnrctl start
17
18# Logs
19less /opt/oracle/product/12c/db/network/admin/listener.ora

Local Listner

1# in Oracle prompt
2show parameter listener;
3NAME                                 TYPE        VALUE
4------------------------------------ ----------- ------------------------------
5listener_networks                    string
6local_listener                       string      LISTENER_TOTO
7remote_listener                      string
  • First LISTENER_TOTO must be defined in the tnsnames.ora.
1# in Oracle prompt
2alter system set local_listener='LISTENER_TOTO' scope=both;
3alter system register;

 1lsnrctl status
 2
 3LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 29-APR-2021 18:58:48
 4Copyright (c) 1991, 2016, Oracle.  All rights reserved.
 5Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
 6STATUS of the LISTENER
 7------------------------
 8Alias                     LISTENER
 9Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
10Start Date                29-APR-2021 18:11:13
11Uptime                    0 days 0 hr. 47 min. 34 sec
12Trace Level               off
13Security                  ON: Local OS Authentication
14SNMP                      OFF
15Listener Log File         /u01/oracle/base/diag/tnslsnr/myhost/listener/alert/log.xml
16Listening Endpoints Summary...
17  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost.example.com)(PORT=1521)))
18Services Summary...
19Service "+ASM" has 1 instance(s).
20  Instance "+ASM", status READY, has 1 handler(s) for this service...
21Service "+ASM_DATA" has 1 instance(s).
22  Instance "+ASM", status READY, has 1 handler(s) for this service...
23Service "+ASM_FRA" has 1 instance(s).
24  Instance "+ASM", status READY, has 1 handler(s) for this service...
25Service "IANA" has 1 instance(s).
26  Instance "IANA", status READY, has 1 handler(s) for this service...
27Service "IANAXDB" has 1 instance(s).
28  Instance "IANA", status READY, has 1 handler(s) for this service...
29The command completed successfully

Static Listner: TNSnames.ORA

Services have to be listed in tnsnames.ora of client hosts.

by default: $ORACLE_HOME/network/admin/. change take effect after listner restart.

  • Example of tnsnames.ora used by client SQLplus and TNSping:
1XE =
2  (DESCRIPTION =
3    (ADDRESS = (PROTOCOL = TCP)(HOST = shigerum-pc)(PORT = 1521))
4    (CONNECT_DATA =
5      (SERVER = DEDICATED)
6      (SERVICE_NAME = XE)
7    )
8  )
  • An other with service name:
 1# vi ${ORACLE_HOME}/network/admin/tnsnames.ora
 2# vi ${ORACLE_HOME}/client/network/admin/tnsnames.ora
 3LOCAL =
 4  (DESCRIPTION =
 5    (ADDRESS = (PROTOCOL= TCP)(Host= 10.0.0.3)(Port= 1521))
 6    (CONNECT_DATA = (SID = REMOTE))
 7  )
 8
 9# then connect with:
10sqlplus user/password@LOCAL

[Debug] TNSping

  • After defining on app server tnsnames.ora and make sure network is open:
 1# tnsping <service_name> <count> 
 2$ tnsping GRACELANV8_GRA901m 5
 3
 4TNS Ping Utility for Solaris: Version 9.2.0.1.0 - Production on 03-JAN-2003 14:47:09
 5
 6Copyright (c) 1997 Oracle Corporation. All rights reserved.
 7
 8Used parameter files:
 9/usr/oracle/9.2.0/network/admin/sqlnet.ora
10
11Used TNSNAMES adapter to resolve the alias
12Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=gracelan)
13(PORT=1525)) (CONNECT_DATA= (SID=GRA901m)))
14OK (80 msec)
15OK (10 msec)
16OK (10 msec)
17OK (0 msec)
18OK (10 msec)

NB - first result longer since:

  • read alias GRACELANV8_GRA901m from tnsnames.ora
  • resolv dns
  • connect
  • second 10ms since all cache

SQLplus Connexion

  • Connect as root (sysdba) in local
1su - oracle
2sqlplus <Login>/<pwd> as sysdba
  • Connect silent mode (avoid banner)
1sqlplus -s / as sysdba
  • Connect local specific db
1# sid correspond to a $ORACLE_SID
2sqlplus user/password@sid
3sqlplus 'system/PWD'@sid as sysdba
  • Connect to distante db
1sqlplus user/password@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.2)(PORT=1521)))(CONNECT_DATA=(SID=REMOTE)))'
2
3# Service defined in tnsnames.ora (SERVICE_NAME = XE)
4sqlplus login/mdp@service

SQLplus scripting

  • Execute Script:
1sqlplus username/password@databasename @"c:\my_script.sql"
  • Execute Command line:
1sqlplus username/password@database < "EXECUTE some_proc /"
  • Execute Command in multi-lines:
1sqlplus username/password@database <<EOF
2EXECUTE some_proc;
3EXIT;
4EOF
  • Another example:
1###########
2export ORACLE_HOME=/oracleClient/app/oracle/product/version
3export DBUSER=fooUser
4export DBPASSWD=fooPW
5export DBNAME=fooSchema 
6echo "select * from someTable;" | $ORACLE_HOME/bin/sqlplus $DBUSER/$DBPASSWD@$DBNAME

Variables in SQLplus

two types:

  • user vars - used in SQL requests and for SQLplus internal usage.
1-- # Users vars can be call back with `&my_variable`
2DEF[INE] [my_variable = what_inside]
3
4-- # List all vars
5DEF 
6
7-- # Delete a var
8UNDEF
  • link variables for PL/SQL commands
1-- # Define a link variable
2VAR[IABLE] [nom [NUMBER | CHAR(n)]]
3
4-- # Show content of the var
5VARIABLE my_var
6
7-- # Inside sql script can be used with :my_var

Buffer

1sql> LIST   or   L   -- list the last command
2sql> RUN    or   /   -- rerun the last command
3sql> define_ed=vi    -- then ed command open vi with last command. 
4sql> set history on 
5sql> history
6sql> S[AVE] save_output_in_file {[CREATE]|[REPLACE]|[APPEND]}  // file is create with output of the sql command

Parameters

 1SET pagesize 100
 2SET LINESIZE 300
 3SET TIMING ON     -- get the request timing  
 4SET TIME ON       -- get time before command is launch
 5SET SQLPROMPT "_user on _connect_identifier> "   -- change prompt with user and instance name
 6SET TRIMSPOOL {ON|OFF}
 7
 8set lines 200 pages 2000
 9col COMP_NAME for a60
10COLUMN my_research_column FORMAT A25
11
12-- save parameters for next login
13<ORACLE_HOME>/sqlplus/admin/glogin.sql