Final Day reveiw

Question 1

Which type of database backup can be performed while a database is online?

Answer 1

Inconsistent

Question 2

What can be queried from a database while it is in the NOMOUNT state?

Answer 2

Instance parameters

Question 3

Which process duplicates modified blocks from a buffer cache to files on disk?

Answer 3

Database Writer (DBWn)

Question 4

In response to users’ complaints that the system is slow, a database administrator detects that a user is consuming too many resources.

Which parameter controls the use of service units?

Answer 4

COMPOSITE_LIMIT

Question 5

What is the result of the CASCADE option when used with the DROP USER command?

Answer 5

All the user’s objects are removed from the database.

Question 6

Which privilege is required to add a foreign key constraint to a table owned by another user?

Answer 6

REFERENCES

Question 7

An administrator runs the following SQL statements:

CREATE USER HR_User IDENTIFIED BY ‘Password’ DEFAULT TABLESPACE DATA;
GRANT CREATE SESSION, CREATE TABLE to HR_User;

HR_User creates a new table in their own schema.

What happens when HR_User inserts a row into the new table?

Answer 7

The insert fails with an error for missing privileges on the DATA tablespace.

Question 8

What is the NOMOUNT state in Oracle?

Answer 8

The NOMOUNT state is the initial state of an Oracle instance, in which the instance is started but the database is not mounted.

Question 9

What does it mean for a database to be “mounted”?

Answer 9

When a database is “mounted,” it means that the instance is able to access the datafiles and control files of the database.

Question 10

What can be done in the NOMOUNT state?

Answer 10

In the NOMOUNT state, the instance can perform tasks such as creating a new database or recovering an existing one.

Question 11

What is the command to start an instance in NOMOUNT state?

Answer 11

The command to start an instance in NOMOUNT state is “STARTUP NOMOUNT”.

Question 12

What is the command to mount a database?

Answer 12

The command to mount a database is “ALTER DATABASE MOUNT”.

Question 13

What is the command to open a database?

Answer 13

The command to open a database is “ALTER DATABASE OPEN”.

Question 14

What is the difference between mounting and opening a database?

Answer 14

Mounting a database makes it accessible to the instance, while opening a database makes it available for use by users.

Question 15

What can be queried from a database while it is in the NOMOUNT state?

Answer 15

While the database is in the NOMOUNT state, you will not be able to query any data from the database as the database is not mounted, and the instance does not have access to the datafiles and control files of the database.
In NOMOUNT state, you can only query the information available in the data dictionary, such as the initialization parameters, backup and recovery information, and the structure of the database objects. However, you will not be able to access the data stored in the database tables.
You can also check the status of the background processes and the memory structures such as SGA, PGA etc.
Once the database is mounted and opened, you will be able to query the data in the database tables.

Question 16

What is the MOUNTED state in Oracle?

Answer 16

The MOUNTED state is when the instance has successfully mounted the database, allowing the instance to access the datafiles and control files of the database.

Question 17

What is the OPEN state in Oracle?

Answer 17

The OPEN state is when the instance has opened the database, making it available for use by users.

Question 18

What is the CLOSED state in Oracle?

Answer 18

The CLOSED state is when the instance has closed the database, making it unavailable for use by users, but allowing the instance to continue to access the datafiles and control files

Question 19

What is the SHUTDOWN state in Oracle?

Answer 19

The SHUTDOWN state is when the instance is shut down, and the database is not available for use.

Question 20

What is the ARCHIVELOG mode in Oracle?

Answer 20

ARCHIVELOG mode is a mode in which the Oracle database automatically archives redo logs, which are used to recover the database in case of a failure.

Question 21

What is the NOARCHIVELOG mode in Oracle?

Answer 21

NOARCHIVELOG mode is a mode in which the Oracle database does not automatically archive redo logs. This mode is less robust in terms of recovery options.

Question 22

More info on Closed state

Answer 22

Certainly, the CLOSED state in Oracle is when the instance has closed the database, making it unavailable for use by users, but allowing the instance to continue to access the datafiles and control files. Here are some additional details on the closed state:

The closed state is typically used when performing maintenance on the database, such as adding new datafiles or making structural changes to the database.
While the database is closed, users cannot access the data in the tables, but the instance can still access the datafiles and control files.
The instance can perform tasks such as backup and recovery, and it can also read and write to the datafiles.
The instance can also switch the database to another state, such as the open state, once the maintenance is complete.
The command to close the database is “ALTER DATABASE CLOSE”, this command makes the database unavailable for user access and only the instance can access the datafiles and control files.
The closed state is not the same as shutting down the instance, shutting down the instance would close the database but also release all the resources and stop the background processes.
It is important to note that a closed database is not accessible to the users, so the data is not updated, therefore the database can be inconsistent and can’t be used for any query or transaction. A closed database is typically used for maintenance purpose, and before opening the database, it is important to perform recovery actions to make it consistent.

