Listener and Tuning

      Question

      1. Need to export a database using exp from putty even we get a network issue. (????****)
        Answer
      2. Import a schema which is in tablespace USERS to another schema which is in tablespace USERS2?
        remap_tablespace=users:users2 Answer
      3. how to goback to client mode from interactive mode in datapump?
        EXPORT>continue_client Answer
      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 Answer
      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. Answer
      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
        Answer
      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.
        Answer
      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
        Answer
      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;
        Answer
      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.
        Answer
      11. How to register a database to a listener?
        To be set SID in a listener.ora file (or) by using nertcaAnswer
      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
        Answer
      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;
        Answer
      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
        Answer
      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) */
        Answer
      16. How to intrepret execution plan?
        Execution plan read from right most top (or) Inner most top Answer
      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
        Answer
      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..
        Answer
      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)
        Answer
      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
        Answer





      <Previous>
      <Next>