DBA Fundamentals

A: The Oracle Architectural components are:

� 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.

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.

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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).

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

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.

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.

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.

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.

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.

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.

A: SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

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.

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.

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.

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.

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

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.

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).

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

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.

A: SQL> CONNECT / AS SYSDBA

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.
 

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.

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

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

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.
 

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

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

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

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

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.

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

Q: What does the following SQL statement do?

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

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.

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

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.

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
/
 

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.

A: SQL> ALTER INDEX uk_emp
MONITORING USAGE
/
 

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

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

A: SQL> ALTER USER developer

A: SQL> ALTER USER DEVELOPER ACCOUNT LOCK
/
 

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.

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

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.

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