Database startup fails with ORA-00064

Problem Description
Case 01: After it is set SGA_TARGET to 100G with a total of about 300G physical memory on the server "startup" fails with
ORA-00064: object is too large to allocate on this O/S (1,15429280) errors

Case 02: The parameter OPEN_LINKS_PER_INSTNACE is set to 1000 and then startup of the instance causes ORA-00064.

Case 03: After it is set high value of PROCESSES parameter, whenever you try to startup the instance it fails with ORA-00064.

Case 04: "db_files" initialization parameter on 64bit versions of Oracle is set to a higher value and now startup does not work.

Cause of the Problem
As it is already discussed in the post startup migrate fails with ORA-00064 while upgrading to 10.2.0.2 with DBUA the problem happened due to lower default value of oracle hidden parameter _ksmg_granule_size.

The calculation is,
- sga_max_size <= 1024M then _ksmg_granule_size = 4M - sga_max_size > 1024M and <128g then _ksmg_granule_size = 16M - sga_max_size > 128G and <256g then _ksmg_granule_size = 32M - sga_max_size > 256G and <512g then _ksmg_granule_size = 64M

Solution of the Problem
Solution 01: The issue is fixed in Oracle 10.2.0.4.3 (Patch Set Update) and 10.2.0.5 (Server Patch Set). So applying patch will solve the problem.

Solution 02: Disable NUMA optimization on the system
To do this set two hidden parameters like below.
_enable_NUMA_optimization=FALSE
and
_db_block_numa=1

Solution 03: Increase granule size on the system (_ksmg_granule_size)
_ksmg_granule_size=32M

Solution 04: If your PROCESSES initialization parameter has higher value then reduce the value. For example make it less than 1500. Also set the DB_FILES parameter to lower value.

startup migrate fails with ORA-00064 while upgrading to 10.2.0.2 with DBUA

Problem Description
It was tried to upgrade an oracle database from version 9.2.0.7 to 10.2.0.2 using oracle database upgrade assistant. After invoking DBUA ORA-00064: returns. If you also issue startup upgrade from sql*plus it also fails like below.

SQL> startup upgrade
ORA-00064: object is too large to allocate on this O/S (1,7614720)

Cause of the Problem
1. The oracle PROCESSES initialization parameter is set to high value. For example, if you set 'Processes' parameter to a high value (for example > 14000), the instance startup fails with ORA-00064.

2. Low value of granule size.

The oracle hidden parameter "_ksmg_granule_size" is set based on the oracle sga size.
The calculation is,
- sga_max_size <= 1024M then _ksmg_granule_size = 4M
- sga_max_size > 1024M and <128g then _ksmg_granule_size = 16M
- sga_max_size > 128G and <256g then _ksmg_granule_size = 32M
- sga_max_size > 256G and <512g then _ksmg_granule_size = 64M

Now if you set high value of a parameter(for example DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE) that exceeds the value of the granule size that was calculated based on the size of the sga.

If your SGA size is over 1G then you will manually have to set the parameter _ksmg_granule_size. Note that the default setting of _ksmg_granule_size is 4M and low value of _ksmg_granule_size can prohibit the database startup if your memory value is over 1G.

3. "db_files" initialization parameter on 64bit versions of Oracle is set to a higher value.

4. The ORA-00064 error could be occurred even though the big "_ksmg_granule_size" was configured in init.ora file as because during DBUA in 10.2.x it strips out the oracle hidden parameter while opening oracle database.

Solution of the Problem
Solution 01:
Reduce the value of the oracle "PROCESSES" initialization parameter.

For example, open oracle parameter file with editor and put following line
PROCESSES = 1500

Solution 02:
Increase Oracle hidden parameter value of "_ksmg_granule_size" directly to 16M (16777216) or 32M (33554432)

- open oracle parameter file with editor and put following line
_ksmg_granule_size=16777216
or
_ksmg_granule_size=33554432

- Start up gradation manually.

Solution 03:
- Increase SGA size bigger than 1024M to affect granule size. Open oracle parameter file with and editor and put following line
sga_max_size = 1028M

