Managing Oracle

Oracle Enterprise Manager Packs Enabled by Default

2011-12-29T09:36:00.003+01:00

When you've installed Oracle Enterprise Manager Grid Control and you have configured Management Agents for database monitoring, all your databases will show up in the Grid Control application.

We have found a 'glitch' in this behaviour that in my opinion is very misleading.

When a database target is added/discovered in Grid Control, all possible Packs are enabled by default.
This means that if you're running Standard Edition databases, you're violating the SE-license using e.g. the Diagnostic Pack as soon as your database is discovered in Grid Control.

Make sure that you reconfigure the Management Pack Access immediately after a database target is discovered. Pack usage is registered in the database, so when a license check may occur, the usage is shown/seen in the database.
The "Management Pack Access" option can be found through the "Setup" option in the top-right of the screen.

We have not been able to find a way to disable this behaviour where the packs are enabled by default.
If anyone has an idea or a tip, please let me know.

Oracle printing problem

2011-12-02T08:54:00.001+01:00

A printing problem with Oracle ??

Now I must dissapoint you all, if you really have some kind of printing problem related to an Oracle database installation.
This blog is about something totally different.
It handles about a printing problem and it is related to Oracle, but it is not technically related to Oracle.

The other day I was printing an Oracle manual about ASM, the Oracle 11g Storage Administration Guide. After having printed half of the document, the printer paused for a couple of seconds then went on printing.
When removing the document I detected that after the point where it had paused, the document was malformed. That is, the characters where unreadable, but the layout of the document was correct.
The image below shows how one page of the document looked like:

At first glance this page looks quite normal.
However, as said above it contains unreadable information.

But in fact it isn't. It is not quite unreadable.
It is a very consequent printer hick-up. The printer did something wrong and continued this until the end of the document.

When I reprinted the failed part, I discovered what went wrong.

Because the layout of the document was exactly correct, compared to the same reprinted page, we started looking at the page more carefully.

The first thing we noticed was the page number. On the correctly printerd (original) page this page should have been page "6-7". On the malformed document however, the page number was "7.8"

If you translate this to ASCII table values the correct page number is represented by "36-2D-37". the malformed page number is represented by "37-2E-38".

Here is is quickly seen that all characters in the document have been replaced by the next character in the ASCII table. So an "A" is replaced by a "B" an "M" by an "N" etc..etc..
Funny to see, is that the layout was preserved and also the character spacing of the document. So however in the malformed document e.g an "n" was printed, it used the character space of the letter that should have been printed, the "m".
This is clearly seen in the last blue item, starting with "Ben jojt.....", what should have read "Adminis...".

For those who would like to see the original page, I've included it below.

Finally I would like to say, that it is just a printer hick-up, nothing related to Oracle whatsoever, but since I've never seen something strange like this in my whole IT career, I thought it was funny to share.

Tablespace reorganization

2011-11-28T10:01:00.001+01:00

In our datawarehouse we often run into the "problem" that tablespaces contain a lot of free space within the tablespace itself. Most of the time, this is caused by the fact that the warehouse programs first build up new objects and afterwards destroy old objects.
In the future we might need to have a more definitive solution for this, like converting the tablespaces to one with an uniform size.

However until then I use to do a tablespace reorganization. This means I pick a block_id of the tablespace datafile, somewhere around the point of total usage.

For this procedure to work, you need a tablespace that can temporary store the objects that lie beyond the free space in the tablespace you want to reclaim. This temporary store tablespace should NOT be an oracle temporary tablespace, but just a permanent tablespace that temporary can hold these objects.
It is preferred to create a seperate tablespace for this, as this can afterwards be removed again.

