ASM Extents

Every ASM disk is divided into allocation units (au). ASM files are stored as extents and an extent consists of one or more allocation unit, though it was only 11g that brought in variable sized extents. The ASM instance provides the RDBMS instance with an extent map that the RDBMS instance then uses when doing I/O.

The diagram above is meant to show the extents of a pair of ASM files distributed amongst the available drives in a disk group. Essentially this is the algorithm that ASM uses to maximise the I/O performance - spread all data across the disks in a disk group.

When you create a disk group in 11g you can specify the size of the allocation unit to be from 1MB to 64MB, the size doubling between these limits. That is you can set the size of the au for a disk group to be one of 1, 2, 4, 8, 16, 32, or 64MB.

Clearly the larger the au size chosen the less the number of extents it will take to map a file of a given size. The larger au are clearly beneficial for large data files and cuts down on SGA required to track. Each individual extent resides on a single disk.

Extents can vary in size from 1 au to 8 au to 64 au. The number of au a given extent will use is dependent on the number of extents allocated and the extent size increases at a threshold of 20,000 extents to 8 and then again at 40,000 extents to 64. Again this is designed to be beneficial to larger data files, requiring less extents to be tracked.

You can see how the extents are allocated between disks in a disk group by looking at the X$KFFXP view:


SQL> select count(*), group_kffxp, disk_kffxp
from X$KFFXP
group by group_kffxp, disk_kffxp
order by group_kffxp;


This will show you how many au have been allocated to each disk, if you have a healthy balanced system each disk in a disk group should have a similar number of au.

A very useful script for looking at all this is available on metalink, look for Note: 351117.1, diagnosing ASM space issues, well worth having a look at.

Legacy Applications and Oracle 11g

I’ve been testing various custom built applications running against an 11g database. I’ve been testing quite a variety of methods that can be used for database connectivity: JDBC, perl-dbi, pro C* being quite key in the partiuclar environment. There is also a legacy application that is using Oracle Forms/Reports. Version 6. Now, this is currently running against an Oracle 10.2.0.3 database and has done so quite happilly for some time now. However, if you followed and believed Metalink note 27303.1 then you would never have even tried attempting to connect Forms/Reports to a 10g database as this runs version 8.0.5 of the Oracle client and according to the matrix on that note the latest version of the database that an 8.0.5 client should connect to is 8.1.7.

I’ve seen 8.0.5 running against, 9.2, 10.1 and 10.2 all very happily. However something has changed with 11g, and I cannot connect an 8.0.5 client to an 11g instance. When you attempt a connection via sqlplus you recieve the following error:

ORA-00248: invalid option for ALTER SESSION

So I traced the client connection and I traced the listener and I traced the database and eventually saw the alter session that was causing the problem:

(b097) nspsend: 40 61 6C 74 65 72 20 73  |@alter s|
(b097) nspsend: 65 73 73 69 6F 6E 20 73  |ession s|
(b097) nspsend: 65 74 20 6E 6C 73 5F 6C  |et nls_l|
(b097) nspsend: 61 6E 67 75 61 67 65 3D  |anguage=|
(b097) nspsend: 20 27 45 4E 47 4C 49 53  | 'ENGLIS|
(b097) nspsend: 48 27 20 6E 6C 73 5F 74  |H' nls_t|
(b097) nspsend: 65 72 72 69 74 6F 72 79  |erritory|
(b097) nspsend: 3D 20 27 55 4E 49 54 45  |= 'UNITE|
(b097) nspsend: 44 40 20 4B 49 4E 47 44  |D@ KINGD|
(b097) nspsend: 4F 4D 27 20 6E 6C 73 5F  |OM' nls_|
(b097) nspsend: 63 75 72 72 65 6E 63 79  |currency|
(b097) nspsend: 3D 20 27 C2 A3 27 20 6E  |= '..' n|
(b097) nspsend: 6C 73 5F 69 73 6F 5F 63  |ls_iso_c|
(b097) nspsend: 75 72 72 65 6E 63 79 3D  |urrency=|
(b097) nspsend: 20 27 55 4E 49 54 45 44  | 'UNITED|
(b097) nspsend: 20 4B 49 4E 47 44 4F 4D  | KINGDOM|
(b097) nspsend: 27 20 40 6E 6C 73 5F 6E  |' @nls_n|
(b097) nspsend: 75 6D 65 72 69 63 5F 63  |umeric_c|
(b097) nspsend: 68 61 72 61 63 74 65 72  |haracter|
(b097) nspsend: 73 3D 20 27 2E 2C 27 20  |s= '.,' |
(b097) nspsend: 6E 6C 73 5F 63 61 6C 65  |nls_cale|
(b097) nspsend: 6E 64 61 72 3D 20 27 47  |ndar= 'G|
(b097) nspsend: 52 45 47 4F 52 49 41 4E  |REGORIAN|
(b097) nspsend: 27 20 6E 6C 73 5F 64 61  |' nls_da|
(b097) nspsend: 74 65 5F 40 66 6F 72 6D  |te_@form|
(b097) nspsend: 61 74 3D 20 27 44 44 2D  |at= 'DD-|
(b097) nspsend: 4D 6F 6E 2D 59 59 27 20  |Mon-YY' |
(b097) nspsend: 6E 6C 73 5F 64 61 74 65  |nls_date|
(b097) nspsend: 5F 6C 61 6E 67 75 61 67  |_languag|
(b097) nspsend: 65 3D 20 27 45 4E 47 4C  |e= 'ENGL|
(b097) nspsend: 49 53 48 27 20 20 6E 6C  |ISH'  nl|
(b097) nspsend: 73 5F 73 6F 72 74 3D 20  |s_sort= |
(b097) nspsend: 27 42 49 4E 05 41 52 59  |'BIN.ARY|
(b097) nspsend: 27 00 00 00 00 00 00 00  |'.......|
(b097) nspsend: normal exit

