Wednesday, October 31, 2012

Oracle EBS Patch Tips

What We Mean By Patch ?

Is Program designed to fix some Bugs Or Particular problem .


types of file in patch

cxxxx.drv         the ‘c’ or copy driver
dxxxx.drv         the ‘d’ or ‘database’ driver
gxxxx.drv         the ‘g’ or ‘generate’ driver  -form rep chart
jxxxx.zip         jar file

Applying Patch On EBS ?

-Go to Patch Directory , Unzip .
-inside patch Directory , you find Patch.DRV
-After Run Apps-.evn run adpatch
-answer on the question .
-On Some where in the question , you will be asked to apply patch name which is .DRV


Note : You Need to Enable Maintenance Mode using adadmin .


You will find Topics in Details on my blog talking about how to use adadmin and Adpatch .

Thank you
Osama Mustafa

Oracle Default Users

When you are creating Database , By Default there's Users Created In Installation Phase , Check Them :

SYS
The SYS user owns all base tables and user-accessable view of the data dictionary (Oracle configuration information). No Oracle user should ever alter (update, delete, or insert) any rows or schema objects conatained in the SYS schema, because such activity can compromise data integrety. The security administrator must keep strict control of this central account. 
 
SYSTEM
The SYSTEM user is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools. 
 
SYSMAN
The SYSMAN user represents the Enterprise Manager super admin account. This EM admin can create and modify other EM admin accounts as well as admin the database instance itself.
 
DBSNMP
The DBSNMP user is used by EM to monitor the database. EM uses this account to access performance stats about the database. The DBSNMP credentials sometimes referred to as the monitoring credentials. 
 
 

In addition to these users, a user can connect with diffrent levels of privileges, namely SYSDBA and SYSOPER. When you connect using "connect sys/passwd as sysdba" your connecting as the SYS user and requesting SYSDBA privs. Because the SYS user is the Oracle equivilent to the UNIX root user Oracle makes you specify the amount of control you have, which is why you'll get an error if you try to connect without specifying the privs.
 
The big diffrence between SYSDBA and SYSOPER privs is that SYSDBA can do anything (just like root). The SYSOPER privs allow you just about the same amount of control but won't allow you to look at user data. Both privs allow you to ALTER DATABASE, CREATE SPFILE, STARTUP or SHUTDOWN, ALTER DATABASE ARCHIVELOG, and includes RESTRICTED SESSION privs. However, only SYSDBA can CREATE or DROP DATABASE, and the ALTER DATABASE RECOVER options for SYSOPER are limited to complete recovery only. 
 
 
There's Another Users you can find Them On This Link 
 
 
Thank you 
Osama mustafa

Tuesday, October 30, 2012

Find blocking sessions In Oracle

  You can query the gv$lock and gv$session views to locate a blocking session in RAC.

Miladin Modrakovic offers this script to detect and kill RAC blocking sessions, using  GV$Session and GV$Lock.

 CREATE OR REPLACE PROCEDURE kill_blocker
AS
   sqlstmt   VARCHAR2 (1000);
BEGIN
   FOR x IN (SELECT gvh.SID sessid, gvs.serial# serial,
                    gvh.inst_id instance_id
               FROM gv$lock gvh, gv$lock gvw, gv$session gvs
              WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2
                                             FROM gv$lock
                                            WHERE request = 0
                                           INTERSECT
                                           SELECT id1, id2
                                             FROM gv$lock
                                            WHERE lmode = 0)
                AND gvh.id1 = gvw.id1
                AND gvh.id2 = gvw.id2
                AND gvh.request = 0
                AND gvw.lmode = 0
                AND gvh.SID = gvs.SID
                AND gvh.inst_id = gvs.inst_id)
   LOOP
      sqlstmt :=
            ‘ALTER SYSTEM KILL SESSION ”’
         || x.sessid
         || ‘,’
         || x.serial
         || ‘,@’
         || x.instance_id
         || ””;
      DBMS_OUTPUT.put_line (sqlstmt);

      EXECUTE IMMEDIATE sqlstmt;
   END kill_blovk;
END TEST;
/         
when the script  will generate it will execute Alter session to kill This Session .

Some Useful Link
1-Erik Wramner

Thank you
Osama mustafa
 


 

Sunday, October 28, 2012

Redhat virtualization (KVM)

I am So Proud Today to write this topic , after 14 Hours Of continuous working . I Enabled Virtualization On Redhat , Its was challenging to me ,  I feel for moment its impossible to enable it .

