Monday, 23 January 2012

Start and Stop an Oracle Clusterware

Use the below steps to stop and start an Oracle Clusterware

1. Check the status of CRS using below command.
crsctl check crs

2. Login to each server(node) of your clusterware using root user.

3. Stop the clusterware using below command
srvctl stop crs

4. Start the crs using below command
srvctl start crs

Start and Stop an Oracle Real Application Cluster Database

In this article, we will see how to start and stop an Oracle Real Application Cluster Database.

Stopping an Oracle RAC database

1.Check the list of applications running before stopping the RAC by using below command on any one node.
crs_stat -t

Note:every resource inside Oracle Clusterware is registered as type 'application' in 10g and 11g R1

2.Stop each instance on all nodes(servers)

srvctl stop instance -d db_name -i instance_name -o options_to_shutdown

3.Stop asm on all nodes(servers)

srvctl stop asm -n node_name -i asm_instance_name -o options_to_shutdown_asm

4.stop nodeapps on all nodes

srvctl stop nodeapps -n node_name

Note:You can stop the listener using srvctl on all nodes as below.

Check the listener that are running on the server as below
ps -ef|grep tns

then stop the listener on each node using below command

srvctl stop listener -n node_name -l lister_name

Starting an Oracle RAC database

To start the Oracle RAC database follow the above steps in reverse order.

Start/Stop All Instances with SRVCTL using below command.

$ srvctl start database -d db_name

$ srvctl stop database -d db_name

Note: These all commands can be executed from any one node. You need not to login on each node to execute these commands as long your SRVCTL is working fine

Sunday, 22 January 2012

Oracle enable row movement

When you add the clause "enable row movement" to a create table statement, you are granting permission to Oracle to change the ROWID's. It basically enables rows to move. If table is already created, you can enable row movement by using below command.

SQL> alter table table_name enable row movement;

Note that tables have to be in an ASSM (Automatic Segment Space Managment) tablespace for this to work so if they are not, you have to move them there first in order to enable row movement.

We use "enable row movement"  in below cases

1. We want to reclaim a space with the "alter table shrink space"  

SQL> alter table table_name shrink space;

2. When we want to use flashback table because we achieve that by:

a) deleting all rows modified or inserted since the time you want to flashback to
b) inserting all rows modified or deleted since the time you want to flashback to

so, if the emp_id record in EMP table is updated - we delete and insert it - it'll have a new rowid, it

Example of flashback table is

TO_TIMESTAMP('2012-23 08:10:00',

Friday, 20 January 2012

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

 When user was connecting to the database though an application, user got the error as below.

Error: ORA-12505: TNS:listener does not currently know of SID given in connect descriptor at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)

Cause: User was unable to connect to database through connect descriptor that we use to connect to database from remote server or from application.

Solution: Since listener is running,try to connect the database manually through connect descriptor that is usually stored in tnsnames.ora file.  Always make sure that connect descriptor has correct db name, hostname and port name.

sqlplus user1/pwd@TESTDB

SQL*Plus: Release Production on Fri Jan 20 04:39:03 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ORA-12505: TNS:listener does not currently know of SID given in connect

Then check your database name in connect descriptor available at tnsnames.ora file.
If that is correct then check your services in listener to find whether requested db is being listened by the listener. You can use below command to check the same.

lsnrctl services listener_name

If db is found, then wait for few seconds and check the connection. If db is not found in the services, then manually register your database to the listener by using below command.

SYS@TEST>alter system register;

Check after few seconds, you will be able to connect to the database using connect descriptor.

Wednesday, 18 January 2012

Ports used in OBIEE 11g

You can use below command to check which ports are currently in use

netstat -a -b -v

Default ports used in OBIEE 11g are

used for
Oracle IIOP-server port (Internet Inter-ORB Protocol)
Oracle IIOP-server SSL port
Oracle IIOP-server Client-Server SSL port
Discovery port for coordinator of Java cache
JMS Server port
Monitor Controller port for Intra Cluster Communication with Cluster Controllers
Monitor Server port for Intra Cluster Communication with clustered Analytics Server
- Remote procedure call port of Oracle BI Server
- Outfacing port used for ODBC connections

standard port for OC4J
- used for Web Browser access, in Catalog manager etc.
standard port for BI Server scheduler
Client controller part Clustering
port for presentation services and ISAPI
Java Host port
Remote Method Invoker (RMI) port
Remote Method Invoker (RMI) SSL port

Tuesday, 17 January 2012

Oracle 11g Release 2 ( is available as patch set

Oracle Database 11g Release 2 ( is available in the latest media pack as patch set.

To download the software from My Oracle Support:

    Log on to My Oracle Support.

    Click Patches & Updates.

    In the Patch Search section, click the Search tab.

    Enter 10404530 in the patch number field.

    Select a platform to download.

    Click Search.

    In the Patch Search Results page, under Patch Name, click the patch number to display the patch details.

    Click Download.

    From the File Download window, click each file name to download the selected software.

Refer the readme document to get which zip file corresponds to which installation Type.