Oracle Index Cluster Storage

A Cluster Index is an object oriented to gain performance when frequent master/detail relation ships are found between two tables. This is the fastest way to perform a join between two tables, but there are storage issues when this storage structure is used. The purpose of this article is to analyze the way Oracle reserves storage for a Cluster Index

Create cluster and verify Cluster storage

SQL> l
1 create cluster cluster_emp
2 (department_id NUMBER(4))
3* index


SQL> create index i_cluster_emp on cluster cluster_emp;

Index created.

SQL> r
1 create table emp cluster cluster_emp(department_id)
2 as
3* select employee_id, first_name, last_name, hire_date, salary, department_id from employees

Table created.

SQL> create table dept
2 cluster cluster_emp(department_id)
3 as
4 select * from departments;

Table created.


Verify where data is stored

SQL> r
1 select owner, segment_name, file_id, block_id, blocks, bytes
2 from dba_extents
3 where segment_name in ('I_CLUSTER_EMP','CLUSTER_EMP')
4* order by segment_name, extent_id

OWNER SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS BYTES
-------- ------------- ---------- ---------- ---------- ----------
HR CLUSTER_EMP 4 3825 8 65536
HR CLUSTER_EMP 4 1817 8 65536
HR CLUSTER_EMP 4 3817 8 65536
HR CLUSTER_EMP 4 3809 8 65536
HR I_CLUSTER_EMP 4 3833 8 65536


Determine how many rows are retrieved for each department Id when join is performed

SQL> R
1 SELECT D.DEPARTMENT_ID, COUNT(*)
2 FROM EMP E, DEPT D
3 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
4 GROUP BY D.DEPARTMENT_ID
5* ORDER BY D.DEPARTMENT_ID

DEPARTMENT_ID COUNT(*)
------------- ----------
10 1
20 2
30 6
40 1
50 45
60 5
70 1
80 34
90 3
100 6
110 2

11 rows selected.

Display where specific rows are stored.

