Friday, December 28, 2012

Oracle Physical Standby Modes



Regarding to Oracle Documents That Describe Standby Modes, There's Are Three Types you can follow the documentation.



Check Standby Modes :

SQL> select PROTECTION_MODE from v$database ;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

Primary Database: prim
Standby Database: stdby 


Note: the below Commands Should Apply on Primary Database on Mount Mode Then Open database.


Convert Between Modes (Switch Modes)


MAXIMIZE AVAILABILITY 


SQL> alter system set log_archive_dest_2='SERVICE=stdby AFFIRM SYNC VALID_FOR=(online_logfiles,primary_role) db_unique_name=stdby';
System altered.

 SQL> alter database set standby Database to MAXIMIZE AVAILABILITY ;
Database altered.

MAXIMIZE PERFORMANCE


 SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby';
 System Altered  

SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
Database Altered

MAXIMIZE Proctection


SQL> alter system set log_archive_dest_2='SERVICE=stdby AFFIRM SYNC VALID_FOR=(online_logfiles,primary_role) db_unique_name=stdby';
System altered.

shutdown immediate ; 
Startup mount ;
Alter database set standby database to maximize Protection ;
Alter database open ; 


Some Useful Sites:
1-Convert Modes Using dgmgrl


Thank you

Osama Mustafa





Thursday, December 27, 2012

ORA-01210: data file header is media corrupt

ORA-00283: recovery session canceled due to errors
ORA-01110: data file 24: '/ora/data/gamesds/system02.DBF'
ORA-01122: database file 24 failed verification check
ORA-01110: data file 24: '/ora/data/gamesds/system02.DBF'
ORA-01210: data file header is media corrupt



Error Cause:
The file header block is internally inconsistent. The beginning of the block has a header with a checksum and other data for insuring the consistancy of the block. It is possible that the last disk write did not operate correctly. The most likely problem is that this is not a datafile for any database. 
 
 
Solution :

Have operating system make correct file available to database. If the trace file dump indicates that only the checksum is wrong, restore from a backup and do media recovery.

 Or The Below Solution , You Must Be Sure that you have Valid Backup to restore it .

SQL> shutdown immediate
SQL > Startup mount 
SQL> ALTER DATABASE DATAFILE '/ora/data/gamesds/system02.DBF' OFFLINE ;
SQL > ALTER database Open ;

Thank you
Osama Mustafa

Monday, December 24, 2012

DBMS_METADATA Is Missing During expdp

The Error Appear During The expdp Database , And Looks

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.CONFIGURE_METADATA_UNLOAD []
ORA-04067: not executed, package body "SYS.DBMS_METADATA" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_METADATA"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 62
ORA-06512: at "SYS.KUPW$WORKER", line 6226

Cause:

 SYS.DBMS_METADATA Is Missing .



Solution :

Check if the above object exists

SELECT owner, object_name, object_type, status FROM dba_objects WHERE  object_name='DBMS_METADATA';
Not Found :

$ORACLE_HOME/rdbms/admin/prvtmeta.plb
$ORACLE_HOME/rdbms/admin/utlrp.sql

Friday, December 21, 2012

Startup/Shutdown Logical Standby Database

Simple Steps to do that


Shutdown Steps :

On Primary do the following :

SQL > alter system switch logfile ;
SQL > alter system archive log current ; 

Go to Standby :

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> shutdown immediate;
Primary Database :

SQL> shutdown immediate;

Startup Steps :

Primary Database

SQL>startup;
 Standby Database:

SQL > startup
SQL > alter database start logical standby apply immediate ;

Thank you
Osama Mustafa

Bulid Logical Database

I Post Earlier What is the Different Between Logical Database and Standby Database , Both Are Type For Data Guard .


Today i will take about how to configure Logical Database , to do this you must first create Physical Standby Which i mention it before , And you can Follow the link .

These steps for 10g,  Now After create physical Standby Database , And Make sure its working Without any problem , You Have to follow the below steps to Create Oracle Logical Standby.


Steps to convert Standby Database to Logical Standby Database :

Step #1 : 

in this step logminer Dictionary Must be Built into redo data. ( Build LogMiner Tables for Dictionary)

On Primary

SQL> begin
  2  dbms_logstdby.build;
  3  end ;
  4  /


OR
 SQL> execute dbms_logstdby.build;

Step #2:

Now we have stop Managed recovery between primary and standby :

On Standby :

SQL> alter database recover managed standby database cancel ;

Database altered.

Step #3 :

Convert Physical Standby to Logical Standby .

On Standby :


SQL> alter database recover to logical standby stdby ;

Database altered.

Notices : If this command hang and take to much Time then Back to step #1 and did it again .

Step #4:

On Standby ( New Logical)  we open it in Resetlog mode .


On New Logical ( Standby) :

SQL> shutdown immediate ;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup mount ;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             532678648 bytes
Database Buffers          301989888 bytes
Redo Buffers                2396160 bytes
Database mounted.

SQL>
SQL>
SQL> alter database open resetlogs ;

Database altered.

Step #5:

Start Apply Service On logical


SQL> alter database start logical standby apply ;

Database altered.

OR


