Block Change Tracking and Duplicate: avoid ORA-19755

If you use Block Change Tracking on your production database and try to duplicate it, you there are good possibilities that you will encounter this error:

The problem is caused by the block change tracking file entry that exists in the target controlfile, but Oracle can’t find the file because the directory structure on the auxiliary server changes.

After the restore and recovery of the auxiliary database, the duplicate process tries to open the DB but the bct file doesn’t exist and the error is thrown.

If you do a quick google search you will find several workarounds:

  • disable the block change tracking after you get the error and manually open the auxiliary instance (this prevent the possibility to get the duplicate outcome from the rman return code)
  • disable the BCT on the target before running the duplicate (this forces your incremental backups to read all your target database!)
  • Richard Harrison proposed another workaround, you can read more about it here.

There is another workaround that I like more (and that you can also find as comment in Richard’s post):

  • Disable the Block Change Tracking on the auxiliary while it’s doing the restore/recovery (in mounted status)

(This solutions isn’t coming from me, but as far as I know, the father of this solution is a colleague working at Trivadis.)

You can easily fork a process before running the duplicate command that:

  • loops and checks the auxiliary instance status
  • run the disable as soon as the auxiliary is mounted

I’ve worked out this script that does the job:

Run it  just before the duplicate! e.g.

HTH

Ludovico

Check the actual ulimits for all the running Oracle instances

I’ve read the recent good post from my friend Rene on Pythian’s Blog about how to troubleshoot when user ulimits are different from what specified in limits.conf:

Quick Tip : Oracle User Ulimit Doesn’t Reflect Value on /etc/security/limits.conf

I would like to add my 2 cents:

Once you fix the problem, you may want to check (any maybe monitor) when an instance is running with a wrong value (and maybe encounter the famous Error message: Linux-x86_64 Error: 23: Too many open files in system).

This single line gives you an overview of all your instances at once:

If you find any wrong values, plan a restart before you encounter any error during peak hours!

Ludo

 

 

RAC Attack! 12c is back to Geneva!

ninja-suisseVersion française ici.

After a great success in 2014, RAC Attack! comes back to Geneva!
Set up an Oracle Real Application Clusters 12c environment on your laptop, try advanced configurations or simply take the opportunity to discuss about Oracle technology with the best experts in Suisse Romande!
Experienced volunteers (ninjas) will help you  address any related issues and guide you through the setup process.

Where? Trivadis office, Chemin Château-Bloch 11, CH1219 Geneva

When? Thursday September 17th, 2015, from 17h00 onwards

Cost? It is a FREE event! It is a community based, informal and enjoyable workshop. You just need to bring your own laptop and your desire to have fun!

Confirmed Ninjas:
Ludovico Caldara
– Oracle ACE, RAC SIG Chair & co-auteur RAC Attack
Eric Grancher – Membre OAK Table & Senior DBA
Jacques Kostic – OCM 11g & Senior Consultant chez Trivadis

Limited places! Reserve your seat and T-shirt now!

tshirt_racattack_2015Agenda:
17.00 – Welcome and T-shirt distribution
17.30 – RAC Attack 12c part I
19.30 – Pizza and Beers! (sponsored by Trivadis)
20.00 – RAC Attack 12c part II
22.00 – Group photo and wrap-up!!

Still undecided? Look at what we did last year!

To subscribe, simply fill-in the form below! I will send you.a confirmation as soon as possible. First come, first serve.

Fields marked with an * are required

Enter your details below:

To participate in the workshop, you need to bring your own laptop.

Required specification:
a) any 64 bit OS that supports Oracle Virtual Box
b) 8GB RAM, 50GB free HDD space
c) an administrative account on the laptop

Check if you've read and understood the requirements:

Smart Bash Prompt for Oracle

If you are an Oracle customer who has several database versions running, you have to deal with scripts that become more and more complex to maintain. Depending on the version or the edition of your database, you may want to run different pieces of code. This forces you to get programmatically more information about your database version and edition (e.g., in order to run a statspack or AWR report if your software is either Enterprise or Standard).
The most common way to get information about the software is connecting to the database and getting it through a couple of selects. But what if you don’t have any running databases?
The ORACLE_HOME inventory has such information, and you can get it with a short shell function:

