Question related to Oracle database:
What is an Oracle Instance?
What information is stored in Control File?
When you start an Oracle DB which file is accessed first?
What is RDBMS?
Question related to Memory:
What is the Job of SMON, PMON processes?
What is a Checkpoint?
Which Process reads data from Datafiles?
Which Process writes data in Datafiles?
What is a Shared Pool?
What is PGA_AGGREGRATE_TARGET parameter?
Large Pool is used for what?
What is DB Buffer Cache Advisor?
Can you change SHARED_POOL_SIZE online?
When creating a new database, how would you handle the memory allocation?
Name some memory areas Oracle uses,and state their purpose?
What is difference between Multithreaded/Shared Server and Dedicated Server?
How does 32-bit Oracle compare with 64-bit in terms of memory restrictions?
What are the efects of using bind variables?
How would you ensure that a table’s blocks stayed in the SGA?
Why would a DBA pin packages into memory?
Explain what the parameter session_cached_cursors is used for?
What is kept in the Database Buffer Cache?
Question related to Transactions:
Explain how Oracle achieves read consistency?
Explain an ORA-01555 / Snapshot too old error and how you might prevent them?
What happens when a commit is issued?
What is an Oracle Instance?
What information is stored in Control File?
When you start an Oracle DB which file is accessed first?
What is RDBMS?
Question related to Memory:
What is the Job of SMON, PMON processes?
What is a Checkpoint?
Which Process reads data from Datafiles?
Which Process writes data in Datafiles?
What is a Shared Pool?
What is PGA_AGGREGRATE_TARGET parameter?
Large Pool is used for what?
What is DB Buffer Cache Advisor?
Can you change SHARED_POOL_SIZE online?
When creating a new database, how would you handle the memory allocation?
Name some memory areas Oracle uses,and state their purpose?
What is difference between Multithreaded/Shared Server and Dedicated Server?
How does 32-bit Oracle compare with 64-bit in terms of memory restrictions?
What are the efects of using bind variables?
How would you ensure that a table’s blocks stayed in the SGA?
Why would a DBA pin packages into memory?
Explain what the parameter session_cached_cursors is used for?
What is kept in the Database Buffer Cache?
Question related to Transactions:
Explain how Oracle achieves read consistency?
Explain an ORA-01555 / Snapshot too old error and how you might prevent them?
What happens when a commit is issued?
A commit is a “end transaction” marker and make permanent all changes performed in the transaction. A transaction is a sequence of SQL statements that Oracle Database treats as a single unit.
What happens when a Rollback hapens?
Some Backup and Recovery Questions:
What is written in Redo Log Files?
How many maximum Redo Logfiles one can have in a Database?
Explain the differences between an export, a hot backup and a cold backup and the benefits associated with each?
What is COMPRESS and CONSISTENT setting in EXPORT utility?
What is the difference between Direct Path and Convention Path loading?
What files are required to be included in a complete hot/online backup?
Explain the differences between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages of each?
Can you take partial backups if the Database is running in NOARCHIVELOG mode?
Can you take Online Backups if the the database is running in NOARCHIVELOG mode?
How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?
You cannot shutdown the database for even some minutes, then in which mode you should run the database?
How can you backup a control file?
What are redo logs? Explain their importance?
What happens duiring a instance recover?
Explain what effect “NOLOGGING” commands have on the database? How would this affect standby databases and point in time recovery?
A large amount of data has been deleted by a user of an application. The data can’t be re-entered via the application because it’s not all available. What options would you investigate them?
A datafile has been deleted while the database was open and being used. What would you do?
Scenario: An RMAN backup is multiplexing all of the datafiles to one tape drive. (Filesperset>1 channels >1). What are the implications? How would you improve the situation?
Can you import objects from Oracle ver. 7.3 to 9i?
Scenario: A busy database only has a full backup to tape each day using RMAN. 250 GB of archivelogs are generated per day. What are the implications? How would you improve the situation?
Where should you place Archive logfiles, in the same disk where DB is or another disk?
Can you take online backup of a Control file if yes, how?
What is a Logical Backup?
Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?
Why do you take tablespaces in Backup mode?
What is the advantage of RMAN utility?
How RMAN improves backup time?
Can you take Offline backups using RMAN?
How do you see information about backups in RMAN?
What is a Recovery Catalog?
Should you place Recovery Catalog in the Same DB?
Can you use RMAN without Recovery catalog?
Can you take Image Backups using RMAN?
Can you use Backupsets created by RMAN with any other utility?
Where RMAN keeps information of backups if you are using RMAN without Catalog?
You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?
Which is more efficient Incremental Backups using RMAN or Incremental Export?
Can you start and shutdown DB using RMAN?
How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?
You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?
You lost some datafiles and you don’t have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?
How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?
You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?
You loss controlfile how do you recover from this?
The current logfile gets damaged. What you can do now?
What is a Complete Recovery?
What is Cancel Based, Time based and Change Based Recovery?
Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?
Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?
A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don’t have any backups?
How do you recover from the loss of a controlfile if you have backup of controlfile?
Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?
Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?
Have you faced any emergency situation? Tell us how you resolved it?
At one time you lost parameter file accidentally and you don’t have any backup. How you will recreate a new parameter file with the parameters set to previous values.
Some Lock and Latches Questions:
What are latches used for?
What happens when a session has to wait for a latch?
Explain what deadlocks are and how to avoid them?
When do indexes help to reduce lock contention?
Some Storage Questions:
What is Automatic Management of Segment Space setting?
How do you control number of Datafiles one can have in an Oracle database?
How many Maximum Datafiles can there be in an Oracle Database?
Can you make a Datafile auto extendible? If yes, how?
What is a Locally Managed Tablespace?
Can you redefine a table Online?
Explain the relationship between a data block, an extent and a segment?
.What is the difference between Range Partitioning and Hash Partitioning?
What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
How do you move tables from one tablespace to another tablespace?
How do you add a datafile to a tablespace?
How do you resize a datafile?
What view would you use to look at the size of a datafile?
What view would you use to determine free space in a (non-tempfile) tablespace?
What is PCT Increase setting?
Describe what the PCT_FREE setting does.
What are chained rows and migrated rows? What could you do to limit their effect on performance?
Some Performance Questions:
When should you rebuild indexes?
Can you built indexes online?
Can you see Execution Plan of a statement?
How would you determine what session’s arer connected and what resources they are waiting for?
How can you gather statistics on a table?
How often you should collect statistics for a table?
How do you collect statistics for a table, schema and Database?
Can you make collection of Statistics for tables automatic?
How can you enable a trace for a session?
How would you go about generating an EXPLAIN plan?
How would you go about increasing the buffer cache hit ratio?
Name some Oracle performance tools or sources of statistics?
How would you begin investigating a performance problem, beginning from a user’s complaint?
What is Cost Based Optimization?
What is STATSPACK tool?
Can you assign Priority to users?
Some Data Warehouse Questions:
What characterises a data warehouse as opposed to an OLTP database?
Which Oracle features are commonly useful to data warehouse databases?
Briefly describe how each of the features from the previous question work?
What tools might be used for ETL?
Some Development Support Questions:
List and describe typical environments used in the development life of an application? (Eg. Production, development, QA,etc)
How would you manage the promotion of changes through the environments to ensure success when released into production?
How can a DBA help developers to design an application?
Explain the differences between a FUNCTION, PROCEDURE, PACKAGES and anonymous PL/SQL blocks?
After an import of a schema has finished, what should you check before notifying the users that the database can be used?
Give two types of referential integrity constraints?
How would you create a copy of a database with new name on a different server?
You have just compiled a PL/SQL package but got some errors. How would you view the errors?
On which columns you should create Indexes?
What types of Indexes are available in Oracle?
What is B-Tree Index?
A table is having few rows, should you create indexes on this table?
A Column is having many repeated values which type of index you should create on this column, if you have to?
What is an Index Organized Table?
What is a Global Index and Local Index?
Some Questions about RAC:
Briefly describe what RAC is, and what the intended benefits are?
What are two essential RAC hardware components?
What are two methods of RAC load balancing?
What does cache fusion mean?
What is the voting disk for?
What is the hangcheck timer for in Linux?
What performance issues should be considered when migrating an application to RAC?
Some Replication Questions:
Describe some types of Oracle Replication?
What is the difference between a complete refresh and a fast refresh?
Some Unix/Linux Questions:
How do you see how many instances are running?
How do you automate starting and shutting down of databases in UNIX?
How do you list the files in an Unix directory while also showing hidden files?
You have written a script to take backups. How do you make it run automatically every week?
How do you execute a UNIX command in the background?
What is OERR utility?
How do you see Virtual Memory Statistics in Linux?
How do you see how much hard disk space is free in Linux?
What is SAR?
What is SHMMAX?
Swap partition must be how much the size of RAM?
What is DISM in Solaris?
How do you see how many memory segments are acquired by Oracle Instances?
How do you see which segment belongs to which database instances?
What is VMSTAT, IOSTAT and NETSTAT?
How would you change all ocurrences of a value using VI?
How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?
Give two kernel parameters that affect an Oracle install?
How do you remove Memory segments?
What is the difference between Soft Link and Hard Link?
What is stored in oratab file?
What unix command will control the default file permissions when files are created?
Give the command to display space usage on the UNIX file system?
Explain the read, write, and execute permissions on an UNIX directory?
How do you see how many processes are running in UNIX?
How do you kill a process in UNIX?
Can you change priority of a Process in UNIX?
Briefly, how do you install Oracle software on UNIX?
Miscellaneous Questions:
How do you delete duplicate rows in a table?
Which default Database roles are created when you create a Database?
Name three files that can be used to configure SQL*Net for server and client?
What problems are associated with the use of generic user ids?
Where would you look for errors from the database engine?
An application is generating an ORA-00600 error in the database. What would you do about it?
Give two examples of how you might determine the structure of the table “DEPT”?
What is the difference between an pfile and spfile?
Compare TRUNCATE and DELETE for a table?
Give the stages of instance startup to a usable state where normal users may access it?
When creating a user, what are the minimun permissions that must be granted to allow them to connect to the database?
How would you find out what a particular pfile does?
What methods can you use to compact an index?
A table has been identified as needing reorganization. How would you plan for it? How would you implement it?
A user can’t loggin to a remote Oracle database via an application using SQL*Net from their PC. What would you do to determine the cause?
A database is not shutting down 15 minutes after the shutdown immediate command was issued. What would you do?
An error appears about a table that can’t extend due to insufficient space in the tablespace. What would you do?
You have been asked to write the requeriments for an automatic “health check” system for a database. What things would you include?
You have been presented with a UNIX database server, and have been logged in as the root user. How would you find the databases and obtain access to them?
Can you audit SELECT statements?
What does DBMS_FGA package do?
You want users to change their passwords every 2 months. How do you enforce this?
Can you disable and enable Primary key?
What are latches used for?
What happens when a session has to wait for a latch?
Explain what deadlocks are and how to avoid them?
When do indexes help to reduce lock contention?
Some Storage Questions:
What is Automatic Management of Segment Space setting?
How do you control number of Datafiles one can have in an Oracle database?
How many Maximum Datafiles can there be in an Oracle Database?
Can you make a Datafile auto extendible? If yes, how?
What is a Locally Managed Tablespace?
Can you redefine a table Online?
Explain the relationship between a data block, an extent and a segment?
.What is the difference between Range Partitioning and Hash Partitioning?
What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
How do you move tables from one tablespace to another tablespace?
How do you add a datafile to a tablespace?
How do you resize a datafile?
What view would you use to look at the size of a datafile?
What view would you use to determine free space in a (non-tempfile) tablespace?
What is PCT Increase setting?
Describe what the PCT_FREE setting does.
What are chained rows and migrated rows? What could you do to limit their effect on performance?
Some Performance Questions:
When should you rebuild indexes?
Can you built indexes online?
Can you see Execution Plan of a statement?
How would you determine what session’s arer connected and what resources they are waiting for?
How can you gather statistics on a table?
How often you should collect statistics for a table?
How do you collect statistics for a table, schema and Database?
Can you make collection of Statistics for tables automatic?
How can you enable a trace for a session?
How would you go about generating an EXPLAIN plan?
How would you go about increasing the buffer cache hit ratio?
Name some Oracle performance tools or sources of statistics?
How would you begin investigating a performance problem, beginning from a user’s complaint?
What is Cost Based Optimization?
What is STATSPACK tool?
Can you assign Priority to users?
Some Data Warehouse Questions:
What characterises a data warehouse as opposed to an OLTP database?
Which Oracle features are commonly useful to data warehouse databases?
Briefly describe how each of the features from the previous question work?
What tools might be used for ETL?
Some Development Support Questions:
List and describe typical environments used in the development life of an application? (Eg. Production, development, QA,etc)
How would you manage the promotion of changes through the environments to ensure success when released into production?
How can a DBA help developers to design an application?
Explain the differences between a FUNCTION, PROCEDURE, PACKAGES and anonymous PL/SQL blocks?
After an import of a schema has finished, what should you check before notifying the users that the database can be used?
Give two types of referential integrity constraints?
How would you create a copy of a database with new name on a different server?
You have just compiled a PL/SQL package but got some errors. How would you view the errors?
On which columns you should create Indexes?
What types of Indexes are available in Oracle?
What is B-Tree Index?
A table is having few rows, should you create indexes on this table?
A Column is having many repeated values which type of index you should create on this column, if you have to?
What is an Index Organized Table?
What is a Global Index and Local Index?
Some Questions about RAC:
Briefly describe what RAC is, and what the intended benefits are?
What are two essential RAC hardware components?
What are two methods of RAC load balancing?
What does cache fusion mean?
What is the voting disk for?
What is the hangcheck timer for in Linux?
What performance issues should be considered when migrating an application to RAC?
Some Replication Questions:
Describe some types of Oracle Replication?
What is the difference between a complete refresh and a fast refresh?
Some Unix/Linux Questions:
How do you see how many instances are running?
How do you automate starting and shutting down of databases in UNIX?
How do you list the files in an Unix directory while also showing hidden files?
You have written a script to take backups. How do you make it run automatically every week?
How do you execute a UNIX command in the background?
What is OERR utility?
How do you see Virtual Memory Statistics in Linux?
How do you see how much hard disk space is free in Linux?
What is SAR?
What is SHMMAX?
Swap partition must be how much the size of RAM?
What is DISM in Solaris?
How do you see how many memory segments are acquired by Oracle Instances?
How do you see which segment belongs to which database instances?
What is VMSTAT, IOSTAT and NETSTAT?
How would you change all ocurrences of a value using VI?
How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?
Give two kernel parameters that affect an Oracle install?
How do you remove Memory segments?
What is the difference between Soft Link and Hard Link?
What is stored in oratab file?
What unix command will control the default file permissions when files are created?
Give the command to display space usage on the UNIX file system?
Explain the read, write, and execute permissions on an UNIX directory?
How do you see how many processes are running in UNIX?
How do you kill a process in UNIX?
Can you change priority of a Process in UNIX?
Briefly, how do you install Oracle software on UNIX?
Miscellaneous Questions:
How do you delete duplicate rows in a table?
Which default Database roles are created when you create a Database?
Name three files that can be used to configure SQL*Net for server and client?
What problems are associated with the use of generic user ids?
Where would you look for errors from the database engine?
An application is generating an ORA-00600 error in the database. What would you do about it?
Give two examples of how you might determine the structure of the table “DEPT”?
What is the difference between an pfile and spfile?
Compare TRUNCATE and DELETE for a table?
Give the stages of instance startup to a usable state where normal users may access it?
When creating a user, what are the minimun permissions that must be granted to allow them to connect to the database?
How would you find out what a particular pfile does?
What methods can you use to compact an index?
A table has been identified as needing reorganization. How would you plan for it? How would you implement it?
A user can’t loggin to a remote Oracle database via an application using SQL*Net from their PC. What would you do to determine the cause?
A database is not shutting down 15 minutes after the shutdown immediate command was issued. What would you do?
An error appears about a table that can’t extend due to insufficient space in the tablespace. What would you do?
You have been asked to write the requeriments for an automatic “health check” system for a database. What things would you include?
You have been presented with a UNIX database server, and have been logged in as the root user. How would you find the databases and obtain access to them?
Can you audit SELECT statements?
What does DBMS_FGA package do?
You want users to change their passwords every 2 months. How do you enforce this?
Can you disable and enable Primary key?
No comments:
Post a Comment