What Is a Database ?
Ans : Database is a structure that stores information about
multiple types of entities, the attributes (or characteristics) of the
entities, and the relationships among the entities. It also
contains data types for attributes and indexes.
contains data types for attributes and indexes.
Which type of storage system is Oracle?
What other storage systems are available? | Oracle DBA
Ans: Oracle is a type of Relational Database
Management System (RDBMS). Another available storage system is
Hierarchical Storage Management system, such as Information Management System (IMS)
from IBM and Integrated Database Management System (IDMS) from Computer
Associates.
Define Database Management System (DBMS) and RDBMS. | Oracle DBA
DBMS is a program that defines the rules for data
storage and retrieval. RDBMS is a special type of DBMS that stores the
data in the relational format as described in the relational theory by E.F.
Codd.
How is Oracle different from other programming languages? | Oracle DBA
Oracle or any other DBMS is a program that handles user
requests (data retrieval, storage, or modification requests) without any
requirement to specify the algorithm to do so.
Give some examples of join methods. | Oracle DBA
Some
examples of join methods are given as follows:
i. Merge join — Sorts both the joining tables by using the join key and then merges the sorted rows.
ii. Nested loop join — Applies filter conditions specific to the outer table and gets a result set. After that, it joins the inner table with the result set using either an index or a full table scan.
iii. Hash join — Uses hash algorithm for filter conditions on smaller table first and then performs the same hashing algorithm on the other table for joined columns. After that, it returns the matching rows.
i. Merge join — Sorts both the joining tables by using the join key and then merges the sorted rows.
ii. Nested loop join — Applies filter conditions specific to the outer table and gets a result set. After that, it joins the inner table with the result set using either an index or a full table scan.
iii. Hash join — Uses hash algorithm for filter conditions on smaller table first and then performs the same hashing algorithm on the other table for joined columns. After that, it returns the matching rows.
Which sorting algorithm does Oracle follow? | Oracle DBA
Oracle used to follow a balanced binary tree sorting
algorithm to effectively build an n-memory index on the incoming data.
Binary tree search places a huge memory and CPU overhead on the system for large searches; therefore, Oracle introduced an algorithm based on heap sort, which is more efficient. However, it also has a limitation of re-ordering incoming rows even when the data is not arriving out of order.
Binary tree search places a huge memory and CPU overhead on the system for large searches; therefore, Oracle introduced an algorithm based on heap sort, which is more efficient. However, it also has a limitation of re-ordering incoming rows even when the data is not arriving out of order.
What is the standard normal form for most Online Transaction Processing (OLTP) databases? | Oracle DBA
3NF is the standard normal form for most OLTP databases.
What is join dependency? | Oracle DBA
A join dependency is a situation where a table can be
created by joining two or more tables.
what is the Difference between Database and instance? | Oracle DBA
Database is the collection of datafiles that contain
the information of interest; whereas, Instance is the common of
background processes and memory structure.
Chapter 2: Oracle Architecture
1. What are the components of an Oracle instance?
1. What are the components of an Oracle instance?
2. What is the difference between database and instance?
3. What are the different types of memory structure available in Oracle?
4. What are the components of SGA?
5. Why do you need PGA?
PGA is a raemor, area used by Oracle database.it Is
required to store session specific information.
6. What are the disk components?
6. What are the disk components?
Data files, Redo Logs, Contro, files, Password files, and
Parameter files are the disk components
7. What happens when a user requests for some information from RDBMS?
7. What happens when a user requests for some information from RDBMS?
The
following steps are performed when a user requests for some information:
1. RDBMS checks if a copy of the parsed SQL statement exists in the library cache. If parsed copy exists, then steps 2 to 6 are skipped.
2. RDBMS validates the syntax of the statement.
3. RDBMS ensures that all the columns and tables referenced in the statement exist.
4. RDBMS acquires parse locks on objects referenced in the statement so that their definitions do not change while statement is parsed.
5. RDBMS ensures that the user has sufficient privileges.
6. Statement is parsed and execution plan is created.
7. Statement is executed.
8. Values are fetched.
1. RDBMS checks if a copy of the parsed SQL statement exists in the library cache. If parsed copy exists, then steps 2 to 6 are skipped.
2. RDBMS validates the syntax of the statement.
3. RDBMS ensures that all the columns and tables referenced in the statement exist.
4. RDBMS acquires parse locks on objects referenced in the statement so that their definitions do not change while statement is parsed.
5. RDBMS ensures that the user has sufficient privileges.
6. Statement is parsed and execution plan is created.
7. Statement is executed.
8. Values are fetched.
8. What do you mean by data consistency? How does Oracle maintain consistency of data?
In a multi-user environment, there can be situations when
one or more users are reading certain set of data while other is modifying the
same set of data. Data consistency provides a stable or consistent data to the
users throughout the session.
Data consistency is maintained through rollback segments. A rollback segment holds the data image before change; therefore, one or more sessions will get the same image of the data throughout the session that were reading the data while another session is updating.
9. What happens when multiple users try to update the same set of data?
Data consistency is maintained through rollback segments. A rollback segment holds the data image before change; therefore, one or more sessions will get the same image of the data throughout the session that were reading the data while another session is updating.
9. What happens when multiple users try to update the same set of data?
Oracle uses locking mechanism to ensure that only one
session or user can update a single set of data at given point in time.
10. How long does the rollback segment hold data to maintain consistency?
10. How long does the rollback segment hold data to maintain consistency?
Rollback
segment holds the previous version of data block until either it gets committed
or the space is not available for reuse. If a session is reading specific block
from rollback segment while the other session has committed the information,
then more rollback space is required to maintain more rollback information. In
that case, this specific block ages out of the rollback segment and the reading
or selecting session gets an error.
11. What is the process of updating or inserting certain data in the database?
Following is the process to update or insert data in
the database:
i. RDBMS searches for parsed statement in library cache or parses the statement to generate execution plan.
ii. Server process retrieves relevant data from disk to the buffer cache. In case of inserting data in the database, the data block with sufficient free space will be retrieved.
iii. Lock is acquired. The data block is updated in the buffer cache.
iv. A lock is acquired on rollback segment as well to store old version of data in case rollback is required.
v. User process creates a redo entry in the redo log buffer to support recovery.
12. What is System Change Number (SCN)?
i. RDBMS searches for parsed statement in library cache or parses the statement to generate execution plan.
ii. Server process retrieves relevant data from disk to the buffer cache. In case of inserting data in the database, the data block with sufficient free space will be retrieved.
iii. Lock is acquired. The data block is updated in the buffer cache.
iv. A lock is acquired on rollback segment as well to store old version of data in case rollback is required.
v. User process creates a redo entry in the redo log buffer to support recovery.
12. What is System Change Number (SCN)?
SCN is an ID that Oracle generates for every transaction.
It is recorded with the corresponding change in a redo entry.
13. What is DataBase WRiter (DBWR)?
DBWR is a background process that writes changed data blocks from buffer cache to the datafile. The changed data blocks are called dirty blocks.
13. What is DataBase WRiter (DBWR)?
DBWR is a background process that writes changed data blocks from buffer cache to the datafile. The changed data blocks are called dirty blocks.
14. When does DBWR
write to the datafile?
Following are the situations when DBWR writes to the
datafile:
i. When checkpoint occurs.
ii. When number of dirty blocks reaches a threshold.
iii. Every three seconds due to timeout.
iv. When server process needs free space in buffer cache to read new blocks.
15. What is LoG WRiter (LGWR)?
i. When checkpoint occurs.
ii. When number of dirty blocks reaches a threshold.
iii. Every three seconds due to timeout.
iv. When server process needs free space in buffer cache to read new blocks.
15. What is LoG WRiter (LGWR)?
LGWR is the background process that writes redo
information from redo log buffers to the log files.
16. When does LGWR write to the log file?
16. When does LGWR write to the log file?
Following are the situations when LGWR writes to the
log file:
i. When a user process commits a transaction.
ii. When redo log buffer is one-third full.
iii. When more than a megabyte of changes are recorded into the redo log buffer.
iv. Before DBWR writes modified blocks to the datafiles.
17. What is the physical structure of the disk resources in Oracle
i. When a user process commits a transaction.
ii. When redo log buffer is one-third full.
iii. When more than a megabyte of changes are recorded into the redo log buffer.
iv. Before DBWR writes modified blocks to the datafiles.
17. What is the physical structure of the disk resources in Oracle
Following
is the physical structure of the disk resources in Oracle:
Control files — Stores the status of the physical structure of the database. It contains different types of information, such as archive log history, datafile records, redo threads, log records, and database name.
Datafiles — Refers to the physical files of the operating system that store the data of all logical structures in the database
Redo log files — Refers to the files, which log a history of all the changes made to the database.
18. What is the logical structure of the disk resources?
Control files — Stores the status of the physical structure of the database. It contains different types of information, such as archive log history, datafile records, redo threads, log records, and database name.
Datafiles — Refers to the physical files of the operating system that store the data of all logical structures in the database
Redo log files — Refers to the files, which log a history of all the changes made to the database.
18. What is the logical structure of the disk resources?
Following
is the logical structure of the disk resources:
Data block — Refers to the smallest logical storage unit. Size of a data block is a multiple of operating system block size.
Extent — Refers to the contiguous set of data blocks, which is allocated as a unit to a segment.
Tablespace — Refers to the final logical storage unit. It is mapped to physical datafile.
Segment— Allocates a logical structure, such as table. It is a set of extents, which are stored in the same
tablespace.
19. What is the difference between a temporary tablespace and a permanent tablespace?
Data block — Refers to the smallest logical storage unit. Size of a data block is a multiple of operating system block size.
Extent — Refers to the contiguous set of data blocks, which is allocated as a unit to a segment.
Tablespace — Refers to the final logical storage unit. It is mapped to physical datafile.
Segment— Allocates a logical structure, such as table. It is a set of extents, which are stored in the same
tablespace.
19. What is the difference between a temporary tablespace and a permanent tablespace?
A temporary
tablespace provides temporary storage during the processing of database
function, such as sorting; whereas, a permanent tablespace is used to store
permanent database objects, such as tables, partitions, indexes, and clusters.
You do not need to backup or restore a temporary tablespace.
You do not need to backup or restore a temporary tablespace.
20. Name a tablespace, which is automatically created when you create a database.
The SYSTEM tablespace is created automatically during
database creation. It contains data dictionary objects.
All data stored on behalf of stored PL/SQL program units (procedures, functions, packages, and triggers) resides In the SYSTEM tablespace, which Is always online when the database is open.
21. Which file is accessed first when you start an Oracle database?
All data stored on behalf of stored PL/SQL program units (procedures, functions, packages, and triggers) resides In the SYSTEM tablespace, which Is always online when the database is open.
21. Which file is accessed first when you start an Oracle database?
Initialization parameter file or SPFILE is always
accessed first when an Oracle database is started.
This file is used to determine database level setting because those values are stored as parameters in this file.
22. What is the job of the System Monitor (SMON) and Process Monitor (PMON) processes?
This file is used to determine database level setting because those values are stored as parameters in this file.
22. What is the job of the System Monitor (SMON) and Process Monitor (PMON) processes?
. SMON helps in recovery at instance startup. It is responsible for
cleaning up temporary segments and coalescing free extents. SMON also performs
failed instance recovery for other failed Real Application Clusters instances.
PMON performs process recovery when a user process fails. It is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.
PMON periodically checks the status of dispatcher and server processes, and restarts any process that have stopped running (but not the process, which Oracle has terminated intentionally). It also registers information about the instance and dispatcher processes with the network listener.
Similar to SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.
PMON performs process recovery when a user process fails. It is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.
PMON periodically checks the status of dispatcher and server processes, and restarts any process that have stopped running (but not the process, which Oracle has terminated intentionally). It also registers information about the instance and dispatcher processes with the network listener.
Similar to SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.
23. How do you control
the number of datafiles in an Oracle database?
The number of datafiles in an Oracle database is
controlled by the initialization parameter DB_FILES
24. How do you determine the maximum number of datafiles in an Oracle database?
24. How do you determine the maximum number of datafiles in an Oracle database?
DB_FILES
initialization parameter determines the maximum number of datafiles. A change
in this parameter requires a restart of the database.
The default value of DB_FILES is operating system specific.
When determining a value for DB_FILES, you should consider the following situations:
i. If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit without first shutting down the database.
ii. If the value of DB_FILES is too high, memory is unnecessarily consumed.
The default value of DB_FILES is operating system specific.
When determining a value for DB_FILES, you should consider the following situations:
i. If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit without first shutting down the database.
ii. If the value of DB_FILES is too high, memory is unnecessarily consumed.
25. How do you control the maximum number of redo log files in a database?
The maximum number of redo log files can be controlled by
the parameter MAXLOGFILES.
26. What is the difference between SPFILE and PFILE?
26. What is the difference between SPFILE and PFILE?
Oracle can use both SPFILE and PFILE as initialization
parameter files.
SPFILE is a binary file while PFILE is a text file.
You can change the values in PFILE by directly editing it but those changes would not take effect until the restart of the Whereas, you have to use Oracle statements to change the values in SPFILE and these changes can be incorporated dynamically in a running database.
27. What are the advantages of using SPFILE?
SPFILE is a binary file while PFILE is a text file.
You can change the values in PFILE by directly editing it but those changes would not take effect until the restart of the Whereas, you have to use Oracle statements to change the values in SPFILE and these changes can be incorporated dynamically in a running database.
27. What are the advantages of using SPFILE?
SPFILE supports dynamic changes in parameter values. The
changes in SPFILE can only be made by using the Oracle statements; therefore,
there is no chance of accepting impossible changes as it will be checked by the
system. As a result, chances of human errors are reduced.
Moreover, back up of SPFILE is possible through RMAN.
28. How can you find out if the database is using a PFILE or SPFILE?
Moreover, back up of SPFILE is possible through RMAN.
28. How can you find out if the database is using a PFILE or SPFILE?
A non-null value for the spfile parameter indicates that
the database is using SPFILE. The following query can give the desired result:
SQL>SELECT value FROM v$parameter WHERE name='spfi1e'; If the preceding query returns the value, then the database is using SPFILE; otherwise, the database is using PFILE.
29. How do you view parameter values when you are using PFILE or SPFILE?
SQL>SELECT value FROM v$parameter WHERE name='spfi1e'; If the preceding query returns the value, then the database is using SPFILE; otherwise, the database is using PFILE.
29. How do you view parameter values when you are using PFILE or SPFILE?
You can use the SHOW PARAMETER command from SQL*Plus or
query v$PARAMETER view to see the value of the parameters.
30. How can you start the database using PFILE or SPFILE?
30. How can you start the database using PFILE or SPFILE?
Oracle scans for SPFILE or PFILE in the ORACLE_HOME/dbs
(UNIX) or ORACLE_HOME/database (Windows) directory.
First, it looks for SPFILE and then for PFILE in the directories.
Oracle searches in the following order:
1. spfile.ora
2. spfile.ora
3. init.ora
4. init.ora
If you want to use a specific PFILE or SPFILE, you can specify the complete path and file name at the startup of the Oracle database using the following command: SQL>startup pfile='path/filename'
31. Explain the process to change parameter values.
First, it looks for SPFILE and then for PFILE in the directories.
Oracle searches in the following order:
1. spfile.ora
2. spfile.ora
3. init.ora
4. init.ora
If you want to use a specific PFILE or SPFILE, you can specify the complete path and file name at the startup of the Oracle database using the following command: SQL>startup pfile='path/filename'
31. Explain the process to change parameter values.
PFILE is a
text file and can be edited in any editor.
SPFILE is a binary file and has to be modified using the ALTER SYSTEM statement. The syntax to modify the SPFILE is given as follows:
ALTER SYSTEM =
SCOPE=
COMMENT=
SPFILE is a binary file and has to be modified using the ALTER SYSTEM statement. The syntax to modify the SPFILE is given as follows:
ALTER SYSTEM =
SCOPE=
COMMENT=
32. How can you covert SPFILE to PFILE 3nd PFILE to SPFILE?
One can easily migrate from a PFILE to SPFILE or vice
versa.
Execute the following commands from a user with the SYSDBA or SYSOPER privilege:
SQL> CREATE PFILE FROM SPFILE;
SQL> CREATE SPFILE FROM PFILE;
33. How can you backup the parameter files?
Execute the following commands from a user with the SYSDBA or SYSOPER privilege:
SQL> CREATE PFILE FROM SPFILE;
SQL> CREATE SPFILE FROM PFILE;
33. How can you backup the parameter files?
RMAN can be used to backup SPFILE with control file if CONFIGURE CONTROLFILE AUTOBACKUP is ON. The
following RMAN command can be used to backup the control file:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
The following RMAN command can be used to restore an SPFILE:
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
34. What is the
PGA_AGGREGRATE_TARGET parameter?
The PGA_AGGREGATE_TARGET parameter specifies the target
aggregate PGA memory available to all server processes attached to an instance.
This parameter is used to set overall size of work-area required by various components. A non-zero value of this parameter is equivalent to setting WORKAREA_SIZE_POLICY parameter to AUTO. In this case, you do not need to specify individual *_AREA_SIZE parameter values.
35. What happens after a user process fails?
This parameter is used to set overall size of work-area required by various components. A non-zero value of this parameter is equivalent to setting WORKAREA_SIZE_POLICY parameter to AUTO. In this case, you do not need to specify individual *_AREA_SIZE parameter values.
35. What happens after a user process fails?
PMON cleans up the memory after user process fails.
36. What happens during startup mount?
36. What happens during startup mount?
In case of startup mount, the parameter and control files
can be read but the datafiles and the log files cannot be accessed because
database is not yet open. Generally, this mode is used when a datafile recovery
is required.
37. What background process refreshes materialized views?
37. What background process refreshes materialized views?
The job queue processes refresh the materialized views.
38. What is the difference between multithreaded/shared server and dedicated server?
38. What is the difference between multithreaded/shared server and dedicated server?
In case of a dedicated server, a server process is
associated with a single user process and serves it dedicatedly.
In case of a shared server, a single server process can serve multiple user processes. This is achieved with the help of a dispatcher process, which places each user process in a single request queue. Server process picks up the user process whenever it is free. After that, the server process puts the result in the individual response queue associated with different dispatcher processes.
39. What is a listener process?
In case of a shared server, a single server process can serve multiple user processes. This is achieved with the help of a dispatcher process, which places each user process in a single request queue. Server process picks up the user process whenever it is free. After that, the server process puts the result in the individual response queue associated with different dispatcher processes.
39. What is a listener process?
The listener or Transparent Network Substrate (TNS)
listener is a server process that provides network connectivity to the Oracle
database. The listener is configured to listen for connection requests on a
specified port on the database server. When an incoming request is received on
the port, the listener attempts to resolve the request and forward the
connection information to the appropriate database instance.
40. What is large pool? What are the benefits of using large pool?
40. What is large pool? What are the benefits of using large pool?
Oracle large pool is an optional memory component of the
Oracle database SGA. This area is used for providing large memory allocations
in many situations that arise during the operations of an Oracle database
instance.
Memory from the large pool can be allocated for the following components:
i. Session memory for the shared server and the Oracle XA interface for distributed transactions
ii. I/O server processes
iii. Parallel query buffers.
Chapter 3: Backup & Recovery
Memory from the large pool can be allocated for the following components:
i. Session memory for the shared server and the Oracle XA interface for distributed transactions
ii. I/O server processes
iii. Parallel query buffers.
Chapter 3: Backup & Recovery
1.
What do you understand by database
backup? What are the different types of backup?
Database is used to store the
data, which is the most crucial part of a business. It is important to keep the
data safe; and therefore, backup provides a way to safeguard data against any
loss.
There are mainly three types of backup, which are given as follows:
i. Full backup or normal backup — Refers to the backup of all the datafiles, control files, and SPFILEs.
ii. Incremental backup — Refers to the backup of files and folders that have been modified or created after the previous backup. After this backup, the attribute is again reset.
iii. Differential backup — Refers to the backup of files and folders that have been modified or created after a previous full or incremental backup. The difference here is that the attributes are not reset. Therefore, every time it backup those files whose attributes have not been reset or changed.
2. Which files must be backed up?
There are mainly three types of backup, which are given as follows:
i. Full backup or normal backup — Refers to the backup of all the datafiles, control files, and SPFILEs.
ii. Incremental backup — Refers to the backup of files and folders that have been modified or created after the previous backup. After this backup, the attribute is again reset.
iii. Differential backup — Refers to the backup of files and folders that have been modified or created after a previous full or incremental backup. The difference here is that the attributes are not reset. Therefore, every time it backup those files whose attributes have not been reset or changed.
2. Which files must be backed up?
The following files must be
backed up:
1. Database files:
? Control files
? Archived log files
2. INIT.ORA:
? Password files
3. What is full backup?
1. Database files:
? Control files
? Archived log files
2. INIT.ORA:
? Password files
3. What is full backup?
A full backup is a backup of all
the datafiles, control files, and SPFILE.
A full backup can be made with RMAN or the operating system commands wh le the database is open or closed. As a rule, you must perform full backup, if your database is not running in the archive log mode.
4. Which tools can you use for full backup?
A full backup can be made with RMAN or the operating system commands wh le the database is open or closed. As a rule, you must perform full backup, if your database is not running in the archive log mode.
4. Which tools can you use for full backup?
You can use either the operating
system utilities or the RMAN utility for full backup.
However, Oracle recommends the use of RMAN utility.
5. Can you backup the online redo log files?
However, Oracle recommends the use of RMAN utility.
5. Can you backup the online redo log files?
We cannot backup the online redo
logs; however, online redo logs are protected by multiplexing and optionally by
archiving.
6. What is the difference between hot backup and cold backup?
6. What is the difference between hot backup and cold backup?
Hot backup is taken when
database is still online while cold backup is taken when database is offline.
Database needs to be in the archive log mode for the hot backup but there is no such requirement for the cold backup.
7. Why more redo is generated during hot backup?
Database needs to be in the archive log mode for the hot backup but there is no such requirement for the cold backup.
7. Why more redo is generated during hot backup?
During the hot backup, when a
tablespace is put up in the backup mode, all the datafiles that belongs to the
tablespace get their checkpoint frozen.
When DBWR process writes the changed data blocks to the datafile, the same data blocks are written to redo log files to support roll forward process to maintain the consistency of a hot backup. This is the reason for large redo generation and requirement of archive logs for hot backup.
8. What is the difference between incremental backup and differential backup?
When DBWR process writes the changed data blocks to the datafile, the same data blocks are written to redo log files to support roll forward process to maintain the consistency of a hot backup. This is the reason for large redo generation and requirement of archive logs for hot backup.
8. What is the difference between incremental backup and differential backup?
Both, Incremental and
differential backup files that have been modified or created after the previous
backup. However, attributes are reset after the incremental backup but not
after
the differential backup.
9. Can we take incremental backup without taking the full backup?
the differential backup.
9. Can we take incremental backup without taking the full backup?
No, full backup should be taken before
the incremental backup.
10. How to recover database if we lost the control file and we do not have a backup and the database is also down?
10. How to recover database if we lost the control file and we do not have a backup and the database is also down?
You need to check whether the
control file backup is available in the trace file (udump) or the alert log to
recover the database.
11. What is RMAN?
11. What is RMAN?
RMAN is an Oracle
supplied tool or utility that can be used to manage backup and recovery
activities.
12. Suppose you have taken a manual backup of a datafile using the operating system. How RMAN will know about it?
12. Suppose you have taken a manual backup of a datafile using the operating system. How RMAN will know about it?
RMAN maintains the repository of
backup information. This repository can be maintained in the recovery catalog
or the control file.
In case of the manual backup, information about the backup needs to be recorded in the RMAN repository. This can be done through the catalog command.
13. What is catalog command and how is it used?
In case of the manual backup, information about the backup needs to be recorded in the RMAN repository. This can be done through the catalog command.
13. What is catalog command and how is it used?
Catalog command is used to
register different types of information with RMAN's repository.
Catalog command can be used to perform the following tasks:
i. Add information of backup pieces and image copies in the repository that are on disk a Record information about the level 0 backup in the RMAN repository.
ii. Record information about the copy taken by the operating system
Following examples show the use of catalog command:
1.Catalog an archive log — CATALOG ARCHIVELOG '<filename>';
2.Catalog a data file copy — CATALOG DATAFILECOPY '<filename>' Level <level>;
14. How do you mark the beginning of backup and what happens after that?
Catalog command can be used to perform the following tasks:
i. Add information of backup pieces and image copies in the repository that are on disk a Record information about the level 0 backup in the RMAN repository.
ii. Record information about the copy taken by the operating system
Following examples show the use of catalog command:
1.Catalog an archive log — CATALOG ARCHIVELOG '<filename>';
2.Catalog a data file copy — CATALOG DATAFILECOPY '<filename>' Level <level>;
14. How do you mark the beginning of backup and what happens after that?
You can use the following
statement to mark the beginning of backup:
SQL>alter database begin backup; The preceding statement marks the beginning of a backup by freezing the header of the datafiles. After that, the files cannot be changed. The changes in the data will be recorded in the files after the backup is complete.
You can indicate the completion or end of the backup using the following statement:
SQL>alter database end backup;
15. How do you find the total database size in the database?
SQL>alter database begin backup; The preceding statement marks the beginning of a backup by freezing the header of the datafiles. After that, the files cannot be changed. The changes in the data will be recorded in the files after the backup is complete.
You can indicate the completion or end of the backup using the following statement:
SQL>alter database end backup;
15. How do you find the total database size in the database?
You can use the following database views to get the
information on database size:
i. dba_segments — Gives the information about the used space. You can take a total of all the bytes in the dba_segments view to get the used space.
ii. dba_data__files — Gives the Information on space allocated to datafiles for permanen tablespaces.
iii. v$log— Gives the information on redo log files.
16. Can you track changes to blocks during hot backup?
i. dba_segments — Gives the information about the used space. You can take a total of all the bytes in the dba_segments view to get the used space.
ii. dba_data__files — Gives the Information on space allocated to datafiles for permanen tablespaces.
iii. v$log— Gives the information on redo log files.
16. Can you track changes to blocks during hot backup?
Oracle database llg offers bock
change tracking feature. It records the information in bock change tracking
file. RMAN uses this information to d termine specific blocks to be backed up
again without re-scanning the entire datafile.
17. What are the architectural components of RMAN?
17. What are the architectural components of RMAN?
Following are the
architectura components of RMAN:
i. RMAN executable
ii. Server processes
iii. Channels
iv. Target database
v. Recovery catalog database (optional)
vi. Media management layer (optional)
vii. Backups, backup sets, and backup pieces.
18. How does RMAN improve performance of backup?
i. RMAN executable
ii. Server processes
iii. Channels
iv. Target database
v. Recovery catalog database (optional)
vi. Media management layer (optional)
vii. Backups, backup sets, and backup pieces.
18. How does RMAN improve performance of backup?
RMAN uses multiple channels and
does not take backup of free blocks. This is the reason why performance of RMAN
backup is better.
19. What are channels?
19. What are channels?
RMAN process uses channel to
communicate with I/O devices.
You can control the type of I/O device, parallelism, number of files, and size of files by allocating channels.
20. Can you take offline backup with RMAN?
You can control the type of I/O device, parallelism, number of files, and size of files by allocating channels.
20. Can you take offline backup with RMAN?
Yes, you can take offline backup
with RMAN.
21. What Is a recovery catalog?
21. What Is a recovery catalog?
Recovery
catalog is an inventory of the backup taken by RMAN for the database. It is
used to restore a physical backup, reconstruct it, and make it available to the
Oracle server.
22. Can you use RMAN without recovery catalog?
Yes, RMAN can be used without
recovery catalog.
23. Why is the catalog optional?
23. Why is the catalog optional?
Catalog is optional because RMAN
manages backup and recovery operations and it requires a place to store
necessary information about the database. RMAN always stores this information
in the target database control file. You can also store the RMAN metadata in a
recovery catalog schema contained in a separate database. The recovery catalog
schema must be stored in a database other than the target database.
24. What does RMAN backup consist of?
24. What does RMAN backup consist of?
RMAN backup consists of a backup
of all or part of a database. This results from issuing an RMAN backup command.
A backup consists of one or more backup sets.
25. What
is a backup set?
Backup set is a logical grouping
of backup files that are created when you issue an RMAN backup command. It is
RMAN's name for a collection of files associated with a backup. A backup set
is composed of one or more backup pieces.
26. What is a backup piece?
is composed of one or more backup pieces.
26. What is a backup piece?
Backup piece is a physical
binary file created by RMAN during a backup. They are written to a backup
medium, such as disk or tape. Backup pieces contain blocks from the target
The following rules must be kept in mind while constructing a backup piece from datafiles:
i. A datafile cannot span backup sets
ii. A datafile can span backup pieces as long as it stays within one backup set
iii. Datafiles and control files can coexist in the same backup sets
iv. Archived redo log files are never in the same backup set as datafiles or control files
v. RMAN is the only tool that can operate on backup pieces; therefore, you must use the RMAN tool if you need to restore a file from an RMAN backup. You cannot manually reconstruct the database files from the backup pieces; therefore, you must use RMAN to restore files from a backup piece.
27. What are the benefits of using RMAN?
The following rules must be kept in mind while constructing a backup piece from datafiles:
i. A datafile cannot span backup sets
ii. A datafile can span backup pieces as long as it stays within one backup set
iii. Datafiles and control files can coexist in the same backup sets
iv. Archived redo log files are never in the same backup set as datafiles or control files
v. RMAN is the only tool that can operate on backup pieces; therefore, you must use the RMAN tool if you need to restore a file from an RMAN backup. You cannot manually reconstruct the database files from the backup pieces; therefore, you must use RMAN to restore files from a backup piece.
27. What are the benefits of using RMAN?
Following are the benefits of
using RMAN:
i. Incremental backups that only copy data blocks, which have changed since the last backup.
ii. Tablespaces are not put in the backup mode; therefore, there is no extra redo log generation during online backups
iii. Detection of corrupt blocks during backups
iv. Parallelization of I/O operations
v. Automatic logging of all the backup and recovery operations
vi. Built-in reporting and listing commands.
28.Suppose you lost a control file. How do you recover from this?
i. Incremental backups that only copy data blocks, which have changed since the last backup.
ii. Tablespaces are not put in the backup mode; therefore, there is no extra redo log generation during online backups
iii. Detection of corrupt blocks during backups
iv. Parallelization of I/O operations
v. Automatic logging of all the backup and recovery operations
vi. Built-in reporting and listing commands.
28.Suppose you lost a control file. How do you recover from this?
You need to perform the
following steps to recover the control file:
i. Start the database in the nomount mode
ii. Create the control file from the control file backup and place it in the correct location
iii. Mount the database
iv. Recover the database
v. Open the database.
29. Can you take partial backups if the database is running in the NOARCHIVELOG mode?
i. Start the database in the nomount mode
ii. Create the control file from the control file backup and place it in the correct location
iii. Mount the database
iv. Recover the database
v. Open the database.
29. Can you take partial backups if the database is running in the NOARCHIVELOG mode?
n this case, partial backup is
possible; however, they are not useful.
30. Can you take online backups if the database is running in the NOARCHIVELOG mode?
30. Can you take online backups if the database is running in the NOARCHIVELOG mode?
No. The database needs to be in
the ARCHIVELOG mode to support online backups.
31. How do you bring the database in the ARCHIVELOG mode from the NOARCHIVELOG mode?
31. How do you bring the database in the ARCHIVELOG mode from the NOARCHIVELOG mode?
To bring the database in the
ARCHIVELOG mode from the NOARCHIVELOG mode, you should edit your init.ora /
spfile with the following information:
1og_archi ve_dest='/uOl/oradata/archlog'
(for example)
log„archive_format=,%t_%s.dbf' (for example)
log_archive_start=true
-- for versions lower than lOg
sql>shutdown;
sql>startup mount
sql>alter database archivelog;
sql>alter database open;
It is preferable to backup the database before switching to the ARCHIVELOG mode.
32. If you cannot shutdown the database even for a minute, then in which mode would you run the database?
1og_archi ve_dest='/uOl/oradata/archlog'
(for example)
log„archive_format=,%t_%s.dbf' (for example)
log_archive_start=true
-- for versions lower than lOg
sql>shutdown;
sql>startup mount
sql>alter database archivelog;
sql>alter database open;
It is preferable to backup the database before switching to the ARCHIVELOG mode.
32. If you cannot shutdown the database even for a minute, then in which mode would you run the database?
In this case, the database
should be run in the ARCHIVELOG mode.
33. Where should you place archive log files- in the same disk where database exists or in another disk?
33. Where should you place archive log files- in the same disk where database exists or in another disk?
Archive log files should not be
saved in the same disk as datafiles to ensure recovery in case of media
failure. They should be kept on separate mount points or disk.
34. Can you take online backup of a control file?
34. Can you take online backup of a control file?
Yes, by using the following
statement:
alter database backup controlfile to '<location>' or trace.
35. What is logical backup?
alter database backup controlfile to '<location>' or trace.
35. What is logical backup?
Logical backup is a process of
extracting data in the form of SQL statements, where it is useful to recover in
case the objects are lost. The main drawback of using this backup is that MEAN
TIME TO RECOVER is high.
36. Can
you take the backup of log files if the database is running in the ARCHIVELOG
mode?
No, you cannot take the backup
of log files if the database is running in the ARCHIVELOG mode
37. Suppose you want to retain only last three backups of datafiles. How do you perform it in RMAN?
37. Suppose you want to retain only last three backups of datafiles. How do you perform it in RMAN?
You need to configure the
retention period in RMAN to retain the last three backups of datafiles.
38. Which is more efficient- incremental backups using RMAN or incremental export?
38. Which is more efficient- incremental backups using RMAN or incremental export?
RMAN incremental backup is more efficient
than the incremental export.
39. How do you recover from the loss of datafile if the database is running in the NOARCHIVELOG mode?
39. How do you recover from the loss of datafile if the database is running in the NOARCHIVELOG mode?
The datafile can be recovered
from the cold backup. However, it cannot be recovered to the point of failure.
40. Consider a situation where you have lost some datafiles and don't have the full backup. The database was running in the NOARCHIVELOG mode. What can you do now?
40. Consider a situation where you have lost some datafiles and don't have the full backup. The database was running in the NOARCHIVELOG mode. What can you do now?
The datafile can be recreated if
it is NON-SYSTEM datafile. However, data will be lost.
Chapter 5: Managing
Physical Structure
1. What is the control file? How is it used?
1. What is the control file? How is it used?
Control file is a file that
contains all the information about the physical structure of the database, such
as the number of log files and their location.
Oracle database server uses control file to find its physical component.
2. Where is the information about control file stored?
Oracle database server uses control file to find its physical component.
2. Where is the information about control file stored?
Initialization parameter file or
server parameter file stores the information about control file. The name of
the parameter to store control file information is CONTROL_FILES.
3. At what stage of instance, startup information about control file is read (from parameter file)?
3. At what stage of instance, startup information about control file is read (from parameter file)?
Control file is
required to mount the database; therefore, the control file information should
be available before mounting, that is, at mount stage.
4. What kind of information is stored in a control file?
4. What kind of information is stored in a control file?
Control file stores information
about log switches, checkpoints, and modification in disk resources.
5. Can you recover a control file?
5. Can you recover a control file?
No. A backup of control file
generates a script to create a new.
6. What happens when control file is damaged?
6. What happens when control file is damaged?
You cannot restore the database
if control file is damaged.
7. How do you ensure that control file is safe considering the importance of it?
7. How do you ensure that control file is safe considering the importance of it?
Multiplexing is used to ensure
availability of the control file. It means creating multiple copies of the
control file. Ideally, you should keep those copies in different physical
locations so that
in case of media failure, you have at least one copy of control file that can be used.
8. Explain the steps of creating copy of a control file.
in case of media failure, you have at least one copy of control file that can be used.
8. Explain the steps of creating copy of a control file.
Following are the steps to
create a copy of a control file:
i. Shutdown the database using the shutdown normal, shutdown immediate, or shutdown transactional commanc
ii. Copy the control file to another disk using operat system's file copy command
iii. Modify the CONTROL_FILE parameter in initializat: parameter file to include additional control file
iv. Restart the instance.
9. How can you obtain the information about control file?
i. Shutdown the database using the shutdown normal, shutdown immediate, or shutdown transactional commanc
ii. Copy the control file to another disk using operat system's file copy command
iii. Modify the CONTROL_FILE parameter in initializat: parameter file to include additional control file
iv. Restart the instance.
9. How can you obtain the information about control file?
You can obtain the information
about control file from I initialization parameter file.
Alternatively,you can query the V$CONTROLFILE arc I V$CONTROLFILE_RECORD_SECTION views to obtan information about control files.
10. How do you add a datafile to a tablespace?
Alternatively,you can query the V$CONTROLFILE arc I V$CONTROLFILE_RECORD_SECTION views to obtan information about control files.
10. How do you add a datafile to a tablespace?
You can add a datafile to a
tablespace by using the i-TABLESPACE ADD DATAFILE SIZE; statement.
11. How do you resize a datafile?
11. How do you resize a datafile?
You can resize a datafile by
using the ALTER DATABASE
DATAFILE RESIZE statement.
12. What view would you use to look at the size of a datafile?
DATAFILE RESIZE statement.
12. What view would you use to look at the size of a datafile?
The DBA_DATA_FILES view can be
used to look at the size of a datafile.
13. What view would you use to determine free space in a tablespace?
13. What view would you use to determine free space in a tablespace?
The DBA__FREE_SPACE view can be
used to determine free space in a tablespace.
14. What are online redo log files? How are they used?
14. What are online redo log files? How are they used?
Redo log files are the disk
resources to store data changes.
Whenever data is changed, the information about the change is stored in the redo log file. It helps in recovery.
15. Define the redo log group and the redo log member.
Whenever data is changed, the information about the change is stored in the redo log file. It helps in recovery.
15. Define the redo log group and the redo log member.
Redo log group is a set of
identical redo log files. Each log file in the group is referred to as a redo
log member. A database needs to maintain at least two redo log groups.
16. What is archive log file? How is it used?
16. What is archive log file? How is it used?
Archive log file stores redo log
information persistently. A redo log is overwritten with time; therefore,
archive log maintains that lost information. 17. How can you get the
information about log
17. How can you get the information about log files and archive log files?
17. How can you get the information about log files and archive log files?
There are different data
dictionary views, such as V$LOGFILE and V$ARCHIVE_LOG, to obtain information
about log files and archive log files.
18. What is log switch?
18. What is log switch?
A log switch is a point when LoG
WRiter (LGWR) fills one online redo log group and starts writing to another. At
every log switch a checkpoint occurs.
19. How would you force a log switch?
19. How would you force a log switch?
A log switch can be forced by
using the ALTER SYSTEM SWITCH LOGFILE; statement.
20. What is checkpoint?
20. What is checkpoint?
A checkpoint is a point when a
background process updates the headers of all datafiles and control files to
synchronize the files. At checkpoint, DataBase WRiter (DBWR) writes the dirty
buffers to the datafile.
Chapter 7: Real Application Cluster
1. WhatisRAC?
1. WhatisRAC?
Real Application cluster (RAC)
is a clustering solution. It ensures high availability for database
applications.
A RAC setup contains at least two nodes for a database. RAC provides high availability and load balancing through these nodes.
2. How is RAC different from non-RAC databases?
A RAC setup contains at least two nodes for a database. RAC provides high availability and load balancing through these nodes.
2. How is RAC different from non-RAC databases?
A non-RAC database has a single
node while a RAC database has multiple (at least two) nodes. The nodes in a RAC
set up share storage. RAC offers failover option while a non-RAC database does
not offer failover option because it is based on a single node.
3. What is an interconnect network?
3. What is an interconnect network?
An interconnect network is a
network between nodes of a cluster. It uses switches to ensure that only nodes
can access this network. Generally, this is a high-speed connection.
4. Explain the software component of RAC.
4. Explain the software component of RAC.
In a RAC set up, each node has
an Instance similar to a non RAC database. Each nstance in RAC has its own
memory structures and background processes.
Oracle RAC instances use two processes: GES (Global Enqueue Service) and GCS (Global Cache Service) that enable cache fusion.
5. List the background processes required for RAC.
Oracle RAC instances use two processes: GES (Global Enqueue Service) and GCS (Global Cache Service) that enable cache fusion.
5. List the background processes required for RAC.
The background processes
required for RAC are given as follows:
i. ACMS— Atomic C ntrolfile to Memory Service
ii. GTXO-j— Global Transaction Process
iii. LMON— Global Enqueue Service Monitor
iv. LMD— Global Enqueue Service Daemon
v. LMS— Global Cache Service Process
vi. LCKO— Instance Enqueue Process
vii. RMSn — Oracle RAC Management Processes
viii. RSMN— Remote Slave Monitor.
6. What is Global Resource Directory (GRD)?
i. ACMS— Atomic C ntrolfile to Memory Service
ii. GTXO-j— Global Transaction Process
iii. LMON— Global Enqueue Service Monitor
iv. LMD— Global Enqueue Service Daemon
v. LMS— Global Cache Service Process
vi. LCKO— Instance Enqueue Process
vii. RMSn — Oracle RAC Management Processes
viii. RSMN— Remote Slave Monitor.
6. What is Global Resource Directory (GRD)?
GRD is used by GES and GCS to
maintain status of datafiles and cached blocks. This process provides required
information for cache fusion and maintains data integrity.
7. What is the benefit of using Virtual IP (VIP)?
7. What is the benefit of using Virtual IP (VIP)?
Whenever an application uses VIP
to connect to a database, it can failover to another available node in case of
a failure of one node. You need to use VIP as the host name in the Transparent
Network Substrate (TNS) entry to use VIP.
8. What is the use of Atomic Controlfile Memory Service (ACMS)?
8. What is the use of Atomic Controlfile Memory Service (ACMS)?
ACMS ensures global updates to
System Global Area (SGA) in a RAC set up.
9. How does RAC ensures data consistency between two nodes?
9. How does RAC ensures data consistency between two nodes?
RAC ensures data consistency
between two nodes through cache fusion.
10. What is cache fusion?
10. What is cache fusion?
In a RAC
setup, each node has its own memory; however, they share physical datafiles. As
we know that data blocks are read into memory for updates or query in a single
instance database. Similarly, in a RAC set up, a node reads a block from
datafile when the node needs it. However, in RAC setting, the required block
may be already available in the memory of another node and it is faster to read
block from the memory of another block than from a datafile. Therefore, RAC
provides a mechanism to read block from memory of one node to the memory of
another node. This mechanism is called cache fusion.
Oracle uses high-speed interconnect to communicate between nodes. GES monitors and the Instance Enqueue process manages the cache fusion.
Oracle uses high-speed interconnect to communicate between nodes. GES monitors and the Instance Enqueue process manages the cache fusion.
11. What are the major RAC wait events?
The major RAC wait events are
associated with buffer cache.
The most common wait events in RAC are gc cr request, which occurs when an instance tries to retrieve the data from the remote cache and gc buffer busy, which occurs when one instance finds the GC buffer busy.
12. What is the use of GTXO-j?
The most common wait events in RAC are gc cr request, which occurs when an instance tries to retrieve the data from the remote cache and gc buffer busy, which occurs when one instance finds the GC buffer busy.
12. What is the use of GTXO-j?
GTX is a Global Transaction
process, which provides transparent support for XA global transactions. The
number of these processes is decided automatically based on the requirements.
13. What is the use of Lock Monitor process?
13. What is the use of Lock Monitor process?
It manages global resources and
locks. It configures locks and resources wheneve an instance joins or leaves
the cluster. It is also called Global Enqueue Service Monitor.
14. What is the use of LMD?
14. What is the use of LMD?
LMD stands for Lock Monitor
Daemon process. This process is used for deadlock detection. It also manages
remote resource and enqueue requests.
15. What is the use of LMS?
15. What is the use of LMS?
LMS stands for Lock Monitor
Service. The primary job of this process is to transfer data blocks between
cache of different nodes using high-speed interconnect. It is the most
important and critical process for cache fusion. Each node has at least two LMS
processes.
16. What is the use of LCK0?
16. What is the use of LCK0?
LCKO is lock process, which is
used to manage requests for shared resources.
17. What is the use of RMSn?
17. What is the use of RMSn?
RMSn are RAC Management
processes. These processes manage RAC and create more resources whenever new
instances are added to RAC.
18. What is the use of RSMN?
18. What is the use of RSMN?
RSMN stands for Remote Slave
Monitor process. It creates slave processes on remote instance on behalf of
master coordinating process running on another instance.
19. What components are shared in RAC?
19. What components are shared in RAC?
A RAC setup shares datafiles,
control files, SP Files, redo log files; therefore, these files must be stored
in the cluster-aware shared storage.
20. Why do we need to store datafiles in shared storage in RAC environment?
20. Why do we need to store datafiles in shared storage in RAC environment?
A RAC setup is essentially a
single database accessible through multiple nodes. Therefore, the datafiles in
a RAC setup must be shared while the processes, which are used to manage or
access those files, are available on each individual node.
Chapter 8: Oracle Packages
1. How can you record information about current session?
2. What is the use of recording information about current session?
3. Name some of the procedures available in the dbms_application_info package.
4. Is there any other way to read the information set by the dbms_application_info package?
5. Suppose you want to modify partitioning scheme of certain tables in a live database. How can you do that?
6. Suppose you have developed scripts to redefine tables using the dbms_redefinition package and the user is granted privilege to execute the dbms_redefinition package but still the user gets insufficient privilege error when the script is executed. What is possible reason for the error?
7. Can you redefine a table online?
8. How can you use the dbms_redefinition package?
9. How can you find out If a table can be redefined?
10. Suppose you have developed a script to redefine a table after confirming that the table can be redefined; however, the redefinition process fails in the middle and further attempts show that it cannot be redefined as materialized view exists on the table. Why did this happen and how can you recover from the situation?
11. Does Oracle provide any package to find out fragmented data blocks?
12. Can you get information about a specific rowid of a table using any Oracle supplied package?
13. How can you use the dbms_sql package to execute the DMLs dynamically?
14. How can you execute quer es using the dbms_sql package?
15. Does Oracle support asynchronous notification?
16. How can a session indicate its interest in receiving alerts?
17. When does an alert gets signaled?
18. How can you process messages in order asynchronously?
19. List some of the procedures provided by the dbms_aq package.
20. How can you create an Advanced Queuing (AQ)?
21. How can you grant privileges on AQ to other users?
22. What procedures can you use to register with an AQ?
Chapter 8: Oracle Packages
1. How can you record information about current session?
2. What is the use of recording information about current session?
3. Name some of the procedures available in the dbms_application_info package.
4. Is there any other way to read the information set by the dbms_application_info package?
5. Suppose you want to modify partitioning scheme of certain tables in a live database. How can you do that?
6. Suppose you have developed scripts to redefine tables using the dbms_redefinition package and the user is granted privilege to execute the dbms_redefinition package but still the user gets insufficient privilege error when the script is executed. What is possible reason for the error?
7. Can you redefine a table online?
8. How can you use the dbms_redefinition package?
9. How can you find out If a table can be redefined?
10. Suppose you have developed a script to redefine a table after confirming that the table can be redefined; however, the redefinition process fails in the middle and further attempts show that it cannot be redefined as materialized view exists on the table. Why did this happen and how can you recover from the situation?
11. Does Oracle provide any package to find out fragmented data blocks?
12. Can you get information about a specific rowid of a table using any Oracle supplied package?
13. How can you use the dbms_sql package to execute the DMLs dynamically?
14. How can you execute quer es using the dbms_sql package?
15. Does Oracle support asynchronous notification?
16. How can a session indicate its interest in receiving alerts?
17. When does an alert gets signaled?
18. How can you process messages in order asynchronously?
19. List some of the procedures provided by the dbms_aq package.
20. How can you create an Advanced Queuing (AQ)?
21. How can you grant privileges on AQ to other users?
22. What procedures can you use to register with an AQ?
Chapter 9: Trouble
Shooting & Performance Tuning
1. Name a few places you will look to get more details on a performance issue.
1. Name a few places you will look to get more details on a performance issue.
2. What is the use of ALERT log file? Where can you find the ALERT log file?
The ALERT
log is a log file that records database-wide events.
The information in the ALERT log file is generally used for trouble shooting.
Following events are recorded in the ALERT log file:
i. Database shutdown and startup information
ii. All non-default parameters
iii. Oracle internal (ORA-600) errors
iv. Information about a modified control file
v. At log switch
The location of ALERT log file is specified in the BACKGROUND_DUMP_DEST parameter.
The information in the ALERT log file is generally used for trouble shooting.
Following events are recorded in the ALERT log file:
i. Database shutdown and startup information
ii. All non-default parameters
iii. Oracle internal (ORA-600) errors
iv. Information about a modified control file
v. At log switch
The location of ALERT log file is specified in the BACKGROUND_DUMP_DEST parameter.
3. Which tools are available to monitor performance?
Oracle offers Oracle Enterprise
Manager (OEM) to monitor performance.
OEM can also be used to startup and shutdown the instance. In addition, it can be used to manage database in general.
4. What are the background trace files?
OEM can also be used to startup and shutdown the instance. In addition, it can be used to manage database in general.
4. What are the background trace files?
Background trace files are
associated with background processes and are generated when certain background
process experiences an error. The information in background trace files is
generally used for trouble shooting.
These files are located in the directory specified in the BACKGROUND_DUMP_DIRECTORY parameter.
5. What is a user process trace file?
These files are located in the directory specified in the BACKGROUND_DUMP_DIRECTORY parameter.
5. What is a user process trace file?
A user
process trace file is a trace file that is produced by user session. However,
this is an optional file, which is generated if the user wants to generate the
file. This file is generated when the value of SQL_TRACE parameter is set to
TRUE for a session.
This parameter can be set at database, instance, or session level. If it is set at instance level, trace file will be generated for all the connected sessions. If it is set at session level, trace file will be generated only for the specified session.
The location of user process trace file is specified in the USER_DUMP_DEST parameter.
The information in user process trace file is generally used for trouble shooting.
This parameter can be set at database, instance, or session level. If it is set at instance level, trace file will be generated for all the connected sessions. If it is set at session level, trace file will be generated only for the specified session.
The location of user process trace file is specified in the USER_DUMP_DEST parameter.
The information in user process trace file is generally used for trouble shooting.
6. Which trace file is used for performance tuning and why?
User process trace file is used
for performance tuning because it contains information about execution plan and
resource consumption. This information can be used for performance tuning.
7. Which utility can you use to make trace file more readable?
8. How can you monitor performance of the database pro-actively?
9. What are dynamic performance views? Who has the access to these views?
10. What is a lock?
11. What is db file sequential read wait event?
12. What do you understand by db file scattered read?
13. How can you find out if a session is blocking another?
14. Describe the Oracle Wait Interface.
15. What do the db_file_sequential_read and db_file_scattered_read events indicate?
16. How can you get more details about the blocking session?
17. How would you determine what sessions are connected and what resources they are waiting for?
18. What are different types of locks?
19. What are the different locking modes and how can you identify those?
20. How can you identify locked object?
Chapter 10: Data Guard
1. What is data guard?
2. Why do you need to have data guard? What are the benefits of using data guard?
3. Explain the architecture of data guard.
4. What are the services required on primary database?
5. What are the services required on standby database?
6. What happens when standby database is not available?
7. Is there any difference in the data changes in primary and secondary database?
8. What is role transition and when does it happen?
9. What is the maximum number of standby databases that can be associated with a production database?
10. What is the difference between logical standby database and physical standby database?
11. How can you find out whether a database is primary or standby?
7. Which utility can you use to make trace file more readable?
8. How can you monitor performance of the database pro-actively?
9. What are dynamic performance views? Who has the access to these views?
10. What is a lock?
11. What is db file sequential read wait event?
12. What do you understand by db file scattered read?
13. How can you find out if a session is blocking another?
14. Describe the Oracle Wait Interface.
15. What do the db_file_sequential_read and db_file_scattered_read events indicate?
16. How can you get more details about the blocking session?
17. How would you determine what sessions are connected and what resources they are waiting for?
18. What are different types of locks?
19. What are the different locking modes and how can you identify those?
20. How can you identify locked object?
Chapter 10: Data Guard
1. What is data guard?
2. Why do you need to have data guard? What are the benefits of using data guard?
3. Explain the architecture of data guard.
4. What are the services required on primary database?
5. What are the services required on standby database?
6. What happens when standby database is not available?
7. Is there any difference in the data changes in primary and secondary database?
8. What is role transition and when does it happen?
9. What is the maximum number of standby databases that can be associated with a production database?
10. What is the difference between logical standby database and physical standby database?
11. How can you find out whether a database is primary or standby?
Chapter 11: Oracle Installation & UNIX
1.
What
is Optimal Flexible Architecture (OFA)? Why is it important to use OFA?
2. Where in the Oracle directory tree structure are audit traces placed?
3. Give the command to display space usage on the UNIX system.
4. Give the command to list the files in the UNIX directory to list hidden files.
5. How do you execute a UNIX command in the background?
6. How do you execute a UNIX command that will continue running even after you log out? .
7. What is the difference between a soft link and a hard link?
8. What privileges are available on a UNIX directory?
9. How can you replace a string in a file in the vi editor?
10. Give two UNIX kernel parameters that affect installation of Oracle.
11. List the major steps in installation of Oracle software on UNIX in brief.
12. How do you find out the number of instances that are running on a server?
13. How do you automate starting and shutting down of databases in UNIX?
14. WhatisOERR?
15. How do you view virtual memory statistics in Linux?
2. Where in the Oracle directory tree structure are audit traces placed?
3. Give the command to display space usage on the UNIX system.
4. Give the command to list the files in the UNIX directory to list hidden files.
5. How do you execute a UNIX command in the background?
6. How do you execute a UNIX command that will continue running even after you log out? .
7. What is the difference between a soft link and a hard link?
8. What privileges are available on a UNIX directory?
9. How can you replace a string in a file in the vi editor?
10. Give two UNIX kernel parameters that affect installation of Oracle.
11. List the major steps in installation of Oracle software on UNIX in brief.
12. How do you find out the number of instances that are running on a server?
13. How do you automate starting and shutting down of databases in UNIX?
14. WhatisOERR?
15. How do you view virtual memory statistics in Linux?
No comments:
Post a Comment