MANNUVAL DATABSE CREATION
Complete
the following steps to create a database with the
CREATE DATABASE statement. The examples
create a database named mynewdb.
Step 1: Specify an
Instance Identifier (SID)
ORACLE_SID is used to distinguish
this instance from other Oracle Database instances that you may create later
and run concurrently on the same host computer. The maximum number of
characters for ORACLE_SID is 12, and only letters
and numeric digits are permitted. On some platforms, the SID is case-sensitive
ORACLE_SID=mynewdb
export ORACLE_SID
Step 2: Ensure That the
Required Environment Variables Are Set
export
ORACLE_BASE=/d01/base
export
ORACLE_HOME=/d01/base/home
export ORACLE_SID=mynewdb
export
PATH=$ORACLE_HOME/bin:$PATH
Step 3: Choose a
Database Administrator Authentication Method
You must be authenticated and granted appropriate system
privileges in order to create a database. You can authenticate as an
administrator with the required privileges in the following ways:
·
With a password file
·
With operating system authentication
The password file
creation syntax of the
ORAPWD command is as follows:ORAPWD FILE=filename [ENTRIES=numusers]
[FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}
ORAPWD FILE=orapwdmynewdb password=oracle
$ORACLE_HOME/bin/orapwd
file=orapw$ORACLE_SID password=srikanth entries=5
Step 4: Create the
Initialization Parameter File
When an Oracle
instance starts, it reads an initialization parameter file. This file can be a
text file, which can be created and modified with a text editor, or a binary
file, which is created and dynamically modified by the database. The binary
file, which is preferred, is called a server parameter file. In this step, you create a text
initialization parameter file. In a later step, you create a server parameter
file from the text file.
The sample text initialization parameter file is named init.ora
and is found in the following location on most platforms:
$ORACLE_HOME/dbs
The following is the content of the sample file:
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you start by providing
# a starting point to customize your RDBMS installation for your site.
#
# NOTE: The values that are used in this file are only intended to be used
# as a starting point. You may want to adjust/tune those values to your
# specific hardware and needs. You may also consider using Database
# Configuration Assistant tool (DBCA) to create INIT file and to size your
# initial set of tablespaces based on the user input.
###############################################################################
# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.1.0'
For
example, to create a database with a global database name of
mynewdb.orafact.com, edit the parameters of the new parameter
file as follows:DB_NAME = mynewdb
DB_DOMAIN = orafact.com
ØDB_NAMEmust be set to a text string of no more than eight characters
ØDB_DOMAINis a text string that specifies the network domain where the database is created
Ø A flash recovery area is a location in which Oracle Database can store and manage files related to backup and recovery. It is distinct from the database area, which is a location for the current database files
You specify a flash recovery area with the following
initialization parameters:
·
DB_RECOVERY_FILE_DEST: Location of the flash
recovery area. This can be a directory, file system, or Automatic Storage
Management (ASM) disk group. It cannot be a raw file system.
In an Oracle Real
Application Clusters (RAC) environment, this location must be on a cluster file
system, ASM disk group, or a shared directory configured through NFS.
·
DB_RECOVERY_FILE_DEST_SIZE: Specifies the maximum
total bytes to be used by the flash recovery area. This initialization
parameter must be specified beforeDB_RECOVERY_FILE_DEST is enabled.
Ø The
CONTROL_FILES initialization parameter
specifies one or more control filenames for the database. When you execute the CREATE DATABASE statement, the control
files listed in the CONTROL_FILES parameter are created.
Step 6: Connect to the
Instance
Start
SQL*Plus and connect to your Oracle Database instance with the SYSDBA system privilege.
- To authenticate with a password
file, enter the following commands, and then enter the SYS password when prompted:
·
$ sqlplus /nolog
·
SQL> CONNECT SYS AS
SYSDBA
SQL*Plus
outputs the following message:
Connected to an idle
instance.
Step 7: Create a
Server Parameter File
CREATE SPFILE FROM PFILE;
Start
an instance without mounting a database. Typically, you do this only during
database creation or while performing maintenance on the database. Use theSTARTUP command with the NOMOUNT clause. In this example, because the
initialization parameter file or server parameter file is stored in the default
location, you are not required to specify the PFILE clause:
STARTUP NOMOUNT
Step 9: Issue the
CREATE DATABASE Statement
The
following statement creates database mynewdb. This database name must agree with the DB_NAME parameter in the initialization
parameter file. This example assumes the following:
- The initialization parameter
file specifies the number and location of control files with the CONTROL_FILES parameter.
- The directory /u01/app/oracle/oradata/mynewdb exists.
CREATE DATABASE
mynewdb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1
('/u01/app/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2
('/u01/app/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3
('/u01/app/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE
'/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE
'/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE
'/u01/app/oracle/oradata/mynewdb/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE
UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE
'/u01/app/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO
TABLESPACE undotbs
DATAFILE
'/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE
UNLIMITED;
Step 10: Create
Additional Tablespaces
To
make the database functional, you need to create additional tablespaces for
your application data. The following sample script creates some additional
tablespaces:
CREATE TABLESPACE
apps_tbs LOGGING
DATAFILE
'/u01/app/oracle/oradata/mynewdb/apps01.dbf'
SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- create a tablespace
for indexes, separate from user tablespace (optional)
CREATE TABLESPACE
indx_tbs LOGGING
DATAFILE
'/u01/app/oracle/oradata/mynewdb/indx01.dbf'
SIZE 100M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
Step 11: Run Scripts
to Build Data Dictionary Views
Run
the scripts necessary to build data dictionary views, synonyms, and PL/SQL
packages, and to support proper functioning of SQL*Plus:
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
EXIT
The
at-sign (@) is shorthand for the command that runs a
SQL*Plus script. The question mark (?) is a SQL*Plus variable indicating the Oracle home directory.
The following table contains descriptions of the scripts:
|
Script
|
Description
|
|
CATALOG.SQL
|
Creates the views of the data
dictionary tables, the dynamic performance views, and public synonyms for
many of the views. Grants PUBLIC access
to the synonyms.
|
|
CATPROC.SQL
|
Runs all scripts required for or
used with PL/SQL.
|
|
PUPBLD.SQL
|
Required for SQL*Plus. Enables
SQL*Plus to disable commands by user.
|
Step 12: Run Scripts
to Install Additional Options (Optional)
You
may want to run other scripts. The scripts that you run are determined by the
features and options you choose to use or install. Many of the scripts
available to you are described in the Oracle Database Reference.
If
you plan to install other Oracle products to work with this database, see the
installation instructions for those products. Some products require you to
create additional data dictionary tables. Usually, command files are provided
to create and load these tables into the database data dictionary.
Take a full backup of
the database to ensure that you have a complete set of files from which to
recover if a media failure occurs. For information on backing up a database,
see Oracle Database Backup and Recovery User's
Guide.
You
might want to configure the Oracle instance to start automatically when its
host computer restarts. See your operating system documentation for
instructions. For example, on Windows, use the following command to configure
the database service to start the instance upon computer restart:
ORADIM -EDIT -SID sid
-STARTMODE AUTO -SRVCSTART SYSTEM [-SPFILE]
You
must use the -SPFILE argument if you want the instance to
read an SPFILE upon automatic restart.
No comments:
Post a Comment