Search this Blog

Friday, December 3, 2010

AIX Agent11g patch => Bug Solved!!

09-12-2010:
Oracle has fixed the "below" bug and release a new Agent11g installation package for AIX on OTN. It is preferred to use this new package above the workaround below.


03-12-2010:
Regarding the Agent install error:

Checking Temp space: must be greater than 150 MB. Actual 541 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4096 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-12-02_02-01-58PM. Please wait ...xxxxx agent> Exception in thread "main" java.lang.NoClassDefFoundError: oracle.sysman.oii.oiix.OiixNetOps
at oracle.sysman.install.oneclick.EMGCInstallStaticVariables.(EMGCInstallStaticVariables.java:119)
at java.lang.J9VMInternals.initializeImpl(Native Method)
at java.lang.J9VMInternals.initialize(J9VMInternals.java:200)
at oracle.sysman.install.oneclick.EMGCInstaller.parseCommandLineArgs(EMGCInstaller.java:506)
at oracle.sysman.install.oneclick.EMGCInstaller.init(EMGCInstaller.java:204)
at oracle.sysman.install.oneclick.EMGCInstaller.main(EMGCInstaller.java:1042)
Caused by: java.lang.ClassNotFoundException: oracle.sysman.oii.oiix.OiixNetOps
at java.net.URLClassLoader.findClass(URLClassLoader.java:421)
at java.lang.ClassLoader.loadClass(ClassLoader.java:643)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:346)
at java.lang.ClassLoader.loadClass(ClassLoader.java:609)
... 6 more


Oracle support has supplied us with a workaround TAR file.
You can find it here: http://www.mediafire.com/file/17apim8h8lci88q/ouicore.tar

The workaround steps are:

=== ODM Action Plan ===
1. Download the ouicore.tar file attached to the SR.
2. cp ouicore.tar
3. cd
4. tar -xvf ouicore.tar
5. ls -al aix/agent/stage/Components/oracle.swd.oui.core/11.1.0.8.0/1/DataFiles
-> this should show 5 filegroup jar files and directory Expanded
6. Start the installation

Success !!!

Friday, November 12, 2010

RMAN 11g suddenly fails on AIX, nothing changed

Okay, after 1.5 hours digging into the system (and my brains) we have solved yet another mysterious RMAN problem.


What was the case?

After a successfull backup last night, rman suddenly fails to allocate a tape channel while trying to backup archivelogs. The tape channel in this case points to a TSM management layer.
The error message shown was:


RMAN> run {
2> allocate channel ch1 type 'sbt_tape' parms
3> 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
4> backup
5> incremental level 0
6> tag arch_bk
7> format 'arch_%d_t%t_s%s_p%p'
8> (archivelog all delete input );
9> sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
10> }
11>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on ch1 channel at 11/12/2010 08:34:42
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27000: skgfqsbi: failed to initialize storage subsystem (SBT) layer
IBM AIX RISC System/6000 Error: 106: Reserved errno was encountered
Additional information: 7011
ORA-19511: Error received from media manager layer, error text:
SBT error = 7011, errno = 106, sbtopen: system error

When searching on Google or Oracle Support, the combination of ORA-19554, ORA-27000,ORA-27000 and IBM AIX RISC System/6000 Error: 106 usually point to problems with writing the TSM logfiles ( tdpoerror.log, dsmerror.log). Most of the problem then are solved by setting correct permissions for these files, making the Oracle owner able to write these files.

In our case the permissions were OK, and as said above the backup of last night had run successfully.
Trying manually to establish a channel connection also failed.

At this point I had discussedthe problem with the tape-storage guys about what could be wrong here. They told me TSM was working OK, no problem whatsoever.
They agreed it had to be something on the Oracle server, simply because of the fact RMAN suddenly stopped working, even when nothing had changed.

In the tdpo.opt file I suddenly found the TDPO_TRACE_FILE option be turned on.
This tracefile pointed to a file called 'tdpo.out'

In this file I saw the following entry:

11/12/10 08:27:27.086 [1531908] [1] : init2.cpp ( 359): sbtinit(): Rman Trace File: '/opt/wcsprd/ora/diag/rdbms/wcsprd/WCSPRD1/trace/sbtio.log'

This meant that the one attempt this morning that succeeded to allocate a channel manually, logged this entry in the trace file.
All other attempts allocating a channel hadn't logged this message.

This combined with the :

IBM AIX RISC System/6000 Error: 106: Reserved errno was encountered
Additional information: 7011
ORA-19511: Error received from media manager layer, error text:
SBT error = 7011, errno = 106, sbtopen: system error

I went looking into the trace directory where sbtio.log was located.
In this directory a list of 6886 tracefiles was found. In my opinion this should not be a problem while trying to open a file there.

However it was the cause of the problem !!!

After cleaning this directory, using the adrci tool, RMAN now works again.


Conclusion:

RMAN is a very powerfull tool for making online backups.
It also sometimes is a very misterious tool.
It sometimes stops working when nothing has changed in the configuration, displaying an error message that is not always very clear.
The Automatic Diagnostic Repository (ADR) is a very usefull feature of Oracle 11g. It centralizes all database related logfiles into one location, making problem searching easier.
That this tool however can turn into problems for RMAN, I never would have expected.
Luckily Oracle has included the ADRCI ( ADR commandline interface ) that enables the DBA to easily clean out the ADR.
We now have scheduled a daily cleanout of the ADR, simply to avoid RMAN problems


Finally one remark:
In my honest opinion the fact that there were only 6886 files in the trace directory, should not have caused such a problem for RMAN. The Oracle owner's ulimit permissions are all set to unlimited where it comes to 'number of open files'.







