Oracle for Beginners to Expert

Friday, October 3, 2014

Upgrading Oracle database 11.2.0.2 to 11.2.0.4

1) Patch Set to 11.2.0.3 has been suspended by oracle it means we need to go to 11.2.0.4

Step 1) Download and Install the 11.2.0.4 software binaries
Step 2) Run the pre-upgarde tool . From the existing oracle instance
$ORACLE_HOME/rdbms/admin/utlu112i.sql

If pre-upgrade tool is not run then error is obtained at the time of upgrade

Step 3) Run dbupgdiag.sql script from the below My Oracle Support article to verify that all the components in dba_registry are valid and no invalid data dictionary objects exist in dba_objects

Metalink id :-- Note 556610.1 ( For downloading the script for step 3)

Step 4)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

Step 5) After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

Step 6) Take the backup of the database before upgrade

rman target /
run
{
backup database plus archivelog ;
}

Step 7) Shutdown the database

Step 8) Copy the pfile,passwordfile to new dbs directory from old dbs directory

Step 9) Copy the listener.ora,tnsnames.ora and sqlnet.ora from old $ORACLE_HOME/network/admin to new $ORACLE_HOME/network/admin

Step 10) Set ORACLE_HOME to new binary location

Step 11) sqlplus / as sysdba
sql > startup upgrade
sql > @$ORACLE_HOME/rdbms/admin/catupgrd.sql;
sql > shutdown immediate
Step 12) Restart the database in normal mode

Step 13) @$ORACLE_HOME/rdbms/admin/catuppst.sql

Step 14) @$ORACLE_HOME/rdbms/admin/utlrp.sql;

Step 15) Run Script from step 3

Step 16) Upgrade the time zone to latest version using DBMS_DST

Follow document link Note 1201253.1

Tuesday, September 30, 2014

Parallel Processing

Parallel Processing should be used under this two conditions

1) When plenty of free resource is available . The aim of PX is to reduce the respnose time by distributing the work done by single process

2) It can used for SQL statements that take more than a dozen seconds to execute serially

If PX is commonly used for many SQL statements,the degree of parallelism should be set at the table or index level.

If it is used only for specific batches or reports . It is better to enable it at the session level or through hints

Insert statements with values clause cannot be parallelized

DML --> Insert,update,delete & merge can be executed in parallel when

1) Table has a trigger
2) A table has either a foreign key constraint refering itself
3) an object column is modified
4) a clustered or temporary table is modified

An actual distribution for a SQL statement , you can use the dynamic performance view v$pq_tqstat.

Information provided for current session and last SQL statements

Direct path insert gives better performance as it generates minimum undo . In fact undo is generated for only space management operation . For Ex :-- To increase the high watermark and to add new extent to the segment and not for the rows contained in the blocks that are inserted by direct-path .

The purpose of minimal logging is t
\'o minimize the redo generation

You can set minimual logging by setting parameter nologging at the table or partition level

Mimimial loggging is supported for direct path loads and some DDL Statements

Fetching numerous row at a time is called row prefetching

Row prefetching is enabled by JDBC drive by default

Sunday, September 28, 2014

Cluster startup error

Service gpnpd failed to come up

Resolution as follows

[root@rac1 bin]# ./crsctl start cluster
CRS-2672: Attempting to start 'ora.evmd' on 'rac1'
CRS-2676: Start of 'ora.evmd' on 'rac1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rac1'
CRS-2674: Start of 'ora.gpnpd' on 'rac1' failed
CRS-2673: Attempting to stop 'ora.evmd' on 'rac1'
CRS-2677: Stop of 'ora.evmd' on 'rac1' succeeded
CRS-4000: Command Start failed, or completed with errors.

[root@rac1 bin]# pwd

/u01/app/12.1.0/grid/bin

[root@rac1 bin]# ./crsctl stat res -t -init

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.asm

1 ONLINE OFFLINE Instance Shutdown,ST

ABLE

ora.cluster_interconnect.haip

1 ONLINE OFFLINE STABLE

ora.crf

1 ONLINE ONLINE rac1 STABLE

ora.crsd

1 ONLINE OFFLINE STABLE

ora.cssd

1 ONLINE OFFLINE STABLE

ora.cssdmonitor

1 ONLINE OFFLINE STABLE

ora.ctssd

1 ONLINE OFFLINE STABLE

ora.diskmon

1 ONLINE OFFLINE STABLE

ora.evmd

1 ONLINE OFFLINE STABLE

ora.gipcd

1 ONLINE ONLINE rac1 STABLE

ora.gpnpd

1 ONLINE OFFLINE STABLE

ora.mdnsd

1 ONLINE ONLINE rac1 STABLE

ora.storage

1 ONLINE OFFLINE STABLE

--------------------------------------------------------------------------------

[root@rac1 bin]# ./u01/app/11.2.0/grid/bin/ocrcheck

