oracle 21c with default container database is having issues with Hammerdb 4.4 #330

Jubayer1228 · 2022-03-22T21:02:11Z

Jubayer1228
Mar 22, 2022

Hi All,
I am trying to run hammerdb 4.4 on oracle 21c and getting ORA01017 invalid username/password: logon error. I tried to debug this scenarios.

tnsping works for the service which hammerdb will use to talk to the database
username system works when I tried to log into the databadse.
lsnrctl status command shows the active services which matches the service name that database has.

Answered by YAllandit

Apr 1, 2022

Hi there,
Just to move the 21c version concern away, I run Hammerdb 4.4 with a 21c database freshly installed in OCI DBCS.
HDB was running on another compute node within the same subnet.

SQL> select * from v$version
Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production

SQL> ALTER SESSION SET CONTAINER=cdb$root;

Session altered.

SQL> col name for a8
col open_time for a33
select con_id,name,dbid,open_mode,open_time from v$containers;SQL> SQL>

CON_ID NAME           DBID OPEN_MODE  OPEN_TIME

     1 CDB$ROOT 1958648267 READ WRITE 01-APR-22 04.15.49.438 PM +02:00
     2 PDB$SEED 4022997756 READ ONLY  01-APR-22 04.15.49.452 PM +02:00
     3 HDB1     1612939884 READ WRITE 01-APR-22 0…

View full answer

sm-shaw · 2022-03-23T12:21:10Z

sm-shaw
Mar 23, 2022
Maintainer

Please make sure that your instance "tpcc" is a pluggable database and you are not trying to create the schema in the container database. Try using instance names such as CDB and PDB to make it clear.

If you try to create a user in the container database with sqlplus, you will get the same error:

SQL> create user tpcc identified by tpcc;
create user tpcc identified by tpcc
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

As below, a manual test and then GUI build has no problem logging in with this password for the system user to the pluggable database and the build proceeds as expected, so this looks like an Oracle error rather than a HammerDB one.

./bin/tclsh8.6 
% package require Oratcl
4.6
% set connect system/#Eval7720@RAZPDB1
system/#Eval7720@RAZPDB1
% oralogon $connect
oratcl0

Please confirm and mark as answered.

1 reply

russfellows Mar 24, 2022

Issues with connecting, see below. One BIG question is "Should the "TPROC-C" user already exist?" Logic would dictate "yes", but experience seems to indicate no.

Jubayer1228 · 2022-03-23T17:25:49Z

Jubayer1228
Mar 23, 2022
Author

Hi Steve,
Thank you so much for your reply.
By default, oracle 21c, we can only create container database. see attached screenshot.
Are you able to run hammerdb 4.4 or 4.3 in oracle 21c? If so, could you be able to let me know what are the steps needed to achieve that?

1 reply

sm-shaw Mar 23, 2022
Maintainer

Yes of course you can use 21c, If your container is up and running then use the command "CREATE PLUGGABLE DATABASE ..." to create a pluggable database - this is where you create your schema.

Jubayer1228 · 2022-03-23T22:44:52Z

Jubayer1228
Mar 23, 2022
Author

I already have a pluggable database which I created with oracle DBCA UI. I have to use oracle service name as global database name, not the pluggable database name. If I use cdbpdb1 as the oracle service name in Hammerdb build options, I am getting could not resolve the connect identifier. Also tnsping is not working in that case.
Are you aware of anyone actually tested hammerdb against oracle 21c. We could not find any public materials available on that. Almost all documentation on Hammerdb around oracle is for 12c which is ancient. A little bit for 19c and nothing with 21c.

Shot 2022-03-23 at

4 36 51 PM" src="https://user-images.githubusercontent.com/4262022/159807859-d2979f02-7722-42a9-a3f6-53a0f9ffa445.png"> .

3 replies

sm-shaw Mar 24, 2022
Maintainer

OK so again your issue is at the Oracle level rather than HammerDB, if you cannot tnsping your service name then HammerDB will not be able to connect either. You should start here https://docs.oracle.com/en/database/oracle/oracle-database/21/multi/introduction-to-the-multitenant-architecture.html

Yes 21c is fine, for the documentation read this article as an introduction https://www.hammerdb.com/blog/uncategorized/interacting-with-open-source-for-hammerdb-code-and-documentation/

