Weloverwogen : Een duurzamere wereld begint bij jezelf
email : Password :
Oracle IT Misc
X session pass over a swith user

When, for example, installing oracle you need to have an X-session open.
In a lot of environments you ar not allowed to login to a server with a non-named account, for sercurity reasons.
So you first have to login with a named account, and then switch user to the desired account.

If you want to forward an X-session, for instance to do a (graphical) install of Oracle you have to make sure
that X will still be forwarded after the switch user.


Following might be a solution for you:

Make sure your system can handle X (Cygwin on windows/X on linux)
See if you can for instance run xclock.


[martijn@dtp13212 ~]$ xclock
Warning: Missing charsets in String to FontSet conversion
^C
[martijn@dtp13212 ~]$

If a xclock appeared on your screen your system is capable of handling X.

Now connect to the destination server with X-forwarding on with ssh:

[martijn@dtp13212 ~]$ ssh -X ambos_adm@oahu.hhs.nl
ambos_adm@oahu.hhs.nl s password: 
Last login: Wed Mar  2 09:17:45 2016 from dtp13212.org.ads.hhs.nl
/usr/bin/xauth:  creating new authority file /local/home/ambos_adm/.Xauthority
ambos_adm@oahu 23 $ 
ambos_adm@oahu 23 $ 
ambos_adm@oahu 23 $


Check whether you now have X using (for example) xclock

ambos_adm@oahu 23 $ xclock

ambos_adm@oahu 24 $
A xclock apeared on my screen so I am still good.

Now switch user to (for example oracle) and see whether you can use X
ambos_adm@oahu 24 $ sudo su - oracle

[sudo] password for ambos_adm: 
06:24 PM oracle@oahu.hhs.nl:~() $ xclock
X11 connection rejected because of wrong authentication.
X connection to localhost:10.0 broken (explicit kill or server shutdown).
06:25 PM oracle@oahu.hhs.nl:~() $

Apparently I can not use X.

This is because (as the message indicates) there is an authentication error.

To solve this we have to 1) export the display and 2) add the MIT-MAGIC-COOKIE to the users xauth list.

So... back to the user we logges in with to get the right DISPLAY to export and the right magic cookie:

ambos_adm@oahu 25 $ echo $DISPLAY
localhost:10.0
ambos_adm@oahu 26 $ xauth list
oahu.hhs.nl/unix:10  MIT-MAGIC-COOKIE-1  f58e9cb475295bd395c5c4372155bb77
ambos_adm@oahu 27 $


Switch back to the desired user and export the DISPLAY and add the MIT_MAGIC-COOKIE

ambos_adm@oahu 27 $ sudo su - oracle
06:29 PM oracle@oahu.hhs.nl:~() $ export DISPLAY=localhost:10.0
06:29 PM oracle@oahu.hhs.nl:~() $ xauth add oahu.hhs.nl/unix:10  MIT-MAGIC-COOKIE-1  f58e9cb475295bd395c5c4372155bb77


And now check whether you can use X, by again invoking xclock.
If an xclock appeared you have positive confirmation that you can use X as the desired user on the server.
X session pass over a swith user
Martijn2016-03-02 18:31:34
2016-03-02 18:30:002017-03-02 18:30:00

To which database am I connected

To which database am I connected: Have a look at global_name.
In the following TCDB is a container database which holds pluggable database DECOST.

Connect to the conatianer to have a look at global_name:

06:04 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ export ORACLE_SID=TCDB
06:04 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ . oraenv
The Oracle base remains unchanged with value /u01/app/oracle
06:05 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 18:05:03 2016

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


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

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
TCDB

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
06:05 PM oracle@hhs-ded3001.ads.hhs.nl:~()


And now have a look when in the pluggable database:

06:05 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus martijn/martijn@decost

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 18:05:13 2016

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