But After finishing KVM installation and see everything is working fine i knew its deserve every moments . I believe in what they say now " Never Give Up " . I posting this article today for the people who wants to learn something new. And if you face any problem you can contact me here, I Wrote this because there's no much information about the package that you should install while you are working on Virtualization But Hope you will find this useful.


you can install the package from Redhat Media , some Package will Generate what called Package Dependency , You will start searching for missing Package on media with same name as appear to you . its waste of time , i will post under this topic the most important package that you need to install to avoid this error. 
Some Other Information you need to know To Check If KVM Support : 

Solution One Using :

/proc/cpuinfo , the output :


The flags to look out for are:
  • vmx — Intel VT-x, basic virtualization
  • svm — AMD SVM, basic virtualization
  • ept — Extended Page Tables, an Intel feature to make emulation of guest page tables faster.
  • vpid — VPID, an Intel feature to make expensive TLB flushes unnecessary when context switching between guests.
  • npt — AMD Nested Page Tables, similar to EPT.
  • tpr_shadow and flexpriority — Intel feature that reduces calls into the hypervisor when accessing the Task Priority Register, which helps when running certain types of SMP guests.
  • vnmi — Intel Virtual NMI feature which helps with certain sorts of interrupt events in guests.
Solution Two :


dmesg | less
 
The two messages to look out for are:

kvm: no hardware support and
kvm: disabled by bios
To load KVM:

Intel
modprobe kvm_intel 
 AMD :
modprobe kvm_amd
 
 


 

Package that you need to install (Sorted) :

 etherboot-zroms-kvm-5.4.4-10.el5.x86_64.rpm
 kmod-kvm-83-105.el5.x86_64.rpm
 celt051-0.5.1.3-0.el5.x86_64.rpm
 log4cpp-1.0-4.el5.x86_64.rpm
 qpixman-0.13.3-4.el5.x86_64.rpm
 qcairo-1.8.7.1-3.el5.x86_64.rpm
 qffmpeg-libs-0.4.9-0.15.20080908.el5.x86_64.rpm
 qspice-libs-0.3.0-39.el5.x86_64.rpm
 kvm-83-105.el5.x86_64.rpm
 cyrus-sasl-md5-2.1.22-5.el5.x86_64.rpm
 iscsi-initiator-utils-6.2.0.871-0.10.el5.x86_64.rpm
 bridge-utils-1.1-2.x86_64.rpm
 kvm-qemu-img-83-105.el5.x86_64.rpm
 gnome-python2-gnomekeyring-2.16.0-3.el5.x86_64.rpm
 gtk-vnc-0.3.8-3.el5.x86_64.rpm
 gtk-vnc-python-0.3.8-3.el5.x86_64.rpm
 xen-libs-3.0.3-94.el5.x86_64.rpm
 xen-devel-3.0.3-94.el5.x86_64.rpm
 libvirt-0.6.3-20.el5.x86_64.rpm
 virt-viewer-0.0.2-3.el5.x86_64.rpm
 libvirt-python-0.6.3-20.el5.x86_64.rpm
 python-virtinst-0.400.3-5.el5.noarch.rpm
 virt-manager-0.6.1-8.el5.x86_64.rpm

Some Other package you need to install for avoiding dependency error : 

xz-lib-***
xz-4.***
If you need to Run  convirt install the below package also (Sorted) :

kernel-xen-2.6.18-164.el5
xen-3.0.3-94.el5
gpg-pubkey-32a349c9-493c185a
socat-1.6.0.1-1.el5.rf
tunctl-1.5-2.el5
python-crypto-2.0.1-13.1.el5.kb.1
python-paramiko-1.7.4-1.el5
convirt-1.1-1.fedora 

 Thank you
Osama Mustafa

Friday, October 26, 2012

ORA-01502: index '%' or partition of such index is in unusable state

To Solve this error you could use more than one solution :

Solution One :

declare
begin
   for index_rec in (select owner, index_name
                     from dba_INDEXES
                     where status = 'UNUSABLE')
   loop                    
      execute immediate 'alter index ' || index_rec.owner || '.' ||
          index_rec.index_name || ' rebuild';
   end loop;
end;

Solution two:

10g and above :
alter system set skip_unusable_indexes=true;
 thank you
Osama mustafa 

Find which User are Running SQL Query

I will post this script here , and you can download it also :

