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=46137344test1.__java_pool_size=4194304test1.__large_pool_size=4194304test1.__shared_pool_size=134217728test1.__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=10test1.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=189792256test1.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 testcontrolfile reuseLOGFILE GROUP 1 ('+DG1/TEST/ONLINELOG/redo01a.log') SIZE 10M REUSE,GROUP 2 ('+DG1/TEST/ONLINELOG/redo02a.log') SIZE 10M REUSEMAXLOGFILES 5MAXLOGMEMBERS 5MAXLOGHISTORY 1MAXDATAFILES 100MAXINSTANCES 1CHARACTER SET zhs16gbkNATIONAL CHARACTER SET AL16UTF16DATAFILE '+DG1/TEST/DATAFILE/system01.dbf' SIZE 325M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITEDSYSAUX DATAFILE '+DG1/TEST/DATAFILE/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITEDUNDO TABLESPACE undotbs1DATAFILE '+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 testcontrolfile reuseLOGFILE GROUP 1 ('+DG1/TEST/ONLINELOG/redo01a.log') SIZE 10M REUSE,GROUP 2 ('+DG1/TEST/ONLINELOG/redo02a.log') SIZE 10M REUSEMAXLOGFILES 5MAXLOGMEMBERS 5MAXLOGHISTORY 1MAXDATAFILES 100MAXINSTANCES 1CHARACTER SET zhs16gbkNATIONAL CHARACTER SET AL16UTF16DATAFILE '+DG1/TEST/DATAFILE/system01.dbf' SIZE 325M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITEDSYSAUX DATAFILE '+DG1/TEST/DATAFILE/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITEDUNDO TABLESPACE undotbs1DATAFILE '+DG1/TEST/DATAFILE/undotbs01.dbf'SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDMon Sep 26 20:15:19 2011WARNING: Default Temporary Tablespace not specified in CREATE DATABASE commandDefault Temporary Tablespace will be necessary for a locally managed database in future releaseStarting background process ASMBASMB started with pid=19, OS id=27922Starting background process RBALRBAL started with pid=20, OS id=27926Mon Sep 26 20:15:23 2011SUCCESS: diskgroup DG1 was mountedSUCCESS: diskgroup DG1 was dismountedSUCCESS: diskgroup RECOVER was mountedSUCCESS: diskgroup RECOVER was dismountedMon Sep 26 20:15:23 2011SUCCESS: diskgroup DG1 was mountedSUCCESS: diskgroup RECOVER was mountedMon Sep 26 20:15:23 2011Database mounted in Exclusive ModeMon Sep 26 20:15:23 2011Successful mount of redo thread 1, with mount id 2060991191Assigning activation ID 2060991191 (0x7ad83ad7)Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: +DG1/test/onlinelog/redo01a.logSuccessful open of redo thread 1Mon Sep 26 20:15:24 2011MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setMon Sep 26 20:15:24 2011SMON: enabling cache recoveryMon Sep 26 20:15:24 2011create 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 onlineMon Sep 26 20:15:29 2011Completed: 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 onlineMon Sep 26 20:15:29 2011create 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 2011Thread 1 advanced to log sequence 2 Current log# 2 seq# 2 mem# 0: +DG1/test/onlinelog/redo02a.logMon Sep 26 20:15:36 2011CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '+DG1/TEST/DATAFILE/undotbs01.dbf'SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDMon Sep 26 20:15:41 2011Successfully onlined Undo Tablespace 1.Completed: CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '+DG1/TEST/DATAFILE/undotbs01.dbf'SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDMon Sep 26 20:15:41 2011create tablespace SYSAUX datafile '+DG1/TEST/DATAFILE/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO onlineCompleted: create tablespace SYSAUX datafile '+DG1/TEST/DATAFILE/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO onlineMon Sep 26 20:15:44 2011ALTER DATABASE DEFAULT TABLESPACE SYSTEM Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM Mon Sep 26 20:15:47 2011SMON: enabling tx recoveryMon Sep 26 20:15:48 2011Threshold 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 QMNCQMNC started with pid=23, OS id=28488Mon Sep 26 20:15:49 2011Completed: CREATE DATABASE testcontrolfile reuseLOGFILE GROUP 1 ('+DG1/TEST/ONLINELOG/redo01a.log') SIZE 10M REUSE,GROUP 2 ('+DG1/TEST/ONLINELOG/redo02a.log') SIZE 10M REUSEMAXLOGFILES 5MAXLOGMEMBERS 5MAXLOGHISTORY 1MAXDATAFILES 100MAXINSTANCES 1CHARACTER SET zhs16gbkNATIONAL CHARACTER SET AL16UTF16DATAFILE '+DG1/TEST/DATAFILE/system01.dbf' SIZE 325M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITEDSYSAUX DATAFILE '+DG1/TEST/DATAFILE/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITEDUNDO TABLESPACE undotbs1DATAFILE '+DG1/TEST/DATAFILE/undotbs01.dbf'SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITEDMon Sep 26 20:15:49 2011db_recovery_file_dest_size of 2048 MB is 0.00% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace 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.dbfSQL> select name from v$tempfile;no rows selectedSQL> create temporary tablespace temp 2 tempfile '+DG1';Tablespace created.SQL> select name from v$tempfile;NAME--------------------------------------------------------------------------------+DG1/test/tempfile/temp.281.762898635SQL> 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.762898685SQL> 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 stringSQL> 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 parametersORA-17502: ksfdcre:4 Failed to create file +DG1/test/spfile/spfileprod.oraORA-15173: entry 'spfile' does not exist in directory 'test'
---首先在ASM下建立相关的目录
[oracle@rac1 dbs]$ export ORACLE_SID=+ASM1[oracle@rac1 dbs]$ asmcmdASMCMD> lsDG1/RECOVER/ASMCMD> cd DG1ASMCMD> lsPROD/TEST/ASMCMD> cd TESTASMCMD> lsCONTROLFILE/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 rmaliasASMCMD> 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 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining optionsSQL> create spfile='+DG1/TEST/SPFILE/spfiletest.ora' from pfile;File created.SQL> startup forceORACLE instance started.Total System Global Area 213909504 bytesFixed Size 1218604 bytesVariable Size 163579860 bytesDatabase Buffers 46137344 bytesRedo Buffers 2973696 bytesDatabase mounted.
---修改pfile 连接到spfile
[oracle@rac1 dbs]$ vi inittest1.ora
*.cluster_database=FALSEtest1.log_archive_dest_1='location=/u01/arch'*.log_archive_format='arch_%t_%s_%r.log'*.SPFILE='+DG1/test/spfile/spfiletest.ora'~
SQL> startup forceORACLE instance started.Total System Global Area 213909504 bytesFixed Size 1218604 bytesVariable Size 163579860 bytesDatabase Buffers 46137344 bytesRedo Buffers 2973696 bytesDatabase mounted.Database opened.SQL> show parameter spfileNAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string +DG1/test/spfile/spfiletest.ora
七、修改相应的rac参数
SQL> show parameter clusterNAME TYPE VALUE------------------------------------ ----------- ------------------------------cluster_database boolean FALSEcluster_database_instances integer 1cluster_interconnects stringSQL> 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 bytesFixed Size 1218604 bytesVariable Size 163579860 bytesDatabase Buffers 46137344 bytesRedo Buffers 2973696 bytesDatabase mounted.Database opened.---添加redo 日志给instance 2SQL> 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=ySQL> SQL> startupORA-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 forceORACLE instance started.Total System Global Area 213909504 bytesFixed Size 1218604 bytesVariable Size 109053908 bytesDatabase Buffers 100663296 bytesRedo Buffers 2973696 bytesORA-01618: redo thread 2 is not enabled - cannot mount---在节点1 ，如下操作SQL> startup forceORACLE instance started.Total System Global Area 213909504 bytesFixed Size 1218604 bytesVariable Size 163579860 bytesDatabase Buffers 46137344 bytesRedo Buffers 2973696 bytesDatabase 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 redoSQL> 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 forceORACLE instance started.Total System Global Area 213909504 bytesFixed Size 1218604 bytesVariable Size 109053908 bytesDatabase Buffers 100663296 bytesRedo Buffers 2973696 bytesDatabase mounted.Database opened.SQL> select instance_name from gv$instance;INSTANCE_NAME----------------test2test1
九、注册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 -tName 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 asmNAME=ora.rac1.ASM1.asmNAME=ora.rac2.ASM2.asm[oracle@rac1 dbs]$ crs_start -f ora.rac1.ASM1.asmAttempting to start `ora.rac1.ASM1.asm` on member `rac1`Start of `ora.rac1.ASM1.asm` on member `rac1` succeeded.[oracle@rac1 dbs]$ crs_stat -tName 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 instNAME=ora.prod.prod1.instNAME=ora.prod.prod2.instNAME=ora.test.test1.instNAME=ora.test.test2.inst[oracle@rac1 dbs]$ crs_stat|grep dbNAME=ora.prod.dbNAME=ora.test.db[oracle@rac1 dbs]$ crs_start -f ora.test.test1.instAttempting 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.instAttempting 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.dbAttempting to start `ora.test.db` on member `rac1`Start of `ora.test.db` on member `rac1` succeeded.[oracle@rac1 dbs]$ crs_stat -tName 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]$
@ 至此，数据库建立完成！
本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1365001