Oracle 手动创建数据库

最新推荐文章于 2022-08-12 10:44:40 发布

ckth47350

最新推荐文章于 2022-08-12 10:44:40 发布

阅读量151

点赞数

文章标签：数据库开发工具运维

手动安装Oracle数据库

一 安装准备

1.1 配置本地YUM，安装软件

[root@chen ~]# cd /etc/yum.repos.d/

[root@chen yum.repos.d]# cp public-yum-ol6.repo yum.repo

[root@chen yum.repos.d]# mv public-yum-ol6.repo /root/

[root@chen yum.repos.d]# vim yum.repo

[chen_cc]

name=Oracle_chen

baseurl=file:///mnt

gpgcheck=0

enabled=1

[root@chen yum.repos.d]# mount /dev/sr1 /mnt

[root@chen yum.repos.d]# yum list

[root@chen yum.repos.d]# yum -y install compat-libstdc++-33-3.2.3 elfutils-libelf gcc gcc-c++* glibc glibc-common glibc-common glibc-devel libaio libaio-devel libgcc libstdc++-* libstdc++-devel make sysstat

1.2创建用户，组

[root@chen ~]# groupadd -g 1000 oinstall

[root@chen ~]# groupadd -g 1001 dba

[root@chen ~]# useradd -u 1000 -g oinstall -G dba oracle

[root@chen ~]# mkdir -p /u01/app/oracle

[root@chen ~]# mv p10404530_112030_Linux-x86-64_* /u01/

[root@chen ~]# chown oracle.oinstall -R /u01/

[oracle@chen u01]$ unzip p10404530_112030_Linux-x86-64_1of7.zip

[oracle@chen u01]$ unzip p10404530_112030_Linux-x86-64_2of7.zip

1.3配置oracle用户环境变量

[oracle@chen ~]$ vim .bash_profile

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0

export ORACLE_SID=orcl

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'

[oracle@chen ~]$ source .bash_profile

1.4安全配置

[root@chen ~]# vim /etc/security/limits.conf

# End of file

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft stack 10240

"/etc/security/limits.conf" 56L, 2034C written

[root@chen ~]# vim /etc/sysctl.conf

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall = 2097152

kernel.shmmax = 536870912

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

"/etc/sysctl.conf" 52L, 1461C written

[root@chen ~]# sysctl -p

二 安装数据库软件

[root@chen ~]# xhost +

[oracle@chen ~]$ ./u01/database/runInstaller

。。。。。。

三 手动创建数据库目录

[oracle@chen oracle]$ mkdir -p /u01/app/oracle/oradata/orcl

[oracle@chen oracle]$ mkdir -p /u01/app/oracle/flash_recovery_area

[oracle@chen 11.2.0]$ mkdir -p /u01/app/oracle/admin/orcl/{adump,bdump,cdump,dpdump,udump,pfile}

其中

1 adump ：审计信息

2 bdump ：后台进程trace 和alert log

3 cdump ：core trace，一般是用来日志应用程序的

4 pfile ：初始化参数文件 initSID

5 udump ：前台手动trace的比如sql trace之后session的trace文件

6 dpdump：Oracle默认的directory，可在dba_directories查到，是expdp,impdp的默认路径

四 创建参数文件

[oracle@chen oracle]$ cd /u01/app/oracle/product/11.2.0/dbs/

[oracle@chen dbs]$ cp init.ora initorcl.ora

[oracle@chen dbs]$ vim initorcl.ora

*.compatible='11.2.0.3.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl'

*.db_block_size=8192

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=1G

*.diagnostic_dest='/u01/app/oracle'

*.memory_target=500M

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.undo_management=auto

五 创建密码文件

[oracle@chen dbs]$ cd $ORACLE_HOME/dbs

[oracle@chen dbs]$ orapwd file=orapworcl password=oracle

六 创建数据库脚本

[oracle@chen ~]$ touch create_db.sql

[oracle@chen ~]$ vim create_db.sql

create database orcl

user sys identified by oracle

user system identified by oracle

maxinstances 1

maxloghistory 100

maxlogfiles 5

maxlogmembers 3

maxdatafiles 1000

character set zhs16gbk

national character set al16utf16

logfile

group 1('/u01/app/oracle/oradata/orcl/redo01.log') size 10m,

group 2('/u01/app/oracle/oradata/orcl/redo02.log') size 10m,

group 3('/u01/app/oracle/oradata/orcl/redo03.log') size 10m

datafile

'/u01/app/oracle/oradata/orcl/system.dbf' size 100m autoextend on next 1m maxsize unlimited

sysaux datafile

'/u01/app/oracle/oradata/orcl/sysaux.dbf' size 50m autoextend on next 1m maxsize unlimited

