Posted by: Monish | January 5, 2015

How to change the default Listener in 12c ASM

Please note that the steps need to be done before the default listener is dropped/removed. If not then the default listener need to be added and then new listener name needs to be configured.


Add the new listener:

srvctl add listener -l L_HOSTNAME -p TCP:1527 -o /oracle/GRID/121_2


Check the status if the service is added

[oracle@HOSTNAME admin]$ crsctl stat res -t
Name           Target  State        Server                   State details
Local Resources
               ONLINE  ONLINE       HOSTNAME                 STABLE
               OFFLINE OFFLINE      HOSTNAME                 STABLE
               ONLINE  ONLINE       HOSTNAME                 STABLE
               ONLINE  ONLINE       HOSTNAME                 STABLE
               ONLINE  ONLINE       HOSTNAME                 Started,STABLE
               OFFLINE OFFLINE      HOSTNAME                 STABLE
Cluster Resources
      1        ONLINE  ONLINE       HOSTNAME                 Open,STABLE
      1        ONLINE  ONLINE       HOSTNAME                 STABLE
      1        OFFLINE OFFLINE                               STABLE
      1        ONLINE  ONLINE       HOSTNAME                 STABLE


Modify with the new value:

[oracle@HOSTNAME admin]$ srvctl modify asm -l L_HOSTNAME
[oracle@HOSTNAME admin]$ srvctl config asm
ASM home: <CRS home>
Password file: +DATA/orapwasm
ASM listener: L_HOSTNAME
Spfile: +DATA/ASM/ASMPARAMETERFILE/registry.253.877930002
ASM diskgroup discovery string:
[oracle@HOSTNAME admin]$


Remove the old listener

[oracle@HOSTNAME admin]$ srvctl remove listener -l LISTENER


Check the status

[oracle@HOSTNAME admin]$ crsctl stat res -t
Name           Target  State        Server                   State details
Local Resources
               ONLINE  ONLINE       HOSTNAME                 STABLE
               ONLINE  ONLINE       HOSTNAME                 STABLE
               ONLINE  ONLINE       HOSTNAME                 STABLE
               ONLINE  ONLINE       HOSTNAME                 Started,STABLE
               OFFLINE OFFLINE      HOSTNAME                 STABLE
Cluster Resources
      1        ONLINE  ONLINE       HOSTNAME                 Open,STABLE
      1        ONLINE  ONLINE       HOSTNAME                 STABLE
      1        OFFLINE OFFLINE                               STABLE
      1        ONLINE  ONLINE       HOSTNAME                 STABLE


Crosscheck the ASM configuration

[oracle@HOSTNAME admin]$ srvctl config asm
ASM home: <CRS home>
Password file: +DATA/orapwasm
ASM listener: L_HOSTNAME
Spfile: +DATA/ASM/ASMPARAMETERFILE/registry.253.877930002
ASM diskgroup discovery string:
[oracle@HOSTNAME admin]$
Posted by: Monish | April 28, 2011

EXP using pipe

Using the following script we can take export backup using pipe.
Change the DIRECTORY value to your backup location.

suppose the following script name is exp_pipe.ksh then execute it as

nohup ./exp_pipe.ksh $SID &

