DBV utility can be used to check. RMAN can be used to solve it.
dbms_repair package check_object ,SKIP_CORRUPT_BLOCKS,FIX_CORRUPT_BLOCKS,DUMP_ORPHAN_KEYS
2)what is the use of running root.sh while installing oracle in linux :
Creates oratab file and grants some permissions
3)how much value set for SHMMAX :
1/2 of RAM
4)how much you allocate SGA when ur RAM 8 GB :
4 GB
6)what is the default level of statspack : 5
7)which command you use to start the Database at the server startup itself :
Specify the database in oratab and use init.d file
8)what is the command to check the available database in linux
: ps -ef|grep smon
9)what is ORA-1555 : snapshot too old-Transaction header of undo is replaced by another transacrion
10)Did u come across any deadlock ORA-60 : Yes
11)scenario: if the file system is full due to the tablespace growth,as a DBA what you do or how you rectify the problem?
add one more datafile to the tablesapce in different moutpoint
12)how you monitor the database healh check
Using scripts and statspack ADDM/AWR and alert log
13)what are the top 5 timed events you have seen in statspack :
I/O events, CPU time, log file and control file parallel write etc
14)scenario:
table A contains 100 records starting from 1.....100 with primary key
table B contain 500 records with out primary key i want to export 100 records of table A to B, what you will do.> Insert into table b select * from table a
15)how to check the row migration and row chaining
chain_cnt from user_tables
(OR) ANALYZE TABLE tab1 LIST CHAINED ROWS; Then query Chained_Rows table(UTLCHAIN.SQL), table fetch continued row in V$SYSSTAT view OR STATSPACK.
16)what is the precaution you take to avoid row chaining and row migration.
: Use higher pctfree for row migration, LARGE BLOCK SIZE FOR ROW CHAINING
17)DB Architechture
18)which process will write from datafile to DB buffer cache : server process
19) what are the operations happening when a user issue an SQL statement?
20)if DB uses in backup mode & suddenly got crashed.what we will have to on startup : v$backup, end backup
21)Instance recovery and transaction recovery.operations happening there : roll forward and roll backward
22)how to set undo management auto? : undo_management= auto and undo_tbs
23)how to kill the oracle process from unix with a command :
kill o kill -9
24)fragmentation on table and fragmentation on index. How to resolve it :
reorganize index and recreate table
25)TYpes of rman Backups :
full and incremental(cumulative,differentiative)
26)How to remove last recently created 5 files in unix :
ls -tr | tail -4 Will give you the last 4 files , to remove ls -Q1rt | tail -4 | xargs rm
27)Select * from emp where wmpno=10...where empno<10....where empno>10;select * from emp what will be the difference in explain plan
--> index scan, index range scan, index range scan, full table scan
29)Parameter to be set for sqltrace at instance level or user level
==>sql_trace
30)200 Mb commited data and 200 Mb uncommitted data in buffer cache .what will happen if we to do 1000 Mb of update?
31)DB crashed & during opening its asking for recovery on datafile 1. what are the steps to do? ==> Apply archived logs and redo
32)DB cloning steps
33)optimal value for SHMMAx : depends on SGA size
34) full steps of installation to DB creation in unix:
As root user create groups and user, set kernel values, as Oracle user start install
35)what are the main areas to look in Statspack report?
load profile, instance efficiency, wait events and Sql part
36)shell script for hot backup
37)Undo tb recvery when DB is up with out shutting down?
38)How to find out what a session was doing last 2 hours? : ASH can be used(gv$active_session_history)
39)difference b/w AWR and statspack : Automatic stats gathering, ASH
40)AWK command in unix?
-----------------------------------------------------
41)session going slow.How to proceed? : Check dba_waiters , DBA_lockers, wait events and other locks
42)if we open and change enties in spfile means ,will it work? Dont edit spfile, its a binary file created and maintained by oracle
43)From a export dump file,how you see the data with out import==>show=y
44) How you speed up the import?-commit=N,increase Buffer value,index=n,indexfile=filename
45)Diff b/w LMT and DMT => Datab dictioanry managed and managed by bit maps
46)with out backup of datafile, how u recover the datafile online:
47)how to set the limits to the user-profiles: /etc/limits ==> ulimit
48)what is the impact of buffer during the export? : Size of evaluating buffer
49)what is DBfile sequential read,scattered read,latch free, busybufferwait,log buffer wait? All are wait events
50)what is cost based and rule based? Cost is based on statistics and rule is based on predefined set of 15 rules
51)how you recover the single datafile in RMAN? recover datafile
52)how you find out the number of CPU in linux server? cat /proc/cpuinfo
53)what is the difff b/w db_file and data_file : init.ora parameter for max no of files and any file of a tbs.
54)how do you create materualized view and how do you create materialized view log? create materilized view and create mat view log
55)what are the inner join available in Oracle==> nested loop,sortmerge,Hashjoin
56)Types of index-Bitmap,B-tree,function index, reverse key index,
57)what is the diff b/w dedicated server,MTS ==> dedicated server process for every user process and a pool of server process
58)how to recover the datafile which is not included in the Backup
59)10g importatnt feauture : expdp, asm,assm,grid, oem grid control, awr,addm,ash,tarnsportable tablespace, sysaux, recycle bin and other flash back features etc
60)why datapump faster compare to Normal imp/exp : use pl/sql for data transfer, server side, oracle directory , resumable feature
61) how you do block recovery in 10g
62)how to enable block track changing
63)what is the diff b/w latch and enqueue
64)what are the diff tpye of latches
65)what are the types of locks-row lock and exclusive lock
66)what are the steps for upgrade 10g db
67)what is the comparision of compute and estimate statisctics: full and a sampling of stats
68)how to interprete the Sql trace 0/p file. how to trace another session? dbms_system,dbms_monitor,dbms_support)=>tkprof
69)RMAN 10g Feauture-Change_block_tracking
70)Diff b.w LMT and DMT locally (bit map) and dictionary managed
71)Transportable TTBS : exp feature
72)How to verify the backup with out restot=ring-Restore validate database
73)Suppose you have 4GB RAM. how will u configure a database? Size of SGA and PGA.(SHMMAX shoul be half of RAM and SGA should be 80% of SHMMAX)-OLTP OLAP 50-50%
74)In LMT, it maintain bitmap. where the bitmaps stored in the datafiles- first 3 blocks of datafile
75) what is vmstat,netstat,iostat => unix system tools
76)what is recursive calls => internal sql stmts used by oracle to process suer stmts
77)what is blevel,height,cordinality of index ==>
78)we have 25G and 120 GB of DB ,In 120GB 20 GB table is dropped. how can you get the table without shutdown => partial clone
79)which SCN will update whether lower bound or upper bound?
80)what ae the components of forms an report
81)I have production database and it is register in the repository.i want to register the database with same DBID and DBNAMe.
==========================================================================================================
How do you identify the top memory consuming processes in unix ?
ps aux | head -1 ; ps aux | sort -rn +3 | head
How do you identify the top CPU consuming processes in unix ?
ps aux | head -1 ; ps aux | sort -rn +2 | head -10
How do you identify the number of CPU, machine name, total physical memory, os version, and patch level in unix or windows?
cat /proc/cpuinfo|grep processor|wc –l
How do you identify if paging/swapping occurring in unix ?
How do you identify disk(i/o) performance in unix or windows ?
How do you identify the CPU performance in unix or windows ?
How do you identify the total physical memory size of the server ?
How do you identify how many cpus the server has and what is the CPU speed?
How do you identify the operating version and the patch level and the hardware model ?
How do you identify how much free space is left for disk /u01/oracle ?
How do you identify the total size of a directory /u01/oracle/10.2.0 ?
How do you kill a running os process ?
How do you identify all the processes owned by unix user account oracle ?
Give me the command to identify the CPU run queue, IO waits, CPU Blocked queue, diskreads/s ?
What is asynch i/o ? How do you disable it or enable it ? What is direct i/o ?
Tell me about the RAID levels you worked with ? Which RAID level you suggest for OLTP and DW systems ?
Tell me about you experience with storage solutions ?
Write a SQL Query to retrieve the session id by passing the operating system process id as the input parameter ?
Write a SQL Query to retrieve the OS process id by passing the SID as the input parameter ?
Write a SQL Query to retrieve the SQL_TEXT by passing the SID as the input parameter ?
Write a SQL Query to retrieve the session waits by passing the SID as the input parameter ?
Write a SQL Query to retrieve the session wait events by passing the SID as the input parameter ?
Write a SQL Query to retrieve the session sorting details(how much sorting space is used) by passing the SID as the input parameter ?
Write a SQL Query to retrieve the sid, login time with exact hours and minutes, serial#, machine, module, program by passing the SID as the input parameter ?
Write a SQL Query to retrieve all the server processes from v$session ?
Write a SQL Query to retrieve all the client processes from v$session ?
Give me the syntax for tkprof to generate explain plan and to sort the outfile with elapsed timings ?
Give me the syntax for enabling and disabling trace for a running session by passing the SID as the input parameter ?
Give me the syntax for to generate SADC formatted SAR report ? Tell me what is SADC ? When do you use it ?
How do you identify free space, used space,total space for each tablespace in the database ?
How do you install and setup the statspack ? Give me the brief steps ?
How do you automate the snapshots of the statspack ?
How do you de-install the statspack ?
How do you generate the statspack report ?
What are the things you check in the statspack report ?
How do you purge statspack data ?
Explain me about the concept of rollback segment OPTIMAL,WRAPS AND EXTENDS ?
How do you know if there is a rollback contention ?
How do you know if full tablescans are occuring ?
How do you know if a session is sorting or not ?
What is "db file scattered read" and "db file sequential read" ?
What is lock mode no 6 ?
1: null,
2: Row Share (SS),
3: Row Exclusive (SX),
4: Share (S),
5: Share Row Exclusive (SSX) and
6: Exclusive(X)
What is ora-3113 ?
End of communication channel
What is the difference between latch and enqueue ?
Can you describe me about some database events and when do you try to track those ?
How do you enable a specific event at database level ?
How do you identify the top bad sqls in the database ?
How do you identify where the database i/o is occuring ? I mean which datafiles and tables are getting max i/o hits ?
How do you diagnose and fix the block corruption in the database ?
How does the archiving and purging technology helps database performance ? When do you suggest this solution ?
How do you enable the trace on a running session ? How do you identify the trace file for a session ?
How do I run the tkprof ? What are the various options you use with tkprof ?
How do you identify the sql statement for a session ?
How do you get the sql explain in tkprof file ?
How do you sort the expensive sqls using tkprof ?
How do you identify the os process id if I know the oracle SID ?
How do you setup the statspack ?
How do you purge the statspack data ?
How do you automate the statspack snapshots ?
How do you drop the statspack from the database ?
How do you kill a running session ?
How do you identify the sid of a session ?
How do you identify the sorting in the database ?
What are the three different methods for enabling the trace ?
What are the various options you can use for tkprof and explain plan ?
What is parallel query? How do you disable the parallel query at database level ? How do you run a sql command with parallel query option ?
What is row chaining and row migration ? How do you identify,troubleshoot and solve this issue ?
What is data block header ?
What is enqueue ?
What is latch ? Can you name some latches which you noticed in the databases you managed ?
What is db_block_lru_latches ?
What file system you prefer for redo-logs ?
Explain me about snapshot too old error and how you fix this error ?
Explain me about table and index fragmentation ? How do you identify and fix those issues ?
Explain me about how you rebuild a highly fragmented database ?
Explain me about the difference between locally vs dictionary managed tablespaces ?
Explain me about bitmap,btree and function based indexes ?
Explain me about partitioning and when do you suggest partitioning ?
Tell me about hash,list,range partitioning ?
Tell me about pga_aggregate_target ?
What database view you use to track the PGA and SGA current usage ?
Explain me about the roll forward and rollback during the instance startup ?
Explain me about how the table definition settings pct_free,pct_used,freelists,transactions can affect the performance ?
Give me Top 10 Init database parameters which will affect the performance ?
Tell me about your experience with statspack ? How do you resolve the instance performance issues using statspack ?
What are the things you check in the statspack report ?
What is the snap_level in statspacks ?
What is AWR and ADDM ? How do you turn on the AWR data collection ?
Give me about some memory advisor views which are available in 10g ?
Tell me about your experience with 10g Grid Monitoring ?
What are the various events you monitor in the database proactively ?
How do you monitor the database issues which can only monitored reactively?
How do you identify the long running transaction in the database ?
v$session_longops
Can you identify which rollback segment a session is using ?
What is v$session_longops ? When do you look at this view ?
What are the various things you were able to monitor with 10g Grid monitoring ?
Can you implement SLA monitoring, Response time monitoring using 10g Grid ?
How do you identify the blocking sessions and waiting sessions in the database ?
How do you identify the pl/sql code which is a candidate for pinning into the sharedpool ?
Tell me about when do you use multiple block sizes for different tablespaces and the steps to implement this ?
Tell me about how you identify the data skewness ? How you fix it ?
Tell me about your experience with histograms ? When do you use histograms ?
What is a global index and what is a local index ?
How do you rebuild a table without writing it to the redo-log files ? What option should be disabled at the database level in order to do this ?
What is optimizer_index_cost_adj ? How does it control the full table scans and optimizer access paths ?
What is stripe size setting in RAID ? How does it affect the performance ?
How do you determine which blocksize is appropriate for OLTP and DW ?
How do you determine the value for db_multi_block_read_count ?
How do you identify if a package is currently being accessed by session ?
How do you determine the instance level total wait events in the database ?
How do you check/monitor if there is hot block contention occurring in the db?
Explain me about steps for setting up materialized views with fast refresh option?
What query_rewrite_enabled? What does it do?
Which sql script can be used to create the plan_table table?
Which sql script can be used to create the chained_rows table ?
Which sql script can be used to format the explain plan outputs ?
select column_name,comments from dict_columns
where table_name='V$LOCK'
/
