Oracle11g Rac创建数据库语句及其细节.doc

登录数据库 sqlplus username/passwordORACLE_SID(环境变量) 数据库对应的实例的名字，该名字决定了连接哪个具体的数据库show user 查看当前用户desc tablename 查看表结构 desc describe的缩写查询员工的姓名，和工资select first_name,salary from s_emp;查询员工的名字和职位select first_name,title from s_emp;edit 修改sql语句 l 查看 /运行select * from s_dept; 列出部门表的所有信息列出每个员工的年薪select first_name,salary*12 from s_emp;列出每个员工的总收入select first_name,salary*12*(1+commission_pct/100) tol sal from s_emp;空值会导致算术表达式为空，Oracle认为null为无穷大select first_name,salary*12*(1+nvl(commission_pct,0)/100) tol_sal,Commission_pctfrom s_empnvl(p1,p2)if(p1 is null) then return p2;else return p1;coalesce 和nvl实现的是同样的功能，nvl只能用在Oracle，coalesce可以用在多种数据库,nvl的两个参数的类型必须一致继续试验：oracleoel501 $ orapw file=$ORACLE_HOME/dbs/orapwRAC password=zzzz entries=5 force=yoracleoel501 $ cat init.ora |grep -v # initRAC1.ora;oracleoel501 $ vi initRAC1.ora*.compatible=11.2.0*.control_files=+DATA/RAC/CONTROLFILE/control00.ctl*.db_block_size=8192*.db_name=RAC*.open_cursors=300*.pga_aggregate_target=120M*.processes=150*.remote_login_passwordfile=EXCLUSIVE*.sga_target=256Moracleoel501 $ sqlplus / as sysdbaSQL startup nomount;SQL create spfile=+DATA/RAC/SPFILE/spfileRAC.ora from pfile;SQL shutdown abort;oracleoel501 $ vi initRAC1.oraspfile=+DATA/RAC/SPFILE/spfileRAC.oraoracleoel501 $ sqlplus / as sysdbaSQL startup nomountSQL CREATE DATABASE RAC 2 USER SYS IDENTIFIED BY zzzz 3 USER SYSTEM IDENTIFIED BY zzzz 4 LOGFILE 5 GROUP 1 (+DATA/RAC/ONLINELOG/redo1_01.rdo) SIZE 25M BLOCKSIZE 512, 6 GROUP 2 (+DATA/RAC/ONLINELOG/redo1_02.rdo) SIZE 25M BLOCKSIZE 512 7 MAXLOGFILES 5 8 MAXLOGMEMBERS 5 9 MAXLOGHISTORY 110 MAXDATAFILES 10011 CHARACTER SET AL32UTF812 NATIONAL CHARACTER SET AL16UTF1613 EXTENT MANAGEMENT LOCAL14 DATAFILE +DATA/RAC/DATAFILE/system00.dbf SIZE 700M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED15 SYSAUX DATAFILE +DATA/RAC/DATAFILE/sysaux00.dbf SIZE 700M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED16 DEFAULT TEMPORARY TABLESPACE tempts117 TEMPFILE +DATA/RAC/TEMPFILE/temp01.dbf SIZE 20M REUSE18 UNDO TABLESPACE undotbs119 DATAFILE +DATA/RAC/DATAFILE/undotbs1_01.dbf20 SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;Database created.oracleoel501 $ srvctl add database -d RAC -o /opt/oracle/app/oracle/product/11.2.0/dbhome_1 -c RAC -p +DATA/RAC/spfile/spfileRAC.oraoracleoel501 $ srvctl add instance -d RAC -i RAC1 -n oel501oracleoel501 $ srvctl add instance -d RAC -i RAC2 -n oel502gridoel501 $ srvctlstop instance -d RAC -n oel501gridoel501 $ srvctl start instance -d RAC -n oel501gridoel501 $ srvctl stop instance -d RAC -n oel502gridoel501 $ srvctl start instance -d RAC -n oel502PRCR-1013 : Failed to start resource ora.rac.dbPRCR-1064 : Failed to start resource ora.rac.db on node oel502CRS-2546: Server oel502 is not onlineoracleoel501 $ sqlplus / as sysdbaSQL ?/rdbms/admin/catalog SQL ?/rdbms/admin/catproc.等一段时间SQL alter system set undo_tablespace=undotbs1 scope=spfile sid=RAC1;SQL alter system set undo_tablespace=undotbs2 scope=spfile sid=RAC2;SQL alter database add logfile instance RAC2 group 3 +DATA/rac/onlinelog/redo2_01.rdo size 25MSQL alter database add logfile instance RAC2 group 4 +DATA/rac/onlinelog/redo2_02.rdo size 25MSQL alter database enable thread 2;SQL shutdown immediate;rootoel502 #/opt/grid/app//grid/crs/install/roothas.pl -verbose -deconfig -force rootoel502 # /opt/grid/app//grid/root.shoracleoel502 $ sqlplus / as sysdbaSQL startup.Database opened.gridoel501 $ srvctl start instance -d rac -n oel501PRCR-1013 : Failed to start resource ora.rac.dbPRCR-1064 : Failed to start resource ora.rac.db on node oel501CRS-5017: The resource action ora.rac.db start encountered the following error: ORA-01034: ORACLE not availableORA-27101: shared memory realm does not existLinux-x86_64 Error: 2: No such file or directoryProcess ID: 0Session ID: 0 Serial number: 0. For details refer to (:CLSN00107:) in /opt/grid/app/11.2.0/grid/log/oel501/agent/crsd/oraagent_oracle/oraagent_oracle.log.CRS-2674: Start of ora.rac.db on oel501 failed$DIAG_INFO/rdbms/trace/alertlog: USER (ospid: 22841): terminating the instance due to error 304gridoel501 $ oerr ora 30400304, 00000, requested INSTANCE_NUMBER is busy/ *Cause: An instance tried to start by using a value of the/ initialization parameter INSTANCE_NUMBER that is already in use./ *Action: Either/ a) specify another INSTANCE_NUMBER,/ b) shut down the running instance with this number/ c) wait for instance recovery to complete on the instance with/ this number.oracleoel502 $ sqlplus / as sysdbaSQL show parameter instance_numberNAME TYPE VALUE- - -instance_number integer 0SQL alter system set instance_number=2 scope=spfile sid=RAC2;SQL shutdown immediateoracleoel501 $ sqlplus / as sysdbaSQL startup nomountSQL show parameter instance_numberNAME TYPE VALUE- - -instance_number integer 0SQL alter system set instance_number=1 scope=spfile sid=RAC1;SQL shutdown immediategridoel502 $ srvctl start instance -d rac -n oel502PRCR-1013 : Failed to start resource ora.rac.dbPRCR-1064 : Failed to start resource ora.rac.db on node oel502CRS-5017: The resource action ora.rac.db start encountered the following error: ORA-01102: cannot mount database in EXCLUSIVE mode. For details refer to (:CLSN00107:) in /opt/grid/app/11.2.0/grid/log/oel502/agent/crsd/oraagent_oracle/oraagent_oracle.log.CRS-2674: Start of ora.rac.db on oel502 failed$DIAG_INFO/rdbms/trace/alertlog: USER (ospid: 8609): terminating the instance due to error 1102gridoel502 $ oerr o