OracleDBA之路之Create Database(3)

OracleDBA之路之Create Database(三)

手工创建数据库

参考oracle11g联机文档

Administrator's Guide
2 Creating and Configuring an Oracle Database
Creating a Database with the CREATE DATABASE Statement

?

Step 1: Specify an Instance Identifier (SID)

确定使用的SID:

ORACLE_SID=hzh

Step 2: Ensure That the Required Environment Variables Are Set设置环境变量

ORACLE_BASE=/u01
ORACLE_HOME=$ORACLE_BASE/oracle
ORACLE_SID=hzh
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH

?

在环境变量中ORACLE_HOME 和ORACLE_SID 是必须有的

可以把以上内容写到一个脚本中放到 /u01/oracle/dbs/下 hzh.env

使该环境变量生效

cd /u01/oracle/dbs

. ./hzh.env

Step 3: Choose a Database Administrator Authentication Method验证方法

1：With a password file

ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}]

Example

orapwd FILE=orapworcl ENTRIES=30

2：With operating system authentication

If you decide to authenticate with a password file, create the password file .If you decide to authenticate with operating system authentication, ensure that you log in to the host computer with a user account that is a member of the appropriate operating system user group. On the UNIX and Linux platforms, for example, this is typically the dba user group. On the Windows platform, the user installing the Oracle software is automatically placed in the required user group.?

Step 4: Create the Initialization Parameter File创建pfile

初始化参数文件中 DB_NAME是必须的，其他都可以使用缺省值

Sample Initialization Parameter File

db_name='ORCL'memory_target=1Gprocesses = 150audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'audit_trail ='db'db_block_size=8192db_domain=''db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'db_recovery_file_dest_size=2Gdiagnostic_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# devicescontrol_files = (ora_control1, ora_control2)compatible ='11.2.0'

Step 5: (Windows Only) Create an Instance

略，我们此处实验环境为Linux

Step 6: Connect to the Instance

To authenticate with a password file, enter the following commands, and then enter the SYS password when prompted:

$ sqlplus /nologSQL> CONNECT SYS AS SYSDBA

To authenticate with operating system authentication, enter the following commands:

$ sqlplus /nologSQL> CONNECT / AS SYSDBA

SQL*Plus outputs the following message:

Connected to an idle instance.

Step 7: Create a Server Parameter File

CREATE SPFILE FROM PFILE;

If you are using Oracle-managed files and your initialization parameter file does not contain the CONTROL_FILES parameter, you must create a server parameter file now so the database can save the names and location of the control files that it creates during the CREATE DATABASE statement.

Step 8: Start the Instance

STARTUP NOMOUNT

Step 9: Issue the CREATE DATABASE Statement

CREATE DATABASE mynewdb   USER SYS IDENTIFIED BY sys_password   USER SYSTEM IDENTIFIED BY system_password   LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log') SIZE 100M BLOCKSIZE 512,           GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log') SIZE 100M BLOCKSIZE 512,           GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log') SIZE 100M BLOCKSIZE 512   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

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;