SELECT 
SUBSTR(SS.USERNAME,1,8) USERNAME,
SS.OSUSER "USER",
AR.MODULE || ' @ ' || SS.machine CLIENT,
SS.PROCESS PID,
TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,
AR.DISK_READS DISK_READS,
AR.BUFFER_GETS BUFFER_GETS,
SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,
W.EVENT EVENT,
SS.status,
AR.SQL_fullTEXT SQL
FROM V$SESSION_WAIT W,
V$SQLAREA AR,
V$SESSION SS, 
v$timer T
WHERE SS.SQL_ADDRESS = AR.ADDRESS
AND SS.SQL_HASH_VALUE = AR.HASH_VALUE
AND SS.SID = w.SID (+)
AND ss.STATUS = 'ACTIVE'
AND W.EVENT != 'client message'
ORDER BY  SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC

To Download the Script Press Here


Thank you
Osama Mustafa

check character set in Oracle

Simple Topic just to people who asked before why NLS_CHARACTER SET Not appearing in my V$PARAMETER View :


Just Use two Below Query Depend on your version 

SQL> select value from nls_database_parameters where 
parameter = 'NLS_CHARACTERSET' ;
 
VALUE
----------------------------------------
AR8MSWIN1256
 
 
Check the National Character set :
 
SQL> select value from nls_database_parameters where 
parameter = 'NLS_NCHAR_CHARACTERSET' ; 

 Or you can Use :

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;


Thank you
Osama Mustafa

Tuesday, October 23, 2012

Oracle Performance - Statspack

I mention before in my blog how to Generate AWR Reports But now How to Generate Statpack and how to use it !!!!


enable Statpack :

By default Statpack is disable to enable it do the following

1- Create tablespace with size at least 200MB
2-@?/rdbms/admin/spcreate

Disable it 

@?/rdbms/admin/spdrop
 
Statpack works on snapshot you can do the following with oracle snap shot :
 
Generate Snapshot : 

exec statspack.snap;
 
Or
 
exec statspack.snap(i_snap_level => 10, i_modify_parameter => 'true'); 

Levels Description :


Level 0 - This level captures general statistics, 
including rollback segment, row cache, SGA, system events, background 
events, session events, system statistics, wait statistics, lock 
statistics, and Latch information.

Level 5 - This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels. 

Level 6 - This level includes capturing SQL plan and SQL plan usage 
information for high resource usage SQL Statements, along with all data 
captured by lower levels. 

Level 7 - This level captures segment level statistics, including 
logical and physical reads, row lock, itl and buffer busy waits, along 
with all data captured by lower levels. 
 
Level 10 - This level includes capturing Child Latch statistics, along with all data captured by lower levels. 
 
Delete SnapShot 

@?/rdbms/admin/sppurge;
 
Check SnapShot on Database Level : 

col "Date/Time" format a30
select	snap_id
,       snap_level
,	to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Date/Time"
from	stats$snapshot
,	v$database
order by snap_id
/
 To Scheducle One Hour Generate Snapshot :

@?/rdbms/admin/spauto.sql
 
This script use DBMS_JOB to create new job . you can check them from view dba_jobs
 and to delete job "exec dbms_job.remove();"
 
 

Finally Generate statpack Reports :
 
 @?/rdbms/admin/spreport.sql

Monday, October 22, 2012

AWR Reports Vs ASH Reports

Sometimes when you face performance issue On your database, Usually first thing you are doing is Generate One of the above reports . But What is the benefits of these reports ? What is the difference ? When Can i use them ?


Let Start

I share in my blog earlier how to generate AWR reports . But Today we are talking About another topic .

Automatic Workload Repository Reports/(AWR):

this report appear in 10g Database, I heard that some people said "AWR used Instead Of Statpack" !! NO AWR is higher Version of statpack , statpack still exists but you have to enable it.

The AWR takes a snap shot of the database in specified intervals (default is one hour) and stores in Sysaux tablespace. and you can change this interval using the following :

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
    interval  => 30);          -- Minutes. Current value retained if NULL.
END;
/

The snap shots are taken automatically if the statistics_level parameter is set to typical/all. If it set to basic then statistics details are not gathered,The AWR contains the performance statistics and workload information on the database.

and you can take extra snapshot by :

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 22, 
    high_snap_id => 32);
END;
/


As i mention above you can enable it , disable by the following command :

