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=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.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 /nolog
  SQL> CONNECT SYS AS SYSDBA
  

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

  $ sqlplus /nolog
  SQL> 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;