Archive

Archive for the ‘Oracle10g’ Category

RemoteOperationException:ERROR:Wrong Password for User

March 12, 2011 Leave a comment

Problem in Oracle 10g database on Windows 2000/2003 Professional/Enterprise error message  “RemoteOperationException:ERROR:Wrong Password for User”

Solutions

1. go to Control Panel -> Administrative Tools.

2. Click on Local Security Policy.

3. Local Policies, User Rights Assignment.

4. Double click, ‘Log on as a batch job‘.

5. Add your local user that you are supplying for the Host Credentials.

 

 

Categories: Remote Operation Exception

Listener Commands

March 1, 2011 Leave a comment 
current_listener  
Syntax : set current_listener <listener_name>
LSNRCTL> set current_listener LISTENER
LSNRCTL> show current_listener
displaymode 
Syntax : set displaymode <RAW | COMPAT | NORMAL | VEBOSE>
LSNRCTL> show displaymode
Service display mode is NORMAL
LSNRCTL> set displaymode VERBOSE
inbound_connect_timeout 
Syntax : set inbound_connect_timeout <value>
LSNRCTL> set inound_connect_timeout 0
log_directory     
Syntax : set log_directory <directory_path>
LSNRCTL> set log_directory c:\listner_log
log_file    
Syntax : set log_file <file_name>
LSNRCTL> set log_file listner.log
log_status  
Syntax : set log_status < ON | OFF>
LSNRCTL> set log_status ON
password    
Syntax : set password
LSNRCTL> set password
Password:
rawmode     
Syntax : set rawmode <ON | OFF>
LSNRCTL> set rawmode OFF
save_config_on_stop     
Syntax : set save_config_on_stop <ON | OFF>
LSNRCTL> set save_config_on_stop ON
startup_waittime  
Syntax : set startup_waittime <value>
LSNRCTL> set startup_waittime 0
trc_directory     
Syntax : set trc_directory <directory path>
LSNRCTL> set trc_directory c:\listner_log
trc_file    
Syntax : set trc_level <file_name>
LSNRCTL> set trc_file listener.trc
trc_file    
Syntax : set trc_level <file_name>
LSNRCTL> set trc_file listener.trc
trc_level set trc_level <value>
Value

Description
0 – 1

off

2 – 3

err

4 – 5

user

6 – 14

admin

15

dev

16 – 99

support

 
LSNRCTL> set trc_level 0

Services view the current listener services 

lsnrctl services
Start the listener  lsnrctl start
Stop the listener  lsnrctl stop
Show status lsnrctl status
Listener version lsnrctl version
Categories: Listener Commands, Oracle10g Tags:

RESTRICTED SESSION

February 24, 2011 Leave a comment 
SQL> select logins from v$instance;
LOGINS
----------
ALLOWED

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.

SQL> select logins from v$instance;
LOGINS
----------
RESTRICTED

$ sqlplus <username>/<password>@<serviceid>
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 24 09:26:48 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
System altered.

‘alter system enable restricted session‘ does not “kick out” user automatically. It just blocks new user who does not have the ‘restrict session’ privilege to login, similar to ‘ALTER SYSTEM QUIESCE RESTRICTED’, which blocks new non-dba users. However, I can scarely see the instances when non-dba users have been granted to the ‘restrict session’ privilege.

ALTER SYSTEM QUIESCE RESTRICTED;

Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active. For example, If a user issues a SQL query in an attempt to force an inactive session to become active, the query will appear to be hung. When the database is later unquiesced, the session is resumed, and the blocked action is processed.

The following statement restores the database to normal operation:

ALTER SYSTEM UNQUIESCE;

All non-DBA activity is allowed to proceed. In an Oracle Real Application Clusters environment, this statement is not required to be issued from the same session, or even the same instance, as that which quiesced the database. If the session issuing the ALTER SYSTEM UNQUIESCE statement terminates abnormally, then the Oracle Database server ensures that the unquiesce operation completes.

The ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance. The column values has one of these values:

  • NORMAL: Normal unquiesced state.
  • QUIESCING: Being quiesced, but some non-DBA sessions are still active.
  • QUIESCED: Quiesced; no non-DBA sessions are active or allowed.
Categories: RESTRICTED SESSION Tags:

Monitoring SGA (Free Memory) Using v$sgastat

February 7, 2011 1 comment

In this article, we will discuss how to get monitor free memory in System Global Area (SGA) in Oracle 10g. The System Global Area (SGA) Memory Structures in data. The SGA is read/write.  A system global area is a group of shared memory areas that dedicated to an Oracle database instance. For example Large Pool, Shared Pool, Java Pool etc.

SQL> SHOW SGA;
Total System Global Area  608174080 bytes
Fixed Size                  2006160 bytes
Variable Size             176161648 bytes
Database Buffers          423624704 bytes
Redo Buffers                6381568 bytes

Let’s use v$sgastat parameter in this article. The v$sgastat parameter displays detailed information on the system global area (SGA).

SQL> DESC v$sgastat;
Name                                      Null?    Type
----------------------------------------- -------- ----------------
POOL                                               VARCHAR2(12)
NAME                                               VARCHAR2(26)
BYTES                                              NUMBER

Let’s have a short explanation of all column of the v$sgastat parameter:

POOL: Designates the pool in which the memory in NAME resides:

  • Shared Pool: Memory is allocated from the shared pool.
  • Large Pool: Memory is allocated from the large pool.
  • Java Pool: Memory is allocated from the Java pool.
  • Streams Pool: Memory is allocated from the Streams pool.

NAME: The name column means system global area component name.

BYTES: The memory size in bytes.

Now we will use a simple SQL to see the free memory in SGA.

SQL> Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB
  2  From V$sgastat
  3  Where Name Like '%free memory%';
POOL         FREE_MEMORY_IN_MB
------------ -----------------
shared pool                 22
large pool                   3
java pool                    4
streams pool                 8
Categories: Monitoring SGA Using v$sgastat Tags:

Step by Step Oracle Enterprise Manager Configuration

February 7, 2011 Leave a comment

In this article, we will discuss monitoring configuration in Oracle Enterprise Manager.

Go to http://<hostname&gt;:<port-number>/em then specifying the system username and password after that we will click Login button.

N.B: Make sure enterprise manager console is running. to check it, issue following command.

$ emctl status dbconsole

