Speaker and Ninja at Collaborate14 – #C14LV

Posted on by
Reply

COLLABORATE 14 IOUG Forum

This year I will have the honor to present at Collaborate14, from April 7th to 11th. First of all, many thanks to Trivadis that has kindly agreed to send me to the conference.

My session (#603):
Oracle Data Guard 12c: Real-Time Cascade, Far Sync Instances and other goodies
has been accepted, so if you plan to attend Collaborate, I will be glad to see you there!
My paper and presentation are ready, but I’ll wait the post-conference before publishing them. Meanwhile, you can get a little sneak peak of my live demo (I’ll cut something, somewhere, but my new SSD disk should reduce the time elapsed, I have to do it again with the new hardware to get correct timings 🙂 ). There’s no audio, since it’s supposed to be my failover demo if I’ll have problems during my session.

Part I


Part II

I’ve submitted another abstract about Policy Managed Databases, but it has been put in the waiting list, assuming that Data Guard has a lot more users and the interest in new Data Guard 12c features will be higher than PMDBs that are rarely used in production environments (and I’m sad about it, keep in touch if you want to know more about this great technology).

 

RAC Attack 12c!

I’ll be organizing the RAC Attack again, along with Seth Miller, Yury Velikanov and Kamran Agayev. Sharing this exciting role with an Oracle ACE and two ACE Directors makes me  proud of what I’m doing, but more than this, I’m happy to repeat another exciting experience like I had at OOW13.

This Year RAC Attack will be an official pre-conference workshop. We have been contacted directly by the IOUG, and we’re making improvements. We’ll install RAC 12c and discuss about advanced topics, have a lot of fun, drink a beer together and jump a lot! 🙂

Other mentors at the workshop will be Leighton Nelson, Maaz Anjum, Biju Thomas. You should know them already, so join us!

And don’t forget, register before February 12th, so you take benefit of the early bird discount!

—
Ludovico

Removing passwords from Oracle scripts: Wallets and Proxy Users

Posted on by
10


Very often I encounter customers that include Oracle account passwords in their scripts in order to connect to the database.
For DBAs, sometimes things are
easier when they run scripts locally using the oracle account, since the “connect / as sysdba” usually do the job, even with some security concerns. But what if we need other users or we need to connect remotely?
Since longtime Oracle supplies secure wallets and the proxy authentication. Let’s see what they are and how to use them.

Secure Wallet
Secure wallets are managed through the tool mkstore. They allow to store a username and password in a secure wallet accessible only by its owner. The wallet is then accessed by the Oracle Client to connect to a remote database, meaning that you DON’T HAVE to specify any username and password!

Let’s see how to implement this the quick way:

Create a directory that will contain your wallet:

Create the wallet, use an arbitrary complex password to protect it:

Immagine that you’ve a user created with a very complex password:

Then you need to insert these credentials, including the connect string, into the wallet.

Keep in mind that you can have multiple credentials in the wallet for different remote descriptors (connect strings), but if you want many credentials for the very same connect string you need to create different wallets in different directories.

Now you need to tell your Oracle Client to use that wallet by using the wallet_location parameter in your sqlnet.ora, so you need to have a private TNS_ADMIN:

If everything’s alright, you should be able to connect to the database PROD as the batch user, without specifying any username or password.

Attention: when mkstore modifies the wallet, only the clients with the same or above versions will be able to access the wallet, so if you access your wallet with a 11g client you shouldn’t modify the wallet with a 12c version of mkstore. This is not documented by Oracle, but you can infer it from different “not a bug” entries on MOS 🙂

Proxy Users
You can push things a little farther, and hook your wallet with a proxy user, in order to connect to arbitrary users. That’s it, a proxy user is entitled to connect to the database on behalf of other users. In this example, we’ll see how, through the batch account, we can connect as OE, SH or HR:

Now I can specify with which user I want to work on the DB, connect to it through the batch account, without specifying the password thanks to the wallet:

 

See how it’s easy? But don’t forget to keep your wallet secure using unix/windows permissions!

Oracle Instances and real memory consumption on Linux and Solaris

Posted on by
8

There’s a way to know the REAL memory usage by Oracle Instance, including all connecting processes and using the shell rather than a connection to oracle?

The short answer is “I think so” 🙂

Summing up RSS column from ps output, is not reliable because Linux uses a copy-on-write on process forks and also doesn’t take into account correctly the shared memory and other shared allocations.

I’ve come across this post on Pythian’s Blog from Marc Billette.

While it seems good I’ve had discording results depending on platform and release.

Instead, I’ve tried to create a shell snippet that always uses pmap but works differently and SEEMS to work correctly on Linux ans Solaris.

Basically, using the pmap script I get a lot of information about the different memory areas allocated to the process:

 

Initially I’ve tried to decode correctly the different kinds of memory the same way other scripts I’ve found online do:

but finally the ADDRESS is the same from different processes when the memory area is shared, so my script now just get a unique line for each address and sums up the memory size (not the rss one!):

This should give the total virtual memory allocated by the different Oracle instances.

The results I get are plausible both on Linux and Solaris.

Example:

If you find any error let me know and I’ll fix the script!

—

Ludovico

Regular TNS-12508 critical alerts in EM12c

Posted on by
1

Yesterday I’ve come across a small request from a customer.
They were receiving REGULARLY critical alerts in EM12c from some listeners due to error TNS-12508.

The facts:

  • only 10g listeners were affected
  • every day, only one occurrence of the error and always at the same time on a named host
  • no apparent correlations between times on different hosts

I’ve analyzed the log to see the error.

 

Notice that after the error there are two requests: show log_directory and show trc_directory.
So I’ve supposed that it’s an additional request failing on 10g listeners but not on 11g listeners.
The “help” command of 10g and 11g releases shows that the two releases have some different commands. One of them is “show oracle_home” that has been introduced in 11g.

  • First I’ve searched for scheduled scripts (the customer literally have a huge library of scripts run against the databases to automate maintenance tasks)
  • Then I’ve asked to the team that manages an automatic discovery tool that feeds the CMDB
  • Finally, I’ve come across this note on Metalink that explain the error:

Repetitive TNS-12508 Errors logged for a listener target after upgrade to 12.1.0.3 DB plugin or higher (Doc ID 1596633.1)

I’ve applied the change to the metrics in EM12c to ignore the error for 10g listeners.
—
Ludovico

Playing with Oracle 12c Multitenant Users and Roles

Posted on by
4

I’ve realized these days that the great list of articles by Oracle Alchemist does not contain any articles describing a little more in depth common roles and Users.

I’ve found these ones by Pete Finnigan and Bobby Curtis:

http://www.petefinnigan.com/weblog/archives/00001366.htm

http://dbasolved.com/2013/06/29/common-user-vs-local-user-12c-edition/

http://www.petefinnigan.com/weblog/archives/00001370.htm

But I would like to investigate a little more.

My test environment

Just to give you an idea, I have two PDBs (HR and HR2), each containing an HR schema.

Creating the common user

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.

so I specify the correct prefix, and it works:

The user is common, so it appears in all the containers, I can check it by querying CDB_USERS from the root container.

Creating the local user

Then I create also a local user into the HR PDB.

 

CDB_USERS scope

From the PDB I see only the users in the PDB scope:

If I change to the root, I see the users valid into all the containers:

Creating a common role

Do the roles obey to the same rules valid for the users?

Yes, they do! So, let’s create a common role with the C## prefix:

It works, but if I try to create a common role into the root container only, I get an error:

And also if I try to create a local role into the root, I can’t:

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:

So I create the local role with container=current:

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.

 Granting object privileges to the roles

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:

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):

 Granting common and local roles to commond and local users

