Tag: Oracle Blog .

ORA-00313 ,ORA-00312 open failed for members of log group

Posted on by Osama Mustafa in Database
ORA-00313: open failed for members of log group 206 of thread 2
ORA-00312: online log 206 thread 2: ‘/u05/oradata/stdby/’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

 Solution :

1-Mount the database.

SQL>STARTUP MOUNT
Database mounted.

2-Check the status of the logile to see whether it is current.

SELECT STATUS FROM V$LOG WHERE GROUP#=2;
STATUS
—————-
CURRENT

 Note : If the status did not CURRENT then simply drop the log file by:

 

SQL>ALTER DATABASE DROP LOGFILE GROUP 2;

3-Add new Redo Log by :

SQL>ALTER DATABASE ADD LOGFILE GROUP 4 ‘u03/App/Oradata/redo3.log’ SIZE 50M;

4-Do Recover and Open Database Resetlog :


SQL>RECOVER DATABASE UNTIL CANCEL;

SQL>ALTER DATABASE OPEN RESETLOGS; 

Enjoy
Osama Mustafa

LMT Vs ASSM

Posted on by Osama Mustafa in Database
Two methods to manage space :

  • Locally managed tablespace (LMT)—The LMT is implemented by adding the EXTENT MANAGEMENT LOCAL clause to the tablespace definition syntax. Unlike the older dictionary managed tablespaces (DMTs), LMTs automate extent management and keep the Oracle DBA from being able to specify the NEXT storage parameter to govern extent sizes. The only exception to this rule is when NEXT is used with MINEXTENTS at table creation time.

  • Automatic segment space management (ASSM)—The ASSM tablespace is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition syntax. ASSM tablespaces automate freelist management by replacing the traditional one-way linked-list freelists with bitmap freelists, and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes.(method used by Oracle to manage space inside data blocks. It eliminates the need to specify parameters like PCTUSED, Freelists and Freelist groups for objects created in the tablespace. )

both of these space management methods are optional features, and Oracle gurus may still use the more detailed methods should they desire to do so. It is important to note that bitmap segment management in Oracle9i is optional and can only be implemented at the tablespace level. Existing systems may continue to use the traditional method of freelist management.

 Example :

CREATE TABLESPACE ts1 
DATAFILE '/app/orafata/ts1.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL       -- Enable LMT
SEGMENT SPACE MANAGEMENT AUTO -- Enable ASSM

 Some Tips For LMT :

From DMT to LMT: 

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');

Benfits Of LMT : 
1-Dictionary contention is reduced.
2-Space wastage removed.
3-ST enqueue contention reduced.

Limitations of ASSM :

1-Once allocated, the DBA has no control over the storage behavior of individual tables and indexes inside the tablespace.
2-Large objects cannot use ASSM, and separate tablespaces must be created for tables that contain LOB datatypes.
3-You cannot create a temporary tablespace with ASSM. This is because of the transient nature of temporary segments when sorting is performed.
4-Only locally managed tablespaces can use bitmap segment management.
5-There may be performance problems with super high-volume DML (e.g., INSERTs, UPDATES, and DELETEs).

Enjoy
Osama Mustafa

Active Trace Steps

Posted on by Osama Mustafa in Database
There’s More than One Way to active Oracle Trace , This Topic will talk about how to do this ?

Lets Rock N Roll

1-Alter session set sql _trace Statement 


alter session set sql_trace = true;
Or ( The two Statement is equal )

alter session set events ‘10046 trace name context forever, level 1’;

2-DBMS_Session.Set_Sql_Trace 

dbms_session.set_sql_trace (true);
Deactivate

dbms_session.set_sql_trace (false);

 3-DBMS_Support.Start_trace 

dbms_support.start_trace (binds=>{true|false}, waits=>{true|false});

Or to Disable it

dbms_support.stop_trace;

4-Alter Session set event
5-dbms_system.set_ev
6-Oradebug Command
7-DBMS_MONITOR.Session_trace_enable

Ref Link :
1-Oracle-Base 
2-Gplivna 

Enjoy
Osama Mustafa 

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 

	

	

	






	
	

		
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