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

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

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

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

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

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

 

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