The snippet searches for a patchset entry in comps.xml to get the patch version rather than the base version (for releases prior to 11gR2 where out-of-place patching occurs). If a patchset cannot be found, it looks for the base version. Depending on the major release, the information about the edition is either in globalvariables.xml (11g, 12c) or in context.xml (10g).
When you call this “ohversion” function, you get both the Oracle version and the edition of your current ORACLE_HOME.
If you’re using the bash as user shell, you may want to take one step forward and  include this information in a much fancier bash prompt than the prompt by default:

2015_06_05_16_22_56_sso0419iAlthough this prompt may seem long, it has several advantages that save you a lot of typing:
• The newline character inside the prompt let’s you start typing commands on an almost empty line so you don’t have to worry about how long your command is.
• The full username@host:path can be copied and pasted quickly for scp commands.
• The time inside the square brackets is helpful to track timings.
• The indication of the current environment (version, edition, SID) lets you know which environment you’re working on.
• The leading number is the exit code of the last command ($?). It’s green when the exit code is zero and red for all other exit codes.
• Hash characters before and after the prompt mitigate the risk of copying and pasting the wrong line by mistake inside your session.

Note: this post originally appeared on IOUG Tips & Best Practices Booklet 9th edition.

SQL Plan Directives: they’re always good… except when they’re bad!

The new Oracle 12c optimizer adaptive features are just great and work well out of the box in most cases.

Recently, however,  I’ve experienced my very first problem with SQL Plan Directives migrating a database to 12c, so I would like to share it.

Disclaimer 1: this is a specific problem that I found on ONE system. My solution may not fit with your environment, don’t use it if you are not sure about what you’re doing!

Disclaimer 2: despite I had this problem with a single SPD, I like adaptive features and I encourage to use them!!

Problem: a query takes a sub-second in 11gR2, in 12c it takes 12 seconds or more.

V_TAB_PROP is a very simple view. It just selects a central table “TAB” and then takes different properties by joining  a property table “TAB_PROP”.

To do that, it does 11 joins on the same property table.

On the property table, TAB_PROP_ID and PROP_ID are unique (they compose the pk), so nested loops and index unique scans are the best way to get this data.
The table is 1500Mb big and the index 1000Mb.

This was the plan in 11g:

In 12c, the plan switches to adaptive, and half of the joins are converted to hash joins / full table scans:

However, the inflection point is never reached. The execution keeps the default plan that has half of the joins HJ and the other half NL.

The problem in this case is the SQL Directive. Why?

There are to many distinct values for TAB_ID and the data is very skewed.

The histogram on that column is OK and it always leads to the correct plan (with the adaptive features disabled).
But there are still some “minor” misestimates, and the optimizer sometimes decides to create a SQL Plan directive:

The Directive instructs the optimizer to do a dynamic sampling, but with a such big and skewed table this is not ok, so the Dynamic sampling result is worse than using the histogram. I can check it by simplifying the query to just one join:

What’s the fix?

I’ve tried to drop the directive first, but it reappears as soon as there are new misestimates.
The best solution in my case has been to disable the directive, an operation that can be done easily with the DBMS_SPD package:

I did this on a QAS environment.
Because the production system is not migrated to 12c yet, it’s wise to import these disabled directives in production before the optimizer creates and enables them.

Off course, the directives can’t be created for objects that do not exist, the import  has to be done after the objects migrate to the 12c version.

Because the SQL Plan Directives are tied to specific objects and not specific queries, they can fix many statements at once, but in case like this one, they can compromise several statements!

Monitoring the creation of new directives is an important task as it may indicate misestimates/lack of statistics on one side or execution plan changes on the other one.

Oracle RAC and the Private Cloud. And why small customers are not implementing it. Not yet.

Cloud. What a wonderful word. Wonderful and gray.
If you are involved in the Oracle Community, blogs and conferences, you certainly care about it and have perhaps your own conception of it or ideas about how to implement it.

My Collaborate 2015 RAC SIG experience

During the last Collaborate Conference, I’ve “tried” to animate the traditional RAC SIG Round-Table  with this topic:

In the last few years, cloud computing and infrastructure optimization have been the leading topics that guided the IT innovation. What’s the role of Oracle RAC in this context?

During this meeting leading RAC specialists, product managers, RAC SIG representatives and RAC Attack Ninjas will come together and discuss with you about the new Oracle RAC 12c features for the private cloud and the manageability of RAC environments.

Join us for the great discussion. This is your chance to have a great networking session!

Because it’s the RAC SIG meeting, most of the participants DO HAVE a RAC environment to manage, and are looking for best practices and ideas to improve it, or maybe they want to share their experiences.

