Oracle & Unix: Datafile naming error

A text editor formatting flaw could really end a life of a DBA in jeopardy, that is what I felt when I realized that one of the tablespaces that I created had a filename with white spaces - such a database gone live into the production is a nightmarish experience. Here is the situation, a tablespace creation script (vi editor) had a filename broken down to the next line and I thought it just came down to the next line after having reached the end of line, but it wasn't. It looked like shown below



CREATE TABLESPACE "USERS_BIG"
LOGGING
DATAFILE '/prd/u01/oradata/prod/users_big01.dbf' SIZE 2500M REUSE, 
'/prd/u01/oradata/prod/use
rs_big02.dbf' SIZE 2500M REUSE,
'/prd/u01/oradata/prod/users_big03.dbf' SIZE 5000M REUSE;

This is what it looks like from the inside, of the database and that of the Operating System.



SQL> select file_name from dba_data_files 
  2  where tablespace_name like '%BIG%';

FILE_NAME
------------------------------------------------------
/prd/u01/oradata/prod/users_big01.dbf
/prd/u01/oradata/prod/use
rs_big02.dbf

/prd/u01/oradata/prod/users_big03.dbf

UNIX:/prd/u01/oradata/prod > ls -ltr
total 184320194
-rw-r-----   1 oracle   dba      5242888192 May 22 17:12 users02.dbf
-rw-r-----   1 oracle   dba      2621448192 May 22 17:12 use
rs_big02.dbf
-rw-r-----   1 oracle   dba      5242888192 May 22 17:12 users01.dbf
-rw-r-----   1 oracle   dba      524296192 May 22 17:12 tools02.dbf
-rw-r-----   1 oracle   dba      524296192 May 22 17:12 tools01.dbf

Here are some vain efforts to locate the file individually, but nothing worked out.



UNIX:/prd/u01/oradata/prod > ls -ltri
total 184320194
        20 -rw-r-----   1 oracle   dba      5242888192 May 22 17:12 users02.dbf
        19 -rw-r-----   1 oracle   dba      5242888192 May 22 17:12 users01.dbf
        30 -rw-r-----   1 oracle   dba      2621448192 May 22 17:12 use
rs_big02.dbf
        18 -rw-r-----   1 oracle   dba      524296192 May 22 17:12 tools02.dbf
        17 -rw-r-----   1 oracle   dba      524296192 May 22 17:12 tools01.dbf

UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -print
./use
rs_big02.dbf
UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -exec ls -ltr {} \;
-rw-r-----   1 oracle   dba      2621448192 May 22 17:12 ./use
rs_big02.dbf
UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -exec ls -ltr {} \;|awk '{print $9}'
./use
rs_big02.dbf

UNIX:/prd/u01/oradata/prod > cp "users_big02.dbf" tempos.dbf
cp: cannot access users_big02.dbf

