Friday, July 23, 2010

Statspack setup in Brief

It seems statspack is the only option if the database is standard edition. Diagnostic and Tuning packs are not an option on standard edition.

Installing statspack metalink ntoe 149113.1
Blog is for 11gR1 (11.1.0.7)
SQL> @?/rdbms/admin/spcreate

Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: pefstat
pefstat


Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data.  Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.

Choose the PERFSTAT users's default tablespace.  This is the tablespace
in which the STATSPACK tables and indexes will be created.

TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
TOOLS                          PERMANENT
USERS                          PERMANENT
WSTBS                          PERMANENT
..
Pressing  will result in STATSPACK's recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: tools

Using tablespace TOOLS as PERFSTAT default tablespace.


Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas).  Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.

Choose the PERFSTAT user's Temporary tablespace.

TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP                           TEMPORARY *

Pressing  will result in the database's default Temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: temp

Using tablespace temp as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
... Creating views     
..
SQL> set echo off;
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
Configuring statspack refer metalink note 149121.1 which lists default values.
Change the default snap level 5 to 7 (Good for taking snapshots every hour or level 0 and every 15 minutes)
exec statspack.modify_statspack_parameter(i_snap_level => 7, i_modify_parameter=>'true');
Verify the changes in
select snap_level from stats$statspack_parameter;

SNAP_LEVEL
----------
7
On RAC systems run the parameter modify command on each instance and check snap level has been set on all nodes
SQL> select instance_number,snap_level from stats$statspack_parameter;

INSTANCE_NUMBER SNAP_LEVEL
--------------- ----------
              1          7
              2          7
To take snapshots simply run
declare
snap_no number;
begin
snap_no := statspack.snap;
dbms_output.put_line('snapshot number '||snap_no);
end;
/
To take snapshot automatically set aq_tm_processes and job_queue_processes to > 0 and
@?/rdbms/admin/spauto
will take snapshot every hour on the hour. In a RAC environment run the spauto script on all nodes to add instnace specific jobs.
JOB   INSTANCE INTERVAL
---- ---------- ------------------------------
30          1 trunc(SYSDATE+1/24,'HH')
50          2 trunc(SYSDATE+1/24,'HH')
Change the snapshot interval with
dbms_job.interval(jobid,interval);
Create statspack report with
@?/rdbms/admin/spreport
For best performance when running statspack report gather statistics on perfstat schema with
dbms_stats.gather_schema_stats('PERFSTAT');
Making baseline explained on metalink note 394937.1
There are two ways to make baselines using snapshots, which include snap_shot ranges, date range. Make a baseline with
exec Statspack.make_baseline(begin_snapid,end_snapid);
or 
statspack.make_baseline(to_date(begin_date),to_date(end_date));
Snapshots making the baselines are not affected by snapshot purge.

There are number of ways to purge snapshots by specifying range of snapshots, range of dates, purge all snapshots before a certain date or delete all snapshots taken number of days before the current date.
statspack.purge(begin_snapid,end_snapid);
could be used for this.
@?/rdbms/admin/sppurge
could also be used to purge the snapshot. It will purge snapshot of the currently connected instance.

Drop statspack with
@?/rdbms/admin/spdrop
Tuning with statspack on metalink note 228913.1
Deleting snapshots at regular intervals, this is not part of the oracle statspack setup files.
create table to store deleted snap information.
create table deleted_snaps(delete_time timestamp primary key,
min_snapid number, 
max_snapid number, 
dbid number, 
instance_number number);
Procedure for keeping only the snapshots taken two weeks ago
create or replace
Procedure Delete_Snaps As
min_snap_id stats$snapshot.snap_id%type;
max_snap_id stats$snapshot.snap_id%type;
i_dbid stats$snapshot.snap_id%type;
i_instance_num stats$snapshot.snap_id%type;
snapshots_purged pls_integer;
cursor delsnaps is select dbid,instance_number,min(snap_id) as min,
max(snap_id) as max from stats$snapshot 
where to_char(snap_time,'YYYY-MM-DD') = to_char(sysdate-15,'YYYY-MM-DD') 
group by dbid,instance_number;
begin
open delsnaps;
loop
fetch delsnaps into i_dbid,i_instance_num,min_snap_id,max_snap_id;
Exit When Delsnaps%Notfound;
snapshots_purged := statspack.purge( i_begin_snap      => min_snap_id
, i_end_snap        => max_snap_id
, i_snap_range      => true
, i_extended_purge  => true
, i_dbid            => i_dbid
, i_instance_number => i_instance_num);

Insert Into Deleted_Snaps values(Systimestamp,Min_Snap_Id,Max_Snap_Id,i_dbid,i_instance_num);  
end loop;
Close Delsnaps;
commit;
End;
/
Schedule a job to delete at 12.15 am on a Rac environment it will delete snapshots of all instances that meet the delete criteria, no need to run on all instances.
Declare
  Job_Id Number;
Begin
  Dbms_job.submit(job => job_id,
  what => 'Delete_Snaps;',
  next_date => trunc(SYSDATE + 1,'DD') + 15/1444 ,
  Interval => 'trunc(SYSDATE + 1,''DD'') +15/1444');
end;
/
Update on 07 December 2011
At times it is possible to see the following error message on the alert log
ORA-12012: error on auto execute of job 50
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 5661
ORA-06512: at "PERFSTAT.STATSPACK", line 105
ORA-06512: at line 1
This is because there could be same values for columns (MUTEX_TYPE, LOCATION) in v$mutex_sleep view. As a workaround disable the unique constraint and create a non-unique index on affected columns.
SQL> alter table perfstat.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;

SQL> create index perfstat.STATS$MUTEX_SLEEP_PK on STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);
This has been tracked as the BUG:5443234 - STATSPACK.SNAP CAUSING UNIQUE CONSTRAINT (STATS$MUTEX_SLEEP_PK) VIOLATED mentioned in the metalink note ORA-1 on PERFSTAT.STATS$MUTEX_SLEEP_PK when executing statspack.snap [ID 382993.1]



Update on 07 October 2020
As of 12.2 DBMS_JOB package is depreciated. Use DBMS_SCHEDULER to create a snap job and statspack delete job as below.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STATSPACK_SNAP_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'statspack.snap',
start_date => TO_TIMESTAMP('0' ,'MI'),
repeat_interval => 'FREQ=HOURLY',
enabled => TRUE);
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'Delete_Snaps_job',
job_type => 'STORED_PROCEDURE',
job_action => 'Delete_Snaps',
start_date => trunc(SYSDATE + 1,'DD') + 30/1444,
repeat_interval => 'FREQ=DAILY',
enabled => TRUE);
END;
/

Tuesday, July 20, 2010

Silent installation highlights

Install the software only. In this case standard edition installation is done.

./runInstaller -responseFile /home/oracle/database/response/standard.rsp -silent

Patchset installation (11.1.0.7). Set OCM ignore configuration to true
#------------------------------------------------------------------------------
# Name : COLLECTOR_IGNORE_CONFIGURATION
# Datatype : Boolean
# Description : This is set to true to ignore OCM configuration page display.
# Metalink credentials.
# Valid values : true,false
# Default value : false
# Mandatory : No
#------------------------------------------------------------------------------
COLLECTOR_IGNORE_CONFIGURATION=true
And run patch installation
./runInstaller -silent -responsefile /home/oracle/Disk1/response/patchset.rsp

Run net configuration assistant. Only difference is instead of (-) for command line parameters should use (/).
netca /silent /responsefile /home/oracle/database/response/netca.rsp

Finally the database creation. Set EMCONFIGURATION to LOCAL.
dbca -silent -responsefile /home/oracle/database/response/dbca.rsp
These are only the highlights, values within the rsp files must be set correctly.

Change of oraInventory Locations in 10g and 11g

It appears the default or peferred location for oraInventory directory has been changed in Oracle 11g (R1 and R2) versions from that of the 10gR2.
The "Choosing Mount Points" sections in the appendix (B on 10g, C on 11g) is refered it will contain
Directory                            Path
Oracle base($ORACLE_BASE) /u01/app/oracle
Oracle Inventory $ORACLE_BASE/oraInventory
Oracle home $ORACLE_BASE/product/(10.2/11.1)11.2.0/dbhome_1
According to this oraInventory comes under the Oracle base directory.