I’ve started the session by asking how many people are currently operating a private cloud and how many would like to implement it.

With my biggest surprise (so big that I felt immediately uncomfortable), except one single person, nobody raised the hand.

What?

I’ve spent a very bad minute, I was almost speechless. I was actually asking myself: “is my conception of private cloud wrong?”. Then my good friend Yury came in help and we started the discussion about the RAC features that enable private cloud capabilities. During those 30 minutes, almost no users intervened. Then Oracle Product Managers (RAC, ASM, QoS, Cloud) started explaining their point of view, and I suddenly realized that

when talking about Private Cloud, there is a huge gap between the Oracle Private Cloud implementation best practices and the small customers skills and budgets.

When Oracle product managers talk about Private Cloud, they target big companies and advice to plan the infrastructure using:

  • Exadata
  • Full-pack of options for a total of 131k per CPU:
    • Enterprise Edition (47.5k)
    • Multitenant (17.5k)
    • Real Application Clusters (23k)
    • Partitioning (11.5k)
    • Diagnostic Pack (7.5k)
    • Tuning Pack (5k)
    • Lifecycle Management Pack (12k)
    • Cloud Management Pack (7.5k)
  • Flex Cluster
  • Policy Managed Databases
  • Quality of Services Management
  • Rapid Home provisioning
  • Enterprise Manager and DBaaS Self Service portal

The CapEx needed for such a stack is definitely a show stopper for most small-medium companies. And it’s not only about the cost. When I gave my presentation about Policy Managed Databases at Collaborate in 2014, and later about Multitenant and MAA at Open World, it was clear that “almost” nobody (let’s say less than 5%, just to give an idea) uses these new technologies. Many of them are new and, in some cases, not stable. Notably, Multitenant and QoS are not working together as of now. Qos will work with the new resource manager at PDB level only in release 12.2 (and still not guaranteed).

For the average company (or the average DBA), there is more than enough to be scared about, so private cloud is not seen as easy to implement.

So there’s no private cloud solution for SMBs?

It really depends on what you want to achieve, and at which level.

Based on my experience at Trivadis, I can say that you can achieve Private Cloud for less. Much less.

What a Private Cloud should guarantee? According to its NIST definition, five four things:

  1. On-demand self-service.
  2. Broad network access.
  3. Resource pooling.
  4. Rapid elasticity.
  5. Measured service.

Number 5 is a clear field of EM, and AWR Warehouse new feature may be of great help, for free  (but still, you can do a lot on your own with Statspack and some scripting if you are crazy enough to do it without Diagnostic pack).

Numbers 3 and 4 are a peculiarity of RAC, and they are included in the EE+RAC license. By leveraging OVM, there are very good opportunities of savings if the initial sizing of the solution is a problem. With OVM you can start as small as you want.

Number 1 depends on standards and automation already in place at your company. Generally speaking, nowadays scripting automatic provisioning with DBCA and APEX is very simple. If you’re not comfortable with coding, tools like the Trivadis Toolbox make this task easier. Moreover, nobody said that the self-service provisioning must be done through a web interface by the final user. It might be (and usually is) triggered by an event, like the creation of a service request, so you can keep web development outside of your cloud.

Putting all together

You can create a basic Private Cloud that fits perfectly your needs without spending or changing too much in your RAC environment.

Automation doesn’t mean cost, you can do it on your own and keep it simple. If you need an advice, ideas or some help, just drop me an email (myfirstname.mylastname@trivadis.com), it would be great to discuss about your need for private cloud!

Things can be less complex than what we often think. Our imagination is the new limit!

Ludovico

#C15LV RAC Attack wrap

Did I say in some of my previous posts that I love RAC Attack? I love it more during Collaborate conference, because doing it as a pre-conference workshop is just the right way the get people involved and go straight to the goal: learning while having fun together.

We had way less people than expected but it still has been a great success!

The t-shirts have been great for coloring the room: as soon as people finished the installation of the first Linux VM, they’ve got one t-shirt.
DSC04023

Look at the room at the beginning of the workshop:

DSC04034

 

after a few hours, the room looked better! New ninjas, red stack, happy participants :-)

DSC04118

We had a very special guest today. Mr. RAC PM in person has tried and validated our installation instructions 😉
DSC04049

We got pizza again, but because of restrictions at the convention center, it has been a beer-free afternoon :-(

Thank you anyway to the OTN for sponsoring almost everything!!

DSC04100

 

Looking forward to organize the next RAC Attack, Thank you guys!! :-)

