Create simple user in 12c pluggable database

Here’s a summary of my first attempt at creating a very basic user in a new pluggable database rather than the ‘old style’ normal database…….

As I’m lazy we set up this alias for sys as sysdba access…..

[oracle@server-name]:ED12G:[~]# alias s='sqlplus / as sysdba'

Let’s log on and get a list of the pluggable databases

[oracle@server-name]:ED12G:[~]# s

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 22 18:54:38 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA12GCONTAINER                READ WRITE NO

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/ED12G/oradata/orcl/system01.dbf
/oracle/ED12G/oradata/orcl/sysaux01.dbf
/oracle/ED12G/oradata/orcl/undotbs01.dbf
/oracle/ED12G/oradata/orcl/pdbseed/system01.dbf
/oracle/ED12G/oradata/orcl/users01.dbf
/oracle/ED12G/oradata/orcl/pdbseed/sysaux01.dbf
/oracle/ED12G/oradata/orcl/ORA12GCONTAINER/system01.dbf
/oracle/ED12G/oradata/orcl/ORA12GCONTAINER/sysaux01.dbf
/oracle/ED12G/oradata/orcl/ORA12GCONTAINER/ORA12GCONTAINER_users01.dbf

9 rows selected.

Now to create a new pluggable db as a copy of another one (bit of trial and error here)

SQL>
SQL> alter pluggable database ORA12GCONTAINER read only;
alter pluggable database ORA12GCONTAINER read only
                                         *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL>  alter pluggable database ORA12GCONTAINER close;

Pluggable database altered.

SQL> create pluggable database test from ORA12GCONTAINER file_name_convert=('/oracle/ED12G/oradata/orcl/ORA12GCONTAINER','/oracle/ED12G/oradata/orc                                                  l/test');
create pluggable database test from ORA12GCONTAINER file_name_convert=('/oracle/ED12G/oradata/orcl/ORA12GCONTAINER','/oracle/ED12G/oradata/orcl/test')
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode


SQL> alter pluggable database ORA12GCONTAINER open read only;

Pluggable database altered.

SQL> create pluggable database test from ORA12GCONTAINER file_name_convert=('/oracle/ED12G/oradata/orcl/ORA12GCONTAINER','/oracle/ED12G/oradata/orc                                                  l/test');
create pluggable database test from ORA12GCONTAINER file_name_convert=('/oracle/ED12G/oradata/orcl/ORA12GCONTAINER','/oracle/ED12G/oradata/orcl/tes                                                  t')
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level
ORA-19504: failed to create file "/oracle/ED12G/oradata/orcl/test/system01.dbf"


