建库前准备
前提保证Oracle软件已安装,环境变量设置没有问题。
查看当前实例名 echo $ORACLE_SID
指定一个实例名 export ORACLE_SID=test; 任意修改
进入环境变量修改 .bash_profile 只能有一个SID,想看哪个库,使他的SID生效一下就行了。
. .bash_profile => source .bash_profile 使环境变量生效
--------------------------------Oracle 11g-------------------------------自写
pfile设置
/u01/app/oracle/product/11.2.0/db_1/dbs inittestdb.ora
db_name='testdb'
memory_target=500M
control_files='/u02/oradata/test/control01.ctl','/u02/oradata/test/control02.ctl'
进入数据,启动startup nomount pfile=inittest ,因为没有spfile 。
-----建库脚本
CREATE DATABASE testdb
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
LOGFILE GROUP 1 ('/u02/oradata/test/redo01a.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u02/oradata/test/redo02a.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u02/oradata/test/redo03a.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u02/oradata/test/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u02/oradata/test/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u02/oradata/test/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u02/oradata/test/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u02/oradata/test/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
@?/rdbms/admin/catalog.sql sys用户跑脚本(创建对象、同义词、视图)
@?/rdbms/admin/catproc.sql sys用户跑脚本(建表与授权,包体的创建)
@?/sqlplus/admin/pupbld.sql system/oracle用户跑脚本
@?/rdbms/admin/utlsampl.sql 创建scott用户
--------------------------------Oracle 10g-------------------------------翟老师
pfile设置
/u01/app/oracle/product/11.2.0/db_1/dbs initBOB.ora
sga_target=400m
db_name=BOB
control_file=’u01/app/oracle/oradata/BOB/disk1/control01.ctl’,
’u01/app/oracle/oradata/BOB/disk2/control01.ctl’,
’u01/app/oracle/oradata/BOB/disk3/control01.ctl’;
undo_management=auto
undo_tablespace=undotbs
user_dump_dest=/u01/app/oracle/oradata/BOB/udump
backgroup_dump_dest=/u01/app/oracle/oradata/BOB/bdump
core_dump_dest=/u01/app/oracle/oradata/BOB/cdump
-----建库脚本
CREATE DATABASE testdb
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/BOB/disk01 rede01.log’,
‘/u01/app/oracle/oradata/BOB/disk02 rede01.log’) SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/BOB/disk01 rede01.log’,
‘/u01/app/oracle/oradata/BOB/disk02 rede01.log’) SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/BOB/disk01 rede01.log’,
‘/u01/app/oracle/oradata/BOB/disk02 rede01.log’) SIZE 100M
CHARACTER SET zhs16gsk
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/BOB/disk03/system01.dbf' SIZE 325M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/BOB/disk04/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/BOB/disk05/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/BOB/disk04/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
跑脚本 sys用户:@?/rdbms/admin/catalog --创建数据字典(2分钟)
Sys用户:@?/rdbms/admin/catproc --创建包和存储过程(5分钟的时间)
System/manager用户:@?/sqlplus/admin/pupbld --让一般用户能登陆数据库
create tablespace users datafile ‘/u01/app/oracle/oradata/BOB/disk3/users01.dbf’
size 50 autoextend on next 10m maxsize unlimited;
alter database default tablespace users; --设置users为默认表空间
注意:建库失败怎么办
1、删除oradata下该BOB文件夹及其文件,并删除初始化参数文件。
2、重新编写初始化参数文件和建库脚本,注意两点:
(1)undo 、temp 表空间要换名字
(2)建库脚本和初始化参数文件中的undo表空间要名字一致。
------------------------------------------------------------------------------------------