Archive for July, 2013

How to (re)configure Oracle Enterprise Manager – unexpected part II.

2013-07-30 1 comment

When I wrote about (re)configuring Oracle Enterprise Manager last time, I wrote something like that: “When you have a problem with creating the Oracle EM via command line (you’ve got the insufficient privileges error) try to use the Database Configuration Assistant“. I think it’s time to be more specific 🙂
Suppose, we did the following:

set ORACLE_SID=orcl
emca -config dbcontrol db -repos create

and get the insufficient privileges error:

STARTED EMCA at Jun 28, 2012 11:08:59 AM
EM Configuration Assistant, Version Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID:  orcl
Exception in thread "main" oracle.sysman.emcp.exception.DatabaseUnavailableException: Database instance unavailable.
        at oracle.sysman.emcp.DatabaseChecks.throwDBUnavailableException(
        at oracle.sysman.emcp.DatabaseChecks.checkDbAvailabilityImpl(
        at oracle.sysman.emcp.DatabaseChecks.checkDbAvailability(
        at oracle.sysman.emcp.DatabaseChecks.getDbServiceName(
        at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(
        at oracle.sysman.emcp.EMConfigAssistant.statusMain(
        at oracle.sysman.emcp.EMConfigAssistant.main(

and the emca log contains the below details:

oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
CONFIG: ORA-01031: insufficient privileges
oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-01031: insufficient privileges
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.connect(
at oracle.sysman.emcp.util.GeneralUtil.initSQLEngineLoacly(
at oracle.sysman.emcp.util.GeneralUtil.initSQLEngine(
at oracle.sysman.emcp.DatabaseChecks.checkDbAvailabilityImpl(

From the above logs, we find two hints: database instance is down and ORA-01031. So, some possible reasons may be:
1. Database may not be up – confirm that the database is up. Connect to the database via SQLPLUS

sqlplus / as sysdba

and do

select instance_name, status, version from v$instance;

the output should be similar to that:

------------- ------ ----------
orcl          OPEN

2. Confirm whether the ORACLE_HOME environmental variable is correct. This involves comparing the current ORACLE_HOME environmental setting, with the one that was set the last time the database was started.
To check the current ORACLE_HOME (MS Windows):


To check the ORACLE_HOME which was set at the time when the database was last started, connect to SQLPLUS (sys as sysdba) and issue the following:

var OHM varchar2(100);
exec dbms_system.get_env('ORACLE_HOME', :OHM);
print OHM;

If the ORACLE_HOME was set wrongly at the time that the database was started (as determined by above steps), then the database will need to be shut down and restarted. To shut the database down, it will be necessary to set the “wrong” ORACLE_HOME (the one which is returned in the OHM output). After the database has been successfully shut down, then the correct ORACLE_HOME should be set, and the database can be re-started.

3. Confirm whether it’s possible to connect to the database as the SYSDBA user remotely, using sqlplus. As part of the repository creation/drop process, the SYS user needs to be able to connect as SYSDBA. The “ORA-01031: insufficient privileges error” is returned when the SYS user is unable to connect as SYSDBA. To test this outside of dbconsole, test a sqlplus remote connection (one which connects via a tnsnames.ora alias) as sysdba, eg:

sqlplus sys/@ as sysdba

If the above returns the ORA-01031 error check whether the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set correctly in the database. For the purposes of configuring dbconsole, it must be set to ‘EXCLUSIVE’. This value can be checked this way:

show parameter remote_login_passwordfile

and the output should be like that

NAME                      TYPE   VALUE
------------------------- ------ ---------
remote_login_passwordfile string EXCLUSIVE

If this parameter is not set to ‘EXCLUSIVE’, the following command can be run:

alter system set remote_login_passwordfile=EXCLUSIVE scope=both;

For dbconsole, and check whether the %ORACLE_HOME%\network\admin\sqlnet.ora (or %TNS_ADMIN%\sqlnet.ora) contains the entry:


In my case the value of SQLNET.AUTHENTICATION_SERVICES was NONE. So, I changed to NTS and think that finally I’ll be able to configure the OEM. Unfortunatelly, still there was an error. So, I started to dig up the logfiles in the %ORACLE_HOME%\_\sysman\log folder and there found the source of the problem (the OracleDBConsolesrvc.log and emdb.nohup are especially useful).


Yet another post about new earthshaking feature in Oracle 12c.

Dont’ worry – this post (despite the title) won’t be about yet another new earthshaking feature in Oracle 12c. In fact you WILL NOT find here any post about Oracle 12c. At least until Oracle 12c R2 will be ready.
I’ve started to work with Oracle from version 9.2, then Oracle 10g R2, and then Oracle 11g R2. I see no reasons to change something and I’ll wait calmly for the version 12c R2.
By the way, the Oracle 12c pluggable database is a feature (as one Oracle executive has called a “fundamental” architectural change for database technology) that SQL Server had already for at least 10 years.

Categories: Oracle, RDBMS Tags:

Exploring the LOCAL_LISTENER parameter.

The original version of the below article was created by Ed Stevens and could be find here.
In one of my previous post “Resolving Oracle networking problems – ORA-12514” I mentioned the role of the initialization parameter LOCAL_LISTENER in dynamic registration of the database instance to the listener. Now I’d like to deliver on my promise to explore that piece of the puzzle.

The LOCAL_LISTENER initialization parameter.
So we’ve seen that the listener is able to start up and successfully handle connection requests without a listener.ora configuration file. It does this by using all defaults (including the listener name and port) and the database instance is able to register itself with the listener.
How does the instance know how to contact the listener in order to register itself? It uses the initialization parameter LOCAL_LISTENER. From the Oracle Database Reference 11g Release 2 (11.2) we read:
LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners
Let’s see what my instance says about that:

OK, so the instance is supposed to use LOCAL_LISTENER to locate the listener so that it (the instance) can register itself with the listener. But I don’t have LOCAL_LISTENER set to anything. Well, it so happens that LOCAL_LISTENER has a default value that dovetails nicely with the default settings of the listener. Again, from the Reference manual:
Default value: (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host.
I’ve seldom found a good reason NOT to run the listener with anything other the default name and port, but some people insist, and that’s when we need to adjust LOCAL_LISTENER to match up.

A mismatch has been detected between Redo records and Rollback (Undo) records ORA-00600/ORA-00607.

Last night I got the following error on one of my database (Oracle 9i):

ORA-00600: internal error code, arguments: [4193], [4271], [4056], ...

The Oracle Support gives more information about it:

versions 6.0 to 10.1
We are validating the Undo block sequence number in the undo block against
the Redo block sequence number relating to the change being applied.
This error is reported when this validation fails.
process failure
possible rollback segment corruption
This error may indicate a rollback segment corruption.
This may require a recovery from a database backup depending on the situation.

Here is the possible solution without recovery from a database backup. In SQLPLUS type the following command:

show parameter undo;

The output should be similar to this:

NAME              TYPE     VALUE
----------------- -------- ---------
undo_management   string   AUTO
undo_retention    integer  900
undo_tablespace   string   UNDOTBS

If the parameter UNDO_MANAGEMENT is set to AUTO, then change it to MANUAL (in my case I have the database on Windows Server and use the spfile):

alter system set undo_management='MANUAL' scope=spfile;
shutdown immediate;
create undo tablespace NEW_UNDOTBS datafile 'e:\oracle\oradata\SID\NEW_UNDOTBS01.DBF' size 1G;
alter system set undo_tablespace='NEW_UNDOTBS' scope=spfile;
alter system set undo_management='AUTO' scope=spfile;

After another shutdown the new parameters should be taken and if you want you can drop old UNDO tablespace. Sometimes when you try to do it, you can get the error similar to that:

drop tablespace UNDOTBS01 including contents;
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

How to handle that situation?
In SQLPLUS type the following command:

select * from v$rollname;

The output should be similar to this:

USN        NAME
---------- -----------------
0           SYSTEM
1          _SYSSMU1$
2          _SYSSMU2$
3          _SYSSMU3$
4          _SYSSMU4$
5          _SYSSMU5$
6          _SYSSMU6$

You need to create PFILE from SPFILE and shutdown the database:

create pfile='c:\new.init' from spfile;
File Created
shutdown immediate;

Then edit pfile and made the following changes:


Then startup database from new pfile, then try to drop old UNDO tablespace, this way should work now. After drop old UNDO tablespace shutdown the database and start it up with the spfile.