Linux环境手动创建oracle10g数据库实践

环境OS

[root@T_life_db etc]# uname -a

Linux T_life_db 2.6.18-238.el5 #1 SMP Sun Dec 19 14:22:44 EST 2010 x86_64 x86_64 x86_64 GNU/Linux

建库过程

1创建新用户组

创建用户组oracle和dba

查看要创建的用户组是否存在

[root@T_life_db home]# less /etc/group

确定不存在，创建之

[root@T_life_db home]# groupadd oracle

[root@T_life_db home]# groupadd dba

2创建新用户

创建用户oracle

查看要创建的用户是否存在

[root@T_life_db oracle]# grep bash /etc/passwd

确定不存在，创建、分配组并初始化密码

[root@T_life_db home]# useradd oracle -g oracle -G dba

[root@T_life_db home]# passwd oracle

修改oracle软件卷和数据卷owner

[root@T_life_db home]#chown oracle:dba /oracle

[root@T_life_db home]#chown oracle:dba /oradata

3安装oracle10g软件

从另一同平台主机拷贝oracle软件tar包到软件卷。

本次通过SSH的ftp工具拷贝。

解tar包完成软件安装

[oracle@T_life_db ~]$tar xvf product.tar

4手动创建DB

4.1创建环境变量文件

创建环境变量文件prof_lifetest并加载，文件内容如下

PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin

export PATH

#Oracle DB 10g Environment

export ORACLE_SID=lifetest

export ORACLE_BASE=/oracle

export ORACLE_HOME=$ORACLE_BASE/product/db10gr2

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export ORA_NLS10=$ORACLE_HOME/nls/data

export LIBPATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib32:$ORACLE_HOME/ctx/lib

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/usr/lib:/lib

export CLASSPATH=$ORACLE_HOME/product/jlib:$ORACLE_HOME/jlib:$ORACLE_HOME/jre

export PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_DOC=$ORACLE_HOME

export TMP=/tmp

export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'

export EDITOR=vi

export AIXTHREAD_SCOPE=S

set -o vi

umask 022

4.2创建dump目录

[oracle@T_life_db oracle]$ mkdir admin

[oracle@T_life_db oracle]$ cd admin

[oracle@T_life_db admin]$ mkdir lifetest

[oracle@T_life_db admin]$ cd lifetest

[oracle@T_life_db lifetest]$ mkdir -p adump bdump cdump udump

4.3创建数据文件目录

[oracle@T_life_db oradata]$ mkdir lifetest

4.4创建pfile

创建文件$ORACLE_HOME/dbs/initlifetest.ora内容如下：

*._gby_hash_aggregation_enabled=false

*.aq_tm_processes=0

*.background_dump_dest='/oracle/admin/lifetest/bdump'

*.compatible='10.2.0.1.0'

*.control_files=/oradata/lifetest/control01.ctl,/oradata/lifetest/control02.ctl, /oradata/lifetest/control03.ctl

*.core_dump_dest='/oracle/admin/lifetest/cdump'

*.cursor_sharing='EXACT'

*.db_block_size=8192

*.db_domain='CCIC'

*.db_file_multiblock_read_count=16

*.db_files=1000

*.db_name='lifetest'

*.fast_start_mttr_target=300

*.global_names=TRUE

*.instance_name='lifetest'

*.job_queue_processes=10

*.log_buffer=1048576

*.NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

*.open_cursors=800

*.OS_AUTHENT_PREFIX=''

*.pga_aggregate_target=200000000

*.processes=300

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.remote_os_authent=FALSE

*.sessions=150

*.session_cached_cursors=100

*.session_max_open_files=20

*.sga_max_size=800000000

*.shared_pool_size=107497472

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/oracle/admin/lifetest/udump'

4.5创建密码文件

orapwd file=orapwlifetest password=oracle entries=5 force=y

4.6启动数据库

SQL>startup nomount

本次发生一些错误，详述如下。

错误1

现象

ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []

原因

查询metalink得到问题原因，是由于获得主机信息时有错误。详见下面链接

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(from=BOOKMARK&bmDocID=336447.1&bmDocDsrc=KB&bmDocType=PROBLEM&bmDocTitle=Startup%20Database%20Produces%20Ora-00600:%20%5BKeltnfy-Ldminit%5D&viewingMode=1143))

处理

修改hosts文件.

$ more /etc/hosts

127.0.0.1lifedbtest localhost.localdomain localhost

::1localhost6.localdomain6 localhost6

增加一行。

10.0.13.198T_life_db

确认通过hostname可以ping通。

[oracle@T_life_db dbs]$ ping T_life_db

PING T_life_db (10.0.13.198) 56(84) bytes of data.

64 bytes from T_life_db (10.0.13.198): icmp_seq=1 ttl=64 time=0.019 ms

64 bytes from T_life_db (10.0.13.198): icmp_seq=2 ttl=64 time=0.013 ms

错误2

现象

SQL> startup nomount

ORA-00371: not enough shared pool memory, should be atleast 107497472 bytes

原因

shared pool memory不足

处理

在pfile中添加一行

*.shared_pool_size=107497472

4.7创建建库脚本

建库脚本create_lifetest.sql，内容如下：

