手工创建数据库
参考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;