oracle 9i 用脚本创建数据全过程

最新推荐文章于 2025-12-09 11:04:34 发布

原创最新推荐文章于 2025-12-09 11:04:34 发布 · 1.8k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #脚本 #database #archive #statistics #sql

本文详细介绍了一个从零开始创建Oracle数据库的全过程，包括用户创建、环境变量配置、文件及目录设置、数据库实例创建以及系统对象的建立等关键步骤。

注：以下步骤是一个实例，参考者注意修改参数值，比如用户名，SID,端口等(注意红色部分)！

1．创建帐务数据库用户:ora_bill

useradd -d /LCdisk/oradata/bill -g dba ora_bill

root:/LCdisk/oradata/bill>passwd ora_bill

su – ora_bill

2．创建环境变量ORACLE_SID=testbill;

3.创建文件

oracle:/home/oracle/product/9.2.0/dbs>more inittestacct.ora>inittestbill.ora

root:/home/oracle/product/9.2.0/dbs>chown ora_bill inittestbill.ora

添加该文件内容

#cache and I/O

DB_BLOCK_SIZE=8192

DB_CACHE_SIZE=20971520

# Cursors and Library Cache

#CURSOR_SHARING=SIMILAR

OPEN_CURSORS=300

# Diagnostics and Statistics

BACKGROUND_DUMP_DEST='/LCdisk/oradata/bill/admin/bdump'

CORE_DUMP_DEST=/LCdisk/oradata/bill/admin/cdump

#TIMED_STATISTICS=TRUE

USER_DUMP_DEST='/LCdisk/oradata/bill/admin/udump'

# Control File Configuration

control_files = ("/LCdisk/oradata/bill/ctlfile1", "/LCdisk/oradata/bill/ctlfile2")

# Archive

LOG_ARCHIVE_DEST_1='LOCATION=/LCdisk/oradata/bill/archive'

LOG_ARCHIVE_FORMAT=%t_%s.dbf

LOG_ARCHIVE_START=FALSE

# Shared Server

# Uncomment and use first DISPATCHES parameter below when your listener is

# configured for SSL

# (listener.ora and sqlnet.ora)

# DISPATCHERS = "(PROTOCOL=TCPS)(SER=MODOSE)",

dispatchers='(PROTOCOL=TCP) (SERVICE=testbillXDB)'

# Miscellaneous

COMPATIBLE=9.2.0

DB_NAME=testbill

INSTANCE_NAME=testbill

Manually Creating an Oracle Database

# Pools

JAVA_POOL_SIZE=31457280

LARGE_POOL_SIZE=1048576

SHARED_POOL_SIZE=52428800

# Processes and Sessions

PROCESSES=150

# Redo Log and Recovery

#FAST_START_MTTR_TARGET=300

# Resource Manager

#RESOURCE_MANAGER_PLAN=SYSTEM_PLAN

# Sort, Hash Joins, Bitmap Indexes

SORT_AREA_SIZE=524288

# Automatic Undo Management

UNDO_MANAGEMENT=AUTO

UNDO_TABLESPACE=undotbs

4.修改文件

ora_bill:/home/oracle/product/9.2.0/network/admin>vi listener.ora

添加内容

LISTENER3=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.8.91)(PORT = 1525))

)

SID_LIST_LISTENER3 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = testbill)

(ORACLE_HOME = /home/oracle/product/9.2.0)

(SID_NAME = testbill)

)

5.修改文件

ora_bill:/home/oracle/product/9.2.0/network/admin>vi tnsnames.ora

添加内容

TESTBILL =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.8.91)(PORT = 1525))

)

(CONNECT_DATA =

(SERVICE_NAME = testbill)

)

6.创建目录

ora_bill:/LCdisk/oradata/bill/admin/udump>

ora_bill:/LCdisk/oradata/bill/admin/bdump>

ora_bill:/LCdisk/oradata/bill/admin/cdump>

root:/LCdisk/oradata/bill/admin>chown oracle *

root:/LCdisk/oradata/bill>chmod -R 777 admin

root:/LCdisk/oradata/bill/admin>su - ora_bill

7.创建数据库实例

ora_bill:/LCdisk>sqlplus /nolog

SQL> conn / as sysdba

SQL> startup nomount

SQL> CREATE DATABASE TESTBILL

2 USER SYS IDENTIFIED BY hncs

3 USER SYSTEM IDENTIFIED BY hncs

4 LOGFILE GROUP 1 ('/LCdisk/oradata/bill/redo01.log') SIZE 256M,

5 GROUP 2 ('/LCdisk/oradata/bill/redo02.log') SIZE 256M,

6 GROUP 3 ('/LCdisk/oradata/bill/redo03.log') SIZE 256M

7 MAXLOGFILES 5

8 CHARACTER SET ZHS16GBK

9 NATIONAL CHARACTER SET AL16UTF16

10 DATAFILE '/LCdisk/oradata/bill/system01.dbf' SIZE 1024M REUSE

11 DEFAULT TEMPORARY TABLESPACE tempts1

12 TEMPFILE '/LCdisk/oradata/bill/temp01.dbf'

13 SIZE 4096M REUSE

14 UNDO TABLESPACE undotbs

15 DATAFILE '/LCdisk/oradata/bill/undotbs01.dbf'

16 SIZE 4096M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

Database created.

SQL> create user testbill identified by "1234";

2 /

User created.

SQL> grant dba to testbill;

Grant succeeded.

SQL> grant connect to testbill;

Grant succeeded.

8. 运行脚本（创建系统对象）

----------------------------------------------------------------------------------

@/home/oracle/product/9.2.0/rdbms/admin/catalog.sql;

@/home/oracle/product/9.2.0/rdbms/admin/catexp7.sql;

@/home/oracle/product/9.2.0/rdbms/admin/catblock.sql;

@/home/oracle/product/9.2.0/rdbms/admin/catproc.sql;

@/home/oracle/product/9.2.0/rdbms/admin/catoctk.sql;

@/home/oracle/product/9.2.0/rdbms/admin/owminst.plb;

connect SYSTEM/manager

@/home/oracle/product/9.2.0/sqlplus/admin/pupbld.sql;

-----------------------------------------------------------------------

connect SYSTEM/manager

@/home/oracle/product/9.2.0/sqlplus/admin/help/hlpbld.sql; (param:helpus)--//.sql)

9.重启数据库

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 131563864 bytes

Fixed Size 741720 bytes

Variable Size 109051904 bytes

Database Buffers 20971520 bytes

Redo Buffers 798720 bytes

Database mounted.

Database opened.