On 10g documentation it states "The first time you install Oracle software on a system, Oracle Universal Installer prompts you to specify the path to this directory. then Oracle recommends that you choose the following path:
oracle_base/oraInventory
Oracle Universal Installer creates the directory that you specify and sets the correct owner, group, and permissions for it. You do not need to create it."

However on 11g documentations "The first time you install Oracle software on a system, Oracle Universal Installer checks if you have created an OFA-compliant directory structure with the format u[01-09]/app, such as /u01/app, and that the user running the installation has permissions to write to that path. If this is true, then Oracle Universal Instaler creates the Oracle Inventory directory similar to /u[01-09]/app/oraInventory. For example:
/u01/app/oraInventory If you have set the environment variable ORACLE_BASE for the oracle user, then Oracle Universal Installer creates the Oracle Inventory directory similar to $ORACLE_BASE/../oraInventory. For example, if ORACLE_BASE is set to /opt/oracle/11, then the Oracle Inventory directory is created similar to /opt/oracle/oraInventory."

Furthermore both on 11gR1 and 11gR2 documentation it is stated "Oracle recommends that you do not put the oraInventory directory under Oracle base for a new installation. However, if you have an existing installation, then you should follow the steps suggested in this section."

This change could be observed in the OUI installer when started with ORACLE_BASE vairable set the oraInventory location defaults to $ORACLE_BASE/../oraInventory.

ORACLE_BASE=/opt/app/oracle

10gR2

11gR1

11gR2


Monday, July 19, 2010

Script for tailing alert log and useful aliases

