Home » Server Options » Data Guard » Replace string (Oracle RDBMS 11g)
Replace string [message #651543] Thu, 19 May 2016 23:45 Go to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Good a day to everyone,

We have got a big database with 504 datafiles within, and I have got create a physical standby for it, ok, it's easy but difficult (of course, to me because of poor SQL) to convert string.

We have got datafile location as following

Primary: +DATA/wrhprim/datafile/data.279.907075777
DG: +DATA/wrhstd/datafile/data.279.907075777

The simply SQL to generate file_name location is

select 'set newname for datafile '''||file_name||''' to '''||''||file_name||''''||';'
     from dba_data_files where rownum<=10;


and the simply sql to get wrhprim is:

SELECT SUBSTR(file_name,7,7) from dba_data_files where rownum<=10;


But, I have no idea to put the convert string into one of SQL (wrhprim to wrhstd)

May you help me, thank you very much

[Updated on: Thu, 19 May 2016 23:47]

Report message to a moderator

Re: Replace string [message #651544 is a reply to message #651543] Fri, 20 May 2016 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is just the opposite with me - I'm not a DBA so the following code is probably quite stupid but I hope you'll get the idea: if the sample string looks like 'set newname for datafile wrhprim to wrhprim', I'm going to replace the second 'wrhprim' occurrence to 'wrhstd' so that the result looks like 'set newname for datafile wrhprim to wrhstd':
SQL> with test as
  2    (select 'set newname for datafile wrhprim to wrhprim' col from dual)
  3  select regexp_replace(col, 'wrhprim', 'wrhstd', 1, 2) result
  4  from test;

RESULT
------------------------------------------
set newname for datafile wrhprim to wrhstd

SQL>

If that's not what you are looking for, could you provide a sample input and desired output (or just wait for someone who, actually, knows what's going on).
Re: Replace string [message #651545 is a reply to message #651543] Fri, 20 May 2016 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No need of any SET NEWNAME, Oracle does it for you:
db_file_name_convert='wrhprim,wrhstd'


Re: Replace string [message #651547 is a reply to message #651543] Fri, 20 May 2016 01:11 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Are you using OMF? If so, Uncle Oracle will take care of the file names, you don't need to do anything.
Re: Replace string [message #651558 is a reply to message #651547] Fri, 20 May 2016 01:31 Go to previous messageGo to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
@LittleFoot: Thank you very much, your query is exactly what I need.

     WITH dtfstri
        AS (SELECT      'set newname for datafile '''
                     || file_name
                     || ''' to '''
                     || ''
                     || file_name
                     || ''''
                     || ';'
                         col
              FROM   dba_data_files)
SELECT   REGEXP_REPLACE (col,
                         'wrhprim',
                         'wrhstd',
                         1,
                         2)
             result
  FROM   dtfstri;

set newname for datafile '+DATA/wrhprim/datafile/indx_0631.676.907173861' to '+DATA/wrhstd/datafile/indx_0631.676.907173861';



@Michel & John: Thank you for your advice. However, I often do manually many times, even using RMAN restore/recovery without duplicate method, thanks again.

[Updated on: Fri, 20 May 2016 01:32]

Report message to a moderator

Re: Replace string [message #651561 is a reply to message #651558] Fri, 20 May 2016 01:38 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, you do NOT need to use SET NEWNAME; just let Oracle do its job.

Previous Topic: Duplicate DB for Standby
Next Topic: No of archivelog generation between the period in RAC envirnoment
Goto Forum:
  


Current Time: Thu Mar 28 10:01:33 CDT 2024