Oracle 11gR2 创建数据库实例.doc

Oracle 11gR2 创建数据库实例因为工作需要在Oracle11gR2库中新建一数据库实例。采用脚本命令创建，建议使用oracle用户进行以下操作。顺序如下：1.创建实例启动用的参数文件。在程序默认的参数文件里修改即可：重命名规则 initSID.ora/u01/oracle/product/11.2.0/db_1/dbs/ 下，目录尽量使用绝对路径，采用ORACLE_BASE等环境变量有可能报错。例 initcrm.oradb_name=crm -修改#memory_target=500m -注释掉sga_target=200m -添加pga_aggregate_target=60m -添加processes = 150audit_file_dest=/u01/oracle/admin/crm/adump -建立目录audit_trail =dbdb_block_size=8192db_domain=db_recovery_file_dest=/u01/oracle/flash_recovery_area -建立目录db_recovery_file_dest_size=2Gdiagnostic_dest=/u01/oracle -修改dispatchers=(PROTOCOL=TCP) (SERVICE=ORCLXDB)open_cursors=300remote_login_passwordfile=EXCLUSIVEundo_tablespace=UNDOTBS12.创建相应目录：mkdir -p /u01/oracle/admin/ora11g/adumpmkdir -p /u01/oracle/admin/ora11g/dpdumpmkdir -p /u01/oracle/flash_recovery_area3.创建密码文件：命名规则 orapwSID，目录依旧是/u01/oracle/product/11.2.0/db_1/dbs/Linux下orapw+实例名Windows下pwd+实例名$ orapwd file=orapwora11g password=oracle4.创建数据库实例设置环境变量 export ORACLE_SID=crm执行以下命令sqlplus /nologconn / as sysdba注意：本处可能报错需要可能的原因： a）glibc-devel-2.5-58.el5_6.4、libaio-0.3.106-5包是否已安装，命令分别为rpm-qglibc-devel、rpm-qlibaio b）oracle目录权限不足或属组不对，chmod6751oracle 授权、chown-Roracle.oinstall/u01/oracle 修改属组执行建库脚本：startup nomount pfile=/u01/oracle/product/11.2.0.1/db1/dbs/initcrm.ora;CREATE DATABASE crmMAXINSTANCES 8MAXLOGHISTORY 1MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100DATAFILE /u01/oracle/oradata/system01.dbf SIZE 1024M REUSEEXTENT MANAGEMENT LOCALSYSAUX DATAFILE /u01/oracle/oradata/sysaux01.dbf SIZE 500M REUSESMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE /u01/oracle/oradata/temp01.dbf SIZE 20M REUSESMALLFILE UNDO TABLESPACE UNDOTBS1 DATAFILE /u01/oracle/oradata/undo01.dbf SIZE 300M REUSECHARACTER SET ZHS16GBKNATIONAL CHARACTER SET AL16UTF16LOGFILE GROUP 1 (/u01/oracle/oradata/log01.dbf) SIZE 50M,GROUP 2 (/u01/oracle/oradata/log02.dbf) SIZE 50M,GROUP 3 (/u01/oracle/oradata/log03.dbf) SIZE 50M;创建USERS表空间CREATE SMALLFILE TABLESPACE USERS LOGGING DATAFILE /u01/app/oradata/user01.dbf SIZE 1000M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACEMANAGEMENT AUTO;ALTER DATABASE DEFAULT TABLESPACE USERS;-SQL create spfile from pfile;File created.-sqlplus / as sysdbaSQL show user;/u01/oracle/product/11.2.0.1/db1/rdbms/admin/catalog.sql;/u01/oracle/product/11.2.0.1/db1/rdbms/admin/catblock.sql;/u01/oracle/product/11.2.0.1/db1/rdbms/admin/catproc.sql;/u01/oracle/product/11.2.0.1/db1/rdbms/admin/catoctk.sql;/u01/oracle/product/11.2.0.1/db1/rdbms/admin/owminst.plb;使用system用户编译/u01/oracle/product/11.2.0.1/db1/sqlplus/admin/pupbld.sql;/u01/oracle/product/11.2.0.1/db1/sqlplus/admin/help/hlpbld.sql helpus.sql;使用sys用户编译/u01/oracle/product/11.2.0.1/db1/javavm/install/initjvm.sql;/u01/oracle/product/11.2.0.1/db1/xdk/admin/initxml.sql;/u01/oracle/product/11.2.0.1/db1/xdk/admin/xmlja.sql;/u01/oracle/product/11.2.0.1/db1/rdbms/admin/catjava.sql;/u01/oracle/product/11.2.0.1/db1/rdbms/admin/catexf.sql;/u01/oracle/product/11.2.0.1/db1/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP YES;/u01/oracle/product/11.2.0.1/db1/rdbms/admin/catxdbj.sql;/u01/oracle/product/11.2.0.1/db1/rdbms/admin/catrul.sql;spool /oracle/admin/edidb/scripts/ordinst.log append/u01/oracle/product/11.2.0.1/db1/ord/admin/ordinst.sql SYSAUX SYSAUX;spool offspool /u01/oracle/admin/ora11g/scripts/interMedia.log append/u01/oracle/product/11.2.0.1/db1/ord/im/admin/iminst.sql;spool offset echo onspool /oracle/admin/edidb/scripts/lockAccount.log appendBEGINFOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS IN (OPEN, LOCKED, EXPIRED) AND USERNAME NOT IN (SYS,SYSTEM) )LOOP dbms_output.put_line(Locking and Expiring: | item.USERNAME); execute immediate alter user | sys.dbms_assert.enquote_name( sys.dbms_assert.schema_name( item.USERNAME),false) | password expire account lock ;END LOOP;END;/spool off到此，数据库实例建立完毕。6.配置tns与listenser/u01/oracle/product/11.2.0.1/db1/network/admin/下tnsnames.oraCRM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = crm) )listener.ora监听方式有多种，要求不高的话建议复用原监听，好处是变动小，缺点是不同实例公用监听，可能会不方便。listener.oraSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = orcl) (ORACLE_HOME = /u01/oracle/product/11.2.0.1/db1) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = crm) (ORACLE_HOME = /u01/oracle/product/11.2.0.1/db1) (SID_NAME = crm) ) )单配监听：SID_LIST_LISTENER= (SID_LIST = (SID_DESC = (SID_NAME = crm) (ORACLE_HOME = /u01/oracle/product/11.2.0.1/db1) (PROGRAM = extproc) ) )LISTENER= (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521) )注意：黄色背景部分需要对应，且不能为数据库实例名，注意listener端口与tns端口对应。采用单配listener方式时，crm或toad登录出现ORA-12537 连接关闭 错误，但tnsping crm正常，设置sqlnet.ora参数TCP.VALIDNODE_CHECKING=no 未解决问题。判断应该是listener配置问题，改为第一种方