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)
Generate command to Rename log file from ASM to non-ASM [message #581657] Tue, 09 April 2013 12:24 Go to next message
Lucky A
Messages: 53
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
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.
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 Go to previous messageGo to next message
Lucky A
Messages: 53
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57602
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
tick, tick, tick
how does does time spent here compare to opening an editor & doing string replace?
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 Go to previous messageGo to next message
Lucky A
Messages: 53
Registered: October 2007
Member
Is there a way to modify the above query to achieve the desired result?
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 Go to previous messageGo to next message
BlackSwan
Messages: 21938
Registered: January 2009
Senior Member
  1* select replace('within string group exists','group','redo') from dual
SQL> /

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

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 Go to previous message
Lucky A
Messages: 53
Registered: October 2007
Member
Thanks for your help.
Previous Topic: Outer inner loop
Next Topic: insert Rupee(currency) symbol
Goto Forum:
  


Current Time: Wed Apr 16 12:38:58 CDT 2014

Total time taken to generate the page: 0.11364 seconds