Learn how to create a new database manually. As part of its database software files, Oracle Database provides a sample initialization parameter file, which can you can edit to suit your needs.
You can choose to create database creation scripts using Oracle Database Configuration Assistant.
The following are the types of Database creation tasks:
Copy an existing database and delete the old database.
Copy an existing database and keep the old database.
Create a new database when no database exists on your system.
Manual Database Creation Tasks
Use Manual Database Creation Tasks to understand the manual tasks involved in creating a new database for each of these database creation categories. Each step is explained in detail in the following subsections.
Table 4-1 Manual Database Creation Tasks
|Task||Copy existing database and delete old database||Copy existing database and keep old database||Create new database when no database exists on system|
|About Exporting an Existing Database||
|Deleting Database Files||
|Modifying the Initialization Parameter File||
|Starting an Oracle Database Instance||
|About Creating and Starting an Oracle Database Service||
|Adding the CREATE DATABASE Statement in a Script||
|Running the CREATE DATABASE Script||
|About Importing a Database||
|Updating ORACLE_SID in the Registry||
Only if you change the default
|Backing Up the New Database||
Note:If you copy data from an existing database to the new database, select Yes; otherwise, No. Also, if you import tables and other objects from the existing database, select Yes; otherwise, No.
An example in the following sections demonstrates how to create a database. In this example, the existing database is the starter database with a
orcl located in directory
orcl to a new database with a database name and
prod located in the directory
\oradata\prod. Then, delete the starter database
About Exporting an Existing Database
You are required to export an existing database only if you intend to copy its contents to a new database.
If you are working with data from an earlier Oracle release, then you can use Export for this task. If you are using Oracle Database 10g Release 1 (10.1) or later data, then Oracle recommends that you use Data Pump Export because it supports new Oracle Database 10g Release 1 (10.1) or later features, such as floating points.
Although you can start Data Pump Export or Export in either the parameter mode or an interactive mode, Oracle recommends parameter mode. Interactive mode provides less functionality than the parameter mode and exists for backward compatibility only.
The syntax for Data Pump Export parameter mode is:
The syntax for Data Pump Export interactive mode is:
Enter only the command
If you use the parameter mode, then Data Pump Export considers the file names and the directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in the
DUMPFILE= parameter in triple quotation marks. For example:
If Data Pump Export is used in an interactive mode, then the file name or the directory name can contain a space without quotation marks.
The syntax for Export parameter mode is:
The syntax for the Export interactive mode is:
C:\> exp SYSTEM Password: password
Enter only the command
If you use the parameter mode, then Export considers the file names and the directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in the
FILE= parameter in triple quotation marks. For example:
If Export is used in an interactive mode, then the file name or the directory name can contain a space without quotation marks.
Oracle Database Utilities for more information about using Data Pump Export or Export
Exporting All Data from an Existing Database
Describes how to export all data from an existing database to a new database.
ORACLE_SIDto the database service of the database whose contents you intend to export. For example, if the database you intend to export is the starter database
orcl, then enter the following at the command prompt. Note that there are no spaces around the equal sign (
C:\> set ORACLE_SID=orcl
- If the existing database is Oracle Database 10g Release 1 (10.1) or later, then start Data Pump Export from the command prompt:
C:\> expdp SYSTEM DUMPFILE=myexp.dmp FULL=y LOG=myexp.log Password: password
You now have a full database export of the starter database
orclin the file
myexp.dmp. All messages from Data Pump Export are logged in file
- If the existing database is earlier than Oracle Database 10g Release 1 (10.1), then start Export from the command prompt:
C:\> exp SYSTEM FILE=myexp.dmp FULL=y LOG=myexp.log Password: password
You now have a full database export of the starter database
orclin the file
myexp.dmp. All messages from Export are logged in the file
Deleting Database Files
Deleting database files is required only when you copy an existing database to a new database to replace the old database.
In the following example, you delete the database files of the starter database
To delete database files:
- Shut down starter database
orclat the command prompt:
C:\> oradim -SHUTDOWN -SID orcl -SHUTTYPE inst -SHUTMODE immediate
- Delete the following files from the directory
control01.ctl control02.ctl control03.ctl index01.dbf drsys01.dbf cwmlite01.dbf example01.dbf system01.dbf temp01.dbf tools01.dbf undotbs01.dbf user01.dbf xdb01.dbf redo01.log redo02.log redo03.log
Modifying the Initialization Parameter File
Describes how to modify the initialization parameter file.
To use the starter database
orcl as the basis for your new database:
Place the copy in
Modify the file by performing the following tasks:
Starting with Oracle9i Release 2 (9.2), nesting of quotation marks using the backslash (\) escape character is no longer supported. This affects how Oracle Database interprets the parameter values in your initialization parameter file. For example, if you specified
CONTROL_FILES = "ctlfile\'1.ora"in releases before release 9.2, the file name was interpreted as
ctlfile'1.ora. Starting with release 9.2, the file name will be interpreted as
Oracle highly recommends modifying your parameter files to remove such references and other methods of nesting quotation marks in the initialization parameter values.
If you do not have an existing database on your system, then you cannot copy an existing initialization parameter file to use as the basis for your new initialization parameter file. However, you can use the sample initialization parameter file
This is the basis for the initialization parameter file for the database
If you use the
initsmpl.orafile as the basis for the initialization parameter file, then the following parameters must be set to the indicated values, otherwise you cannot start database
DB_NAMEindicates the database name and must match the name used in the
CREATE DATABASEstatement. Give a unique database name to each database. You can use eight characters for a database name. The name is not required to match the
SIDof the database service.
CONTROL_FILES = (
CONTROL_FILESlists the database control files. You do not have to control files on your file system at this point, because control files are created when you run the
CREATE DATABASEstatement. Ensure that you specify the complete path and the file name, including the drive letter.
Modifying the initialization parameter
DB_FILESis not required, but it is recommended to optimize performance. Set this parameter to the same number as the value of the
MAXDATAFILESoption of the
CREATE DATABASEstatement. The value of
100is used for this example.
DIAGNOSTIC_DESTinitialization parameter sets the location of the Automatic Diagnostic Repository (ADR), which is a directory structure stored outside of the database. The ADR is used in problem diagnostics.
ORACLE_HOME\logif the environment variable
ORACLE_BASEis not set.
ORACLE_BASEvariable if the environment variable
Oracle Database Installation Guide for Microsoft Windows for information about ADR
Oracle Database Reference for information about other initialization parameters that you can add or modify
About Creating and Starting an Oracle Database Service
Learn how to create and start an Oracle Database service.
Perform either of the following steps:
Copy an existing database to a new database and keep the old database
Create a new database when you have no other database to copy
Before you create the database, first create a Windows service to run the database. This service is the Oracle Database process,
oracle.exe, installed in the form of a Windows service.
Use ORADIM to create the service.
Creating and Starting an Oracle Database Service
Learn how to create and start an Oracle Database service.
To create and start an Oracle Database service:
- Run ORADIM from the command prompt:
C:\> oradim -NEW -SID prod -STARTMODE manual -PFILE "C:\app\username\admin\prod\pfile\init.ora"
Note that the previously created initialization parameter file is specified, with complete path, including drive name. You can check if the service is started in the Services window of the Control Panel. ORADIM automatically creates Oracle Database services under the Oracle Home User account. If the Oracle Home User account is a Windows Local User Account or a Windows Domain User Account, then ORADIM prompts for its password.
- Set the value of
prod. Note that there are no spaces around the equal sign (=) character:
C:\> set ORACLE_SID=prod
Access to Oracle Wallets in a File System for Oracle Database Services
Discusses about accessing Oracle Wallets.
When an Oracle wallet is created in the file system, only the user creating the wallet is granted access to that wallet by wallet creation tools. Therefore, Oracle Database services (running as the Windows User Account) might not be able to access the wallet unless you explicitly grant access to the wallet using Windows tools.
Starting an Oracle Database Instance
Learn how to start an instance without mounting a database.
SQL> STARTUP NOMOUNT
You must not specify the
PFILE clause in this example, because the initialization parameter file is stored in the default location. At this point, there is no database. Only the System Global Area (SGA) is created and the background processes are started in preparation for the creation of a new database.
Adding the CREATE DATABASE Statement in a Script
CREATE DATABASE statement is a SQL statement that creates the database.
A script containing this statement can be used anytime you create a database.
CREATE DATABASE statement has the following parameters:
MAXDATAFILES- default value: 32, maximum value: 65534
MAXLOGFILES- default value: 32, maximum value: 255
CHARACTER SET parameter determines the database character set of the new database. The default value is US7ASCII, however the recommended value is AL32UTF8. AL32UTF8 is the Oracle implementation of the Unicode Standard character set in UTF-8 encoding form. Unicode is suitable for storing text in practically any written language of the world.
When you run the
CREATE DATABASE statement, Oracle Database performs several operations depending upon the clauses that you specified in the
CREATE DATABASE statement or the initialization parameters that you have set.
Oracle Managed Files is a feature that works with the
CREATE DATABASE statement to simplify administration of Oracle Database. Oracle Managed Files eliminates the requirement to directly manage operating system files comprising an Oracle Database server, because you specify operations in terms of database objects rather than file names.
To create the database
prod, copy and save the following statement in a file named
CREATE DATABASE prod USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password MAXLOGFILES 5 MAXDATAFILES 100 DATAFILE 'C:\app\
username\oradata\prod\system01.dbf' SIZE 325M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED UNDO TABLESPACE "UNDOTBS" DATAFILE 'app\
username\oradata\prod\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED CHARACTER SET AL32UTF8 logfile 'C:\app\username\oradata\prod\redo01.log' size 100M reuse, 'C:\app\username\oradata\prod\redo02.log' size 100M reuse, 'C:\app\username\oradata\prod\redo03.log' size 100M reuse Oracle Database Installation Guide for Microsoft WindowsEXTENT MANAGEMENT LOCAL;
Oracle Database Administrator's Guide for more information about using Oracle Managed Files
Oracle Database Installation Guide for Microsoft Windows for more information about recommended database character sets
Running the CREATE DATABASE Script
Use this procedure to run the CREATE DATABASE script.
To use the SQL script to create a database:
- Verify that the service is started in the Control Panel. In this example, the service name is
OracleServicePROD, and its status column must display
Started. If not, then select the service name and select Start.
C:\> set ORACLE_SID=PROD
- Start SQL*Plus from the command prompt, and connect to the database as SYSDBA:
C:\> sqlplus /NOLOG SQL> CONNECT / AS SYSDBA
- Turn on spooling to save messages:
SQL> SPOOL script_name.log
- Run the script
.sqlthat you created in Adding the CREATE DATABASE Statement in a Script.
If the database is successfully created, then the instance is started and the following message appears numerous times:
About Importing a Database
Learn how to use Data Pump Import or Import.
You can use Data Pump Import (for Oracle Database 10g Release 1 (10.1) or later data) or Import (for earlier data) to import the full export created into the new database. Although you can start Data Pump Import or Import using either the parameter mode or the interactive mode, Oracle recommends the parameter mode because it provides more functionality. Interactive mode exists solely for backward compatibility.
The syntax for Data Pump Import parameter mode is:
The syntax for Data Pump Import interactive mode is:
C:\> impdp SYSTEM Password: password
SYSTEM to begin an interactive session and let Data Pump Import prompt you for information it needs.
If you use the parameter mode, then Data Pump Import considers the file names and the directory names to be invalid if they contain one or more blank spaces. The workaround is to enclose the full path in the
DUMPFILE=parameter in triple quotation marks. For example:
If you use Data Pump Import in an interactive mode, then the file name or the directory name can contain a space without the quotation marks.
If the original database from which the export file was generated contains a tablespace that is not in the new database, then Import tries to create that tablespace with associated data files.
The easy solution is to ensure that both the databases contain the same tablespaces. Data files are not required to be identical. Only the tablespace names are important.
Updating ORACLE_SID in the Registry
If this is the first database on your computer or if you intend to make the new database the default database, then you must make a change in the registry.
Perform the following steps:
- Start Registry Editor at the command prompt:
The Registry Editor window appears.
- Select the subkey
\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0for the first Oracle home on your computer. For subsequent installations to different Oracle homes on the same computer, the path is
IDwhere ID is the unique number identifying the Oracle home.
- Locate the parameter
ORACLE_SIDon the right side of the Registry Editor window.
- Double-click the parameter name and change the data to the new
SID, which is
prodin this example.
Creating the ORACLE_SID Parameter
If you do not yet have the parameter
ORACLE_SID, because this is the first database on your system, then you must create it.
- Select New from the Edit menu.
- Select Expandable String Value from the menu list.
- A New Value #1 expandable string value name is created on the right pane of the Registry Editor window of data type
- Right-click the parameter, select Rename to rename it to
ORACLE_SIDand press Enter.
- Double-click the
ORACLE_SIDentry to change the value data to the new
An Edit String dialog box appears:
- Enter PROD in the Value data field.
- Click OK.
Registry Editor adds parameter
- Select Exit from the File menu.
Registry Editor exits.
Backing Up the New Database
Use this procedure to prevent data loss.
If anything goes wrong while operating the new database without a backup, then you must repeat the database creation procedure. Back up your database now to prevent loss of data.
To back up the new database:
- Shut down the database instance and stop the service:
C:\> oradim -SHUTDOWN -SID prod -SHUTTYPE srvc,inst -SHUTMODE immediate
ORADIMreturns the prompt immediately, you must wait for the database and the service to stop completely before continuing to Step 2. Wait until the Control Panel indicates service
OracleServicePRODhas stopped. If you do not do this, then the backup is useless because it was taken while data was being written to data files.
- Back up database files using the tool of your choice.
Database files consist of the initialization parameter file, control files, online redo log files, and data files.
When the backup is complete, you can start the database again, create users and objects, if necessary, make any other changes, and use the database.
Back up the database after making any significant changes, such as switching into the archiving mode or adding a tablespace or a data file.
Do not store database files on a compressed drive. This can result in write errors and a decreased performance.