How the reorganizing works:
E.g If we have a tablespace of 10Gb in size and its datafile ( let's assume we have a single file ), has 4Gb of free space, I use the following query to determine the upper-bound block_id:

SQL> select /*+ rule */
max(block_id)*0.7
from dba_extents a,
where a.tablespace_name='USERS' ;

If a tablespace has more datafiles, then you need to specify a certain datafile by adding the the file_id column in the query. In that case first look up the datafile number by joining v$tablespace and v$datafile.

After the block_id has been calculated, I use the following complex query to generate table move and index rebuild statements:

set pages 9999 lines 200
accept "Reorg tablespace : " &tabsource;
accept "Temporary store tablespace (not TEMP) : " &tabtemp;

select distinct cmd from
(SELECT /*+ rule */
'alter '
|| DECODE (segment_type
, 'TABLE SUBPARTITION', 'TABLE'
, 'TABLE PARTITION', 'TABLE'
, 'INDEX', 'INDEX'
, 'INDEX PARTITION', 'INDEX'
, 'TABLE','TABLE'
, 'TABLE' -- TABLE is the default
)
||' '
||owner
||'.'
|| segment_name
|| ' '
|| DECODE (segment_type, 'INDEX', 'REBUILD','INDEX PARTITION','REBUILD','MOVE')
|| ' '
|| DECODE (segment_type
, 'TABLE SUBPARTITION', 'SUBPARTITION'
, 'TABLE PARTITION', 'PARTITION'
, 'INDEX PARTITION','PARTITION')
|| ' '
|| partition_name
|| ' tablespace '
|| &tabtemp
|| DECODE (segment_type, 'INDEX', ' ONLINE','INDEX PARTITION',' ONLINE')
|| ';' cmd
FROM dba_extents
WHERE tablespace_name = &tabsource
--AND FILE_ID=10
AND block_id > [Calulated Block_size]) ;

In the above query the use of FILE_ID is commented out. This because we assumed that we have only one datafile. Otherwise enter the file_id that you've found in v$datafile;

The result of this query is a lot of object-rebuild statements, that move tables and tablepartitions to the tablespace defined by &tabtemp. The indexes and index-partitions are online rebuild in the same tablespace.

Remember to SAVE the script, as you need it to move everything back to the original tablespace.

Run the script outside business hours, as moving table objects will lock the tables, invalidate indexes and all dependent packages/procedures.

As soon as the script is finished, purge the tablespace of its 'recyclebin' contents, by issuing as SYS:

SQL> purge tablespace USERS;

Afterwards the tablespace datafile can be shrunk to the desired size, preferably subtracting the size of the empty space in the beginning from the total datafile size.
As soon as that is finished, reopen the script you saved, edit it and change back all tablespace names, to the original source tablespace ( in this example USERS ).
Then rerun the script .

Finally use UTLRP to rebuild all invalidated packages/procedure and indexes. Also, if you created a temporary tablespace for this procedure, drop that again.

In our databases, this procedure usually frees lots of free space. Last week also I was able to return 29Gb of free space back to the OS.

Copyright: This script can be freely used. If you use it as an explanation on your own site/blog, a link to the source (my blog) is appreciated.

Which rights are Granted PUBLIC

2011-11-08T15:40:00.001+01:00

During a schema copy action from an old database to a new one, we ran into problems that certain packages where not compiled successfully.
Some dependencies failed, because the schema user in the new database failed to have execution priviliged on certain SYS packages, like DBMS_LOCK.

The settings of both schema's where identical, so the rights must have been set in the past by using the PUBLIC account.
For those who don't know, the PUBLIC role is a role used for setting database global rights, which every user is allowed to have.

What I needed was a list of GRANT statements for the PUBLIC user that existed in the "old" database but not (yet) in the "new" database.

I started browsing the usual tables like DBA_ROLE_ROLES, DBA_ROLES, DBS_ROLE_PRIVS, but nowhere I could find the right for the PUBLIC role.

After spending almost an hour on Google, I came accross a set of views called KU$_ROGRANT_VIEW, KU_SYSGRANT_VIEW and KU$_OBJGRANT_VIEW

Especially the last one contained the information I was looking for. This table contains columns like 'GRANTEE', 'OBJ_NUM' and the privilege name 'PRIVNAME'.
Joining this table with DBA_OBJECTS, using the below query, resulted in -at least- the list with all EXECUTION grants for the PUBLIC role.
I was looking for these EXECUTION grants, because the uncompilable packages complained about dependencies, not about missing tables.

The statement:

SQL> SELECT 'grant '
|| a.privname
|| ' on '
|| b.owner
|| '.'
|| b.object_name
|| ' to '
|| a.grantee
|| ';'
FROM KU$_OBJGRANT_VIEW a, dba_objects b
WHERE grantee = 'PUBLIC'
AND a.obj_num = b.object_id
AND a.privname='EXECUTE'
AND b.object_name not like '%/%'
order by b.object_name;

Using this statement on both the "old" and "new" database I was able to create a list of missing GRANT statements (there were 10 of them) that had to be executed on the "new" database.

Afterwards all packages were compiled successfully.

Unregister RMAN database directly from catalog DB

2011-10-07T10:14:00.002+02:00

What DBA never moved databases to new hardware ??
And what DBA never forgot to unregister the database first from the RMAN catalog ??

If you didn't unregister the database after a database move, then at those crucial moments, months or years later, you might end up facing a problem when trying to e.g. restore that database from backup.

Imagine you're in the middle of disaster, the production database server crashed, management is breathing down your neck, and you need to restore this database as soon as possible.
You prepare a new server, install Oracle on it and immediately start the restore, but then disaster strikes again:

RMAN> run {
2> allocate channel ch0 device type 'sbt';
3> restore controlfile;
4> }

allocated channel: ch0
channel ch0: sid=147 instance=hkdrt1 devtype=SBT_TAPE
channel ch0: Veritas NetBackup for Oracle - Release 6.5 (2009050106)

Starting restore at 2009-DEC-03 11:57:20

released channel: ch0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/03/2009 11:57:20
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20005: target database name is ambiguous

The restore fails, because RMAN does not know which of the databases known in the recovery catalog with the given SID needs to be restored.
You should have unregistered the old copy of the database years back, when you moved the database to that "new" server which now is totally dead.
You should have -maybe- tried a restore back then to make sure it worked !!

But hey, even with the management in your back everything now goes smooth.
You have this blog guide to follow:

First of all:
Following this blog, you are directly administrating the RMAN catalog. Make sure you only follow the below procedure, do not manually delete records from any of the tables, or change any of the data, as you corrupt the catalog in doing so, rendering it totally useless

How to continue?

We are going to use a database package that is installed in this catalog database. The package is called dbms_rcvcat and it has a procedure unregisterdatabase that cleans out all information about a database by providing a database key (db_key) and a database id (db_id).
The statement to do this looks like:

SQL> exec dbms_rcvcat.unregisterdatabase(db_key,db_id);

Now we only need to find these db_key and db_id values.
First connect to the database which contains the RMAN catalog using whatever SQLplus program you like.
Find in the catalog the list of databases know to the catalog with this SID:

SQL> select db_key, db_name,
reset_time, dbinc_status
from dbinc where db_name = 'YOURSID';

The result is a list of registered databases with this SID name.

DB_KEY	DB_NAME	RESET_TIME	DBINC_STATUS
411792	YOURSID	7-11-2002 14:29:22	PARENT
411792	YOURSID	24-10-2005 13:40:18	CURRENT
7455112	YOURSID	10-10-2008 21:41:20	CURRENT

In this example a couple of records are shown for the same SID. If correctly configured a database has only one CURRENT record in the catalog. A CURRENT record represents a database that is - according to the RMAN catalog - currently an active representation of a running database.
Since however a database can have only one CURRENT incarnation the one with the most recent RESET_TIME is the actual CURRENT database registration.
A PARENT incarnation is a database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation. It is therefore not always the case that a PARENT incarnation exists.

In the above example the database registration with DB_KEY=7455112 seems to be the actual current database, as this one has the most recent reset_time.
This means we are going to unregister the database with the db_key equal to 411792;

Now we only need to find the DB_ID that belongs to this database:

SQL> select db_key, db_id
from db where db_key= 411792;

The result will look like:

DB_KEY	DB_ID
411792	412102591

Now that we know the DB_KEY and the DB_ID, we can use the above mentioned procedure to unregister this database from the catalog:

SQL> exec dbms_rcvcat.unregisterdatabase(411792,412102591);

Don't worry, this statement can run for minutes ( up to 10-15 minutes on our system )!!

Afterwards re-run the select from the DBINC table again, and you'll see that the database registration has been removed from the catalog.
If a PARENT record existed for this DB_KEY, that record has also been deleted.

Conclusion:

The above procedure enables you to successfully clean out ( unregister ) databases from the RMAN catalog that do no longer exist.
This procedure also is a very risky procedure. Especially if one does now know what he is looking at, and expecially if the management team is breathing down your neck.
If you're in a disaster situation, where an immediate restore of a production environment is wanted, it is not the good time to do these kind of exercises.
If and when you enter by mistake the DB_KEY and DB_ID of the latest CURRENT incarnation ( the database you need to restore ) and you accidently press [Enter], then immediately start thinking about another job !!
Any restore of that particular database by means of RMAN then is no longer possible!!

ORA-27123: Unable to attach a shared memory segment

2011-09-29T14:23:00.004+02:00

This morning we had another brainteaser starting one of our databases.

The situiation was as following:

We have TEST/DEV server with a single Oracle 9.2.0.8 installation on it, installed as user "oracle"

Next to that there are several application users, called appt01, appt02, appt03 and appt04, all using this same ORACLE_HOME for their database.

All databases were running fine this morning.

We shutdown the appt01 to install a patch.

When trying to restart the database, suddenly this error appeared:

SQL> startup
ORA-27123: Unable to attach a shared memory segment
IBM AIX RISC System/6000 Error: 13: Permission denied

Using Google and MOS docs (115753.1 &167250.1), all information pointed to the incorrect file permissions of the "oracle" binary where the sticky bit has to be set. However this could be possible, because the patch installation rebuild this binary, it felt as not the correct reason. The file permssions were correct when looking at the file:

oracle@devrsv1% cd$ORACLE_HOME
oracle@devrsv1% ls -l ./bin/oracle
-rwsr-s--x 1 oracle dba 70518002 Sep 29 12:09 ./bin/oracle

Investigating even more it was soon discovered that all other three database were able to stop and restart as expected. They showed no problems at all, even though they use the same ORACLE_HOME and settings.

After hours of searching and looking at the system, I finally turned to the OS.
Because the error message showed something about shared memory, I started investigating into that direction.
As user root, we executed:

# ipcs -mob

This statement usually shows all active shared memory segments, their sizes and the owners of the segments. In our case, only the output header appeared:

IPC status from /dev/mem as of Thu Sep 29 14:17:01 DFT 2011
T ID KEY MODE OWNER GROUP NATTCH SEGSZ

Now this showed that something was really wrong with the OS shared memory administration.
The other three databases were running, but also from these three no shared memory was shown, not even mentioning the standard OS shared memory pools.

We then decided to restart the complete server. And that solved the problem.
After the restart all databases started again as expected. Also the shared memory segments were correctly shown.
So even the error message displayed, usually points into the direction of the 'sticky bit' of the oracle binary, the problem can also be caused by a total shared memory problem of the system, caused by whatever reason.

Database Index administration

2011-09-28T13:49:00.000+02:00

I would like to share some PL/SQL code that enables us DBA's to do index maintenance. In our data warehouse there were a couple of procedures that saved very basic index information.
Then the indexes where dropped and after the warehouse load they were recreated.
The pitfall of these simple procedures was that they were not able to cope with partitioned indexes and other complex structures. These more complex indexes were rebuild as normal non-partitioned indexes afterwards.

To be able to also rebuild partitioned and other index structures I rewrote the complete procedure, making effective use of the DBMS_METADATA package available in the SYS account.

This DBMS_METADATA package has a function called GET_DDL that is able to return the DDL of any object in the database, like tablespaces, tables, indexes and others.
If you e.g. would want to know the DDL of the EMP table, simple use the following query:

SQL > select dbms_metadata.get_ddl ('TABLE','EMP') from dual;

Ofcourse the above statement needs to be executed as the owner of the table. If you want to execute it as SYS then you must supply the function with the table owner, like:

SQL> select sys.dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

The result is a CLOB variable containing the DDL to (re)create the EMP table of user SCOTT:

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10 CHAR),
"JOB" VARCHAR2(9 CHAR),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_VALID_JOB" CHECK (job in ('CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT')) ENABLE,
PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 1017
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 1017
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"

