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
This is what it looks like from the inside, of the database and that of the Operating System.
Here are some vain efforts to locate the file individually, but nothing worked out.
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.
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
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.
No comments:
Post a Comment