Custom Search

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"



Friday, July 12, 2013

ORA-65096: invalid common user or role name or ORA-65049: creation in oracle 12c database

ORA-65096: invalid common user or role name or ORA-65049: creation in oracle 12c


65049, 00000,"invalid common user or role name"
 *Cause:  An attempt was made to create a common user or role with a name
          that wass not valid for common users or roles.  In addition to
          the usual rules for user and role names, common user and role
          names must start with C## or c## and consist only of ASCII
          characters.
  *Action: Specify a valid common user or role name.


65049, 00000, "creation of local user or role is not allowed in CDB$ROOT"
  *Cause:   An attempt was made to create a local user or role in CDB$ROOT.
  *Action:  If trying to create a common user or role, specify CONTAINER=ALL.



 Solution
================================
================================
(you must have to create "common user"
under CDB$ROOT with "c##" or "C##" beging of the user name. it is a rule)


In oracle 12c Database there are mainly two type of users

1) common users
2) local users

[ Note :  CDB = Container Database : A container is a collection of schemas, objects, and related structures in a Multitenant container Database that appears logically to an application as a separate database. Within a CDB, each container has a unique ID and name.
       
            PDB = Pluggable Database : The root and every PDB is considered a container. PDBs isolate data and operations so that from the perspective of a user or application, each PDB appears as if it were a traditional non-CDB.
          A PDB is a user-created set of schemas, objects, and related structures that appears logically to an application as a separate database. Every PDB is owned by SYS, which is a common user in the CDB, regardless of which user created the PDB.

        ROOT Container : Every CDB has one and only one root container, which stores the system metadata required to manage PDBs. All PDBs belong to the root. The name of the root is CDB$ROOT.
The root does not store user data. Thus, you must not add user data to the root or modify system-supplied schemas in the root. However, you can create common users and roles for database administration . A common user with the necessary privileges can switch between PDBs.
 ]



you have to understand the difference between them for mitigation the error.

Common Users in a CDB
======================

A common user is a database user that has the same identity in the root and in every existing and future PDB. Every common user can connect to and perform operations within the root, and within any PDB in which it has privileges.

Every common user is either Oracle-supplied or user-created. Examples of Oracle-supplied common users are SYS and SYSTEM.


Common users have the following characteristics:

    A common user can log in to any container (including CDB$ROOT) in which it has the CREATE SESSION privilege.


    The name of every user-created common user must begin with the characters c## or C##. (Oracle-supplied common user names do not have this restriction.)


    The names of common users must contain only ASCII or EBCDIC characters.


    Every common user is uniquely named across all containers.

  
    The schemas for a common user can differ in each container.




Local Users in a CDB
============================

A local user is a database user that is not common and can operate only within a single PDB. Local users have the following characteristics:

    A local user is specific to a particular PDB and owns a schema in this PDB.

    A local user on one PDB cannot log in to another PDB or to the root
   
    The name of a local user must not begin with the characters c## or C##.

    The name of a local user must only be unique within its PDB.

    Whether local users can access objects in a common schema depends on their user privileges.
   

=======================================
======================================
In details with live examples
======================================
=========================================


SQL>
SQL>
SQL> conn sys@halim12c as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> select * from v$version ;

BANNER                                                                             CON_ID
------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production            0
PL/SQL Release 12.1.0.1.0 - Production                                                  0
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 - Production                                          0
NLSRTL Version 12.1.0.1.0 - Production                                                  0

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create user halim identified by halim ;
create user halim identified by halim
            *
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL>
SQL>
SQL>
SQL> create user halimdb identified by halimdb container=current;
create user halimdb identified by halimdb container=current
                                  *
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
SQL>
SQL>
SQL>
SQL>
SQL> create user halim identified by halim container=ALL ;
create user halim identified by halim container=ALL
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL>

===========================================
===========================================
           Solution
(you must have to create "common user"
under CDB$ROOT with "c##" or "C##" beging of the user name.
 it is a rule)
===========================================
============================================
SQL>
SQL> create user c##halim identified by halim ;

User created.

SQL>
SQL>
SQL> create user c##halim_1 identified by halim container=ALL ;

User created.

SQL>
SQL>
SQL>
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME')
  2    AS CUR_CONTAINER FROM DUAL;

CUR_CONTAINER
------------------------------------------------------------------------------------------
--------------------------------------------------------
CDB$ROOT

SQL>
SQL>
SQL>
SQL>
SQL> grant create session to c##halim ;

Grant succeeded.

SQL>
SQL>
SQL> conn c##halim/halim
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL>
SQL>
SQL>
SQL> conn c##halim/halim@halim12c
Connected.
SQL>
SQL>
SQL> conn sys/sys@halim12c as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> SELECT NAME, PDB FROM V$SERVICES
  2    ORDER BY PDB, NAME;

NAME                           PDB
------------------------------ ------------------------------
HALIM12C                       CDB$ROOT
HALIM12CXDB                    CDB$ROOT
SYS$BACKGROUND                 CDB$ROOT
SYS$USERS                      CDB$ROOT
pdb_hr                         PDB_HR

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter session set container=pdb_hr ;

Session altered.

SQL>
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME')
  2    AS CUR_CONTAINER FROM DUAL;

CUR_CONTAINER
------------------------------------------------------------------------------------------
--------------------------------------------------------
PDB_HR

SQL>
SQL>
SQL>
SQL>
SQL> create user halim identified by halim ;
create user halim identified by halim
                                *
ERROR at line 1:
ORA-01109: database not open


SQL>
SQL>
SQL> startup
ORA-24543: instance startup or shutdown not allowed in pluggable database
SQL>
SQL>
SQL> alter database pdb_hr open ;

Database altered.

SQL>
SQL>
SQL>
SQL> create user halim identified by halim ;

User created.

SQL>
SQL>
SQL>
SQL> grant dba to halim ;

Grant succeeded.

SQL>
SQL>
SQL>