Friday, December 21, 2012

Exadata Backup on ZFS.

Links to this post
Below is Test of Exadata x2 half rack Database backup on ZFS.


Size : 2 TB
Used Size : 1.6 TB
Time Taken: 1hr 34 Mins.
Storage: 4 share of 300TB ZFS.
Channels Allocated: 5 channels per node per share. Totally 20 channels across 4 nodes and 4 shares.


#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
export ORACLE_SID=DUMMYDB1
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=/u01/app/oracle/product/11.2.0.3/db_1/network/admin
export ALTER SESSION NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI'
export LOGFILE=/home/oracle/DUMMYDB_rman_zfs_level0_ver3.log

#setup oracle environment based on SID
ORAENV_ASK=NO
. oraenv

rman target / catalog rman/xxxx@catalogdb <> ${LOGFILE}
run {
sql 'alter system set "_backup_disk_bufcnt"=64 scope=memory';
sql 'alter system set "_backup_disk_bufsz"=1048576 scope=memory';
allocate channel c01 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest301_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c02 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest302_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c03 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest303_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c04 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest304_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c05 DEVICE TYPE DISK FORMAT '/export/share8/DUMMYDB/DUMMYDBtest305_%U' CONNECT 'sys/XXXX@DUMMYDB1.WORLD';
allocate channel c06 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest306_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c07 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest307_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c08 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest308_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c09 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest309_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c10 DEVICE TYPE DISK FORMAT '/export/share7/DUMMYDB/DUMMYDBtest310_%U' CONNECT 'sys/XXXX@DUMMYDB3.WORLD';
allocate channel c11 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest311_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c12 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest312_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c13 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest313_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c14 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest314_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c15 DEVICE TYPE DISK FORMAT '/export/share6/DUMMYDB/DUMMYDBtest315_%U' CONNECT 'sys/XXXX@DUMMYDB2.WORLD';
allocate channel c16 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest316_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c17 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest317_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c18 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest318_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c19 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest319_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
allocate channel c20 DEVICE TYPE DISK FORMAT '/export/share5/DUMMYDB/DUMMYDBtest320_%U' CONNECT 'sys/XXXX@DUMMYDB4.WORLD';
sql "alter system archive log current";
backup incremental level 0 database plus archivelog;
sql "alter database backup controlfile to trace";
release channel c01;
release channel c02;
release channel c03;
release channel c04;
release channel c05;
release channel c06;
release channel c07;
release channel c08;
release channel c09;
release channel c10;
release channel c11;
release channel c12;
release channel c13;
release channel c14;
release channel c15;
release channel c16;
release channel c17;
release channel c18;
release channel c19;
release channel c20;
}
exit
EOF

References:

http://www.oracle.com/technetwork/database/features/availability/maa-wp-dbm-zfs-backup-1593252.pdf

http://www.oracle.com/technetwork/database/features/availability/maa-tech-wp-sundbm-backup-11202-183503.pdf

Thursday, December 13, 2012

GoldenGate performance Tuning - 1

Links to this post
Problem : Replicat was doing Full table scan on Updating 804000 rows.

Source. = 11.2.0.3 /TARGG

Below is update statement which was issued to update 804000 rows.
This completed in 15 seconds on SOURCE side DB.

sql_id :ghsutzgq0m8j9 

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID ghsutzgq0m8j9
--------------------
UPDATE dummy11.OS_HISTORYSTEP_BLD SET ENTRY_ID=5555, STEP_ID=100000,
ACTION_ID=20000, OWNER='JIGNESHKANKRECHA',
START_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000
PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
FINISH_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000
PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'),
DUE_DATE=TO_TIMESTAMP('5/28/2012 9:19:11.910000 PM','fmMMfm/fmDDfm/YYYY
fmHH12fm:MI:SS.FF AM'), STATUS='QC Validation failed', CALLER='KALIYA'
where ID  BETWEEN 1220000 AND 5999999

Plan hash value: 3578452229

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                    |       |       |   376 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |    47 |  4277 |   376   (1)| 00:00:05 |
-----------------------------------------------------------------------------------------


------------------------------------------------------------------
Target. = 10.2.0.4 / SOUGG

Below statment Replicat executed ,
Because i had BATCHSQL replicat made BATCH of few rows and send for update.
that is the reason we see COUNT STOPKEY in execution plan.

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID b4t7hv6p53165
--------------------
UPDATE "dummy11"."OS_HISTORYSTEP_BLD" SET "ENTRY_ID" = :a1,"STEP_ID" =
:a2,"ACTION_ID" = :a3,"OWNER" = :a4,"START_DATE" = :a5,"FINISH_DATE" =
:a6,"DUE_DATE" = :a7,"STATUS" = :a8,"CALLER" = :a9 WHERE "ID" = :b0 AND ROWNUM = 1

Plan hash value: 185372276

------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |                    |       |       |   304 (100)|          |
|   1 |  UPDATE             | OS_HISTORYSTEP_BLD |       |       |            |          |
|   2 |   COUNT STOPKEY     |                    |       |       |            |          |
|   3 |    TABLE ACCESS FULL| OS_HISTORYSTEP_BLD |     1 |    91 |   304   (1)| 00:00:04 |
------------------------------------------------------------------------------------------

--Excerpt from Report file.

BATCHSQL BATCHESPERQUEUE 100, BATCHTRANSOPS 10000, OPSPERBATCH 10000, OPSPERQUEUE 100000

MAP resolved (entry dummy11.OS_HISTORYSTEP_BLD):
  MAP "dummy11"."OS_HISTORYSTEP_BLD", TARGET dummy11.OS_HISTORYSTEP_BLD, KEYCOLS(ID);

2012-12-12 12:00:30  WARNING OGG-00869  No unique key is defined for table 'OS_HISTORYSTEP_BLD'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
Using following columns in default map by name:
  ID, ENTRY_ID, STEP_ID, ACTION_ID, OWNER, START_DATE, FINISH_DATE,
  DUE_DATE, STATUS, CALLER
Using the following key columns for target table dummy11.OS_HISTORYSTEP_BLD: ID.

--------some statistic of replicat. 
2012-12-12 12:18:22  INFO    OGG-01408  Restoring current schema for DDL operation to [GGADMIN].
              104578 records processed as of 2012-12-12 12:21:05 (rate 84,delta 236)
              457309 records processed as of 2012-12-12 13:35:07 (rate 80,delta 79)
              913119 records processed as of 2012-12-12 13:39:47 (rate 153,delta 1628)
              943118 records processed as of 2012-12-12 13:41:25 (rate 155,delta 305)
              953118 records processed as of 2012-12-12 13:42:43 (rate 155,delta 127)
              963118 records processed as of 2012-12-12 13:44:25 (rate 154,delta 98)
              973118 records processed as of 2012-12-12 13:46:29 (rate 153,delta 80)
              983118 records processed as of 2012-12-12 13:48:57 (rate 151,delta 67)
              993118 records processed as of 2012-12-12 13:51:47 (rate 148,delta 58)
             1003118 records processed as of 2012-12-12 13:54:59 (rate 146,delta 51)
             1013118 records processed as of 2012-12-12 13:58:35 (rate 143,delta 46)


SOLUTION:

1) kill session of replicat which was running update statement ,,This will abend replicat.
2) Add Index on ID column which was in KEYCOLS Of MAP statement.
3) start replicat .

And we are on Road again....

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:40

GGSCI (catlmsxt205) 4> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:32:42

GGSCI (catlmsxt205) 6> INFO ALL
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      03:33:06

GGSCI (catlmsxt205) 7> INFO ALL

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     TAPSDR      00:00:00      00:00:03

Execution plan of Update after Index.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b4t7hv6p53165, child number 0
-------------------------------------
UPDATE "dummy11"."OS_HISTORYSTEP_BLD" SET "ENTRY_ID" = :a1,"STEP_ID" =
:a2,"ACTION_ID" = :a3,"OWNER" = :a4,"START_DATE" = :a5,"FINISH_DATE" =
:a6,"DUE_DATE" = :a7,"STATUS" = :a8,"CALLER" = :a9 WHERE "ID" = :b0 AND ROWNUM =
1

Plan hash value: 2252287618

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                    |       |       |     5 (100)|          |
|   1 |  UPDATE            | OS_HISTORYSTEP_BLD |       |       |            |          |
|*  2 |   COUNT STOPKEY    |                    |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| OS_HIST_BLDIDX     |     1 |    91 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)
   3 - access("ID"=TO_NUMBER(:B0))

Tuesday, November 6, 2012

GoldenGate High availability using Clusterware

Links to this post
This post is to setup High Availability for Goldengate.

Clusterware will manage Goldengate resources to start/stop/relocate of Goldengate Processes.

For 10g & 11gR1 Clusterware.

--Add VIP for Goldengate application

--As Oracle - Create resource profile.  Use IP address which was pre-allocated 1.2.2 section. 
/u00/app/oracle/product/10.2.0/CRS/bin/crs_profile -create ggatevip -t application -a /u00/app/oracle/product/10.2.0/CRS/bin/usrvip -p balanced -h dummyxt200,dummyxt205 -o oi=eth0,ov=10.10.10.10,on=255.255.255.0

--Next, Register the VIP as oracle:
/u00/app/oracle/product/10.2.0/CRS/bin/crs_register ggatevip

--Because the assignment of an IP address is done by the root user, you have to set the ownership of the VIP to the root user. 

--Connect as root and execute:
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm ggatevip -o root

--As root, allow oracle to run the script to start the VIP.
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm ggatevip -u user:oracle:r-x

--Then, as oracle, start the VIP:
/u00/app/oracle/product/10.2.0/CRS/bin/crs_start ggatevip

--To validate whether the VIP is running and on which node it is running, execute:
/u00/app/oracle/product/10.2.0/CRS/bin/crs_stat ggatevip -t

Try to Ping Goldengate VIP. 
ping -c4 [Goldengate VIP]

Copy & Test GoldenGate action script
Place Goldengate Action script Which is in Addendum of This Post to $CRS_HOME/crs/public on each Node.  

$CRS_HOME/crs/public/ggaction.scr
/u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr

Give full permission on it.
chmod 777 ggaction.scr

Once copied please test it using below argument. 

/u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr  [start|stop|check]
/u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr stop

Add Goldengate application resource to cluster
--As Oracle create profile for Goldengate Application 
/u00/app/oracle/product/10.2.0/CRS/bin/crs_profile -create goldengate_app -t application -r ggatevip -a /u00/app/oracle/product/10.2.0/CRS/crs/public/ggaction.scr -o ci=10

--As oracle
/u00/app/oracle/product/10.2.0/CRS/bin/crs_register goldengate_app

--As root
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm goldengate_app -o root

--As root
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_setperm goldengate_app -u user:oracle:r-x

--As oracle
sudo /u00/app/oracle/product/10.2.0/CRS/bin/crs_start goldengate_app

--As oracle.
/u00/app/oracle/product/10.2.0/CRS/bin/crs_stat goldengate_app -t

Name           Type           Target    State     Host
------------------------------------------------------------
ggatevip       application    ONLINE    ONLINE    dummyxt200
goldengate_app application    ONLINE    ONLINE    dummyxt200

Manage Application.
--To Relocate Goldengate on different Node. 
dummyxt200@:/u00/app/oracle/product/10.2.0/CRS/crs/public :CRS $crs_relocate -f goldengate_app
Attempting to stop `goldengate_app` on member `dummyxt200`
Stop of `goldengate_app` on member `dummyxt200` succeeded.
Attempting to stop `ggatevip` on member `dummyxt200`
Stop of `ggatevip` on member `dummyxt200` succeeded.
Attempting to start `ggatevip` on member `dummyxt208`
Start of `ggatevip` on member `dummyxt208` succeeded.
Attempting to start `goldengate_app` on member `dummyxt208`
Start of `goldengate_app` on member `dummyxt208` succeeded.

--Test relocation of resource on All Node in cluster. 

crs_relocate -f goldengate_app -n 

--Confirm Goldengate process has been started on relocated Node.

