| How to link asm file alias and db file names? [message #570565] |
Mon, 12 November 2012 06:15  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Here's the case.
During a duplicate process to a new database name, rman crashed after the restore but before the switch datafile all.
So now, we have under ASM the data files under the correct (new) diskgroup but v$datafile contains the previous names (and so diskgroup) and v$datafile_header is empty.
RMAN is completly lost, our solution is to manually rename each file under SQL*Plus using ALTER DATABASE RENAME FILE.
Unfortunately, we are using or migrating to OMF, so file names are meaningless and we are unable to associate ASM files with database files.
Here's the question:
Does anyone know a way (query or anything else) to associate the ASM files to the database files.
Here's an abstract of what we have for one (small) tablespace:
ASMCMD [+ORAXQG1_L136_DG1/ORAXPG1/DATAFILE] > ls -l N47CAW*
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE NOV. 12 10:00:00 Y N47CAW1.276.799152039
DATAFILE UNPROT COARSE NOV. 12 10:00:00 Y N47CAW1.318.799151641
SQL> select file#, name from v$datafile where ts#=17
2 /
FILE# NAME
---------- ------------------------------------------------------------
23 +ORAXPG1_L136_DG1/oraxpg1/n47oaw/n47caw101.dbf
53 +ORAXPG1_L136_DG1/oraxpg1/datafile/n47caw1.315.764937863
How to know if the previous "+ORAXPG1_L136_DG1/oraxpg1/datafile/n47caw1.315.764937863" is now "N47CAW1.276.799152039" or "N47CAW1.318.799151641"?
Regards
Michel
[Edit: fix ALTER DATABASE statement]
[Updated on: Mon, 12 November 2012 07:14] Report message to a moderator |
|
|
|
| Re: How to link asm file alias and db file names? [message #570571 is a reply to message #570565] |
Mon, 12 November 2012 06:55  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I can't see a way to do it. But since you can discover the new names of all the datafiles can you not crate a new controlfile with CREATE CONTROLFILE and list them all in the DATAFILE clause? Then Oracle should be able to sort it out.
Perhaps! I would derive the filenames from a query against v$asm_file.
Hope this helps.
|
|
|
|
|
|
| Re: How to link asm file alias and db file names? [message #570573 is a reply to message #570571] |
Mon, 12 November 2012 07:05 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Thanks for your answer John, the problem was to associate the file I can get from v$datafile and the one with v$asm_alias (v$asm_file does not contain the name), names are different, file# and file_number either. (The names in v$asm_alias are the names we get from "ls" command in asmcmd.)
Currently I failed to see how we can join both dictionaries information.
Regards
Michel
[Updated on: Mon, 12 November 2012 07:06] Report message to a moderator |
|
|
|
| Re: How to link asm file alias and db file names? [message #570575 is a reply to message #570573] |
Mon, 12 November 2012 07:18 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I was thinking of calculating the names, like this: 1 select d.name,a.name,'+'||d.name||'/'||a.name as asmname from
2 v$asm_alias a join v$asm_file f on
3 (a.group_number=f.group_number and
4 a.file_number=f.file_number and
5 a.FILE_INCARNATION=f.incarnation)
6 join v$asm_diskgroup d on(a.group_number=d.group_number)
7* where f.type='DATAFILE'
SQL> /
NAME NAME ASMNAME
-------------------- -------------------- ----------------------------------------
DG1 SYSTEM.256.792534317 +DG1/SYSTEM.256.792534317
DG1 SYSAUX.257.792534343 +DG1/SYSAUX.257.792534343
DG1 UNDO1.258.792534369 +DG1/UNDO1.258.792534369
DG1 UNDO2.265.792535039 +DG1/UNDO2.265.792535039
DG1 SYSTEM.273.798213425 +DG1/SYSTEM.273.798213425
DG1 SYSAUX.274.798213429 +DG1/SYSAUX.274.798213429
DG1 UNDO1.275.798213431 +DG1/UNDO1.275.798213431
DG1 UNDO2.276.798213435 +DG1/UNDO2.276.798213435
Glad you got the answer quickly! A much better solution than mine.
|
|
|
|
| Re: How to link asm file alias and db file names? [message #570579 is a reply to message #570575] |
Mon, 12 November 2012 08:33 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
With what you posted you get the name in ASM but not how to relate it with the name in the databas (remember that we are in the middle of a bad crash and names are different in the database and in ASM).
Actually my solution does not work as it as ALTER DATABASE RENAME does not accept a file number but a file name.
So we have to change it to a 3 steps method (still restricting the script to 2 files I mentioned to save space).
1/ From the alert.log, as above, create a first file that will contain the file number and last part of the alter database statement:
grep 'Full restore complete of datafile' alertX.log | \
sed -e 's/Full restore complete of datafile //' \
-e "s/datafile copy /'/" \
-e "s/\. Elapsed time:.*$/';/" \
-e 's/^\(.\) /0\1 /' | \
sort -n \
| egrep '^23 |^53 '
==>
23 to '+ORAXQG1_L136_DG1/oraxpg1/datafile/n47caw1.318.799151641';
53 to '+ORAXQG1_L136_DG1/oraxpg1/datafile/n47caw1.276.799152039';
2/ Generate a spool file in SQL*Plus with the file number and the first par of the statement:
SQL> col f format a88
SQL> set head off
SQL> select to_char(file#,'fm00'),
2 'alter database rename file '''||name||'''' f
3 from v$datafile
4 where file# in (23,53)
5 order by 1
6 /
23 alter database rename file '+ORAXQG1_L136_DG1/oraxpg1/datafile/n47caw1.318.799151641'
53 alter database rename file '+ORAXQG1_L136_DG1/oraxpg1/datafile/n47caw1.276.799152039'
3/ In the end use "join" in Unix to generate the final script to execute to rename the file:
join -o 1.2,1.3,1.4,1.5,1.6,2.2,2.3 michel2 michel1 >michel3
==> (long lines splitted)
alter database rename file '+ORAXPG1_L136_DG1/oraxpg1/n47oaw/n47caw101.dbf'
to '+ORAXQG1_L136_DG1/oraxpg1/datafile/n47caw1.318.799151641';
alter database rename file '+ORAXPG1_L136_DG1/oraxpg1/datafile/n47caw1.315.764937863'
to '+ORAXQG1_L136_DG1/oraxpg1/datafile/n47caw1.276.799152039';
After that, it is (almost) OK:
- RMAN recover
- SQL*Plus "recover using backup controlfile until cancel" (the open resetlogs does not work here)
- CANCEL (at first ask)
- alter database open resetlogs;
Regards
Michel
[Edit: change step 2 query which was not the correct one used.]
[Updated on: Tue, 13 November 2012 08:53] Report message to a moderator |
|
|
|
| Re: How to link asm file alias and db file names? [message #570582 is a reply to message #570579] |
Mon, 12 November 2012 08:46 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Agreed! My technique cannot give you a mapping of old to new name. I suspect that such a mapping is architecturally impossible, because ASM has no access to controlfile or dictionary information. The only information it has is the file extent metadata, which does not (I believe) contain anything as useful as tablespace and datafile numbers. Sure, files have a tag which may include the tablespace name, but that is of no technical significance.
I do wonder if my technique would have worked: create a new controlfile specifying all those calculated datafile names, mount and open. When the database moves from mount to open it should inspect the datafile headers, therefore work out which datafile belongs to which tablespace. The problem might be that the new filenames would conflict with the names in the dictionary. But I think Oracle would sort that out, and update the dictionary with the newly discovered names.
I would love to test this, but I'm not going to.
|
|
|
|
|
|
|
|
| Re: How to link asm file alias and db file names? [message #570602 is a reply to message #570600] |
Mon, 12 November 2012 11:06 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Well, I've done the test: I renamed my datafiles with OS commands, and created a controlfile pointing to the new names, and opened the database no problem. I didn't extend the test to include temp and online logfiles.
Thinking it through:
The controlfile includes the datafile names, so Oracle can locate them. If you look at the header of each datafile,
strings o1_mf_system_89spqwco_.dbf|more
the tablespace name is there. So having located the datafiles, Oracle can find the system tablespace. Then (afaik) it will read the bootstrap$ table (I assume its location is hardcoded somewhere?) to find the ddl and the physical location of the critical data dictionary tables:
select * from bootstrap$ order by line#;
None of those tables include the datafile names, not even file$. So Oracle can open the dictionary without needing to know about changed file names. Looking at the source code for dba_data_files the reason becomes clear: the file name is not retrieved from a dictionary table, but from v$dbfile in the controlfile. So nowhere in the dictionary do we actually have the datafile names.
That is my pointless research for the day! Thank you for raising the issue, Michel. Though of course I'm sorry you had to deal with such a horrible problem.
But there you go: life may be tough when you are a DBA, but it ain't boring.
|
|
|
|
|
|
| Re: How to link asm file alias and db file names? [message #570705 is a reply to message #570678] |
Wed, 14 November 2012 12:49 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
After more investigating, the script log contains (translation: "base de données"="database", "cible"="target", "auxiliaire"="auxiliary","Démarrage"="Starting","Script mémoire"="Memory script"):
Recovery Manager: Release 10.2.0.5.0 - Production on Dim. Nov. 11 20:33:04 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN>
connecté à la base de données cible : ORAXPG1 (DBID=4120806728)
RMAN>
connexion établie avec la base de données auxiliaire : ORAXQG1 (non montée)
RMAN>
RMAN> sql 'alter session set optimizer_mode=rule';
utilisation du fichier de contrôle de la base de données cible au lieu du catalogue de récupération
instruction SQL : alter session set optimizer_mode=rule
RMAN> RUN {
2> ALLOCATE AUXILIARY CHANNEL CH0 TYPE DISK;
3> ALLOCATE AUXILIARY CHANNEL CH1 TYPE DISK;
4> ALLOCATE AUXILIARY CHANNEL CH2 TYPE DISK;
5> ALLOCATE AUXILIARY CHANNEL CH3 TYPE DISK;
6> ALLOCATE AUXILIARY CHANNEL CH4 TYPE DISK;
7> ALLOCATE AUXILIARY CHANNEL CH5 TYPE DISK;
8> ALLOCATE AUXILIARY CHANNEL CH6 TYPE DISK;
9> ALLOCATE AUXILIARY CHANNEL CH7 TYPE DISK;
10> SET UNTIL TIME "to_date('11.11.2012 08:00:00','DD.MM.YYYY HH24:MI:SS')";
11> DUPLICATE TARGET DATABASE TO oraxqg1;
12> }
I said, that the error came at the beginning of SWITCH ALL statement (which would be what I'd manually do) but in fact it is in the CHECK READONLY (an internal statement generated by the DUPLICATE command):
Démarrage de Duplicate Db dans 11.11.2012 20:33:22
contenu de script mémoire:
{
set until scn 310127515845;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
...
set newname for clone datafile 78 to new;
restore
check readonly
clone database
;
}
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