I made a copy of the broken name datafile to a corrected name datafile at the OS level and then renamed it at the database level by including the whitespaces in the source datafile argument of the 'alter database rename file' statement. The copying at the OS level required the entire white spaces to be included in the source file name argument of the 'cp' command, while being enclosed in double quotes (") as shown below.



UNIX:/prd/u01/oradata/prod > cp "use
> rs_big02.dbf" users_big02.dbf

SQL> select file_name from dba_data_files 
  2  where tablespace_name like '%BIG%';

FILE_NAME
----------------------------------------------------
/prd/u01/oradata/prod/users_big01.dbf
/prd/u01/oradata/prod/use
rs_big02.dbf

/prd/u01/oradata/prod/users_big03.dbf
/prd/u01/oradata/prod/users_big04.dbf

  
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDO                           ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
INDX                           ONLINE
PERFSTAT                       ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
USERS_BIG                      ONLINE

9 rows selected.


SQL> alter tablespace users_big offline normal;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDO                           ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
INDX                           ONLINE
PERFSTAT                       ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
USERS_BIG                      OFFLINE

9 rows selected.

SQL> !ls -ltr
total 185090258

-rw-r-----   1 oracle   dba      5242888192 May 23 04:22 users_big03.dbf
-rw-r-----   1 oracle   dba      2621448192 May 23 04:22 users_big01.dbf
-rw-r-----   1 oracle   dba      2621448192 May 23 04:22 use
rs_big02.dbf
-rw-r-----   1 oracle   dba      10485768192 May 23 04:22 undo.dbf
-rw-r-----   1 oracle   dba      209715712 May 23 04:22 redo_PROD_2a.log


SQL> select sum(bytes)/1024/1024,file_name from dba_data_files 
  2  where tablespace_name like '%USER%' 
  3  group by file_name order by file_name;

SUM(BYTES)/1024/1024 FILE_NAME
-------------------- -----------------------------------------------
                     /prd/u01/oradata/prod/use
                     rs_big02.dbf

                5000 /prd/u01/oradata/prod/users01.dbf
                5000 /prd/u01/oradata/prod/users02.dbf
                5000 /prd/u01/oradata/prod/users03.dbf
                5000 /prd/u01/oradata/prod/users04.dbf
                     /prd/u01/oradata/prod/users_big01.dbf
                     /prd/u01/oradata/prod/users_big03.dbf
                     /prd/u01/oradata/prod/users_big04.dbf

8 rows selected.


SQL> alter database rename file '/prd/u01/oradata/prod/use
  2  rs_big02.dbf' to '/prd/u01/oradata/prod/users_big02.dbf';

Database altered.


SQL> select sum(bytes)/1024/1024,file_name from dba_data_files 
  2  where tablespace_name like '%USER%'group by file_name 
  3  order by file_name;

SUM(BYTES)/1024/1024 FILE_NAME
-------------------- ------------------------------------------------
                5000 /prd/u01/oradata/prod/users01.dbf
                5000 /prd/u01/oradata/prod/users02.dbf
                5000 /prd/u01/oradata/prod/users03.dbf
                5000 /prd/u01/oradata/prod/users04.dbf
                     /prd/u01/oradata/prod/users_big01.dbf
                     /prd/u01/oradata/prod/users_big02.dbf
                     /prd/u01/oradata/prod/users_big03.dbf
                     /prd/u01/oradata/prod/users_big04.dbf

8 rows selected.


SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                CHANGE#    TIME
---------- ------- ------- ----------------- ---------- ---------
        18 OFFLINE OFFLINE OFFLINE NORMAL             0
        19 OFFLINE OFFLINE OFFLINE NORMAL             0
        20 OFFLINE OFFLINE OFFLINE NORMAL             0
        21 OFFLINE OFFLINE OFFLINE NORMAL             0

SQL> alter tablespace USERS_BIG online;

Tablespace altered.


SQL> select * from v$recover_file;

no rows selected


SQL> select sum(bytes)/1024/1024,file_name from dba_data_files 
  2  where tablespace_name like '%USER%' 
  3  group by file_name order by file_name;

SUM(BYTES)/1024/1024 FILE_NAME
-------------------- ----------------------------------------------------
                5000 /prd/u01/oradata/prod/users01.dbf
                5000 /prd/u01/oradata/prod/users02.dbf
                5000 /prd/u01/oradata/prod/users03.dbf
                5000 /prd/u01/oradata/prod/users04.dbf
                2500 /prd/u01/oradata/prod/users_big01.dbf
                2500 /prd/u01/oradata/prod/users_big02.dbf
                5000 /prd/u01/oradata/prod/users_big03.dbf
                5000 /prd/u01/oradata/prod/users_big04.dbf

8 rows selected.

Removing the file at the OS level is a tricky job, the safest is to locate the inode number of the file and use the 'inum' flag of the 'find' command to remove it with the 'exec' flag as shown below



UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -print
./use
rs_big02.dbf
UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -exec ls -ltr {} \;
-rw-r-----   1 oracle   dba      2621448192 May 23 04:22 ./use
rs_big02.dbf
UNIX:/prd/u01/oradata/prod > find ./ -inum "30" -exec rm {} \;
UNIX:/prd/u01/oradata/prod >


UNIX:/prd/u01/oradata/prod > ls -tlr
total 184320194
-rw-r-----   1 oracle   dba      1048584192 May 23 04:30 sysaux.dbf
-rw-r-----   1 oracle   dba      10485768192 May 23 04:42 undo.dbf
-rw-r-----   1 oracle   dba      5242888192 May 23 04:45 users_big04.dbf
-rw-r-----   1 oracle   dba      5242888192 May 23 04:45 users_big03.dbf
-rw-r-----   1 oracle   dba      2621448192 May 23 04:45 users_big02.dbf
-rw-r-----   1 oracle   dba      2621448192 May 23 04:45 users_big01.dbf
-rw-r-----   1 oracle   dba      9961480192 May 23 04:45 system01.dbf


SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDO                           ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
INDX                           ONLINE
PERFSTAT                       ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
USERS_BIG                      ONLINE

9 rows selected.

Oracle & Unix

Wednesday, November 4, 2009

Datafile naming error

No comments:

About Me

Blog Archive