Home » RDBMS Server » Backup & Recovery » How to link asm file alias and db file names? (10.2.0.4)
How to link asm file alias and db file names? [message #570565] Mon, 12 November 2012 06:15 Go to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
John Watson
Messages: 4610
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 #570572 is a reply to message #570565] Mon, 12 November 2012 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually we found the solution for this point: even if the rman log didn't contain any information about the file renames, the alert.log file contains them. For the files I mentioned we have the following lines (long lines spitted):
Mon Nov 12 10:48:04 NFT 2012
Full restore complete of datafile 23 to datafile copy \
+ORAXQG1_L136_DG1/oraxpg1/datafile/n47caw1.318.799151641.  Elapsed time: 0:14:04
  checkpoint is 310127512252
  last deallocation scn is 309742422042
...
Mon Nov 12 10:54:57 NFT 2012
Full restore complete of datafile 53 to datafile copy \
+ORAXQG1_L136_DG1/oraxpg1/datafile/n47caw1.276.799152039.  Elapsed time: 0:14:19
  checkpoint is 310127512503
  last deallocation scn is 309742422045

Easy to crunch the alert.log to get the associated ALTER DATABASE statement we want (last egrep added to only output the files I mentioned in my previous post):
grep 'Full restore complete of datafile' alertX.log | \
  sed -e 's/Full restore complete of datafile/alter database rename file/' \
      -e "s/datafile copy /'/" \
      -e "s/\.  Elapsed time:.*$/';/" \
| egrep ' 23 | 53 '
==>
alter database rename file 23 to '+ORAXQG1_L136_DG1/oraxpg1/datafile/n47caw1.318.799151641';
alter database rename file 53 to '+ORAXQG1_L136_DG1/oraxpg1/datafile/n47caw1.276.799152039';

Let's see now if we'll can complete our clone.

I'm still interesting if someone can find a solution based on the dictionaries (ASM and DB).

Regards
Michel
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
John Watson
Messages: 4610
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
and then generating sensible aliases to present to the database.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
John Watson
Messages: 4610
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 #570598 is a reply to message #570582] Mon, 12 November 2012 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe we'll try this one next time we'll get the problem. Wink
Thanks for your time.

Regards
Michel
Re: How to link asm file alias and db file names? [message #570600 is a reply to message #570598] Mon, 12 November 2012 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Forgot to mention that I agree with your diagnosis in your first paragraph.

Regards
Michel
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 Go to previous messageGo to next message
John Watson
Messages: 4610
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 #570678 is a reply to message #570565] Wed, 14 November 2012 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For the record and to be more specific the error was in a DUPLICATE command after the end of the RESTORE part and at the beginning of the SWITCH ALL one.
The actual error we got is:
Fin de restore dans 12.11.2012 01:09:16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: erreur irrécupérable lors de l'exécution de la commande
RMAN-04022: l'ID de montage de base de données cible 4235612882 ne concorde pas 
avec l'ID de montage de canal 4236258689
ORA-03114: pas connecté à ORACLE
RMAN-03002: échec de la commande Duplicate Db à 11/12/2012 01:09:16

Translated it comes:
End of restore at 12.11.2012 01:09:16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-04022: target database mount id 4235612882 does not match channel's mount id 4236258689
ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 11/12/2012 01:09:16

Note that the actual database ID (4120806728) is neither 4235612882 nor 4236258689 as mentioned in the error message!

Regards
Michel
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 Go to previous message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Recovery from 11.2.0.3 PSU2 RAC to non-RAC
Next Topic: ORA-27000: skgfqsbi: failed to initialize storage subsystem (SBT) layer
Goto Forum:
  


Current Time: Tue Sep 30 15:32:35 CDT 2014

Total time taken to generate the page: 0.05658 seconds