Quickest ways to get access to alert*.log is to have aliases created for them. Following aliases will be useful in getting a tail output on asm alert log and crsd.log
asmsid=`grep ^\+ASM /etc/oratab | cut -d ":" -f1`
alias asmtail='tail -f $ORACLE_BASE/diag/asm/+asm/$asmsid/trace/alert_$asmsid.log'
alias crsdtail='tail -f $CRS_HOME/log/`hostname | cut -d "." -f1`/crsd/crsd.log'
Add these .bashrc or .bash_profile and alias will be available like any other normal command.
However to create a alias for getting tail of a database alert log is not so simple. There are several consideration specially in development environments if there are multiple databases (db1, db2),multiple versions (10g,11g) and multiple configurations (clustered and non-clustered) all will have different paths to the alert log.
Following scripts has been written incorporating all these complexities and has been tested on a environment where there was 11g RAC, 11g and 10g non-RAC database instances. dbtail script "function" will use the current ORACLE_SID value in the environment and tail the alert log corresponding to that ORACLE_SID.
Add to .bashrc or .bash_profile and script function will be available on the command prompt.
Only assumptions made are $CRS_HOME $ORACLE_BASE and $ORACLE_SID variables are set
dbtail() {

echo $ORACLE_SID
tailpath=""
# get current node's id
if [ -f $CRS_HOME/bin/olsnodes ]; then
nodeid=`$CRS_HOME/bin/olsnodes -l -n | cut -f2`
fi


#check if 11g
if [ -d $ORACLE_BASE/diag/rdbms ]; then

#check if 11g cluster db log
if [ -d $ORACLE_BASE/diag/rdbms/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`}/$ORACLE_SID ]; then

tailpath=$ORACLE_BASE/diag/rdbms/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`}/$ORACLE_SID/trace/alert_$ORACLE_SID.log
else
#check if 11g non cluster db log
if [ -d $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID ]; then
tailpath=$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log

else

#check if 10g
if [ -d $ORACLE_BASE/admin ]; then

# non cluster 10g
if [ -f $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log ]; then

tailpath=$ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
else

if [ -d $ORACLE_BASE/admin/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`} ]; then

tailpath=$ORACLE_BASE/admin/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`}/bdump/alert_$ORACLE_SID.log

fi

fi

fi

fi

fi
else
#no 11g in the system, check if 10g
if [ -d $ORACLE_BASE/admin ]; then

# non cluster 10g
if [ -f $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log ]; then

tailpath=$ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log
else

if [ -d $ORACLE_BASE/admin/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`} ]; then

tailpath=$ORACLE_BASE/admin/${ORACLE_SID:0:`expr ${#ORACLE_SID} - ${#nodeid}`}/bdump/alert_$ORACLE_SID.log

fi

fi

fi
fi

echo "tailing output of --->>>"$tailpath
tail -100f $tailpath
}


Sunday, July 18, 2010

java.lang.NoClassDefFoundError: OsInfo

After upgrading to 10.2.0.5 following error could be seen when using opatch.
/opt/app/oracle/product/10.2.0/ent/OPatch/opatch version
Exception in thread "main" java.lang.NoClassDefFoundError: OsInfo
Invoking OPatch 10.2.0.4.9

OPatch Version: 10.2.0.4.9

OPatch succeeded.
Believe this is due to missing OsInfo class in the opatch binaries bundled with 10.2.0.5 upgrade software.
Download the latest opatch from metalink and unzip into affected oracle home and error should disappear.

Friday, July 16, 2010

ORA-15061: ASM operation not supported

When ASM and RDBMS are in two seperate homes in 11gR1 and 11gR2
SQL> create tablespace test datafile '+DATA(datafile)' size 10m
2 ;
create tablespace test datafile '+DATA(datafile)' size 10m
*
ERROR at line 1:
ORA-19510: failed to set size of 1280 blocks for file
"+DATA/livedb/datafile/test.285.724527409" (block size=8192)
ORA-17505: ksfdrsz:1 Failed to resize file to size 1280 blocks
ORA-15061: ASM operation not supported [41]
could be seen when RDBMS has been applied a PSU while ASM isn't.

To resovle apply the PSU to ASM Home as well.

Above has been observed in 11gR1.

What this means in 11gR2 is that before applying the PSU to RDBMS home have to wait and see if the patch for grid infrastructure is released, since as of 11gR2 grid home is also the ASM home.
metalink note 1070880.1 describes the issue in 11gR2.

Updated information for PSU 11.2.0.1.2 available on metalink note 1089071.1

ONS configuration doesn't get the full hostname

On Oracle 11gR1 clusterware after a fresh installation (11.1.0.6) and even after upgrading to 11.1.0.7 clusterware, the ONS configuration doesn't have the full hostname given by the hostname command on linux. Even if the full hostname is given during the clusterware installation. Only the 'short' hostname will appear without the domain values.
$CRS_HOME/bin/onsctl ping
Number of onsconfiguration retrieved, numcfg = 2
onscfg[0]
{node = db01, port = 6251}
Adding remote host db01:6251
onscfg[1]
{node = db02, port = 6251}
Adding remote host db02:6251
ons is running ...

$ hostname
db02.edsa.local
ONS config information could also be seen in the ocrdump as well. This mismatch is noted in metalink notes 744849.1,754619.1 and 759895.1. It is important that hostname in the ons config and hostname given by the linux hostname command match. It is vital for Fast Connection Failover (FCF metalink note 566573.1) used in java applications with jdbc.
If the hostname is a mismatch the FCF will not work.
To fix it simply remove the invalid hostname entry and add the full hostname
# racgons remove_config short_hostname
# racgons add_config full_hostname:6251


Oracle 11gR2 RAC SPfile Issue

It seems in Oracle 11gR2 whenever srvctl is used to start the database it actively replaces the init*.ora entry in the ORACLE_HOME/dbs directory. But if the spfile was later recreated in ASM it will have a different filename and entry added during srvctl start (taken from OCR) is nolonger valid and shows a error message that is not relavent to the cause of the error.
Here are some observations.

on 10.2.0.4
srvctl srvctl config database -d livedb -a
tbxdb1 livedb2 /opt/oracle/app/oracle/product/10.2.0/db_1
tbxdb2 livedb1 /opt/oracle/app/oracle/product/10.2.0/db_1
DB_NAME: null
ORACLE_HOME: /opt/oracle/app/oracle/product/10.2.0/db_1
SPFILE: +DATA/livedb/spfilelivedb.ora
DOMAIN: domain.com
DB_ROLE: null
START_OPTIONS: null
POLICY: AUTOMATIC
ENABLE FLAG: DB ENABLED

more initlivedb2.ora
SPFILE='+DATA/livedb/spfilelivedb.ora'
on 11.1.0.7
srvctl config database -d livedb -a
278071-dbclus1 livedb1 /opt/dm/app/oracle/product/11.1.0/db_1
278072-dbclus2 livedb2 /opt/dm/app/oracle/product/11.1.0/db_1
DB_UNIQUE_NAME: livedb
DB_NAME: livedb
ORACLE_HOME: /opt/dm/app/oracle/product/11.1.0/db_1
SPFILE: +DATA/livedb/spfilelivedb.ora
DOMAIN: domain.com
DB_ROLE: null
START_OPTIONS: null
POLICY: AUTOMATIC
ENABLE FLAG: DB ENABLED

more initlivedb1.ora
SPFILE='+DATA/livedb/spfilelivedb.ora'
on 11.2.0.1
srvctl config database -d clusdb
Database unique name: clusdb
Database name:
Oracle home: /opt/app/oracle/product/11.2.0/clusdb
Oracle user: oracle
Spfile: +DATA/clusdb/spfileclusdb.ora_1277909842526
Domain: domain.net
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: clusdb
Database instances: clusdb1
Disk Groups: DATA,FLASH
Services:
Database is administrator managed

more initclusdb1.ora
SPFILE='+DATA/clusdb/spfileclusdb.ora_1277909842526' # line added by Agent
From the outputs above it could be seen that in 11.2 real spfile location is referred not the ASM alias.

But the real spfile name has changed due to recreation
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfileclusdb.ora => +DATA/CLUSDB/PARAMETERFILE/
spfile.265.723730605
When trying to start the database with srvctl following error is thrown
srvctl start database -d clusdb
PRCR-1079 : Failed to start resource ora.clusdb.db
ORA-01078: failure in processing system parameters
CRS-2674: Start of 'ora.clusdb.db' on 'hpc1' failed
CRS-2632: There are no more servers to try to place resource 'ora.clusdb.db' on that would satisfy its placement policy
One way to solve the problem is to edit the init*.ora file to point to spfiledbname.ora in the ASM instance and start the database with sqlplus.
vi initclusdb1.ora
SPFILE='+DATA/clusdb/spfileclusdb.ora'
sqlplus / as sysdba
sql>startup
will bring up the database but in subsequent srvctl start will add the original entry to the init*.ora file. Content of the ORACLE_HOME/dbs directory
ls
hc_clusdb1.dat initclusdb1.ora orapwclusdb orapwclusdb1 snapcf_clusdb1.f
more initclusdb1.ora
SPFILE='+DATA/clusdb/spfileclusdb.ora'
Only one init*.ora file and it's content is shown. Stop and start the database with srvctl
srvctl stop database -d clusdb
ls
hc_clusdb1.dat initclusdb1.ora orapwclusdb orapwclusdb1 snapcf_clusdb1.f

srvctl start database -d clusdb
PRCR-1079 : Failed to start resource ora.clusdb.db
ORA-01078: failure in processing system parameters
CRS-2674: Start of 'ora.clusdb.db' on 'hpc1' failed
CRS-2632: There are no more servers to try to place resource 'ora.clusdb.db' on that would satisfy its placement policy

ls
hc_clusdb1.dat initclusdb1.ora initclusdb1.ora.bak.hpc1 orapwclusdb orapwclusdb1 snapcf_clusdb1.f

more initclusdb1.ora
SPFILE='+DATA/clusdb/spfileclusdb.ora_1277909842526' # line added by Agent
As seen from the above outputs the existing init file is backed up and a new one created with the original spfile location by the agent.

Best way to fix it is by using srvctl modify to add the generic spfile location.
srvctl modify database -d clusdb -p +DATA/clusdb/spfileclusdb.ora
srvctl config database -d clusdb
Database unique name: clusdb
Database name:
Oracle home: /opt/app/oracle/product/11.2.0/clusdb
Oracle user: oracle
Spfile: +DATA/clusdb/spfileclusdb.ora
Domain: domain.net
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: clusdb
Database instances: clusdb1
Disk Groups: DATA,FLASH
Services:
Database is administrator managed
Whenever spfile is recreated new spfile could be linked to the ASM alias thus making the init*.ora file content valid all the time.

On metalink the error messages CRS-2632, CRS-2674, ORA-01078 has also been linked to metalink notes 1069254.1, 889845.1, 1071324.1, 1088754.1 with regard to 11gR2 issues.

Thursday, July 8, 2010

Inserting sar output to database table

This shell commands shows how to insert couple of sar commands' output into a database table.

Key
" - double quote
' - single quote


First command is
sar
Linux 2.6.18-194.el5 07/08/2010

12:00:01 AM CPU %user %nice %system %iowait %steal %idle
12:10:01 AM all 1.06 0.00 0.43 1.29 0.00 97.21
12:20:01 AM all 0.34 0.00 0.43 1.04 0.00 98.19
12:30:01 AM all 0.35 0.00 0.46 1.15 0.00 98.05
12:40:01 AM all 0.34 0.00 0.46 1.11 0.00 98.09
12:50:01 AM all 0.33 0.00 0.49 1.06 0.00 98.13
01:00:01 AM all 0.34 0.00 0.46 1.09 0.00 98.11
01:10:01 AM all 0.37 0.00 0.48 1.09 0.00 98.06
01:20:01 AM all 0.33 0.00 0.49 1.12 0.00 98.06
Create the table with
Create Table Sar_Cpu (Collection_Time Timestamp (6),CPU_PCT NUMBER(5,2),SYSTEM_PCT NUMBER(5,2),IOWAIT_PCT NUMBER(5,2),IDLE_PCT  Number(5,2));
Execute the command to insert data into the table
sar | grep -v "^[a-Z]\|CPU" | awk '{print "insert into sar_cpu values (to_timestamp(""'\''"$1"'\''"",
""
'\''HH24:MI:SS""'\''""),"$4","$6","$7","$9");"}' | sqlplus -s asanga/***

Next is sar -q
Linux 2.6.18-194.el5       07/08/2010

12:00:01 AM runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15
12:10:01 AM 1 598 0.20 0.10 0.09
12:20:01 AM 0 596 0.21 0.27 0.16
12:30:01 AM 0 595 0.39 0.27 0.20
12:40:01 AM 0 596 0.66 0.40 0.29
12:50:01 AM 0 596 0.32 0.29 0.27
01:00:01 AM 0 595 0.01 0.08 0.16
01:10:01 AM 1 595 0.14 0.25 0.20
01:20:01 AM 0 596 0.03 0.10 0.14
Create the table to insert the data
create table sar_loadavg (sample_time timestamp,run_queue number(2),process_lis number(4),load_avg_1 number(5,2),load_avg_5 number(5,2),load_avg_15 number(5,2));
Run the shell command
sar -q | grep -v "^[a-Z]\|CPU" | awk '{print "insert into sar_loadavg values (to_timestamp(""'\''"$1"'\''"",
""
'\''HH24:MI:SS""'\''""),"$3","$4","$5","$6","$7");"}' | sqlplus -s asanga/***
Choose something similar to ADMon to plot a graph with the inserted data


Wednesday, July 7, 2010

Migrating Voting Disk to ASM in 11gR2 Clusterware

This blog uses the clusterware migrated to 11.2 which has its voting disk on block device.
Vote disks will be migrated to ASM diskgroup created earlier to store OCR which has ASM Compatibility set to 11.2.0.0. It is a disk group with normal redundency which stores three voting disks. There's no way to directly influence the number of voting disks in a disk group.
add css votedisks and delete css votedisks commands are not useable for vote disks in ASM, only for vote disks in shared file system.

