9 Replies Latest reply: Feb 14, 2014 6:32 PM by rp0428 RSS

    ORA-65096 installing Oracle 12cR1 Example Schemas

    Dude!

      Hi,

       

      Database: 12cR1, 12.0.1.0, x64

      System: Oracle Linux 6.4, x64

       

      I installed a simple starter database during the installation of Oracle Database 12cR1 with the PDBORCL option selected (default) and would like to install the Oracle Example data, such as users HR schema, etc. according to http://docs.oracle.com/cd/E16655_01/server.121/e15979/installation.htm#I4367

       

      @?/demo/schema/human_resources/hr_main.sql

      CREATE USER hr IDENTIFIED BY hr

                  *

      ERROR at line 1:

      ORA-65096: invalid common user or role name

       

       

      Perhaps the scripts were not designed for container and plug-able databases. I did the following:

       

      alter pluggable database pdborcl open;

      alter session set container=PDBORCL;

      @?/demo/schema/human_resources/hr_main.sql

      User altered.

      Grant succeeded.

      Grant succeeded.

      Connected.

      GRANT execute ON sys.dbms_stats TO hr

                                         *

      ERROR at line 1:

      ORA-01917: user or role 'HR' does not exist

       

      I could probably create a normal database, but I wonder if there is any other option without having to modify the setup scripts.

       

      Thanks!

        • 1. Re: ORA-65096 installing Oracle 12cR1 Example Schemas
          HDeiby

          Hello,

           

          In the link Installing Sample Schemas say that you need create the schemas manually (HR, OE,PM, IX, and SH) before run the hr_main.sql.

           

          I will try explain you why you got error:

           

          @?/demo/schema/human_resources/hr_main.sql

          CREATE USER hr IDENTIFIED BY hr

                      *

          ERROR at line 1:

          ORA-65096: invalid common user or role name

           

          You got error because you are trying create HR as a common user. In 12c, if you are connected to CDB and you don't include "CONTAINER" it will create as Common User. If you are connected to PDB and you don't include "CONTAINER" it will create as LOCAL User.

          To create Common User the username need start with "C##", so "HR" doesn't start with "C##" for that you got "ORA-65096: invalid common user or role name"

           

           

          alter pluggable database pdborcl open;

          alter session set container=PDBORCL;

          @?/demo/schema/human_resources/hr_main.sql

          User altered.

          Grant succeeded.

          Grant succeeded.

          Connected.

          GRANT execute ON sys.dbms_stats TO hr

                                             *

          ERROR at line 1:

          ORA-01917: user or role 'HR' does not exist

           

          you got error because HR is not created in PDB. You need create HR, OE,PM, IX, and SH Users manually in PDB. After that you need connected to PDB as SYSDBA.

          • 2. Re: ORA-65096 installing Oracle 12cR1 Example Schemas
            Dude!

            Please note: Setting the TWO_TASK environment variable as mentioned further down below seems to be the more elegant solution. I therefore modified the instructions below:

             

            Thank you for the response and trying to help, but the solution was different.

             

            I don't know whether or not the following is the right way, but it worked:

             

            1. Edit /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora to include the PDB name, e.g. pdborcl.

             

            PDBORCL =

              (DESCRIPTION =

                (ADDRESS = (PROTOCOL = TCP)(HOST = vm21)(PORT = 1521))

                (CONNECT_DATA =

                  (SERVER = DEDICATED)

                  (SERVICE_NAME = pdborcl)

                )

              )

             

            2. Modify the hr_main.sql script to connect to the PDB:

            $ cd /u01/app/oracle/product/12.1.0/dbhome_1/demo/schema/human_resources

            $ cp hr_main.sql hr_main.sql_orig

            $ a='CONNECT sys/\&pass_sys'

            $ b='CONNECT hr/\&pass'

            $ sed -i "s|^$a|$a@pdborcl|g" hr_main.sql

            $ sed -i "s|^$b|$b@pdborcl|g" hr_main.sql

            3. Set the shell environment to the SID of CBD (orcl) and connect to the PDB (pdborcl):

            $ . oraenv

            $ sqlplus sys@pdborcl as sysdba

             

            2. Configure the shell environment for the CDB (orcl) and set the TWO_TASK variable to the PDB (pdborcl)

             

            $ . oraenv

            $ export TWO_TASK=pdborcl

            $ sqlplus sys/password as sysdba

            SQL*Plus: Release 12.1.0.1.0 Production on Sat Feb 15 01:17:59 2014

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

            ...

            SQL> sho con_name

            PDBORCL

             

            3. Follow the documentation and execute the script:

             

            SQL> @?/demo/schema/human_resources/hr_main.sql

            ...

            PL/SQL procedure successfully completed.


            Don't forget to unset the TWO_TASK variable.

            $ unset TWO_TASK

            • 3. Re: ORA-65096 installing Oracle 12cR1 Example Schemas
              35e541da-a036-4e3f-8c09-e3706546f689

              Your solution was just what i was looking for.

              I was trying to connect Autodesk Map 3D to Oracle. It automatically creates the spatial user and it wasn't designed for containers.

              I added the PDB (pdborcl) to Service Naming in Net Manager. After this i just connected using a common user and the PDB as Service Name. It created its users with no problem.

               

              Thank you!

              • 4. Re: ORA-65096 installing Oracle 12cR1 Example Schemas
                polosoft.com

                Hi,

                 

                You can just set TWO_TASK environment variable before setting up your sample schema.


                For ex:

                export TWO_TASK=PDBORCL

                sqlplus sys/passwd@PDBORCL as sysdba

                @?/demo/schema/human_resources/hr_main.sql


                Indeed, when TWO_TASK is set, the client connections by default will use TNS and alias in your tnsnames.ora instead of local connection and ORACLE_SID.

                 

                "The TWO_TASK (on UNIX) or LOCAL (on Windows) environment variable can be set to a connection identifier. This removes the need to explicitly enter the connection identifier whenever a connection is made in SQL*Plus "

                http://docs.oracle.com/cd/E16655_01/server.121/e18404/apd.htm


                Kind regards,


                Alex

                • 5. Re: ORA-65096 installing Oracle 12cR1 Example Schemas
                  rp0428

                  Ok - except that was NOT the problem OP had at all.

                   

                  OP did an install that INCLUDED the sample schema; there was no 'before setting up your sample schema' option. OP just didn't realize that the sample schema was in the PDB (PDBORCL) and that you have to connect to a PDB using a SERVICE_NAME, not a SID. OP didn't have an entry in their TNS file for the PDB.


                  • 6. Re: ORA-65096 installing Oracle 12cR1 Example Schemas
                    Dude!

                    As far as I remember the sample schema was not included when I did a default installation. The user "hr" for instance, did not exist, and I wanted to run the demo sql scripts to create it.

                     

                    The problem with the demo scripts are that they connect using "sys/password as sysdba", and do not prompt for the PDB name. Connections will therefore be made to the CDB relying on the ORACLE_SID, which causes an error because creating non-common users in CDB$ROOT is not allowed.

                     

                    As far as I can tell setting the TWO_TASK variable is a very elegant solution and it works fine - I just tested it. Of course, it is still necessary to have the PDB service name in tnsnames.ora, but there is no more need to modify the demo scripts.

                    • 7. Re: ORA-65096 installing Oracle 12cR1 Example Schemas
                      rp0428
                      As far as I remember the sample schema was not included when I did a default installation

                      Well I don't know since I wasn't there. But this is what you said:

                      I installed a simple starter database during the installation of Oracle Database 12cR1 with the PDBORCL option selected (default)

                      That 'starter' dabase would include the sample schemas and data.

                       

                      The important thing is that you got your problem resolved.

                      • 8. Re: ORA-65096 installing Oracle 12cR1 Example Schemas
                        Dude!

                        Thanks for the e-mail and wonderful solution. Just a note, specifying the PDB in the connect string is not even necessary. It's also important people don't forget to unset the TWO_TASK variable when done.

                        • 9. Re: ORA-65096 installing Oracle 12cR1 Example Schemas
                          Dude!

                          The dbca app in 12c actually does not mention starter database anymore, like it did in 11g.

                           

                          When you create a database in advanced mode, there is a checkbox "Sample Schemas", which is not selected by default. But I just used dbca to create another cdb database using the default (not advanced mode) and the pdb does also not include the hr user.