SQL> alter database start logical standby apply immediate ;
Database altered.

Just As Check if everything Goes Fine :

SQL> select database_role , Name from v$database ;

DATABASE_ROLE    NAME
---------------- ---------
LOGICAL STANDBY  STDBY

Thank you
Osama Mustafa

Wednesday, December 19, 2012

ora-00845 memory_target not supported on this system

The Error Will be Appear like the following :
SQL> startup nomount;
ORA-00845: MEMORY_TARGET not supported on this system
 Cause :

This error comes up because you tried to use the Automatic Memory Management (AMM) feature of Oracle 11g R2. Well done, but it seems that your shared memory filesystem (shmfs) is not big enough. So let’s look at the steps necessary to enlarge your shared memory filesystem to avoid the error above.

Solution

mount -t tmpfs shmfs -o size= /dev/shm

Thank you
Osama Mustafa

Tuesday, December 18, 2012

Logical Standby Vs Physical Standby

What is the difference between these two Data Guard configuration , When To Use them :

Physical Standby:

1- Physical Standby Database Its exactly same As Primary Database.
2-  In Physical Data Guard The archivelog Applied directly after transfer from primary database (FTP)

Properties of Physical Standby

1- Maintain Is Easy.
2- Creation is Easy .
3- Copy Of your Primary Database ( Disaster Recovery Solution ).

Usage :

High availability solutions Or disaster recovery Solution.

Logical Standby

1 - Opposite Of standby Database , Which is not Match primary Database .
2 - This Kind Of Configuration can be Opened in Read Only Mode .
3 - can have additional materialized views and indexes added for faster performance
4 - LogMiner Techniques to transfer Archivelog.

Properties Of Logical Database :

1 - Open In Read only Mode .
2 - Sometimes its used as RollBack Solution In Upgrade ,

Usage:

1 - reporting Database to avoid overhead in primary database.
2 - Query Database .


How they Works :

Regarding to Oracle documentation  :

LNS (log-write network-server) and ARCH (archiver) processes running on the primary database select archived redo logs and send them to the standby database, where the RFS (remote file server) background process within the Oracle instance performs the task of receiving archived redo-logs originating from the primary database.

Alternatively, a supplementary mechanism may transfer the archived redo logs. On the standby database a Fetch Archive Log (FAL) client monitors for gaps in the sequence of received logs. If it finds a gap, it may invoke one or more Fetch Archive Log (FAL) servers to run on the primary database to forward the missing item(s).

Once the archived redo logs have arrived, other processes (such as an ARCH (Archiver process), an MRP (Managed Recovery Process), and/or an LSP (Logical Standby Process)) may set about applying the log contents to the standby database.

 Conclusion :

The difference between physical and logical standby is in the way the changes from the primary are applied. Both created as an exact image of the primary database. Both receive redo logs from the primary database.

 Refernce :
 Oracle DataGuard architecture 


Thank you
Osama mustafa

Friday, December 14, 2012

Some Command To Deal With Standby Database

Open Standby In Read Only :

    SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
    SQL> ALTER DATABASE OPEN READ ONLY;

Back to Mount Point ( Same as Before Open in Read Only ) :

    SQL > shutdown;
    SQL > startup nomount
    SQL > alter database mount standby database
    SQL > alter database recover managed standby database disconnect from session;

Switch Over Primary As Standby , And Standby As Primary

Primary Database : Prim
Standby Database : Stdby

On Prim :

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
SQL> SHUTDOWN NORMAL;
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;


On standby:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> SHUTDOWN;
SQL> STARTUP;

On Prim :

    SQL> ALTER DATABASE RECOVDR MANAGED STANDBY DATABASE;



On standby:

SQL> ALTER SYSTEM ARCHIVE LOG START;
SQL> ALTER SYSTEM SWITCH LOGFILE





Thank you
Osama Mustafa

Step By Step to Create Phyiscal Standby Dastabase 10g

I Post Earlier Some Topics About Dealing with Data Guard , Today i will start Mention Steps How To Create Data Guard Standby Database Manually . Its Long East Steps .Just Follow Them :



primary Information :
Instance Name : prim
Hostname : PrimDB10g

Standby Information :
Instance Name : Stdby
Hostname : StdbyDB10g

Some Note :
1-On Standby Hostname Just install Database Software without Create Any Database.
2-Primary Database and Standby Database should be able to ping .
3-Primary and Standby Should be The Same .


Step #1 : On primary Database 

-Primary Database Should be in Archive log mode.


SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Current log sequence           1

SQL > Shutdown immediate ;
SQL > Startup mount ;
SQL > Alter database archivelog ;
SQL > Alter database open ;


- Enable Force Logging to make sure  takes precedence and all operations are logged
  into the redo logs.

  SQL> ALTER DATABASE FORCE LOGGING;

-Create Password Since password file should be the same on Both , you can create it on Primary Database and Copy to Standby with Change name .

[oracle@PrimDB10g]$ orapwd file=@ORACLE_HOME/db/orapwprim password=oracle force=y
Step #2 :Adding Standby Logfile .

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4  '/u01/app/oracle/oradata/prim/stby04.log' size 50m;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5   '/u01/app/oracle/oradata/prim/stby04.log' size 50m;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5   '/u01/app/oracle/oradata/white/stby04.log' size 50m;

 Step #3:

On Primary Database Should Create Pfile , to modify it and add parameters .

SQL> CREATE PFILE FROM SPFILE;
OR
SQL> CREATE PFILE='/initSID' from spfile;

prim.__db_cache_size=121634816
prim.__java_pool_size=41943040
prim.__large_pool_size=4194304
prim.__shared_pool_size=109051904
prim.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/prim/adump'
*.background_dump_dest='/u01/app/oracle/admin/prim/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/prim/control01.ctl','/u01/app/oracle/oradata/prim/control02.ctl','/u01/app/oracle/oradata/prim/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/prim/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prim'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=283115520
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/prim/udump'
db_unique_name='prim'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stdby)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/prim/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'
LOG_ARCHIVE_DEST_2='SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=stdby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=stdby
FAL_CLIENT=prim
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/stdby/','/u01/app/oracle/oradata/prim/'
log_file_name_convert='/u01/app/oracle/oradata/stdby/','/u01/app/oracle/oradata/prim/'



Once you Create and modify pfile you should startup database with it .

SQL > Shutdown immediate 
SQL > Startup nomount pfile='/u01/initPrim.ora';
SQL > Alter database mount ;
SQL > alter database open ;
SQL > Create Spfile from pfile = '/u01/initPrim.ora';

Bounce Database again .



Step#4 : Backup Primary Database Using RMAN 



RMAN> backup full database format '/u01/rman/%d_%U.bkp' plus archivelog format '/u01/rman/%d_%U.bkp';

Next, create a standby controlfile backup via RMAN:
RMAN> configure channel device type disk format '/u01/rman/%U';

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

Move The backupset to Standby Using SCP

scp -r oracle@Primary-Database-ip :/u01/rman oracle@standby-database-ip:/u01/
The primary and standby database location for backup folder must be same.

Steps#5 :  Start Working On Standby Database .

 you should Create standby folders , to make sure No error will be appear .

mkdir -p /u01/app/oracle/oradata/stdby
mkdir -p /u01/app/oracle/oradata/stdby/arch
mkdir -p /u01/app/oracle/admin/stdby
mkdir -p /u01/app/oracle/admin/stdby/adump
mkdir -p /u01/app/oracle/admin/stdby/bdump
mkdir -p /u01/app/oracle/admin/stdby/udump
mkdir -p /u01/app/oracle/flash_recovery_area/PRIM
mkdir -p /u01/app/oracle/flash_recovery_area/PRIM/onlinelog


Step#6: Parameter file for Standby

stdby.__db_cache_size=121634816
stdby.__java_pool_size=41943040
stdby.__large_pool_size=4194304
stdby.__shared_pool_size=109051904
stdby.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stdby/adump'
*.background_dump_dest='/u01/app/oracle/admin/stdby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/stdby/control01.ctl','/u01/app/oracle/oradata/stdby/control02.ctl','/u01/app/oracle/oradata/stdby/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/stdby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prim'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stdbyXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=283115520
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/stdby/udump'
db_unique_name='stdby'
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prim,stdby)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/stdby/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
LOG_ARCHIVE_DEST_2='SERVICE=prim LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=prim'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER='prim'
FAL_CLIENT='stdby'
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/prim/','/u01/app/oracle/oradata/stdby/'
log_file_name_convert='/u01/app/oracle/oradata/prim/','/u01/app/oracle/oradata/stdby/'



Step#7:Configure Listener and tnsnames.ora On Both :

On Primary Database :

tnsnames.ora


STDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stdby)
    )
  )
PRIM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prim)
    )
  )



Same On Standby , On Standby Databsase Use netca to create listener . and make sure its up
test the connection between both nodes via tnsping m and its should be able to Connect .

Step#8:

on Standby Database

echo "stdby:/u01/app/oracle/product/10.2.0/db_1:N" >> /etc/oratab

export ORACLE_SID=stdby
sqlplus / as sysdba
SQL > startup nomount pfile='/u01/initSTDBY.ora';
 SQL > Create Spfile from pfile = '/u01/initSTDBY.ora';

Bounce Database.
SQL > Startup nomount ;
SQL > exit;

export ORACLE_SID=stdby ;
rman target=sys/oracle@prim auxiliary=/

Connection should be like the following :
connected to target database: PRIM (DBID=3603586489) --Prim
connected to auxiliary database: PRIM (not mounted) --Standby

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;


Now We Done Creating Standby Database ,  we will Start Sync :

SQL> alter database recover managed standby database disconnect from session;

 You can Check If everything is up to date with standby , use archive log list on both node to check archive log number .


Thank you
Osama Mustafa

Bounce Standby Database

Shutdown Standby Database :


    sqlplus /as sysdba
    alter database recover managed standby database cancel;
    shutdown;
 
 

Startup Standby Database :


     sqlplus /as sysdba
     startup nomount
     alter database mount standby database
     alter database recover managed standby database disconnect from session;


Thank you
Osama Mustafa

Thursday, December 13, 2012

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed



Cause:
Missing Space Issue

Solution 
log_archive_dest_1 = 'LOCATION=/u01/app/oracle/prim/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=prim'

Should be 

log_archive_dest_1 = 'LOCATION=/u01/app/oracle/prim/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'




Thank you
Osama Mustafa

Wednesday, December 12, 2012

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

SQL> shutdown immediate ;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount ;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2095704 bytes
Variable Size             155190696 bytes
Database Buffers          121634816 bytes
Redo Buffers                6291456 bytes
Database mounted.
SQL> alter database archivelog ;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> shutdown immediate ;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2095704 bytes
Variable Size             155190696 bytes
Database Buffers          121634816 bytes
Redo Buffers                6291456 bytes
Database mounted.
Database opened.
SQL>

SQL> SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount ;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  2095704 bytes
Variable Size             155190696 bytes
Database Buffers          121634816 bytes
Redo Buffers                6291456 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

Sunday, December 9, 2012

Check FRA Size

The Below query To check FRA Used Space and Free Space its very Useful Query :

set lines 100
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used  / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/ 


Thank you
Osama mustafa

Friday, December 7, 2012

Check Database Features

Small topics but Useful to know which Feature you have on your database Using View :

9i ---> V$OPTION
10g and above --->  DBA_FEATURE_USAGE_STATISTICS


 Thank you
Osama Mustafa

AWR vs ADDM vs ASH

AWR : automatic workload repository

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.
I will not get into Details how to generate AWR since i mention it before on my Blog .


 ADDM : automatic database diagnostic monitor


analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks.and we use it for the following cases :

  •      CPU bottlenecks
  •      Undersized memory structures
  •      I/O capacity issues
  •      High load SQL statements
  •      RAC specific issues
  •      Database configuration issues
  •      Also provides recommendations on hardware changes, database configuration & schema changes.
Generate ADDM  :
  • Login to SQL
  • @$ORACLE_HOME/rdbms/admin/addmrpt.sql
  •  enter system password when you asked for .
  • Specify a begin_snap from the list and press Enter.
  • Specify the end_snap from the list and press Enter.
  •  Report Name
ASH : Active Session History 

statistics from the in-memory performance monitoring tables also used to track session activity and simplify performance tuning.

ASH reports Give the following information :

  • Top User Events (frequent wait events)
  • Details to the wait events
  • Top Queries
  • Top Sessions
  • Top Blocking Sessions
  • Top DB Object.
  • Activity Over Time
 Generate ASH reports :

The Best way to do that using OEM.  (Enterprise manager).

Thank you
Osama Mustafa

Monday, December 3, 2012

Encrypt Your PL/SQL Code Using Wrap Command

Today I will make demonstration how to hide your code , Function , Procedure and anything Related to PL/SQL Code , to do this you have to know wrap utility in oracle .

Step #1:

You Have to Write Sample Code to Make test on it .

Sample Code :

SQL> CREATE OR REPLACE PROCEDURE testproc
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('Wrap Me!');
 END;
/
Procedure created.

Ensure Procedure Run Successfully :

SQL> exec testproc

PL/SQL procedure successfully completed.

The above Steps Just to make sure our Procedure will run suceesfully without any error , now i will delete it again and start using wrap to encrypt.

SQL> conn osama/osama;
Connected.
SQL>
SQL> drop procedure testproc ;

Procedure dropped.
Step #2:

Using Wrap to Create plb File .

-Save Above Procedure in File Called TestProc.sql under /home/oracle .

[oracle@localhost ~]$ wrap iname=Testproc.sql

PL/SQL Wrapper: Release 10.2.0.5.0- Production on Mon Dec 03 22:42:14 2012

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing Testproc.sql to Testproc.plb
 OR

If i want to get my procedure script wrapped and no one can read my file :

[oracle@localhost ~]$ wrap iname=Testproc.sql oname=wrapped.sql

PL/SQL Wrapper: Release 10.2.0.5.0- Production on Mon Dec 03 22:46:12 2012

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing Testproc.sql to wrapped.sql

Open Wrapped.sql 

[oracle@localhost ~]$ more wrapped.sql
CREATE OR REPLACE PROCEDURE testproc wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
49 85
SOwY8JEJe1MROnuBTksxRTO9iJ4wg5nnm7+fMr2ywFwWoWLRPhaXroubdIvAwDL+0oYJjwlp
uFKbskr+KLK957KzHQYwLK4k6rKBpVHb4USaw+kyLvYOxeokH/Y5pkT0tnU=

/
 
Step #3:

Now I want to create procedure in my database :

[oracle@localhost ~]$ sqlplus osama/osama

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Dec 3 22:48:03 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @Testproc.plb

Procedure created.

SQL>
SQL>
SQL> exec Testproc

PL/SQL procedure successfully completed.




 


Sunday, December 2, 2012

ORA-01438 Which Column

ORA-01438: value larger than specified precision allowed for this column

You will receive this error while trying to insert big Value In Column with Specific Range , But How Could You know Which Column is it i will some hint that could help you to do that :

Hint #1 :

1-ORA-01438  : For Numeric Value
2-ORA-12899  : For Varchar2 Value

Hint #2 :

Enable Audit On know which One of these Column caused the error

SQL > Create table test as select * from scott.dept
SQL> desc dept
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 DEPTNO                              NOT NULL NUMBER(2)
 DNAME                                        VARCHAR2(14)
 LOC                                          VARCHAR2(13)
 
SQL> audit insert on Osama.test whenever not successful;
 
Audit succeeded.
 
SQL> insert into osama.test values(2000,'Osama','Osama');
insert into scott.dept values(2000,'osama','Osama')
                              *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this
column
 
 
SQL> select sql_text,returncode from dba_audit_trail
  2  where owner='OSAMA' and obj_name='TEST';
 
SQL_TEXT
----------------------------------------------------------------------
RETURNCODE
----------
insert into scott.dept values(2000,'Osama','Osama')
      1438
 Hint #3 :

Enable Tracing Level 1438


SQL > conn osama/osama ;
SQL> create table test as select * from scott.dept ;
 
SQL> select * from test ;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
 
 
SQL> alter system set events='1438 trace name Errorstack forever,level 10';
 
 
SQL> insert into test values (100000000000000000,'osama','JOR');                         
insert into test values (100000000000000000,'osama','JOR')
                         *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
 
In Trace File :
 
ORA-01438: value larger than specified precision allowed for this column
Current SQL statement for this session:
insert into test values (100000000000000000,'osama','JOR')


Thank you
Osama Mustafa

Spool File With Date/Time Name

Amazing way to spool  automatically :

SET TERMOUT OFF
COLUMN today_ddmmyyyy_col NEW_VALUE today_ddmmyyyy
SELECT TO_CHAR ( SYSDATE, 'DDMMYYYY') AS today_ddmmyyyy_col
FROM    dual;
SET TERMOUT ON
SPOOL log_&today_ddmmyyyy..log

Thank you
Osama Mustafa

Wednesday, November 28, 2012

Reclaim Space in Tablespace using Shrink Command

SQL> ALTER TABLE TABLE_NAME enable ROW movement;

SQL> ALTER TABLE TABLE_NAME shrink SPACE;

SQL> ALTER TABLE TABLE_NAME disable ROW movement;

Benefits regarding to Oracle Documentation :

  • Full table scans will take less time (a table scan will always scan up to the HWM even if space is not used)
  • Better index access takes place because of a smaller B-Tree
  • Space is freed up for other database objects
  • Space below the HWM is released and the HWM is moved down

 Thank you
Osama Mustafa

Tuesday, November 27, 2012

OPEN_CURSOR in Oracle

Open_Cursor Parameter , in this article i will discuss what this parameter do  , I used Oracle Documentation to describe this parameter but in simpler way .

In Oracle Documentation

Open_cursor : specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.

to check in database

SQL> show parameter Open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300
 
As we see in the last Line , "its prevent Session from opening excessive number of cursor"  how is that !!!

Open_cursor located in shared_pool which is part of SGA ( library Cache) , The benefit of this parameter is to prevent Session clogging CPU with requests .

But what i mean when I set this Parameter to integer , let take the above example :

-Parameter is set to 300
-That mean each session can have  300 cursors

If this session fill the 300 What will happened !!!
 "ora-1000 maximum open cursors exceeded"
On Documentation Oracle Recommended to set this Parameter for high value  for Application usage , if the above error raised and you already set to high Value then make sure your developer is closing their cursor and this is common issue.

SQL> Show parameter cursor

NAME                                      TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                             string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                               integer     300
session_cached_cursors               integer     20

But what others Parameter mean,  what each parameter mean lets ask this question what if User or session run query include the same cursor , is it take another space in memory !!!

it dose not make sense , so in this case we use SESSION_CACHED_CURSOR.

regarding to oracle documentation

SESSION_CACHED_CURSOR : its Number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache.  

you have to know some truth about this parameter :
1) its cache only close cursor which mean if you cursor still opened how could it be cached !!!

2) you can set this parameter > OPEN_CURSOR , < OPEN_CURSOR Or equal OPEN_CUROSR , that indicate us between relation between this parameter ----> NOTHING.

3) if you are not setting this parameter ( Value 0 ) then no cursor will be cached for session . But Wait we said oracle used shared pool in cursor Yes , your cursor will be in SHARED_POOL but session have to find this cursor , so if you set to non zero first thing oracle will do is check SHARED_POOL ( Library cache ) and back to you with result .


After discuss point number 3 we see the main advantage for cache cursor which is better performance , enhance query execution time.

But the shared_pool has limited size so i can't cache all my cursor , now next parameter will avoid this

CURSOR_SPACE_FOR_TIME 

Boolean value (TURE|FALSE) each one of them mean something

FALSE : Shared SQL areas can be deallocated from the library cache to make room for new SQL statements.

TRUE : Shared SQL areas are kept pinned in the shared pool , if you are using application contain lot of cursor it will be good to set it to enhance execution time ( to do that shared pool must be large enough to hold all open cursors simultaneously) .


 if i have 3 query each one want to use same cursor how oracle handle this , Parameter number 4 will control that.

CURSOR_SHARING

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

this parameter take three value ( FORCE | SIMILAR | EXACT ) i will describe each one of them , Oracle Documentation definition :  

-Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

-Similar Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

-Exact Only allows statements with identical text to share the same cursor.

you can't imagine how many document you will find if you search on google for this parameter , examples , article and tuning for it.

to set this parameter you have to make sure what you need , understand it.the best way to do that with trying to test it.


Thank you
Osama Mustafa



Saturday, November 24, 2012

Change Listener Port (Single Instance)

In this Topic i will discuss how to change listener ports , The Database 10gR2 , Operating System RHEL 5.7  this demonstration will work on any Database or platforms , The Simplest way to change listener Ports


Information :
DB Version : 10.2.0.5
OS : RHEL 5.7
Listener Name : LISTENER
Old Port : 1521
New Port : 1523


Step one :  Check Listener Status .

The Below screen describe The Old Status For Listener Notice the Port is 1521 (Default One)


Step two : Stop Listener  (lsnrctl stop)


Step three : Use netca command  to Change Listener Port Follow the screens




in below screen you can choose the listener port you want to change .



Step Four : After Close netca GUI listener will start automatically






Step Five : Go to $ORACLE_HOME to change tnsnames.ora that used Old Listener port to new port.





Listener.ora will change automatically via netca GUI so no need to update it .

Step Six : Database will not register atomically after doing the above steps , so you have to set LOCAL_LISTENER Parameter Via Sqlplus like the following , i will post lsrnctl status to confirm what i saying, also notice new port that has been changed .


Now , Sqlplus / as sysdba




I Used Scope=memory to test the connection first  , after test the connection successfully you can change scope = spfile.

Step Seven : Test Connection to make sure Every User will Able To connect .





Thank you
Osama Mustafa

Thursday, November 22, 2012

What Oracle Version Digit Mean

The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.
Database Maintenance Release Number

The second digit represents a maintenance release level. Some new features may also be included.
Application Server Release Number

The third digit reflects the release level of the Oracle Application Server (OracleAS).
Component-Specific Release Number

The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.
Platform-Specific Release Number

The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.


Thank you
Osama Mustafa

Wednesday, November 21, 2012

ora-12705 cannot access nls data files or invalid environment specified

This error appear On Sql developer .

ORA-00604: error occurred at recursive SQL level 1
ORA-12705: Cannot access NLS data files or invalid environment specified


Solution :

Try to add following lines to %SQL_DEV_HOME%\sqldeveloper\bin\sqldeveloper.conf
AddVMOption -Duser.language=en
AddVMOption -Duser.country=US


us depend on your language .
en :  England and so on ....




Thank you
Osama Mustafa

BLOB to CLOB

i took this procedure from OTN forum  to convert BLOB to CLOB


CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP

v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));

DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;
RETURN v_clob;
END blob_to_clob;
/
 Example :
Select blob_to_clob(blob_column) from table_name;

Monday, November 19, 2012

Check DataGaurd Role Primary/Standby

Which Database is the primary Database , Which One Is the Standby

Simple Query Will Answer this , The Scenario Like the following I have Data Guard I want to check which one Of these database are Primary Or Standby How Can I do that :

SQL > select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

The above Output Indicate that you are Now On Primary Database, Different Output for Standby

SQL > select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

There's More than One Way
SQL> SELECT controlfile_type FROM V$database;

Output On Primary Database :

CONTROL
——–
CURRENT

On Standby :

CONTROL
——–
STANDBY

Thank you
Osama Mustafa

WARNING: Subscription for node down event still pending

Warning Appear like the following : 
 
[oracle@sun3 ~]$ cat /u01/app/oracle/product/10.2.0/network/log/listener1.log
 
19-NOV-2012 14:00:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl1)(CID=(PROGRAM=sqlplus@sun3)(HOST=sun3)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.4)(PORT=53680)) * establish * orcl1 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
19-NOV-2012 14:01:02 * ping * 0
19-NOV-2012 14:08:34 * service_update * orcl1 * 0
19-NOV-2012 14:18:37 * service_update * orcl1 * 0
WARNING: Subscription for node down event still pending
19-NOV-2012 14:23:32 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=sun3)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
19-NOV-2012 14:23:59 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl1)(CID=(PROGRAM=sqlplus@sun3)(HOST=sun3)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.4)(PORT=53505)) * establish * orcl1 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
19-NOV-2012 14:28:40 * service_update * orcl1 * 0
19-NOV-2012 14:30:38 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl1)(CID=(PROGRAM=sqlplus@sun3)(HOST=sun3)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.4)(PORT=5290)) * establish * orcl1 * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
19-NOV-2012 14:38:43 * service_update * orcl1 * 0
WARNING: Subscription for node down event still pending
 
Solution  


In Listener.ora add the following :


SUBSCRIBE_FOR_NODE_DOWN_EVENT_=OFF

Thank you
osama mustafa

Saturday, November 17, 2012

Change Listener Default Name

Step One :

ps -ef | grep tns

oracle    4214     1  0 22:51 ?        00:00:00 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inherit


Step two :

lsnrctl stop LISTENER
[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 17-NOV-2012 22:55:41

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully


Step Three :

Go to $ORACLE_HOME/network/admin and modify Listener.ora

[oracle@localhost ~]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/





SID_LIST_OSAMA=
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  )

OSAMA =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
  )


Step Four :

lsnrctl start OSAMA


Step Five :

SQL> show parameter local_listener

SQL> alter system set local_listener='(address=(protocol=tcp)(host=xx.xx.xx.xx)(port=1521))';
SQL> alter system register;

Thank you
Osama Mustafa

Thursday, November 15, 2012

Dealing With Oracle Traces

This article using Oracle Database 11g , I will post for 10g Later .

How Could I change name for Oracle Trace :

alter session set tracefile_identifier = 'some_id';

    SQL> alter session set tracefile_identifier = 'osama';

    Session altered.

    SQL> oradebug tracefile_name

    /u01/app/oracle/admin/orcl/udump/orcl_ora_6207_osama.trc

maximum size of Oracle Trace File

-By Set max_dump_file_size parameter
- alter session set max_dump_file_size = unlimited;

Finding Oracle Trace File for current session :

     SELECT value
    FROM v$diag_info
    WHERE name = 'Default Trace File';

Finding Oracle Trace File for Current Database Process

     SELECT pid, program, tracefile
    FROM v$process;

To find all trace files for the current instance:

     SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

and you could use ADRCI features.

Thank you
Osama mustafa

SGA_MAX_SIZE & SGA_TARGET / MEMORY_TARGET & MEMORY_MAX_TARGET

In this article i will discuss the Difference between these parameter and how to use them ,  MEMORY_TARGET & MEMORY_MAX_TARGET  parameters appear in 11g .


I will depend on oracle documentation to describe these parameter :

SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic.

The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.

MEMORY_TARGET & MEMORY_MAX_TARGET 

you can manage SGA and PGA together rather than managing them separately.

If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified by you.

If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.

If MEMORY_TARGET is set to non zero value:

  • SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
  • SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.
  • SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
  • PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).


You can have some Description  On http://support.oracle.com

 Thank you
Osama Mustafa

ORADEBUG to Display Trace Name and Location


The Below Example Show you how I did it :

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 15 17:46:00 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid
Statement processed.
SQL> alter database backup controlfile to trace; --any Statement to Generate Trace File.

Database altered.

SQL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_6207.trc
SQL>









Thank you
Osama Mustafa

Wednesday, November 14, 2012

Register Listener In Database

Listener listens to new connections who is trying to connect to DB server. If the listener goes down, new users would not be able to connect to DB server. But still, already connected users would be able to do their work normally.

Listener   waiting  requests  from Clients to connect to the Instance. By default, the Listener name is (amazingly enough)“Listener” (but you can call it anything you like). It listens for connection requests on aparticular port (the default port number in 8.0 and above is 1521, but once again you canset this to any valid port number if you wish). A client knows where to contact the Listener (the machine it’s running on, and the port it’s listening on) because  a local configuration file, called “tnsnames.ora”, gives it the necessary information. More advanced configurations can dispense with the  tnsnames.ora .



How  to  Register  Listener  In  Database  

1.) Static Instance Registration
2.) Dynamic Instance Registration


Lets Discuss These Method and Start With Static Instance Registration :

Its basic method , and use $ORACLE_HOME\NETWORK\ADMIN\listener.ora its look like

    LISTENER =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
      )
     
And When You Fire lsnrctl status , instance Name Appear with Unknown like the following :

Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

 The status is unknown because there is no mechanism to guarantee that the specified status even exists.Here the listener assumes that instance will be there whenever there will be any request. It donot have inforamtion about the status of the Current Instance.

Dynamic Instance Registration (service registration):
  
in this Way the PMON is Responsible about Register Listener ,

Benefit for this way like the following :


1.) Simplified configuration  :  Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.

Note :  The SID_LIST_listener_name parameter is still required if we are using Oracle Enterprise Manager to manage the database.

2.) Connect-time fail over  : Because the listener always knows the state of the instances, service registration facilitates automatic fail over of the client connect request to a different instance if one instance is down.
In a static configuration model, a listener would start a dedicated server upon receiving a client request. The server would later find out that the instance is not up, causing an "Oracle not available" error message.

3.) Connection load balancing : Service registration enables the listener to forward client connect requests to the least loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes.  To ensure service registration works properly .
Services Summary...
Service "ORCL" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCL2" has 1 instance(s).
  Instance "ORCL2", status READY, has 1 handler(s) for this service...
The command completed successfully
 To register Database name with listener in this way you could use :

SQL> ALTER SYSTEM REGISTER;

Thank you
Osama Mustafa 

Tuesday, November 13, 2012

ORA-01102: cannot mount database in EXCLUSIVE mode

Error happened when try open database :

SQL> startup
ORACLE instance started.

Total System Global Area 267227136 bytes
Fixed Size 2212496 bytes
Variable Size 205524336 bytes
Database Buffers 54525952 bytes
Redo Buffers 4964352 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode


 In alert.log
sculkget: lock held by PID: 12359
ORA-09968: unable to lock file
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 12359
ORA-1102 signalled during: ALTER DATABASE MOUNT...
On OS level :

[oracle@localhost trace]$ ps -ef | grep pmon

oracle 10222 1 0 10:27 ? 00:00:00 ora_pmon_ORCL
oracle 2522 1 0 Nov09 ? 00:00:06 ora_pmon_orcl

Use :

 Kill -9 10222 2522

Then
1-After that fire export ORACLE_SID= (watch case sensitive )
2-sqlplus / as sysdba
3-startup ;

Thank you
Osama mustafa

Some Photo Of Duabi Gitex






Thank you
Osama mustafa

Friday, November 9, 2012

Multiple DataFiles and Mutliple Tablespace

As Any Database administrator you are managing your database check the size of your tablespace and datafiles , try to get better performance But why we are using Mutliple Tablespace and datafiles :

Advantage multiple tablespaces :

Control disk space allocation for database data
Assign specific space quotas for database users
Control availability of data by taking individual tablespaces online or offline
Perform partial database backup or recovery operations
Allocate data storage across devices to improve performance

The Same For Multiple DataFiles :
put each data file on a separate disk array. This decreases contention between disks.

Thank you
Osama Mustafa

extract cpio file

Some Oracle Installation file is .CPIO

what this file mean : extension is a UNIX and its file archive .

How to Extract this file :

$ cat cpio_file | cpio -idmv

Simple Topic But Useful .


Thank you
Osama mustafa


Wednesday, November 7, 2012

Session memory For Oracle User

To check the Memory Usage for Each Seesion :

select username,name,value
from v$session join v$sesstat using (sid)
join v$statname using (statistic#)
where name = 'session pga memory' and username='';

Example :

USERNAME              NAME                                   VALUE
----------------------------------------------------------- ----------
SCDB                       session pga memory                    699884

SCDB                       session pga memory                     651376


Thank you
Osama mustafa

Sunday, November 4, 2012

V$Session_Wait VS V$Session_event

Both of these tables are view In database , I will talk about what is the difference between them and Why some of record appear in V$Session_wait but not in V$session_event , Simple Topic But useful and good to know , Also Check the document that i post For Oracle that gave you structure for both of these view :

V$SESSION_WAIT 
displays the current or last wait for each session.

V$SESSION_EVENT
lists information on waits for an event by a session

This is simulation for both table appear what is the common between two view :

Conn SPP/SPP
 
select SID, EVENT from v$session_wait where event='DIAG idle wait';
 
SID          EVENT
5   DIAG idle wait
8   DIAG idle wait
 
select SID,EVENT from v$session_event where event ='DIAG idle wait' ;
 
SID          EVENT
5   DIAG idle wait
8   DIAG idle wait
Also its good to know when you are using 10g or later you can use v$seesion which is  gives you real-time information, what is happening right now.


gives you real-time information, what is happening right now :


there's different type of enqueue:wait in Oracle like the following :


    enq: TX - allocate ITL entry
 
    enq: TX - contention
 
    enq: TX - index contention
 
    enq: TX - row lock contention 
 

to check them you can query V$EVENT_NAME view provides a 
complete list of all the enq: wait events. 

But in V$session_wait you can check the following :

     P1: Lock TYPE (or name) and MODE



    P2: Resource identifier ID1 for the lock



    P3: Resource identifier ID2 for the lock

 Which is not found in v$session_event .

So We can say :

V$SESSION_WAIT 
displays the events for which sessions have just completed waiting or are currently waiting.
V$SESSION_EVENT 
is similar to V$SYSTEM_EVENT, but displays all waits for each session.

 Reference Document :
1-V$session_wait
2-V$session_event
3-Events

Thank you
Osama Mustafa

 

LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_1



LOG_ARCHIVE_DEST 
is applicable only if we are running the database in ARCHIVELOG mode. LOG_ARCHIVE_DEST parameter are used to specified the archiving location. The Location specified by log_archive_dest must be local . We choose to archive only two local location  i.e,  primary and a secondary destination ( using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST ) .



LOG_ARCHIVE_DEST_n 
initialization parameter defines up to ten (where n = 1, 2, ... 10) destinations in oracle 10g and thirty one (n=1,2....31)  destination in oracle 11g , each of which must specify either the  LOCATION or  the SERVICE  attribute to specify where to archive the redo data. All other attributes are optional.We set the attributes for the LOG_ARCHIVE_DEST_n initialization parameter to control different aspects of how redo transport services transfer redo data from a production or primary database destination to another (standby) database destination.For every LOG_ARCHIVE_DEST_n initialization parameter that we define, we must specify corresponding LOG_ARCHIVE_DEST_STATE_n parameter. The LOG_ARCHIVE_DEST_STATE_n (where n is an integer from 1 to 10) initialization parameter specifies whether the corresponding destination is currently enabled or disabled.
 

LOG_ARCHIVE_FORMAT 

Syntax : LOG_ARCHIVE_FORMAT = 'log%t_%s_%r.arc'


is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter.

The following variables can be used in the format:

%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

Reference Document :
1-Oracle Log_Archive_log Documentation.
2-Oracle Log_arcchive_Dest