Unlike earlier crsctl votedisk commands, on 11gR2 it is not required to run the vote disk commands as root user. They could be run as user owning the cluster.

1. Get the current configuration of the vote disk
crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 755a7dc1e2cfcf76bf5b79632d35b4a9 (/dev/sdc6) []
Located 1 voting disk(s).
Output shows current vote disks is in a block device and also the File Universal Id (FUID) which is used in delete command.

2. Migrate the vote disk from block device to ASM with
crsctl replace votedisk +CLUSTERDG
CRS-4256: Updating the profile
Successful addition of voting disk 0e3826501aa84ff2bfe43653ceac3e22
Successful addition of voting disk a568adb9e2d84f48bf290182977742d7
Successful addition of voting disk 0573b03a65494fdbbfcd49d2bdc335fd.
Successful deletion of voting disk 755a7dc1e2cfcf76bf5b79632d35b4a9.
Successfully replaced voting disk group with +CLUSTERDG.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
On cluster alert log
2010-07-07 14:26:05.972
[cssd(7330)]CRS-1605:CSSD voting file is online: ORCL:CLUS1; details in /opt/app/11.2.0/grid/log/hpc1/cssd/ocssd.log.
2010-07-07 14:26:05.992
[cssd(7330)]CRS-1605:CSSD voting file is online: ORCL:CLUS2; details in /opt/app/11.2.0/grid/log/hpc1/cssd/ocssd.log.
2010-07-07 14:26:06.009
[cssd(7330)]CRS-1605:CSSD voting file is online: ORCL:CLUS3; details in /opt/app/11.2.0/grid/log/hpc1/cssd/ocssd.log.
2010-07-07 14:26:06.376
[cssd(7330)]CRS-1626:A Configuration change request completed successfully
2010-07-07 14:26:06.382
[cssd(7330)]CRS-1601:CSSD Reconfiguration complete. Active nodes are hpc1 .
here CLUS1,CLUS2 and CLUS3 are the ASM disks in the CLUSTERDG disk group.

3. Query the current vote disk configuration
crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 0e3826501aa84ff2bfe43653ceac3e22 (ORCL:CLUS1) [CLUSTERDG]
2. ONLINE a568adb9e2d84f48bf290182977742d7 (ORCL:CLUS2) [CLUSTERDG]
3. ONLINE 0573b03a65494fdbbfcd49d2bdc335fd (ORCL:CLUS3) [CLUSTERDG]
Located 3 voting disk(s).
4. All vote disks are not on ASM and former block device is no longer in use with the cluster.




To Migrate back to a block device from ASM

1. Replace the vote disk to block device
crsctl replace votedisk /dev/sdc6
Now formatting voting disk: /dev/sdc6.
CRS-4256: Updating the profile
Successful addition of voting disk 506e58aa8c4d4f94bf25fb8b646bfd5d.
Successful deletion of voting disk 0e3826501aa84ff2bfe43653ceac3e22.
Successful deletion of voting disk a568adb9e2d84f48bf290182977742d7.
Successful deletion of voting disk 0573b03a65494fdbbfcd49d2bdc335fd.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
2. Query the vote disk configuration
crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 506e58aa8c4d4f94bf25fb8b646bfd5d (/dev/sdc6) []
Located 1 voting disk(s).

To add another voting disk

1. Set the permission on the new block device to 660 and ownership to oracle:oinstall and run
crsctl add css votedisk /dev/sdc5
Now formatting voting disk: /dev/sdc5.
CRS-4603: Successful addition of voting disk /dev/sdc5.

crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 506e58aa8c4d4f94bf25fb8b646bfd5d (/dev/sdc6) []
2. ONLINE 78f5eeb96ce44fabbf1483601d29c410 (/dev/sdc5) []

To delete vote disk using FUID
1. FUID obtained in the previous step is used here
crsctl delete css votedisk 78f5eeb96ce44fabbf1483601d29c410
CRS-4611: Successful deletion of voting disk 78f5eeb96ce44fabbf1483601d29c410.

crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 506e58aa8c4d4f94bf25fb8b646bfd5d (/dev/sdc6) []
Located 1 voting disk(s).
Same way path to voting disk could also be used for deletion
crsctl add css votedisk /dev/sdc5
Now formatting voting disk: /dev/sdc5.
CRS-4603: Successful addition of voting disk /dev/sdc5.

clusdb1]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 506e58aa8c4d4f94bf25fb8b646bfd5d (/dev/sdc6) []
2. ONLINE 17fd2191fe3c4f33bf9345272d27b58d (/dev/sdc5) []

crsctl delete css votedisk /dev/sdc5
CRS-4611: Successful deletion of voting disk /dev/sdc5.

crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 506e58aa8c4d4f94bf25fb8b646bfd5d (/dev/sdc6) []
Located 1 voting disk(s).

Useful metalink notes
OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) [ID 428681.1]

Related Posts
Migrating OCR to ASM in 11gR2 Clusterware
Moving OCR, Vote and ASM from Raw Devices to Block Devices
Migrating block devices using ASM instance to ASMLib

Migrating OCR to ASM in 11gR2 Clusterware

Before migrating the OCR to ASM there are serveral things to consider. On Oracle Clusterware Administration and Deployment Guide 11g Release 2 (11.2)E10717-03 August 2009 following is said about having an OCR in the ASM.
You can store the OCR in an Oracle ASM disk group that has
external redundancy.
If a disk fails in the disk group, or if you bring down Oracle ASM, then
you can lose the OCR because it depends on Oracle ASM for I/O.
To avoid this issue, add another OCR to a different disk group.
Alternatively, you can store OCR on a block device, or on a shared file
system using OCRCONFIG to enable OCR redundancy.
Oracle does not support storing the OCR on different storage types
simultaneously, such as storing OCR on both Oracle ASM and a shared
file system, except during a migration.
If Oracle ASM fails, then OCR is not accessible on the node on
which Oracle ASM failed, but the cluster remains operational. The
entire cluster only fails if the Oracle ASM instance on the OCR
master node fails, if the majority of the OCR locations are in
Oracle ASM, and if there is an OCR read or write access, then the
crsd stops and the node becomes inoperative.

Ensure that Oracle ASM disk groups that you create are mounted
on all of the nodes in the cluster.

But this has been re-written on Oracle Clusterware Administration and Deployment Guide 11g Release 2 (11.2) E10717-11 April 2010
If OCR is stored in an Oracle ASM disk group with external
redundancy, then Oracle recommends that you add another OCR
location to another disk group to avoid the loss of OCR, if a disk
fails in the disk group.
Oracle does not support storing the OCR on different storage types
simultaneously, such as storing OCR on both Oracle ASM and a shared
file system, except during a migration.
If an Oracle ASM instance fails on any node, then OCR becomes
unavailable on that particular node.
If the crsd process running on the node affected by the Oracle ASM
instance failure is the OCR writer, the majority of the OCR locations are
stored in Oracle ASM, and you attempt I/O on OCR during the time the
Oracle ASM instance is down on this node, then crsd stops and becomes
inoperable. Cluster management is now affected on this particular node.
Under no circumstances will the failure of one Oracle ASM instance on
one node affect the whole cluster.

Ensure that Oracle ASM disk groups that you create are mounted
on all of the nodes in the cluster.

This has not been verified as the setup used in this blog is a cluster setup upgraded to 11.2 and only has one node.
This does not require node to be down.

1. Current setup uses block devices for OCR
ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 148348
Used space (kbytes) : 4480
Available space (kbytes) : 143868
ID : 552644455
Device/File Name : /dev/sdc5
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
2. Created a ASM diskgroup here called CLUSTERDG with Normal Redundancy and explicitly mount it, to store the OCR. To store OCR/Vote disks in an ASM diskgroup the ASM Compatibility must be set to 11.2.0.0
create diskgroup clusterdg normal redundancy failgroup failgp1 disk 'ORCL:CLUS1'  
failgroup failgp2 disk 'ORCL:CLUS2' failgroup failgp3 disk 'ORCL:CLUS3'
ATTRIBUTE 'compatible.asm' = '11.2';

