ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

When you run rman, clone or datapump against NFS mount points it may trigger ORA-27054 error.

Oracle recommends:

a) The mount table (eg; /etc/mnttab) can be read to check the mount options

b) The NFS mount is mounted with the "hard" option

c) The mount options include rsize>=32768 and wsize>=32768

d) For RAC environments, where NFS disks are supported, the "noac" mount option is used.

But actually we do NOT need hard option, the workaround is using following mount options. For example, when you just mount disk from production to development. We don't want anything wrong then cause production disk hangs.

rw,forcedirectio,nointr,proto=tcp,suid,rsize=32768,wsize=32768,noac

How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?

--How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? [ID 336014.1]

-- locate DataPump master tables:

set linesize 110

col "OWNER.OBJECT" for a32

SELECT o.status, o.object_id, o.object_type,

o.owner||'.'||object_name "OWNER.OBJECT"

FROM dba_objects o, dba_datapump_jobs j

WHERE o.owner=j.owner_name AND o.object_name=j.job_name ;

select * from DBA_DATAPUMP_JOBS

--drop the tables

drop table SYS.SYS_IMPORT_FULL_02

Disable IPv6 on RedHat Enterprise RHEL 5.7

source: http://www.jules.fm/Logbook/files/rhel6_disable_ipv6.html

Disable IPv6 on RedHat Enterprise RHEL 6

Add a new file /etc/modprobe.d/ipv6.conf containing

alias net-pf-10 off

alias ipv6 off

Edit /etc/sysconfig/network and add a line saying

NETWORKING_IPV6=off

Then do

chkconfig ip6tables off

then reboot.

Install Oralce 11gr2 Grid on Virtual Box

This document only focus on main steps and gotchas.

Part I install VM

1. Download and install VirtualBox. You can download from oracle site or http://dlc.sun.com.edgesuite.net/virtualbox/4.1.4/VirtualBox-4.1.4-74291-Win.exe

2. When install choose customized folder such as D, which has space for your disks. This will eliminate a lot click when choosing folder later.

3. First just create one virtual machine and install Linux, clone it later once first node1 configuration is read.

4. Add three network adapters. one choosing bridged, the other two can choose virtual machine only or bridged. The first one will comunicate with your other pcs.

5. add at least three disk for ASM. Choose SCSI controler. After disk is added, modify disk type choose multi-path mount.

6. uname -a

Linux node1.localdomain 2.6.32-100.34.1.el6uek.x86_64 #1 SMP Wed May 25 17:46:45 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

[root@node1 app]# cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.1 (Santiago)

Part II prepare node1

1. download Oracle Linux 6.1 from http://epd-akam-intl.oracle.com/aaruna04/vol/patch28/EPD/V26568-01.zip?AuthParam=1317761330_bf1d83b0979d4275357db54d1a672c74&FilePath=/aaruna04/vol/patch28/EPD/V26568-01.zip&File=V26568-01.zip&params=NndQNG1mQzc3c01CeitnMlZHRGdCUTphcnU9MTM3ODk3NjYmZW1haWw9cm9iaW4uc29uZ0BlZG1vbnRvbi5jYSZmaWxlX2lkPTQwNzc5NzY4JnBhdGNoX2ZpbGU9VjI2NTY4LTAxLnppcCZ1c2VyaWQ9ZXBkLXJvYmluLnNvbmdAZWRtb250b24uY2Emc2l6ZT0zNDM2NzkyNTExJmNvbnRleHQ9QUAxNStIQGFhcnUyMDEub3JhY2xlLmNvbStQQDEzODE3NDk2JmRvd25sb2FkX2lkPTk2NDQxNDY@

2. Install the Linux.

Customize the package during installation. Remember to choose Destop package else you won't have X-server installed.

3. after install

a. change /etc/inittab to runlevel 3.

b. configure network adapters

eth0 use DHCP

eth1 use static 192.168.10.1

eth2 use static 192.168.20.11

c. edit /etc/hosts

127.0.0.1 localhost node1 localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost node1 localhost.localdomain localhost6 localhost6.localdomain6

192.168.10.1 node1 node1.localdomain

192.168.20.1 node1-vip node1-vip.localdomain

192.168.10.2 node2 node2.localdomain

