Thursday, January 31, 2013

DIM-00014: Cannot open the Windows NT Service Control Manager.



After i took class on how to create database using CREATE database command in windows,

One of My Student tried creating a service using oradim and got the below error.


##########################
#  Error
##########################






##########################
#  Error Occurred
##########################

Error occurs when tried to create a database service using oradim in Windows 7

##########################
## Cause
##########################
  1. User Access Control is Enabled
  2. Owner which ran the Command is not the owner of the oracle software.
##########################
## Solution
##########################

1.  Disable User Access Control by 

  • Click Start, and then click Control Panel.
  • In Control Panel, click User Accounts.
  • In the User Accounts window, click User Accounts.
  • In the User Accounts tasks window, click Turn User Account Control on or off.
2.  Run the command prompt by logging to the owner of the software

3.  Start -> Accessories ->  Right click on Command Prompt 


and select "Run as Administrator".

Wednesday, January 30, 2013

ORA-19815 & ORA-16055 errors comes for different reasons also.



Whenever we see the error ORA-19815, we always assume that FRA is Nearing 100% and that is the reason we received the error.


But today i came across this and found that its not true all the times.


We used to get frequent ORA- errors from one of our Primary Dataguard database and the error recorded in Primary is ORA-16055 : FAL request rejected.


At the same time when we check the standby Alert log, error recorded in Standby is ORA-19815 which indicates that FRA is getting filled and reached 100%



##########################
#  Error
##########################

ORA-00270: error creating archive log
ORA-16055: FAL request rejected
ORA-16041: Remote File Server fatal error
ORA-19815: WARNING: db_recovery_file_dest_size of 424128020480 bytes is 96.93% used, and has 13021216768 remaining bytes available.

##########################
#  Errors Recorded in Primary 
##########################

Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/bdump/primdb_arc2_5171.trc:
ORA-00270: error creating archive log
Mon Jan  7 16:35:49 2013
FAL[server, ARC2]: FAL archive failed, see trace file.
Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/bdump/primdb_arc2_5171.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Mon Jan  7 16:35:49 2013
ORACLE Instance primdb - Archival Error. Archiver continuing.
Mon Jan  7 16:36:03 2013
Errors in file /opt/oracle/admin/primdb/bdump/primdb_arc1_5169.trc:
ORA-16041: Remote File Server fatal error

##########################
#  Errors Recorded in Standby 
##########################

Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/udump/primdb_rfs_17693.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 424128020480 bytes is 96.93% used, and has 13021216768 remaining bytes available.
Mon Jan  7 16:35:49 2013
Errors in file /opt/oracle/admin/primdb/udump/primdb_rfs_17693.trc:
ORA-00270: error creating archive log +primdb_RECO
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 1062207488 bytes disk space from 424128020480 limit

##########################
#  Error Occurred
##########################

Error occurs randomly when RFS process tries to receive redo from Primary.

##########################
## Cause
##########################

Problem is actually caused by the parallel archive options set for Standby database destination in Primary Database.


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=stdbydb OPTIONAL ARCH SYNC NOAFFIRM REOPEN=30 MAX_CONNECTIONS=2 
NET_TIMEOUT=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdbydb

Eventhough we have log_archive_max_processes was 4 which can very well handle the parallel archiving option, error occurs.

I have also tried setting upto 10 archiver process which didnt help.


##########################
## Solution
##########################

As the problem is mainly because of the Parallel archiving option, i have modified to use only one connection for archiving by setting MAX_CONNECTIONS = 1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      service=stdbydb OPTIONAL ARCH SYNC NOAFFIRM REOPEN=30 MAX_CONNECTIONS = 1
 NET_TIMEOUT=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdbydb


  1. Modify MAX_CONNECTIONS=1 in log_archive_dest_2
  2. Defer and Enable log_archive_dest_state_2

This helped me and the false errors stopped recording in my primary and standby alert logs.

Thursday, January 24, 2013

Location of OCR Files


Oracle Cluster Registry (OCR) File location :

Need to find the location of OCR but your CRS is down. Then how we can find?

Below are the methods from which we can find out where your OCR files are stored.
  1. When CRS is up & running
  2. When CRS is down or UP

##########################
#  1) When CRS is Up & Running
##########################

Set ASM environment or crs environment to run the below command.


 ocrcheck 

+ASM3 @ myhost03:/
 ocrcheck 
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3788
Available space (kbytes) : 258332
ID : 519574392
Device/File Name :  +OCR_VOTE 
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user

So here OCR files are stored in    +OCR_VOTE  Diskgroup

