1). Tell me about yourself?
2). Explain your current working environment?
3). Explain about your backup strategies?
4). What is a latch and mutex?
5). What is cursor_sharing and what are it's parameters?
6). What is SCAN,OCR and Voting Disk
7) . What are ASM mandatory parameters?
8). What are wait classes?
9). What is row lock contention?
10). What is commit event? How to avoid it?
11). What is scattered read and sequential read?
12). How do you backup and manage your RMAN catalog database?
13). What is checkpoint? and CKPT background Process.
14). What is SMON/PMON
15). What is multi block read count?
16). What is the difference between ASH and AWR?
----------------------------------------------------------------------------------------------------------
2). Explain your current working environment?
3). Explain about your backup strategies?
4). What is a latch and mutex?
5). What is cursor_sharing and what are it's parameters?
6). What is SCAN,OCR and Voting Disk
7) . What are ASM mandatory parameters?
8). What are wait classes?
9). What is row lock contention?
10). What is commit event? How to avoid it?
11). What is scattered read and sequential read?
12). How do you backup and manage your RMAN catalog database?
13). What is checkpoint? and CKPT background Process.
14). What is SMON/PMON
15). What is multi block read count?
16). What is the difference between ASH and AWR?
----------------------------------------------------------------------------------------------------------
1. In DBWn :- What is DBW0 through DBW9, and then DBWA through DBWJ?
Oracle keeps frequently use Database blocks(table rows) in an area of the SGA(Computer RAM) known as the buffer cache.
When we do changes to to table data/block using Insert/Update/Delete statement the Oracle blocks become dirty.
In summary we can say that changed Oracle blocks in Buffer Cache are called DIRTY BLOCKS.
When we do changes to to table data/block using Insert/Update/Delete statement the Oracle blocks become dirty.
In summary we can say that changed Oracle blocks in Buffer Cache are called DIRTY BLOCKS.
The DBWR or DWWn process writes the Dirty block from Database Buffer Cache to Datafiles.
if there is very heavy DML(insert,update, delete) happening in database. Orace needs multiple DBWR process to fastly move dirty buffer to disk.
if there is very heavy DML(insert,update, delete) happening in database. Orace needs multiple DBWR process to fastly move dirty buffer to disk.
- For 11gr2 we can define 36 DBWR processes so 0-9 (total 10) and a-z (total 26)
- For 10g and 11gr1 we can define 20 DBWR processes so 0-9 (total 10) and a-j ( total 10)
- For 9i we can define 10 DBWR processes so 0-9 (total 10)
2. What is Row Chaining and Row Migration?
This happens when the data for a row in a table is too large to fit into a single data block.
Row Chaining - Occurs when the row is too large to fit into one data block when it is first inserted.
In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment.
Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG, LOB, etc.
Row chaining in these cases is unavoidable.
In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment.
Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG, LOB, etc.
Row chaining in these cases is unavoidable.
Row Migration - Occurs when a row that originally fitted into one data block is updated so that the overall
row length increases, and the block’s free space is already completely filled. In this case, Oracle migrates
the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle
preserves the original row piece of a migrated row to point to the new block containing the migrated row: the rowid
of a migrated row does not change.
row length increases, and the block’s free space is already completely filled. In this case, Oracle migrates
the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle
preserves the original row piece of a migrated row to point to the new block containing the migrated row: the rowid
of a migrated row does not change.
When a row is chained or migrated, performance associated with this row decreases because Oracle must read more
than one data block to get the information for that row.
than one data block to get the information for that row.
3. What is the meaning of the Redo thread?
In Non-RAC or regular database all Redo log file groups belong to one Instance/Database so the Redo Threads are always 1.
In RAC database each Instance of RAC has it own Redo Log File Groups and they are identified by Redo Thread numbers. For 3 node RAC it is (1,2,3)
A separate redo thread for each RAC instance avoids contention for a single set of redo log files, thereby reducing performance bottleneck.
A separate redo thread for each RAC instance avoids contention for a single set of redo log files, thereby reducing performance bottleneck.
select group#,thread#,members from v$log
4. What is the meaning of the Spatial in the enterprise edition?
Oracle Spacial is a feature in oracle used to store Maps, geography and Location data within database. Spatial is used by GPS, GIS(geographic information systems).
It is an optional component in Oracle Enterprise edition.
It is an optional component in Oracle Enterprise edition.
5. What is the hot standby database?
Its a sybase database concept. In hot standby database whatever transactions are done at Primary Database are simulataneously applied at Standby Database
6. What is the meaning of swapping and paging?
Unix OS divides its RAM into small pieces of memory called pages. Swapping is the process in which a page of memory is copied to the swap space on hard disk, to free up RAM. The combined sizes of the physical memory(RAM) and the swap space(Hard Disk Partition) is the amount of virtual memory available in the operating system.
With Paging a OS can store and retrieve data from Virtual Memory( swap space) for use in main memory.
7. What is the “starter” database during oracle software installation?
Starter Database is like any Oracle Database and is used to create a new database while installation Oracle Software.
8. Why we set extent management local during the manually database creation?
When we select Extent Management Local option during database creation, Oracle uses “Locally Managed Tablespace” instead of “Dictionary Managed Tablespace”.
Locally Managed Tablespace is a tablespace that manages its own extents by maintaining a bitmap(0/1 value) in each datafile to keep track of the free(0) or used(1) status of blocks in that datafile.
Benefits : Using Locally managed tablespace to track Free and Used Blocks in Datafiles does not generate rollback information because they do not update tables in the Data Dictionary and hence better performance.
Locally Managed Tablespace is a tablespace that manages its own extents by maintaining a bitmap(0/1 value) in each datafile to keep track of the free(0) or used(1) status of blocks in that datafile.
Benefits : Using Locally managed tablespace to track Free and Used Blocks in Datafiles does not generate rollback information because they do not update tables in the Data Dictionary and hence better performance.
9. Why we use Scope clause in alter system command like ALTER SYSTEM SET parameter_name = parameter_value
SCOPE = {MEMORY | SPFILE | BOTH};
Scope keyword of ALTER SYSTEM controls where you want to make the Parameter value change Only in MEMORY or in SPFILE or BOTH in MEMORY and SPFILE.
Note: If a parameter is dynamic examle memory_target we can use all three options MEMORY/SPFILE/BOTH depending on what we want to do.
If the parameter is static example memory_max_target we can use only SPFILE option.
------------------------------------------------------------------------------------------------------------------------------
Note: If a parameter is dynamic examle memory_target we can use all three options MEMORY/SPFILE/BOTH depending on what we want to do.
If the parameter is static example memory_max_target we can use only SPFILE option.
------------------------------------------------------------------------------------------------------------------------------
1. What is PCT Free and PCT Used, PCT_Increased, Dropped in User_Tables table.
PCT_FREE and PCT_USED oracle data block sizing parameters.
PCTINCREASE is oracle table/segment sizing parameter.
PCTFREE specifies % of space reserved for future updates.
PCTUSED specifies % of space below which the Oracle Block Space usage must fall to make it a Free Block.
PCTINCREASE specifies the % by which the 3rd and subsequent extent of a Table/Segment grows over the previous extent. This is true for Dictionary managed tablespace. Default value : 50%
In locally managed tablespaces, Oracle Database uses the value of PCTINCREASE during segment creation to determine the initial segment size and ignores this parameter during subsequent space allocation.
DROPPED column in user_tables indicates that the table is dropped and is in the recycle bin(YES/NO)
2. What is the use of v$fixed_tables in oracle?
v$fixed_tables is a dynamic view that stores the list of Oracle Internal Tables and Dynamic Performance Views. This is stored in Oracle Data Dictionary
3. How to get information / details about a tablespace?
select * from dba_tablespaces
select * from v$tablespace
4. Is there any other way to copy only the structure of a table except with a wrong condition like 1=2 ?
Yes it is possible to get the Table structure using DBMS_METADATA package.
Example: To get EMP table structure Run below sql in Sql Developer
select dbms_metadata.get_ddl(‘TABLE’,’EMP’,’SCOTT’) from dual;
5. Do all commands of SQL prompt not run in SQL developer, e.g. show parameter control_file.
You can only run Sql Queries in SQL Developer. To run show parameter we have to user SQLPLUS.
6. While using ‘select * from v$logmnr_contents’, it is asking that first it should be invoked. How to invoke it first?
To analyze Redo/Archived log files using log miner we have to build logminer dictionary and then specify Redo/Archived log files to analyze. Then only we can query v$logmnr_contents.
Refer to http://www.oracle-dba-online.com/log_miner.htm for examples
7. Does every process have its own trace file? (e.g. select * from v$process………… there are columns like PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM, do we need to manually allocate values to these variables, if yes, how to calculate them?)
Each Oracle Connection and corresponding process has its own trace files. Each Oracle Process has it own PGA( PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM ). Oracle allocates these PGA values as required by the Process.
PGA_AGGREGATE_TARGET specifies a shared PGA memory that is used by all Process.
8. What does $uname -a command do?
It’s a linux command that gives machine name, linux version and other details.
9. How to run / use DBNEWID (to rename a database)?
DBNEWId or nid is used to rename a database in MOUNT stage.
The command corresponding to DBNEWID is nid. It is present in $ORACLE_HOME/bin folder.
Steps to rename a database using nid tool :
a) SHUTDOWN IMMEDIATE
b) STARTUP MOUNT
c) nid TARGET=sys/password DBNAME=new_db_name
10. What does the word dbconsole mean?
Dbconsole is database console. It is the GUI screen that comes once we start the Oracle Enterprise Manager Database Control or Grid Control.
No comments:
Post a Comment