debug opatch xml curroption

##

##debug opatch xml curroption

##

#export OPATCH_DEBUG=TRUE

oak 11GDB ora11g $ opatch lsinventory

Invoking OPatch 11.1.0.6.2

Oracle Interim Patch Installer version 11.1.0.6.2

Copyright (c) 2007, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/ora11g/product/11.1.0/db_1

Central Inventory : /u01/app/oraInventory

from : /var/opt/oracle/oraInst.loc

OPatch version : 11.1.0.6.2

OUI version : 11.1.0.7.0

OUI location : /u01/app/ora11g/product/11.1.0/db_1/oui

Log file location : /u01/app/ora11g/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2011-12-29_14-12-22PM.log

org.xml.sax.SAXParseException: : XML-20108: (Fatal Error) Start of root element expected.

at oracle.xml.parser.v2.XMLError.flushErrorHandler(XMLError.java:422)

at oracle.xml.parser.v2.XMLError.flushErrors1(XMLError.java:287)

at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:345)

remove oracle homes from /u01/app/oraInventory/ContentsXML/inventory.xml to find out which home cause the issue.

#One of the homes /u01/app/ora11g/product/11.1.0/db_1/inventory/ContentsXML/oraclehomeproperties.xml files corrupted

#recover the file from backup if you have one. If not, run oinstaller to fix it.

11R2 grid infrastructure gotcha

Version: Oracle Clusterware active version on the cluster is [11.2.0.1.0]

Issues with 11R2 itself

1. ASM diskgroup shows offline after reboot.

SQL> ALTER DISKGROUP ALL ENABLE VOLUME ALL /* asm agent */

SUCCESS: ALTER DISKGROUP ALL ENABLE VOLUME ALL /* asm agent */

WARNING: failed to online diskgroup resource ora.DATADG.dg (unable to communicate with CRSD/OHASD)

WARNING: failed to online diskgroup resource ora.RECOVERYDG.dg (unable to communicate with CRSD/OHASD)

#check status

$GRID_HOME/bin/srvctl status diskgroup -g diskgroupname -a

#To start or sync diskgroup resource:

$GRID_HOME/bin/srvctl start diskgroup -g diskgroupname

upgrade gotchas

1. try to use same user as old version RAC, do NOT try use different user. The upgrade won't success, and have to do a lot troubleshooting to bring RAC to 11R2.
2. If you did use different users, the following are things you need look into and change.
   a. rootUpgrade.sh will fail, then you have to deconfigure it ($GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force)
   b. restore all files belong to old RAC (Restore original /etc/init.d/initcss initcrs

   Restore /var/opt/oracle/

   Restore old version crshome

   c. run rootUpgrade.sh again
   d. this will upgrade your RAC to 11R2, but there are more things need be fixed.
   e. fix ASM by remove asm (srvctl remove asm) you need remove all database and related service first. add asm back(srvctl add asm), then add database and related service back. REF:

   ASMCA Fails When Upgrading to 11.2 due to Different ASM/CRS User or Non-default ASM Name [ID 1113073.1]

   
   f.fix nodeapps (mainly because the ownership not right) srvctl stat res -p > res.log
   ## workaround for nodeapps

cd $grid_home

find . -name log | xargs chmod 775

chmod g+w /u01/app/11.2.0/grid/opmn/logs/

chmod g+w /u01/app/11.2.0/grid/eons/init

chmod -R g+w /u01/app/11.2.0/grid/opmn/conf/

chmod g+w /u01/app/11.2.0/grid/network/admin

chmod g+w /u01/app/11.2.0/grid/network/admin/*

chmod -R g+w /u01/app/11.2.0/grid/ons/

h. fix TNS_ADMIN create a softlink from $GRID_HOME/network/admin to /var/opt/oracle

tar command to copy files to avoid system cache issue

This is the tar command used to copy directories:

cd fromdir; tar cf - .| (cd todir; tar xvf -)

showsystem in solaris

#!/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#

speedinmhz=`(/usr/bin/kstat -m cpu_info | grep clock_MHz | awk '{ print $2 }' | sort -u)`

speedinghz=`echo "scale=2; $speedinmhz/1000" | bc`

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"

echo "Processor speed: $speedinmhz MHz ($speedinghz GHz)"

# 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

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