Michael Dinh
RAC KISS on VirtualBox
Practice makes perfect, but it does not have to be difficult. Please don’t short yourself by not trying to understand what is going on.
This is my third RAC install and I got tired of doing it the hard manual way; hence, decided to script what I can.
ASM will still need to be configured after rackiss:
oracleasm configure -i oracleasm init oracleasm createdisk asm01 /dev/sdb1 ... oracleasm scandisks oracleasm listdisks
Tested Configuration: Linux rac01.localdomain 2.6.39-200.24.1.el6uek.x86_64 using V33411-01.iso on Virtual Box 4.2.12r84980
Modify script based on network configuration for the following:
/etc/resolv.conf /etc/hosts /etc/dnsentries.txt
CUSTOMIZE MINIMAL INSTALL
Base System > Base > Client Management Tools Desktops > Desktop > Desktop Platform > Graphical Administration Tools > X Window System Applications > Internet Browser 740 packages NTP not configured KDUMP not enabled
Using GUI from VirtualBox Guest OS
Disaable Software Updates: System > Preferences > System > Software Updates Install Guest Additions Devices > Install Guest Additions (ISO)
SCRIPT: rackiss.sh
#/bin/sh
{
echo -e "\n******** Disable packagekit refresh ********"
sed -i 's/enabled=1/enabled=0/g' /etc/yum/pluginconf.d/refresh-packagekit.conf
grep enabled /etc/yum/pluginconf.d/refresh-packagekit.conf
echo -e "\n******** Disable avahi-daemon 1501093.1 ********"
/etc/init.d/avahi-daemon stop
chkconfig avahi-daemon off
chkconfig avahi-daemon --list
echo -e "\n******** Silent Install oracle-rdbms-server-11gR2-preinstall ********"
yum install oracle-rdbms-server-11gR2-preinstall -q -y
echo -e "\n********* Silent Install dnsmaq, oracleasm, kernel *********"
yum install dnsmasq oracleasm-support oracleasmlib kernel-uek-devel-2.6.39-200.24.1.el6uek.x86_64 -q -y
echo -e "\n******** Download oracleasmlib ********"
wget http://download.oracle.com/otn_software/asmlib/oracleasmlib-2.0.4-1.el6.x86_64.rpm
echo -e "\n******** Install oracleasmlib ********"
yum localinstall oracleasmlib-2.0.4-1.el6.x86_64.rpm -y
echo -e "\n******** Verify yum install ********"
yum install oracle-rdbms-server-11gR2-preinstall -q -y
yum install dnsmasq oracleasm-support oracleasmlib kernel-uek-devel-2.6.39-200.24.1.el6uek.x86_64 -q -y
yum list install oracleasmlib-2.0.4-1.el6.x86_64
echo -e "\n******** Update rp_filter Note 1501093.1 ********"
sed -i 's/rp_filter = 1/rp_filter = 0/g' /etc/sysctl.conf
grep rp_filter /etc/sysctl.conf
echo -e "\n******** Disable Firewall ********"
service iptables save
service iptables stop
chkconfig iptables off
chkconfig iptables --list
echo -e "\n******** AutoStart dnsmasq ********"
chkconfig dnsmasq on
chkconfig dnsmasq --list
echo -e "\n******** Update /etc/sysconfig/network ********"
echo "NOZEROCONF=yes" >> /etc/sysconfig/network
cat /etc/sysconfig/network
echo -e "\n******** Update /etc/grub.conf ********"
sed -i 's/quiet/quiet divider=10/g' /etc/grub.conf
grep divider /etc/grub.conf
echo -e "\n******** Update /etc/pam.d/login ********"
echo "session required pam_limits.so" >> /etc/pam.d/login
grep pam_limits.so /etc/pam.d/login
echo -e "\n******** Disable SELINUX ********"
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
grep SELINUX=disabled /etc/selinux/config
echo -e "\n******** Rename /etc/ntp.conf"
mv /etc/ntp.conf /etc/ntp.conf.org
ls -l /etc/ntp.conf.org
chmod 0444 /etc/ntp.conf.org
echo -e "\n******** Rename /etc/dnsmasq.conf ********"
mv /etc/dnsmasq.conf /etc/dnsmasq.conf.org
chmod 0444 /etc/dnsmasq.conf.org
ls -l /etc/dnsmasq.conf.org
echo -e "\n******** Configure /etc/hosts ********"
echo "192.168.56.11 rac01 rac01.localdomain" >> /etc/hosts
echo "192.168.56.12 rac02 rac02.localdomain" >> /etc/hosts
echo "192.168.56.13 rac03 rac03.localdomain" >> /etc/hosts
echo "192.168.56.21 rac01-vip rac01-vip.localdomain" >> /etc/hosts
echo "192.168.56.22 rac02-vip rac02-vip.localdomain" >> /etc/hosts
echo "192.168.56.23 rac03-vip rac03-vip.localdomain" >> /etc/hosts
echo "10.10.10.11 rac01-priv rac02-priv.localdomain" >> /etc/hosts
echo "10.10.10.12 rac02-priv rac02-priv.localdomain" >> /etc/hosts
echo "10.10.10.13 rac03-priv rac03-priv.localdomain" >> /etc/hosts
chmod 0444 /etc/hosts
cat /etc/hosts
echo -e "\n******** Configure /etc/dnsentries.txt for dnsmasq ********"
echo "192.168.56.11 rac01.localdomain" > /etc/dnsentries.txt
echo "192.168.56.12 rac02.localdomain" >> /etc/dnsentries.txt
echo "192.168.56.13 rac03.localdomain" >> /etc/dnsentries.txt
echo "192.168.56.31 dinh-scan.localdomain" >> /etc/dnsentries.txt
echo "192.168.56.32 dinh-scan.localdomain" >> /etc/dnsentries.txt
echo "192.168.56.33 dinh-scan.localdomain" >> /etc/dnsentries.txt
chmod 0444 /etc/dnsentries.txt
cat /etc/dnsentries.txt
echo -e "\n******** Configure /etc/dnsmasq.conf for dnsmasq ********"
echo "listen-address=127.0.0.1" > /etc/dnsmasq.conf
echo "strict-order" >> /etc/dnsmasq.conf
echo "domain-needed" >> /etc/dnsmasq.conf
echo "bogus-priv" >> /etc/dnsmasq.conf
echo "no-poll" >> /etc/dnsmasq.conf
echo "no-hosts" >> /etc/dnsmasq.conf
echo "local=/localdomain/" >> /etc/dnsmasq.conf
echo "addn-hosts=/etc/dnsentries.txt" >> /etc/dnsmasq.conf
echo "cache-size=150" >> /etc/dnsmasq.conf
chmod 0444 /etc/dnsmasq.conf
cat /etc/dnsmasq.conf
echo -e "\n******** Configure /etc/resolv.conf for dnsmasq ********"
echo "nameserver 127.0.0.1" > /etc/resolv.conf
echo "nameserver 172.16.0.1" >> /etc/resolv.conf
echo "nameserver 8.8.8.8" >> /etc/resolv.conf
echo "search localdomain" >> /etc/resolv.conf
chattr +i /etc/resolv.conf
cat /etc/resolv.conf
echo -e "\n******** Start dnsmasq ********"
service dnsmasq start
echo -e "\n******** Test nslookup ********"
nslookup rac01
nslookup rac02
nslookup rac03
nslookup dinh-scan
nslookup dinh-scan
nslookup dinh-scan
nslookup www.google.com
echo -e "\n******** Install Guest Additions ********"
/media/VBOXADDITIONS_4.2.12_84980/VBoxLinuxAdditions.run
echo -e "\n******** Add asmadmin asmdba ********"
groupadd -g 54325 asmadmin
groupadd -g 54326 asmdba
echo -e "\n******** Update Oracle's group ********"
usermod -g dba -G dba,asmadmin,asmdba,vboxsf oracle
id oracle
echo -e "\n******** Create directories ********"
mkdir -p /u01/app/oraInventory
mkdir -p /u01/app/11.2.0.3/grid
mkdir -p /u01/app/oracle
mkdir -p /u01/app/oracle/cfgtoollogs
mkdir -p /u01/app/oracle/product/11.2.0.3/dbhome_1
chown -R oracle:dba /u01
chmod -R 775 /u01
find /u01 -type d -ls
} > /tmp/rackiss.log 2>&1
echo -e "\n******** Check /tmp/rackiss.log for errors ********"
exit
Verify mount for VBOXADDITIONS exist
[root@rac01 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg01-lv_root
28G 1.8G 25G 7% /
tmpfs 1004M 100K 1004M 1% /dev/shm
/dev/sda1 485M 55M 405M 12% /boot
/dev/sr0 57M 57M 0 100% /media/VBOXADDITIONS_4.2.12_84980
Create and Run rackiss.sh
[root@rac01 ~]# vi rackiss.sh [root@rac01 ~]# chmod 755 rackiss.sh [root@rac01 ~]# time ./rackiss.sh ******** Check /tmp/rackiss.log for errors ******** real 33m43.730s user 0m49.952s sys 0m33.525s
Review Log
[root@rac01 ~]# cat /tmp/rackiss.log
******** Disable packagekit refresh ********
enabled=0
******** Disable avahi-daemon 1501093.1 ********
Shutting down Avahi daemon: [ OK ]
avahi-daemon 0:off 1:off 2:off 3:off 4:off 5:off 6:off
******** Silent Install oracle-rdbms-server-11gR2-preinstall ********
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Importing GPG key 0xEC551F03:
Userid: "Oracle OSS group (Open Source Software group) <build@oss.oracle.com>"
From : http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
********* Silent Install dnsmaq, oracleasm, kernel *********
******** Download oracleasmlib ********
--2013-05-19 20:35:49-- http://download.oracle.com/otn_software/asmlib/oracleasmlib-2.0.4-1.el6.x86_64.rpm
Resolving download.oracle.com... 63.141.192.73, 63.141.192.10
Connecting to download.oracle.com|63.141.192.73|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 13300 (13K) [application/x-redhat-package-manager]
Saving to: âoracleasmlib-2.0.4-1.el6.x86_64.rpmâ
2013-05-19 20:35:50 (234 KB/s) - âoracleasmlib-2.0.4-1.el6.x86_64.rpmâ
******** Install oracleasmlib ********
Loaded plugins: security
Setting up Local Package Process
Examining oracleasmlib-2.0.4-1.el6.x86_64.rpm: oracleasmlib-2.0.4-1.el6.x86_64
Marking oracleasmlib-2.0.4-1.el6.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracleasmlib.x86_64 0:2.0.4-1.el6 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
oracleasmlib x86_64 2.0.4-1.el6 /oracleasmlib-2.0.4-1.el6.x86_64 27 k
Transaction Summary
================================================================================
Install 1 Package(s)
Total size: 27 k
Installed size: 27 k
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : oracleasmlib-2.0.4-1.el6.x86_64 1/1
Verifying : oracleasmlib-2.0.4-1.el6.x86_64 1/1
Installed:
oracleasmlib.x86_64 0:2.0.4-1.el6
Complete!
******** Verify yum install ********
Package oracle-rdbms-server-11gR2-preinstall-1.0-7.el6.x86_64 already installed and latest version
Package dnsmasq-2.48-13.el6.x86_64 already installed and latest version
Package oracleasm-support-2.1.8-1.el6.x86_64 already installed and latest version
Package kernel-uek-devel-2.6.39-200.24.1.el6uek.x86_64 already installed and latest version
Loaded plugins: security
Installed Packages
oracleasmlib.x86_64 2.0.4-1.el6 @/oracleasmlib-2.0.4-1.el6.x86_64
******** Update rp_filter Note 1501093.1 ********
net.ipv4.conf.default.rp_filter = 0
******** Disable Firewall ********
iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Unloading modules: [ OK ]
iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off
******** AutoStart dnsmasq ********
dnsmasq 0:off 1:off 2:on 3:on 4:on 5:on 6:off
******** Update /etc/sysconfig/network ********
NETWORKING=yes
HOSTNAME=rac01.localdomain
NOZEROCONF=yes
******** Update /etc/grub.conf ********
kernel /vmlinuz-2.6.39-200.24.1.el6uek.x86_64 ro root=/dev/mapper/vg01-lv_root rd_LVM_LV=vg01/lv_swap rd_NO_LUKS LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16 rd_LVM_LV=vg01/lv_root KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM rhgb quiet divider=10 numa=off
kernel /vmlinuz-2.6.32-279.el6.x86_64 ro root=/dev/mapper/vg01-lv_root rd_LVM_LV=vg01/lv_swap rd_NO_LUKS LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16 crashkernel=auto rd_LVM_LV=vg01/lv_root KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM rhgb quiet divider=10 numa=off
******** Update /etc/pam.d/login ********
session required pam_limits.so
******** Disable SELINUX ********
SELINUX=disabled
******** Rename /etc/ntp.conf
-rw-r--r--. 1 root root 1917 Jul 6 2010 /etc/ntp.conf.org
******** Rename /etc/dnsmasq.conf ********
-r--r--r--. 1 root root 21214 May 19 20:35 /etc/dnsmasq.conf.org
******** Configure /etc/hosts ********
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.56.11 rac01 rac01.localdomain
192.168.56.12 rac02 rac02.localdomain
192.168.56.13 rac03 rac03.localdomain
192.168.56.21 rac01-vip rac01-vip.localdomain
192.168.56.22 rac02-vip rac02-vip.localdomain
192.168.56.23 rac03-vip rac03-vip.localdomain
10.10.10.11 rac01-priv rac02-priv.localdomain
10.10.10.12 rac02-priv rac02-priv.localdomain
10.10.10.13 rac03-priv rac03-priv.localdomain
******** Configure /etc/dnsentries.txt for dnsmasq ********
192.168.56.11 rac01.localdomain
192.168.56.12 rac02.localdomain
192.168.56.13 rac03.localdomain
192.168.56.31 dinh-scan.localdomain
192.168.56.32 dinh-scan.localdomain
192.168.56.33 dinh-scan.localdomain
******** Configure /etc/dnsmasq.conf for dnsmasq ********
listen-address=127.0.0.1
strict-order
domain-needed
bogus-priv
no-poll
no-hosts
local=/localdomain/
addn-hosts=/etc/dnsentries.txt
cache-size=150
******** Configure /etc/resolv.conf for dnsmasq ********
nameserver 127.0.0.1
nameserver 172.16.0.1
nameserver 8.8.8.8
search localdomain
******** Start dnsmasq ********
Starting dnsmasq: [ OK ]
******** Test nslookup ********
Server: 127.0.0.1
Address: 127.0.0.1#53
Name: rac01.localdomain
Address: 192.168.56.11
Server: 127.0.0.1
Address: 127.0.0.1#53
Name: rac02.localdomain
Address: 192.168.56.12
Server: 127.0.0.1
Address: 127.0.0.1#53
Name: rac03.localdomain
Address: 192.168.56.13
Server: 127.0.0.1
Address: 127.0.0.1#53
Name: dinh-scan.localdomain
Address: 192.168.56.33
Name: dinh-scan.localdomain
Address: 192.168.56.31
Name: dinh-scan.localdomain
Address: 192.168.56.32
Server: 127.0.0.1
Address: 127.0.0.1#53
Name: dinh-scan.localdomain
Address: 192.168.56.31
Name: dinh-scan.localdomain
Address: 192.168.56.32
Name: dinh-scan.localdomain
Address: 192.168.56.33
Server: 127.0.0.1
Address: 127.0.0.1#53
Name: dinh-scan.localdomain
Address: 192.168.56.32
Name: dinh-scan.localdomain
Address: 192.168.56.33
Name: dinh-scan.localdomain
Address: 192.168.56.31
Server: 127.0.0.1
Address: 127.0.0.1#53
Non-authoritative answer:
Name: www.google.com
Address: 74.125.224.177
Name: www.google.com
Address: 74.125.224.178
Name: www.google.com
Address: 74.125.224.179
Name: www.google.com
Address: 74.125.224.180
Name: www.google.com
Address: 74.125.224.176
******** Install Guest Additions ********
Verifying archive integrity... All good.
Uncompressing VirtualBox 4.2.12 Guest Additions for Linux............
VirtualBox Guest Additions installer
Copying additional installer modules ...
Installing additional modules ...
Removing existing VirtualBox non-DKMS kernel modules [ OK ]
Building the VirtualBox Guest Additions kernel modules
The headers for the current running kernel were not found. If the following
module compilation fails then this could be the reason.
The missing package can be probably installed with
yum install kernel-uek-devel-2.6.39-200.24.1.el6uek.x86_64
Building the main Guest Additions module [ OK ]
Building the shared folder support module [ OK ]
Building the OpenGL support module [ OK ]
Doing non-kernel setup of the Guest Additions [ OK ]
Starting the VirtualBox Guest Additions [ OK ]
Installing the Window System drivers
Installing X.Org Server 1.10 modules [ OK ]
Setting up the Window System to use the Guest Additions [ OK ]
You may need to restart the hal service and the Window System (or just restart
the guest system) to enable the Guest Additions.
Installing graphics libraries and desktop services componen[ OK ]
******** Add asmadmin asmdba ********
******** Update Oracle's group ********
uid=54321(oracle) gid=54322(dba) groups=54322(dba),54323(vboxsf),54325(asmadmin),54326(asmdba)
******** Create directories ********
654346 4 drwxrwxr-x 3 oracle dba 4096 May 19 21:06 /u01
654347 4 drwxrwxr-x 5 oracle dba 4096 May 19 21:06 /u01/app
654349 4 drwxrwxr-x 3 oracle dba 4096 May 19 21:06 /u01/app/11.2.0.3
654350 4 drwxrwxr-x 2 oracle dba 4096 May 19 21:06 /u01/app/11.2.0.3/grid
654351 4 drwxrwxr-x 4 oracle dba 4096 May 19 21:06 /u01/app/oracle
654352 4 drwxrwxr-x 2 oracle dba 4096 May 19 21:06 /u01/app/oracle/cfgtoollogs
654353 4 drwxrwxr-x 3 oracle dba 4096 May 19 21:06 /u01/app/oracle/product
654354 4 drwxrwxr-x 3 oracle dba 4096 May 19 21:06 /u01/app/oracle/product/11.2.0.3
654355 4 drwxrwxr-x 2 oracle dba 4096 May 19 21:06 /u01/app/oracle/product/11.2.0.3/dbhome_1
654348 4 drwxrwxr-x 2 oracle dba 4096 May 19 21:06 /u01/app/oraInventory
[root@rac01 ~]#
Connecting to RAC DB with SQL*Plus
Download
Instant Client Package – Basic Lite
Instant Client Package – SQL*Plus
http://www.oracle.com/technetwork/topics/winsoft-085727.html
Modify hosts file
C:\Windows\System32\drivers\etc>more hosts # Copyright (c) 1993-2009 Microsoft Corp. # # This is a sample HOSTS file used by Microsoft TCP/IP for Windows. # # This file contains the mappings of IP addresses to host names. Each # entry should be kept on an individual line. The IP address should # be placed in the first column followed by the corresponding host name. # The IP address and the host name should be separated by at least one # space. # # Additionally, comments (such as these) may be inserted on individual # lines or following the machine name denoted by a '#' symbol. # # For example: # # 102.54.94.97 rhino.acme.com # source server # 38.25.63.10 x.acme.com # x client host # localhost name resolution is handled within DNS itself. # 127.0.0.1 localhost # ::1 localhost 192.168.56.31 dinh-scan1 192.168.56.32 dinh-scan2 192.168.56.33 dinh-scan3 192.168.56.21 rac01-vip 192.168.56.22 rac02-vip 192.168.56.23 rac03-vip
Modify tnsnames
D:\instantclient_11_2>more tnsnames.ora
jay =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dinh-scan1)(PORT = 1551))
(ADDRESS = (PROTOCOL = TCP)(HOST = dinh-scan2)(PORT = 1551))
(ADDRESS = (PROTOCOL = TCP)(HOST = dinh-scan3)(PORT = 1551))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jay)
)
)
jay1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jay)
)
)
jay2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac02-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jay)
)
)
jay3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac03-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = jay)
)
)
Modify login.sql
D:\instantclient_11_2>more login.sql
--
-- Copyright (c) 1988, 2011, Oracle and/or its affiliates.
-- All rights reserved.
--
-- NAME
-- glogin.sql
--
-- DESCRIPTION
-- SQL*Plus global login "site profile" file
--
-- Add any SQL*Plus commands here that are to be executed when a
-- user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
-- This script is automatically run
--
set termout off
define _pr="SQL> "
column pr new_value _pr
select UPPER(SYS_CONTEXT('USERENV','SERVER_HOST'))||':(&_USER@&_CONNECT_IDENTIFIER):'||SYS_CONTEXT('USERENV','DATABASE_ROLE')||'> '
pr from dual;
set sqlprompt "&_pr"
column pr clear
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
define _editor=D:\PortableApps\npp.6.3.2.bin.minimalist\notepad++
set editfile E:\TMP
set termout on
Modify environment variables
D:\instantclient_11_2>set TNS_ADMIN TNS_ADMIN=D:\instantclient_11_2 D:\instantclient_11_2>set SQLPATH SQLPATH=D:\instantclient_11_2 D:\instantclient_11_2>set PATH Path=D:\Program Files\Oracle\VirtualBox;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:\Windows\sys tem32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files\Intel\Intel(R) Management Eng ine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\OpenCL SD K\2.0\bin\x86;C:\Program Files (x86)\Intel\OpenCL SDK\2.0\bin\x64;D:\instantclient_11_2 PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
D:\>sqlplus sys@\”dinh-scan1:1551/jay\” as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 18 18:32:31 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options RAC01:(SYS@dinh-scan1:1551/jay):PRIMARY> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
D:\>sqlplus sys@jay2 as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 18 18:32:43 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options RAC02:(SYS@jay2):PRIMARY> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
D:\>sqlplus sys@\”dinh-scan1:1551/jay\” as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat May 18 18:32:50 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options RAC03:(SYS@dinh-scan1:1551/jay):PRIMARY> exit
Where’s my RAC dbconsole
[oracle@rac01 ~]$ tail /etc/oratab
# The first and second fields are the system identifier and home # directory of the database respectively. The third filed indicates # to the dbstart utility that the database should , "Y", or should not, # "N", be brought up at system boot time. # # Multiple entries with the same $ORACLE_SID are not allowed. # # +ASM1:/u01/app/11.2.0.3/grid:N # line added by Agent jay:/u01/app/oracle/product/11.2.0.3/dbhome_1:N # line added by Agent [oracle@rac01 ~]$ . oraenv ORACLE_SID = [oracle] ? jay The Oracle base has been set to /u01/app/oracle [oracle@rac01 ~]$ emctl stsatus dbconsole Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME to database unique name. [oracle@rac01 ~]$ export ORACLE_UNQNAME=jay [oracle@rac01 ~]$ emctl status dbconsole Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0 Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved. https://rac01:1158/em/console/aboutApplication Oracle Enterprise Manager 11g is running. ------------------------------------------------------------------ Logs are generated in directory /u01/app/oracle/product/11.2.0.3/dbhome_1/rac01_jay/sysman/log [oracle@rac01 ~]$ nslookup rac01 Server: 127.0.0.1 Address: 127.0.0.1#53 Name: rac01.localdomain Address: 192.168.56.11 [oracle@rac01 ~]$ https://192.168.56.11:1158/em <--
Live, Learn and Share
Live, Learn and Share is the signature from a friend that I used to work with – Chau Vu
Today, I learned something new because I was shared something by a colleague – Jing Han
If you know part of filename for what you are looking for but don’t know where it resides, then you can use locate to find it.
$ uname -an Linux lax.localdomain 2.6.39-200.24.1.el6uek.x86_64 #1 SMP Sat Jun 23 02:39:07 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux [oracle@lax:db01]/home/oracle $ ll /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/alert_db01.log -rw-r----- 1 oracle oinstall 29046 May 6 20:14 /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/alert_db01.log [oracle@lax:db01]/home/oracle $ locate alert_db01.log /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/alert_db01.log [oracle@lax:db01]/home/oracle $ locate alert /home/oracle/rda/modules/DBalert.def /home/oracle/rda/modules/TSTalert.cfg /home/oracle/rda/modules/TSTalert.def /u01/app/oracle/diag/rdbms/lax_db01/db01/alert /u01/app/oracle/diag/rdbms/lax_db01/db01/alert/log.xml /u01/app/oracle/diag/rdbms/lax_db01/db01/trace/alert_db01.log /u01/app/oracle/product/11.2.0.3/db_1/apex/images/alert.gif /u01/app/oracle/product/11.2.0.3/db_1/apex/images/alert_error.gif /u01/app/oracle/product/11.2.0.3/db_1/apex/images/alert_info.gif /u01/app/oracle/product/11.2.0.3/db_1/apex/images/alert_warning.gif /u01/app/oracle/product/11.2.0.3/db_1/apex/images/htmldb/icons/alert_warning.gif /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/ecm/patch/cpf/remedies_alerts.jspf /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/ecm/patch/cpf/lov/alertLov.jsp /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/ip/render/elem/alertsParam.jsp /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/ip/render/elem/webapps/summary/alertParam.jsp /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/monitoring/alertAcknowledgeConfirm.jsp /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/monitoring/alertClearConfirm.uix /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/monitoring/alertDetails.uix /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/monitoring/em2go/alertDetails.uix /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert/alert_help.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert/alert_help_ja.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs/alert_cs_help.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs/alert_cs_help_de.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs/alert_cs_help_es.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs/alert_cs_help_fr.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs/alert_cs_help_it.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs/alert_cs_help_ja.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs/alert_cs_help_ko.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs/alert_cs_help_pt_BR.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs/alert_cs_help_zh_CN.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/online_help/alert_cs/alert_cs_help_zh_TW.jar /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/sdk/alertsInclude.jsp /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/sdk/em2go/alertsInclude.jsp /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/sdk/em2go/webappalertsInclude.jsp /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/sdk/em2go/webapprelatedalertsInclude.jsp /u01/app/oracle/product/11.2.0.3/db_1/j2ee/OC4J_EM/applications/em/em/target/groups/alerts.jsp /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/database/instance/alertlog /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/database/instance/alertlog/archivepurge /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/database/instance/alertlog/archivepurge/netUtil.js /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/database/monitoring/alertLogContent.uix /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/database/monitoring/alertLogEntries.uix /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/monitoring/alertClearConfirm.uix /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/monitoring/alertDetails.uix /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert/alert_help.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert/alert_help_ja.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs/alert_cs_help.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs/alert_cs_help_de.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs/alert_cs_help_es.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs/alert_cs_help_fr.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs/alert_cs_help_it.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs/alert_cs_help_ja.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs/alert_cs_help_ko.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs/alert_cs_help_pt_BR.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs/alert_cs_help_zh_CN.jar /u01/app/oracle/product/11.2.0.3/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/online_help/alert_cs/alert_cs_help_zh_TW.jar /u01/app/oracle/product/11.2.0.3/db_1/owb/wf/demo/wfalertb.sql /u01/app/oracle/product/11.2.0.3/db_1/owb/wf/demo/wfalertd.sql /u01/app/oracle/product/11.2.0.3/db_1/owb/wf/demo/wfalerts.sql /u01/app/oracle/product/11.2.0.3/db_1/owb/wf/java/oracle/apps/fnd/wf/icons/alert.gif /u01/app/oracle/product/11.2.0.3/db_1/sysman/admin/emdrep/sql/core/10.2.0.4/severity/alert_schema_upgrade.sql /u01/app/oracle/product/11.2.0.3/db_1/sysman/admin/scripts/alertlog.pl /u01/app/oracle/product/11.2.0.3/db_1/sysman/admin/scripts/alertlogAdr.pl /u01/app/oracle/product/11.2.0.3/db_1/sysman/admin/scripts/alertlogAdrViewer.pl /u01/app/oracle/product/11.2.0.3/db_1/sysman/admin/scripts/alertlogAdr_util.pl /u01/app/oracle/product/11.2.0.3/db_1/sysman/admin/scripts/alertlogViewer.pl /u01/app/oracle/product/11.2.0.3/db_1/sysman/admin/scripts/alertlog_find.pl /u01/app/oracle/product/11.2.0.3/db_1/sysman/admin/scripts/alertlog_util.pl /u01/app/oracle/product/11.2.0.3/db_1/sysman/admin/scripts/archivealertlog.pl /u01/app/oracle/product/11.2.0.3/db_1/sysman/admin/scripts/purgealertlog.pl /usr/share/icons/gnome/16x16/status/weather-severe-alert.png /usr/share/icons/gnome/22x22/status/weather-severe-alert.png /usr/share/icons/gnome/24x24/status/weather-severe-alert.png /usr/share/icons/gnome/32x32/status/weather-severe-alert.png /usr/share/icons/gnome/scalable/status/weather-severe-alert.svg /usr/share/sounds/gnome/default/alerts /usr/share/sounds/gnome/default/alerts/bark.ogg /usr/share/sounds/gnome/default/alerts/drip.ogg /usr/share/sounds/gnome/default/alerts/glass.ogg /usr/share/sounds/gnome/default/alerts/sonar.ogg [oracle@lax:db01]/home/oracle $
Go Live, Learn and Share
It’s a dirty job
Before, I worked for organization that frowned upon database jobs and only uses cron job.
Now, it’s just the opposite and I get to learn about dbms_scheduler job.
Create job to run PLSQL_BLOCK every 5 minutes at: 21,22,23 (24 hour format)
LAX:(MDINH@db01)> @createjob
SYSDATE
-------------------
2013-05-04 12:29:55
LAX:(MDINH@db01)> begin
2 dbms_scheduler.create_job (
3 job_name => 'DIRTY_JOB',
4 job_type => 'PLSQL_BLOCK',
5 job_action => 'BEGIN NULL; END;',
6 start_date => trunc(sysdate)+21/24,
7 repeat_interval => 'FREQ=minutely; BYHOUR=21,22,23; INTERVAL=5',
8 end_date => NULL,
9 enabled => TRUE);
10 end;
11 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> @@checkjob dirty
LAX:(MDINH@db01)> set lines 200 veri off echo off
-- dba_scheduler_jobs
JOB_NAME REPEAT_INTERVAL NEXT_RUN_DATE
------------------------------ ------------------------------------------------------------ ----------------------------------------
DIRTY_JOB FREQ=minutely; BYHOUR=21,22,23; INTERVAL=5 04-MAY-13 09.00.00.000000 PM -07:00
-- dba_scheduler_job_run_details
no rows selected
-- FORCE RUN_JOB
LAX:(MDINH@db01)> exec dbms_scheduler.RUN_JOB('DIRTY_JOB');
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> @@checkjob dirty
LAX:(MDINH@db01)> set lines 200 veri off echo off
-- dba_scheduler_jobs
JOB_NAME REPEAT_INTERVAL NEXT_RUN_DATE
------------------------------ ------------------------------------------------------------ ----------------------------------------
DIRTY_JOB FREQ=minutely; BYHOUR=21,22,23; INTERVAL=5 04-MAY-13 09.00.00.000000 PM -07:00
-- dba_scheduler_job_run_details
LOG_DATE JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION
----------------------------------- ------------------------------ ---------- ----------------------------------- ---------------
04-MAY-13 12.29.55.112836 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.29.55.111618 PM -07:00 +000 00:00:00
!!! Shoot – do not want to wail until 21:00 to know results !!!
– CHANGE START_DATE & REPEAT_INTERVAL
LAX:(MDINH@db01)> exec dbms_scheduler.SET_ATTRIBUTE(name=>'DIRTY_JOB',attribute=>'start_date',value=>SYSDATE); PL/SQL procedure successfully completed. LAX:(MDINH@db01)> exec dbms_scheduler.SET_ATTRIBUTE(name=>'DIRTY_JOB',attribute=>'repeat_interval',value=>'FREQ=minutely;BYHOUR=12,13;INTERVAL=5'); PL/SQL procedure successfully completed. LAX:(MDINH@db01)> @@checkjob dirty LAX:(MDINH@db01)> set lines 200 veri off echo off -- dba_scheduler_jobs JOB_NAME REPEAT_INTERVAL NEXT_RUN_DATE ------------------------------ ------------------------------------------------------------ ---------------------------------------- DIRTY_JOB FREQ=minutely;BYHOUR=12,13;INTERVAL=5 04-MAY-13 12.34.55.000000 PM -07:00 -- dba_scheduler_job_run_details LOG_DATE JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION ----------------------------------- ------------------------------ ---------- ----------------------------------- --------------- 04-MAY-13 12.29.55.112836 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.29.55.111618 PM -07:00 +000 00:00:00
Back from lunch and looks good.
Job did not run after 14:00 as expected.
LAX:(MDINH@db01)> select sysdate from dual; ------------------- 2013-05-04 14:10:43 LAX:(MDINH@db01)> @checkjob dirty -- dba_scheduler_jobs JOB_NAME REPEAT_INTERVAL NEXT_RUN_DATE ------------------------------ ------------------------------------------------------------ ---------------------------------------- DIRTY_JOB FREQ=minutely;BYHOUR=12,13;INTERVAL=5 05-MAY-13 12.04.55.000000 PM -07:00 -- dba_scheduler_job_run_details LOG_DATE JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION ----------------------------------- ------------------------------ ---------- ----------------------------------- --------------- 04-MAY-13 12.29.55.112836 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.29.55.111618 PM -07:00 +000 00:00:00 04-MAY-13 12.34.55.045811 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.34.55.038072 PM -07:00 +000 00:00:00 04-MAY-13 12.39.55.050034 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.39.55.040566 PM -07:00 +000 00:00:00 04-MAY-13 12.44.55.079379 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.44.55.057040 PM -07:00 +000 00:00:00 04-MAY-13 12.49.55.132232 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.49.55.124111 PM -07:00 +000 00:00:00 04-MAY-13 12.54.55.238525 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.54.55.230027 PM -07:00 +000 00:00:00 04-MAY-13 12.59.55.046174 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.59.55.037725 PM -07:00 +000 00:00:00 04-MAY-13 01.04.55.057574 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.04.55.049089 PM -07:00 +000 00:00:00 04-MAY-13 01.09.55.043001 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.09.55.038407 PM -07:00 +000 00:00:00 04-MAY-13 01.14.55.058771 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.14.55.053925 PM -07:00 +000 00:00:00 04-MAY-13 01.19.55.048791 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.19.55.039703 PM -07:00 +000 00:00:00 04-MAY-13 01.24.55.038104 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.24.55.029655 PM -07:00 +000 00:00:00 04-MAY-13 01.29.55.046963 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.29.55.041092 PM -07:00 +000 00:00:00 04-MAY-13 01.34.55.063285 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.34.55.055247 PM -07:00 +000 00:00:00 04-MAY-13 01.39.55.050528 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.39.55.041886 PM -07:00 +000 00:00:00 04-MAY-13 01.44.55.042718 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.44.55.023271 PM -07:00 +000 00:00:00 04-MAY-13 01.49.55.056224 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.49.55.051302 PM -07:00 +000 00:00:00 04-MAY-13 01.54.55.046269 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.54.55.037763 PM -07:00 +000 00:00:00 04-MAY-13 01.59.55.059423 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.59.55.051539 PM -07:00 +000 00:00:00 19 rows selected.
LAX:(MDINH@db01)> @dropjob
LAX:(MDINH@db01)> exec dbms_scheduler.DISABLE ('DIRTY_JOB',TRUE);
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> exec dbms_scheduler.STOP_JOB('DIRTY_JOB',TRUE);
BEGIN dbms_scheduler.STOP_JOB('DIRTY_JOB',TRUE); END;
*
ERROR at line 1:
ORA-27366: job "MDINH.DIRTY_JOB" is not running
ORA-06512: at "SYS.DBMS_ISCHED", line 199
ORA-06512: at "SYS.DBMS_SCHEDULER", line 557
ORA-06512: at line 1
LAX:(MDINH@db01)> exec dbms_scheduler.DROP_JOB('DIRTY_JOB',TRUE);
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> @@checkjob dirty
LAX:(MDINH@db01)> set lines 200 veri off echo off
-- dba_scheduler_jobs
no rows selected
-- dba_scheduler_job_run_details
LOG_DATE JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION
----------------------------------- ------------------------------ ---------- ----------------------------------- ---------------
04-MAY-13 12.29.55.112836 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.29.55.111618 PM -07:00 +000 00:00:00
04-MAY-13 12.34.55.045811 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.34.55.038072 PM -07:00 +000 00:00:00
04-MAY-13 12.39.55.050034 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.39.55.040566 PM -07:00 +000 00:00:00
04-MAY-13 12.44.55.079379 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.44.55.057040 PM -07:00 +000 00:00:00
04-MAY-13 12.49.55.132232 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.49.55.124111 PM -07:00 +000 00:00:00
04-MAY-13 12.54.55.238525 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.54.55.230027 PM -07:00 +000 00:00:00
04-MAY-13 12.59.55.046174 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 12.59.55.037725 PM -07:00 +000 00:00:00
04-MAY-13 01.04.55.057574 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.04.55.049089 PM -07:00 +000 00:00:00
04-MAY-13 01.09.55.043001 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.09.55.038407 PM -07:00 +000 00:00:00
04-MAY-13 01.14.55.058771 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.14.55.053925 PM -07:00 +000 00:00:00
04-MAY-13 01.19.55.048791 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.19.55.039703 PM -07:00 +000 00:00:00
04-MAY-13 01.24.55.038104 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.24.55.029655 PM -07:00 +000 00:00:00
04-MAY-13 01.29.55.046963 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.29.55.041092 PM -07:00 +000 00:00:00
04-MAY-13 01.34.55.063285 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.34.55.055247 PM -07:00 +000 00:00:00
04-MAY-13 01.39.55.050528 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.39.55.041886 PM -07:00 +000 00:00:00
04-MAY-13 01.44.55.042718 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.44.55.023271 PM -07:00 +000 00:00:00
04-MAY-13 01.49.55.056224 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.49.55.051302 PM -07:00 +000 00:00:00
04-MAY-13 01.54.55.046269 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.54.55.037763 PM -07:00 +000 00:00:00
04-MAY-13 01.59.55.059423 PM -07:00 DIRTY_JOB SUCCEEDED 04-MAY-13 01.59.55.051539 PM -07:00 +000 00:00:00
19 rows selected.
LAX:(MDINH@db01)> exec dbms_scheduler.PURGE_LOG();
PL/SQL procedure successfully completed. LAX:(MDINH@db01)> @checkjob dirty -- dba_scheduler_jobs no rows selected -- dba_scheduler_job_run_details no rows selected LAX:(MDINH@db01)>
SCRIPTS Used:
$ cat checkjob.sql
set lines 200 veri off echo off set pages 1000 col JOB_NAME for a30 col NEXT_RUN_DATE for a40 col REPEAT_INTERVAL for a60 col ACTUAL_START_DATE for a35 col LOG_DATE for a35 col RUN_DURATION for a15 col STATUS for a10 pro -- dba_scheduler_jobs select job_name,repeat_interval,next_run_date from dba_scheduler_jobs where regexp_like(job_name,'&1','i'); pro -- dba_scheduler_job_run_details select log_date,job_name,status,actual_start_date,run_duration from dba_scheduler_job_run_details where regexp_like(job_name,'&1','i') order by actual_start_date asc;
$ cat createjob.sql
select sysdate from dual;
set echo on
begin
dbms_scheduler.create_job (
job_name => 'DIRTY_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN NULL; END;',
start_date => trunc(sysdate)+21/24,
repeat_interval => 'FREQ=minutely; BYHOUR=21,22,23; INTERVAL=5',
end_date => NULL,
enabled => TRUE);
end;
/
@@checkjob dirty
pro -- FORCE RUN_JOB
set echo on
exec dbms_scheduler.RUN_JOB('DIRTY_JOB');
@@checkjob dirty
pro !!! Shoot - do not want to wail until 21:00 to know results !!!
pro -- CHANGE START_DATE REPEAT_INTERVAL
set echo on
exec dbms_scheduler.SET_ATTRIBUTE(name=>'DIRTY_JOB',attribute=>'start_date',value=>SYSDATE);
exec dbms_scheduler.SET_ATTRIBUTE(name=>'DIRTY_JOB',attribute=>'repeat_interval',value=>'FREQ=minutely;BYHOUR=12,13;INTERVAL=5');
@@checkjob dirty
$ cat dropjob.sql
set echo on
exec dbms_scheduler.DISABLE ('DIRTY_JOB',TRUE);
exec dbms_scheduler.STOP_JOB('DIRTY_JOB',TRUE);
exec dbms_scheduler.DROP_JOB('DIRTY_JOB',TRUE);
@@checkjob dirty
exec dbms_scheduler.PURGE_LOG();
What kind of jobs are you doing or using?
OCM Installation Response Generator
Do you get annoyed at having to answer the questions all the time for patching?
Provide your email address to be informed of security issues, install and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name: You have not provided an email address for notification of security issues. Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
You won’t have to answer those questions anymore if you create OCM response file.
$ /u01/app/oracle/product/11.2.0.3/db_1/OPatch/ocm/bin/emocmrsp
OCM Installation Response Generator 10.3.4.0.0 - Production Copyright (c) 2005, 2010, Oracle and/or its affiliates. All rights reserved. Provide your email address to be informed of security issues, install and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name: You have not provided an email address for notification of security issues. Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y The OCM configuration response file (ocm.rsp) was successfully created.
Response file – ocm.rsp is create at the current directory.
$ ll ocm.rsp
-rw-r--r-- 1 oracle oinstall 623 Apr 29 21:37 ocm.rsp
Here comes the patching part and don’t ask me again
$ /u01/app/oracle/product/11.2.0.3/db_1/OPatch/opatch apply -ocmrf /home/oracle/ocm.rsp
Oracle Interim Patch Installer version 11.2.0.3.4 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0.3/db_1 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0.3/db_1/oraInst.loc OPatch version : 11.2.0.3.4 OUI version : 11.2.0.3.0 Log file location : /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2013-04-29_20-38-04PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 13343438 13696216 13923374 14275605 14727310 16056266 Do you want to proceed? [y|n] y User Responded with: Y All checks passed. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/app/oracle/product/11.2.0.3/db_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying sub-patch '13343438' to OH '/u01/app/oracle/product/11.2.0.3/db_1' Patching component oracle.rdbms.rsf, 11.2.0.3.0... Patching component oracle.rdbms, 11.2.0.3.0... Patching component oracle.rdbms.dbscripts, 11.2.0.3.0... Verifying the update... Applying sub-patch '13696216' to OH '/u01/app/oracle/product/11.2.0.3/db_1' Patching component oracle.rdbms.rsf, 11.2.0.3.0... Patching component oracle.rdbms, 11.2.0.3.0... Patching component oracle.sdo.locator, 11.2.0.3.0... Patching component oracle.sysman.console.db, 11.2.0.3.0... Patching component oracle.sysman.oms.core, 10.2.0.4.4... Verifying the update... Applying sub-patch '13923374' to OH '/u01/app/oracle/product/11.2.0.3/db_1' ApplySession: Optional component(s) [ oracle.network.cman, 11.2.0.3.0 ] not present in the Oracle Home or a higher version is found. Patching component oracle.rdbms.rsf, 11.2.0.3.0... Patching component oracle.rdbms, 11.2.0.3.0... Patching component oracle.rdbms.dbscripts, 11.2.0.3.0... Patching component oracle.network.rsf, 11.2.0.3.0... Patching component oracle.network.listener, 11.2.0.3.0... Patching component oracle.sysman.console.db, 11.2.0.3.0... Verifying the update... Applying sub-patch '14275605' to OH '/u01/app/oracle/product/11.2.0.3/db_1' ApplySession: Optional component(s) [ oracle.precomp.lang, 11.2.0.3.0 ] not present in the Oracle Home or a higher version is found. Patching component oracle.network.client, 11.2.0.3.0... Patching component oracle.network.rsf, 11.2.0.3.0... Patching component oracle.precomp.common, 11.2.0.3.0... Patching component oracle.rdbms, 11.2.0.3.0... Patching component oracle.rdbms.dbscripts, 11.2.0.3.0... Patching component oracle.rdbms.rman, 11.2.0.3.0... Patching component oracle.rdbms.rsf, 11.2.0.3.0... Patching component oracle.rdbms.util, 11.2.0.3.0... Verifying the update... Applying sub-patch '14727310' to OH '/u01/app/oracle/product/11.2.0.3/db_1' Patching component oracle.rdbms, 11.2.0.3.0... Patching component oracle.rdbms.dbscripts, 11.2.0.3.0... Patching component oracle.rdbms.rsf, 11.2.0.3.0... Patching component oracle.sdo.locator, 11.2.0.3.0... Patching component oracle.sysman.console.db, 11.2.0.3.0... Patching component oracle.sysman.oms.core, 10.2.0.4.4... Verifying the update... Applying sub-patch '16056266' to OH '/u01/app/oracle/product/11.2.0.3/db_1' ApplySession: Optional component(s) [ oracle.network.cman, 11.2.0.3.0 ] not present in the Oracle Home or a higher version is found. Patching component oracle.network.listener, 11.2.0.3.0... Patching component oracle.network.rsf, 11.2.0.3.0... Patching component oracle.ovm, 11.2.0.3.0... Patching component oracle.rdbms, 11.2.0.3.0... Patching component oracle.rdbms.rman, 11.2.0.3.0... Patching component oracle.rdbms.rsf, 11.2.0.3.0... Patching component oracle.sdo.locator, 11.2.0.3.0... Patching component oracle.rdbms.deconfig, 11.2.0.3.0... Verifying the update... OPatch found the word "warning" in the stderr of the make command. Please look at this stderr. You can re-run this make command. Stderr output: ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg' /u01/app/oracle/product/11.2.0.3/db_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg' /u01/app/oracle/product/11.2.0.3/db_1/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg' /u01/app/oracle/product/11.2.0.3/db_1/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg' Composite patch 16056266 successfully applied. OPatch Session completed with warnings. Log file location: /u01/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2013-04-29_20-38-04PM_1.log OPatch completed with warnings.
EXTRA, EXTRA:
Scripts to download patch.
PLEASE NOTE the difference in naming conventions between version patch and OPatch.
$ cat getpatch.sh
#!/bin/sh
DN=`dirname $0`
BN=`basename $0`
FN=${1:?"---> USAGE: $DN/$BN "}
wget --http-user=fakeuser@mail.com --http-password=fakepassword --no-check-certificate --output-document=p${1}_112030_Linux-x86-64.zip "https://updates.oracle.com/Orion/Download/download_patch/p${1}_112030_Linux-x86-64.zip"
exit
$ cat getopatch.sh
#!/bin/sh wget --http-user=fakeuser@mail.com --http-password=fakepassword --no-check-certificate --output-document=p6880880_112000_Linux-x86-64.zip "https://updates.oracle.com/Orion/Download/download_patch/p6880880_112000_Linux-x86-64.zip" exit
Download patch
$ ./getpatch.sh
./getpatch.sh: line 5: 1: ---> USAGE: ./getpatch.sh <patch number>
$ ll *16056266*
ls: cannot access *16056266*: No such file or directory
$ ./getpatch.sh 16056266
--2013-04-29 21:00:06-- https://updates.oracle.com/Orion/Download/download_patch/p16056266_112030_Linux-x86-64.zip Resolving updates.oracle.com... 141.146.44.51 Connecting to updates.oracle.com|141.146.44.51|:443... connected. HTTP request sent, awaiting response... 301 Moved Permanently Location: https://login.oracle.com/pls/orasso/orasso.wwsso_app_admin.ls_login?site2pstoretoken=v1.2~E4066BF0~987B78EA259CB31D8BBC6647FDEE2DC7510D66F0A54FD6E7394DC81087F3535168022325FA0D7143E72537529DC60E9E1491A6E7EA2414B38EF6E710353365E803F669AF7F790254A8AFD47D776A218FA4B7F252BC2942C0B1BF65DFBC6296EDB7CBF365277E3646943194E1D6B74105B6A9E9F2EBECC1C0FD25943B4234AA240801C9EB0A2C859B6BB45222645B4092019E17801422ABDF91DECF57F2FBC6D26B0F60842FD49019330A27A629E9C82000BE9AB648FA8B3C70FCA74F1ECB7D29001B87A6622CE81E299C088EFE9AAE87BA92854AA220ED81 [following] --2013-04-29 21:00:19-- https://login.oracle.com/pls/orasso/orasso.wwsso_app_admin.ls_login?site2pstoretoken=v1.2~E4066BF0~987B78EA259CB31D8BBC6647FDEE2DC7510D66F0A54FD6E7394DC81087F3535168022325FA0D7143E72537529DC60E9E1491A6E7EA2414B38EF6E710353365E803F669AF7F790254A8AFD47D776A218FA4B7F252BC2942C0B1BF65DFBC6296EDB7CBF365277E3646943194E1D6B74105B6A9E9F2EBECC1C0FD25943B4234AA240801C9EB0A2C859B6BB45222645B4092019E17801422ABDF91DECF57F2FBC6D26B0F60842FD49019330A27A629E9C82000BE9AB648FA8B3C70FCA74F1ECB7D29001B87A6622CE81E299C088EFE9AAE87BA92854AA220ED81 Resolving login.oracle.com... 141.146.8.119 Connecting to login.oracle.com|141.146.8.119|:443... connected. HTTP request sent, awaiting response... 401 Unauthorized Reusing existing connection to login.oracle.com:443. HTTP request sent, awaiting response... 302 Moved Temporarily Location: https://updates.oracle.com/osso_login_success?urlc=v1.2%7EA4976F93813F9B35D4A1906FC5551FCF60299648E704348146F3111CE746E2C649E4BF1726C0D94107745E2307F14284E943B765C55859B993213F7FE963869554CB565F1B265C9D5391ADF4D908CD97BC62C96BA4925D889B40C7B51E52A215B44CC70ECEF5E544FF284736BFE50544D25D557DFDDEBAA0F6888A6EC22D0CC7ACB761FE37A0BD3D2B74629E91E2AAB1BAC09AA65DFCF41C5266242F8F89577DFD62055BCA94ACD87E4637489000C9951E18C55EF90C9E37C8D11E536612A9C30715A3D1302531E9A385DB4407CD9A6903BF55E738BCA312D28ED8ECE2784D67DBDE1F25B787FB1998C5B4E10F7BC463A5DF512BD88B5DD67744C479F88AE2A33DCE6745FB9736FDC396D72A6B93F9A90450CA1AE9BBB3B3DAF2DADA8F1194B4BC1B10E5DE070B79EFBC1EE4321D0D66DC9C59152257DB3EFC0899CC57C4A4E9D84BCB7A6518BA3044C5C86622DEAC2CF9135C635D0CA68A [following] --2013-04-29 21:00:38-- https://updates.oracle.com/osso_login_success?urlc=v1.2%7EA4976F93813F9B35D4A1906FC5551FCF60299648E704348146F3111CE746E2C649E4BF1726C0D94107745E2307F14284E943B765C55859B993213F7FE963869554CB565F1B265C9D5391ADF4D908CD97BC62C96BA4925D889B40C7B51E52A215B44CC70ECEF5E544FF284736BFE50544D25D557DFDDEBAA0F6888A6EC22D0CC7ACB761FE37A0BD3D2B74629E91E2AAB1BAC09AA65DFCF41C5266242F8F89577DFD62055BCA94ACD87E4637489000C9951E18C55EF90C9E37C8D11E536612A9C30715A3D1302531E9A385DB4407CD9A6903BF55E738BCA312D28ED8ECE2784D67DBDE1F25B787FB1998C5B4E10F7BC463A5DF512BD88B5DD67744C479F88AE2A33DCE6745FB9736FDC396D72A6B93F9A90450CA1AE9BBB3B3DAF2DADA8F1194B4BC1B10E5DE070B79EFBC1EE4321D0D66DC9C59152257DB3EFC0899CC57C4A4E9D84BCB7A6518BA3044C5C86622DEAC2CF9135C635D0CA68A Connecting to updates.oracle.com|141.146.44.51|:443... connected. HTTP request sent, awaiting response... 301 Moved Permanently Location: https://updates.oracle.com/Orion/Download/download_patch/p16056266_112030_Linux-x86-64.zip [following] --2013-04-29 21:00:39-- https://updates.oracle.com/Orion/Download/download_patch/p16056266_112030_Linux-x86-64.zip Connecting to updates.oracle.com|141.146.44.51|:443... connected. HTTP request sent, awaiting response... 302 Found Location: http://aru-akam.oracle.com/adcarurepos/vol/patch40/PLATFORM/CORE/Linux-x86-64/R80112030/p16056266_112030_Linux-x86-64.zip?AuthParam=1367294568_5a3356da38d8dd77c27d9ef722be6b7a&FilePath=/adcarurepos/vol/patch40/PLATFORM/CORE/Linux-x86-64/R80112030/p16056266_112030_Linux-x86-64.zip&File=p16056266_112030_Linux-x86-64.zip¶ms=RU5zK21lVUxPZnJZam1vZTMxYjZjQTphcnU9MTU5NjI4MDMmZW1haWw9ZGluaEBweXRoaWFuLmNvbSZmaWxlX2lkPTU4Mzg5MzI4JnBhdGNoX2ZpbGU9cDE2MDU2MjY2XzExMjAzMF9MaW51eC14ODYtNjQuemlwJnVzZXJpZD1vLWRpbmhAcHl0aGlhbi5jb20mc2l6ZT0zMjE1MjY3MiZjb250ZXh0PUFAMTArSEBhYXJ1dm10cDAxLm9yYWNsZS5jb20rUEAmZG93bmxvYWRfaWQ9NzM3MTg5OTI@ [following] --2013-04-29 21:00:48-- http://aru-akam.oracle.com/adcarurepos/vol/patch40/PLATFORM/CORE/Linux-x86-64/R80112030/p16056266_112030_Linux-x86-64.zip?AuthParam=1367294568_5a3356da38d8dd77c27d9ef722be6b7a&FilePath=/adcarurepos/vol/patch40/PLATFORM/CORE/Linux-x86-64/R80112030/p16056266_112030_Linux-x86-64.zip&File=p16056266_112030_Linux-x86-64.zip¶ms=RU5zK21lVUxPZnJZam1vZTMxYjZjQTphcnU9MTU5NjI4MDMmZW1haWw9ZGluaEBweXRoaWFuLmNvbSZmaWxlX2lkPTU4Mzg5MzI4JnBhdGNoX2ZpbGU9cDE2MDU2MjY2XzExMjAzMF9MaW51eC14ODYtNjQuemlwJnVzZXJpZD1vLWRpbmhAcHl0aGlhbi5jb20mc2l6ZT0zMjE1MjY3MiZjb250ZXh0PUFAMTArSEBhYXJ1dm10cDAxLm9yYWNsZS5jb20rUEAmZG93bmxvYWRfaWQ9NzM3MTg5OTI@ Resolving aru-akam.oracle.com... 69.31.119.160, 69.31.119.187 Connecting to aru-akam.oracle.com|69.31.119.160|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 32152672 (31M) [application/zip] Saving to: âp16056266_112030_Linux-x86-64.zipâ 100%[==>] 32,152,672 1.32M/s in 24s 2013-04-29 21:01:13 (1.29 MB/s) - âp16056266_112030_Linux-x86-64.zipâ
$ ll *16056266*
-rw-r--r-- 1 oracle oinstall 32152672 Apr 14 23:55 p16056266_112030_Linux-x86-64.zip
CRS and Cluster
CRS and Cluster, what’s the difference and which one should I use for start and stop?
CRS is Cluster Ready Service and used to manage HAS (High Availability Services)
Cluster is where the application runs on.
Stop CRS is local to the node and will shutdown local HAS – crsctl stop crs
Stop cluster will shutdown across all nodes and will NOT shutdown HAS – crsctl stop cluster -all
Here’s the catch.
Start cluster will NOT start the node without HAS running – crsctl start cluster -all
The node must be started manually – crsctl start crs
DEMO:
[root@rac01 ~]# /u01/app/11.2.0.3/grid/bin/crsctl status server
NAME=rac01 STATE=ONLINE NAME=rac02 STATE=ONLINE NAME=rac03 STATE=ONLINE
[root@rac01 ~]# /u01/app/11.2.0.3/grid/bin/crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac01' CRS-2673: Attempting to stop 'ora.crsd' on 'rac01' CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac01' CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac01' CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'rac01' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac01' CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.rac01.vip' on 'rac01' CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.scan2.vip' on 'rac01' CRS-2677: Stop of 'ora.scan2.vip' on 'rac01' succeeded CRS-2672: Attempting to start 'ora.scan2.vip' on 'rac02' CRS-2677: Stop of 'ora.rac01.vip' on 'rac01' succeeded CRS-2672: Attempting to start 'ora.rac01.vip' on 'rac03' CRS-2676: Start of 'ora.rac01.vip' on 'rac03' succeeded CRS-2676: Start of 'ora.scan2.vip' on 'rac02' succeeded CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'rac02' CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'rac02' succeeded CRS-2677: Stop of 'ora.DATA.dg' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'rac01' CRS-2677: Stop of 'ora.asm' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.ons' on 'rac01' CRS-2677: Stop of 'ora.ons' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.net1.network' on 'rac01' CRS-2677: Stop of 'ora.net1.network' on 'rac01' succeeded CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac01' has completed CRS-2677: Stop of 'ora.crsd' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac01' CRS-2673: Attempting to stop 'ora.ctssd' on 'rac01' CRS-2673: Attempting to stop 'ora.evmd' on 'rac01' CRS-2673: Attempting to stop 'ora.asm' on 'rac01' CRS-2677: Stop of 'ora.evmd' on 'rac01' succeeded CRS-2677: Stop of 'ora.mdnsd' on 'rac01' succeeded CRS-2677: Stop of 'ora.asm' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac01' CRS-2677: Stop of 'ora.ctssd' on 'rac01' succeeded CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'rac01' CRS-2677: Stop of 'ora.cssd' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.crf' on 'rac01' CRS-2677: Stop of 'ora.crf' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'rac01' CRS-2677: Stop of 'ora.gipcd' on 'rac01' succeeded CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac01' CRS-2677: Stop of 'ora.gpnpd' on 'rac01' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac01' has completed CRS-4133: Oracle High Availability Services has been stopped.
[root@rac01 ~]# /u01/app/11.2.0.3/grid/bin/crsctl status server
CRS-4535: Cannot communicate with Cluster Ready Services CRS-4000: Command Status failed, or completed with errors.
[root@rac01 ~]# /u01/app/11.2.0.3/grid/bin/crsctl check cluster -all
CRS-4639: Could not contact Oracle High Availability Services CRS-4000: Command Check failed, or completed with errors.
[root@rac01 ~]# ssh rac02 /u01/app/11.2.0.3/grid/bin/crsctl status server
NAME=rac02 STATE=ONLINE NAME=rac03 STATE=ONLINE
[root@rac01 ~]# ssh rac02 /u01/app/11.2.0.3/grid/bin/crsctl check cluster -all
************************************************************** rac02: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** rac03: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online **************************************************************
[root@rac01 ~]# ps -ef|grep grid
root 10899 10603 0 10:24 pts/1 00:00:00 grep grid
[root@rac02 ~]# /u01/app/11.2.0.3/grid/bin/crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'rac02' CRS-2673: Attempting to stop 'ora.crsd' on 'rac03' CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac02' CRS-2673: Attempting to stop 'ora.oc4j' on 'rac02' CRS-2673: Attempting to stop 'ora.cvu' on 'rac02' CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'rac02' CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'rac02' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac02' CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac02' CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac03' CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'rac03' CRS-2673: Attempting to stop 'ora.rac03.vip' on 'rac03' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac03' CRS-2673: Attempting to stop 'ora.DATA.dg' on 'rac03' CRS-2677: Stop of 'ora.rac03.vip' on 'rac03' succeeded CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'rac02' succeeded CRS-2673: Attempting to stop 'ora.scan2.vip' on 'rac02' CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'rac02' succeeded CRS-2673: Attempting to stop 'ora.scan3.vip' on 'rac02' CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac02' succeeded CRS-2673: Attempting to stop 'ora.rac02.vip' on 'rac02' CRS-2677: Stop of 'ora.scan2.vip' on 'rac02' succeeded CRS-2677: Stop of 'ora.rac02.vip' on 'rac02' succeeded CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'rac03' succeeded CRS-2673: Attempting to stop 'ora.scan1.vip' on 'rac03' CRS-2677: Stop of 'ora.scan3.vip' on 'rac02' succeeded CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac03' succeeded CRS-2673: Attempting to stop 'ora.rac01.vip' on 'rac03' CRS-2677: Stop of 'ora.scan1.vip' on 'rac03' succeeded CRS-2677: Stop of 'ora.rac01.vip' on 'rac03' succeeded CRS-2677: Stop of 'ora.oc4j' on 'rac02' succeeded CRS-2677: Stop of 'ora.cvu' on 'rac02' succeeded CRS-2677: Stop of 'ora.DATA.dg' on 'rac02' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'rac02' CRS-2677: Stop of 'ora.DATA.dg' on 'rac03' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'rac03' CRS-2677: Stop of 'ora.asm' on 'rac03' succeeded CRS-2677: Stop of 'ora.asm' on 'rac02' succeeded CRS-2673: Attempting to stop 'ora.ons' on 'rac03' CRS-2677: Stop of 'ora.ons' on 'rac03' succeeded CRS-2673: Attempting to stop 'ora.net1.network' on 'rac03' CRS-2677: Stop of 'ora.net1.network' on 'rac03' succeeded CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac03' has completed CRS-2673: Attempting to stop 'ora.ons' on 'rac02' CRS-2677: Stop of 'ora.ons' on 'rac02' succeeded CRS-2673: Attempting to stop 'ora.net1.network' on 'rac02' CRS-2677: Stop of 'ora.net1.network' on 'rac02' succeeded CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac02' has completed CRS-2677: Stop of 'ora.crsd' on 'rac03' succeeded CRS-2673: Attempting to stop 'ora.ctssd' on 'rac03' CRS-2673: Attempting to stop 'ora.evmd' on 'rac03' CRS-2673: Attempting to stop 'ora.asm' on 'rac03' CRS-2677: Stop of 'ora.crsd' on 'rac02' succeeded CRS-2673: Attempting to stop 'ora.ctssd' on 'rac02' CRS-2673: Attempting to stop 'ora.evmd' on 'rac02' CRS-2673: Attempting to stop 'ora.asm' on 'rac02' CRS-2677: Stop of 'ora.evmd' on 'rac03' succeeded CRS-2677: Stop of 'ora.evmd' on 'rac02' succeeded CRS-2677: Stop of 'ora.ctssd' on 'rac03' succeeded CRS-2677: Stop of 'ora.ctssd' on 'rac02' succeeded CRS-2677: Stop of 'ora.asm' on 'rac03' succeeded CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac03' CRS-2677: Stop of 'ora.asm' on 'rac02' succeeded CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'rac02' CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac02' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'rac02' CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'rac03' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'rac03' CRS-2677: Stop of 'ora.cssd' on 'rac02' succeeded CRS-2677: Stop of 'ora.cssd' on 'rac03' succeeded
[root@rac02 ~]# ps -ef|grep grid
root 2253 1 1 09:02 ? 00:01:02 /u01/app/11.2.0.3/grid/bin/ohasd.bin reboot grid 2491 1 0 09:02 ? 00:00:24 /u01/app/11.2.0.3/grid/bin/oraagent.bin grid 2503 1 0 09:02 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/mdnsd.bin grid 2513 1 0 09:02 ? 00:00:03 /u01/app/11.2.0.3/grid/bin/gpnpd.bin grid 2524 1 0 09:02 ? 00:00:18 /u01/app/11.2.0.3/grid/bin/gipcd.bin root 2537 1 0 09:02 ? 00:00:28 /u01/app/11.2.0.3/grid/bin/orarootagent.bin root 2549 1 1 09:02 ? 00:01:19 /u01/app/11.2.0.3/grid/bin/osysmond.bin root 3367 1 1 09:04 ? 00:00:54 /u01/app/11.2.0.3/grid/bin/ologgerd -M -d /u01/app/11.2.0.3/grid/crf/db/rac02 root 8324 8022 0 10:28 pts/0 00:00:00 grep grid
[root@rac02 ~]# /u01/app/11.2.0.3/grid/bin/crsctl status server
CRS-4535: Cannot communicate with Cluster Ready Services CRS-4000: Command Status failed, or completed with errors.
[root@rac02 ~]# /u01/app/11.2.0.3/grid/bin/crsctl check crs
CRS-4638: Oracle High Availability Services is online CRS-4535: Cannot communicate with Cluster Ready Services CRS-4530: Communications failure contacting Cluster Synchronization Services daemon CRS-4534: Cannot communicate with Event Manager
[root@rac02 ~]# /u01/app/11.2.0.3/grid/bin/crsctl start cluster -all
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac02' CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac03' CRS-2676: Start of 'ora.cssdmonitor' on 'rac02' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'rac02' CRS-2672: Attempting to start 'ora.diskmon' on 'rac02' CRS-2676: Start of 'ora.cssdmonitor' on 'rac03' succeeded CRS-2672: Attempting to start 'ora.cssd' on 'rac03' CRS-2672: Attempting to start 'ora.diskmon' on 'rac03' CRS-2676: Start of 'ora.diskmon' on 'rac02' succeeded CRS-2676: Start of 'ora.diskmon' on 'rac03' succeeded CRS-2676: Start of 'ora.cssd' on 'rac02' succeeded CRS-2672: Attempting to start 'ora.ctssd' on 'rac02' CRS-2676: Start of 'ora.cssd' on 'rac03' succeeded CRS-2672: Attempting to start 'ora.ctssd' on 'rac03' CRS-2676: Start of 'ora.ctssd' on 'rac02' succeeded CRS-2672: Attempting to start 'ora.evmd' on 'rac02' CRS-2676: Start of 'ora.ctssd' on 'rac03' succeeded CRS-2672: Attempting to start 'ora.evmd' on 'rac03' CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac02' CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac03' CRS-2676: Start of 'ora.evmd' on 'rac02' succeeded CRS-2676: Start of 'ora.evmd' on 'rac03' succeeded CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rac02' succeeded CRS-2672: Attempting to start 'ora.asm' on 'rac02' CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rac03' succeeded CRS-2672: Attempting to start 'ora.asm' on 'rac03' CRS-2676: Start of 'ora.asm' on 'rac02' succeeded CRS-2672: Attempting to start 'ora.crsd' on 'rac02' CRS-2676: Start of 'ora.asm' on 'rac03' succeeded CRS-2672: Attempting to start 'ora.crsd' on 'rac03' CRS-2676: Start of 'ora.crsd' on 'rac02' succeeded CRS-2676: Start of 'ora.crsd' on 'rac03' succeeded
[root@rac02 ~]# /u01/app/11.2.0.3/grid/bin/crsctl check cluster -all
************************************************************** rac02: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** rac03: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online **************************************************************
[root@rac02 ~]# /u01/app/11.2.0.3/grid/bin/crsctl status server
NAME=rac02 STATE=ONLINE NAME=rac03 STATE=ONLINE
[root@rac01 ~]# ps -ef|grep grid
root 11599 2537 0 10:46 pts/0 00:00:00 grep grid [root@rac01 ~]# ssh rac02 /u01/app/11.2.0.3/grid/bin/crsctl status server
NAME=rac02 STATE=ONLINE NAME=rac03 STATE=ONLINE
[root@rac01 ~]# /u01/app/11.2.0.3/grid/bin/crsctl start crs
CRS-4123: Oracle High Availability Services has been started. [root@rac01 ~]# /u01/app/11.2.0.3/grid/bin/crsctl status server
CRS-4535: Cannot communicate with Cluster Ready Services CRS-4000: Command Status failed, or completed with errors.
[root@rac01 ~]# /u01/app/11.2.0.3/grid/bin/crsctl status server
CRS-4535: Cannot communicate with Cluster Ready Services CRS-4000: Command Status failed, or completed with errors.
[root@rac01 ~]# /u01/app/11.2.0.3/grid/bin/crsctl start cluster
CRS-4691: Oracle Clusterware is already running CRS-4000: Command Start failed, or completed with errors.
[root@rac01 ~]# /u01/app/11.2.0.3/grid/bin/crsctl check cluster -all
************************************************************** rac01: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** rac02: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online ************************************************************** rac03: CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online **************************************************************
[root@rac01 ~]# /u01/app/11.2.0.3/grid/bin/crsctl status server
NAME=rac01 STATE=ONLINE NAME=rac02 STATE=ONLINE NAME=rac03 STATE=ONLINE
Now, stop and start is making me tired. Now I get to spend – go shopping.
New Features – Pages
Please take a look at Menu (all the way to the right) and you will find:
Linux
Patches and Upgrades
Information is good, but how fast can you find it?
Changed from Pages to Menu.
Disk Performance Benchmark
Where should I put my ASM disks?
-----------------------------------------------------------------------
CrystalDiskMark 3.0.2 x64 (C) 2007-2013 hiyohiyo
Crystal Dew World : http://crystalmark.info/
-----------------------------------------------------------------------
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]
Sequential Read : 194.352 MB/s
Sequential Write : 194.577 MB/s
Random Read 512KB : 72.506 MB/s
Random Write 512KB : 123.474 MB/s
Random Read 4KB (QD=1) : 0.938 MB/s [ 229.0 IOPS]
Random Write 4KB (QD=1) : 2.777 MB/s [ 678.0 IOPS]
Random Read 4KB (QD=32) : 2.689 MB/s [ 656.5 IOPS]
Random Write 4KB (QD=32) : 2.917 MB/s [ 712.0 IOPS]
Test : 4000 MB [E: 11.0% (51.4/465.8 GB)] (x5)
Date : 2013/04/20 19:24:20
OS : Windows 7 Ultimate Edition SP1 [6.1 Build 7601] (x64)
WD VelociRaptor 10K RPM 500GB
-----------------------------------------------------------------------
CrystalDiskMark 3.0.2 x64 (C) 2007-2013 hiyohiyo
Crystal Dew World : http://crystalmark.info/
-----------------------------------------------------------------------
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]
Sequential Read : 320.813 MB/s
Sequential Write : 325.493 MB/s
Random Read 512KB : 48.393 MB/s
Random Write 512KB : 70.716 MB/s
Random Read 4KB (QD=1) : 0.543 MB/s [ 132.6 IOPS]
Random Write 4KB (QD=1) : 3.107 MB/s [ 758.7 IOPS]
Random Read 4KB (QD=32) : 2.531 MB/s [ 618.0 IOPS]
Random Write 4KB (QD=32) : 3.125 MB/s [ 762.9 IOPS]
Test : 4000 MB [D: 7.1% (47.8/675.5 GB)] (x5)
Date : 2013/04/20 19:36:23
OS : Windows 7 Ultimate Edition SP1 [6.1 Build 7601] (x64)
WD Black 7200K RPM 500GB Intel RST RAID0 500GB
-----------------------------------------------------------------------
CrystalDiskMark 3.0.2 x64 (C) 2007-2013 hiyohiyo
Crystal Dew World : http://crystalmark.info/
-----------------------------------------------------------------------
* MB/s = 1,000,000 byte/s [SATA/300 = 300,000,000 byte/s]
Sequential Read : 133.935 MB/s
Sequential Write : 112.881 MB/s
Random Read 512KB : 34.773 MB/s
Random Write 512KB : 31.217 MB/s
Random Read 4KB (QD=1) : 0.461 MB/s [ 112.5 IOPS]
Random Write 4KB (QD=1) : 1.329 MB/s [ 324.5 IOPS]
Random Read 4KB (QD=32) : 2.113 MB/s [ 516.0 IOPS]
Random Write 4KB (QD=32) : 1.353 MB/s [ 330.3 IOPS]
Test : 4000 MB [C: 27.3% (34.9/127.9 GB)] (x5)
Date : 2013/04/20 19:55:37
OS : Windows 7 Ultimate Edition SP1 [6.1 Build 7601] (x64)
WD Black 7200K RPM 500GB Intel RST RAID1 5OOGB
DNS for RAC on VirtualBox
Just completed RAC training and wanted to put into practice.
SCAN IPs MUST NOT be in the /etc/hosts file because it will result in only 1 SCAN IP for the entire cluster.
I started looking for simple DNS server to be used with RAC and came across dnsmasq.
LINUX VERSION
[root@rac01 ~]# uname -an Linux rac01.localdomain 2.6.39-200.24.1.el6uek.x86_64 #1 SMP Sat Jun 23 02:39:07 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
HOSTS
[root@rac01 ~]# cat /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 #NAT (VBox) #eth0 (DHCP) #Internal Networking (VBox) #Private eth2 10.10.10.11 rac01-priv rac01-priv.localdomain 10.10.10.12 rac02-priv rac02-priv.localdoamin #Host Networking (VBox) #Public eth1 192.168.56.11 rac01 rac01.localdomain 192.168.56.12 rac02 rac02.localdomain #Virtual IP 192.168.56.21 rac01-vip rac01-vip.localdomain 192.168.56.22 rac02-vip rac02-vip.localdomain #SCAN IP #192.168.56.31 scan scan.localdomain #192.168.56.32 scan scan.localdomain #192.168.56.33 scan scan.localdomain [root@rac01 ~]#
INSTALATION
yum install dnsmasq
VERIFY INSTALLATION
[root@rac01 ~]# yum list dnsmasq Loaded plugins: refresh-packagekit, security Installed Packages dnsmasq.x86_64 2.48-13.el6 @ol6_latest [root@rac01 ~]# rpm -qa dnsmasq dnsmasq-2.48-13.el6.x86_64
ADD IP to resolv.conf MUST BE FIRST LINE
[root@rac01 ~]# cat /etc/resolv.conf # Generated by NetworkManager nameserver 192.168.56.11 domain gateway.2wire.net search gateway.2wire.net localdomain nameserver 172.16.0.1
WRITE PROTECTING /etc/resolv.conf FILE – resolve.conf changed on reboot due to DHCP
chattr +i /etc/resolv.conf
CONFIGURE dnsmasq
[root@rac01 ~]# cat /etc/dnsmasq.conf listen-address=192.168.56.11 port=53 no-resolv domain-needed bogus-priv dns-forward-max=150 cache-size=1000 neg-ttl=3600 no-poll no-hosts addn-hosts=/etc/racdns
CREATE DNS ENTRIES
[root@rac01 ~]# cat /etc/racdns 192.168.56.31 scan scan.localdomain 192.168.56.32 scan scan.localdomain 192.168.56.33 scan scan.localdomain 192.168.56.11 rac01 rac01.localdomain 192.168.56.12 rac02 rac02.localdomain 192.168.56.21 rac01-vip rac01-vip.localdomain 192.168.56.22 rac02-vip rac02-vip.localdomain
TEST NSLOOKUP
[root@rac01 ~]# nslookup scan Server: 192.168.56.11 Address: 192.168.56.11#53 Name: scan.localdomain Address: 192.168.56.33 Name: scan.localdomain Address: 192.168.56.31 Name: scan.localdomain Address: 192.168.56.32 [root@rac01 ~]# nslookup scan Server: 192.168.56.11 Address: 192.168.56.11#53 Name: scan.localdomain Address: 192.168.56.31 Name: scan.localdomain Address: 192.168.56.32 Name: scan.localdomain Address: 192.168.56.33 [root@rac01 ~]# nslookup scan Server: 192.168.56.11 Address: 192.168.56.11#53 Name: scan.localdomain Address: 192.168.56.32 Name: scan.localdomain Address: 192.168.56.33 Name: scan.localdomain Address: 192.168.56.31 [root@rac01 ~]# nslookup rac01 Server: 192.168.56.11 Address: 192.168.56.11#53 Name: rac01.localdomain Address: 192.168.56.11 [root@rac01 ~]# nslookup rac02 Server: 192.168.56.11 Address: 192.168.56.11#53 Name: rac02.localdomain Address: 192.168.56.12 [root@rac01 ~]# nslookup rac01-vip Server: 192.168.56.11 Address: 192.168.56.11#53 Name: rac01-vip.localdomain Address: 192.168.56.21 [root@rac01 ~]# nslookup rac02-vip Server: 192.168.56.11 Address: 192.168.56.11#53 Name: rac02-vip.localdomain Address: 192.168.56.22
ADD SERVICE RESTART
[root@rac01 ~]# /sbin/chkconfig dnsmasq on [root@rac01 ~]# /sbin/chkconfig dnsmasq --list dnsmasq 0:off 1:off 2:on 3:on 4:on 5:on 6:on
MANUAL SERVICE RESTART
[root@rac01 ~]# /etc/init.d/dnsmasq restart Shutting down dnsmasq: [ OK ] Starting dnsmasq: [ OK ]
Hard Coding 101
Just don’t do it!
I must be naive, but is there a reason prefix schema name for objects owned by the same owner?
There’s no harm if there is never a need to migrate the data from one schema to another. But it’s a night mare when there is.
Here’s a simple analogy. It’s like getting a tattoo of your first girl friend Jane and end up marrying Jill. It’s a night mare.
Here is a test case
LAX:(MDINH@db01)> create or replace trigger mdinh.trig_t before insert on mdinh.t begin null; end;
2 /
Trigger created.
LAX:(MDINH@db01)>
[oracle@lax:db01]/home/oracle
$ expdp logfile=t.log schemas=mdinh include=trigger dumpfile=t.dmp directory=DATA_PUMP_DIR
Export: Release 11.2.0.3.0 - Production on Wed Mar 20 21:27:10 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA logfile=t.log schemas=mdinh include=trigger dumpfile=t.dmp directory=DATA_PUMP_DIR
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/tmp/t.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:27:25
[oracle@lax:db01]/home/oracle
$ impdp schemas=mdinh remap_schema=mdinh:scott include=trigger sqlfile=trigger_ddl.sql dumpfile=t.dmp directory=DATA_PUMP_DIR
Import: Release 11.2.0.3.0 - Production on Wed Mar 20 21:29:22 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_SCHEMA_01": /******** AS SYSDBA schemas=mdinh remap_schema=mdinh:scott include=trigger sqlfile=trigger_ddl.sql dumpfile=t.dmp directory=DATA_PUMP_DIR
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYS"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 21:29:27
[oracle@lax:db01]/home/oracle
$ cat /tmp/trigger_ddl.sql
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TRIGGER
-- CONNECT SCOTT
CREATE TRIGGER "SCOTT"."TRIG_T" before insert on mdinh.t begin null; end;
/
ALTER TRIGGER "SCOTT"."TRIG_T" ENABLE;
ALTER TRIGGER "SCOTT"."TRIG_T"
COMPILE
PLSQL_OPTIMIZE_LEVEL= 2
PLSQL_CODE_TYPE= INTERPRETED PLSCOPE_SETTINGS= 'IDENTIFIERS:NONE'
;
[oracle@lax:db01]/home/oracle
$
Solve the mystery – Database crash without a clue
So there I was, troubleshooting root cause for database crash without a clue.
There are no trace files and no core dumps. The alert log is shown below?
What do you think happened?
Mar 18 20:50:08 2013 Thread 1 advanced to log sequence 5 (LGWR switch) Current log# 2 seq# 5 mem# 0: /oracle/flashrecovery/LAX_DB01/onlinelog/o1_mf_2_8nctxzw9_.log Mon Mar 18 20:50:09 2013 Archived Log entry 56 added for thread 1 sequence 4 ID 0x55ed081e dest 1: Mon Mar 18 20:55:18 2013 Adjusting the default value of parameter parallel_max_servers from 40 to 35 due to the value of parameter processes (50) Starting ORACLE instance (normal) Mon Mar 18 20:55:27 2013 ALTER DATABASE OPEN Beginning crash recovery of 1 threads Started redo scan Completed redo scan read 49 KB redo, 38 data blocks need recovery Started redo application at Thread 1: logseq 4, block 3 Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0 Mem# 0: /oracle/flashrecovery/LAX_DB01/onlinelog/o1_mf_1_8nctxrsl_.log Recovery of Online Redo Log: Thread 1 Group 2 Seq 5 Reading mem 0 Mem# 0: /oracle/flashrecovery/LAX_DB01/onlinelog/o1_mf_2_8nctxzw9_.log Completed redo application of 0.04MB Completed crash recovery at Thread 1: logseq 5, block 3, scn 642127 38 data blocks read, 38 data blocks written, 49 redo k-bytes read LGWR: STARTING ARCH PROCESSES
[oracle@lax:db01]/u01/app/oracle/diag/rdbms/lax_db01/db01/trace
$ last reboot|head -1
reboot system boot 2.6.39-200.24.1. Mon Mar 18 20:53 – 21:12 (00:19)
The system was rebooted !!!
Oracle Move objects to another tablespace
Move objects to another tablespace for tables, indexes, lobs, iots.
Table with LONG datatype can only be moved using export and import.
Note:
DBA_INDEXES – IOT1 – organization index tablespace users overflow tablespace ndat
DBA_TABLES – IOT2 – organization index tablespace ndat overflow tablespace users
Example below is to move all objects out of USERS tablespace.
LAX:(MDINH@db01)> @c
Table dropped.
Table dropped.
Table dropped.
Table dropped.
Table dropped.
LAX:(MDINH@db01)> create table longt (id number, name long, junk raw(16)) tablespace users;
Table created.
LAX:(MDINH@db01)> create table tab(id int, name varchar2(30)) tablespace users;
Table created.
LAX:(MDINH@db01)> create bitmap index bm on tab(id);
Index created.
LAX:(MDINH@db01)> create table iot1(id int, name varchar2(30), constraint pk1 primary key (id))
2 organization index tablespace users overflow tablespace ndat;
Table created.
LAX:(MDINH@db01)> create table iot2(id int, name varchar2(30), constraint pk2 primary key (id))
2 organization index tablespace ndat overflow tablespace users;
Table created.
LAX:(MDINH@db01)> create table lob (id number, picture blob, resume clob)
2 lob (picture) store as picturelobs (tablespace users index (tablespace users))
3 lob (resume) store as (tablespace users index resumeidx (tablespace users));
Table created.
LAX:(MDINH@db01)> create index x on lob(id) tablespace users;
Index created.
LAX:(MDINH@db01)> create or replace procedure p( p_id in int, p_resume in varchar2 )
2 as
3 begin
4 insert into lob(id,resume) values (p_id,p_resume);
5 commit;
6 end;
7 /
Procedure created.
LAX:(MDINH@db01)> exec p(1,rpad('*',32767,'*') );
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> exec p(1,rpad('*',32767,'*') );
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> exec p(1,rpad('*',32767,'*') );
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> @o
LAX:(MDINH@db01)> set lines 130
LAX:(MDINH@db01)> col column_name for a30
LAX:(MDINH@db01)> col nm for a30
LAX:(MDINH@db01)> col tn for a30
LAX:(MDINH@db01)> set echo on
LAX:(MDINH@db01)> select owner||'.'||table_name nm, iot_name, iot_type from dba_tables where tablespace_name='USERS';
NM IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ------------
MDINH.LONGT
MDINH.TAB
MDINH.SYS_IOT_OVER_15444 IOT2 IOT_OVERFLOW
MDINH.LOB
LAX:(MDINH@db01)> select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS';
NM TN INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
MDINH.PK1 MDINH.IOT1 IOT - TOP
MDINH.RESUMEIDX MDINH.LOB LOB
MDINH.SYS_IL0000015447C00002$$ MDINH.LOB LOB
MDINH.X MDINH.LOB NORMAL
MDINH.BM MDINH.TAB BITMAP
LAX:(MDINH@db01)> select owner||'.'||table_name nm, column_name, index_name, segment_name from dba_lobs where tablespace_name='USERS';
NM COLUMN_NAME INDEX_NAME SEGMENT_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
MDINH.LOB RESUME RESUMEIDX SYS_LOB0000015447C00003$$
MDINH.LOB PICTURE SYS_IL0000015447C00002$$ PICTURELOBS
LAX:(MDINH@db01)> @m
LAX:(MDINH@db01)> set serverout on
LAX:(MDINH@db01)> -- MOVE LOB FIRST
LAX:(MDINH@db01)> declare
2 l_sql varchar2(200);
3 begin
4 for x in (
5 select owner||'.'||table_name nm, column_name, index_name, segment_name from dba_lobs where tablespace_name='USERS'
6 ) loop
7 begin
8 l_sql := 'alter table '||x.nm||' move lob('||x.column_name||') store as '||x.segment_name||' (tablespace nlob)';
9 execute immediate l_sql;
10 -- dbms_output.put_line(l_sql);
11 exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
12 end;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> -- MOVE IOT FROM DBA_TABLES
LAX:(MDINH@db01)> declare
2 l_sql varchar2(200);
3 begin
4 for x in (
5 select owner||'.'||iot_name nm from dba_tables where tablespace_name='USERS' and iot_name is not null
6 ) loop
7 begin
8 l_sql := 'alter table '||x.nm||' move tablespace ndat overflow tablespace ndat';
9 execute immediate l_sql;
10 -- dbms_output.put_line(l_sql);
11 exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
12 end;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> -- MOVE IOT FROM DBA_INDEXES
LAX:(MDINH@db01)> declare
2 l_sql varchar2(200);
3 begin
4 for x in (
5 select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS' and index_type in ('IOT - TOP')
6 ) loop
7 begin
8 l_sql := 'alter table '||x.tn||' move tablespace ndat overflow tablespace ndat';
9 execute immediate l_sql;
10 -- dbms_output.put_line(l_sql);
11 exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
12 end;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> -- MOVE TABLES
LAX:(MDINH@db01)> declare
2 l_sql varchar2(200);
3 begin
4 for x in (
5 select owner||'.'||table_name nm from dba_tables where tablespace_name='USERS' and iot_name is null
6 ) loop
7 begin
8 l_sql := 'alter table '||x.nm||' move tablespace ndat';
9 execute immediate l_sql;
10 -- dbms_output.put_line(l_sql);
11 exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
12 end;
13 end loop;
14 end;
15 /
!!!!! ERROR: alter table MDINH.LONGT move tablespace ndat
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> -- MOVE INDEXES
LAX:(MDINH@db01)> declare
2 l_sql varchar2(200);
3 begin
4 for x in (
5 select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS' and index_type in ('NORMAL','BITMAP')
6 ) loop
7 begin
8 l_sql := 'alter index '||x.nm||' rebuild tablespace ndat';
9 execute immediate l_sql;
10 -- dbms_output.put_line(l_sql);
11 exception when others then dbms_output.put_line('!!!!! ERROR: '||l_sql);
12 end;
13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> @@o.sql
LAX:(MDINH@db01)> set lines 130
LAX:(MDINH@db01)> col column_name for a30
LAX:(MDINH@db01)> col nm for a30
LAX:(MDINH@db01)> col tn for a30
LAX:(MDINH@db01)> set echo on
LAX:(MDINH@db01)> select owner||'.'||table_name nm, iot_name, iot_type from dba_tables where tablespace_name='USERS';
NM IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ------------
MDINH.LONGT
LAX:(MDINH@db01)> select owner||'.'||index_name nm, table_owner||'.'||table_name tn, index_type from dba_indexes where tablespace_name='USERS';
no rows selected
LAX:(MDINH@db01)> select owner||'.'||table_name nm, column_name, index_name, segment_name from dba_lobs where tablespace_name='USERS';
no rows selected
LAX:(MDINH@db01)>
port forwarding with plink
Plink (a command-line interface to the PuTTY back ends)
C:\Users\dinh>plink
PuTTY Link: command-line connection utility
0.62 x64 Built by Tad DeVries http://splunk.net
Usage: plink [options] [user@]host [command]
("host" can also be a PuTTY saved session name)
Options:
-V print version information and exit
-pgpfp print PGP key fingerprints and exit
-v show verbose messages
-load sessname Load settings from saved session
-ssh -telnet -rlogin -raw -serial
force use of a particular protocol
-P port connect to specified port
-l user connect with specified username
-batch disable all interactive prompts
The following options only apply to SSH connections:
-pw passw login with specified password
-D [listen-IP:]listen-port
Dynamic SOCKS-based port forwarding
-L [listen-IP:]listen-port:host:port
Forward local port to remote address
-R [listen-IP:]listen-port:host:port
Forward remote port to local address
-X -x enable / disable X11 forwarding
-A -a enable / disable agent forwarding
-t -T enable / disable pty allocation
-1 -2 force use of particular protocol version
-4 -6 force use of IPv4 or IPv6
-C enable compression
-i key private key file for authentication
-noagent disable use of Pageant
-agent enable use of Pageant
-m file read remote command(s) from file
-s remote command is an SSH subsystem (SSH-2 only)
-N don't start a shell/command (SSH-2 only)
-nc host:port
open tunnel in place of session (SSH-2 only)
-sercfg configuration-string (e.g. 19200,8,n,1,X)
Specify the serial configuration (serial only)
Configuration:
Oracle RDBMS on Virtual Box using NAT(eth0) and Host Only Networking(eth1) listening on port 1531.
Windows7 Host:Ethernet adapter VirtualBox Host-Only Network:
Connection-specific DNS Suffix . :
Description . . . . . . . . . . . : VirtualBox Host-Only Ethernet Adapter
Physical Address. . . . . . . . . : 08-00-27-00-8C-10
DHCP Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
Link-local IPv6 Address . . . . . : fe80::84bc:48b3:8e40:e0d1%42(Preferred)
Autoconfiguration IPv4 Address. . : 169.254.224.209(Preferred)
Subnet Mask . . . . . . . . . . . : 255.255.0.0
Default Gateway . . . . . . . . . :
DHCPv6 IAID . . . . . . . . . . . : 638058535
DHCPv6 Client DUID. . . . . . . . : 00-01-00-01-18-87-4B-A4-D4-BE-D9-60-5A-3F
DNS Servers . . . . . . . . . . . : fec0:0:0:ffff::1%1
fec0:0:0:ffff::2%1
fec0:0:0:ffff::3%1
NetBIOS over Tcpip. . . . . . . . : Enabled
OEL 6.3 Guest:
[oracle@lax:db01]/home/oracle
$ ifconfig -a
eth0 Link encap:Ethernet HWaddr 08:00:27:CC:FF:D7
inet addr:10.0.2.15 Bcast:10.0.2.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fecc:ffd7/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:74 errors:0 dropped:0 overruns:0 frame:0
TX packets:95 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:24907 (24.3 KiB) TX bytes:9397 (9.1 KiB)
eth1 Link encap:Ethernet HWaddr 08:00:27:2C:2E:24
inet addr:169.254.224.210 Bcast:169.254.255.255 Mask:255.255.0.0
inet6 addr: fe80::a00:27ff:fe2c:2e24/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:1049 errors:0 dropped:0 overruns:0 frame:0
TX packets:853 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:157877 (154.1 KiB) TX bytes:150602 (147.0 KiB)
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:2206 errors:0 dropped:0 overruns:0 frame:0
TX packets:2206 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:331342 (323.5 KiB) TX bytes:331342 (323.5 KiB)
LISTENER port 1531
[oracle@lax:db01]/home/oracle $ lsnrctl status listener_db01 LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 10-MAR-2013 20:29:52 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=lax.localdomain)(PORT=1531)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767))) STATUS of the LISTENER ------------------------ Alias listener_db01 Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 10-MAR-2013 19:56:07 Uptime 0 days 0 hr. 33 min. 44 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0.3/db_1/network/admin/listener.ora Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lax)(PORT=1531))) Services Summary... Service "lax_db01" has 2 instance(s). Instance "db01", status UNKNOWN, has 1 handler(s) for this service... Instance "db01", status READY, has 1 handler(s) for this service... The command completed successfullyPort forwarding – connecting to server using IP address: 169.254.224.210
C:\Users\dinh>plink -batch -v -x -a -T -C -noagent -ssh -L 127.0.0.1:1521:169.254.224.210:1531 oracle@169.254.224.210 -pw oracle Looking up host "169.254.224.210" Connecting to 169.254.224.210 port 22 Server version: SSH-2.0-OpenSSH_5.3 Using SSH protocol version 2 We claim version: SSH-2.0-PuTTY_Local:_Dec_15_2011_21:59:21 Server supports delayed compression; will try this later Doing Diffie-Hellman group exchange Doing Diffie-Hellman key exchange with hash SHA-256 Host key fingerprint is: ssh-rsa 2048 0f:4f:4c:21:99:0a:41:f3:bf:28:8c:e2:c9:1c:45:f6 Initialised AES-256 SDCTR client->server encryption Initialised HMAC-SHA1 client->server MAC algorithm Initialised AES-256 SDCTR server->client encryption Initialised HMAC-SHA1 server->client MAC algorithm Using username "oracle". Using SSPI from SECUR32.DLL Attempting GSSAPI authentication GSSAPI authentication request refused Sent password Access granted Initiating key re-exchange (enabling delayed compression) Doing Diffie-Hellman group exchange Doing Diffie-Hellman key exchange with hash SHA-256 Initialised AES-256 SDCTR client->server encryption Initialised HMAC-SHA1 client->server MAC algorithm Initialised zlib (RFC1950) compression Initialised AES-256 SDCTR server->client encryption Initialised HMAC-SHA1 server->client MAC algorithm Initialised zlib (RFC1950) decompression Opened channel for session Local port 127.0.0.1:1521 forwarding to 169.254.224.210:1531 Started a shell/command Opening forwarded connection to 169.254.224.210:1531 Nothing left to send, closing channel Forwarded port closed Opening forwarded connection to 169.254.224.210:1531 Nothing left to send, closing channel Forwarded port closed ^CPort forwarding – connecting to server using PuTTY Saved Session: vm-lax_db01
Note: the IP address of the server used to forward port still needs to be listed unless the IP is added to hosts file.
C:\Users\dinh>plink -v -x -a -T -C -noagent -ssh -L 127.0.0.1:1521:169.254.224.210:1531 oracle@vm-lax_db01 -pw oracle Looking up host "169.254.224.210" Connecting to 169.254.224.210 port 22 Server version: SSH-2.0-OpenSSH_5.3 Using SSH protocol version 2 We claim version: SSH-2.0-PuTTY_Local:_Dec_15_2011_21:59:21 Server supports delayed compression; will try this later Doing Diffie-Hellman group exchange Doing Diffie-Hellman key exchange with hash SHA-256 Host key fingerprint is: ssh-rsa 2048 0f:4f:4c:21:99:0a:41:f3:bf:28:8c:e2:c9:1c:45:f6 Initialised AES-256 SDCTR client->server encryption Initialised HMAC-SHA1 client->server MAC algorithm Initialised AES-256 SDCTR server->client encryption Initialised HMAC-SHA1 server->client MAC algorithm Using username "oracle". Using SSPI from SECUR32.DLL Attempting GSSAPI authentication GSSAPI authentication request refused Sent password Access granted Initiating key re-exchange (enabling delayed compression) Doing Diffie-Hellman group exchange Doing Diffie-Hellman key exchange with hash SHA-256 Initialised AES-256 SDCTR client->server encryption Initialised HMAC-SHA1 client->server MAC algorithm Initialised zlib (RFC1950) compression Initialised AES-256 SDCTR server->client encryption Initialised HMAC-SHA1 server->client MAC algorithm Initialised zlib (RFC1950) decompression Opened channel for session Local port 127.0.0.1:1521 forwarding to 169.254.224.210:1531 Started a shell/command Opening forwarded connection to 169.254.224.210:1531 Nothing left to send, closing channel Forwarded port closedConnect using SQL*Plus
C:\Users\dinh>sqlplus mdinh/mdinh@127.0.0.1:1521/lax_db01 SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 11 00:00:14 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name from v$instance; INSTANCE_NAME ---------------- db01 SQL> select db_unique_name from v$database; DB_UNIQUE_NAME ------------------------------ lax_db01 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Linux Tips and Tricks
find with regex
Version tested:
$ uname -a Linux lax.localdomain 2.6.39-200.24.1.el6uek.x86_64 #1 SMP Sat Jun 23 02:39:07 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
Find 1 directory below admin, directories adump, bdump, cdump, udump
File extensions: .aud, .trc, .trm
File size: zero byte
File name: sbtio.log
Print files deleted older that 1 day.
find /u01/app/oracle/admin/*/[abcu]dump -type f \( -name “*\.aud” -o -name “*\.tr[cm]” -o -size 0c -o -name “sbtio.log” \) -mtime +1 -print -exec rm {} \;
Example:
[oracle@lax:db01]/home/oracle
$ find /u01/app/oracle/admin/*/[abcu]dump -type f \( -name "*\.aud" -o -name "*\.tr[cm]" -o -size 0c -o -name "sbtio.log" \) -mtime +1 -print -exec rm {} \;
/u01/app/oracle/admin/db01/adump/db01_ora_1927_1.aud
/u01/app/oracle/admin/db01/adump/db01_ora_1937_1.aud
/u01/app/oracle/admin/db01/adump/db01_ora_1960_1.aud
/u01/app/oracle/admin/db01/adump/db01_ora_1987_1.aud
/u01/app/oracle/admin/db01/bdump/test.trc
/u01/app/oracle/admin/db01/bdump/t.1
/u01/app/oracle/admin/db01/udump/my.trc
/u01/app/oracle/admin/db01/udump/t.2
Find 2 directories below rdbms, directory trace
find /u01/app/oracle/diag/rdbms/*/*/trace -type f \( -name “*\.tr[cm]” -o -size 0c -o -name “sbtio.log” \) -mtime +1 -print -exec rm {} \;
Example:
[oracle@lax:db01]/home/oracle
$ find /u01/app/oracle/diag/rdbms/*/*/trace -type f \( -name "*\.tr[cm]" -o -size 0c -o -name "sbtio.log" \) -mtime +1 -print -exec rm {} \;
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_vkrm_2033.trc
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_j001_2313.trm
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_arc0_2028.trm
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_vkrm_2305.trm
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_ckpt_2137.trc
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_dm00_2223.trc
/u01/app/oracle/diag/rdbms/lax_db01/db01/trace/db01_dbrm_1942.trm
Find and remove directory cdmp*, core*
find /u01/app/oracle/admin/*/[bc]dump -type d \( -name “cdmp*” -o -name “core*” \) -mtime +1 -print -exec rm -rf {} \;
Example:
[oracle@lax:db01]/home/oracle
$ find /u01/app/oracle/admin/*/[bc]dump -type d \( -name "cdmp*" -o -name "core*" \) -mtime +1 -print -exec rm -rf {} \;
/u01/app/oracle/admin/db01/cdump/cdmp
find: `/u01/app/oracle/admin/db01/cdump/cdmp': No such file or directory
/u01/app/oracle/admin/db01/cdump/core1
find: `/u01/app/oracle/admin/db01/cdump/core1': No such file or directory
Find and delete files NOT new than the provided file
find . \( ! -newer db01_ora_1937_2.aud \) -print -exec rm {} \;
Example:
[oracle@lax:db01]/u01/app/oracle/admin/adump
$ ls -alrt
total 376
-rw-r----- 1 oracle oinstall 763 Feb 27 22:26 db01_ora_6746_2.aud
-rw-r----- 1 oracle oinstall 763 Feb 27 22:30 db01_ora_6851_2.aud
-rw-r----- 1 oracle oinstall 769 Feb 27 22:30 db01_ora_6898_1.aud
-rw-r----- 1 oracle oinstall 970 Feb 28 07:09 db01_ora_6918_1.aud
-rw-r----- 1 oracle oinstall 765 Feb 28 07:11 db01_ora_31831_2.aud
-rw-r----- 1 oracle oinstall 771 Feb 28 07:11 db01_ora_31865_1.aud
-rw-r----- 1 oracle oinstall 775 Feb 28 07:11 db01_ora_31870_1.aud
-rw-r----- 1 oracle oinstall 763 Feb 28 09:44 db01_ora_1950_2.aud
-rw-r----- 1 oracle oinstall 769 Feb 28 09:44 db01_ora_1984_1.aud
-rw-r----- 1 oracle oinstall 773 Feb 28 09:44 db01_ora_1989_1.aud
drwxr-xr-x 4 oracle oinstall 4096 Feb 28 09:52 ..
-rw-r----- 1 oracle oinstall 763 Mar 2 22:11 db01_ora_1976_2.aud
-rw-r----- 1 oracle oinstall 769 Mar 2 22:11 db01_ora_2010_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:11 db01_ora_2015_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:22 db01_ora_2078_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:22 db01_ora_2079_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:22 db01_ora_2080_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:22 db01_ora_2081_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:22 db01_ora_2082_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:22 db01_ora_2083_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:22 db01_ora_2088_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:22 db01_ora_2089_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:24 db01_ora_2090_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:24 db01_ora_2091_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:24 db01_ora_2092_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2097_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2098_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2099_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2100_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2101_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2102_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2103_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2104_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2105_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2106_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:26 db01_ora_2107_1.aud
-rw-r----- 1 oracle oinstall 970 Mar 2 22:27 db01_ora_2111_1.aud
-rw-r----- 1 oracle oinstall 763 Mar 2 22:28 db01_ora_2112_2.aud
-rw-r----- 1 oracle oinstall 769 Mar 2 22:28 db01_ora_2146_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2152_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2180_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2181_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2182_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2183_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2184_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2185_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2190_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2191_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2192_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2193_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 2 22:28 db01_ora_2194_1.aud
-rw-r----- 1 oracle oinstall 763 Mar 4 07:15 db01_ora_1987_2.aud
-rw-r----- 1 oracle oinstall 768 Mar 4 07:15 db01_ora_2021_1.aud
-rw-r----- 1 oracle oinstall 968 Mar 4 07:16 db01_ora_2026_1.aud
-rw-r----- 1 oracle oinstall 763 Mar 4 20:27 db01_ora_1927_2.aud
-rw-r----- 1 oracle oinstall 769 Mar 4 20:27 db01_ora_1961_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 4 20:27 db01_ora_1966_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 4 20:29 db01_ora_2010_2.aud
-rw-r----- 1 oracle oinstall 970 Mar 4 22:37 db01_ora_2445_1.aud
-rw-r----- 1 oracle oinstall 763 Mar 5 22:01 db01_ora_1960_2.aud
-rw-r----- 1 oracle oinstall 769 Mar 5 22:01 db01_ora_1994_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 5 22:01 db01_ora_1999_1.aud
-rw-r----- 1 oracle oinstall 970 Mar 5 23:12 db01_ora_2160_1.aud
-rw-r----- 1 oracle oinstall 763 Mar 6 06:28 db01_ora_1937_2.aud
-rw-r----- 1 oracle oinstall 769 Mar 6 06:28 db01_ora_1971_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:28 db01_ora_1976_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:29 db01_ora_2022_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:31 db01_ora_2046_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:33 db01_ora_2063_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:35 db01_ora_2071_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:35 db01_ora_2073_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:45 db01_ora_2103_2.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:45 db01_ora_2111_2.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:46 db01_ora_2120_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:48 db01_ora_2132_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:52 db01_ora_2165_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:59 db01_ora_2189_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 07:00 db01_ora_2194_2.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 07:01 db01_ora_2219_1.aud
-rw-r----- 1 oracle oinstall 970 Mar 6 07:31 db01_ora_2260_1.aud
-rw-r----- 1 oracle oinstall 763 Mar 7 20:48 db01_ora_1956_2.aud
-rw-r----- 1 oracle oinstall 769 Mar 7 20:48 db01_ora_1990_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:48 db01_ora_1995_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:51 db01_ora_2045_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:53 db01_ora_2058_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:54 db01_ora_2067_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:56 db01_ora_2081_2.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:57 db01_ora_2094_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:59 db01_ora_2112_1.aud
-rw-r----- 1 oracle oinstall 970 Mar 7 21:45 db01_ora_2546_1.aud
-rw-r----- 1 oracle oinstall 763 Mar 9 19:32 db01_ora_2353_2.aud
-rw-r----- 1 oracle oinstall 769 Mar 9 19:32 db01_ora_2387_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 9 19:32 db01_ora_2392_1.aud
drwxr-xr-x 2 oracle oinstall 4096 Mar 9 19:32 .
[oracle@lax:db01]/u01/app/oracle/admin/adump
$ find . \( ! -newer db01_ora_1937_2.aud \) -print -exec rm {} \;
./db01_ora_2091_1.aud
./db01_ora_2160_1.aud
./db01_ora_31865_1.aud
./db01_ora_2015_1.aud
./db01_ora_2097_1.aud
./db01_ora_2180_1.aud
./db01_ora_2107_1.aud
./db01_ora_2191_1.aud
./db01_ora_6918_1.aud
./db01_ora_1976_2.aud
./db01_ora_2194_1.aud
./db01_ora_2090_1.aud
./db01_ora_2081_1.aud
./db01_ora_2192_1.aud
./db01_ora_2079_1.aud
./db01_ora_2152_1.aud
./db01_ora_2083_1.aud
./db01_ora_2182_1.aud
./db01_ora_2105_1.aud
./db01_ora_2104_1.aud
./db01_ora_1994_1.aud
./db01_ora_2181_1.aud
./db01_ora_1927_2.aud
./db01_ora_6851_2.aud
./db01_ora_2102_1.aud
./db01_ora_6746_2.aud
./db01_ora_2078_1.aud
./db01_ora_1960_2.aud
./db01_ora_1937_2.aud
./db01_ora_1989_1.aud
./db01_ora_2193_1.aud
./db01_ora_2082_1.aud
./db01_ora_2146_1.aud
./db01_ora_2183_1.aud
./db01_ora_2101_1.aud
./db01_ora_2184_1.aud
./db01_ora_2111_1.aud
./db01_ora_1984_1.aud
./db01_ora_1987_2.aud
./db01_ora_2112_2.aud
./db01_ora_31831_2.aud
./db01_ora_2021_1.aud
./db01_ora_2106_1.aud
./db01_ora_1966_1.aud
./db01_ora_2010_1.aud
./db01_ora_2103_1.aud
./db01_ora_2190_1.aud
./db01_ora_2089_1.aud
./db01_ora_2098_1.aud
./db01_ora_2088_1.aud
./db01_ora_2099_1.aud
./db01_ora_2080_1.aud
./db01_ora_31870_1.aud
./db01_ora_2185_1.aud
./db01_ora_2010_2.aud
./db01_ora_2092_1.aud
./db01_ora_2026_1.aud
./db01_ora_1950_2.aud
./db01_ora_6898_1.aud
./db01_ora_2100_1.aud
./db01_ora_1999_1.aud
./db01_ora_2445_1.aud
./db01_ora_1961_1.aud
[oracle@lax:db01]/u01/app/oracle/admin/adump
$ ls -alrt
total 124
drwxr-xr-x 4 oracle oinstall 4096 Feb 28 09:52 ..
-rw-r----- 1 oracle oinstall 769 Mar 6 06:28 db01_ora_1971_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:28 db01_ora_1976_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:29 db01_ora_2022_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:31 db01_ora_2046_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:33 db01_ora_2063_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:35 db01_ora_2071_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:35 db01_ora_2073_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:45 db01_ora_2103_2.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:45 db01_ora_2111_2.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:46 db01_ora_2120_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:48 db01_ora_2132_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:52 db01_ora_2165_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 06:59 db01_ora_2189_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 07:00 db01_ora_2194_2.aud
-rw-r----- 1 oracle oinstall 773 Mar 6 07:01 db01_ora_2219_1.aud
-rw-r----- 1 oracle oinstall 970 Mar 6 07:31 db01_ora_2260_1.aud
-rw-r----- 1 oracle oinstall 763 Mar 7 20:48 db01_ora_1956_2.aud
-rw-r----- 1 oracle oinstall 769 Mar 7 20:48 db01_ora_1990_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:48 db01_ora_1995_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:51 db01_ora_2045_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:53 db01_ora_2058_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:54 db01_ora_2067_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:56 db01_ora_2081_2.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:57 db01_ora_2094_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 7 20:59 db01_ora_2112_1.aud
-rw-r----- 1 oracle oinstall 970 Mar 7 21:45 db01_ora_2546_1.aud
-rw-r----- 1 oracle oinstall 763 Mar 9 19:32 db01_ora_2353_2.aud
-rw-r----- 1 oracle oinstall 769 Mar 9 19:32 db01_ora_2387_1.aud
-rw-r----- 1 oracle oinstall 773 Mar 9 19:32 db01_ora_2392_1.aud
drwxr-xr-x 2 oracle oinstall 4096 Mar 9 19:33 .
Resetting Sequences
I know what you are thinking. Why on earth would you reset a sequence? Let me tell you why.
Schema A ‘s tables are being refreshed from production to development database.
Schema A contains the data and references sequences in Schema B.
From the scheme for things, sequence > max(value) from table or sequence < max(value) from table.
max(value) from table:
LAX:(MDINH@db01)> select max(id) from t; 100
sequence > max(value):
LAX:(MDINH@db01)> select s.nextval from dual; 1978979888
Sequence reset:
LAX:(MDINH@db01)> DECLARE
l_seq NUMBER;
2 3 BEGIN
4 FOR x IN(
5 SELECT -1*(s.nextval-(SELECT MAX(id) FROM t)) seq FROM dual
6 ) LOOP
7 EXECUTE immediate 'alter sequence s increment by '||to_number(x.seq);
8 SELECT s.nextval INTO l_seq FROM dual;
9 EXECUTE immediate 'alter sequence s increment by 1';
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> select s.nextval from dual;
101
sequence < max(value):
LAX:(MDINH@db01)> drop sequence s;
Sequence dropped.
LAX:(MDINH@db01)> create sequence s;
Sequence created.
LAX:(MDINH@db01)> select s.nextval from dual;
1
Sequence reset:
LAX:(MDINH@db01)> DECLARE
2 l_seq NUMBER;
3 BEGIN
4 FOR x IN(
5 SELECT -1*(s.nextval-(SELECT MAX(id) FROM t)) seq FROM dual
6 ) LOOP
7 EXECUTE immediate 'alter sequence s increment by '||to_number(x.seq);
8 SELECT s.nextval INTO l_seq FROM dual;
9 EXECUTE immediate 'alter sequence s increment by 1';
10 END LOOP;
11 END;
12 /
PL/SQL procedure successfully completed.
LAX:(MDINH@db01)> select s.nextval from dual;
101
LAX:(MDINH@db01)>
Happy Resetting!
CLI versus GUI
Lately, I have been working more with CLI after having some dependencies on GUI.
No worries, there is not a lack of information from Google to steal what you want.
Good artists copy, Great artists steal – Steve Jobs
Here is an example on how to recreate the user with the same password and privileges.
C:\SQL>sqlplus system/oracle @extract_user.sql scott SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 6 22:28:38 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:635FFF7EF7E7AA0C4E561203F88246E6B426778B26E6D86B3478778A6AC5;F894844C34402B67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; -- GRANT "CONNECT" TO "SCOTT"; GRANT "RESOURCE" TO "SCOTT"; GRANT "DBA" TO "SCOTT"; GRANT "PLUSTRACE" TO "SCOTT"; GRANT UNLIMITED TABLESPACE TO "SCOTT"; GRANT EXECUTE ON "SYS"."DBMS_CRYPTO" TO "SCOTT"; ****************************** Execute: cr_extract_user.sql ****************************** Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
To verify the password is indeed the same as the original, I created user mdinh with the same hash value for the password.
C:\SQL>sqlplus system/oracle SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 6 22:29:07 2013 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create user mdinh IDENTIFIED BY VALUES 'S:635FFF7EF7E7AA0C4E561203F88246E6B426778B26E6D86B3478778A6AC5;F894844C34402B67'; User created. SQL> grant dba to mdinh; Grant succeeded. SQL> conn mdinh/tiger Connected. SQL> conn scott/tiger Connected. SQL>
As you can see, both scott and mdinh are tiger.
What do you prefer more, CLI or GUI?
DBA_ERRORS
Learn something new every day, from the twitter feed, DBA – Default Blame Acceptor.
Another is DBA_ERRORS
Here is an example:
SQL> alter procedure bad_proc compile; Warning: Procedure altered with compilation errors. SQL> show error; Errors for PROCEDURE BAD_PROC: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/1 PL/SQL: SQL Statement ignored 4/8 PL/SQL: ORA-00904: "E": invalid identifier SQL> SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text 2 FROM dba_errors 3 WHERE owner='SCOTT' 4 AND text LIKE '%ORA-%' 5 AND name='BAD_PROC' 6 ORDER BY 1 7 ; OWNER||'.'||NAME||'|'||TYPE -------------------------------------------------------------------------- TEXT -------------------------------------------------------------------------------- SCOTT.BAD_PROC|PROCEDURE PL/SQL: ORA-00904: "E": invalid identifier SQL>
Thanks to Luke from Pythian for raising my curiosity.
It’s amazing the number of information you can find when you go digging.
For example: DBMS_UTILITY.COMPILE_SCHEMA
10.2 – procedure compiles all procedures, functions, packages, and triggers in the specified schema
11.2 – procedure compiles all procedures, functions, packages, views and triggers in the specified schema
Let’s test this for 11.2.
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> create or replace force view tv as select * from t; Warning: View created with compilation errors. SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text 2 FROM dba_errors 3 WHERE owner='SCOTT' 4 AND text LIKE '%ORA-%' 5 AND name='TV' 6 ORDER BY 1 7 ; OWNER||'.'||NAME||'|'||TYPE -------------------------------------------------------------------------- TEXT -------------------------------------------------------------------------------- SCOTT.TV|VIEW ORA-00942: table or view does not exist SQL> create table t as select * from emp; Table created. SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text 2 FROM dba_errors 3 WHERE owner='SCOTT' 4 AND text LIKE '%ORA-%' 5 AND name='TV' 6 ORDER BY 1 7 ; OWNER||'.'||NAME||'|'||TYPE -------------------------------------------------------------------------- TEXT -------------------------------------------------------------------------------- SCOTT.TV|VIEW ORA-00942: table or view does not exist SQL> execute DBMS_UTILITY.COMPILE_SCHEMA (schema=>user,compile_all=>false); PL/SQL procedure successfully completed. SQL> SELECT DISTINCT owner||'.'||name||'|'||type,text 2 FROM dba_errors 3 WHERE owner='SCOTT' 4 AND text LIKE '%ORA-%' 5 AND name='TV' 6 ORDER BY 1 7 ; no rows selected SQL>
Lastly, be aware of the BUG:
DBMS_UTILITY.COMPILE_SCHEMA Always Turns The DEBUG Flag to TRUE [ID 1208971.1]
PL/SQL – Version: 10.2.0.4 to 11.1.0.7 – Release: 10.2 to 11.1
