Oracle Study之案例--Oracle 11g RAC手工建库案例
在一些特殊情况下无法用DBCA建库，可以考虑在RAC 环境下通过手工方式建库，步骤和单实例基本一致，只是其中有些需要注意的地方。
环境：
操作系统： RedHat EL5
集群软件： CRS 10.2.0.1
数据库软件： Oracle 10.2.0.1
RAC 手工建库：test
一、在rac1 建立口令文件
[oracle@rac1 dbs]$ orapwd file=orapwtest1 password=oracle entries=3 force=y
二、建立pfile 文件
--利用已有的库（prod），来建立
[oracle@rac1 dbs]$sqlplus '/as sysdba' SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 26 20:01:36 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/10.2.0 /db_1/dbs/spfileprod1.ora SQL> create pfile='/home/oracle/prod1.ora' from spfile; File created.
[oracle@rac1 ~]$ cp prod1.ora $ORACLE_HOME/dbs/inittest1.ora
修改inittest1.ora后， 如下： [oracle@rac1 dbs]$ cat inittest1.ora test1.__db_cache_size=46137344 test1.__java_pool_size=4194304 test1.__large_pool_size=4194304 test1.__shared_pool_size=134217728 test1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/test/adump' *.background_dump_dest='/u01/app/oracle/admin/test/bdump' *.cluster_database=false *.compatible='10.2.0.1.0' *.control_files='+DG1/test/controlfile/control01.ctl','+RECOVER/test/controlfile/control02.ctl' *.core_dump_dest='/u01/app/oracle/admin/test/cdump' *.db_block_size=8192 *.db_create_file_dest='+DG1' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='test' *.db_recovery_file_dest='+RECOVER' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)' test1.instance_number=1 *.job_queue_processes=10 test1.log_archive_dest_1='location=/u01/arch' *.log_archive_format='arch_%t_%s_%r.log' *.open_cursors=300 *.pga_aggregate_target=60817408 *.processes=150 *.remote_listener='LISTENERS_PROD' *.remote_login_passwordfile='exclusive' *.sga_max_size=210763776 *.sga_target=189792256 test1.thread=1 *.undo_management='AUTO' test1.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/test/udump'
创建相关目录：
[oracle@rac1 dbs]$mkdir -p /u01/app/oracle/admin/test/cdump [oracle@rac1 dbs]$mkdir -p /u01/app/oracle/admin/test/bdump [oracle@rac1 dbs]$mkdir -p /u01/app/oracle/admin/test/udump [oracle@rac1 dbs]$mkdir -p /u01/app/oracle/admin/test/adump
三、instane 启动到nomount ，建库
-------------将instance启动到nomount 状态建库，建库脚本如下：
CREATE DATABASE test
controlfile reuse
LOGFILE GROUP 1 ('+DG1/TEST/ONLINELOG/redo01a.log') SIZE 10M REUSE,
GROUP 2 ('+DG1/TEST/ONLINELOG/redo02a.log') SIZE 10M REUSE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET zhs16gbk
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '+DG1/TEST/DATAFILE/system01.dbf' SIZE 325M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
SYSAUX DATAFILE '+DG1/TEST/DATAFILE/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '+DG1/TEST/DATAFILE/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;建库日志如下：
[oracle@rac1 ~]$ tail -f /u01/app/oracle/admin/test/bdump/alert_test1.log
CREATE DATABASE test
controlfile reuse
LOGFILE GROUP 1 ('+DG1/TEST/ONLINELOG/redo01a.log') SIZE 10M REUSE,
GROUP 2 ('+DG1/TEST/ONLINELOG/redo02a.log') SIZE 10M REUSE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET zhs16gbk
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '+DG1/TEST/DATAFILE/system01.dbf' SIZE 325M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
SYSAUX DATAFILE '+DG1/TEST/DATAFILE/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '+DG1/TEST/DATAFILE/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Mon Sep 26 20:15:19 2011
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Starting background process ASMB
ASMB started with pid=19, OS id=27922
Starting background process RBAL
RBAL started with pid=20, OS id=27926
Mon Sep 26 20:15:23 2011
SUCCESS: diskgroup DG1 was mounted
SUCCESS: diskgroup DG1 was dismounted
SUCCESS: diskgroup RECOVER was mounted
SUCCESS: diskgroup RECOVER was dismounted
Mon Sep 26 20:15:23 2011
SUCCESS: diskgroup DG1 was mounted
SUCCESS: diskgroup RECOVER was mounted
Mon Sep 26 20:15:23 2011
Database mounted in Exclusive Mode
Mon Sep 26 20:15:23 2011
Successful mount of redo thread 1, with mount id 2060991191
Assigning activation ID 2060991191 (0x7ad83ad7)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: +DG1/test/onlinelog/redo01a.log
Successful open of redo thread 1
Mon Sep 26 20:15:24 2011
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Sep 26 20:15:24 2011
SMON: enabling cache recovery
Mon Sep 26 20:15:24 2011
create tablespace SYSTEM datafile '+DG1/TEST/DATAFILE/system01.dbf' SIZE 325M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online
Mon Sep 26 20:15:29 2011
Completed: create tablespace SYSTEM datafile '+DG1/TEST/DATAFILE/system01.dbf' SIZE 325M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online
Mon Sep 26 20:15:29 2011
create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Mon Sep 26 20:15:36 2011
Thread 1 advanced to log sequence 2
Current log# 2 seq# 2 mem# 0: +DG1/test/onlinelog/redo02a.log
Mon Sep 26 20:15:36 2011
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '+DG1/TEST/DATAFILE/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Mon Sep 26 20:15:41 2011
Successfully onlined Undo Tablespace 1.
Completed: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '+DG1/TEST/DATAFILE/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Mon Sep 26 20:15:41 2011
create tablespace SYSAUX datafile '+DG1/TEST/DATAFILE/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online
Completed: create tablespace SYSAUX datafile '+DG1/TEST/DATAFILE/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO online
Mon Sep 26 20:15:44 2011
ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Mon Sep 26 20:15:47 2011
SMON: enabling tx recovery
Mon Sep 26 20:15:48 2011
Threshold validation cannot be done before catproc is loaded.
Threshold validation cannot be done before catproc is loaded.
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=23, OS id=28488
Mon Sep 26 20:15:49 2011
Completed: CREATE DATABASE test
controlfile reuse
LOGFILE GROUP 1 ('+DG1/TEST/ONLINELOG/redo01a.log') SIZE 10M REUSE,
GROUP 2 ('+DG1/TEST/ONLINELOG/redo02a.log') SIZE 10M REUSE
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET zhs16gbk
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '+DG1/TEST/DATAFILE/system01.dbf' SIZE 325M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
SYSAUX DATAFILE '+DG1/TEST/DATAFILE/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '+DG1/TEST/DATAFILE/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
Mon Sep 26 20:15:49 2011
db_recovery_file_dest_size of 2048 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.四、添加tablespace
------------建库完毕查看，建立其余表空间
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DG1/test/datafile/system01.dbf +DG1/test/datafile/undotbs01.dbf +DG1/test/datafile/sysaux01.dbf SQL> select name from v$tempfile; no rows selected SQL> create temporary tablespace temp 2 tempfile '+DG1'; Tablespace created. SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- +DG1/test/tempfile/temp.281.762898635 SQL> create tablespace users 2 datafile '+DG1'; Tablespace created. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DG1/test/datafile/system01.dbf +DG1/test/datafile/undotbs01.dbf +DG1/test/datafile/sysaux01.dbf +DG1/test/datafile/users.282.762898685 SQL> ALTER database default tablespace users; Database altered. SQL> alter database default temporary tablespace temp; Database altered. SQL> SQL> create undo tablespace undotbs2 2 datafile '+DG1' size 100m reuse 3 autoextend on maxsize unlimited 4 extent management local;
五、建立数据字典
Tablespace created.
--运行以下数据字典的脚本
[oracle@rac1 dbs]$more cr_dict.sql
@?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql @?/rdbms/admin/catblock.sql @?/rdbms/admin/catexp.sql @?/rdbms/admin/catexp7.sql @?/rdbms/admin/catoctk.sql @?/rdbms/admin/owminst.plb @?/rdbms/admin/catclust.sql @?/rdbms/admin/utlrp.sql
六、创建spfile
---创建spfile
SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string SQL> create spfile='+DG1/test/spfile/spfileprod.ora' from pfile; create spfile='+DG1/test/spfile/spfileprod.ora' from pfile * ERROR at line 1: ORA-01078: failure in processing system parameters ORA-17502: ksfdcre:4 Failed to create file +DG1/test/spfile/spfileprod.ora ORA-15173: entry 'spfile' does not exist in directory 'test'
---首先在ASM下建立相关的目录
[oracle@rac1 dbs]$ export ORACLE_SID=+ASM1 [oracle@rac1 dbs]$ asmcmd ASMCMD> ls DG1/ RECOVER/ ASMCMD> cd DG1 ASMCMD> ls PROD/ TEST/ ASMCMD> cd TEST ASMCMD> ls CONTROLFILE/ DATAFILE/ ONLINELOG/ TEMPFILE/ ASMCMD> help asmcmd [-p] [command] The environment variables ORACLE_HOME and ORACLE_SID determine the instance to which the program connects, and ASMCMD establishes a bequeath connection to it, in the same manner as a SQLPLUS / AS SYSDBA. The user must be a member of the SYSDBA group. Specifying the -p option allows the current directory to be displayed in the command prompt, like so: ASMCMD [+DATAFILE/ORCL/CONTROLFILE] > [command] specifies one of the following commands, along with its parameters. Type "help [command]" to get help on a specific ASMCMD command. commands: -------- cd du find help ls lsct lsdg mkalias mkdir pwd rm rmalias ASMCMD> mkdir SPFILE [oracle@rac1 dbs]$ export ORACLE_SID=test1 [oracle@rac1 dbs]$ sqlplus '/as sysdba' SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 26 20:39:53 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> create spfile='+DG1/TEST/SPFILE/spfiletest.ora' from pfile; File created. SQL> startup force ORACLE instance started. Total System Global Area 213909504 bytes Fixed Size 1218604 bytes Variable Size 163579860 bytes Database Buffers 46137344 bytes Redo Buffers 2973696 bytes Database mounted.
---修改pfile 连接到spfile
[oracle@rac1 dbs]$ vi inittest1.ora
*.cluster_database=FALSE test1.log_archive_dest_1='location=/u01/arch' *.log_archive_format='arch_%t_%s_%r.log' *.SPFILE='+DG1/test/spfile/spfiletest.ora' ~
SQL> startup force ORACLE instance started. Total System Global Area 213909504 bytes Fixed Size 1218604 bytes Variable Size 163579860 bytes Database Buffers 46137344 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DG1/test/spfile/spfiletest.ora
七、修改相应的rac参数
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
SQL> alter system set cluster_database=true scope=spfile;
System altered.
SQL> alter system set cluster_database_instances=2 scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 213909504 bytes
Fixed Size 1218604 bytes
Variable Size 163579860 bytes
Database Buffers 46137344 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
---添加redo 日志给instance 2
SQL> alter database add logfile thread 2
2 group 4 ('+DG1/test/onlinelog/redo04a.log') size 10m;
Database altered.
SQL> alter database add logfile thread 2
2 group 5 ('+DG1/test/onlinelog/redo05a.log') size 10m;
Database altered.
SQL> alter system set undo_tablespace=undotbs2 sid='test2';
System altered.
SQL> alter system set thread=2 sid='test2';
System altered.八、在第二个节点上，启动instance
1）创建口令文件 [oracle@rac2 dbs]$ orapwd file=orapwtest2 password=oracle entries=3 force=y SQL> SQL> startup ORA-29760: instance_number parameter not specified ---启动instance 失败 2）在节点1 上，添加如下信息 SQL> alter system set instance_number=1 scope=spfile sid='test1'; System altered. SQL> alter system set instance_number=2 scope=spfile sid='test2'; System altered. 3）节点2 redo不可用 ----节点2 ，instance 2启动失败 SQL> startup force ORACLE instance started. Total System Global Area 213909504 bytes Fixed Size 1218604 bytes Variable Size 109053908 bytes Database Buffers 100663296 bytes Redo Buffers 2973696 bytes ORA-01618: redo thread 2 is not enabled - cannot mount ---在节点1 ，如下操作 SQL> startup force ORACLE instance started. Total System Global Area 213909504 bytes Fixed Size 1218604 bytes Variable Size 163579860 bytes Database Buffers 46137344 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 53 10485760 1 NO INACTIVE 273378 26-SEP-11 2 1 54 10485760 1 NO CURRENT 273381 26-SEP-11 4 2 0 10485760 1 YES UNUSED 0 5 2 0 10485760 1 YES UNUSED 0 ---激活thread 2 redo SQL> alter database enable thread 2; Database altered. SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 53 10485760 1 NO INACTIVE 273378 26-SEP-11 2 1 54 10485760 1 NO CURRENT 273381 26-SEP-11 4 2 1 10485760 1 NO CURRENT 273410 26-SEP-11 5 2 0 10485760 1 YES UNUSED 0 ---启动节点2 instance 成功 SQL> startup force ORACLE instance started. Total System Global Area 213909504 bytes Fixed Size 1218604 bytes Variable Size 109053908 bytes Database Buffers 100663296 bytes Redo Buffers 2973696 bytes Database mounted. Database opened. SQL> select instance_name from gv$instance; INSTANCE_NAME ---------------- test2 test1
九、注册database、instance，并验证
[oracle@rac1 dbs]$ srvctl add database -d test -o $ORACLE_HOME [oracle@rac1 dbs]$ srvctl add instance -d test -i test1 -n rac1 [oracle@rac1 dbs]$ srvctl add instance -d test -i test2 -n rac2 [oracle@rac1 dbs]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.prod.db application ONLINE ONLINE rac2 ora....d1.inst application ONLINE ONLINE rac1 ora....d2.inst application ONLINE ONLINE rac2 ora....SM1.asm application OFFLINE OFFLINE ora....C1.lsnr application ONLINE ONLINE rac1 ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2 ora.test.db application OFFLINE OFFLINE ora....t1.inst application OFFLINE OFFLINE ora....t2.inst application OFFLINE OFFLINE [oracle@rac1 dbs]$ crs_stat|grep asm NAME=ora.rac1.ASM1.asm NAME=ora.rac2.ASM2.asm [oracle@rac1 dbs]$ crs_start -f ora.rac1.ASM1.asm Attempting to start `ora.rac1.ASM1.asm` on member `rac1` Start of `ora.rac1.ASM1.asm` on member `rac1` succeeded. [oracle@rac1 dbs]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.prod.db application ONLINE ONLINE rac2 ora....d1.inst application ONLINE ONLINE rac1 ora....d2.inst application ONLINE ONLINE rac2 ora....SM1.asm application ONLINE ONLINE rac1 ora....C1.lsnr application ONLINE ONLINE rac1 ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2 ora.test.db application OFFLINE OFFLINE ora....t1.inst application OFFLINE OFFLINE ora....t2.inst application OFFLINE OFFLINE [oracle@rac1 dbs]$ crs_stat|grep inst NAME=ora.prod.prod1.inst NAME=ora.prod.prod2.inst NAME=ora.test.test1.inst NAME=ora.test.test2.inst [oracle@rac1 dbs]$ crs_stat|grep db NAME=ora.prod.db NAME=ora.test.db [oracle@rac1 dbs]$ crs_start -f ora.test.test1.inst Attempting to start `ora.test.test1.inst` on member `rac1` Start of `ora.test.test1.inst` on member `rac1` succeeded. [oracle@rac1 dbs]$ crs_start -f ora.test.test2.inst Attempting to start `ora.test.test2.inst` on member `rac2` Start of `ora.test.test2.inst` on member `rac2` succeeded. [oracle@rac1 dbs]$ crs_start -f ora.test.db Attempting to start `ora.test.db` on member `rac1` Start of `ora.test.db` on member `rac1` succeeded. [oracle@rac1 dbs]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.prod.db application ONLINE ONLINE rac2 ora....d1.inst application ONLINE ONLINE rac1 ora....d2.inst application ONLINE ONLINE rac2 ora....SM1.asm application ONLINE ONLINE rac1 ora....C1.lsnr application ONLINE ONLINE rac1 ora.rac1.gsd application ONLINE ONLINE rac1 ora.rac1.ons application ONLINE ONLINE rac1 ora.rac1.vip application ONLINE ONLINE rac1 ora....SM2.asm application ONLINE ONLINE rac2 ora....C2.lsnr application ONLINE ONLINE rac2 ora.rac2.gsd application ONLINE ONLINE rac2 ora.rac2.ons application ONLINE ONLINE rac2 ora.rac2.vip application ONLINE ONLINE rac2 ora.test.db application ONLINE ONLINE rac1 ora....t1.inst application ONLINE ONLINE rac1 ora....t2.inst application ONLINE ONLINE rac2 [oracle@rac1 dbs]$
@ 至此，数据库建立完成！