-bash: ./u01/app/11.2.0/grid/bin/ocrcheck: No such file or directory

[root@rac1 bin]# cd /u01/app/11.2.0/grid/bin/

-bash: cd: /u01/app/11.2.0/grid/bin/: No such file or directory

[root@rac1 bin]# pwd

/u01/app/12.1.0/grid/bin

[root@rac1 bin]# ./ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 4

Total space (kbytes) : 409568

Used space (kbytes) : 3380

Available space (kbytes) : 406188

ID : 1384592632

Device/File Name : +DATA

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@rac1 bin]# pwd

/u01/app/12.1.0/grid/bin

You have new mail in /var/spool/mail/root

[root@rac1 bin]# ./crsctl start crs -excl

CRS-2672: Attempting to start 'ora.evmd' on 'rac1'

CRS-2676: Start of 'ora.evmd' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.gpnpd' on 'rac1'

CRS-2676: Start of 'ora.gpnpd' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac1'

CRS-2676: Start of 'ora.cssdmonitor' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.cssd' on 'rac1'

CRS-2672: Attempting to start 'ora.diskmon' on 'rac1'

CRS-2676: Start of 'ora.diskmon' on 'rac1' succeeded

CRS-2676: Start of 'ora.cssd' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2672: Attempting to start 'ora.ctssd' on 'rac1'

CRS-2676: Start of 'ora.ctssd' on 'rac1' succeeded

CRS-5017: The resource action "ora.cluster_interconnect.haip start" encountered the following error:

Start action for HAIP aborted. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac1/crs/trace/ohasd_orarootagent_root.trc".

CRS-2674: Start of 'ora.cluster_interconnect.haip' on 'rac1' failed

CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'

CRS-5017: The resource action "ora.cluster_interconnect.haip start" encountered the following error:

Start action for HAIP aborted. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac1/crs/trace/ohasd_orarootagent_root.trc".

CRS-2674: Start of 'ora.cluster_interconnect.haip' on 'rac1' failed

CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

CRS-4000: Command Start failed, or completed with errors.

[root@rac1 bin]# ./crsctl stop resource ora.crsd -init

[root@rac1 bin]# ./crsctl stop resource ora.crsd -init;

CRS-2500: Cannot stop resource 'ora.crsd' as it is not running

CRS-4000: Command Stop failed, or completed with errors.

[root@rac1 bin]# ./crsctl start crs -excl -nocrs

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'

CRS-5017: The resource action "ora.cluster_interconnect.haip start" encountered the following error:

Start action for HAIP aborted. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac1/crs/trace/ohasd_orarootagent_root.trc".

CRS-2674: Start of 'ora.cluster_interconnect.haip' on 'rac1' failed

CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

CRS-4000: Command Start failed, or completed with errors.

[root@rac1 bin]# pwd

/u01/app/12.1.0/grid/bin

You have new mail in /var/spool/mail/root

[root@rac1 bin]# ./crsctl stat res -t -init

--------------------------------------------------------------------------------

Name Target State Server State details

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.asm

1 ONLINE OFFLINE Instance Shutdown,ST

ABLE

ora.cluster_interconnect.haip

1 ONLINE OFFLINE STABLE

ora.crf

1 ONLINE ONLINE rac1 STABLE

ora.crsd

1 OFFLINE OFFLINE STABLE

ora.cssd

1 ONLINE ONLINE rac1 STABLE

ora.cssdmonitor

1 ONLINE ONLINE rac1 STABLE

ora.ctssd

1 ONLINE ONLINE rac1 OBSERVER,STABLE

ora.diskmon

1 OFFLINE OFFLINE STABLE

ora.evmd

1 ONLINE INTERMEDIATE rac1 STABLE

ora.gipcd

1 ONLINE ONLINE rac1 STABLE

ora.gpnpd

1 ONLINE ONLINE rac1 STABLE

ora.mdnsd

1 ONLINE ONLINE rac1 STABLE

ora.storage

1 ONLINE OFFLINE STABLE

--------------------------------------------------------------------------------

[root@rac1 bin]# gpnptool get

-bash: gpnptool: command not found

Check the gpnpd profile

[root@rac1 bin]# ./gpnptool get

Warning: some command line parameters were defaulted. Resulting command line:

./gpnptool.bin get -o-