select name,type,compatibility from v$asm_diskgroup;

NAME TYPE COMPATIBILITY
-------------------- -------------------- --------------------
FLASH EXTERN 11.2.0.0.0
CLUSTERDG NORMAL 11.2.0.0.0
DATA EXTERN 11.2.0.0.0
From Oracle Grid Infrastructure installation Guide "For Oracle Clusterware files, Normal redundancy disk groups provide 3 voting disk files, 1 OCR and 2 copies (one primary and one secondary mirror). With normal redundancy, the cluster can survive the loss of one failure group. For most installations, Oracle recommends that you select normal redundancy."

3. Since this only has one OCR location need to use a add/delete approach. If there were multiple OCR locations then
ocrconfig -replace current_location new location
command could be used. Command is run as root
ocrconfig -add +CLUSTERDG
Verify on alerthostname.log
2010-07-07 12:50:01.288
[crsd(7409)]CRS-1007:The OCR/OCR mirror location was replaced by +CLUSTERDG.
On crsd.log
2010-07-07 12:50:00.945: [  OCRRAW][1385945408]propriowv_bootbuf: Vote information on disk 0 [/dev/sdc5] is adjusted from [0/0] to [2/2]
2010-07-07 12:50:01.165: [ OCRASM][1385945408]proprasmo: ASM cache size is [5MB]
2010-07-07 12:50:01.165: [ OCRASM][1385945408]proprasmo: ASM cache [5MB] enabled for disk group [CLUSTERDG].
2010-07-07 12:50:01.187: [ OCRRAW][1385945408]proprioo: for disk 0 (/dev/sdc5), id match (1), total id sets, (2) need recover (0), my votes (2), total votes (2), commit_lsn (19), lsn (19)
2010-07-07 12:50:01.187: [ OCRRAW][1385945408]proprioo: my id set: (2011771966, 1372893121, 0, 0, 0)
2010-07-07 12:50:01.187: [ OCRRAW][1385945408]proprioo: 1st set: (2011771966, 1028247821, 0, 0, 0)
2010-07-07 12:50:01.187: [ OCRRAW][1385945408]proprioo: 2nd set: (2011771966, 1372893121, 0, 0, 0)
2010-07-07 12:50:01.277: [ OCRRAW][1385945408]propriogid:1_2: INVALID FORMAT
2010-07-07 12:50:04.946: [ OCRRAW][1385945408]propriowv_bootbuf: Vote information on disk 1 [+CLUSTERDG] is adjusted from [0/0]
to [1/2]
2010-07-07 12:50:04.966: [ OCRRAW][1385945408]propriowv_bootbuf: Vote information on disk 0 [/dev/sdc5] is adjusted from [2/2] to [1/2]
2010-07-07 12:50:05.027: [ OCRAPI][1385945408]u_masmd:11: clscrs_register_resource2 succeeded [0]. Return [0]
2010-07-07 12:50:05.027: [ OCRAPI][1385945408]proa_modify_dep: ora.crsd resource in OHAS depends on ora.cssd, ora.ctssd and ora.asm




4. Check new OCR location with
ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 148348
Used space (kbytes) : 4500
Available space (kbytes) : 143848
ID : 552644455
Device/File Name : /dev/sdc5
Device/File integrity check succeeded
Device/File Name : +CLUSTERDG
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Oracle doesn't support having OCR in different storage types except for migration.

5. Delete the block device OCR location
ocrconfig -delete /dev/sdc5
Verify on cluster alert log
2010-07-07 12:51:49.413
[crsd(7409)]CRS-1010:The OCR mirror location /dev/sdc5 was removed.
and on crsd.log
2010-07-07 12:51:37.290: [UiServer][1549629760] S(0x2aaab0034e40): set Properties ( root,0x2105c4b0)
2010-07-07 12:51:37.301: [UiServer][1547528512] processMessage called
2010-07-07 12:51:37.301: [UiServer][1547528512] Sending message to PE. ctx= 0x20fcaa10
2010-07-07 12:51:37.302: [ CRSPE][1543326016] Processing PE command id=165. Description: [Stat Resource : 0x2aaaac35bca0]
2010-07-07 12:51:37.302: [ CRSPE][1543326016] PE Command [ Stat Resource : 0x2aaaac35bca0 ] has completed
2010-07-07 12:51:37.302: [ CRSPE][1543326016] UI Command [Stat Resource : 0x2aaaac35bca0] is replying to sender.
2010-07-07 12:51:37.302: [UiServer][1547528512] Done for ctx=0x20fcaa10
2010-07-07 12:51:49.341: [ OCRRAW][1385945408]propriowv_bootbuf: Vote information on disk 1 [] is adjusted from [1/2] to [2/2]
2010-07-07 12:51:49.383: [ OCRASM][1385945408]proprasmo: ASM cache size is [5MB]
2010-07-07 12:51:49.383: [ OCRASM][1385945408]proprasmo: ASM cache [5MB] enabled for disk group [CLUSTERDG].
2010-07-07 12:51:49.412: [ OCRRAW][1385945408]proprioo: for disk 0 (+CLUSTERDG), id match (1), total id sets, (2) need recover (0), my votes (2), total votes (2), commit_lsn (19), lsn (19)
2010-07-07 12:51:49.412: [ OCRRAW][1385945408]proprioo: my id set: (1372893121, 1028247821, 0, 0, 0)
2010-07-07 12:51:49.413: [ OCRRAW][1385945408]proprioo: 1st set: (2011771966, 1372893121, 0, 0, 0)
2010-07-07 12:51:49.413: [ OCRRAW][1385945408]proprioo: 2nd set: (1372893121, 1028247821, 0, 0, 0)
2010-07-07 12:51:49.475: [ OCRAPI][1385945408]u_masmd:11: clscrs_register_resource2 succeeded [0]. Return [0]
2010-07-07 12:51:49.475: [ OCRAPI][1385945408]proa_modify_dep: ora.crsd resource in OHAS depends on ora.cssd, ora.ctssd and ora.asm
6. Finally check the OCR with
ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 148348
Used space (kbytes) : 4500
Available space (kbytes) : 143848
ID : 552644455
Device/File Name : +CLUSTERDG
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded

Related Posts
Moving OCR, Vote and ASM from Raw Devices to Block Devices
Migrating Voting Disk to ASM in 11gR2 Clusterware
Migrating block devices using ASM instance to ASMLib

Tuesday, July 6, 2010

Upgrading 11.1.0.7 RAC to 11.2.0.1

1. runInstaller from 11.2 database software and select the upgrade existing database option

This blog is about upgrading the RAC to 11.2, where clusterware was upgraded to 11.2
2. Select the cluster nodes where RAC is upgraded

3. Give a new location for the software

4.Summary

5. Installation starts

6.End of the installation the database upgrade assistant (DBUA) is invoked by the OUI

7. Refer metalink notes 815679.1,412160.1,977512.1 for timezone upgrades

8. Preupgrade Summary

9. Upgrade Summary


Upgrading 11.1.0.7 Clusterware,ASM to 11.2.0.1

This blog gives highlights of upgrading a 11gR1 clusterware to 11gR2 grid infrastructure. The system that is used for upgrade is the one intially created as a 10gR2 cluster and then upgraded to 11gR1 where raw devices were moved to block devices and finally block device ASM was moved to ASMLib

1. Leave the existing RAC running. As of 11gR2 all upgrades are rolling upgrades

2. Unset ORACLE_BASE,ORACLE_HOME,ORACLE_SID,ORA_CRS_HOME,ORA_NLS10 and TNS_ADMIN. Remove from PATH and LD_LIBRARY_PATH variables any reference to existing system.

3. Prepare new locations for grid infrastructure home, scan IP, operating system user groups

4. runInstaller from the grid infrastructure software and select the upgrade option

5.Although Oracle says "Oracle recommends that you leave Oracle RAC instances running. When you start the root script on each node, that node's instances are shut down and then started up again by the rootupgrade.sh script" the installer complains but this could be ignored and proced to next step.

