DBA Sensation

March 10, 2009

dbms_stats has error when gather statistics on case sensitive objects

Filed under: [System Performance tuning] — Tags: , , , , — zhefeng @ 3:33 pm

i was trying to use dbms_stats gather the statistics for a table named: “XTREME_EN”.”Financials”, but got this error:

sys@FUN10U09> exec dbms_stats.gather_table_stats(ownname => ‘XTREME_EN’,tabname
=> ‘Financials’);
BEGIN dbms_stats.gather_table_stats(ownname => ‘XTREME_EN’,tabname => ‘Financials’); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE “XTREME_EN”.”FINANCIALS”, insufficient privil
eges or does not
exist
ORA-06512: at “SYS.DBMS_STATS”, line 13427
ORA-06512: at “SYS.DBMS_STATS”, line 13457
ORA-06512: at line 1

However, if i use traditional analyze command, it works:

sys@FUN10U09> ANALYZE TABLE “XTREME_EN”.”Financials” COMPUTE STATISTICS;

Table analyzed.

Is that because dbms_stats stupider than analyze? Of course not, Oracle is always encouraging you to use “dbms_stats” instead of using “analyze”.

After search on the metalink, i found this doc: “DBMS_STATS Reports ORA-20000 and ORA-06512 On Case Sensitive Object Names”  Doc ID: 343355.1

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4774819970862237887::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,343355.1,1,1,1,helvetica

According to this doc, we have to mention the object name in double quotes.This is very similar to how SQL supports mixed cases.

For our case:

sys@FUN10U09> exec dbms_stats.gather_table_stats(ownname => ‘XTREME_EN’,tabname
=> ‘”Financials”‘);

PL/SQL procedure successfully completed.

Works perfectly!

Advertisements

February 20, 2009

Detailed steps for removing a node from 10gR2 3-nodes RAC

Filed under: [RAC] — Tags: , , , , — zhefeng @ 11:36 am

Background:

we have to remove a node from Oracle 10gR2 RAC.

OS: Redhat EL4

Node name:

db01

db02

db05

Storage: ASM (instance name +ASM)

The most important 3 steps that need to be followed are;

A.         Remove the instance using DBCA.

B.         Remove the node from the cluster.

C.         Reconfigure the OS and remaining hardware.

Here is a breakdown of the above steps.

A. Remove the instance using DBCA.

————————————–

1. Verify that you have a good backup of the OCR (Oracle Configuration  Repository) using ocrconfig -showbackup or dd command.

Run the backup manually with dd for OCR and Voting disk:

[oracle@db01 ocr_voting]$ pwd

/databases/oracle/backup/ocr_voting

[oracle@db01 ocr_voting]$ dd if=/dev/raw/raw1 of=before_del_3rdnode.ocr

[oracle@db01 ocr_voting]$ dd if=/dev/raw/raw2 of=before_del_3rdnode.vote

2. Run DBCA from one of the nodes you are going to keep (db01).  Leave the database up and also leave the departing instance up and running.

3. Choose “Instance Management”

4. Choose “Delete an instance”

5. On the next screen, select the cluster database (p10c) from which you will delete an instance.  Supply the system privilege username and password.

6. On the next screen, a list of cluster database instances will appear.  Highlight the instance you would like to delete then click next.

7.         If you have services configured, reassign the services.  Modify the

services so that each service can run on one of the remaining

instances.  Set “not used” for each service regarding the instance

that is to be deleted.  Click Finish.

8.         If your database is in archive log mode you may encounter the

following errors (10gR2 doesn’t have this issue):

ORA-350

ORA-312

This may occur because the DBCA cannot drop the current log, as

it needs archiving.  This issue is fixed in the 10.1.0.3

patchset. But previous to this patchset you should click the

ignore button and when the DBCA completes, manually archive

the logs for the deleted instance and dropt the log group.

SQL>  alter system archive log all;

SQL>  alter database drop logfile group 2;

9.         Verify that the dropped instance’s redo thread has been removed by

querying v$log.  If for any reason the redo thread is not disabled

then disable the thread.

SQL> alter database disable thread 2;

10. Verify that the instance was removed from the OCR (Oracle Configuration Repository) with the following commands:

srvctl config database -d <db_name>

[oracle@vanpgprodb01 dbca]$ srvctl config database -d p10c

db01 p10c1 /databases/oracle/db

db02 p10c2 /databases/oracle/db

cd <CRS_HOME>/bin/crs_stat

11. If this node had an ASM instance and the node will no longer be a part of the cluster you will now need to remove the ASM instance with:

srvctl stop asm -n <nodename>

[oracle@db01 dbca]$ srvctl stop asm -n db05

srvctl remove asm -n <nodename>

[oracle@db01 dbca]$ srvctl remove asm -n db05

Verify that asm is removed with:

srvctl config asm -n <nodename>

[oracle@db01 dbca]$ srvctl config asm -n db05  –the output is nothing instead of show you the ASM instance name and asm home

Delete the ASM folders on deleting node:

rm -r $ORACLE_BASE/admin/+ASM
[root@db05 admin]# rm -rf +ASM