HammerDB is open source, including the documentation, all under GNU licences. This means it is community driven, if you think a section of documentation is out of date then you have the opportunity to update it, make a pull request here https://github.com/TPC-Council/HammerDB/tree/master/DocBook and give something back. If not you, then who should do this? For experts the current 12c documentation is fine for later releases, if you think that beginners need more detail then why not make helping others part of your journey? We look forward to your contribution.

russfellows Mar 24, 2022

Steve,

Appreciate the guidance. I too have read the documentation you refer to, and still the process is not only unclear, but after attempting every scenario I have thought of, still it does not work. This is in MARKED contrast to using HammerDB with SQL Server, where the process is quite clear and straightforward. I have used HammerDB with SQL Server 2012, 2016 and 2019 with only a few minor issues (like needing to change the client version from the default, etc.).

For HammerDB with Oracle 18c, it was a bit more complicated, but I was able to get it working after only a few attempts. Again, not so with 21c and Hammer4.4. I would have thought someone would have run HammerDB with 21c and discovered the differences from 18c. Apparently not.

sm-shaw Mar 24, 2022
Maintainer

So to recap on what has been raised so far, with this issue with pluggable databases:

ORA-65096: invalid common user or role name

A: Typically, the user is trying to create a user in the container database rather than the pluggable database.

TNS-03505: Failed to resolve name & ORA-12154 could not resolve the connect identifier specified

A: The listener or TNS has not been configured correctly to connect to the service name.

Neither of these are HammerDB issues. HammerDB use the Native C language interface called the Oracle Call Interface to connect to Oracle. As described here, "Oracle Call Interface is highly reliable. Oracle tools such as SQLPlus, Real Application Testing (RAT), SQLLoader, and Data-Pump all use Oracle Call Interface." ... so does HammerDB.

Unfortunately, 'still it does not work' gives no guidance on what the issue is or how you can be helped. There may be a difference introduced between 18c or 21c, or experienced Oracle users may not have a problem with either, therefore we rely on the community to raise specific issues that can be reproduced.

russfellows · 2022-03-24T18:42:33Z

russfellows
Mar 24, 2022

Steve,

Again, thanks for responding. In trying to use the hammerdbcli to debug this session, conencting as "sysetm" works, connecting as the TPROC-C user does not work. It is unclear if the TPROC-C user should already exist, or not. What debug steps should be taken after connecting? See screenshots.

So connecting with "system" works in hammerdbcli, but in the UI, there are errors, with "missing close brace".
As seen, connecting with system user and password works. However, using the TPROC-C user and password does not work, should it? This is not clear.

If in the UI we repeat the system user and password for the TPROC-C user, we receive different errors, of missing close brace, see below.

1 reply

sm-shaw Mar 25, 2022
Maintainer

OK so there is a lot going on here. Taking it step by step.

If you get an ORA-01017 invalid username/password with HammerDB for tpcc3/tpcc3@tpccpdb1 it will be the same with sqlplus (as noted the underlying libraries are exactly the same) so it means the username of tpcc3 or password of tpcc3 is invalid.
For the TPROC-C user this is in the HammerDB documentation as follows:
https://www.hammerdb.com/docs/ch04s03.html#d0e1008
The TPROC-C user is the name of a user to be created that will own the TPROC-C schema. This user can have any name you choose but must not already exist and adhere to the standard rules for naming Oracle users. You may if you wish run the schema creation multiple times and have multiple TPROC-C schemas created with ownership under a different user you create each time.
In the dialog screenshot you have the TPROC-C user as the system user, as above the TPROC-C is created during schema creation time - therefore you have asked HammerDB to create the system user, firstly this will fail as it already exists and secondly the system user is common across the container and all pluggable databases (i.e. there is only one system user for the CDB and all PDBs so trying to change the password for this user in a PDB is not allowed either).
Missing close-brace - The # is a comment character in HammerDB - as you've seen it works with system but it is possible that along with other errors something is being commented out when you run the script. For troubleshooting you would be much better off with the tpcc3/tpcc3 username password combo while you sort out the other issues.

