Sunday, March 29, 2015

ORA-01157: cannot identify/lock data file

While starting up a database, you may face following error messages which would not allow you to open the database.

ORA-01157: cannot identify/lock data file <file#> - see DBWR trace file
ORA-01110: data file <file#>: <file_name>

Tuesday, March 24, 2015

Manual upgrading Oracle database 11g to 12c

If you want to know how we upgrade an 11g database to 12c using DBUA, click here
For upgrading 12.1.0.1 to 12.1.0.2 using DBUA, click here.

This article explains how to upgrade 11.2.0.2 and above, and 12.1.0.1 to 12.1.0.2 manually.
For details of each step, take a look at official 12c

Wednesday, March 18, 2015

ORACLE.EXE (SHAD) Processes Keep Increasing

On Windows based Oracle installations, if you hit an issue whereby you see that processes/sessions keep increasing unabated, and you keep receiving “ORA-00020: maximum number of processes exceeded” error while connecting to the database and increasing the value of parameter “processes” remains insufficient always,

ORA-00020: maximum number of processes exceeded

You may face this error message while connecting to the database instance which will also hinder you connecting with the database instance. The reason of this error is the value of init parameter “processes” which requires to be increased to allow Oracle instance to span a new process for new session request. For example, if value of this parameter is set to 100,

Tuesday, March 17, 2015

The OracleService service terminated unexpectedly

On Windows platform, the OS level service which has a name like “OracleService<SID>”, needs to be running for the database to work. If this service stops, database also stops. You may be in a situation where you receive complaints about database sudden crash, which requires DBA intervention to start this service and database. If you open Windows event viewer, it would show a message as follows.

The OracleService<SID> service terminated unexpectedly.  It has done this <n> time(s).  The following corrective action will be taken in <n>milliseconds:
Restart the service.

Most common reason of this error is resource contention. If you face this issue, you should start monitoring the system resources (CPU, RAM, Page file) usage. OSWatcher is a good tool to accomplish this. Once this issue happens, the monitoring tool should show you which resources consumption is the actual cause of this service crash – and you can take corrective actions.

Once I faced this error; and OSWatcher showed me that at the time of service crash, RAM/Pagefile was fully consumed, and the reason for this full RAM consumption was huge memory consumption by a service called “Oracle <SID> VSS Writer Service”. This service is part of Oracle starting 11g, and in 12c, the display name of this service is like “OracleVssWriter<SID>”. For this particular case, this is because of a bug (memory leek by this service) which will be resolved in 12.2. As a workaround, you should disable/stop this service if you are not using this service for Volume Shadow Copy, or stop this service just after you finish your shadow copy snapshot and then start it again when you next time need to create a snapshot.


I also faced this issue at a customer site where resource consumption was OK. Only one issue I found was that Oracle 10.2 was installed on Windows 2008 R2, which is an unsupported platform for Oracle 10.2. So I could just suspect that this unsupported installation might be causing this service crash. I suggested them to do a re-install on Windows 2008 which is supported for Oracle 10.2

Monday, March 16, 2015

ORA-06553: PLS-801: internal error [56319]

You will face this error while connecting to a database which has word size (32-bit or 64-bit) different than the actual installation binaries (32-bit or 64-bit). It means that if database was created (and running) on a 64-bit platform and then you move/restore same database to a 32-bit platform, or vice versa, you will be facing this problem.

You may also face this error if you have a standby database running on a platform which has Oracle binaries different than the binaries of primary database platform, and you perform a switchover. After the switchover, you might be facing same error.

Reason of this error is that PL/SQL objects (functions, procedures, packages) were compiled using a different word size (suppose 32-bit) when database was created/running, and now you restore/move this database to a 64-bit platform and hence these objects code is inconsistent with the binaries.

To solve this problem, you first need to invalidate all PL/SQL objects and then re-compile them so that new compiled code becomes consistent with the available binaries.

1.      Startup database with STARTUP UPGRADE option
2.      Invalidate the PL/SQL objects by running $ORACLE_HOME/rdbms/admin/utlip.sql
3.      Validate the PL/SQL objects by running $ORACLE_HOME/rdbms/utlrp.sql
4.      Shutdown the database
5.      Startup the database

Tuesday, March 10, 2015

OEM Database Control Setup fails with ACCESS DENIED error

On Windows platform, if OEM Database Control configuration fails with “SEVERE: Error instantiating EM configuration files” message, this could be because of permission issue of the OS user doing the OEM configuration. See bellow

C:\Users>emca -config dbcontrol db -repos create

STARTED EMCA at Mar 11, 2015 11:46:03 AM
EM Configuration Assistant, Version 11.2.0.3.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.

Enter the following information:
Database SID: mydb
Listener ORACLE_HOME [ D:\ORACLE\1120 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ D:\ORACLE\1120

Local hostname ................ MYSERVER
Listener ORACLE_HOME ................ D:\ORACLE\1120
Listener port number ................ 1521
Database SID ................ mydb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.EMConfig perform
SEVERE: Error instantiating EM configuration files
Refer to the log file at D:\ORACLE\cfgtoollogs\emca\mydb\emca_2015_03_11_11_46_02.log for more details.
Could not complete the configuration. Refer to the log file at D:\ORACLE\cfgtoollogs\emca\mydb\emca_2015_03_11_11_46_02.log for more details.

C:\Users>


Screenshot of command prompt could be as follows















If we open the log file mentioned above, it would show messages as follows. Highlighted in red is actually a hint that were actual problem is.
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
CONFIG: Exit value of 255
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
CONFIG: Creating shared install...
Source location: D:\ORACLE\1120
Destination (shared install) : D:\ORACLE\1120\PMS03198DBB_mydb
DeployMode : dbconsole

Creating directories...
Setting console properties ...
Setting log and trace files locations for Console ...
Setting log and trace files locations for Agent ...

Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
CONFIG: OpenSCManager FAILED: Access is denied.

Service creation failed. Aborting...

Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
WARNING: Error executing CMD /C D:\ORACLE\1120\bin\emctl.bat deploy dbconsole -d mydb D:\ORACLE\1120\PMS03198DBB_mydb PMS03198DBB:3938 PMS03198DBB mydb
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.EMAgentConfig instantiateEMConfigFiles
CONFIG: Failed to deploy state dirs
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.EMConfig perform
SEVERE: Error instantiating EM configuration files
Refer to the log file at D:\ORACLE\cfgtoollogs\emca\mydb\emca_2015_03_11_11_46_02.log for more details.
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.EMConfig perform
CONFIG: Stack Trace:
oracle.sysman.emcp.exception.EMConfigException: Error instantiating EM configuration files
            at oracle.sysman.emcp.EMAgentConfig.updateAgentConfigFiles(EMAgentConfig.java:3481)
            at oracle.sysman.emcp.EMAgentConfig.performConfiguration(EMAgentConfig.java:1465)
            at oracle.sysman.emcp.EMAgentConfig.invoke(EMAgentConfig.java:300)
            at oracle.sysman.emcp.EMAgentConfig.invoke(EMAgentConfig.java:278)
            at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:259)
            at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:599)
            at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1482)
            at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:583)
            at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:529)
Mar 11, 2015 11:58:45 AM oracle.sysman.emcp.EMConfig restoreOuiLoc
CONFIG: Restoring oracle.installer.oui_loc to D:\ORACLE\1120\oui

Solution
Actual problem is related to the permission of the user, which is not allowing the configuration command to create the OS level service for OEM. To solve the problem, the command prompt (to execute the OEM configuration command) should be opened with Administrator privileges, as follows.