Friday, October 15, 2010

Under the hood of ASM

With Oracle ASM the Oracle DBA has a new very powerfull storage medium where he/she can put the database files.
However ASM already was introduced in Oracle 10g, for a lot of DBA's it is still a black-box with only very small holes in it where you can peek in.

In the beginning, I must confess I was very suspicious about ASM, especially because of being a black-box.
However it occured to me very soon that it looks like a black-box because there is nothing much to see.
ASM is very limited in what it shows to the DBA, and even now I think the word 'limited' does not fit here. The word 'basic' is more fitting. ASM shows only basic information of what it is housing and what it is doing.
For showing this information ASM has a couple of views available. These are the only views available. In contradiction to a "normal" database instance, all dictionary views are not available in ASM instances.
The following views are available:


View NameDescription
V$ASM_DISKGROUPLists all available diskgroups with their characteristics
V$ASM_DISKGROUP_STATSynonym for v$asm_diskgroup
V$ASM_DISKLists all disks available to ASM that match the ASM disk_string parameter
V$ASM_DISK_STATSynonym for v$asm_disk
V$ASM_FILEContains an entry for every file stored in ASM
V$ASM_ALIASLists the known file names that link to ASM files
V$ASM_CLIENTLists the databases that connect to this ASM instance
V$OPERATIONLists long running ASM operations, like rebalance actions


Last week we had an issue, where ASM showed very high I/O activity on 2 of the 9 disks in a diskgroup. The behaviour could not be explained by the activity in the application. It looked like ASM was either doing a large rebalance on its own, or we were slowly running into a problem with the spreading of the data.

Unfortunately the above views can't give you an overview about where the data really is put, i.e. on which disk.
As everyone knows ASM uses striping technology, to spread the data across all disks.
It however looked like we had hit some hot block in a table(space) that was only stored on these two disks.

Using my most famous library "Google", after a while I found a kind of hidden view. That is, all above views have a system view which has the similar information.
There is however 1 view that hasn't a 'user readable' alias.

This view contains information about the striping of the database objects across the disks of a diskgroup. This view is called x$kffxp and it can be joined with the other views to get the needed information.
However our "problem" stopped after a couple of hours I did check the seperation of the serveral tablespace blocks across the disks. There was nothing abnormal found, however if there had been something abnormal, it might be that the "problem" was indeed a rebalance, solving this inbalance.

I used the following script to check the balance of the data across the disks:

set pages 9999 lines 200
column name format a40
select a.name, b.disk_kffxp disk, count(disk_kffxp) blocks
from
v$asm_alias a
, x$kffxp b
, v$asm_file c
where
a.group_number=b.group_kffxp
and a.group_number=c.group_number
and a.file_number=c.file_number
and a.file_number=b.number_kffxp
and c.type in ('DATAFILE','TEMPFILE','ONLINELOG')
group by a.name, b.disk_kffxp
order by a.name, count(disk_kffxp) desc;

Output then could look like ( for the SYSAUX tablespace ):

NAME DISKNR BLOCKS
SYSAUX.338.715000059 6 242
SYSAUX.338.715000059 7 229
SYSAUX.338.715000059 2 218
SYSAUX.338.715000059 5 216
SYSAUX.338.715000059 0 213
SYSAUX.338.715000059 1 211
SYSAUX.338.715000059 4 211
SYSAUX.338.715000059 3 207
SYSAUX.338.715000059 8 181

Conclusion:
The SYSAUX tablespace is reasonably balanced, although the difference between disk 8 and disk 6 is more than 40 blocks stored.

Thursday, October 14, 2010

NOTE: Unident of disk

In our tracefile of the ASM rebalancer process, every 5 minutes the following line is shown:

NOTE:Unident of disk:/dev/asmspfile

There is not really much information to be found on what this can mean, even on My Oracle Support there is no information found.

I am guessing this message is explained by the fact that the diskname matches our asm_diskstring:

SQL> show parameter asm_diskstring

NAME TYPE VALUE
------------------------------
asm_diskstring string /dev/asm*


but in fact is not part of any (active) diskgroup
In our case this disk, or raw device, contains the shared spfile of the ASM instances across our RAC nodes.

If this assumption is true, then this message can be safely ignored.


What is the risk:

If you don't keep a good administration of what disks have which function, or you mess up with the disk naming convention anyway, the risk exists that you'll add disks to a diskgroup that might be in use for other functions.
Like in our case, if I would not know this disk contains the ASM spfile, it would be very easy to add that disk to a diskgroup. And I doubt if ASM would complain doing so.

Thursday, September 23, 2010

Using Oracle Configuration Manager

In this section the installation/configuration of Oracle's Configuration Manager is explained.

What is this Configuration Manager?

Oracle's Configuration Manager is a tool that enables users to link their Oracle database configuration to an account on the Oracle Support site.

The advantages are huge !!

By linking your system to the Oracle Support Site the system is analyzed, checked and monitored constantly. This means that Oracle Support is able to warn you up front of any problem, risk or abnomality in your configuration.

Next to that the Oracle Support Site offers you - the customer - the possibility to browse your own system in a graphical manner.
If you browse to the Support Site a Window is shown, called "Systems".

This window will be empty in the beginning, because you didn't link any systems to Oracle Support before as shown here:






Also other windows like 'Patch and Health Recommendations' show no information.


Setup Configuration Manager:

In order for configuration Manager to start doing his job, make sure that:

  • There is an Internet connection from the database server configured.
  • You are logged in as the owner of the Oracle database.
Browse to the directory $ORACLE_HOME/ccr.
This is the base directory for the Configuration Manager.