<?xml version="1.0" encoding="UTF-8"?><gpnp:GPnP-Profile Version="1.0" xmlns="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:gpnp="http://www.grid-pnp.org/2005/11/gpnp-profile" xmlns:orcl="http://www.oracle.com/gpnp/2005/11/gpnp-profile" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.grid-pnp.org/2005/11/gpnp-profile gpnp-profile.xsd" ProfileSequence="6" ClusterUId="7e7ad9a8f8245f05ffad6b38a93508ac" ClusterName="rac-scan" PALocation=""><gpnp:Network-Profile><gpnp:HostNetwork id="gen" HostName="*"><gpnp:Network id="net1" IP="192.168.10.0" Adapter="eth0" Use="cluster_interconnect"/><gpnp:Network id="net2" IP="192.168.56.0" Adapter="eth1" Use="public"/></gpnp:HostNetwork></gpnp:Network-Profile><orcl:CSS-Profile id="css" DiscoveryString="+asm" LeaseDuration="400"/><orcl:ASM-Profile id="asm" DiscoveryString="" SPFile="+DATA/rac-scan/ASMPARAMETERFILE/registry.253.858426557" Mode="legacy"/><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"/><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"/><ds:Reference URI=""><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"/><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"> <InclusiveNamespaces xmlns="http://www.w3.org/2001/10/xml-exc-c14n#" PrefixList="gpnp orcl xsi"/></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"/><ds:DigestValue>qD+D2x79xr16FoMfaWXKc1AcYBE=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>Yb2cGmHj/flpEvMi4ZzkqUfr/EsPX3yXtqXTraPLG3pffP5IEfwpzF+Q5WQCoYFq5byEmcslYZKl+eNe4Wxog0c183dk5NbAKzdT4S818XD8eh8+XrJP3XtXt2qA2O8FvcLK++1m27Eus7TW+MG/DTUU58V1z2CFyHh9rCF8QaY=</ds:SignatureValue></ds:Signature></gpnp:GPnP-Profile>

Success.

Change the ip address of eth1 and eth0

[root@rac1 bin]# ifconfig eth1 inet 192.168.56.2

[root@rac1 bin]# ifconfig eth0

[root@rac1 bin]# cat /etc/hosts

127.0.0.1 localhost.localdomain localhost

192.168.56.2 rac1.localdomain rac1

192.168.10.3 rac1-priv.localdomain rac1-priv

192.168.56.4 rac1-vip.localdomain rac1-vip

192.168.56.5 rac-scan.localdomain rac-scan

192.168.56.6 rac2.localdomain rac2

192.168.10.7 rac2-priv.localdomain rac2-priv

192.168.56.8 rac2-vip.localdomain rac2-vip

[root@rac1 bin]# ifconfig eth0 inet 192.168.10.3

[root@rac1 bin]# ./crsctl start res ora.cluster_interconnect.haip -init

CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'rac1'

CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.asm' on 'rac1'

CRS-2676: Start of 'ora.asm' on 'rac1' succeeded

CRS-2672: Attempting to start 'ora.storage' on 'rac1'

CRS-2676: Start of 'ora.storage' on 'rac1' succeeded

[root@rac1 bin]# ./gpnptool get

Warning: some command line parameters were defaulted. Resulting command line:

./gpnptool.bin get -o-

Success.

[root@rac1 bin]# ifconfig -a

eth0 Link encap:Ethernet HWaddr 08:00:27:BE:F9:01

inet addr:192.168.10.3 Bcast:192.168.10.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:41660 errors:0 dropped:0 overruns:0 frame:0

TX packets:18776 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:3665536 (3.4 MiB) TX bytes:4289493 (4.0 MiB)

eth0:1 Link encap:Ethernet HWaddr 08:00:27:BE:F9:01

inet addr:169.254.229.41 Bcast:169.254.255.255 Mask:255.255.0.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

eth1 Link encap:Ethernet HWaddr 08:00:27:3A:51:18

inet addr:192.168.56.2 Bcast:192.168.56.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:24668 errors:0 dropped:0 overruns:0 frame:0

TX packets:253 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:1825506 (1.7 MiB) TX bytes:28575 (27.9 KiB)

eth2 Link encap:Ethernet HWaddr 08:00:27:11:31:D5

inet addr:10.0.0.19 Bcast:10.0.0.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:10052 errors:0 dropped:0 overruns:0 frame:0

TX packets:123 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:1424473 (1.3 MiB) TX bytes:20276 (19.8 KiB)

lo Link encap:Local Loopback

inet addr:127.0.0.1 Mask:255.0.0.0

UP LOOPBACK RUNNING MTU:16436 Metric:1

RX packets:3126 errors:0 dropped:0 overruns:0 frame:0

TX packets:3126 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:0

RX bytes:3800660 (3.6 MiB) TX bytes:3800660 (3.6 MiB)

[root@rac1 bin]# ifconfig eth1

eth1 Link encap:Ethernet HWaddr 08:00:27:3A:51:18

inet addr:192.168.56.2 Bcast:192.168.56.255 Mask:255.255.255.0

UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1

RX packets:24709 errors:0 dropped:0 overruns:0 frame:0

TX packets:259 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:1828108 (1.7 MiB) TX bytes:31067 (30.3 KiB)

Start the services

[root@rac1 bin]# ./crsctl start res ora.crsd -init

CRS-2672: Attempting to start 'ora.crsd' on 'rac1'

CRS-2676: Start of 'ora.crsd' on 'rac1' succeeded

