Oracle 11gR2 创建数据库实例

PHP中文网 • 2025年2月22日 19:31:45 • 数据库 • 阅读 2

因为工作需要在Oracle 11gR2库中新建一数据库实例。采用脚本命令创建，建议使用oracle用户进行以下操作。顺序如下：1.创建实例启

startup nomount pfile=”/u01/oracle/product/11.2.0.1/db1/dbs/initcrm.ora”;
create database “crm”
maxinstances 8
maxloghistory 1
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
datafile ‘/u01/oracle/oradata/system01.dbf’ size 1024m reuse
extent management local
sysaux datafile ‘/u01/oracle/oradata/sysaux01.dbf’ size 500m reuse
smallfile default temporary tablespace temp tempfile ‘/u01/oracle/oradata/temp01.dbf’ size 20m reuse
smallfile undo tablespace “undotbs1” datafile ‘/u01/oracle/oradata/undo01.dbf’ size 300m reuse
character set zhs16gbk
national character set al16utf16
logfile 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 SPACE MANAGEMENT AUTO;
ALTER DATABASE DEFAULT TABLESPACE “USERS”;

——————————————

SQL> create spfile from pfile;

File created.

—————————————–

sqlplus / as sysdba
SQL> 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 off

spool /u01/oracle/admin/ora11g/scripts/interMedia.log append
@/u01/oracle/product/11.2.0.1/db1/ord/im/admin/iminst.sql;
spool off

set echo on
spool /oracle/admin/edidb/scripts/lockAccount.log append
BEGIN
FOR 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.ora

CRM = (DESCRIPTION =

(ADDRESS_LIST = (ADDRESS =

(PROTOCOL = TCP) (HOST = 127.0.0.1)(PORT = 1521)

)

(CONNECT_DATA = (SERVICE_NAME = crm)

)

listener.ora 监听方式有多种，要求不高的话建议复用原监听，，好处是变动小，缺点是不同实例公用监听，可能会不方便。

listener.ora

SID_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)
    )
)

单配监听：

发布者：PHP中文网，转转请注明出处：https://www.chuangxiangniao.com/p/1923061.html