Alter – Technology Geek

ORA-12516: TNS:listener could not find available handler

Posted on June 11, 2012December 23, 2018 by Osama Mustafa in Database

after the increasing the Number processes in the Oracle database, it solved the problem.

But Before this , i am gonna show you some steps to check the process via OS and Database LETS BEGIN :

check if the number of connections reaches the database’s process parameter using following Unix command:

ps -ef | grep oracleSID | grep -v grep | wc -l
or
ps aux | grep oracleSID | grep -v grep | wc -l

check if the number of connections reaches the database’s process parameter using following Database Command :

SQL> show parameter processes

NAME                TYPE        VALUE
------------------ ----------- -------
processes           integer     150

SQL> select count(*) from v$process;

  COUNT(*)
----------
       149

SQL> show parameter sessions

NAME                TYPE        VALUE
------------------ ----------- -------
Session              integer     150

SQL> select count(*) from v$session;

  COUNT(*)
----------
       149

Now We need To Increase the Both Parameter By :

SQL> alter system set processes=300 scope=spfile;

System altered.

SQL> alter system set Session=300 scope=spfile;

System altered.

If this Solution Not Work For you , Try this One :

SQL> alter system set local_listener=

“(ADDRESS=(PROTOCOL=tcp)(HOST=10.122.28.12)(PORT=1521))” sid=’ORCL1′;

System altered.

SQL> show parameter local

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string (ADDRESS=(PROTOCOL=tcp)(HOST=1
0.122.28.12)(PORT=1521))

Thank you

Osama Mustafa

Automatic Storage Management (ASM) / Part 1

Posted on April 20, 2012December 23, 2018 by Osama Mustafa in Database

using SQL*Plus connect to the idle instance.

export ORACLE_SID=+ASM
sqlplus / as sysdba

Startup and Shutdown of ASM Instances

ASM instance are started and stopped in a similar way to normal database instances. The options for the STARTUP command are:

FORCE – Performs a SHUTDOWN ABORT before restarting the ASM instance.
MOUNT – Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.
NOMOUNT – Starts the ASM instance without mounting any disk groups.
OPEN – This is not a valid option for an ASM instance.

The options for the SHUTDOWN command are:

NORMAL – The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
IMMEDIATE – The ASM instance waits for any SQL transactions to complete then shuts down. It doesn’t wait for sessions to exit.
TRANSACTIONAL – Same as IMMEDIATE.
ABORT – The ASM instance shuts down instantly.

ASM Disk Groups

level of redundancy:

NORMAL REDUNDANCY – Two-way mirroring, requiring two failure groups.
HIGH REDUNDANCY – Three-way mirroring, requiring three failure groups.
EXTERNAL REDUNDANCY – No mirroring for disks that are already protected using hardware mirroring or RAID. If you have hardware RAID it should be used in preference to ASM redundancy, so this will be the standard option for most installations.

Create Example :

CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
  FAILGROUP failure_group_1 DISK
    '/devices/diska1' NAME diska1,
    '/devices/diska2' NAME diska2
  FAILGROUP failure_group_2 DISK
    '/devices/diskb1' NAME diskb1,
    '/devices/diskb2' NAME diskb2;

DROP Example :

DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

Using Alter to Add Disk :

ALTER DISKGROUP disk_group_1 ADD DISK
  '/devices/disk*3',
  '/devices/disk*4';

Using Alter to Drop Disk :

ALTER DISKGROUP disk_group_1 DROP DISK diska2;

Using Alter to Re-size Disk :

ALTER DISKGROUP disk_group_1
  RESIZE DISK diska1 SIZE 100G;

Using Alter to Re-size all disk in Failure Group:

ALTER DISKGROUP disk_group_1
  RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;

Using Alter to Resize all Disk In DiskGroup :

ALTER DISKGROUP disk_group_1
  RESIZE ALL SIZE 100G;

Using Alter To dismiss pending disk to be Dropped :

ALTER DISKGROUP disk_group_1 UNDROP DISKS;

Some Useful Command to Mount ASM disk group Manually :

ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP disk_group_1 DISMOUNT;
ALTER DISKGROUP disk_group_1 MOUNT;

Thank You

Osama mustafa

Resize Redo Log

Posted on January 17, 2012December 23, 2018 by Osama Mustafa in Database

