admin 管理员组

文章数量: 887032


2024年1月15日发(作者:绿色颜色代码)

Oracle 11gR2 创建数据库实例

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

1.创建实例启动用的参数文件。在程序默认的参数文件里修改即可:重命名规则

/u01/oracle/product/11.2.0/db_1/dbs/ 下,目录尽量使用绝对路径,采用ORACLE_BASE等环境变量有可能报错。

db_name='crm' --修改

#memory_target=500m --注释掉

sga_target=200m --添加

pga_aggregate_target=60m --添加

processes = 150

audit_file_dest='/u01/oracle/admin/crm/adump' --建立目录

audit_trail ='db'

db_block_size=8192

db_domain=''

db_recovery_file_dest='/u01/oracle/flash_recovery_area' --建立目录

db_recovery_file_dest_size=2G

diagnostic_dest='/u01/oracle' ---修改

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

2.创建相应目录:

mkdir -p /u01/oracle/admin/ora11g/adump

mkdir -p /u01/oracle/admin/ora11g/dpdump

mkdir -p /u01/oracle/flash_recovery_area

3.创建密码文件:命名规则 orapwSID,目录依旧是 /u01/oracle/product/11.2.0/db_1/dbs/

Linux下orapw+实例名

Windows下pwd+实例名

$ orapwd file=orapwora11g password=oracle

4.创建数据库实例

设置环境变量 export ORACLE_SID=crm

执行以下命令

sqlplus /nolog

conn / as sysdba

注意:本处可能报错需要可能的原因: a)5_6.4、libaio-0.3.106-5包是否已安装,命令分别为rpm -q glibc-devel、rpm -q libaio

b)oracle目录权限不足或属组不对,chmod 6751 oracle 授权、chown -R ll /u01/oracle 修改属组

执行建库脚本:

startup nomount pfile="/u01/oracle/product/11.2.0.1/db1/dbs/";

CREATE DATABASE "crm"

MAXINSTANCES 8

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

DATAFILE '/u01/oracle/oradata/' SIZE 1024M REUSE

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/u01/oracle/oradata/' SIZE 500M REUSE

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oracle/oradata/' SIZE 20M REUSE

SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/oracle/oradata/' SIZE 300M REUSE

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16

LOGFILE GROUP 1 ('/u01/oracle/oradata/') SIZE 50M,

GROUP 2 ('/u01/oracle/oradata/') SIZE 50M,

GROUP 3 ('/u01/oracle/oradata/') SIZE 50M;

创建USERS表空间

CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/u01/app/oradata/' 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/;

@/u01/oracle/product/11.2.0.1/db1/rdbms/admin/;

@/u01/oracle/product/11.2.0.1/db1/rdbms/admin/;

@/u01/oracle/product/11.2.0.1/db1/rdbms/admin/;

@/u01/oracle/product/11.2.0.1/db1/rdbms/admin/;

使用system用户编译

@/u01/oracle/product/11.2.0.1/db1/sqlplus/admin/;

@/u01/oracle/product/11.2.0.1/db1/sqlplus/admin/help/ ;

使用sys用户编译

@/u01/oracle/product/11.2.0.1/db1/javavm/install/;

@/u01/oracle/product/11.2.0.1/db1/xdk/admin/;

@/u01/oracle/product/11.2.0.1/db1/xdk/admin/;

@/u01/oracle/product/11.2.0.1/db1/rdbms/admin/;

@/u01/oracle/product/11.2.0.1/db1/rdbms/admin/;

@/u01/oracle/product/11.2.0.1/db1/rdbms/admin/ change_on_install SYSAUX TEMP YES;

@/u01/oracle/product/11.2.0.1/db1/rdbms/admin/;

@/u01/oracle/product/11.2.0.1/db1/rdbms/admin/;

spool /oracle/admin/edidb/scripts/ append

@/u01/oracle/product/11.2.0.1/db1/ord/admin/ SYSAUX SYSAUX;

spool off

spool /u01/oracle/admin/ora11g/scripts/ append

@/u01/oracle/product/11.2.0.1/db1/ord/im/admin/;

spool off

set echo on

spool /oracle/admin/edidb/scripts/ 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__line('Locking and Expiring: ' || ME);

execute immediate 'alter user ' ||

_e_name(

__name(

ME),false) || ' password expire account lock' ;

END LOOP;

END;

/

spool off

到此,数据库实例建立完毕。

6.配置tns与listenser /u01/oracle/product/11.2.0.1/db1/network/admin/下

CRM = (DESCRIPTION =

(ADDRESS_LIST = (ADDRESS =

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

)

)

(CONNECT_DATA = (SERVICE_NAME = crm)

)

)

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

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)

)

)

单配监听:

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正常,设置参数ODE_CHECKING=no 未解决问题。判断应该是listener配置问题,改为第一种方式问题解决。

7. 实例停止然后启动,监听停止,然后启动。完成数据库实例和监听的加载

sqlplus / as sysdba

SQL> shutdown immediate --“关闭数据库实例”

SQL> startup --“启动数据库实例”

SQL>exit

$ lsnrctl stop listener -停止名为listener的监听服务

$ lsnrctl start listener -启动名为listener的监听服务

$ lsnrctl reload listener -重新加载名为listener的监听服务


本文标签: 实例 监听 数据库 创建 启动