Last Successful login time: Wed Mar 02 2016 17:53:00 +01:00

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

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
DECOST

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
06:05 PM oracle@hhs-ded3001.ads.hhs.nl:~() $
To which database am I connected
Martijn2016-03-02 18:10:32
2016-03-02 18:15:002017-03-02 18:15:00

Conainer versus pluggable databases.

WARNING: These are my first steps into the container and pluggable database world. So a lot information could very well not be acurate at all.

Below stuff is tried on a RDBMS 12.1.0.2 64bit on RedHar Enterprise Linux 7.

The container database I created is TCDB
The pluggable database I created is DECOST

Entering a container database:
I can login with sqlplus / as sysdba

05:00 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ export ORACLE_SID=TCDB
05:00 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ . oraenv
The Oracle base has been changed from TCDB to /u01/app/oracle
05:00 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 17:00:50 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.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.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
05:00 PM oracle@hhs-ded3001.ads.hhs.nl:~() $


And I can connect by TNS:

05:03 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus sys/qwert97@TCDB as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 17:03:21 2016

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


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

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DECOST                         READ WRITE NO
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
05:03 PM oracle@hhs-ded3001.ads.hhs.nl:~() $


Entering a pluggable database:

Trying as sysdba

05:03 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ export ORACLE_SID=DECOST
05:05 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ . oraenv
The Oracle base remains unchanged with value /u01/app/oracle
05:05 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 17:06:03 2016

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

Connected to an idle instance.

SQL> exit
Disconnected
05:06 PM oracle@hhs-ded3001.ads.hhs.nl:~() $

Nope that is not working...

Then trying the same thing by TNS:

05:06 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus sys/qwerty97@decost as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 17:07:36 2016

Copyright (c) 1982, 2014, 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
05:07 PM oracle@hhs-ded3001.ads.hhs.nl:~() $

No chance.

Well then try to login with the pdbadmin (created while installing the pluggable database) user
First try it whithout TNS:


05:09 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus pdbadmin/qwert97

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 17:09:51 2016

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

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3640
Additional information: -494425873
Process ID: 0
Session ID: 0 Serial number: 0


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
05:09 PM oracle@hhs-ded3001.ads.hhs.nl:~() $

Also no luck.

Then I will try by TNS:


05:09 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus pdbadmin/qwert97@decost

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 17:10:34 2016

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

Last Successful login time: Wed Mar 02 2016 16:59:11 +01:00

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

SQL> 
SQL> 
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
05:11 PM oracle@hhs-ded3001.ads.hhs.nl:~() $

So...we are in.

Creating a user in the container:
This is just plain and simple as creating a user in a normal database.

Creating a user in the pluggable:


05:33 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ export ORACLE_SID=TCDB
05:34 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ . oraenv
The Oracle base remains unchanged with value /u01/app/oracle
05:34 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 17:34:08 2016

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


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

SQL> alter session set container=decost;

Session altered.

SQL> create user martijn identified by martijn;

User created.

SQL> grant create session to martijn;

Grant succeeded.

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


And try to connect:

05:34 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus martijn/martijn@decost

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 17:35:05 2016

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


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

SQL> select user from dual;

USER
------------------------------
MARTIJN

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
05:35 PM oracle@hhs-ded3001.ads.hhs.nl:~() $


Dropping a user from a pluggable database


05:49 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ export ORACLE_SID=TCDB
05:49 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ . oraenv
The Oracle base remains unchanged with value /u01/app/oracle
05:49 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 17:49:21 2016

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


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

SQL> alter session set container=decost;

Session altered.

SQL> drop user martijn cascade;

User dropped.

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


Now see whether we can still login with that user:

05:49 PM oracle@hhs-ded3001.ads.hhs.nl:~() $ sqlplus martijn/martijn@decost

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 17:49:48 2016

Copyright (c) 1982, 2014, 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
05:49 PM oracle@hhs-ded3001.ads.hhs.nl:~() $

Now we can not .....success
Container versus pluggable databases
Martijn2016-03-02 17:03:01
2016-03-02 18:00:002017-03-02 18:00:00