if [ $# -ne 1 ]
   echo “Usage : $0 ORACLE_SID”

export ORACLE_SID=$1

export ORACLE_HOME=/u01/oracle/product/9.2.0
DATE=`date +%Y%m%d`
DIRECTORY=/u01/backup; export DIRECTORY
PIPE=${DIRECTORY}/export_pipe_${DATE}.dmp; export PIPE

rm -f ${PIPE}
test -p ${PIPE} || mknod ${PIPE} p
chmod +rw ${PIPE}

ls -l $PIPE
echo “cat $PIPE | /usr/bin/gzip > ${FILENAME}”
echo “${ORACLE_HOME}/bin/exp log=${LOG} file=${PIPE} owner=scott”
cat $PIPE | /usr/bin/gzip > ${FILENAME} &
${ORACLE_HOME}/bin/exp log=${LOG} file=${PIPE} STATISTICS=NONE owner=scott  <<!

To execute the same script in HP-UNIX environment, change the gzip location to /usr/contrib/bin/gzip


Hi All,

After a long time I’m posting my blog. Actually I was very busy in my life. Literally did not get much time. At the same time change the company. New company, new environment!

Anyways before coming to the main topic of the post I want to share the story behind this issue.

One day I got a request to compile the invalid objects on the production database. A very basic activity, so I ran the utlrp.sql to compile them. But wow all remain same. So I ran again, still same. I got amazed. Never faced such issue. Ok so went to look which are Invalid and who are the owner. Found most of them belongs to APPS schema.

So I tried with dbms_utility.compile_schema to compile the Apps schema. And BANG!! You wont believe within 15 min the Invalid object count reached to 20K. Then and there all the users and the client start complaining that the application is not working. Nothing is working. That time I felt like standing in the marriage ceremony NAKED! I aborted the utility, and checked all 20K invalid objects belong to APPS schema. Asked so many seniors, but nobody had any clue, utlrp.sql was useless.

We start looking all possible aspects, and found the PLSQL_CODE_TYPE is set as NATIVE. That’s cool, its me plsql will use the C compiler to compile. Here is he story, there was no C compiler on the server.

It was a huge issue, coz it was weekday and pure business hour, and the business is down because of invalid objects.

We did not had time to install the C compiler because it was a Solaris 64bit server and the compiler comes with the CD from vendor, the customer told he has 30 CDs and don’t know which one have the software.

So we change the   PLSQL_CODE_TYPE parameter to INTERPRETED and tried to compile them manually. We prepared a script and starts compile them. It took really long time but worked.

SQL>select ‘alter package ‘||object_name||’ compile body;’ from user_objects where status=’INVALID’ and object_type=’PACKAGE BODY’ AND ROWNUM<1001;

Anyways the database came up. And the business started.

Now, the main thing, find the ROOT CAUSE. After doing all sorts of research found that long time back someone change the plsql_code_type parameter from interpreted to native without the C compiler and the proper steps. So when we tried to compile the invalid object, it was looking for the C compile but there was none.

NATIVE is faster than INTERPRETED. Though the issue was resolved but there was still performance issue. So we had to make it NATIVE. So, following are the steps.

The Steps: –

1. Ensure that following are properly configured to support native compilation:

* A supported C compiler is installed in the database environment and that the spnc_commands file has the correct command templates for this compiler.
* The PLSQL_NATIVE_LIBRARY_DIR is set. This is a required system-level only parameter that specifies the full path and directory name of the location of the shared libraries that contain natively compiled PL/SQL code. The value must be explicit and point to an existing, accessible directory; the path cannot contain a variable such as ORACLE_HOME. Use the ALTER SYSTEM command or update the initialization file to set the parameter value.
* The PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT is set correctly for the number of natively compiled units after conversion.
* A test PL/SQL unit can be compiled

2. Shut down application services, the listener, and the database.

* Shut down all of the Application services including the Forms Processes, Web Servers, Reports Servers, and Concurrent Manager Servers. After shutting down all of the Application services, ensure that all of the connections to the database have been terminated.
* Shut down the TNS listener of the database to ensure that no new connections are made.
* Shut down the database in normal or immediate mode as the user SYS.

3. Set PLSQL_CODE_TYPE to NATIVE in the initialization parameter file. If the database is using a server parameter file, then set this after the database has started. See “PLSQL_CODE_TYPE Initialization Parameter”.

The value of PLSQL_CODE_TYPE does not affect the conversion of the PL/SQL units in these steps. However, it does affect all subsequently compiled units and it should be explicitly set to the compilation type that you want.

4. Start up the database in upgrade mode, using the UPGRADE option.

5. Execute the following code to list the invalid PL/SQL units. You can save the output of the query for future reference with the SQL SPOOL command.

REM To save the output of the query to a file: SPOOL pre_update_invalid.log
REM To stop spooling the output: SPOOL OFF

If any Oracle supplied units are invalid, try to validate them. For example:


6. Execute the following query to determine how many objects are compiled NATIVE and INTERPRETED. Use the SQL SPOOL command if you want to save the output.


Any objects with a NULL plsql_code_type are special internal objects and can be ignored.

7. Run the $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script as the user SYS to update the plsql_code_type setting to NATIVE in the dictionary tables for all PL/SQL units. This process also invalidates the units. Use TRUE with the script to exclude package specifications; FALSE to include the package specifications.
@$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql TRUE

This update must be done when the database is in UPGRADE mode. The script is guaranteed to complete successfully or rollback all the changes.

8. Shut down the database and restart in NORMAL mode.

9. Before you run the utlrp.sql script, Oracle recommends that no other sessions are connected to avoid possible problems. You can ensure this with:


10. Run the $ORACLE_HOME/rdbms/admin/utlrp.sql script as the user SYS. This script recompiles all the PL/SQL modules using a default degree of parallelism. See the comments in the script for information on setting the degree explicitly.

If for any reason the script is abnormally terminated, rerun the utlrp.sql script to recompile any remaining invalid PL/SQL modules.

11. After the compilation completes successfully, verify that there are no new invalid PL/SQL units using the query in point 5. You can spool the output of the query to the post_upgrade_invalid.log file and compare the contents with the pre_upgrade_invalid.log file, if it was created previously.

12. Re-execute the query in point 6. If recompiling with dbmsupgnv.sql, confirm that all PL/SQL units, except TYPE specifications and package specifications if excluded, are NATIVE. If recompiling with dbmsupgin.sql, confirm that all PL/SQL units are INTERPRETED.

13. Disable the restricted session mode for the database, then start the services that you previously shut down. To disable restricted session mode:


Read More…

Error: ORA-1684
Text: max # extents (%s) reached in table %s.%s partition %s
Cause: A table tried to extend past maxextents
Action: If maxextents is less than the system maximum, raise it. Otherwise,
you must recreate with larger initial, next or pctincrease params

Posted by: Monish | August 4, 2009

Unlock record of a Oracle table

Session: 1

SQL> select * from dept;
DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON

Lock one row.

SQL> update dept set dname='DEV' where deptno=30;
1 row updated.

Do not commit

Session: 2

Try to lock the same row

SQL> update dept set dname='TESTING' where deptno=30;

Execute this srcipts to gather lock statistic.

SQL> select (select username from v$session where sid=a.sid) BLOCKER,
2 a.sid,
4 (select username from v$session where sid=b.sid) BLOCKEE,
5 b.sid
6 from v$lock a, v$lock b
7 where a.block = 1
8 and b.request > 0
9 and a.id1 = b.id1
10 and b.id2 = b.id2
11 /

BLOCKER                        SID        'ISBLOCKING BLOCKEE                        SID
------------------------------ ---------- ----------- ------------------------------ ----------
SCOTT                          146        IS BLOCKING SCOTT                          151

SQL> col OBJ format a15
SQL> col SS format a15
SQL> set linesize 200

SQL> select owner||'.'||object_name obj
2 ,oracle_username||' ('||s.status||')' oruser
3 ,os_user_name osuser
4 ,l.process unix
5 ,''''||s.sid||','||s.serial#||'''' ss
6 , rs
7 ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
8 from v$locked_object l
9 ,dba_objects o
10 ,v$session s
11 ,v$transaction t
12 ,v$rollname r
13 where l.object_id = o.object_id
14 and s.sid=l.session_id
15 and s.taddr=t.addr
16 and t.xidusn=r.usn
17 order by osuser, ss, obj
18 /

OBJ             ORUSER            OSUSER                         UNIX         SS RS TIME
--------------- ----------------- ------------------------------ ------------ --------------- ------------------------------ -------------------
SCOTT.DEPT      SCOTT (INACTIVE)  oracle                          17809       '146,188'

_SYSSMU1$ 2009/08/04 06:03:37

here ss means <session_id>,<serial#>

Session: 3

Kill the session to release the lock:

ALTER SYSTEM KILL SESSION ‘<session_id>,<serial#>’;

SQL> alter system kill session '146,188';

System altered.

Posted by: Monish | June 9, 2009

WARNING: inbound connection timed out (ORA-3136)

What the error is telling you is that a connection attempt was made, but the session authentication was not provided before SQLNET.INBOUND_CONNECT_TIMEOUT seconds.

As far as adverse effects in the long run, you have a user or process that is unable to connect to the database. So someone is unhappy about the database/application.
In 10gR2, SQLNET.INBOUND_CONNECT_TIMEOUT the parameters were set to have a default of 60 (seconds).

Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite).
Before setting SQLNET.INBOUND_CONNECT_TIMEOUT, verify that there is not a firewall or Network Address Translation (NAT) between the client and server. Those are common cause for ORA-3136.
The parameter is to be added in sqlnet.ora on the server, so wont require a shutdown.
and edit it at server side.


Reference: Oracle’s database forum and was provided by Eric Jenkinson:

Posted by: Monish | June 9, 2009

SQL Script | Tablespace Threshold value

Use the following script to find out tablespace name under the mentioned threshold value:

select trunc(s.bytes/f.maxbytes, 2) used_pct, ‘TABLESPACE ‘ || s.tablespace_name || ‘ is ‘ || to_char(trunc(s.bytes/f.maxbytes, 2)*100) || ‘% full’ description from (select tablespace_name, sum(bytes) bytes from dba_segments
group by tablespace_name) s, (select tablespace_name, sum(greatest(nvl(maxbytes, 0), bytes)) maxbytes from dba_data_files group by tablespace_name) f where s.tablespace_name = f.tablespace_name and s.bytes/f.maxbytes >.90;

The threshold value can be changed by changing the last two numeric digits.

Posted by: Monish | June 9, 2009

SQL Script | Perticular Tablesapce Details

Use the following script is to find out the details of a particular tablespace

set lines 999
col file_name format a70;
select file_name,bytes/(1024*1024),maxbytes/(1024*1024),autoextensible from dba_data_files
where tablespace_name=’tablespace-name’

Older Posts »