default tablespace users datafile

'/u01/app/oracle/oradata/orcl/user01.dbf' size 50m autoextend on next 1m maxsize unlimited

default temporary tablespace temp tempfile

'/u01/app/oracle/oradata/orcl/temp01.dbf' size 50m autoextend on next 1m maxsize unlimited

undo tablespace undotbs1 datafile

'/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 50m autoextend on next 1m maxsize unlimited;

七 启动实例，执行脚本，创建数据库

[oracle@chen ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 23 18:10:11 2015

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 521936896 bytes

Fixed Size 2229944 bytes

Variable Size 314575176 bytes

Database Buffers 201326592 bytes

Redo Buffers 3805184 bytes

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

-------------------------------- ------------------------

orcl STARTED

SQL> @/home/oracle/create_db.sql

Database created.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

orcl OPEN

八 创建数据字典

SQL> conn /as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql

SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

SQL> @$ORACLE_HOME/rdbms/admin/catexp.sql

SQL> conn system/oracle

SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql /*Warning:Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM*/

其中

1 catalog.sql /*创建数据库数据字典文件,例如：all_users*/

2 catproc.sql /*创建数据库基本过程和包，例如：substr...*/

3 pupbld.sql /*Warning:Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM*/

4 catexp.sql

5 EXPORT需要的VIEW是由CATEXP.SQL创建，这些内部VIEW用于EXPORT组织DUMP文件中数据格式。大部分VIEW用于收集创建DDL语句的，其他的主要供ORACLE开发人员用。

这些VIEW在不同ORACLE版本之间有可能不同，每个版本可能都有新的特性加入。所以在新的版本里面执行旧的dump文件会有错误，一般可以执行CATEXP.SQL解决这些问题，

解决向后兼容问题的一般步骤如下：

(1)导出数据库的版本比目标数据库老的情况：

:在需要导入的目标数据库中执行旧的CATEXP.SQL

:使用旧的EXPORT导出DUMP文件

:使用旧的IMPORT导入到数据库中

:在数据库中执行新的CATEXP.SQL，以恢复该版本的EXPORT VIEW

(2)导出数据库的版本比目标数据库新的情况：

:在需要导入的目标数据库中执行新的CATEXP.SQL

:使用新的EXPORT导出DUMP文件

:使用新的IMPORT导入到数据库中

:在数据库中执行旧的CATEXP.SQL，以恢复该版本的EXPORT VIEW

九 创建监听文件

1 在没有$ORACLE_HOME/network/admin/listener.or情况下，会默认启动/u01/app/oracle/diag/tnslsnr/chen/listener/alert/log.xml文件启动监听

[oracle@chen admin]$ lsnrctl

LSNRCTL> start

LSNRCTL> status

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 23-JUL-2015 19:03:14

Uptime 0 days 0 hr. 1 min. 27 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Log File /u01/app/oracle/diag/tnslsnr/chen/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chen)(PORT=1521)))

Services Summary...

Service "orcl" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully

2 创建listener.ora文件

[oracle@chen ~]$ cd $ORACLE_HOME/network/admin

[oracle@chen admin]$ cp samples/* .

[oracle@chen admin]$ ls

listener.ora samples shrept.lst sqlnet.ora tnsnames.ora

[oracle@chen admin]$ echo >listener.ora

[oracle@chen admin]$ vim listener.ora

LISTENR =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = PROTOCOL = TCP)(HOST = chen)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

LOGGING_LISTENER = ON

[oracle@chen admin]$ lsnrctl

LSNRCTL> start

Starting /u01/app/oracle/product/11.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/chen/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chen)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 23-JUL-2015 19:16:44

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/chen/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chen)(PORT=1521)))

The listener supports no services

The command completed successfully

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 23-JUL-2015 19:16:44

Uptime 0 days 0 hr. 1 min. 44 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/chen/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=chen)(PORT=1521)))

Services Summary...

Service "orcl" has 1 instance(s).

Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully

3 创建tnsname.ora文件

[oracle@chen admin]$ echo >tnsnames.ora

[oracle@chen admin]$ vim tnsnames.ora

199 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = chen)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

[oracle@chen admin]$ tnsping 199

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 23-JUL-2015 19:21:30

Used parameter files:

/u01/app/oracle/product/11.2.0/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = chen)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

OK (0 msec)

十查看

SQL> select * from all_users;

USERNAME USER_ID CREATED

------------------------------ ---------- ------------

APPQOSSYS 31 23-JUL-15

DBSNMP 30 23-JUL-15

ORACLE_OCM 21 23-JUL-15

DIP 14 23-JUL-15

OUTLN 9 23-JUL-15

SYSTEM 5 23-JUL-15

SYS 0 23-JUL-15