iSelfSchooling - Final Exam Evaluation - Questions and Answers

· Memory (SGA) such as Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, Java Pool, etc.

· Background Processes such as Log Writer, DB Writer, Checkpoint, Archiver, SMON, etc.

· Oracle Physical Layout such as Datafiles, Controlfiles, Online Redo log files, Parameter file, Password file, etc.

Q: What are the Oracle Memory Components?

A: All components such as Shared Pool (Library Cache, Dictionary Cache), Buffer Cache, Online Redo Log file, Large Pool, Java Pool as well as a few other items are referred to as the System Global Area (SGA). And the place stores information like bind variable values, sort areas, cursor handling, etc for a specific user is called Program Global Area (PGA). The PGA is used to store only real values in place of bind variables for executing SQL statements. The combination of these two memories structure while they are running is called Oracle Instance.

Q: What is the Server Parameter File?

A: The Server Parameter File is a binary file and Oracle uses it to change the most of its system parameters dynamically.

Q: What is the Parameter File?

A: The Parameter file is a configuration file and it contains all the Oracle instance and database configuration parameters. When you change any parameter using this file, you should shutdown and startup the Oracle Database.

Q: How do you use the init.ora file?

A: The init.ora file is called initialized or parameter file. It is a configuration file.

Q: What is the System Global Area (SGA)?

A: The SGA contains of Shared Pool (Library Cache, Dictionary Cache), Buffer Cache, Online Redo Log file, Large Pool, Java Pool as well as a few other items.

Q: What is the Shared Pool in SGA?

A: The Shared Pool contains the Library Cache and the Dictionary Cache as well as a few other items, which are not in the scope of this section. The Library Cache holds all users’ SQL statements, Functions, Procedures, and Packages. It stores parsed SQL statement with its execution plan for reuse. The Dictionary Cache, sometimes also referred to as the Row Cache, holds the Oracle repository data information such as tables, indexes, and columns definitions, usernames, passwords, synonyms, views, procedures, functions, packages, and privileges information.

Q: What does the Buffer Cache hold in SGA?

A: The Buffer Cache holds users’ data. Users query their data while they are in the Buffer Cache. If user’s request is not in the Buffer Cache then server process has to bring it from disk. The smallest unit in the buffer cache is an Oracle block. The buffer cache can be increased or decreased by granule unit. The smallest Granule Unit is 4Meg if the SGA size is less than 128Meg and the smallest Granule Unit become 16Meg is the SGA size is more than 128Meg.

Q: What are the differences between the Library Cache and Dictionary Cache?

A: The Library Cache holds user’s SQL statements, PL/SQL programs, but the Dictionary Cache holds only repository information such as user’s table name, its access privileges, and etc.

Q: What is the Redo Log Buffer in SGA?

A: The Redo Log Buffer holds users’ entries such as INSERT, UPDATE, DELETE, etc (DML) and CREATE TABLE, DROP TABLE (DDL). The Redo Entries are information that will be used to reconstruct, or redo, changes made to a database. The Log Writer writes the entries into the Online Redo Log files when a COMMIT occurs, every 3 seconds, or when one third of the Redo Log Buffer is full. That will guarantee a database recovery to a point of failure if an Oracle database failure occurred.

Q: Describe the Large Pool component in SGA?

A: The Large Pool holds information about the Recovery Manager (RMAN) utility when RMAN is running. If you use the Multi-threaded Server (MTS) process, you may allocate the Oracle Memory structure such that you can get advantage of using Large Pool instead of the Shared Pool. Notice that when you use dedicated servers, user session information is housed in the PGA.

Q: Describe the Multi-threaded Server process?

A: The Multi-threaded Server process will be used when a user send his/her request by using a shared server. A user’s request will be assigned to a dispatcher based on the availability of dispatchers. Then the dispatcher will send or receive request from an assigned shared server.

Q: What are PGA and UGA?

A: When you are running dedicated servers then the session information can be stored inside the process global area (PGA). The UGA is the user global area, which holds session-based information. When you are running shared servers then the session information can be stored inside the user global area (UGA).

