Custom Search

Thursday, October 16, 2014

ORA-15183: ASMLIB initialization error [driver/agent not installed] in oracle database at linux server.



ORA-15183: ASMLIB initialization error [driver/agent not installed]

After reinstalling grid infrastructure and oracle software  or applying patch (PSU) in linux server. You may face below error (and in alert log ORA-15183 error) when you will try to startup the oracle database.
Only three command give you the solution.  Solution is below.


SQL>
SQL> host rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Oct 15 15:05:21 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup     

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 10/15/2014 15:05:41
ORA-03113: end-of-file on communication channel
Process ID: 16205
Session ID: 199 Serial number: 44057

RMAN> shutdown immediate

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 10/16/2014 10:23:44
RMAN-12001: could not open channel default
RMAN-10008: could not create channel context
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE

RMAN> exit






Alert log content:

NOTE: ASMB registering with ASM instance as Standard client 0xffffffffffffffff (reg:4060941312) (new connection)
Wed Oct 15 15:05:37 2014
Errors in file /userdata/app/oracle/diag/rdbms/icmsdb1/ICMSDB/trace/ICMSDB_rbal_16178.trc:
ORA-15183: ASMLIB initialization error [driver/agent not installed]
Wed Oct 15 15:05:37 2014
WARNING: FAILED to load library: /opt/oracle/extapi/64/asm/orcl/1/libasm.so
Wed Oct 15 15:05:37 2014
starting up 1 shared server(s) ...
Wed Oct 15 15:05:37 2014
NOTE: ASMB connected to ASM instance +ASM osid: 16186 (Flex mode; client id 0xffffffffffffffff)
NOTE: initiating MARK startup
Starting background process MARK
Wed Oct 15 15:05:37 2014
MARK started with pid=27, OS id=16192
Wed Oct 15 15:05:37 2014
NOTE: MARK has subscribed
ORACLE_BASE from environment = /userdata/app/oracle
Wed Oct 15 15:05:39 2014
alter database mount
Wed Oct 15 15:05:40 2014
NOTE: ASMB mounting group 1 (DATA)
Wed Oct 15 15:05:40 2014
WARNING: cellinit.ora is missing. RBAL is terminating the instance.
RBAL (ospid: 16178): terminating the instance due to error 27625
Wed Oct 15 15:05:41 2014
System state dump requested by (instance=1, osid=16178 (RBAL)), summary=[abnormal instance termination].
System State dumped to trace file /userdata/app/oracle/diag/rdbms/icmsdb1/ICMSDB/trace/ICMSDB_diag_16152_20141015150541.trc
Wed Oct 15 15:05:41 2014
Dumping diagnostic data in directory=[cdmp_20141015150541], requested by (instance=1, osid=16178 (RBAL)), summary=[abnormal instance termination].
Wed Oct 15 15:05:41 2014
Instance terminated by RBAL, pid = 16178








Cause:

This is an Ownership issue. After reinstalling grid and oracle software, or applying Patch, it changed the group (ASMADMIN) to (OINSTALL) for ORACLE user and   $ORACLE_HOME/bin folder. So now you have to change the group back again to ASMADMIN and give appropriate permission also.
Solution:

With root user :-

[oracle@icms-test-db-st bin]$
[oracle@icms-test-db-st bin]$
[oracle@icms-test-db-st bin]$ su - root
Password:
[root@icms-test-db-st ~]#
[root@icms-test-db-st ~]#
[root@icms-test-db-st ~]# cd $ORACLE_HOME/bin
[root@icms-test-db-st bin]#
[root@icms-test-db-st bin]# chgrp asmadmin oracle
[root@icms-test-db-st bin]#
[root@icms-test-db-st bin]# chmod 6751 oracle
[root@icms-test-db-st bin]#
[root@icms-test-db-st bin]#



After that Database will be startup ..

SQL>
SQL>
SQL>
SQL> host rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Oct 16 10:24:01 2014

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount ;

Oracle instance started
database mounted

Total System Global Area    4110417920 bytes

Fixed Size                     2932096 bytes
Variable Size               2197815936 bytes
Database Buffers            1895825408 bytes
Redo Buffers                  13844480 bytes

RMAN>