Here is a newly created database redo log information:

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
———- ———- ———- ———- ———- — —————- ————- ——————-
1 1 17 52428800 1 NO INACTIVE 996238 06/09/2007 22:01:59
2 1 18 52428800 1 NO INACTIVE 1006432 06/09/2007 22:13:32
3 1 19 52428800 1 NO CURRENT 1036439 07/09/2007 09:56:44

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
———- ——- ——- ————————————————-
3 ONLINE /database/data/redo03.log
2 ONLINE /database/data/redo02.log
1 ONLINE /database/data/redo01.log

Here is how i changed this to five 200M redo logs:

SQL> alter database add logfile group 4 (‘/database/data/redo04.log’) size 200M;
SQL> alter database add logfile group 5 (‘/database/data/redo05.log’) size 200M;

while running following sql commands, if you hit an error like this:

ORA-01623: log 3 is current log for instance RPTDB (thread 1) – cannot drop
ORA-00312: online log 3 thread 1: ‘/database/data/redo03.log’

you should run ” alter system switch logfile;” until current log is 4 or 5. Then execute “alter system checkpoint;”

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

then move (or maybe drop) old redo logs

mv /database/data/redo01.log /database/data/redo01_old.log
mv /database/data/redo02.log /database/data/redo02_old.log
mv /database/data/redo03.log /database/data/redo03_old.log

finally

SQL> alter database add logfile group 1 (‘/database/data/redo01.log’) size 200M;
SQL> alter database add logfile group 2 (‘/database/data/redo02.log’) size 200M;
SQL> alter database add logfile group 3 (‘/database/data/redo03.log’) size 200M;

Change Enterprise manager password for 11g

Posted on December 3, 2011December 23, 2018 by Osama Mustafa in Application

For the DB Control Release 11.2 and higher, you need to set the environment variable ORACLE_UNQNAME to the value of the DB_UNIQUE_NAME database parameter.

Steps :

1.Stop the DB Control
On Unix
$ emctl stop dbconsole

On Windows
Open a Command Window and type **> emctl stop dbconsole

2.Check that the DB Control is stopped
On Unix
$ emctl status dbconsole

On Windows
Open a Command Window and type
**>emctl status dbconsole

3.Connect to the database as a user with DBA privilege with SQL*Plus and execute:

SQL> alter user sysman identified by ;

4.Check the new password

SQL> connect sysman/[@database_alias]

5.From the database directory $ORACLE_HOME/bin, execute:
On Unix

$ emctl setpasswd dbconsole
Provide the new SYSMAN password

On Windows

**>: emctl setpasswd dbconsoleProvide the new SYSMAN password

6.Restart the DB Control
On Unix

$ emctl start dbconsole

On Windows

Open a DOS Command Window and type
**>: emctl start dbconsole

Thank you

Osama Mustafa

TKPROF And Oracle Trace Analysis

Posted on November 10, 2011December 23, 2018 by Osama Mustafa in Database

What is the TKPROF ??!!

The TKPROF program :

converts Oracle trace files into a more readable form. If you have a problem query you can user TKPROF to get more information.

But first you need to enable this tools since its unactivated in oracle to start using it follow the below steps :

1-ALTER SYSTEM SET TIMED_STATISTICS = TRUE;

2-we need table called PLAN_TABLE if its dosen’t found create it by following steps :

@ORACLE_HOME\rdbms\admin\utlxplan.sql
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYS.PLAN_TABLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON SYS.PLAN_TABLE TO PUBLIC;

3-after doing the Previous steps , now you can use the amazing tools for example :

ALTER SESSION SET SQL_TRACE = TRUE;

SELECT COUNT(*)
FROM   dual;

ALTER SESSION SET SQL_TRACE = FALSE;

OR Another Way to trace file :

TKPROF

explain=user/password@service table=sys.plan_table

Output will be like :

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

 SELECT COUNT(*)
 FROM   dual

call    count    cpu elapsed    disk   query current     rows
------- -----  ----- ------- ------- ------- -------  -------
Parse       1   0.02    0.02       0       0       0        0
Execute     1   0.00    0.00       0       0       0        0
Fetch       2   0.00    0.00       0       1       4        1
------- -----  ----- ------- ------- ------- -------  -------
total       4   0.02    0.02       0       1       4        1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 121  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE 
      1   TABLE ACCESS FULL DUAL

Thank you

Osama mustafa