From a PDB, I can grant local roles to local users or common users:

But I can’t grant a common role to a common user with container=all if I’m in a PDB:

To grant the a common role to a common user I can decide either to:

  •  give the grant locally while connected to the PDB:

  •  give the grant commonly while connected to the root:

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:

Verifying the grants

Ok, I’ve given the grants and I’ve never verified if they work, so far.

Let’s try with the select 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.

What’s the mess? When I’ve created the user c##goofy, I’ve granted create and alter session without the container=all:

According to the documentation, the grant command uses container=current by default (common=N):

So, I need to give the grants commonly to let c##goofy connect to all the PDBs:

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:

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:

Yeah, it works as expected.

Now let’s try the common user:

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:

Let’s try to clean things: for sure I don’t need the grant local to the root:

Then I can choose between revoking the common privilege or the local one. Let’s try to remove the local one:

I’ve removed the local one, but I have still the common one (I’m connected to the PDB so the entries from the other containers are not displayed):

I still have access to the tables as expected:

So, you must pay attention to a couple of things:

  • 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!

🙂

—
Ludovico

First steps with MySQL Enterprise Monitor 3.0

Posted on by
11

During the MySQL Connect @ Open World, Oracle has announced the release of MySQL Enterprise Monitor 3.0.

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).

mem3_patch_search

  • 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.