$ cd $ORACLE_HOME/ccr

Now start the Setup of the Configuration Manager :

$ ./bin/setupCCR

The system will ask you for a valid Oracle Support Account. This account should be able to manage systems on the MOS site.

Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your MetaLink
Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: myaccount@mycompany.com
Provide your MetaLink password to receive security updates via your MetaLink account.
Password (optional):


The system starts configuring "Configuration Manager"

** Installing base package **
Deploying core - Version 10.3.0.1.0

** Registering installation with Oracle Configuration Manager server(s) **
Deploying engines - Version 10.2.7.1.0
Deploying metricdata - Version 10.2.4.0.3
Deploying scripts - Version 10.3.0.1.0
Deploying urda - Version 4.14.8.12.2

** Getting package updates from ContentServer **

Oracle Configuration Manager has been configured in connected mode. If the
target ORACLE_HOME is running a database, please refer to the
"Post-installation Database Configuration" section of the OCM Installation
and Administration Guide
(http://www.oracle.com/technology/documentation/ocm.html) to complete the
installation.

View configuration data reports and access valuable configuration best
practices by going to MetaLink.

After the configuration is finished, the database needs to be configured too. Before running the configuration script make sure that the parameter UTL_FILE_DIR contains access to the $ORACLE_HOME/ccr/state directory. It might be needed to bounce the database since this parameter is a static parameter.

Now run the database configuration script. You might be prompted for the database instance name and/or the password. If ORACLE_SID is set and the database can be reached through OS-authentication, the script will just configure that database, without asking anything:

$ ./admin/scripts/installCCRSQL.sh collectconfig
Successfully installed collectconfig in the database with SID=ORCL.

Now run the first collection to be uploaded to Oracle Support.

./bin/emCCR collect

This forces the Configuration Manager to upload the current configuration. At this time there is also a daemon running that will upload configurations once a day. The output of the above statement should look like:

$ ./bin/emCCR collect
Oracle Configuration Manager - Release: 10.3.3.1.0 - Production
Copyright (c) 2005, 2010, Oracle and/or its affiliates. All rights reserved.
------------------------------------------------------------------
Collection and upload done.


This completes the installation.

Now login to the Oracle Support Site using the same account as used during the setup. As soon as the Dashboard appears, we are looking for a Window called "Task: Associate Collections". If this window is now shown, click on the link "Customize Page..." and drag this window to a slot on the Dashboard.

Note: This window will only stay visible if there any Collections to be Associated, so probably the window will not be shown initially, unless there is already one configuration uploaded and waiting to be associated.
This window is used to link a System to a Support Identifier. Please take care, because this action can only be done once and can not be reverted !!!

The window looks like below:



Select the correct support Identifier from the pull-down menu saying "Select a Support Identifier" and Click on the "Associate" button in the bottom corner of the Window.

As soon as all the Associations are successfull, this Task window will dissappear again.

This completes the setup of the Configuration Manager. Now you're able to browse the System and receive Patch warnings.

Tuesday, August 24, 2010

Oracle table reorganization: Online or Offline

When using tables with large amounts of rows and especially after a lot of rows have been deleted from such a table, reorganizing the table may improve the performance of the table.
Oracle knows two types of table reorganizations.

  • Rebuilding the table by means of recreating it

  • Rebuilding the table by shrinking its free space ( Oracle 10g and up )

Below the two methods are explained.


Rebuilding the table by means of recreating it
There are two ways of rebuilding a table by recreating it. The first option is to export all the data into a file using the export utility.
After that truncate ( of recreate ) the table and reload the data back into it. The disadvantage here however is a long downtime.

Another method is moving the table either to another tablespace or within the same tablespace.
The advantage here is that the downtime will me much less, but it also has some disadvantages:


  • The tablespace needs to be able to store this second copy of the table
  • The tables indexes, any depending objects like packages, procedures can become INVALID and might need to be rebuild
  • There will be a table-level exclusive lock on the table involved. Any application using that table will be affected by this lock.

By moving the table to another tablespace temporarily, the DBA is also able to reorganize the tablespace.


Rebuilding the table by shrinking its free space
Starting with Oracle 10g all the above is replaced by a very nice feature called 'SHRINK SPACE';
As of this release, tables can be reorganized online, without affecting the users functionality.
That is:

* The table itself must be in an ASMM tablespace
* The table must have row movement enabled
* At the end of the action a shortt table level lock takes place to adjust the HWM (Highwater Mark) of the table.


And it's easy !!
Just enable 'row movement' and start shrinking tables:

SQL> ALTER TABLE MYTABLE ENABLE ROW MOVEMENT;
This enables row movement of the table. THis is necessary so that oracle is able to actually move the rows to other free extents in order to shrink the space needed.

SQL> ALTER TABLE MYTABLE SHRINK SPACE COMPACT;
This shrinks the used space of the table, but does not update the HWM. This is usefull if you don't want to have a table-level lock during business hours.

SQL> ALTER TABLE MYTABLE SHRINK SPACE;
This command shrinks the contents of the table and subsequently updates the HWM. This statement can also be used after a shrink with 'compact' option has been done, just to update the HWM.

SQL> ALTER TABLE MYTABLE SHRINK SPACE CASCADE;
This command shrinks the contents of the table and all dependent objects like indexes.


High Water Mark:
Now we have mentioned the High Water Mark (HWM) a couple of times. A short explanation:
The HWM is the pointer to that location of a table where on any point in history the table has grown to. If a lot of rows are deleted from a table, a lot of free space exists before the HWM. Doing a full table scan Oracle will not only read the actual rows, but also the empty space up to the HWM.
This is wasted time and is worsening the performance of getting data from that table.
So if from a large table a lot of rows are deleted, e.g. a count(*) will still take as long as before the delete.


Triggers and indexes:
In contradiction to moving a table, or exporting/importing its data, triggers and indexes are NOT affected by a shrink space command. They will remain valid and functional during and after the operation.


Wasted space:
How to determine if a table is a candidate for a reorganization ?

Use the below script to determine if a table has a lot of wasted space.
If it substantial in relation to the total size of the table, then a reorganization ( shrink ) is recommended.

set lines 200;
column owner format a15;
column segment_name format a30;

select
a.owner,
a.segment_name,
a.segment_type,
round(a.bytes/1024/1024,0) MBS,
round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED
from dba_segments a, dba_tables b
where a.owner=b.owner
and a.owner not like 'SYS%'
and a.segment_name = b.table_name
and a.segment_type='TABLE'
group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0)
having round(bytes/1024/1024,0) >100
order by round(bytes/1024/1024,0) desc ;