The procedures I wrote uses a simple table for administration purposes. This table exists of four columns:
INDEX_NAME of type VARCHAR(30)
TABLE_NAME of type VARCHAR(30)
QUERY of type CLOB
STMT_ORDER of type NUMBER(3)

The first three column are quite self-explaining. They contain the table_name, and index_name that is being dropped, and of course the query that is needed to recreate the index afterwards.
The last column STMT_ORDER determines the order of queries. This is important e.g. when creating primary-key indexes with extra constraints. The primary key needs to exist, before a constraint can be added to it.

The DDL structure of this table looks like:

CREATE TABLE WBX_DDL_INDEXES
(
INDEX_NAME VARCHAR2 (30 CHAR)
, TABLE_NAME VARCHAR2 (30 CHAR)
, QUERY CLOB
, STMT_ORDER NUMBER (3)
)
LOB (
QUERY)
STORE AS
(
TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
NOCACHE LOGGING)
TABLESPACE USERS
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX WBX_DDL_INDEXES_PK
ON WBX_DDL_INDEXES (INDEX_NAME, TABLE_NAME, STMT_ORDER)
NOLOGGING
TABLESPACE TOOLS
NOPARALLEL;

ALTER TABLE WBX_DDL_INDEXES ADD
( CONSTRAINT WBX_DDL_INDEXES_PK PRIMARY KEY
(INDEX_NAME, TABLE_NAME, STMT_ORDER)
USING INDEX WBX_DDL_INDEXES_PK ENABLE VALIDATE);

There are two procedures written, the first responsible for dropping a given index, the second for recreating it. Note that the procedure take a table_name as argument, where one would maybe expect a table_name, index_name combination.
However, since we are using this is a large datawarehouse, all indexes of a given table are dropped. We therefore don't need to specify the index_name.
The "DROP_INDEX" procedure looks like:

procedure drop_index
(p_table in varchar2
)
is
q clob;
l_count number(3);

-- Use a cursor with a complex statement to determine all indexes and their types
cursor c1(p_table varchar2)
is
select ui.index_name, ui.table_name, ui.index_type, ui.uniqueness, uc.constraint_type
from user_indexes ui
left outer join user_constraints uc
on ui.index_name=uc.index_name
and ui.table_name=uc.table_name
where ui.table_name=p_table
and ui.index_type <> 'LOB'
order by uc.constraint_type;
begin
l_count := 1;
for r1 in c1(p_table)
loop
if r1.constraint_type = 'P'
then
q := sys.dbms_metadata.get_ddl('CONSTRAINT', r1.index_name);
begin
insert into wbx_ddl_indexes (index_name, table_name, query, stmt_order)
values ( r1.index_name, p_table, q, l_count);
exception
when dup_val_on_index then
null;
end;
-- if the index is a primary key, drop it using an "ALTER TABLE.."
execute immediate 'alter table '||p_table||' drop primary key';
else
q := sys.dbms_metadata.get_ddl('INDEX', r1.index_name);
begin
insert into wbx_ddl_indexes (index_name, table_name, query, stmt_order)
values ( r1.index_name, p_table, q, l_count);
exception
when dup_val_on_index then
null;
end;
-- if the index is a normal index, use the "DROP INDEX" statement
execute immediate 'drop index '||r1.index_name;
end if;

l_count := l_count+1;
commit;
end loop;
end;

The "CREATE_INDEX" procedure looks like:

procedure create_index
(p_table in varchar2
)
is
q clob;
exec_q varchar2(32767);
l_index varchar2(30);
l_length number(10);
-- Use a cursor to go through all saved index statements
cursor c1(l_table varchar2)
is
select index_name, query,stmt_order
from wbx_ddl_indexes
where table_name=l_table
order by stmt_order asc;

begin
for r1 in c1(p_table)
loop
begin
select index_name
into l_index
from user_indexes where table_name=p_table and index_name=r1.index_name;

exception
when no_data_found then
-- Index does not exist, create it
select query into exec_q
from wbx_ddl_indexes
where index_name=r1.index_name
and table_name=p_table
and stmt_order=r1.stmt_order;

execute immediate exec_q;
end;

-- As soon as the index is created, update the administration
delete
from wbx_ddl_indexes
where index_name = r1.index_name
and table_name=p_table
and stmt_order=r1.stmt_order;

commit;
end loop;
end;

It is too much to exactly explain the procedure here themselves. Anyone who can read PL/SQL code should be able to understand the procedures.

Feel free however to post questions or send me an Email.

Database SAN interactions

2011-07-07T16:41:00.012+02:00

I would like to address a problem we are seeing on our systems, that is totally impossible from the viewpoint of SAN engineers.

The situation:

We have two Oracle databases.

The first one is an Oracle 10.2.0.4 databas

e, running on a Logical Partition (LPAR) of an IBM pSeries-7 server. The storage of this database is located on an IBM DS6800 SAN box, connected by Fiber cards to this server

The second database is an Oracle 11.

1.0.7.4 two node RAC database, running on two physically seperated IBM pSeries-6 servers. The storage of this database is in ASM, however located on the same IBM DS6800 SAN Storage server.

So, we have two database, totally physically seperated from each other. The only part they do share is the SAN box.

Now the problem:

If we put load on database 1 ( this is a Datawarehouse database ), database 2 starts suffering from it. Let's have a look the graphs to see what I mean:

The load on database 1:

The effect on database 2:

(Please Note that the graphs are not perfectly aligned in this blog)

The effect is thus that if database 1 is especially busy with LGWR ( system I/O ), database 2 starts to show I/O waits, seen as more and more active sessions with the dark-blue I/O color in Grid Control. The effect stops as soon as database 1 reduces (finishes) its LGWR activity.

Another strange effect we've seen is coming from another database "database3". This database is yet on another server (IBM pSeries-5), but also has its database on the same DS6800 storage server.

This database, an "old" 9.2.0.8 database, used to do a forced log-switch every 15 minutes, by means of a crontab script. This forced log-switch showed up as spikes in Grid Control of this database ( the orange "commit" color).

Again these spikes also reflect themselves as I/O waits on the RAC (database 2).