The home page is displaying. After that we will click Monitoring Configuration under the Related Links.

The Configure Database Instance: Properties page is displaying. We will enter the correct password in the Monitor Password field and click Test Connection.

The Database Instance Configuration Result page is displaying. After some time next page is displaying.

The Configure Database Instance: Review page is displaying. We will click Submit button.

The Database Instance Configuration Result page is displaying. We will click OK button, after that we will go to Home page of the Oracle Database.


 

Categories: Oracle 10g Enterprise Manager, Oracle Enterprise Manager Configuration, Oracle10g Tags:

2010 in review

January 2, 2011 Leave a comment

The stats helper monkeys at WordPress.com mulled over how this blog did in 2010, and here’s a high level summary of its overall blog health:

Healthy blog!

The Blog-Health-o-Meter™ reads Fresher than ever.

Crunchy numbers

Featured image

The average container ship can carry about 4,500 containers. This blog was viewed about 17,000 times in 2010. If each view were a shipping container, your blog would have filled about 4 fully loaded ships.

 

In 2010, there were 32 new posts, growing the total archive of this blog to 67 posts. There were 12 pictures uploaded, taking up a total of 3mb. That’s about a picture per month.

The busiest day of the year was October 13th with 164 views. The most popular post that day was Use oradim in Linux.

Where did they come from?

The top referring sites in 2010 were google.co.in, en.wordpress.com, google.com, facebook.com, and yandex.ru.

Some visitors came searching, mostly for pl/sql lock timer, oracle parameterized view, oradim linux, oracle 11g oradim unix, and db_writer_processes.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

1

Use oradim in Linux November 2009

2

Oracle 10g Enterprise Manager January 2010

3

Parameterized View in Oracle April 2010
1 comment

4

ER Diagram of HR System November 2009

5

Partition Table In Oracle January 2010

Categories: Oracle10g

Oracle & Iron Man – Great Combination

August 31, 2010 Leave a comment

http://www.oracle.com/us/ironman2/index.html

Categories: Oracle10g Tags:

Auditing Table Data using Trigger

July 26, 2010 1 comment

Create New User in Oracle

CREATE User <UserName> IDENTIFIED BY <Passsword>;

Grant User necessary privileges

GRANT CREATE session TO <username>;

GRANT CREATE TABLE TO <username>;

GRANT CREATE TRIGGER TO <username>;

ALTER USER <username> QUOTA UNLIMITED ON <TablespaceName>

Create a Transactional Table

CREATE TABLE AD_DURATIONS
  (
      Ad_Duration_Cn    Varchar2(10 Byte)     Not Null Enable,
      Ad_Start_Date     Date Not              Null Enable,
      Ad_End_Date       Date Not              Null Enable,
      Modified_By       Varchar2(10 Byte),
      Modified_Date     Date                  Default Sysdate,
      Constraint CJ_AD_DURATIONS_PK Primary Key (AD_DURATION_CN)
  );

Insert value in the AD_DURATIONS Table

Insert Into Ad_Durations (Ad_Duration_Cn, Ad_Start_Date,
                              Ad_End_Date, Modified_By)
Values ('10001', To_Date('26-JUL-10', 'DD-MON-RR'),
                              To_Date('30-JUL-10', 'DD-MON-RR'), 'Tamim');

Insert Into Ad_Durations (Ad_Duration_Cn, Ad_Start_Date,
                              Ad_End_Date, Modified_By)
Values ('10002', To_Date('31-JUL-10', 'DD-MON-RR'),
                              To_Date('05-Aug-10', 'DD-MON-RR'), 'Khan');

Create a Log Table for AD_DURATIONS Data

Here Modified_By, Modified_Date and Action is Audit Column.

CREATE TABLE AD_DURATIONS_LOG
  (
      Ad_Duration_Cn    Varchar2(10 Byte),
      Ad_Start_Date     Date,
      Ad_End_Date       Date,
      Modified_By       Varchar2(20 Byte),
      Modified_Date     Timestamp (6),
      Action            Varchar2(20 Byte)
  );

Create a Trigger on AD_DURATIONS table

Create Or Replace TRIGGER AD_DURATIONS_LOG
AFTER DELETE OR UPDATE ON AD_DURATIONS
FOR EACH ROW
Begin
  If Updating Then
      Insert Into Ad_Durations_Log(Ad_Duration_Cn, Ad_Start_Date,
                  Ad_End_Date, Modified_By,Modified_Date,Action)
      Values (:Old.Ad_Duration_Cn, :Old.Ad_Start_Date,:Old.Ad_End_Date,
                  :Old.Modified_By,:Old.Modified_Date,'Update');
  Elsif Deleting Then
      Insert Into Ad_Durations_Log(Ad_Duration_Cn, Ad_Start_Date,
                  Ad_End_Date, Modified_By,Modified_Date,Action)
      Values (:Old.Ad_Duration_Cn, :Old.Ad_Start_Date, :Old.Ad_End_Date,
                  :Old.Modified_By,:Old.Modified_Date,'Delete');
  End If;
END;

Update on AD_DURATIONS Table

Update Ad_Durations
Set Ad_End_Date = To_Date('31-JUL-10', 'DD-MON-RR')
Where Ad_Duration_Cn = 10001;

Update from AD_DURATIONS Table

Delete From Ad_Durations
Where Ad_Duration_Cn = 10002;

Now Selecting data from AD_DURATIONS_LOG Table.

Select * From Ad_Durations_Log;
Categories: Auditing Table Data using Trigger Tags:

CONTROLFILE AUTO BACKUP

July 11, 2010 3 comments

RMAN is one of the very useful utility provided by Oracle for backup and recovery Purpose. Oracle online backups were introduced with Version 6, where tablespace must be kept in backup mode in order to take online backups.

RMAN has following default parameters and its default values:

RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\S
NCFORCL.ORA'; # default

Or

RMAN>  SHOW CONTROLFILE AUTOBACKUP;

RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;

By default CONTROLFILE AUTOBACKUP is OFF. But it is strongly recommended enabling CONTROLFILE AUTOBACKUP ON.

Advantage:

  • RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible.
  • Restore the RMAN repository contained in the control file when the control file is lost and you have no recovery catalog. You do not need a recovery catalog or target database control file to restore the control file auto backup.
  • Control file auto backup can keep track of add a data file, resize, increase/decrease the size of data files or etc.
  • If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically backs up the control file and the current server parameter file (if used to start up the database) in one of two circumstances: when a successful backup must be recorded in the RMAN repository, and when a structural change to the database affects the contents of the control file which therefore must be backed up

