Posts

Showing posts from November, 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-TreeSpace is freed up for other database objectsSpace below the HWM is released and the HWM is moved down
 Thank you
Osama Mustafa

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 :

-Param…

Change Listener Port (Single Instance)

Image
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 Sql…

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

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;

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:liste…

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_li…

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 …

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

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 Wi…

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

Image
Thank you
Osama mustafa

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


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

Critical Patch Update / CVE-2012-3137

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 informatio…

LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_1