Listener and Tuning

1. Need to export a database using exp from putty even we get a network issue. (????****)
   
2. Import a schema which is in tablespace USERS to another schema which is in tablespace USERS2?
   remap_tablespace=users:users2
3. how to goback to client mode from interactive mode in datapump?
   EXPORT>continue_client
4. How will you trouble shoot a client server connection issue? Explain all the possible issues?
   Have to check the TNS and Listener in $ORACLE_HOME/network/admin or by using Netca in linux also will recreate a new one connects the client server
5. If listener is down, what will happen to the existing connection? what will happen to new connection?
   If there are 100 sessions connected to the database and if listener goes down then no new sessions will be able to connect to the database but already connected sessions will remain unaffected.
6. How to check how many listeners are running on the server? How to check their status?
   a) ps -ef|grep -i tns
   b) lsnrctl status
7. Is it possible to change the listener and tnsnames location? If so how to do that?
   Yes, it is possible to change by using TNS_ADMIN parameter
   Setting the TNS_ADMIN environment variable on a Microsoft® Windows® 2000 and 2003 system:
   • On the desktop Right-click on My Computer and select Properties > Advanced tab
   • Click the Environment Variables button.
   • In the System variables section click the new button
   • Enter a variable name of TNS_ADMIN then enter a variable value of the path to the TNSNAMES.ORA file that will be used as the default.
   For example:
   Variable Name TNS_ADMIN
   Variable Value C:\Oracle\Ora8i\Network\Admin
   • Click OK
   • Reboot the machine for the changes to take effect.
8. Explain the steps to recover a database using a backup from a location? (****)
   => Catalog start with ‘path’ (to register the control file for backup information)
   => alter database mount
   => restore database
   => recover database
9. Explain the steps RMAN cloning and Active cloning?
   a) No backup needed for active cloning
   [Linux]$ rman target userid/passwd@sourceDB auxiliary userid/passwd@newDB
   RMAN> DUPLICATE TARGET DATABASE TO ”NEWDBNAME” FROM ACTIVE DATABASE NOFILENAMECHECK;
10. Can we have 2 listeners for a single database and 1 listener for multiple database? If so, how?
    a) Yes, We can have 2 listener for a Single database by creating in listener.ora file,but port number should be different
    LISTENER_SSS =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP) (HOST = sss-oracle) (PORT = 1521))
    )
    )
    SID_LIST_LISTENER_SSS =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = SSS)
    (ORACLE_HOME = /u01/product/10.2.0/db_1)
    )
    )
    ————————
    LISTENER_SAKTHI =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP) (HOST = sss-oracle) (PORT = 1522))
    )
    )
    SID_LIST_LISTENER_SAKTHI =
    (SID_LIST =
    (SID_DESC =
    (ORACLE_HOME = /u01/product/10.2.0/db_1)
    (SID_NAME = SSS)
    )
    )
    b) Yes, you can have one listener for more than one database.
11. How to register a database to a listener?
    To be set SID in a listener.ora file (or) by using nertca
12. How to setup a recover catalog? explain with steps?
    step 1: create user RC identified by RC default tablespace user’s quota unlimited on users;
    step 2: grant connect,resource,recovery_catalog_owner to rc;
    step 3: rman catalog rc/rc@DBNAME
    (or)
    rman catalog rc/rc
    step 4: create catalog
    step 5: register database
    step 6: list incarnation
13. Find the user who is locking a table?
    v$locked_object
    step 1: select * from v$locked_object;
    step 2: select object_name,owner,object_type from dba_objects where object_id=12455;
14. How to find if a query is using index?
    First, we need to execute the query:
    Sql > Select * from txn_xn_123;
    explain the table and it stores in a dictionary table called plan_table:
    Sql > explain plan for select * from txn_xn_123;
    If we wants in readable format,then we need to plan_table_output:
    Sql > select * from table(dbms_xplan.display);
    >>>Then we can get the output that the query is used Index / not
15. What are the possible chances that an index is not used by a query?How to force a index to be used?
    a) small table is the reason that an index is not used by a query.
    b) By using hints index in query:
    SQL> explain plan for select /*+ index (tablename indexcolumnname) */
16. How to intrepret execution plan?
    Execution plan read from right most top (or) Inner most top
17. If a user reports that a query hangs, how will you trouble shoot?
    v$session –> Check active query and check with event for what type of issue is there
    v$sql –> sql_text
18. If a query run yesterday for 2 mins and it takes more than 1 hr, what might be the possible issue?
    * We need to check in ASH – dba_hist_active_sess_history
    * Select Sql_exec_start-max(sample_time) from dba_hist_active_sess_history
    * Then we need to check the event about the possible issue and make tuning the sql by using plan’s like hash,index, or etc..
19. How will you kill a session even after it marked for killing in DB?
    step 1:by using sid will get paddr in v$session
    step 2:by using paddr will get spid in v$process
    step 3: will kill the session by using spid in an OS Level ($ kill -9 9021)
20. How many constraints are there in the table and on what columns? What are the constraint types?
    a) It depends upon the No. of columns and it should be a unique column
    user_constraints, user_cons_columns
    b) Unique, not null, Check, Default, Primary key, foreign key and Composite primary key
    Primary Key: It doesn’t allow Null value (Empty) & Duplicate value
    Foreign Key: It allows Null value (Empty) & Duplicate value, Reference key of PK
    Composite Key: It has Multiple Column; we can create constraint more the one column