SQL> !mkdir /oracle/ED12G/oradata/orcl/test
mkdir: cannot create directory `/oracle/ED12G/oradata/orcl/test': File exists

SQL> !ls /oracle/ED12G/oradata/orcl/test
ORA12GCONTAINER_users01.dbf  sysaux01.dbf  system01.dbf

SQL> !rm /oracle/ED12G/oradata/orcl/test/*

SQL> set timing on
SQL>  create pluggable database test from ORA12GCONTAINER file_name_convert=('/oracle/ED12G/oradata/orcl/ORA12GCONTAINER','/oracle/ED12G/oradata/orcl/test');

Pluggable database created.

Elapsed: 00:00:13.40
SQL> !ls /oracle/ED12G/oradata/orcl/test
ORA12GCONTAINER_users01.dbf  sysaux01.dbf  system01.dbf  temp01.dbf

SQL> alter pluggable database test open;

Pluggable database altered.

Elapsed: 00:00:03.71

Now we have a new ‘database’ but it seems service_names is not updated as a database parameter even though it is registered in the listener – so it seems to do it via the existing registration somehow

SQL> sho parameter servic

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      orcl.e-ssi.net
SQL> !lsnrctl stat

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 22-JUL-2013 19:05:14

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1525)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                05-JUL-2013 11:15:00
Uptime                    17 days 7 hr. 50 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/12.0.0/network/admin/listener.ora
Listener Log File         /oracle/diag/tnslsnr/server-name/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server-name.e-ssi.net)(PORT=1525)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=server-name.e-ssi.net)(PORT=5500))(Security=(my_wallet_directory=/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ED12GXDB.e-ssi.net" has 1 instance(s).
  Instance "ED12G", status READY, has 1 handler(s) for this service...
Service "ora12gcontainer.e-ssi.net" has 1 instance(s).
  Instance "ED12G", status READY, has 1 handler(s) for this service...
Service "orcl.e-ssi.net" has 1 instance(s).
  Instance "ED12G", status READY, has 1 handler(s) for this service...
Service "test.e-ssi.net" has 1 instance(s).
  Instance "ED12G", status READY, has 1 handler(s) for this service...
The command completed successfully

Tracking through the listener log we see no mention of the registration – just an update which must include it

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@server-name]:ED12G:[~]# cd /oracle/diag/tnslsnr/server-name/listener/
[oracle@server-name]:ED12G:[/oracle/diag/tnslsnr/server-name/listener]# ls
alert  cdump  incident  incpkg  lck  log  metadata  metadata_dgif  metadata_pv  stage  sweep  trace
[oracle@server-name]:ED12G:[/oracle/diag/tnslsnr/server-name/listener]# cd trace
[oracle@server-name]:ED12G:[/oracle/diag/tnslsnr/server-name/listener/trace]# ls -rlt
total 5248
-rw-r----- 1 oracle oinstall 5361454 2013-07-22 19:06 listener.log
[oracle@server-name]:ED12G:[/oracle/diag/tnslsnr/server-name/listener/trace]# tail -200 listener.log
22-JUL-2013 19:05:34 * service_update * ED12G * 0
22-JUL-2013 19:06:15 * ping * 0
WARNING: Subscription for node down event still pending
22-JUL-2013 19:06:15 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=server-name)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=server-name.e-ssi.net)(PORT=1525)))(VERSION=202375424)) * status * 0
22-JUL-2013 19:06:43 * service_update * ED12G * 0

Now let’s create a user in the new test container (again trial and error here)

[oracle@server-name]:ED12G:[/oracle/diag/tnslsnr/server-name/listener/trace]# s

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 22 19:08:17 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user test identified by test container=test;
create user test identified by test container=test
                                              *
ERROR at line 1:
ORA-65013: invalid CONTAINER clause


SQL>  create user test identified by test container=(test);
 create user test identified by test container=(test)
                                               *
ERROR at line 1:
ORA-65013: invalid CONTAINER clause


SQL>  create user test identified by test container='test';
 create user test identified by test container='test'
                                               *
ERROR at line 1:
ORA-65013: invalid CONTAINER clause


SQL> alter session set container=test;

Session altered.

SQL> create user test identified by test container=current;
create user test identified by test container=current
            *
ERROR at line 1:
ORA-01920: user name 'TEST' conflicts with another user or role name

This was a surprise – it seems my earlier attempt at creating a user in an old test pluggable db still exists in the container db metadata!

SQL> drop user test;

User dropped.

SQL> create user test identified by test container=current;

User created.

SQL> exit

Now lets log on (again a bit of trial and error)

[oracle@server-name]:ED12G:[~]# sqlplus test/test@//localhost:1525/test.e-ssi.net

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 11:28:29 2013

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

ERROR:
ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@server-name]:ED12G:[~]#
[oracle@server-name]:ED12G:[~]# s

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 11:28:42 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt

SQL> grant create session to test;
grant create session to test
                        *
ERROR at line 1:
ORA-01917: user or role 'TEST' does not exist




SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORA12GCONTAINER                READ ONLY  NO
         4 TEST                           READ WRITE NO
SQL> alter session set container=test;

Session altered.

SQL> grant create session to test;

Grant succeeded.

SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@server-name]:ED12G:[~]# sqlplus test/test@//localhost:1525/test.e-ssi.net

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 11:29:38 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Now with a useful trick of using the TWO_TASK environment variable we can log on directly without having to specify @NEW_SERVICE_NAME

[oracle@server-name]:ED12G:[~]# export TWO_TASK=//localhost:1525/test.e-ssi.net
[oracle@server-name]:ED12G:[~]# sqlplus test/test

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 11:29:58 2013

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

Last Successful login time: Tue Jul 23 2013 11:29:38 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> sho pdbs
ERROR:
ORA-00942: table or view does not exist


SP2-1545: This feature requires Database availability.
SQL> select * from v$database;
select * from v$database
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> alter session set sql_trace=TRUE;
ERROR:
ORA-01031: insufficient privileges


SQL> exit

Let’s grant it create session and track down what it’s trying to select from (again trial and error here to some degree)

 [oracle@server-name]:ED12G:[~]# s

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 11:34:21 2013

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@server-name]:ED12G:[~]# unset TWO_TASK
[oracle@server-name]:ED12G:[~]# s

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 11:34:33 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=test;

Session altered.

SQL> grant alter session to test;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@server-name]:ED12G:[~]# export TWO_TASK=//localhost:1525/test.e-ssi.net
[oracle@server-name]:ED12G:[~]# sqlplus test/test

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 11:34:59 2013

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

Last Successful login time: Tue Jul 23 2013 11:29:58 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> sho pdbs
ERROR:
ORA-00942: table or view does not exist


SP2-1545: This feature requires Database availability.
SQL> alter session set sql_trace=true;

Session altered.

SQL> sho pdbs
ERROR:
ORA-00942: table or view does not exist


SP2-1545: This feature requires Database availability.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

Now we find the trace file and discover what sqlplus wants to look at

[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# unset TWO_TASK
[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# s

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 11:36:30 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter session set container=test;

Session altered.

SQL> grant select on v$pdbs to test;
grant select on v$pdbs to test
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


SQL> grant select on v_$pdbs to test;

Grant succeeded.

SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# export TWO_TASK=//localhost:1525/test.e-ssi.net
[oracle@server-name]:ED12G:[/oracle/diag/rdbms/orcl/ED12G/trace]# sqlplus test/test

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 23 11:37:14 2013

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

Last Successful login time: Tue Jul 23 2013 11:34:59 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 TEST                           READ WRITE NO
SQL>

And now we are in with a basic user and we can see which container name we are connected to.

2 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hi can you let me know can i login with sqldevloper using other users??

    ReplyDelete