[root@rac1 bin]# ./crsctl start res ora.cssd -init

CRS-5702: Resource 'ora.cssd' is already running on 'rac1'

CRS-4000: Command Start failed, or completed with errors.

[root@rac1 bin]#

Output of OCRCHECK

OCR :-- Oracle Cluster Registry

Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 3380
Available space (kbytes) : 406188
ID : 1384592632
Device/File Name : +DATA
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

Clusterverification Utility Sample Output

-bash-3.2$ ./runcluvfy.sh stage -pre crsinst -fixup -n rac1,rac2 -verbose

Performing pre-checks for cluster services setup

Checking node reachability...

Check: Node reachability from node "rac1"
Destination Node Reachable?
------------------------------------ ------------------------
rac2 yes
rac1 yes
Result: Node reachability check passed from node "rac1"

Checking user equivalence...

Check: User equivalence for user "oracle"
Node Name Comment
------------------------------------ ------------------------
rac2 passed
rac1 passed
Result: User equivalence check passed for user "oracle"

Checking node connectivity...

Checking hosts config file...
Node Name Status Comment
------------ ------------------------ ------------------------
rac2 passed
rac1 passed

Verification of the hosts config file successful

Interface information for node "rac2"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.56.6 192.168.56.0 0.0.0.0 UNKNOWN 08:00:27:F0:6C:E6 1500
eth1 192.168.10.7 192.168.10.0 0.0.0.0 UNKNOWN 08:00:27:37:97:F2 1500

Interface information for node "rac1"
Name IP Address Subnet Gateway Def. Gateway HW Address MTU
------ --------------- --------------- --------------- --------------- ----------------- ------
eth0 192.168.56.2 192.168.56.0 0.0.0.0 10.0.0.1 08:00:27:BE:F9:01 1500
eth1 192.168.10.3 192.168.10.0 0.0.0.0 10.0.0.1 08:00:27:3A:51:18 1500
eth2 10.0.0.19 10.0.0.0 0.0.0.0 10.0.0.1 08:00:27:11:31:D5 1500

Check: Node connectivity of subnet "192.168.56.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac2:eth0 rac1:eth0 yes
Result: Node connectivity passed for subnet "192.168.56.0" with node(s) rac2,rac1

Check: TCP connectivity of subnet "192.168.56.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:192.168.56.2 rac2:192.168.56.6 passed
Result: TCP connectivity check passed for subnet "192.168.56.0"

Check: Node connectivity of subnet "192.168.10.0"

WARNING:
Make sure IP address "192.168.10.3" is up and is a valid IP address on node "rac1"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac2:eth1 rac1:eth1 no
Result: Node connectivity failed for subnet "192.168.10.0"

Check: TCP connectivity of subnet "192.168.10.0"
Source Destination Connected?
------------------------------ ------------------------------ ----------------
rac1:192.168.10.3 rac2:192.168.10.7 failed
Result: TCP connectivity check failed for subnet "192.168.10.0"

Check: Node connectivity of subnet "10.0.0.0"
Result: Node connectivity passed for subnet "10.0.0.0" with node(s) rac1

Check: TCP connectivity of subnet "10.0.0.0"
Result: TCP connectivity check passed for subnet "10.0.0.0"

Interfaces found on subnet "192.168.56.0" that are likely candidates for a private interconnect are:
rac2 eth0:192.168.56.6
rac1 eth0:192.168.56.2

WARNING:
Could not find a suitable set of interfaces for VIPs

Result: Node connectivity check failed

Check: Total memory
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 7.81GB (8188052.0KB) 1.5GB (1572864.0KB) passed
rac1 7.81GB (8188052.0KB) 1.5GB (1572864.0KB) passed
Result: Total memory check passed

Check: Available memory
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 7.28GB (7636388.0KB) 50MB (51200.0KB) passed
rac1 7.17GB (7518068.0KB) 50MB (51200.0KB) passed
Result: Available memory check passed

Check: Swap space
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 9.76GB (1.0233364E7KB) 7.81GB (8188052.0KB) passed
rac1 9.76GB (1.0233364E7KB) 7.81GB (8188052.0KB) passed
Result: Swap space check passed

Check: Free disk space for "rac2:/tmp"
Path Node Name Mount point Available Required Comment
---------------- ------------ ------------ ------------ ------------ ------------
/tmp rac2 /tmp 9.47GB 1GB passed
Result: Free disk space check passed for "rac2:/tmp"

Check: Free disk space for "rac1:/tmp"
Path Node Name Mount point Available Required Comment
---------------- ------------ ------------ ------------ ------------ ------------
/tmp rac1 /tmp 9.14GB 1GB passed
Result: Free disk space check passed for "rac1:/tmp"

Check: User existence for "oracle"
Node Name Status Comment
------------ ------------------------ ------------------------
rac2 exists passed
rac1 exists passed
Result: User existence check passed for "oracle"

