Got Vagrant Oracle running, now what?

[davehodg]

On my 12.5.1 Macbook, following this blog https://blogs.oracle.com/linux/post/quick-and-easy-installation-of-oracle-database-12c-on-oracle-linux-in-oracle-vm-virtualbox got my up and running. Now I have hit two speed bumps.

1. Having su-ed to oracle in virtualbox, sqlplus / sys as sysdba connects me to the database. Then what? I can't create users:

create user scott identified by tiger * ERROR at line 1: ORA-65096: invalid common user or role name

2. I can't connect to the database from outside virtual box. I believe my listener is running:

` $ lsnrctl status

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 09-SEP-2022 11:01:30

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) STATUS of the LISTENER

Alias LISTENER Version TNSLSNR for Linux: Version 21.0.0.0.0 - Production Start Date 07-SEP-2022 15:05:08 Uptime 1 days 19 hr. 56 min. 21 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora Listener Log File /opt/oracle/diag/tnslsnr/localhost/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "XE" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "XEXDB" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "e817f154477cb0e7e055000000000001" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Service "xepdb1" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... The command completed successfully ` I can telnet to port 1521 from outside, but sqlplus won't give me a connection. I want to be able to develop on the mac host.

`% sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 9 11:04:18 2022 Version 19.8.0.0.0

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

Enter password: ERROR: ORA-12545: Connect failed because target host or object does not exist `

Putting 127.0.0.1 in the incantation makes no difference.

[totalamateurhour]

You'll want to create the user in the first pluggable database, xepdb1, not in the container database. See Multitenant Architecture.

Try connecting as explained in this quick start

sqlplus sys/[Password]@//localhost:1521/XEPDB1 as sysdba

What incantation are you using from the Mac host command line to connect to the database in the VM?

[PaulNeumann]

@davehodg

For the first issue, the error is due to trying to create a user in the container database (CDB) without following the required naming convention. You almost never want to create a user in the CDB, because most "real" work is done in the PDB. However, if you really want to create a common user in the CDB, the username has to be prefixed with C## or c##. For example, CREATE USER C##SCOTT IDENTIFIED BY tiger;

It'd be better to set the active container to the PDB before running the CREATE USER statement. For the 21c XE VM, the pluggable database is called "XEPDB1", so the following should work:

ALTER SESSION SET CONTAINER = XEPDB1;
CREATE USER SCOTT IDENTIFIED BY tiger;

For the second issue, you need to give SQL*Plus a little more information to connect. SQL*Plus doesn't automatically know about the VM. Try using sqlplus sys@localhost/XEPDB1 as sysdba to connect to the PDB, or sqlplus sys@localhost/XE as sysdba to connect to the CDB. For either command, you'll be prompted for SYS's password. The password was displayed at the end of the VM provisioning. If you don't have it, you can use the setPassword.sh script to change it, as described in the README.md file.

I hope this helps.

[totalamateurhour]

jinx

[PaulNeumann]

Apologies. :-)

[davehodg]

@totalamateurhour @PaulNeumann

Inside the VM I was using:

sqlplus  / as SYSDBA

So, my sysdba has no password. So then, what's the incantation from outside? And from inside to connect to XEPDB1?

For the other suggestion I get:

% sqlplus sys@localhost/XEPDB1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 10 11:14:44 2022
Version 19.8.0.0.0

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

Enter password: 
ERROR:
ORA-01017: invalid username/password; logon denied

This, however, worked fine:

ALTER SESSION SET CONTAINER = XEPDB1;
CREATE USER SCOTT IDENTIFIED BY tiger;

However, from outside the VM:

% sqlplus scott/tiger@//localhost:1521/XEPDB1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 10 11:18:43 2022
Version 19.8.0.0.0

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

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: 

It didn't work.

Suggestions on how to use a Python/Ruby/Perl/Java connect string are also welcome.

[PaulNeumann]

@davehodg

The SYS database user has the same password whether connecting from inside or outside the VM. Either inside or outside the VM, sqlplus sys/[password]@//localhost:1521/XE as sysdba or sqlplus sys/[password]@//localhost:1521/XEPDB1 as sysdba will work. The password must either be supplied as part of the command, or entered when SQL*Plus prompts for it. If you want SQL*Plus to prompt for the password, use sqlplus sys@//localhost:1521/XEPDB1 as sysdba. See Starting SQL*Plus in the documentation for more information.

Inside the VM only, sqlplus / AS SYSDBA will also work, because the oracle OS user is a member of the dba OS group. Members of the dba OS group can be authenticated by the OS. See Authentication of Database Administrators by Using the Operating System in the documentation.

sqlplus scott/tiger@//localhost:1521/XEPDB1 as sysdba won't work either inside or outside the VM, because the scott database user is not a privileged user. The as sysdba part of the command is used only when connecting as a privileged user. sqlplus scott/tiger@//localhost:1521/XEPDB1 will work both inside and outside the VM.

Example connection strings for several languages are included in the Oracle Database XE Quick Start.

[davehodg]

My sys has no password. The sqlplus incantation doesn't work.

Thanks for the connection strings.

[totalamateurhour]

My sys has no password. The sqlplus incantation doesn't work.

SYS does have a password. I think you missed this part of Paul's response:

Inside the VM only, sqlplus / AS SYSDBA will also work, because the oracle OS user is a member of the dba OS group.

Do you still need help with this issue?

[totalamateurhour]

Closing due to lack of response. Please re-open if necessary.