SQL> R
1 SELECT D.DEPARTMENT_ID,
2 DBMS_ROWID.ROWID_RELATIVE_FNO(D.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(D.ROWID) DEPT_ROW,
3 DBMS_ROWID.ROWID_RELATIVE_FNO(E.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(E.ROWID) EMP_ROW,
4 COUNT(*)
5 FROM DEPT D, EMP E
6 WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
7 GROUP BY
8 D.DEPARTMENT_ID,
9 DBMS_ROWID.ROWID_RELATIVE_FNO(D.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(D.ROWID),
10 DBMS_ROWID.ROWID_RELATIVE_FNO(E.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(E.ROWID)
11* ORDER BY DEPARTMENT_ID

DEPARTMENT_ID DEPT_ROW EMP_ROW COUNT(*)
------------- -------- -------- ----------
10 4:1821 4:1821 1
20 4:1822 4:1822 2
30 4:3829 4:3829 6
40 4:1823 4:1823 1
50 4:3830 4:3830 45
60 4:3832 4:3832 5
70 4:1824 4:1824 1
80 4:1819 4:1819 34
90 4:3831 4:3831 3
100 4:3828 4:3828 6
110 4:1817 4:1817 2

11 rows selected.

There it can be seen that for each department Id one block is assigned, this will make Oracle cluster to require more storage than that required to store individual independent tables. On the other hand if few employee rows are assigned to a department, a lot of block space will be wasted. Transactional operations over the individual cluster participant tables will make rows to be physically reallocated, thus making Oracle clusters suitable for fast join operations, but slow for transactional tables.

Block dumped to verify physical row storage from the first two cluster rows.

C:\Oracle\product\10.1.0\admin\alpha\udump>more alpha_ora_6088.trc
Dump file c:\oracle\product\10.1.0\admin\alpha\udump\alpha_ora_6088.trc
Wed Apr 02 01:12:30 2008
ORACLE V10.1.0.5.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1077M/2046M, Ph+PgF:3110M/3938M, VA:1701M/2047M
Instance name: alpha

Redo thread mounted by this instance: 1

Oracle process number: 13

Windows thread id: 6088, image: ORACLE.EXE (SHAD)


*** 2008-04-02 01:12:30.265
*** ACTION NAME:() 2008-04-02 01:12:30.250
*** MODULE NAME:(SQL*Plus) 2008-04-02 01:12:30.250
*** SERVICE NAME:(SYS$USERS) 2008-04-02 01:12:30.250
*** SESSION ID:(152.1209) 2008-04-02 01:12:30.250
Start dump data blocks tsn: 4 file#: 4 minblk 1821 maxblk 1822
buffer tsn: 4 rdba: 0x0100071d (4/1821)
scn: 0x0000.001deadc seq: 0x01 flg: 0x06 tail: 0xeadc0601
frmt: 0x02 chkval: 0x1b2c type: 0x06=trans data
Block header dump: 0x0100071d
Object id on Block? Y
seg/obj: 0xc312 csc: 0x00.1dea96 itc: 2 flg: E typ: 1 - DATA
brn: 1 bdba: 0x1000ef1 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.021.0000051a 0x008005bb.02be.34 C--- 0 scn 0x0000.001dea72
0x02 0x0003.00a.000008e7 0x00800526.04cd.12 --U- 1 fsc 0x0000.001deadc

data_block_dump,data header at 0x6051264
===============
tsiz: 0x1f98
hsiz: 0x20
pbl: 0x06051264
bdba: 0x0100071d
76543210
flag=--------
ntab=3
nrow=3
frre=-1
fsbo=0x20
fseo=0x1f47
avsp=0x1f27
tosp=0x1f27
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=1 offs=1
0x16:pti[2] nrow=1 offs=2
0x1a:pri[0] offs=0x1f82
0x1c:pri[1] offs=0x1f60
0x1e:pri[2] offs=0x1f47
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 2 comc: 2 pk: 0x0100071d.0 nk: 0x0100071d.0
col 0: [ 2] c1 0b
tab 1, row 0, @0x1f60
tl: 34 fb: -CH-FL-- lb: 0x0 cc: 5 cki: 0
col 0: [ 2] c2 03
col 1: [ 8] 4a 65 6e 6e 69 66 65 72
col 2: [ 6] 57 68 61 6c 65 6e
col 3: [ 7] 77 bb 09 11 01 01 01
col 4: [ 2] c2 2d
tab 2, row 0, @0x1f47
tl: 25 fb: -CH-FL-- lb: 0x2 cc: 3 cki: 0
col 0: [14] 41 64 6d 69 6e 69 73 74 72 61 74 69 6f 6e
col 1: [ 2] c2 03
col 2: [ 2] c2 12
end_of_block_dump
buffer tsn: 4 rdba: 0x0100071e (4/1822)
scn: 0x0000.001deadc seq: 0x01 flg: 0x06 tail: 0xeadc0601
frmt: 0x02 chkval: 0x028c type: 0x06=trans data
Block header dump: 0x0100071e
Object id on Block? Y
seg/obj: 0xc312 csc: 0x00.1dea96 itc: 2 flg: E typ: 1 - DATA
brn: 1 bdba: 0x1000ef1 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.02a.0000051a 0x008005bb.02be.36 C--- 0 scn 0x0000.001dea73
0x02 0x0003.00a.000008e7 0x00800526.04cd.13 --U- 1 fsc 0x0000.001deadc

data_block_dump,data header at 0x6051264
===============
tsiz: 0x1f98
hsiz: 0x22
pbl: 0x06051264
bdba: 0x0100071e
76543210
flag=--------
ntab=3
nrow=4
frre=-1
fsbo=0x22
fseo=0x1f2c
avsp=0x1f0a
tosp=0x1f0a
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=2 offs=1
0x16:pti[2] nrow=1 offs=3
0x1a:pri[0] offs=0x1f82
0x1c:pri[1] offs=0x1f5c
0x1e:pri[2] offs=0x1f41
0x20:pri[3] offs=0x1f2c
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 3 comc: 3 pk: 0x0100071e.0 nk: 0x0100071e.0
col 0: [ 2] c1 15
tab 1, row 0, @0x1f5c
tl: 38 fb: -CH-FL-- lb: 0x0 cc: 5 cki: 0
col 0: [ 3] c2 03 02
col 1: [ 7] 4d 69 63 68 61 65 6c
col 2: [ 9] 48 61 72 74 73 74 65 69 6e
col 3: [ 7] 77 c4 02 11 01 01 01
col 4: [ 3] c3 02 1f
tab 1, row 1, @0x1f41
tl: 27 fb: -CH-FL-- lb: 0x0 cc: 5 cki: 0
col 0: [ 3] c2 03 03
col 1: [ 3] 50 61 74
col 2: [ 3] 46 61 79
col 3: [ 7] 77 c5 08 11 01 01 01
col 4: [ 2] c2 3d
tab 2, row 0, @0x1f2c
tl: 21 fb: -CH-FL-- lb: 0x2 cc: 3 cki: 0
col 0: [ 9] 4d 61 72 6b 65 74 69 6e 67
col 1: [ 3] c2 03 02
col 2: [ 2] c2 13
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 1821 maxblk 1822

C:\Oracle\product\10.1.0\admin\alpha\udump>

Data Dictionary Statistics Gathering

Normally data dictionary statistics in 9i is not required unless performance issues are detected. Metalink note where data dictionary gathering is discouraged (245051.1) dates back from 16-APR-2004, almost four years ago. In 10g data dictionary statistics are collected just the same as any other schema object. There are official documents that suggest the statistics gathering in 9i (216550.1). It is commonly suggested to gather data dictionary statistics in the Oracle Applications environments. In fact, when performing a 9i database upgrade in an applications database, it is listed as a post upgrade step.

In 10g this is an official recommendation ML (457926.1).


Known issues when data dictionary statistics are present in sys schema.
It is suggested to gather data dictionary statistics in 9i under direct oracle support advice when performance problems are detected.
In catpach for 9.2.0.2, 9.2.0.3 and 9.2.0.4 there was an note documenting the issue that the catpatch process took too much time due to the existing of statistics
This document doesn't state that data dictionary statistics musn't be collected, it only suggest data dictionary statistics must be deleted prior to the upgrade process and once it finishes statistics can be collected once more.

In fact the official suggestion states this:
dbms_stats.delete_schema_stats('SYS'); -- prior to perform the upgrade process
dbms_stats.gather_schema_stats('SYS'); -- once the process has finished

Ref. Gathering Data Dictionary Statistics
Doc ID: Note:245051.1

There is anohter documented issue, listed back on 14-JAN-2005, which states that querying dba_free_space hangs if data dictionary statistics are collected, but the metalink note never states that statistics are not suggested, but it rather suggests a procedure to solve this issue.
Another documented issue dates back to 20-Aug-2003, and reported issues when working with portal 3.0.9.8.1 if data dictionary were present.

Identify if data dictionary statistics have been collected.
If there are performance problems due to large data dictionary access, it is advisable to check if data dictionary statistics have been collected. It can be easily displayed by querying data dictionary tables belonging to sys.

select table_name, last_analyzed
from dba_tables where owner='SYS'
and table_name='FET$';

This other query is useful to know how many sys tables have statistics:

select distinct trunc(last_analyzed), count(*)
from dba_tables
where owner='SYS'
group by trunc(last_analyzed)


Upgrade guide.

If collecting data dictionary statistics during the upgrade process is a time consuming task, it is advisable to collect them prior to perform the upgrade task. Both in Oracle 8i and 9i, the procedure that must be run is DBMS_STATS.GATHER_SCHEMA_STATS, in fact the procedure suggested is by means of EXEC DBMS_STATS.GATHER_DICTIONARY_STATS, which was added starting DB10gR1.


References and documentation.

Complete Checklist for Manual Upgrades to 10gR2
Doc ID: Note:316889.1

Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Doc ID: Note:216550.1

Select COUNT(*) from DBA_FREE_SPACE Hangs
Doc ID: Note:121729.1

Initial Portal Page Request Takes A Lot of Time
Doc ID: Note:198471.1

Gather Optimizer Statistics For Sys And System
Doc ID: Note:457926.1

Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)?
Doc ID: Note:35272.1

Ref. How to quickly verify whether data dictionary statistics has been collected
Doc ID: Note:333175.1

java.lang.Exception: Exception in sending Request :: null

When accessing Enterprise Manager DB Control Console a couple of error messages display, right after providing the sys credentials, the console appears with a down arrow and at the header it says:

java.lang.Exception: Exception in sending Request :: null

If you try to move to another tab, such as the performance tab, it asks for credentials and responses:

Io exception: Unknown host specified

These are symptoms of a not properly configured Agent Time Zone.


Troubleshooting Agent Time Zone

This case happened to me on a Windows XP Professional in Mexican Spanish after a timezone change. I notice a similar error shows up on databases affected by the automatic time zone changes after synchronization with the time.windows.com, which recently has included two time zones for Mexico - one named old and the other named new-

Troubleshooting
0. Consider these files when performing the troubleshooting phase:
emd.properties
supportedtzs.lst

1. Stop the console
\bin\emctl stop dbconsole

2. Backup the file located at ORACLE_HOME/hostname_SID/sysman/config/emd.properties

3. Edit the emd.properties file and look for the agentTZRegion (generally appears at the end of the file)

At the file emd.properties, located at the ORACLE_HOME/hostname_instanceName/sysman/config

change the value

agentTZRegion=GMT To agentTZRegion=America/Mexico_City

Evidently, this is for the particular case of Mexico City, but similar issues could apply to other time zones.
For other timezones affected, such as the one corresponding to Egypt, change the value of this parameter from GMT+2 to Egypt which is included in the supportedtzs.lst file.
So the parameter will be agentTZRegion=Egypt

4. When agent is unable to find a proper time zone it will adopt GMT, so it could be the value registered so far. Change this value by the value corresponding to the OS time zone, this time zone should be one listed at the ORACLE_HOME/sysman/admin/nsupportedtzs.lst file.

5. Execute this command
emctl resetTZ agent
emctl config agent getTZ

After issuing the resetTZ command a similar issue like this one may appear:
To complete this process, you must either:

connect to the database served by this DBConsole as user 'sysman', and execute:

SQL> exec mgmt_target.set_agent_tzrgn('pc06.oracle.com:3938','America/Mexico_City')

-- or --

connect to the database served by this DBConsole as user 'sys', and execute:

SQL> alter session set current_schema = SYSMAN;
SQL> exec mgmt_target.set_agent_tzrgn('pc06.oracle.com:3938','America/Mexico_City')

In order for these commands to be successful, you are required the agent to have registered some values at the EM repository. Check this query, there should be similar information displayed when connected as sysman:
SQL> select target_name, target_type from mgmt_targets;

TARGET_NAME TARGET_TYPE
------------------------------------
pc06.oracle.com host
orcl.oracle.com oracle_database
pc06.oracle.com:3938 oracle_emd
Management Services and Repository oracle_emrep
LISTENER_pc06.oracle.com oracle_listener

6. The previous command will ask to perform some actions at the sysman repository level. Execute the reset at the repository level by:
SQL> alter session set current_schema = SYSMAN;

SQL> exec mgmt_target.set_agent_tzrgn('hostname:3938','TimeZone');

This command should be successful, otherwise it could be because the agent hasn't ever started and it has never registered, even with the wrong TZ at the repository.

In order for you to verify this has ever run and the agent is properly registered, issue this query as sysman:

SQL> select target_name, target_type from mgmt_targets;
The target with the default port 3938 is the target we are looking for.

The target related to the port 3938 should be listed. Otherwise try to start the agent so it can register this target. If agent is not starting, please veriy at the logs what could be a reason that prevents agent from starting. Most of the times it is because of a wrong specified

6. Try to login to the dbconsole and check if the error still exists.

7. Now start the EM Console, the problem should be fixed by now.


Notes.
The value of the time zone is a valid value listed at ORACLE_HOME/sysman/admin/nsupportedtzs.lst Make sure the time zone matches that of the host computer.

if when executing the emdctl command an error related to java appears, it is because the right java version is not being invoked. Make sure the path environment variable properly includes the jdk environment provided at the same Oracle Home where the database is related to.

path=%PATH%;C:\Oracle\product\10.2.0\db_1\jdk\jre\bin;C:\Oracle\product\10.2.0\db_1\jdk\bin
path=%path%;C:\Oracle\product\10.2.0\db_1\jdk\jre\bin\client



References.

Problem: Startup Agent: ORA-20233: Invalid agent name when running mgmt_target.set_agent_tzrgn procedure in repository
Doc ID: Note:388280.1 --> This note has particularly helped me to solve my particular issue.

Problem: Startup Agent: EM Agent will not start due to Timezone mismatch (Daylight Savings changes for Australia)
Doc ID: Note:362888.1 --> some useful ideas were taken from this note

Problem: Startup Agent: Agent Fails to Start due to Incorrect Timezone File Used as Pointed by ORA_TZFILE
Doc ID: Note:409121.1

Problem: Startup Agent: ORA-20233: Invalid agent name when running mgmt_target.set_agent_tzrgn procedure in repository
Doc ID: Note:388280.1

Enable Automatic logon on Windows 2003

Verbatim from the Microsoft Article Id. 324737

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.
To use Registry Editor (Regedt32.exe) to turn on automatic logon, follow these steps:

1.	Click Start, and then click Run.
2.	In the Open box, type Regedt32.exe, and then press ENTER.
3.	Locate the following subkey in the registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon
4.	Double-click the DefaultUserName entry, type your user name, and then click OK.
5.	Double-click the DefaultPassword entry, type your password, and then click OK.NOTE: If the DefaultPassword value does not exist, it must be added. To add the value, follow these steps: a. On the Edit menu, click New, and then point to String Value. b. Type DefaultPassword, and then press ENTER. c. Double-click DefaultPassword. d. In the Edit String dialog, type your password and then click OK. NOTE: If no DefaultPassword string is specified, Windows automatically changes the value of the AutoAdminLogon key from 1 (true) to 0 (false), disabling the AutoAdminLogon feature.
6.	On the Edit menu, click New, and then point to String Value.
7.	Type AutoAdminLogon, and then press ENTER.
8.	Double-click AutoAdminLogon.
9.	In the Edit String dialog box, type 1 and then click OK.
10.	Quit Registry Editor.
11.	Click Start, click Shutdown, and then type a reason in the Comment text box.
12.	Click OK to turn off your computer.
13.	Restart your computer. You can now log on automatically.

Notes To bypass the AutoAdminLogon process and to log on as a different user, hold down the SHIFT key after you log off or after Windows restarts.

Registry change will not work if the “Logon Banner” is defined on the server either by a Group Policy object (GPO) or by a local policy. When policy is changed to not impact server, the feature works as expected.

An interactive console logon that has a different user on the server changes the DefaultUserName registry entry as the last logged on user indicator. AutoAdminLogon relies on the DefaultUserName entry to match the user and the password. Therefore, AutoAdminLogon may fail. You may configure a shutdown script to set the correct DefaultUserName entry for AutoAdminLogonAs. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

119364 (http://support.microsoft.com/kb/119364/) AutoAdminLogon loses DefaultUserName

Issues Installing Patchset 10.2.0.3.0 on Windows XP

When attempting to install the Patchset 10.2.0.3.0 upgrade OUI complainted about a msvcr71.dll file in use. All Oracle services were shutdown, as directed by the Installation Guide. So this file shouldn't be in use by any Oracle executable. So where the culprit is? I found it by means of the Process Explorer, this is an utility that can be downloaded from the microsoft site, by navigating through the url: http://www.sysinternals.com/, the Microsoft Technet site can be reache, a couple of clicks and you're done, you can download it. A useful tool is the ListDLL too.

I found a svchost.exe process grabbing the msvcr71.dll file, I just killed it and the Installation process just went on to the end. I should point out that killing an OS process is a very serious matter, don't kill it unless you are absolutely sure what you are killing. DLL's shouldn't be locked by third party processes, so Oracle can just smoothly perform the upgrade. By means of the graphical tool previously mentioned I managed to know which process was locking the DLL.

Side Note, msvcr71.dll: is a module containing standard C library functions such asprintf, memcpy, and cos. It is a part of the Microsoft C RuntimeLibrary.

Orastack

The orastack utility is a tool provided by oracle to set the stack size reserved or commited on a per thread basis in the Oracle Server on Windows Operating Systems. I had to use it because on a 10gR2 Winx32 database frequent ORA-04030 errors displayed along with TNS-12518.

The database must be shutdown prior to issue the command, otherwise this error will show up:

C:\Oracle\product\10.2.0\db_1\BIN>orastack oracle.exe 524288

Couldn't open file with CreateFile()

GetLastError() == 32

The default value is 1024K, it can be reduced to 512K, but it is not recommended to reduce the value below 300K, otherwise the ORA-01331 may start to show up.

Syntax:

• orastack {no arguments} will display a useful README 1st.
• orastack executableName will display the current settings
• orastack executableName newValue will reset the current value to the specified newValue

ORA-04030 After 10gR2 Upgrade on Windows 2003

I have faced this exact situation, a large awful Error stack with errors such as:
ORA-04030, TNS-12518.

The particular environment where I faced these issues was:

Oracle 10gR2 (10.2.0.1.0) for Windows x32

Windows 2003 Enterprise Edition 32 bits

It was Right after migrating a user processes intensive application from 9iR2 (9.2.0.8.0) to the above referred 10gR2 release.
I had documented this issue with the metalik Note: 10gR2 Dedicated Connections Intermittently Fail with TNS-12518 Doc ID: Note:371983.1

One of the recommendations was to reduce memory consumption, and among one of the solutions was the use of orastack.exe to achieve this reduction. Right now I am documenting the issues and drawbacks of performing this procedure.

Aonother recommendation is to reduce the SGA consumption, this was achieved by reducing the values of the SGA_TARGET and SGA_MAX_SIZE instance parameters.


Update 11/Feb/2008

The orastack utility was helpful in reducing both, the Oracle and tnslsnr executables stack size. Additionally, a reduction in users' contention greatly helped in reducing the frequency of ORA-04030 errors, it doesn't mean this error was completely wiped out, I am still waiting for the 10.2.0.4.0 patchset to assess if I can completely get rid of this issue.

Forums Code of Conduct

Having actively participated at the Oracle forums, I have collected some useful recommendations for making life at forums easier for everybody.

1. Have a meaningful subject line. The title should properly summarize the problem you are facing, this will attract people who may already have had a similar issue more easily.

2. Give details about versions and technologies (DB, OS, working environment). Depending on the version combinations a suitable solution can or can't be found, if not properly specified the answer scope may be unnecessarily wide and blind guesses will start to be posted.

3. Give detailed error messages. Posting monosyllabic or partially specified error messages or being non specific won't bring you specific answers for sure.

4. If possible provide steps to reproduce the problem. A problem could be reproduced by the posters and configuration issues could be easily spotted. Lab environments could be easily set up.

5. Search before you post. Don't try to reinvent the wheel when someone else has already done it for you. A suitable answer can be found, a 10 minute goggling effort could find you an answer.

6. Get a name. Anonymous users are like ghosts without a name. user615770, for example, says nothing about a person. Everybody can take a few minutes to give themselves a real id, this will make posters more responsible for what they are postings, since they a real person with a real name and they are not hiding behind the mask of a generic and anonymous User Id.

7. If you solve it on your own, share the solution and mark problems with [Solved] in your thread title. There are people who not only feedback if the solutions provided helped in solving their issue, they never track their threads (specially seen when people mark their threads as Urgent). Thinking about other people who are looking for a similar solution, they never know if the provided steps lead to a successful or an unsuccessful result, or even if another workaround was found.

8. Never judge people because of their english level. Most of the people at the international forums aren't English native speakers (including myself), and they do their best to make themselves clear and understandable, let's help them instead of laughing at them, particularly if posting on a technical forum, which should not be used as an English language level assessment tool.

9. Never use discriminatory comments, nor references to ethnic groups or religions, nor make any comment that could be offensive to anybody, in a technical forum, strictly technical comments are never offensive to anybody.

10. Kill Uppercase and exclamation marks. This is considered as yelling and it could be offensive for some people, including me. 'Please' is always an advisable and suggestible keyword.

11. Think of your threads and contributions as if they were written on stone. More people than you could imagine is reading or will read the thread, and each time this thread is read people will read it as is.

12. RTFM is not an answer. I have seen several times self-qualified senior level whatever providing this kind of answers. RTFM is equal as saying "don't bother me, stop @%##~ me", and this is not an acceptable technical answer, specially for those who pretend to be self qualified as senior.

13. Address the community with respect. Self explanatory.

Thanks to Shay Shmeltzer's Blog out of which, this code of conduct proposal is based.

Issues found when upgrading 9.2.0 to 10.2.0

Note: I strongly encourage people to read the Upgrade guide, read metalink Notes, get involved with 10g new features and setup a testing environment prior to perform the actual upgrade on a production environment.

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

In my particular case, the reason why I faced this issue was because I enabled ASMM and I didn't declare a value for SGA_TARGET. All parameters managed by ASMM were set to zero, but I forgot to explicitly set SGA_TARGET and SGA_MAX_SIZE. After setting suitable values for these parameters I was able to start my database. _shared_pool_reserved_min_alloc is a hidden parameter and this error appears due to ASMM missconfiguration.

This was on a manually upgraded database. I followed the below mentioned documentation, which was of great help.

Upgrade Guide
Oracle 10g New Features

Metalink Notes:

• Complete checklist for manual upgrades of Oracle databases: 421191.1

Heap size nK exceeds notification threshold (2048K) warning
An error similar to this one is frequently seen at the alert.log file. Several queries which used to work without any issue, suddenly start to be reported at the alert.log file without apparent reason.
Memory Notification: Library Cache Object loaded into SGA
Heap size 11369K exceeds notification threshold (2048K)
Details in trace file d:\oracle\admin\aonmxdbf\udump\aonmxdbf_ora_3624.trc
KGL object name :EXPLAIN PLAN SET STATEMENT_ID = 'TRACE156742' INTO DBO.IW_PLAN_TABLE FOR Select

This is actually not an error, and the database is not about to crash, it only means a Heap size is greater than the value registered as a threshold. This is an issue on 10.2.0.1.0, this issue has been fixed on later patchsets. If you want to get rid of this warning, then an internal parameter has to be modified to a value greated than 2M. This should be made from a sql plus prompt connected as sysdba:

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;


ORA-04030: out of process memory
An error similar to the one shown below is written at the alert.log file, a process has run out of memory. The error manual is funny, according to it you don't have to do any thing.

ORA-04030: out of process memory when trying to allocate string bytes (string,string)
Cause: Operating system process private memory has been exhausted
Action: none

Errors in file d:\oracle\admin\aonmxdbf\udump\aonmxdbf_ora_3176.trc:
ORA-04030: out of process memory when trying to allocate 66172 bytes (pga heap,kco buffer)
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x60504019] [ADDR:0x4]
[UNABLE_TO_WRITE] []

This error showed up when an end user tried to access an application with a view ranked as one of the top queries in the database. I have increased the PGA_AGGREGATE_TARGET instance parameter and I have enabled ASMM.

I should further clarify, this happened on a Windows 2003 environment, and I found out that the boot didn't have the /3G switch required to access more than 2G RAM memory. So I have set it too since the memory requirements were very close to the 2G limit.

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /3GB

The ORA-04030 was accompanied by TNS-12518 (TNS:listener could not hand off client connection)

It happened intermitently and when workload increased. As detailed on my other post this was due to excesive resources allocated by all the new features for this release. It is documented at the metalink note 371983.1.


Instance parameters compatible and log_archive_format
Once the database was upgraded, according to the install guide, the compatible parameter remained at 9.2.0.0.0, once the upgrade process was stabilized the parameter changed to 10.2.0.1.0, this made instance parameter such as log_archive_format to have a mandatory different format: it must contain the archive format mask %s %r %t.

Zayle Letzel Trojan

"Todo por ti Letzel"

What's the Zayle Trojan
The Zayle or Letzel is a trojan virus that attacks the USB devices, such as regular pen drives, USB storages, digital cameras, ipods, mp3 players, etc. This can be recognized when you see the hidden system file autorun.inf along an executable named crsvc.exe (Zarteck), this prevents the pen drive from being safely removed, and it may prevent the device from being formatted.

The autorun.inf file can be opened with notepad and a couple of text images can be seen (as shown below).

Zayle could be a rearrange of the name Elyza, and the message posted by the hacker reads "TODO POR TI LETZEL", which means "All for you Letzel". This could be dedicated either to the hacker's girlfriend or this could be dedicated to the architect Jan Letzel, who created the Hiroshima Peace Memorial, just mere speculation.


HowTo get rid of Zayle

1. Disable the system restore feature. Go to the Control Panel -> System -> System Restore And disable the Restore system by clicking on the Deactivate System Restore on all units check box. Accept it and close this dialog window.
2. From the Task Manager find a process named crsvc.exe and kill it.
3. Disable the 'hide protected Operating system files' option from the folder options. Go to Tools -> folder options -> See -> Hide Protected operating system files and deselect this option, so the hidden system files appear listed on the explorer.
4. From the explorer, do not open the pendrive with double click, this will re-enable the trojan. Explore it instead by right clicking on the drive letter icon.
5. Delete the autorun.inf and the crsv.exe files.
6. Open the regedit, Start menu -> run (or click [Win]+r then type regedit on the dialog box and accept. On the regedit explorer look for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run, and there you will find an entry named syslog that calls the crsvc.exe file, remove this entry and restart your computer.
7. The infection is gone.

Zayle's Signature
Autorun signature left by this trojan at the autorun.inf file when it infects the pendrive (very creative and romantic):

[autorun]
open=crsvc.exe
shell\1=ZAYLE
shell\1\Command=crsvc.exe
shellexecute=crsvc.exe

TODO POR TI LETZEL (All for you Letzel)
[A heart text image]


[A rose text image]