Even if we changed the execute time in the crontab of database 3, the spikes on database 2 moved accordingly. Because the application running on the RAC is our Webshop, suffers badly from these I/O waits, we turned these forced log-switches of.

Now, has anybody of you DBA's or Storage guys out there seen this kind of behaviour. The storage guys here at the company can't believe that database affect each other like this by means of the SAN. The SAN has a huge cache, it is tuned, etc...etc.., so theoretically this kind of behaviour is not possible.

In my opinion the LGWR "log-sync" actions cause the storage server to wait for a synchroneous action from the database, thus freezing or delaying all other actions with it.

Any other ideas are welcome. Solutions of course are even more welcome

Thanks

Add On: As of this week ( oct. 5th 2011 ), the storage of database 2 has been moved to a newly purchased SAN storage, totally isolating it from the other storage ( and thus the othter databases).
The inheritence effect now is gone !!

Grid Control 11g screens freeze

2011-05-03T11:12:00.003+02:00

Last year we installed Oracle Enterprise Manager Grid Control 11g.

The main reason was the fact that we were (still) on version 10.2.0.3 and we experienced some troubles with that version. Also Oracle anounced the problem with this version regarding the end-of-life of the SSL-certificate on Dec 31th 2010.

The installation went very smoothly. See also my other posts about this subject.

The only problem we encountered with this new version was that the 'Performance Graphs' froze after a database restart of one of the monitored databases.

It is expected that there is no response anymore for the graph to build up, after a database shutdown, but you would expect that the graph just shows nothing, but keeps advancing in time.

This was not the case.

Also after the target database was restarted, the graph still kept showing the status before the shutdown. Nothing could be done to get over this, except a complete restart of Grid Control.

After such a complete restart, the graph refreshed and started showing real-time information again.

We started a Service Request at Oracle Support for this and thanks to DITO ( Demo It To Oracle) we were able to clearly show to Oracle support what happened. With CamStudio I recorded a complete movie about the problem and send it to Oracle Support.

They immediately recognized the problem as being bug 10307099 and supplied a patch for it.

This solved the problem.

I was very happy to see the power of CamStudio to help me in clearly showing/explaining the problem to Oracle Support. This encouraged me to use it in other cases as well. And it is free !!!

After node restart RAC cluster does not start

2011-03-21T10:37:00.004+01:00

Today we have been very busy trying to figure out why after a system restart the RAC nodes didn't want to start the cluster stack anymore.

The biggest problem we had was there was absolutely no logging at all !!

The system seemed totally not startable.

All checks to get the cluster online where -in our opinion- successfull.

We were able to create an ocrdump, and query the voting disks.

Finally we started to dig into the processes that were running.

We found a process called:

/etc/init.cssd startcheck

This process seemed to hang. Waiting for something.

Looking into the script we read that this function checked for all needed resources to be available, and as long as they were not available, went for a sleep of 60 seconds.

We detected it used the AIX logging system, of which we however did not "catch" the logging messages by means of the syslog.conf file.

After a while we decided to start a "startcheck" of our own, using the debug options op de Korn shell:

# ksh -x /etc/init.cssd startcheck

The result was that it showed that there was some logging in files in /tmp calles cssxxxx where xxxx is a numeric value.

Looking into the last one of these files, it showed that the votingdisks where missing.

This is contradiction to the

# $CRS_HOME/bin/crsctl query votedisk

which still showed everything was fine with the voting disks.

The real problem was in the fact that the 'crsctl' command, just looks for the device files to be present and to be readable.

If there were/are actually disks attached to these device files was not checked.

It turned out that a major network problem we've had two days earlier disturbed something on the SAN network, making it not possible anymore to connect the logical drives to this server.

What I totally do not like here is the total lack of any logging in the usual logging location of CRS, being $CRS_HOME/log/.

Maybe in future releases ( we are using 11.1.0.7) this problem is better handled.

Copying Archived Logfile from ASM to a file system

2011-01-20T10:35:00.002+01:00

Following OTN tread http://forums.oracle.com/forums/thread.jspa?threadID=2154801&tstart=0 about copying archived logfiles from ASM to a UNIX filesystem, I am posting a script we used in the past.

Nowadays we use RMAN to get the files from ASM directly into a TSM diskpool, but back then we used another method for putting the archived logfiles on backup, which resulted in the need for this script:

# Script to copy archived logfiles from ASM to a file system on UNIX

# In this case all archived logfiles found in v$archived_log are copied to a staging mount point /opt/asmprd/ar01

# The username/password used to access the database need to be set before use of this script.

# For sake of security I removed these lines from this script

# The script needs to be run as owner of the ASM instance

export ORACLE_SID=WCSPRD

export FSFULL=FALSE

export ORACLE_BASE=/opt/$LOGNAME/ora

export CRS_HOME=/opt/crsprd/ora/11.1.0

export ORA_CRS_HOME=$CRS_HOME

export ASM_HOME=/opt/asmprd/ora/11.1.0

export ORA_ASM_HOME=$ASM_HOME

export ORACLE_HOME=$ORACLE_BASE/11.1.0

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$CRS_HOME/lib:$ORACLE_HOME/lib32:$CRS_HOME/lib32

export LIBPATH=$LD_LIBRARY_PATH

export PATH=$ORACLE_HOME/bin:$CRS_HOME/bin:$PATH

export TNS_ADMIN=/opt/oraadmin/network/admin

export ORACLE_SID=+ASM

export HOST=`hostname`

export LOG_FILE=/var/adm/log/StageArchives_${HOST}.log

export A_FILE_COPIED=FALSE

# Optionally turn Debug on to see what the script does

export Debug=false

# Functions

Log() {

echo "`date +%Y/%m/%d` (`date +%H:%M:%S`) : $1" | tee -a $LOG_FILE

} #End Log

Continue()

{

echo "Continue....? [n]\c"; read ans

case $ans in

y|Y) echo "\n\nContinuing...."

break;;

*) exit 0

break;;

esac

unset ans

}

GetArchiveList()

{

echo "set termout off heading off pages 9999 lines 70 feedback off" > /tmp/getArchives.$$.sql

echo "column name format A70;" >> /tmp/getArchives.$$.sql

echo "spool /tmp/archivelist.lst;" >> /tmp/getArchives.$$.sql

echo "select trim(name) from v\$archived_log where deleted='NO' and archived='YES';" >> /tmp/getArchives.$$.sql

echo "quit;" >> /tmp/getArchives.$$.sql

$ORACLE_HOME/bin/sqlplus -S ${WCSPRD_SYS}@WCSPRD as sysdba @/tmp/getArchives.$$.sql

if [ "$Debug" = "true" ]

then

cat /tmp/getArchives.$$.sql

Continue

rm -f /tmp/getArchives.$$.sql

}

StoreArchivesInFS()

{

ArchName=$1

ArchShortName=`basename $1`

ArchDest=/opt/asmprd/ar01/${ArchShortName}

AvailFS=`df -gv /opt/asmprd/ar01 | grep -v Free| awk '{print $5}' | cut -d'%' -f1`

Log "Archiving $ArchDest"

if [ "$Debug" = "true" ]

then

echo "AvailFS = $AvailFS"

echo "Commando = $ASM_HOME/bin/asmcmd cp $ArchName $ArchDest"

Continue

if [ $AvailFS -lt 90 ]

then

echo "$ASM_HOME/bin/asmcmd cp $ArchName $ArchDest"

$ASM_HOME/bin/asmcmd cp $ArchName $ArchDest >/dev/null 2>&1

if [ -f "$ArchDest" ]

then

export A_FILE_COPIED=TRUE

$ASM_HOME/bin/asmcmd rm $ArchName >/dev/null 2>&1

Log "$ArchName has been deleted from ASM."

else

echo "ArchDest=$ArchDest"

ls -l $ArchDest

else

export FSFULL=TRUE

}