Solution 04:
- Run DBUA with -initParam flag.

$ dbua -initParam "_ksmg_granule_size"=16777216
or
$ dbua -initParam "_ksmg_granule_size"=33554432

Non-Oracle user hits SP2-0642 or SP2-1503/SP2-152 after DST patch

Problem Description
The oracle user (owner of the oracle installation) is able to run sql*plus.

However users different than the owner of the installation (Oracle user is usually the owner) are not able to run SQL*Plus after the DST patches were applied at operating system and database software level. Non oracle users who do not belong under dba unix group it fails with

"SP2-0642: SQL*Plus internal error state 2165, context 4294967295:0:0
Unable to proceed"


Or, after applying DST patch (i.e. Patch 5632264) to Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0 it fails with following errors.

$ sqlplus /nolog 
SP2-1503: Unable to initialize Oracle call interface 
SP2-0152: ORACLE may not be functioning properly

Cause of the Problem
SQL*Plus is unable to access $ORACLE_HOME/oracore/zoneinfo/timezone.dat file. The error message is indicating timezone.dat file is missing from install or inadequate permissions to access $ORACLE_HOME/oracore/zoneinfo directory.

If you do ls -l you will see output like,

$ls -l oracore/zoneinfo/timezone.dat
-rw-r----- 1 oracle dba 161096 Mar 10 20:12 oracore/zoneinfo/timezone.dat

From the permission, it is clear that the users others than oracle and not under dba group do not have privileges on the timezone.dat file, so they are not able to read/write the file above. It needs at least read privileges on other unix group.

Solution of the Problem
To implement the solution, execute the following steps:

1. Manually change the privileges for the $ORACLE_HOME/oracore/zoneinfo directory and files.

$ chmod o+r oracore
$ chmod o+r oracore/zoneinfo/timezone.dat

Alternatively, you can do.
$ cd $ORACLE_HOME
$ chmod -R 755 oracore


2. After you change the permission setting SQL*Plus should be working.
$ sqlplus

SP2-1503 SP2-0152 returned after invoking sqlplus as a non-admin user

Problem Symptoms
When logged on to the Windows server as a non-Administrator OS account using Microsoft Terminal Services client (mstsc.exe)/ Remote desktop option, starting SQL*Plus fails with

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

But, starting SQL*Plus works when logged on to the Windows server using an Administrator OS account.

Also it works when logged on locally to the Windows server console using the non-Administrator OS account.

Cause of the Problem
The issue is related to a Windows Security configuration. The problem is caused by a security policy called "Create Global Objects". The user account that is used to run the program does not have the "Create global objects" user right. This security policy was introduced with Windows 2000 SP4, and determines if applications started during a Terminal Services session can create or access globally accessible memory.

By default, members of the Administrators group, the System account, and Services that are started by the Service Control Manager are assigned the "Create global objects" user right. That's why administrators group members do not receive any error while non-administrator members get errors.

Solution of the Problem
Assign the "Create global objects" user right to the non-Administrator account.

1. Click Start, point to Programs, point to Administrative Tools, and then click Local Security Policy.

2. Expand Local Policies, and then click User Rights Assignment.

3. In the right pane, double-click Create global objects.

4. In the Local Security Policy Setting dialog box, click Add.

5. In the Select Users or Group dialog box, click the user account that you want to add, click Add, and then click OK.

6. Click OK.

After 11.2g new installation invoking sqlplus fails with SP2-1503 SP2-0152

Problem Description
After new Oracle 11.2g installation(64 bit) invoking sqlplus returns SP2-1503 SP2-0152 errors like below.

$ sqlplus
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

If you browse to $ORACLE_HOME/oracore/zoneinfo directory and check privilege by using command ls -l you will see a similar output like below,