Question 23

What is the Apply Server (ASnn) in Oracle?

Answer 23

A: The Apply Server (ASnn) is a background process that is used to apply changes from the redo log to a replicated or materialized database.

Question 24

What types of replication and data warehousing configurations use the Apply Server (ASnn)?

Answer 24

The Apply Server (ASnn) is used in configurations such as Oracle Streams and Oracle GoldenGate, which are used to replicate data between databases.

Question 25

What is the function of the Apply Server (ASnn)?

Answer 25

The Apply Server (ASnn) reads the redo log and applies the changes to the replicated or materialized database. This ensures that the replicated or materialized database remains in sync with the primary database.

Question 26

How does the Apply Server (ASnn) ensure that the replicated or materialized database is in sync with the primary database?

Answer 26

The Apply Server (ASnn) reads the redo log and applies the changes to the replicated or materialized database in the same order that they were made to the primary database. This ensures that the replicated or materialized database has the same data as the primary database.

Question 27

What is the function of the Database Writer (DBWn)?

Answer 27

The Database Writer (DBWn) is responsible for writing changes made to the database buffer cache to data files on disk.

Question 28

What is the function of the Log Writer (LGWR)?

Answer 28

The Log Writer (LGWR) is responsible for writing redo log entries to the online redo log files on disk, these entries contain information about changes made to the database.

Question 29

What types of data do the Database Writer (DBWn) and Log Writer (LGWR) write to disk?

Answer 29

The Database Writer (DBWn) writes changes made to the database buffer cache to datafiles on disk, while the Log Writer (LGWR) writes redo log entries to the online redo log files on disk.

Question 30

What is the main difference between the two processes?

Answer 30

The main difference is that the Database Writer (DBWn) writes changes made to the database buffer cache to datafiles on disk, while the Log Writer (LGWR) writes redo log entries to the online redo log files on disk. The DBWn process writes the changes that were made to the database buffer cache to the datafiles, while the LGWR writes the redo logs, which contain information about the changes that were made to the database.

Question 31

When do the two process runs?

Answer 31

The Database Writer (DBWn) process runs continuously and writes changes to disk as they are made to the database buffer cache. The Log Writer (LGWR) process runs continuously and writes redo log entries to disk as they are generated by the database.

Question 32

What is the LOG_ARCHIVE_DEST parameter?

Answer 32

The LOG_ARCHIVE_DEST parameter specifies the location of archived redo logs, which are used for recovery and replication.

Question 33

What is the DIAGNOSTIC_DEST parameter?

Answer 33

The DIAGNOSTIC_DEST parameter specifies the location of diagnostic files, such as trace files, core dumps, and alert logs.

Question 34

What is the AUDIT_FILE_DEST parameter?

Answer 34

The AUDIT_FILE_DEST parameter specifies the location of audit files, which contain information about database activity, such as user logins and SQL statements executed.

Question 35

What is the main difference between the three parameters?

Answer 35

The main difference is the type of files that they specify the location of. LOG_ARCHIVE_DEST specifies the location of archived redo logs, DIAGNOSTIC_DEST specifies the location of diagnostic files, and AUDIT_FILE_DEST specifies the location of audit files.

Question 36

What is the purpose of these files?

Answer 36

Archived redo logs are used for recovery and replication, diagnostic files are used for troubleshooting and monitoring the database, and audit files are used for auditing and compliance purposes.

Question 37

What is the Oracle backup process?

Answer 37

Oracle provides several methods for creating backups of the database, including full backups, incremental backups, and archived redo logs. Full backups create a complete copy of the entire database, while incremental backups only copy changes made since the last backup. Archived redo logs capture changes made to the database in real-time, allowing for more granular recovery options.

Question 38

What is the Oracle restore process?

Answer 38

In order to restore a database, you will need to have a valid backup of the database, as well as all of the necessary archived redo logs. The restore process typically involves using Oracle's recovery manager (RMAN) to restore the backup and apply the archived redo logs. Different restore options may be available depending on the type of backup and the specific recovery scenario, such as point-in-time recovery or incomplete recovery.

Question 39

What is the difference between complete recovery and incomplete recovery?

Answer 39

Complete recovery is the process of restoring the database to the most recent consistent state by applying all the archived redo logs. Incomplete recovery is the process of restoring the database to a specific point in time by applying a subset of the archived redo logs. Complete recovery is typically used when the database is fully functional but a problem has occurred, while incomplete recovery is typically used when the database is in an inconsistent state or when specific data needs to be recovered.

Question 40

What is required to perform a complete or incomplete recovery?

Answer 40

Both complete recovery and incomplete recovery require a consistent full backup of the database and all the archived redo logs. In case of incomplete recovery, you will also need to have all the archived redo logs up to the point in time you want to recover.