oracle 18c 转 11g,安装Oracle：Oracle 18c、Oracle 11g

日萌社

人工智能AI：Keras PyTorch MXNet TensorFlow PaddlePaddle 深度学习实战(不定时更新)

yum -y update

yum -y upgrade

oracle所有数据库产品都可以下载。其中express版是完全免费的，其他的产品如果商用就要许可证了。

另外，没有购买许可证也没有服务。

Oracle Database Software Downloads

https://www.oracle.com/database/technologies/oracle-database-software-downloads.html

Oracle Database Express Edition

Oracle Database 18c Express Edition

https://www.oracle.com/database/technologies/xe-downloads.html

Oracle Database 11g Release 2 Express Edition for Windows 64

https://www.oracle.com/database/technologies/xe-prior-releases.html

Oracle Database 11g Release 2 Express Edition for Linux x86 and Windows

https://www.oracle.com/database/technologies/xe-prior-releases.html

certutil -hashfile 文件名 MD5：用于获取文件的MD5

certutil -hashfile 文件名 SHA1：用于获取文件的SHA1

certutil -hashfile 文件名 SHA256：用于获取文件的SHA256

可以先更新依赖

yum install -y smartmontools bc compat-libcap1 compat-libstdc++-33 glibc-devel ksh libaio-devel libstdc++-devel xorg-x11-utils xorg-x11-xauth bind-utils nfs-utils psmisc sysstat unzip

第一步安装

oracle-database-preinstall-18c

centos6：https://yum.oracle.com/repo/OracleLinux/OL6/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el6.x86_64.rpm

centos7：https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

安装 rpm -ivh oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

第二部安装

oracle-database-xe-18c-1.0-1.x86_64.rpm

下载：https://www.oracle.com/database/technologies/appdev/xe/quickstart.html

1.Oracle Linux

Download oracle-database-xe-18c-1.0-1.x86_64.rpm

yum -y localinstall oracle-database*18c*

/etc/init.d/oracle-xe-18c {start|stop|restart|configure|delete}

1.初始化

root用户执行下述命令/etc/init.d/oracle-xe-18c configure，然后输入密码 nagisa。

最终显示如下信息：

数据库创建完成。有关详细信息, 请查看以下位置的日志文件:

/opt/oracle/cfgtoollogs/dbca/XE。

数据库信息:

全局数据库名:XE

系统标识符 (SID):XE

初始化完成后可以通过 netstat -anp |grep 1521 查看到正在占用该端口

可通过查看日志目录 /opt/oracle/cfgtoollogs/dbca/XE 查找报错信息

默认安装目录：/opt/oracle/product/18c/dbhomeXE

2.初始化报错：

Specified value of sga_target 548M is too small, needs to be at least 632M

解决：

服务器的内存或者虚拟机分配给linux的内存不足，分配大一点

执行/etc/init.d/oracle-xe-18c configure初始化失败之后，必须执行/etc/init.d/oracle-xe-18c delete

先删除之前初始化失败的文件数据，然后再重新执行/etc/init.d/oracle-xe-18c configure 再次初始化。

3.启动：

查看监听当前状态：lsnrctl status

启动监听：/etc/init.d/oracle-xe-18c start

停用监听：/etc/init.d/oracle-xe-18c stop

ps aux|grep oracle

netstat -anp |grep 1521

2.Red Hat compatible Linux distribution

Download oracle-database-xe-18c-1.0-1.x86_64.rpm

curl -o oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm

yum -y localinstall oracle-database*18c*

/etc/init.d/oracle-xe-18c configure

3.配置

1.编辑环境变量的profile配置文件：vim /etc/profile

2.profile配置文件末尾添加如下配置信息：

export ORACLE_SID=XE

export ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE

export ORACLE_BASE=/opt/oracle

export PATH=$PATH:/opt/oracle/product/18c/dbhomeXE/bin

3.保存配置文件，重新加载配置文件：source /etc/profile

4.主要配置文件

/opt/oracle/product/18c/dbhomeXE/network/admin

listener.ora

sqlnet.ora

tnsnames.ora

5.sqlplus

sqlplus /nolog

conn /as sysdba

报错：

ORA-01017:用户名/口令无效,登录被拒绝

ORA-01017: invalid username/password; logon denied

解决：

1.把当前root用户添加到下面各种组中：

usermod -a -G oinstall root

usermod -a -G dba root

usermod -a -G oper root

usermod -a -G backupdba root

usermod -a -G dgdba root

usermod -a -G kmdba root

usermod -a -G racdba root

2.查看root的id：id root

打印出信息：uid=0(root) gid=0(root) 组=0(root),54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)

可用于把当前用户从xx组中移除