$cd $ORACLE_HOME/oracore/zoneinfo
$ls -l 
total 10092
drwxr-xr-x 2 oracle dba 4096 May 12 17:02 big
drwxr-xr-x 2 oracle dba 4096 May 12 17:02 little
-rw-r--r-- 1 oracle dba 5725 Jun 12 12:02 readme.txt
-rw-r--r-- 1 oracle dba 25681 Jun 16 12:02 timezdif.csv
-rw-r--r-- 1 oracle dba 792894 Jul 10 10:11 timezlrg_10.dat
-rw-r--r-- 1 oracle dba 787272 Jul 10 10:11 timezlrg_11.dat
-rw-r--r-- 1 oracle dba 493675 Jul 10 10:11 timezlrg_1.dat
-rw-r--r-- 1 oracle dba 507957 Jul 10 10:11 timezlrg_2.dat
-rw-r--r-- 1 oracle dba 527717 Jul 10 10:11 timezlrg_3.dat
-rw-r--r-- 1 oracle dba 531137 Jul 10 10:11 timezlrg_4.dat
-rw-r--r-- 1 oracle dba 587487 Jul 10 10:11 timezlrg_5.dat
-rw-r--r-- 1 oracle dba 586750 Jul 10 10:11 timezlrg_6.dat
-rw-r--r-- 1 oracle dba 601242 Jul 10 10:11 timezlrg_7.dat
-rw-r--r-- 1 oracle dba 616723 Jul 10 10:11 timezlrg_8.dat
-rw-r--r-- 1 oracle dba 801410 Jul 10 10:11 timezlrg_9.dat
-rw-r--r-- 1 oracle dba 345637 Jul 10 10:11 timezone_10.dat
-rw-r--r-- 1 oracle dba 345356 Jul 10 10:11 timezone_11.dat
-rw-r--r-- 1 oracle dba 274427 Jul 10 10:11 timezone_1.dat
-rw-r--r-- 1 oracle dba 274900 Jul 10 10:11 timezone_2.dat
-rw-r--r-- 1 oracle dba 286651 Jul 10 10:11 timezone_3.dat
-rw-r--r-- 1 oracle dba 286264 Jul 10 10:11 timezone_4.dat
-rw-r--r-- 1 oracle dba 286310 Jul 10 10:11 timezone_5.dat
-rw-r--r-- 1 oracle dba 286217 Jul 10 10:11 timezone_6.dat
-rw-r--r-- 1 oracle dba 286815 Jul 10 10:11 timezone_7.dat
-rw-r--r-- 1 oracle dba 302100 Jul 10 10:11 timezone_8.dat
-rw-r--r-- 1 oracle dba 351525 Jul 10 10:11 timezone_9.dat

Cause of the Problem
If you look for the ls -l output you will see timezone.dat and timezlrg.dat are not present but multiple versions timezlrg.* and timezone.* are present. The problem happened because there is a choice of timezone versions are available.

Solution of the Problem
To resolve the problem, create links to one of the timezone_nn.dat files. While creating symbolic links you should choose the latest timezone version unless you have a requirement for a specific version.

$ cd $ORACLE_HOME/oracore/zoneinfo
$ ln -s timezone_11.dat timezone.dat
$ ln -s timezlrg_11.dat timezlrg.dat

How to login to RHEL4 after you have forgotten root password

This post will guide to you what to do after you have forgotten you linux root password and help you how to login as root under in Red Hat Enterprise Linux 4.

The idea is you can log in using single-user mode and create a new root password.

Step 01: Using your machine restart button manually reboot your computer.

Step 02: If you use the default boot loader, GRUB, you can enter single user mode. To do so, at the boot loader menu, use the arrow keys to highlight the installation you want to edit and type [A] to enter into append mode.

Step 03: You are presented with a prompt that looks similar to the following:

grub append> ro root=LABEL=/

Step 04: Press the Spacebar once to add a blank space, then add the word single to tell GRUB to boot into single-user Linux mode. The result should look like the following:

ro root=LABEL=/ single

Step 05: Press [Enter] and GRUB will boot single-user Linux mode. After it finishes loading, you will be presented with a shell prompt similar to the following:

sh-2.05b#

Step 06: You can now change the root password by typing

passwd root
You will be asked to re-type the password for verification. Once you are finished, the password will be changed. You can then reboot by typing reboot at the prompt; then you can log in to root as you normally would.

