Custom Search

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 users

1) common users
2) local users

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>


For more see here
http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdblogic.htm#CNCPT89259


Identity column in oracle 12c Database

Identity column in oracle 12c Database
-----------------------------------------------

Now in Oracle Database 12c Release 1 database has ANSI-compliant IDENTITY column
like Microsoft SQL Server's Identity column for auto increment number generation.

So, now migration from Microsoft SQL Server's to oracle database will be easier
in case of IDENTITY COLUMN.


There are three option for identity  when we will create a table.

1)  by default : BY DEFAULT If you specify BY DEFAULT, then Oracle Database uses
                 the sequence generator to assign a value to the column by default,
                 but you can also explicitly assign a specified value to the column.

2)  by default on null : If you specify ON NULL, then Oracle Database uses the
                         sequence generator to assign a value to the column when a subsequent
                         INSERT statement attempts to assign a value that evaluates to NULL.

3)  always  : ALWAYS If you specify ALWAYS, then Oracle Database always uses the
              sequence generator to assign a value to the column. If you attempt to
              explicitly assign a value to the column using INSERT or UPDATE,
              then an error will be returned. This is the default.


In details explanation is below
=================================


[oracle@halim12c] $ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 18:32:33 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn sys@halim12c as sysdba
Enter password:
Connected.
SQL>
SQL> col banner format a78
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                                                              0
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>
SQL>
SQL> select host_name from v$instance ;

HOST_NAME
----------------------------------------------------------------
halim-srv.localdomain

SQL>
SQL> select name from V$database ;

NAME
---------
HALIM12C

SQL>
SQL>
SQL>
               =============================================================
                       Identity column "generated by default" example
                ===========================================================
              =================================================================
SQL>
SQL>
SQL>
SQL> create table t_identity_def (id number generated by default as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL> insert into t_identity_def (name) values ('halim');

1 row created.

SQL>
SQL> insert into t_identity_def (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1

SQL>
SQL>
SQL> truncate table  t_identity_def ;

Table truncated.

SQL>
SQL>
SQL> select * from t_identity_def ;

no rows selected

SQL>
SQL>
SQL> insert into t_identity_def (name) values ('halim2');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def ;

        ID NAME
---------- ------------------------------
         3 halim2

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> insert into t_identity_def (id,name) values (null,'halim');
insert into t_identity_def (id,name) values (null,'halim')
                                             *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."T_IDENTITY_DEF"."ID")

SQL>
SQL>


               =============================================================
                  Identity column "generated by default on null" example
                ===========================================================
              =================================================================


SQL>
SQL> create table t_identity_def_null (id number generated by default on null as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL>
SQL> select * from t_identity_def_null ;

no rows selected

SQL>
SQL>
SQL>
SQL> insert into t_identity_def_null (id,name) values (null,'halim');

1 row created.

SQL>
SQL> insert into t_identity_def_null (id,name) values (null,'halim1');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (id,name) values (66,'halim1');

1 row created.

SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1
        66 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL> commit ;

Commit complete.

SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1
        66 halim1
         4 halim1

SQL>
SQL>
SQL> truncate table  t_identity_def_null ;

Table truncated.

SQL>
SQL>
SQL> select * from  t_identity_def_null;

no rows selected

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim2');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from  t_identity_def_null;

        ID NAME
---------- ------------------------------
         5 halim1
         6 halim1
         7 halim2

SQL>
SQL>
SQL>

                =============================================================
                       Identity column "generated always" example
                ===========================================================
              =================================================================

SQL>
SQL>
SQL>
SQL> create table t_identity_always (id number generated always as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL> insert into t_identity_always (name) values ('always');

1 row created.

SQL>
SQL> insert into t_identity_always (name) values ('always1');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from  t_identity_always;

        ID NAME
---------- ------------------------------
         1 always
         2 always1

SQL>
SQL>
SQL> insert into t_identity_always (id,name) values (3,'always1');
insert into t_identity_always (id,name) values (3,'always1')
                               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL>
SQL>
SQL>
SQL>