To perform control file auto backup on issue the following command

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
Categories: CONTROLFILE AUTO BACKUP Tags:

SQL Tuning Advisor in Oracle 10g

June 14, 2010 Leave a comment

In Oracle 10g, the SQL tuning process has been automated by introducing a new manageability feature call Automatic SQL Tuning. This feature is designed to work equally well for OLTP and Data Warehouse workloads. Unlike existing tools, automatic SQL Tuning id preformed in the database server by the Oracle query optimizer itself, running in special mode. When running in this mode, the Oracle query optimizer is referred to us the Automatic Tuning Optimizer.

Create a Table to Test a SQL

Create Table TestEmp
       As Select * from Scott.emp;

Write a SQL that you need to tune.

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM TESTEMP
ORDER by SAL;

Execute DBMS_STATS Package for Gather Table Satieties

SQL> BEGIN
  2    DBMS_STATS.GATHER_TABLE_STATS(
  3    ownname          => 'TEST', 
  4    tabname          => 'TESTEMP',
  5    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  6    method_opt       => 'FOR ALL COLUMNS SIZE AUTO'
  7    );
  8  END;
  9  /
PL/SQL procedure successfully completed.

How to use dbms_sqltune Package

CREATE OR REPLACE PROCEDURE P_SQL_TUNING (
        p_sql CLOB
  )
AS
  v_task VARCHAR2(30);
BEGIN
  /* Drop the task in case we are re-running... */
  BEGIN
      DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'sql_tuning_task');
      EXCEPTION
         WHEN OTHERS THEN -- ORA-13605
            NULL;
  END;
 
  /* Create a SQL Tuning task for our SQL... */
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                   sql_text    => p_sql,
                   time_limit  => 1,
                   scope       => 'COMPREHENSIVE',
                   task_name   => 'sql_tuning_task',
                   description => 'Demo of DBMS_SQLTUNE'
                   );          
  /* Execute the task... */
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'sql_tuning_task');
  
  /* We want to run this again... */
  ROLLBACK;
END P_SQL_TUNING;

Execute p_Sql_Tuning Procedure

SQL> exec P_SQL_TUNING('SELECT emp.EMPNO, emp.ENAME, emp.JOB,
                        emp.MGR, emp.HIREDATE, emp.SAL, emp.COMM, emp.DEPTNO
                        FROM TESTEMP emp,TESTEMP empmgr
                        Where emp.empno=empmgr.mgr
                        ORDER by SAL'
                       );
 
PL/SQL procedure successfully completed.

Tuning Result

SQL> set long 80000
SQL> col recs format a90
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_task') AS result
     FROM   DUAL;
 
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_tuning_task
Tuning Task Owner                 : TEST
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1
Completion Status                 : COMPLETED
Started at                        : 06/14/2010 12:24:18
Completed at                      : 06/14/2010 12:24:18
Number of Index Findings          : 1
 
-------------------------------------------------------------------------------
Schema Name: TEST
SQL ID     : grfz43h6vdf3d
SQL Text   : SELECT emp.EMPNO, emp.ENAME, emp.JOB, emp.MGR, emp.HIREDATE,
             emp.SAL, emp.COMM, emp.DEPTNO FROM TESTEMP emp,TESTEMP empmgr
             Where emp.empno=empmgr.mgr ORDER by SAL
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.
 
  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index TEST.IDX$$_041C0001 on TEST.TESTEMP('MGR');
 
  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original
-----------
Plan hash value: 719480185
 
-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    13 |   533 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |         |    13 |   533 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN         |         |    13 |   533 |     7  (15)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| TESTEMP |    13 |    52 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| TESTEMP |    14 |   518 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP"."EMPNO"="EMPMGR"."MGR")
   3 - filter("EMPMGR"."MGR" IS NOT NULL)
 
2- Using New Indices
--------------------
Plan hash value: 2840960280
 
--------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |    13 |   533 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |                |    13 |   533 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS      |                |    13 |   533 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TESTEMP        |    14 |   518 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | IDX$$_041C0001 |     1 |     4 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."EMPNO"="EMPMGR"."MGR")
       filter("EMPMGR"."MGR" IS NOT NULL)

Clean up

The DBMS_SQLTUNE package provides APIs to remove the objects we created as follows.

SQL> exec DBMS_SQLTUNE.DROP_TUNING_TASK('sql_tuning_task');
 
PL/SQL procedure successfully completed.

Note:

The Following error message means user have no permission to user Advisor privilege

ORA-13616: The current user <User Name> has not been granted the ADVISOR privilege.

Solutions:

Grant ADVISOR to <User Name>;
Categories: SQL Tuning Advisor Tags:

Parameterized View in Oracle

April 10, 2010 2 comments

There is nothing like parameterize view in Oracle, but you can use parameterize view in oracle following way. There is two possible option, You can use the User Environment variable to manage a session variable (dbms_application_info package), another way is write a package to get and set value parameterize view in the global variable or sys_context, later on a view can use the value from, which called so called parameterized view.

Example:

Creating Table

CREATE TABLE APPLICATIONS (
    APPLICATION_CN             VARCHAR2 (10 BYTE) NOT NULL ENABLE,
    APPLICATION_NAME           VARCHAR2 (20 BYTE) NOT NULL ENABLE,
    APPLICATION_DESCRIPTION    VARCHAR2 (200 BYTE),
    APPLICATION_STATUS         VARCHAR2 (5 BYTE) DEFAULT 'True'
  );

Inserting Data in to the table

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10001','ERP','Enterprise resource Planning ','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10002','Oracle ERP','Oracle Enterprise resource Planning ','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10003','Web Base Application','Web Base Application','True');

Insert into APPLICATIONS (APPLICATION_CN,APPLICATION_NAME,APPLICATION_DESCRIPTION,APPLICATION_STATUS) 
values ('10004','Desktop Application','Desktop Application','True');

Commit;

Create a Package

create or replace
PACKAGE TestParam_VW_PKG AS
 PROCEDURE P_SET_APPLICATION_CN (  
    p_Application_CN            VARCHAR2
  );

  FUNCTION F_GET_APPLICATION_CN RETURN VARCHAR2;