create database lifetest

MAXINSTANCES 1

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 5

MAXDATAFILES 1000

DATAFILE

'/oradata/lifetest/system01.dbf' size 200m reuse autoextend on next 1m maxsize unlimited extent management local

sysaux datafile

'/oradata/lifetest/sysaux01.dbf' size 200m reuse autoextend on next 1m maxsize unlimited

default temporary tablespace TEMP tempfile

'/oradata/lifetest/temp01.dbf' size 200m reuse autoextend on next 1m maxsize unlimited

undo tablespace UNDOTBS1 datafile

'/oradata/lifetest/undo01.dbf' size 200m reuse autoextend on next 5M maxsize unlimited

logfile

GROUP 1 ('/oradata/lifetest/redo1.dbf') size 256m,

GROUP 2 ('/oradata/lifetest/redo2.dbf') size 256m,

GROUP 3 ('/oradata/lifetest/redo3.dbf') size 256m

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16

;

4.8执行建库脚本

SQL> @create_lifetest.sql

Database created.

耗时大约几分钟。时间随创建文件的大小变化。注意建库脚本要在nomount状态下运行。本次运行出现一些错误，详述如下。

错误1

现象

alert log显示

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE

'/oradata/lifetest/undo01.dbf' size 200m reuse autoextend on next 5M maxsize unlimited

ORA-30012 signalled during: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE

'/oradata/lifetest/undo01.dbf' size 200m reuse autoextend on next 5M maxsize unlimited

...

Tue Mar8 11:57:54 2011

Errors in file /oracle/admin/lifetest/udump/lifetest_ora_16414.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-30012: undo tablespace 'UNDODBS1' does not exist or of wrong type

Tue Mar8 11:57:54 2011

Errors in file /oracle/admin/lifetest/udump/lifetest_ora_16414.trc:

ORA-01501: CREATE DATABASE failed

ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 5792

ORA-00604: error occurred at recursive SQL level 1

ORA-30012: undo tablespace 'UNDODBS1' does not exist or of wrong type

Error 1519 happened during db open, shutting down database

USER: terminating instance due to error 1519

Instance terminated by USER, pid = 16414

ORA-1092 signalled during: create database lifetest

lifetest_ora_16414.trc显示

*** 2011-03-08 11:57:48.166

*** SERVICE NAME:() 2011-03-08 11:57:48.166

*** SESSION ID:(323.3) 2011-03-08 11:57:48.166

kccsga_update_ckpt: num_1 = 1, num_2 = 0, num_3 = 0, lbn_2 = 0, lbn_3 = 0

Control file created with size 864 blocks

ORA-00604: error occurred at recursive SQL level 1

ORA-30012: undo tablespace 'UNDODBS1' does not exist or of wrong type

Offending statement at line 5792

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE

'/oradata/lifetest/undo01.dbf' size 200m reuse autoextend on next 5M maxsize unlimited

ORA-01501: CREATE DATABASE failed

ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 5792

ORA-00604: error occurred at recursive SQL level 1

ORA-30012: undo tablespace 'UNDODBS1' does not exist or of wrong type

原因

建库脚本和pfile中对undo表空间的描述不一致。

pfile中

*.undo_tablespace='UNDODBS1'

建库脚本中

undo tablespace UNDOTBS1 datafile

处理

修改pfile同建库脚本一致。关闭数据库并重启到nomount。

错误2

现象

再次执行建库脚本

SQL> @create_lifetest.sql

create database lifetest

*

ERROR at line 1:

ORA-01501: CREATE DATABASE failed

ORA-00200: control file could not be created

ORA-00202: control file: '/oradata/lifetest/control01.ctl'

ORA-27038: created file already exists

Additional information: 1

SQL> @create_lifetest.sql

create database lifetest

*

ERROR at line 1:

ORA-01501: CREATE DATABASE failed

ORA-00301: error in adding log file '/oradata/lifetest/redo1.dbf' - file cannot

be created

ORA-27038: created file already exists

Additional information: 1

原因

上次建库失败时的遗留的controlfile，redo log没有清除

处理

[oracle@T_life_db lifetest]$ rm *.ctl

[oracle@T_life_db lifetest]$ rm redo*

4.9创建数据字典

数据库创建完成后，v$动态视图可以使用，DBA_XXX等视图还不可用。

再运行脚本创建ORACLE的数据字典。建议spool输出脚本运行结果，便于排查错误。

SQL>@?/rdbms/admin/catalog.sql

SQL>@?/rdbms/admin/catproc.sql

SQL>@?/rdbms/admin/catexp.sql

这3个脚本的执行时间均需要几分钟。

到此手动创建DB过程完成。

5配置监听和tns

5.1listener.ora中增加配置

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = lifetest)

(ORACLE_HOME = /oracle/product/db10gr2)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = T_life_db)(PORT = 1521))

)

)

启动监听

[oracle@T_life_db admin]$ lsnrctl start

5.2tnsnames.ora中增加配置

lifetest =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.13.198)(PORT = 1521))

)

(CONNECT_DATA =

(SID = lifetest)

)

)

远程登录验证无误。

 

出处: http://aspen1982.itpub.net/post/43125/521761