Docker-images: Oracle 19.3.C windows cannont connect to sql plus

[oMainz]

Hello,
First off thanks for the great image
I'm having a couple issue some which i could solve but wanted to report and some I haven't been able to solve yet.

The container is running on Windows.
First Issue I encountered was once I Stopped the container starting was impossible.
Once I found your website https://geraldonit.com/2017/08/21/creating-an-oracle-database-docker-image/ I noticed the docker stop -t command this wasn't included in the readme but solved it for me.

The other issue is I cannot connect using sqlplus / as sysdba using it results in password request

docker exec -it 1c bash
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 16 07:32:22 2020
Version 19.3.0.0.0

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

ERROR:
ORA-12162: TNS:net service name is incorrectly specified


Enter user-name:

Now this is a small issue as I can connect using

[oracle@1c06025ba3ab ~]$ $ORACLE_HOME/bin/sqlplus -s system/password@localhost:1521/ORCLCDB as sysdba

select 1 from dual;

         1
----------
         1

without $ORACLE_HOME/bin/sqlplus -s I cannot connect.

My final problem is I cannot seem to be able to change the MAX_STRING_SIZE
Steps taken:
ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
shutdown normal;

Once the DB is shutdown it get the message that SQL PLus is no longer connected.
Trying to reconnect is impossible:
ORA-12162: TNS:net service name is incorrectly specified

Help would really be appreciated, what am I doing wrong?

[oMainz]

Ill post my run scripts in here

docker run -d --name oracletest  --ipc=host --add-host=dockerhost:172.17.0.1   -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=Password. -e ORACLE_CHARACTERSET=AL32UTF8 -v C:\docker\\MAN_GDC_ORACLE_19c\setup:/docker-entrypoint-initdb.d/setup -v C:\docker\\MAN_GDC_ORACLE_19c\startup:/docker-entrypoint-initdb.d/startup oracle/database:19.3.0-ee

Log:

ORACLE PASSWORD FOR SYS, SYSTEM AND PDBADMIN: Password.

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-JAN-2020 07:55:35

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

Starting /opt/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/9339f63bb453/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-JAN-2020 07:55:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/9339f63bb453/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete`
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /opt/oracle/cfgtoollogs/dbca/ORCLCDB.
Database Information:
Global Database Name:ORCLCDB
System Identifier(SID):ORCLCDB
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/ORCLCDB.log" for further details.

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 16 08:17:38 2020
Version 19.3.0.0.0

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


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

SQL> 
System altered.

SQL> 
System altered.

SQL> 
Pluggable database altered.

SQL> 
PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Executing user defined scripts
/opt/oracle/runUserScripts.sh: running /opt/oracle/scripts/setup/001_createTablespace.sql

System altered.


Tablespace created.


Tablespace created.


Tablespace created.


The Oracle base remains unchanged with value /opt/oracle
#########################
DATABASE IS READY TO USE!
#########################

[oMainz]

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SQL> ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;

System altered.

SQL> commit;
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE
**SQL> startup upgrade
SP2-0640: Not connected**

[oracle@9339f63bb453 ~]$ $ORACLE_HOME/bin/sqlplus sys/password@localhost:1521/ORCLCDB1 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 16 08:53:52 2020
Version 19.3.0.0.0

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified


Enter user-name:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@9339f63bb453 ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 16-JAN-2020 08:54:09

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                16-JAN-2020 07:55:35
Uptime                    0 days 0 hr. 58 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/9339f63bb453/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully

[gvenzl]

Hi @oMainz, for sqlplus / as sysdba to work you first have to set the ORACLE_SID= to your database SID. By default, that's ORCLCDB.

You can do that in two different ways:

1) Use . oraenv which also sets the $ORACLE_HOME and $PATH and is not strictly necessary:

[oracle@ol7 ~]$ docker exec -ti oracletest bash
[oracle@bf616ac345f1 ~]$ . oraenv
ORACLE_SID = [] ? ORCLCDB
The Oracle base remains unchanged with value /opt/oracle
[oracle@bf616ac345f1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 18 19:08:16 2020
Version 19.3.0.0.0

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


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

SQL>

1. Set $ORACLE_SID explicitly via export ORACLE_SID=ORCLCDB in my example:

[oracle@ol7 ~]$ docker exec -ti oracletest bash
[oracle@bf616ac345f1 ~]$ export ORACLE_SID=ORCLCDB
[oracle@bf616ac345f1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 18 19:09:20 2020
Version 19.3.0.0.0

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


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

SQL>

Also note that in your last example you have a typo in your connect string by using ORCLCDB1 instead of ORCLCDB: $ORACLE_HOME/bin/sqlplus sys/password@localhost:1521/ORCLCDB1 as sysdba

[gvenzl]

Also note that CDB$ROOT always uses standard data types. This is documented here:

Note:

The root continues to use STANDARD semantics even after MAX_STRING_SIZE is set to
EXTENDED. The reason for setting MAX_STRING_SIZE to EXTENDED in the root is so all the PDBs in the CDB can inherit the EXTENDED setting from the root.

[gvenzl]

I will close this ticket for now.
If you continue to have issues, please reopen it!

[oMainz]

@gvenzl thank you both solution worked for me