7 Replies Latest reply on Jun 17, 2020 2:02 PM by The Insightful Professor

    OracleXE 18c Error on first use

    bobparis

      Installed OracleXE 18c no errors. Oracle Instance Manager reports NO problems.
      Went to create database using Database Configuration Assistant and get:

       

       

        • 1. Re: OracleXE 18c Error on first use
          L. Fernigrini

          If you have correctly installed XE, then you already have an instance created. Remenber that you can havve only 1 XE instance per server (physical or virtual).

           

          What kind of database are you trying to create? An additional PDB? Can you post the previous screens?

          • 2. Re: OracleXE 18c Error on first use
            bobparis

            Hello,

            Thanks for the reply. Did not do screen captures at each step.
            Took two tries to get the install to run to completion. (java null exception)
            This is my development box so wanted XE as CBD then PDB for DEV, PDB for TEST, etc.

            • 3. Re: OracleXE 18c Error on first use
              L. Fernigrini

              So you get that error when creating an additional PDB?

              • 4. Re: OracleXE 18c Error on first use
                bobparis

                Created the CBD did not specify a PDB. Used DCA to create first PDB the got error

                • 5. Re: OracleXE 18c Error on first use
                  The Insightful Professor

                  The following steps are taken from a workshop I have used when teaching DBA classes. These steps are performed manually using SQL*Plus rather than using the Database Configuration Assistant.

                   

                  Creating a pluggable database from the seed PDB

                   

                  Connect to the root as SYSDBA and run the following commands.

                   

                  Run the following query to determine the location of data files for the existing databases. Use the CON_ID value for PDBORCL from the SHOW PDBS command.

                   

                  SELECT CON_ID, TABLESPACE_NAME, FILE_NAME

                  FROM CDB_DATA_FILES

                  WHERE CON_ID = 3; -- XEPDB1

                   

                  We observe that the files for this database are located in the XEPDB1 folder within

                  C:\APP\STUDENT\PRODUCT\18.0.0\ORADATA\XE\XEPDB1

                   

                  Create a similar directory for the new PDB. The name we will give to this PDB is PDB2. Use the SQL*Plus HOST command to access the operating system command prompt.

                   

                  HOST

                   

                  Now run the following command to create the directory.

                   

                  MKDIR C:\APP\STUDENT\PRODUCT\18.0.0\ORADATA\XE\PDB2

                   

                  Confirm that the directory was created by switching to it.

                   

                  CD C:\APP\STUDENT\PRODUCT\18.0.0\ORADATA\XE\PDB2

                   

                  Return to SQL*Plus.

                   

                  EXIT

                   

                  CREATE PLUGGABLE DATABASE PDB2

                  ADMIN USER PDB2_ADMIN IDENTIFIED BY oracle

                  ROLES = (DBA)

                  FILE_NAME_CONVERT = ('C:\app\student\product\18.0.0\oradata\XE\pdbseed',

                  'C:\app\student\product\18.0.0\oradata\XE\PDB2');

                   

                  Verify the status, open mode, and service names of the PDBs in the CDB. The CDB_PDBS view describes PDBs belonging to a given CDB. When queried from the root, it will describe all PDBs belonging to a given CDB. When queried from a PDB, it will always return no rows.

                   

                  SELECT PDB_NAME, STATUS

                  FROM CDB_PDBS;

                   

                  The status value of NEW indicates the PDB has never been opened since it was created. It must be opened in READ WRITE mode for Oracle to perform processing needed to complete the integration of the PDB into the CDB and mark it NORMAL. An error will be thrown if an attempt is made to open the PDB read only. NORMAL indicates the PDB is ready to be used.

                   

                  SELECT NAME, OPEN_MODE FROM V$PDBS;

                   

                  A new service is created when a PDB is created.

                   

                  SELECT NAME, CON_ID

                  FROM V$ACTIVE_SERVICES

                  ORDER BY 1;

                   

                  List the data files for the new PDB. Supply the CON_ID value for this PDB as shown in the output of the previous query.

                   

                  SELECT NAME

                  FROM V$DATAFILE

                  WHERE CON_ID = 4; -- PDB2

                   

                  The new PDB must be opened in read/write mode for Oracle Database to complete the integration of the new PDB into the CDB. While connected to root, issue the following command to change the open mode of PDB2.

                   

                  ALTER PLUGGABLE DATABASE PDB2 OPEN;

                   

                  Confirm you are connected to root.

                   

                  SHOW CON_NAME

                   

                  Run the following query to confirm the change to the open mode.

                   

                  SELECT NAME, OPEN_MODE FROM V$PDBS;

                   

                  Connect to PDB2 by setting the container for the current session. This requires knowledge of the service name. Remember that when a PDB is created, a service is created with the name of the PDB.

                   

                  ALTER SESSION SET CONTAINER = PDB2;

                   

                  Confirm you are connected to PDB2.

                   

                  SHOW CON_NAME CON_ID

                   

                  At this point, we can only connect to PDB2 if we are a common user and can change the container. To connect directly to PDB2 we need to update the tnsnames.ora file. There is one local user in PDB2. The administrator that was created automatically by the CREATE PLUGGABLE DATABASE statement we executed. In this statement, we provided the password in the IDENTIFIED BY clause.

                   

                  When creating a new PDB from the seed, an administrator must be specified for the PDB in the CREATE PLUGGABLE DATABASE statement. The statement creates the administrator as a local user in the PDB and grants the PDB_DBA role locally to the administrator. The ROLES option of the CREATE PLUGGABLE DATABASE statement allows you to grant predefined Oracle roles to the PDB_DBA role locally in the PDB. The new administrator for the PDB is granted the PDB_DBA common role locally in the PDB. The PDB_DBA has limited privileges.

                   

                  When we executed the CREATE PLUGGABLE DATABASE statement, we specified the DBA role would be granted to the local administrator account.

                   

                  The service has the same name as the PDB and can be used to access the PDB. Oracle Net Services must be configured properly for clients to access this service. The tnsnames.ora file can be updated to include an entry for PDB2 by using the Net Configuration Assistant.

                   

                  After adding the entry, connect to PBD2 as PDB2_ADMIN using the Oracle Net Services name.

                   

                  CONNECT PDB2_ADMIN/oracle@PDB2

                   

                  Verify the connection is to PDB2.

                   

                  SHOW CON_NAME CON_ID

                  • 6. Re: OracleXE 18c Error on first use
                    Dude!?

                    Doesn't 18c XE come with a multitenant database named XE already installed?

                     

                    Did you delete the database and try to recreate it? What are you trying to accomplish?

                    • 7. Re: OracleXE 18c Error on first use
                      The Insightful Professor

                      Installing Oracle Database 18c Express Edition will create a container database (CDB) named XE and one pluggable database (PDB) named XEPDB1.

                       

                      The steps I presented above can be used to create another PDB. This is apparently the objective here.

                       

                      While using the Database Configuration Assistant will cause these steps to be performed, I believe that it is important to understand the process

                      Furthermore, by having the operations for each step available, it is possible to use any of these commands to diagnose an installation issue.

                       

                      An explanation of the overall process is available through a video I posted on YouTube:

                      https://www.youtube.com/watch?v=GDuHuIrXzLA