User access to database within limits of time

:C:\Documents and Settings\Administrator>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user shahid identified by shahid;

User created.

SQL> grant connect, resource to shahid;

Grant succeeded.

SQL> conn shahid/shahid
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
2 AFTER LOGON ON DATABASE
3 BEGIN
4 IF USER = 'SHAHID' THEN
5 IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
6 THEN
7 RAISE_APPLICATION_ERROR(-20998,' Dear user 'USER'! You can''t login between 08 and 22');
8 END IF;
9 END IF;
10 END limit_connection;
11 /

Trigger created.

SQL> select to_char(sysdate,'hh24') from dual;

TO
--
23

SQL> conn shahid/shahid
Connected.
SQL> select to_char(sysdate,'hh24') from dual;

TO
--
18

SQL> conn shahid/shahid
ERROR:ORA-00604: error occurred at recursive SQL level 1ORA-20998: Dear user SHAHID! You can't login between 08 and 22ORA-06512: at line 5 Warning: You are no longer connected to ORACLE.SQL>

SNIPED sessions and ORA-00020: maximum number of processes (%s) exceeded

When you implement the resource limit, the sessions that exceed the IDLE limit is marked as SNIPED in V$SESSION view and you may get “ORA-00020: maximum number of processes (%s) exceeded” error because Oracle doesn’t kill that session in OS level and it assumes it as a “process”. So for this, you need to kill those sessions manually
Here I show a little demonstration of the whole process: – First of all, set the RESOURCE_LIMIT parameter to TRUE to enforce the resource limit in database profilesSQL> show parameter resource_limit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE
SQL> alter system set resource_limit=true;
System altered.
- Then create a profile and set IDLE_TIME to 1 minute:SQL> create profile test_profile limit
2 idle_time 1;
Profile created.
- Create a user and assign the profile to that user:SQL> grant dba to usr identified by usr;
Grant succeeded.
SQL> alter user usr profile test_profile;
User altered.
- Change the PROCESSES parameter to make the maximum number of operating system processes lowerSQL> show parameter process
NAME TYPE VALUE
processes integer 150
SQL> alter system set processes=25 scope=spfile;
System altered.
SQL> startup force
SQL> show parameter processes
NAME TYPE VALUE
processes integer 25
SQL> select count(1) from v$process;
COUNT(1)
----------
22
Now open two different terminals and connect to the database with USR user:sqlplus usr/usr
Check the view V$PROCESS. It should be 24SQL> select count(1) from v$process;
COUNT(1)
----------
24
Now open third terminal and try to connect to the database with the user USR.You will get an error because the count of the processes will reach the limit:[oracle@localhost ~]$ sqlplus usr/usr
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
Enter user-name:
SQL>
Now wait for a minute to reach the limit of the IDLE_LIMIT resource (we’ve set it to 1 minute) and query the SYSDATE from any USR session:SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;
STATUS COUNT(1) USERNAME
-------- ---------- ------------------------------
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
SNIPED 2 USR
3
That’s the issue. If you try to connect to the database, you’ll get ORA-00020 error again. Please note that SNIPED doesn’t mean that it’s KILLED. It is not either killed, nor active. The user is not able to run any query, however it holds a process on OS level:SQL> select count(1) from v$process;
COUNT(1)
----------
24
Run any query with already connected (and SNIPED) USR user. You’ll get the following error:SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again
SQL>
Now query V$SESSION and V$PROCESS views again:SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;
STATUS COUNT(1) USERNAME
-------- ---------- ------------------------------
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
SNIPED 1 USR
4
SQL> select count(1) from v$process;
COUNT(1)
----------
24
The process will be free only when you “exit” from Sql*Plus. Exit from the session that you got an error and query V$PROCESS again:SQL> select count(1) from v$process;
COUNT(1)
----------
23
To kill the SNIPED sessions you have two options. The first option is to run ALTER SYSTEM KILL SESSION command. For this you need to get SID and SERIAL# of the sniped session.SQL> select sid, s.serial#, status from v$process p, v$session s
where paddr=addr
and s.username='USR';
SID SERIAL# STATUS
---------- ---------- --------
9 10 SNIPED
SQL> alter system kill session '9,10' immediate;
System altered.
SQL> select s.status, count(1), s.username from v$process p, v$session s
where paddr(+)=addr
group by s.status, s.username
order by 1;
STATUS COUNT(1) USERNAME
-------- ---------- ------------------------------
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
KILLED 1 USR
3
After some seconds you’ll see that the session is cleared from both views:SQL> /
STATUS COUNT(1) USERNAME
-------- ---------- ------------------------------
ACTIVE 1 SYS
ACTIVE 16
INACTIVE 2 SYS
3
However, due to some bugs, sometimes you may not get the sessions killed using ALTER SYSTEM KILL SESSSION command. For this, you need to kill the process from OS level.SQL> select spid, status from v$process p, v$session s
where paddr=addr
and s.username='USR';
SPID STATUS
------------ --------
2795 SNIPED
[oracle@localhost ~]$ kill -9 2795
SQL> select spid, status from v$process p, v$session s
where paddr=addr
and s.username='USR';
no rows selected
SQL>
Run any sql command on the killed session:SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
SQL>

