Problem
You realize when initially creating a database that some features (when enabled) have long-lasting implications for table performance and availability. Specifically, when creating the database, you want to do the following:
- Enforce that every tablespace ever created in the database must be locally managed. Locally managed tablespaces deliver better performance than the obsolete dictionary-managed technology.
- Ensure users are automatically assigned a default permanent tablespace. This guarantees that when users are created they are assigned a default tablespace other than
SYSTEM. With the deferred segment feature (more on this later), if a user has theCREATE TABLEprivilege, then it is possible for that user to create objects in theSYSTEMtablespace even without having a space quota on the SYSTEM tablespace. This is undesirable. It’s true they won’t be able to insert data into tables without appropriate space quotas, but they can create objects, and thus inadvertently clutter up the SYSTEM tablespace. - Ensure users are automatically assigned a default temporary tablespace. This guarantees that when users are created they are assigned the correct temporary tablespace when no default is explicitly provided.
Solution
There are two different tools that you can use to create an Oracle database:
- SQL*Plus using the
CREATE DATABASEstatement - Database Configuration Assistant (dbca)
These techniques are described in the following subsections.
SQL*Plus
Use a script such as the following to create a database that adheres to reasonable standards that set the foundation for a well-performing database:
CREATE DATABASE O12C
MAXLOGFILES 16
MAXLOGMEMBERS 4
MAXDATAFILES 1024
MAXINSTANCES 1
MAXLOGHISTORY 680
CHARACTER SET AL32UTF8
DATAFILE
'/u01/dbfile/O12C/system01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE undotbs1 DATAFILE
'/u02/dbfile/O12C/undotbs01.dbf'
SIZE 800M
SYSAUX DATAFILE
'/u01/dbfile/O12C/sysaux01.dbf'
SIZE 500M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/u02/dbfile/O12C/temp01.dbf'
SIZE 500M
DEFAULT TABLESPACE USERS DATAFILE
'/u01/dbfile/O12C/users01.dbf'
SIZE 50M
LOGFILE GROUP 1
('/u01/oraredo/O12C/redo01a.rdo',
'/u02/oraredo/O12C/redo01b.rdo') SIZE 200M,
GROUP 2
('/u01/oraredo/O12C/redo02a.rdo',
'/u02/oraredo/O12C/redo02b.rdo') SIZE 200M,
GROUP 3
('/u01/oraredo/O12C/redo03a.rdo',
'/u02/oraredo/O12C/redo03b.rdo') SIZE 200M
USER sys IDENTIFIED BY f0obar
USER system IDENTIFIED BY f0obar;The prior CREATE DATABASE script helps establish a good foundation for performance by enabling features such as the following:
- Defines the SYSTEM tablespace as locally managed via the
EXTENT MANAGEMENT LOCALclause; this ensures that all tablespaces ever created in database are locally managed. Starting with Oracle Database 12c, the SYSTEM tablespace is always created as locally managed. - Defines a default tablespace named USERS for any user created without an explicitly defined default tablespace; this helps prevent users from being assigned the SYSTEM tablespace as the default.
- Defines a default temporary tablespace named TEMP for all users; this helps prevent users from being assigned the SYSTEM tablespace as the default temporary tablespace. Users created with a default temporary tablespace of SYSTEM can have an adverse impact on performance, as this will cause contention for resources in the SYSTEM tablespace.
Solid performance starts with a correctly configured database. The prior recommendations help you create a reliable infrastructure for your table data.
dbca
Oracle’s dbca utility has a graphical interface and a command line mode from which you can configure and create databases. The visual tool is easy to use and has a very intuitive interface. In Linux/Unix environments to use the dbca in graphical mode, ensure you have the proper X software installed, then issue the xhost + command, and make certain your DISPLAY variable is set; for example:
$ xhost +
$ echo $DISPLAY
:0.0
$ xhost +
$ echo $DISPLAY
:0.0The dbca is invoked from the operating system as follows:
$ dbcaYou’ll be presented with a series of screens that allow you to make choices on the configuration. You can choose the “Advanced Mode” option which gives you more control on aspects such as file placement and multiplexing of the online redo logs.
By default, the dbca creates a database with the following characteristics:
- Defines the SYSTEM tablespace as locally managed.
- Defines a default tablespace named USERS for any user created without an explicitly defined default tablespace.
- Defines a default temporary tablespace named TEMP for all users.
Like the SQL*Plus approach, these are all desirable features that provide a good foundation to build applications on.
The dbca utility also allows you to create a database in silent mode, without the graphical component. Using dbca in silent mode with a response file is an efficient way to create databases in a consistent and repeatable manner. This approach also works well when you’re installing on remote servers, which could have a slow network connection or not have the appropriate X software installed.
You can also run the dbca in silent mode with a response file. In some situations, using dbca in graphical mode isn’t feasible. This may be due to slow networks or the unavailability of X software. To create a database, using dbca in silent mode, perform the following steps:
- Locate the dbca.rsp file.
- Make a copy of the dbca.rsp file.
- Modify the copy of the dbca.rsp file for your environment.
- Run the dbca utility in silent mode.
First, navigate to the location in which you copied the Oracle database installation software, and use the find command to locate dbca.rsp:
$ find . -name dbca.rsp
./12.1.0.1/database/response/dbca.rspCopy the file so that you’re not modifying the original (in this way, you’ll always have a good, original file):
$ cp dbca.rsp mydb.rspNow, edit the mydb.rsp file. Minimally, you need to modify the following parameters: GDBNAME, SID, SYSPASSWORD, SYSTEMPASSWORD, SYSMANPASSWORD, DBSNMPPASSWORD, DATAFILEDESTINATION, STORAGETYPE, CHARACTERSET, and NATIONALCHARACTERSET. Following is an example of modified values in the mydb.rsp file:
[CREATEDATABASE]
GDBNAME = "O12C"
SID = "O12C"
TEMPLATENAME = "General_Purpose.dbc"
SYSPASSWORD = "f00bar"
SYSTEMPASSWORD = "f00bar"
SYSMANPASSWORD = "f00bar"
DBSNMPPASSWORD = "f00bar"
DATAFILEDESTINATION ="/u01/dbfile"
STORAGETYPE="FS"
CHARACTERSET = "AL32UTF8"
NATIONALCHARACTERSET= "UTF8"Next, run the dbca utility in silent mode, using a response file:
$ dbca -silent -responseFile /home/oracle/orainst/mydb.rspYou should see output such as
Copying database files
1% complete
...
Creating and starting Oracle instance
...
62% complete
Completing Database Creation
...
100% complete
Look at the log file ... for further details.If you look in the log files, note that the dbca utility uses the rman utility to restore the data files used for the database. Then, it creates the instance and performs post-installation steps. On a Linux server you should also have an entry in the /etc/oratab file for your new database.
Many DBAs launch dbca and configure databases in the graphical mode, but a few exploit the options available to them using the response file. With effective utilization of the response file, you can consistently automate the database creation process. You can modify the response file to build databases on ASM and even create RAC databases. In addition, you can control just about every aspect of the response file, similar to launching the dbca in graphical mode.
Tip
You can view all options of the dbca via the help parameter:
dbca -help
How It Works
A properly configured and created database will help ensure that your database performs well. It is true that you can modify features after the database is created. However, often a poorly crafted CREATE DATABASE script leads to a permanent handicap on performance. In production database environments, it’s sometimes difficult to get the downtime that might be required to reconfigure an improperly configured database. If possible, think about performance at every step in creating an environment, starting with how you create the database.
When creating a database, you should also consider features that affect maintainability. A sustainable database results in more uptime, which is part of the overall performance equation. The CREATE DATABASE statement in the “Solution” section also factors in the following sustainability features:
- Creates an automatic UNDO tablespace (automatic undo management is enabled by setting the
UNDO_MANAGEMENTandUNDO_TABLESPACE initialization parameters); this allows Oracle to automatically manage the rollback segments. This relieves you of having to regularly monitor and tweak. - Places datafiles in directories that follow standards for the environment; this helps with maintenance and manageability, which results in better long-term availability and thus better performance.
- Sets passwords to non-default values for DBA-related users; this ensures the database is more secure, which in the long run can also affect performance (e.g., if a malcontent hacks into the database and deletes data, then performance will suffer).
- Establishes three groups of online redo logs, with two members each, sized appropriately for the transaction load; the size of the redo log directly affects the rate at which they switch. When redo logs switch too often, this can degrade performance. Keep in mind that when you create a new database that you may not know the appropriate size and will have to adjust this later.
You should take the time to ensure that each database you build adheres to commonly accepted standards that help ensure you start on a firm performance foundation.
If you’ve inherited a database and want to verify the default permanent tablespace setting, use a query such as this:
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';If you need to modify the default permanent tablespace, do so as follows:
SQL> alter database default tablespace users;To verify the setting of the default temporary tablespace, use this query:
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
To change the setting of the temporary tablespace, you can do so as follows:
SQL> alter database default temporary tablespace temp;You can verify the UNDO tablespace settings via this query:
SELECT name, value
FROM v$parameter
WHERE name IN ('undo_management','undo_tablespace');If you need to change the undo tablespace, first create a new undo tablespace and then use the ALTER SYSTEM SET UNDO_TABLESPACE statement.