So it looks like you have created a pluggable database of tpccpdb1 already and can connect to this as system. So change your build schema options to a new user and password that doesn't exist in the pdb (eg tpcc3 / tpcc3 ) also make sure that the tablespace exists where the schema will be installed (either create tpcctab upfront or use USERS by default) and the temporary tablespace exists. Run the schema build and it will be created in your pluggable database. You can then confirm that you can login with sqlplus with the user and password you create (eg tpcc3 / tpcc3 ).

Jubayer1228 · 2022-03-30T14:31:12Z

Jubayer1228
Mar 30, 2022
Author

Hi Steve,
Thank you so much for your detail response.

once we logged in with system user, we are in the root container named cdb$root. create user sql command will fail here unless we alter the session with the following commnad.
alter session set container=tpccpdb1 #[pluggable container name]
after that we can use the create user command to create the user. This user does not have login privilege as it has been created in pluggable container.

I have added that “alter session set container=tpccpdb1” command in line 922 of HammerDB/src/oracle/oraoltp.tcl file. after that hammerdb UI is able to create the TPROC user. [attached screenshot]

then again in line 1784 of oraoltp.tcl file "set connect $tpcc_user/$tpcc_pass@$instance” this command is executed which is failing as the newly created user does not have login privileges. If we use system info here then we are getting unique constraint violation error.

I was able to create common user using
CREATE USER c##comm_user
IDENTIFIED BY comm_pwd
DEFAULT TABLESPACE example
QUOTA 20M ON example
TEMPORARY TABLESPACE temp_tbs;
this common user has login privileges.

Here are the excerpt from the oracle documentation.
By default, when you connect to the RAC instance, you connect to the CDB$ROOT

My question is what type of user hammerdb is expecting? local user that need to be created in pluggable container. if that is true then this user need to have login privileges. I have tried this command "grant create session to user" but that didn't help either.

0 replies

russfellows · 2022-03-30T20:54:36Z

russfellows
Mar 30, 2022

Steve, any ideas?

Again, it does not appear that HammerDB 4.3 or 4.4 can operate with Oracle 21c per all of the attempts. At a minimum, HammerDB appears to be missing SQLPLUS commands to perform an "Alter Session set container=$OraServiceName", where $OraServiceName is the name provided as the first variable in the UI.

Are you aware of anyone who has successfully run HammerDB with 21c?

0 replies

sm-shaw · 2022-03-31T11:04:15Z

sm-shaw
Mar 31, 2022
Maintainer

So you are saying:

once we logged in with system user, we are in the root container named cdb$root.

and in the screenshot shows logging in to the pluggable database as the system user it still goes into cdb$root?
so for example:
Login is as sysdba we go into cdb$root

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 31 06:56:12 2022
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

Login in to the container as system we go into cdb$root

sqlplus system/#Eval7720@RAZCDB1;

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 31 06:56:36 2022
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Thu Mar 31 2022 06:53:29 -04:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

Login to the pluggable database as system this has now changed and you go into cdb$root in 21c changing previous behaviour from what is seen here with 19c?

sqlplus system/#Eval7720@RAZPDB2;

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 31 06:56:51 2022
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Thu Mar 31 2022 06:56:36 -04:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> show con_name;

CON_NAME
------------------------------
RAZPDB2

As before HammerDB logs in as system, creates the user and password and does the grants for you. This user should not be created before and you don't need to change anything in HammerDB. If 21c now prevents logging in to a pluggable database as the system user then of course that will prevent the user creation and this is the issue.

Being a system user does not cause a constraint violation, the constraint violation comes from trying to create and load the same schema when it already exists. But of course it should not be created under the system user.

0 replies

YAllandit · 2022-04-01T16:55:26Z

YAllandit
Apr 1, 2022

Hi there,
Just to move the 21c version concern away, I run Hammerdb 4.4 with a 21c database freshly installed in OCI DBCS.
HDB was running on another compute node within the same subnet.

SQL> select * from v$version
Oracle Database 21c EE High Perf Release 21.0.0.0.0 - Production

SQL> ALTER SESSION SET CONTAINER=cdb$root;

Session altered.

SQL> col name for a8
col open_time for a33
select con_id,name,dbid,open_mode,open_time from v$containers;SQL> SQL>