RmanCrosscheck()

{

# Crosscheck and clean repository

#Log "Crosscheck and clean repository"

$ORACLE_HOME/bin/rman target ${WCSPRD_SYS}@WCSPRD rcvcat ${TSMORA_TSMCAT}@tsmora cmdfile StageArchives.cmd

}

# Main Program

PROG_PID=$$

RUNNING=`ps -ef| grep StageArchives.sh | grep -v ${PROG_PID} | grep -v grep | awk '{print $2}'`

if [ -n "$RUNNING" ]

then

Log "Old Running session(s) found with PID(s): $RUNNING "

ps -ef| grep ${RUNNING} | grep -v grep >> $LOG_FILE

for tpid in $RUNNING

{

kill $tpid

}

GetArchiveList

cat /tmp/archivelist.lst | while read ARCHFILE

if [ ! "$ARCHFILE" = "" ]

then

StoreArchivesInFS $ARCHFILE

if [ "$FSFULL" = "TRUE" ]

then

# Insert some action here to clean up the destination file system

done

# Use Rman to update the recovery catalog

RmanCrosscheck

unset FSFULL ARCHFILE

#rm -f /tmp/archivelist.lst

The script contains a call to 'rman' using the command file 'StageArchives.cmd'

This file looks like:

allocate channel for maintenance type disk;

crosscheck archivelog all;

delete noprompt expired archivelog all;

quit;

Please note that I am not able to give any support to anyone who wants to use this script.

You are allowed to copy and use it, but for the remainder you're on your own.

Success!

Installing Oracle Agent11g

2011-01-11T14:09:00.018+01:00

In this thread the installation of Oracle's Management Agent 11g for Grid Control 11g is explained.

The agent installation is not a manual install anymore. Oracle has decided to only provide in silent installs.

The available options are:

- Push install from the OMS server ( requires ssh to be configured on the client )

- Pull install from the client ( requires wget to be installed )

- Silent install from an Agent download.

Unfortunately the first two options are not suitable for AIX servers. With a push install from the OMS server, the push installer tries to start a ssh-session on the server to be installed, using the ssh binary in a Linux style path ( /usr/local/bin/ssh ).

The second installation type is also unfortunately more Linux minded. Wget, the Linux source download tool, is by default not available on AIX.

The last method is the easiest, because no extra software ( ssh, wget) needs to be installed or configured.

You can download the Agent11g installer from:

http://www.oracle.com/technetwork/oem/grid-control/downloads/agentsoft-090381.html

After downloading, FTP the agent 11g install file to the client to be installed.

Unzip it into a temporary directory.

Create an Installation response file, with the contents like below. A sample configuration file can be found in /aix/response.

The to be changed values are marked in bold-white.

####################################################################

## ##

## Specify values for the variables listed below to customize ##

## your installation. ##

## ##

## Each variable is associated with a comment. The comment ##

## identifies the variable type. ##

## ##

## Please specify the values in the following format: ##

## ##

## Type Example ##

## String "Sample Value" ##

## Boolean True or False ##

## Number 1000 ##

## StringList {"String value 1","String Value 2"} ##

## ##

## The values that are given as need to be ##

## specified for a silent installation to be successful. ##

## ##

## This response file is generated by Oracle Software ##

## Packager. ##

###################################################################

RESPONSEFILE_VERSION=2.2.1.0.0

#################################################################################

#Inputs for Oracle Configuration Manager

#################################################################################

#-------------------------------------------------------------------------------

#SECURITY_UPDATES_VIA_MYORACLESUPPORT: Whether security updates are reqi-

# uired via My Oracle Support

#DECLINE_SECURITY_UPDATES: Whether security updates should be declined.

#MYORACLESUPPORT_USERNAME & MYORACLESUPPORT_PASSWORD: User name and passw-

# ord for My Oracle Support access, these will be effected only when SECU-

# RITY_UPDATES_VIA_MYORACLESUPPORT=true

#COLLECTOR_SUPPORTHUB_URL: If there is not direct connection and Support

# hub is deployed. Need to specify that URL.

#-------------------------------------------------------------------------------

SECURITY_UPDATES_VIA_MYORACLESUPPORT=False

DECLINE_SECURITY_UPDATES=True

################################################################################

#PARAMETERS FOR SOFTWARE UPDATES

################################################################################

#-------------------------------------------------------------------------------

#INSTALL_UPDATES_SELECTION:

# Option 1. If you want to skip the software updates, provide

# INSTALL_UPDATES_SELECTION="skip"

# Option 2. If you have already downloaded the updates then provide

# INSTALL_UPDATES_SELECTION="staged"

# If you choose the Option 2 then make sure you also provide STAGE_LOCATION.

#STAGE_LOCATION: Stage location for software updates. It will be effected

# only when INSTALL_UPDATES_SELECTION is set to "staged"

#-------------------------------------------------------------------------------

INSTALL_UPDATES_SELECTION="skip"

STAGE_LOCATION=

################################################################################

#PROXY DETAILS FOR SECURITY UPDATES/ SOFTWARE UPDATES

################################################################################

#-------------------------------------------------------------------------------

#PROXY_USER: User name for proxy access.

#PROXY_PWD: Password for proxy access.

#PROXY_HOST: Server providing proxy access.

#PROXY_PORT: Port for proxy access.

#------------------------------------------------------------------------------

PROXY_USER=

PROXY_PWD=

PROXY_HOST=

PROXY_PORT=

################################################################################

#Various inputs required for Installation and Configuration

################################################################################

#-------------------------------------------------------------------------------

#ORACLE_AGENT_HOME_LOCATION: The location of oracle agent home.

#Agent home "agent11g" would be created under the directory specified by

#ORACLE_AGENT_HOME_LOCATION.

#-------------------------------------------------------------------------------

ORACLE_AGENT_HOME_LOCATION=/opt/wcst01/ora/

#-------------------------------------------------------------------------------

#DEINSTALL_LIST: List of components to be deinstalled during a

# deinstall session.The following choices are available. The value should

# contain only one of these choices.The choices are of the form

# Internal Name, Version : External name. Please use the internal name and

# version while specifying the value.

# oracle.sysman.top.agent, 10.2.0.0.0 : Oracle Management Agent 10.2.0.0.0

# oracle.sysman.top.em_seed, 10.2.0.0.0 : Oracle Enterprise Manager Repository Database 10.2.0.0.0

# oracle.sysman.top.oms, 10.2.0.0.0 : Oracle Enterprise Manager Grid Console 10.2.0.0.0

#-------------------------------------------------------------------------------

DEINSTALL_LIST={"oracle.sysman.top.agent","11.1.0.1.0"}

#-------------------------------------------------------------------------------

#b_silentInstall: The user should not change this value

#CLUSTER_NODES: This variable represents the cluster node names

# selected by the user for installation.

#-------------------------------------------------------------------------------

b_silentInstall=true

CLUSTER_NODES=vtl1h121ps,vtl1h123ps

#-------------------------------------------------------------------------------

#TOPLEVEL_COMPONENT: The top level component to be installed in the

# current session.Value should contain only one of the following available

# choices.The choices are of the form Internal Name, Version : External name.

# Please use the internal name and version while specifying the value.