比如把当前root用户从dba组中移除：gpasswd -d root dba

3.使用oracle用户：su - oracle

1.sqlplus sys/sys as sysdba

2.创建用户的时候用户名以c##或者C##开头即可。

create user username

identified by username

default tablespace tablespace_name;

比如：CREATE USER c##rootuser IDENTIFIED BY 123456;

3.授予权限

grant connect,resource,dba to 用户名;

grant connect,resource,create any table,drop any table,create sequence,

select any table, create any index, drop any index,

alter any trigger, create any trigger, drop any trigger,

alter any type, create any type, drop any type,

create any view, drop any view, create any directory,

create procedure, query rewrite, create session to 用户名;

比如：

grant connect,resource,dba to c##rootuser;

grant connect,resource,create any table,drop any table,create sequence,select any table, create any index, drop any index,alter any trigger, create any trigger, drop any trigger,alter any type, create any type, drop any type,create any view, drop any view, create any directory,create procedure, query rewrite, create session to c##rootuser;

4.授予DBA权限

grant select on sys.v_$process to 用户名;

grant select on sys.v_$parameter to 用户名;

grant execute on dbms_lock to 用户名;

grant select on sys.v_$lock to 用户名;

grant select on sys.v_$session to 用户名;

grant select on sys.v_$mystat to 用户名;

grant select on sys.v_$session_wait to 用户名;

grant select on dba_kgllock to 用户名;

grant select on sys.v_$sqltext to 用户名;

grant select on sys.slog$ to 用户名;

grant alter session to 用户名;

grant select on dba_undo_extents to 用户名;

grant select on dba_tablespaces to 用户名;

grant select on dba_free_space to 用户名;

grant select on dba_data_files to 用户名;

5.sqlplus c##rootuser/123456

#conn c##rootuser/123456;

show user;

#查看当前登录的用户的表

select table_name from user_tables;

#超级管理员：sys/change_on_install;

#普通管理员：system/manager;

#普通用户：scott/tiger;

sqlplus sys/change_on_install as sysdba

sqlplus system/manager as sysdba

sqlplus scott/tiger as sysdba

sqlplus

输入用户名：sys

输入密码：change_on_install as sysdba

6.添加测试数据

create table student(

sno   varchar2(3) not null,

sname varchar2(9) not null,

ssex  varchar2(3) not null,

sbirthday date,

sclass varchar2(5),

constraint pk_student primary key(sno)

);

insert into student(sno,sname,ssex,sbirthday,sclass) values(108,'曾华','男',to_date('1977-09-01','yyyy-mm-dd'),95033);

insert into student(sno,sname,ssex,sbirthday,sclass) values(105,'匡明','男',to_date('1975-10-02','yyyy-mm-dd'),95031);

insert into student(sno,sname,ssex,sbirthday,sclass) values(107,'王丽','女',to_date('1976-01-23','yyyy-mm-dd'),95033);

insert into student(sno,sname,ssex,sbirthday,sclass) values(101,'李军','男',to_date('1976-02-20','yyyy-mm-dd'),95033);

insert into student(sno,sname,ssex,sbirthday,sclass) values(109,'王芳','女',to_date('1975-02-10','yyyy-mm-dd'),95031);

insert into student(sno,sname,ssex,sbirthday,sclass) values(103,'陆君','男',to_date('1974-06-03','yyyy-mm-dd'),95031);

select * from student;

sqlplus

window 上安装 sqlplus

https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

instantclient-basic-windows.x64-19.6.0.0.0dbru.zip

instantclient-sqlplus-windows.x64-19.6.0.0.0dbru.zip

上述两个压缩包都会自动解压到同一个文件夹instantclient_19_6，进入该文件夹找到sqlplus.exe执行。

linux 上安装 sqlplus

https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

rpm -ivh oracle-instantclient19.8-basic-19.8.0.0.0-1.x86_64.rpm

rpm -ivh oracle-instantclient19.8-sqlplus-19.8.0.0.0-1.x86_64.rpm

1、连接远程数据库sqlplus user/passwd@IP:端口/实例名

例如：sqlplus root/nagisa@192.168.126.200:1521/orcl

2、选择实例登录sqlplus user/passwd@实例名

例如：sqlplus root/nagisa@192.168.126.200:1521/orcl

Oralce SQL Developer

https://www.oracle.com/tools/downloads/sqldev-downloads.html

sqldeveloper-19.2.1.247.2212-no-jre.zip

解压后 执行 sqldeveloper.exe

Oracle 11g、Oracle 18c 下载

https://www.oracle.com/database/technologies/oracle-database-software-downloads.html?source=:ow:o:h:feb::RC_WWMK200701P00098:DevLiveDatabase_OcomBanner