--Issue below command on Node where Goldengate application resource is relocated. 
> ps -ef | grep mgr
root     23942     1  0 15:41 ?        00:00:00 ./mgr PARAMFILE /ORAGG/product/11.2.1/gghome10/dirprm/mgr.prm REPORTFILE /ORAGG/product/11.2.1/gghome10/dirrpt/MGR.rpt PROCESSID MGR PORT 7809
> ps -ef | grep extract
root     23958 23942  2 15:41 ?        00:00:01 /ORAGG/product/11.2.1/gghome10/extract PARAMFILE /ORAGG/product/11.2.1/gghome10/dirprm/identde.prm REPORTFILE /ORAGG/product/11.2.1/gghome10/dirrpt/IDENTDE.rpt PROCESSID IDENTDE USESUBDIRS
root     23959 23942  0 15:41 ?        00:00:00 /ORAGG/product/11.2.1/gghome10/extract PARAMFILE /ORAGG/product/11.2.1/gghome10/dirprm/identdp.prm REPORTFILE /ORAGG/product/11.2.1/gghome10/dirrpt/IDENTDP.rpt PROCESSID IDENTDP USESUBDIRS

--To stop Goldengate using clusterware. 
> crs_stop goldengate_app
Attempting to stop `goldengate_app` on member `dummyxt208`
Stop of `goldengate_app` on member `dummyxt208` succeeded.
dummyxt208 | CRS | /ORAGG/product/11.2.1/gghome10
> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ggatevip       application    ONLINE    ONLINE    dummyxt208
goldengate_app application    OFFLINE   OFFLINE

--To start Goldengate using clusterware. 
dummyxt200@:/ORAGG/product/11.2.1/gghome10 :CRS $crs_start goldengate_app
Attempting to start `ggatevip` on member `dummyxt200`
Start of `ggatevip` on member `dummyxt200` succeeded.
Attempting to start `goldengate_app` on member `dummyxt200`
Start of `goldengate_app` on member `dummyxt200` succeeded.

For 11gR2 Clusterware

--Add VIP Goldengate Application

. oraenv
ORACLE_SID = [TARGG1] ? GRID
The Oracle base for ORACLE_HOME=/u00/app/11.2.0/GRID is /u00/app/oracle

--As root 
sudo /u00/app/11.2.0/GRID/bin/appvipcfg create -network=1 -ip=10.10.10.10 -vipname=mvggatevip -user=root

-----------------Output---------------
Production Copyright 2007, 2008, Oracle.All rights reserved
2012-11-04 10:24:30: Creating Resource Type
2012-11-04 10:24:30: Executing cmd: /u00/app/11.2.0/GRID/bin/crsctl add type app.appvip.type -basetype cluster_resource -file /u00/app/11.2.0/GRID/crs/template/appvip.type
2012-11-04 10:24:30: Create the Resource
2012-11-04 10:24:30: Executing cmd: /u00/app/11.2.0/GRID/bin/crsctl add resource mvggatevip -type app.appvip.type -attr USR_ORA_VIP=10.10.10.10,START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network),STOP_DEPENDENCIES=hard(ora.net1.network),ACL='owner:root:rwx,pgrp:root:r-x,other::r--,user:root:r-x'

dummyxt271 | GRID | /export/home/oracle
--As root,  Allow the Oracle Grid infrastructure software owner (e.g. oracle) to run the script to start the VIP.
sudo /u00/app/11.2.0/GRID/bin/crsctl setperm resource mvggatevip -u user:oracle:r-x

--As oracle, start the VIP:
/u00/app/11.2.0/GRID/bin/crsctl start resource mvggatevip
CRS-2672: Attempting to start 'mvggatevip' on 'dummyxt274'
CRS-2676: Start of 'mvggatevip' on 'dummyxt274' succeeded


--To validate whether the VIP is running and on which node it is running, execute:
/u00/app/11.2.0/GRID/bin/crsctl status resource mvggatevip
NAME=mvggatevip
TYPE=app.appvip.type
TARGET=ONLINE
STATE=ONLINE on dummyxt274

--At this point you can also connect to another server in the subnet and ping the VIP's IP address. You should get a reply from this IP address.

ping -c4 mvggatevip

Copy & Test Goldengate Action script.
--Place Goldengate Action script from Addendum of this Post to $GRID_HOME/crs/public  on each Node.  
/u00/app/11.2.0/GRID/crs/public/ggaction.scr [start|stop|check]
/u00/app/11.2.0/GRID/crs/public/ggaction.scr start

Add goldengate application resource.
--As Oracle
/u00/app/11.2.0/GRID/bin/crsctl add resource ggateapp -type cluster_resource -attr  "ACTION_SCRIPT=/u00/app/11.2.0/GRID/crs/public/ggaction.scr, CHECK_INTERVAL=30, START_DEPENDENCIES='hard(mvggatevip) pullup(mvggatevip)', STOP_DEPENDENCIES='hard(mvggatevip)'"

--As Oracle 
dummyxt271 | GRID | /u00/app/11.2.0/GRID/crs/public
crsctl start resource ggateapp
CRS-2672: Attempting to start 'ggateapp' on 'dummyxt274'
CRS-2676: Start of 'ggateapp' on 'dummyxt274' succeeded

--As Oracle 
crsctl status resource ggateapp
NAME=ggateapp
TYPE=cluster_resource
TARGET=ONLINE
STATE=ONLINE on dummyxt274

--confirm of Goldengate has started on perticular Node. 
dummyxt274 | ORA102 | /export/home/oracle
ps -ef | grep mgr
oracle   12582     1  0 10:46 ?        00:00:00 ./mgr PARAMFILE /ORAGG/product/11.2.1/gghome11/dirprm/mgr.prm REPORTFILE /ORAGG/product/11.2.1/gghome11/dirrpt/MGR.rpt PROCESSID MGR PORT 7809

--Relocate Goldengate Application. 
crsctl relocate resource ggateapp -f
CRS-2673: Attempting to stop 'ggateapp' on 'dummyxt274'
CRS-2677: Stop of 'ggateapp' on 'dummyxt274' succeeded
CRS-2673: Attempting to stop 'mvggatevip' on 'dummyxt274'
CRS-2677: Stop of 'mvggatevip' on 'dummyxt274' succeeded
CRS-2672: Attempting to start 'mvggatevip' on 'dummyxt271'
CRS-2676: Start of 'mvggatevip' on 'dummyxt271' succeeded
CRS-2672: Attempting to start 'ggateapp' on 'dummyxt271'
CRS-2676: Start of 'ggateapp' on 'dummyxt271' succeeded

--Test Relocate on each node. 
crsctl relocate resource ggateapp -n Node_name -f

