Search

OakieTags

Who's online

There are currently 0 users and 41 guests online.

Recent comments

Oakies Blog Aggregator

Philosophy 16

I couldn’t help laughing when I saw this.

RMAN duplicate and read-only tablespaces

This one is to be filed under the “little things I didn’t really know until now” section: RMAN duplicate. Be honest to yourself: would you have known the answer to this question: what happens to tablespaces which are read-only on the source during an RMAN duplication (not for standby)?

Some background

I have started my career as a DBA on Oracle 8.1.7.0, and at the time not everyone fully embraced RMAN. OK, RMAN made it really hard at the time to fall in love with it. So when we “cloned” our production database to development, the following steps were followed:

  1. For each tablespace in the database, put it in hot backup mode and copy the files across to the destination location, then end the hot backup mode.
  2. Copy any archived logs generated in during the hot backup mode to DEV.
  3. Create a backup controlfile to trace.
  4. Edit the create controlfile statement (Set #2-resetlogs) and extract the “create controlfile” statement, adding the new database name in the “set” clause.
  5. Start DEV in nomount mode.
  6. Run create controlfile statement.
  7. Recover the database until cancel using the backup controlfile, applying all archived logs.
  8. Open the database with the resetlogs clause.

That worked ok, unless you had a data file offline or read only.

The test

I recently had interesting lunch discussions with my friends Jerry and Martin, because I tried that again in relation with clonedb testing. The clonedb procedure for some reason didn’t like a read-only datafile so I wanted to dig deeper.

Obviously times have moved on and I reproduced the 8i case with 11.2.0.3. To prove the point, I defined USERS tablespace on the source as read only. That’s a common data lifecycle measure and I was wondering what effect that had.

SQL> select tablespace_name,status from dba_tablespaces
  2  where tablespace_name = 'USERS';
TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

Without adding further tension to it, there was no difference in the procedure. Here is the trace file, generated using the “alter database backup controlfile to trace as ‘/tmp/martin.sql’ syntax. Nice to be able to specify where the trace goes-otherwise you’d have to dig it out in the diagnostic destination. Have a look:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE set DATABASE DEV RESETLOGS ARCHIVELOG
 MAXLOGFILES 21
 MAXLOGMEMBERS 3
 MAXDATAFILES 300
 MAXINSTANCES 1
 MAXLOGHISTORY 2920
LOGFILE
 GROUP 1 (
 '/oradata/DEV/redo/redo01.dbf'
 ) SIZE 100M BLOCKSIZE 512,
 GROUP 2 (
 '/oradata/DEV/redo/redo02.dbf'
 ) SIZE 100M BLOCKSIZE 512,
 GROUP 3 (
 '/oradata/DEV/redo/_redo03.dbf'
 ) SIZE 100M BLOCKSIZE 512,
 GROUP 4 (
 '/oradata/DEV/redo/redo04.dbf',
 ) SIZE 100M BLOCKSIZE 512
DATAFILE
 '/oradata/DEV/data/SYSTEM01.dbf',
 '/oradata/DEV/data/SYSAUX01.dbf',
 '/oradata/DEV/data/UNDO01.dbf',
 '/oradata/DEV/data/SOE.dbf'
CHARACTER SET AL32UTF8
;

No surprises here. However-where is the USERS data file gone? Scroll down in the script:

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00005'
 TO '/oradata/DEV/data/USERS01.dbf';

-- Online the files in read-only tablespaces.
ALTER TABLESPACE "USERS" ONLINE;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.

Aha! So in order to finish the procedure, you have to “online” the file, and if memory serves me right these steps weren’t in the 8i script (but I may be mistaken). After opening the database with the resetlogs option the datafile was indeed “missing”, and I had to run through the steps shown above before I could select from it.

Can RMAN do better?

I was curious how RMAN duplication handles a read-only tablespace. The command executed was simple enough:

$ rman target sys/xxx@source auxiliary sys/xxx@dest

I spare you the RMAN output, it’s verbose enough even without enabling debug mode to find out what was happening. There was no surprise (to me):

contents of Memory Script:
{
 set newname for datafile 1 to
 "/oradata/DEV/data/SYSTEM01.dbf";
 set newname for datafile 2 to
 "/oradata/DEV/data/SYSAUX01.dbf";
 set newname for datafile 3 to
 "/oradata/DEV/data/UNDO01.dbf";
 set newname for datafile 4 to
 "/oradata/DEV/data/SOE.dbf";
 set newname for datafile 5 to
 "/oradata/DEV/data/USERS01.dbf";
...

Except when I saw this:

contents of Memory Script:
{
 Alter clone database open resetlogs;
}
executing Memory Script

database opened

contents of Memory Script:
{
 catalog clone datafilecopy "/oradata/DEV/data/USERS01.dbf";
 switch clone datafile 5 to datafilecopy
 "/oradata/DEV/data/USERS01.dbf";
 #online the readonly tablespace
 sql clone "alter tablespace USERS online";
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/oradata/DEV/data/USERS01.dbf RECID=6 STAMP=779713734

datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=779713734 file name=/oradata/DEV/data/USERS01.dbf

sql statement: alter tablespace USERS online
Finished Duplicate Db at 04-APR-12

RMAN> exit

So long story reduced to the minimum: the procedure seems to be enhanced in the current release! Read only tablespaces stay read only. There is one slight caveat: if you have an “OFFLINE” datafile in the source, it will be “ONLINE” in the clone.

SQL> connect sys/xxx@source
Connected.
SQL> select name,status from v$datafile;

NAME                                                        STATUS
----------------------------------------------------------- -------
/oradata/PROD/data/SYSTEM01.dbf                             SYSTEM
/oradata/PROD/data/SYSAUX01.dbf                             ONLINE
/oradata/PROD/data/UNDO01.dbf                               ONLINE
/oradata/PROD/data/SOE.dbf                                  ONLINE
/oradata/PROD/data/USERS01.dbf                              OFFLINE

SQL> connect sys/xxx@clone
Connected.
SQL> select name,status from v$datafile;

NAME                                                       STATUS
---------------------------------------------------------- -------
/oradata/DEV/data/SYSTEM01.dbf                             SYSTEM
/oradata/DEV/data/SYSAUX01.dbf                             ONLINE
/oradata/DEV/data/UNDO01.dbf                               ONLINE
/oradata/DEV/data/SOE.dbf                                  ONLINE
/oradata/DEV/data/USERS01.dbf                              ONLINE

Interesting!

Another one (conference) bites the dust…

Unfortunately I’ve just had to pull out of the Austrian Oracle User Group conference in Vienna in June. It’s a one day conference and only one of my papers was selected. The ACED travel support requires that at least two* papers are selected before you can apply for approval, so that knocks me out of the running. Sorry for any inconvenience to the organizers. On the positive side, another slot has opened up in the schedule for someone else to present. :)

As a note to other ACEDs, make sure conference organizers are fully aware of the conditional nature of your application. I always try to remind people that I can only come if I get travel approval, but in this case that message got lost somewhere down the chain.

Cheers

Tim…

* This is not a recent change to the policy. It has been the case for as long as I can remember.




Failed Logon Attempts

April 4, 2012 A fair number of articles on this blog are Oracle performance related in one respect or another.  I started thinking about how to measure how much more efficient something is when compared to something that is never performed.  Consider a situation where you had a database user account that is a member of the [...]

Statspack quiz

I’ve a level 5 Statspack report from a real production 11.2.0.2 database with the following Top 5 Timed events section:

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
log buffer space                                    28       1,396  49870   41.0
buffer busy waits                                   51       1,049  20567   30.8
CPU time                                                       320           9.4
log file parallel write                          4,011         159     40    4.7
log file sync                                    3,275         153     47    4.5

The complete report (without SQL and several other sections) is here. Can you explain what & why is wrong in this Top?
I must admit I don’t have the definitive answer (probably yet), but I have partial explanation of the issue as well as several ideas.

Filed under: Oracle, Performance Tagged: quiz, Statspack

VirtualBox 4.1.12 Released…

Just a couple of weeks after the last release, to make sure we didn’t think it was an April fools joke, Oracle waited until April 2nd to release VirtualBox 4.1.12 into the wild. :)

You can see the changelog here.

Happy upgrading.

Cheers

Tim…




Advert: Nederlands preview event ‘Collaborate 12′ (Dutch)

‘COLLABORATE 12: Technology and Applications Forum for the Oracle Community’ is de grootste Oracle gebruikersgroep conferentie ter wereld en wordt jaarlijks georganiseerd in de Verenigde Staten. Het is hét evenement om bij te zijn wanneer je geïnteresseerd bent niet alleen in Oracle Applications (E-Business Suite, JD Edwards, Siebel, etc.) maar ook in Oracle Technologie in

Read More…

Advanced Oracle Troubleshooting v2.0 seminars in 2012

I am going to run my updated Advanced Oracle Troubleshooting online training again, in May and June! :)

