Monday, March 23, 2009

Modify NLS_LENGTH_SEMANTICS online

From Peopletools 8.48, when working on a UNICODE Oracle database, it is recommanded, do not say mandatory, to set the NLS_LENGTH_SEMANTICS to CHAR to be able to enter multi-bytes characters.

By default, NLS_LENGTH_SEMANTICS is set to BYTE.
This is a problem if you didn't modify this parameter when started the database, you cannot change it afterwards online without bouncing the database.
If you can change the value for your own session (alter session) and everything works fine and you can play with the new value, if you do it on system level (alter system), the new value is not taken in account (however, the alter system command does not return any error !):
SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';

NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics BYTE TRUE


SQL> alter system set nls_length_semantics=char;

System altered.

SQL> create table sysadm.nicolas(column1 varchar2(10));

Table created.

SQL> select column_name,data_type,data_length,char_length,char_used

from dba_tab_columns where table_name='NICOLAS';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 10 10 B

As you can see, the character used is BYTE, not CHAR as it is expected after parameter value change.

The only workaround to this, is to change the spfile and bounce the database :
SQL> alter system set nls_length_semantics=char scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2074664 bytes
Variable Size 746588120 bytes
Database Buffers 318767104 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> drop table sysadm.nicolas;

Table dropped.

SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';

NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics CHAR FALSE


SQL> create table sysadm.nicolas(column1 varchar2(10));

Table created.

SQL> select column_name,data_type,data_length,char_length,char_used from dba_tab_columns where table_name='NICOLAS';

COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 30 10 C

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi


Now it is becoming CHAR as expected from the beginning.

I found it disapointed.

Addendum : it is an known bug #1488174 (internal bug, non-public), opened several years ago on database version 8.x, not solved yet. Find out more in the metalink note #144808.1.

Friday, March 20, 2009

Create index with compute statistics

When you are (re)building an index on Peoplesoft with in Application designer, Peoplesoft is writing a sql script to (re)create the index on the database level.
The statement of index creation contains "compute statistics" option.
If in most of cases there is no issue in that, sometimes you could have some strange behaviour behind this simple statement.

Some times ago, on one of our 9.2 database, we had a query which was not fast as expected. Just to try, we decided to create a new index. It was still not fast enough, then drop this new index.
Strangely, this index creation makes Oracle optimizer change his behaviour comparing before the index (creation and drop) and start to use an other explain plan.
How comes ? We created and drop a new index (through Application Designer) and Oracle "decided" to change the execution plan, hmmm, strange.
After looking deeper, it appears the table statistics had been updated by the index creation containing "compute statistics".

Example :
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------


SQL> create index idx on emp(ename) compute statistics;

Index created.

SQL> drop index idx;

Index dropped.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------
14 20/03/09


But, in some cases, I wouldn't want the table statistics to be updated by my index creation. That's bad, that does not happen without this option "compute statistics" :
SQL> create index idx on emp(ename);

Index created.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------

Finally, found an event to work around and avoid that table statistics update :

SQL> alter session set events '8130 trace name context forever';

Session altered.

SQL> create index idx on emp(ename) compute statistics;

Index created.

SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';

NUM_ROWS LAST_ANA
---------- --------

Ah, now that's better, now if I drop this index, my explain plan won't change as before the index.

This case has been tested on 9.2.0.6 and 9.2.0.8. Seems not to be a problem anymore in the higher version.

Enjoy,

Sunday, March 15, 2009

Peoplesoft 8.49 on OEL 64-bits

As I wrote few days ago, Peoplesoft on Linux 64-bits is now supported on OEL5.2 min (and also RHEL) from Peopletools 8.49.14 and higher.
The Peoplesoft binaries are the same for 64-bits and 32-bits OS.
So, you could either install the same Peoplesoft (application binaries and Peopletools binaries) you already have for your 32-bits Linux, or much more simple, just copy the entire PS_HOME over the network.
There is no issue about that.

The slight difference is for the Oracle libraries.
Do not forget, Peoplesoft is still working with in the 32-bits Oracle libraries, so you have to create a link to the 32-bits librairies inside the 64-bits folder.

If on a 32-bits OS you have to create the following link :
[oracle@orion2:/apps/oracle/product/11.1.0/lib]$ ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 libclntsh.so.9.0

On a 64-bits Oracle binaries, it must be the following :
[oracle@orion2:/apps/oracle/product/11.1.0/lib]$ ln -s $ORACLE_HOME/lib32/libclntsh.so.11.1 libclntsh.so.9.0