END TestParam_VW_PKG;

Create a Package body for Get and Set operation

 
create or replace
PACKAGE BODY TestParam_VW_PKG AS
  g_Application_CN            VARCHAR2(10);
  PROCEDURE P_SET_APPLICATION_CN (  
    p_Application_CN          VARCHAR2
  ) AS
  BEGIN
    g_Application_CN := p_Application_CN;
  END;

  FUNCTION F_GET_APPLICATION_CN RETURN VARCHAR2
  IS
  BEGIN
    RETURN g_Application_CN;
  END;
END TestParam_VW_PKG;

Create a View base on the Packege

CREATE OR REPLACE FORCE VIEW APPLICATION_VW ("APPLICATION_CN", "APPLICATION_NAME", "APPLICATION_DESCRIPTION", "APPLICATION_STATUS")
AS
  SELECT APPLICATION_CN,
    APPLICATION_NAME,
    APPLICATION_DESCRIPTION,
    APPLICATION_STATUS
  FROM  APPLICATIONS
  WHERE APPLICATION_CN = TestParam_VW_PKG.F_GET_APPLICATION_CN;

How to use the view

SQL> Exec TESTPARAM_VW_PKG.P_SET_APPLICATION_CN('10001');

PL/SQL procedure successfully completed.

SQL> SELECT APPLICATION_CN, APPLICATION_NAME, APPLICATION_DESCRIPTION, APPLICATION_STATUS
FROM APPLICATION_VW ;
APPLICATION_CN    APPLICATION_NAME     APPLICATION_DESCRIPTION        APPLICATION_STATUS
---------------   -------------------- -----------------------------  ------------------
10001             ERP                  Enterprise resource Planning   True
Categories: Oracle10g, Parameterized View in Oracle Tags:

Proxy User in Oracle 10g

February 10, 2010 Leave a comment

First we’ll create two database Schema users(cpdata, mpdata) and One Big Application User (app_user called Proxy User). The app_user will be used by a web application to connect to the database. Usually the application user, in this case cpdata and mpdata, will be authenticated by the web application through app_user.

Create User cpdata and mpdata as a Sys or System user which contain schema objects.

create user cpdata identified by cpdata;
grant create session, create table to cpdata;             
alter user cpdata quota unlimited on users;              

create user mpdata identified by mpdata;
grant create session, create table to mpdata;             
alter user mpdata quota unlimited on users;

Create Table on cpdata and mpdata schema

create table cpdata.test1
(
        Test1  varchar2(10),
        Test2  varchar2(10)
);      

create table mpdata.test2
(
        Test1  varchar2(10),
        Test2  varchar2(10)
);

Create User app_user, for a Proxy User.

create user app_user identified by app_user;
grant create session to app_user;

Grant Proxy Authentication

alter user cpdata grant connect through app_user;
alter user mpdata grant connect through app_user;

Now Connect cpdata and mpdata user using Proxy Authentication and access there schema objects

SQL> connect app_user[cpdata]/app_user@<SID>
Connected.
SQL> select user from dual;

USER
------------------------------
CPDATA

SQL> desc test1;
Name                                      Null?    Type
----------------------------------------- -------- ------------------
TEST1                                              VARCHAR2(10)
TEST2                                              VARCHAR2(10)

SQL> connect app_user[mpdata]/app_user@<SID>
Connected.
SQL> select user from dual;

USER
------------------------------
MPDATA

SQL> desc test2;
Name                                      Null?    Type
----------------------------------------- -------- ------------------
TEST1                                              VARCHAR2(10)
TEST2                                              VARCHAR2(10)

SQL> select sys_context('USERENV','PROXY_USER') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')
-------------------------------------------------------------
APP_USER

More :

http://www.oracle.com/technology/products/ias/toplink/doc/1013/main/_html/dblgcfg008.htm

http://www.oracle.com/technology/tech/java/oc4j/1013/how_to/how-to-ds-proxy/doc/how-to-ds-proxy.html

http://www.dba-oracle.com/t_proxy_connect_authentication.htm

Categories: Proxy User in Oracle

Oracle 10g Enterprise Manager

January 26, 2010 1 comment

Oracle 10g on Windows 2003 Server:

Having set the ORACLE_SID variable, I am trying to start the dbconsole using following command.

Problem 01:

emctl start dbconsole

The errors Message :

OC4J Configuration issue. c:\oracle\product\10.2.0\db_1/oc4j/j2ee/OC4J_DBConsole
_192.168.1.41_cjdb not found.

Solution:

Step 01: Find the hostname of Oracle Server and set the ORACLE_HOSTNAME Environment variable

$ hostname

Run these commands For Linux: 

$ ORACLE_HOSTNAME=myhost.domainname.com
$ export ORACLE_HOSTNAME

Run these commands For Windows: 

> Set ORACLE_HOSTNAME=myhost.domainname.com

It’s good idea to drop & recreate Enterprise Manager Repository with correct listener/port

Step 02: Now configure the em Repository following way

Drop and then create em repository for single instance

emca -deconfig   dbcontrol db
emca -config     dbcontrol db

For Cluster (drop and create and RAC EM)

emca -deconfig   dbcontrol db
emca -config     dbcontrol db

Example: 

emca -config dbcontrol db
 
STARTED EMCA at Jan 26, 2010 10:14:19 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Enter the following information:
Database SID: cjdb
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional): tamim@cyberjahan.com
Outgoing Mail (SMTP) server for notifications (optional): 25
-----------------------------------------------------------------
 
You have specified the following settings
 
Database ORACLE_HOME ................ c:\oracle\product\10.2.0\db_1
 
Database hostname ................ 192.168.1.41
Listener port number ................ 1521
Database SID ................ cjdb
Email address for notifications ............... tamim@cyberjahan.com
Outgoing Mail (SMTP) server for notifications ............... 25
 
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Jan 26, 2010 10:15:45 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at c:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\cjdb\emca_2010-01-26_10-14-19-AM.log.
Jan 26, 2010 10:15:57 AM oracle.sysman.emcp.util.PlatformInterface executeComman
d
WARNING: Error executing CMD /C c:\oracle\product\10.2.0\db_1\bin\emctl.bat depl
oy dbconsole c:\oracle\product\10.2.0\db_1\192.168.1.41_cjdb 192.168.1.41:3938 1
92.168.1.41 cjdb
Jan 26, 2010 10:15:57 AM oracle.sysman.emcp.EMConfig perform
SEVERE: Error instantiating EM configuration files
Refer to the log file at c:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\cjdb\emc
a_2010-01-26_10-14-19-AM.log for more details.
Could not complete the configuration. Refer to the log file at c:\oracle\product
\10.2.0\db_1\cfgtoollogs\emca\cjdb\emca_2010-01-26_10-14-19-AM.log for more deta
ils.