6. This test system only had a single node but if there were multiple nodes then Oracle "recommends that you select all cluster member nodes for the upgrade, and then shut down database instances on each node before you run the upgrade root script, starting the database instance up again on each node after the upgrade is complete"
Also Oracle "Oracle recommends that you upgrade Oracle ASM at the same time that you upgrade the Oracle Clusterware binaries. Until ASM is upgraded, Oracle databases that use ASM can't be created. Until ASM is upgraded, the 11g release 2 (11.2) ASM management tools in the Grid home (for example, srvctl) will not work."

7. Give the new SCAN IP

8. Oracle uses a less powerful asmsnmp user to monitor the asm upgrade. Give a password to be associated with this user.

9. New locations for the installation, 11gR2 grid upgrade is an out of place upgrade.

10. Summary


11. When prompted run the rootupgrade.sh script as root user

rootupgrade.sh output
/opt/app/11.2.0/grid/rootupgrade.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME=  /opt/app/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:
The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)
[n]:

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.sh script.
Now product-specific root actions will be performed.
2010-07-06 10:14:05: Parsing the host name
2010-07-06 10:14:05: Checking for super user privileges
2010-07-06 10:14:05: User has super user privileges
Using configuration parameter file: /opt/app/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
Cluster Synchronization Services appears healthy
Event Manager appears healthy
Cluster Ready Services appears healthy
Shutting down Oracle Cluster Ready Services (CRS):
Jul 06 10:14:21.900 | INF | daemon shutting down
Stopping resources.
This could take several minutes.
Successfully stopped Oracle Clusterware resources
Stopping Cluster Synchronization Services.
Shutting down the Cluster Synchronization Services daemon.
Shutdown request successfully issued.
Shutdown has begun. The daemons should exit soon.
LOCAL ADD MODE
Creating OCR keys for user 'root', privgrp 'root'.. 
Operation 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 daemon to inittab
CRS-4123: Oracle High Availability Services has been started.
ohasd is starting
CRS-2672: Attempting to start 'ora.mdnsd' on 'hpc1'
CRS-2676: Start of 'ora.mdnsd' on 'hpc1' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'hpc1'
CRS-2676: Start of 'ora.gipcd' on 'hpc1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'hpc1'
CRS-2676: Start of 'ora.gpnpd' on 'hpc1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'hpc1'
CRS-2676: Start of 'ora.cssdmonitor' on 'hpc1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'hpc1'
CRS-2672: Attempting to start 'ora.diskmon' on 'hpc1'
CRS-2676: Start of 'ora.diskmon' on 'hpc1' succeeded
CRS-2676: Start of 'ora.cssd' on 'hpc1' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'hpc1'
CRS-2676: Start of 'ora.ctssd' on 'hpc1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'hpc1'
CRS-2676: Start of 'ora.crsd' on 'hpc1' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'hpc1'
CRS-2676: Start of 'ora.evmd' on 'hpc1' succeeded
clscfg: EXISTING configuration version 4 detected.
clscfg: version 4 is 11 Release 1.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.

hpc1     2010/07/06 10:17:18     /opt/app/11.2.0/grid/cdata/crs_10g_cluster/backup_20100706_101718.ocr
Start upgrade invoked..
Started to upgrade the Oracle Clusterware. This operation may take a few minutes.
Started to upgrade the CSS.
The CSS was successfully upgraded.
Started to upgrade the OCR.
The OCR was successfully upgraded.
Started to upgrade the CRS.
The CRS was successfully upgraded.
Successfully upgraded the Oracle Clusterware.
Oracle Clusterware operating version was successfully set to 11.2.0.1.0

hpc1     2010/07/06 10:19:49     /opt/app/11.2.0/grid/cdata/hpc1/backup_20100706_101949.olr
Preparing packages for installation...
cvuqdisk-1.0.7-1
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
Updating inventory properties for clusterware
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 12001 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/app/oraInventory
'UpdateNodeList' was successful.                     
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 12001 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /opt/app/oraInventory
'UpdateNodeList' was successful.
12. Click on the OUI window which will then run the ASMCA to upgrade the ASM followed by the DB Control upgrade. Once these upgrades are done, upgrade of clusterware is completed.

13. crs_stat is deprecated in 11gR2 but still works. Instead should use crsctl
crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.DATA.dg    ora....up.type ONLINE    ONLINE    hpc1
ora.FLASH.dg   ora....up.type ONLINE    ONLINE    hpc1
ora....ER.lsnr ora....er.type ONLINE    ONLINE    hpc1
ora....N1.lsnr ora....er.type ONLINE    ONLINE    hpc1
ora.asm        ora.asm.type   ONLINE    ONLINE    hpc1
ora....b1.inst application    ONLINE    ONLINE    hpc1
ora.clusdb.db  application    ONLINE    ONLINE    hpc1
ora.eons       ora.eons.type  ONLINE    ONLINE    hpc1
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora.hpc1.vip   ora....t1.type ONLINE    ONLINE    hpc1
ora....network ora....rk.type ONLINE    ONLINE    hpc1
ora.oc4j       ora.oc4j.type  OFFLINE   OFFLINE
ora.ons        ora.ons.type   ONLINE    ONLINE    hpc1
ora....ry.acfs ora....fs.type ONLINE    ONLINE    hpc1
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    hpc1

crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
14. Vote disk is still in the block device, though not supported for new installation, it's still a valid location for upgrades
./crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
1. ONLINE   755a7dc1e2cfcf76bf5b79632d35b4a9 (/dev/sdc6) []
Located 1 voting disk(s).
15. OCR is also in a block device same as vote disk. Not supported as a location for new installation but valid for upgrades.
ocrcheck
Status of Oracle Cluster Registry is as follows :
Version                  :          3
Total space (kbytes)     :     148348
Used space (kbytes)      :       4308
Available space (kbytes) :     144040
ID                       :  552644455
Device/File Name         :  /dev/sdc5
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user
16. /etc/oratab is auto upgraded with the new location of the ASM home, with 11gR2 it is same as grid infrastructure home
+ASM1:/opt/app/11.2.0/grid:N
17. /etc/inittab now has the oracle high availability service daemon entry not the three clusterware entires as before
tail /etc/inittab
h1:35:respawn:/etc/init.d/init.ohasd run >/dev/null 2>&1 </dev/null
18.Finally to confrim the active,release and software versions
crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.1.0]

crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.1.0]

crsctl query crs softwareversion
Oracle Clusterware version on node [hpc1] is [11.2.0.1.0]

Friday, July 2, 2010

Migrating block devices using ASM instance to ASMLib

Metalink note 394955.1 describes how to migrate raw devices to ASMLib. Same theory is employed here to migrate block devices to ASM. This blog uses the block devices which were migrated from raw devices.

A block (or raw) device could be migrated to ASMLib and vise versa. But it is easier to migrate a device that was used in the ASMLib to use as a block (or raw) device than other way around. Reason is that when a disk is used to create a ASM instance either with ASMLib or directly using ASM itself, as in the case of block or raw devices, a disk tag or a disk marker "ORCLDISK" is written to the disk header among other things.
When a disk is used with ASMLib then in addition to a disk tag, a disk label is also added. This is missing when a raw or block devices are added to ASM with ASM itself (without the use of ASMLib). Disk label is the text used in the creatdisk command in the oracleasm.
/etc/init.d/oracleasm createdisk VOL1 /dev/sdc10
Creating Oracle ASM disk "DATA" [ OK ]
Here the text DATA is the disk label. This could be seen in v$asm_disk view in a ASM instance.
ASM instance using block devices
NAME       LABEL                PATH
---------- -------------------- ---------
DATA_0000 /dev/sdc8
FLASH_0000 /dev/sdc9
Label is missing.

ASM instance using ASMLib
NAME       LABEL                PATH
---------- -------------------- ------------
DATA01 DATA01 ORCL:DATA01
FLASH01 FLASH01 ORCL:FLASH01
Label is present.

Block or raw devices doesn't have any use for the label so ASMLib disks could be easily migrated to use raw or block devices. But ASMLib does look into the label and if it is not present it will not work.

To migrate block devices to ASMLib all that is required is to label them.

