1. Explain briefly about Oracle database architecture?
Oracle database architecture is a combination of instance and database. Instance is a
combination of memory structures and background processes which helps in reading and
writing the data to/from the database .
2. Which background process is used during user connectivity?
PMON
3. What is difference between oracle SID and Oracle service name?
Oracle SID is the unique name that uniquely identifies your instance/database where as
the service name is the TNS alias can be same or different as SID.
4. What are bind variables?
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area.
This avoids a hard parse each time, which saves on various locking and latching resource
we use to check object existence and so on.
5. What are base tables? When and how they will get created?
Base tables are dictionary information of the database. They will be created at the time of
database creation using SQL.BSQ script.
6. What are different views DBA uses? Which script will create them?
We use data dictionary views to look into permanent information about the database and
dynamic performance views to get ongoing actions in the database. Both the views will be
created after database creation using catalog.sql script.
7. Why to execute catproc.sql script?
It will create necessary packages and procedures which DBA use for certain actions.
8. Explain the phases of SQL execution
SQL execution contains 2 phases.
Parsing – in which syntax checking, semantic checking and dividing the statement into
literals will be done
Execution – in which parsed statement will get converted into ASCII format and will
be executed
Fetch – in which data will be fetched either from database buffer cache or database.
9. What is mean by semantic checking? Which component helps in that?
Semantic checking means checking for the privileges for the user or in other words authorizing the user.Base tables or dictionary will help in doing this.
10. What is the difference between data block/extent/segment?
A data block is the smallest unit of logical storage for a database object. As objects grow
they take chunks of additional storage that are composed of contiguous data blocks. These
groupings of contiguous data blocks are called extents. All the extents that an object
takes when grouped together are considered the segment of the database object.
12. What is the difference between PGA and UGA?
When you are running dedicated server then process information stored inside the process
global area (PGA) and when you are using shared server then the process information stored
inside user global area (UGA)
13. What is server process?
It is a process created to help the user process either in reading/writing the data in the database.
14. What is the difference between physical and logical read?
If we fetch data from database buffer cache, then its called logical read. If we fetch it from
database, its called physical read as it includes an I/O operation.
15. Why to maintain a copy of data in database buffer cache?
When the same query is ran by same or different user, data can be picked from buffer cache
thus avoiding I/O and improving performance.
16. Why server process will not start searching from MRU end?
If server process starts searching at MRU end, there is a chance that data may get flush
from buffer cache by the ime it reaches LRU end. In that case again to fetch data, we need
to do an I/O which is costly. So oracle designed its architecture that server process will
search only from LRU end.
17. What are the logical structures of the database?
Tablespace, segment, extent and oracle data block are logical structures.
18. What are the 4 mandatory tablespaces req to run the database?
SYSTEM, SYSAUX, TEMP and UNDO.
19. Can I have a database without SYSAUX tablespace in 10g?
Yes, but load will be more on system tablespace.
20. If we have physical structures, why do we need logical structures?
Logical strutures are defined to provide the easeness in maintenance.
21. Explain the difference between a block, extent and segment?
Block is a basic storage unit where as extent is a small memory area allocated to a table
and segment is a object which occupies space.
22. What are redo entries? When and where they will be created?
A single atomic chance happened to the database is called redo entry. They will be created
when we run any DML or DDL commands or when any changes are done in the database.
They will be created in PGA.
23. What is different status for a block in database buffer cache?
UNUSED, FREE, PINNED and DIRTY.
24. What is write-ahead protocol?
LGWR writing before DBWR is called write-ahead protocol.
25. Will there be any undo generated in case of DDL statement processing? If so why?
Yes. Reason is even tough it is a DDL externally, it will be DML to base tables and to
rollback them always oracle requires undo.
26. What is PGA?
PGA is a memory area which is used to store user’s exclusive information like session
information and helps in executing a query.
27. What is the difference between memory allocation in 8i and 9i for PGA?
In 8i, we need to set different individual parameters where as in 9i setting only one
parameter PGA_AGGREGATE_TARGET would be enough
28. Which is correct? Sorting will take place in PGA or temp tablespace?
Both. If the data which to be sorted is more than sort area size of PGA, then temp
tablespace will be used.
29. How you will define INSTANCE and DATABASE?
INSTANCE is a combination of memory structures and background processes which
helps in reading/writing the data. DATABASE is mix of physical and logical structures
which helps in storing user data.
30. What is SGA?
SGA is a combination of different memory structures which helps in several actions in the database.
31. What are the responsibilities of SMON?
It will do instance recovery, coalesces the tablespace and will release the temp segments
occupied by a transaction when it is completed.
32. Why oracle maintains 2 redolog files?
To avoid any space constraints.
33. What is SCN and which process writes that?
SCN is a unique number assigned to a transaction which modifies data in the database.
CKPT process will always update this SCN to both datafiles header and control file.
35. What information control file contains?
It contains latest SCN, all locations and sizes of datafiles and redolog files, database
creation date and timestamp and controlfile parameters.
36. What is server parameter file and how it is different from parameter file?
Spfile is a binary file and it helps in changing parameters efficiently than a pfile.
37. What is Ifile and when it is used?
Ifile is a index file which can help database when pfile or spfile are not in default location.
38. How to recover if I lost parameter pfile or spfile?
We can recover from alert log file which contains non-default parameters.
39. If we have both pfile and spfile in place, which file oracle will use during startup?
Spfile.
40. What are hidden parameters and their use?
Hidden parameters are to be used only on recommendation of oracle support and
sometimes they help us in providing work around to any serious problem in the database.
41. What is the purpose of password file?
It is used to authenticate any user is connecting as SYSDBA from a remote machine.
Even though there is a password file, still I observed that any user is being able to
connect as sysdba
42. While creating password file, what is the use of ENTRIES?
To specify how many users with sysdba role can connect to the database remotely.
43. What is ASMM? Which background process helps in that?
Setting SGA_TARGET parameter is called ASMM. This helps in managing all SGA
components automatically and MMAN is the background process helps in that.
44. How database will behave when you have both ASMM and individual parameters are configured?
Values of individual parameters will act as minimum and ASMM as maximum.
You increased the SGA_MAX_SIZE parameter to a higher value. But when trying to increase
45. SGA_TARGET, its throwing error that “it cannot increase”. What might be the reason?
Kernel parameter SHMMAX may be reached. We should increase that first.
46. Which parameter helps ASMM to be affective? What is the value it should be set to?
SGA_TARGET and value is dependent on no of transactions in the database.
47. What is AMM in 11g?
Managing both SGA and PGA automatically by oracle is called Automatic Memory
Management (AMM).
48. My database size is 1 TB, how much SGA will you configure?
SGA size is not dependant on database size, it depends on how many transactions
happening in the database.
49. What is alert log? How it is helpful for DBA?
It is file which helps in diagnosing all the errors occurred in the database.
50. What are the contents of alert log file?
Database startup and shutdown times, non-default parameters, any logical or physical
structural changes and alloracle errors etc.
51. What happens if we remove alert log file when database is in use?
No effect on the database functionality. Oracle will create a new alert log file.
52. If we have alert log file to diagnose the problem, why we need trace files? What are they?
Alert log itself cannot provide complete information about the error, in which case it
will generate trace file. Depends on the error, it will generate background, core or
user trace files.
53. Which background process is responsible for writing into alert log file?
All the background processes are responsible.
54. Which process will start first when instance is started?
PSP0, process spawner. This background process will start other processes like PMON,
SMON etc. But in alert log first it will show PMON.
55. Which file is req to place database in NOMOUNT state and what happens in that phase?
Pfile or spfile is required to bring database to NOMOUNT state and instance will be
started (backgroung processes will be started and memory will be allocated to SGA
from RAM) in this phase.
56. Which file is req to bring into MOUNT phase and what is the use of this phase?
Control file is required and it is for maintenance of some database actions.
57. What files are needed to open the database and How oracle knows the locations of them?
Datafiles and redolog files are required to open the database and oracle will get that
information from controlffiles.
58. What you will do when SHUTDOWN IMMEDIATE command hanging for last 30 min?
We can open another terminal and issue shut abort. Then once again startup and do shut immediate.
59. What could be reason for SHUTDOWN IMMEDIATE command hanging for long time?
Might be there is a transaction which is large and rollback is happening for the same.
60. What is server result cache? What benefit we get out of it?
It is a new component introduced in 11g. It will store the rows of a query directly thus avoids the need of executing a statement.
61. Explain how a select statement will get the benefit by using server result cache?
Read how a select statement will process in 11g from class notes.
62. What parameters to be used to make 11g database use server result cache?
SERVER_RESULT_CACHE.
63. How to increase the size of server result cache?
By increasing the size specified by SERVER_RESULT_CACHE or by increasing MEMORY_TARGET parameter if AMM is used.
64. Where all the trace files located in 11g?
It is in a single location defined by DIAG_DEST parameter.
Which background process clears sessions and releases locks when system fails?
SMON (here system means database).
65. What is OFA? Do you recommend to implement it?
It is a rule which says database related files should split across multiple disks and yes
i will recommend to implement it.
66. Why oracle recommends OFA? How it is related to OS?
As one disk will have only one I/O header, it will be burden to the database if we place
all the files in single hard disk. So oracle recommends OFA.
67. As a DBA, when you will take decision to enable shared server architecture?
When we observed ORA-04030 or ORA-04031 errors very frequently in alert log file.
68. You configured all the parameters to enable shared server architecture, but still users facing memory allocation issues. What you would do?
We need to check if SERVER=SHARED is mentioned in client TNS entry or not.
69. What are the parameters you use to configure shared server architecture?
DISPATCHERS
MAX_DISPATCHERS
SHARED_SERVER_PROCESSES
MAX_SHARED_SERVER_PROCESSES.
70. How many slave process we can have for DBWR and SMON?
DBWR – 20, SMON – 16 .
71. I connected to database as sysdba, but its not allowing me to shutdown the database,
what may be the reason?
The connection would have been shared server connection in which case oracle will not
allow to shutdown/startup the database.
72. What are the components of Physical database structure of Oracle Database?
ORACLE database is comprised of three types of files. One or more Data files, two are more Redo Log files, and one or more Control files.
73. What are the components of Logical database structure of ORACLE database?
Tablespaces and the Database's Schema Objects.
74. What is a Tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.
75. What is SYSTEM tablespace and When is it Created?
Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
76. Explain the relationship among Database, Tablespace and Data file.
Each databases logically divided into one or more tablespaces One or more data files are explicitly created for each tablespace.
77. What is schema?
A schema is collection of database objects of a User.
78. What are Schema Objects ?
Schema objects are the logical structures that directly refer to the database's data. Schema
objects include tables,views,sequences,synonyms, indexes, clusters, database triggers,
procedures,functions packages and database links.
79. Can objects of the same Schema reside in different tablespaces.?
Yes.
80. Can a Tablespace hold objects from different Schemes ?
Yes.
81. What is Table ?
A table is the basic unit of data storage in an ORACLE database. The tables of a database
hold all of the user accessible data. Table data is stored in rows and columns.
Thank you
Oracle database architecture is a combination of instance and database. Instance is a
combination of memory structures and background processes which helps in reading and
writing the data to/from the database .
2. Which background process is used during user connectivity?
PMON
3. What is difference between oracle SID and Oracle service name?
Oracle SID is the unique name that uniquely identifies your instance/database where as
the service name is the TNS alias can be same or different as SID.
4. What are bind variables?
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area.
This avoids a hard parse each time, which saves on various locking and latching resource
we use to check object existence and so on.
5. What are base tables? When and how they will get created?
Base tables are dictionary information of the database. They will be created at the time of
database creation using SQL.BSQ script.
6. What are different views DBA uses? Which script will create them?
We use data dictionary views to look into permanent information about the database and
dynamic performance views to get ongoing actions in the database. Both the views will be
created after database creation using catalog.sql script.
7. Why to execute catproc.sql script?
It will create necessary packages and procedures which DBA use for certain actions.
8. Explain the phases of SQL execution
SQL execution contains 2 phases.
Parsing – in which syntax checking, semantic checking and dividing the statement into
literals will be done
Execution – in which parsed statement will get converted into ASCII format and will
be executed
Fetch – in which data will be fetched either from database buffer cache or database.
9. What is mean by semantic checking? Which component helps in that?
Semantic checking means checking for the privileges for the user or in other words authorizing the user.Base tables or dictionary will help in doing this.
10. What is the difference between data block/extent/segment?
A data block is the smallest unit of logical storage for a database object. As objects grow
they take chunks of additional storage that are composed of contiguous data blocks. These
groupings of contiguous data blocks are called extents. All the extents that an object
takes when grouped together are considered the segment of the database object.
12. What is the difference between PGA and UGA?
When you are running dedicated server then process information stored inside the process
global area (PGA) and when you are using shared server then the process information stored
inside user global area (UGA)
13. What is server process?
It is a process created to help the user process either in reading/writing the data in the database.
14. What is the difference between physical and logical read?
If we fetch data from database buffer cache, then its called logical read. If we fetch it from
database, its called physical read as it includes an I/O operation.
15. Why to maintain a copy of data in database buffer cache?
When the same query is ran by same or different user, data can be picked from buffer cache
thus avoiding I/O and improving performance.
16. Why server process will not start searching from MRU end?
If server process starts searching at MRU end, there is a chance that data may get flush
from buffer cache by the ime it reaches LRU end. In that case again to fetch data, we need
to do an I/O which is costly. So oracle designed its architecture that server process will
search only from LRU end.
17. What are the logical structures of the database?
Tablespace, segment, extent and oracle data block are logical structures.
18. What are the 4 mandatory tablespaces req to run the database?
SYSTEM, SYSAUX, TEMP and UNDO.
19. Can I have a database without SYSAUX tablespace in 10g?
Yes, but load will be more on system tablespace.
20. If we have physical structures, why do we need logical structures?
Logical strutures are defined to provide the easeness in maintenance.
21. Explain the difference between a block, extent and segment?
Block is a basic storage unit where as extent is a small memory area allocated to a table
and segment is a object which occupies space.
22. What are redo entries? When and where they will be created?
A single atomic chance happened to the database is called redo entry. They will be created
when we run any DML or DDL commands or when any changes are done in the database.
They will be created in PGA.
23. What is different status for a block in database buffer cache?
UNUSED, FREE, PINNED and DIRTY.
24. What is write-ahead protocol?
LGWR writing before DBWR is called write-ahead protocol.
25. Will there be any undo generated in case of DDL statement processing? If so why?
Yes. Reason is even tough it is a DDL externally, it will be DML to base tables and to
rollback them always oracle requires undo.
26. What is PGA?
PGA is a memory area which is used to store user’s exclusive information like session
information and helps in executing a query.
27. What is the difference between memory allocation in 8i and 9i for PGA?
In 8i, we need to set different individual parameters where as in 9i setting only one
parameter PGA_AGGREGATE_TARGET would be enough
28. Which is correct? Sorting will take place in PGA or temp tablespace?
Both. If the data which to be sorted is more than sort area size of PGA, then temp
tablespace will be used.
29. How you will define INSTANCE and DATABASE?
INSTANCE is a combination of memory structures and background processes which
helps in reading/writing the data. DATABASE is mix of physical and logical structures
which helps in storing user data.
30. What is SGA?
SGA is a combination of different memory structures which helps in several actions in the database.
31. What are the responsibilities of SMON?
It will do instance recovery, coalesces the tablespace and will release the temp segments
occupied by a transaction when it is completed.
32. Why oracle maintains 2 redolog files?
To avoid any space constraints.
33. What is SCN and which process writes that?
SCN is a unique number assigned to a transaction which modifies data in the database.
CKPT process will always update this SCN to both datafiles header and control file.
35. What information control file contains?
It contains latest SCN, all locations and sizes of datafiles and redolog files, database
creation date and timestamp and controlfile parameters.
36. What is server parameter file and how it is different from parameter file?
Spfile is a binary file and it helps in changing parameters efficiently than a pfile.
37. What is Ifile and when it is used?
Ifile is a index file which can help database when pfile or spfile are not in default location.
38. How to recover if I lost parameter pfile or spfile?
We can recover from alert log file which contains non-default parameters.
39. If we have both pfile and spfile in place, which file oracle will use during startup?
Spfile.
40. What are hidden parameters and their use?
Hidden parameters are to be used only on recommendation of oracle support and
sometimes they help us in providing work around to any serious problem in the database.
41. What is the purpose of password file?
It is used to authenticate any user is connecting as SYSDBA from a remote machine.
Even though there is a password file, still I observed that any user is being able to
connect as sysdba
42. While creating password file, what is the use of ENTRIES?
To specify how many users with sysdba role can connect to the database remotely.
43. What is ASMM? Which background process helps in that?
Setting SGA_TARGET parameter is called ASMM. This helps in managing all SGA
components automatically and MMAN is the background process helps in that.
44. How database will behave when you have both ASMM and individual parameters are configured?
Values of individual parameters will act as minimum and ASMM as maximum.
You increased the SGA_MAX_SIZE parameter to a higher value. But when trying to increase
45. SGA_TARGET, its throwing error that “it cannot increase”. What might be the reason?
Kernel parameter SHMMAX may be reached. We should increase that first.
46. Which parameter helps ASMM to be affective? What is the value it should be set to?
SGA_TARGET and value is dependent on no of transactions in the database.
47. What is AMM in 11g?
Managing both SGA and PGA automatically by oracle is called Automatic Memory
Management (AMM).
48. My database size is 1 TB, how much SGA will you configure?
SGA size is not dependant on database size, it depends on how many transactions
happening in the database.
49. What is alert log? How it is helpful for DBA?
It is file which helps in diagnosing all the errors occurred in the database.
50. What are the contents of alert log file?
Database startup and shutdown times, non-default parameters, any logical or physical
structural changes and alloracle errors etc.
51. What happens if we remove alert log file when database is in use?
No effect on the database functionality. Oracle will create a new alert log file.
52. If we have alert log file to diagnose the problem, why we need trace files? What are they?
Alert log itself cannot provide complete information about the error, in which case it
will generate trace file. Depends on the error, it will generate background, core or
user trace files.
53. Which background process is responsible for writing into alert log file?
All the background processes are responsible.
54. Which process will start first when instance is started?
PSP0, process spawner. This background process will start other processes like PMON,
SMON etc. But in alert log first it will show PMON.
55. Which file is req to place database in NOMOUNT state and what happens in that phase?
Pfile or spfile is required to bring database to NOMOUNT state and instance will be
started (backgroung processes will be started and memory will be allocated to SGA
from RAM) in this phase.
56. Which file is req to bring into MOUNT phase and what is the use of this phase?
Control file is required and it is for maintenance of some database actions.
57. What files are needed to open the database and How oracle knows the locations of them?
Datafiles and redolog files are required to open the database and oracle will get that
information from controlffiles.
58. What you will do when SHUTDOWN IMMEDIATE command hanging for last 30 min?
We can open another terminal and issue shut abort. Then once again startup and do shut immediate.
59. What could be reason for SHUTDOWN IMMEDIATE command hanging for long time?
Might be there is a transaction which is large and rollback is happening for the same.
60. What is server result cache? What benefit we get out of it?
It is a new component introduced in 11g. It will store the rows of a query directly thus avoids the need of executing a statement.
61. Explain how a select statement will get the benefit by using server result cache?
Read how a select statement will process in 11g from class notes.
62. What parameters to be used to make 11g database use server result cache?
SERVER_RESULT_CACHE.
63. How to increase the size of server result cache?
By increasing the size specified by SERVER_RESULT_CACHE or by increasing MEMORY_TARGET parameter if AMM is used.
64. Where all the trace files located in 11g?
It is in a single location defined by DIAG_DEST parameter.
Which background process clears sessions and releases locks when system fails?
SMON (here system means database).
65. What is OFA? Do you recommend to implement it?
It is a rule which says database related files should split across multiple disks and yes
i will recommend to implement it.
66. Why oracle recommends OFA? How it is related to OS?
As one disk will have only one I/O header, it will be burden to the database if we place
all the files in single hard disk. So oracle recommends OFA.
67. As a DBA, when you will take decision to enable shared server architecture?
When we observed ORA-04030 or ORA-04031 errors very frequently in alert log file.
68. You configured all the parameters to enable shared server architecture, but still users facing memory allocation issues. What you would do?
We need to check if SERVER=SHARED is mentioned in client TNS entry or not.
69. What are the parameters you use to configure shared server architecture?
DISPATCHERS
MAX_DISPATCHERS
SHARED_SERVER_PROCESSES
MAX_SHARED_SERVER_PROCESSES.
70. How many slave process we can have for DBWR and SMON?
DBWR – 20, SMON – 16 .
71. I connected to database as sysdba, but its not allowing me to shutdown the database,
what may be the reason?
The connection would have been shared server connection in which case oracle will not
allow to shutdown/startup the database.
72. What are the components of Physical database structure of Oracle Database?
ORACLE database is comprised of three types of files. One or more Data files, two are more Redo Log files, and one or more Control files.
73. What are the components of Logical database structure of ORACLE database?
Tablespaces and the Database's Schema Objects.
74. What is a Tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.
75. What is SYSTEM tablespace and When is it Created?
Every ORACLE database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
76. Explain the relationship among Database, Tablespace and Data file.
Each databases logically divided into one or more tablespaces One or more data files are explicitly created for each tablespace.
77. What is schema?
A schema is collection of database objects of a User.
78. What are Schema Objects ?
Schema objects are the logical structures that directly refer to the database's data. Schema
objects include tables,views,sequences,synonyms, indexes, clusters, database triggers,
procedures,functions packages and database links.
79. Can objects of the same Schema reside in different tablespaces.?
Yes.
80. Can a Tablespace hold objects from different Schemes ?
Yes.
81. What is Table ?
A table is the basic unit of data storage in an ORACLE database. The tables of a database
hold all of the user accessible data. Table data is stored in rows and columns.
Thank you
No comments:
Post a Comment