192.168.20.2 node2-vip node2-vip.localdomain

192.168.10.254 ORA-RAC1 ORA-RAC1.localdomain

d. shared libiary

cd /lib64

cp /lib64/libcap.so.2 /lib64/libcap.so.1

e. format disk

such as /dev/sda1, /dev/sdb1, /dev/sdc1

g. prepare users and groups. commands( useradd, groupadd)

oinstall:x:1000:

asmadmin:x:1001:grid

dba:x:1002:grid,oracle

asmdba:x:1003:oracle

user:

grid:x:1000:1000::/home/grid:/bin/bash

oracle:x:1001:1000::/home/oracle:/bin/bash

f. change owner of disk

add lines in /etc/rc.local

chown grid:asmadmin /dev/sda1

chown grid:asmadmin /dev/sdb1

chown grid:asmadmin /dev/sdc1

h. disable ipv6

Disable IPv6 on RedHat Enterprise RHEL 6

Add a new file /etc/modprobe.d/ipv6.conf containing

alias net-pf-10 off

alias ipv6 off

Edit /etc/sysconfig/network and add a line saying

NETWORKING_IPV6=off

Then do

chkconfig ip6tables off

then reboot.

PART III clone VM

1. from virtual box menu select clone machine. Name new machine node2.

vi /etc/sysconfig/network

2. start node.

a. change hostname

hostname node2

b. vi /etc/hosts and change localhost line to node2.

cat /etc/hosts

127.0.0.1 localhost node2 localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost node2 localhost.localdomain localhost6 localhost6.localdomain6

192.168.10.1 node1 node1.localdomain

192.168.20.1 node1-vip node1-vip.localdomain

192.168.10.2 node2 node2.localdomain

192.168.20.2 node2-vip node2-vip.localdomain

192.168.10.254 ORA-RAC1 ORA-RAC1.localdomain

c. change network adapter name. comment out node1 enthr and rename network adapter to 0,1,2

[root@node2 app]# cat /etc/udev/rules.d/70-persistent-net.rules

# PCI device 0x8086:0x100e (e1000)

SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="08:00:27:4d:41:de", ATTR{dev_id}=="0x0", ATTR{type}=="1", KERNEL=="eth*", NAME="eth0"

# PCI device 0x8086:0x100e (e1000)

SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="08:00:27:33:4a:2b", ATTR{dev_id}=="0x0", ATTR{type}=="1", KERNEL=="eth*", NAME="eth1"

# PCI device 0x8086:0x100e (e1000)

SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="08:00:27:a0:09:d6", ATTR{dev_id}=="0x0", ATTR{type}=="1", KERNEL=="eth*", NAME="eth2"

3. reboot then reconfig the IPs.

ifconfig eth1 192.168.10.12

ifconfig eth2 192.168.20.12

PART IV prepare install Grid

1. setup ssh authentication

a. login node1 as grid

ssh-keygen

ssh-copyid node1 grid@node1

ssh-copyid node2 grid@node1

b. login node2 as grid and repeat step a.

2. start install Grid

TIPS from Oracle ASM disk owner

issue: Linux disk owner can not be changed simply such as chown oracle:oinstall /dev/sdb1

shortcut:

[root@node1 ~]# cat /etc/udev/rules.d/52-oracle.permissions.rules

# OCR disks

KERNEL=="sda1",OWNER="grid" GROUP="asmdba",MODE="0660"

# UNDO disks

KERNEL=="sdb1",OWNER="grid" GROUP="asmdba",MODE="0660"

# DATA disks

KERNEL=="sdc1",OWNER="grid" GROUP="asmdba",MODE="0660"

from article:

http://download.oracle.com/docs/cd/E11882_01/install.112/e22489/storage.htm#CDEBFDEH

By default, the 2.6 kernel device file naming scheme udev dynamically creates device file names when the server is started, and assigns ownership of them to root. If udev applies default settings, then it changes device file names and owners for voting disks or Oracle Cluster Registry partitions, corrupting them when the server is restarted. For example, a voting disk on a device named /dev/sdd owned by the user grid may be on a device named /dev/sdf owned by root after restarting the server.If you use ASMLIB, then you do not need to ensure permissions and device path persistency in udev.