##########################
#  2) When CRS is Down or Up
##########################

Look into 
 ocr.loc  file, Location of this file changes depending on the OS,

In Linux Platforms,    /etc/oracle/ocr.loc  

In Solaris Platforms,  /var/opt/oracle/ocr.loc 

+ASM3 @ myhost03:/
> more  /etc/oracle/ocr.loc  
ocrconfig_loc=  +OCR_VOTE 
local_only=FALSE

CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating


We had a Hardware problem in one of our 3 Node RAC Cluster and the 3rd Node needs to be rebooted. So before rebooting the server, we need to bring down all the oracle process to have a clean shutdown.


So i started shutting down all the oracle database instances using srvctl. Finally i was left with ASM instance.


When i tried to shutdown the asm instance got the below error.


##########################
#  Error
##########################

CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.MYDB_RECO.dg', but the force option was not specified


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

#  Full Error 
##########################


> srvctl stop asm -n srvnode03
PRCR-1014 : Failed to stop resource ora.asm
PRCR-1065 : Failed to stop resource ora.asm
CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.MYDB_RECO.dg', but the force option was not specified

##########################
#  Error Occurred
##########################

When i tried to stop the ASM Instance


Command Executed


srvctl stop asm -n srvnode03


Where,


srvnode03  -- is the name of my server which is the 3rd node in my Cluster


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

## Cause
##########################

ASM instance cannot be shutdown if the OCR files are stored in ASM. 

How to Find OCR Files Location?


http://stepintooracledba.blogspot.com/2013/01/location-of-ocr-files.html


##########################
## Solution
##########################

So the only possible way is to stop crs in 3rd node which will automatically bring down the ASM instance also.


crsctl stop crs


Above command can be executed only by root user and the executable is in $GI_HOME/bin 
path 

Thursday, January 17, 2013

ORA-02095: specified initialization parameter cannot be modified


##########################
#  Error
##########################

ORA-02095: specified initialization parameter cannot be modified

ORA-02095 Error may mislead, check the below post,

http://stepintooracledba.blogspot.com/2013/02/ora-02095-misleading.html

##########################
#  Error Occurred
##########################

When i tried to change processes parameter as SYS user.

Command Executed

alter system set processes=300;

(or)

alter system set processes=300 scope=both;

##########################
## Cause
##########################

Processes parameter is a STATIC Parameter which cannot be changed at instance level.

##########################
## Solution
##########################

ALTER SYSTEM command with,

SCOPE=both  --- Applies the changes in the instance level as well as the spfile used for Startup

If SCOPE is not used, then it applies the changes at instance level.

  • Check Database is Using SPFILE

SQL>sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DB1_DATA/db1/spfiledb1.ora


From above command we can confirm that database is using spfile, so next we need to check whether the parameter we are going to change can be modified at instance level.


set lines 200
col name for a35
col value for a25

Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name='processes';

NAME                                VALUE                     ISDEFAULT ISSES ISSYS_MOD
----------------------------------- ------------------------- --------- ----- ---------
processes                           600                       FALSE     FALSE FALSE


From the Above output we can find that whether a parameter can be modified at INSTANCE level.

ISSES_MODIFIABLE 

VARCHAR2(5) 

TRUE - the parameter can be changed with ALTER SESSION
FALSE - the parameter cannot be changed with ALTER SESSION 

ISSYS_MODIFIABLE 

VARCHAR2(9) 

IMMEDIATE - the parameter can be changed with ALTER SYSTEM
DEFERRED - the parameter cannot be changed until the next session
FALSE - the parameter cannot be changed with ALTER SYSTEM 

So our processes parameter cannot be modified at instance level. So we need to update only the spfile, which will be reflected in the next instance startup.


SQL>alter system set processes=300 scope=spfile;

System altered.



SQL>sho parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     150


See, Here parameter value is not changed at the INSTANCE level, but its updated in SPFILE. Once we bounce the database it will be reflected.


SQL>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1426065608 bytes
Database Buffers          671088640 bytes
Redo Buffers               38502400 bytes
Database mounted.
Database opened.
SQL>sho parameter process

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     300


For Eg :


NAME                                VALUE                     ISDEFAULT ISSES ISSYS_MOD
----------------------------------- ------------------------- --------- ----- ---------
log_archive_max_processes           4                         TRUE      FALSE IMMEDIATE


log_archive_max_processes parameter can be changed immediately via ALTER SYSTEM command and it will be applicable at instance level.


Thursday, January 3, 2013

ORA-09925: Unable to create audit trail file & SVR4 Error: 49: Disc quota exceeded