Wednesday, September 17, 2014

Enq: TM - contention waits in oracle database and solution !

Today I was noticed that, one delete (5 rows ) statement taking so much time in a particular table only. then I looked into it, found  "Enq: TM - contention" waits taking times .....
In this table there is a foreign key relationship with other table.
and  "Enq: TM - contention" indicate there are unindexed foreign key constraints. so I just created an index on that foreign key column or columns (in foreign table) it solved the problem.

Here is a script that can find  unindexed foreign key constraints for a specific user .....

SELECT * FROM (
SELECT c.table_name, cc.column_name, cc.position column_position
FROM   user_constraints c, user_cons_columns cc
WHERE  c.constraint_name = cc.constraint_name
AND    c.constraint_type = 'R'
MINUS
SELECT i.table_name, ic.column_name, ic.column_position
FROM   user_indexes i, user_ind_columns ic
WHERE  i.index_name = ic.index_name
)
ORDER BY table_name, column_position;

Saturday, December 21, 2013

How to send a email if any ERROR occurs in oracle database alert log files in oracle 10g, 11g, 12c. Steps

 1) Create a external table for the alert log content.  
  How to create external table for alert log.  
 2) Create a view from this external table like...  
 ---------------------------------------------------------  
   CREATE OR REPLACE FORCE VIEW DBA_MAIN.VW_ALERT_LOG  
 (  
   NO_OF_LINE,  
   TEXT  
 )  
 AS  
   SELECT NO_of_line, text  
    FROM ( SELECT ROWNUM NO_of_line, text  
         FROM dba_main.alert_log_external  
       ORDER BY ROWNUM DESC)  
   WHERE UPPER (text) LIKE UPPER ('%ERROR%')   
 /  
 -----------------------------------------------------------  
 3) Create a table from this above view like....  
 -------------------------------------------------------------  
 CREATE TABLE DBA_MAIN.TBL_ALERT_LOG_ERROR  
 AS  
   SELECT No_of_line, text, 'Y' send_flg FROM DBA_MAIN.VW_ALERT_LOG;  
 ---------------------------------------------------------------  
 3) Create a email address table and insert some email addresses like below...  
 ------------------------------------------------------------  
 CREATE TABLE DBA_MAIN.EMAIL_ADDRESS  
 (  
  ID    NUMBER,  
  NAME   VARCHAR2(50 BYTE),  
  EMAIL   VARCHAR2(100 BYTE),  
  ACT_FLAG VARCHAR2(1 BYTE)          DEFAULT 'Y'  
 ) ;  
 Insert into DBA_MAIN.EMAIL_ADDRESS  
   (ID, NAME, EMAIL, ACT_FLAG)  
  Values  
   (1, 'Abdul Halim', 'halim@test.com', 'Y');  
 Insert into DBA_MAIN.EMAIL_ADDRESS  
   (ID, NAME, EMAIL, ACT_FLAG)  
  Values  
   (2, 'user2', 'test@test.com', 'N');  
 Insert into DBA_MAIN.EMAIL_ADDRESS  
   (ID, NAME, EMAIL, ACT_FLAG)  
  Values  
   (3, 'user1', 'test2@test.com', 'N');  
 COMMIT;  
 4) Create a package for sending email from the oracle database   
 (In this package, i created two procedures , one for sending email with attachment   
 another is sending email without attachment ment. I am using first one here)  
 -------------------------------------------------------------  
 SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment  
 -----------------------------------------------------------  
 4) create belows trigger on "DBA_MAIN.TBL_ALERT_LOG_ERROR" this table..  
 --------------------------------------------------------------------------  
 CREATE OR REPLACE TRIGGER DBA_MAIN.TRG_ALERT_LOG_ERROR_EMAIL  
 before INSERT ON DBA_MAIN.TBL_ALERT_LOG_ERROR  
 REFERENCING NEW AS new     
 FOR EACH ROW  
 WHEN (  
 new.send_flg='N'  
    )  
 begin  
 /*  
  SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment  
     ( sender_email_address     varchar2, -- Must be single E-mail address  
      recipient_email_addresses  varchar2,   
      email_subject        varchar2,  
      email_body_text       varchar2,  
      email_attachment_text    clob,   -- No restriction on size of text  
      email_attachment_file_name  varchar2    
     )  
    */  
  for i in (select email from dba_main.email_address where act_flag='Y') loop   
  SEND_EMAIL_FROM_PL_SQL.send_email_with_attachment  
     ( 'halim@test.com', -- Must be single E-mail address  
      i.email,      'Test Email from Database!',  
      'Plz check the Alert for more info.... ' || :new.text,  
      :new.text,   -- No restriction on size of text  
      'Attention'   
     ) ;  
     end loop;  
 end;  
 /  
 -------------------------------------------------------------------------  
 5) create a procedure...for job....  
 ---------------------------------------------------------  
 CREATE OR REPLACE PROCEDURE dpr_alter_log_error_marge  
 IS  
 BEGIN  
   MERGE INTO TBL_ALERT_LOG_ERROR f_table  
     USING DBA_MAIN.VW_ALERT_LOG v_table  
       ON (v_table.text = f_table.text) --- and UPPER (v_table.text) LIKE UPPER ('%ERROR%') )  
   WHEN MATCHED  
   THEN  
    UPDATE SET f_table.SEND_FLG = 'Y'  
   WHEN NOT MATCHED  
   THEN  
    INSERT   (NO_OF_LINE, TEXT, SEND_FLG)  
      VALUES (ROWNUM, v_table.text, 'N');  
 END;  
 ----------------------------------------------------------  
 6) create a job like belows ( it will check alert log error and if found then send it email to DBA..  
 -----------------------------------------------------------  
 DECLARE  
  X NUMBER;  
 BEGIN  
  SYS.DBMS_JOB.SUBMIT  
  ( job    => X   
   ,what   => 'DBA_MAIN.DPR_ALTER_LOG_ERROR_MARGE;'  
   ,next_date => to_date('20/12/2013 10:55:34','dd/mm/yyyy hh24:mi:ss')  
   ,interval => 'SYSDATE+60/1440 '  
   ,no_parse => FALSE  
  );  
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));  
 COMMIT;  
 END;  
 /  
 ------------------------------------------------------------  