To configure the enterprise manager database control for your database, issue the following commands as oracle user in Linux Platform:

[oracle@dbsrv oracle]$ export ORACLE_HOME=<ORACLE_HOME>
[oracle@dbsrv oracle]$ export ORACLE_SID=<ORACLE_SID>
[oracle@dbsrv bin]$ cd $ORACLE_HOME/bin
[oracle@dbsrv bin]$ ./emca -config dbcontrol db -repos create
Now just follow the instructions on the screen… you’ll need the SYS, DBSNMP and SYSAUX passwords.

Sometimes you already have the SYSMAN, DBSNMP and SYSAUX accounts, and the previous EMCA command can fail to create the DBConsole repository the error ORA-20001 occurs.

ORA-20001 Sysman Already Exists While Running EMCA

If the previous drop procedure fails and by checking the logs we see ORA-01031 insufficient privileges, this may be because we don’t have a password file created. Just go to $ORACLE_HOME/dbs and check if we have a file called pwd<sid>.ora. If not, create it…

[oracle@dbsrv oracle]$ cd $ORACLE_HOME/bin
[oracle@dbsrv bin]$ ./orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5

Problem 02:

emctl status dbconsole
Unable to determine local host from URL REPOSITORY_URL=http://192.168.1.41:%EM_U
PLOAD_PORT%/em/upload/

Solution:

Change the value of REPOSITORY_URL in following way

%ORACLE_HOME%\<hostname_sid>\sysman\config\emd.properties
REPOSITORY_URL=http://xxxx:5500/em/upload/

Example: 

#REPOSITORY_URL=http://192.168.1.41:%EM_UPLOAD_PORT%/em/upload/
REPOSITORY_URL=http://192.168.1.41:5500/em/upload/

Create Database Console

To create the configuration files and repository for Database Console, run:

$ emca -config dbcontrol db -repos create

Drop Database Console

To drop (remove) the configuration files and repository for Database Console, run:

$ emca -deconfig dbcontrol db -repos drop

Recreate Database Console

To recreate the configuration files and repository for Database Console, run:

$ emca -config dbcontrol db -repos recreate

Using EMCA with Real Application Clusters

In this section, I will be using two Database Control consoles running; one on linux1 and the other on linux2. From either of these consoles, you can manage and monitor all targets in the cluster.

For information on the current cluster configuration, you can run:

$ emca -displayConfig dbcontrol –cluster 

Create Database Console

 
$ emca -config dbcontrol db -repos create –cluster

Drop Database Console

 
$ emca -deconfig dbcontrol db -repos drop -cluster

Troubleshooting EMCA

If you have any problems when running emca, check the log file under:

ORACLE_HOME/cfgtoollogs/emca/<ORACLE_SID>/
 
Log Directory: 
$ORACLE_HOME/cfgtoollogs/emca
For Linux:
u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca 
For Windows:
C:\oracle\product\10.2.0\db_1\cfgtoollogs\emca
Categories: Oracle 10g Enterprise Manager, Oracle10g Tags:

Auditing in Oracle Database

November 29, 2009 Leave a comment

The Oracle Server provides several auditing options. For example, you can choose to audit certain types of SQL statements, such as INSERT or UPDATE statements. You can audit statements that use specific system privileges, such as CREATE TABLE or ALTER TABLE. You can also audit the unsuccessful attempts to perform some type of action in the database, such as failed login attempts. Auditing can be limited to a specific user or group of users, or can even be limited to actions performed on a specific schema object on a “by session” or “by access” basis.

The following three types of audits are provide by Oracle

 1. Session audits (LOGON,LOGOFF etc)
 2. Database action and object audits and
 3. DDL(CREATE, ALTER & DROP of objects)

The three main views to see the AUDIT Information are:

  • DBA_AUDIT_TRAIL – Standard auditing only (from AUD$).
  • DBA_FGA_AUDIT_TRAIL – Fine-grained auditing only (from FGA_LOG$) [For 10g].
  • DBA_COMMON_AUDIT_TRAIL – Both standard and fine-grained auditing   [For 10g].

To enable database auditing, you must provide a value for the AUDIT_TRAIL parameter.

The initialization parameters of audit facility of Oracle

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \ORCL\ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB

Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
DB              Auditing is enabled. Audit records will be written to the
                SYS.AUD$ table.
OS              Auditing is enabled. Audit records will be written to an
                audit trail in the operating system.
db,extended     As db, but the SQL_BIND and SQL_TEXT columns are also populated.
NONE            Auditing is disabled (default).
xml-            Auditing is enabled, with all audit records stored
                as XML format OS files.
xml,extended    As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
TRUE            This value is supported for backward-compatibility
                with versions of Oracle;it is equivalent to the DB value.
FALSE           This value is supported for backward-compatibility
                with versions of Oracle;it is equivalent to the NONE value.

In Oracle 10g Release 1, db_extended was used in place of db,extended. The XML options are new to Oracle 10g Release 2.

The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.

The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.

Audit in SYS.AUD$ desertion Table

Set audit_trail to DB in pfile (audit_trail = DB) .

Enable auditing and direct audit records to the database audit trail

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250452 bytes
Variable Size             230689644 bytes
Database Buffers          377487360 bytes
Redo Buffers                2940928 bytes
Database mounted.

The command to begin auditing connects (login) attempts is:

AUDIT SESSION;
AUDIT SESSION WHENEVER SUCCESSFUL;
AUDIT SESSION WHENEVER NOT SUCCESSFUL;

To view the report of Audit session run the following query.