Enable AWR :
alter system set  statistic_level = {typical | all} scope=spfile ;
Disable AWR :
alter system set   set statistic_level = {basic} scope=spfile
 Check Statistic if its been Gathered By Fire :
Select * from V$statistics_level;
The statistics are collected and stored in memory in SGA. The in memory statistics collection area is a circular buffer, where the old data is overwritten after flushing to disk.


he AWR statistics snap shot are owned by Sys schema. ,The AWR is used to collect performance statistics including:
  • Wait events used to identify performance problems.
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
  • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
  • Object usage statistics.
  • Resource intensive SQL statements.
 
 Active Session History (ASH) :


 The Name of this reports describe what is the major difference between it and AWR , The ASH contains recent information on active sessions sampled every second. The AWR are taken every one hour and its one hour old will not help in diagnosing issues that are current on the database. Typically to resolve issues urgenly on the database, details information pertaining o last 5 to 10 mins is critical. Because recording session activity is expensive, ASH samples V$SESSION every second and records the event for which he session are waiting.




ASH information through V$active_session_history , you can can check this table from Oracle Documentation .

As appear in the Documentation the View contain :
It include

* sql identifier of sql statement.
* object no., file no., and block no.
* wait event identifier & parameters.
* user identifier, Session identifier and Serial number.
* client identifier and name of the operating system program.


Conclusion :

ASH can help you when there's a sudden performance degradation of the database felt.
AWR - historic past snapshot intervals.

AWR, stores the session performance statistics for analysis later.
ASH - the storage is not persistent and as time progresses, the old entries are removed to accommodate new ones. They can be viewed using V$ACTIVE_SESSION_HISTORY.

For More Information also about Baseline in AWR.
Thank You
Osama Mustafa 

Sunday, October 21, 2012

Linux Command For DBA

Enable SSH in Linux: -
#chkconfig --list sshd
#chkconfig --levels 2345 sshd on

to find a file in a directory:-
$ find (directory_path) -name (file_name)

to find a value in a vi editor:-
in vi editor [esc] & :/(find_value)

to delete a value in a vi editor:-
in vi editor [esc] & :%s /delete_value

to delete contents in a file:-
in vi editor [esc] & :/%d

to replace a value in a vi editor:-
in vi editor [esc] & :%s /search_value/replace_value

to see the size of a file (or) directory:-
$du -csh (file or directory name)

to count the files & directories in a directory:-
$ls -l |wc -l

to count the files in a directory:-
$find . -type f |wc -l

to count the directries in a directory:-
$find . -type d |wc -l

to see the file-system in linux:-
$file /sbin/init
(or)
$getconf LONG_BIT

How to mount Windows shared folder in Linux: -
$mount.cifs //IPAddrss/share_folder_name/ /mnt -o user=username


Thank you 
Osama Mustafa

Patchest Number For Database

This is Some useful number for Database patches , you can download them from http://support.oracle.com

9.2.0.4 = 3095277
9.2.0.5 = 3501955
9.2.0.6 = 3948480
9.2.0.7 = 4163445
9.2.0.8 = 4547809(9i Final)
10.1.0.3 = 3761843
10.1.0.4 = 4163362
10.1.0.5 = 4505133
10.2.0.2 = 4547817
10.2.0.3 = 5337014
10.2.0.4 = 6810189

Thank you
Osama Mustafa

Wednesday, October 17, 2012

Oracle Critical Patch Update Advisory - October 2012

A Critical Patch Update (CPU) is a collection of patches for multiple security vulnerabilities. Critical Patch Update patches are usually cumulative but each advisory describes only the security fixes added since the previous Critical Patch Update advisory. Thus, prior Critical Patch Update advisories should be reviewed for information regarding earlier published security fixes.


Follow : Oracle Patch 


Thank you
Osama Mustafa

Tuesday, October 16, 2012

Moving Schema to another Tablespace using "move" command.

Step One: Create New  tablespace

SQL>Create tablespace datafile ‘\osama.dbf’ size 1G;

Step Two : Check User On which Tablespace


SQL> select owner, tablespace_name from dba_tables where owner=''
 
Step three:Create Script 


sqlplus / as sysdba
spool osama.log



SQL> select 'alter table ' || table_name || ' move tablespace mytab ;' from dba_tables where owner='' ;
 
Spool off ;
 
Step Four : Move Index

SQL> select 'Alter index ' || index_name || ' rebuild tablespace mytab ;' from dba_indexes where owner='' ;
 
Step Five : Check Again