Check: Group existence for "oinstall"
Node Name Status Comment
------------ ------------------------ ------------------------
rac2 exists passed
rac1 exists passed
Result: Group existence check passed for "oinstall"

Check: Group existence for "dba"
Node Name Status Comment
------------ ------------------------ ------------------------
rac2 exists passed
rac1 exists passed
Result: Group existence check passed for "dba"

Check: Membership of user "oracle" in group "oinstall" [as Primary]
Node Name User Exists Group Exists User in Group Primary Comment
---------------- ------------ ------------ ------------ ------------ ------------
rac2 yes yes yes yes passed
rac1 yes yes yes yes passed
Result: Membership check for user "oracle" in group "oinstall" [as Primary] passed

Check: Membership of user "oracle" in group "dba"
Node Name User Exists Group Exists User in Group Comment
---------------- ------------ ------------ ------------ ----------------
rac2 yes yes yes passed
rac1 yes yes yes passed
Result: Membership check for user "oracle" in group "dba" passed

Check: Run level
Node Name run level Required Comment
------------ ------------------------ ------------------------ ----------
rac2 5 3,5 passed
rac1 5 3,5 passed
Result: Run level check passed

Check: Hard limits for "maximum open file descriptors"
Node Name Type Available Required Comment
---------------- ------------ ------------ ------------ ----------------
rac2 hard 131072 65536 passed
rac1 hard 131072 65536 passed
Result: Hard limits check passed for "maximum open file descriptors"

Check: Soft limits for "maximum open file descriptors"
Node Name Type Available Required Comment
---------------- ------------ ------------ ------------ ----------------
rac2 soft 131072 1024 passed
rac1 soft 131072 1024 passed
Result: Soft limits check passed for "maximum open file descriptors"

Check: Hard limits for "maximum user processes"
Node Name Type Available Required Comment
---------------- ------------ ------------ ------------ ----------------
rac2 hard 131072 16384 passed
rac1 hard 131072 16384 passed
Result: Hard limits check passed for "maximum user processes"

Check: Soft limits for "maximum user processes"
Node Name Type Available Required Comment
---------------- ------------ ------------ ------------ ----------------
rac2 soft 131072 2047 passed
rac1 soft 131072 2047 passed
Result: Soft limits check passed for "maximum user processes"

Check: System architecture
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 x86_64 x86_64 passed
rac1 x86_64 x86_64 passed
Result: System architecture check passed

Check: Kernel version
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 2.6.32-100.26.2.el5 2.6.18 passed
rac1 2.6.32-100.26.2.el5 2.6.18 passed
Result: Kernel version check passed

Check: Kernel parameter for "semmsl"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 250 250 passed
rac1 250 250 passed
Result: Kernel parameter check passed for "semmsl"

Check: Kernel parameter for "semmns"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 32000 32000 passed
rac1 32000 32000 passed
Result: Kernel parameter check passed for "semmns"

Check: Kernel parameter for "semopm"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 100 100 passed
rac1 100 100 passed
Result: Kernel parameter check passed for "semopm"

Check: Kernel parameter for "semmni"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 142 128 passed
rac1 142 128 passed
Result: Kernel parameter check passed for "semmni"

Check: Kernel parameter for "shmmax"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 4398046511104 536870912 passed
rac1 4398046511104 536870912 passed
Result: Kernel parameter check passed for "shmmax"

Check: Kernel parameter for "shmmni"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 4096 4096 passed
rac1 4096 4096 passed
Result: Kernel parameter check passed for "shmmni"

Check: Kernel parameter for "shmall"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 1073741824 2097152 passed
rac1 1073741824 2097152 passed
Result: Kernel parameter check passed for "shmall"

Check: Kernel parameter for "file-max"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 6815744 6815744 passed
rac1 6815744 6815744 passed
Result: Kernel parameter check passed for "file-max"

Check: Kernel parameter for "ip_local_port_range"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 between 9000 & 65500 between 9000 & 65500 passed
rac1 between 9000 & 65500 between 9000 & 65500 passed
Result: Kernel parameter check passed for "ip_local_port_range"

Check: Kernel parameter for "rmem_default"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 262144 262144 passed
rac1 262144 262144 passed
Result: Kernel parameter check passed for "rmem_default"

Check: Kernel parameter for "rmem_max"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 4194304 4194304 passed
rac1 4194304 4194304 passed
Result: Kernel parameter check passed for "rmem_max"

Check: Kernel parameter for "wmem_default"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 262144 262144 passed
rac1 262144 262144 passed
Result: Kernel parameter check passed for "wmem_default"

Check: Kernel parameter for "wmem_max"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 1048576 1048576 passed
rac1 1048576 1048576 passed
Result: Kernel parameter check passed for "wmem_max"

