分类： Oracle
2010-04-12 22:44:24
ORACLE 2010-03-21 16:15:55 阅读54 评论1 字号：大中小
0，给电脑增加2块SCSI硬盘 1
1，安装ASM 1
2，创建两个ASM磁盘组，分别为：GROUP1、GROUP2 2
下面是生产的日志！ 3
3,开始创建数据库，首先建立参数文件，保存为 $ORACLE_HOME/dbs/initbkeep.ora 4
4，建立密码文件$ORACLE_HOME/dbs/orapwdbkeep 5
5建立跟踪、日志文件的路径 5
6，启动数据库到nomount状态 5
查看启动日志 5
7，创建并运行建库的脚本 6
8，创建erp表空间
9，创建数据字典视图
10，建立spfile
下面这个做法才是真的！
11，创建数据库用户
12，配置网络服务
13，扩展
a, 查看ASM groups 组号、组名、总大小、空闲空间
b, 删除group2
c, 把ORCL:DISK2添加到GROUP1中去
d, 查看负载信息
e, 重新负载均衡
正文：
Oracle 10g 手工创建数据库
今天尝试除DBCA以外的另一种方法来创建数据库，先前做过oracle9i手工建库的实验；数据版本是10g 10.2.0.1.0 搭配了ASM。
[root@kk ~]# uname -rm
2.6.18-92.el5 i686
[root@kk ~]# cd 32bit/
[root@kk 32bit]# rpm -ivh oracleasm-support-2.1.3-1.el5.i386.rpm
warning: oracleasm-support-2.1.3-1.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:oracleasm-support ########################################### [100%]
[root@kk 32bit]# rpm -ivh oracleasm-2.6.18-92.el5
[root@kk 32bit]# rpm -ivh oracleasm-2.6.18-92.el5-2.0.5-1.el5.i686.rpm
warning: oracleasm-2.6.18-92.el5-2.0.5-1.el5.i686.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:oracleasm-2.6.18-92.el5########################################### [100%]
[root@kk 32bit]# rpm -ivh oracleasmlib-2.0.4-1.el5.i386.rpm
warning: oracleasmlib-2.0.4-1.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:oracleasmlib ########################################### [100%]
[root@kk 32bit]# vi /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take on
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - SELinux is fully disabled.
SELINUX=disabled
# SELINUXTYPE= type of policy in use. Possible values are:
# targeted - On
# strict - Full SELinux protection.
SELINUXTYPE=targeted
~
[root@kk 32bit]# setenforce 0
[root@kk 32bit]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]
[root@kk 32bit]# /etc/init.d/oracleasm enable
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]
[root@kk 32bit]# cd /opt/oracle/product/10.2.0/db_1/bin/
[root@kk bin]# ./localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab
Startup will be queued to init within 90 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
kk
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)
[root@kk bin]# fdisk -l
Disk /dev/sda: 42.9 GB, 42949672960 bytes
255 heads, 63 sectors/track, 5221 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sda1 * 1 25 200781 83 Linux
/dev/sda2 26 286 2096482+ 82 Linux swap / Solaris
/dev/sda3 287 5221 39640387+ 83 Linux
Disk /dev/sdb: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 1044 8385898+ 83 Linux
Disk /dev/sdc: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdc1 1 1044 8385898+ 83 Linux
[root@kk bin]# /etc/init.d/oracleasm createdisk DISK1 /dev/sdb1
Marking disk "DISK1" as an ASM disk: [ OK ]
[root@kk bin]# /etc/init.d/oracleasm createdisk DISK2 /dev/sdc1
Marking disk "DISK2" as an ASM disk: [ OK ]
[root@kk bin]# /etc/init.d/oracleasm listdisk
Usage: /etc/init.d/oracleasm {start|stop|restart|enable|disable|configure|createdisk|deletedisk|querydisk|listdisks|scandisks|status}
[root@kk bin]# /etc/init.d/oracleasm listdisks
DISK1
DISK2
Tips:要创建diskgroup就必须运行+ASM实例
如何创建+ASM实例呢？
dbca ==> Configure Automatic Storage Management 这样就ok了
[oracle@kk bdump]$ cat alert_+ASM.log
Wed Mar 17 12:44:09 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /opt/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned off.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
large_pool_size = 12582912
instance_type = asm
remote_login_passwordfile= SHARED
background_dump_dest = /opt/oracle/admin/+ASM/bdump
user_dump_dest = /opt/oracle/admin/+ASM/udump
core_dump_dest = /opt/oracle/admin/+ASM/cdump
MMAN started with pid=4, OS id=5843
PSP0 started with pid=3, OS id=5841
DBW0 started with pid=5, OS id=5845
PMON started with pid=2, OS id=5839
CKPT started with pid=7, OS id=5849
SMON started with pid=8, OS id=5851
LGWR started with pid=6, OS id=5847
RBAL started with pid=9, OS id=5853
GMON started with pid=10, OS id=5855
Wed Mar 17 12:44:15 2010
SQL> ALTER DISKGROUP ALL MOUNT
Wed Mar 17 12:44:16 2010
Loaded ASM Library - Generic Linux, version 2.0.4 (KABI_V2) library for asmlib interface
[oracle@kk bdump]$ export ORACLE_SID=+ASM
[oracle@kk bdump]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 17 12:50:55 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> create diskgroup group1 external redundancy disk 'ORCL:DISK1';
Diskgroup created.
SQL> create diskgroup group2 external redundancy disk 'ORCL:DISK2';
Diskgroup created.
SQL> select name,path,group_number from v$asm_disk;
NAME PATH GROUP_NUMBER
--------------- ---------------------- ----------------------------
DISK1 ORCL:DISK1 1
DISK2 ORCL:DISK2 2
[oracle@kk dbs]$ pwd
/opt/oracle/product/10.2.0/db_1/dbs
[oracle@kk dbs]$ vi initbkeep.ora
*.audit_file_dest='/opt/oracle/admin/bkeep/adump'
*.background_dump_dest='/opt/oracle/admin/bkeep/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+GROUP1/bkeep/control01.ctl','+GROUP1/bkeep/control02.ctl','+GROUP1/bkeep/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/bkeep/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='bkeep'
*.db_recovery_file_dest='+GROUP1'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=erpoptmXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=1073741824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1147483648
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/bkeep/udump'
~
"initbkeep.ora" [New] 21L, 762C written
可以看到参数文件主要是指定了数据库名、跟踪文件的位置、控制文件的位置、数据块大小、内存参数等。Linux 系统中
该文件用于sys用户以sysdba身份远程管理登录，这里的密码可以和建立数据库脚本理的sys密码不同，如果是远程以sysdba身份登录则需要使用这里的密码。密码文件和参数文件在相同的目录下
[oracle@kk bdump]$ orapwd file=/opt/oracle/product/10.2.0/db_1/dbs/orapwbkeep password=sys entries=5
[oracle@kk bdump]$ id
uid=500(oracle) gid=500(dba) groups=500(dba) context=root:system_r:unconfined_t:SystemLow-SystemHigh
[oracle@kk bdump]$ mkdir -p /opt/oracle/admin/bkeep/adump
[oracle@kk bdump]$ mkdir -p /opt/oracle/admin/bkeep/bdump
[oracle@kk bdump]$ mkdir -p /opt/oracle/admin/bkeep/cdump
[oracle@kk bdump]$ mkdir -p /opt/oracle/admin/bkeep/udump
[oracle@kk bdump]$ export ORACLE_SID=bkeep
[oracle@kk bdump]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 17 13:04:27 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 1219016 bytes
Variable Size 301991480 bytes
Database Buffers 838860800 bytes
Redo Buffers 15556608 bytes
SQL>
[root@kk bdump]# cat alert_bkeep.log
Wed Mar 17 13:04:38 2010
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
processes = 150
sga_target = 1157627904
control_files = +GROUP1/bkeep/control01.ctl, +GROUP1/bkeep/control02.ctl, +GROUP1/bkeep/control03.ctl
db_block_size = 8192
compatible = 10.2.0.1.0
db_file_multiblock_read_count= 16
db_recovery_file_dest = +GROUP1
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
dispatchers = (PROTOCOL=TCP) (SERVICE=erpoptmXDB)
job_queue_processes = 10
background_dump_dest = /opt/oracle/admin/bkeep/bdump
user_dump_dest = /opt/oracle/admin/bkeep/udump
core_dump_dest = /opt/oracle/admin/bkeep/cdump
audit_file_dest = /opt/oracle/admin/bkeep/adump
db_name = bkeep
open_cursors = 300
pga_aggregate_target = 1073741824
PMON started with pid=2, OS id=6161
PSP0 started with pid=3, OS id=6163
MMAN started with pid=4, OS id=6165
DBW0 started with pid=5, OS id=6167
CKPT started with pid=7, OS id=6171
SMON started with pid=8, OS id=6173
RECO started with pid=9, OS id=6175
LGWR started with pid=6, OS id=6169
MMON started with pid=11, OS id=6179
Wed Mar 17 13:04:42 2010
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=6181
CJQ0 started with pid=10, OS id=6177
Wed Mar 17 13:04:42 2010
starting up 1 shared server(s) ...
[oracle@kk bdump]$ vi /home/oracle/build_bkeep.sql
CREATE DATABASE bkeep
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY sys
LOGFILE GROUP 1 ('+GROUP1/bkeep/redo01.log') SIZE 100M,
GROUP 2 ('+GROUP1/bkeep/redo02.log') SIZE 100M,
GROUP 3 ('+GROUP1/bkeep/redo03.log') SIZE 100M
MAXLOGFILES 16
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '+GROUP1/bkeep/system01.dbf' SIZE 2048M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '+GROUP1/bkeep/sysaux01.dbf' SIZE 1024M REUSE
DEFAULT TEMPORARY TABLESPACE temptbs1
TEMPFILE '+GROUP1/bkeep/temp01.dbf'
SIZE 2048M REUSE
UNDO TABLESPACE undotbs1
DATAFILE '+GROUP1/bkeep/undotbs01.dbf'
SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Tips：脚本主要是指定sys和system的密码，redo文件，字符集，数据文件，临时表空间和回滚段等信息。
SQL> @/home/oracle/build_bkeep.sql;
Database created.
SQL> create tablespace erp
2 datafile '+GROUP1/bkeep/erp01.dbf' size 100M
3 autoextend on;
Tablespace created.
SQL> @?/rdbms/admin/catalog.sql
......
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/catproc.sql;
......
PL/SQL procedure successfully completed.
SQL> create spfile='+GROUP1/bkeep/spfilebkeep.ora' from pfile; <==下次启动oracle不会使用这个spfile！！
File created.
SQL> create spfile from pfile; <==不要加参数
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 1219016 bytes
Variable Size 301991480 bytes
Database Buffers 838860800 bytes
Redo Buffers 15556608 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAME TYPE VALUE
--------------- -------------- -----------------------------------------
spfile string /opt/oracle/product/10.2.0/db_ 1/dbs/spfilebkeep.ora
SQL> create user erp
2 identified by erp
3 default tablespace erp
4 temporary tablespace temptbs1
5 pro
User created.
SQL> grant dba to erp;
Grant succeeded.
SQL> conn erp/erp
Error accessing PRODUCT_USER_PRO
Warning: Product user pro
You may need to run PUPBLD.SQL as SYSTEM
Connected.
Tips: 出现这个错误提示并不会影响数据库的使用，手动建库一般会有这个警告出现。 PRODUCT_USER_PRO
SQL> conn system/sys
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql;
Synonym created.
SQL> conn erp/erp
Connected.
[oracle@kk admin]$ vi /opt/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
BKEEP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.254.99)(PORT = 1521))
(CONNECT_DA
(SERVER = DEDICATED)
(SERVICE_NAME = bkeep)
)
)
[oracle@kk admin]$ lsnrctl start
SQL> select group_number,name,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME TOTAL_MB FREE_MB
------------------------ ---------- ---------------- ------------
1 GROUP1 8189 1544
2 GROUP2 8189 8139
SQL> drop diskgroup GROUP2; <==下面会报错，但重启+ASM实例后证实group2被干掉了！
drop diskgroup GROUP2
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
SQL> drop diskgroup GROUP2;
ERROR:
ORA-03114: not connected to ORACLE
SQL> CONN /as sysdba
Connected.
SQL> drop diskgroup GROUP2;
drop diskgroup GROUP2
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> conn /as sysdba
Connected to an idle instance.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> desc v$asm_disk;
ERROR:
ORA-01034: ORACLE not available
SQL> startup
ASM instance started
Total System Global Area 83886080 bytes
Fixed Size 1217836 bytes
Variable Size 57502420 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL> select name,total_mb,free_mb from v$asm_diskgroup; <== 看出来了吗？group2被成功干掉！
NAME TOTAL_MB FREE_MB
---------- --------------- ------------------
GROUP1 8189 1544
SQL> select name,path from v$asm_disk;
NAME PATH
----------------- -------------------------------
ORCL:DISK2 <==呵呵，DISK2只有路径，没有name，说明它不属于任何组了
DISK1 ORCL:DISK1
SQL> alter diskgroup GROUP1 add disk 'ORCL:DISK2' name DISK2;
Diskgroup altered.
SQL> select name,total_mb,free_mb from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB
---------- ---------------- ---------------
GROUP1 6378 9731 <==哈哈，GROUP1的容量变大了！
SQL> select * from v$asm_operation; <==查看正在进行的负载均衡的操作。
GROUP_NUMBER OPERATION STATE POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------------------- ---------------- --------- -------- ----------- -------- -------------- ------------- ----------------
1 REBAL RUN 1 1 1851 3326 1774 0
SQL> alter diskgroup group1 rebalance power 11; <==power 11 使负载均衡操作占有更多的cpu，所以很快就完整了！
Diskgroup altered.
SQL> select * from v$asm_operation; <== 这里就没有负载均衡的动作了！
no rows selected
相关链接：
diskgroup 管理：http://space.itpub.net/?uid-354732-act
http://space.itpub.net/354732/viewspace-627700
ASM: http://bkeep.blog.163.com/blog/static/12341429020102179628659/
手动安装oracle10G 10.2.0.1.0
