RMAN Incarnations revisited (11G)

Time for an update to a older post. I have previously talked about the annoyance of connecting to RMAN with a duplicated database where the DBID has not been changed. RMAN happily breaks the catalog by assuming the “new” database is a new incarnation, and prevents the previous owner of the catalog from using the backups.

I wrote a blog post a while ago about hacking your way past this problem, but was recently informed by Martin Bach that there was actually an RMAN command to fix the Incarnation problem I had encountered, so I though I had better take a look and see if it worked!

Well, the first thing I noticed was that Oracle 11G does not break when connecting from a different database with the same DBID the way it did in Oracle 10G:

[oracle@localhost ~]$ rman target system/oracle catalog rman/rman@orcl1
Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jan 26 12:26:10 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL1 (DBID=1229390655)
connected to recovery catalog database

RMAN> list incarnation;
starting full resync of recovery catalog
full resync complete
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 18 ORCL1 1229390655 PARENT 1 13/08/09 23:00:48
1  2 ORCL1 1229390655 CURRENT 754488 30/10/09 11:38:43

RMAN> list backup summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
331 B A A DISK 26/01/13 08:55:32 1 1 NO BACKUP1
375 B A A DISK 26/01/13 09:06:44 1 1 NO BACKUP2
400 B A A DISK 26/01/13 09:07:02 1 1 NO BACKUP3
587 B F A DISK 26/01/13 11:20:09 1 1 YES FULL BACKUP
609 B F A DISK 26/01/13 11:20:11 1 1 NO TAG20130126T112010

And on the alternate database:

[oracle@localhost ~]$ rman target system/oracle catalog rman/rman@orcl1
Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jan 26 12:15:07 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=1229390655)
connected to recovery catalog database

RMAN> list incarnation;
starting full resync of recovery catalog
full resync complete
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 18 ORCL2 1229390655 PARENT 1 13/08/09 23:00:48
1  2 ORCL2 1229390655 CURRENT 754488 30/10/09 11:38:43

RMAN> list backup summary;

specification does not match any backup in the repository

RMAN>

Whilst the incarnations look a little incorrect (referring to ORCL2), the system does not break. So, no more need to hack around with incarnations if the system breaks accidentally. However, what if you register the other database…

[oracle@localhost ~]$ rman target system/oracle catalog rman/rman@orcl1
Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jan 27 05:44:06 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=1229390655)
connected to recovery catalog database
RMAN> register database;
starting full resync of recovery catalog
full resync complete
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of register command on default channel at 01/27/2013 05:44:12
RMAN-20002: target database already registered in recovery catalog

So, after a little effort it would appear I can’t easily break the incarnations in Oracle 11G. So let’s try. I recovered the ORCL1 database to create a new incarnation to see how ORCL2 would behave when connected:

on ORCL1:

[oracle@localhost ~]$ rman target system/oracle catalog rman/rman@orcl1
Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jan 27 12:32:09 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL1 (DBID=1229390655)
 connected to recovery catalog database


RMAN> list incarnation;
 List of Database Incarnations
 DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
 ------- ------- -------- ---------------- --- ---------- ----------
 1 18 ORCL1 1229390655 PARENT 1 13/08/09 23:00:48
 1 2 ORCL1 1229390655 PARENT 754488 30/10/09 11:38:43
 1 921 ORCL1 1229390655 CURRENT 10215936 27/01/13 12:27:12 <- new incarnation

<BR>

And now ORCL2 behaves a little differently, recognising the ORCL1 incarnations correctly, and throwing an error:

[oracle@localhost ~]$ rman target system/oracle catalog rman/rman@orcl1
Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jan 27 12:19:27 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL2 (DBID=1229390655)
 connected to recovery catalog database

RMAN> list incarnation;
 List of Database Incarnations
 DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
 ------- ------- -------- ---------------- --- ---------- ----------
 1 18 ORCL1 1229390655 PARENT 1 13/08/09 23:00:48
 1 2 ORCL1 1229390655 PARENT 754488 30/10/09 11:38:43
 1 921 ORCL1 1229390655 CURRENT 10215936 27/01/13 12:27:12

RMAN> list backup summary;
RMAN-00571: ===========================================================
 RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
 RMAN-00571: ===========================================================
 RMAN-03002: failure of list command at 01/27/2013 12:19:38
 RMAN-06004: ORACLE error from recovery catalog database: RMAN-20004: target database name does not match name in recovery catalog

So, what if I change the name of ORCL2 back to ORCL1. Can I reproduce my error then?

[oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Sun Jan 27 12:23:29 2013
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

SQL> startup mount;
 ORACLE instance started.
Total System Global Area 456146944 bytes
 Fixed Size 1344840 bytes
 Variable Size 381684408 bytes
 Database Buffers 67108864 bytes
 Redo Buffers 6008832 bytes
 Database mounted.

 SQL> exit
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@localhost dbs]$ nid target=system/oracle dbname=orcl1 setname=yes
DBNEWID: Release 11.2.0.2.0 - Production on Sun Jan 27 12:24:10 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL2 (DBID=1229390655)
Connected to server version 11.2.0
Control Files in database:
 /home/oracle/app/oracle/oradata/orcl/control01.ctl
 /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Change database name of database ORCL2 to ORCL1? (Y/[N]) => Y
Proceeding with operation
 Changing database name from ORCL2 to ORCL1
 Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - modified
 Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - modified
 Datafile /home/oracle/app/oracle/oradata/orcl/system01.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/sysaux01.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/undotbs01.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/users01.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/example01.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/APEX_1246426611663638.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/APEX_1265209995679366.db - wrote new name
 Datafile /home/oracle/app/oracle/oradata/orcl/temp01.db - wrote new name
 Control File /home/oracle/app/oracle/oradata/orcl/control01.ctl - wrote new name
 Control File /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl - wrote new name
 Instance shut down
Database name changed to ORCL1.
 Modify parameter file and generate a new password file before restarting.
 Succesfully changed database name.
 DBNEWID - Completed succesfully.
