0,给电脑增加2块SCSI硬盘1
1,安装ASM1
2,创建两个ASM磁盘组,分别为:GROUP1、GROUP22
下面是生产的日志!3
3,开始创建数据库,首先建立参数文件,保存为$ORACLE_HOME/dbs/initbkeep.ora4
4,建立密码文件$ORACLE_HOME/dbs/orapwdbkeep5
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以外的另一种方法来创建数据库,先前做过;数据版本是10g10.2.0.1.0搭配了ASM。
,给电脑增加2块SCSI硬盘
,安装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 one of these three values:
#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 - Only targeted network daemons are protected.
#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 without typing an
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)
,创建两个ASM磁盘组,分别为:GROUP1、GROUP2
[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 BootStartEndBlocksIdSystem
/dev/sda1*12520078183Linux
/dev/sda2262862096482+82Linux swap / Solaris
/dev/sda3287522139640387+83Linux
Disk /dev/sdb: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device BootStartEndBlocksIdSystem
/dev/sdb1110448385898+83Linux
Disk /dev/sdc: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device BootStartEndBlocksIdSystem
/dev/sdc1110448385898+83Linux
[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;
NAMEPATHGROUP_NUMBER
-----------------------------------------------------------------
DISK1ORCL:DISK11
DISK2ORCL:DISK22
开始创建数据库,首先建立参数文件,保存为$ORACLE_HOME/dbs/initbkeep.ora
[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系统中
,建立密码文件$ORACLE_HOME/dbs/orapwdbkeep
该文件用于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
,启动数据库到nomount状态
[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 Size1219016 bytes
Variable Size301991480 bytes
Database Buffers838860800 bytes
Redo Buffers15556608 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.
,创建erp表空间
SQL> create tablespace erp
2datafile '+GROUP1/bkeep/erp01.dbf' size 100M
3autoextend on;
Tablespace created.
,创建数据字典视图
SQL> @?/rdbms/admin/catalog.sql
......
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/catproc.sql;
......
PL/SQL procedure successfully completed.
,建立spfile
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 Size1219016 bytes
Variable Size301991480 bytes
Database Buffers838860800 bytes
Redo Buffers15556608 bytes
Database mounted.
Database opened.
SQL> show parameter spfile;
NAMETYPEVALUE
----------------------------------------------------------------------
spfilestring/opt/oracle/product/10.2.0/db_ 1/dbs/spfilebkeep.ora
,创建数据库用户
SQL> create user erp
2identified by erp
3default tablespace erp
4temporary tablespace temptbs1
5profile DEFAULT;
User created.
SQL> grant dba to erp;
Grant succeeded.
SQL> conn erp/erp
Error accessing PRODUCT_USER_PROFILE
Warning:Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
Tips:出现这个错误提示并不会影响数据库的使用,手动建库一般会有这个警告出现。PRODUCT_USER_PROFILE是SYSTEM用户的一个表,存储客户端程序执行命令方面的限制信息,可以根据提示用system用户执行脚本来消除:
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_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bkeep)
)
)
[oracle@kk admin]$ lsnrctl start
,扩展
查看ASM groups组号、组名、总大小、空闲空间
SQL> select group_number,name,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER NAMETOTAL_MBFREE_MB
--------------------------------------------------------------
1GROUP181891544
2GROUP281898139
删除group2
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 Area83886080 bytes
Fixed Size1217836 bytes
Variable Size57502420 bytes
ASM Cache25165824 bytes
ASM diskgroups mounted
SQL> select name,total_mb,free_mb from v$asm_diskgroup;<==看出来了吗?group2被成功干掉!
NAMETOTAL_MBFREE_MB
-------------------------------------------
GROUP181891544
SQL> select name,pathfrom v$asm_disk;
NAMEPATH
------------------------------------------------
ORCL:DISK2<==呵呵,DISK2只有路径,没有name,说明它不属于任何组了
DISK1ORCL:DISK1
把ORCL:DISK2添加到GROUP1中去
SQL> alter diskgroup GROUP1 add disk'ORCL:DISK2' nameDISK2;
Diskgroup altered.
SQL> select name,total_mb,free_mb from v$asm_diskgroup;
NAMETOTAL_MBFREE_MB
-----------------------------------------
GROUP16378 9731<==哈哈,GROUP1的容量变大了!
查看负载信息
SQL> select * from v$asm_operation;<==查看正在进行的负载均衡的操作。
GROUP_NUMBER OPERATIONSTATEPOWERACTUALSOFAR EST_WORKEST_RATE EST_MINUTES
------------------------------------------------------------------------------------------------------------------------
1 REBALRUN111851332617740
重新负载均衡
SQL> alter diskgroup group1 rebalance power 11;<==power 11使负载均衡操作占有更多的cpu,所以很快就完整了!
Diskgroup altered.
SQL> select * from v$asm_operation;<==这里就没有负载均衡的动作了!
no rows selected
相关链接: