Question 1.: What is Log Switch?
Answer: The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.
Question 2. What are the steps involved in Instance Recovery?
Answer: Rolling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments.
Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a.
Releasing any resources (locks) held by transactions in process at the time of the failure.
Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.
Question 3. Do you need a commit after DDL statements?
Answer: DDL IS A AUTO COMMIT, YOU DON??T NEED TO COMMIT AGAIN
Question 4. How would you pass hints to the SQL processor?
Answer: USING COMMENT LINES WITH (+) SIGN YOU CAN PASS THE HINTS TO SQL ENGINE. For example à /* +PARALLEL( ) */
Question 5. what is the difference between group by and order by?
Answer: group by is used when we use aggregate functions on the columns in a query the other columns should be in group by query
select empno,ename,sum(sal) from emp
group by empno,ename
Order by is used to sort values either in ascending or descending order
Question 6. A DBA had to remove some Archivelogs to free up space in filesystem. Now when the RMAN job starts to backup Archivelogs, it complains about missing Archivelogs that were deleted by DBA. To resolve the issue and continue backing up remainder of Archivelogs, which RMAN command can be used so it won't complain about missing Archivelogs.
Answer: Crosscheck command
Question 7. Which RMAN command is used to create an exact replica of a database in new host?
Answer: DUPLICATE DATABASE
Question 8. How do you install Statspack?
Answer: By running $ORACLE_HOME/rdbms/admin/spcreate.sql script
Question 9. Process you follow to start looking into Performance issue at database level (If the application is running very slow, at what points do you need to go about the database in order to improve the performance?)
· Run a TOP command in Unix to see CPU usage (identify CPU killer processes)
· Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and memory usage and possible blocking
· Run STATSPACK report to identify:
1. TOP 5 WAIT EVENTS
2. RESOURCE intensive SQL statements
· See if STATISTICS on affected tables needs to be re-generated
· IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and see whether new index or use of HINT brings the cost of SQL down.
Question 10. Explain below wait events in STATSPACK report DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE
Answer: DB SCATTERED READ - FULL TABLE SCAN
DB SEQUENTIAL REAL - IO
ENQUEUE - LOCKING
Question 11. List five most important parameter in 9i affecting performance
DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE
Question 12. What is PGA_AGGREGATE_TARGET?
Answer: This parameter controls the maximum amount of memory PGA which can be used by the queries when WORKAREA_SIZE_POLICY is set to Auto.
The value you can be set in Bytes, kilobytes (K), megabytes (M) or gigabytes (G). The default value is 0
This parameter also has an effect on the execution plans of the cost based optimizer. The optimizer uses the value of the parameter PGA_AGGREGATE_TARGET to derive an estimate for the minimum and maximum amount of memory which should be available at run-time for each sort, hash-join and bitmap operator in the query. Based on this minimum and maximum value, the optimizer selects the best plan.
Question 13. How do you analyze table partition using Oracle provided package?
Answer: DBMS_STATS.GATHER_TABLE_STATS with GRANULARITY => 'PARTITION' OPTION
Question 14. You see a wait on LMS process in statspack, what does that mean?
Answer: A. Wait is due to Data Guard Broker.
Question 15. Name three advisory statistics you can collect.
Question 16. Explain procedure to Change CHARACTERSET of a database.
Answer: Can't change CHARACTERSET of a database, you will need to re-create the database with appropriate CHARACTERSET.
Question 17. If you had a tablespace, TEST_TABLESPACE, which consists of three files:
TEST01.dbf, TEST02.dbf, and TEST03.dbf, and someone accidentally used the Unix command "rm" to delete the file TEST02.dbf, what else would you need in order to recover all the data thatwas present in TEST_TABLESPACE at the time that TEST02.dbf was deleted?
Answer: All Archivelogs
Question 18. How do you put database is ARCHIVELOG mode, explain procedure
1. Modify init.ora parameter START_ARCHIVE=TRUE
2. SQL> SHUTDOWN IMMEDIATE;
3. STARTUP MOUNT;
4. ALTER DATAVASE ARCHIVELOG;
5. ALTER DATABASE OPEN;
Question 19. How can you tell if an index on particular table is USED or NOT USED in 9i?
Answer: By turning MONITORING ON that index and querying into INDEX_USAGE table
Question 20. Explain FORCE LOGGING feature in 9i.
Answer: By setting FORCE LOGGING to TRUE, all transactions will generate REDO. This is used for Data Guard, so no data will be missed (i.e. transactions that were run with NOLOGGING option) in Standby database.
Get More Questions and Answers with Explanation at Oracle Forums.