Check: Kernel parameter for "aio-max-nr"
Node Name Configured Required Comment
------------ ------------------------ ------------------------ ----------
rac2 3145728 1048576 passed
rac1 3145728 1048576 passed
Result: Kernel parameter check passed for "aio-max-nr"

Check: Package existence for "make-3.81"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 make-3.81-3.el5 make-3.81 passed
rac1 make-3.81-3.el5 make-3.81 passed
Result: Package existence check passed for "make-3.81"

Check: Package existence for "binutils-2.17.50.0.6"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 binutils-2.17.50.0.6-14.el5 binutils-2.17.50.0.6 passed
rac1 binutils-2.17.50.0.6-14.el5 binutils-2.17.50.0.6 passed
Result: Package existence check passed for "binutils-2.17.50.0.6"

Check: Package existence for "gcc-4.1.2"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 gcc-4.1.2-50.el5 gcc-4.1.2 passed
rac1 gcc-4.1.2-50.el5 gcc-4.1.2 passed
Result: Package existence check passed for "gcc-4.1.2"

Check: Package existence for "libaio-0.3.106 (i386)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 libaio-0.3.106-5 (i386) libaio-0.3.106 (i386) passed
rac1 libaio-0.3.106-5 (i386) libaio-0.3.106 (i386) passed
Result: Package existence check passed for "libaio-0.3.106 (i386)"

Check: Package existence for "libaio-0.3.106 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 libaio-0.3.106-5 (x86_64) libaio-0.3.106 (x86_64) passed
rac1 libaio-0.3.106-5 (x86_64) libaio-0.3.106 (x86_64) passed
Result: Package existence check passed for "libaio-0.3.106 (x86_64)"

Check: Package existence for "glibc-2.5-24 (i686)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 glibc-2.5-58 (i686) glibc-2.5-24 (i686) passed
rac1 glibc-2.5-58 (i686) glibc-2.5-24 (i686) passed
Result: Package existence check passed for "glibc-2.5-24 (i686)"

Check: Package existence for "glibc-2.5-24 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 glibc-2.5-58 (x86_64) glibc-2.5-24 (x86_64) passed
rac1 glibc-2.5-58 (x86_64) glibc-2.5-24 (x86_64) passed
Result: Package existence check passed for "glibc-2.5-24 (x86_64)"

Check: Package existence for "compat-libstdc++-33-3.2.3 (i386)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 compat-libstdc++-33-3.2.3-61 (i386) compat-libstdc++-33-3.2.3 (i386) passed
rac1 compat-libstdc++-33-3.2.3-61 (i386) compat-libstdc++-33-3.2.3 (i386) passed
Result: Package existence check passed for "compat-libstdc++-33-3.2.3 (i386)"

Check: Package existence for "compat-libstdc++-33-3.2.3 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 compat-libstdc++-33-3.2.3-61 (x86_64) compat-libstdc++-33-3.2.3 (x86_64) passed
rac1 compat-libstdc++-33-3.2.3-61 (x86_64) compat-libstdc++-33-3.2.3 (x86_64) passed
Result: Package existence check passed for "compat-libstdc++-33-3.2.3 (x86_64)"

Check: Package existence for "elfutils-libelf-0.125 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 elfutils-libelf-0.137-3.el5 (x86_64) elfutils-libelf-0.125 (x86_64) passed
rac1 elfutils-libelf-0.137-3.el5 (x86_64) elfutils-libelf-0.125 (x86_64) passed
Result: Package existence check passed for "elfutils-libelf-0.125 (x86_64)"

Check: Package existence for "elfutils-libelf-devel-0.125"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 elfutils-libelf-devel-0.137-3.el5 elfutils-libelf-devel-0.125 passed
rac1 elfutils-libelf-devel-0.137-3.el5 elfutils-libelf-devel-0.125 passed
Result: Package existence check passed for "elfutils-libelf-devel-0.125"

Check: Package existence for "glibc-common-2.5"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 glibc-common-2.5-58 glibc-common-2.5 passed
rac1 glibc-common-2.5-58 glibc-common-2.5 passed
Result: Package existence check passed for "glibc-common-2.5"

Check: Package existence for "glibc-devel-2.5 (i386)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 glibc-devel-2.5-58 (i386) glibc-devel-2.5 (i386) passed
rac1 glibc-devel-2.5-58 (i386) glibc-devel-2.5 (i386) passed
Result: Package existence check passed for "glibc-devel-2.5 (i386)"

Check: Package existence for "glibc-devel-2.5 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 glibc-devel-2.5-58 (x86_64) glibc-devel-2.5 (x86_64) passed
rac1 glibc-devel-2.5-58 (x86_64) glibc-devel-2.5 (x86_64) passed
Result: Package existence check passed for "glibc-devel-2.5 (x86_64)"

Check: Package existence for "glibc-headers-2.5"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 glibc-headers-2.5-58 glibc-headers-2.5 passed
rac1 glibc-headers-2.5-58 glibc-headers-2.5 passed
Result: Package existence check passed for "glibc-headers-2.5"