DSC04106
DSC04134

CCasAEUUEAA7bvL

 

 

 

Ludo

 

It’s time to Collaborate again!!

Collaborate15_Horizontal_LogoIn a little more than a couple of weeks, the great Collaborate conference will start again.

My agenda will be quite packed again, as speaker, panelist and workshop organizer:

Date/Time Event
08/04/2015
3:15 pm - 4:15 pm
Oracle RAC, Data Guard, and Pluggable Databases: When MAA Meets Oracle Multitenant
IOUG Collaborate 15, Las Vegas NV
08/04/2015
4:30 pm - 5:30 pm
Panel: Nothing to BLOG About - Think Again
IOUG Collaborate 15, Las Vegas NV
12/04/2015
9:00 am - 4:00 pm
RAC Attack! 12c
IOUG Collaborate 15, Las Vegas NV
15/04/2015
5:30 pm - 6:00 pm
IOUG RAC SIG Meeting
IOUG Collaborate 15, Las Vegas NV

 

RAC Attack! 12c

This technical workshop and networking event (never forget it’s a project created several years ago thanks to an intuition of Jeremy Schneider), confirms to be one of the best, long-living projects in the Oracle Community. It certainly boosted my Community involvement up to becoming an Oracle ACE. This year I’m the coordinator of the organization of the workshop, it’s a double satisfaction and it will certainly be a lot of fun again. Did I said that it’s already full booked? I’ve already blogged about it (and about what the lucky participants will get) here.

 

Oracle RAC, Data Guard, and Pluggable Databases: When MAA Meets Oracle Multitenant 

One of my favorite presentations, I’ve presented it already at OOW14 and UKOUG Tech14, but it’s still a very new topic for most people, even the most experienced DBAs. You’ll learn how Multitenant, RAC and Data Guard work together. Expect colorful architecture schemas and a live demo!  You can read more about it in this post.

 

Panel: Nothing to BLOG About – Think Again

My friend Michael Abbey (Pythian) invited me to participate in his panel about blogging. It’s my first time as panelist, so I’m very excited!

 

IOUG RAC SIG Meeting

Missing this great networking event is not an option! I’m organizing this session as RAC SIG board member (Thanks to the IOUG for this opportunity!). We’ll focus on Real Application Clusters role in the private cloud and infrastructure optimization. We’ll have many special guests, including Oracle RAC PM Markus Michalewicz, Oracle QoS PM Mark Scardina and Oracle ASM PM James Williams.

Can you ever miss it???

 

A good Trivadis representative!!

trivadis.com

This year I’m not going to Las Vegas alone. My Trivadis colleague Markus Flechtner , one of the most expert RAC technologists I have the chance to know, will also come and present a session about RAC diagnostics:

615: RAC Clinics- Starring Dr. ORACHK, Dr CHM and Dr. TFA

Mon. April 13| 9:15 AM – 10:15 AM | Room Palm D

If you speak German you can follow his nice blog: http://oracle.markusflechtner.de/

Looking forward to meet you there

Ludovico

Moving Clusterware Interconnect from single NIC/Bond to HAIP

Very recently I had to configure a customer’s RAC private interconnect from bonding to HAIP to get benefit of both NICs.

So I would like to recap here what the hypothetic steps would be if you need to do the same.

In this example I’ll switch from a single-NIC interconnect (eth1) rather than from a bond configuration, so if you are familiar with the RAC Attack! environment you can try to put everything in place on your own.

First, you need to plan the new network configuration in advance, keeping in mind that there are a couple of important restrictions:

  1. Your interconnect interface naming must be uniform on all nodes in the cluster. The interconnect uses the interface name in its configuration and it doesn’t support different names on different hosts
  2. You must bind the different private interconnect interfaces in different subnets (see Note: 1481481.1 – 11gR2 CSS Terminates/Node Eviction After Unplugging one Network Cable in Redundant Interconnect Environment if you need an explanation)

 

Implementation 

The RAC Attack book uses one interface per node for the interconnect (eth1, using network 172.16.100.0)

To make things a little more complex, we’ll not use the eth1 in the new HAIP configuration, so we’ll test also the deletion of the old interface.

What you need to do is add two new interfaces (host only in your virtualbox) and configure them as eth2 and eth3, e.g. in networks 172.16.101.0 and 172.16.102.0)

 

modify /var/named/racattack in order to use the new addresses (RAC doesn’t care about logical names, it’s just for our convenience):