[note: I have already got the relevant init.ora and oratab setup]
[oracle@localhost dbs]$ . oraenv
 ORACLE_SID = [orcl2] ? orcl1
 The Oracle base has been set to /home/oracle/app/oracle

 [oracle@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Sun Jan 27 12:24:31 2013
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
 ORACLE instance started.
Total System Global Area 456146944 bytes
 Fixed Size 1344840 bytes
 Variable Size 381684408 bytes
 Database Buffers 67108864 bytes
 Redo Buffers 6008832 bytes
 Database mounted.
SQL> alter database open;
Database altered.

SQL >exit

[oracle@localhost dbs]$ rman target system/oracle catalog rman/rman@orcl1

Recovery Manager: Release 11.2.0.2.0 – Production on Sun Jan 27 12:52:45 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL1 (DBID=1229390655)
connected to recovery catalog database

RMAN> list incarnation;

database reset to incarnation 2
starting full resync of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 18 ORCL1 1229390655 PARENT 1 13/08/09 23:00:48
1 2 ORCL1 1229390655 CURRENT 754488 30/10/09 11:38:43
1 921 ORCL1 1229390655 ORPHAN 10215936 27/01/13 12:27:12

So, the newly rename-to ORCL1 thinks we are at incarnation 2. However, log back into the original ORCL1 and it resets the incarnation back to 921. Still no corruption, still no problem!

So, I still can’t prove whether the ALTER DATABASE SET INCARNATION command will work as mentioned to me, or whether it’s just something that allows me to recover across a resetlogs command. Looks like I’ll have to reinstall Oracle 10G… tomorrow.

Oracle Data Files

I was looking at the contents of a tablespace recently – just the extents, not a block dump – with the intention of shrinking a couple of the associated datafiles (don’t ask why – the reason is both stupid and irrelevant). I needed to know just how much I could shrink immediately, and if there were any quick wins in terms of moving objects from near the end of files so even more space could be reclaimed. I was a little surprised to discover that something which I have been doing for over 20 years was not as common knowledge as I thought it was, so I though I would try to let some more people know how to do this.

So, what’s in a tablespace? It’s made up of a number of datafiles (whether in ASM, RAW or on a journalled file-system is irrelevant for this conversation). Within the datafiles are extents. These are logical groupings of blocks – a multiple of the block size – which show where your objects are stored within the datafile.

I wrote this piece of SQL to show where the extents live in 1992. It still works today, regardless of whether your tablespaces are dictionary or locally managed:

 
SELECT tablespace_name, file_id, owner, segment_name, block_id begin,
blocks, block_id+blocks-1 end, bytes/1024 KB, '' free
FROM sys.dba_extents
where tablespace_name = 'USERS'
UNION
SELECT tablespace_name, file_id, '' owner, '' segment_name, block_id begin,
blocks, block_id+blocks-1 end, bytes/1024 KB, 'FREE' free 
FROM sys.dba_free_space 
where tablespace_name = 'USERS' 
ORDER BY 1,2,5

TSPC ID OWNER  SEGMENT_NAME    BEGIN  BLOCKS   END     KB  FREE
USERS 4 SCOTT  DEPT              128       8   135     64
USERS 4 SCOTT  PK_DEPT           136       8   143     64
USERS 4 SCOTT  EMP               144       8   151     64
USERS 4 SCOTT  PK_EMP            152       8   159     64
USERS 4 SCOTT  SALGRADE          160       8   167     64
USERS 4                          168     128   295   1024 FREE
USERS 4 SYS    TABLE_1           296       8   303     64
USERS 4 SYS    TABLE_1           304       8   311     64
USERS 4 SYS    TABLE_1           312       8   319     64
USERS 4 SYS    TABLE_1           320       8   327     64
USERS 4 SYS    TABLE_1           328       8   335     64
USERS 4 SYS    IND_1             336       8   343     64
USERS 4                          344      40   383    320 FREE
USERS 4                          384     128   511   1024 FREE
USERS 4                          512     128   639   1024 FREE
USERS 6 SYS    TABLE_2           128     128   255   1024
USERS 6                          256   12544 12799 100352 FREE
USERS 6 SYS    IND_2           12800     256 13055   2048

So, as we can see from the output above, the tablespace USERS has 2 datafiles; “4″ and “6″ (you can identify them fully using view DBA_DATA_FILES). We can shrink file 4 immediately by 1024K+1024K+320K = a bit over 2M.

File 6 is a bit more of a problem. There’s an index IND_2 in the way, unhelpfully located at the end of the datafile, which stops us from shrinking the datafile. If you try to resize the file using ALTER DATABASE DATAFIL E ‘+DATA/orcl/…..’ 10M, it will fail with the error ORA-03297: file contains used data beyond requested RESIZE value. In this case, the remedy may be as simple as performing an ALTER INDEX IND_2 REBUILD TABLESPACE USERS ONLINE; This will (probably) relocate the index somewhere lower in the file and allow us to shrink the datafile. Moving tables with an ALTER TABLE … MOVE command may prove more difficult depending upon your throughput due to the extra locks needed at the start and end of the transaction, and extra care needs to be take with any LOB objects involved.

UKOUG Conference 2012 Agenda Online

Today the UK’s big Oracle User event has just lifted its skirts to show off its new frilly agenda, which can be found here.

I have been a member of the UKOUG for well over a decade, and a Special Interest Group (SIG) Deputy Chairman for the last 5 or 6 years. As an independent consultant this costs me money in lost earnings, basic membership fees, travel, hotels, flowers for the wife, etc. However, I make my living as an Oracle consultant and I need to keep up with trends, understand a broad range of information about the Oracle world, network, and learn. All of these opportunities are afforded by the UKOUG, especially at Conference time.
So, read the agenda, think about what you could do to improve the way things work at your company, and pester your boss for a pass. You never know where it could lead. If you’re lucky, it could lead to an invite to the launch of the agenda, involving Oracle luminaries, canapes, beer and dancing (and here’s the evidence)

Who can you spot? Debra Lilley? Bernie Duggs? Julian Dyke? Martin Widlake? Coskan Gundogar? Dawn Rigby?

Outside Interests – not work!

So, what do you do that isn’t work. Sure, you work hard in the office. You might even do work when you get home at night on your own “lab” setup – VM’s make that so easy these days. Even an old desktop can spin up a couple of VM’s if you stick some extra memory in it. I just bought 12GB for £25, so that’s pretty cheap and easy to do.

You might have read some technical books on your commute to and from the office, or listened to technical podcasts on the way there and back if you drive.

When you stop working, or learning, or “playing” with technology, what do you do to unwind? Well, next week I have decided to drive from my home in London to Naples as part of a 100-strong banger rally. You can read more about it on the Team Monkey Wrench blog. I love cars (even this dreadful one), and it’s a pretty good adventure.

So, what do you do to relax? Jump out of a plane? Play chess against your kids? Drink yourself senseless in a bar? We all need a break from technology now and again. Go out into the world a do something fun.

The Fear of Presenting

I am deputy chairman of the UK Oracle User Group: Availability, Infrastructure and Management  (AIM) SIG. We arrange several groups per year where we look to get speakers to present on all manner of subject in relation to the remit of the SIG: Exadata, RAC, Partitioning, Grid Control, Managing DBA’s, etc (for more info, check here). However, it can sometimes be difficult to get presenters, and close to impossible to get new presenters.

Now, most people are pretty scared to get up in front of their peers and present. It initially seems quite a daunting prospect. However, I was recently reading Dr Richard Feynman’s autobiography, which puts the fear of your first presentation into perspective:

When he was a graduate student at Princeton, Feynman was working as a research student and was encouraged by John Wheeler to give a talk on an electrodynamics theory they were working on, as “you need experience in giving talks”. Feynman found out later that especially invited to the talk were Henry Norris Russell, Professor Wolfgang Pauli, and Professor Albert Einstein. Three of the most preeminent scientists of their day (you might have heard of at least one of them).

So, if you think your talk is going to be difficult standing in front of a couple of contractors, 3 geeks from the local council, a couple of bankers and some bloke from a supermarket, it’s not. Well, not compared to being open to critique by Pauli and Einstein! Nobody is going to be critical of your talk, only supportive. Nobody expects a presenter to have all of the answers. Many of us have witnessed the consummate presenter Jonathan Lewis writing impossible SQL on a flip chart, much to his chagrin. So if Jonathan can make an amusing mistake, I don’t think we’re overly worried about anybody else making one either.

As for Feynman, his only regret about the seminar as that he can’t remember exactly what Prof. Pauli has said when he raised a question, as he thinks it might have been the answer to making a quantum version of his electrodynamic theory.

So, if you DO present, and I would really encourage you to present,  listen carefully to any questions asked by the audience. They just might give you the answer you are looking for.

For the record and to stop frivolous posts, Martin, I DO present occasionally. Just not as much as perhaps I should.

Bank with your conscience

Well, there’s a lot been said about bankers in the last few years, much of it negative and with good reason. Tony Robinson asked if they were human in an excellent rant on Question Time. Manipulating the LIBOR is pretty egregious. It undermines the basis of truth behind our (formerly) well-regarded banks. It shows them to be self-interested liars and cheats.

Many of the huge numbers of people who work for banks are just doing their job, ticking along, feeding their families, not being evil. I have worked as a consultant inside many banks, and have also worked directly for one or two. The staff in banks are like workers everywhere; some good, some bad, some incompetent and some dazzling in their genius. Like all large companies.

At present, Barclays has covered itself in shame and I suspect that it’s merely the first name on the guilty sheet. However, I’m a Barclays customer. I have a couple of accounts with them. I use the current account daily.

I don’t think I can continue to be a customer of a bank whose behaviour is so reprehensible.

It would seem that the law cannot prosecute the offending parties within the bank, so I feel my only recourse is to leave the bank. I don’t need Barclays. I’m going to move somewhere more ethical, so I need to look at the company as well as the interest rates. I already have other current accounts that I can use, which will happen in the short term until I strike the right balance between ethics and savings.

If EVERYBODY did that, Barclays would no longer exist to be unethical.That would make a lot of people unemployed, including several friends and family members… but…

If EVERYBODY did that, banks such as the Co-op, Triodos Bank, The Ecology Building Society and the Charity Bank would grow large, and employ those ordinary employees that Barclays had to let go.

Ask yourself this; why do you bank with your current bank? Apparently, you are more likely to get divorced than change bank account – that’s why they pay good interest to kids. Get ‘em young and get ‘em hooked.

It’s SO easy to change your current account it’s amazing that more people don’t do it. Plenty of banks even pay you to move to them! You open an account with your new bank and ask them to move your account. They do it all – letter to your employer, direct debits, standing orders, everything. And they usually give you a (temporary) large overdraft to ensure you have no payment problems.

Bank with your conscience

Complex Passwords

Increasing numbers of Yahoo mail passwords appear to have been compromised; I don’t use Yahoo [although in a historically stupid move, I have multiple email addresses from multiple providers including hotmail, gmail, my ISP and my own domain ]. Anyway, I have been getting an increasing number of spam emails from friends and acquaintances with Yahoo accounts. Not from any other source. I have been multiply spammed from multiple yahoo accounts this year, but from no other provider. The conclusion I draw from this is that either Yahoo has had its password file compromised and the spammers are slowly working their way through it, or it has a significant hole in its security, or there is a focussed piece of malware out there harvesting Yahoo passwords.

Either way, I would strongly recommend that anybody who uses a Yahoo email go and change their password, make it computer-complex (i.e. long), write it on a Post-it and stick it next to your desk (at home – not in the office where everyone can read it)

WHAT! I hear you cry. Why do THAT! You’re mad! Well, no. Brute force attacks are rare, and they will generally use standard dictionary words. I hate to tell you, but hackers know you replace E with 3, A with 4 and L with 1. So your password of AFR1C4 it as much a dictionary word as AFRICA to a computer. [ If you want a really hard-to-crack, easy-to-remember password, I suggest you refer to this XKCD cartoon http://xkcd.com/936 ]

The likelihood is that your password will be compromised by malware and not brute force attacks, in which case it doesn’t matter how complex it is. The chance it will be compromised by a burglar looking in your desk drawer is very low indeed (although people with teenage children need to be a bit more cautious.)

And change your passwords occasionally – at least once a year. How many of you out there have 2 or 3 different passwords that they use everywhere? A (seemingly) complex one for your bank account and “password” for your forum accounts? And you have NEVER changed them as it would mean changing 200 accounts and it’s too much like hard work? Thought so. One day you will be pwned by the hackers.