oracle 手动建库 --test

最近在参加ocp 的实训，很有收获，下面练习 oracle 手动建库： 

 

关于手动建库：
[oracle@aoracle ezhoudg]$ cd $ORACLE_HOME
cd dbs

Step1:

Create some folders:
mkdir -p /u02/ezhoudg/disk1
mkdir -p /u02/ezhoudg/disk2
mkdir -p /u02/ezhoudg/disk3
mkdir -p /u02/ezhoudg/udump
mkdir -p /u02/ezhoudg/cdump
mkdir -p /u02/ezhoudg/bdump

Step2；
create .ora file:
[oracle@aoracle dbs]$ cat init.ora | grep -v ^# > ezhoudg.ora
vi ezhoudg.ora

add following line into the end:

control_files = ('/u02/disk1/control01.ctl','/u02/disk2/contol02.ctl','/u02/disk3/control03.ctl')
sga_target=300m
background_dump_dest=/u02/ezhoudg/bdump
core_dump_dest=/u02/ezhoudg/cdump
user_dump_dest=/u02/ezhoudg/udump
undo_management=auto
undo_tablespace=ezhoudgtbs

Step3:

export ORACLE_SID=ezhoudg

create the bdump file:

[oracle@aoracle dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 21 10:29:26 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> !stty erase ^h

SQL>

SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/pp/oracle/product/10.2/db_1/dbs/initezhoudg.ora'
SQL>

好，下面查错：

oracle@aoracle dbs]$ mv ezhoudg.ora initezhoudg.ora

SQL> startup nomount;
ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information
SQL>

发现 sga 的设置超过原来的设置：

修改：
#db_block_buffers = 100                                                 # SMALL

#shared_pool_size = 3500000                                            # SMALL

发现可以了：

[oracle@aoracle bdump]$ ls -l
total 4
-rw-r--r-- 1 oracle oinstall 2095 Sep 21 10:52 alert_ezhoudg.log
[oracle@aoracle bdump]$ pwd
/u02/ezhoudg/bdump

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1219160 bytes
Variable Size              96470440 bytes
Database Buffers          213909504 bytes
Redo Buffers                2973696 bytes

好，我们来看一下log:
cd /u02/ezhoudg/bdump

[oracle@aoracle bdump]$ more alert_ezhoudg.log
Wed Sep 21 10:52:15 2011
Starting ORACLE instance (normal)
Cannot determine all dependent dynamic libraries for /proc/self/exe
Unable to find dynamic library libocr10.so in search paths
RPATH = /ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/ade/aime1_build2101/oracle/
has/lib/:
LD_LIBRARY_PATH is not set!
The default library directories are /lib and /usr/lib
Unable to find dynamic library libocrb10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /u01/pp/oracle/product/10.2/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
  processes                = 50
  sga_target               = 314572800
  control_files            = /u02/disk1/control01.ctl, /u02/disk2/contol02.ctl, /u02/disk3/control03.ctl
  log_buffer               = 2927616
  log_checkpoint_interval  = 10000
  db_files                 = 80
  db_file_multiblock_read_count= 8
  undo_management          = AUTO
  undo_tablespace          = ezhoudgtbs
  global_names             = TRUE
  parallel_max_servers     = 5
  background_dump_dest     = /u02/ezhoudg/bdump
  user_dump_dest           = /u02/ezhoudg/udump
  max_dump_file_size       = 10240
  core_dump_dest           = /u02/ezhoudg/cdump
  db_name                  = DEFAULT
PSP0 started with pid=3, OS id=6467
MMAN started with pid=4, OS id=6469
PMON started with pid=2, OS id=6465
CKPT started with pid=7, OS id=6475
LGWR started with pid=6, OS id=6473
DBW0 started with pid=5, OS id=6471
RECO started with pid=9, OS id=6479
SMON started with pid=8, OS id=6477
MMNL started with pid=11, OS id=6483
MMON started with pid=10, OS id=6481
[oracle@aoracle bdump]$

step4: create passwd file:
[oracle@aoracle dbs]$ orapwd file=orapwezhoudg password=oracle

step5:

Edit the sql script to create the database:
vi db.sql
create database ezhoudg
user sys identified by oracle
user system identified by oracle
maxinstances 2
maxdatafile 2048
maxlogmembers 5
maxloghistory 20
datafile '/u02/ezhoudg/system01.dbf' size 300m autoextend on next 30m maxsize unlimited
sysaux datafile '/u02/ezhoudg/sysaux01.dbf' size 300m autoextend on next 30m maxsize unlimited
dafault temporary tablespace temp tempfile '/u02/ezhoudg/tmp01.dbf' size 200m
undo tablespace ezhoutdgtbs datafile '/u02/ezhoudg/ezhoudgtbs01.dbf' size 100m autoextend on next 10m
default tablespace ezhoudg datafile '/u02/ezhoudg/ezhoudg01.dbf' size 100m
logfile
group 1 ('/u02/ezhoudg/disk1/redo1_1.rdo','/u02/ezhoudg/disk1/redo1_2.rdo','/u02/ezhoudg/disk1/redo1_3.rdo') size 20m,
group 2 ('/u02/ezhoudg/disk1/redo2_1.rdo','/u02/ezhoudg/disk1/redo2_2.rdo','/u02/ezhoudg/disk1/redo2_3.rdo') size 20m,
group 3 ('/u02/ezhoudg/disk1/redo3_1.rdo','/u02/ezhoudg/disk1/redo3_2.rdo','/u02/ezhoudg/disk1/redo3_3.rdo') size 20m;

建库，还是 报错：
SQL> @db   
create database ezhoudg
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01504: database name 'EZHOUDG' does not match parameter db_name 'DEFAULT'

注意，建库的过程中， tail -30 alert_ezhoudg.log 都是可以看到log 的详细信息的。

解决方法：
 vi initezhoudg.ora
 update : db_name=ezhoudg
 
好，下面再建：

发现，还是报刚才的错：
SQL> @db
create database ezhoudg
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01504: database name 'EZHOUDG' does not match parameter db_name 'DEFAULT'

郁闷中，
原因可能是 数据库启动的时候 调用了original 的initezhoudg.ora,
下面再启动一下：
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  314572800 bytes
Fixed Size                  1219160 bytes
Variable Size              96470440 bytes
Database Buffers          213909504 bytes
Redo Buffers                2973696 bytes

建库：

SQL> @db
create database ezhoudg
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

查错：
看一下spfile:

[oracle@aoracle dbs]$ more spfileezhou.ora

ezhou.__db_cache_size=222298112
ezhou.__java_pool_size=4194304
ezhou.__large_pool_size=4194304
ezhou.__shared_pool_size=100663296
ezhou.__streams_pool_size=0
*.audit_file_dest='/u01/pp/oracle/admin/ezhou/adump'
*.background_dump_dest='/u01/pp/oracle/admin/ezhou/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u02/ezhou/control01.ctl','/u02/ezhou/control02.ctl','/u02/ezhou/control03.ctl'
*.core_dump_dest='/u01/pp/oracle/admin/ezhou/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ezhou'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ezhouXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=335544320
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/pp/oracle/admin/ezhou/udump'

可能是pfile 的问题，下面从新建一个：
SQL> startup nomount
SQL> create spfile from pfile;

看一下spfile:

[oracle@aoracle dbs]$ ls -lrt
total 68
-rw-r----- 1 oracle oinstall  8385 Sep 11  1998 init.ora
-rw-r----- 1 oracle oinstall 12920 May  3  2001 initdw.ora
-rw-rw---- 1 oracle oinstall  1544 Aug 27 21:22 hc_ezhou.dat
-rw-rw---- 1 oracle oinstall    24 Aug 27 21:24 lkEZHOU
-rw-r----- 1 oracle oinstall  1536 Aug 27 21:29 orapwezhou
-rw-r----- 1 oracle oinstall  2560 Aug 27 21:29 spfileezhou.ora
-rw-rw---- 1 oracle oinstall  1544 Sep 21 10:52 hc_ezhoudg.dat
-rw-r----- 1 oracle oinstall  1536 Sep 21 12:51 orapwezhoudg
-rw-rw---- 1 oracle oinstall    24 Sep 21 13:19 lkEZHOUDG
-rw-r--r-- 1 oracle oinstall  1002 Sep 21 13:20 initezhoudg.ora
-rw-r--r-- 1 oracle oinstall   957 Sep 21 13:34 db.sql
-rw-r----- 1 oracle oinstall  2560 Sep 21 13:47 spfileezhoudg.ora
[oracle@aoracle dbs]$ more spfileezhoudg.ora

*.background_dump_dest='/u02/ezhoudg/bdump'
*.control_files='/u02/ezhoudg/disk1/control01.ctl','/u02/ezhoudg/disk2/contol02.ctl','/u02/ezhoudg/disk3/c
ontrol03.ctl'
*.core_dump_dest='/u02/ezhoudg/cdump'
*.db_file_multiblock_read_count=8# SMALL 
*.db_files=80# SMALL 
*.db_name='ezhoudg'
*.global_names=TRUE
*.log_buffer=32768# SMALL
*.log_checkpoint_interval=10000
*.max_dump_file_size='10240'# limit trace file size to 5 Meg each
*.parallel_max_servers=5# SMALL
*.processes=50# SMALL 
*.sga_target=300m
*.undo_management='auto'
*.undo_tablespace='ezhoudgtbs'
*.user_dump_dest='/u02/ezhoudg/udump'

SQL> @db

Database created.

好下面run 两个shell：
sql>@?/rdbms/admin/catalog
sql>@?/rdbms/admin/catproc

好，上面手动建库完成。