1. How many memory layers are in the shared pool?
2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
3. How can you tell how much space is left on a given file system and how much space each of the file system’s subdirectories take-up?
df -h
du -h
4. Define the SGA and how you would configure SGA for a mid-sized OLTP environment? What is involved in tuning the SGA?
5. What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
6. Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
7. How do you tell what your machine name is and what is its IP address?
1.uname -n
2.ifconfig -a
8. How would you go about verifying the network name that the local_listener is currently using?
LSNRCTL> show current_listener
Current Listener is LISTENER
LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domain.net)(PORT=1521)))
LISTENER parameter “log_status” set to ON
The command completed successfully
LSNRCTL>
9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
10. What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?
11. What is the recommended interval at which to run statspack snapshots, and why?
12. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
13. Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
17. How would you best determine why your MVIEW couldn’t FAST REFRESH?
18. How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?
19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
20. How would you begin to troubleshoot an ORA-3113 error?
ORA-03113 –> An unexpected end-of-file was processed on the communication channel. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down. This message could occur when any of the following commands have been issued:
ALTER SYSTEM KILL SESSION … IMMEDIATE
ALTER SYSTEM DISCONNECT SESSION … IMMEDIATE
SHUTDOWN ABORT/IMMEDIATE/TRANSACTIONAL
If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error that requires the assistance of Oracle Support Services.
21. Which dictionary tables and/or views would you look at to diagnose a locking issue?
1.v$locked_object
2.v$lock
22. An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed”, how do you approach troubleshooting this issue?
23. How would you extract DDL of a table without using a GUI tool?
Use DBMS_METADATA package and use GET_DDL procedure.
24. You’re getting high “busy buffer waits” - how can you find what’s causing it?
25. What query tells you how much space a tablespace named “test” is taking up, and how much space is remaining?
26. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
I think it looks like Archive log destination is full.
1.If you have seperate backup script for archive log please use that.
2.Or move some archive log fils to diffrent location to get free space.
3.Try to connect the DB now.
27. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle’s. What database recovery options are available? Database is in archive log mode.
28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).
29. How do you increase the OS limitation for open files (LINUX and/or Solaris)?
30. Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.
31. Explain how you would restore a database using RMAN to Point in Time?
Shut down the target database if it is open
Mount the target database
Check the format of NLS_LANG & NLS_DATE_FORMAT variables
Start RMAN and connect to target database
run{
set until time ’specify the time u want to recover the database
upto’;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
32. How does Oracle guarantee data integrity of data changes?
33. Which environment variables are absolutely critical in order to run the OUI?
ORACLE_HOME, ORACLE_SID,ORACLE_BASE,PATH & LD_LIBRARY_PATH
34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
SELECT username, COUNT(*) count
FROM v$session
GROUP BY username;
35. Why does Oracle not permit the use of PCTUSED with indexes?
This is because an index is a complex data structure, not randomly organized like a heap table ( a normal table u get when u type create table syntax) Data must go where it ‘belongs’. Unlike a heap where blocks are sometimes available for inserts, blocks are always available for new entries in an index. If the data belongs on a given block because of its values, it will go there regardless of how full or empty the block is.
PCTFREE will reserve space on a newly created index, but not for subsequent operations on it for much the same reason as why
PCTUSED is not used at all.
The same thigh is true even for index organized tables too…
36. What would you use to improve performance on an insert statement that places millions of rows into that table?
1.Disable the constraint.
2.Drop the non-unique indexes.
3.Set Undo tablespace properly.
4.Create a big redo log groups.
37. What would you do with an “in-doubt” distributed transaction?
38. What are the commands you’d issue to show the explain plan for “select * from dual”?
39. In what script is “snap$” created? In what script is the “scott/tiger” schema created?
40. If you’re unsure in which script a sys or system-owned object is created, but you know it’s in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
42. You create a private database link and upon connection, fails with: ORA-2085: connects to . What is the problem? How would you go about resolving this error?
43. I have my backup RMAN script called “backup_rman.sh”. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
44. Explain the concept of the DUAL table.
Dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X. Like
SQL> desc dual
Name Null? Type
———————– ——– —————-
DUMMY VARCHAR2(1)
SQL> select * from dual;
D
-
X
45. What are the ways tablespaces can be managed and how do they differ?
.Dictionary managed and locally managed.
Dictionary managed.
——————-
Here To allocate next extent it gets free blocks info from data dictionary every time, it’s a i/o contention issue.
Locally managed.
—————
In Locally managed tablespace free blocks information is available as bitmap in data file headers. No need to go dictionary.
SEGMENT SPACE MANAGEMENT AUTO option is still useful with LMT
46. From the database level, how can you tell under which time zone a database is operating?
47. What’s the benefit of “dbms_stats” over “analyze”?
48. Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
49. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.
How do you return the top-N results of a query in Oracle? Why doesn't the obvious method work?
Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don't get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:
SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 5;
Describe the Oracle Wait Interface, how it works, and what it provides. What are some limitations? What do the db_file_sequential_read and db_file_scattered_read events indicate?
The Oracle Wait Interface refers to Oracle's data dictionary for managing wait events. Selecting from tables such as v$system_event and v$session_event give you event totals through the life of the database (or session). The former are totals for the whole system, and latter on a per session basis. The event db_file_sequential_read refers to single block reads, and table accesses by rowid. db_file_scattered_read conversely refers to full table scans. It is so named because the blocks are read, and scattered into the buffer cache.
Every DBA should know something about the operating system that the database will be running on. The questions here are related to UNIX but you should equally be able to answer questions related to common Windows environments.
1. How do you list the files in an UNIX directory while also showing hidden files?
ls -ltra
2. How do you execute a UNIX command in the background?
Use the "&"
3. What UNIX command will control the default file permissions when files are created?
Umask
4. Explain the read, write, and execute permissions on a UNIX directory.
Read allows you to see and list the directory contents.
Write allows you to create, edit and delete files and subdirectories in the directory.
Execute gives you the previous read/write permissions plus allows you to change into the directory and execute programs or shells from the directory.
5. the difference between a soft link and a hard link?
A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system.
6. Give the command to display space usage on the UNIX file system.
df -lk
7. Explain iostat, vmstat and netstat.
Iostat reports on terminal, disk and tape I/O activity.
Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat reports on the contents of network data structures.
8. How would you change all occurrences of a value using VI?
Use :%s///g
9. Give two UNIX kernel parameters that effect an Oracle install
SHMMAX & SHMMNI
10. Briefly, how do you install Oracle software on UNIX.
Basically, set up disks, kernel parameters, and run orainst.
2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
3. How can you tell how much space is left on a given file system and how much space each of the file system’s subdirectories take-up?
df -h
du -h
4. Define the SGA and how you would configure SGA for a mid-sized OLTP environment? What is involved in tuning the SGA?
5. What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
6. Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
7. How do you tell what your machine name is and what is its IP address?
1.uname -n
2.ifconfig -a
8. How would you go about verifying the network name that the local_listener is currently using?
LSNRCTL> show current_listener
Current Listener is LISTENER
LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname.domain.net)(PORT=1521)))
LISTENER parameter “log_status” set to ON
The command completed successfully
LSNRCTL>
9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
10. What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?
11. What is the recommended interval at which to run statspack snapshots, and why?
12. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
13. Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
17. How would you best determine why your MVIEW couldn’t FAST REFRESH?
18. How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?
19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
20. How would you begin to troubleshoot an ORA-3113 error?
ORA-03113 –> An unexpected end-of-file was processed on the communication channel. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down. This message could occur when any of the following commands have been issued:
ALTER SYSTEM KILL SESSION … IMMEDIATE
ALTER SYSTEM DISCONNECT SESSION … IMMEDIATE
SHUTDOWN ABORT/IMMEDIATE/TRANSACTIONAL
If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error that requires the assistance of Oracle Support Services.
21. Which dictionary tables and/or views would you look at to diagnose a locking issue?
1.v$locked_object
2.v$lock
22. An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed”, how do you approach troubleshooting this issue?
23. How would you extract DDL of a table without using a GUI tool?
Use DBMS_METADATA package and use GET_DDL procedure.
24. You’re getting high “busy buffer waits” - how can you find what’s causing it?
25. What query tells you how much space a tablespace named “test” is taking up, and how much space is remaining?
26. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
I think it looks like Archive log destination is full.
1.If you have seperate backup script for archive log please use that.
2.Or move some archive log fils to diffrent location to get free space.
3.Try to connect the DB now.
27. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle’s. What database recovery options are available? Database is in archive log mode.
28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).
29. How do you increase the OS limitation for open files (LINUX and/or Solaris)?
30. Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.
31. Explain how you would restore a database using RMAN to Point in Time?
Shut down the target database if it is open
Mount the target database
Check the format of NLS_LANG & NLS_DATE_FORMAT variables
Start RMAN and connect to target database
run{
set until time ’specify the time u want to recover the database
upto’;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
32. How does Oracle guarantee data integrity of data changes?
33. Which environment variables are absolutely critical in order to run the OUI?
ORACLE_HOME, ORACLE_SID,ORACLE_BASE,PATH & LD_LIBRARY_PATH
34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
SELECT username, COUNT(*) count
FROM v$session
GROUP BY username;
35. Why does Oracle not permit the use of PCTUSED with indexes?
This is because an index is a complex data structure, not randomly organized like a heap table ( a normal table u get when u type create table syntax) Data must go where it ‘belongs’. Unlike a heap where blocks are sometimes available for inserts, blocks are always available for new entries in an index. If the data belongs on a given block because of its values, it will go there regardless of how full or empty the block is.
PCTFREE will reserve space on a newly created index, but not for subsequent operations on it for much the same reason as why
PCTUSED is not used at all.
The same thigh is true even for index organized tables too…
36. What would you use to improve performance on an insert statement that places millions of rows into that table?
1.Disable the constraint.
2.Drop the non-unique indexes.
3.Set Undo tablespace properly.
4.Create a big redo log groups.
37. What would you do with an “in-doubt” distributed transaction?
38. What are the commands you’d issue to show the explain plan for “select * from dual”?
39. In what script is “snap$” created? In what script is the “scott/tiger” schema created?
40. If you’re unsure in which script a sys or system-owned object is created, but you know it’s in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
42. You create a private database link and upon connection, fails with: ORA-2085: connects to . What is the problem? How would you go about resolving this error?
43. I have my backup RMAN script called “backup_rman.sh”. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
44. Explain the concept of the DUAL table.
Dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X. Like
SQL> desc dual
Name Null? Type
———————– ——– —————-
DUMMY VARCHAR2(1)
SQL> select * from dual;
D
-
X
45. What are the ways tablespaces can be managed and how do they differ?
.Dictionary managed and locally managed.
Dictionary managed.
——————-
Here To allocate next extent it gets free blocks info from data dictionary every time, it’s a i/o contention issue.
Locally managed.
—————
In Locally managed tablespace free blocks information is available as bitmap in data file headers. No need to go dictionary.
SEGMENT SPACE MANAGEMENT AUTO option is still useful with LMT
46. From the database level, how can you tell under which time zone a database is operating?
47. What’s the benefit of “dbms_stats” over “analyze”?
48. Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
49. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.
How do you return the top-N results of a query in Oracle? Why doesn't the obvious method work?
Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don't get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:
SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 5;
Describe the Oracle Wait Interface, how it works, and what it provides. What are some limitations? What do the db_file_sequential_read and db_file_scattered_read events indicate?
The Oracle Wait Interface refers to Oracle's data dictionary for managing wait events. Selecting from tables such as v$system_event and v$session_event give you event totals through the life of the database (or session). The former are totals for the whole system, and latter on a per session basis. The event db_file_sequential_read refers to single block reads, and table accesses by rowid. db_file_scattered_read conversely refers to full table scans. It is so named because the blocks are read, and scattered into the buffer cache.
Every DBA should know something about the operating system that the database will be running on. The questions here are related to UNIX but you should equally be able to answer questions related to common Windows environments.
1. How do you list the files in an UNIX directory while also showing hidden files?
ls -ltra
2. How do you execute a UNIX command in the background?
Use the "&"
3. What UNIX command will control the default file permissions when files are created?
Umask
4. Explain the read, write, and execute permissions on a UNIX directory.
Read allows you to see and list the directory contents.
Write allows you to create, edit and delete files and subdirectories in the directory.
Execute gives you the previous read/write permissions plus allows you to change into the directory and execute programs or shells from the directory.
5. the difference between a soft link and a hard link?
A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system.
6. Give the command to display space usage on the UNIX file system.
df -lk
7. Explain iostat, vmstat and netstat.
Iostat reports on terminal, disk and tape I/O activity.
Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat reports on the contents of network data structures.
8. How would you change all occurrences of a value using VI?
Use :%s///g
9. Give two UNIX kernel parameters that effect an Oracle install
SHMMAX & SHMMNI
10. Briefly, how do you install Oracle software on UNIX.
Basically, set up disks, kernel parameters, and run orainst.
No comments:
Post a Comment