root.sh fails with libocci.so does not exist

Platform HP Itanium. Oracle 11g (11.1.0.6 installation).

Because an ANSI C compiler isn’t installed on all servers by default the genoccish script causes root.sh to fail. The problem only appears when running root.sh after an 11.1.0.6 or 11.1.0.7 installation

The error message is

chmod: can’t access libocci.so

chmod: can’t change libocci.so: No such file or directory

Finished product-specific root actions.

Instructions that correct the issue are

vi $ORACLE_HOME/bin/genoccish and replace

#LD=“${CCCDIR}/opt/aCC/bin/aCC -Wl,+h,${OCCI_LIB} -b ${LD_BITNESS} -o ${LIB_DIR}/${OCCI_LIB} -  ,+forceload ${LIB_DIR}/${OCCI_ARC}”

with

LD=“ld +h ${OCCI_LIB} -b -o ${LIB_DIR}/${OCCI_LIB} +forceload ${LIB_DIR}/${OCCI_ARC}”

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

relink all

run root.sh as user root.

The Oracle reference is  bug 7651884 for 11gR1

 

There are a few options to resolve the issue

 

1)       Ignore

2)       Use a workaround by editing genoccish script and then relinking after the installation is complete. This only needs to be done once and not after every patch or change (unless genoccish is overwritten which is unlikely)

3)       If libocci is require then there are two solutions available from HP that are fully supported by Oracle development :-

1. Install the evaluation copy of the C++ compiler product. This trial version has all the functionality needed to relink applications, even after the evaluation period expires.
2. Use the aCC-Link product. Beginning with the 2008 releases of HP-UX 11i (v1, v2, and v3), there
is bundle installed with the core operating system environments called
HP-ACC-Link. This bundle contains the binary /usr/ccs/bin/aCC_link, which
properly re-links any C++ objects on any HP-UX platform.

 

I personally am in favour of 3(2) as I do not think it is best practise to ignore error messages (which could be hiding something else), we do not want any type of compiler on production servers and the edit genoccish option is a bit messy and time consuming, although it does work.

I

CTAS when using a virtual column in 11g

This blog is about the new 11g functionality virtual column and highlights a gotcha when a CTAS is used against such a table.

drop table vctest;

create table vctest (team varchar(12),played number,points number,

avg_points number (2,1) GENERATED ALWAYS as (points/played ));

insert into vctest (team,played,points) values (’Chelsea’,21, 42);

insert into vctest (team,played,points) values (’Liverpool’,21, 46);

insert into vctest (team,played,points) values (’Man Utd’,20,44 );

insert into vctest (team,played,points) values (’Arsenal’,21,38);

insert into vctest (team,played,points) values (’Aston Villa’,21,41);

select * from vctest order by 2 desc;

TEAM PLAYED POINTS AVG_POINTS
Chelsea 21 42 2
Liverpool 21 46 2.2
Aston Villa 21 41 2
Arsenal 21 38 1.8
Man Utd 20 44 2.2

create table vc_copy as select * from vctest;

select * from vc_copy order by 3 desc;

TEAM PLAYED POINTS AVG_POINTS
Chelsea 21 42 2
Liverpool 21 46 2.2
Aston Villa 21 41 2
Arsenal 21 38 1.8
Man Utd 20 44 2.2

The tables look identical but we test by adding the same row to both tables

Insert into vctest (team,played,points) values (’Everton’,21, 35);

Insert into vc_copy (team,played,points) values (’Everton’,21, 35);

commit;

vtest TEAM PLAYED POINTS AVG_POINTS
Everton 21 35 1.7

vc_copy TEAM PLAYED POINTS AVG_POINTS
Everton 21  35

Notice that the avg_points column is not completed for Everton as the table has been copied with the data values and not the statement that generates the values.

One workaround would be to copy table as before, drop the avg_points column and then create it as a virtual column again

If it is a large table then it is best to mark the column as unused and drop later

alter table vc_copy set unused (avg_points);

desc vc_copy;

Name Null Type
TEAM  VARCHAR2(12)
PLAYED NUMBER 
POINTS NUMBER 
3 rows selected

alter table vc_copy add avg_points number (2,1) GENERATED ALWAYS as (points/played );

desc vc_copy;

Name Null Type
TEAM  VARCHAR2(12)
PLAYED NUMBER 
POINTS NUMBER 
AVG_POINTS NUMBER(2,1) 
4 rows selected

select * from vc_copy order by 3 desc;

TEAM PLAYED POINTS AVG_POINTS
Liverpool 21 46 2.2
Man Utd 20 44 2.2
Chelsea 21 42 2
Aston Villa 21 41 2
Arsenal 21 38 1.8
Everton 21 35 1.7
6 rows selected

Finally, another option to create the table is to use dbms_metadata to grab the DDL to create the copy table as a select *. Edit the DDL to remove and then create the table, later adding the avg_points column as a virtual column

select DBMS_METADATA.GET_DDL(’TABLE’,’VCTEST’) from dual;

How not to set up remote log shipping – Dataguard and Streams

I was trying to set up a Streams archived_log downstream capture between two 11.1.0.6 databases and I was having problems in getting the archive log files to transport over to the target database where they will be read and applied.

First steps was to change log_archive_dest to use log_archive_dest_1 and log_archive_dest_2

