Database Administration

When an Oracle database instance is started, two processes are created, the listener process and the Oracle process. Oracle on the Windows NT operation system uses the Windows NT implementation of threads. The Oracle process consists of the shared threads and the dedicated shadow threads.

Database data is stored in 8 KB blocks in data files on disk. To accelerate read and write access to data, these data blocks are cached in the database buffer pool in main memory.

Modifications to database data are logged in the online redo log files. This procedure ensures data security. To ensure fail-safe database operation, without using additional operating system utilities, the control files and the online redo log files of the database system should be mirrored.

The Oracle database management system holds the executable SQL statements in the Shared SQL Area,which is part of the shared pool.
Each R/3 work process:
Connects to the database as one database user, SAPR3

Handles database requests for the different R/3 System users

Communicates with a corresponding shadow thread on the database

Can reconnect to the database if the connection is lost due to database errors

Starting and Stopping Database
****************************

When an Oracle database is started, it goes through 3 phases:

In the No mount phase, the database instance is built up. Operating system resources are allocated using configuration information stored in the profile init.ora.

In the Mount phase, the control files of the database are evaluated. The system reads the information about the file structure of the database. Data files and logs are not yet opened.

In the Open phase, all files in the database system are opened. If required, an instance recovery is performed immediately after opening the database. Pending database transactions are ended.

You can shut down the database using one of three commands:

SHUTDOWN NORMAL: No new database logon possible. After all database user have logged off,the database is closed systematically: All files are closed, the database is dismounted, and the instance is shut down. The database is consistent after shutdown.

SHUTDOWN IMMEDIATE: Only the current commands are executed. PMON ends all sessions and performs a rollback of the open transactions. The database is then closed systematically (as for a normal shutdown). The database is consistent after shutdown.
Caution: DBWR and ARCH may require up to 1 hour post-processing time.

SHUTDOWN ABORT: Emergency database shutdown. Users are not logged off, and a rollback of the open transactions is not performed. The database is not consistent after shutdown. An instance recovery is automatically performed at the next database startup.



An Oracle database system has three threads that write information from the Shared Global Area (SGA) to the appropriate files:

During a checkpoint, the Database writer (DBWR) asynchronously writes the changed blocks from the SGA to the database data files.

To speed up the writing of checkpoints, the Checkpoint (CKPT) thread starts.

The Logwriter (LGWR) synchronously writes the change log from the SGA redo log buffer to the currently active online redo log file.

In a production database system, you must always run the database in ARCHIVELOG mode and ensure that the Archiver thread (ARCH) is started (init.ora: log_archive_start = TRUE). ARCH archives a completed online redo log file into an offline redo log file in the archive directory.

ARCH determines the archive directory from the init.ora parameter log_archive_dest (default: ?\saparch\) and determines the file name from the parameter log_archive_format.

Once the offline redo log file has been successfully created, the corresponding online redo log file is released to be overwritten with new log information.

If there is no freespace available in the archive directory, or if a file with the same name already exists in the archive directory, the Archiver does not archive the file. After a corresponding number of redo log switches, the database becomes "stuck". Database changes cannot be committed as long as this archiver stuck situation persists.



A database is divided into logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments (tables or indexes). Segments are further divided into extents, which consist of contiguous data blocks. A data block (normally 8 KB) is the smallest unit of I/O used by a database.

A tablespace in an Oracle database consists of one or more physical data files. A data file can be associated with only one tablespace. You can increase a tablespace in two ways:
1) Add a data file to a tablespace. When you add another data file to an existing tablespace, you increase the amount of disk space allocated to the corresponding tablespace.
2) Increase the size of a data file.

Storage parameters such as INITIAL, NEXT, and MAXEXTENTS allow you to manage space allocated to a table. In the next unit, we discuss how to manage storage with these storage parameters.



The R/3 naming convention for tablespace names is defined as follows:
PSAP< tablespace_name > < extension >.

The abbreviations in the tablespace name are part of the directory name and file name of each data file. Directories and data files are numbered.

The objects located in the tablespaces SYSTEM, PSAPROLL, and PSAPTEMP belong either to the Oracle database users SYS or SYSTEM. Do not create any objects owned by other users in these tablespaces.

The objects located in the other tablespaces belong to the SAP R/3 database user SAPR3. R/3 System users do not have a database system user.

The R/3 System and SAP tools, such as SAPDBA, require that the naming conventions be observed. The installed system constitutes a logical unit, which you should not change. In this way, SAP can ensure that you receive fast and efficient support.


ORACLE DIRECTORY STRUCTURE
*****************************



Directory and file names are standardized in the R/3 environment. We recommend that you use the following standards:

Tablespace files reside in the sapdata directories.

The online redo log files reside in the origlog and mirrlog directories.

The offline redo log files are written to the saparch directory.

There should be at least 3 copies of the Oracle control file.

The profile init.ora configures the Oracle instance, and resides in directory database.

The profile init.sap configures the backup tools brbackup and brarchive, and resides in directory database.

The profile init.dba configures the sapdba tool, and resides in directory database.

The Oracle alert file is written to directory saptrace\background.

Trace files of the Oracle shadow processes are written to the directory saptrace\usertrace

During reorganization, export datasets are written to directory sapreorg.

The directories saparch, sapcheck, sapreorg, and sapbackup are used by the SAP database tools.