rm -f $ORACLE_HOME/dbs/*ASM*

[root@db05 dbs]# rm -rf *ASM*

Remove the ASM library on node3:

[root@db05 db]# /etc/init.d/oracleasm stop

Unmounting ASMlib driver filesystem: [  OK  ]

Unloading module “oracleasm”: [  OK  ]

[root@db05 db]# rpm -qa | grep oracleasm

oracleasmlib-2.0.2-1

oracleasm-2.6.9-55.ELsmp-2.0.3-1

oracleasm-support-2.0.3-1

[root@db05 db]# rpm -ev oracleasm-support-2.0.3-1 oracleasm-2.6.9-55.ELsmp-2.0.3-1 oracleasmlib-2.0.2-1

warning: /etc/sysconfig/oracleasm saved as /etc/sysconfig/oracleasm.rpmsave

[root@db05 db]# rm -f /etc/sysconfig/oracleasm.rpmsave

[root@db05 db]# rm -f /etc/rc.d/init.d/oracleasm

[root@db05 db]# rm -f /etc/rc0.d/*oracleasm*

[root@db05 db]# rm -f /etc/rc1.d/*oracleasm*

[root@db05 db]# rm -f /etc/rc2.d/*oracleasm*

[root@db05 db]# rm -f /etc/rc3.d/*oracleasm*

[root@db05 db]# rm -f /etc/rc4.d/*oracleasm*

[root@db05 db]# rm -f /etc/rc5.d/*oracleasm*

[root@db05 db]# rm -f /etc/rc6.d/*oracleasm*

B.         Remove the Node from the Cluster

—————————————-

Once the instance has been deleted, the process of removing the node from the cluster is a manual process. This is accomplished by running scripts on the deleted node to remove the CRS install, as well as scripts on the remaining nodes to update the node list.  The following steps assume that the node to be removed is still functioning.

1. Remove the listener and nodeapp on deleting node:

1). To delete node first stop and remove the nodeapps on the node you are removing.  Assuming that you have removed the ASM instance as the root user on a remaining node;

# srvctl stop nodeapps -n <nodename>

[oracle@db01 dbs]$ srvctl stop nodeapps -n db05

[oracle@db01 dbs]$ crs_stat -t

Name           Type           Target    State     Host

————————————————————

ora.p10c.db    application    ONLINE    ONLINE    db02

ora….c1.inst application    ONLINE    ONLINE    db01

ora….c2.inst application    ONLINE    ONLINE    db02

ora….SM1.asm application    ONLINE    ONLINE   db01

ora….01.lsnr application    ONLINE    ONLINE    db01

ora….b01.gsd application    ONLINE    ONLINE    db01

ora….b01.ons application    ONLINE    ONLINE    db01

ora….b01.vip application    ONLINE    ONLINE    db01

ora….SM2.asm application    ONLINE    ONLINE    db02

ora….02.lsnr application    ONLINE    ONLINE    db02

ora….b02.gsd application    ONLINE    ONLINE    db02

ora….b02.ons application    ONLINE    ONLINE    db02
ora….b02.vip application    ONLINE    ONLINE    db02

ora….05.lsnr application    OFFLINE   OFFLINE

ora….b05.gsd application    OFFLINE   OFFLINE

ora….b05.ons application    OFFLINE   OFFLINE

ora….b05.vip application    OFFLINE   OFFLINE

2). Run netca.  Choose “Cluster Configuration”.

3). Only select the node you are removing and click next.

4). Choose “Listener Configuration” and click next.

5). To delete the listener: Choose “Delete” and delete any listeners configured on the node you are removing.

6).  Run <CRS_HOME>/bin/crs_stat.  Make sure that all database resources are running on nodes that are going to be kept.  For example:

NAME=ora.<db_name>.db

TYPE=application

TARGET=ONLINE

STATE=ONLINE on <node2>

[oracle@db05 db]$ crs_stat -t

Name           Type           Target    State     Host

————————————————————

ora.p10c.db    application    ONLINE    ONLINE    db02

ora….c1.inst application    ONLINE    ONLINE    db01

ora….c2.inst application    ONLINE    ONLINE    db02

ora….SM1.asm application    ONLINE    ONLINE    db01

ora….01.lsnr application    ONLINE    ONLINE   db01

ora….b01.gsd application    ONLINE    ONLINE   db01

ora….b01.ons application    ONLINE    ONLINE   db01

ora….b01.vip application    ONLINE    ONLINE    db01

ora….SM2.asm application    ONLINE    ONLINE   db02

ora….02.lsnr application    ONLINE    ONLINE    db02

ora….b02.gsd application    ONLINE    ONLINE    db02

ora….b02.ons application    ONLINE    ONLINE    db02

ora….b02.vip application    ONLINE    ONLINE    db02

ora….b05.gsd application    OFFLINE   OFFLINE

ora….b05.ons application    OFFLINE   OFFLINE

ora….b05.vip application    OFFLINE   OFFLINE

Ensure that this resource is not running on a node that will be removed (this step hasn’t been done in our case since crs stats shows everything correct).  Use <CRS_HOME>/bin/crs_relocate to perform this.

Example:  crs_relocate ora.<db_name>.db

7).  As the root user, remove the nodeapps on the node you are removing.

# srvctl remove nodeapps -n <nodename>

[root@db05 db]# srvctl remove nodeapps -n db05

Please confirm that you intend to remove the node-level applications on node db05 (y/[n]) y

[root@db05 db]# crs_stat -t

Name           Type           Target    State     Host

————————————————————

ora.p10c.db    application    ONLINE    ONLINE    db02

ora….c1.inst application    ONLINE    ONLINE    db01

ora….c2.inst application    ONLINE    ONLINE    db02

ora….SM1.asm application    ONLINE    ONLINE    db01

ora….01.lsnr application    ONLINE    ONLINE    db01

ora….b01.gsd application    ONLINE    ONLINE    db01

ora….b01.ons application    ONLINE    ONLINE    db01

ora….b01.vip application    ONLINE    ONLINE    db01

ora….SM2.asm application    ONLINE    ONLINE    db02

ora….02.lsnr application    ONLINE    ONLINE    db02

ora….b02.gsd application    ONLINE    ONLINE    db02

ora….b02.ons application    ONLINE    ONLINE    db02

ora….b02.vip application    ONLINE    ONLINE    db02

2. Remove the Oracle Database Software from the Node to be Deleted

1). On node3 make sure you have correct ORACLE_HOME

[oracle@db05 db]$ echo $ORACLE_HOME

/databases/oracle/db

2). Update Node List for Oracle Database Software – (Remove node3):

[oracle@db05 bin]$ export DISPLAY=10.50.133.143:0

[oracle@db05 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME CLUSTER_NODES=”” -local

Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /databases/oracle/oraInventory

‘UpdateNodeList’ was successful.

Note: Although the OUI does not launch an installer GUI, the DISPLAY  environment variable still needs to be set!

3). De-install Oracle Database Software

Next, run the OUI from the node to be deleted (linux3) to de-install the Oracle Database software. Make certain that you choose the home to be removed and not just the products under that home.

4). Update Node List for Remaining Nodes in the Cluster (on any of remaining nodes)

[oracle@db01 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORACLE_HOME “CLUSTER_NODES={vanpgprodb01,vanpgprodb02}”

Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /databases/oracle/oraInventory

‘UpdateNodeList’ was successful.

3. Remove the Node to be Deleted from Oracle Clusterware

1). Remove Node-Specific Interface Configuration

[oracle@db05 db]$ export ORA_CRS_HOME=/databases/oracle/crs

[oracle@db05 db]$ grep ‘^remoteport’ $ORA_CRS_HOME/opmn/conf/ons.config

remoteport=6200

[oracle@db05 bin]$ ./racgons remove_config vanpgprodb05:6200

racgons: Existing key value on vanpgprodb05 = 4948.

WARNING: db05:6200 does not exist.

[oracle@db05 bin]$ ./racgons remove_config db05:4948

racgons: Existing key value on vanpgprodb05 = 4948.

racgons: db05:4948 removed from OCR.

[oracle@db05 bin]$ ./oifcfg delif -node db05

PROC-4: The cluster registry key to be operated on does not exist.

PRIF-11: cluster registry error

Note: this error has been approved ok.

4. Disable Oracle Clusterware Applications

Running this script will stop the CRS stack and delete the ocr.loc  file on the node to be removed. The nosharedvar option assumes the ocr.loc file is not on a shared file sytem.

While logged into node3 as the root user account, run the following:

[root@vanpgprodb05 install]# ./rootdelete.sh local nosharedvar nosharedhome

CRS-0210: Could not find resource ‘ora.db05.LISTENER_DB05.lsnr’.

CRS-0210: Could not find resource ‘ora.db05.ons’.

CRS-0210: Could not find resource ‘ora.db05.vip’.

CRS-0210: Could not find resource ‘ora.db05.gsd’.

Shutting down Oracle Cluster Ready Services (CRS):

Feb 17 15:24:19.153 | INF | daemon shutting down

Stopping resources. This could take several minutes.

Successfully stopped CRS resources.

Stopping CSSD.

Shutting down CSS daemon.

Shutdown request successfully issued.

Shutdown has begun. The daemons should exit soon.

Checking to see if Oracle CRS stack is down…

Checking to see if Oracle CRS stack is down…

Oracle CRS stack is not running.

Oracle CRS stack is down now.

Removing script for Oracle Cluster Ready services

Updating ocr file for downgrade

Cleaning up SCR settings in ‘/etc/oracle/scls_scr’

5. Delete Node from Cluster and Update OCR

Upon successful completion of the rootdelete.sh script, run the rootdeletenode.sh script to delete the node (linux3) from the Oracle cluster and to update the Oracle Cluster Registry (OCR). This script should be run from a pre-existing / available node in the cluster (node1) as the root user account:

Before executing rootdeletenode.sh, we need to know the node number associated with the node name to be deleted from the cluster. To determine the node number, run the following command as the oracle user account from node1:

[oracle@db01 bin]$ pwd

/databases/oracle/crs/bin

[oracle@db01 bin]$ olsnodes -n

db01    1

db02    2

db05    3

Note: notice the node # from result, we need to use it for removing node.

[root@db01 install]# pwd

/databases/oracle/crs/install

[root@db01 install]# ./rootdeletenode.sh db05,3

CRS-0210: Could not find resource ‘ora.db05.LISTENER_DB05.lsnr’.

CRS-0210: Could not find resource ‘ora.db05.ons’.

CRS-0210: Could not find resource ‘ora.db05.vip’.

CRS-0210: Could not find resource ‘ora.db05.gsd’.

CRS-0210: Could not find resource ora.db05.vip.

CRS nodeapps are deleted successfully

clscfg: EXISTING configuration version 3 detected.

clscfg: version 3 is 10G Release 2.

Successfully deleted 14 values from OCR.

Key SYSTEM.css.interfaces.nodevanpgprodb05 marked for deletion is not there. Ignoring.

Successfully deleted 5 keys from OCR.

Node deletion operation successful.

‘db05,3’ deleted successfully

[root@db01 install]# ../bin/olsnodes -n

db01    1

db02    2

6. Update Node List for Oracle Clusterware Software – (Remove node3)

From Node3 as Oracle user:

[oracle@db05 bin]$ pwd

/databases/oracle/crs/oui/bin

[oracle@db05 bin]$ export ORA_CRS_HOME=/databases/oracle/crs

[oracle@db05 bin]$ export DISPLAY=10.50.133.143:0

[oracle@db05 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORA_CRS_HOME CLUSTER_NODES=”” -local CRS=true

Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /databases/oracle/oraInventory

‘UpdateNodeList’ was successful.

7. De-install Oracle Clusterware Software

[oracle@db05 bin]$ pwd

/databases/oracle/crs/oui/bin

[oracle@db05 bin]$ ./runInstaller

After delete the crs software, the directory will be deleted as well.

8. Update Node List for Remaining Nodes in the Cluster

[oracle@db01 bin]$ export ORA_CRS_HOME=/databases/oracle/crs

[oracle@db01 bin]$ export DISPLAY=10.50.133.143:0

[oracle@db01 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=$ORA_CRS_HOME “CLUSTER_NODES={db01,db02}” CRS=true

Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /databases/oracle/oraInventory

‘UpdateNodeList’ was successful.

C.         Reconfigure the OS and remaining hardware.

————————————————-

1. Check the tnsnames.ora on the rest of nodes if exists.

2. Delete oracle_home and crs_home

3. Next, as root, from the deleted node, verify that all init scripts and soft links are removed:

For Linux:

rm -f /etc/init.d/init.cssd

rm -f /etc/init.d/init.crs

rm -f /etc/init.d/init.crsd

rm -f /etc/init.d/init.evmd

rm -f /etc/rc2.d/K96init.crs

rm -f /etc/rc2.d/S96init.crs

rm -f /etc/rc3.d/K96init.crs

rm -f /etc/rc3.d/S96init.crs

rm -f /etc/rc5.d/K96init.crs

rm -f /etc/rc5.d/S96init.crs

rm -Rf /etc/oracle

rm -rf /etc/ora.tab

Reference:

1. Removing a Node from an Oracle RAC 10g Release 2 Cluster on Linux – (CentOS 4.5 / iSCSI)

by Jeff Hunter http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_23.shtml

2. How to delete a node from 3 node RAC in 10GR2

http://www.oraclefaq.net/2007/06/21/how-to-delete-a-node-from-3-node-rac-in-10gr2/

3. 10 Adding and Deleting Nodes and Instances on UNIX-Based Systems

Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide 10g Release 2 (10.2) Part Number B14197-09

http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/adddelunix.htm#BEIJAJHH

4. Removing a Node from a 10g RAC Cluster  Doc ID: 269320.1

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:4196773229713543167::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,269320.1,1,1,1,helvetica

February 2, 2009

Change IP address for oracle RAC public and VIP interfaces

Filed under: [RAC] — Tags: — zhefeng @ 4:33 pm

My company is doing massive re-ip project. So our Oracle RAC ip address has to be changed as well. Fortunately, we don’t need to change hostname, otherwise the story will be more complicated.

1. Current Environment
1). Machine IP:
Node1: vmrac01
Node2: vmrac02

## eth1-Public
10.50.96.101 vmrac01 vmrac01.test.com
10.50.96.102 vmrac02 vmrac02.test.com
## eth0-Private
192.168.199.1 vmracprv01 vmracprv01.test.com
192.168.199.2 vmracprv02 vmracprv02.test.com
## VIP
10.50.96.103 vmracvip01 vmracvip01.test.com
10.50.96.104 vmracvip02 vmracvip02.test.com

2). cluster information:
cluster name — vm10cls
database name — v10c
Instance 1 — v10c1
Instance 2 — v10c2
Node1 — vmrac01
Node2 — vmrac02

2. New IP changing map(different subnet mask too):
10.50.96.101/255.255.255.0 vmrac01 –> 10.50.99.41/255.255.252.0
10.50.96.102/255.255.255.0 vmrac02 –> 10.50.99.42/255.255.252.0
10.50.96.103/255.255.255.0 vmracvip01 –> 10.50.99.43/255.255.252.0
10.50.96.104/255.255.255.0 vmracvip02 –> 10.50.99.44/255.255.252.0

3. steps 1 — change RAC IP settings
1). bring service down, make sure everything was offline except css daemon
bash-3.1$ srvctl stop database -d v10c
bash-3.1$ srvctl stop nodeapps -n vmrac01
bash-3.1$ srvctl stop nodeapps -n vmrac02
bash-3.1$ crs_stat -t
Name Type Target State Host
————————————————————
ora.v10c.db application OFFLINE OFFLINE
ora….c1.inst application OFFLINE OFFLINE
ora….c2.inst application OFFLINE OFFLINE
ora….SM1.asm application OFFLINE OFFLINE
ora….01.lsnr application OFFLINE OFFLINE
ora….c01.gsd application OFFLINE OFFLINE
ora….c01.ons application OFFLINE OFFLINE
ora….c01.vip application OFFLINE OFFLINE
ora….SM2.asm application OFFLINE OFFLINE
ora….02.lsnr application OFFLINE OFFLINE
ora….c02.gsd application OFFLINE OFFLINE
ora….c02.ons application OFFLINE OFFLINE
ora….c02.vip application OFFLINE OFFLINE

2). backup OCR and Voting disks
bash-3.1$ ocrcheck|grep -i file
Device/File Name : /dev/raw/raw1
bash-3.1$ crsctl query css votedisk
0. 0 /dev/raw/raw2
located 1 votedisk(s).

#dd if=/dev/raw/raw1 of=/database/temp/ocr_vote_bk/ocr.bak
#dd if=/dev/raw/raw2 of=/database/temp/ocr_vote_bk/vote.bak

3). get current config:
bash-3.1$ oifcfg getif
eth0 10.50.96.0 global public –current network for public
eth1 192.168.199.0 global cluster_interconnect –we are not going to change this

4). delete current public ip:
bash-3.1$ oifcfg delif -global eth0

5). change to new network:
bash-3.1$ oifcfg setif -global eth0/10.50.99.0:public

6). change vip address:
a. check current one
bash-3.1$ srvctl config nodeapps -n vmrac01 -a
VIP exists.: /vmracvip01/10.50.96.103/255.255.255.0/eth0
bash-3.1$ srvctl config nodeapps -n vmrac02 -a
VIP exists.: /vmracvip02/10.50.96.104/255.255.255.0/eth0
b. Modify VIP component (has to be the css owner, “root” usually)
#srvctl modify nodeapps -n vmrac01 -A 10.50.99.43/255.255.252.0/eth0
#srvctl modify nodeapps -n vmrac02 -A 10.50.99.44/255.255.252.0/eth0
c. double verify the changes
bash-3.1$ srvctl config nodeapps -n vmrac01 -a
VIP exists.: /vmracvip01/10.50.99.43/255.255.252.0/eth0
bash-3.1$ srvctl config nodeapps -n vmrac02 -a
VIP exists.: /vmracvip02/10.50.99.44/255.255.252.0/eth0

7). change the hosts file(on both nodes):
## eth1-Public
10.50.99.41 vmrac01 vmrac01.test.com
10.50.99.42 vmrac02 vmrac02.test.com
## eth0-Private
192.168.199.1 vmracprv01 vmracprv01.test.com
192.168.199.2 vmracprv02 vmracprv02.test.com
## VIP
10.50.99.43 vmracvip01 vmracvip01.test.com
10.50.99.44 vmracvip02 vmracvip02.test.com

8). if the listener is using any IP address, it also needs to be changed.

4. Steps 2 — change OS IP settings
1). change IP
[root@vmrac01]# vi /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE=eth0
BOOTPROTO=static
IPADDR=10.50.99.41
NETMASK=255.255.252.0
HWADDR=00:50:56:BD:05:14
ONBOOT=yes

[root@vmrac02]# vi /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE=eth0
BOOTPROTO=static
IPADDR=10.50.99.42
NETMASK=255.255.252.0
HWADDR=00:50:56:BD:3E:08
ONBOOT=yes

2). change the default gateway on both nodes (if needed, here since they are in same vlan so i didn’t change them)
[root@vmrac01 ~]# cat /etc/sysconfig/network
NETWORKING_IPV6=yes
HOSTNAME=vmrac01
NETWORKING=yes
NISDOMAIN=nis
GATEWAY=10.50.96.1 <– here is the default gateway to be changed

3) Change the IP Address’es on the known_hosts ssh config files for oracle user
$ su – oracle
$ cd .ssh
$ cp known_hosts known_hosts.bak
$ modify the old IP’s to the new IP’s

4). restart network (on both node)
#service network restart

5). restart crs daemon (on both node)
#crsctl stop crs
#crsctl start crs

5. Step3 — verify everything

reference:
1. "How to Change Interconnect/Public Interface IP or Subnet in Oracle Clusterware", Doc ID: 283684.1
2. "Modifying the VIP or VIP Hostname of a 10g or 11g Oracle Clusterware Node", DOC ID: 276434.1
3. "How to change Public and VIP component address in case of RAC?" http://orcl-experts.info/index.php?name=FAQ&id_cat=9

January 5, 2009

Oracle OS Authentication

Filed under: [OS related topics] — Tags: — zhefeng @ 5:24 pm

Step1: create Operation system User account.
In this testing, we used NIS user called: guest_os
So it’s reachable for all the NIS memeber machines.

Step2: Make sure the os prefix in Oracle database was not null
#turn on local OS authentication
SQL> show parameter os_auth

NAME TYPE VALUE
———————————— ———– ——————————
os_authent_prefix string ops$

Step3: Make sure the remote OS authentication parameter was turned on
#turn on remote OS authenciation
sql>alter system set remote_os_authent=true scope=spfile;
SQL> show parameter remote_os

NAME TYPE VALUE
———————————— ———– ——————————
remote_os_authent boolean TRUE
remote_os_roles boolean FALSE

Step4: create a mapping user in Oracle database to map the OS username
sql>create user OPS$GUEST_OS identified externally default tablespace users temporary tablespace temp;
sql>grant connect to OPS$GUEST_OS;

Step5: testing on another linux client machine
bash-3.00$ id
uid=63371(guest_os) gid=1000(rd) groups=1000(rd)
bash-3.00$ sqlplus /@fun11u03

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jan 5 16:19:56 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Note: if you are configure a windows user account for remote OS auth, then then mapped username in Oracle should be like this:
“OPS$domainname\username” and in sqlnet.ora has to have this line: SQLNET.AUTHENTICATION_SERVICES= (NTS)

Reference:
1. http://www.dba-oracle.com/security/local_os_authentication.htm
2. http://www.oracle-base.com/articles/misc/OsAuthentication.php

December 19, 2008

how to use dbms_stats package in a efficient way

Filed under: [System Performance tuning] — zhefeng @ 4:31 pm

i found a very good doc talking about oracle dbms_stats package from dba-oracle website: here is the link (http://www.dba-oracle.com/oracle_tips_dbms_stats1.htm)

The old fashioned “analyze table” and dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to SQL performance. This is because the cost-based SQL Optimizer (CBO) relies on the quality of the statistics to choose the best execution plan for all SQL statements. The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats results in faster SQL execution plans.

Let’s see how dbms_stats works. It’s easy! Here is a sample execution of dbms_stats with the options clause:
exec dbms_stats.gather_schema_stats( –
ownname => ‘SCOTT’, –
estimate_percent => dbms_stats.auto_sample_size, –
method_opt => ‘for all columns size repeat’, –
degree => 34 –
)

When the options clause is specified you may specify GATHER options. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored.
exec dbms_stats.gather_schema_stats( –
ownname => ‘SCOTT’, –
options => ‘GATHER AUTO’
)

There are several values for the options parameter that we need to know about:

*gather – re-analyzes the whole schema.

*gather empty – Only analyze tables that have no existing statistics.

*gather stale – Only re-analyze tables with more than 10% modifications (inserts, updates, deletes).

*gather auto – This will re-analyze objects which currently have no statistics and objects with stale statistics. (like “gather empty”+”gather stale”)

Note that both gather stale and gather auto require monitoring. If you issue the “alter table xxx monitoring” command, Oracle tracks changed tables with the dba_tab_modifications view. Below we see that the exact number of inserts, updates and deletes are tracked since the last analysis of statistics.
SQL> desc dba_tab_modifications;

Name Type
——————————–
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
TRUNCATED VARCHAR2(3)

The most interesting of these options is the gather stale option. Because all statistics will become stale quickly in a robust OLTP database, we must remember the rule for gather stale is > 10% row change (based on num_rows at statistics collection time).

Hence, almost every table except read-only tables will be re-analyzed with the gather stale option. Hence, the gather stale option is best for systems that are largely read-only. For example, if only 5% of the database tables get significant updates, then only 5% of the tables will be re-analyzed with the “gather stale” option.

The CASCADE option

When analyzing specific tables, the cascade option can be used to analyze all related objects based on foreign-key constraints. For example, stats$snapshot has foreign key referential integrity into all subordinate tables (stats$sysstat, etc.), so a single analyze can invoke an analyze of all subordinate tables:

exec dbms_stats.gather_table_stats( –
ownname => ‘PERFSTAT’, –
tabname => ’STATS$SNAPSHOT’ –
estimate_percent => dbms_stats.auto_sample_size, –
method_opt => ‘for all columns size skewonly’, –
cascade => true, –
degree => 7 –
)

The DEGREE Option

Note that you can also parallelize the collection of statistics because the CBO does full-table and full-index scans. When you set degree=x, Oracle will invoke parallel query slave processes to speed up table access. Degree is usually about equal to the number of CPUs, minus 1 (for the OPQ query coordinator).
Automating sample size with dbms_stats

Now that we see how the dbms_stats options works, get see how to specify the sample size for dbms_stats. The following estimate_percent argument is a new way to allow Oracle’s dbms_stats to automatically estimate the “best” percentage of a segment to sample when gathering statistics:

estimate_percent => dbms_stats.auto_sample_size

You can verify the accuracy of the automatic statistics sampling by looking at the dba_tables sample_size column. It is interesting to note that Oracle chooses between 5% to 20% for a sample_size when using automatic sampling.

In our next installment we will look at automatics the collection of histogram data from dbms_stats.

December 11, 2008

How to set trace for others sessions, for your own session and at instance level

Filed under: [System Performance tuning] — zhefeng @ 10:32 am

This is a very good detailed article about oracle tracing. The original links is here:
http://www.petefinnigan.com/ramblings/how_to_set_trace.htm

Tools to analyse trace files

Up to and including Oracle 10g the tool that is generally used to analyse trace files is called tkprof. This tool formats the trace files that have been generated into a more readable format. Understanding the trace file format seems daunting on first inspection. A good source for details on the trace file format is a metalink note 39817.1. In 10g there is a new tool for formatting trace files called trcsess. This tool has been designed to deal with the new trace facilities that allow trace to be identified based on client identifier or by a combination of service name / module / action. This allows trace to be completed even if connection pooling and multi-threading is used. An individual client in these circumstances could share many different sessions.

Find out where the trace file will be written to

If the user you are using is not a DBA or to be more specific has not been granted access to the data dictionary view V$PARAMETER then you will need to use this technique to find out where your trace files are written to:

SQL> set serveroutput on size 1000000 for wra
SQL> declare
2 paramname varchar2(256);
3 integerval binary_integer;
4 stringval varchar2(256);
5 paramtype binary_integer;
6 begin
7 paramtype:=dbms_utility.get_parameter_value(‘user_dump_dest’,integerval,stringval);
8 if paramtype=1 then
9 dbms_output.put_line(stringval);
10 else
11 dbms_output.put_line(integerval);
12 end if;
13 end;
14 /
C:\oracle\admin\sans\udump

PL/SQL procedure successfully completed.

SQL>

If the user you are using has access to the base views then you can do the following instead.

SQL> select name,value
2 from v$parameter
3 where name=’user_dump_dest’;

NAME
—————————————————————-
VALUE
——————————————————————————–
user_dump_dest
C:\oracle\admin\sans\udump

SQL>

Making trace files available

There is an undocumented parameter _trace_files_public that if set to true changes the file permissions in the user_dump_dest directory when trace files are created to allow everyone to read them. This parameter can be checked with the following SQL. Beware that this is an undocumented parameter and should not be routinely set to true as some information in trace files can be used by hackers or malicious users. You can set this parameter by adding the following line to the init.ora file:

# allow trace files to be created with public permissions
_trace_files_public=true
# disable this feature:
#_trace_files_public=true
# or =>
_trace_files_public=false

Here is the SQL to check the value of this parameter:

SQL> select x.ksppinm name,y.ksppstvl value
2 from sys.x$ksppi x,sys.x$ksppcv y
3 where x.inst_id=userenv(‘Instance’)
4 and y.inst_id=userenv(‘Instance’)
5 and x.indx=y.indx
6 and x.ksppinm=’_trace_files_public’;

NAME
—————————————————————-
VALUE
——————————————————————————–
_trace_files_public
FALSE

SQL>

Let’s start with some examples of how to check trace for another session that is connected to the database.

Now find the SID and SERIAL# of the other session

We are using a simple example and the session we are looking for is for the user SCOTT and we are logged into this session with AS SYSDBA. We need to be logged in as SYS or AS SYSDBA so that we can access the packages DBMS_SUPPORT and DBMS_SYSTEM needed to set trace in another session or in our own session. Again as with the first example about access to v$parameter a user with access to the views V$SESSION and V$PROCESS is needed. First lets find the SID and SERIAL#

SQL> connect system/manager@sans as sysdba
Connected.
SQL> col sid for 999999
SQL> col serial# for 999999
SQL> col username for a20
SQL> col osuser for a20
SQL> select s.sid,s.serial#,s.username,s.osuser
2 from v$session s,v$process p
3 where s.paddr=p.addr;

SID SERIAL# USERNAME OSUSER
——- ——- ——————– ——————–
1 1 SYSTEM
2 1 SYSTEM
3 1 SYSTEM
4 1 SYSTEM
5 1 SYSTEM
6 1 SYSTEM
7 1 SYSTEM
8 1 SYSTEM
9 253 SYSTEM ZULIA\pete
10 20 SCOTT ZULIA\pete

10 rows selected.

SQL>

great the SID and SERIAL# that we need are 10 and 20.

A word about trace levels

Before we use the DBMS_SYSTEM package to set trace in SCOTT’s session we need to discuss what levels are. Trace in fact sets an event in the Oracle kernel (what is an event? – An event is simply a flag to the Oracle kernel to tell it to emit some trace messages or to add some additional processing or to activate some new functionality. Some events are used by support analysts and developers to force certain conditions to occur for testing purposes). In our case we want to look at event number 10046 – This event tells the Oracle kernel to emit trace lines and timings. The levels available in Oracle through some of the interfaces used to set trace are:

* Level 0 = No statistics generated
* Level 1 = standard trace output including parsing, executes and fetches plus more.
* Level 2 = Same as level 1.
* Level 4 = Same as level 1 but includes bind information
* Level 8 = Same as level 1 but includes wait’s information
* Level 12 = Same as level 1 but includes binds and waits

For a complete list of events that can be set look at the file $ORACLE_HOME/rdmbs/mesg/oraus.msg on Unix or Linux. This file is not shipped on Windows systems. Also setting any event other that trace (10046) should not be done without the guidance of Oracle support.

Set trace in another session using DBMS_SYSTEM

First lets set trace in SCOTT’s session using the DBMS_SYSTEM package. Before we do let’s turn on timed statistics so that the trace files get timing info and also set the dump file size so that there is plenty of room for the trace being generated.

SQL> exec dbms_system.set_bool_param_in_session(10,20,’timed_statistics’,true);

PL/SQL procedure successfully completed.

SQL> exec dbms_system.set_int_param_in_session(10,20,’max_dump_file_size’,2147483647);

PL/SQL procedure successfully completed.

OK, here we set trace in SCOTT’s session

SQL> — now use standard dbms_support interface
SQL> exec dbms_system.set_sql_trace_in_session(10,20,true);

PL/SQL procedure successfully completed.

SQL> — execute some code
SQL> exec dbms_system.set_sql_trace_in_session(10,20,false);

PL/SQL procedure successfully completed.

SQL>

A second way to set trace in another session – This time setting trace level as well

Next we can again use the DBMS_SYSTEM interface but this time use the set event syntax. This allows us to set any event in the database. This is of course not sanctioned by Oracle support and can cause damage to your database if not done correctly. Use this interface with care and just set 10046 (trace) events. Here is how it is done:

SQL> exec dbms_system.set_ev(10,20,10046,8,”);

PL/SQL procedure successfully completed.

SQL> — execute some code
SQL> exec dbms_system.set_ev(10,20,10046,0,”);

PL/SQL procedure successfully completed.

Installing the DBMS_SUPPORT package

Using the example above we set trace to level 8, you can of course set it to any level you wish from the list we discussed above. Next we will use the DBMS_SUPPORT package to set trace. This package is not installed by default and is in fact undocumented and indeed on some platforms and versions its not even shipped and you will need to talk to Oracle support and get it from metalink. First we will install the package:

SQL> — now do the same with dbms_support
SQL> — the package has to be installed first – you should ask Oracle first though!
SQL> @%ORACLE_HOME%\rdbms\admin\dbmssupp.sql

Package created.

Package body created.

SQL>

Use DBMS_SUPPORT to set trace in another users session

Next use the interface to again set trace for SCOTT’s session that we found earlier. here it is:

SQL> exec dbms_support.start_trace_in_session(10,20,waits=>true,binds=>false);

PL/SQL procedure successfully completed.

SQL> — execute some code
SQL> exec dbms_support.stop_trace_in_session(10,20);

PL/SQL procedure successfully completed.

SQL>

use DBMS_SUPPORT to set trace in your own session

OK, that’s how to set trace in SCOTT’s session. How do we set trace in our own session. Well first we can use all of the approaches seen above and pass in the SID and SERIAL# for our own session. There are other methods for setting trace in your own session though. The first is again using the DBMS_SUPPORT package. Here it is:

SQL> exec dbms_support.start_trace(waits=>true,binds=>false);

PL/SQL procedure successfully completed.

SQL> — run some code
SQL> exec dbms_support.stop_trace;

PL/SQL procedure successfully completed.

SQL>

Use DBMS_SESSION to set trace in your own session

The next method for setting trace in our own session also is done using a built in package, this time DBMS_SESSION. here it is:

SQL> — in your own session using dbms_session
SQL> exec dbms_session.set_sql_trace(true);

PL/SQL procedure successfully completed.

SQL> — execut some code
SQL> exec dbms_session.set_sql_trace(false);

PL/SQL procedure successfully completed.

SQL>

using oradebug to set trace through SQL*Plus

oradebug is a debugging utility that is essentially undocumented and is intended for use by Oracle support analysts for various tasks one of which is that it can be used to set trace. oradebug is available from svrmgrl before Oracle 9i and from SQL*Plus after. The first step in using this tool is to find the OS PID or the Oracle PID of the process you want to analyse. You can do this as follows:

SQL> connect system/manager@sans as sysdba
Connected.
SQL> col sid for 999999
SQL> col serial# for 999999
SQL> col spid for a8
SQL> col username for a20
SQL> col osuser for a20
1 select s.sid,s.serial#,p.spid,p.pid,s.username,s.osuser
2 from v$session s,v$process p
3* where s.paddr=p.addr
SQL> /

SID SERIAL# SPID PID USERNAME OSUSER
——- ——- ——– ———- ——————– ——————–
1 1 2528 2 SYSTEM
2 1 2536 3 SYSTEM
3 1 2540 4 SYSTEM
4 1 2544 5 SYSTEM
5 1 2552 6 SYSTEM
6 1 2604 7 SYSTEM
7 1 2612 8 SYSTEM
8 1 2652 9 SYSTEM
10 343 3740 12 SYS ZULIA\pete
12 70 864 13 SCOTT ZULIA\pete

10 rows selected.

Now that we have found the Operating System PID and Oracle PID (values 864 and 13 in this case) of SCOTT’s session we can use this to set trace with the oradebug tool as follows:

SQL> — set the OS PID
SQL> oradebug setospid 864
Windows thread id: 864, image: ORACLE.EXE
SQL> — or set the Oracle pid
SQL> oradebug setorapid 13
Windows thread id: 864, image: ORACLE.EXE
SQL> — set the trace file size to unlimitd
SQL> oradebug unlimit
Statement processed.
SQL> — now turn on trace for SCOTT
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL> — run some queries in another session and then turn trace off
SQL> oradebug event 10046 trace name context off
Statement processed.

Some things to be aware of

You should be aware that some of these methods allow setting of extended trace and some do not. Those that allow extended trace are easy to spot. These methods include ways to set the trace level or include variables suitably named such as waits or binds which again enable extended trace facilities. Some trace methods have a default level such as set sql_trace=true which sets trace to level 8. The rest set trace to normal trace levels.

One other point to note is that we have looked first at ways to set trace in another session to the one you are logged into and also now at ways of setting trace in your own session, there is a third option, which is to set trace for the whole system (i.e for all users sessions), This is not recommended unless you know what you are doing and are monitoring trace as you can quickly fill the file system.

Setting trace at the instance level using the init.ora

Trace can be set in the database initialization file the init.ora file. If you use spfile then you can still use the init.ora file and then copy it to the spfile. Simply add the following line to the init.ora file:

sql_trace=true

You can also set timed_statistics and max_dump_file_size in the init.ora file in the same way. i.e

timed_statistics=true
max_dump_file_size=unlimited

Trace can also be disabled at the instance level by simply commenting out the same parameter or by deleting it. A commented line is shown next:

#sql_trace=true

Or you can set the same parameter to false:

sql_trace=false

A second instance level method – setting events

Another method that can be used to set trace at the instance level is to add an event (or multiple events)to the initialization file, the init.ora as described above. Again if you use spfile’s then you can copy the init.ora to spfile or use ALTER SYSTEM to set the value in the spfile. Here is an example of setting the trace event 10046 to level 12 in the initialization file:

# set the event in the init.ora
event = “10046 trace name context forever, level 12”
# to turn off the event simply comment out the line as follows:
# event = “10046 trace name context forever, level 12”

Using ALTER SESSION to set trace in your own session

The alter session command can be used to set trace for the current session as follows:

SQL> alter session set sql_trace=true;

Session altered.

SQL> — execute some code
SQL> alter session set sql_trace=false;

Session altered.

SQL>

This method can also be used to set timing and dump file size for the current session as follows:

SQL> alter session set timed_statistics=true;

Session altered.

SQL> alter session set max_dump_file_size=unlimited;

Session altered.

SQL>

Using ALTER SESSION to set extended trace using events

One last method I want to demonstrate is the alter session syntax to set events. Again stick to 10046 (trace) and do not attempt to set any of the other events that are available without Oracles say so in a supported system. Here is the example of setting trace to level 12, including binds and waits:

SQL> alter session set events ‘10046 trace name context forever, level 12’;

Session altered.

SQL> — execute some code
SQL> alter session set events ‘10046 trace name context off’;

Session altered.

SQL>

A sample logon trigger to set trace

Quite often you would like trace to be set for a session as soon as the user logs on. Also you may want to be able to set trace for a specific set of users when they log in. This can easily be done with a database logon trigger. Here is a sample trigger.

Connected to:
Personal Oracle9i Release 9.2.0.1.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 – Production

SQL> create or replace trigger set_trace after logon on database
2 begin
3 if user not in (‘SYS’,’SYSTEM’) then
4 execute immediate ‘alter session set timed_statistics=true’;
5 execute immediate ‘alter session set max_dump_file_size=unlimited’;
6 execute immediate ‘alter session set sql_trace=true’;
7 end if;
8 exception
9 when others then
10 null;
11 end;
12 /

Trigger created.

SQL> sho errors
No errors.
SQL>

OK, that was easy. You can also use the alter session set events ‘10046 trace name context forever,level 12’ syntax if you prefer You can also enable other checks within the trigger if need by using any valid PL/SQL logic that you prefer. One tip is that if you have any troubles with your system trigger and it causes logins to fail is to always include, as I have, an exception handler that calls null; for any error condition. If all else fails you can disable system triggers by setting the parameter _system_trig_enabled=false in the initialisation file. This undocumented / hidden parameter stops the processing of system triggers such as logon triggers.

Using ALTER SYSTEM to set trace at the instance level

Finally you can also use the alter system syntax to set trace at the system level. Here is a simple example:

SQL> alter system set sql_trace=true scope=spfile;

System altered.

SQL>
SQL> — to turn it off again do:
SQL> alter system set sql_trace=false scope=spfile

System altered.

SQL>

Checking the privileges of the packages used to set trace

Some of the packages used in this example have to be run as SYS or you need to be logged in AS SYSDBA or specific privileges need to be granted against those packages for the user that will run them. The default privileges for DBMS_SYSTEM, DBMS_SUPPORT and for DBMS_SESSION are showed next in output from who_can_access.sql (A script that shows privileges hierarchically for an object who’s name is passed in). Here they are:

— check who has access to dbms_system
who_can_access: Release 1.0.0.0.0 – Production on Fri Feb 27 12:53:24 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

get user input

NAME OF OBJECT TO CHECK [USER_OBJECTS]: dbms_system
OWNER OF THE OBJECT TO CHECK [USER]: sys
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [/tmp]:

Checking object => SYS.DBMS_SYSTEM
====================================================================

Object type is => PACKAGE (TAB)
Privilege => EXECUTE is granted to =>
Role => OEM_MONITOR which is granted to =>
User => SYS

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

— check who has access to dbms_support
who_can_access: Release 1.0.0.0.0 – Production on Fri Feb 27 12:54:29 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

get user input

NAME OF OBJECT TO CHECK [USER_OBJECTS]: dbms_support
OWNER OF THE OBJECT TO CHECK [USER]: sys
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [/tmp]:

Checking object => SYS.DBMS_SUPPORT
====================================================================

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

— check who has access to dbms_session
who_can_access: Release 1.0.0.0.0 – Production on Fri Feb 27 12:55:31 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

get user input

NAME OF OBJECT TO CHECK [USER_OBJECTS]: dbms_session
OWNER OF THE OBJECT TO CHECK [USER]: sys
OUTPUT METHOD Screen/File [S]:
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [/tmp]:

Checking object => SYS.DBMS_SESSION
====================================================================

Object type is => PACKAGE (TAB)
Privilege => EXECUTE is granted to =>
Role => PUBLIC

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

That’s it, there are many ways to set trace in your session, in others sessions and at system level. Also many ways to enable extended trace. Beware of the privileges needed to run some of them and beware of setting events explicitly.

New tracing methods in Oracle 10g – DBMS_MONITOR

Oracle 10g offers a new package to allow sessions to be traced end to end in multi-tier architectures that share sessions using connection pooling or multi-threading. This package allows applications written using for instance JDBC / Java or something like Forte to be traced where it would normally be very difficult to identify a database session belonging to a client as the sessions / clients pairings change with time.

The new functionality works in three levels. You can use the old SID / SERIAL# pairings to identify a session but you can also use a client identifier or a service name / module / action to identify a client session to be traced. The package also offers a set of procedures to allow statistics to be gathered for the same groups. These statistics can then be selected from dynamic views.

let’s now take a look at some of the features of this package.

Setting trace with DBMS_MONITOR using SID / SERIAL#

Trace can be set for the current user session, for the current session or for another users session. First lets look at tracing another users session. First we need to get the SID and SERIAL# – we will use SCOTT connected through SQL*Plus as our sample session:

SQL> select s.sid,s.serial#,s.username
2 from v$session s, v$process p
3 where s.paddr=p.addr
SQL> /


SID SERIAL# USERNAME
———- ———- ——————————
248 153 SCOTT
258 61 DBSNMP
251 418 SYSMAN
255 961 SYS
249 215

27 rows selected.

SQL>

OK as with previous methods we can use a SID / SERIAL# pair of 248 and 153. lets set trace for this user session:

SQL> exec dbms_monitor.session_trace_enable(248,153,TRUE,FALSE);

PL/SQL procedure successfully completed.

SQL> — execute some sql
SQL> — in the other session
SQL> — turn trace off
SQL> exec dbms_monitor.session_trace_disable(248,153);

PL/SQL procedure successfully completed.

SQL>

Setting trace at the session level using DBMS_MONITOR

The same procedures can be used to set trace for the session by omitting the serial#. This is demonstrated next:

SQL> exec dbms_monitor.session_trace_enable(248);

PL/SQL procedure successfully completed.

SQL> — execute some sql in the other session
SQL> — turn off trace
SQL> exec dbms_monitor.session_trace_disable(248);

PL/SQL procedure successfully completed.

SQL> — or you can turn it on with
SQL> exec dbms_monitor.session_trace_enable(248,null);

PL/SQL procedure successfully completed.

SQL> — turn off again with:
SQL> exec dbms_monitor.session_trace_disable(248,null);

PL/SQL procedure successfully completed.

SQL>

Setting trace for the current session using DBMS_MONITOR

Setting trace for the current user session is done by leaving out the SID and SERIAL# altogether by setting them to NULL. Here is an example:

SQL> — trace the current session
SQL> exec dbms_monitor.session_trace_enable(null,null);

PL/SQL procedure successfully completed.

SQL> — execute some code
SQL> — turn it off again
SQL> exec dbms_monitor.session_trace_disable(null,null);

PL/SQL procedure successfully completed.

SQL> — to get waits and binds do
SQL> exec dbms_monitor.session_trace_enable(null,null,true,true);

PL/SQL procedure successfully completed.

SQL> — execute some code
SQL> — then turn off tracec
SQL> exec dbms_monitor.session_trace_disable(null,null);

PL/SQL procedure successfully completed.

SQL> — or turn it on like this
SQL> exec dbms_monitor.session_trace_enable();

PL/SQL procedure successfully completed.

SQL> — execute some SQL and then turn off trace
SQL> exec dbms_monitor.session_trace_disable();

PL/SQL procedure successfully completed.

SQL>

That completes some of the ways to use DBMS_MONITOR to set trace using SID, SERIAL#, or at the session level or for the current session.

Set trace using a client identifier

Tracing using the client identifier allows trace to be set across multiple sessions as many Oracle shadow processes can work on behalf of one client. Also trace is persistent across all instances and restarts. First we need to see how the client identifier is set. This can be done using the DBMS_SESSION package as follows:

SQL> exec dbms_session.set_identifier(‘pete id’);

PL/SQL procedure successfully completed.

SQL>

We can check now for a specific identifier in the V$SESSION view with the client_identifier column.

SQL> select s.username,s.client_identifier
2 from v$session s,v$process p
3 where s.paddr=p.addr
4 and client_identifier is not null;

USERNAME
——————————
CLIENT_IDENTIFIER
—————————————————————-
SCOTT
pete id

SQL>

OK, now we can use this information to set trace for this client identifier as follows:

SQL> exec dbms_monitor.client_id_trace_enable(‘pete id’,true,false);

PL/SQL procedure successfully completed.

SQL> — wait for the client session to do something
SQL> — turn off trace as follows:
SQL> exec dbms_monitor.client_id_trace_disable(‘pete id’);

PL/SQL procedure successfully completed.

SQL>

That was quite easy!. next let’s look at setting trace at the service, module action levels.

Setting trace for service/module/action with DBMS_MONITOR

This method of setting trace acts hierarchically. The first level is that trace is set globally for the whole database (all instances) You can override this by setting an instance name in the call to turn on trace. For this example as I am on a single instance database I will leave this parameter at its default. There are three levels to the hierarchy. If we set ACTION to NULL then all actions for the module and service are traced. The next level, if we set MODULE to NULL then all actions for all modules for the specified service name are traced. The trace will be collected into multiple trace files and the new tool trcsess must be used to collate all the trace files into one usable file.

The service name can be set using the package DBMS_SERVICE and the procedure CREATE_SERVICE. Here is an example:

SQL> exec dbms_service.create_service(‘Test Service’,’test network’);

PL/SQL procedure successfully completed.

SQL> — it can be deleted with
SQL> exec dbms_service.delete_service(‘Test Service’);

PL/SQL procedure successfully completed.

SQL>

The service name can quite often be set already by the tool. It could be used to group together a set of programs / modules that perform some business task. Next let’s see how the module and actions can be set.

SQL> — set action
SQL> exec dbms_application_info.set_action(‘PAYMENT’);

PL/SQL procedure successfully completed.

SQL> — set the module
SQL> exec dbms_application_info.set_module(‘ACCOUNTS’,’PAYMENT’);

PL/SQL procedure successfully completed.

SQL>

To view the relevant service names, modules and actions for sessions in the database you can use the v$SESSION view as follows:

SQL> col service_name for a15 wrapped
SQL> col username for a15 wrapped
SQL> col module for a15 wrapped
SQL> col action for a15 wrapped
SQL> select s.username,s.service_name,s.module,s.action
2 from v$session s,v$process p
3 where s.paddr=p.addr;

USERNAME SERVICE_NAME MODULE ACTION
————— ————— ————— —————
SYSMAN SANS
SYSMAN SANS OEM.SystemPool
DBSNMP SYS$USERS emagent@emil (T
NS V1-V3)

DBSNMP SYS$USERS emagent@emil (T
NS V1-V3)

SYS$USERS
SYS SANS ACCOUNTS PAYMENT
SCOTT SANS SQL*Plus

29 rows selected.

SQL>

As we deleted our sample service name set up with DBMS_SERVICE.CREATE_SERVICE we will just use the default value SANS inserted by Oracle in our test case. Let’s test some of the methods of setting trace with this functionality.

SQL> — set trace for all modules and actions for SANS service name
SQL> exec dbms_monitor.serv_mod_act_trace_enable(‘SANS’,DBMS_MONITOR.ALL_MODULES,DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);

PL/SQL procedure successfully completed.

SQL> — turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable(‘SANS’);

PL/SQL procedure successfully completed.

SQL> — now trace all actions for service SANS and module accounts
SQL> exec dbms_monitor.serv_mod_act_trace_enable(‘SANS’,’ACCOUNTS’,DBMS_MONITOR.ALL_ACTIONS,TRUE,FALSE,NULL);

PL/SQL procedure successfully completed.

SQL> — now turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable(‘SANS’,’ACCOUNTS’);

PL/SQL procedure successfully completed.

SQL> — finally test service SANS, module ACCOUNTS and action PAYMENT
SQL> exec dbms_monitor.serv_mod_act_trace_enable(‘SANS’,’ACCOUNTS’,’PAYMENT’,TRUE,FALSE,NULL);

PL/SQL procedure successfully completed.

SQL> — turn it off
SQL> exec dbms_monitor.serv_mod_act_trace_disable(‘SANS’,’ACCOUNTS’,’PAYMENT’);

PL/SQL procedure successfully completed.

SQL> — you can turn on or off binds and waits as well or use the waits=>true
SQL> — syntax instead.
SQL>

OK, that wraps up the new procedures in 10g that can be used to turn on trace in different ways to capture true end to end trace for multi-tier applications. You should also be aware that DBMS_MONITOR also provides procedures to enable statistic gathering at the same levels of client identifier and service name/module/action. These statistics are stored and can then be accessed by selecting from V$SERV_MOD_ACT_STATS and V$CLIENT_STATS views. I will not detail those procedures here as this short paper is concentrating on trace only.

One last idea – use AUTOTRACE in SQL*Plus

OK, one final way to set and get trace, is to use the SQL*Plus AUTOTRACE facilities. There are a few settings that you can use. These are as follows:

* set autotrace off – The default – no output
* set autotrace on explain – This shows only the optimizer path
* set autotrace on statistics – This only shows SQL statistics
* set autotrace on – Includes both of the above
* set autotrace traceonly – As above but the query output is not displayed

One more final item – CBO trace 10053

One other event that you might like to try and experiment with is the 10053 event. This event traces the Cost Based Optimizer (CBO) and shows all of the plans and costs assigned to them that it tried in its search for the best cost and also is shows how it came to its decision. The 10053 event has two levels 1 and 2. More detail is emitted if level 1 is used rather than level 2. The output is again sent to a trace file in the directory specified by user_dump_dest. The trace is only generated if the SQL is hard parsed and also obviously uses the CBO. To get a trace file you can use any of the methods above that allow the event number to be specified. An example is:

SQL> alter session set events ‘10053 trace name context forever, level 1

Session altered.

SQL> — execute some SQL to create a CBO trace.
SQL> — turn CBO trace off
SQL> alter session set events ‘10053 trace name context off’;

Session altered.

SQL>

An excellent document describing how to interpret CBO trace files called “A Look under the Hood of CBO – the 10053 Event.pdf” has been written by Wolfgang Breitling of Centrex Consulting Corporation. The URL for Wolfgangs site is http://www.centrexcc.com/papers.html

Adds-on:
About 10046 tracing
10046 has 4 tracing levels
1 – Standard SQL_TRACE, the same as sql_trace
4 – Level 1 + bind values
8 – Level 1 + wait events
12 – Level 1 + Level 4 + Level 8
Like sql_trace,10046 can be set on system level or session level
1. system level
Add the parameter in:
event=”10046 trace name context forever,level 12″

2. session level
SQL> alter session set events ‘10046 trace name context forever’;
SQL> alter session set events ‘10046 trace name context forever, level 8’;
SQL> alter session set events ‘10046 trace name context off’;

Note: for finding the crossing trace file for current session, use this query to get the location:
select d.value||’/’||lower(rtrim(i.instance, chr(0)))||’_ora_’||p.spid||’.trc’ trace_file_name
from
( select p.spid
from v$mystat m,v$session s,v$process p
where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = ‘thread’ and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = ‘user_dump_dest’) d;

Using import (imp) or data pump import (impdp) to import a table without data results in the table’s statistics being locked in 10gR2

Filed under: [System Performance tuning] — zhefeng @ 9:03 am

when you import the table without data by using impdp, the statistics was locked after importing. Whatever you re-gather the statistics, the statistics won’t change and it won’t give your the locked error even.

The solution are varied from serveral way:
1. Gather the statistics with force=true
exec dbms_stats.unlock_schema_stats(ownname => 'WORLDDB9_REP', force => TRUE);

2. unlock the statistics using DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS, then gather statistics on the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS
EXEC DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'WORLDDB9_REP', tabname => 'TRIGGER_BASED');
exec dbms_stats.unlock_schema_stats(ownname => 'WORLDDB9_REP');

3. To prevent import (imp) from locking the table’s statistics when importing a table without the rows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table’s statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).

Metalink: Note:433240.1

December 8, 2008

Oracle RAC-stop crs autostart on one node

Filed under: [RAC] — zhefeng @ 12:24 pm

***Background:
One of the RAC node vanpgprodb05 always has some system level issue and every time when we reboot the machine the crs on that machine gets restart automatically. We don’t want that happen during the time when we are fixing it. So temporarily disable the crs autostart on that node is necessary.

***Related document on metalink:
Oracle notes: Note:298073.1

The CRS profiles for these resources must be modified for the new CRS behavior
to take effect for all RAC databases installed on the cluster. If, however,
the affect of the change is to be limited to a subset of installed databases,
the list of resources needs to be filtered further. (The rest of this section
should be skipped if the new CRS behavior is to be in effect for all databases
installed on the cluster.)
Please note that since more than one database may be installed on a cluster,
to modify the level of protection for a particular database, one must identify
the resources that represent entities of this database. This may be easily
accomplished since the names of the resources belonging to the above- stated
types always start with ora.. For instance, ora.linux.db
means that the resource belongs to the database named linux. Only resources of
the above-enumerated types that belong to the selected databases will need to
have their profiles modified.
MODIFYING RESOURCE PROFILES
Please note that Oracle strongly discourages any modifications made to CRS
profiles for any resources starting with . Never make any modifications to
CRS profiles for resources but the ones explicitly described below in
this document.

To modify a profile attribute for a resource, the following steps must be
followed:
1.Generate the resource profile file by issuing the following command:
crs_stat -p resource_name > $CRS_HOME/crs/public/resource_name.cap

2.Update desired attributes by editing the file created in step 1.

3.Commit the updates made as a part of the previous step by issuing the
following command
crs_register -u resource_name

4. Verify the updates have been committed by issuing the following command
crs_stat -p resource_name

For each of the resources identified as a part of the preceding section, the
following modifications must be made:
1.Resources of type inst must have the following attributes modified
AUTO_START must be set to 2
RESTART_ATTEMPTS must be set to 0 or 1. The former value will prevent
CRS from attempting to restart a failed instance at all while the latter
will grant it a single attempt; if this only attempt is unsuccessful,
CRS will leave the instance as is.
2.Resources of type db, srv, cs must have the following attributes modified
AUTO_START must be set to 2

***Practice steps:
1. Objective: to disable the crs autostart on node vanpgprodb05
2. catch all the resources profile on vanpgprodb05
1). from vanpgprodb01 (at that time the vanpgrpodb05 is down), run these catch commands
$crs_stat -p ora.p10c.p10c5.inst>$CRS_HOME/crs/public/ora.p10c.p10c5.inst.cap
$crs_stat -p ora.vanpgprodb05.ASM3.asm>$CRS_HOME/crs/public/ora.vanpgprodb05.ASM3.asm.cap
$crs_stat -p ora.vanpgprodb05.gsd>$CRS_HOME/crs/public/ora.vanpgprodb05.gsd.cap
$crs_stat -p ora.vanpgprodb05.LISTENER_VANPGPRODB05.lsnr>$CRS_HOME/crs/public/ora.vanpgprodb05.LISTENER_VANPGPRODB05.lsnr.cap
$crs_stat -p ora.vanpgprodb05.ons>$CRS_HOME/crs/public/ora.vanpgprodb05.ons.cap
$crs_stat -p ora.vanpgprodb05.vip>$CRS_HOME/crs/public/ora.vanpgprodb05.vip.cap

2). editing these .cap files, change parameter autostart=2 (not autostart).

3). crs_register -u resource_name
$crs_register -u ora.p10c.p10c5.inst
$crs_register -u ora.vanpgprodb05.ASM3.asm
$crs_register -u ora.vanpgprodb05.gsd
$crs_register -u ora.vanpgprodb05.LISTENER_VANPGPRODB05.lsnr

4). use crs_stat -p resource_name to verify the parameter has been changed
crs_stat -p|grep AUTO_START=2 -B 7

5). reboot the node vanpgprodb05 now, after reboot
[oracle@vanpgprodb01 public]$ crs_stat -t
Name Type Target State Host
————————————————————
ora.p10c.db application ONLINE ONLINE vanp…db02
ora….c1.inst application ONLINE ONLINE vanp…db01
ora….c2.inst application ONLINE ONLINE vanp…db02
ora….c5.inst application OFFLINE OFFLINE
ora….SM1.asm application ONLINE ONLINE vanp…db01
ora….01.lsnr application ONLINE ONLINE vanp…db01
ora….b01.gsd application ONLINE ONLINE vanp…db01
ora….b01.ons application ONLINE ONLINE vanp…db01
ora….b01.vip application ONLINE ONLINE vanp…db01
ora….SM2.asm application ONLINE ONLINE vanp…db02
ora….02.lsnr application ONLINE ONLINE vanp…db02
ora….b02.gsd application ONLINE ONLINE vanp…db02
ora….b02.ons application ONLINE ONLINE vanp…db02
ora….b02.vip application ONLINE ONLINE vanp…db02
ora….SM3.asm application OFFLINE OFFLINE
ora….05.lsnr application OFFLINE OFFLINE
ora….b05.gsd application OFFLINE OFFLINE
ora….b05.ons application ONLINE ONLINE vanp…db05
ora….b05.vip application ONLINE ONLINE vanp…db05

Note: for ONS, the autostart is “AUTO_START=always”, and you can’t take vip off

6). the disabling works! don’t forget the change the autostart back when problem gets resolved. repeat steps 2)-4)

Adds-on: if you want to stop the autostart for css daemon either, then do this:
/etc/init.d/init.cssd stop

If you want to stop it from autostarting after bootup;
[root@vanpgprodb06 rc3.d]# /etc/init.d/init.crs disable
Automatic startup disabled for system boot.

December 5, 2008

Moving Table/Index to a New Segment or Tablespace

Filed under: [System Performance tuning] — zhefeng @ 11:54 am

The following statement moves the my_table to a new segment, specifying new storage parameters:

ALTER TABLE my_table MOVE
STORAGE ( INITIAL 20K
NEXT 40K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 0 );

Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.

If the table includes LOB column(s), this statement can be used to move the table along with LOB data and LOB index segments (associated with this table) which the user explicitly specifies. If not specified, the default is to not move the LOB data and LOB index segments.

To move a TABLE TO another TABLESPACE, issue the following command:
ALTER TABLE [table_name] MOVE TABLESPACE [new_tablespace];

To move an INDEX, use the following command:
ALTER INDEX [index_name] REBUILD TABLESPACE [new_tablespace];

To move the LOB when moving the table, use the following:
ALTER TABLE [table_name] MOVE TABLESPACE [new_tablespace] LOB (lob_item) STORE AS (TABLESPACE [another_new_tablespace]);

To move the PARTITION TABLE to another TABLESPACE, use:
ALTER TABLE [table_name] MOVE PARTITION [partition_name] TABLESPACE [new_tablespace];

November 7, 2008

10g RAC installation – copy files to remote node error

Filed under: [Installation] — zhefeng @ 9:36 am

When i installed the database software on two nodes RAC, got error like below:

When copying files to remote nodes during a 10.2 RAC installation you may see the following error on the screen and in the installActions.log file:

INFO: Saving Cluster Inventory
SEVERE: oracle.sysman.oii.oiip.oiipg.OiipgRemoteFileOperationException: Bootstrapping installer to nodes failed.Dir path
is /tmp/OraInstall2005-06-01_04-11-13PM. [PRKC-1002 : All the submitted commands did not execute successfully]
———————————————————————————-
opcbrh2:
/bin/tar: ./common/rulemap.xml: time stamp 2005-06-01 16:11:22 is 609 s in the future
/bin/tar: ./common: time stamp 2005-06-01 16:13:24 is 731 s in the future
/bin/tar: ./cpuinfo.txt: time stamp 2005-06-01 16:13:29 is 736 s in the future
…continued…

That’s because the two nodes have different time. The solution is using NTP on both nodes and sync the time to the same.

1. edit /etc/ntp.conf

add one line:

server ntpdhost.rd.crystald.net  #you can also use other NTP servers like http://www.pool.ntp.org/

2. sync the time with NTP server

#ntpdate -u ntpdhost.rd.crystald.net

3. start the NTP service

#service ntpd start

4.test date for both node

bash-3.1$ date;ssh vanpgvmrac02 date
Fri Nov  7 09:35:33 PST 2008
Fri Nov  7 09:35:33 PST 2008

After the time has been synchronized, the installation could be completed successfully.

« Newer PostsOlder Posts »

Blog at WordPress.com.