9 Replies Latest reply: Feb 14, 2014 5:06 PM by Dude! RSS

ORA-65096 installing Oracle 12cR1 Example Schemas

Dude! Guru
Currently Being Moderated

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 Oracle ACE
    Currently Being Moderated

    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! Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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! Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated
    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! Guru
    Currently Being Moderated

    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! Guru
    Currently Being Moderated

    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.