Tag: osama oracle

ORA-02020: too many database links in use

Posted on by Osama Mustafa in Database
Error :

ORA-02020: too many database links in use

Solution :

Increase the open_links and open_links_instance parameter in the DB . Bounce Database

Or

SQL>alter session close database link “link name”;

Thank you
Osama mustafa

ORA-19527: physical standby redo log must be renamed

Posted on by Osama Mustafa in Database
In Standby Database Alert log i Found the following :

Attempt to start background Managed Standby Recovery process (neonprd)
MRP0 started with pid=31, OS id=5623962
Mon Oct 8 09:12:10 2012
MRP0: Background Managed Standby Recovery process started (neonprd)
Managed Standby Recovery not using Real Time Apply
parallel recovery setup failed: using serial mode
Mon Oct 8 09:12:17 2012
Waiting for all non-current ORLs to be archived…
Mon Oct 8 09:12:17 2012
Errors in file /oracle/admin/neonprd/bdump/neonprd_mrp0_5623962.trc:
ORA-00367: checksum error in log file header
ORA-00318: log 1 of thread 1, expected file size 512 doesn’t match 512
ORA-00312: online log 1 thread 1: ‘/oracle/redolog/neonprd/redo01a.log’
Clearing online redo logfile 1 /oracle/redolog/neonprd/redo01a.log
Clearing online log 1 of thread 1 sequence number 267655
Mon Oct 8 09:12:17 2012
Errors in file /oracle/admin/neonprd/bdump/neonprd_mrp0_5623962.trc:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: ‘/oracle/redolog/neonprd/redo01a.log’
Clearing online redo logfile 1 complete
Media Recovery Waiting for thread 1 sequence 268189
Mon Oct 8 09:12:17 2012

 Solution :

Solution for this Error is so Simple , This Problem Occur when Database parameter “log_file_name_convert” is not set.

Alter system set log_file_name_convert= Scope=Spfile ;

Also You can check :
ORA-19527 reported in Standby Database when starting Managed Recovery [ID 352879.1]

Thank you
Osama Mustafa

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 

	

	

	




	
	

		
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