But now the bit that really fails:

(b097) nsprecv: reading from transport...
(b097) nttrd: entry
(b097) nttrd: socket 208 had bytes read=11
(b097) nttrd: exit
(b097) nsprecv: 11 bytes from transport
(b097) nsprecv: tlen=11, plen=11, type=12
(b097) nsprecv: packet dump
(b097) nsprecv: 00 0B 00 00 0C 00 00 00  |........|
(b097) nsprecv: 01 00 01 00 00 00 00 00  |........|

This sequence of bytes being recieved is repeated 3 times. I compared this with the trace of a connection to 10.2.0.3, the alter session is the same in both cases, but this connection actually receives some data:

(b07f) nsprecv: reading from transport...
(b07f) nttrd: entry
(b07f) nttrd: socket 208 had bytes read=410
(b07f) nttrd: exit
(b07f) nsprecv: 410 bytes from transport
(b07f) nsprecv: tlen=410, plen=410, type=6
(b07f) nsprecv: packet dump
(b07f) nsprecv: 01 9A 00 00 06 00 00 00  |........|
(b07f) nsprecv: 00 00 08 0A 00 13 00 00  |........|
(b07f) nsprecv: 00 13 41 55 54 48 5F 56  |..AUTH_V|
(b07f) nsprecv: 45 52 53 49 4F 4E 5F 53  |ERSION_S|
(b07f) nsprecv: 54 52 49 4E 47 12 00 00  |TRING...|
(b07f) nsprecv: 00 12 2D 20 36 34 62 69  |..- 64bi|
(b07f) nsprecv: 74 20 50 72 6F 64 75 63  |t Produc|

Oracle support will not touch this issue as an 8.0.5 has long ago fallen out of support. I have attempted playing around with the NLS settings but to no avail I cannot seem to make it work. This will almost certainly prevent the RDBMS being upgraded to 11g for at least a year, one possibility being mooted is to create an empty 10gR2 database and have this legacy application connect in to it and then use database links to the 11g database to actually retrieve the data. It does not exactly sound optimal to me.

I guess the moral of the story is not to let your clients lag so far behind your database instance!

APEX ate my 11g upgrade

I have been working on upgrading a 10.2.0.3 database to 11g for a few weeks now. It is RAC so requires some groundwork before doing the actual RDBMS upgrade, but I’m finding an interesting issue when running the catupgrd.sql script against my database.

The system I’m upgrading has Oracle Application Express installed:

sql> select comp_id, comp_name, version, status
from dba_registry
where comp_id='APEX';

COMP_ID       COMP_NAME                   VERSION     STATUS

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

APEX          Oracle Application Express  2.2.1.00.04 VALID

When you upgrade to 11g APEX is installed by default, but if you already have a version of it, it will attempt to upgrade this version to 3.0.1. When I run catupgrd.sql script everything seems to be happy and going along fine until:

Thank you for installing Oracle Application Express.

Oracle Application Express is installed in the FLOWS_030000 schema.

.

.

– Now beginning upgrade. This will take several minutes.——-

– Ensuring template names are unique ——-

– Migrating metadata to new schema ——-

ERROR:

ORA-03114: not connected to ORACLE

ERROR:

ORA-03114: not connected to ORACLE

.

.

At this point the catupgrd.sql script has bombed out and nothing further in the upgrade gets successfully run against the database. Digging a little deeper we can see an ORA-07745 has been thrown:

