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.
This comment has been removed by the author.
ReplyDeleteHi can you let me know can i login with sqldevloper using other users??
ReplyDelete