Verification or Oracle Asynchronous I/O

Oracle has a lot of post mentioning about asynchronous disk I/O setup for storage in both UNIX kernel, and Oracle binary. Following is a build in database view which shows what database components are using it.

This can be used to verify async I/O is configured and in use. This approach is different then just checking kernel configuration, and Oracle init.ora or spfile.ora parameter

SQL> select file_no, filetype_name, small_read_megabytes, small_read_reqs, large_read_megabytes, large_read_reqs, asynch_io from v$iostat_file

   FILE_NO FILETYPE_NAME                SMALL_READ_MEGABYTES SMALL_READ_REQS LARGE_READ_MEGABYTES LARGE_READ_REQS ASYNCH_IO

---------- ---------------------------- -------------------- --------------- -------------------- --------------- ---------

         0 Other                                           1             694                    0               0 ASYNC_OFF

         0 Control File                                  612           39156                   20              24 ASYNC_OFF

         0 Log File                                        0               3                    0               0 ASYNC_OFF

         0 Archive Log                                     0               0                    0               0 ASYNC_OFF

         0 Data File Backup                                0               0                    0               0 ASYNC_OFF

         0 Data File Incremental Backup                    0               0                    0               0 ASYNC_OFF

         0 Archive Log Backup                              0               0                    0               0 ASYNC_OFF

         0 Data File Copy                                  0               0                    0               0 ASYNC_OFF

         0 Flashback Log                                   2             146                   12               6 ASYNC_OFF

         0 Data Pump Dump File                             0               0                    0               0 ASYNC_OFF

         1 Data File                                    3645          463518                 7879            7880 ASYNC_ON

         1 Temp File                                       0               1                    0               0 ASYNC_ON

         2 Data File                                    2934          374908                 6261            6261 ASYNC_ON

         3 Data File                                    1300          166347                 2771            2771 ASYNC_ON

         4 Data File                                       4             179                    0               0 ASYNC_ON

         5 Data File                                     977          124142                 2009            2009 ASYNC_ON

         6 Data File                                    1727          216201                 3468            3468 ASYNC_ON

         7 Data File                                     322           41237                  697             697 ASYNC_ON

         8 Data File                                       4             549                    0               0 ASYNC_ON

19 rows selected.

Display UNIX Date with Timezone

Following is a trick to display UNIX date in any timezone without permanently changing TZ variable.

$ TZ=EST5EDT date "+%F %T %z"
2010-01-14 16:49:17 -0500

This is what it does is similar to below
$ TZ2=$TZ
$ TZ=EST5EDT
$ date "+%F %T %z"
$ TZ=$TZ2
$ unset TZ2

It won't change the current value stored in TZ as well
$ date
Thu Jan 14 21:50:02 GMT 2010
$ export TZ=CST6CET
$ date
Thu Jan 14 15:50:39 CST 2010
$ TZ=EST5EDT date "+%F %T %z"
2010-01-14 16:50:47 -0500
$ echo $TZ
CST6CET
$ date
Thu Jan 14 15:50:53 CST 2010

sed to remove word

Assume I have a string of "(PORT=12)"

Use sed to remove the number

bash-2.05$ echo "(PORT=12)" | sed "s/\(PORT=\)\([0-9]*\)/\1/"
(PORT=)

Use sed to remove the word, and keep the number

bash-2.05$ echo "(PORT=12)" | sed "s/\(PORT=\)\([0-9]*\)/\2/"
(12)

vi Macro Sample 1 for Oracle

Product: Oracle database, UNIX vi editor

Tips for Oracle in UNIX or Linux environment. This will map F4 function key to perform following so that it helps DBA to edit the SQL script
1. Insert a blank line before each of the ALTER TABLE statement

This is helpful when you get a SQL from someone, or imp utility, which you need to find the position of ALTER TABLE fast while scrolling down the page

Following vi macro will search for ALTER TABLE, and insert a blank line before it.  Then it move the cursor down 2 lines
:map <F4> /ALTER TABLE^V<ESC>O^V<ESC>jj

Linux getent command

In Linux, getting information from /etc/passwd, /etc/group, /etc/hosts, etc are being replaced with /usr/bin/getent command. This command support NIS and LDAP environment where account information and group are setup in remote servers.

Common use commands:
getent passwd - Get all accounts setup. Replace /etc/passwd
getent group - Get all group. Repalce /etc/group
getent hosts - Get all hostname. Similar to /etc/hosts
getent alias - Get user name alias
getent ethers - Get ethernet
getent protocols - /etc/protocols

Syncing thousands of files across multiple destination in UNIX

Product: UNIX, Linux

UNIX file system, regardless of which type, most of them do not have self defrag feature, or it is an extra license plus configuration (re-format, re-mount) to use it. This post indicated a free and relatively quick way to defrag, and improve the file system respond time

Some applications that created 100,000 of files size 4 GB, and needs to move out to its dedicated mount point.  "ls" command alone takes 3 min to return, and tar-untar takes 45 min to complete.

The challenge is how to complete the migration within 30 min of downtime.

Assumption, there are files that are old.  Time stamp can be use to indicate whether time is changed

Solution

1. Copy current files to new destination: (cd /dir2; tar cvf - /dir1 | tar xvf -)
2. Find out files changes in last half day, if supported, or 1 day and save it. This will reduce time require to copy new files /tmp/newfile.txt: find /dir1 -mtime -0.5 (only in Linux)
3. There are 2 approaches to sync up new files now
4. Option 1: use "for F in $(cat /tmp/newfile.txt); do G=$(basename $F); if [ ! -f /dir2/$G ]; then cp $F /dir2; fi; done
5. Option 2: Use rsync and supply /tmp/newfile.txt to sync between /dir1 and /dir2

This approach can optimize file system which gives a slow respond time when executing "ls" command. It is near impossible, or very time consuming to identify it is a fragmented inode (technical word). It could applies to database, CRM, Genesys log, Crystal Reports output directory, any html location, web servers with many scripts or logs, etc