SQL> select owner, tablespace_name from dba_tables where owner='';
 
 
Thank you 
Osama mustafa

Monday, October 15, 2012

DBID without Select OR RMAN

You can retive database id without Using v$view this way is useful when you losing your control file or data file and you need to know your DBID :


connect / as sysdba
SQL> startup nomount;
 
 
SQL> alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/system01.dbf' block min 1 block max 2;
 
System altered.
 
tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
Start dump data block from file /home/oracle/app/oracle/oradata/orcl/system01.dbf minblk 1 maxblk 2
 V10 STYLE FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=1229390655=0x4947033f, Db Name='ORCL'
... 
SQL>  alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf'  block min 1 block max 2;
 
System altered.
 
tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
Start dump data block from file /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf minblk 1 maxblk 2
 V10 STYLE FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=1229390655=0x4947033f, Db Name='ORCL'


Thank you
Osama Mustafa

find the time of the last DDL statement that has been applied on a table.

SELECT object_name, object_type, last_ddl_time
  FROM dba_objects (or all_objects)
 WHERE owner = <<owner of table>>
   AND object_name = 'MY_TABLE'


Thank you
Osama mustafa

DDL With the WAIT Option

The DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message


For Example :

CREATE TABLE lock_tab (id  NUMBER);
INSERT INTO lock_tab VALUES (1);
 
ALTER SESSION SET ddl_lock_timeout=30;
 
ALTER TABLE lock_tab ADD (description  VARCHAR2(50)); 

ALTER TABLE lock_tab ADD (
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
 Ref :
Oracle_base


Thank you
Osama mustafa

Saturday, October 13, 2012

Create Backup to another Location




run {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u01/Rman/%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/u02/Rman/%U';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'u02/rman/%F';
backup incremental level 0 database;
release channel disk1;
release channel disk2;

sql 'alter system archive log current';
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u01/Rman/LOG_%t_%s_%p_%U';
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/u02/Rman/LOG_%t_%s_%p_%U';
backup archivelog all DELETE INPUT;
release channel disk1;
release channel disk2;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
}

Thank you
Osama mustafa

Friday, October 12, 2012

manual record to the Alert.log

Oracle Give us an Option to record our own error in alertlog.log But How can we Do that :

The procedure kdswrt in dbms_system package allows us to write own messages in the alert log / trace files or both.It receives two parameters:

  1. -   A number that indicates where do we want to write our message:

  •      Writing to a TRACE file.
  •      Writing to the Alert.log file.
  •      Writing to both of them.
       2.   A text string (the message itself).


How to Use it  :

exec dbms_system.ksdwrt(2, 'ORA-10200: Error in Database.);

 Sun Sep 29 10:00:57 2010
Thread 1 advanced to log sequence 7615 (LGWR switch)
Current log# 1 seq# 7615 mem# 0: u01/app/oracle/product/11.2.0/dbhome_1/REDO01.LOG
Sun Sep 29 11:10:15 2010
ORA-10200: Error in Database.
Sun Sep 29 11:10:15 2010
Thread 1 advanced to log sequence 7616 (LGWR switch)
Current log# 2 seq# 7616 mem# 0: u01/app/oracle/product/11.2.0/dbhome_1/REDO02.LOG
Sun Sep 29 10:00:57 2010
Thread 1 advanced to log sequence 7615 (LGWR switch)
Current log# 1 seq# 7615 mem# 0: u01/app/oracle/product/11.2.0/dbhome_1/REDO01.LOG

 It is a very useful feature, as we can use it in our PL/SQL procedures inside the exception handling section or to indicate the procedure’s progression.

As i mention before my blog you can use X$DBGALERTEXT to check alert log via sqlplus (11g).


Thank you
Osama Mustafa

Wednesday, October 10, 2012

Check Rman Backup/Validate

There's More than One Way to Do it :

Check One :

To check Datavase Backup

RMAN > Restore Validate Database ;
Check Two : 

Check Spfile

RMAN > restore validate spfile to 'c:\temp\spfile.ora';

Check Three :

Test Control File

RMAN> restore validate controlfile to 'c:\temp\control01.ctl';

Check Four :

Test Archive log

RMAN> list backup of archivelog all;
 or
RMAN> list backup of archivelog all completed after 'sysdate -1';

Then
RMAN> restore validate archivelog from sequence XXX until sequence XXX; 


Thank you
Osama Mustafa

Tuesday, October 9, 2012

12c new features

Tom Kyte , Talk about 12c new features LETS START :

1) "With" clause can define PL/SQL functions

2) Improved defaults, including Default col to a sequence or "default if (on) null".  Or always use a generated as an identity (with optional sequence def info).  Or Metadata-only defaults (default on an added column). 