Difference between connecting to database as normal and sysdba/sysoper

As it is discussed in Database Administrator Authentication, it is said whenever you connect to database as sysdba privilege, you are connecting to SYS default schema. And whenever you are connecting to database as sysoper privilege, you are connecting to PUBLIC default schema.

Note that, sysdba and sysoper are special privilege and therefore certain types of operations can be performed whenever you assign sysdba/sysoper privilege to a user. A complete lists of operations that can be performed by the user who has sysdba/sysoper privilege are listed in the post SYSDBA and SYSOPER authorized operations. A very important thing to remember that, whenever you only assign these two privileges to a user and you don't assign any more privilege then user will not be able to do any schema/table level modification unless you specifically login as sysdba privilege.

With examples I will try to make you more clear between the differences.

sysdba privilege is not enough for a user to login to database unless he login as sysdba privilege
1. Login as sysdba.
E:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jul 17 15:12:05 2010

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


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

2. Create a user named test_sysdba with password test_sysdba.
SQL> create user test_sysdba identified by test_sysdba;

User created.

3. Change default tablespace to users of test_sysdba user.
SQL> alter user test_sysdba default tablespace users;

User altered.

4. Grant sysdba privilege to user test_sysdba.
SQL> grant sysdba to test_sysdba;

Grant succeeded.

As soon as we assign sysbda privilege under password file there will be an entry. By querying v$pwfile_users view we can see an entry.

SQL> select * from v$pwfile_users ;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
TEST_SYSDBA                    TRUE  FALSE FALSE

5. Try to connect to database as test_sysdba
SQL> conn test_sysdba/test_sysdba
ERROR:
ORA-01045: user TEST_SYSDBA lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

As we have not specify "as sysdba" while login so it does not permit test_sysdba to login to database even he has sysdba privilege.

If you don't login as SYSDBA privilege it will act as a normal user
1. Log in as sysdba
SQL> conn / as sysdba
Connected.

2. Grant create session privilege to test_sysdba.
SQL> grant create session to test_sysdba;

Grant succeeded.

3. Now try to login as test_sysdba privilege and try to create table.
SQL> conn test_sysdba/test_sysdba
Connected.

SQL> create table test(col1 number);
create table test(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

The "create table" statement fails as while login we did not specify "sysdba" privilege, and so user has connected to database as normal user.

4. Now connect as sysdba privilege and grant dba to test_sysdba user.
SQL> conn / as sysdba
Connected.

SQL> grant dba to test_sysdba;

Grant succeeded.

5. Connection will be successful as it has dba role but still it is normal test_sysdba user.
SQL> conn test_sysdba/test_sysdba
Connected.

SQL> create table test_sysdba_table1(col1 number);

Table created.

SQL> show user
USER is "TEST_SYSDBA"

Note that the user is TEST_SYSDBA.

Whenever we specify "sysdba privilege" while connecting the schema became SYS
1. Connect to database with test_sysdba user and using sysdba privilege.
SQL> conn test_sysdba/test_sysdba as sysdba
Connected.

SQL> show user
USER is "SYS"

Note that, now user became SYS as we specified "as sysdba" while login.

2. As it is SYS user and any table we create will go under SYS default "SYSTEM" tablespace whereas whenever we connect as normal test_sysdba user it would go under that user default schema.

SQL> create table test_sysdba_table2(col1 number);

Table created.

SQL> set lines 200
SQL> select owner, table_name, tablespace_name from dba_tables where table_name like 'TEST_SYSDBA_TABLE%';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST_SYSDBA                    TEST_SYSDBA_TABLE1             USERS
SYS                            TEST_SYSDBA_TABLE2             SYSTEM

Whenever we connect through sysoper privilege the schema is PUBLIC
SQL> grant sysoper to test_sysdba;

Grant succeeded.

SQL> conn test_sysdba/test_sysdba as sysoper;
Connected.

SQL> show user
USER is "PUBLIC"

SQL> create table test_sysdba_table3(col1 number);
create table test_sysdba_table3(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

So user connecting as sysoper privilege will not be able to create table as PUBLIC user is not permitted so.