The above script is only working if the tables involved have statistics gathered on them


PCTFREE
An extra remark about PCTFREE.
Tables with a high value of PCTFREE show also a higher value of wasted space.
The space reserved for PCTFREE in a block can not be shrinked. So even after a shrink of a table a lot of wasted space can remain.

The effect of PCTFREE also seen before the shrink is executed when looking at the wasted space using the above script.

In an example we have three identical tables with the following structure:

SQL> CREATE TABLE T1 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 40;
SQL> CREATE TABLE T2 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 10;
SQL> CREATE TABLE T3 ( ID NUMBER(10), VAL VARCHAR2(1000 CHAR) ) PCTFREE 50;


All tables are loaded with the same data, approx. 5.5 million rows.
When running the above script it shows:

SEGMENT_NAMESEGMENT_TYPEMBSWASTED
-------------------------------------------------------------
T1TABLE50642348
T2TABLE3333617
T3TABLE61443428


Just because of the variation in the value of PCTFREE, the total size and wasted space are different per table.

The effect of PCTFREE after the shrink is that not all "wasted space" is released.
The term "wasted space" therefore is not really valid with a high PCTFREE. It is wasted space + the reserved space directly available for updates. Of course above, just after table creation there is no wasted space. It is only reserved (pctfree) space. Therefore - for this example - we are going to delete approx 30% of the rows of all tables.

SQL> delete from t1 where length(val)< 300;

SQL> delete from t2 where length(val)< 300;

SQL> delete from t3 where length(val)< 300;

As soon as the delete is finished we compute stats on these tables in order for the wasted-space script to have a valid calculation base.
Running the script again now shows:

SEGMENT_NAMESEGMENT_TYPEMBSWASTED
-------------------------------------------------------------
T1TABLE50642789
T2TABLE33331058
T3TABLE61443869

After this we are going to 'shrink' the available wasted space, using:

SQL> ALTER TABLE T1 SHRINK SPACE CASCADE;
SQL> ALTER TABLE T2 SHRINK SPACE CASCADE;
SQL> ALTER TABLE T3 SHRINK SPACE CASCADE;

The result is:


SEGMENT_NAMESEGMENT_TYPEMBSWASTED
-------------------------------------------------------------
T1TABLE42451970
T2TABLE2779505
T3TABLE5292

3018

This test clearly shows that even if Oracle or its tools like ADDM inform you about wasted space in a table, it might be that this is because you have a high value for PCTFREE. Shrinking these table might afterwards look like if nothing happened.
Check in that case if PCTFREE is high and if the value of 'wasted space' divided by the 'total size' is equal to that value:

Example:

Table T1: PCTFREE = 40% ==> 1970 / 4245= 46%
Table T3: PCTFREE = 50% ==> 3018/5292 = 57%

So, actually the 'wasted space' of these tables is respectively 6% and 7 %

Wednesday, July 14, 2010

AIX based RAC reboots nodes by itself

Problem statement:

After a couple of weeks running with no problem at all, our 2 node RAC running on Oracle release 11.1.0.7 on AIX 5.3 TL10 platforms started rebooting nodes by itself.


Analysis:

Analyzing the CRS, ASM and Rdbms logfiles, it simply showed the server had terminated. There were no logs to be found of a gracefull shutdown of the cluster resources.

In contradiction to this, the AIX errpt showed a "USER SHUTDOWN" as the reason for the reboot. From the viewpoint of AIX someone or something had issued a system shutdown.
Because the servers didn't crash, but restarted instead, the UNIX admins confirmed it must have been a shutdown command with restart options that brought the system down.
However in such a case, one would expect the Cluster resources to go down gracefully which would have been seen in the CRS logs.

Oracle support confirmed the sudden restart of the system, by seeing a time-gap in the logfiles. The logging of actions suddenly stopped on all resources, and then started again when the cluster resources where coming up again.


Resolving the issue:

Beause we were unable to find a cause of our own, and Oracle support was searching into the direction of a system problem ( believing that not a RAC process had restarted the server ) the created S/R got somehow stuck. There were no new things found to discover.

Only after escalating the S/R to a prio 1, after just another unscheduled reboot, Oracle support started asking about the process status of the 'oprocd' daemon.
It triggered us in starting to browse the internet for information about the 'oprocd' daemon.
Using Note 265769.1 we pinpointed the reason of the reboot must be with oprocd.

Finally we found the logfile that made sense. In /etc/oracle/oprocd we found the logfile from just before the reboot. It clearly stated:

AlarmHandler: timeout(1892 msec) exceeds interval(1000 msec)+margin(500 msec). Rebooting NOW.