Q: Describe the log writer background process (LGWR)?

A: The LGWR’s job is to write the redo user’s entries from the Redo Log Buffer.

Q: How often LGWR writes user’s entries to the Online Redo Log Buffer files?

A: It writes user’s entries when the buffer exceeds one third of the Redo Log Buffer, every 3 seconds, or when a user executes the commit SQL statement.

Q: Describe the Checkpoint process?

A: The Checkpoint signals DB writers to write all dirty blocks into the disk. The Checkpoint will occurred either by a specific defined time, size of the Online Redo Log file used by DBA, or when an Online Redo log file will be switched from on log file to another.

Q: How do you automatically force the Oracle to perform a checkpoint?

A: The following are the parameters that will be used by a DBA to adjust time or interval of how frequently its checkpoint should occur on its database.

LOG_CHECKPOINT_TIMEOUT = 3600 # every one hour

LOG_CHECKPOINT_INTERVAL=1000 # number of OS blocks

Q: What is the Recovery Process?

A: The RECO will be used only if you have a distributed database. You use this process to recover a database if a failure occurs due to physical server problem or communication problem.

Q: What is the Lock Background Process?

A: The LCKn background process will be used if you have multiple instances accessing to only one database. An example of that is a Parallel Server or a Real Application Clusters.

Q: How does the Archive Process work?

A: This background process archives the Online Redo Log file when you are manually or automatically switching an Online Redo Log file. An example of manually switching is: ALTER SYSTEM SWITCH LOGFILE or ALTER SYSTEM ARCHIVE LOG CURRENT.

Q: How do you configure your database to do an automatic archiving?

A: SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Q: What is the System Monitor Process?

A: The SMON job is: when you start your database, it will make sure that all datafiles, controlfiles, and log files are synchronized before opening a database. If they are no, it will perform an instance recovery. It will check the last SCN that was performed against the datafiles. If it finds that there are transactions that were not applied against the datafile, then it will recover or synchronize the datafile from either the Online Redo Log files or the Archive Log files. The smaller Online Redo log files will bring a faster database recovery.

Q: Describe the Program Monitor Process Job?

A: A user may be disconnected either by canceling its session or by communication link. In either act, the PMON will start and perform an action to clean the reminding memory allocation that was assigned to the user.

Q: What are the differences between the SPFILE and PFILE startup?

A: You can read or change the init.ora file (PFILE) for startup of the Oracle database. It contains all Oracle parameters file to configure a database instance. In this file, you can reset and change the Buffer Cache size, Shared Pool size, Redo Log Buffer size, etc. You also can change the location of your control files, mode of a database such as archivelog mode or noarchivelog mode, and many other parameter options that you will learn them in the course of this book.

But using Server Parameter File-SPFILE, you can not read the file. It is in a binary format. If you want to change database parameters dynamically, you should create the Server Parameter file (CREATE SPFILE FROM PFILE) and startup your database using the SPFILE file. There are some few parameters that you still need to shutdown and startup the database, if you want to make the parameter in effect.

Q: What is the controlfile?

A: You cannot read this file and it is in a binary format. If you want to see the content of control file or the layout of your database, you should use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement. It writes a trace file into the %ORACLE_BASE<db-name> directory.

Q: How do you backup your database controlfiles?

A: SQL> ALTER DATABASE BACKUP CONTROLFILE TO c:.bk;

Q: What does a controlfile contain?

A: It contains information the structure of your database layout, database name, last System Change Number (SCN) number, your database mode (archivelog mode or noarchivelog mode), maximum number of log files, maximum number of log members, maximum number of instances, maximum of number of datafiles, the location of the database Online Redo Log files, and backup information.

Q: Describe the password file?

A: The password file is an external file that contains the password of sysdba or sysoper. To use the password file you should set the REMOTE_LOGIN_PASSWORD parameter to exclusive or shared mode in the Parameter File (Example: REMOTE_LOGIN_PASSWORD=EXCLUSIVE).