Improve export import speed

You can utilize parallelizing export when using data pump,but for traditional export use the following
Export of Databases - reads data by running a select statement of the data and generating the DDL to perform the import process in future.
Fast Exports
1) Use direct=y 2) Until disk is not fully utilized try running exports in parallel. 3) Keep export file on different disk then the datafiles. 4) Run exports in two part rather than one i.e. 1st rows=n and second as indexes=n rows=y constraints=n.
Fast Imports
1) Use the first file out of two files created with the exports, It will insert all data but will not create any indexes and constraints. Once the data insertion is done run imp with indexfile option to extract script of index creation in text file. edit the file to include parallel clause and set parameters db_file_multiblock_read_count to 128 with sort_area_size to a higher value and workarea_size_policy=AUTO.

Step By Step Installation Of Oracle Applications Release 12

STEP by STEP Installation of Oracle Applications Release 12
This is the install process for R12 Oracle applications on LINUX. Assuming that the LINUX is ready with all the rpms and OS settings.
I am doing a multiuser installation on LINUX.
In order to prepare for a multi-user installation, you must first create an oracle user account and an applmgr user account. Both should be created with a default
shell that is compatible with the Bourne shell. Log in as root to run Rapid Install. Then specify the oracle user as the Oracle OS user, and the applmgr user as the
Apps OS user.
Please complete all standard pre-reqs for LINUX install ( Like "/etc/sysctl.conf" for Shared memory and semaphors)
Create Oracle user
#useradd -u 1100 -g dba oraalpha #passwd oraalpha Changing password for user oraalpha. New UNIX password: BAD PASSWORD: it is based on a dictionary word Retype new UNIX password: passwd: all authentication tokens updated successfully. #
#useradd -u 1101 -g dba appalpha #passwd appalpha Changing password for user appalpha. New UNIX password: BAD PASSWORD: it is based on a dictionary word Retype new UNIX password: passwd: all authentication tokens updated successfully.
Create the STAGE area (Stage area is the software location for R12 software)
The script prompts you to choose the components that you want to stage:
Oracle Applications
Oracle Database technology stack (RDBMS)
Oracle Applications database (Databases)
Oracle Applications technology stack (Tools)
APPL_TOP
You should be able to see the following directories. (After your stage location is ready)Software/Disk1/rapidwiz/adautostg.pl : This helps in creating the stage area for your installation.
startCDoraDBoraAppDBoraASoraApps
After your stage area is ready, You can start the installation.Please note that starting with R12 you need not to install the JDK seprately.
Login as root (on GUI , May be a VNC session)
cd /StageR12/startCD/Disk1/rapidwiz Please note : If you want to use an alias (not the actual name of the host machine), use the -servername parameter when you start Rapid Install.
rapidwiz -servername alias rapidwiz -techstack (Can be used to install techstack only)
If install fails in between and you may need to restart it , You can use rapidwiz -restart ========Installation Begins...cd /StageR12/startCD/Disk1/rapidwiz ./rapidwizOracle applications release 12 installation screen 1.
Oracle applications release 12 installation screen : This is welcome screen
Another important discion to make here. If you want to keep all services on a single node you should do a Single node installation. If you want to spread the services on different boxes you can add another node. In General, The recommonded way is that we should keep the admin and Db server on one node and web/form on another node. This is to get the best perofmance.

Installation in progress....



Post innstall checks.

This is a known error and generally occurs if you donot have enough memory at the time of post check. If you retry you may get success, Otherwise free some memory and then retry.

You must have all greens here in order to have your oracle applications work.

You are now ready to use the Oracle Applocations Release 12