Wednesday, October 2, 2013

Windows batch job script for oracle export import backup

windows batch job script for oracle export (and import) backup (2 copy 1. ORCL 2. ORCL_1)

----------------------------------------------------
@echo off
set ORACLE_SID=ORCL

if exist C:\oracle_exp\export_backup\ORCL\ORCL.log copy C:\oracle_exp\export_backup\ORCL\ORCL.log C:\oracle_exp\export_backup\ORCL\ORCL_1.log >NUL:

if exist C:\oracle_exp\export_backup\ORCL\ORCL.dmp copy C:\oracle_exp\export_backup\ORCL\ORCL.dmp C:\oracle_exp\export_backup\ORCL\ORCL_1.dmp >NUL:

exp 'sys/sys@ORCL_10g as sysdba' full=yes file=C:\oracle_exp\export_backup\ORCL\ORCL.dmp log=C:\oracle_exp\export_backup\ORCL\ORCL.log
-----------------------------------------------------------------------------------

===========================================
 =========================================
  Expdp logical backup with windows batch job script
  ========================================
==========================================



 The below script is for windows batch job to backup an Oracle database 10g/11g with expdp (datapump) utility. This script setting oracle_sid, NLS_LANG, some local and remote (mapped) drive locations and Archive_program (7-Zip) location.
it creating and zipped an expdb backup, then copied to various location. (local and remote).

This script keeping three copy of backup file to the local location and eight copy to the remote location.

[Note: select batch job option "Run whether user is logged on or not" and "run with highest Privileges" and give the user password once.]

--------------------------------------------------------------------------------------------------------------------
@echo off

set ORACLE_SID=HALIMDB
set NLS_LANG=.WE8MSWIN1252

SET Today=%Date:~4,2%-%Date:~7,2%-%Date:~10,4%


SET BASE_FOLDER=C:\oracle_exp\export_backup\HALIMDB
SET BASE_NAME=zip_%ORACLE_SID%
SET DUMPFILE_FOLDER=C:\oracle_exp\export_backup\HALIMDB

SET ARCHIVE_PROGRAM="C:\Program Files\7-Zip\7z.exe"