If you do not use ASMLIB, then you must create a custom rules file. When udev is started, it sequentially carries out rules (configuration directives) defined in rules files. These files are in the path /etc/udev/rules.d/. Rules files are read in lexical order. For example, rules in the file 10-wacom.rules are parsed and carried out before rules in the rules file 90-ib.rules.

When specifying the device information in the UDEV rules file, ensure that the OWNER, GROUP and MODE are specified before any other characteristics in the order shown. For example, if you want to include the characteristic ACTION on the UDEV line, then specify ACTION after OWNER, GROUP, and MODE.

Where rules files describe the same devices, on the supported Linux kernel versions, the last file read is the one that is applied.

To configure a permissions file for disk devices, complete the following tasks:

1. To obtain information about existing block devices, run the command scsi_id (/sbin/scsi_id) on storage devices from one cluster node to obtain their unique device identifiers. When running the scsi_id command with the -s argument, the device path and name passed should be that relative to the sysfs directory /sys (for example, /block/device) when referring to /sys/block/device. For example:

   # /sbin/scsi_id -g -s /block/sdb/sdb1 360a98000686f6959684a453333524174   # /sbin/scsi_id -g -s /block/sde/sde1 360a98000686f6959684a453333524179 

   Record the unique SCSI identifiers of clusterware devices, so you can provide them when required.

   Note:

   The command scsi_id should return the same device identifier value for a given device, regardless of which node the command is run from.

2. Configure SCSI devices as trusted devices (white listed), by editing the /etc/scsi_id.config file and adding options=-g to the file. For example:

   # cat > /etc/scsi_id.config vendor="ATA",options=-p 0x80 options=-g 

3. Using a text editor, create a UDEV rules file for the Oracle ASM devices, setting permissions to 0660 for the installation owner and the group whose members are administrators of the Oracle Grid Infrastructure software. For example, using the installation owner grid and using a role-based group configuration, with the OSASM group asmadmin:

   # vi /etc/udev/rules.d/99-oracle-asmdevices.rules  KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id", RESULT=="14f70656e66696c00000000", OWNER="grid", GROUP="asmadmin", MODE="0660" KERNEL=="sd?2", BUS=="scsi", PROGRAM=="/sbin/scsi_id", RESULT=="14f70656e66696c00000001", OWNER="grid", GROUP="asmadmin", MODE="0660" KERNEL=="sd?3", BUS=="scsi", PROGRAM=="/sbin/scsi_id", RESULT=="14f70656e66696c00000002", OWNER="grid", GROUP="asmadmin", MODE="0660" 

4. Copy the rules.d file to all other nodes on the cluster. For example:

   # scp 99-oracle-asmdevices.rules root@node2:/etc/udev/rules.d/99-oracle-asmdevices.rules 

5. Load updated block device partition tables on all member nodes of the cluster, using /sbin/partprobe devicename. You must do this as root.

6. Run the command udevtest (/sbin/udevtest) to test the UDEV rules configuration you have created. The output should indicate that the block devices are available and the rules are applied as expected. For example:

   # udevtest /block/sdb/sdb1 main: looking at device '/block/sdb/sdb1' from subsystem 'block' udev_rules_get_name: add symlink 'disk/by-id/scsi-360a98000686f6959684a453333524174-part1' udev_rules_get_name: add symlink 'disk/by-path/ip-192.168.1.1:3260-iscsi-iqn.1992-08.com.netapp:sn.887085-part1' udev_node_mknod: preserve file '/dev/.tmp-8-17', because it has correct dev_t run_program: '/lib/udev/vol_id --export /dev/.tmp-8-17' run_program: '/lib/udev/vol_id' returned with status 4 run_program: '/sbin/scsi_id' run_program: '/sbin/scsi_id' (stdout) '360a98000686f6959684a453333524174' run_program: '/sbin/scsi_id' returned with status 0 udev_rules_get_name: rule applied, 'sdb1' becomes 'ocr1' udev_device_event: device '/block/sdb/sdb1' validate currently present symlinks udev_node_add: creating device node '/dev/ocr1', major = '8', minor = '17',  mode = '0640', uid = '0', gid = '500' udev_node_add: creating symlink '/dev/disk/by-id/scsi-360a98000686f6959684a453333524174-part1' to '../../ocr1' udev_node_add: creating symlink '/dev/disk/by-path/ip-192.168.1.1:3260-iscsi-iqn.1992-08.com.netapp:sn.84187085 -part1' to '../../ocr1' main: run: 'socket:/org/kernel/udev/monitor' main: run: '/lib/udev/udev_run_devd' main: run: 'socket:/org/freedesktop/hal/udev_event' main: run: '/sbin/pam_console_apply /dev/ocr1 /dev/disk/by-id/scsi-360a98000686f6959684a453333524174-part1 /dev/disk/by-path/ip-192.168.1.1:3260-iscsi-iqn.1992-08.com.netapp:sn.84187085- part1' 

   In the example output, note that applying the rules renames OCR device /dev/sdb1 to /dev/ocr1.