Enter with Default username and password
You must change all password to secure your oracle applications here.

Error Details:checking URL = http://:/OA_HTML/AppsLogin RW-50016: Error: - {0} was not created:File = {1}Fix: This is a known error and generally occurs if you donot have enough memory at the time of post check. If you retry you may get success, Otherwise free some memory and then retry.you may check this from DBA also, That this is related to Memory.ORA-00060: Deadlock detected. More info in file /u05/oracle/ALPHA/db/tech_st/10.2.0/admin/ALPHA_serv0611/udump/alpha_ora_10277.trc.Wed Apr 14 01:47:32 2010Process J000 died, see its trace fileWed Apr 14 01:47:32 2010kkjcre1p: unable to spawn jobq slave processWed Apr 14 01:47:32 2010Errors in file /u05/oracle/ALPHA/db/tech_st/10.2.0/admin/ALPHA_serv0611/bdump/alpha_cjq0_16160.trc:Waited for process J000 to initialize for 60 seconds*** 2010-04-14 00:31:20.440Dumping diagnostic information for J000:OS pid = 27634loadavg : 11.75 5.49 5.12memory info: free memory = 0.00Mswap info: free = 0.00M alloc = 0.00M total = 0.00MF S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD000 D oraalpha 27634 1 0 78 0 - 23427 lock_p Mar22 ? 00:00:00 ora_j000_ALPHA(no debugging symbols found)Using host libthread_db library "/lib/tls/libthread_db.so.1".(no debugging symbols found)[Thread debugging using libthread_db enabled]Please feel free to write if you need additional information on R12 install. This blog is for STUDY purpose only, Please read Oracle Official documentations for the Refrences.

Setting an Oracle Environment variable – ORACLE_HOME

Hello,When I check my blog hit counter statistics, my blog is being hit and searched by the blog users with the word “Oracle Home”, but, looks like they are not finding the required information on “Oracle Home” Environment variable. So, I wanted to explain about ORACLE_HOME in my simple terms which is helpful for novice.What is ORACLE_HOME used for?* The ORACLE_HOME is an environment variable which is used to set and define the path of Oracle Home (server) Directory.* The ORACLE_HOME directory will have the sub directories, binaries, executables, programs, scripts, etc. for the Oracle Database.* This directory can be used by any user who wants to use the particular database.* If the ORACLE_HOME variable is defined as an environment variable, then during the installation process, the Oracle Home Path will be set to the directory defined as default. If the variable is not defined, then the Oracle will take its own default location. i.e. The ORACLE_HOME variable does not have to be preset as an environment variable, it can be set during the installation process.* Basically The ORACLE_HOME variable is in the following ORACLE_BASE directory:ORACLE_HOME=$ORACLE_BASE/product/10.2.0.What is ORACLE_BASE used for?* The ORACLE_BASE is also an environment variable to define the base/root level directory where you will have the Oracle Database directory tree - ORACLE_HOME defined under the ORACLE_BASE directory.* Basically, The ORACLE_BASE directory is a higher-level directory, than ORACLE_HOME, that you can use to install the various Oracle Software Products and the same Oracle base directory can be used for more than one installation.Note: If you did not set the ORACLE_BASE environment variable before starting OUI, the Oracle home directory is created in an app/username/directory on the first existing and writable directory from /u01 through /u09 for UNIX and Linux systems, or on the disk drive with the most available space for Windows systems. If /u01 through /u09 does not exist on the UNIX or Linux system, then the default location is user_home_directory/app/username.How to check if ORACLE_HOME is set already?On Unix/Linux Systems:Basically, before or after the Oracle Database is installed, the oracle user profile, the environment variable file, is prepared where all the required environment variables for Oracle are set. i.e. ORACLE_BASE, ORACLE_HOME, ORACLE_SID,PATH, LD_LIBRARY_PATH, NLS_LANG, etc.The user profile file can be.bash_profile – Bash Shell.profile – Bourne Shell or Korn shell.login­ – C ShellNote: This user profile file will be under user’s home directory i.e. $HOME/.bash_profileTo check specific environment variable set:$ echo $ORACLE_HOMETo check all the environment variables set:$ envOn Windows Systems:To check specific environment variable set:C:\> set ORACLE_HOMEORC:\echo %ORACLE_HOME%To check all the environment variables set:C:\> setOrC:\> envOther way, to check the ORACLE_HOME, is as follows.Start -> Run -> Regedit (enter) -> HKEY_LOCAL_MACHINE -> SOFTWARE –> ORACLEi.e. My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLEHow to check using sqlplus command:To find the ORACLE_HOME path in Oracle DatabaseHow to set the ORACLE_HOME environment variable?On Unix/Linux Systems:Define the ORACLE_HOME value in the user profile file i.e. .bash_profile or .profileORACLE_HOME=$ORACLE_BASE/product/10.2.0export ORACLE_HOMESource the user profile as follows:Bash shell:$ . ./.bash_profileBourne shell or Korn shell:$ . ./.profileC shell:% source ./.loginIf no profile file is set with environment variables, then physically also be set as follows:Bourne, Bash, or Korn shell:$ ORACLE_BASE=/oracle/app$ export ORACLE_BASE$ ORACLE_HOME=$ORACLE_BASE/product/10.2.0$ export ORACLE_HOMEC Shell:% setenv ORACLE_BASE /oracle/app% setenv ORACLE_HOME /oracle/app/product/10.2.0On Windows Systems:My Computer -> Properties -> Advanced -> Environment Variables -> System Variables -> New/Edit/Delete (to set the variables)After setting the environment variables as above, open a fresh CMD tool and check whether they set properly or not. Do not try on already opened CMD tool to make sure the variables set or not.Another way to physically set the variables as follow at the DOS prompt:C:\> set ORACLE_HOME=C:\oracle\app\product\10.2.0C:\> echo %ORACLE_HOME%Note: I would suggest to refer the Oracle Documentation on Installation where these environment variables are discussed and explained with the enough information.

