Tuesday, October 18, 2011

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

Thursday, October 6, 2011

Disable IPv6 on RedHat Enterprise RHEL 5.7


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