Oracle手工建库的笔记（不通过图形界面）

+手工建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 <

*.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 <

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=change_on_install

sqlplus "/ as sysdba" <

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 <

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

 

阅读(3242) | 评论(0) | 转发(0) |

0

上一篇：Linux下安装oracle的简要步骤笔记

下一篇：oracle下DML操作是否会产生redo

给主人留下些什么吧！~~

评论热议

请登录后评论。

登录 注册