1. Set the new asm_diskstring to ORACL:* in the ASM instance
show parameter asm_diskstring
NAME TYPE VALUE
----------------- ----------- --------------------
asm_diskstring string /dev/sdc8, /dev/sdc9

alter system set asm_diskstring='ORCL:*' scope=spfile;
2.Shutdown the database instances and asm instances. If a disk is renamed while in use it could result in loss of data.

3. Install and configure ASMLib if already not done.

4. use ASMLib renamedisk to add a label to the disks. Tried to rename the disk as per above mentioned metalink note and received following warning.
/etc/init.d/oracleasm renamedisk /dev/sdc8 CLUSDATA
WARNING: Changing the label of an disk marked for ASM is a very dangerous
operation. If this is really what you mean to do, you must
ensure that all Oracle and ASM instances have ceased using
this disk. Otherwise, you may LOSE DATA.
If you really wish to change the label, rerun with the force-renamedisk command.
Renaming disk "/dev/sdc8" to "CLUSDATA": [FAILED]
which shows how serious the consequences are if rename was done while ASM is up.
Ok then try the force-renamedisk
/etc/init.d/oracleasm force-renamedisk /dev/sdc8 CLUSDATA
Renaming disk "/dev/sdc8" to "CLUSDATA": [ OK ]
/etc/init.d/oracleasm force-renamedisk /dev/sdc9 CLUSFLASH
Renaming disk "/dev/sdc9" to "CLUSFLASH": [ OK ]
This time no warning. Scan and list the disks
/etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks: [ OK ]
/etc/init.d/oracleasm listdisks
CLUSDATA
CLUSFLASH




5. Start the ASM instances. On the ASM alert log verify
cluster_database         = TRUE
instance_number = 1
asm_diskstring = "ORCL:*"
.
.
SQL> ALTER DISKGROUP ALL MOUNT
NOTE: cache registered group DATA number=1 incarn=0xe4da9dba
NOTE: cache began mount (first) of group DATA number=1 incarn=0xe4da9dba
NOTE: cache registered group FLASH number=2 incarn=0xe4da9dbb
NOTE: cache began mount (first) of group FLASH number=2 incarn=0xe4da9dbb
NOTE:Loaded lib: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
NOTE: Assigning number (1,0) to disk (ORCL:CLUSDATA)
NOTE: Assigning number (2,0) to disk (ORCL:CLUSFLASH)
NOTE: start heartbeating (grp 1)
kfdp_query(): 4
kfdp_queryBg(): 4
NOTE: cache opening disk 0 of grp 1: DATA_0000 label:CLUSDATA
NOTE: F1X0 found on disk 0 fcn 0.0
NOTE: cache mounting (first) group 1/0xE4DA9DBA (DATA)
* allocate domain 1, invalid = TRUE
NOTE: attached to recovery domain 1
NOTE: cache recovered group 1 to fcn 0.9731
NOTE: LGWR attempting to mount thread 1 for diskgroup 1
NOTE: LGWR mounted thread 1 for disk group 1
NOTE: opening chunk 1 at fcn 0.9731 ABA
NOTE: seq=17 blk=1358
NOTE: cache mounting group 1/0xE4DA9DBA (DATA) succeeded
NOTE: cache ending mount (success) of group DATA number=1 incarn=0xe4da9dba
NOTE: start heartbeating (grp 2)
kfdp_query(): 6
kfdp_queryBg(): 6
NOTE: cache opening disk 0 of grp 2: FLASH_0000 label:CLUSFLASH
NOTE: F1X0 found on disk 0 fcn 0.0
NOTE: cache mounting (first) group 2/0xE4DA9DBB (FLASH)
* allocate domain 2, invalid = TRUE
NOTE: attached to recovery domain 2
NOTE: cache recovered group 2 to fcn 0.4360
NOTE: LGWR attempting to mount thread 1 for diskgroup 2
NOTE: LGWR mounted thread 1 for disk group 2
NOTE: opening chunk 1 at fcn 0.4360 ABA
NOTE: seq=17 blk=248
NOTE: cache mounting group 2/0xE4DA9DBB (FLASH) succeeded
NOTE: cache ending mount (success) of group FLASH number=2 incarn=0xe4da9dbb
5. Label could be seen in the v$asm_disk view
NAME       LABEL      PATH
---------- ---------- --------------
DATA_0000 CLUSDATA ORCL:CLUSDATA
FLASH_0000 CLUSFLASH ORCL:CLUSFLASH

Related Posts
Migrating OCR to ASM in 11gR2 Clusterware
Migrating Voting Disk to ASM in 11gR2 Clusterware
Moving OCR, Vote and ASM from Raw Devices to Block Devices
Moving ASMLib disk to block devices (Non-ASMLib) in 11gR2 Standalone
Moving ASMLib disk to block devices (Non-ASMLib) in 11gR1 RAC
Moving ASMLib disk to block devices (Non-ASMLib) in 11gR2 RAC

Thursday, July 1, 2010

Moving OCR, Vote and ASM from Raw Devices to Block Devices

Two methods to move OCR from raw devices to block devices.
This blog uses the 10g cluster upgraded to 11g to move the raw device files (OCR,Vote, ASM files) to block devices.

Online method does not require any cluster downtime but require additional space, a new block device apart from the one already bound to the raw device.
Offline method requires a cluster downtime but does not require any additional space. It uses the existing block devices and removes the binding to the raw device.
This blog uses the Offline method. More on this and other methods on metalink note 428681.1

Moving OCR from raw device to block device with cluster downtime.

1. Shutdown all cluster services
crs_stop -all
and as root
crsctl stop crs
2. Check the status of the ocr
 ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 152364
Used space (kbytes) : 2780
Available space (kbytes) : 149584
ID : 552644455
Device/File Name : /dev/raw/raw1
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded
3. As root run
ocrconfig -repair ocr /dev/sdc5
ocrconfig -overwrite
second command creates a file in $CRS_HOME/log/hostname/client/ocrconfig_pid.log. If there are multiple files then do a ls -lrt. The above command should have exited with a success status.
Oracle Database 11g CRS Release 11.1.0.7.0 - Production Copyright 1996, 2007 Oracle. All rights reserved.
2010-07-01 11:15:11.193: [ OCRCONF][2976058240]ocrconfig starts...
2010-07-01 11:15:12.883: [ OCRCONF][2976058240]Successfully overwrote OCR configuration on disk
2010-07-01 11:15:12.883: [ OCRCONF][2976058240]Exiting [status=success]...
since this only had on ocr location that's all required to move to block device.
Check the status of the ocr
ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 152364
Used space (kbytes) : 2780
Available space (kbytes) : 149584
ID : 552644455
Device/File Name : /dev/sdc5
Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded
4. Edit the udev rules files connected with raw devices and remove any entries added to bind this block device to raw device and change the permissions of the raw device.

5. Create a new udev rules file to set the permission and ownership of the block device to 640 and root:ointall
vi /etc/udev/rules.d/99-sdc.rules
#OCR
KERNEL=="sdc5*", GROUP="oinstall", MODE="640"
start_udev
Refer Udev under what conditions should start_udev be run?
# /sbin/udevadm trigger --type=subsystems --action=add
# /sbin/udevadm trigger --type=devices --action=add
# /sbin/udevadm trigger --type=subsystems --action=change
# /sbin/udevadm trigger --type=devices --action=change
Verify if the raw device binding to the block device is gone. If not restart the server which will start the cluster and use the new ocr location.

Moving Vote disk from raw devices to block devices

1. Query the currently configured voting disks
crsctl query css votedisk
0. 0 /dev/raw/raw2
Located 1 voting disk(s).
2. For 10g the cluster must be down and for 11.1 moving to a block device is an online operation and no cluster outage is required. 10g has a bug 3972986 which could corrupt the ocr if voting disk is added while cluster is up. More on this bug and workaround on metalink note 390880.1.
If the cluster only has one voting disks then a spare block device or raw device is needed, because cluster must have at least one voting disk inplace at all times, removal of all voting disks is not allowed. If there are multiple voting disks then moving to block device from raw device could be done one voting disk at a time.
Since this cluster only has one vote disk, a new partition was created of same size which is 150MB (10g only requires 20MB and 11g requires 280MB for new installations) and permissions and ownership is set. This spare device is called /dev/sdc10. raw2 is bound to /dev/sdc6