3) Bigger varchar2, nvarchar2, raw -up to 32K.  But follows rules like LOB, if over 4K will be out of line. (max_SQL_String_Size init param)

4) TopN and Pagenation queries using the 'OFFSET' clause + optional 'FETCH next N rows' in SELECT.  Eg: SELECT ... FROM t ORDER BY y FETCH FIRST 5 ROWS

5) Row pattern matching using the "MATCH_RECOGNIZE" clause.  Gonna take a while to get this one.

6) Partitioning improvements including ASYNC Global Index maintenance (includes new jobs to do work 'later'), cascade truncate & exchange, multi ops in a single DDL, online partition moves (no RDBMS_REDEFINITION), "interval + reference" partitioning.

7) Adaptive execution plans, which sets thresholds and allows execution plans to switch if threshold is exceeded.  (Also 'gather_plan_statistics' hint.)  Shown by 'Statistics Collector' steps in trace/tkprof.

8) Enhanced statistics. Dynamic sampling goes to 'eleven', turning it persistent.  New histograms: hybrid (for more than 254 distinct values, instead of height-balanced) and top.  Stats gathered on data loads automatically. (By the way, don't regather stats if not needed.)  Session private statistics for GTTs. 

9) UNDO for temporary objects, managed in TEMP, which eliminates REDO on the permanent UNDO. (ALTER SESSION/SYSTEM SET TEMP_UNDO_ENABLED=TRUE/FALSE)

10) Data optimization, or Information Lifecycle Management, which detects block use - hot, medium, dormant - and allows policies in table defintion (new ILM clause) to compress or archive data after time.

11) "transaction Guard' to preserve commit state, which includes TAF r/w transfer and restart for some types of transactions.

12) pluggable databases!  Implications too numerous to list right now.  Suffice it to say, huge resource improvements, huge consolidation possibilities.  Looking forward to reality.


Thank you
Osama mustafa

emagent : Memory 0x0 encountered

When you try to login to EM the following screen appear :











-bash-3.00$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.7.0
Copyright (c) 1996, 2008 Oracle Corporation.  All rights reserved.

https://rgpdb1.rg.com:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.
-bash-3.00$
-bash-3.00$
-bash-3.00$
-bash-3.00$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.7.0
Copyright (c) 1996, 2008 Oracle Corporation.  All rights reserved.

https://rgpdb1.rg.com:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ............................................................................................. failed.
------------------------------------------------------------------
Logs are generated in directory /pdb01/oraprod/db/tech_st/11.1.0/rgpdb1_rgprd1/sysman/log
 
 
Solution:

Check the following processes and kill them :

ps -ef | grep emagent
ps -ef | grep DEMS

-bash-3.00$ kill -9 PID
-bash-3.00$ kill -9 PID
 Then

-bash-3.00$ emctl stop dbconsole
-bash-3.00$ emctl start dbconsole 

 
 Thank you 
Osama Mustafa 



 

Oracle RAC 12c: New Features

1. Application Continuity

2. Oracle Flex ASM

With this feature, database instances use remote ASM instances. 


3. Oracle ASM Disk Scrubbing

Checks for logical data corruptions and repair them automatically.


4. Enhancements to Policy-based Databases

Actively utilizes different sized servers


5. What - if analysis for server pool management


6. Standardized deployment and patching 

Introducing GHS, rapid home provisioning and gold images


7. A new "ghctl" command for better patching


8. Oracle Utility Cluster


9. Dynamic IP Management and name resolution made easy


10. IPv6 Based IP Addresses Support for client connectivity


11. Multi-purpose Installation


12. Oracle installer will run Fix-up scripts & "root.sh" scripts across nodes. You don't have to run the scripts manually on RAC nodes.




Thank you Asif Momen .


Thank you 
Osama Mustafa 

Monday, October 8, 2012

ORA-02020: too many database links in use

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

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



Friday, October 5, 2012

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

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

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



Thursday, October 4, 2012

Active Trace Steps

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 

Tuesday, October 2, 2012

Oracle OpenWorld , Amazing World














Enjoy 
Osama Mustafa

Temporary tablespace group

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