oracle后台包创建,oracle dba 手动创建数据实例

1.手动建库大致步骤

设置环境变量.bash_profile

创建目录结构

创建参数文件(位置:$ORACLE_HOME/dbs)

生成密码文件

执行建库脚本

创建数据字典

其他设置

2.DBCA 脚本创建

2.1设置系统环境变量

ORACLE_HOME=/app/oracle/11g/11.2.4.2;export ORACLE_HOME

ORACLE_BASE=/app/oracle/11g;export ORACLE_BASE

ORACLE_SID=dw; export ORACLE_SID

PATH=/usr/sbin:$PATH; export $PATH

PATH=$ORACLE_HOME/bin:$PATH; export $PATH

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

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

2.2创建相应的目录

cd $ORACLE_BASE

cd admin

mkdir $ORACLE_SID

cd $ORACLE_SID

mkdir adump bdump cdump dpdump pfile udump

ps:确认相应的目录

echo  $ORACLE_SID

echo  $ORACLE_BASE

echo  $ORACLE_HOME

2.3修改pfile参数

具体情况而定修改相应的参数如：audit_file_dest，background_dump_dest，control_files，core_dump_dest，log_archive_dest_1，user_dump_dest等

2.4生成密码文件

cd $ORACLE_HOME/dbs

orapwd file=orapw$ORACLE_SID password=&pwd entries=8

2.5 连接到数据库，启动到nomount状态

sqlplus / as sysdba

startup nomount

2.6执行建库脚本

ASM 磁盘组

Cat create_db.sql

CREATE DATABASE dw

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 '+DATA_DG' size 300m,

GROUP 2 '+DATA_DG' size 300m,

GROUP 3 '+DATA_DG' size 300m

MAXLOGFILES 32

MAXLOGMEMBERS 3

MAXLOGHISTORY 1

MAXDATAFILES 300

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE '+DATA_DG' SIZE 800M REUSE

SYSAUX DATAFILE '+DATA_DG' SIZE 800M REUSE

DEFAULT TABLESPACE users

DATAFILE '+DATA_DG'

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE temp

TEMPFILE '+data_dg'

SIZE 5120M REUSE

UNDO TABLESPACE UNDOTBS1

DATAFILE '+DATA_DG'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

文件系统

CREATE DATABASE dw

USER SYS IDENTIFIED BY oracle

USER SYSTEM IDENTIFIED BY oracle

LOGFILE GROUP 1 ('/u01/app/oracle/oradata/dw/redo01a.log','/u01/app/oracle/oradata/dw/redo01b.log') SIZE 512M),

GROUP 2 ('/u01/app/oracle/oradata/dw/redo02a.log','/u01/app/oracle/oradata/dw/redo02b.log') SIZE 512M),

GROUP 3 ('/u01/app/oracle/oradata/dw/redo01a.log','/u01/app/oracle/oradata/dw/redo01b.log') SIZE 512M),

MAXLOGFILES 32

MAXLOGMEMBERS 3

MAXLOGHISTORY 1

MAXDATAFILES 300

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE '/u01/app/oracle/oradata/dw/system01.dbf' SIZE 800M REUSE

SYSAUX DATAFILE '/u01/app/oracle/oradata/dw/sysaux01.dbf' SIZE 800M REUSE

DEFAULT TABLESPACE users

DATAFILE '/u01/app/oracle/oradata/dw/user01.dbf

SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE temp

TEMPFILE '/u01/app/oracle/oradata/dw/temp01.dbf'

SIZE 5120M REUSE

UNDO TABLESPACE UNDOTBS1

DATAFILE '+DATA_DG'

SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Ps:

相应的路径与属性根据相应的需求进行修改

2.6执行创建数据字典脚本

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/sqlplus/admin/pupbld.sql #需使用system用户执行

3.DBCA静默建库

vmhost:oracle > dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName dw -sid dw -sysPassword oracle -systemPassword oralce -storageType ASM -diskGroupName data_dg -nationalCharacterSet "AL16UTF16" -characterSet "AL32UTF8" -nodeinfo cnlf081000 -sampleSchema false -asmSysPassword oracle -variablesFile /app /oracle/11g/11.2.4.2/dbs/initdw.ora -recoveryAreaDestination +fra_dg   -databaseType  DATA_WAREHOUSING

Copying database files

1% complete

3% complete

9% complete

15% complete

21% complete

27% complete

30% complete

Creating and starting Oracle instance

32% complete

36% complete

40% complete

44% complete

45% complete

48% complete

50% complete

Creating cluster database views

52% complete

70% complete

Completing Database Creation

73% complete

76% complete

85% complete

94% complete

100% complete

Look at the log file "/app/oracle/11g/cfgtoollogs/dbca/dw/dw.log" for further details.

vmhost:oracle>