7. Enter the command to restart the UDEV service.

   On Asianux, Oracle Linux 5, and RHEL5, the commands are:

   # /sbin/udevcontrol reload_rules # /sbin/start_udev 

   On SUSE 10 and 11, the command is:

   # /etc/init.d boot.udev restart

Script show Solaris system information

"showsystem" 45 lines, 1448 characters

#!/bin/bash

banner=`uname -a`

kernelid=`uname -v`

machinemodel=`prtdiag -v | head -1 | sed 's/System Configuration: Sun Microsystems //'`

raminfo=`prtdiag -v | grep "Memory size"`

echo $banner

echo

echo "Kernel:$kernelid "

echo "Model:$machinemodel "

echo $raminfo

echo

nproc=`(/usr/bin/kstat -m cpu_info | grep chip_id | sort -u | wc -l | tr -d ' ')`

vproc=`(/usr/bin/kstat -m cpu_info | grep 'module: cpu_info' | sort -u | wc -l | tr -d ' ')`

#ncore=`(/usr/bin/kstat -m cpu_info | grep core_id | sort -u | wc -l | tr -d ' ')`

ncore=`(/usr/bin/kstat -m cpu_info | grep core_id | awk '{ print $2 }'| sort -u | wc -l | tr -d ' ')` #thanks to James Ervin#

nstrandspercore=$(($vproc/$ncore))

ncoresperproc=$(($ncore/$nproc))

echo "Total number of physical processors: $nproc"

echo "Number of virtual processors: $vproc"

echo "Total number of cores: $ncore"

echo "Number of cores per physical processor: $ncoresperproc"

echo "Number of hardware threads (strands or vCPUs) per core: $nstrandspercore"

# now derive the vcpu-to-core mapping based on above information #

echo -e "\n** Socket-Core-vCPU mapping **"

vpid=0

for ((i = 1; i <= ${nproc}; ++i ))

do

echo -e "\nPhysical Processor $i:"

for ((j = 1; j <= ${ncoresperproc}; ++j ))

do

echo -e "\tCore $j:"

echo -e "\t\tvCPU ids: $vpid-$(($vpid+$nstrandspercore-1))"

vpid=$(($vpid+$nstrandspercore))

done

done

setup mail function in 11g use new ACL

This summary is not available. Please click here to view the post.

SQL Server get all tables size

key function sp_spaceused @TableName

code:

drop table #TempTable;

CREATE TABLE #TempTable

(

tableName varchar(100),

numberofRows varchar(100),

reservedSize varchar(50),

dataSize varchar(50),

indexSize varchar(50),

unusedSize varchar(50)

);

go

DECLARE tableCursor CURSOR

FOR

select [name]

from dbo.sysobjects

where OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY;

declare @tablename varchar(1000)

OPEN tableCursor

--Get the first table name from the cursor

FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

--Dump the results of the sp_spaceused query to the temp table

insert #TempTable

EXEC sp_spaceused @TableName

--Get the next table name

FETCH NEXT FROM tableCursor INTO @TableName

END

close tableCursor;

go

select * from #TempTable order by convert(int,substring(datasize,1,charindex('KB',datasize) -1)) desc

ORA-01548 active rollback segment , Unable drop undo tablespace

When undo get a huge rollback segment it may causing a lot trouble to shutdown and start. Oracle seems to check all active undo and do recovery. But sometime the rollback segment was bad.

To clean this up

