Oracle 9i 手动建库

Oracle 9i 手动建库

环境:RedHat Enterprise Linux 4.8, Oracle 9.2.0.8

1.设置环境变量

# Oracle 9i
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/9.2.0
ORACLE_TERM=xterm
ORACLE_OWNER=oracle
ORACLE_SID=vrh9i

LD_LIBRARY_PATH=$ORACLE_HOME/lib
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib

LD_ASSUME_KERNEL=2.4.1
TMP=/tmp
TMPDIR=$TMP

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:/sbin

export PATH ORACLE_BASE ORACLE_HOME ORACLE_TERM ORACLE_OWNER ORACLE_SID LD_LIBRARY_PATH CLASSPATH LD_ASSUME_KERNEL TMP TMPDIR

2.手工创建如下目录

mkdir -p /u01/app/oracle/admin/vrh9i/cdump
mkdir -p /u01/app/oracle/admin/vrh9i/bdump
mkdir -p /u01/app/oracle/admin/vrh9i/udump
mkdir -p /u01/app/oracle/admin/vrh9i/adump
mkdir -p /u01/app/oracle/oradata/vrh9i

chmod -R 755 /u01
chown -R oracle:oinstall /u01



3. 创建最简单的initvrh9i.ora文件

cd $ORALE_HOME/dbs

cat init.ora | grep -v ^# | grep -v ^$ > initSID.ora


$ vi $ORACLE_HOME/dbs/initvrh9i.ora

db_block_size=8192
db_cache_size=801112064

cursor_sharing=similar
open_cursors=300

background_dump_dest=/u01/app/oracle/admin/vrh9i/bdump
core_dump_dest=/u01/app/oracle/admin/vrh9i/cdump
user_dump_dest=/u01/app/oracle/admin/vrh9i/udump
audit_file_dest=/u01/app/oracle/admin/vrh9i/adump

control_files=(/u01/app/oracle/oradata/vrh9i/control01.ctl,
/u01/app/oracle/oradata/vrh9i/control02.ctl,
/u01/app/oracle/oradata/vrh9i/control03.clt)

log_archive_dest_1='location=/u01/app/oracle/oradata/vrh9i/archive'
log_archive_format=%t_%s.dbf
log_archive_start=true

compatible=9.2.0
db_name=vrh9i

db_domain=oracle.com
remote_login_passwordfile=exclusive

instance_name=vrh9i

java_pool_size=31457280
large_pool_size=1048576
shared_pool_size=52428800
log_buffer=6283264

processes=150

sga_max_size=1g
pga_aggregate_target=214748364

fast_start_mttr_target=300

resource_manager_plan=system_plan

sort_area_size=1048576

undo_management=auto
undo_tablespace=undotbs1
                                              

4. 启动数据库到nomount状态

此时已经有可供启动的初始化参数文件了，将数据库启动到nomount状态。

SQL> startup nomount;

5. 创建spfile

实例启动以后立刻创建spfile，然后重启一次数据库，让数据库能够使用到spfile。

SQL> create spfile from pfile;

6.创建密码文件

用orapwd程序创建orapwvrh9i密码文件，如果记不清楚orapwd程序怎么用，直接敲orapwd然后回车，会告诉你语法是怎样的。

orapwd file=$ORACLE_HOME/dbs/orapwvrh9i password=oracle entries=30

7. 要快速找到例句要查的是Administrator’s Guide这本文档中第二章 Creating an Oracle Database -> Creating the database -> Issue the CREATE DATABASE Statement，
这里有完整的一条SQL语句，copy出来，然后按照实际需求编辑相应的地方， 然后执行（NOTE:修改MAXLOGFILES,undo,default）。

具体如下：

[oracle@vrhvrh9i dbs]$ vi createdb.sql
       
CREATE DATABASE vrh9i
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/vrh9i/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/vrh9i/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/vrh9i/redo03.log') SIZE 100M
MAXLOGFILES 30
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 200
MAXINSTANCES 2
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/app/oracle/oradata/vrh9i/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
DATAFILE '/u01/app/oracle/oradata/vrh9i/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/vrh9i/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
       
SQL> startup nomount
ORACLE instance started.

Total System Global Area  335544320 bytes
Fixed Size                  2020640 bytes
Variable Size             113248992 bytes
Database Buffers          218103808 bytes
Redo Buffers                2170880 bytes

SQL> @createdb.sql

Database created.

SQL>
       
8.创建缺省表空间

[oracle@vrhvrh9i dbs]$ sqlplus / as sysdba

SQL> CREATE TABLESPACE CLS DATAFILE '/var/oracle/oradata/vrh9i/cls01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;



9. 运行catalog.sql（建数据字典视图） 和 catproc.sql（建存储过程包）

只需要运行这两个SQL，都在$ORACLE_HOME/rdbms/admin中，创建必须的数据字典和内置的package等

SQL>spool /home/oracle/cat.log

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

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



最终可以查看一些状态

select open_mode, name from v$database;

select startup_time,status from v$instance;