As there’s been so much demand from all over the world (thanks to everyone who’ve sent me mails about it ;-), I’ll actually do it twice, once for the US/European timezones (8am-12pm in California and d 5-9pm in central Europe) and once for the APAC timezones (2pm-6pm in Sydney/Melbourne) on every day. Both events are split into 2 parts, for the US event there’s around a month between the parts, for the APAC event there’s a week betweern Part1 and 2 (so you can get some rest and/or some work done in between too :-)

As I’m now part of Enkitec, our training crew will help me out with all the registration and payment stuff, everything else (the webinar URL, slides, scripts, Q&A and the actual training you will get from me as usual :)

Check out the details here:

 

Note to previous AOT attendees – I will schedule a Q&A session soon too!

“See” you soon!

(ok, I think I should finish some of my long-overdue blog articles now ;-)

There’s convincing and then there’s curiosity

This past week, Cary Millsap posted one of the ways he’s teaching his children to understand concepts and to prove to him that they understand the methods for solving problems by presenting them with a classic puzzle.

The puzzle talks about the length of a piece of string wrapped around a circle (in this case, the whole Earth), and how much longer it would need to be if it was raised 4 more inches off the ground? As his kids worked through the problem, Cary provided us with the neat answer in which you didn’t need to know the circumference of the Earth, or any other circle for that matter — the increase in length is a simple function applied to the additional height above the ground.