What does this mean, and what is 'oprocd' ??

The 'oprocd' process is a processor monitor daemon. It monitors CPU load by setting a timer to 1000ms. After that is gets into a sleep for 1000ms. As soon as it wakes up it checks if the sleep period has been as long as the set timer. If it doesn't the maximum margin to deviate from the set timer is by default 500ms ( half a second ). If it wakes up beyond that margin, the oprocd decides to reboot the system.

Now that we really got a message to work on, we dumped that into Google and it came up with a really helpfull page: http://www.aixmind.com/?p=845
This internet page clearly described all the problems we've had, including all the symptoms and logfile entries.
It explained a couple of steps on how to solve this problem.

We send the page to Oracle support, asking if we could be suffering from that problem. After an hour we received an "Action plan" to solve the problem. The action plan Oracle support send us, matched for 90% the instructions of the page we sent.
Oracle support also informed us that this was a known problem, starting to develop as early as release 10.2.0.4.
They confirmed the default values 'oprocd' is running with ( interval 1000ms and margin 500ms ) are too low. In the newest release - I recon 11.2 - these defaults will be increased.

I think it is unfortunate that it takes three weeks to come with a 'known' problem and then only after we sent a hint to Oracle support.


The solution:
Oracle Support send us the following action plan to be implemented:

1. Execute as root
#crsctl stop crs

2. Execute as root
#/bin/oprocd stop

3. Ensure that Clusterware stack is down by running ps -ef egrep "crsd.binocssd.binevmd.binoprocd". This should return no processes.

4. From one node of the cluster, change the value of the "diagwait" parameter to 13 seconds by issuing the command as root:
#crsctl set css diagwait 13 -force

5. From one node of the cluster execute the following command as root:
#crsctl unset css misscount -force

6. Restart the Oracle Clusterware on all the nodes by executing:
#crsctl start crs

7. Validate that the node is running by executing:
#crsctl check crs

This solution increases the value for the margin from 500ms to 10000ms, so 20x higher.

This will :
a) Give less chance that oprocd get outside of the total time period ( 11000ms ).
b) If there is somehow a problem, which will bring oprocd outside of this time, the system has an addition 9500ms to write more informational logs to disk.


Unfortunately our RAC nodes did not come up correctly after the change.

The 'crs_stat -t' showed:

# ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....D1.inst application ONLINE OFFLINE
ora....D2.inst application ONLINE OFFLINE
ora.WCSPRD.db application ONLINE OFFLINE
ora....RD1.srv application ONLINE OFFLINE
ora....RD2.srv application ONLINE OFFLINE
ora....sprd.cs application ONLINE OFFLINE
ora....SM1.asm application ONLINE ONLINE vsv1h171ps
ora....PS.lsnr application ONLINE ONLINE vsv1h171ps
ora....1ps.gsd application ONLINE ONLINE vsv1h171ps
ora....1ps.ons application ONLINE ONLINE vsv1h171ps
ora....1ps.vip application ONLINE ONLINE vsv1h171ps
ora....SM2.asm application ONLINE ONLINE vsv1h181ps
ora....PS.lsnr application ONLINE ONLINE vsv1h181ps
ora....1ps.gsd application ONLINE ONLINE vsv1h181ps
ora....1ps.ons application ONLINE ONLINE vsv1h181ps
ora....1ps.vip application ONLINE ONLINE vsv1h181ps

and the alert_log of the databases kept showing:

Tue Jul 13 22:11:18 2010
Starting ORACLE instance (normal)
Tue Jul 13 22:12:08 2010
Starting ORACLE instance (normal)
Tue Jul 13 22:12:56 2010
Starting ORACLE instance (normal)
Tue Jul 13 22:13:24 2010
Starting ORACLE instance (normal)
Tue Jul 13 22:13:51 2010
Starting ORACLE instance (normal)
Tue Jul 13 22:28:17 2010
Starting ORACLE instance (normal)

The nodes had to be completely rebooted to get up and running.
This might be explained by the fact that oprocd is build into the AIX kernel, which would mean he kernel has to be reloaded after the above changes.

Thursday, June 24, 2010

ACL and XDB Problems

Problem statement:

After upgrading a database from Oracle 11.1.0.6 to 11.1.0.7 an XDB installation was performed to be able to send Emails from the database.
As of Oracle 11g the sending of Emails needs the creation of an Access Control List, to control which database users can access a remote host for relaying ( or sending ) the Email to.

On the Internet there are scripts available to create such an ACL.

We used the script below to first create a procedure that takes care of all the steps involved in creating an ACL:

create or replace procedure mailserver_acl(
aacl varchar2,
acomment varchar2,
aprincipal varchar2,
aisgrant boolean,
aprivilege varchar2,
aserver varchar2,
aport number)
is
begin
begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL(aacl);
dbms_output.put_line('ACL dropped.....');
exception
when others then
dbms_output.put_line('Error dropping ACL: 'aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(aacl,acomment,aprincipal,aisgrant,aprivilege);
dbms_output.put_line('ACL created.....');
exception
when others then
dbms_output.put_line('Error creating ACL: 'aacl);
dbms_output.put_line(sqlerrm);
end;
begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(aacl,aserver,aport);
dbms_output.put_line('ACL assigned.....');
exception
when others then
dbms_output.put_line('Error assigning ACL: 'aacl);
dbms_output.put_line(sqlerrm);
end;
commit;
dbms_output.put_line('ACL commited.....');
end;

/


Now we used the following call to this procedure to create an ACL for an user:

begin
mailserver_acl(
'mailserver_acl.xml', -- Name of the ACL list
'ACL used for Email', -- Just some comment about what this list is/does
'SCOTT', -- The user/role to grant this privilige to
TRUE, -- Is the privilege granted (TRUE) or denied (FALSE)
'connect', -- What right granted
'our.mail.server', -- to what server
25); -- on which Port
end;


The first time the result was:
ORA-46105: Unable to load security class DAV::dav

Looking at the database objects, an Invalid package was detected and recompiled.
After recompiling the package and retrying the procedure, the below problem appeared:
ORA-46105: Unable to load security class http://xmlns.oracle.com/plsql:network


Googling a bit around, gave us the result that the problem was in a failed XDB installation. This was true, because there still were some Invalid XDB objects, which couldn't be compiled.


The solution:

The below steps brought us to a solution. It took an S/R with Oracle support to get the steps right, because the Notes aren't that clear:

First make a note of the tablepace used by the XDB user in the database.
Also write down the name of the TEMP tablespace of that database.
These values will be needed in the reinstallation steps.

First deinstall the current XDB installation. Refer to Note 742014.1 if you're using applications like APEX or Oracle Multimedia.

$ sqlplus /nolog
SQL> conn / as sysdba
SQL> spool xdb_reins.log
SQL> shutdown Immediate;
SQL> startup;
SQL> @?/rdbms/admin/catnoqm.sql


This will completely deinstall the XDB installation and also drop the XDB user.
Now shutdown the database again, leave and restart SQLplus.

SQL> shutdown immediate
SQL> quit;

It is very important in this stage to exit and restart SQLplus
Use the statement below to reinstall the XDB packages.

Note that you need to enter the above noted values at the prompt of the SQL script.

In our case we entered 'xdb' for the password of the XDB user, 'sysaux' for the tablespace and 'temp' for the temporary tablespace;

SQL> conn / as sysdba
SQL> spool xdb_reins.log append --append to the existing logfile
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql <xdb> <target tablespace> <temp tablespace>
SQL> spool off
SQL> quit

When these steps finish, the Oracle Note about reinstalling XDB also finishes.

It is thanks to good assistance of Oracle Support ( and no, I don't get money for this statement ) that our reinstallation succeeded.

There is another step to take !!
That is why.

You have to re-register XML components of the EXFSYS schema's.
Note 558834.1 discusses these steps. But I'll also list them here.
I strongly recommend to first read the Note, before executing these steps!!


-- Register XML Schemas for the Rules Manager component (part of CATPROC)

SQL> connect / as sysdba
SQL> alter session set current_schema = EXFSYS;
SQL> @?/rdbms/admin/rulpbs.sql

SQL> commit;


Note 558834.1 also show steps for other schema's but they only need to be executed for Oracle Intermedia.

After all these steps and another restart of the database, our ACL could be created and we were able to start using UTL_MAIL packages.

Enable Flashback for RAC

Enabling Flashback in RAC is just a bit more complex than enabling flashback in a single instance.
First we need to make sure that:

- Our RAC database is running in ARCHIVELOG mode. This can be checked using:

SQL> select log_mode from v$database

LOG_MODE
--------------
ARCHIVELOG

- The database is using some kind of shared storage, preferably ASM.
In the statements below a diskgroup of ASM is used. Remember to use the '+' sign in front of the diskgroup name, like '+FB01'.


Now we need to set the required parameters in the spfile to support the flashback function:

SQL> alter system set db_recovery_file_dest_size=20G scope=spfile sid='*';
SQL> alter system set db_recovery_file_dest='' scope=spfile sid='*';
SQL> alter system set db_flashback_retention_target=


The value for represents a value for which you want to be able to keep data. This value is ofcourse restricted by the available diskspace. The default is 1440 minutes.

Now we need to shutdown all instances of the RAC database. This means the complete cluster will be down during this action. If the database name is RACDB then the statement to shutdown the database looks like:

$ srvctl stop database -d RACDB

Start on one of the nodes an instance in mount mode:

SQL> startup mount;

Then turn flashback on using the command:

SQL> alter database flashback on

Verify the setting with the below SQL:

SQL> select log_mode, flashback_on from v$database

LOG_MODE FLASHBACK_ON
--------- -------------------
ARCHIVELOG YES

Now shutdown the instance again and restart the cluster database:

SQL> shutdown immediate
SQL> quit;

$ srvctl start database -d RACDB

Remember that a shutdown of the database als may have stopped the resources for the transparant application failover. They then need to be restarted also again.

This concludes the enabling of flashback on RAC.


Bug 7334226: RAC instance crash possible with FLASHBACK on

Please look up Note 7334226.8 on the Oracle Support site.
It states that all RAC versions up to 11.1.0.7 suffer from possible instance crashes if flashback is enabled. The issue is fixes in patch set update 11.1.0.7.2. and in the 11.2.0.1 base-release

Wednesday, June 16, 2010

SSH equivalence on RAC

A couple of months ago, we setup our first RAC cluster consisting of two LPAR on an IBM AIX Power6 server. Although it has been months ago, and the cluster has been running a production database now successfully for over a month, I do want to share the 'frustration's we've had with setting up the user-equivalence, needed for the installation.


For the newbie's among us, first of all a short explanation of the phrase "SSH equivalence":

SSH equivalence means that acrosse different servers a user, and especially its credentials and rights, are considered equal to the local server.
With SSH equivalence configured a logged on user can access a remote server without password if and only if his account with the same settings also exist on that remote server.

SSH equivalence is needed during the installation of the RAC software. All parts of the Oracle installation ( CRS, ASM and the Rdbms ) are each installed on the local host and afterwards copied to all remote nodes using this SSH equivalence.


First of all a short description of how to setup SSH equivalence. Of course the Oracle installation manual has a more detailed explanation to follow:

Generate a keypair on each node:
[crsprd@node1 ~]$ cd
[crsprd@node1 ~]$ mkdir .ssh
[crsprd@node1 ~]$ chmod 700 ~/.ssh
[crsprd@node1 ~]$ /usr/bin/ssh-keygen -t rsa

Create a authorized_keys file from the public keyfile:
[crsprd@node1 ~]$ cd .ssh
[crsprd@node1 ~]$ cat id_rsa.pub >> authorized_keys
[crsprd@node1 ~]$ ls

The result will be 3 files.
One of them is the file "authorized_keys"
Copy the authorized_keys file to the other node(s):
First make sure the .ssh directory exists in the home directory of the user on each node.

[crsprd@node1 ~]$ scp authorized_keys node2:/opt/crsprd/ora/home/.ssh/

On the question to accept the RSA keys, enter Yes:
It will add the remote-node to the local file called 'known_hosts'.
If this file does not exist it is created automatically.

[crsprd@node1 ~]$ scp authorized_keys node2:/opt/crsprd/ora/home/.ssh/

The authenticity of host 'node2 (xxx.xxx.0.181) can't be established.
RSA key fingerprint is 7e:60:60:ae:40:40:d1:a6:f7:4e:zz:me:a7:48:ae:f6:7e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'node1,xxx.xxx.0.181' (RSA) to the list of known hosts
authorized_keys 100% 828 7.5MB/s 00:00

Log on to the other node(s) as the same user en enter the .ssh directory.

[crsprd@node1 ~]$ ssh node2
The authenticity of host node2 (xxx.xxx.0.181) can’t be established.
RSA key fingerprint is z3:z3:33:z3:z3:33:zz:76:z3:z3:z3.
Are you sure you want to continue connecting? (yes/no)? yes

Add the local public key to the authorized keys file:
[crsprd@node2 ~]$ cd .ssh
[crsprd@node2 ~]$ cat id_rsa.pub >> authorized_keys

Repeat this action for every node in the the cluster, until you've added the last node.
Then start copying back this file to every node ( In this example there are only two nodes)
The result must/will be that on every host the authorized_keys file is identical

[crsprd@node2 ~]$ scp authorized_keys node1:/opt/crsprd/ora/home/.ssh/

Now you should have the SSH equivalence setup.


We followed these steps from the Oracle installation manual and then the problems started to begin:

The first problem we encountered, was that we were forced to enter the passphrase for the SSH connection every time, with every command.
This resulted in not being able to install any of the software applications.

Only with assistance of an onsite Oracle consultant, and only after he saw me configuring the SSH equivalence the problem was solved:

Don't enter a passphrase!!

Just press <Enter> when prompted.
Now that was a great thing to discover. We were trying to secure the SSH connection by means of this passphrase where Oracle always uses an empty passphrase ( i.e. according to this consultant).

As soon we had discovered this issue, we were able to start the installation of the CRS and it succeeded completely.

Then however problem two appeared. We were using seperate accounts for CRS, ASM and the Rdbms. In the oracle documentation we used, I never found a statement that when using multiple users all these users should have SSH equivalence seperately.

Okay, okay... afterwards I must say it is quite logical that all users should have this ability. But until we found this out, I was always assuming the CRS ( and therefore the crs owner ) would be responsible for the distibution of the installation files across the nodes.


Wednesday, June 9, 2010

Create RAC database results in PRKP-1001 and CRS-0215

During the installation of a two Node RAC cluster we decided to install CRS and ASM on the latest stable Oracle level ( 11.1.0.7) and for reasons of application compatibility installed the database with release 10.2.0.4

All installation actions went fine. CRS, ASM and the database installation had their own owners and installation directories. All environments were physically seperated.
CRS, ASM and the listener were running OK.
However during the creation of the 10.2.0.4 database we ran into troubles.

Using 'dbca' for the database creation the final steps of starting the just created instance, dbca failed with the
PRKP-1001 and CRS-0215 errors, giving no real clue of what was going on.

Thinking it must be a kind of compatibility problem, we tried all kinds of combinations.
Creating the database with the 10.2.0.4 software, but administrating it with the 11.1.0.7 release. Nothing really worked.
We even tried to install the database software with one of the other owners, just to rule out any possibility of conflicts, but that also didn't work.


After a couple of hours digging through the logfiles and banging our heads against the wall, we solved the problem !!

It turned out the the use of a central TNS_ADMIN location, i.e. the location where the listener.ora and tnsnames.ora resides, wasn't correctly passed through to the running resources. Although 'dbca' was aware of this central location, it failed to update the Cluster resources responsible for the database with this information.

It resulted in the resources looking for listener.ora and tnsnames.ora in $OH/network/admin.
Errors ORA-01078 and ORA-00119 appeared in the logs of the database.
The starting of the cluster resources then was solved by copying the listener.ora and tnsnames.ora to the $OH/network/admin directory.

But that was not the initial goal.
We especially created a central location for these files.

Then finally with help of Oracle support we also solved that challenge:
It is possible to tell the resources afterwards what the TNS_ADMIN location is.
This can be done by setting the environment variable TNS_ADMIN to the correct value for every resource involved, as shown below:

$> export TNS_ADMIN=/opt/oracle/network/admin
$> srvctl setenv db -d -t TNS_ADMIN=/opt/oracle/network/admin
$> srvctl setenv inst -d -i -t TNS_ADMIN=/opt/oracle/network/admin
$> srvctl setenv inst -d -i -t TNS_ADMIN=/opt/oracle/network/admin

Executing these statements from one node only is sufficient, as this information is saved into the Oracle Cluster Registry

Tuesday, June 8, 2010

LOCK_SGA and ASMM

With the introduction of AMM or ASMM as of Oracle 10g the option to lock the SGA into memory has become obsolete. Creating a database using dbca, especially with Oracle 11g, will turn on ASMM. 
By default lock_sga is set to FALSE.

If one favors the use of LOCK_SGA, AMM needs to be disabled. 
To do this, both MEMORY_TARGET & MEMORY_MAX_TARGET needs to be removed from the init- or spfile. 
 For databases started with a plain-text init.ora file, edit the file and simply remove the lines with these two variables. 
If either one does not exist in the file, it doesn't matter, just remove the existing ones. 
Now add a value for SGA_TARGET, and optionally minimum values for the different buffers. 
Finally set LOCK_SGA to TRUE. 
Save the file and bounce the database. 

For databases started with a spfile, follow the below instructions. First an example of how settings can look:

SQL> show parameter memory_target 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ memory_target                        big integer 4544M 

SQL> show parameter memory_max_target 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ memory_max_target                    big integer 4544M 

SQL> show parameter sga_target 
NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ sga_target                           big integer 0 

MEMORY_TARGET is set. MEMORY_MAX_TARGET also. 
This parameter most of time calculates or inherits its value from MEMORY_TARGET. 
SGA_TARGET is set to zero. 

Now execute the following: 

SQL> alter system reset memory_target scope=spfile sid='*'; 

 System altered. 

SQL> alter system reset memory_max_target scope=spfile sid='*'; 
alter system reset memory_max_target scope=spfile sid='*' * 
ERROR at line 1: ORA-32010: cannot find entry to delete in SPFILE 

Most of the times there is no entry for MEMORY_MAX_TARGET in the spfile 

SQL> alter system set sga_target=4544M scope=spfile ; 

System altered. 

SQL> alter system set lock_sga=true scope=spfile; 

System altered. 

Now bounce the database. 
Please note: It might be necessary for RAC systems to repeat the 'reset' statements specifically for each different instance name.

ASM Stripe on Stripe


We have
a two node RAC cluster running on AIX5.3 (TL 10).
It has been the first server running on ASM.
During a performance analysis we have seen that the RAC system with the ASM shows more latency in I/O than it predecessor, which was a DG system on normal AIX file systems.
The ASM is configured with two diskgroups, each existing of multiple disks. The disks are virtual RAW devices, configured through an IBM VIO server and existing on a SAN. The SAN itself stripes its data across multiple disks using a RAID5 solution.
As one knows ASM also uses striping as a method to spread data across the disks of a diskgroup.

In this topic we are going to investigate if the "extra" striping ASM does, has a negative effect on the I/O performance. Before we start the image to the left kind of explains how striping works. Because the disks offered to and used by ASM are virtual, they exist in a striped manner on the real physical disks of the SAN. Any data within the ASM diskgroup is striped also, and is theoretically double striped.

For the test we use a similar system, however not a RAC, configured with the same ASM and RDBMs versions ( 11.1.0.7 ).
On the SAN storage six (6) virtual devices are comfigured. Five (5) disks of 16Gb each forming a striped ASM diskgroup and one (1) disk of 64Gb that forms a diskgroup with just this single disk and therefore no striping. All diskgroups use 'External' redundancy.

On the diskgroup using the 5 disks a database is created, called STRIPEDB.
On the single disk diskgroup a database is created, called NOSTRIPE.
Both databases are identical. Tablespace sizes and memory settings are equal.


First of all: What would we expect the result to be.
a) The striped diskgroup is faster
b) The non-striped diskgroup is faster
c) The speed of both diskgroups is the same