Q: How do you create a password file?

A: To create the password file, you should run the ORAPWD utility from operating system.

For example:

MS-DOS> ORAPWD FILE=%ORACLE_HOME\b0 orapw<sid>.pwd \par PASSWORD=mypass ENTRIES=3

The ENTRIES parameter specifying the number of user entries allowed for the password file. Now, the DBA can be connected to the database as a user with sysdba privilege.

Q: Describe the Online Redo Log file?

A: The Online Redo Log files hold the Redo Entries. You should have at least two or more Redo Log Groups. Each group may have more than one member. It is a good practice to multiplex Online Redo Log members. The Redo Entries are information that will be used to reconstruct, or redo, changes made to a database. The Log Writer writes the entries into the Online Redo Log files when a COMMIT occurs, every 3 seconds, or when one third of the Redo Log Buffer is full. That will guarantee a database recovery to a point of failure if an Oracle database failure occurred.

Q: How do you start up an instance with the MOUNT option?

A: SQL> CONNECT / AS SYSDBA

SQL> STARTUP MOUNT

--OR--

SQL> STARTUP NOMOUNT

SQL> ALTER DATABASE MOUNT;

Q: Describe the IMMEDIATE option in the SHUTDOWN command.

A: The IMMEDIATE option means not to wait for a user to log off and roll back uncommitted transactions, then shut down the instance and close the database.

Q: Describe the ABORT option in the SHUTDOWN command.

A: The ABORT option tells Oracle not to wait for a user and do not roll back for any transaction and shutdown the instance. If you SHUTDOWN with the ABORT option and then you start your database, the SMON will perform an instance recovery automatically.

Q: Describe the PFILE parameter in the STARTUP command.

A: It tells the Oracle to use the specific parameter file that is in the PFILE parameter.

Q: What does the following SQL statement?

SQL> ALTER DATABASE BACKUP CONTROLFILE
TO 'c:_ddmmyyyy.ctl'
/
A: It will backup a controlfile.

Q: What is the ALERT file in an Oracle database?

A: It is a log file that any unknown problems with the database such as, not enough space in the rollback segment or the maximum extent reached in a table.

Q: How many different types of database mode can you change your database to?

A: Six! ARCHIVELOG, NOARCHIVELOG, SUSPEND, RESUME, RESTRICTED SESSION, and QUIESCE RESTRICTED mode.

Q: What does the following statement do?

SQL> CREATE SPFILE

FROM

PFILE='%ORACLE_HOME%.ora'
/
A: It creates a Server Parameter File using an specific parameter file.

Q: How do you configure a database to an archive mode?

A: First you should change the following parameters in the parameter file.

log_archive_dest = /u01/app/oracle/admin/<database_name>/arch

log_archive_start = true

log_archive_format = log%s.arc

Then do the following in the SQLPLUS utility.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

PFILE=%ORACLE_HOME%.ora
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Q: What does the following SQL statement?

SQL> ALTER DATABASE RENAME FILE 'C:.LOG' TO

'C:a.log'
/

A: Assuming that Online Redo Log file is offline, it relocates it to different location.

Q: What are the differences between an Oracle-Managed and User-Managed files?

A: A User-Managed file will be defined by an Oracle user. If you drop the tablespace that was using the file, you should physically delete it from the disk. But an Oracle-Managed file will be created and defined by Oracle. If you drop the tablespace that was using the file, oracle will physically deletes the file from the disk. It knows where the file is located.

Q: How do you maintain a tablespace using the Oracle-Managed file technique?

A: You should tell Oracle where it should locate and create datafiles.

SQL> ALTER SYSTEM SET db_create_file_dest='c:';

Q: What does the following SQL statement do?

SQL> CREATE TEMPORARY TABLESPACE mytemp
TEMPFILE 'mytemp_01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M
/

A: It creates locally managed temporary tablespace with uniform size option.

Q: What are the PCTFREE and PCTUSED space allocations in the CREATE TABLE statement?