If you’re new to MEM installations, just refer to the official documentation.

 

New vest

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.

mem3_overview

mem3_treeThe 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.

mem3_auto_discovery

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! 🙂

How many Oracle instances can be consolidated on a single server?

Posted on by
5

According to Exadata consolidation guide, this is what you can consolidate on Oracle specialized Hardware:

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.

 

My customer environment however, was NOT a production one. On the production they have 45.

Some replies…

 

 

 

Wissem cores 73 on a production system, 1TB memory!

 

Chris correctly suggests to give a try to the new 12c consolidation features:

 

Kevin, as a great expert, already experimented one hundred instances environment:

But Bertrand impresses with his numbers!

 

 

 

 

 

Intel platform with 1TB of RAM = Xeon E7, suggests Kevin:

 

 

 

Flashdba has seen 87 instances on a single host, but on a Multi-node RAC: but still huge and complex!

 

 

 

Conclusion

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! 🙂

—

Ludo

A good news to start November in a good mood

Posted on by
1

I’ve just noticed that my room mate at #OOW13 has been recognised as ACE Director.

Even if he was already a world-known performance specialist, this announcement makes me one more time proud to know him. Well deserved Chris! 🙂

OOW13: How a week in the bay changed the way I think about my job

Posted on by
1

In my previous post I’ve talked about some news from the Open World. Now it’s time to talk about MY week and why I’ve loved it…

 

It’s all about community involvement

The most exciting thing about the OOW is the possibility to interact with great people, bloggers, speakers, experts, and now I can also say friends. In the last decade, I’ve worked hard for customers and dealt with almost all the problems and architectures that an Oracle DBA can face, but I’ve always done few efforts for the community, sometimes because I was having no time, sometimes because I was perceiving it as time-expensive and gainless. I was wrong. It’s time-consuming, yes, but it lets me feel richer.

 

The first good news

When I’ve landed in the USA, the first mail I’ve read was the following:

Congratulations Bjoern. Bobby, Gokhan and Ludovico on being elected as RAC SIG Office bearers!!!

Vice President: Bjoern Rost

US Conference Events Chair: Bobby Curtis

Website Chair : Gokhan Atil

Regional Chair Europe: Ludovico Cadara

We look forward for you all to take us to the next level. I will include you on our board member email group.

Congratulations again!!

K.P.Singh
Current President, RAC SIG

We’ve made an official announcement on twitter recently:

So I’ve attended my first RAC SIG meeting at OOW13 at the OTN lounge.

RAC SIG meeting at OOW13

Now I’m weekly (or even daily) in contact with the board by e-mail, tomorrow we’ll have a conf-call to put the bases to work on for the next year. Wish us good luck! 🙂

 

RAC Attack 12c

Well, I’ve talked about the RAC ATTACK in my previous posts. The event has been a success, we’ve had many attendants, curious people and friends coming to do the lab or get in touch. It has been a great opportunity to collaborate with (and be praised by) several community experts including Oracle ACEs and ACE directors:

The Ninjas:
Seth Miller (@Seth_M_Miller)
Bjoern Rost (@brost)
Yury Velikanov (@yvelik)
Leighton Nelson (@leight0nn)
Bertrand Drouvot (@BertrandDrouvot)
Kamran Agayev (@KamranAgayev)
Martin Nash (@mpnsh)
Maaz Anjum (@maaz_anjum)
Hans Forbrich (@HansForbric)
Tim Hall ‏ (@oraclebase)
Ludovico Caldara (@ludovicocaldara)  that’s me! 🙂

Other contributors and great people that worked on the project:
Laura Ramsey (@OracleDBDev)
Jeremy Schneider (@jer_s)
Osama Mustafa (@OsamaOracle)
Bobby Curtis (@dbasolved)
Alex Gorbachev (@alexgorbachev)

