Issue : message ora -01109 database not open.
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;
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’;
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 )
echo exit | sqlplus user/pass@connect @scriptname - more here
Issue : How to Shutdown Oracle DB
sql> shutdown NORMAL|IMMEDIATE|ABORT ( Abort kills everything immediately , the fastest but not the safest way - more here