A: The PCTFREE parameter means that an Oracle user can add records to a block until the unused space block reaches to the PCTFREE value. When a block uses all space up to the “1-PCTFREE” percentage, it stops adding records to the block. Oracle takes that block out of the Freelist. It means that records can not be added to the block any more unless you delete records from the block till it reaches to the PCTUSED value. Then Oracle will add the block in the Freelist again and records can be added to the block. And this process continues to determine when to add records in or stop adding records from the block.

Q: How do you create an UNDO tablespace?

A: SQL> CREATE UNDO TABLESPACE my_undo_tablespace
DATAFILE SIZE 100K
/

Q: What are the differences between a DICTIONARY-MANAGED and LOCALLY managed tablespace?

A: The difference between the DICTIONALY-MANAGED and LOCALLY MANAGED tablespace is: In the locally managed tablespace all information about the datafiles such as the last performed checkpoint, etc are stored locally in the datafiles of the tablespace but in the DICTIONAY-MANAGED tablespace all such information would be stored in the Oracle repository in the SYSTEM tablespace.

Q: How do you create a TRANSACTION temporary table?

A: SQL> CREATE GLOBAL TEMPORARY TABLE test_temp
(col1 NUMBER(5) PRIMARY KEY,
col2 VARCHAR2(10) check (col2 BETWEEN 'A' AND 'T'))
ON COMMIT DELETE ROWS
/

Q: What are the differences between a row migration and chained record?

A: A “Chained Record” happens when a user updates a record and the new value can not fit in the existing allocated location. So, Oracle stores the value in a space that allocated for them (PCTFREE) and add a pointer to the row so that it knows where the rest of the record is. This process calls row Chaining. Now, if there was no space in the PCTFREE area, then Oracle will take the whole record and migrate it in a different block that has space. This process calls row Migration.

Q: How do you monitor a usage of an index table?

A: SQL> ALTER INDEX uk_emp
MONITORING USAGE
/

Q: What does the EXCEPTIONS INTO EXCEPTIONS clause perform in the ALTER TABLE statement?

A: It will insert all exceptions that were found during the ALTER TABLE command into the EXCEPTIONS table.

Q: How do you create a user account?

A: SQL> CREATE USER developer
IDENTIFIED BY developer
DEFAULT TABLESPACE iself_data
TEMPORARY TABLESPACE temp
QUOTA 10K ON iself_data
QUOTA 0K ON SYSTEM
PROFILE default
PASSWORD EXPIRE
ACCOUNT UNLOCK
/

Q: How do you assign a default tablespace to a user?

A: SQL> ALTER USER developer

DEFAULT TABLESPACE iself_data

Q: How do you lock a user?

A: SQL> ALTER USER DEVELOPER ACCOUNT LOCK
/

Q: What are the Oracle database triggers?

A: The Oracle database triggers are triggers that will occur when an Oracle event happens either by the DML statements such as UPDATE, DELETE , INSERT, etc; the DDL statements such as DROP, CREATE, etc; the Database events such as SHUTDOWN, STARTUP, etc; or events in a schema such as dropping a table in an specific schema.

Q: How do you start or stop auditing?

A: SQL> ALTER SYSTEM SET audit_trail=db SCOPE=spfile
/
and you should shutdown and startup the database.

Then start auditing by using the AUDIT command.

Ex: SQL> AUDIT DELETE
ON iself.emp
BY ACCESS
WHENEVER SUCCESSFUL
/

And stop auditing by using the NOAUDIT command.

Ex: SQL> NOAUDIT ALL
/

Q: What is a cluster table in the Oracle database?

A: A cluster is a schema object that contains one or more tables that all have one or more columns in common. Rows of one or more tables that share the same value in these common columns are physically stored together within the database. Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT statements, and UPDATE statements that modify cluster key values.

Q: How do you re-organize all a schema’s tables?

A: SQL> EXECUTE dbms_stats.gather_schema_stats ('your_schema_name');