Apart from that, everything is same and works fine.

This migration could be a good step before managing the coming Peopletools 8.50 upgrade, because it has been told the Peopletools 8.50 will be a real 64-bits application, moreover it seems it will be supported only on Linux 64-bits, the certification on a 32-bits Linux system will be retired.
Read pages 34 and 43 of PeopleTools Roadmap - OOW 2008

Enjoy,

A database move from 32-bits to 64-bits

Today, a move of a database from OEL5.3 32-bits to OEL5.3 64-bits. The database is running on Oracle 11.1.0.7 32-bits on source.

Firstly, be sure your target OS is a 64-bits OS :
[oracle@orion2:/apps/oracle/product/11.1.0/bin]$ uname -m
x86_64


And also an Oracle software 64-bits :
[oracle@orion2:/apps/oracle/product/11.1.0/bin]$ file oracle
oracle: setuid setgid ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped


Shut the source database down, and copy to the new pletform all the files (including spfile or pfile).
Then on the 64-bits OS, start the database in upgrade mode and run utlirp.sql script, that'll invalidate all the objects :
[oracle@orion2:/apps/oracle/admin/DMOCRM9/pfile]$ export ORACLE_SID=DMOCRM9
[oracle@orion2:/apps/oracle/admin/DMOCRM9/pfile]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 15 11:16:14 2009

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

Connected to an idle instance.

SQL> startup upgrade pfile='/apps/oracle/admin/DMOCRM9/pfile/initDMOCRM9.ora'
ORACLE instance started.

Total System Global Area 764121088 bytes
Fixed Size 2163600 bytes
Variable Size 197135472 bytes
Database Buffers 557842432 bytes
Redo Buffers 6979584 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
[...]


Finally, restart your database in normal mode, and run utlrp.sql script to recompile all the invalidated objects :
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/apps/oracle/admin/DMOCRM9/pfile/initDMOCRM9.ora'
ORACLE instance started.

Total System Global Area 764121088 bytes
Fixed Size 2163600 bytes
Variable Size 197135472 bytes
Database Buffers 557842432 bytes
Redo Buffers 6979584 bytes
Database mounted.
Database opened.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> Rem
[...]


Check the status of the objects onto the database :
SQL> select * from dba_registry
SQL> /

CATALOG
Oracle Database Catalog Views
11.1.0.7.0 VALID 15-MAR-2009 11:47:55
SERVER SYS
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATALOG



CATPROC
Oracle Database Packages and Types
11.1.0.7.0 VALID 15-MAR-2009 11:47:55
SERVER SYS

SYS
DBMS_REGISTRY_SYS.VALIDATE_CATPROC

DBSNMP,OUTLN,SYSTEM,TSMSYS


SQL>
SQL> select count(*) from dba_objects where status != 'VALID';

COUNT(*)
----------
0


Lastly, you can be happy with your 64-bits database :
[oracle@orion2:/apps/oracle/admin/DMOCRM9/pfile]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sun Mar 15 12:01:13 2009

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


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

SQL>
SQL> select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> desc v$session
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)
[...]

Thursday, March 12, 2009

Peoplesoft on Linux 64-bits

Good news for the Linux 64-bits customers. Peoplesoft announced the 3rd of March'09 they are now certified on Linux 64-bits (OEL/RHEL), please see the metalink3 note :
Oracle PeopleSoft extends certification of OEL and RHEL 5 x86-64

The requirements for 64-bits appliance are OEL5.2 or RHEL5.2 minimum, and Peopletools 8.49.14 min.

Now, need to be tested on my lab...

Sunday, March 08, 2009

OTN forums : 28 weeks later

Following the previous post I wrote last September (28 days later), today 28 weeks later, in reference to an other horror movie, is a logical way to go right now.
So, 28 weeks later, are there any improvements in OTN Forums ?
No, not really. Even worse. As a "birthday" present, today a new issue appears (again) with the email address...

Less and less valuable people are particpating, people does not learn how to give points or "close" thread by marking thread as answered when it is not, forums slowness and often up-down effects, and of course, still these bloody markups we cannot avoid anymore, and still no getting back the red bullets to mark threads as (un)read.

It seems the upgrade of last summer was a downgrade, nothing more. And despite all the complaints of OTN members, nothing has been done to really improve the OTN forums.

Finally, why continue to use it ?