# oracle.sysman.top.agent, 10.3.0.0.0 : Oracle Management Agent 10.3.0.0.0

# Example: TOPLEVEL_COMPONENT = {"oracle.sysman.top.agent","10.3.0.0.0"}

#-------------------------------------------------------------------------------

TOPLEVEL_COMPONENT={"oracle.sysman.top.agent","11.1.0.1.0"}

#-------------------------------------------------------------------------------

#SELECTED_LANGUAGES: Languages in which the components will be installed.

# The following choices are available. The value should contain only one of

# these choices.The choices are of the form Internal Name : External name.

# Please use the internal name while specifying the value.

# en, : English

# de, : German

# es, : Latin American Spanish

# es_MX, : Mexican Spanish

# es_ES, : Spanish

# fr, : French

# fr_CA, : Canadian French

# it, : Italian

# iw, : Hebrew

# ja, : Japanese

# ko, : Korean

# pt_BR, : Brazilian Portuguese

# zh_CN, : Simplified Chinese

# zh_TW, : Traditional Chinese

#Example: SELECTED_LANGUAGES = {"en"}

#-------------------------------------------------------------------------------

SELECTED_LANGUAGES={"en"}

#-------------------------------------------------------------------------------

#COMPONENT_LANGUAGES: Languages in which the components will be installed.

# The following choices are available. The value should contain only one of

# these choices.The choices are of the form Internal Name : External name.

# Please use the internal name while specifying the value.

# en, : English

# de, : German

# es, : Latin American Spanish

# es_MX, : Mexican Spanish

# es_ES, : Spanish

# fr, : French

# fr_CA, : Canadian French

# it, : Italian

# iw, : Hebrew

# ja, : Japanese

# ko, : Korean

# pt_BR, : Brazilian Portuguese

# zh_CN, : Simplified Chinese

# zh_TW, : Traditional Chinese

#Example: COMPONENT_LANGUAGES = {"en"}

#-------------------------------------------------------------------------------

COMPONENT_LANGUAGES={"en"}

#-------------------------------------------------------------------------------

#OMS_HOST: OMS host info required to connect to OMS

#OMS_PORT: OMS port info required to connect to OMS

#AGENT_REGISTRATION_PASSWORD: Agent Registration Password needed to

# establish a secure connection to the OMS.

#-------------------------------------------------------------------------------

OMS_HOST=[Your FQN OMS server]

OMS_PORT=1159

#OMS_PORT=4889

AGENT_REGISTRATION_PASSWORD=[Agent registration password entered during OMS installation]

#-------------------------------------------------------------------------------

#s_agentSrvcName: Sets the agent Service Name and this variable can be

# used to overrite the agent service name calculated by the install. This is

# required for only Windows.

# Example:

# s_agentSrvcName = "Oracleagent11gAgent" ; default value

# s_agentSrvcName = "GridAgent" ; User specified value

#-------------------------------------------------------------------------------

#s_agentSrvcName=""

####################################################################################

#Please Don't change the values of these variables

####################################################################################

#-------------------------------------------------------------------------------

#FROM_LOCATION: Complete path to the products.xml.

#b_upgrade: "whether it is Upgrade or not"

#EM_INSTALL_TYPE: install type

#-------------------------------------------------------------------------------

FROM_LOCATION="[Absolute path to products.xml]"

b_upgrade=false

EM_INSTALL_TYPE="AGENT"

Note that the above response file is from an installation where the to be installed server was a two-node RAC. When provided with the cluster names, the installer is able to install all nodes in one time.

When installing a RAC cluster, these options are mandatory. When not, comment out the option "CLUSTER_NODES"

Also note that in the above example there are two entries for OMS_PORT. One is commented out. Use port 1159 for SSL communication between the agent and the OMS server. The agent is in that case automatically secured. Use port 4889 for non-SSL communication.

The to be used ports here are the defaults. During the OMS installation, the port list of the OMS server is shown. Make sure you use the ports that match your OMS server.

The last entry that needs some attention is the one at the bottom of the file. However in the example a relative path has been given, the installer needs an absolute path instead.

When ready, start the installer using the command:

cp aix/response/additionalAgent.rsp aix/agent

cd aix/agent

./runInstaller [-CLUSTER_NAME=] -silent -responseFile -noconfig

The is the name of the RAC-cluster.

This name can be obtained by looking into an OCR-dump file and look for the entry:

[SYSTEM.css.clustername]

ORATEXT :

How to generate an OCR dump file can be found here .

When the installation finishes, run the given 'root.sh' script.

After that the agents need to be configured on each host ( in a cluster installation do this host by host )

Go to the AGENT_HOME/bin directory and execute:

./agentca -f -n -c -i /etc/oraInst.loc

When installing a non-RAC system, the -n and -c options can be left out.

This finishes the installation of the Oracle Agent 11g on AIX.

It might be necessary to 'upload' the first configuration manually, before the agent will appear in the OMS console. Use the following command to do so:

./emctl upload agent ( for a RAC do this on each node )

Service XDB is down alert in Grid Control

2011-01-11T08:50:00.016+01:00

Problem:

GridControl is reporting the XDB service as being down for RAC database.

In this thread it is investigated why this alert is thrown. We are using a RAC database called RACTST to show all settings and SQL queries.

It must be said that we haven't seen this alert being thrown for single-server databases. The Metric isn't even available in Grid Control there.

Background:

When creating a database with 'dbca' one can choose whether to use shared server setup. Even when shared server is disabled during database creation, afterwards the parameter 'dispatchers' is still set.

For anyone who is unfamiliar with the shared server term, consult the Oracle Database Administrators Guide at: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/manproc003.htm#i1010000

What do we see:

The default value for the dispatchers parameter looks like:

dispatchers='(PROTOCOL=TCP) (SERVICE=RACTSTXDB)'

In this parameter the service 'RACTSTXDB' is configured. This service then is automatically made available. The service availability can be checked by querying the gv$services table:

SQL> select inst_id,name from gv$services where name='RACTSTXDB';

INST_ID NAME

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

1 RACTSTXDB

2 RACTSTXDB

Because this is a 2-node RAC it is clearly shown that the service is available on both nodes.

When does the alert occur ?

Most DBA's disable the 'dispatchers' parameter whenever they choose to not use shared servers. Disabling the dispatcher is easily done by resetting the parameter from the spfile:

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

System altered.

It is also possible to turn of the dispatchers dynamically afterwards, by per using:

SQL> alter system reset dispatchers scope=memory sid='';

One can also restart the instance(s) to turn of the parameter after the spfile change.

Now wait for approx. 5 minutes, and voila, there is the alert:

Clicking on the alert shows a bit more detail, about when the alert occured:

A check in gv$services confirms the service being 'down' ( in fact it is gone):

SQL> select inst_id,name from gv$services where name='RACTSTXDB';

no rows selected.

The service is however still available in the database. A select from dba_services, still shows is as being known:

SQL> select name from dba_services where name='RACTSTXDB';

NAME                                                            

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

RACTSTXDB                                                       

1 row selected.

Solution:

Disabling the alert in GridControl would be a permanent "solution".

But that's more like a non-DBA workaround. You wouldn't be warned about any other real service being down.

There are three solutions to get rid of the alert:

- Either re-enable the dispatcher, or

- Remove the service from the database, or

- Learn to live with the alert being there.

The first is easy. Put back the 'dispatchers' parameter in the spfile with 'scope=both', and you're done. But stubborn as we are, DBA's never give up.

Well the second step, as said in the 'Problem' section, the alert is only thrown for RAC databases. For single instances, the Metric isn't even available in Grid Control.