##########################
#  Error
##########################

ORA-09925: Unable to create audit trail file

##########################
#  Full Error 
##########################

ORA-09925: Unable to create audit trail file
SVR4 Error: 49: Disc quota exceeded
Additional information: 9925
ORA-01075: you are currently logged on

##########################
#  Error Occurred
##########################

When i tried to login to the database as SYS user.

Command Executed

sqlplus / as sysdba

##########################
## Cause
##########################

There may be many reasons for this ORA-09925 Error. Possibly because of,
  1. FRA Become Full
  2. Number of Files Quota Exceeded 
  3. ORACLE_HOME is 100% full
##########################
## Solution
##########################
  • FRA Become Full
Some times this happens when FRA becomes 100%, check the usage of FRA and clear it. Run below query to check FRA usage.

Select * from v$flash_recovery_area_usage;

If Archive logs occupied FRA, then run a backup of archivelogs with delete input option.

  • Number of Files Quota Exceeded 

SVR4 Error: 49: Disc quota exceeded

The above error accompanied ORA-09925 Error, so there is a possibility that the disk quota might have exceeded. Run the below command to check quota for the User.


mydb @server01:/opt/oracle/admin/mydb/bdump

quota -v
Disk quotas for oracle (uid 1001):
Filesystem     usage  quota  limit    timeleft  files  quota  limit    timeleft
/home/username
               898688 819200 1024000                380     -1     -1



From the above output we can see that usage(898688) is which is higher than the quota(819200). So reduce the number of files created. Some times Audit files generation will be high with a bunch of files. So clearing the audit files will help to overcome this error.

There are chances that some times the quota might have not exceeded.

In the below output, usage is very less still Disk quota Exceeded Error.

mydb @server01:/opt/oracle/admin/mydb/bdump

quota -v
Disk quotas for oracle (uid 1001):
Filesystem     usage  quota  limit    timeleft  files  quota  limit    timeleft
/home/username
               89688 819200 1024000                380     -1     -1

  • ORACLE_HOME is 100% full

In the above case problem is ORACLE_HOME has 100% FULL. 


Run df -h and Clear the Files.

Some times Space will be not be cleared after removing files also. This is because the deleted FILES are being used by ORALCE or Other Process. Use lsof command to check the List of Open Files.

If the files are used by database, then bouncing the database will release the space used.


Wednesday, January 2, 2013

ERROR: NMO not setuid-root (Unix-only)


##########################
#  Error
##########################

ERROR: NMO not setuid-root (Unix-only)

##########################
#  Full Error 
##########################

ERROR: NMO not setuid-root (Unix-only)

##########################
#  Cause
##########################

Error occurred while running a shell script from enterprise manager. We have a shell scripts which will delete, archive database log files (dump, trace, alert log files).

##########################
#  Command Executed
##########################

Ran a shell script from Enterprise Manager

##########################
## Solution
##########################

### 11.2.0.1.0

nmo and nmb Agent Binary files are owned by Oracle user.

Actually these files needs to have root ownership. After installing Agent home in the server, the permission from oracle user will be changed by root.sh script which will be ran at the end of the Agent home installation.

Before :


agent11g @ server01:/opt/oracle/product/11.2/agent11g/bin
> ls -al nm?
-rws--x---   1 oracle   dba        22472 Mar 16  2012 nmb
-rws--x---   1 oracle   dba        29360 Mar 16  2012 nmo

Reason for improper file ownership may be because,

  1. Root.sh script is not run at the end
  2. there may be a interruption while root.sh script was ran.
Simple solution to fix this error is, change the ownership from oracle to root user.

Run below commands as Root User :

sudo chown root /opt/oracle/product/11.2/agent11g/bin/nmo
sudo chown root /opt/oracle/product/11.2/agent11g/bin/nmb

After :

no_sid @ server01:/opt/oracle/product/10.2/agent11g/bin
> ls -al nm?
-rws--x---   1 root     dba        22472 Mar 16  2012 nmb
-rws--x---   1 root     dba        29360 Mar 16  2012 nmo

Above is for 11g Agent Home.

A 10g Home Should look like below,

no_sid @ server01:/opt/oracle/product/10.2/agent10g/bin
> ls -al nm?
-rwsr-s---   1 root     dba        22472 Mar 16  2012 nmb
-rwsr-s---   1 root     dba        29360 Mar 16  2012 nmo

Note :

If root.sh script is not run then recommended option is to run root.sh script rather than giving just permission to these files, becuase root.sh does relinking of binaries and other activities.