CON_ID NAME           DBID OPEN_MODE  OPEN_TIME

     1 CDB$ROOT 1958648267 READ WRITE 01-APR-22 04.15.49.438 PM +02:00
     2 PDB$SEED 4022997756 READ ONLY  01-APR-22 04.15.49.452 PM +02:00
     3 HDB1     1612939884 READ WRITE 01-APR-22 04.15.51.067 PM +02:00

SQL> ALTER SESSION SET CONTAINER=HDB1
2 ;

Session altered.

SQL> col name for a8
col open_time for a33
select con_id,name,dbid,open_mode,open_time from v$containers;SQL> SQL>

CON_ID NAME           DBID OPEN_MODE  OPEN_TIME

     3 HDB1     1612939884 READ WRITE 01-APR-22 04.15.51.067 PM +02:00

The TPCC user is created within the PDB:
SQL> select username from dba_users where username like 'T%'

USERNAME

TPCC

Once the tablespace created and the data loaded, the workload generator works fine:

Hope this helps!!!

1 reply

sm-shaw Apr 1, 2022
Maintainer

Thank you Yann! Yann is one of the top Oracle experts, so that is unequivocal proof that HammerDB v4.4 has no problem running against 21c. Thanks again!

Jubayer1228 · 2022-04-06T19:54:23Z

Jubayer1228
Apr 6, 2022
Author

Hi Yann & Steve,

Thank you so much for the help. Finally , I was able to resolve this issue. I had to use common user which has a "c##" as prefix.

From Yann screenshot, I can see, he didn't have to create this special type of user "common user". For now I am good with having common user as TPROC-C user.

So Thanks again. Yes, HammerDb v4.4 can run against oracle 21c. we can mark this as resolved.

1 reply

russfellows Apr 6, 2022

I just wanted to follow up on this. We did not see the "TPROC-C" user id used by Yann. However, as noted, only the special "common user" would work, there was no way for this to work without the common user.

Do you want someone to update the documentation? It is unclear that this can work without a common user, which is not documented anyplace.

Secondly, we note Yann's somewhat poor Oracle performance matches our poor performance. The highly variable TPM rate, along with the final NOPM results of 58,649 are similar to our results using a system with 8 vCPU's and 16 users. However, this is SIGNIFICANTLY less than the results obtained from running SQL Server on Windows using identical hardware. I will open a new "Discussion" on this aspect.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

oracle 21c with default container database is having issues with Hammerdb 4.4 #330

{{title}}

Replies: 9 comments 8 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

oracle 21c with default container database is having issues with Hammerdb 4.4 #330

Jubayer1228 Mar 22, 2022

Replies: 9 comments · 8 replies

sm-shaw Mar 23, 2022 Maintainer

russfellows Mar 24, 2022

Jubayer1228 Mar 23, 2022 Author

sm-shaw Mar 23, 2022 Maintainer

Jubayer1228 Mar 23, 2022 Author

sm-shaw Mar 24, 2022 Maintainer

russfellows Mar 24, 2022

sm-shaw Mar 24, 2022 Maintainer

russfellows Mar 24, 2022

sm-shaw Mar 25, 2022 Maintainer

Jubayer1228 Mar 30, 2022 Author

russfellows Mar 30, 2022

sm-shaw Mar 31, 2022 Maintainer

YAllandit Apr 1, 2022

USERNAME

sm-shaw Apr 1, 2022 Maintainer

Jubayer1228 Apr 6, 2022 Author

russfellows Apr 6, 2022

Jubayer1228
Mar 22, 2022

Replies: 9 comments 8 replies

sm-shaw
Mar 23, 2022
Maintainer

Jubayer1228
Mar 23, 2022
Author

sm-shaw Mar 23, 2022
Maintainer

Jubayer1228
Mar 23, 2022
Author

sm-shaw Mar 24, 2022
Maintainer

sm-shaw Mar 24, 2022
Maintainer

russfellows
Mar 24, 2022

sm-shaw Mar 25, 2022
Maintainer

Jubayer1228
Mar 30, 2022
Author

russfellows
Mar 30, 2022

sm-shaw
Mar 31, 2022
Maintainer

YAllandit
Apr 1, 2022

sm-shaw Apr 1, 2022
Maintainer

Jubayer1228
Apr 6, 2022
Author