echo"#### IT MAY TAKE SOME MINUTES BEFORE EVERYTHING START WORKING ####"
read-p""
dgmgrl-echosys/manager<<EOF
show configuration
EOF
For the demo I’ve used 5 machines running 3 database instances and 2 Far Sync instances. I cannot provide the documentation for creating the demo environment, but the scripts may be useful to understand how the demo works.
As already pointed by the existing articles, I can’t create a common user into the root container without the c## prefix, unless I’m altering the hidden parameter _common_user_prefix.
PgSQL
1
2
3
4
5
SQL>createusergoofyidentifiedbypippo;
createusergoofyidentifiedbypippo
*
ERRORatline1:
ORA-65096:invalidcommonuserorrolename
so I specify the correct prefix, and it works:
Oracle PL/SQL
1
2
3
4
5
6
7
SQL>createuserC##GOOFYidentifiedbypippo;
Usercreated.
SQL>grantcreatesession,altersessiontoc##goofy;
Grantsucceeded.
The user is common, so it appears in all the containers, I can check it by querying CDB_USERS from the root container.
Note that the error ORA-65049 is different from the ORA-65096 that I’ve got before.
My conclusion is that the clause container of the create role and create user statements doesn’t make sense as you can ONLY create common users and roles into the root container and only local ones into the PDBs.
Creating a local role
Just as experiment, I’ve tried to see if I can create a local role with container=ALL. It doesn’t work:
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
SQL>altersessionsetcontainer=hr;
Sessionaltered.
SQL>createroleREGION_ROLEcontainer=ALL;
createroleREGION_ROLEcontainer=ALL
*
ERRORatline1:
ORA-65050:CommonDDLsonlyallowedinCDB$ROOT
So I create the local role with container=current:
Oracle PL/SQL
1
2
3
SQL>createroleREGION_ROLEcontainer=CURRENT;
Rolecreated.
Now, from the PDB I can see the two roles I can access, whereas from the root I can see all the roles I’ve defined so far: the common role is available from all the PDBs, the local role only from the db where it has been defined, just like the users.
From the root I can’t give grants on objects that reside in a PDB since I cannot see them: I need to connect to the PDB and give the grants from there:
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL>grantselectonhr.countriestoC##country_role;
grantselectonhr.countriestoC##country_role
*
ERRORatline1:
ORA-00942:tableorviewdoesnotexist
SQL>altersessionsetcontainer=hr;
Sessionaltered.
SQL>grantselectonhr.countriestoC##COUNTRY_ROLE;
Grantsucceeded.
SQL>grantselectonhr.regionstoREGION_ROLE;
Grantsucceeded.
Now, if I query CDB_TAB_PRIVS from the root, I see that the grants are given at a local level (con_id=3 and common=N):
give the grant commonly while connected to the root:
Oracle PL/SQL
1
2
3
4
5
6
7
SQL>altersessionsetcontainer=cdb$root;
Sessionaltered.
SQL>grantC##COUNTRY_ROLEtoc##goofycontainer=all;
Grantsucceeded.
I can also grant locally both roles and system privileges to common users while connected to the root container: in this case the privileges are applied to the root container only. Finally having the clause container finally starts to make sense:
Ok, I’ve given the grants and I’ve never verified if they work, so far.
Let’s try with theselect any table privilege I’ve given in the last snippet. I expect C##GOOFY to select any table from the root container and only HR.COUNTRIES and HR.REGIONS on the HR PDB bacause they have been granted through the two roles.
Now I see that the grants give two distinct permissions : one local and the other common.
If I revoke the grants without container clause, actually only the local one is revoked and the user can continue to login. To revoke the grants I would need to check and revoke both local and common privileges.
After the first revoke statement, I can still connect to HR and verify that my select any table privilege doesn’t apply to the PDB as it’s local to the root container:
Oracle PL/SQL
1
2
3
4
5
6
7
SQL>connectC##GOOFY/pippo@node4:1521/hr
Connected.
SQL>selectcount(*)fromhr.DEPARTMENTS;
selectcount(*)fromhr.DEPARTMENTS
*
ERRORatline1:
ORA-00942:tableorviewdoesnotexist
After that, I want to check the privileges given through the local and common roles.
I expect both users to select from hr.countries and hr.regions since they have been granted indirectly by the roles.
Let’s try the local user first:
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL>connectgoofy/pippo@node4:1521/hr
Connected.
SQL>selectcount(*)fromhr.regions;
COUNT(*)
----------
4
1rowselected.
SQL>selectcount(*)fromhr.countries;
COUNT(*)
----------
25
1rowselected.
Yeah, it works as expected.
Now let’s try the common user:
Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL>connectc##goofy/pippo@node4:1521/hr
Connected.
SQL>selectcount(*)fromhr.regions;
COUNT(*)
----------
4
1rowselected.
SQL>selectcount(*)fromhr.countries;
COUNT(*)
----------
25
1rowselected.
It also work, so everything is ok.
Common and local grants, why you must pay attention
During the example, I’ve granted the C##COUNTRY_ROLE many times: locally to PDB, locally to the ROOT, commonly. The result is that I’ve flooded the grant table with many entries:
When granting privileges from the root container, keep in mind that container=current is the default even when the grantee or the role granted are common.
When revoking the grants with a Multitenant architecture, keep in mind that there is a scope and you may need more than one statement to actually remove the grant from all the scopes.
As always, I look for opinions and suggestions, feel free to comment!
But Oracle has fixed this twice, in the new release it’s possible to use identity columns as well, avoiding the necessity to create explicitly a new sequence:
Oracle instances on Unix/Linux servers have been composed historically by separated server processes to allow the database to be multi-user, in opposite with Windows that has always been multithread (Oracle 7 on MS-DOS was a single-user process, but this is prehistory…). The background processes number has increased to support all the new features of Oracle, up to this new Oracle 12c release. On a simple database installation you’ll be surprised to have this output from a ps command (38 processes):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# ps -eaf | grep CLASSIC | grep -v grep
oracle35821021:59?00:00:00ora_pmon_CLASSIC
oracle35841021:59?00:00:00ora_psp0_CLASSIC
oracle35901421:59?00:00:51ora_vktm_CLASSIC
oracle35961021:59?00:00:00ora_gen0_CLASSIC
oracle35991021:59?00:00:00ora_mman_CLASSIC
oracle36081021:59?00:00:00ora_diag_CLASSIC
oracle36121021:59?00:00:00ora_dbrm_CLASSIC
oracle36161021:59?00:00:00ora_dia0_CLASSIC
oracle36201021:59?00:00:00ora_dbw0_CLASSIC
oracle36241021:59?00:00:04ora_lgwr_CLASSIC
oracle36281021:59?00:00:00ora_ckpt_CLASSIC
oracle36321021:59?00:00:00ora_smon_CLASSIC
oracle36361021:59?00:00:00ora_reco_CLASSIC
oracle36401021:59?00:00:00ora_lreg_CLASSIC
oracle36441021:59?00:00:00ora_rbal_CLASSIC
oracle36481021:59?00:00:00ora_asmb_CLASSIC
oracle36521021:59?00:00:01ora_mmon_CLASSIC
oracle36591021:59?00:00:00ora_mmnl_CLASSIC
oracle36641021:59?00:00:00ora_d000_CLASSIC
oracle36671021:59?00:00:00ora_s000_CLASSIC
oracle36721021:59?00:00:00ora_mark_CLASSIC
oracle37071021:59?00:00:01ora_o000_CLASSIC
oracle37171021:59?00:00:01ora_o001_CLASSIC
oracle37251021:59?00:00:00ora_tmon_CLASSIC
oracle37291021:59?00:00:00ora_tt00_CLASSIC
oracle37361021:59?00:00:00ora_smco_CLASSIC
oracle37381022:00?00:00:00ora_w000_CLASSIC
oracle37491022:00?00:00:00ora_fbda_CLASSIC
oracle37511022:00?00:00:00ora_aqpc_CLASSIC
oracle37571022:00?00:00:00ora_qm02_CLASSIC
oracle37591022:00?00:00:00ora_p000_CLASSIC
oracle37631022:00?00:00:00ora_p001_CLASSIC
oracle37651022:00?00:00:00ora_q002_CLASSIC
oracle37671022:00?00:00:00ora_p002_CLASSIC
oracle37691022:00?00:00:00ora_q003_CLASSIC
oracle37711022:00?00:00:00ora_p003_CLASSIC
oracle37741022:00?00:00:00ora_cjq0_CLASSIC
oracle38011022:00?00:00:02ora_vkrm_CLASSIC
If you have consolidated many databases without the pluggable database feature, you’ll end up to have several hundreds of processes even without users connected. But Oracle 12c now introduce the possibility to start an instance using multithreading instead of the traditional processes. This could lead to some optimizations due to the shared process memory, and reduced context switches overhead, I presume (need to test it).
Enabling the Multithreaded Execution
By default this feature is not enabled, so you have to set it explicitly:
1
2
3
4
5
SQL>alter system set threaded_execution=truescope=spfile;
System altered.
SQL>
And in parallel, you’ll need to add this line to the listener.ora:
1
DEDICATED_THROUGH_BROKER_listener=on
After a restart, the instance will show only a bunch of processes:
So we have the Process Monitor (pmon), the Process Spawner (psp0), the Virtual Keeper of Time (vktm), the Database Writer (dbw0) and two new multithreaded processes (u004) and (u005). “U” can stand for User or Unified?
Where can I find the information on the other processes?
They still exist in the v$process view, thus leading to some confusion when talking about Oracle Processes with your sysadmins… The new EXECUTION_TYPE column show if the Oracle Process is executed as a thread or as an OS process, and the SPID let us know which process actually executes it.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
PID SPID PNAME EXECUTION_
-----------------------------
24792PMON PROCESS
34794PSP0 PROCESS
44800VKTM PROCESS
54804GEN0 THREAD
64804SCMN THREAD
184804LREG THREAD
194804RBAL THREAD
204804ASMB THREAD
114804DBRM THREAD
144804LGWR THREAD
154804CKPT THREAD
164804SMON THREAD
74804MMAN THREAD
174810RECO THREAD
124810DIA0 THREAD
104810SCMN THREAD
94810DIAG THREAD
254810N000 THREAD
504810Q002 THREAD
494810W004 THREAD
214810MMON THREAD
224810MMNL THREAD
234810D000 THREAD
244810S000 THREAD
514810Q003 THREAD
264810MARK THREAD
274810W001 THREAD
284810THREAD
294810THREAD
304810TMON THREAD
314810TT00 THREAD
324810SMCO THREAD
334810FBDA THREAD
344810W000 THREAD
354810AQPC THREAD
364810CJQ0 THREAD
374810P000 THREAD
384810P001 THREAD
394810P002 THREAD
404810P003 THREAD
414810VKRM THREAD
424810THREAD
434810O000 THREAD
454810W002 THREAD
464810QM02 THREAD
474810W003 THREAD
134818DBW0 PROCESS
84884PROCESS
1NONE
What about the User processes?
Well, I’ve spawned 200 user processes with sqlplus, and got 200 threads:
1
2
3
4
5
6
7
8
9
10
SQL>select BACKGROUND,EXECUTION_TYPE,count(*)
2>fromv$process group by background,EXECUTION_TYPE;
BEXECUTION_ COUNT(*)
---------------------
1PROCESS4
1THREAD34
PROCESS1
NONE1
THREAD200
On the OS side, I’ve registered an additional process to distribute the load of the new user processes. Damn, I start to being confusional using the term “process” o_O
By using the multithreaded execution, the operating system authentication doesn’t work.
1
2
3
4
5
6
7
8
9
10
[oracle@luc12c01~]$sqlplus/assysdba
SQL*Plus:Release12.1.0.1.0Production on Fri May1001:14:172013
Copyright(c)1982,2013,Oracle.All rights reserved.
ERROR:
ORA-01017:invalid username/password;logon denied
Enter user-name:
Unless Oracle will review it’s authentication mechanism in a future patchset, you’ll need to rely on the password file and use the password to connect to the instance as sysdba, even locally.
What about performance?
In theory, threads should be faster and with a lower footprint:
The main benefit of threads (as compared to multiple processes) is that the context switches are much cheaper than those required to change current processes. Sun reports that a fork() takes 30 times as long as an unbound thread creation and 5 times as long as a boundthread creation.
In some operating systems running on some hardware, switching between threads belonging to the same process is much faster than switching to a thread from different process (because it requires more complicated process context switch). http://en.wikipedia.org/wiki/Thread_switching_latency
In practice, I’ll do some tests and let you know! 🙂
What about the good old OS kill command to terminate processes?
Good question! Currently I have not found any references to an orakill command (that exists on Windows). Hope it will arrive soon!
Cheers
—
Ludo
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.Accept
Privacy & Cookies Policy
Privacy Overview
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.