29 Nov The curious case of the datafile that “belonged” to 2 databases
First I want to start disclosing that I have an idea on how this happened, but haven’t been able to recreate this issue, also that I was lucky enough that the tablespace that had the issue was a DUMMY tablespace so it didn’t cause an issue.
So now that the above disclosure is done, what happened is that I have a SOURCE database with 1 location for it’s datafiles +DATA1/SOURCE, but at some point someone added a second location for testing for a TEST1 tablespace in +DATA2/TEST, this SOURCE database was duplicated via RMAN to 2 databases, call them DUP1 and DUP2. In the RMAN duplicate command , the parameter DB_FILE_NAME_CONVERT was used to change the location of the datafiles of the SOURCE database to the DUP1 or DUP2, but it was only input for the known location, not for the testing location.
What it was input in the RMAN command:
How it should have been:
And because the diskgroup and directory of +DATA2/TEST exist where the DUP1 and DUP2 would reside, it didn’t cause an issue when it ran the duplicate, and this is something that is still yet unknown to me, the duplicate finished successfully for both databases and no issues arose from the open resetlogs.
As you might already know when you issue the DUPLICATE command the DBID is implicitly changed , and what this does is it updates the controlfile with the new DBID and also the datafile headers , that way is how it will know that these datafiles XX belong to database NN.
So when both databases where up and running, I ran a backup from both databases, DUP1 finished successfully, DUP2 failed with the following error:
One of the first things I did was run a VALIDATE DATABASE, and indeed it was showing me the same error:
The next thing I did was to verify what that file was and the DBID of the DUP2 database, as the error of the header led me that way
Because RMAN error 06169 was telling me that it couldn’t read the header, I did a dump of the header
And indeed saw that the DBID of test01.dbf belonged to DUP1, which had been the first of the 2 databases duplicated
So just to be sure, I went to DUP1 and verified, and yes it was the same DBID that the header dump told me ,and yes the tablepsace was online as well in DUP1
Because that particular tablespace was of no use for the application, I just went ahead and dropped it
I, have to say it’s in 220.127.116.11 with a GI of 18.104.22.168 in RHEL 5 x86_64, but it goes to say that whenever you do a duplicate and you use the DB_FILE_NAME_CONVERT , be sure that all of your locations are covered . But what is really bugging me is how DUP2 was able to open resetlogs without this being an issue, I will try to recreate this and update this post if and when I do.