There are
basically two ways to create an Oracle database:
1. Using the Database Configuration Assistant
(DBCA)
2. With the SQL create database statement
This article shows you steps using second option to create
a database manually on Linux.
STEP 1: Install oracle software first using ./runinstaller
STEP 2: Create all the
necessary directories. Followings are my directories:
testdb1]$ ls
admin backup archive
admin]$ ls
data redo control bdump udump adump
cdump
STEP 3: Next prepare the database creation script.
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.
Following is my script
"testdb1.sql"
CREATE DATABASE
"testdb1"
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1
'/home/oracle/testdb1/admin/redo/redo1.log' SIZE 10M,
GROUP 2
'/home/oracle/testdb1/admin/redo/redo2.log' SIZE 10M,
GROUP 3
'/home/oracle/testdb1/admin/redo/redo3.log' SIZE 10M
DATAFILE
'/home/oracle/testdb1/admin/data/system.dbf'
size 100m,
'/home/oracle/testdb1/admin/data/usr04.dbf'
size 10m
sysaux datafile
'/home/oracle/testdb1/admin/data/sysaux.dbf' size 100m
undo tablespace undotbs
datafile
'/home/oracle/testdb1/admin/data/undo.dbf' size 50m
CHARACTER SET US7ASCII;
STEP 4: Prepare the init file. Like this one
[inittestdb1.ora]
*.audit_file_dest='/home/oracle/testdb1/admin/adump'
*.background_dump_dest='/home/oracle/testdb1/admin/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/home/oracle/testdb1/admin/control/control01.ctl',
'/home/oracle/testdb1/admin/control/control02.ctl','/home/oracle/testdb1/admin/control/control03.ctl'
*.core_dump_dest='/home/oracle/testdb1/admin/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='testdb1'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=indiaXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/home/oracle/testdb1/archive'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=200278016
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=601882624
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/home/oracle/testdb1/admin/udump'
*.db_recovery_file_dest='/home/oracle/testdb1/backup'
*.db_recovery_file_dest_size=2147483648
STEP 5: Now perform the following steps:
$ export ORACLE_SID=testdb1
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/inittestdb1.ora
nomount
ORACLE instance started.
Total System Global Area 603979776
bytes
Fixed Size 1263176 bytes
Variable Size 167774648 bytes
Database Buffers 427819008 bytes
Redo Buffers 7122944 bytes
SQL> @testdb1.sql
Database created
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 6: Run the catalog.sql and catproc.sql scripts
So your database is create. Now just run the catalog.sql and catproc.sql
scripts.
so run this script in open stage of the instance
You will find the in $ cd
$ORACLE_HOME/rdbms/admin
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL>
@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> select name from v$database;
NAME
---------
TESTDB1
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:
SQL> 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.
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.
Now Database is ready to use
I
hope this article helped you. Your suggestions/feedback are most welcome.
Keep
learning... Have a great day!!!
