OraFAQ Forum: SQL & PL/SQL » Generate command to Rename log file from ASM to non-ASM

Home » SQL & PL/SQL » SQL & PL/SQL » Generate command to Rename log file from ASM to non-ASM (Oracle 11.2.0.3 on RedHat)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Generate command to Rename log file from ASM to non-ASM [message #581657]

Tue, 09 April 2013 12:24

Lucky A
Messages: 68
Registered: October 2007

Member

Hi,

I am in the process of recovering a database from an ASM environment to a non-ASM environment using RMAN. I've got a query to generate the command to RENAME the file:

SELECT 'ALTER DATABASE RENAME FILE '''||MEMBER||''' TO '''||'/u01/data/dev'||
SUBSTR(MEMBER,
INSTR(MEMBER, '/', -1, 1),
INSTR(MEMBER, '.', 1, 1) - INSTR(MEMBER, '/', -1, 1)) ||
DECODE(SUBSTR (MEMBER, 1, INSTR(MEMBER, '/', 1, 1) -1), '+DATA', 'a', '+FRA', 'b', 'c') ||'.log'';'
FROM V$LOGFILE;

When I run the query, this is an example of the result I get:

ALTER DATABASE RENAME FILE '+FRA/db1/onlinelog/group_3.259.766075171' TO '/u01/data/dev/group_3b.log';
ALTER DATABASE RENAME FILE '+DATA/db1/onlinelog/group_1.262.766075829' TO '/u01/data/dev/group_1a.log';

But I want to replace the "group" with "redo" and get the below result:

ALTER DATABASE RENAME FILE '+FRA/db1/onlinelog/group_3.259.766075171' TO '/u01/data/dev/redo_3b.log';
ALTER DATABASE RENAME FILE '+DATA/db1/onlinelog/group_1.262.766075829' TO '/u01/data/dev/redo_1a.log';

Thanks,

Lucky

Report message to a moderator

Re: Generate command to Rename log file from ASM to non-ASM [message #581659 is a reply to message #581657]

Tue, 09 April 2013 12:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

when your only tool is a hammer, every problem appears to be a nail.

Consider directing the output to a text file & then use any text editor to change the strings to be as you require.

Report message to a moderator

Re: Generate command to Rename log file from ASM to non-ASM [message #581663 is a reply to message #581659]

Tue, 09 April 2013 12:45

Lucky A
Messages: 68
Registered: October 2007

Member

I'm trying to generate a command, using the query to save time. Using a text editor for a large database will be time consuming.

Report message to a moderator

Re: Generate command to Rename log file from ASM to non-ASM [message #581664 is a reply to message #581657]

Tue, 09 April 2013 12:46

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

But I want to replace the "group" with "redo" and get the below result:

So do it.
What prevent you from using REPLACE function?

Regards
Michel

Report message to a moderator

Re: Generate command to Rename log file from ASM to non-ASM [message #581665 is a reply to message #581663]

Tue, 09 April 2013 12:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

tick, tick, tick
how does does time spent here compare to opening an editor & doing string replace?

Report message to a moderator

Re: Generate command to Rename log file from ASM to non-ASM [message #581667 is a reply to message #581665]

Tue, 09 April 2013 12:58

Lucky A
Messages: 68
Registered: October 2007

Member

Is there a way to modify the above query to achieve the desired result?

Report message to a moderator

Re: Generate command to Rename log file from ASM to non-ASM [message #581670 is a reply to message #581667]

Tue, 09 April 2013 13:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

  1* select replace('within string group exists','group','redo') from dual
SQL> /

REPLACE('WITHINSTRINGGROU
-------------------------
within string redo exists

Report message to a moderator

Re: Generate command to Rename log file from ASM to non-ASM [message #581678 is a reply to message #581670]

Tue, 09 April 2013 15:11

Lucky A
Messages: 68
Registered: October 2007

Member

Thanks for your help.

Report message to a moderator

Previous Topic:	Outer inner loop
Next Topic:	insert Rupee(currency) symbol

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 29 23:33:03 CDT 2025