Goldengate Action script.
#!/bin/sh
#############################################################################
#@(#) Clusterware script to manage Golden Gate v1.0
# Script to Manage Golden Gate from Clusterware
# change required Environment. 
#############################################################################
GGS_HOME=/ORAGG/product/11.2.1/gghome11
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${GGS_HOME}
ORACLE_HOME=/u00/app/oracle/product/11.2.0/DB
export GGS_HOME LD_LIBRARY_PATH ORACLE_HOME
# Function runCmd to run the Golden Gate Script Execution
runCmd()
{
ggsciCmd=$1
result=`${GGS_HOME}/ggsci << EOF
${ggsciCmd}
exit
EOF`
}
# Function CheckMgr to check the Golden Gate Manager process
checkMgr()
{
if ( [ -f "${GGS_HOME}/dirpcs/MGR.pcm" ] )
then
pid=`cut -f8 "${GGS_HOME}/dirpcs/MGR.pcm"`
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |cut -d " " -f2` ]
then
exit 0
else
if [ ${pid} = `ps -e |grep ${pid} |grep mgr |cut -d " " -f1` ]
then
exit 0
else
exit 1
fi
fi
else
exit 1
fi
}
# Main Code to get the input and run it
case $1 in
'start') runCmd 'start manager'
runCmd 'start er *'
sleep 5
checkMgr
;;
'stop') runCmd 'stop er *'
runCmd 'stop er *!'
runCmd 'stop manager!'
exit 0
;;
'check') checkMgr
;;
'clean') runCmd 'stop er *'
runCmd 'stop er *!'
runCmd 'kill er *'
runCmd 'stop manager!'
exit 0
;;
'abort') runCmd 'stop er *!'
runCmd 'kill er *'
runCmd 'stop manager!'
exit 0
;;
esac
# End of Script
#############################################################################

Reference: Oracle GoldenGate Best Practices: Oracle GoldenGate high availability using Oracle Clusterware [ID 1313703.1]

Monday, October 22, 2012

Exadata Upgrade / Upgrade GI to 11.2.0.3

Links to this post
Few days back we did upgrade from 11.2.0.2 to 11.2.0.3 on Exadata x2 half rack.

This was direct upgrade from 11.2.0.2 to 11.2.0.3 with Bundle Patch 7  /  patch 13992240

Some High level steps. 
  • Oracle Grid Infrastructure 11.2.0.3 Installation (Dont run rootupgrade.sh at the end)
  • Install BP7 on New Grid home.
  • Run rootupgrade.sh (Outage Time)
  • 11.2.0.3 database software Installation. 
  • Install BP7 on New Oracle home
  • Upgrade database to 11.2.0.3 (Outage Time)

Attached is Detail Upgrade Document on Google docs.



Let me know if you any difficulties viewing above Document on jonyjt@gmail.com



Wednesday, October 17, 2012

OGG-01496 Failed to open target trail file

Links to this post


My Datapump was abending after start, because It was looking for MISSING remote trailfile at perticular RBA , that trailfile was deleted.

If you want to reposition extract please visit. Re-position Extract at particular RBA

here is from source side.

GGSCI (RACD1.localdomain.com) 6> INFO RMTTRAIL *

       Extract Trail: ./dirdat/DE
             Extract: RACDE
               Seqno: 1
                 RBA: 1049
           File Size: 10M

       Extract Trail: ./dirdat/DP
             Extract: RACDP
               Seqno: 0
                 RBA: 4499
           File Size: 10M

some output from ggserr.log

2012-10-17 19:47:58  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT RACDP starting.
2012-10-17 19:47:58  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP starting.
2012-10-17 19:47:59  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP started.
2012-10-17 19:48:04  INFO    OGG-01226  Oracle GoldenGate Capture for Oracle, racdp.prm:  Socket buffer size set to 27985 (flush size 27985).
--This is the error....
2012-10-17 19:48:04  ERROR   OGG-01496  Oracle GoldenGate Capture for Oracle, racdp.prm:  Failed to open target trail file ./dirdat/DP000000, at RBA 4499.
2012-10-17 19:48:04  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, racdp.prm:  PROCESS ABENDING.

I had trailfile missing on Target side, which DATAPUMP was looking for.

TEST:/u00/app/gghome/dirdat :TEST$ll
total 12
-rw-rw-rw-  1 oracle oinstall 1167 Aug  2 09:49 XP000000
drwxr-xr-x 14 oracle oinstall 4096 Oct 17 16:10 ..
drwxrwxr-x  2 oracle oinstall 4096 Oct 17 16:52 .

So i did this below on Source side and start datapump successfully.

GGSCI (RACD1.localdomain.com) 11> ALTER EXTRACT RACDP ETROLLOVER

2012-10-17 19:54:30  INFO    OGG-01520  Rollover performed.  For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file;  it will not happen automatically.
EXTRACT altered.


GGSCI (RACD1.localdomain.com) 12> INFO RMTTRAIL *

       Extract Trail: ./dirdat/DE
             Extract: RACDE
               Seqno: 1
                 RBA: 1049
           File Size: 10M

       Extract Trail: ./dirdat/DP
             Extract: RACDP
               Seqno: 1
                 RBA: 0
           File Size: 10M



GGSCI (RACD1.localdomain.com) 13> START RACDP

Sending START request to MANAGER ...
EXTRACT RACDP starting


GGSCI (RACD1.localdomain.com) 14> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     RACDE       00:00:00      00:00:01
EXTRACT     RUNNING     RACDP       00:00:00      00:00:02


2012-10-17 19:55:20  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT RACDP starting.
2012-10-17 19:55:20  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP starting.
2012-10-17 19:55:31  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, racdp.prm:  EXTRACT RACDP started.
2012-10-17 19:55:39  INFO    OGG-01226  Oracle GoldenGate Capture for Oracle, racdp.prm:  Socket buffer size set to 27985 (flush size 27985).
2012-10-17 19:55:40  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, racdp.prm:  No recovery is required for target file ./dirdat/DP000001, at RBA 0 (file not opened).
2012-10-17 19:55:40  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, racdp.prm:  Output file ./dirdat/DP is using format RELEASE 10.4/11.1.

Monday, October 1, 2012

Change Data guard Protection Mode

Links to this post

There are 3 ways of shipping redo data to a physical standby:

LGWR SYNC = Max Availability & Max Protection
LGWR ASYNC = Max Performance
ARCH


Maximum Protection = This protection level uses a synchronous replication process to ensure that no data loss will occur if the primary database fails. It also enforces rules that prevent multiple failure events from causing data loss. This protection level will never allow a primary database to acknowledge commit success for an unprotected transaction.To provide this level of protection, the redo data that is needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before Oracle can acknowledge commit success to the application. To ensure that data loss cannot occur, the primary database will shut down if a fault prevents it from writing its redo stream to the standby redo log of at least one standby database.


Maximum Availability = This protection level uses a synchronous replication process that provides zero data loss protection without compromising the availability of the primary database. Like Maximum Protection, commit success is not acknowledged to the application until the redo that is needed to recover that transaction is written to the local online redo log and to the standby redo log of at least one standby database. Unlike Maximum Protection, however, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. The primary database will stall for a maximum of net_timout seconds (user configurable) before proceeding, in order to maintain availability of the primary database. Data Guard automatically resynchronizes primary and standby databases when the connection is restored. Data loss is possible if a second failure occurs before the resynchronization process is complete.

Maximum performance = This protection mode (the default) is an asynchronous replication process that provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by acknowledging commit success as soon as the redo data that is needed to recover that transaction is written to the local online redo log without waiting for confirmation by the standby that the data is protected. The redo data stream of the primary database is transmitted to the standby database directly from the Oracle in-memory log buffer as quickly as it is generated. .

The default is Maximum Performance.

SYNC | ASYNC = Specifies whether the redo data is to be received at the destination before the transaction is committed. ASYNC is default.

AFFIRM | NOAFFIRM = Control whether the redo destination acknowledges received redo data. NOAFFIRM is the default for ASYNC if not specified. AFFIRM is the default for SYNC for if not specified.

NET_TIMEOUT = Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN = Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 300 seconds.

Use LGWR SYNC if you use either Maximum Protection OR Maximum Availability.
so here LGWR is directly connected with RFS,henc there will be no LNS process.

On the primary database, query the RECOVERY_MODE column in the V$ARCHIVE_DEST_STATUS view, which displays the standby database's operation as MANAGED_RECOVERY for Redo Apply and MANAGED REAL TIME APPLY for real-time apply.


By default standby is running in MAXIMUM PERORMANCE Mode,

SQL> alter system set log_archive_dest_2='SERVICE=STDBY LGWR ASYNC COMPRESSION=ENABLE REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY

SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


============Change to MAXIMUM AVAILABILITY========================

STDBY-> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

System altered.

--On Primary Change Log shipping attributes. 

PROD-> alter system set log_archive_dest_2='SERVICE=STDBY SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;

System altered.

--On Primary Change Mode.
PROD-> alter database set standby database to maximize availability;

Database altered.

---Start Recovery Process on STANDBY. 
STDBY-> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

STDBY-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY


--In Primary database Alertlog.

ALTER SYSTEM SET log_archive_dest_2='SERVICE=STDBY SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH;
Wed Aug 15 06:35:30 2012
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Standby redo logfile selected to archive thread 1 sequence 19
LGWR: Standby redo logfile selected for thread 1 sequence 19 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 19 (LGWR switch)
  Current log# 1 seq# 19 mem# 0: /u01/test/redo/redo01.log
Wed Aug 15 06:35:30 2012
Archived Log entry 24 added for thread 1 sequence 18 ID 0xbacde77 dest 1:
Wed Aug 15 06:35:48 2012
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected to archive thread 1 sequence 20
LGWR: Standby redo logfile selected for thread 1 sequence 20 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 20 (LGWR switch)
  Current log# 2 seq# 20 mem# 0: /u01/test/redo/redo02.log
Wed Aug 15 06:35:49 2012
Archived Log entry 26 added for thread 1 sequence 19 ID 0xbacde77 dest 1:
Wed Aug 15 06:36:29 2012
alter database set standby database to maximize availability
Completed: alter database set standby database to maximize availability

========================Change it to MAXIMUM PROTECTION ==============================

STDBY->select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Elapsed: 00:00:00.05

PROD-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

----STOP RECOVERY PROCESS ON STANDBY. 
STDBY->ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Elapsed: 00:00:00.22

--On Primary Change Mode.
PROD-> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

Database altered.

PROD-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION   MAXIMUM PROTECTION

---Start Recovery Process on STANDBY.  
STDBY->ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Elapsed: 00:00:06.07
STDBY->select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION   MAXIMUM PROTECTION

Elapsed: 00:00:00.00

--In Standby database Alertlog.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Mon Oct 01 13:29:59 2012
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u00/app/oracle/diag/rdbms/stdby/STDBY/trace/STDBY_mrp0_3240.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1054907
Errors in file /u00/app/oracle/diag/rdbms/stdby/STDBY/trace/STDBY_mrp0_3240.trc:
ORA-16037: user requested cancel of managed recovery operation
MRP0: Background Media Recovery process shutdown (STDBY)
Waiting for MRP0 pid 3240 to terminate
Managed Standby Recovery Canceled (STDBY)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Mon Oct 01 13:34:39 2012
Changing standby controlfile to MAXIMUM PROTECTION mode
Mon Oct 01 13:34:39 2012
Archived Log entry 23 added for thread 1 sequence 30 ID 0xbacde77 dest 1:
RFS[1]: Selected log 4 for thread 1 sequence 31 dbid 195900002 branch 784012804
Mon Oct 01 13:34:59 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (STDBY)
Mon Oct 01 13:34:59 2012
MRP0 started with pid=28, OS id=3256
MRP0: Background Managed Standby Recovery process started (STDBY)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/stdby/archive/1_30_784012804.dbf
Media Recovery Waiting for thread 1 sequence 31 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 31 Reading mem 0
  Mem# 0: /u01/stdby/redoredo04.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Mon Oct 01 13:45:08 2012
Standby controlfile consistent with primary
RFS[1]: Selected log 5 for thread 1 sequence 32 dbid 195900002 branch 784012804
Mon Oct 01 13:45:10 2012
Archived Log entry 24 added for thread 1 sequence 31 ID 0xbacde77 dest 1:
Mon Oct 01 13:45:11 2012
Media Recovery Waiting for thread 1 sequence 32 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 32 Reading mem 0
  Mem# 0: /u01/stdby/redoredo05.log
  

===================================================================================================================

Saturday, September 29, 2012

Duplicate for standby from active database / Rman Duplicate for standby

Links to this post

steps to create STANDBY database.

--Put Primary db into logging mode.
--Modify standby side listener.
--Set init parameter on Primary db.
--Create DUMMY init.ora for standby db.
--Copy primary db password file on standby side.
--Create standby redo log (SRL) on primary side.
--start standby db in NOMOUNT mode, test connection from both side.
--RMAN duplicate target database for standby from active database
--start recovery process.

Also visit ...How to change Data guard Protection Mode.


*--Put Primary db into logging mode.

ALTER DATABASE ENABLE FORCE LOGGING;


*--Modify standby side listener & reload it.

Add below part into SID_LIST

(SID_DESC =
      (GLOBAL_DB_NAME=STDBY)
      (SID_NAME = STDBY)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
     )

--Standby side Listner.ora

TEST:/u00/app/oracle/product/11.2.0/db_1/network/admin :TEST$cat listener.ora

SID_LIST_LISTENER_TEST =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (GLOBAL_DB_NAME=TEST)
      (SID_NAME = TEST)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
     )
---Edit below part 
   (SID_DESC =
      (GLOBAL_DB_NAME=STDBY)
      (SID_NAME = STDBY)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
     )
  )
-----
LISTENER_TEST =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.101 )(PORT = 1524))
    )
  )


--Now reload listener.

TEST:/u00/app/oracle/product/11.2.0/db_1/dbs :STDBY$lsnrctl reload LISTENER_TEST

LSNRCTL for Linux: Version 11.2.0.1.0 - Production
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
TEST:/u00/app/oracle/product/11.2.0/db_1/dbs :STDBY$lsnrctl status LISTENER_TEST

LSNRCTL for Linux: Version 11.2.0.1.0 - Production 
Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_TEST
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u00/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u00/app/oracle/diag/tnslsnr/TEST/listener_test/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.101)(PORT=1524)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "STDBY" has 1 instance(s).
  Instance "STDBY", status UNKNOWN, has 1 handler(s) for this service...
Service "TEST" has 1 instance(s).
  Instance "TEST", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


--primary side Listener.ora

SID_LIST_LISTENER_PROD =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (GLOBAL_DB_NAME=PROD)
      (SID_NAME = PROD)
      (ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
     )
  )

LISTENER_PROD =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.102 )(PORT = 1525))
    )
  )



--tnsnames.ora on Both side. 

TEST_INST=(description=(address=(protocol=tcp)(host= 192.168.100.101 )(port=1524))(connect_data=(SERVER=DEDICATED)(SID=TEST)))
TEST_SERV=(description=(address=(protocol=tcp)(host= 192.168.100.101 )(port=1524))(connect_data=(SERVER=DEDICATED)(service_name=TEST)))
PROD_INST=(description=(address=(protocol=tcp)(host= 192.168.100.102 )(port=1525))(connect_data=(SERVER=DEDICATED)(SID=PROD)))
PROD_SERV=(description=(address=(protocol=tcp)(host= 192.168.100.102 )(port=1525))(connect_data=(SERVER=DEDICATED)(service_name=PROD)))


*--Set init parameter on Primary db.

--primary is running on SPFILE. 

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u00/app/oracle/product/11.2.0/db_1/dbs/spfilePROD.ora
                                                  

--below are parameter on primary. 
DB_NAME=PROD
DB_UNIQUE_NAME=PROD

--SET BELOW PARAMTER ON PRIMARY. 

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,STDBY)' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/test/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STDBY LGWR ASYNC COMPRESSION=ENABLE REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 SCOPE=BOTH;
ALTER SYSTEM SET FAL_SERVER=STDBY SCOPE=BOTH;
ALTER SYSTEM SET FAL_CLIENT=PROD SCOPE=BOTH;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='PROD','STDBY'
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

---ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/test/redo','/u01/stdby/redo' scope=both; --- CANT CHANGE ON FLY. 


*--Create DUMMY init.ora for standby db.

TEST:/u00/app/oracle/product/11.2.0/db_1/dbs :STDBY$cat initSTDBY.ora
DB_NAME=NOTREAL


*----Copy primary db password file on standby side.

PROD:/u00/app/oracle/product/11.2.0/db_1/dbs :PROD$scp orapwPROD oracle@192.168.100.101:/u00/app/oracle/product/11.2.0/db_1/dbs


*--Create standby redo log (SRL) on primary side.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/test/redo/redo04.log' size 10m ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/test/redo/redo05.log' size 10m ;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/test/redo/redo06.log' size 10m ;


*--start standby db in NOMOUNT mode, test connection from both side.

TEST:/u00/app/oracle/product/11.2.0/db_1/dbs :STDBY$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 29 16:07:01 2012

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

Connected to an idle instance.

@STDBY->startup nomount;
ORACLE instance started.

Total System Global Area            367439872 bytes
Fixed Size                            2213456 bytes
Variable Size                       318769584 bytes
Database Buffers                     41943040 bytes
Redo Buffers                          4513792 bytes


--Test connections. You should be able to start/stop standby db from Primary side.

PROD:/u00/app/oracle/product/11.2.0/db_1/network/admin :PROD$tnsping STDBY
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production 
Copyright (c) 1997, 2009, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (description=(address=(protocol=tcp)(host= 192.168.100.101)(port=1524))(connect_data=(SERVER=DEDICATED)(service_name=STDBY)))
OK (0 msec)

--Lets startup and stop standby from Primary side. 
 
PROD:/u00/app/oracle/product/11.2.0/db_1/network/admin :PROD$sqlplus sys/system@STDBY as sysdba

SQL*Plus: Release 11.2.0.1.0 Production 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

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

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area  367439872 bytes
Fixed Size                  2213456 bytes
Variable Size             318769584 bytes
Database Buffers           41943040 bytes
Redo Buffers                4513792 bytes
SQL>


*--RMAN duplicate target database for standby from active database (full output is at end of the post)

RMAN will be invoked from Primary as below.

PROD:/u00/app/oracle/product/11.2.0/db_1/dbs :PROD$rman target  sys/system@PROD_SERV AUXILIARY sys/system@STDBY

Recovery Manager: Release 11.2.0.1.0 - Production on 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=195900002)
connected to auxiliary database: NOTREAL (not mounted)

RMAN> run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert='PROD','STDBY','/u01/test/','/u01/stdby/'
set db_file_name_convert='/u01/test/','/u01/stdby/'
set log_file_name_convert='/u01/test/redo/','/u01/stdby/redo'
set 'db_unique_name'='STDBY'
nofilenamecheck;
}


--start recovery process on standby side by applying Real-time redo.

@STDBY->ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

--you can also check output from alert log on standby side. 

STDBY:/u00/app/oracle/diag/rdbms/stdby/STDBY/trace :STDBY$tail -f alert_STDBY.log
--------
Primary database is in MAXIMUM PERFORMANCE mode
Tue Aug 14 14:44:27 2012
RFS[3]: Assigned to RFS process 6688
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 3141
RFS[2]: Selected log 4 for thread 1 sequence 9 dbid 195900002 branch 784012804
RFS[3]: Opened log for thread 1 sequence 8 dbid 195900002 branch 784012804
Archived Log entry 1 added for thread 1 sequence 8 rlc 784012804 ID 0xbacde77 dest 2:
RFS[2]: Selected log 5 for thread 1 sequence 10 dbid 195900002 branch 784012804
Tue Aug 14 14:44:28 2012
Archived Log entry 2 added for thread 1 sequence 9 ID 0xbacde77 dest 1:
TEST:/u00/app/oracle/diag/rdbms/stdby/STDBY/trace :TEST$
TEST:/u00/app/oracle/diag/rdbms/stdby/STDBY/trace :TEST$tail -f alert_STDBY.log
Primary database is in MAXIMUM PERFORMANCE mode
Tue Aug 14 14:44:27 2012
RFS[3]: Assigned to RFS process 6688
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 3141
RFS[2]: Selected log 4 for thread 1 sequence 9 dbid 195900002 branch 784012804
RFS[3]: Opened log for thread 1 sequence 8 dbid 195900002 branch 784012804
Archived Log entry 1 added for thread 1 sequence 8 rlc 784012804 ID 0xbacde77 dest 2:
RFS[2]: Selected log 5 for thread 1 sequence 10 dbid 195900002 branch 784012804
Tue Aug 14 14:44:28 2012
Archived Log entry 2 added for thread 1 sequence 9 ID 0xbacde77 dest 1:
Tue Aug 14 14:46:09 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (STDBY)
Tue Aug 14 14:46:09 2012
MRP0 started with pid=30, OS id=6734
MRP0: Background Managed Standby Recovery process started (STDBY)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/stdby/archive/1_8_784012804.dbf
Media Recovery Log /u01/stdby/archive/1_9_784012804.dbf
Media Recovery Waiting for thread 1 sequence 10 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 10 Reading mem 0
  Mem# 0: /u01/stdby/redoredo05.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION 




=====================full output of duplicate database==================================


PROD:/u00/app/oracle/product/11.2.0/db_1/dbs :PROD$rman target  sys/system@PROD_SERV AUXILIARY sys/system@STDBY

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Aug 14 14:45:13 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=195900002)
connected to auxiliary database: NOTREAL (not mounted)

RMAN> run{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert='PROD','STDBY','/u01/test/','/u01/stdby/'
set db_file_name_convert='/u01/test/','/u01/stdby/'
set log_file_name_convert='/u01/test/redo/','/u01/stdby/redo'
set 'db_unique_name'='STDBY'
nofilenamecheck;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=51 device type=DISK

allocated channel: prmy2
channel prmy2: SID=31 device type=DISK

allocated channel: prmy3
channel prmy3: SID=42 device type=DISK

allocated channel: stby1
channel stby1: SID=20 device type=DISK

Starting Duplicate Db at 14-AUG-12

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u00/app/oracle/product/11.2.0/db_1/dbs/orapwPROD' auxiliary format
 '/u00/app/oracle/product/11.2.0/db_1/dbs/orapwSTDBY'   targetfile
 '/u00/app/oracle/product/11.2.0/db_1/dbs/spfilePROD.ora' auxiliary format
 '/u00/app/oracle/product/11.2.0/db_1/dbs/spfileSTDBY.ora'   ;
   sql clone "alter system set spfile= ''/u00/app/oracle/product/11.2.0/db_1/dbs/spfileSTDBY.ora''";
}
executing Memory Script

Starting backup at 14-AUG-12
Finished backup at 14-AUG-12

sql statement: alter system set spfile= ''/u00/app/oracle/product/11.2.0/db_1/dbs/spfileSTDBY.ora''

contents of Memory Script:
{
   sql clone "alter system set  control_files =
 ''/u01/stdby/data/control01.ctl'', ''/u01/stdby/data/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''LOCATION=/u01/stdby/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDBY'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u01/test/'', ''/u01/stdby/'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/u01/test/redo/'', ''/u01/stdby/redo'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''STDBY'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =  ''/u01/stdby/data/control01.ctl'', ''/u01/stdby/data/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=/u01/stdby/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDBY'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u01/test/'', ''/u01/stdby/'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/u01/test/redo/'', ''/u01/stdby/redo'' comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''STDBY'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     367439872 bytes

Fixed Size                     2213456 bytes
Variable Size                247466416 bytes
Database Buffers             113246208 bytes
Redo Buffers                   4513792 bytes
allocated channel: stby1
channel stby1: SID=18 device type=DISK

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/stdby/data/control01.ctl';
   restore clone controlfile to  '/u01/stdby/data/control02.ctl' from
 '/u01/stdby/data/control01.ctl';
}
executing Memory Script

Starting backup at 14-AUG-12
channel prmy1: starting datafile copy
copying standby control file
output file name=/u00/app/oracle/product/11.2.0/db_1/dbs/snapcf_PROD.f tag=TAG20120814T144542 RECID=1 STAMP=791304343
channel prmy1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-AUG-12

Starting restore at 14-AUG-12

channel stby1: copied control file copy
Finished restore at 14-AUG-12

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/stdby/data/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/stdby/data/system01.dbf";
   set newname for datafile  2 to
 "/u01/stdby/data/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/stdby/data/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/stdby/data/users01.dbf";
   set newname for datafile  5 to
 "/u01/stdby/data/test.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/stdby/data/system01.dbf"   datafile
 2 auxiliary format
 "/u01/stdby/data/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/stdby/data/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/stdby/data/users01.dbf"   datafile
 5 auxiliary format
 "/u01/stdby/data/test.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/stdby/data/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 14-AUG-12
channel prmy1: starting datafile copy
input datafile file number=00001 name=/u01/test/data/system01.dbf
channel prmy2: starting datafile copy
input datafile file number=00002 name=/u01/test/data/sysaux01.dbf
channel prmy3: starting datafile copy
input datafile file number=00005 name=/u01/test/data/test.dbf
output file name=/u01/stdby/data/test.dbf tag=TAG20120814T144553
channel prmy3: datafile copy complete, elapsed time: 00:00:46
channel prmy3: starting datafile copy
input datafile file number=00003 name=/u01/test/data/undotbs01.dbf
output file name=/u01/stdby/data/sysaux01.dbf tag=TAG20120814T144553
channel prmy2: datafile copy complete, elapsed time: 00:01:13
channel prmy2: starting datafile copy
input datafile file number=00004 name=/u01/test/data/users01.dbf
output file name=/u01/stdby/data/system01.dbf tag=TAG20120814T144553
channel prmy1: datafile copy complete, elapsed time: 00:01:28
output file name=/u01/stdby/data/users01.dbf tag=TAG20120814T144553
channel prmy2: datafile copy complete, elapsed time: 00:00:15
output file name=/u01/stdby/data/undotbs01.dbf tag=TAG20120814T144553
channel prmy3: datafile copy complete, elapsed time: 00:00:42
Finished backup at 14-AUG-12

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=791304219 file name=/u01/stdby/data/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=791304219 file name=/u01/stdby/data/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=791304219 file name=/u01/stdby/data/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=791304219 file name=/u01/stdby/data/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=791304219 file name=/u01/stdby/data/test.dbf
Finished Duplicate Db at 14-AUG-12
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: stby1

Tuesday, September 11, 2012

CTAS for partition tables / create table as select * from partition table

Links to this post
create table as select * from , for partition tables as below.

Here is Table DDL

CREATE TABLE CNS.COMM_CNS_dummy_data
(
  FILE_DT                         NUMBER(6),
  PARENT_HQ_NAME                  VARCHAR2(90 BYTE),
  PARENT_HQ_FIPS_COUNTRY_CODE     VARCHAR2(2 BYTE),
  PARENT_HQ_COUNTRY_CODE          VARCHAR2(3 BYTE),
  PARENT_HQ_STATE                 VARCHAR2(10 BYTE),
  HIERARCHY_CODE                  VARCHAR2(2 BYTE),
  DIAS_CODE                       VARCHAR2(9 BYTE),
  NUMBER_OF_FAMILY_MEMBERS        VARCHAR2(5 BYTE),
  FAMILY_UPDATE_DATE              VARCHAR2(8 BYTE),
  SIC1                            VARCHAR2(32 BYTE),
  SIC2                            VARCHAR2(32 BYTE),
  SIC3                            VARCHAR2(32 BYTE),
  SIC4                            VARCHAR2(32 BYTE),
  SIC5                            VARCHAR2(32 BYTE),
  SIC6                            VARCHAR2(32 BYTE),
  NATIONAL_IDENTIFICATION_NUMBER  VARCHAR2(20 BYTE),
  FILLER10                        VARCHAR2(6 BYTE),
  FILLER11                        VARCHAR2(6 BYTE),
  FIRST_EXECUTIVE_FIRST_NAME      VARCHAR2(13 BYTE),
  CENSUS_GEOCODE                  VARCHAR2(15 BYTE),
  CENSUS_GEOCODE_MATCH_FLAG       VARCHAR2(1 BYTE),
  CENSUS_2003_NECTA_DIV_FIPS      VARCHAR2(5 BYTE),
)
TABLESPACE CNS_DATA
PARTITION BY RANGE (FILE_DT)
(  
  PARTITION CNS_dummy_data_201105 VALUES LESS THAN (201106) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201106 VALUES LESS THAN (201107) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201107 VALUES LESS THAN (201108) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201108 VALUES LESS THAN (201109) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201109 VALUES LESS THAN (201110) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201110 VALUES LESS THAN (201111) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201111 VALUES LESS THAN (201112) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201112 VALUES LESS THAN (201201) TABLESPACE CNS_DATA,  
)
;


CTAS for above table will be

CREATE TABLE cns.COMM_cns_dummy_data_BK PARTITION BY RANGE (FILE_DT)
(
  PARTITION cns_dummy_data_201105 VALUES LESS THAN (201106),
  PARTITION cns_dummy_data_201106 VALUES LESS THAN (201107),
  PARTITION cns_dummy_data_201107 VALUES LESS THAN (201108),
  PARTITION cns_dummy_data_201108 VALUES LESS THAN (201109),
  PARTITION cns_dummy_data_201109 VALUES LESS THAN (201110),
  PARTITION cns_dummy_data_201110 VALUES LESS THAN (201111),
  PARTITION cns_dummy_data_201111 VALUES LESS THAN (201112),
  PARTITION cns_dummy_data_201112 VALUES LESS THAN (201201)
  )
  AS SELECT * FROM 
  cns.COMM_cns_dummy_data PARALLEL;

Thursday, September 6, 2012

Active Dataguard vs Goldengate.

Links to this post

Few days back i have provided Recommendation on what should use for DR. 

Active Data Guard or Oracle Goldengate. 

Primary Functional Comparison .

Oprations
Active Data Guard 
  GoldenGate V11.2
Suitable
Disaster recovery /  Read-only Reporting.
Fine-grain Replication / Object Transformation
Security
User  grant/privileges are same on Target & Source
User access can restricted as per requirement.
Data type support
All data-type supported by Primary
*  (Attached separately)
Replication
·      Physical block-to-block copy of whole DB on Target.  (Carried out by Redo logs). 
·      Target will be read only  (Can be Open temporary for read/write)
·      Support Parallel DML on RAC span on Multiple instances.

·      No object-Transformation allowed
·      Support compression.
·      Only Required Table are subject for Replication  (More controlled). (carried out by Trailfiles) 
·      Target will be in read/write all Time.
·      Does Not support Parallel DML on RAC  spawn from Multiple instances
·      Object-Transformation Allowed.
·      Not-support Compression. 
Maintenance.
Automatic Outage Gap resolution.
Low-Maintenance
Manual Invention needed for Troubleshoot.
Moderate - Maintenance.
Clustered DB
Only One instance will Perform recovery  on Target.
All instance will perform recovery on Target side.  (Load balance)


Other things to look for . 

·         Do you need Copy of source database or  Set of tables required to be Replicate
·         SLA for lag/latency.
·         Amount of Archive log Generated / day by database  ( Per Instance for RAC )
·         Is source & target are in different Time-zone.
·         No of tables with LOB/CLOB data to be replicate.
·         Network bandwidth.


Tuesday, August 14, 2012

resmgr:pq queued | enq: JX - SQL statement queue | PX Queuing: statement queue

Links to this post
We have 4 Node RAC on 11.2.0.2 on exadata machine.

Few days back we had problem of Trucate statment was running slow. it took around 1 hour.
when truncate is running slow.
either object is locked or its bug. But here it was something else.

TRUNCATE TABLE DUMMY_USAGE.DUMMY_CUST_BILL_CYC_USG_F_BLD1 DROP STORAGE;

After pulling AWR , we found that "resmgr:pq queued" is in Top 5 wait events.
It looks that database has been suffered from "lack of enough parallel servers".
even TRUNCATE statement also tried to ran into parallel.


in 11.2.0.2

resmgr:pq queued is The time the session waited for sufficient parallel query processes to become available to run this session with the requested degree of parallelism

in 11.2.0.1

Wait event "PX Queuing: statement queue" is the event When statement waits on about to run.

Wait event "enq: JX - SQL statement queue" is event when statement have few more statments lined up ahead of it.


Wait event on DB. So database has heavily waited on "resmgr:pq queued"

MIN(SAMPLE_TIME) MAX(SAMPLE_TIME)                 COUNT EVENT
8/10/2012 3:39:15.324 AM 8/10/2012 7:00:26.064 AM 11812 resmgr:pq queued
8/10/2012 2:13:36.850 AM 8/10/2012 7:00:16.054 AM 3045 CPU
8/10/2012 3:05:19.667 AM 8/10/2012 6:59:03.635 AM 2638 direct path read temp
8/10/2012 2:00:45.579 AM 8/10/2012 6:59:15.940 AM 1987 cell smart table scan

Which SQL has waited Most from "lack of Enogh parallel servers"

SELECT MIN(SAMPLE_TIME),MAX(SAMPLE_TIME),COUNT(*) AS COUNT , EVENT,SQL_ID FROM DBA_HIST_ACTIVE_sESS_HISTORY WHERE SNAP_ID BETWEEN 1594 AND 1598 AND EVENT='resmgr:pq queued' group by EVENT,SQL_ID ORDER BY COUNT DESC;

MIN(SAMPLE_TIME)         MAX(SAMPLE_TIME)        COUNT    EVENT           SQL_ID
---------------------------------------------------------------------------------------------
8/10/2012 4:24:47.633 AM 8/10/2012 6:25:40.210 AM 723 resmgr:pq queued brjbmruuj4qca

8/10/2012 4:06:58.060 AM 8/10/2012 5:49:08.703 AM 540 resmgr:pq queued 1ztm7k9fbawks
8/10/2012 4:07:05.815 AM 8/10/2012 5:38:45.312 AM 477 resmgr:pq queued 8fdq282ydmj9y
8/10/2012 4:06:58.060 AM 8/10/2012 5:37:57.564 AM 473 resmgr:pq queued 5pgx9fqn6b18k
8/10/2012 4:06:58.060 AM 8/10/2012 5:37:07.457 AM 470 resmgr:pq queued bfyb7a00r9m97
8/10/2012 4:06:58.060 AM 8/10/2012 5:34:07.155 AM 452 resmgr:pq queued dck4f08ywn2gk
8/10/2012 4:07:05.815 AM 8/10/2012 5:33:34.783 AM 448 resmgr:pq queued 2tjdy0su2yyrg
8/10/2012 4:06:58.060 AM 8/10/2012 5:32:26.963 AM 442 resmgr:pq queued a770bk5gnyp49
8/10/2012 4:06:58.060 AM 8/10/2012 5:31:56.913 AM 439 resmgr:pq queued 8qgm5az2pstpw
8/10/2012 4:07:05.815 AM 8/10/2012 5:31:24.561 AM 435 resmgr:pq queued 3gytqk6h6hy46
8/10/2012 4:07:05.815 AM 8/10/2012 5:31:14.513 AM 434 resmgr:pq queued 73bxznwgh04yf

8/10/2012 4:49:52.568 AM 8/10/2012 5:49:08.703 AM 356 resmgr:pq queued 3qkqq4w2z03jh
8/10/2012 4:06:58.060 AM 8/10/2012 5:03:13.976 AM 338 resmgr:pq queued g0b4p5zsn5xwp
8/10/2012 4:06:58.060 AM 8/10/2012 5:02:23.885 AM 333 resmgr:pq queued 0k5796sr9sgrq
8/10/2012 4:07:05.815 AM 8/10/2012 5:01:01.402 AM 324 resmgr:pq queued a8m336ddng5z7
8/10/2012 4:07:05.815 AM 8/10/2012 4:59:41.282 AM 316 resmgr:pq queued 8wz2qpu6t3b4r
8/10/2012 4:06:58.060 AM 8/10/2012 4:58:53.520 AM 312 resmgr:pq queued c1a03akcb57nm
8/10/2012 4:39:51.493 AM 8/10/2012 5:31:06.810 AM 308 resmgr:pq queued bsuwb8ksjgg7f
8/10/2012 5:38:15.272 AM 8/10/2012 6:32:10.874 AM 287 resmgr:pq queued 8tkfm8yg6fy3z

SQL> SELECT * FROM DBA_HIST_SQLTEXT WHERE sql_id in ('brjbmruuj4qca','1ztm7k9fbawks');

      DBID SQL_ID        SQL_TEXT                                                                         COMMAND_TYPE
---------- ------------- -------------------------------------------------------------------------------- ------------
1664458898 brjbmruuj4qca TRUNCATE TABLE DUMMY_USAGE.DUMMY_TOP_TALKERS_DLY_SUM_SWP DROP STORAGE                        85
2309640764 8fdq282ydmj9y SELECT Last_day(cduf.time_key)         AS time_key                                          3


when Oracle put statments to Queue for parallel servers.

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database queues SQL statements that require parallel execution if the necessary parallel server processes are not available. After the necessary resources become available, the SQL statement is dequeued and allowed to execute. The default dequeue order is a simple first in, first out queue based on the time a statement was issued.

The following is a summary of parallel statement processing.

1) A SQL statements is issued.

2) The statement is parsed and the DOP is automatically determined.

3) Available parallel resources are checked.

A) If there are enough parallel resources and there are no statements ahead in the queue waiting for the resources, the SQL statement is executed.

B) If there are not enough parallel servers, the SQL statement is queued based on specified conditions and dequeued from the front of the queue when specified conditions are met.

Parallel statements are queued if running the statements would increase the number of active parallel servers above the value of the PARALLEL_SERVERS_TARGET initialization parameter. For example, if PARALLEL_SERVERS_TARGET is set to 64, the number of current active servers is 60, and a new parallel statement needs 16 parallel servers, it would be queued because 16 added to 60 is greater than 64, the value of PARALLEL_SERVERS_TARGET.

The default value is described in "PARALLEL_SERVERS_TARGET". This value is not the maximum number of parallel server processes allowed on the system, but the number available to run parallel statements before parallel statement queuing is used. It is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement gets all of the parallel server resources required and to prevent overloading the system with parallel server processes. Note all serial (nonparallel) statements execute immediately even if parallel statement queuing has been activated.

If a statement has been queued, it is identified by the resmgr:pq queued wait event.

Parameter to define parallelism were defined something like below,
SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     FALSE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      16
parallel_degree_policy               string      AUTO   --AUTO DOP is used. 
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     32
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     TRUE
parallel_server_instances            integer     4   
parallel_servers_target              integer     16
parallel_threads_per_cpu             integer     1

SQL> show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     24  
parallel_threads_per_cpu             integer     1


It have only 32 Parallel servers and queuing will start even if 16 servers are Used.

so Both parallel_max_servers & parallel_servers_target have set incorrect.

Auto DOP is calculated as (parallel_threads_per_cpu × parallel_server_instances × cpu_count )= 96

The default value for parallel_server_target is set to 4 times the default DOP.

((4 × CPU_count) × parallel_threads_per_cpu) × active_instances = PARALLEL_SERVER_TARGET

((4 × 24) × 1) × 4) = 384 should be PARALLEL_SERVER_TARGET


Managing Parallel Statement Queuing with Hints

NO_STATEMENT_QUEUING

When PARALLEL_DEGREE_POLICY is set to AUTO, this hint enables a statement to bypass the parallel statement queue. For example:

SELECT /*+ NO_STATEMENT_QUEUING */ emp.last_name, dpt.department_name
FROM employees emp, departments dpt
WHERE emp.department_id = dpt.department_id;

STATEMENT_QUEUING

When PARALLEL_DEGREE_POLICY is not set to AUTO, this hint enables a statement to be delayed and to only run when parallel processes are available to run at the requested DOP. For example:

SELECT /*+ STATEMENT_QUEUING */ emp.last_name, dpt.department_name
FROM employees emp, departments dpt
WHERE emp.department_id = dpt.department_id;

There is also a hidden parameter which control parallel queuing.
_parallel_statement_queuing=TRUE





Tuesday, August 7, 2012

Install 11.2.0.3 Grid Infrastructure and Database.

Links to this post


This is Demo of Installation of 11.2.0.3 Grid Infrastructure and Database on Linux x86_64 bit OS.

It install as Job Role separation.

I have skipped few Pre-requisite such as security, kernal parameter modification

Steps.
--Create user/group
--create GI/OH and set ownership/permission.
--check umask
--Set permission on ASM disks
--Configure ASM driver and Mark ASM Disks.
--Configure DNS server for SCAN IP.
--Configure zone files.
--configure ssh for GRID/ORACLE user
--runcluvfy.sh
--Installation of Grid Infrastrcuture.
--Execute root scripts.
--Create Diskgroup for Database.
--Install Database software and create DB.
--Root.sh after DB installation.


--Create user/group

/usr/sbin/groupadd -g 1020 asmadmin
/usr/sbin/groupadd -g 1021 asmdba
/usr/sbin/groupadd -g 1030 asmoper
/usr/sbin/groupadd -g 1031 dba
/usr/sbin/groupadd -g 1032 oper
useradd -u 1101 -g oinstall -G dba,asmdba oracle
useradd -u 1100 -g oinstall -G asmadmin,asmdba,asmoper grid

uid=1100(grid)   gid=1000(oinstall) groups=1000(oinstall),1020(asmadmin),1021(asmdba),1030(asmoper)
uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1021(asmdba),1031(dba)


---create GI/OH and set ownership/permission.

--GI home
mkdir -p  /u00/app/11.2.3/grid
chmod 775 /u00/app/11.2.3/grid
chown -R grid:oinstall /u00
chmod -R 775 /u00

--OH home
mkdir -p  /u01/app/oracle/11.2.3/db
chmod 775 /u01/app/oracle/11.2.3/db
chown -R  oracle:oinstall /u01
chmod -R 775 /u01


--check umask

TESTP2@:/home/grid : $umask
0022


--Set permission on ASM disks

[root@TESTP1 ~]# cat /etc/rc.d/rc.local
#!/bin/sh
#
# This script will be executed *after* all the other init scripts.
# You can put your own initialization stuff in here if you don't
# want to do the full Sys V style init stuff.

touch /var/lock/subsys/local
chown grid:asmadmin /dev/sdb1
chown grid:asmadmin /dev/sdc1
chown grid:asmadmin /dev/sdd1
chown grid:asmadmin /dev/sde1
chown grid:asmadmin /dev/sdf1
chown grid:asmadmin /dev/sdg1
chown grid:asmadmin /dev/sdh1
chown grid:asmadmin /dev/sdi1
chown oracle:oinstall /dev/sdj1
chmod 660 /dev/sdb1
chmod 660 /dev/sdc1
chmod 660 /dev/sdd1
chmod 660 /dev/sde1
chmod 660 /dev/sdf1
chmod 660 /dev/sdg1
chmod 660 /dev/sdh1
chmod 660 /dev/sdi1
chmod 777 /dev/sdj1


--Configure ASM driver and Mark ASM Disks.

[root@TESTP1 ~]# /etc/init.d/oracleasm configure -i
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting  without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface [oracle]: grid
Default group to own the driver interface [oinstall]: asmadmin
Start Oracle ASM library driver on boot (y/n) [y]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

[root@PNETN1 ~]# /etc/init.d/oracleasm createdisk DISK1 /dev/sdb1
Marking disk "DISK1" as an ASM disk:                       [  OK  ]
[root@PNETN1 ~]# /etc/init.d/oracleasm createdisk DISK2 /dev/sdc1
Marking disk "DISK2" as an ASM disk:                       [  OK  ]
[root@PNETN1 ~]# /etc/init.d/oracleasm createdisk DISK3 /dev/sdd1
Marking disk "DISK3" as an ASM disk:                       [  OK  ]
[root@PNETN1 ~]# /etc/init.d/oracleasm createdisk DISK4 /dev/sde1
Marking disk "DISK4" as an ASM disk:                       [  OK  ]
[root@PNETN1 ~]# /etc/init.d/oracleasm createdisk DISK5 /dev/sdf1
Marking disk "DISK5" as an ASM disk:                       [  OK  ]
[root@PNETN1 ~]# /etc/init.d/oracleasm createdisk DISK6 /dev/sdg1
Marking disk "DISK6" as an ASM disk:                       [  OK  ]
[root@TESTP1 ~]# /etc/init.d/oracleasm createdisk DISK7 /dev/sdh1
Marking disk "DISK7" as an ASM disk:                       [  OK  ]
[root@TESTP1 ~]# /etc/init.d/oracleasm createdisk DISK8 /dev/sdi1
Marking disk "DISK8" as an ASM disk:                       [  OK  ]

[root@TESTP1 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@TESTP1 ~]# /etc/init.d/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
DISK6
DISK7
DISK8

--Do it on rest of the Nodes. 

[root@TESTP2 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@TESTP2 ~]# /etc/init.d/oracleasm listdisks
DISK1
DISK2
DISK3
DISK4
DISK5
DISK6
DISK7
DISK8


--Configure DNS server for SCAN IP.

rpm required for DNS 
[root@DNS ~]# rpm -qa| grep bind
ypbind-1.19-12.el5
bind-9.3.6-4.P1.el5
system-config-bind-4.0.3-4.0.1.el5
bind-utils-9.3.6-4.P1.el5
bind-libs-9.3.6-4.P1.el5
bind-chroot-9.3.6-4.P1.el5
kdebindings-3.5.4-6.el5


--Configure zone files.

--Named files for Localdomain.com 

[root@DNS ~]# cat /var/named/chroot/etc/named.conf
// Enterprise Linux BIND Configuration Tool
//
// Default initial "Caching Only" name server configuration
//

options {
       directory "/var/named";
       dump-file "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
};


// Zone for this RAC configuration is hingu.net

zone "localdomain.com" in {
  type master;
  file "localdomain.com.zone";
  allow-update { none; };
};

// For reverse lookups

zone "100.168.192.in-addr.arpa" in {
  type master;
  file "100.168.192.in-addr.arpa.zone";
  allow-update { none; };
};


include "/etc/rndc.key";

-- localdomain.zone file. 

[root@DNS named]# cat /var/named/chroot/var/named/localdomain.com.zone
$TTL    1d
localdomain.com.  IN    SOA   dns.localdomain.com. root.localdomain.com. (
    100        ; se = serial number
    8h         ; ref = refresh
    5m         ; ret = update retry
    3w         ; ex = expiry
    3h         ; min = minimum
    )

    IN    NS    dns.localdomain.com.

; DNS server

dns    IN    A    192.168.100.150

; RAC Nodes Public name

RACG1          IN    A    192.168.100.121
RACG2          IN    A    192.168.100.122
RACG3          IN    A    192.168.100.123

RACD1          IN    A    192.168.100.124
RACD2          IN    A    192.168.100.125

PNETN1         IN    A    192.168.100.126
PNETN2         IN    A    192.168.100.127
PNETN3         IN    A    192.168.100.128

TESTP1         IN    A    192.168.100.181
TESTP2         IN    A    192.168.100.182

; RAC Nodes Public VIPs

RACG1-VIP      IN    A    192.168.100.131
RACG2-VIP      IN    A    192.168.100.132
RACG3-VIP      IN    A    192.168.100.133

RACD1-VIP      IN    A    192.168.100.134
RACD2-VIP      IN    A    192.168.100.135

PNETN1-VIP     IN    A    192.168.100.136
PNETN2-VIP     IN    A    192.168.100.137
PNETN3-VIP     IN    A    192.168.100.138


TESTP1-VIP     IN    A  192.168.100.183
TESTP2-VIP     IN    A  192.168.100.184

; 3 SCAN VIPs

RACG-scan       IN    A    192.168.100.151
RACG-scan       IN    A    192.168.100.152
RACG-scan       IN    A    192.168.100.153

RACD-scan       IN    A    192.168.100.154
RACD-scan       IN    A    192.168.100.155
RACD-scan       IN    A    192.168.100.156

PNETN-SCAN      IN    A    192.168.100.157
PNETN-SCAN      IN    A    192.168.100.158
PNETN-SCAN      IN    A    192.168.100.159

TESTP-SCAN      IN    A   192.168.100.185
TESTP-SCAN      IN    A   192.168.100.186
TESTP-SCAN      IN    A   192.168.100.187

---reverse lookup zone file. 

[root@DNS named]# cat /var/named/chroot/var/named/100.168.192.in-addr.arpa.zone
$TTL    1d
@   IN    SOA   dns.localdomain.com. root.localdomain.com. (
    100        ; se = serial number
    8h         ; ref = refresh
    5m         ; ret = update retry
    3w         ; ex = expiry
    3h         ; min = minimum
    )

    IN    NS    dns.localdomain.com.

; DNS machine name in reverse
150        IN    PTR dns.localdomain.com.

; RAC Nodes Public Name in Reverse

121          IN    PTR RACG1.localdomain.com.
122          IN    PTR RACG2.localdomain.com.
123          IN    PTR RACG3.localdomain.com.

124          IN    PTR RACD1.localdomain.com.
125          IN    PTR RACD2.localdomain.com.

126          IN    PTR PNETN1.localdomain.com.
127          IN    PTR PNETN2.localdomain.com.
128          IN    PTR PNETN3.localdomain.com.

181          IN    PTR TESTP1.localdomain.com.
182          IN    PTR TESTP2.localdomain.com.

; RAC Nodes Public VIPs in Reverse

131          IN    PTR      RACG1-VIP.localdomain.com.
132          IN    PTR      RACG2-VIP.localdomain.com.
133          IN    PTR      RACG3-VIP.localdomain.com.

134          IN    PTR      RACD1-VIP.localdomain.com.
135          IN    PTR      RACD2-VIP.localdomain.com.

136          IN    PTR      PNETN1-VIP.localdomain.com.
137          IN    PTR      PNETN2-VIP.localdomain.com.
138          IN    PTR      PNETN3-VIP.localdomain.com.

183          IN    PTR      TESTP1-VIP.localdomain.com.
184          IN    PTR      TESTP2-VIP.localdomain.com.


; RAC Nodes SCAN VIPs in Reverse

151         IN    PTR      RACG-scan.localdomain.com.
152         IN    PTR      RACG-scan.localdomain.com.
153         IN    PTR      RACG-scan.localdomain.com.

154         IN    PTR      RACD-scan.localdomain.com.
155         IN    PTR      RACD-scan.localdomain.com.
156         IN    PTR      RACD-scan.localdomain.com.

157         IN    PTR      PNETN-SCAN.localdomain.com.
158         IN    PTR      PNETN-SCAN.localdomain.com.
159         IN    PTR      PNETN-SCAN.localdomain.com.

185         IN    PTR      TESTP-SCAN.localdomain.com
186         IN    PTR      TESTP-SCAN.localdomain.com.
187         IN    PTR      TESTP-SCAN.localdomain.com.


--start service on DNS server. 
service named start

--Turn on service on reboot of DNS service. 
chkconfig --levl 35 named on 

--put below entries in each node. 

[root@TESTP2 ~]# vi /etc/resolv.conf
search localdomain.com
nameserver 192.168.100.150

--Test SCAN-IP in reverse lookup 

[root@TESTP2 ~]# nslookup TESTP-SCAN
Server:         192.168.100.150
Address:        192.168.100.150#53

Name:   TESTP-SCAN.localdomain.com
Address: 192.168.100.185
Name:   TESTP-SCAN.localdomain.com
Address: 192.168.100.186
Name:   TESTP-SCAN.localdomain.com
Address: 192.168.100.187

[root@TESTP2 ~]# nslookup TESTP-SCAN
Server:         192.168.100.150
Address:        192.168.100.150#53

Name:   TESTP-SCAN.localdomain.com
Address: 192.168.100.187
Name:   TESTP-SCAN.localdomain.com
Address: 192.168.100.185
Name:   TESTP-SCAN.localdomain.com
Address: 192.168.100.186

[root@TESTP2 ~]# nslookup TESTP-SCAN
Server:         192.168.100.150
Address:        192.168.100.150#53

Name:   TESTP-SCAN.localdomain.com
Address: 192.168.100.186
Name:   TESTP-SCAN.localdomain.com
Address: 192.168.100.187
Name:   TESTP-SCAN.localdomain.com
Address: 192.168.100.185


--configure ssh for GRID/ORACLE user

--NODE 1

/home/grid
TESTP1@:/home/grid : $mkdir -p ~/.ssh
TESTP1@:/home/grid : $chmod 700 ~/.ssh
TESTP1@:/home/grid : $/usr/bin/ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/grid/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/grid/.ssh/id_rsa.
Your public key has been saved in /home/grid/.ssh/id_rsa.pub.
The key fingerprint is:
ed:70:21:f8:28:c2:9d:18:bf:c8:ed:d8:0f:23:fe:2a grid@TESTP1.localdomain.com
TESTP1@:/home/grid : $/usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/home/grid/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/grid/.ssh/id_dsa.
Your public key has been saved in /home/grid/.ssh/id_dsa.pub.
The key fingerprint is:
b9:e4:f1:2e:22:78:eb:b6:b3:21:f9:86:24:ee:63:52 grid@TESTP1.localdomain.com

--NODE 2

TESTP2@:/home/grid : $mkdir -p ~/.ssh
TESTP2@:/home/grid : $chmod 700 ~/.ssh
TESTP2@:/home/grid : $/usr/bin/ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/grid/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/grid/.ssh/id_rsa.
Your public key has been saved in /home/grid/.ssh/id_rsa.pub.
The key fingerprint is:
70:af:37:b6:9b:e9:ce:f8:a1:bf:00:00:2c:9d:50:e5 grid@TESTP2.localdomain.com
TESTP2@:/home/grid : $/usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/home/grid/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/grid/.ssh/id_dsa.
Your public key has been saved in /home/grid/.ssh/id_dsa.pub.
The key fingerprint is:
96:d4:e2:55:18:e2:d8:3e:64:25:a3:62:47:6a:4e:0b grid@TESTP2.localdomain.com

--NODE 1 

TESTP1@:/home/grid : $ssh TESTP1 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
The authenticity of host 'testp1 (192.168.100.181)' can't be established.
RSA key fingerprint is 27:ac:4b:9a:e3:d2:ae:6d:2b:71:99:8d:b9:c0:b1:a7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'testp1,192.168.100.181' (RSA) to the list of known hosts.
grid@testp1's password:
TESTP1@:/home/grid : $ssh TESTP1 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys

TESTP1@:/home/grid : $scp /home/grid/.ssh/authorized_keys grid@TESTP2:~/.ssh/
grid@testp2's password:
authorized_keys                                         100% 1026     1.0KB/s   00:00
TESTP1@:/home/grid : $

-- NODE 2 

TESTP2@:/home/grid : $ssh TESTP2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
The authenticity of host 'testp2 (192.168.100.182)' can't be established.
RSA key fingerprint is 27:ac:4b:9a:e3:d2:ae:6d:2b:71:99:8d:b9:c0:b1:a7.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'testp2,192.168.100.182' (RSA) to the list of known hosts.
grid@testp2's password:
TESTP2@:/home/grid : $ssh TESTP2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
TESTP2@:/home/grid : $scp /home/grid/.ssh/authorized_keys grid@TESTP1:~/.ssh/
grid@testp1's password:
authorized_keys                                        100% 2461     2.4KB/s   00:00

--CONFIRM SSH 

TESTP2@:/home/grid : $ssh TESTP1 date
Mon Aug  6 10:47:53 EDT 2012
TESTP2@:/home/grid : $ssh TESTP2 date
Mon Aug  6 10:48:06 EDT 2012

TESTP1@:/home/grid : $ssh TESTP1 date
Mon Aug  6 10:48:22 EDT 2012
TESTP1@:/home/grid : $ssh TESTP2 date
Mon Aug  6 10:48:36 EDT 2012


--Run cluvfy.

TESTP1@:/u01/grid : $./runcluvfy.sh stage -pre crsinst -n TESTP1,TESTP2 -fixup -verbose

Performing pre-checks for cluster services setup

Checking node reachability...

Check: Node reachability from node "TESTP1"
  Destination Node                      Reachable?
  ------------------------------------  ------------------------
  TESTP1                                yes
  TESTP2                                yes
Result: Node reachability check passed from node "TESTP1"


Checking user equivalence...

Check: User equivalence for user "grid"
  Node Name                             Status
  ------------------------------------  ------------------------
  TESTP1                                passed
  TESTP2                                passed
Result: User equivalence check passed for user "grid"

Checking node connectivity...

Checking hosts config file...
  Node Name                             Status
  ------------------------------------  ------------------------
  TESTP1                                passed
  TESTP2                                passed

Verification of the hosts config file successful


Interface information for node "TESTP1"
 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   192.168.100.181 192.168.100.0   0.0.0.0         192.168.152.1   00:0C:29:9A:83:1C 1500
 eth1   192.168.152.181 192.168.152.0   0.0.0.0         192.168.152.1   00:0C:29:9A:83:26 1500


Interface information for node "TESTP2"
 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   192.168.100.182 192.168.100.0   0.0.0.0         192.168.152.1   00:0C:29:CE:9B:85 1500
 eth1   192.168.152.182 192.168.152.0   0.0.0.0         192.168.152.1   00:0C:29:CE:9B:8F 1500


Check: Node connectivity of subnet "192.168.100.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  TESTP1[192.168.100.181]         TESTP2[192.168.100.182]         yes
Result: Node connectivity passed for subnet "192.168.100.0" with node(s) TESTP1,TESTP2


Check: TCP connectivity of subnet "192.168.100.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  TESTP1:192.168.100.181          TESTP2:192.168.100.182          passed
Result: TCP connectivity check passed for subnet "192.168.100.0"


Check: Node connectivity of subnet "192.168.152.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  TESTP1[192.168.152.181]         TESTP2[192.168.152.182]         yes
Result: Node connectivity passed for subnet "192.168.152.0" with node(s) TESTP1,TESTP2


Check: TCP connectivity of subnet "192.168.152.0"
  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  TESTP1:192.168.152.181          TESTP2:192.168.152.182          passed
Result: TCP connectivity check passed for subnet "192.168.152.0"


Interfaces found on subnet "192.168.152.0" that are likely candidates for VIP are:
TESTP1 eth1:192.168.152.181
TESTP2 eth1:192.168.152.182

Interfaces found on subnet "192.168.100.0" that are likely candidates for a private interconnect are:
TESTP1 eth0:192.168.100.181
TESTP2 eth0:192.168.100.182
Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.100.0".
Subnet mask consistency check passed for subnet "192.168.152.0".
Subnet mask consistency check passed.

Result: Node connectivity check passed

Checking multicast communication...

Checking subnet "192.168.100.0" for multicast communication with multicast group "230.0.1.0"...
Check of subnet "192.168.100.0" for multicast communication with multicast group "230.0.1.0" passed.

Checking subnet "192.168.152.0" for multicast communication with multicast group "230.0.1.0"...
Check of subnet "192.168.152.0" for multicast communication with multicast group "230.0.1.0" passed.

Check of multicast communication passed.

Checking ASMLib configuration.
  Node Name                             Status
  ------------------------------------  ------------------------
  TESTP1                                passed
  TESTP2                                passed
Result: Check for ASMLib configuration passed.

Check: Total memory
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        1.6625GB (1743280.0KB)    1.5GB (1572864.0KB)       passed
  TESTP2        1.6625GB (1743280.0KB)    1.5GB (1572864.0KB)       passed
Result: Total memory check passed

Check: Available memory
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        1.441GB (1510964.0KB)     50MB (51200.0KB)          passed
  TESTP2        1.5201GB (1593992.0KB)    50MB (51200.0KB)          passed
Result: Available memory check passed

Check: Swap space
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        3.8437GB (4030456.0KB)    2.4938GB (2614920.0KB)    passed
  TESTP2        3.8437GB (4030456.0KB)    2.4938GB (2614920.0KB)    passed
Result: Swap space check passed

Check: Free disk space for "TESTP1:/tmp"
  Path              Node Name     Mount point   Available     Required      Status
  ----------------  ------------  ------------  ------------  ------------  ------------
  /tmp              TESTP1        /             2.7619GB      1GB           passed
Result: Free disk space check passed for "TESTP1:/tmp"

Check: Free disk space for "TESTP2:/tmp"
  Path              Node Name     Mount point   Available     Required      Status
  ----------------  ------------  ------------  ------------  ------------  ------------
  /tmp              TESTP2        /             2.7832GB      1GB           passed
Result: Free disk space check passed for "TESTP2:/tmp"

Check: User existence for "grid"
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  TESTP1        passed                    exists(1100)
  TESTP2        passed                    exists(1100)

Checking for multiple users with UID value 1100
Result: Check for multiple users with UID value 1100 passed
Result: User existence check passed for "grid"

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

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

Check: Membership of user "grid" in group "oinstall" [as Primary]
  Node Name         User Exists   Group Exists  User in Group  Primary       Status
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            yes           yes           yes           yes           passed
  TESTP2            yes           yes           yes           yes           passed
Result: Membership check for user "grid" in group "oinstall" [as Primary] passed

Check: Membership of user "grid" in group "dba"
  Node Name         User Exists   Group Exists  User in Group  Status
  ----------------  ------------  ------------  ------------  ----------------
  TESTP1            yes           yes           no            failed
  TESTP2            yes           yes           no            failed
Result: Membership check for user "grid" in group "dba" failed

Check: Run level
  Node Name     run level                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        5                         3,5                       passed
  TESTP2        5                         3,5                       passed
Result: Run level check passed

Check: Hard limits for "maximum open file descriptors"
  Node Name         Type          Available     Required      Status
  ----------------  ------------  ------------  ------------  ----------------
  TESTP1            hard          131072        65536         passed
  TESTP2            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      Status
  ----------------  ------------  ------------  ------------  ----------------
  TESTP1            soft          131072        1024          passed
  TESTP2            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      Status
  ----------------  ------------  ------------  ------------  ----------------
  TESTP1            hard          131072        16384         passed
  TESTP2            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      Status
  ----------------  ------------  ------------  ------------  ----------------
  TESTP1            soft          131072        2047          passed
  TESTP2            soft          131072        2047          passed
Result: Soft limits check passed for "maximum user processes"

Check: System architecture
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        x86_64                    x86_64                    passed
  TESTP2        x86_64                    x86_64                    passed
Result: System architecture check passed

Check: Kernel version
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        2.6.18-164.el5xen         2.6.18                    passed
  TESTP2        2.6.18-164.el5xen         2.6.18                    passed
Result: Kernel version check passed

Check: Kernel parameter for "semmsl"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            250           250           250           passed
  TESTP2            250           250           250           passed
Result: Kernel parameter check passed for "semmsl"

Check: Kernel parameter for "semmns"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            32000         32000         32000         passed
  TESTP2            32000         32000         32000         passed
Result: Kernel parameter check passed for "semmns"

Check: Kernel parameter for "semopm"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            100           100           100           passed
  TESTP2            100           100           100           passed
Result: Kernel parameter check passed for "semopm"

Check: Kernel parameter for "semmni"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            142           142           128           passed
  TESTP2            142           142           128           passed
Result: Kernel parameter check passed for "semmni"

Check: Kernel parameter for "shmmax"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            4398046511104  4398046511104  892559360     passed
  TESTP2            4398046511104  4398046511104  892559360     passed
Result: Kernel parameter check passed for "shmmax"

Check: Kernel parameter for "shmmni"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            4096          4096          4096          passed
  TESTP2            4096          4096          4096          passed
Result: Kernel parameter check passed for "shmmni"

Check: Kernel parameter for "shmall"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            1073741824    1073741824    2097152       passed
  TESTP2            1073741824    1073741824    2097152       passed
Result: Kernel parameter check passed for "shmall"

Check: Kernel parameter for "file-max"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            6815744       6815744       6815744       passed
  TESTP2            6815744       6815744       6815744       passed
Result: Kernel parameter check passed for "file-max"

Check: Kernel parameter for "ip_local_port_range"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            between 9000.0 & 65500.0  between 9000.0 & 65500.0  between 9000.0 & 65500.0  passed
  TESTP2            between 9000.0 & 65500.0  between 9000.0 & 65500.0  between 9000.0 & 65500.0  passed
Result: Kernel parameter check passed for "ip_local_port_range"

Check: Kernel parameter for "rmem_default"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            262144        262144        262144        passed
  TESTP2            262144        262144        262144        passed
Result: Kernel parameter check passed for "rmem_default"

Check: Kernel parameter for "rmem_max"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            4194304       4194304       4194304       passed
  TESTP2            4194304       4194304       4194304       passed
Result: Kernel parameter check passed for "rmem_max"

Check: Kernel parameter for "wmem_default"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            262144        262144        262144        passed
  TESTP2            262144        262144        262144        passed
Result: Kernel parameter check passed for "wmem_default"

Check: Kernel parameter for "wmem_max"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            1048576       1048576       1048576       passed
  TESTP2            1048576       1048576       1048576       passed
Result: Kernel parameter check passed for "wmem_max"

Check: Kernel parameter for "aio-max-nr"
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  TESTP1            3145728       3145728       1048576       passed
  TESTP2            3145728       3145728       1048576       passed
Result: Kernel parameter check passed for "aio-max-nr"

Check: Package existence for "make"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        make-3.81-3.el5           make-3.81                 passed
  TESTP2        make-3.81-3.el5           make-3.81                 passed
Result: Package existence check passed for "make"

Check: Package existence for "binutils"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        binutils-2.17.50.0.6-12.el5  binutils-2.17.50.0.6      passed
  TESTP2        binutils-2.17.50.0.6-12.el5  binutils-2.17.50.0.6      passed
Result: Package existence check passed for "binutils"

Check: Package existence for "gcc(x86_64)"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        gcc(x86_64)-4.1.2-46.el5  gcc(x86_64)-4.1.2         passed
  TESTP2        gcc(x86_64)-4.1.2-46.el5  gcc(x86_64)-4.1.2         passed
Result: Package existence check passed for "gcc(x86_64)"

Check: Package existence for "libaio(x86_64)"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        libaio(x86_64)-0.3.106-3.2  libaio(x86_64)-0.3.106    passed
  TESTP2        libaio(x86_64)-0.3.106-3.2  libaio(x86_64)-0.3.106    passed
Result: Package existence check passed for "libaio(x86_64)"

Check: Package existence for "glibc(x86_64)"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        glibc(x86_64)-2.5-42      glibc(x86_64)-2.5-24      passed
  TESTP2        glibc(x86_64)-2.5-42      glibc(x86_64)-2.5-24      passed
Result: Package existence check passed for "glibc(x86_64)"

Check: Package existence for "compat-libstdc++-33(x86_64)"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        compat-libstdc++-33(x86_64)-3.2.3-61  compat-libstdc++-33(x86_64)-3.2.3  passed
  TESTP2        compat-libstdc++-33(x86_64)-3.2.3-61  compat-libstdc++-33(x86_64)-3.2.3  passed
Result: Package existence check passed for "compat-libstdc++-33(x86_64)"

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

Check: Package existence for "elfutils-libelf-devel"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        elfutils-libelf-devel-0.137-3.el5  elfutils-libelf-devel-0.125  passed
  TESTP2        elfutils-libelf-devel-0.137-3.el5  elfutils-libelf-devel-0.125  passed
Result: Package existence check passed for "elfutils-libelf-devel"

Check: Package existence for "glibc-common"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        glibc-common-2.5-42       glibc-common-2.5          passed
  TESTP2        glibc-common-2.5-42       glibc-common-2.5          passed
Result: Package existence check passed for "glibc-common"

Check: Package existence for "glibc-devel(x86_64)"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        glibc-devel(x86_64)-2.5-42  glibc-devel(x86_64)-2.5   passed
  TESTP2        glibc-devel(x86_64)-2.5-42  glibc-devel(x86_64)-2.5   passed
Result: Package existence check passed for "glibc-devel(x86_64)"

Check: Package existence for "glibc-headers"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        glibc-headers-2.5-42      glibc-headers-2.5         passed
  TESTP2        glibc-headers-2.5-42      glibc-headers-2.5         passed
Result: Package existence check passed for "glibc-headers"

Check: Package existence for "gcc-c++(x86_64)"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        gcc-c++(x86_64)-4.1.2-46.el5  gcc-c++(x86_64)-4.1.2     passed
  TESTP2        gcc-c++(x86_64)-4.1.2-46.el5  gcc-c++(x86_64)-4.1.2     passed
Result: Package existence check passed for "gcc-c++(x86_64)"

Check: Package existence for "libaio-devel(x86_64)"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        libaio-devel(x86_64)-0.3.106-3.2  libaio-devel(x86_64)-0.3.106  passed
  TESTP2        libaio-devel(x86_64)-0.3.106-3.2  libaio-devel(x86_64)-0.3.106  passed
Result: Package existence check passed for "libaio-devel(x86_64)"

Check: Package existence for "libgcc(x86_64)"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        libgcc(x86_64)-4.1.2-46.el5  libgcc(x86_64)-4.1.2      passed
  TESTP2        libgcc(x86_64)-4.1.2-46.el5  libgcc(x86_64)-4.1.2      passed
Result: Package existence check passed for "libgcc(x86_64)"

Check: Package existence for "libstdc++(x86_64)"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        libstdc++(x86_64)-4.1.2-46.el5  libstdc++(x86_64)-4.1.2   passed
  TESTP2        libstdc++(x86_64)-4.1.2-46.el5  libstdc++(x86_64)-4.1.2   passed
Result: Package existence check passed for "libstdc++(x86_64)"

Check: Package existence for "libstdc++-devel(x86_64)"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        libstdc++-devel(x86_64)-4.1.2-46.el5  libstdc++-devel(x86_64)-4.1.2  passed
  TESTP2        libstdc++-devel(x86_64)-4.1.2-46.el5  libstdc++-devel(x86_64)-4.1.2  passed
Result: Package existence check passed for "libstdc++-devel(x86_64)"

Check: Package existence for "sysstat"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        sysstat-7.0.2-3.el5       sysstat-7.0.2             passed
  TESTP2        sysstat-7.0.2-3.el5       sysstat-7.0.2             passed
Result: Package existence check passed for "sysstat"

Check: Package existence for "ksh"
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  TESTP1        ksh-20080202-14.el5       ksh-20060214              passed
  TESTP2        ksh-20080202-14.el5       ksh-20060214              passed
Result: Package existence check passed for "ksh"

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

Starting check for consistency of primary group of root user
  Node Name                             Status
  ------------------------------------  ------------------------
  TESTP1                                passed
  TESTP2                                passed

Check for consistency of root user's primary group passed

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

NTP Configuration file check started...
Network Time Protocol(NTP) configuration file not found on any of the nodes. Oracle Cluster Time Synchronization Service(CTSS) can be used instead of NTP for time synchronization on the cluster nodes
No NTP Daemons or Services were found to be running

Result: Clock synchronization check using Network Time Protocol(NTP) passed

Checking Core file name pattern consistency...
Core file name pattern consistency check passed.

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

Check default user file creation mask
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  TESTP1        0022                      0022                      passed
  TESTP2        0022                      0022                      passed
Result: Default user file creation mask check passed
Checking consistency of file "/etc/resolv.conf" across nodes

Checking the file "/etc/resolv.conf" to make sure only one of domain and search entries is defined
File "/etc/resolv.conf" does not have both domain and search entries defined
Checking if domain entry in file "/etc/resolv.conf" is consistent across the nodes...
domain entry in file "/etc/resolv.conf" is consistent across nodes
Checking if search entry in file "/etc/resolv.conf" is consistent across the nodes...
search entry in file "/etc/resolv.conf" is consistent across nodes
Checking file "/etc/resolv.conf" to make sure that only one search entry is defined
All nodes have one search entry defined in file "/etc/resolv.conf"
Checking all nodes to make sure that search entry is "localdomain.com" as found on node "TESTP1"
All nodes of the cluster have same value for 'search'
Checking DNS response time for an unreachable node
  Node Name                             Status
  ------------------------------------  ------------------------
  TESTP1                                failed
  TESTP2                                failed
PRVF-5636 : The DNS response time for an unreachable node exceeded "15000" ms on following nodes: TESTP1,TESTP2

File "/etc/resolv.conf" is not consistent across nodes

Check: Time zone consistency
Result: Time zone consistency check passed
Fixup information has been generated for following node(s):
TESTP1,TESTP2
Please run the following script on each node as "root" user to execute the fixups:
'/tmp/CVU_11.2.0.3.0_grid/runfixup.sh'

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


--we can check DNS response using "time nslookup HOST_NAME"

TESTP1@:/u01/grid : $time nslookup TESTP1
Server:         192.168.100.150
Address:        192.168.100.150#53

Name:   TESTP1.localdomain.com
Address: 192.168.100.181


real    0m0.14s
user    0m0.00s
sys     0m0.01s
TESTP1@:/u01/grid : $time nslookup TESTP2
Server:         192.168.100.150
Address:        192.168.100.150#53

Name:   TESTP2.localdomain.com
Address: 192.168.100.182


real    0m0.02s
user    0m0.00s
sys     0m0.02s




--Start Installation of Grid Infrastructure.















Please Ignore PRVF-5104 .






--Execute root scripts.

to see progress of root.sh
[root@TESTP1 crsconfig]# tail -f /u00/app/11.2.3/grid/cfgtoollogs/crsconfig/rootcrs_testp1.log

[root@TESTP1 Server]# /u00/app/oraInventory/orainstRoot.sh
Changing permissions of /u00/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u00/app/oraInventory to oinstall.
The execution of the script is complete.
[root@TESTP1 Server]# /u00/app/11.2.3/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u00/app/11.2.3/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u00/app/11.2.3/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
OLR initialization - successful
  root wallet
  root wallet cert
  root cert export
  peer wallet
  profile reader wallet
  pa wallet
  peer wallet keys
  pa wallet keys
  peer cert request
  pa cert request
  peer cert
  pa cert
  peer root cert TP
  profile reader root cert TP
  pa root cert TP
  peer pa cert TP
  pa peer cert TP
  profile reader pa cert TP
  profile reader peer cert TP
  peer user cert
  pa user cert
Adding Clusterware entries to inittab
CRS-2672: Attempting to start 'ora.mdnsd' on 'testp1'
CRS-2676: Start of 'ora.mdnsd' on 'testp1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'testp1'
CRS-2676: Start of 'ora.gpnpd' on 'testp1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'testp1'
CRS-2672: Attempting to start 'ora.gipcd' on 'testp1'
CRS-2676: Start of 'ora.gipcd' on 'testp1' succeeded
CRS-2676: Start of 'ora.cssdmonitor' on 'testp1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'testp1'
CRS-2672: Attempting to start 'ora.diskmon' on 'testp1'
CRS-2676: Start of 'ora.diskmon' on 'testp1' succeeded
CRS-2676: Start of 'ora.cssd' on 'testp1' succeeded

ASM created and started successfully.

Disk Group CRS created successfully.

clscfg: -install mode specified
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4256: Updating the profile
Successful addition of voting disk 1b6e9a5de4504f92bf70cd3056169ee6.
Successfully replaced voting disk group with +CRS.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   1b6e9a5de4504f92bf70cd3056169ee6 (/dev/sdb1) [CRS]
Located 1 voting disk(s).
CRS-2672: Attempting to start 'ora.asm' on 'testp1'
CRS-2676: Start of 'ora.asm' on 'testp1' succeeded
CRS-2672: Attempting to start 'ora.CRS.dg' on 'testp1'
CRS-2676: Start of 'ora.CRS.dg' on 'testp1' succeeded
CRS-2672: Attempting to start 'ora.registry.acfs' on 'testp1'
CRS-2676: Start of 'ora.registry.acfs' on 'testp1' succeeded
Configure Oracle Grid Infrastructure for a Cluster ... succeeded

--ON NODE 2 
[root@TESTP2 media]# /u00/app/oraInventory/orainstRoot.sh
Changing permissions of /u00/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u00/app/oraInventory to oinstall.
The execution of the script is complete.


[root@TESTP2 media]# /u00/app/oraInventory/orainstRoot.sh
Changing permissions of /u00/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u00/app/oraInventory to oinstall.
The execution of the script is complete.
[root@TESTP2 media]# /u00/app/11.2.3/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u00/app/11.2.3/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u00/app/11.2.3/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
OLR initialization - successful
Adding Clusterware entries to inittab
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node testp1, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
Configure Oracle Grid Infrastructure for a Cluster ... succeeded


----CLUSTER STATUS After Grid Installation.

TESTP1@:/home/grid :+ASM1 $crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       testp1
               ONLINE  ONLINE       testp2
ora.asm
               ONLINE  ONLINE       testp1                   Started
               ONLINE  ONLINE       testp2                   Started
ora.gsd
               OFFLINE OFFLINE      testp1
               OFFLINE OFFLINE      testp2
ora.net1.network
               ONLINE  ONLINE       testp1
               ONLINE  ONLINE       testp2
ora.ons
               ONLINE  ONLINE       testp1
               ONLINE  ONLINE       testp2
ora.registry.acfs
               ONLINE  ONLINE       testp1
               ONLINE  ONLINE       testp2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       testp2
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       testp1
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       testp1
ora.cvu
      1        ONLINE  ONLINE       testp1
ora.oc4j
      1        ONLINE  ONLINE       testp1
ora.scan1.vip
      1        ONLINE  ONLINE       testp2
ora.scan2.vip
      1        ONLINE  ONLINE       testp1
ora.scan3.vip
      1        ONLINE  ONLINE       testp1
ora.testp1.vip
      1        ONLINE  ONLINE       testp1
ora.testp2.vip
      1        ONLINE  ONLINE       testp2


CREATE DISKGROUP DATABASE

--Create Diskgroup for Database.

TESTP1@:/home/grid :+ASM1 $sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 6 17:46:38 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options


SQL> CREATE DISKGROUP TESTDG  EXTERNAL  REDUNDANCY
SQL> DISK  '/dev/sdc1','/dev/sdd1','/dev/sde1'
SQL> ATTRIBUTE 'au_size'='1M',
SQL> 'compatible.asm' = '11.2', 
SQL> 'compatible.rdbms' = '11.2';
    
Diskgroup created.

--Mount Diskgroup on other Nodes. 

TESTP2@:/home/grid :+ASM2 $sqlplus / as sysasm

SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 6 18:52:06 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> ALTER DISKGROUP TESTDG MOUNT;

Diskgroup altered.

SQL>

--Install Database software and create DB.





















---After this point you will run root script.


--And root.sh after DB installation.

[root@TESTP1 ~]# /u01/app/oracle/11.2.3/db/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/11.2.3/db

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.