Category Archives: Oracle Database

Oracle Multitenant and custom DBCA templates

Posted on by
4

Today I’ve encountered an annoying issue while adapting a few scripts for automatic database creation. I track it here so hopefully it may save a few hours of troubleshooting to someone…

I’ve used the DBCA to prepare a new template:

 

Then continued by customizing other options, including init parameters and datafile/logfile paths. Finally, I’ve saved it as a new template instead of creating the database.

I’ve checked the resulting .dbc and seen that there was, as expected, the parameter “enable_pluggable_database”=”true”.

Then I’ve moved the template file to my $HOME directory and tested the silent database creation with the option “-createAsContainerDatabase true”:

The database configuration has completed successfully, without errors. I’ve accessed my new container, and I’ve been surprised by seing:

In fact, there were no pdb$seed datafiles:

After little investigation, I’ve found these lines in the dbca trace.log:

 

Then I’ve struggled with dbca and templates a few times before finding that, actually, the magic “enable pluggable database” is done by dbca only if the template file name is not customized.

Running the same exact command with the very same template file but renamed to $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc actually works (notice the diff at the first line):

 

 

I’ve also tried to cheat and use a symlink to my previous custom template, and surprisingly, it still works:

In the dbca trace log the message saying that the DB will be NON-CDB disappears:

So the problem is really caused by the different filename/location of the template.

IMHO it’s a kind of bug, the decision between a CDB and NON-CDB should not be taken by DBCA.  Moreover, it’s not based on the content of the template, which would be logic. But today I’m late and lazy, I will not open a SR for this.

:-/

RAC Attack 12c at Collaborate 14 and advanced labs

Posted on by
1

I’ve just published an advanced lab on SlideShare that RAC Attack attendees may do at Collaborate this year, instead of just doing the basic 2-node RAC installation on their laptop.

We’ll offer also an advanced lab about Flex Clusters and Flex ASM (written by Maaz Anjum). Moreover, I’m working on an additional lab that allows to implement a multi-node RAC by using Virtual Box linked clones and GI Home clones like I’ve shown in my previous post.

RAC Attack at #C14LV will be like fun again. We’ll have a few t-shirts for the attendants, designed by me and Chet “Oraclenerd” Justice, kindly sponsored by OTN.

The workshop will end up with beers and snaks (again, thank you OTN for sponsoring this :-)).

2014_03_27_23_46_49_OraclenerdNinjasFINAL

 

If you’re planning to attend Collaborate, join us and start your conference week in a good mood 🙂

 

Multinode RAC 12c cluster on VirtualBox using linked clones

Posted on by
10

Recently I’ve had to install a four-node RAC cluster on my laptop in order to do some tests. I have found an “easy” (well, easy, it depends), fast and space-efficient way to do it so I would like to track it down.

The quick step list

  • Install the OS on the first node
  • Add the shared disks
  • Install the clusterware in RAC mode on on the first node only
  • Remove temporarily the shared disks
  • Clone the server as linked clone as many times as you want
  • Reconfigure the new nodes with the new ip and naming
  • Add back the shared disks on the first node and on all other nodes
  • Clone the GI + database homes in order to add them to the cluster

Using this method the Oracle binaries (the most space consuming portion of the RAC installation) are installed and allocated on the first node only.

The long step list

Actually you can follow many instruction steps from the RAC Attack 12c book.

  • Review the HW requirements  but let at least 3Gb RAM for each guest + 2Gb more for your host (you may try with less RAM but everything will slow down).
  • Download all the SW components , additionally you may download the latest PSU (12.1.0.1.2) from MOS.
  • Prepare the host and install linux on the first node. When configuring the OS, make sure you enter all the required IP addresses for the additional nodes. RAC Attack has two nodes collabn1, collabn2. Add as many nodes as you want to configure. As example, the DNS config may have four nodes

At this point, the procedure starts differing from the RAC Attack book.

  •  Go to the VirtualBox VM settings and delete all the shared disks2014_03_16_22_05_26_Oracle_VM_VirtualBox_Manager
  •  Clone the first server as linked clone (right-click, clone, choose the name, flag “Linked Clone” as many times as the number of additional servers you want.

2014-03-16 22_09_42-Clone Virtual Machine

 

  • By using this method  the new servers will use the same virtual disk file of the first server and a second file will be used to track the differences. This will save a lot of space on the disk.
  • Add back the shared disks to all the servers.
  • Start the other nodes and configure them following the RAC Attack instructions again.
  • Once all the nodes are configured, the GI installation has to be cleaned out on all the cloned servers using these guidelines:

  • Then, on each cloned server, run the perl clone.pl as follows to clone the GI home, but change the LOCAL_NODE accordingly (note: the GI Home name must be identical to the one specified in the original installation!):

  •  Then, on the first node (that you have started and you have reactivated the clusterware stack on it with crsctl enable crs / crsctl start crs ;-)), run this command to add the new nodes in the definition of the cluster:

 

  • from the first server copy these files on all the other nodes:

  •  Then clone also the DB Home (again, run it on each new server and specify the same DB home name that you have used in the original installation):

  •  On each new node run also the updatenodelist and the DB root.sh command to update the node list for the DB home:

  •  and finally, run the GI root.sh on each new node to finalize their inclusion in the cluster!! 🙂

 

  • As result, you should be able to seen all the cluster resources started correctly on all the nodes.

 

I know it seems a little complex, but if you have several nodes this is dramatically faster than the standard installation and also the space used is reduced. This is good if you have invested in a high-performance but low-capacity SSD disk like I did :-(.

Hope it helps, I paste here the official documentation links that I’ve used to clone the installations. The other steps are my own work.

References

 

Some notes about Grid Infrastructure PSU 12.1.0.1.2

Posted on by
10

I’m creating a new 12c RAC environment from scratch, Ii just want to track a few notes (primarily for my personal use ;-)) about the _PSU.

The opatch utility bundled with the GI does not contain the emocmrsp, so it is necessary to install the latest opatch (6880880).

The patching process can patch both GI and RAC homes at once, but if you don’t have a valid database registered, an error is raised:

So you need to patch the Oracle Homes individually if it’s a new installation.

Remind that:

  • The patch must be unzipped by the oracle/grid user in a directory readable to oracle and root (or it will fail with Argument(s) Error… Patch Location not valid) or other funny errors (permission denied errors in the middle of the patch process)
  • Must be applied by the root user
  • Must be applied individually and on every node, one node at time.
  • The opatchauto executable must belong to one of the OH you’re patching (so if you patch GI and RAC separately,  you have to use the correspondent opatch.

Grid:

RAC:

Cheers

—

Ludovico

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

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