Home > Articles

Creating a Database

This chapter is from the book

Create a Database Manually

Even if your company standardizes on using the GUI tools, such as DBCA, it would be a good idea to create a database manually at least once, not only to help you with this test (although it will help you to pass this portion of the exam), but so that you have an idea of what the DBCA is doing behind the scenes. This knowledge will help you to make more informed decisions in creating your databases, going forward, regardless of how you create them.

Step 1: Decide on Your Instance's System Identifier (SID)

The first step in creating the database is to determine the System Identifier (SID). This is what the environment will be set to when your database starts up and shuts down, and what the instance processes will have in their names as unique identifiers.

Step 2: Create the Initialization Parameter File

In the ORACLE_HOME/dbs directory, a set of generic initialization files are available for you to copy and customize to your situation. There is one for a regular OLTP database, one for a data warehouse database, and one for a combination database. Find the one that most closely matches what you intend your database to be used for, copy it to another name (saving the template for reuse later), and make your specific alterations to the copy.

Name the copied init.ora file init<SID>.ora because this is the default naming convention that the Oracle server looks for when it tries to start up the instance.

Edit this file's parameters so that your database and your instance will run effectively on your system.

Let's address the parameters in the initialization file in greater depth:

  • DB_NAME Must be set to a text string of eight characters or less. During database creation, the value assigned to DB_NAME is recorded in the database's datafiles, redo log files, and control files. If, when you attempt to start the database instance, the value of DB_NAME as recorded in the initialization parameter file and the database name in the control files are not the same, the database will not start.

  • DB_DOMAIN A text string that specifies the network domain where the database is created. This is often the name of the organization that owns the database. If the new database will be a part of a distributed database system, special attention needs to be paid to this parameter before database creation.

  • CONTROL_FILES Is assigned the comma-separated list of control file names to use for the new database. When you execute the CREATE DATABASE statement, the control files listed as values assigned to this parameter are created. If you fail to specify a filename for the CONTROL_FILES parameter, Oracle creates a file with a default operating system dependant filename.

  • DB_BLOCK_SIZE Specifies the standard block size of the database. This size is used in the creation of the system tablespace and by default in any other tablespaces created. Take care when determining what this value should be because after the database is created, the value cannot be changed.

  • UNDO_MANAGEMENT Determines whether the database starts in automatic undo management mode. A value of AUTO enables automatic undo management; MANUAL enables manual undo management mode. In 9i, AUTO is suggested, but MANUAL is default.

  • UNDO_TABLESPACE When the instance starts in automatic undo management mode, it needs to have a tablespace in which to store its undo information. The default is SYS_UNDOTBS and is automatically created if you execute a CREATE DATABASE statement with the UNDO_MANAGEMENT initialization parameter set to AUTO and no UNDO TABLESPACE statement in the CREATE DATABASE statement.

Step 3: Connect to and Start the Instance

Connect to the instance as SYSDBA either using operating system authentication or the password file method of authentication and start up the instance using the STARTUP command.

>sqlplus '/ as sysdba'

NOTE

If your parameter file is not in the default location or is not named init<SID>.ora, you may need to specify the PFILE clause in the STARTUP command for the instance to start.

Because you do not yet have a database attached to the instance, you need to start up the instance in NOMOUNT state:

SQL> startup nomount;

The instance is now started and ready for the CREATE DATABASE command.

There are several options for starting up the database. The following are the ways that you can start up the instance and the database, and what each means:

  • STARTUP NOMOUNT Starts the instance but does not mount the database.

  • STARTUP MOUNT Starts the instance and mounts the database but does not open the database.

  • STARTUP OPEN Starts the instance and mounts and opens the database.

  • STARTUP RESTRICT Starts the instance, mounts and opens the database; however, access is restricted to users with restricted session privileges.

  • STARTUP RECOVER Starts the instance but leaves the database closed and begins recovery for whatever failure scenario occurred.

  • STARTUP FORCE Forces the instance to shutdown abort and immediately startup open. This option should only be used for instances having problems either starting or stopping.

Step 4: Issue the CREATE DATABASE Statement

To create the database, issue the CREATE DATABASE command. The CREATE DATABASE command creates data files, control files, redo log files, the system tablespace along with the data file associated with it, and a system rollback segment. It creates the tables that underlie the data dictionary, assigns the character set to the database, optionally sets the database time zone, and mounts and opens the database for use.

The general format follows:

CREATE DATABASE [database name]
[CONTROLFILE REUSE]
[LOGFILE [GROUP integer] file specification]
[MAXLOGFILES integer]
[MAXLOGMEMBERS integer]
[MAXLOGHISTORY integer]
[MAXDATAFILES integer]
[MAXINSTANCES integer]
[ARCHIVELOG|NOARCHIVELOG]
[CHARACTER SET charset]
[NATIONAL CHARACTER SET charset]
[DATAFILE filespec [autoextend]]
[DEFAULT TEMPORARY TABLESPACE tablespace filespec]
[UNDO TABLESPACE tablespace DATAFILE filespec]
[SET TIME_ZONE [time_zone_region]];

Database is the name of the database that you are creating. If the name of the database is omitted, the initialization parameter DB_NAME is used. The database name should be the same as the DB_NAME parameter.

Control file reuse specifies that an existing control file identified by the control file parameter in the initialization file should be reused.

Log file group identifies the names of the log files to be used and the group to which they belong.

MAXLOGFILES specifies the maximum number of redo log files that can ever be created in the database.

MAXLOGMEMBERS specifies the maximum number of redo log file members that any given log file group can have in the database.

MAXLOGHISTORY specifies the maximum number of archive redo logs for automatic media recovery.