1. find out active rollback segment. Either by SQL or try to drop undo tablespace.
   select segment_name, status from dba_rollback_segs where tablespace_name='undotbs_corrupt' and status = ‘NEEDS RECOVERY’;
2. shutdown database. It may take pretty long time to shutdown.
3. change parameter
   *.undo_management='MANUAL'
   *._offline_rollback_segments=_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$)
5. bring up database
6. Drop the UNDO tablespace.
7. Recreate UNDO tablespace.
8. shutdown database
9. change parameter back to original
10. bring up database

The key part is change undocumented parameter _offline_rollback_segments, this way it will allow you to drop active rollback segment.

ref: http://davidalejomarcos.wordpress.com/2010/11/09/ora-01548-active-rollback-segment-_syssmu1_xxxx-found-terminate-dropping-tablespace-while-deleting-undotbs/

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

When database are in mount mode or restricted session enabled you'll get this error since 10g.

There are two way to get around it.

1. Put UR=A in your tnsnames.ora for the entry. e.g.
   

   STBY=
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SERVICE_NAME = STBY)
        (UR = A)
      )
    )
   

   
   
2. Create static listener to allow listener know which oracle SID to connect. e.g.
   SID_LIST_LISTENER =
   (SID_LIST =
   (SID_DESC =
   (GLOBAL_DBNAME = TESTDB.gov.edmonton.ab.ca)
   (ORACLE_HOME = /u01/app/ora10g/product/10.2.0.4/db_1)
   (SID_NAME = TESTDB1)
   )
   )
   After you change listener.ora you need reload listener to take effect.

Fix ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

1. shutdown database
2. change parameter use manual management of undo, else you can't open database
undo_management=manual
3. startup database
4. recreate undo tablespace
drop tablespace undotbs1 including contents and datafiles;
create UNDO TABLESPACE "UNDOTBS1" DATAFILE 'F:\ORACLE\ORADATA\ORACLE_SID\UNDOTBS1.ORA' SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
5. restart database
6. check alert log. done.

Key step of setup APEX on 11G RAC

--Editing the dads.conf File
--/u01/app/ora10g/oms10g/Apache/modplsql/conf/dads.conf
--add the following lines to file dads.conf
Alias /i/ "/u01/app/ora10g/oms10g/Apache/images/"
AddType text/xml xbl
AddType text/x-component htc


Order deny,allow
PlsqlDocumentPath docs
AllowOverride None
PlsqlDocumentProcedure wwv_flow_file_mgr.process_download
PlsqlDatabaseConnectString (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sss-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=xxx-vip)(PORT=1521))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DBSERVICENAME))) TNSFormat
PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword apexpassword
PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize
Allow from all



/*
Beaware no space in the value field and TNSFormat is ritht setting instead of TNSNamesFormat
[Mon Jun 22 16:15:51 2009] [error] mod_plsql: '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oak-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=olive-vip)(PORT=1521
))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=OEMT))) TNSNamesFormat' is not allowed for parameter 'PlsqlDatabaseConnectString' in DAD '/p
ls/apex' (Should be one of [SIDFormat], [ServiceNameFormat], [TNSFormat], [NetServiceNameFormat], or blank for backward compatibility format)
*/

steps needed for Xming

if you get the following error , that means you need add server name to x0.host or xN.host.

Xlib: connection to "162.106.51.66:0.0" refused by server
Xlib: No protocol specified


the file is located in the binary folder with xming. Just add your server name or IP in it.
also you can limit users.
eg.

servera
servera:oracle

reference: http://www.windowsreference.com/free-utilities/xming-free-x-windows-server-for-windows-2000xpvista/

create a oracle user authenticated by OS

1. make sure parameter OS_AUTHENT_PREFIX = "os_" is set.

2. create user in oracle database.

CREATE USER os_oracle IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
;

GRANT dba TO oracle;

exit;

3. make sure the user oracle is in OS dba group.

Putty settings

On windows it is stored in registry.

to tranfer your setting to another machine you can export the setting and import the setting.

to export:
regedit /e putty.reg HKEY_CURRENT_USER\Software\SimonTatham\PuTTY

To import settings:
regedit /s putty.reg


I have run into a problem that the windows title is set and it then become fixed always show the title in the setting. To let dynamic title with server name back you need clear the the setting under windows behaviour. Or delete windowtitle from registry.