Sunday, September 30, 2012

Checkpoint Not Complete


Error Will be In AlertSID.log Like This :
/u01/app/oracle/oradata/redo04.log
Thu Jan 14 22:12:55 2011
Thread 1 cannot allocate new log, sequence 46352
Checkpoint not complete


To Solve this Issue you Can Do More Than One Thing But All of the Solution is Simple :

Solution One :

Modify Database Parameter archive_lag_target Like This :

alter system set archive_lag_target=0 scope=both;

Solution two :

1-backup Full Database
2-Check Free Disk Space
3-Start Do the Following :

SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;

select group#, status from v$log;

Now You need to Work Step by Step On Inactive Logs , and Stat Drop Them like the following :

alter database drop logfile group 1;
Re add the Log File

alter database add logfile group 1 ( ‘/Log-Name01.log‘,/Log-Name02.log’ ) size 75M

alter system switch logfile; / Alter System Checkpoint
 Do this For The Group that you have , and rearrange them again By Adding new Group contain more than one redo log inside them


Simple !!!

Enjoy
Osama Mustafa

How You Know High I/O

Thanks For Pavan at first , All You Have to do Run the Below Query :

select p.spid, s.sid,s.process cli_process, s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,lpad(t.sql_text,30) “Last SQL”
from v$session s, v$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 10000
order by t.disk_reads desc;

Enjoy 
Osama Mustafa

Thursday, September 27, 2012

OUI-10022: The target area cannot be used

Error :

OUI-10022: The target area cannot be used because it is in an invaild state


Solution :

is so simple

check permission on directory .
every thing is Ok .


1. Backup the existing /etc/oraInst.loc file
2. modify /etc/oraInst.loc as follows:

change:

inventory_loc=Old-value

to

inventory_loc=Where you want to create (Usually Oracle_Home)


Enjoy
Osama Mustafa

Wednesday, September 26, 2012

Change Sys Password in Data Gaurd

Sometimes you need to change Sys password which is Simple process in Single Database  , but i f you have Data Guard (Primary , Standby ) Database .

For Some Administration purpose Oracle need Sys password to be identical so if you change Password On Primary you receive Error :

Check that the primary and standby are using a password file and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files.
returning error ORA-16191


 
This is error appeared because on run alter user sys identified by password on primary Database which is saved in dictionary tables , how to fix

On Standby Run orapwd command
orapwd file=$ORACLE_HOME/dbs/orapwSID password=newpassword;


Always change sys password on both primary and standby to be the same


Enjoy
Osama Mustafa



Tuesday, September 25, 2012

RMAN Performance Tuning

There's Amazing Note On MOS

RMAN Myths Dispelled: Common RMAN Performance Misconceptions [ID 134214.1]
RMAN Restore Database Slow / How To Improve RMAN Restore Performance [ID 467694.1]
Advise On How To Improve Rman Performance [ID 579158.1]
 RMAN Performance Tuning Diagnostics [ID 311068.1]
RMAN Performance Tuning Using Buffer Memory Parameters [ID 1072545.1]
RMAN: Monitoring Recovery Manager Jobs [ID 144640.1]
 
 
Enjoy 
Osama Mustafa
 
 
 
 

emca/emcaDbUtil: perl/bin/perl: not found

Error

sept 24, 2012 4:01:02 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
CONFIG: Exit value of 1
sept 24, 2012 4:01:02 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
CONFIG: /u01/oracle/product/11.2.0/sysman/admin/scripts/emca/emcaDbUtil: /u01/oracle/product/11.2.0/perl/bin/perl: not found
 
 

Solution :

1- go to $ORACLE_HOME/bin open emca script using

vi emca 

And Correct $ORACLE_HOME inside it .

2-if the above solution not working  , go to emca log location and check perl executable location ,
for example :
/u01/oracle/product/11.2.0/perl/bin/perl
We get this location from the error log , then find out the Location of perl in $ORACLE_HOME/perl and try to create link to actual location :

ln -s /u01/oracle/product/11.2.0/perl   /u01/oracle/product/11.2.0/perl

















Sunday, September 23, 2012

ORA-03135: connection lost contact

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 23 16:21:46 2012

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

ERROR:
ORA-03135: connection lost contact


workaround:
On the Oracle database server machine open file $ORACLE_HOME/network/admin/sqlnet.ora
Set parameter
SQLNET.EXPIRE_TIME=XX

xx: number



enjoy
Osama Mustafa

Thursday, September 20, 2012

ORA-00054: resource busy and acquire with NOWAIT

There's More than One Solution I would Love to Share :

1-Before 11g, you can mark the tablespace read-only for the duration of the alter table 

Alter Tablespace Test read only
2-in 11g you can mark table read-only :

Alter table test Read only
And You can Check Read Only Tables By :


Select    table_name, read_only from    dba_tables
Where owner = ‘myowner’ and table_name = ‘mytab’;

Just To Make Sure After You Alter table to Read Only prevent Update by Create Trigger



create or replace trigger
   tabl_read_only
before
   insert or update or delete
on mytab
begin
raise_application_error (-999999, 'Table Is Read Only Now');
end; /
3- You Can Check if there's Lock on your Database :