AUTOEXTEND enables or disables the automatic extension of SYSTEM tablespace's data files in the database.

MAXDATAFILES controls the initial sizing of the data file section of the control file at the time when the CREATE DATABASE or CREATE CONTROLFILE commands are issued. If an attempt is made to add a new file with a number greater than the value set to the MAXDATAFILES parameter, but less than the DB_FILES parameter, it causes the control file to expand automatically so that the data files section can accommodate the new files.

The MAXINSTANCES parameter is optional and is primarily used for Real Application Clusters (RAC) environments. The default, if left unspecified, is 1.

The optional ARCHIVELOG|NOARCHIVELOG statement provides the option for the database's redo log files to automatically initially be archived. This is an optional statement at database creation time. NOARCHIVELOG is the default if ARCHIVELOG is not specified. If, after database creation, you choose to change the archive log mode of the database, you can use the ALTER DATABASE command to change between the two. ARCHIVELOG establishes that the redo logs can be reused, but only after they have been archived. NOARCHIVELOG establishes that the redo logs can be reused without archiving their contents.

CHARACTER SET is the character set that the database uses to store the data.

The NATIONAL CHARACTER SET parameter is optional and is used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2.

NOTE

More on National Language Support (NLS) and its associated parameters will be covered in Chapter 7, "Globalization Support."

DATAFILE, when connected directly to the CREATE DATABASE statement, is the file specification used to specify the data files to be used for the system tablespace.

DEFAULT TEMPORARY TABLESPACE instructs the database to assign this tablespace to all users created without having a temporary tablespace specified.

UNDO TABLESPACE creates and names the undo tablespace and the associated data files that should be used to store undo records for the database if you have specified (as Oracle suggests) UNDO_MANAGEMENT=AUTO in the initialization parameter file.

A working example of a CREATE DATABASE script follows:

create database mydb1 controlfile reuse
Logfile 
Group 1 ('/mydatabases/mydb1/log01a.log', 
'/mydatabase02/mydb1/log01b.log') size 50M,
Group 2 ('/mydatabases/mydb1/log02a.log',
'/mydatabase02/mydb1/log02b.log') size 50M,
Group 3 ('/mydatabases/mydb1/log03a.log',
'/mydatabase02/mydb1/log03b.log') size 50M,
Group 4 ('/mydatabases/mydb1/log04a.log',
'/mydatabase02/mydb1/log04b.log') size 50M
Datafile '/mydatabases/mydb1/mydb1_system.dbf' 250M 
AUTOEXTEND ON MAXSIZE 500M
Undo tablespace mydb1_undo1
Datafile '/mydatabases/mydb1/mydb1_undo1.dbf' 50M
Default temporary tablespace mydb1temp tempfile
'/mydatabases/mydb1/mydb1temp1.dbf' size 75M
Extent management local 
Character set US7ASCII
MAXLOGFILES 10
MAXLOGMEMBERS 10
MAXLOGHISTORY 1
MAXDATAFILES 500; 

CAUTION

If you use the CONTROLFILE REUSE command, make absolutely sure that you don't specify a control file attached to another database. The reuse command will cause it to be overwritten. Although this will not cause the database and instance to crash (the one that had the control file overwritten) immediately, it is cause for care and concern. Oracle will not tell you that you are overwriting the wrong file, and, if you don't realize it, this can cause the overwritten database to not start after it is shut down.

If you are using Oracle Managed File (OMF) systems and you have specified the appropriate OMF initialization parameters in the parameter file (DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_DEST_n), the following statement creates the database:

Startup nomount
Create database
Default temporary tablespace mydb1temp;

At this point, your database now contains data files (at least a limited number of them), control files, redo log files, the SYS and SYSTEM users, and all the internal data dictionary tables, but no data dictionary views. You can see the dynamic performance views, such as V$LOGFIEL, V$CONTROLFILE, and V$DATAFILE. If you have not yet changed the default passwords for SYS (change_on_install) and SYSTEM (manager), now is a good time to do it.

Step 5: Run Scripts to Build Data Dictionary Views

Now that you have a database, you need to complete the data dictionary views and procedures. In the ORACLE_HOME/rdbms/admin directory, a pair of scripts need to be run to complete the database's data dictionary creation. CATALOG.sql creates the data dictionary views from which you can gain insight into the contents of the database. CATPROC.sql creates the objects required to use PL/SQL.

Step 6: Run Scripts to Install Additional Options

This step is optional. However, if you need to add any of the optional products, such as Spatial or InterMedia, you can now run the scripts to add these products to your database. You can locate the scripts and the following procedures in the Oracle documentation.

Step 7: Create a Server Parameter File

This step is optional, although highly recommended by Oracle. Your Oracle database was created by starting the instance with a parameter file, or PFILE. Because it is editable, you can migrate, easily, your new database to using a server parameter file. Creation of the server parameter file (or spfile) is accomplished by issuing the following statement:

Create spfile from pfile;

This command looks in the default location (ORACLE_HOME/dbs) for an initialization parameter file (or PFILE) with the default name. If your database is named mydb1, the default name would be initmydb1.ora. The command creates an spfile with the default name in the same default location unless otherwise specified.

NOTE

More on parameter files and server parameter files can be found in Chapter 4.

Step 8: Back Up the Database

Now that your database is created, take this chance to shut down the database and take a cold backup of the new database.

Step 9: Create Additional Tablespaces

After the backups are finished, you can restart the database and create new tablespaces, tables, and users; add data; and in general, open the new database for business.

Pearson IT Certification Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from Pearson IT Certification and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about Pearson IT Certification products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites; develop new products and services; conduct educational research; and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by Adobe Press. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.pearsonitcertification.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020