Page 73
Page 74
The term database is used both as the name for the entire database management environment and as a description (in Oracle terms) of the logical and physical data structures that make up the Relational Database Management System (RDBMS). As Oracle professionals, we define the Oracle database as the configuration files, datafiles, control files, and redo log files that make up the data processing environment, as well as the tables, indexes, and other structures contained within these objects.
There are two default accounts installed with every Oracle database that you should know about. The SYS schema is the owner of all of the internal database tables, structures, supplied packages, procedures, and so on. It also owns all of the V$ and data dictionary views and creates all of the packaged database roles (DBA, CONNECT, RESOURCE, and so on). SYS is the root or administrator ID of an Oracle database, and because of the all-powerful nature, you should avoid doing work logged in as it. Making a simple typo when you're logged in as SYS can be devastating.
The SYS UserID is the only one able to access certain internal data dictionary tables. Because it owns all of the data dictionary structures, it is also the schema you must log in to in order to grant explicit rights to data dictionary objects to other schemas. This is necessary when you're writing stored procedures or triggers using the data dictionary views and tables. The default password for the SYS account when a database is first installed is CHANGE_ON_INSTALL, and every DBA worth his or her salt will follow that advice to the letter.
The SYSTEM schema is also installed upon database creation and is the default schema used for DBA tasks. SYSTEM also has full rights to all database objects, and many third-party tools rely on the existence and privileges of the SYSTEM schema. The default password for the SYSTEM account is MANAGER, and like the SYS account should be changed immediately after creating a database. Many DBAs use the SYSTEM schema to perform database administration tasks, but it's preferable to create a specific schema just to do DBA tasks. This ensures that a specific UserID, linked to a specific person, is responsible for any database modifications.
Because these schemas are well known and exist in every Oracle database, it is important
to change their default passwords immediately upon database installation, securing the
accounts against unauthorized access. If security is a major issue, you might also consider making
it impossible to log in to these accounts and setting valid passwords only when it is necessary
to log in to them.
NOTE |
You can make it impossible to log in to an account by issuing ALTER USER xxx IDENTIFIED BY VALUES `password';, where `password' is any lowercase string. This sets the stored password to the actual value you give, rather than the encrypted version Oracle would store with a normal ALTER USER xxx IDENTIFIED BY `password'; command. It is impossible for Oracle to generate a lowercase encrypted password string, making it impossible to log in to the account. |
Page 75
We can group the pieces of the database into two distinct categories: objects used internally by the RDBMS, which we call system database objects, and objects that can be accessed by any process, which we call user database objects.
When referring to system database objects, we're looking at the database objects the RDBMS uses to support internal database functions. These objects are configured and created by the DBA or the server itself and are not explicitly used in end user database transactions.
The system database objects are:
An explanation of each of these objects follows.
The Initialization Parameter File(s)The initialization parameter file, or init.ora, is the primary configuration point for the RDBMS. It is nothing more than a collection of configuration keys and values, each of which controls or modifies one aspect of the operation of a database and instance. It is an ASCII text file found in $ORACLE_HOME/dbs on UNIX servers and $ORACLE_HOME/database on Windows NT servers. By default it is named initSID.ora, where SID is equal to the system identifier for the database it controls. On a UNIX server this is the filename the Oracle server will look for (where SID will be equal to the value of the $ORACLE_SID environment variable) when starting the database, if an init.ora file is not explicitly provided on the command line. Each Oracle database and instance should have its own unique init.ora file.
The init.ora file can include configuration values from other files using the IFILE parameter. It's also quite common in UNIX environments to link the $ORACLE_HOME/dbs/init.ora file to a file in another location, to allow better control and structure of the database environment installation. Undocumented parameters (used primarily by Oracle Worldwide Customer Support) are named with a leading underscore.
The init.ora is read when the database is started, before the instance is created or the control files are read. The values in the init.ora determine database and instance characteristics such as shared pool, buffer cache, and redo log buffer memory allocations, background processes to automatically start, control files to read, rollback segments to automatically bring online, and so on. Changes made to parameters in the init.ora file will not be recognized until the database is shut down and restarted.
Page 76
The default init.ora file shipped with the Oracle RDBMS, located in the $ORACLE_HOME/dbs directory, comes preconfigured with the essential init.ora parameters and different recommended (arbitrary) values for small, medium, and large databases. This file can be copied and renamed when you're creating new databases and instances.
The configuration parameters set in the init.ora file can be viewed from within a database by querying the V$PARAMETER view. This view lists all of the init.ora parameters and their values, and each one has a flag indicating whether the parameter value is the server default or not.
Explanations of the parameters contained within the default init.ora file are given in Table 6.1. For a more comprehensive list, consult the Oracle Server Reference Manual contained within your server documentation set.
Table 6.1 Common init.ora Parameters
Parameter Name | Use |
audit_trail | Enables or disables writing records to the audit trail. Note that this merely allows auditing; audit actions must be configured separately. |
background_dump_dest | Destination directory for Oracle background process trace files, including alert.log. |
compatible | Compatibility level of the database. Prevents the use of database features introduced in versions higher than the value of this parameter. |
control_files | The control files for this database. |
db_block_buffers | Number of database blocks contained in the buffer cache. db_block_buffers ¥ db_block_size = size of database buffer cache, in bytes. |
db_block_size | Size of the Oracle database block. Cannot be changed once the database has been created. |
db_files | Maximum number of database files that can be opened. |
db_name | Optional name of the database. If used, must match the database name used in the CREATE DATABASE statement. |
db_file_multiblock_read_count | Maximum number of database blocks read in one I/O. Used for sequential scans, and important when tuning full table scans. |
dml_locks | Maximum number of DML locks for all tables by all users of the database. |
log_archive_dest | Destination of archived redo log files. |