Oracle手工建库的笔记（不通过图形界面）
时间：2010-08-31 来源：osdba
+手工建oracle 9i数据库+
===================================================================================
++设置环境变量++
查看.profile中的配置是否正确：
export ORACLE_BASE=/opt/oracle
export ORACLE_SID=oratest
export ORACLE_HOME=/opt/oracle/products/9.2.0
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/udump
mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/arch
++配置pfile++
cat > $ORACLE_HOME/dbs/init$ORACLE_SID.ora <<EOF *.aq_tm_processes=0 *.background_dump_dest='$ORACLE_BASE/admin/$ORACLE_SID/bdump' *.compatible='9.2.0.0.0' *.control_files='$ORACLE_BASE/oradata/$ORACLE_SID/control01.ctl','$ORACLE_BASE/oradata/$ORACLE_SID/control02.ctl' *.core_dump_dest='$ORACLE_BASE/admin/$ORACLE_SID/cdump' *.db_block_size=8192 *.db_cache_size=102400000 *.db_file_multiblock_read_count=16 *.db_name='$ORACLE_SID' *.fast_start_mttr_target=300 *.hash_join_enabled=TRUE *.instance_name='$ORACLE_SID' *.job_queue_processes=0 *.large_pool_size=150000000 *.log_archive_dest_1='location=$ORACLE_BASE/oradata/$ORACLE_SID/arch' *.log_archive_start=TRUE *.open_cursors=300 *.pga_aggregate_target=50000000 *.processes=500 *.query_rewrite_enabled='FALSE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=50000000 *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=10800 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='$ORACLE_BASE/admin/$ORACLE_SID/udump' *.log_archive_start=TRUE EOF
++创建数据库++ 生成建库的脚本： cat >$ORACLE_BASE/admin/$ORACLE_SID/create/createdb.sh <<EOF orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install
sqlplus "/ as sysdba" <<EEE spool $ORACLE_BASE/admin/$ORACLE_SID/create/createdb.log startup nomount pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora; @$ORACLE_BASE/admin/$ORACLE_SID/create/createdb.sql; EEE EOF
cat >$ORACLE_BASE/admin/$ORACLE_SID/create/createdb.sql <<EOF 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 <<EOF 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
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/udump
mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/arch
++配置pfile++
cat > $ORACLE_HOME/dbs/init$ORACLE_SID.ora <<EOF *.aq_tm_processes=0 *.background_dump_dest='$ORACLE_BASE/admin/$ORACLE_SID/bdump' *.compatible='9.2.0.0.0' *.control_files='$ORACLE_BASE/oradata/$ORACLE_SID/control01.ctl','$ORACLE_BASE/oradata/$ORACLE_SID/control02.ctl' *.core_dump_dest='$ORACLE_BASE/admin/$ORACLE_SID/cdump' *.db_block_size=8192 *.db_cache_size=102400000 *.db_file_multiblock_read_count=16 *.db_name='$ORACLE_SID' *.fast_start_mttr_target=300 *.hash_join_enabled=TRUE *.instance_name='$ORACLE_SID' *.job_queue_processes=0 *.large_pool_size=150000000 *.log_archive_dest_1='location=$ORACLE_BASE/oradata/$ORACLE_SID/arch' *.log_archive_start=TRUE *.open_cursors=300 *.pga_aggregate_target=50000000 *.processes=500 *.query_rewrite_enabled='FALSE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=50000000 *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=10800 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='$ORACLE_BASE/admin/$ORACLE_SID/udump' *.log_archive_start=TRUE EOF
++创建数据库++ 生成建库的脚本： cat >$ORACLE_BASE/admin/$ORACLE_SID/create/createdb.sh <<EOF orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install
sqlplus "/ as sysdba" <<EEE spool $ORACLE_BASE/admin/$ORACLE_SID/create/createdb.log startup nomount pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora; @$ORACLE_BASE/admin/$ORACLE_SID/create/createdb.sql; EEE EOF
cat >$ORACLE_BASE/admin/$ORACLE_SID/create/createdb.sql <<EOF 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 <<EOF 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
相关阅读 更多 +
