Tag: Osama

Temporary tablespace group

Posted on by Osama Mustafa in Database
What We Mean By That ?

A temporary tablespace group consists of only temporary tablespace, and has the following properties:

      • It contains one or more temporary tablespaces.
      • It contains only temporary tablespace.
      • It is not explicitly created. It is created implicitly when the first temporary tablespace is assigned to it, and is deleted when the last temporary tablespace is removed from the group. 

Benefits :

Temporary tablespace group has the following benefits:

      • It allows multiple default temporary tablespaces to be specified at the database level.
      • It allows the user to use multiple temporary tablespaces in different sessions at the same time.
      • It allows a single SQL operation to use multiple temporary tablespaces for sorting. 

How to Know How Much Group you have And Each temp assign to them ?  

select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;

 How to Create One :

CREATE TEMPORARY TABLESPACE temp01
     TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G
     TABLESPACE GROUP groupaA_temp;
 
Note : you can create temp tablespace without assign to any group .  


Example 

     CREATE TEMPORARY TABLESPACE temp04
     TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G
     TABLESPACE GROUP ‘’;

     CREATE TEMPORARY TABLESPACE temp04
     TEMPFILE ‘/u01/app/oracle/oradata/temp01.dbf’ SIZE 1G;
 
Using Alter with them 

Remove from the group : 




 ALTER TABLESPACE temp01 TABLESPACE GROUP ‘‘;




Assign to Group : 




ALTER TABLESPACE temp03 TABLESPACE GROUP tempgroup_b;




Define Group for specific user :


     ALTER USER Osama TEMPORARY TABLESPACE tempgroup_A;


 Refecnce Link :
1-ORACLE BASE
2-DBA Kevlar 


Enjoy
Osama Mustafa

	

	

	




	
	

		
Checkpoint Not Complete
				

					Posted on  by Osama Mustafa in Database				


				

	

		
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
				

					Posted on  by Osama Mustafa in Database				


				

	

		
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
				

					Posted on  by Osama Mustafa in Database				


				

	

		
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
				

					Posted on  by Osama Mustafa in Database				


				

	

		
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
				

					Posted on  by Osama Mustafa in Database				


				

	

		
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
				

					Posted on  by Osama Mustafa in Database				


				

	

		
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
				

					Posted on  by Osama Mustafa in Database				


				

	

		
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