swingbench datagenerator connect string

Question

I need to use swingbench to quantify performance of a given host. However since I am pretty new to Databases as such cannot get the datagenerator program to connect to an Oracle DB instance that has been "opened" on the host.

After installing Oracle 12c on the host (and creating a DB along with it), I am starting the DB as such

For the very first time, I was facing issues starting the DB since the initialization file was not present. So I had to create a 'pfile' from an existing 'spfile' which I found present at $ORACLE_HOME

SQL> CREATE PFILE = full path to init.ora FROM SPFILE = full path to existing spfile.ora;

After that I start the DB which 'mounts' and 'opens' the database.

SQL> startup
ORACLE instance started.

Total System Global Area 2421825536 bytes
Fixed Size                  2291232 bytes
Variable Size             654313952 bytes
Database Buffers         1744830464 bytes
Redo Buffers               20389888 bytes
Database mounted.
Database opened.

The relevant details from my DB are:

SQL> SELECT sys_context('USERENV', 'SID') FROM DUAL;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
355

SQL>
SQL> select sys_context('userenv','instance_name') from dual;

SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
db1

SQL> select sys_context('userenv', 'server_host') from dual;

SYS_CONTEXT('USERENV','SERVER_HOST')
--------------------------------------------------------------------------------
oracle_12c

My hostname is:

oracle_12c.localdomain

Now when I try to test the connection from the datagenerator to this database (P.S. from the same host on which the DB is installed), I always get an error. I've tried using both the service-name (or is it called the instance name??) as well as the SID methods of connection as described here. SO on trying using the 'thin style service name syntax' my connect string is:

//oracle_12c.localdomain:1521/db1

My error is an IO error saying connection couldn't be established without giving out much.

I've tried the 'thin style SID syntax' like so:

//oracle_12c.localdomain:1521:355

which gives me a similar error without telling much.

The exact error is "Can't connect using parameters supplied : IO Error : Network adapter couldn't establish the connection"

With these details could anyone help me craft a proper connect string to use with datagenerator? Also is there a file on which I can tail -f on the host that will point me where exactly it is failing?

ALso I presume that the listener does NOT come into the picture for locally originating requests?? Is that true? So does it mean that listerner.ora need not be configured for such requests?

EDIT: My Lister configuration file is:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_12C.localdomain)(PORT = 1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST=
       (SID_DESC=
         (GLOBAL_DBNAME=db1)
         (SID_NAME=db1)
         (ORACLE_HOME=/oracle/product/12.1.0/db_1)
       )
  )

So now after updating this file I start my listener like so:

[oracle@oracle_12c db_1]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 06-APR-2017 04:31:38

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /oracle/diag/tnslsnr/oracle_12c/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle_12c.localdomain)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                06-APR-2017 04:31:39
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /oracle/diag/tnslsnr/oracle_12c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle_12c.localdomain)(PORT=1521)))
Services Summary...
Service "db1" has 1 instance(s).
  Instance "db1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Now at least it looks like my request from swingbench is indeed hitting at my DB because now I get a different error: ORA-01017: invalid username/password, logon denied

So now I change the case from SYS to sys for user to which I get the following error: ORA-28009: Connection as SYS should be as SYSDBA or SYSOPER

I even tried setting the case to insensitive:

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;
System altered.

but to no avail. Keen to hear

Answer 1

You must configure a tcp listener for the JDBC thin driver - that's used by swingbench - to connect to your database.

In listener.ora you will need something like this:

LISTENER = (ADDRESS_LIST= 
 (ADDRESS=(PROTOCOL=tcp)(HOST=yourhostname)(PORT=yourport))
) 

And then the listener has to be started using the lsnctrl command line. I assume you have already created a service which will register with the listener automatically. Then and only then will you be able to connect swingbench.

You can then use the long format connections string:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourhostname)(PORT=yourport))(CONNECT_DATA=(SERVICE_NAME=yourervice)))

which can also be tested with sqlplus:

sqlplus 'scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yourhostname)(PORT=yourport))(CONNECT_DATA=(SERVICE_NAME=yourervice)))'