SQL> set linesize 130
SQL> set pages 100
SQL> col username       format a20
SQL> col sess_id        format a10
SQL> col object format a25
SQL> col mode_held      format a10
SQL> select     oracle_username || ' (' || s.osuser || ')' username
,  s.sid || ',' || s.serial# sess_id
,  owner || '.' || object_name object
,  object_type
,  decode( l.block
,       0, 'Not Blocking'
,       1, 'Blocking'
,       2, 'Global') status
,  decode(v.locked_mode
,       0, 'None'
,       1, 'Null'
,       2, 'Row-S (SS)'
,       3, 'Row-X (SX)'
,       4, 'Share'
,       5, 'S/Row-X (SSX)'
,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
,  session_id
/
  Use

SQL> alter system kill session 'SID,Serial# ';


Enjoy 
OSama Mustafa





 

Wednesday, September 19, 2012

Apply PSU Patch in Oracle

* You Must have two thing to apply PSU Patch :
1-Lastest version For Optach.
2-PSU Patch that you want to apply.


Steps:

$ORACLE_HOME/OPatch/opatch version
Output will be:
Invoking OPatch 11.2.0.1.7
OPatch Version:  11.2.0.1.7
OPatch succeeded 

1-you need to Update latest version For Optach, to do this :

cd $ORACLE_HOME

cp -r Optach/ /u01/backup/Optach

**make sure you in ORACLE_HOME

rm -rf $ORACLE_HOME/Optach

unzip Optach_that_you_download_from_MOS inside $ORACLE_HOME.

Patch Number :6880880

2-Check Optach After Doing Above Steps :

$ORACLE_HOME/OPatch/opatch version
Output will be:
Invoking OPatch 11.2.0.3.0
OPatch Version:  11.2.0.3.0
OPatch succeeded

3-Apply PSU Patch By Doing the Following, for example :

unzip p13923374_11203_.zip
cd 13923374
opatch apply

Answer the question that you been asked by Oracle 

4- Post Installation Steps :

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
 SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT

Create a login screen in oracle forms builder 10g

First :

Create table with two columns (username , password )

CREATE TABLE PASSWORD
(USER_ID    NUMBER(3)  CONSTRAINT  PASSWORD_USER_ID_PK PRIMARY KEY,
 PASSWORD VARCHAR2(20) NOT NULL);
INSERT INTO PASSWORD (USER_ID,PASSWORD)
VALUES (1,100);
INSERT INTO PASSWORD (USER_ID,PASSWORD)
VALUES (2,200);

Second:

Create Forms Look Like the Following :

Third :

You have two way to check Username, password :

Using Trigger in When_button_presses

BEGIN
SELECT USER_ID
INTO   :GLOBAL.USER_ID
FROM   PASSWORD
WHERE  USER_ID = :LOGIN.TI_USER_ID
AND    PASSWORD= :LOGIN.TI_PASSWORD;
GO_BLOCK('PASSWORD');
EXCEPTION
WHEN NO_DATA_FOUND THEN
:GLOBAL.COUNT := :GLOBAL.COUNT + 1;
IF :GLOBAL.COUNT = 1 THEN
MESSAGE('wrong');
MESSAGE('wrong');
ELSIF
:GLOBAL.COUNT = 2 THEN
MESSAGE('wrong');
MESSAGE('wrong');
ELSIF
:GLOBAL.COUNT = 3 THEN
EXIT_FORM;
END IF;
END;
there's million way to do this . this is the easy one


you Can Create procedure inside Program_unit Name it Vaildation_pass.





But Inside When_button_pressed Write procedure name .

 

 Vaildation_pass
PROCEDURE VALIDATION_PASS IS
BEGIN
SELECT USER_ID
INTO   :GLOBAL.USER_ID
FROM   PASSWORD
WHERE  USER_ID = :LOGIN.TI_USER_ID
AND    PASSWORD= :LOGIN.TI_PASSWORD;
GO_BLOCK('PASSWORD');
EXCEPTION
WHEN NO_DATA_FOUND THEN
:GLOBAL.COUNT := :GLOBAL.COUNT + 1;
IF :GLOBAL.COUNT = 1 THEN
MESSAGE(wrong');
MESSAGE(wrong');

ELSIF
:GLOBAL.COUNT = 2 THEN
MESSAGE(wrong');
MESSAGE(wrong');
ELSIF
:GLOBAL.COUNT = 3 THEN
EXIT_FORM;
END IF;
END;

Notice that above code contain global_variable So We need to add Trigger (form_level)/When_new_forms_instance.

Inside this trigger add  :GLOBAL.COUNT = 0


Subject Written by Arefa in Arabic Review it here

Thank You
Osama Mustafa

sysman.emcp.ParamsManager setFlag

This error Appears when are trying maunally creating EM using :

emca -config dbcontrol db -repos create 

To Solve this error make sure you do all the below steps :


1-In your local users and groups add User to ORA_DBA group.
2-Set ORACLE_HOME and ORACLE_SID.

3- sqlplus / as sysdba

@$ORACLE_HOME/rdbms/admin/dbmspool.sql
Package created.


Create the dbmspool package body:

@$ORACLE_HOME/rdbms/admin/prvtpool.plb
View created.
Package body created.


Grant execution privilege on the package:
 

 SQL> grant execute on dbms_shared_pool to sysman;
Grant succeeded.


SQL> grant execute on dbms_shared_pool to dba;
Grant succeeded.


4-Reinstall EM Using
 
emca -deconfig dbcontrol db -repos drop
emca -config dbcontrol db -repos create