Finally I’ve decided to publish the slides about Galera Cluster that I’ve presented twice, at the internal Trivadis TechEvent conference past April and at the Linux Day 2013.
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!
I’ve had the chance to propose a POC on MEM 3.0 to a customer currently relying on MEM 2.3, so we set up a VM to make some tests on it. I want to share my very first experience with it.
The binaries
The binaries you can download from edelivery belong to the “base” 3.0.2 version. But the release 3.0.3 is available since November 1st: in order to get it you must download two patchsets from the My Oracle Support. (Patches & Updates).
Patch 17721950: MySQL Enterprise Monitor Service Manager 3.0.3 for Linux x86 (64-bit)
Patch 17721960: MySQL Enterprise Monitor Agent 3.0.3 for Linux x86 (64-bit)
So, as it is for Oracle RDBMS, now the new versions are released as patchsets that can be installed from scratch.
The installation
The installation of the new product is as straightforward as it was for the previous release. You can still choose between using an existent MySQL database or a new one created my the installer. The installation steps have not changed at all, so I won’t list them here.
MEM3 comes with a new graphic vest, definitely more “Oracle style” and eye-candy. Globally I have to say that the experience is really improved. The overview page resumes well the overall availability, workload and alerts.
The new graph browser allows to choose the correspondent object from the left tree-view pane. You can see from the screenshot that now the agent collects data from the system and regroup the items differently, e.g. the filesystems are now separated objects belonging to the server.
New agent behavior
The new MySQL Enterprise Agent brings some benefits comparing to the old one. Now a single agent installation can monitor all database instances on the server and actively discover new ones.
When a new instance is discovered is not monitored by default: an event is raised so you can create an handle that sends you an email that reminds to enable the monitoring for that instance.
Other remarks
The Query Analyzer is now active by default for instances that have the performance_schema enabled.
Many new graphs are displayed by default providing great information on database statistics trends.
The Event Handling is simpler to manage IMO, but the only choices are still sending an email and/or an SNMP trap. I regret that it’s still impossible to take actions like hooking shell scripts or send WS calls.
The other functionalities are still present and basically unchanged.
Conclusion
Oracle have done a very good job in restyling everything. The new Enterprise Monitor dashboard is really responsive and I haven’t found bugs so far. But I have to say that there is still a lack of features that would make it really “enterprise ready”, such as a plugin mechanism that would allow to interact directly with common event management softwares like EM12c, Tivoli, Remedy or CA SM without relying on SNMP only. Also, it would be great to run actions directly when an event raises.
The POC however has been a complete success and we’ll start next week with the definitive migration! π
NOTE: The maximum number of database instances per cluster is 512 for Oracle 11g Release 1 and higher. An upper limit of 128 database instances per X2-2 or X3-2 database node and 256 database instances per X2-8 or X3-8 database node is recommended. The actual number of database instances per database node or cluster depends on application workload and their corresponding system resource consumption.
But how many instances are actually beeing consolidated by DBAs from all around the world?
I’ve asked it to the Twitter community
I’ve sent this tweet a couple of weeks ago and I would like to consolidate some replies into a single blog post.
who has done more than this on a single server? $ ps -eaf | grep ora_pmon | wc -l 77 #oracle#consolidation
Does this thread of tweets reply to the question? Are you planning to consolidate your Oracle environment? If you have questions about how to plan your consolidation, don’t hesitate to get in touch! π
Even if he was already a world-known performance specialist, this announcement makes me one more time proud to know him. Well deserved Chris! π
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.