1Z031第四章 创建数据库
这章学习目标
1、理解创建数据库的准备工作
2、用DBCA创建数据库
3、手工创建数据库
一、安装数据库
官方的资料中对如何创建数据库说的并不详细，这里只能参考相关文档来补充。最好是看具体OS下的ORACLE安装手册。如何使用DBCA创建这里不多说，每个人自己试一下就知道了。重点在手工创建数据库，纯手工。重点参考文档 Administrator Guid a96521
1、决定实例的标示（SID）
Decide on a unique Oracle system identifier (SID) for your instance and set the
ORACLE_SID environment variable accordingly. This identifier is used to avoid
confusion with other Oracle instances that you may create later and run
concurrently on your system.
The following example sets the SID for the instance and database we are about to
create:
% setenv ORACLE_SID mynewdb
The value of the DB_NAME initialization parameter should match the SID setting.
2、制订数据库管理员验证方式
You must be authenticated and granted appropriate system privileges in order to create a database. You can use the password file or operating system authentication method. Database administrator authentication and authorization is discussed in the following sections of this book:
"Database Administrator Security and Privileges" on page 1-10
"Database Administrator Authentication" on page 1-13
"Creating and Maintaining a Password File" on page 1-20
3、创建初始化参数文件
如何创建第三章关于初始化参数文件中已经说到，这里不再重复。
4、连接到实例
Start SQL*Plus and connect to your Oracle instance AS SYSDBA.
$ SQLPLUS /nolog
CONNECT SYS/password AS SYSDBA
5、启动实例
Start an instance without mounting a database. Typically, you do this only during database creation or while performing maintenance on the database. Use the STARTUP command with the NOMOUNT option. In this example, because the initialization parameter file is stored in the default location, you are not required to specify the PFILE clause:
STARTUP NOMOUNT
At this point, there is no database. Only the SGA is created and background
processes are started in preparation for the creation of a new database.
6、运行CREATE DATABASE语句
To create the new database, use the CREATE DATABASE statement. The following
statement creates database mynewdb:
CREATE DATABASE mynewdb
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
LOGFILE GROUP 1 ('/vobs/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
GROUP 2 ('/vobs/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
GROUP 3 ('/vobs/oracle/oradata/mynewdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/vobs/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
DATAFILE '/vobs/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/vobs/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
A database is created with the following characteristics:
n The database is named mynewdb. Its global database name is
mynewdb.us.oracle.com. See "DB_NAME Initialization Parameter" and
"DB_DOMAIN Initialization Parameter" on page 2-36.
n Three control files are created as specified by the CONTROL_FILES initialization
parameter. See "Specifying Control Files" on page 2-36.
n The password for user SYS is pz6r58 and the password for SYSTEM is y1tz5p.
These two clauses that specify the passwords for SYS and SYSTEM are not
mandatory in this release of Oracle9i. However, if you specify either clause, you
must specify both clauses. For further information about the use of these
clauses, see "Protecting Your Database: Specifying Passwords for Users SYS and
SYSTEM" on page 2-23.
n The new database has three online redo log files as specified in the LOGFILE
clause. MAXLOGFILES, MAXLOGMEMBERS, and MAXLOGHISTORY define limits
for the redo log. See Chapter 7, "Managing the Online Redo Log".
n MAXDATAFILES specifies the maximum number of datafiles that can be open in
the database. This number affects the initial sizing of the control file.
n MAXINSTANCES specifies that only one instance can have this database
mounted and open.
n The US7ASCII character set is used to store data in this database.
n The AL16UTF16 character set is specified as the NATIONAL CHARACRTER
SET, used to store data in columns specifically defined as NCHAR, NCLOB, or
NVARCHAR2.
n The SYSTEM tablespace, consisting of the operating system file
/vobs/oracle/oradata/mynewdb/system01.dbf, is created as specified
by the DATAFILE clause. If the file already exists, it is overwritten.
n The SYSTEM tablespace is a locally managed tablespace. See "Creating a Locally
Managed SYSTEM Tablespace" on page 2-26.
n The DEFAULT_TEMPORARY_TABLESPACE clause creates and names a default
temporary tablespace for this database. See "Creating a Default Temporary
Tablespace" on page 2-24.
n The UNDO_TABLESPACE clause creates and names an undo tablespace to be
used to store undo records for this database if you have specified UNDO_
MANAGEMENT=AUTO in the initialization parameter file. See "Using Automatic
Undo Management: Creating an Undo Tablespace" on page 2-24.
n Because the ARCHIVELOG clause is not specified in this CREATE DATABASE
statement, redo log files will not initially be archived. This is customary during
database creation and an ALTER DATABASE statement can be used later to
switch to ARCHIVELOG mode. The initialization parameters in the initialization
parameter file for mynewdb relating to archiving are LOG_ARCHIVE_DEST_1,
LOG_ARCHIVE_FORMAT, and LOG_ARCHIVE_START. See Chapter 8, "Managing
Archived Redo Logs".
See Also:
n "Understanding the CREATE DATABASE Statement" on
page 2-22
n Oracle9i SQL Reference for more information about specifying
the clauses and parameter values for the CREATE DATABASE
statement
7、 创建附加的tablespace
To make the database functional, you need to create additional files and tablespaces
for users. The following sample script creates some additional tablespaces:
CONNECT SYS/password AS SYSDBA
-- create a user tablespace to be assigned as the default tablespace for users
CREATE TABLESPACE users LOGGING
DATAFILE '/vobs/oracle/oradata/mynewdb/users01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING
DATAFILE '/vobs/oracle/oradata/mynewdb/indx01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
EXIT
For information about creating tablespaces, see Chapter 11, "Managing Tablespaces".
8、 创建数据字典视图
Run the scripts necessary to build views, synonyms, and PL/SQL packages:
CONNECT SYS/password AS SYSDBA
@/vobs/oracle/rdbms/admin/catalog.sql
@/vobs/oracle/rdbms/admin/catproc.sql
EXIT
The following table contains descriptions of the scripts:
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.
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 Oracle9i Database Reference.
See your Oracle installation guide for your operating system for the location of
these scripts.
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 Oracle9i Database Reference.
See your Oracle installation guide for your operating system for the location of these scripts.
9、 运行script安装附加的属性（可选）
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’s data dictionary.
See your Oracle documentation for the specific products that you plan to install for
installation and administration instructions.
10、创建服务器参数文件（建议）
Oracle recommends you create a server parameter file as a dynamic means of
maintaining initialization parameters. The server parameter file is discussed in
"Managing Initialization Parameters Using a Server Parameter File" on page 2-44.
The following script creates a server parameter file from the text initialization
parameter file and writes it to the default location. The instance is shut down, then
restarted using the server parameter file (in the default location).
CONNECT SYS/password AS SYSDBA
-- create the server parameter file
CREATE SPFILE='/vobs/oracle/dbs/spfilemynewdb.ora' FROM
PFILE='/vobs/oracle/admin/mynewdb/scripts/init.ora';
SHUTDOWN
-- this time you will start up using the server parameter file
CONNECT SYS/password AS SYSDBA
STARTUP
EXIT
10、 备份数据库
You should make 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 Oracle9i Backup and Recovery Concepts.
上述步骤基本上在windows和unix下都是一样的。那么参考一些其他的手册如oracle 9.2 admin guid a95491-01对于纯手工创建数据库中在windows下有这样的补充：
在创建数据库前创建启动一个oracle 服务
Before you create the database, first create a Windows service to run the database.
This service is the Oracle9i database process, oracle.exe, installed in the form of a Windows NT service.
Use ORADIM utility to create the service. After it has been created, the service starts
automatically. See "Using ORADIM Utility to Administer an Oracle Instance" on
page 1-27 for information on how to use ORADIM utility.
ORADIM这是创建服务的重要工具看看ORADIM的使用方法
To create and start an Oracle service:
1. Run ORADIM utility from the command prompt:
C:\> oradim -NEW -SID prod -INTPWD password -STARTMODE manual
-PFILE "C:\oracle\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.
2. Set ORACLE_SID to equal prod. Note that there are no spaces around the equal
sign (=) character:
C:\> set ORACLE_SID=prod
如何使用ORADIM请详细看本文档。
对于linux下的oracle安装以及unix下的安装请首先看oracle的相应手册，再推荐一个网址：
http://www.puschitz.com/InstallingOracle9i.shtml
这里是如何在redhat各版本下装9i的详细步骤。如何在linux下装oracle我将专门整理文档。
本章关键是在操作，原理很简单，基本上都了解就不多说了。
|