(If someone is missing please raise the hand!)

Visit the official facebook page for more information.

Also note that the RAC Attack will be present at the UKOUG Tech 2013 and RMOUG 2014, so don’t miss it if you plan to attend these conferences.

 The Blogger Meetup

This meeting, organized by Pythian and OTN, allows to meet the most famous and active bloggers in the Oracle Community. This GREAT VIDEO from Björn Rost should give you an idea (I appear at 19”):

Here’s another one, always from Björn:

The Swim in the bay

Literally, a meetup of crazy guys willing to freeze themselves in the bay the early morning!
Chet Justice has organized this and he managed to get sponsorship for swim-caps and T-shirts 🙂 I’m the third from the left, you may recognize many famous people here 🙂

See the facebook page for more information.

Also for this event there is a video, actually I’m closing the video with a dive 🙂

The Golden Gate Run

Jeff Smith has organized this run Sunday morning, a good way to start the week and my first opportunity to meet many new friends!

Once again, see the Facebook page!

 

Other events

Well, I’ve participated also to the Friends of Pythian Party, the IOUG Party, the Oracle Swiss Partner Lounge, and had many nice evenings.

No, I haven’t attended the appreciation event: I was possessing the ticket but I’ve decided to skip it in favor of a dinner with many great people.

Networking and fun

See some pictures I’ve loaded on Flickr:

You can see the whole set here.

So why everything has changed?

Now I’m part of a community, I’m involved, I learn from others and I let others to learn from me… where applicable! 🙂 Interacting with people from all around the world let me feel stronger and more open to new challenges. And I know that if I have a dream is up to me to chase it and make it come true.

 

PS: This is an incomplete list of the people I’ve met  (other than the RAC Attack contributors).

Make sure to follow them!

Vit Spinka ‏ (@vitspinka)
Vanessa Simmons ‏(@pythiansimmons)
Riyaj Shamsudeen ‏ (@riyajshamsudeen)
Øyvind Isene ‏ (@OyvindIsene)
Carlos Sierra ‏ (@csierra_usa)
Gregory Guillou‏ (@ArKZoYd)
Heli Helskyaho ‏ (@HeliFromFinland)
Marc Fielding ‏ (@mfild)
Jonathan Lewis ‏ (@JLOracle)
Fuad Arshad ‏ (@fuadar)
Chet Justice ‏ (@oraclenerd)
Tim Gorman ‏ (@timothyjgorman)
Mark W. Farnham ‏ (@pudge1954)
Kerry Osborne ‏ (@KerryOracleGuy)
Arjen Visser ‏ (@dbvisit)
Kyle Hailey ‏ (@kylehhailey)
Steve Karam ‏ (@OracleAlchemist)
Eric Grancher ‏ (@EricGrancher)
Arup Nanda ‏ (@arupnanda)
Kent Graziano ‏ (@KentGraziano)
Andrey Goryunov ‏ (@goryunov)
James Morle ‏ (@JamesMorle)
Christo Kutrovsky ‏ (@kutrovsky)
Brian Fitzgerald ‏ (@ExaGridDba)
Kellyn Pot’Vin ‏ (@DBAKevlar)
Karl Arao ‏ (@karlarao)
Jason Arneil‏ (@jarneil)
Gustavo Rene Antunez ‏ (@grantunez)
Frits Hoogland ‏ (@fritshoogland)
Luca Canali ‏ (@LucaCanaliDB)
Chris Buckel ‏ (@flashdba)
Cary Millsap ‏ (@CaryMillsap)
Paul Vallee ‏ (@paulvallee)
Tanel Poder ‏ (@TanelPoder)
Connor McDonald ‏ (@connor_mc_d)
Gwen (Chen) Shapira ‏ (@gwenshap)
Christian Antognini ‏ (@ChrisAntognini)
Mauro Pagano ‏ (@Mautro)
Jérôme Françoisse ‏ (@JeromeFr)
Ittichai ‏ (@ittichai)
Jeff Smith ‏ (@thatjeffsmith)
Michelle Malcher ‏ (@malcherm)
Debra Lilley ‏ (@debralilley)
Doug Burns (@orcldoug)