RAM&KRISHNA BLOG

We can start an instance in various modes: 1. Start the instance without mounting a database. This does not allow access to the database and usually would be done only for database creation or the re-creation of control files. 2. Start the instance and mount the database, but leave it closed. This state allows for certain DBA activities, but does not allow general access to the database. 3. Start the instance, and mount and open the database. This can be done in unrestricted mode, allowing access to all users, or in restricted mode, allowing access for database administrators only. Starting an Instance Without Mounting a Database: You can start an instance without mounting a database. Typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT option: Use 'STARTUP NOMOUNT' command Start an instance and mount the database. Example: C:\Users\>sqlplus SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 31 13:52:36 20

Below are the three methods are available to shut down the oracle database: Normal Shutdown :   During normal shutdown, before the oracle database is shut down, oracle will wait for all active users to disconnect their sessions. As the parameter name (normal) suggest, use this option to shutdown the database under normal conditions. 1. No new connections are allowed after the statement is issued. 2. Before the database is shut down, Oracle waits for all currently connected users to disconnect from the database. The next startup of the database will not require any instance recovery procedures. Example: Connect as sys user. and execute below SQL> shutdown Database closed. Database dismounted. ORACLE instance shut down. Shutdown Immediate:   During immediate shutdown, before the oracle database is shut down, oracle will rollback active transaction and disconnect all active users. Use this option when there is a problem with your database and you don’t have enoug

NOTE > The problem usually happens when using the Direct Path for the SQL*Loader, Direct Load or DDL operations. This requires enough temporary space to build all indexes of the table. If there is no enough space in TEMP tablespace, all rows will still be loaded and imported, but the indexes are left with STATUS = ‘INVALID’. Check invalid indexes using the below query, connecting to SYS user. SQL > select owner, index_name from dba_indexes where status='UNUSABLE'; To check status of a specific index use the below query: SQL> select owner,index_name,table_name,status from dba_indexes where index_name='&index_name'; We can use the below query for rebuilding all the failed indexes in the database. Conn to sys and execute the below. SQL> SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE'; Can unusable indexes using below parameter ' SKIP_UNUSABLE_INDEXE

SQL> ALTER SYSTEM KILL SESSION '222, 18784 ' immediate; ALTER SYSTEM KILL SESSION ' 222, 18784 ' immediate * ERROR at line 1: ORA-00031: session marked for kill * Here the issue is in what this alter system kill command is doing.  * Alter System Kill command > It’s not actually killing the target session (like kill -9 would do for OS processes).  * It just sets a bit in the target sessions state object, which marks that the target session should end.  * But its entirely with the target session to check this bit and act on it. * All the kill session command is doing is ASK the target session to clean up and exit – via setting that bit. * The session trying to issue the kill will hang for 60 seconds and then return this “session marked for kill” message. And the target session does not get killed at all. * The “ORA-00031: session marked for kill” message you see after 60 seconds just means that: * Session marked for  “kill”

You can limit the number of connections from a user process to remote databases using the static initialization parameter OPEN_LINKS. This parameter controls the number of remote connections that a single user session can use concurrently in distributed transactions. If you are not sure how many database links are opened up concurrently by your session's database application, you can query v$dblink select in_transaction, count(*) from v$dblink group by in_transaction; To Increase the Database Links limit.  Follow the below. SQL> select name from v$database; NAME --------------------------- ORCL SQL> show parameter open_links; NAME                                 TYPE                                         value ------------------------------------ --------------------------------- ------------------------------ open_links                           integer                                         4   Default Value is > 4 Here have ch