Tuesday, July 31, 2012

Problematic erase key in Oracle datafile running on unix

In Unix like shell command line, to erase a letter before current cursor, ^H (CTRL+H) or ^? (BACKSPACE) you'd like to use?

1. Introduction

"stty -a" helps to see current keyboard mapping the BACKSPACE key.

dev07:B.11:TESTDB:/software/oracle> stty -a                            
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = DEL; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3
dev07:B.11:TESTDB:/software/oracle> stty erase (here pressed CTRL+h)



dev07:B.11:TESTDB:/software/oracle> stty -a
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = ^H; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3



dev07:B.11:TESTDB:/software/oracle> lss^?    #attemp to use Backspace to delete extra "s" , instead shows as ^?
ksh: lss^?:  not found
dev07:B.11:TESTDB:/software/oracle> lss (followd by pressed CTRL+h)
10g                adhoc              jre                oradata
References:

2. Create a problem file

dev07:B.11:TESTDB:/software/oracle> touch test1^?.txt   #pressed CTRL+h after "1"
dev07:B.11:TESTDB:/software/oracle> ls -l test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt  #^? is invisible to "ls -l" command

dev07:B.11:TESTDB:/software/oracle> ls -ll test1^?.txt    # press escape key after "1", can auto-complete the filename, but still invisible
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt

dev07:B.11:TESTDB:/software/oracle> ls -lb test1^?.txt    #use "b" option to see its octal number is 177, which is DEL in ASCII code table
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1\177.txt

--now play with ^H to create another problematic file

dev07:B.11:TESTDB:/software/oracle> stty -a
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = DEL; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3

dev07:B.11:TESTDB:/software/oracle> touch test.txt   #type test2^H.txt
dev07:B.11:TESTDB:/software/oracle> ls -lb test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1\177.txt  #177 in octal in ASCII is "DEL"
-rw-r--r--   1 oracle     dba              0 Jul 31 17:40 test2\010.txt   #010 in octal in ASCII is "backspace"
dev07:B.11:TESTDB:/software/oracle>
ksh: ^H^H^H^H^H^H^H^H:  not found
dev07:B.11:TESTDB:/software/oracle> ll test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:40 test.txt





3. Make it more troublesome in oracle datafile now.

3.1 first set stty erase as ^H
SQL>  create tablespace testbs datafile '/oracle/UAT/sapdata1/system_1/test1.dbf' size 20m;

Tablespace created.


SQL> alter tablespace testbs  add datafile '/oracle/UAT/sapdata1/system_1/test1^?2.dbf' size 20m;

Tablespace altered.
--  type test1+(Backspace key)+2.dbf in order to create the problem.
SQL> select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/oracle/UAT/sapdata1/system_1/test1.dbf
        74 TESTBS                           20971520       2560 AVAILABLE
          74 NO           0          0            0   20905984        2552
ONLINE

/oracle/UAT/sapdata1/system_1/test1^?2.dbf
        75 TESTBS                           20971520       2560 AVAILABLE
          75 NO           0          0            0   20905984        2552
ONLINE
At OS, it shows :
m1sapuat:orauat 9> ls -lb
total 81952
-rw-r-----   1 orauat     dba        20979712 Jul 30 16:45 test1.dbf
-rw-r-----   1 orauat     dba        20979712 Jul 30 18:42 test1\1772.dbf

3.2 Next, we need to rename it correctly to avoid other impact may arise.

# for database running in ARCHIVELOG mode
alter database datafile 75 offline

 SQL> alter database datafile 75 offline;   
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


  # this testing database runs in NOARCHIVELOG mode.

SQL> alter database datafile 75 offline drop;

Database altered.
-- type the exactly datafile name : test1+(Backspace key)+2.dbf in order
SQL>  alter database rename file  '/oracle/UAT/sapdata1/system_1/test1^?2.dbf' to '/oracle/UAT/sapdata1/system_1/test2.dbf';

Database altered.

-- at OS level, back up the datafile and do the same way to rename it to test2.dbf

-- now recover and online it back

SQL> recover datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';
Media recovery complete.
SQL>  alter database datafile  '/oracle/UAT/sapdata1/system_1/test2.dbf' online;

Database altered.

SQL>  select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/oracle/UAT/sapdata1/system_1/test1.dbf
        74 TESTBS                           20971520       2560 AVAILABLE
          74 NO           0          0            0   20905984        2552
ONLINE

/oracle/UAT/sapdata1/system_1/test2.dbf
        75 TESTBS                           20971520       2560 AVAILABLE
          75 NO           0          0            0   20905984        2552
ONLINE


SQL> select * from v$recover_file;

no rows selected

-- Note that this RENAME approach is preferred than drop the datafile , as drop datafile requires it is a empty file.


References:

Setting terminal characteristics with stty

Unix Tip: Using stty to Your Advantage

ASCII Table and Description

How to Drop a Datafile from a Tablespace

 

 

No comments:

Post a Comment