RAM&KRISHNA BLOG

Here will see how to rename a data file name. Please follow below steps. FILE_NAME TABLESPACE_NAME MAXBYTES/1024/1024/1024 -------------------------------------------------- -------------------- ----------------------- /data/ORA/tpde_02.dbf  TPDE 20 /data/ORA/tpde_01.dbf  TPDE 10 /data2/ORA/tpde_01.dbf  TPDE 30 /data3/ORA/tpde_03.dbf  TPDE 20 Above, the both data files names are same. But they are in diff locations. Now will try to rename the data file to any other name. Which is in /data2/ORA/tpde_01.dbf location Login server & cd to the datafile location. connect to the database. SQL> select name from v$database; NAME --------------------------- TPDE 2. First put the tablespace in offline mode. like below SQL> ALTER TABLESPACE TPDE OFFLINE NORMAL; Tablespace altered. 3. Then go to data file location & try to mv the data file with new name. like below. varthhammr.print.com@/data2/ORA/: mv tpde_01.dbf tpde_04.dbf varth...

NOTE: Here I'm doing backup & restore for the database name => ORCL RMAN COLD BACKUP : We can only take. When the database is in MOUNT state. And the database is in noarchivelog mode. RMAN HOT BACKUP : We can only take if database running in archivelog mode. Here we can take the backup in 'OPEN' state. No need to mount the database. Please see below steps: SQL> select name from v$database; NAME --------- ORCL SQL> select open_mode from v$database;  OPEN_MODE ------------ READ_WRITE SQL> archive log list; Database log mode              No Archive Mode Automatic archival             Disabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     14 Current log sequence           14 SQL> exit Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Productio...

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 2...

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 h...

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  “ki...

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 ------------------------------------ ----------------------...