Oracle – Notes

Issue :  message ora -01109 database not open.

Soln :
C:\> set ORACLE_SID=<YOUR SID>
C:\> sqlplus “/ as sysdba”
SQL> select status from v$instance;

the result should be MOUNTED. If so, try

SQL> alter database open;

if this works the DB will be available, otherwise you’ll get an error, from which we can try to understand something….


Issue :
Starting a Oracle Database – Troubleshooting

1. startup mount  – this command will start the db server and also mount the database, but you will see an ERROR ORA-01102: cannot mount database in exclusive mode
if there is another instance in the same server which has been started in EXCLUSIVE mode and not PARALLEL mode.

to fix this

1. find the instances which are cerated ( may not be running ) in the machine on which oracle is running ( ps -aef | grep pmon )
2. su -oracle and kill the instances
3. Start the Oracle Server again, by command – startup mount.
4. At this point the database should be open, if not use the cmd – SQL> alter database open;

Reference :
http://www.dba-oracle.com/t_ora_01102_cannot_mount_database_in_exclusive_mode.htm
http://www.dba-oracle.com/concepts/starting_database.htm

Issue : Not able to drop a user  ( in this case username is fuse ) – ORA-01940

select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = ‘fuse’ and p.addr (+) = s.paddr;
select job from dba_jobs where log_user=’fuse’
select queue_table, qid from dba_queues where owner=’fuse’;
select  apply_name from  dba_apply where queue_owner=’fuse’;
select  propagation_name from  dba_propagation where  destination_queue_owner=’fuse’;
or
destination_queue_owner=’fuse’;

Issue : Started Oracle DB and checked DB status is Mounted/OPEN – But when i “telnet 127.0.0.01 1521” I still can’t connect.

The issues is the DB is up but the Oracle LISTENER is not up. Follow the steps below to Troubleshoot

1. Make sure the DB is up and OPEN ( A. select status from v$instance  – make sure its up and OPEN  B. ps -aef | grep pmon – Make sure there is one process running )

2. check the tnsnames.ora under /export/home/oracle/products/10.2.0/network/admin and make sure DB and LISTENER entry is pointing to the correct DB host, in this case the DB server /host is on the same machine.

3. Now Start the TNS LINTENER by using the command as a Super user – lsnrctl start

Issue : How to find the schemas in a database

Soln : SELECT username FROM all_users ORDER BY username;

Issue : How To find the current databse
Soln : SELECT * FROM global_name;

Issue : How to run a SQL command on Oracle and exit out of sqlplus after the command is executed ( useflu for automation )

Soln :

echo exit | sqlplus user/pass@connect @scriptname   - more here

Issue : How to Shutdown Oracle DB 
Soln : 
sql> shutdown NORMAL|IMMEDIATE|ABORT ( Abort kills everything immediately , the fastest but not the safest way - more here
Advertisements

Leave a comment

Filed under DB, ORACLE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s