Regards:
Shahid ul Ghani
http://www.dbashahid.blogspot.com

Should I go for Oracle 10g OCA/OCP or 11g OCA/OCP Certification?

Saturday, December 13, 2008


Hello All,Choosing whether to go for Oracle 10g OCA/OCP or 11g OCA/OCP Certification is becoming complex to the Novice or Newbie DBAs. And also, I have seen a couple of posts asking such similar certification doubts more frequently in the OTN Forums, when they are not really sure or confused.Well, this is ever been told by everyone that going for latest version certification is good and ideal. But, what I advise is, first go for Oracle 10g OCA and OCP, then upgrade to Oracle 11g OCP.Following are my views on why to go for Oracle 10g OCA/OCP initially rather than 11g OCA/OCP directly.
As we all know that newer version (11g) does include older version features plus new features and bug fixes of older version issues.
Retirement date for Oracle 10g Certification has not yet been announced by Oracle. Moreover, Oracle Database 11g: Administration I (OCA) exam is only on production i.e. regular and Oracle Database 11g: Administration II (OCP) exam is not yet gone for Production i.e. still Beta Exam.
Oracle Database 10g is still being used as Production for the business in all most all the organizations in the globe. But very less companies are using the Oracle Database 11g for their business, as Oracle 11g is still a base release 11.1.0.6 and yet to go for standard release 11g (11.2.X.X) shortly. This means that Oracle 11g is not fully deployed or used for Production purpose yet.
Oracle Database 10g Release (10.2) still has Oracle primary and extended (Metalink) support for few more years from now, after that Oracle 10g will also be de-supported by Oracle.
Both versioned (10g and 11g) certifications have two exams – Administration I (OCA) and Administration II (OCP). Each exam fees of them is $125 US i.e. there is no vary.
It’s mandatory for the OCP candidates to undergo one approved course from the Approved list of Oracle University Courses for hands on course requirement. This applies to both Oracle 10g and 11g Certification takers.
For Oracle 10g OCP Certification holders, there is only one exam 1Z0-050 - Oracle Database 11g: New Features for Administrators given to upgrade directly to the Oracle 11g OCP. No course or hands on course requirement form is to be submitted to achieve the Oracle 11g OCP (Upgrade) Certification.
In this way, one will have both Oracle 10g and 11g Certifications in hand, and can show the same in their resume or CV. This also fulfills the requirement where the companies are looking for the candidates those are having enough experience with Oracle 10g and 11g, and holding multiple certifications in it.One can go for direct Oracle 11g Certification under the following circumstances.
If you are forced, by your company or manager, to undergo Oracle 11g Course and take Oracle 11g Certification Exams, for their specific requirement on Oracle Database 11g related projects.

How the Optimizer can use Constraint Definitions