In the RAC-FAQ on Oracle Support it says:

When I look at ALL_SERVICES view in my database I see services I did not create, what are they for?
You will always see a default database service that is the same name as your database. This service is available on all instances in the cluster. You will also see two services used by the database SYS$BACKGROUND (for background processes) and SYS$USERS (users who connect via BEQ or without using a service_name). You may also see services that end with XDB which are created for the XML DB feature and you will not be able to manage these services.

The meaning of the last phrase is that this service is not manageable with srvctl. It is unknown as a cluster resource. Is also says that belongs to the XML DB feature, so before proceeding with this thread consult MOS Note 416312.1 and the Oracle Forums thread: http://forums.oracle.com/forums/thread.jspa?threadID=410714&tstart=0

If you're sure you're not using XML-Db features, then you are OK to drop the XDB service.

Use the following command to do so:

SQL> exec dbms_service.delete_service('RACTSTXDB');

PL/SQL procedure successfully completed.

This will drop the service. When done the alert in Grid Control will subsequently be cleared.

And ofcourse as a real DBA we first test this on our TEST systems, don't we ??

The last option 'Learn to live with it' is valid for those users that do use the XML-Db feature and are not able to drop the service. For them I would almost recommend to follow solution 1. It is easier to cope with a running but unused dispatcher, then with an Grid Control alert that sits in your face every day.

Disclaimer:

I nowhere found a reason why we should not be able to drop this service if we are not using XML-Db features. Therefore the real impact is not completely known. If anyone can elaborate about this subject, you're most welcome to do so.

And eh... if you mess up your production system by following the actions in this thread, start blaming yourself, not me. All above is done on a TEST server.

AIX Agent11g patch => Bug Solved!!

2010-12-03T15:48:00.004+01:00

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 !!!

OMSCA Fails during installation of Grid Control 11g on AIX

2010-12-02T20:31:00.004+01:00

During the installation of Grid Control 11g on AIX 5.3TL10 we saw that during the configuration process the OMS Configuration Assistant (omsca) failed for an unknown reason.

I would like to bring THE solution to the attention of all you installers.

The installation can be found, thanks to Arjun Raja on his blog: http://arjunraja.wordpress.com/

Please note that this solution is only valid for AIX systems

RMAN 11g suddenly fails on AIX, nothing changed

2010-11-12T09:30:00.006+01:00

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'.

Under the hood of ASM

2010-10-15T12:05:00.027+02:00

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 Name	Description
V$ASM_DISKGROUP	Lists all available diskgroups with their characteristics
V$ASM_DISKGROUP_STAT	Synonym for v$asm_diskgroup
V$ASM_DISK	Lists all disks available to ASM that match the ASM disk_string parameter
V$ASM_DISK_STAT	Synonym for v$asm_disk
V$ASM_FILE	Contains an entry for every file stored in ASM
V$ASM_ALIAS	Lists the known file names that link to ASM files
V$ASM_CLIENT	Lists the databases that connect to this ASM instance
V$OPERATION	Lists 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 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.

NOTE: Unident of disk

2010-10-14T16:10:00.006+02:00

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.

Using Oracle Configuration Manager

2010-09-23T15:02:00.022+02:00

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.

Install Oracle Enterprise Manager Grid Control 11g on IBM AIX 5.3

2010-09-17T11:10:00.041+02:00

In this thread the installation of Oracle Enterprise Manager Grid Control 11g is explained. The installation takes place on an IBM Pseries server running AIX 5.3 TL-10.

Server requirements:

For setting up a Grid Control server using Oracle Enterprise Manager 11gR1 on AIX 5.3 a server is needed with approx. the following specs:

Operating system: AIX 5.3 TL10

Available RAM: 6Gb ( depending on the number of databases monitored, here less than 50 databases)

Available diskspace: Approx 50Gb in total.

Software requirements

Following the official installation guide, two software prerequisites are to be present and installed:

The first is the bos.perf.proctools package

The second is a Java 6 SDK. This SDK is needed for installing the Weblogic server

The minimum Service Release is 6. On the server this would show up like:

# /usr/java6_64/jre/bin/java -version

java version "1.6.0"

Java(TM) SE Runtime Environment (build pap6460sr8-20100409_01(SR8))

IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 AIX ppc64-64 jvmap6460sr8-20100401_55940 (JIT enabled, AOT enabled)

J9VM - 20100401_055940

JIT - r9_20100401_15339

GC - 20100308_AA)

JCL - 20100408_01

Please note that the above installation has a SR8 update installed.

Oracle recommends to install the Java SDK into a separate directory within the directory that will become "Middelware Home". This is to prevent accidental upgrades from the Java version, which is not supported.

Because the software packages on IBM AIX are installed with the 'smit' tool, they end up in /usr.

To follow the Oracle recommendation, the Java installation is copied into the middleware home directory ( in our case /opt/oemprd/ora/middleware).

$ cp –r /usr/java6_64 /opt/oemprd/ora/middleware/

User requirements

Create an Oracle user who will be the owner of all installations.

Add the following entries to the .profile of this user.

export JAVA_HOME=/opt/oemprd/ora/middleware/java6_64

export PATH=$PATH:$JAVA_HOME/bin

export MW_HOME=/opt/oemprd/ora/middleware/

Note that the Weblogic server will be installed in $MW_HOME/wlserver_10.3

For all below installations, this user needs to have its DISPLAY set and a valid X-Display emulator should be running on your PC

Use e.g. the following command to set the DISPLAY to the correct value:

export DISPLAY=$(who -m | cut -d$ -f2 | tr -d " $"):0

Weblogic Server

The installation manual for Oracle Enterprise Manager 11g dictates a minimum version of Weblogic server 10.3.2. This is THE ONLY SUPPORTED version !!!!

It is important to install the Weblogic server on a server which has no Oracle software installed yet.

Download the Weblogic server from:

http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html

Unzip the downloaded file into the homedirectory of the "oracle" user.

Start the installer using:

$ java -d64 -jar wls1032_generic.jar

After extraction the welcome screen appears Click on Next.
Enter the base directory used as "Middleware Home". Click on Next
Because the Oracle Enterprise Manager 11g is tightly linked to Oracle Support site, we select to enter credentials here. Click in Next
Confirm the selected Java installation to use for weblogic. It should state the installation directory copied above in section 2.1. Click on Next
The installer now shows the installation homes of the two weblogic parts: Weblogic server and Oracle Coherence. Leave the given directories as is and click on Next.
After confirming the installation summary, the installation progress screen is displayed.

After the intallation completes, unselect "Quick Start" and press Done.

Patching WebLogic Server

Before the patching of the Weblogic server can be started, make sure you have:

- Access to the Internet from the server you are installing
- Added the following hosts to /etc/hosts

141.146.54.16 support.oracle.com
66.248.199.47 bidslb.beasys.com

Start the patch installer, using:

oemprd@vsv1h151:/opt/oemprd/ora/middleware/utils/bsu (OEMPRD)
$ bsu.sh -log=/tmp/bsu.log

The file /tmp/bsu.log can be optionally used for detailed logging of the actions the program takes.
It is possible that the installer first is going to update itself. You will be prompted for that.

In the below table the update walkthrough is displayed:

