Forum Stats

  • 3,734,460 Users
  • 2,246,975 Discussions
  • 7,857,302 Comments

Discussions

creating a user in Oracle 19c

User_KC6Y3
User_KC6Y3 Member Posts: 27 Green Ribbon
edited Jun 11, 2021 4:51PM in Database Installation

I am starting to use Oracle 19c for the first time; I am having difficulties creating a user.

Here is what I have done (so far):

  1. I created a database with a global name of "Portal".
  2. As part of that I created a Container database with the pluggable name as: PortalPDB.
  3. I assigned the administrative password as: Oracle19c.
  4. The database created as expected.
  5. I then added the following entries to TNSNAMES.ora:

 (DESCRIPTION =

  (ADDRESS_LIST =

   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.15)(PORT = 1621))

  )

  (CONNECT_DATA =

   (SID = PORTAL)

   (SERVER = DEDICATED)

  )

 )

PORTALPDB =

 (DESCRIPTION =

  (ADDRESS_LIST =

   (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.15)(PORT = 1621))

  )

  (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = PORTALPDB)

  )

 )

PORTAL =

I then restarted the listener service; it shows the following output:

Microsoft Windows [Version 10.0.21390.1010]

(c) Microsoft Corporation. All rights reserved.


C:\WINDOWS\system32>cd\

C:\>lsnrctl status


LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 11-JUN-2021 12:25:29


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


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HP)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias           LISTENER

Version          TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production

Start Date        11-JUN-2021 12:24:56

Uptime          0 days 0 hr. 0 min. 33 sec

Trace Level        off

Security         ON: Local OS Authentication

SNMP           OFF

Listener Parameter File  C:\app\Owner\Product\19.0.0\network\admin\listener.ora

Listener Log File     C:\app\Owner\diag\tnslsnr\HP\listener\alert\log.xml

Listening Endpoints Summary...

 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HP)(PORT=1521)))

 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

Services Summary...

Service "6d424f1421d04ff19b2311a57017951a" has 1 instance(s).

 Instance "portal", status READY, has 1 handler(s) for this service...

Service "CLRExtProc" has 1 instance(s).

 Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "PortalXDB" has 1 instance(s).

 Instance "portal", status READY, has 1 handler(s) for this service...

Service "portal" has 2 instance(s).

 Instance "portal", status UNKNOWN, has 1 handler(s) for this service...

 Instance "portal", status READY, has 1 handler(s) for this service...

Service "portalpdb" has 1 instance(s).

 Instance "portal", status READY, has 1 handler(s) for this service...

The command completed successfully


C:\>

I believe that I should not create a user in the container database; instead, I should create it in the pluggable database.

I then launched SQLPlus and ran these SQLs:


C:\>sqlplus / as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 11 12:44:12 2021

Version 19.3.0.0.0


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



Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0


SQL> select pdb_name, status from dba_pdbs;


PDB_NAME

--------------------------------------------------------------------------------

STATUS

----------

PORTALPDB

NORMAL


PDB$SEED

NORMAL



SQL> connect system/[email protected];

ERROR:

ORA-12541: TNS:no listener



Warning: You are no longer connected to ORACLE.

SQL>


I don't understand what I did wrong.

Any assistance would be appreciated.

Answers

  • User_KC6Y3
    User_KC6Y3 Member Posts: 27 Green Ribbon
    edited Jun 12, 2021 10:18PM

    When I checked the services I have running, i get the following:

    OracleJobSchedulerPORTAL -> disabled (this is ok since I don't need the scheduler)

    OracleOraDB19Home2TNSListener -> automatic and running

    OracleServicePORTAL -> automatic and running

    OracleVSSWriterPORTAL -> automatic but not running

    However, I do NOT see any services for PORTALPDB (my container database).

    When I try to connect using SQLplus (as sysdba) I get this error:

    C:\>sqlplus sys/[email protected] as sysdba


    SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jun 12 18:02:58 2021

    Version 19.3.0.0.0


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


    ERROR:

    ORA-12541: TNS:no listener


    But, if have done things correctly, I HAVE an entry for "portalpdb" in my tnsnames file.

    My Listener.ora file has these entries:

     (SID_DESC =

      (GLOBAL_DBNAME = portal)

      (ORACLE_HOME = C:\app\Owner\Product\19.0.0)

      (SID_NAME = portal)

     )

      

     (SID_DESC =

      (GLOBAL_DBNAME = portalpdb)

      (ORACLE_HOME = C:\app\Owner\Product\19.0.0)

      (SID_NAME = portalpdb)

     )

  • User_KC6Y3
    User_KC6Y3 Member Posts: 27 Green Ribbon

    I restarted my Listener; here is the output:


    C:\>lsnrctl status


    LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 12-JUN-2021 18:19:26


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


    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HP)(PORT=1521)))

    STATUS of the LISTENER

    ------------------------

    Alias           LISTENER

    Version          TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production

    Start Date        12-JUN-2021 18:19:08

    Uptime          0 days 0 hr. 0 min. 22 sec

    Trace Level        off

    Security         ON: Local OS Authentication

    SNMP           OFF

    Listener Parameter File  C:\app\Owner\Product\19.0.0\network\admin\listener.ora

    Listener Log File     C:\app\Owner\diag\tnslsnr\HP\listener\alert\log.xml

    Listening Endpoints Summary...

     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HP)(PORT=1521)))

     (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

    Services Summary...

    Service "CLRExtProc" has 1 instance(s).

     Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

    Service "portal" has 1 instance(s).

     Instance "portal", status UNKNOWN, has 1 handler(s) for this service...

    Service "portalpdb" has 1 instance(s).

     Instance "portalpdb", status UNKNOWN, has 1 handler(s) for this service...

    The command completed successfully


    C:\>


    Some "other' entries are now removed; i.e.: I no longer see this entry:

    Services Summary...

    Service "6d424f1421d04ff19b2311a57017951a" has 1 instance(s).

    I don't know what that entry was.

Sign In or Register to comment.