log_archive_dest_1 = ‘location=/u00/oradata/dbs1/archive’

log_archive_dest_2 = ’service=dbs2 noregister template=/export3/u01/oradata/dbs1/archive/%t_%s_%r.dbf’

I ensured that the passwd file is set up on both sides and the sys password is the same and that remote_login_passwordfile is set to EXCLUSIVE.

In V$archive_dest I see an error of ORA-16191

Primary log shipping client not logged on standby

Cause

An attempt to ship redo to standby without logging on to standby or with invalid user credentials.

Action

Check that primary and standby are using password files and that both primary and standby have the same SYS password. Restart primary and/or standby after ensuring that password file is accessible and REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to SHARED or EXCLUSIVE.

I don’t think I need to make any changes on the target database to actually get the files across as I am not trying to read them as yet.

The service name works and I can connect remotely and I have re-created the password file as well

April Sims offered the following advice

I have encountered this in a DATA GUARD environment as well, see fix below. This also fixed problems with Grid Control in 11g.

Data Collected

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

ORA-16191: Primary log shipping client not logged on standby

Support Update

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

The issue seems to be because of password mismatch between primary and standby.

The password file has to have identical authentication info for both primary and standby

If you change or create the password file using orapwd for SYS,it will not work in 11G.

Action Plan

===========

Turn off the case sensitivity option by changing init.ora parameter SEC_CASE_SENSITIVE_LOGON=FALSE.

Create password files on both servers using the same password and pass ignorecase=Y to orapwd utility.

Make sure you use identical options during password file creation for both primary and standby

However I was still struggling with a ORA-1031 permissions problem as well as the ORA-16191 error.

I finally resolved the issue by taking the following actions

I amended my source database spfile to have the following entries

LOG_ARCHIVE_DEST_2=’SERVICE=DBS2.NET ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

TEMPLATE=/usr/oracle/log_for_dbs1/dbs1_arch_%t_%s_%r.log

DB_UNIQUE_NAME=dbs2′ – this line was new

LOG_ARCHIVE_CONFIG=’DG_CONFIG=(dbs1,dbs2)’ — new entry, using the DB_UNIQUE_NAME of both databases

The target database had an spfile with the following lines

*.LOG_ARCHIVE_DEST_2= ‘LOCATION=/export3/u01/oradata/dbs1/archive MANDATORY VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE) ‘

LOG_ARCHIVE_CONFIG=’DG_CONFIG=(dbs1,dbs2)’ — new entry, using the DB_UNIQUE_NAME of both databases

I still got the ORA-1031 error so I rebuilt the remote password file again and bounced the databases and everything sprung into life.

I think the key thing to do is to ensure that you can connect as sysdba from both databases to each other. If not then create/recreate the password file, bounce the databases and then try again. Once that is working it is time to start changing the spfile options to enable the second archive log destination.

Note that REMOTE_ARCHIVE_ENABLE parameter in 10g has been deprecated in 11g and replaced by LOG_ARCHIVE_CONFIG

11g – library cache mutex X – known bug

On a high volume performance rig (11.0.6.1 2 node RAC, OEL5 Linux) we are seeing the main wait event as ‘library cache: mutex x”. This is an event that is new to 11g and there is not much documentation around it.

An AWR report for a 4 minute snapshot shows the evidence of the problems being caused

Top 5 Timed Foreground Events

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
library cache: mutex X	19,674,620	19,177	1	70.15	Concurrency
log file sync	564,966	5,334	9	19.51	Commit
DB CPU		1,722		6.30
latch: ges resource hash list	78,225	552	7	2.02	Other
latch: row cache objects	44,421	213	5	0.78	Concurrency

There is one hit on Metalink which leads to bug 5928271 and this has been seen by one customer who also has a high throughput database with very high levels of CPU usage. For information our CPU and memory usage is shown below. PS We do not need 128Gb of memory but that is what the servers came with (evaluation purposes).

Host CPU (CPUs: 8 Cores: 8 Sockets: 4)

Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
54.30	104.45	90.9	7.2	0.0	0.3

Instance CPU

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
98.5	98.8	0.0

Memory Statistics

	Begin	End
Host Mem (MB):	128,987.8	128,987.8
SGA use (MB):	2,400.0	2,400.0
PGA use (MB):	1,174.2	1,231.1
% Host Mem used for SGA+PGA:	2.77	2.77

Several changes were recommended to try, one was to increase shared pool size (which was definitely not our problem) and the other  was to try an undocumented parameter _session_cached_instantiations with of value of 100. This did not show any differences on our system and we are now awaiting Oracle to fix the bug (or at least provide more information on what it actually means and how we can address the problems).

 

I will update this blog when I know more

More on Swingbench and benchmarks

There was an interesting post on the Oracle-L newsgroup in response to my recent blog on Swingbench benchmarks. Details of a 32bit v 64 bit benchmark were posted at http://erudans.info/2008/03/06/oracle-32bin-vs-64-bit-quick-test/

I thought it would be useful to run the same benchmark against our test rig. 11g 2 node RAC cluster but only using one instance on V585 servers , 8K block size 2G sga_target 3.1G sga_max_size

Below is an image of the original post and our post. I hope it proves useful for anyone contemplating further testing using Swingbench