Archive

Archive for January, 2010

Remove ^M characters at end of lines in vi

January 29, 2010 Leave a comment

To remove the ^M characters at the end of all lines in vi, use:

:%s/^V^M//g

The ^v is a CONTROL-V character and ^m is a CONTROL-M. When you type this, it will look like this:

:%s/^M//g

Advertisements
Categories: Oracle

Using Rlwrap for Sqlplus

January 26, 2010 Leave a comment

rpm package for SUSE compatible    (x86_64) distribution you can download here.

su –

# rpm -ivh rlwrap-0.30-suse.x86_64.rpm
# exit
echo “alias sqlplus=’rlwrap sqlplus'” >> /home/oracle/.bash_profile
. /home/oracle/.bash_profile
Categories: Linux, Oracle

spfile is created in folder DB_UNKNOWN

January 25, 2010 Leave a comment

Oracle 11g / 64Bit linux / Asm

restoring spfile using rman , the spfile is located in a wrong location

+DATA1/DB_UNKNOWN/PARAMETERFILE/SPFILE.185.634235834

Solution is explained in Metalink solution in Note: 393932.1

1. Create a pfile
   SQL> create pfile='/tmp/pfile' from spfile'+DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.185.634235834';

2.
Shutdown and startup mount using the restored spfile
   SQL> startup mount force pfile=/tmp/pfile

3. Restore
the spfile again to the correct location as DB_NAME is correctly set this time.
   RMAN> restore spfile;

4. Startup again using the spfile
   SQL> startup force

Categories: Oracle

Check Asm Disks

January 18, 2010 Leave a comment
SQL>set pages 50000 lines 120
col path for a30
select disk_number,
mount_status,
header_status,
mode_status,
state,path
from v$asm_disk ;

DISK_NUMBER MOUNT_S HEADER_STATU MODE_ST STATE    PATH
----------- ------- ------------ ------- -------- ------------------------------
0 CLOSED  CANDIDATE    ONLINE  NORMAL   /dev/raw/raw4
1 CLOSED  CANDIDATE    ONLINE  NORMAL   /dev/raw/raw3
0 CACHED  MEMBER       ONLINE  NORMAL   /dev/raw/raw2
0 CACHED  MEMBER       ONLINE  NORMAL   /dev/raw/raw1

SQL>set pages 50000 lines 120
select path,redundancy,total_mb,free_mb,failgroup
from v$asm_disk
where mount_status='CACHED' and header_status='MEMBER' and
mode_status='ONLINE' and state='NORMAL'
order by path;

PATH                     REDUNDA   TOTAL_MB    FREE_MB FAILGROUP
------------------------ ------- ---------- ---------- ------------------------------
/dev/raw/raw1            UNKNOWN       5114       3500 DATA_0000
/dev/raw/raw2            UNKNOWN       5114       4206 FLASH_0000

--check free space in ASM
asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N         512   4096  1048576      5114     3469                0            3469              0  DATA/
MOUNTED  EXTERN  N         512   4096  1048576      5114     4158                0            4158              0  FLASH/

 

 
 

-- Check ASM diskgroup built on raw devices
set pages 50000 lines 120
col path for a15
select a.path,b.NAME from v$asm_disk a, v$asm_diskgroup b
where a.GROUP_NUMBER=b.GROUP_NUMBER and
a.path like '%raw%' ;

PATH            NAME
--------------- ------------------------------
/dev/raw/raw4   FLASH
/dev/raw/raw3   DATA
/dev/raw/raw2   FLASH
/dev/raw/raw1   DATA

check how the different disks of the diskgroups are utilized
select
        instname,
        dbname,
        group_number,
        failgroup,
        disk_number,
        reads,
        writes
from v$asm_disk_iostat
order by 1,2,3,4,5,6
/
INSTNAME   DBNAME   GROUP_NUMBER FAILGROUP  DISK_NUMBER      READS     WRITES
---------- -------- ------------ ---------- ----------- ---------- ----------
TESTORAC   TESTORAC            1 DATA_0000            0      10137       3381
TESTORAC   TESTORAC            1 DATA_0001            1      10535       2960
TESTORAC   TESTORAC            2 FLASH_0000           0       7448       6174
TESTORAC   TESTORAC            2 FLASH_0001           1      20354       8223

Categories: Oracle