Near the end of Cary’s post, he talks about how the understanding of basic relationships and methods gives the ability to convince someone that you actually know what you are talking about and goes on to challenge you to demand the same information from people trying to sell you something.

It’s a wonderful post, and I encourage you to go read it — but I also want you to go beyond the original post a bit.

While Cary’s formula holds for circles, what about other kinds of shapes? What about simple regular polygons (where all sides are the same length)? Do they have the same kind of simple formula? Do you have the curiosity to try and see if you can determine methods for generalizing what you know, applying it, and demonstrating to others?

Convincing me makes me trust you with a particular problem, showing me you won’t stop there makes me trust you even further…

I spent the afternoon playing with the idea and I think I’ve found it — I came up with the following answers:

  • Equilateral triangle (3 sides) = 41.5692 inches
  • Square (4 sides) = 32 inches
  • Pentagon (5 sides) = 29.0617 inches
  • Hexagon (6 sides) = 27.7128 inches
  • Octagon (8 sides) = 26.5097 inches

Looks like it’s tending toward’s Cary’s answer for a circle — perhaps there is a formula after all?  :-)

Best practices? I'm not so sure.

I'll be delivering a webinar this week that has the words "Best Practices" in the presentation title. As I've been preparing, it has really struck me how my definition of a best practice is not the traditional IT definition. Wikipedia says

A best practice is a method or technique that has consistently shown results superior to those achieved with other means, and that is used as a benchmark.

OK. That sounds reasonable, right? So, what's my problem? Well, I think that if this definition is the traditionally accepted standard definition, there's something missing. What about context? I suppose you could infer that a specific context is implied for any best practice, but the inference isn't enough. An unfortunate side effect the label "best practice" has when it is attached to any method or technique is that it makes people blind to the context in which the practice should/could be applied. Can you think of any examples where a practice that is "best" in one context is questionable within another? Sure you can (feel free to share yours in the comments!). But, so often, many people will take a best practice at face value without considering the context of their own situation.

I think a best practice is really a contextual practice. I'm borrowing this term from Eric Ries, author of The Lean Startup. Ries writes that we should strive to understand the context which we find ourselves in, and then apply the practice which is best within that context. I couldn't agree more. It's not about blindly following steps or guidelines that may, or may not, be applicable and particularly useful in your situation. It's about understanding. You've got to understand your situation and take steps that make sense in that context. Not simply take steps that worked for some other person, in some other context, at some other time, just because it is labeled as a "best practice".

My goal for my webinar's list of "best practices" is to offer ways to gain understanding and clarify the context of your specific situation when developing SQL. With a clearer understanding and proper context, you can have confidence to make the right choices that lead to optimal results. Come join me for the webinar and see if I succeed!