Before I started the tests my bet was on b) or c).
That is because the extra overhead should cause extra wait-time (b) or the SAN cache would overcome everything and there was no noteable difference in I/O througput (c).

The test itself consists of the following steps:
1) Create a 10Gb tablespace
2) Create a 20Gb tablespace
3) Create a 40Gb tablespace with two datafiles of 20Gb each
4) Import a medium database (18Gb dumpfile) dump into the empty databases

Of all the steps either the SQL-timings and/or load timewill be measured.


The results:
At first hand the results where somehow dissapointing to me. The creation of both the 10Gb and 20Gb tablespaces was seconds faster on the striped diskgroup. The difference was not large:

10Gb tablespace: 1:09 for the striped diskgroup and 1:11 for the non striped diskgroup
20Gb tablespace: 2:12 for the striped diskgroup and 2:13 for the non striped diskgroup

The creation of the 40Gb diskgroup however gave better results. On the striped diskgroup this took 4:41 minutes. On the non-striped diskgroup it only took 4:17 minutes, so almost half a minute faster !!

Currently the large tests loading the dumpfiles are still running.


The first one, loading a 18Gb dump into the striped database took almost 9 hours to complete. To be precise, it took 8:56 hrs.

The second import totally surprised me. It took an astonishing 14 hours to complete.

I'm still wondering if the cause should be somewhere else. My feeling about the extra overhead that stripe on stripe has, should have made I/O performance worse.
But it doesn't !!
It doubles the I/O performance, which will ofcourse be dependent on the number of disks in the striped diskgroup.


Reason for this difference: The UNIX/SAN guys guessed for either the SAN cache, and/or the stripesize and layout must be a very good match. But anyway writing in parallel to 5 disks must be faster anyway than writing to a single disk, allthough all I/O is virtual.