A simple demonstration of how properly defined Referential Integrity constraints can be used by the Optimizer.....
If two tables (say SALES and SALES_LINES) have a Parent-Child relationship but the proper R.I. constraint is not defined, it is possible (either through faulty application code that uses two separate transactions for an INSERT {or a DELETE} against the two tables OR through erroneous adhoc updates to the data) to have "dangling" child records.
For example, an INSERT into SALES errors out (for lack of space in that tablespace) but the corresponding INSERT into SALES_LINES succeeds and commits, when executed as a separate transaction.
Or an adhoc "data-fix" operation deletes rows from the SALES table without having deleted them from the SALES_LINES table first.
Having UNIQUE Indexes on the two tables will not prevent the occurrence of "parent-less children" !
When querying the SALES_LINES table, it might be necessary to validate that the corresponding SALES row exists. This could be done as a join between the two tables, explicitly included in every query against the SALES_LINES table.
Assuming that the two tables have 100,000 rows (each sale having only 1 line), with proper Unique Indexes, a query for one PROD_ID of 10 different products may execute as :SQL> -- Query for Total Sales for PROD_ID=5SQL> select sum(l.quantity), sum(l.quantity*l.price)2 from sales_lines l, sales s3 where4 -- join code is used to validate that the sale_id is legitimate5 l.sale_id = s.sale_id6 and l.prod_id=57 and l.sale_id between 25000 and 350008 /SUM(L.QUANTITY) SUM(L.QUANTITY*L.PRICE)--------------- -----------------------2441786 48892118.9Execution Plan----------------------------------------------------------Plan hash value: 146457679------------------------------------------------------------------------------------------------ Id Operation Name Rows Bytes Cost (%CPU) Time ------------------------------------------------------------------------------------------------ 0 SELECT STATEMENT 1 39 139 (0) 00:00:02 1 SORT AGGREGATE 1 39 2 NESTED LOOPS 984 38376 139 (0) 00:00:02 * 3 TABLE ACCESS BY INDEX ROWID SALES_LINES 984 33456 139 (0) 00:00:02 * 4 INDEX RANGE SCAN SALES_LINES_UK 10000 24 (0) 00:00:01 * 5 INDEX UNIQUE SCAN SALES_UK 1 5 0 (0) 00:00:01 ------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - filter("L"."PROD_ID"=5)4 - access("L"."SALE_ID">=25000 AND "L"."SALE_ID"<=35000)5 - access("L"."SALE_ID"="S"."SALE_ID") filter("S"."SALE_ID"<=35000 AND "S"."SALE_ID">=25000)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 281 consistent gets 0 physical reads 0 redo size 523 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed However, if I were to define the Constraints as :SQL> REM REM ######################################SQL> REM Now add the Constraint Definitions !SQL> alter table SALES add constraint SALES_PK primary key (sale_id);Table altered.SQL> alter table SALES_LINES add constraint SALES_LINES_FK foreign key (sale_id) references SALES (sale_id);Table altered.SQL> REM REM ###################################### and I re-run the query :SQL> select sum(l.quantity), sum(l.quantity*l.price)2 from sales_lines l, sales s3 where4 -- join code is used to validate that the sale_id is legitimate5 l.sale_id = s.sale_id6 and l.prod_id=57 and l.sale_id between 25000 and 350008 /SUM(L.QUANTITY) SUM(L.QUANTITY*L.PRICE)--------------- -----------------------2441786 48892118.9Execution Plan----------------------------------------------------------Plan hash value: 2517766180----------------------------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time ----------------------------------------------------------------------------------------------- 0 SELECT STATEMENT 1 34 139 (0) 00:00:02 1 SORT AGGREGATE 1 34 * 2 TABLE ACCESS BY INDEX ROWID SALES_LINES 984 33456 139 (0) 00:00:02 * 3 INDEX RANGE SCAN SALES_LINES_UK 10000 24 (0) 00:00:01 -----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - filter("L"."PROD_ID"=5)3 - access("L"."SALE_ID">=25000 AND "L"."SALE_ID"<=35000)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 139 consistent gets 0 physical reads 0 redo size 523 bytes sent via SQL*Net to client 419 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed Oracle can simply eliminate the lookup on the SALES table and do much fewer consistent gets.The presence of the constraint ensures that every SALE_ID in the (child) SALES_LINES table *does* have corresponding SALE_ID in the (parent) SALES table. Since I am not fetching any columns from the SALES table, the join is now unnecessary and the optimizer (smartly) eliminates the join..