Check: Package existence for "gcc-c++-4.1.2"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 gcc-c++-4.1.2-50.el5 gcc-c++-4.1.2 passed
rac1 gcc-c++-4.1.2-50.el5 gcc-c++-4.1.2 passed
Result: Package existence check passed for "gcc-c++-4.1.2"

Check: Package existence for "libaio-devel-0.3.106 (i386)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 libaio-devel-0.3.106-5 (i386) libaio-devel-0.3.106 (i386) passed
rac1 libaio-devel-0.3.106-5 (i386) libaio-devel-0.3.106 (i386) passed
Result: Package existence check passed for "libaio-devel-0.3.106 (i386)"

Check: Package existence for "libaio-devel-0.3.106 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 libaio-devel-0.3.106-5 (x86_64) libaio-devel-0.3.106 (x86_64) passed
rac1 libaio-devel-0.3.106-5 (x86_64) libaio-devel-0.3.106 (x86_64) passed
Result: Package existence check passed for "libaio-devel-0.3.106 (x86_64)"

Check: Package existence for "libgcc-4.1.2 (i386)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 libgcc-4.1.2-50.el5 (i386) libgcc-4.1.2 (i386) passed
rac1 libgcc-4.1.2-50.el5 (i386) libgcc-4.1.2 (i386) passed
Result: Package existence check passed for "libgcc-4.1.2 (i386)"

Check: Package existence for "libgcc-4.1.2 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 libgcc-4.1.2-50.el5 (x86_64) libgcc-4.1.2 (x86_64) passed
rac1 libgcc-4.1.2-50.el5 (x86_64) libgcc-4.1.2 (x86_64) passed
Result: Package existence check passed for "libgcc-4.1.2 (x86_64)"

Check: Package existence for "libstdc++-4.1.2 (i386)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 libstdc++-4.1.2-50.el5 (i386) libstdc++-4.1.2 (i386) passed
rac1 libstdc++-4.1.2-50.el5 (i386) libstdc++-4.1.2 (i386) passed
Result: Package existence check passed for "libstdc++-4.1.2 (i386)"

Check: Package existence for "libstdc++-4.1.2 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 libstdc++-4.1.2-50.el5 (x86_64) libstdc++-4.1.2 (x86_64) passed
rac1 libstdc++-4.1.2-50.el5 (x86_64) libstdc++-4.1.2 (x86_64) passed
Result: Package existence check passed for "libstdc++-4.1.2 (x86_64)"

Check: Package existence for "libstdc++-devel-4.1.2 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 libstdc++-devel-4.1.2-50.el5 (x86_64) libstdc++-devel-4.1.2 (x86_64) passed
rac1 libstdc++-devel-4.1.2-50.el5 (x86_64) libstdc++-devel-4.1.2 (x86_64) passed
Result: Package existence check passed for "libstdc++-devel-4.1.2 (x86_64)"

Check: Package existence for "sysstat-7.0.2"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 sysstat-7.0.2-3.el5_5.1 sysstat-7.0.2 passed
rac1 sysstat-7.0.2-3.el5_5.1 sysstat-7.0.2 passed
Result: Package existence check passed for "sysstat-7.0.2"

Check: Package existence for "unixODBC-2.2.11 (i386)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 unixODBC-2.2.11-7.1 (i386) unixODBC-2.2.11 (i386) passed
rac1 unixODBC-2.2.11-7.1 (i386) unixODBC-2.2.11 (i386) passed
Result: Package existence check passed for "unixODBC-2.2.11 (i386)"

Check: Package existence for "unixODBC-2.2.11 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 unixODBC-2.2.11-7.1 (x86_64) unixODBC-2.2.11 (x86_64) passed
rac1 unixODBC-2.2.11-7.1 (x86_64) unixODBC-2.2.11 (x86_64) passed
Result: Package existence check passed for "unixODBC-2.2.11 (x86_64)"

Check: Package existence for "unixODBC-devel-2.2.11 (i386)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 unixODBC-devel-2.2.11-7.1 (i386) unixODBC-devel-2.2.11 (i386) passed
rac1 unixODBC-devel-2.2.11-7.1 (i386) unixODBC-devel-2.2.11 (i386) passed
Result: Package existence check passed for "unixODBC-devel-2.2.11 (i386)"

Check: Package existence for "unixODBC-devel-2.2.11 (x86_64)"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 unixODBC-devel-2.2.11-7.1 (x86_64) unixODBC-devel-2.2.11 (x86_64) passed
rac1 unixODBC-devel-2.2.11-7.1 (x86_64) unixODBC-devel-2.2.11 (x86_64) passed
Result: Package existence check passed for "unixODBC-devel-2.2.11 (x86_64)"

Check: Package existence for "ksh-20060214"
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 ksh-20100202-1.el5_5.1 ksh-20060214 passed
rac1 ksh-20100202-1.el5_5.1 ksh-20060214 passed
Result: Package existence check passed for "ksh-20060214"

