CREATE DATABASE $ORACLE_SID
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 12
MAXLOGMEMBERS 3
MAXDATAFILES 600
DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/system01.dbf' SIZE 400M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '$ORACLE_BASE/oradata/$ORACLE_SID/temp01.dbf' SIZE 500M REUSE
UNDO TABLESPACE "UNDOTBS1" DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/undotbs01.dbf' SIZE 500M REUSE
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
archivelog
LOGFILE GROUP 1 ('$ORACLE_BASE/oradata/$ORACLE_SID/redo01.log') SIZE 500M,
GROUP 2 ('$ORACLE_BASE/oradata/$ORACLE_SID/redo02.log') SIZE 500M,
GROUP 3 ('$ORACLE_BASE/oradata/$ORACLE_SID/redo03.log') SIZE 500M,
GROUP 4 ('$ORACLE_BASE/oradata/$ORACLE_SID/redo04.log') SIZE 500M,
GROUP 5 ('$ORACLE_BASE/oradata/$ORACLE_SID/redo05.log') SIZE 500M;
--CreateDBCatalog.sql
set echo on
@/opt/oracle/products/9.2.0/rdbms/admin/catalog.sql;
@/opt/oracle/products/9.2.0/rdbms/admin/catblock.sql;
@/opt/oracle/products/9.2.0/rdbms/admin/catproc.sql;
@/opt/oracle/products/9.2.0/rdbms/admin/catoctk.sql;
@/opt/oracle/products/9.2.0/rdbms/admin/owminst.plb;
@$ORACLE_HOME/sqlplus/admin/plustrce.sql;
alter user sys identified by sys;
alter user system identified by system;
create spfile from pfile;
connect SYSTEM/system
@/opt/oracle/products/9.2.0/sqlplus/admin/pupbld.sql;
@/opt/oracle/products/9.2.0/sqlplus/admin/help/hlpbld.sql helpus.sql;
exit;
EOF
chmod 755 $ORACLE_BASE/admin/$ORACLE_SID/create/createdb.sh
运行生成的脚本:
$ORACLE_BASE/admin/$ORACLE_SID/create/createdb.sh
++建其它一些表空间++
CREATE TABLESPACE "TOOLS" LOGGING DATAFILE '/data/oradata/logtest/tools01.dbf' SIZE 200M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT MANUAL ;
CREATE TABLESPACE tbs_test
DATAFILE '/opt/oracle/oradata/oratest/tbs_test_01.dbf' SIZE 20000M autoextend off
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT auto ;
+手工建oracle 10g数据库++
===================================================================================
++配置环境++
groupadd -g 501 oinstall
groupadd -g 502 dba
useradd -c "Oracle software owner" -g oinstall -G oinstall,dba -u 501 -m oracle
(useradd -c "Oracle software owner" -d /export/home/oracle/ -g oinstall -G oinstall,dba -u 501 oracle )
su - root
export ORACLE_BASE=/opt/oracle
export oracle_version=10.2
export ORACLE_SID=oratest
export ORACLE_HOME=$ORACLE_BASE/products/$oracle_version
export ORA_NLS10=$ORACLE_HOME/nls/data
#mkdir -p $ORACLE_BASE/products/$oracle_version
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/create
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/scripts
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump
mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID
#chown -R oracle:oinstall $ORACLE_BASE/admin
++准备pfile++
准备$ORACLE_HOME/dbs/initoratest.ora文件
db_domain='db.alibaba.com'
db_name='oratest'
sga_max_size=2600M
sga_target=2600M
java_pool_size=16777216
large_pool_size=16777216
shared_pool_size=838860800
pga_aggregate_target=1G
processes=500
audit_file_dest='/opt/oracle/admin/oratest/adump'
background_dump_dest='/opt/oracle/admin/oratest/bdump'
user_dump_dest='/opt/oracle/admin/oratest/udump'
core_dump_dest='/opt/oracle/admin/oratest/cdump'
control_files='/opt/oracle/oradata/oratest/control01.ctl','/opt/oracle/oradata/oratest/control02.ctl'
log_archive_dest_1='location=/opt/oracle/oradata/oratest/arch alternate=log_archive_dest_3 reopen=30 MAX_FAILURE=2'
log_archive_format='oratest_%t_%s_%r.arc'
compatible='10.2.0.1.0'
db_block_size=8192
job_queue_processes=10
open_cursors=300
remote_login_passwordfile='NONE'
remote_os_authent=FALSE
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
根据情况添加,solaris不要添加这行。
lock_sga=TRUE
++建库++
sqlplus "/ as sysdba"
startup nomount;
注意字符集:CHARACTER SET US7ASCII
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
CREATE DATABASE oratest
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY system
CONTROLFILE REUSE
LOGFILE GROUP 1 ('/opt/oracle/oradata/oratest/redo_1_1.log') SIZE 200M REUSE,
GROUP 2 ('/opt/oracle/oradata/oratest/redo_2_1.log') SIZE 200M REUSE,
GROUP 3 ('/opt/oracle/oradata/oratest/redo_3_1.log') SIZE 200M REUSE,
GROUP 4 ('/opt/oracle/oradata/oratest/redo_4_1.log') SIZE 200M REUSE,
GROUP 5 ('/opt/oracle/oradata/oratest/redo_5_1.log') SIZE 200M REUSE
MAXLOGFILES 20
MAXLOGMEMBERS 5
MAXLOGHISTORY 1000
MAXDATAFILES 1000
MAXINSTANCES 2
noARCHIVELOG
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/opt/oracle/oradata/oratest/system01.dbf' SIZE 2046M REUSE
SYSAUX DATAFILE '/opt/oracle/oradata/oratest/sysaux01.dbf' SIZE 2046M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/opt/oracle/oradata/oratest/temp01.dbf' SIZE 2046M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '/opt/oracle/oradata/oratest/undotbs01.dbf' SIZE 2046M REUSE
SET TIME_ZONE = '+08:00';
如果是ASM,把建库的内容换成如下:
CREATE DATABASE ctrdm
USER SYS IDENTIFIED BY bcycl11ss98767
USER SYSTEM IDENTIFIED BY jdsku847cndy6
LOGFILE GROUP 1 ('+DG1/oradata/ctrdm/redo_1_1.log') SIZE 512M REUSE,
GROUP 2 ('+DG1/oradata/ctrdm/redo_2_1.log') SIZE 512M REUSE,
GROUP 3 ('+DG1/oradata/ctrdm/redo_3_1.log') SIZE 512M REUSE,
GROUP 4 ('+DG1/oradata/ctrdm/redo_4_1.log') SIZE 512M REUSE,
GROUP 5 ('+DG1/oradata/ctrdm/redo_5_1.log') SIZE 512M REUSE
MAXLOGFILES 20
MAXLOGMEMBERS 5
MAXLOGHISTORY 1000
MAXDATAFILES 2000
MAXINSTANCES 2
ARCHIVELOG
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET UTF8
DATAFILE '+DG1/oradata/ctrdm/system01.dbf' SIZE 2047M REUSE
SYSAUX DATAFILE '+DG1/oradata/ctrdm/sysaux01.dbf' SIZE 2047M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '+DG1/oradata/ctrdm/temp01.dbf' SIZE 16384M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '+DG1/oradata/ctrdm/undotbs01.dbf' SIZE 16384M REUSE;
注意:当create database文件超过裸设备大小时会报ORA-01092:ORACLE例程终止。强行断开连接
--建立内部视图
spool $ORACLE_BASE/admin/$ORACLE_SID/scriptsCreateDB.log
@$ORACLE_HOME/rdbms/admin/catalog.sql;
@$ORACLE_HOME/rdbms/admin/catblock.sql;
@$ORACLE_HOME/rdbms/admin/catproc.sql;
@$ORACLE_HOME/rdbms/admin/catoctk.sql;
@$ORACLE_HOME/rdbms/admin/owminst.plb;
@$ORACLE_HOME/sqlplus/admin/plustrce.sql;
alter user sys identified by sys;
alter user system identified by system;
create spfile from pfile;
connect SYSTEM/system
@$ORACLE_HOME/sqlplus/admin/pupbld.sql;
@$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql;
connect / as sysdba;
shutdown immediate;
connect / as sysdba;
startup;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
spool off
+手工建oracle 11g asm数据库++
===================================================================================
++配置环境++
groupadd -g 501 oinstall
groupadd -g 502 dba
useradd -c "Oracle software owner" -g oinstall -G oinstall,dba -u 501 oracle
(useradd -c "Oracle software owner" -d /export/home/oracle/ -g oinstall -G oinstall,dba -u 501 oracle )
su - root
export ORACLE_BASE=/opt/oracle
export oracle_version=11.1
export ORACLE_SID=oratest
export ORACLE_HOME=/home/oracle/products/11.1
export ORA_NLS10=$ORACLE_HOME/nls/data
++建立asm实例++
需要在root用户下运行:$ORACLE_HOME/bin/localconfig add
export ORACLE_SID=myasm
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile
建文件:$ORACLE_HOME/dbs文件initmyasm.ora:
cat >$ORACLE_HOME/dbs/init$ORACLE_SID.ora <
instance_type=asm
asm_diskgroups=dg1
asm_diskstring='/dev/sd*'
processes=200
large_pool_size=12M
remote_login_passwordfile=exclusive
EOF
#如果使用asm lib 则asm_diskstring设置中的前缀为ORCL:
#asm_diskstring='ORCL:hw*'
启动数据库:
如果报ORA-29701:
SQL> startup;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORA-29701: unable to connect to Cluster Manager
需要运行:
/opt/oracle/products/11.1/bin/localconfig add
查看asm中看到的硬盘:
set linesize 132
col name format a20
col path format a20
col failgroup format a16
select group_number,name,path,state,mode_status from v$asm_disk;
++建数据库实例++
#mkdir -p $ORACLE_BASE/products/$oracle_version
mkdir -p /opt/oracle/admin/$ORACLE_SID/adump
mkdir -p /opt/oracle/admin/$ORACLE_SID/dpdump
mkdir -p /opt/oracle/admin/$ORACLE_SID/pfile
mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID
++准备pfile++
准备$ORACLE_HOME/dbs/init$ORACLE_SID.ora文件
db_name='oratest'
sga_max_size=2600M
sga_target=2600M
java_pool_size=16777216
large_pool_size=16777216
shared_pool_size=838860800
pga_aggregate_target=1G
processes=500
control_files='+dg1/oratest/control01.ctl','+dg1/oratest/control02.ctl'
log_archive_dest_1='location=/opt/oracle/oradata/oratest/arch reopen=30 MAX_FAILURE=2'
log_archive_format='oratest_%t_%s_%r.arc'
compatible='11.1.0'
db_block_size=8192
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
根据情况添加,solaris不要添加这行。
lock_sga=TRUE
++建库++
sqlplus "/ as sysdba"
startup nomount;
CREATE DATABASE oratest
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY system
CONTROLFILE REUSE
LOGFILE GROUP 1 ('+dg1/oratest/redo_1_1.log') SIZE 200M REUSE,
GROUP 2 ('+dg1/oratest/redo_2_1.log') SIZE 200M REUSE,
GROUP 3 ('+dg1/oratest/redo_3_1.log') SIZE 200M REUSE,
GROUP 4 ('+dg1/oratest/redo_4_1.log') SIZE 200M REUSE,
GROUP 5 ('+dg1/oratest/redo_5_1.log') SIZE 200M REUSE
MAXLOGFILES 20
MAXLOGMEMBERS 5
MAXLOGHISTORY 1000
MAXDATAFILES 1000
MAXINSTANCES 2
noARCHIVELOG
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '+dg1/oratest/system01.dbf' SIZE 2046M REUSE
SYSAUX DATAFILE '+dg1/oratest/sysaux01.dbf' SIZE 2046M REUSE
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '+dg1/oratest/temp01.dbf' SIZE 2046M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '+dg1/oratest/undotbs01.dbf' SIZE 2046M REUSE
SET TIME_ZONE = '+08:00';
注意字符集:CHARACTER SET US7ASCII
注意:当create database文件超过裸设备大小时会报ORA-01092:ORACLE例程终止。强行断开连接
--建立内部视图
spool $ORACLE_BASE/admin/$ORACLE_SID/scripts/CreateDBCatalog.log
@$ORACLE_HOME/rdbms/admin/catalog.sql;
@$ORACLE_HOME/rdbms/admin/catblock.sql;
@$ORACLE_HOME/rdbms/admin/catproc.sql;
@$ORACLE_HOME/rdbms/admin/catoctk.sql;
@$ORACLE_HOME/rdbms/admin/owminst.plb;
@$ORACLE_HOME/sqlplus/admin/plustrce.sql;
alter user sys identified by sys;
alter user system identified by system;
create spfile from pfile;
connect SYSTEM/system
@$ORACLE_HOME/sqlplus/admin/pupbld.sql;
@$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql;
connect / as sysdba;
shutdown immediate;
connect / as sysdba;
startup;
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;
spool off
+附录1:使用dm建裸设备+
#这里的大小单位为512字节,即以扇区为单位
echo "0 2097152 linear /dev/sdc1 0 " | dmsetup create oratest_control01
echo "0 2097152 linear /dev/sdc1 2097152" | dmsetup create oratest_control02
echo "0 1048576 linear /dev/sdc1 3145728" | dmsetup create oratest_redo_1_1
echo "0 1048576 linear /dev/sdc1 4194304" | dmsetup create oratest_redo_2_1
echo "0 1048576 linear /dev/sdc1 5242880" | dmsetup create oratest_redo_3_1
echo "0 1048576 linear /dev/sdc1 6291456" | dmsetup create oratest_redo_4_1
echo "0 1048576 linear /dev/sdc1 7340032" | dmsetup create oratest_redo_5_1
echo "0 4192256 linear /dev/sdc1 11532288" | dmsetup create oratest_system01
echo "0 4192256 linear /dev/sdc1 15724544" | dmsetup create oratest_sysaux01
echo "0 4192256 linear /dev/sdc1 19916800" | dmsetup create oratest_temp01
echo "0 4192256 linear /dev/sdc1 24109056" | dmsetup create oratest_undotbs01
echo "0 4194304 linear /dev/sdc1 28303360" | dmsetup create oratest_2048M_001
echo "0 4194304 linear /dev/sdc1 32497664" | dmsetup create oratest_2048M_002
echo "0 4194304 linear /dev/sdc1 36691968" | dmsetup create oratest_2048M_003
echo "0 4194304 linear /dev/sdc1 40886272" | dmsetup create oratest_2048M_004
echo "0 4194304 linear /dev/sdc1 45080576" | dmsetup create oratest_2048M_005
echo "0 4194304 linear /dev/sdc1 49274880" | dmsetup create oratest_2048M_006
echo "0 4194304 linear /dev/sdc1 53469184" | dmsetup create oratest_2048M_007
echo "0 4194304 linear /dev/sdc1 57663488" | dmsetup create oratest_2048M_008
echo "0 67108864 linear /dev/sdc1 120578048" | dmsetup create oratest_32G_009
数据库如果是10.2.0.1不能直接使用块设备,需要把数据库升级到10.2.0.4
否则建库时会报:ORA-27094
ln -s /dev/mapper/oratest_control01 control01.ctl
ln -s /dev/mapper/oratest_control02 control02.ctl
ln -s /dev/mapper/oratest_redo_1_1 redo_1_1.log
ln -s /dev/mapper/oratest_redo_2_1 redo_2_1.log
ln -s /dev/mapper/oratest_redo_3_1 redo_3_1.log
ln -s /dev/mapper/oratest_redo_4_1 redo_4_1.log
ln -s /dev/mapper/oratest_redo_5_1 redo_5_1.log
ln -s /dev/mapper/oratest_system01 system01.dbf
ln -s /dev/mapper/oratest_sysaux01 sysaux01.dbf
ln -s /dev/mapper/oratest_temp01 temp01.dbf
ln -s /dev/mapper/oratest_undotbs01 undotbs01.dbf
ln -s /dev/mapper/oratest_2048M_001 data_2048M_001.dbf
ln -s /dev/mapper/oratest_2048M_002 data_2048M_002.dbf
ln -s /dev/mapper/oratest_2048M_003 data_2048M_003.dbf
ln -s /dev/mapper/oratest_2048M_004 data_2048M_004.dbf
ln -s /dev/mapper/oratest_2048M_005 data_2048M_005.dbf
ln -s /dev/mapper/oratest_2048M_006 data_2048M_006.dbf
ln -s /dev/mapper/oratest_2048M_007 data_2048M_007.dbf
ln -s /dev/mapper/oratest_2048M_008 data_2048M_008.dbf
+附录2 solaris 软分区+
ln -s /dev/md/rdsk/d100 control01.ctl
ln -s /dev/md/rdsk/d101 control02.ctl
ln -s /dev/md/rdsk/d102 system01.dbf
ln -s /dev/md/rdsk/d103 sysaux01.dbf
ln -s /dev/md/rdsk/d104 temp01.dbf
ln -s /dev/md/rdsk/d105 undotbs01.dbf
ln -s /dev/md/rdsk/d106 redo_1_1.log
ln -s /dev/md/rdsk/d107 redo_2_1.log
ln -s /dev/md/rdsk/d108 redo_3_1.log
ln -s /dev/md/rdsk/d109 redo_4_1.log
ln -s /dev/md/rdsk/d110 redo_5_1.log
阅读(3258) | 评论(0) | 转发(0) |