手工创建数据库(create a database manually)

* Choose a unique instance and database name
* Choose a database character set
* Set operating system variables
* Create the initialization parameter file
* Start the instance in NOMOUNT stage
* Create and execute the CREATE DATABASE
command
* Run scripts to generate the data dictionary and 
accomplish post-creation steps
* Create additional tablespaces as needed
1.配置新数据库所需要的目录
> ll  /u01/oradata/
>cp -r denver bbk
>rm -f *
>mkdir archive
即
[oracle@oracle9idemo ~]$ ll /u01/oradata
total 8
drwxr-xr-x  3 oracle oinstall 4096 Jun 23 19:45 bbk
drwxr-xr-x  3 oracle oinstall 4096 May  2 23:54 denver
>ll /u01/admin/
>cp -r denver bbk

[oracle@oracle9idemo ~]$ ll /u01/admin/bbk
total 20
drwxr-xr-x  2 oracle oinstall 4096 Jun 23 20:23 bdump
drwxr-xr-x  2 oracle oinstall 4096 Jun 21 17:15 cdump
drwxr-xr-x  2 oracle oinstall 4096 Jun 21 17:23 create
drwxr-xr-x  2 oracle oinstall 4096 Jun 21 17:17 pfile
drwxr-xr-x  2 oracle oinstall 4096 Jun 23 19:29 udump
删除5个文件夹所有东西
2.配置initbbk.ora
strings spfiledenver.ora initbbk.ora
*.aq_tm_processes=1
*.background_dump_dest='/u01/admin/denver/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/u01/oradata/denver/control01.ctl','/u01/oradata/denver/control02.ctl','/u01/oradata/denver/control03.ctl'
*.core_dump_dest='/u01/admin/denver/cdump'
*.db_block_size=8192
*.db_cache_size=16777216
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='denver'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=denverXDB)'
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=TRUE
*.instance_name='denver'
*.java_pool_size=83886080
*.job_queue_processes=10
*.large_pool_size=16777216
*.log_archive_dest_1='LOCATION=/u01/oradata/denver/archive'
*.log_archive_format='%t_%s.dbf'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=33554432
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=83886080
*.sort_area_size=1048576
*.star_transformation_enabled='TRUE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/admin/denver/udump'