3. Backup the vote disk
dd if=/dev/raw/raw2 of=/home/oracle/votebackup
4. Add the vote disk to the spare block device vote disk, run the command as root
crsctl add css votedisk /dev/sdc10
Now formatting voting disk: /dev/sdc10.
Successful addition of voting disk /dev/sdc10.
Monitor the output on $CRS_HOME/log/hostname/alerthostname.log
2010-07-01 12:16:16.152
[cssd(6403)]CRS-1605:CSSD voting file is online: /dev/sdc10. Details in /opt/app/crs/product/10.2.0/crs/log/hpc1/cssd/ocssd.log.
2010-07-01 12:16:16.169
[cssd(6403)]CRS-1601:CSSD Reconfiguration complete. Active nodes are hpc1 .
Query the new vote disk
crsctl query css votedisk
0. 0 /dev/raw/raw2
1. 0 /dev/sdc10
Located 2 voting disk(s).
5. Remove the raw device vote disk
crsctl delete css votedisk /dev/raw/raw2
Successful deletion of voting disk /dev/raw/raw2.
$CRS_HOME/log/hostname/cssd/ocssd.log should have something similar to
[    CSSD]2010-07-01 12:22:44.983 [1220634944] >TRACE:   clssgmVotediskRemove: requested remove for votedisk 0 /dev/raw/raw2
[ CSSD]2010-07-01 12:22:45.801 [1189165376] >TRACE: clssnmDoSyncUpdate: Wait for 0 vote ack(s)
Query the vote disk is removed from the cluster
crsctl query css votedisk
1. 0 /dev/sdc10
Located 1 voting disk(s).
6. Add the block device that was used for the raw device as a vote disk, change the ownership and permissiosn on the block device before running the below command.
crsctl add css votedisk /dev/sdc6
Now formatting voting disk: /dev/sdc6.
Successful addition of voting disk /dev/sdc6.
From alert log
[cssd(6403)]CRS-1605:CSSD voting file is online: /dev/sdc6. Details in /opt/app/crs/product/10.2.0/crs/log/hpc1/cssd/ocssd.log.
2010-07-01 12:26:50.504
[cssd(6403)]CRS-1601:CSSD Reconfiguration complete. Active nodes are hpc1 .
From ocssd.log
[    CSSD]2010-07-01 12:26:50.489 [1220634944] >TRACE:   clssgmVotediskAdd: requested add for votedisk 0 /dev/sdc6
[ CSSD]2010-07-01 12:26:50.502 [1189165376] >TRACE: clssnmDoSyncUpdate: Wait for 0 vote ack(s)
Query the vote disks
crsctl query css votedisk
0. 0 /dev/sdc6
1. 0 /dev/sdc10
Located 2 voting disk(s).
7. Remove raw device rules from udev rules files and add a new rule for the vote disk in the sdc rule file created in ocr section.
#vote disk
KERNEL=="sdc6", OWNER="oracle", GROUP="oinstall", MODE="660"
8. Remove the spare vote disk added. If following error
 crsctl delete css votedisk /dev/sdc10
Failure 8 with Cluster Synchronization Services while deleting voting disk.
is thrown then restart the cluster and retry
crsctl delete css votedisk /dev/sdc10
Successful deletion of voting disk /dev/sdc10.
9. All done vote disk is moved to use the block device
crsctl query css votedisk
0. 0 /dev/sdc6
Located 1 voting disk(s).




Moving ASM Spfile to block device

If required the ASM spfile which was created in a shared storage raw device could be moved to a block device. Otherwise a init+ASM*.ora file must be created on each $ASM_HOME/dbs/ in the cluster.

1. Before moving create a pfile from the ASM spfile.
create pfile='/home/oracle/asmpfile.ora' from spfile; 
Metalink note 973031.1 describes how to move ASM spfile to shared device. That theory is used here to move from raw dervices to block devices

2. Shutdown database instances and ASM instances

3. Remove ASM spfile entries from the udev rules files, and add a new rule on the udev block device rule file.
# asm spfile
KERNEL=="sdc7", OWNER="oracle", GROUP="oinstall", MODE="660"
4. Unbind the raw device from the block device which will be used to store the ASM spfile. (even if this step is skipped change will still work and raw devices won't be used for ASM spfile)

5. Startup the ASM instance with the pfile created earlier to nomount state.
startup nomount pfile='/home/oracle/asmpfile.ora';
and create the ASM spfile in the new block device location
SQL> create spfile='/dev/sdc7' from pfile='/home/oracle/asmpfile.ora';
6. Edit the $ORACLE_HOME/dbs/init+ASM*.ora file to contain the new spfile location replace
SPFILE='/dev/raw/raw3'
to
SPFILE='/dev/sdc7'
and restart the asm instance. Verify on the asm alert log that new spfile is used
Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in server-side pfile /opt/app/oracle/product/11.1.0/clusdb/dbs/init+ASM1.ora
System parameters with non-default values:
large_pool_size = 12M
spfile = "/dev/sdc7"
instance_type = "asm"
cluster_database = TRUE
instance_number = 1
asm_diskgroups = "DATA"
asm_diskgroups = "FLASH"
diagnostic_dest = "/opt/app/oracle"
7. Shutdown the ASM instance and update the OCR ASM registry with the location of the new ASM spfile.
Usage: srvctl modify asm -n -i [-o ] [-p ]
-n Node name
-i ASM instance name
-o ORACLE_HOME path
-p Server parameter file path
srvctl modify asm -n hpc1 -i +ASM1 -o $ASM_HOME -p /dev/sdc7
Verify OCR reconfiguration on $CRS_HOME/log/hostname/alert*.log
[crsd(7207)]CRS-1005:The OCR upgrade was completed. Version has changed from 185599744 to 185599744. Details in /opt/app/crs/product/10.2.0/crs/log/hpc1/crsd/crsd.log.
2010-07-01 12:38:49.111
[crsd(7207)]CRS-1012:The OCR service started on node hpc1.
2010-07-01 12:38:49.812
[evmd(7208)]CRS-1401:EVMD started on node hpc1.
2010-07-01 12:38:50.776
[crsd(7207)]CRS-1201:CRSD started on node hpc1.
2010-07-01 12:40:05.470
[cssd(8011)]CRS-1601:CSSD Reconfiguration complete. Active nodes are hpc1 .
8. Start the ASM instances and the database instances

Moving ASM diskgroups from raw devices to block devices


1. View the currently used raw devices
select mount_status,state,name,path from v$asm_disk;
MOUNT_S STATE NAME PATH
------- -------- --------------- -------------
CACHED NORMAL DATA_0000 /dev/raw/raw4
CACHED NORMAL FLASH_0000 /dev/raw/raw5
2. Change the asm_diskstring in spfile to discover the block devices instead of the raw devices
alter system set asm_diskstring='/dev/sdc8','/dev/sdc9' scope=spfile;
3. Shutdown the database instances, ASM instances

4. Remove udev rules for asm disk and add new udev rules for block devices
KERNEL=="sdc[8-9]*", OWNER="oracle", GROUP="oinstall", MODE="660"
5. Restart the udev services and then the asm instnaces. On the ASM alert log verify the block devices are used
NOTE: Assigning number (1,0) to disk (/dev/sdc8)
NOTE: Assigning number (2,0) to disk (/dev/sdc9)
5. New paths on the asm disk view.
MOUNT_ST STATE   NAME            PATH
-------- ------- --------------- ---------
CACHED NORMAL DATA_0000 /dev/sdc8
CACHED NORMAL FLASH_0000 /dev/sdc9

Related Posts
Migrating OCR to ASM in 11gR2 Clusterware
Migrating Voting Disk to ASM in 11gR2 Clusterware
Migrating block devices using ASM instance to ASMLib