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.

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

Post a Comment