SET REMOTE_LOC=\\sbwc_share\h$\10g_backup_220_important\Export_backup_10g_220\HALIMDB


SET DUMPFILE_NAME=%ORACLE_SID%.dmp
SET LOGFILE_NAME=%ORACLE_SID%.log
SET BACKUP_FOLDER=%BASE_FOLDER%


SET BACKUP_FILENAME=%BASE_NAME%


REM Script begin
REM expdp, full backup to expdb_dw_dump_dir,
expdp 'EXPDP_HALIMDB/EXPDP_HALIMDB123@HALIMDB' full=yes directory=expdb_HALIMDB_dump_dir dumpfile=%DUMPFILE_NAME% logfile=%LOGFILE_NAME% PARALLEL=5

IF %ERRORLEVEL% NEQ 0 GOTO ERROR


if exist %BACKUP_FOLDER%\%BACKUP_FILENAME%_2.zip copy %BACKUP_FOLDER%\%BACKUP_FILENAME%_2.zip %BACKUP_FOLDER%\%BACKUP_FILENAME%_3.zip /Y
 if exist %BACKUP_FOLDER%\%BACKUP_FILENAME%_1.zip copy %BACKUP_FOLDER%\%BACKUP_FILENAME%_1.zip %BACKUP_FOLDER%\%BACKUP_FILENAME%_2.zip /Y
if exist %BACKUP_FOLDER%\%BACKUP_FILENAME%.zip copy %BACKUP_FOLDER%\%BACKUP_FILENAME%.zip %BACKUP_FOLDER%\%BACKUP_FILENAME%_1.zip /Y



%ARCHIVE_PROGRAM% a -tzip %BACKUP_FOLDER%\%BACKUP_FILENAME% %DUMPFILE_FOLDER%\%DUMPFILE_NAME% %DUMPFILE_FOLDER%\%LOGFILE_NAME%


IF %ERRORLEVEL% NEQ 0 GOTO ERROR

if exist %REMOTE_LOC%\%BACKUP_FILENAME%_6.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_6.zip %REMOTE_LOC%\%BACKUP_FILENAME%_7.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_5.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_5.zip %REMOTE_LOC%\%BACKUP_FILENAME%_6.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_4.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_4.zip %REMOTE_LOC%\%BACKUP_FILENAME%_5.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_3.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_3.zip %REMOTE_LOC%\%BACKUP_FILENAME%_4.zip /Y
 if exist %REMOTE_LOC%\%BACKUP_FILENAME%_2.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_2.zip %REMOTE_LOC%\%BACKUP_FILENAME%_3.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%_1.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%_1.zip %REMOTE_LOC%\%BACKUP_FILENAME%_2.zip /Y
if exist %REMOTE_LOC%\%BACKUP_FILENAME%.zip copy %REMOTE_LOC%\%BACKUP_FILENAME%.zip %REMOTE_LOC%\%BACKUP_FILENAME%_1.zip /Y



copy %BACKUP_FOLDER%\%BACKUP_FILENAME%.zip %REMOTE_LOC%\%BACKUP_FILENAME%.zip /Y


IF %ERRORLEVEL% NEQ 0 GOTO ERROR

DEL %DUMPFILE_FOLDER%\%DUMPFILE_NAME%
DEL %DUMPFILE_FOLDER%\%LOGFILE_NAME%


EXIT 0

:ERROR
EXIT 1


--------------------------------------------------------------------------------------------------------------------

Friday, September 20, 2013

Installation of Oracle Database on Windows 2008 64 bits and listener service is not created and started

This problem you will face when you will work with a domain user, for creating a listerner service in windows 2008, you have to be a administrator of this server.

oracle database service is created fine but no listener service is creating.......

you can do it like belows

1)  Opening CMD prompt with administrator privileges
(Search for cmd and right click and run as administrator)
or
you can find it here C:\Windows\System32\cmd.exe  (right click and Run as Administrator)

> C:\Windows\system32> lsnrctl start

(it will automatically created the listener service...)

or

(As as administrator)

C:\Windows\system32> sc create OracleOraDb11g_home1TNSListener binPath="H:\oracle\product\10.2.0\db_1\BIN\TNSLSNR.exe"