SQL Code:  
SELECT os_username,
     username,
     terminal,
     returncode,
     TO_CHAR(timestamp,   'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
     TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME
FROM dba_audit_session;

Disable Session Audit

NOAUDIT SESSION;

Audit a User 

AUDIT CREATE TABLE BY scott;

Disable User Audit

NOAUDIT CREATE TABLE BY scott;

Audit a User Table

It is also possible to audit SELECT, INSERT, UPDATE, and DELETE operations on specific database tables. Depending on the type of audit you wish to conduct, you can include either the BY SESSION or BY ACCESS clause in the AUDIT command. This clause is available for both object audits and system-level action audits.

It denotes whether an audit record will be written once for each session (BY SESSION) or once for each time a schema objects is accessed (BY ACCESS).

AUDIT INSERT ON scott.emp1 BY SESSION;
AUDIT INSERT ON scott.emp2 BY ACCESS;

Disable User Table Audit

NOAUDIT INSERT ON scott.emp1;

Set audit_trail to OS file destination, in pfile (audit_trail = OS)

audit_file_dest = /u01/oracle/product/10.2.0/admin/orcl/adump

Open your init.ora file and check the AUDIT_FILE_DEST parameter for the location of your operating system audit trail files.

Open the file in Unix system and If you are using Windows NT, then go to Start -> Programs -> Administrative Tools -> Event Viewer. In the menu bar of the Event Viewer, choose Log -> Application. Double click on the events listed.

To find out the name of the database action that corresponds to the numeric code of 100, query the AUDIT_ACTIONS data dictionary view.

SQL Code:
SELECT action, name
FROM audit_actions
WHERE action = 100;

Similarly, to discover the name of the privilege used to allow you to perform this database action, query the STMT_AUDIT_OPTION_MAP data dictionary view. While still logged on as user

SQL Code:
SELECT option#, name
FROM stmt_audit_option_map
WHERE option# = 5;

View Audit Trail

The audit trail is stored in the SYS.AUD$ table. Its contents can be viewed directly or via the following views:

SQL Code:
SQL> SELECT view_name
  2  FROM   dba_views
  3  WHERE  view_name LIKE 'DBA%AUDIT%'
  4  ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
Categories: AUDIT_TRAIL Tags: , ,

Data Dictionary and Dynamic performance view

November 20, 2009 Leave a comment

The Database Library is built on a Data Dictionary, which provides a complete description of record layouts and indexes of the database, for validation and efficient data access. You can use the data dictionary for automated database creation, including building tables, indexes, and referential constraints, and granting access rights to individual users and groups. The database dictionary supports the concept of Attached Objects, which allow database records to include compressed BLOBs (Binary Large Objects) containing images, text, sounds, video, documents, spreadsheets, or programmer-defined data types.

You can request information about the dictionary table

SELECT comments
FROM dictionary WHERE table_name='DICTIONARY'
COMMENTS
--------------------------------------------------------------------------------
Description of data dictionary tables and views

DBA_VIEWS

Description of dba_views 

·   OWNER               Owner of the view
·   VIEW_NAME           Name of the view
·   TEXT_LENGTH         Length of the view text
·   TEXT                View text
·   TYPE_TEXT_LENGTH    Length of the type clause of the object view
·   TYPE_TEXT           Type clause of the object view
·   OID_TEXT_LENGTH     Length of the WITH OBJECT OID clause of the object view
·   OID_TEXT            WITH OBJECT OID clause of the object view
·   VIEW_TYPE_OWNER     Owner of the type of the view if the view is an object view
·   VIEW_TYPE           Type of the view if the view is an object view
·   SUPERVIEW_NAME      Name of the superif view is a subview

To get Full list of your Data Dictionary view run following query on your database. 

SELECT *
from  dba_views
where owner = 'SYS'
      and view_name like 'DBA%';
ORDER BY view_name desc

[N.B: You can use TEXT field to view he information about the objects]

ALL_VIEWS

Description of all_views 

·   OWNER               Owner of the view
·   VIEW_NAME           Name of the view
·   TEXT_LENGTH         Length of the view text
·   TEXT                View text
·   TYPE_TEXT_LENGTH    Length of the type clause of the object view
·   TYPE_TEXT           Type clause of the object view
·   OID_TEXT_LENGTH     Length of the WITH OBJECT OID clause of the object view
·   OID_TEXT            WITH OBJECT OID clause of the object view
·   VIEW_TYPE_OWNER     Owner of the type of the view if the view is an object view
·   VIEW_TYPE           Type of the view if the view is an object view
·   SUPERVIEW_NAME      Name of the superif view is a subview

You can also get Full list of your Data Dictionary view run following query. 

select TEXT
from ALL_VIEWS
where owner='SYSTEM'

V$FIXED_TABLE

This Dynamic performance view Contains the name of all V$, X$ and GV$ tables.

Description of V$FIXED_TABLE 

·   NAME          Name of the object
·   OBJECT_ID     Identifier of the fixed object
·   TYPE          Object type (TABLE | VIEW)
·   TABLE_NUM     Number that identifies the dynamic performance table if it is of type TABLE

To get Full list of Dynamic Performance View (V$) run following query 

SELECT name
from   V$FIXED_TABLE
where  name like 'V$%';

When using these views you should have timed_statistics set to true in your init.ora file. This has minimal effect on performance and can be permanently left on (TIMED_STATISTICS=TRUE)

GV$ views are dynamic performance views in a parallel server environment. There is a GV$ view for almost every V$ view. These have an additional column INST_ID (instance number)

Categories: DBA_VIEWS Tags: , , ,

Monotoring Session and SQL in Oracle10g

November 18, 2009 Leave a comment

Some dynamic performance view to manage oracle user session and SQL.

·        v$sqlarea
·        v$session
·        v$sess_io
·        v$session
·        v$sqltext

Finding currently running SQL

 Select sesion.sid,
        sesion.username,
        optimizer_mode,
        hash_value,
        address,
        cpu_time,
        elapsed_time,
        sql_text
  from  v$sqlarea sqlarea, v$session sesion
  where sesion.sql_hash_value = sqlarea.hash_value
   and  sesion.sql_address    = sqlarea.address
   and  sesion.username is not null

See the I/O being done by an active SQL statement 

select sess_io.sid,
       sess_io.block_gets,
       sess_io.consistent_gets,
       sess_io.physical_reads,
       sess_io.block_changes,
       sess_io.consistent_changes
from   v$sess_io sess_io, v$session sesion
where  sesion.sid = sess_io.sid
  and  sesion.username is not null

SQL to show the full SQL executing for active sessions.

select sesion.sid,
       sql_text
from   v$sqltext sqltext, v$session sesion
where  sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
order by sqltext.piece

Which sessions are active this SQL will help

Select sid,
       to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
       username,
       type,
       status,
       process,
       sql_address,
       sql_hash_value
from   v$session
where  username is not null

Killing Oracle Sessions

SQL Command to Kill Session 

ALTER SYSTEM KILL SESSION 'sid,serial#';
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Killing your own session

alter session set events 'immediate crash';

Kill sessions from the UNIX level with this command

 
kill -9 spid

N.B: This is dangerous and should not normally be done. Pick the wrong OS process and you could crash your instance. There may also be time you need to bulk kill Oracle sessionsor kill all sessions in Oracle.

Simply selecting again v$session will get you the data needed

select 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||'''IMMEDIATE;'
from v$session
where username = '<SomeUserName in upper caps>'
 and  STATUS = 'ACTIVE';

Here is a Package to manage oracle session 

create or replace package manage_users as
  procedure kill_user(the_sid in number);
  procedure kill_user(the_user in varchar2);
end manage_users;
/
create or replace package body manage_users as
  procedure kill_user(the_sid in number) as
    v_str varchar2(50) := 'alter system kill session '||chr(39);
    v_serial number;
  begin
    dbms_output.put_line(chr(10));
    dbms_output.put_line('Session to be killed:');
    dbms_output.put_line('---------------------');
    select serial# into v_serial
    from v$session
    where sid = the_sid;
    v_str := v_str||the_sid||','||v_serial||chr(39)||';';
    dbms_output.put_line(v_str);
    exception
    when no_data_found then
      dbms_output.put_line('No such session.');
  end;
  procedure kill_user(the_user in varchar2) as
    v_str varchar2(50) := 'alter system kill session '||chr(39);
    v_statement varchar2(50);
    cursor user_cur is
    select sid, serial#
    from v$session
    where username = upper(the_user);
  begin
    dbms_output.put_line(chr(10));
    dbms_output.put_line('Sessions to be killed:');
    dbms_output.put_line('----------------------');
    for user_rec in user_cur loop
     v_statement := v_str||user_rec.sid||','||user_rec.serial#||chr(39)||';';
     dbms_output.put_line(v_statement);
     v_statement := null;
    end loop;
  end;
end manage_users;
/

SQL> exec manage_users.kill_user('scott');
Sessions to be killed:
----------------------
alter system kill session '141,8';
alter system kill session '142,213';
alter system kill session '145,37';
PL/SQL procedure successfully completed.
SQL> exec manage_users.kill_user(141);
Session to be killed:
---------------------
alter system kill session '141,8';
PL/SQL procedure successfully completed.

Oracle Error : ORA-00018: maximum number of sessions exceeded

Cause: All session state objects are in use.

Action: Increase the value of the SESSIONS initialization parameter.

SELECT 'Currently, '
 || (SELECT COUNT(*) FROM V$SESSION) || ' out of '
 || DECODE(VL.SESSIONS_MAX,0,'unlimited',VL.SESSIONS_MAX)
 || ' connections are used.' AS USAGE_MESSAGE
FROM V$LICENSE VL

How to Solve the Problem

Alter system set sessions=200 scope=spfile;
shutdown immediate;
startup;

Kill all oracle sessions script

Categories: Monotoring Session And SQL Tags: , , ,

Size Of SGA

November 11, 2009 Comments off 
Size Of SGA =
             (DB_BLOCK_SIZE × BD_BLOCK_BUFFERS) +
             LOG_BUFFER + SHARED_POLL_SIZE + JAVA_POLL_SIZE

To approximate size of the SGA (Shared Global Area), use following formula:

DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE +
SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS + 1MB

NOTE: ADD IN EACH DB_nk_CACHE_SIZE. THERE CAN BE UP TO 4
DB_nk_CACHE_SIZE (2, 4, 8, 16, 32k) DEFINED. ONE OF THE BLOCK SIZES IS
THE DEFAULT BLOCK SIZE AND ITS CACHE SIZE IS DEFINED BY DB_CACHE_SIZE.

Fixed portion: The size of the fixed portion is constant for a release and a plattform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters

Variable portion: The variable portion is called variable because its size (measured in bytes) can be changed. The variable portion consists of:

Large pool (optional) Provides working space for rman (although rman will also work without large pool).

Shared pool The shared pool is used for objects that are shared among all users. For example: table definitions, PL/SQL definitions, cursors and so on.The shared pool can further be subdivided into:

  • Control structures
  • Character sets
  • Dictionary cache

The dictionary cache stores parts of the data dictionary because Oracle has to query the data dictionary very often as it is fundamental to the functioning of Oracle.

  • Library cache

The library cache is further divided into

  • Shared SQL Area,
  • PL/SQL Procedures and
  • Control Structures (Latches and Locks).

The size of the Shared Pool is essentially governed by the initialization parameter shared_pool_size (although shared_pool_size is usually smaller than the size of the shared pool, see here) and db_block_buffers (which plays a role for this size because the database buffer cache must be administered.)

v$db_object_cache displays objects (=tables, indexes, clusters, synonym definitions, PL/SQL procedures/packages and triggers) that are cached in the library cache.

The size for the variable portion is roughly equal to the result of the following statement:

select   sum(bytes)
from     v$sgastat
where    pool in ('shared pool', 'java pool', 'large pool');

Redo log buffer Redo Buffers is roughly equal to the parameter log_buffer.

Database buffer cache It’s size is equal to db_block_size * db_block_buffers.

(Note: db_block_buffers is deprecated as of 9i, so if the init parameter db_cache_size) is set, the buffer cache’s size will be set according to this value.

UGA If the instance is running in MTS mode, there’se also a UGA: user global area Showing information about the SGA You can use v$sga to show the amount of these sizes or alternatively use SHOW SGA in sql*plus.

The following initialization parameters affect the size of the SGA:

  • db_block_buffers
  • db_block_size
  • db_cache_size
  • db_keep_cache_size
  • db_recycle_cache_size
  • java_pool_size
  • large_pool_size
  • log_buffer
  • shared_pool_size
  • streams_pool_size

Limiting the size of the SGA for a user: The amount of SGA that a user can use can be limitted through profiles. Use the private_sga option in the create profile statement.

Dynamic SGA : Dynamic SGA allows to change the size of the buffer cache, the large pool, the shared pool and the process private memory on the fly, that is without shutting down the instance.

Granule size The granule size of the components can be found out with v$sga_dynamic_components.

Peeking into SGA The x$ tables are an sql interface to the SGA and allow to peek into the SGA.

Shared server vs dedicated server The request and response queues and other parts that are found in the pga as dedicated server are found in the sga when the server is a shared server.

Protecting data structures As the SGA is, after all, a shared area, and more than one process can perform read and write operations on the SGA, Oracle has to make sure that those operations don’t get in their ways. This is ensured with locks and latches.

Locking SGA into memory On platform that support it, the init parameter lock_sga can be set to true, which will lock the entire SGA into physical memory.

(Source www.adp-gmbh.ch)

Automate the Sizing of SGA in Oracle 10g

SGA _TARGET provides the following:

  • Single parameter for total SGA size
  • Automatically sizes SGA components
  • Memory is transferred to where most needed
  • Uses workload information
  • Uses internal advisory predictions
  • STATISTICS_LEVEL must be set to TYPICAL

Enable SGA_TARGET 

SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 600M

As we can see out automatic SGA tuning is not enabled so we can enable it by setting the SGA_TARGET parameter value.

SQL> alter system set sga_target=500m;
System altered.
SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
sga_target                           big integer 500M

Resize SGA_TARGET

  • SGA_TARGET is dynamic
  • Can be increased till SGA_MAX_SIZE
  • Can be reduced till some component reaches minimum size
  • Change in value of SGA_TARGET affects only automatically sized components
SQL> show parameter sga_max_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
sga_max_size                         big integer 600M
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
sga_target                           big integer 500M

WE can resize it to only 600m if we will try to increase it from 600m we will get error.

SQL> alter system set sga_target=605m;
alter system set sga_target=605m *
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00823: Specified value of sga_target greater than sga_max_size

For that we must first increase our SGA_MAX_SIZE parameter value.But we must restart out instance because its STATIC parameter.

SQL> alter system set sga_max_size=500 scope=spfile;
System altered.
or
SQL> ALTER SYSTEM SET sga_max_size='524288000'scope=SPFILE  --sga_max_size set to 500MB
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             624953196 bytes
Database Buffers          369098752 bytes
Redo Buffers                4800512 bytes
Database mounted.
Database opened.
SQL> show parameter sga_max_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
sga_max_size                         big integer 500M

SQL> alter system set sga_target=450m;
System altered.

Disable SGA_TARGET

We can Disable our automatic SGA tuning by setting the parameter SGA_TARGET to value digit 0.

SQL> alter system set sga_target=0;

System altered.

To Take SGA Information Run following query.

SQL> select NAME,BYTES/(1024*1024) as "Size In MB",RESIZEABLE  from v$sgainfo
Categories: Size Of SGA Tags:

Automating Shutdown and Startup Oracle10g Database

October 31, 2009 3 comments

To automate database startup and shutdown by using the dbstart and dbshut scripts:

1. Log in as the root user.

2. Edit the oratab file for your platform.

once the instance is created, edit the “/etc/oratab” file setting the restart flag for each instance to ‘Y’.

To open the file, use one of the following commands:

$ vi /etc/oratab

3. Create a file called dbora in /etc/init.d .

$vi /etc/init.d/dbora

!/bin/bash
# Run level script to start Oracle 10g services on RedHat Enterprise Linux (RHEL4.6)
# -------------------------------------------------------------------------
# Copyright (c) 2009 Tamim Khan
# Comment/suggestion: tamimdba@gmail.com
# -------------------------------------------------------------------------
# chkconfig: 345 91 19
# description: Startup/Shutdown Oracle service
# File Location: /etc/rc.d/init.d/dbora

OUSER="oracle"
OPATH="/u01/app/oracle/product/10.2.0/db_1"

# check Oracle db status
function chkdb_status() {

# set username
SUSER="scott"
# set password
SPASS="tiger"

sqlplus -s /nolog > /dev/null 2>&1 <<EOF
whenever sqlerror exit failure
connect $SUSER/$SPASS
exit success
EOF

if [ $? -ne 0 ]; then
 echo "Connection failed : DB is down"
 exit 1
else
 echo "Connection succeeded : DB is up"
fi
}

case "$1" in
 start)
 echo  "### Starting Oracle ### "
 su - $OUSER -c "$OPATH/bin/lsnrctl start"
 su - $OUSER -c "$OPATH/bin/dbstart"
 ;;
 stop)
 echo  "### Stopping Oracle ### "
 su - $OUSER -c "$OPATH/bin/lsnrctl stop"
 su - $OUSER -c "$OPATH/bin/dbshut"
 ;;
 restart)
 $0 stop
 $1 start
 ;;
 isqlstart)
 echo  "### Starting Oracle iSQL Plus ### "
 su - $OUSER -c "$OPATH/bin/isqlplusctl start"
 echo "### Note: You can access service at url:  http://$(hostname):5560/isqlplus"
 ;;
 isqlstop)
 echo  "### Stopping Oracle iSQL Plus ### "
 su - $OUSER -c "$OPATH/bin/isqlplusctl stop"
 ;;
 emstart)
 echo  "### Starting Oracle Enterprise Manager 10g Database Control ###"
 su - $OUSER -c "$OPATH/bin/emctl start dbconsole"
 echo "### Note: You can access service at url:  http://$(hostname):1158/em"
 ;;
 emstop)
 echo  "### Stopping Oracle Enterprise Manager 10g Database Control ###"
 su - $OUSER -c "$OPATH/bin/emctl stop dbconsole"
 ;;
 status)
 echo "### Oracle database status ###"
 chkdb_status
 ;;
 *)
 echo $"Usage: $0 {start|stop|isqlstart|isqlstop|emstart|emstop}"
 exit 1
esac
exit 0

The lines to start and stop the listener can be removed under Oracle 10g release 2, as the dbstart command includes an automatic start of the listener.

4. Change the group of the dbora file to the OSDBA group (typically dba), and set the permissions to 750:

$ chgrp dba dbora
$ chmod 750 dbora

5. Add dbora in checkcofig:

    $ chkconfig  --add dbora
    $ chkconfig  --level 345 dbora on
Categories: dbora Script for RHEL Tags: ,