Oracle Interview Questions....

1. DIFFERENCE  between migration and upgaration mode ?

2. How to add the voting disk ?

3. How to find out the master node ?

4. How to take the OCR and Votingdisk backup ?

5. Which process will write  in to the alert log file ?

6. What you will see in the AWR report  ?

7. Why VIP ?

8. Types of Failovers ?

9.What is the use of change track file ?

10.What will happens when the rman lose the change track file ?

11 . Use of fal_client and fal_server ?

12 . Diff between max performacne and max avilability mode ?

13. Types of Oracle rac services ?

14. What will happens when arch2 process kills ?

15. Location of the CRS backup files ? how it will maintain the backups ?

16. Default optimizer mode ?

17. Diff. between analyzed table casecade and with out ?

18. What will happens when the index rebuild online and with out ?

19. Where you will find the node evictions ?

20. What is the save point in the flash back recovery ?

21. What is bindpeeking ?

22. Use of Histograms and types ?

23. what will happens in the adptive curser sharing ?

24. How "Real Time Appy" works ?

25. 11g Features ?

26. Where you will find out the datagaurd status ?

ans:
archived _gap
managed_staby
archive_log_files.
$dataguard_status
V$RECOVERY_PROGRESS

27. Active datagaurd feature in the 11g ?

28. What you will do if the standby database slow ?

29. After rebooting the server ...cluster shuld not reboot what parameter you shuld set ?

30. In the ASM what will happens when you will added the sum amount of disks?

31. Which command used to recover the block using rman ?

32. What are the base version for the 10 g and 11 g  oracle software ?

33. What is the option in the opatch to remove the 1 patch from the oracle software ?

34. How to diagnos the cluster node evictions ?

35. What is the "DB Sequencial Read " and  what action you will take if the awr top wait event always ?


36. How you find out the DG working properly or not ?

37 . If you received the archive destination reached 99% in your production database , what action you will take ?

38 . IF you define the delay parameter in the archive _dest what will happens ?

39. How to check the CRS backup ?

40 . Who will writes data into the databuffer cache ?

42. What Parameter you will use in the init.ora parameter to setup the DG high avilability mode ?

43. Why SCAN ?

44. what will happens when you will run the root.sh script ?

45. what type of errors you will get in the crs installation while running root.sh script ?

46. Use of diagnal.pl script ?

47. How the TableSpace Pointing Time recvery will works ?

48.In the RAC instance how you will configure the channels for rman backup ?

49.Diff types of protection modes in the DG ?

50. In the max protection mode the database how much time it will wait for the standby database if any network issues ?

51. Which method is good in DG  ? fail over or swith over ? why ?

52. Back ground process of cluster and rac use of them ?

53. What are the ASM backgroud process and Instant background process which is using the asm files ?

54. Types of redendancy in the rman and explain ?

55. Explain about the update process in the Database ?

56. In which mode of database operation rollback and rollfarward will happens ?

57. Newly created datafile missed there is no backup..how to recver ?

58. Sunday full backup and daily incremental backup in the evening  .Monday evening  incremental backup successfull , tuesday morning changetrack file deleted , what will happens to the tuesday evening backup ?

59. What are the RAC features in the 11g R2 ?

60. Two tables are similar size One session truncating the table and other session deleting the table , In the both tables zero rows.after that 2 sessions are selecting on that table ,which query will run fast and why ?

61.How rman will manages the consistancy in the backup  with out redo generation.

62. session  wait event showing like  idle event but blocking the other sessions ,what might be the cause ?

63. What is your approch  when the sunday  night jobs taking the long time  and monday morning p1 call ,what are the details get from the client and what are the diff ways you will check to find out the root cause. 

64. Which process will write  into the alert log file.

65. what is Adaptive cursor sharing .

66.  Use of SCAN ip and how it will work.

 

Role /datafile created Time and Column sizes............

Role Created Time..........

SELECT name AS role, ctime AS created FROM sys.user$

Check The column Size:

select num_rows from dba_tables where table_name='TSS_ORDER_LINES_F';

select AVG_COL_LEN from dba_tab_columns where COLUMN_NAME='WAYBILL_NUMBERS' and table_name='TSS_ORDER_LINES_F';

DataFile Addition date :


col file_name format a65
col tablespace_name format a25
set lin 120
select df.file_id, df.file_name, vdf.creation_time, (df.BYTES/1024)/1024 FILE_SIZE_MB
from dba_data_files df, v$datafile vdf
where df.tablespace_name like 'APPS_TS_SUMMARY'
and df.file_id=vdf.file#
ORDER BY df.tablespace_name, VDF.CREATION_TIME;

Build the Inventory NoteID's...................

MOS Doc 556834.1 - Steps To Recreate Central Inventory(oraInventory) In RDBMS Homes
MOS Doc 472854.1 - How to Recreate the OraInventory on UNIX Systems

Import with out Export dump using network Link..............

In the Target Host....


1 . Create a directory OS level and database level.

mkdir /oracle/export/ASSTEST

CREATE DIRECTORY ASSTEST AS '/oracle/export/ASSTEST';

2. Add the tnsentry of source Db.

create public database link ASSTEST CONNECT TO system IDENTIFIED BY asts4sprd using 'ASSTEST_BACKUP';

3.import your schema using below syntax.

 impdp system/manager99 parfile=schema_ASSTEST.par



vi schema_ASSTEST.par
=============================
DIRECTORY=ASSTEST
NETWORK_LINK=ASSTEST
PARALLEL=20
SCHEMAS=ANC_APPL,IR2_ADMIN,
EXCLUDE=STATISTICS
LOGFILE=ASSTEST.log trace=480300
JOB_NAME=ASSTEST1

To check High redo generation sessions .....

set lines 132
set pages 100
set time on
set timing on
col username format a20
col program format a30
spool redo_vol
--1) Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates
--   how much blocks have been changed by the session. High values indicate a
--   session generating lots of redo.
--   The query you can use is:
       SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
         FROM v$session s, v$sess_io i
        WHERE s.sid = i.sid
          AND i.block_changes > 200
        ORDER BY 5 desc, 1, 2, 3, 4;
--   Run the query multiple times and examine the delta between each occurrence
--   of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.
--2) Query V$TRANSACTION. This view contains information about the amount of
--   undo blocks and undo records accessed by the transaction (as found in the
--   USED_UBLK and USED_UREC columns).

--  The query you can use is:
      SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec
        FROM v$session s, v$transaction t
       WHERE s.taddr = t.addr
         AND t.used_ublk > 200
       ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;
--   Run the query multiple times and examine the delta between each occurrence
--   of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by
--   the session.
spool off

Locks Holders and Waiters..............

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,         
       id1, id2, lmode, request, type   
FROM V$LOCK  
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)   ORDER BY id1, request  ;