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!

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/[email protected] 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

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 <span style="color: #339966;">SH Users manually in PDB. After that you need connected to PDB as SYSDBA.</span>

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|[email protected]|g" hr_main.sql

$ sed -i "s|^$b|[email protected]|g" hr_main.sql

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

$ . oraenv

$ sqlplus [email protected] 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

1060455

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!

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/[email protected] 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

Unknown

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.

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.

Unknown

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.

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.

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.