Checking for multiple users with UID value 0
Result: Check for multiple users with UID value 0 passed

Check: Current group ID
Result: Current group ID check passed
Checking Core file name pattern consistency...
Core file name pattern consistency check passed.

Checking to make sure user "oracle" is not in "root" group
Node Name Status Comment
------------ ------------------------ ------------------------
rac2 does not exist passed
rac1 does not exist passed
Result: User "oracle" is not part of "root" group. Check passed

Check default user file creation mask
Node Name Available Required Comment
------------ ------------------------ ------------------------ ----------
rac2 0022 0022 passed
rac1 0022 0022 passed
Result: Default user file creation mask check passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
The NTP configuration file "/etc/ntp.conf" is available on all nodes
NTP Configuration file check passed

Checking daemon liveness...

Check: Liveness for "ntpd"
Node Name Running?
------------------------------------ ------------------------
rac2 no
rac1 no
Result: Liveness check failed for "ntpd"
PRVF-5415 : Check to see if NTP daemon is running failed
Result: Clock synchronization check using Network Time Protocol(NTP) failed

Pre-check for cluster services setup was unsuccessful on all the nodes.

Saturday, September 27, 2014

STATSPACK REPORT - P2

Load Profile Section

This is used for comparing two reports to see if the Load characteristics from the two report periods are similar

Sample Load Profile Section Report

Definition

a) Redo Size :-- Amount of Redo generated . If increase in the value more DML happening (INSERT,UPDATE & DELETE) than before

b) Logical Reads :-- Reading from memory and diskc
c) Block changes :-- The number of blocks modified during sample interval . If it is more ,more DML statements are taking place (INSERT,UPDATE & DELETE)
d) Physical Reads :-- The number of call that caused physical I/O
e) Physical Writes :-- The number of physical writes issued
f) User calls :-- The number of queries generated
g) Parses :-- Total of both parse hard and soft
h) Hard Parse :-- This is condition when sql statements is parsed .If it is 100% .This is bad .A hard parse is usually accompained by latch contention on the shared pool and library cache latches.
I) soft parse :-- This is obtained by subtracting hard parses from parse
j) Executes :-- How many statements we are executing per second/transaction
k) Transaction :-- How many transactions per second we process

Buffer Nowait Ration :-- This is the percentage of time that the instance made a call to get a buffer . Buffer was made available immediately meaning it did not had to wait for buffer.

Buffer Hit Ratio :-- Also know as buffer-cache hit ratio . It shows % of time a particular block was found in buffer cache instead of performing a physical I/O

Library Hit Ratio :-- give the percentage of pin requests that result in pin hits.A pin hit occurs when the sql or pl/sql code to be executed is already in the library cache and is valid to execute .If “Library Hit ratio” is low . It could mean low shared pool

Execute to Parse.:-- If value is negative, it means that the number of parses is larger than the number of executions. Another cause for a negative execute to parse ratio is if the shared pool is too small and queries are aging out of the shared pool and need to be reparsed.

Parse CPU to Parse Elapsd % :-- Generally this is measure of how available CPU cycles were for SQL parsing. If this is low you may see “latch free” as one of your top wait events.

Redo Nowait Ratio :-- This indicates the amount of redo entries generated for which there was space available in the redo log.

In Memory Sort Ratio :-- This give percentage sorts that were performed in memory.

Soft Parse Ratio :-- This ratio gives the % of parses that were soft,as opposed to hard.

Latch Hit Ratio :-- This is the ratio of total number of latch misses to the number of latch gets for all latches.

Reading Statspack Reoport – P1

1) Wait Events :-- Look for excessive waits and wait times and check for specific problems

2) SQL Ordered by Buffer Gets,Physical Reads and Rows Processed :-- Figure out which SQL statements to tune

3) Instance Activity statistics :-- Compare with baseline report,compute additional statistics

4) Tablespace and File I/O :-- Investigate I/O bottlenecks,identify files and tablespaces with heavy I/O

5) Buffer Pool :-- Identify buffer pool with high I/O

6) Buffer wait statistics :-- Identify buffer type with large waits

7) Enqueue Activity :- Identify the locks which are causing most waits

8) Rollback Segment statistics and Storage :-- Investigate waits for rollback segments

9) Latch Activity,Latch Sleep Breakdown ,Latch Miss Sources :-- Identify latch bottlenacks

10) Non-default init.ora -- Look for init.ora parameter definitions

Look for statspack report in this order

1) Top 5 wait events

2) Load profile

3) Instance Efficiency Hit Ratios

4) Wait Events / Wait Event Histograms

5) Latch Waits

6) Top SQL

7) Instance Activity/Time Model Stats/ O/S Stats

8) File I/O / File Read Histogram / Undo Stats

9) Memory Allocations

10) Undo