A.List the prerequistites necessarey for database creation
1)确定数据库类型(OLTP or OLAP)
2)规划数据库架构, 如datafile, logfile 等存放磁盘, tablespace, 性能安全 (可以参考Oracle 建议的OFA:Optimal Felxible Arch)
Local login (Login Linux first through Telnet, and then you can login oracle by the commond "conn / as sysdba " without password)
password login (the password exist in passwd file, use " conn sys/oracle as sysdba" to login oracle, the password is required by oracle , 口 令文件是放在oracle 的dbs 目录下,只有属于dba这一组的用户可以采用os 方式认证登录。)
Password File Create:
a. create passwd file: "orapwd file=orapwtest password=boobooke entries=10"
b. REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE add init arameter file
c. add user to the password file
d. grent .. to HR
B.create db by DBCA
profile 中设定好环境变量后,用户登录就自动动了。
-------------------------------------
export ORACLE_BASE = ..
export ORACLE_HOME = ..
export ORACLE_SID=chris
export ORACLE_SID=chris/ocommon/nls/admin/data
export PATH = $ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
dbca (if we need to invoke the dbca in comond mode ,we need to install Xmanager tool)
.... (其中有一步要求我们选择Archive mode)
#我们可以把刚才用dbca模式创建的方式存成模板,下次在创建新的db的时候指定这个模板就好了。
#How to delete db that created by dbca manually
------------------------------------
rm -fR admin/chris/
rm -fR oradata/chris/
cd dbs
rm -f orapwchris lkCHRIS spfilechris.ora
------------------------------------
C.Create db by manually
1. choose a unique instance and database name
ORACLE_SID
2. choose a database character set
3. set operationg system environment variables
file: bbk.env
-----------------------------------
export ORACLE_BASE = ..
export ORACLE_HOME = ..
export ORACLE_SID=chris/ocommon/nls/admin/data
export OLACLE_NLS33=$ORACLE_HOME
export PATH = $ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
-----------------------------------
execute the file with commond: ". ./bbk.env"
4. create the init parameter file (pfile)
creat pfile : initbbk.ora
-----------------------------------
nb_name=bbk
....
#这里面如果有指定目录地址,我们得先前手动创建.
-----------------------------------
5.create pwd fileorapw(sid):
[oracle@test oracle]$ orapwd file=/opt/oracle/product/9.2.0/dbs/orapwmydb password=bbk entries=5
这样就创建了一个针对mydb实例的密码文件.默认放到/opt/oracle/product/9.2.0/dbs目录下.此密码是sys用户的,并且在建库脚本了就不能指定sys的密码,会有冲突。
6. connect a db manually
----------------------------------
sqlplus /nolog
conn / as sysdba
----------------------------------
7. create the spfile (oracle 会把control file 的位置回写到spfile中)
----------------------------------
create spfile from pfile;
----------------------------------
8. start the instance in NOMOUNT stage
----------------------------------
STARTUP NOMOUNT;
----------------------------------
6. create and execute the CREATE DBTABASE command
----------------------------------
#method 1:
create database bbk;
#method 2:
@crstudydb.sql
----------------------------------
Detail of crstudydb.sql :
------------------
CREATE DATABASE study
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/study/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/study/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/study/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/study/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/study/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/study/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
------------------
7. run scripts to generate the data dictionary and accomplish post-creation steps(系统存储过程)
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
#?代表$ORACLE_HOME
8. create additional tablespaces as needed
a.
D.Create db by OMF(Oracle Manager Files)
这种方式不推荐使用。这里就不再详述!
1)确定数据库类型(OLTP or OLAP)
2)规划数据库架构, 如datafile, logfile 等存放磁盘, tablespace, 性能安全 (可以参考Oracle 建议的OFA:Optimal Felxible Arch)
用DBCA创建后的目录结构如下:3)认证方式a
oradata (数据文件)
admin(pfile 和 各种Log)
oracle(纯oracle 软件)
Local login (Login Linux first through Telnet, and then you can login oracle by the commond "conn / as sysdba " without password)
password login (the password exist in passwd file, use " conn sys/oracle as sysdba" to login oracle, the password is required by oracle , 口 令文件是放在oracle 的dbs 目录下,只有属于dba这一组的用户可以采用os 方式认证登录。)
Password File Create:
a. create passwd file: "orapwd file=orapwtest password=boobooke entries=10"
b. REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE add init arameter file
c. add user to the password file
d. grent .. to HR
B.create db by DBCA
profile 中设定好环境变量后,用户登录就自动动了。
-------------------------------------
export ORACLE_BASE = ..
export ORACLE_HOME = ..
export ORACLE_SID=chris
export ORACLE_SID=chris/ocommon/nls/admin/data
export PATH = $ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
dbca (if we need to invoke the dbca in comond mode ,we need to install Xmanager tool)
.... (其中有一步要求我们选择Archive mode)
#我们可以把刚才用dbca模式创建的方式存成模板,下次在创建新的db的时候指定这个模板就好了。
#How to delete db that created by dbca manually
------------------------------------
rm -fR admin/chris/
rm -fR oradata/chris/
cd dbs
rm -f orapwchris lkCHRIS spfilechris.ora
------------------------------------
C.Create db by manually
1. choose a unique instance and database name
ORACLE_SID
2. choose a database character set
3. set operationg system environment variables
file: bbk.env
-----------------------------------
export ORACLE_BASE = ..
export ORACLE_HOME = ..
export ORACLE_SID=chris/ocommon/nls/admin/data
export OLACLE_NLS33=$ORACLE_HOME
export PATH = $ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
-----------------------------------
execute the file with commond: ". ./bbk.env"
4. create the init parameter file (pfile)
creat pfile : initbbk.ora
-----------------------------------
nb_name=bbk
....
#这里面如果有指定目录地址,我们得先前手动创建.
-----------------------------------
5.create pwd fileorapw(sid):
[oracle@test oracle]$ orapwd file=/opt/oracle/product/9.2.0/dbs/orapwmydb password=bbk entries=5
这样就创建了一个针对mydb实例的密码文件.默认放到/opt/oracle/product/9.2.0/dbs目录下.此密码是sys用户的,并且在建库脚本了就不能指定sys的密码,会有冲突。
6. connect a db manually
----------------------------------
sqlplus /nolog
conn / as sysdba
----------------------------------
7. create the spfile (oracle 会把control file 的位置回写到spfile中)
----------------------------------
create spfile from pfile;
----------------------------------
8. start the instance in NOMOUNT stage
----------------------------------
STARTUP NOMOUNT;
----------------------------------
6. create and execute the CREATE DBTABASE command
----------------------------------
#method 1:
create database bbk;
#method 2:
@crstudydb.sql
----------------------------------
Detail of crstudydb.sql :
------------------
CREATE DATABASE study
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/study/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/study/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/study/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/study/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/study/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/study/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
------------------
7. run scripts to generate the data dictionary and accomplish post-creation steps(系统存储过程)
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
#?代表$ORACLE_HOME
8. create additional tablespaces as needed
a.
D.Create db by OMF(Oracle Manager Files)
这种方式不推荐使用。这里就不再详述!