add also the reverse lookup in  in-addr.arpa:

 

restart  named on the first node and check that both nodes can ping all the names correctly:

check the nodes that compose the cluster:

on all nodes, make a copy of the gpnp profile.xml (just in case, the oifcfg tool does the copy automatically)

List the available networks:

Get the current ip configuration for the interconnect:

one one node only, set the new interconnect interfaces:

check that the other nodes has received the new configuration:

Before deleting the old interface, it would be sensible to stop your cluster resources (in some cases, one of the nodes may be evicted), in any case the cluster must be restarted completely in order to get the new interfaces working.

Note: having three interfaces in a HAIP interconnect is perfectly working, HAIP works from 2 to 4 interfaces. I’m showing how to delete eth1 just for information!! :-)

on all nodes, shutdown the CRS:

Now you can disable the old interface:

and modify the parameter ONBOOT=no inside the configuration script of eth1 interface.

Start the cluster again:

And check that the resources are up & running:

 

 Testing the high availability

Disconnect cable from one of the two interfaces (virtually if you’re in virtualbox :-) )

Pay attention at the NO-CARRIER status (in eth2 in this example):

check that the CRS is still up & running:

 

The virtual interface eth2:1 as failed over on the second interface as eth3:2

 

After the cable is reconnected, the virtual interface is back on eth2:

 

Further information

For this post I’ve used a RAC version 11.2, but RAC 12c use the very same procedure.

You can discover more here about HAIP:

http://docs.oracle.com/cd/E11882_01/server.112/e10803/config_cw.htm#HABPT5279 

And here about how to set it (beside this post!):

https://docs.oracle.com/cd/E11882_01/rac.112/e41959/admin.htm#CWADD90980

https://docs.oracle.com/cd/E11882_01/rac.112/e41959/oifcfg.htm#BCGGEFEI

 

Cheers

Ludo

RAC Attack at IOUG Collaborate 2015

Once again this year the RAC Attack will be a pre-conference workshop at Collaborate.

Whether you’re a sysadmin, a developer or a DBA, I’m sure you will really enjoy this workshop. Why?

First, you get the opportunity to install a RAC 12c using Virtualbox on your laptop and get coached by many RAC experts, Oracle ACEs and ACE Directors, OCMs and famous bloggers and technologists.

If you’ve never installed it, it will be very challenging because you get hands on network components, shared disks, udev, DNS, Virtual Machine cloning, OS install and so on, and being super-user (root) of your own cluster!! If your a developer, you can then start developing your applications by testing the failover features of RAC and their scalability by checking for global cache wait events.

If you’re already used to RAC, this year we have not one or two, but three deals for you:

  1. Try the semi-automated RAC installation using Vagrant: you’ll be able to have your RAC up and running in minutes and concentrate on advanced features.
  2. Implement advanced labs such as Flex Cluster and Flex ASM or Policy Managed Databases, and discover Hub and Leaf nodes, Server Pools and other features
  3. Ask the ninjas to show you other advanced scenarios or just discuss about other RAC related topics

 

Isn’t enough?

The participants that will complete at least the Linux install (very first stage of the workshop) will get an OTN-sponsored T-shirt of the event, with the very new RAC SIG Logo (the image is purely indicative, the actual design may change):

t-shirt-c15lv

 

Still not enough?

We’ll have free pizza (at lunch) and beer (in the afternoon), again sponsored by the Oracle Technology Network. Can’t believe it? Look at a few images from last year’s edition:

20140407_121101


IMG_0210

BkpvaPBCAAAULIB

RACAttackC14LV

Check the pre-conference workshops on the IOUG Collaborate 15 website and don’t forget to full-fill the requirements before attending the workshop:

To participate in the workshop, participants need to bring their own laptop. Recommended specification: a) any 64 bit OS that supports Oracle Virtual Box b) 8GB RAM, 45GB free HDD space, SSD recommended.

Important: it’s required to pre-download Oracle Database 12c and Oracle Grid Infrastructure 12c for Linux x86-64 from the Oracle Website http://tinyurl.com/rac12c-dl (four files: linuxamd64_12c_database_1of2.zip linuxamd64_12c_database_2of2.zip linuxamd64_12c_grid_1of2.zip linuxamd64_12c_grid_2of2.zip). Due to license restrictions it’s not be possible to distribute Oracle Sofware.

Looking forward to meet you there!!!

— 

Ludovico