You Asked

Hi tom,

i've run into an issue out of the ordinary.

while adding datafile to an existing tablespace we had fews trailing characters inbedded (by mistake) at the end of the .dbf extension.
We have dbvisit replication broken due to this since it can't replicate the ddl with special characters.

we've manage to get the exact command type by the admin in the alertlog:

alter tablespace SCI_DATA add datafile '/home/oracle/oradata/ORCLPRD/Datafiles/SCI_DATA02.dbf''^[[C^[' size 1G autoextend on next 512M
Completed: alter tablespace SCI_DATA add datafile '/home/oracle/oradata/ORCLPRD/Datafiles/SCI_DATA02.dbf''^[[C^[' size 1G autoextend on next 512M

is their a way to inteprete the character being typed after the .dbf so that we can do a rename. (probably some space and backspace typed in
-> Notice the ' after the .dbf

SQL> select * from dba_data_files where file_ID=10;

FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/home/oracle/oradata/ORCLPRD/Datafiles/SCI_DATA02.dbf'
0 SCI_DATA 1.7717E+10 2162688 AVAILABLE 10 YES 3.4360E+10 4194302 65536 1.7716E+10 2162560 ONLINE

SQL> select dump(file_name) from dba_data_files where file_name like '%/home/oracle/oradata/ORCLPRD/Datafiles/SCI_DATA02.dbf%';

DUMP(FILE_NAME)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Typ=1 Len=58: 47,104,111,109,101,47,111,114,97,99,108,101,47,111,114,97,100,97,116,97,47,79,82,67,76,80,82,68,47,68,97,116,97,102,105,108,101,115,47,83,67,73,95,68,65,84,65,48,50,46,100,98,102,39,27,91,67,27

SQL> select chr(46),chr(100),chr(98),chr(102),chr(39),chr(27),chr(91),chr(67),chr(27) from dual;

C C C C C C C C C
- - - - - - - - -
. d b f ' C

Client doesn't want to create a new tablespace and move the data since he can't afford downtime more than 1 hours (here at least 70Go of data)
-> I believe recreation of control file might be too risky
-> The use of pl/sql to store the name in a variable and use it in the alter database rename command failed too.

i was betting on a last solution which would be through the mean of RMAN.

RMAN> REPORT SCHEMA;
RMAN> SQL 'ALTER TABLESPACE SCI_DATA OFFLINE';
RMAN> COPY DATAFILE 10 TO '/home/oracle/oradata/ORCLPRD/Datafiles/SCI_DATA03.dbf';
RMAN> SWITCH DATAFILE 10 TO COPY;
RMAN> RECOVER TABLESPACE SCI_DATA;
RMAN> SQL 'ALTER TABLESPACE SCI_DATA ONLINE';
RMAN> HOST 'rm /home/oracle/oradata/ORCLPRD/Datafiles/SCI_DATA02*';

Thnks for your help & advise?

Regards,
Atish

and Connor said...

So I did this:

create tablespace BLAH
datafile 'C:\ORACLE\ORADATA\DB122\BLAHßßß.DBF' size 10m;

I can derive the characters with a little SQL, eg

SQL> select ascii(substr(name,i,1))
  2  from ( select name from v$datafile where name like '%BLAH%' ),
  3    ( select rownum i from dual connect by level <= 35 );

ASCII(SUBSTR(NAME,I,1))
-----------------------
                     67
                     58
                     92
                     79
                     82
                     65
                     67
                     76
                     69
                     92
                     79
                     82
                     65
                     68
                     65
                     84
                     65
                     92
                     68
                     66
                     49
                     50
                     50
                     92
                     66
                     76
                     65
                     72
                  50079
                  50079
                  50079
                     46
                     68
                     66
                     70

so I can build up the rename I need..

alter tablespace blah offline;

--
-- then rename it at OS level, and then build the rename command
--

set serveroutput on
declare
   fname1 varchar2(100);
   fname2 varchar2(100);
begin
  select name into fname1 from v$datafile where file# = 9;
  fname2 := replace(fname1,chr(50079));
  
  dbms_output.put_line('alter database rename file '''||fname1||''' to '''||fname2||'''');
  execute immediate 'alter database rename file '''||fname1||''' to '''||fname2||'''';
end;
/

alter tablespace blah online;

SQL> select name
  2  from v$datafile
  3  where file# = 9;

NAME
-------------------------------------
C:\ORACLE\ORADATA\DB122\BLAH.DBF

and we're done

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

pl/sql code review for multiple replace function

Nicolas Rabine, April 03, 2017 - 2:52 pm UTC

Hi, that was awesome and a very good approach to correct the issue.
but here you repeated 3 time the same character in the datafile name and it was much easy to deal with in the replace function. how about multiple different characters to replace?

i found this somewhere while googling, do you think its worth a try or maybe you've something more simple (sorry but i'm not a pl/sql expert)?

# Create function

CREATE TYPE t_text IS TABLE OF VARCHAR2(256);

CREATE FUNCTION multiple_replace(
  in_text IN VARCHAR2, in_old IN t_text, in_new IN t_text
)
  RETURN VARCHAR2
AS
  v_result VARCHAR2(32767);
BEGIN
  IF( in_old.COUNT <> in_new.COUNT ) THEN
    RETURN in_text;
  END IF;
  v_result := in_text;
  FOR i IN 1 .. in_old.COUNT LOOP
    v_result := REPLACE( v_result, in_old(i), in_new(i) );
  END LOOP;
  RETURN v_result;
END;

#and then it will probably be use like this in pl/sql block below:

fname2 := multiple_replace(fname1,
                           NEW t_text( chr(1), chr(2), chr(3) ),
                           NEW t_text('', '', ''));

Thnks....

April 04, 2017 - 1:10 am UTC

That code looks fine, but one would hope that our original poster's requirement is a one-off requirement.

If they are regularly getting strange characters in their datafile names, then I think there are bigger issues at play :-)

It doesn't work for me.

Andre de S Araujo, May 25, 2018 - 4:35 am UTC

SQL> 
SQL> set serveroutput on
SQL> declare
  2  
  3     fname1 varchar2(100);
  4  
  5     fname2 varchar2(100);
  6  
  7  begin
  8  
  9    select name into fname1 from v$datafile where file# = 495;
 10  
 11    fname2 := replace(fname1,chr(32));
 12  
 13    dbms_output.put_line('alter database rename file '''||fname1||''' to '''||fname2||'''');
 14  
 15    execute immediate 'alter database rename file '''||fname1||''' to '''||fname2||'''';
 16  
 17  end;
 18  
 19  /
alter database rename file '/oracle/db04/belp62/applsysd63.dbf ' to '/oracle/db04/belp62/applsysd63.dbf'
declare

   fname1 varchar2(100);

   fname2 varchar2(100);

begin

  select name into fname1 from v$datafile where file# = 495;

  fname2 := replace(fname1,chr(32));

  dbms_output.put_line('alter database rename file '''||fname1||''' to '''||fname2||'''');

  execute immediate 'alter database rename file '''||fname1||''' to '''||fname2||'''';

end;
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 495 - new file '/oracle/db04/belp62/applsysd63.dbf' not found
ORA-01110: data file 495: '/oracle/db04/belp62/applsysd63.dbf '
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-06512: at line 15

May 28, 2018 - 2:09 am UTC

a) did you offline the tablespace ?
b) once offline'd you need to move the file to the new name (at the OS leve)

Then you should be able to issue the 'alter' rename commands

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

pl/sql code review for multiple replace function

It doesn't work for me.

More to Explore

Backup/Recovery

rename datafile with dummy characters after .dbf

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

pl/sql code review for multiple replace function

It doesn't work for me.

More to Explore

Backup/Recovery