:%s/denver/bbk/g
:%s/*\.//g

运行后为

aq_tm_processes=1
background_dump_dest='/u01/admin/bbk/bdump'
compatible='9.2.0.0.0'
control_files='/u01/oradata/bbk/control01.ctl','/u01/oradata/bbk/control02.ctl','/u01/oradata/bbk/control03.ctl'
core_dump_dest='/u01/admin/bbk/cdump'
db_block_size=8192
db_cache_size=16777216
db_domain=''
db_file_multiblock_read_count=32
db_name='bbk'
dispatchers='(PROTOCOL=TCP) (SERVICE=bbkXDB)'
fast_start_mttr_target=300
hash_area_size=1048576
hash_join_enabled=TRUE
instance_name='bbk'
java_pool_size=83886080
job_queue_processes=10
large_pool_size=16777216
log_archive_dest_1='LOCATION=/u01/oradata/bbk/archive'
log_archive_format='%t_%s.dbf'
log_archive_start=true
open_cursors=300
pga_aggregate_target=33554432
processes=150
query_rewrite_enabled='TRUE'
remote_login_passwordfile='EXCLUSIVE'
shared_pool_size=83886080
sort_area_size=1048576
star_transformation_enabled='TRUE'
timed_statistics=TRUE
undo_management='AUTO'
undo_retention=10800
undo_tablespace='UNDOTBS1'
user_dump_dest='/u01/admin/bbk/udump'
3.配置bbk.env
ORACLE_SID=bbk
ORACLE_BASE=/u01
ORACLE_HOME=$ORACLE_BASE/oracle
PATH=$ORACLE_HOME/bin:$PATH
ORACLE_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH ORACLE_NLS33  LD_LIBRARY_PATH
>ll
>chmod +x bbk.env 更改权限
运行 . ./bbk.env
>env | greo ORA 查看sid是否更改
即:
[oracle@oracle9idemo ~]$ env | grep ORA
ORACLE_NLS33=/u01/oracle/ocommon/nls/admin/data
ORACLE_SID=bbk
ORACLE_BASE=/u01
ORACLE_HOME=/u01/oracle

4.创建口令文件
orapw file=orapwbbk password=oracle entries=10
查看是否多了一个 orapwbbk
5.创建数据库文件.sql
>touch crdb01.sql
>vi crdb01.sql
添加:
spool dbcreate.log;
CREATE DATABASE "bbk"
     MAXDATAFILES 500
     MAXINSTANCES 8
     MAXLOGFILES  32
     CHARACTER SET "UTF8"
     NATIONAL CHARACTER SET AL16UTF16
     ARCHIVELOG
     DATAFILE
          '/u01/oradata/bbk/system01.dbf' size 300M
     EXTENT MANAGEMENT LOCAL
     DEFAULT TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradata/bbk/tempts01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL
     UNDO TABLESPACE "UNDOTBS1"
     DATAFILE '/u01/oradata/bbk/undotbs01.dbf' SIZE 200M 
     LOGFILE 
  GROUP 1 (
        '/u01/oradata/bbk/redo01a.rdo',
        '/u01/oradata/bbk/redo01b.rdo'
      )SIZE 100M,
  GROUP 2 (
        '/u01/oradata/bbk/redo02a.rdo',
        '/u01/oradata/bbk/redo02b.rdo'
      )SIZE 100M,
  GROUP 3 (
        '/u01/oradata/bbk/redo03a.rdo',
        '/u01/oradata/bbk/redo03b.rdo'
      )SIZE 100M
;
spool off;
>cat crdb01.sql
6.startup nomount
>sqlplus /nolog
>conn /as sysdba
>create spfile from pfile;
查看是否有 spfilebbk.ora
>startup nomount
>@ORACLE_HOME/dbs/crdb01.sql
创建错误信息可在 dbcreate.log 里查看
常见错误信息解决:
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-00200: controlfile could not be created
ORA-00202: controlfile: '/u01/oradata/bbk/control01.ctl'
ORA-27038: skgfrcre: file exists
进入
>ll /u01/oradata/bbk
>rm -f *
重启监听器:
>lsnrctl start
spfielbbk.ora没有创建!!重新创建!
create spfile from pfile;
再次运行
>r
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
口令文件没有创建!
orapw file=orapwbbk password=oracle entries=10
数据库在创建的时候时刻查看以下两个!!
oradata>ll bbk
控制文件
[oracle@oracle9idemo bbk]$ ll
total 1149216
drwxr-xr-x  2 oracle oinstall      4096 Jun 23 19:26 archive
-rw-r-----  1 oracle oinstall   7348224 Jun 23 19:39 control01.ctl
-rw-r-----  1 oracle oinstall   7348224 Jun 23 19:39 control02.ctl
-rw-r-----  1 oracle oinstall   7348224 Jun 23 19:39 control03.ctl
-rw-r-----  1 oracle oinstall 104858112 Jun 23 19:38 redo01a.rdo
-rw-r-----  1 oracle oinstall 104858112 Jun 23 19:38 redo01b.rdo
-rw-r-----  1 oracle oinstall 104858112 Jun 23 19:37 redo02a.rdo
-rw-r-----  1 oracle oinstall 104858112 Jun 23 19:37 redo02b.rdo
-rw-r-----  1 oracle oinstall 104858112 Jun 23 19:37 redo03a.rdo
-rw-r-----  1 oracle oinstall 104858112 Jun 23 19:37 redo03b.rdo
-rw-r-----  1 oracle oinstall 314580992 Jun 23 19:38 system01.dbf
-rw-r-----  1 oracle oinstall 104865792 Jun 23 19:38 tempts01.dbf
-rw-r-----  1 oracle oinstall 209723392 Jun 23 19:38 undotbs01.dbf

>cd /u01/admin/bbk/bdump
alter_bbk.log
>tail -f alter_bbk.log
查看出错信息
[oracle@oracle9idemo bdump]$ tail -f alert_bbk.log
Tue Jun 23 19:38:19 2009
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
Completed: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP
Tue Jun 23 19:38:23 2009
SMON: enabling tx recovery
Tue Jun 23 19:38:27 2009
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: CREATE DATABASE "bbk"
     MAXDATAFILES 500
     M
注意:手工创建数据库重在细心,只要按照这个步骤来,基本不会出什么问题!!
7.创建表空间
create tablespace mytbs datafile '/u01/oradata/bbk/mytbs01.dbf' size 100M exten management local;
8.运行脚本创建数据字典
>spool log1.log
>@?/rdbms/admin/catalog.sql
>spool log2.log
>@?/rdbms/admin/catproc.sql
>spool log3.log
>@?/sqlplus/admin/pupbld.sql
9.重新登录数据库
>sqlplus /nolog
>conn /as sysdba
>startup
SQL> select * from dual;
D
-
X
SQL> create table testbl(id integer,name char(10));
Table created.
SQL> insert into testbl values(1,'bbk');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from testbl;
        ID NAME
---------- ----------
         1 bbk