ORA-07445: exception encountered: core dump [pfrxca()+65] [SIGSEGV] [ADDR:0x10] [PC:0x719A6F1] [Address not mapped to object] []

I’m not too sure how relevant this is, but there is a bug visible in metalink related to  pfrxca: 4176171:

A dump can occur in pfrxca if a PLSQL object gets invalidated while it is being loaded into cache.

Apex is left in the following state:

sql> select comp_id, comp_name, version, status
from dba_registry
where comp_id='APEX';

COMP_ID       COMP_NAME                   VERSION     STATUS

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

APEX          Oracle Application Express  2.2.1.00.04 UPGRADING

I have been able to reproduce this every time with clones of the database I want to upgrade. Oracle actually advised trying to upgrade APEX first by downloading it independently and then subsequently perform the database upgrade. I was quite surprised when  the standalone upgrade to APEX 3.0.1 went and worked flawlessly. During the database upgrade process the same coreins.sql script is called as during the standalone APEX upgrade!

Of course performing the database upgrade with an already upgraded APEX also then ran smoothly without incident. I feel I should point out that in the SR I raised the support analyst did claim that he was able to upgrade to 11g without encountering this issue, whether he was testing with the exact version of APEX that I originally had, and with RAC was not apparent. I know I can reproduce this problem at will.

It  would seem to me that in my system (I have reproduced this on various different servers, but all running 10.2.0.3 on RHEL 4 x86-64) something is becoming invalid during the upgrade and that APEX is not liking it. Yet another example of the fact that you cannot test enough, and to never, ever take anything for granted. Every Oracle system is unique!

Upgrading to Oracle 11g Clusterware

I have just done a couple of 10g to 11g Oracle Clusterware upgrades on a pair of 2 node RAC clusters. These are now happily running 11g Clusterware with 10g ASM and database instances.

First off, I have found the documentation a little bit on the sparse side in terms on how to actually do a clusterware upgrade. It took a little while for me to realise that it is very possible to perform a rolling upgrade when upgrading your clusterware, know I knew this was possible when patching from 10.2.0.X to 10.2.0.Y but it took a little longer for me to understand that this can be done when going up to 11g.

The best place in the online documentation for information about this is Appendix B of the Oracle Clusterware Installation Guide. Another useful thing to look at is metalink note 338706.1 which tells you about the prerequisites you need to fulfill before you can upgrade your clusterware to 11g. Of course it is only with hindsight that I have seen the information there in the Clusterware Installation Guide. Here is what I did to upgrade, you are far better of, unlike myself, running the preupdate.sh script as recommended - but hey this what testing is all about

From the unziped clusterware directory run the cluster verification utility to check your system is ready to upgrade:

runcluvfy.sh stage -pre crsinst -n node1,node2 -verbose

make sure you upgrade any rpm’s needing changed.

Bring down the database and ASM instances on the first node you want to upgrade and then stop crs:

/opt/oracle/product/crs/bin/crsctl stop crs

If you run the preupdate.sh script that is in the clusterware/upgrade directory you don’t need to stop crs yourself or indeed perform the next step in changing permissions of the crs directory as it’s taken care for you.

The permissions on my crs directory were incorrect and the directory was owned by root. I changed them with:

chown -R oracle:oinstall crs/

run the installer and it will detect your CRS_HOME and offer to upgrade it, you want to make sure that on the Specify Hardware Cluster Installation Mode screen you select just the node you want to upgrade, assuming you are doing it rolling:

Once the upgrade has done it’s thing you are prompted to run the rootupgrade script:

[root@linuxrac2 install]# ./rootupgrade
Checking to see if Oracle CRS stack is already up…

copying ONS config file to 11.1 CRS home
/bin/cp: `/opt/oracle/product/crs/opmn/conf/ons.config’ and `/opt/oracle/product/crs/opmn/conf/ons.config’ are the same file
/opt/oracle/product/crs/opmn/conf/ons.config was copied successfully to /opt/oracle/product/crs/opmn/conf/ons.config
WARNING: directory ‘/opt/oracle/product’ is not owned by root
Oracle Cluster Registry configuration upgraded successfully
Adding daemons to inittab

Attempting to start CRS stack
The CRS stack will be started shortly
Oracle CRS stack has failed to start. Check the file /var/adm/messages or the crsd, cssdd, and evmd logs in
/opt/oracle/product/crs/log/linuxrac2 directory for more details

You don’t need to worry when it says CRS stack has failed to start, because after a few moments CRS is running happily! Your database and/or ASM isntance will now be automatically restarted as well.

It is also worth pointing out that the active CRS version only becomes the 11.1.0.6.0 version after all nodes are upgraded:

[root@linuxrac2 crsd]# crsctl query crs softwareversion
CRS software version on node linuxrac2 is 11.1.0.6.0
[root@linuxrac2 crsd]# crsctl query crs activeversion
CRS active version on the cluster is 10.2.0.3.0

You now basically proceed to perform the same on the other nodes in your cluster, and there you have it, a rolling clusterware upgrade from 10g to 11g. I was actually well impressed with how smooth and painless the upgrade was and there really were no brown trouser moments.

It remains to be seen how stable the new 11g clusterware is but I’m sure it’s just a coincidence that about 12 hours after the upgrade one of the nodes on one of the clusters had a kernel panic and froze!

Flashback through a database upgrade

While testing how to perform a 10.2.0.3 to 11.1.0.6 upgrade I thought I would attempt to flashback through the upgrade back to the 10.2.0.3 database. There is a major caveat to this, you must not change the compatible parameter to be higher than your original release or you will not be able to open the database with the 10.2.0.3 software. I’m sure this will work for other 10.2 (and probably 10.1) releases.

[oracle@testdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 17 14:57:45 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> create restore point begin_upgrade guarantee flashback database;

Restore point created.

Best to create a guaranteed restore point, just in case you over run your flashback retention times

SQL> select * from V$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME NAME
———- ———————————- ———– ————– ——— ———
2041092430 2 YES 8192000 17-JAN-08 15.12.47.000000000 BEGIN_UPGRADE

SQL> @/oracle/product/11.1.0/rdbms/admin/utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool 01-17-2008 14:59:03
.
**********************************************************************
Database:
**********************************************************************
–> name: TESTDB
–> version: 10.2.0.3.0
–> compatible: 10.2.0.3
–> blocksize: 8192
–> platform: Linux 64-bit for AMD
–> timezone file: V4
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 1021 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 584 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 685 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 61 MB
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
– No update parameter changes are required.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
– No renamed parameters found. No changes are required.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
–> “background_dump_dest” replaced by “diagnostic_dest”
–> “user_dump_dest” replaced by “diagnostic_dest”
–> “core_dump_dest” replaced by “diagnostic_dest”
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
–> Oracle Catalog Views [upgrade] VALID
–> Oracle Packages and Types [upgrade] VALID
–> JServer JAVA Virtual Machine [upgrade] VALID
–> Oracle XDK for Java [upgrade] VALID
–> Oracle Workspace Manager [upgrade] VALID
–> Oracle Text [upgrade] VALID
–> Oracle XML Database [upgrade] VALID
–> Oracle Java Packages [upgrade] VALID
–> Oracle interMedia [upgrade] VALID
–> Data Mining [upgrade] VALID
–> Expression Filter [upgrade] VALID
–> Rule Manager [upgrade] VALID
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER FLOWS_020000 has dependent objects.
PL/SQL procedure successfully completed.

You must run the utlu111i script as it populates a table that the upgrade script checks, if this has not been run the upgrade will bomb fairly early. Remember to run this with the Oracle 10g environment, it would not work when i tried to run it with the 11g environment.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

startup the database with the 11g ORACLE_HOME

SQL> startup upgrade pfile=’/home/oracle/init11g.ora’;
ORACLE instance started.

Total System Global Area 784998400 bytes
Fixed Size 2148512 bytes
Variable Size 201328480 bytes
Database Buffers 578813952 bytes
Redo Buffers 2707456 bytes
Database mounted.
Database opened.

run the upgrade script

SQL> @?/rdbms/admin/catupgrd
.
.
.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile=’/home/oracle/init11g.ora’;
ORACLE instance started.

Total System Global Area 784998400 bytes
Fixed Size 2148512 bytes
Variable Size 201328480 bytes
Database Buffers 578813952 bytes
Redo Buffers 2707456 bytes
Database mounted.
Database opened.

We now have an upgraded 11g database

SQL> select * from v$version;

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

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Real Application Testing option

go back to the 10g Oracle home

[oracle@testdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jan 17 16:39:17 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount pfile=’/home/oracle/init11g.ora’;
ORACLE instance started.

Total System Global Area 788529152 bytes
Fixed Size 2076008 bytes
Variable Size 205521560 bytes
Database Buffers 574619648 bytes
Redo Buffers 6311936 bytes
Database mounted.

flashback to our restore point

SQL> flashback database to restore point begin_upgrade;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Now our database is back to it’s 10g version

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

So this could be useful for testing an 11g upgrade from a 10.X release, it could enable you to quickly downgrade and retry should something go wrong. However the lack of the ability to open the upgraded release with compatible set to 11 does limit somewhat the new features you could test.

I’m not really sure how practical it is really, I was more just curious what what would actually happen if you attempted to flashback through the upgrade.