Afterwards it downloads the available Weblogic updates will be downloaded. First you need to enter the credentials to access Oracle Support like shown in the figure.
After supplying the login credentials and having verified them the update installer appears. Again the login credentials for Oracle Support are asked, as shown here. We choose here to not enter the credentials, as: • We don't need any Emails about updates • There seems to be some kind of problem with the connection. It keeps returning asking for the credentials. In the "Get Patches" Tab the Weblogic patch "WDJ7" is shown. Select that patch and click download. After a couple of seconds the download finishes and the patch dissappears from the screen.
Select the tab "Manage Patches". In the lower part of the screen the downloaded patch "WDJ7" is shown. Select the patch and click the "Apply" button.
The installer checks for any conflicts. If correct, no conflicts should be found. Click OK to start the update
After the patch is installed it will appear in the upper part of the screen. This concludes the patch installations. Select File, Exit to leave the patch installer.

Installation of Grid database

Install an Oracle 11.1.0.7 release database and patch it with the latest patches.

At the time of writing this was 11.1.0.7.4

For ORACLE_HOME during this installation /opt/oemprd/ora/db/11.1.0 is used

Create a database called OEMPRD which will be used as Catalog for the Enterprise Manager server.

Ensure that all parameters have been set correctly according to the Installation Manual. These parameters are:

*.compatible='11.1.0.0.0'

*.db_block_size=8192

*.db_name='OEMPRD'

*.job_queue_processes=1000

*.log_buffer=104865760

*.open_cursors=450

*.processes=1500

*.remote_login_passwordfile='EXCLUSIVE'

*.session_cached_cursors=400

*.sga_max_size=1G

*.sga_target=1G

Because the installation and configuration of a database should be easy for DBA's working on a Grid Control installation, the procedure is not explained here.

Before continuing, make sure the database and the listener are up and running.

Installing Oracle Enterprise Manager Grid Control

Download and extract the Grid Control installation files.

Enter the directory where the files have been extracted.

Now Download the available updates for the Grid Control installation.

The commands to do so are:

oemprd@vsv1h151:/opt/oemprd/ora/home/staging (OEMPRD)

$ cd install

$ mkdir updates

$ ./utility/downloadSWUpdates -u oracleadmins@kramp.com -p -s ./updates/

Warning: It might be that the downloadSWUpdates program fails. This is because the program "thinks" you're on a 'linux' system. It tries to search for a library file in a non-existing directory called linux.

Solve this by:

$ cd /opt/oemprd/ora/home/staging/install/updates/java/oui/lib

$ ln –s aix linux

$ cd /opt/oemprd/ora/home/staging/install/updates/java/oui/bin/platform/

$ ln –s aix linux

Start the installer using the following syntax:

oemprd@vsv1h151:/opt/oemprd/ora/home/staging (OEMPRD)

$ ./runInstaller -noconfig WLS_DOMAIN_NAME=GridControl

Below the walkthrough of the Grid Control Installation:

In the first screen you're able to enter you 'My Oracle Support' Credentials. It is recommended to do so, enabling the installer to check and download available updates.
Possible screen if Check for "Download and install updates" is selected
Select Installation Type: Select "Install a new Enterprise Manager System" as the server in this installation does not have an OEM installed. Click 'Next'
Check Prerequisites The installer will list all failed prerequisite checks. If needed solve the given problem and confirm if the check runs OK afterwards Click 'Next'
Enter the installation directory of the Oracle Enterprise manager installation and the 'Middleware Home' location ( the location where Weblogic is installed) The installer determines the given entries and they are correct most of the time. Click 'Next'
Weblogic Server Domain. Since Oracle Enterprise Manager 11g runs on a Weblogic environment, a weblogic domain will be created. Specify passwords for the given Weblogic user and Node manager. Click 'Next'
Database credentials. Enter the database servername, Listener Port, SID and SYS password for access to the database created for this purpose.
Setup the Management repository. Enter a password for the to be created SYSMAN user. Confirm the file locations of the to be created tablespaces. Click 'Next'
Enter passwords for the Management Agent registration. Keep this password somewhere safe, as it is needed later when installing/configuring the clients Click 'Next'
The port numbers to be used. It is smart to keep a print of the screen, or write down the port numbers. Click 'Next'
Installation Summary. Click 'Next'
Although the manual states that 400Mb of temp space in /tmp is needed, the installer actually needs 936Mb (or more), as stated on the screen from which the installer started: Space Requirements /tmp/ Required 936MB (only as temporary space) : Available 781MB Increase space in /tmp and afterwards continue the installation by clicking 'Yes'.
Execution of allroot.sh script
After running a root-script for which a dialog box appears, the configuration of the product starts.
If the configuration of OMS fails ( step 5) and the logfile shows: FINE: Cleaning the OMS from the repository Sep 22, 2010 11:17:34 AM oracle.sysman.omsca.adapter.wls.OMSWLSAdapter adapterCleanup FINE: Got the Host URL as: null Sep 22, 2010 11:17:34 AM oracle.sysman.omsca.framework.OMSGenericAdapter postDeployAndReposSetup SEVERE: OMSCA-ERR:Securing of OMS failed. Check the trace file:/opt/oemprd/ora/middleware/weblogic/oms11g/cfgtoollogs/omsca/omsca_20 100922105531.log Sep 22, 2010 11:17:34 AM oracle.sysman.omsca.framework.OMSCAFreshInstall execute SEVERE: Securing of OMS failed. Sep 22, 2010 11:17:34 AM oracle.sysman.omsca.framework.OMSConfigAssistantDriver main FINE: Got resultfalse then the an unsupported version of WebLogic is installed . Only version 10.3.2 is supported for Oracle Enterprise Manager 11g ( see Note: 1096766.1) If the configuration fails at Add-on OMS side configuration, a manual fix needs to be applied to addonca.pl, as described here: http://arjunraja.wordpress.com/ This page says: While installing Oracle 11g on AIX you could hit this failure Do not CANCEL or EXIT the installation from the GUI mode. A check of the logfile reveals this error - 11g Grid Control installation fails at ‘ Add-on OMS Side Configuration ‘ with following error message in oracle.sysman.plugin.virtualization.oms_2010-06-01_01-24-07-PM.log: at java.lang.Terminator.setup(Terminator.java:41) at java.lang.System.completeInitialization(System.java:114) at java.lang.Thread.(Thread.java:127) Caused by: java.lang.RuntimeException: Can’t recognise platform – Unix The following file needs to be backed up and edited as follows Go to the newly installed Oracle Management server HOME/bin folder - cp addonca.pl addonca.pl.org Edit the file - vi addonca.pl Look for Unix under osname and change it to AIX - else { $osname = “Unix” ; } to else { $osname = “AIX” ; } Click on RETRY and the installation completes.

This completes the installation of Grid Control 11g on AIX 5.3

Instructions on how to install the Oracle 11g Management Agent can be found here:

http://managingoracle.blogspot.com/2011/01/installing-oracle-agent11g.html

Oracle table reorganization: Online or Offline

2010-08-24T10:40:00.044+02:00

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_NAME	SEGMENT_TYPE	MBS	WASTED
-----------------------	------------------	----------	----------
T1	TABLE	5064	2348
T2	TABLE	3333	617
T3	TABLE	6144	3428

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_NAME	SEGMENT_TYPE	MBS	WASTED
-----------------------	------------------	----------	----------
T1	TABLE	5064	2789
T2	TABLE	3333	1058
T3	TABLE	6144	3869

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_NAME	SEGMENT_TYPE	MBS	WASTED
-----------------------	------------------	----------	----------
T1	TABLE	4245	1970
T2	TABLE	2779	505
T3	TABLE	5292	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 %

AIX based RAC reboots nodes by itself

2010-07-14T13:37:00.007+02:00

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