Linux部署Oracle

_凌云木_

已于 2022-08-09 16:07:20 修改

阅读量1.5k

点赞数

分类专栏：服务器部署文章标签： linux 运维

于 2022-03-31 17:05:58 首次发布

本文链接：https://blog.csdn.net/jhz13075/article/details/123876820

版权

服务器部署专栏收录该内容

13 篇文章 2 订阅

订阅专栏

一、docker方式

Docker容器oracle 11g部署（一） - 知乎一、获取取镜像执行命令： # docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g 下载的过程少长,镜像约为6.8G 下载完成后查看镜像： docker images 二、镜像重新命名[root@whdata3 ~]# docker t…https://zhuanlan.zhihu.com/p/362849510环境：centos7

1.1 安装docker

echo `systemctl stop docker` && \
echo `yum erase -y docker docker-client  docker-client-latest docker-common docker-latest docker-latest-logrotate docker-logrotate docker-selinux docker-engine-selinux  docker-engine docker-ce` && \
yum install -y yum-utils device-mapper-persistent-data lvm2 && \
yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo && \
yum install docker-ce -y && \
mkdir -p /etc/docker && \
echo '{
  "registry-mirrors": [
"https://x9o4p9lt.mirror.aliyuncs.com",
"https://dockerhub.azk8s.cn",
"https://hub-mirror.c.163.com"
  ]
}'>/etc/docker/daemon.json && \
systemctl daemon-reload && systemctl enable docker && systemctl restart docker && docker version && \
echo `cat /etc/docker/daemon.json`

1.2 拉取创建运行镜像

echo "`docker rm -f oracle_11g`" && \
docker run \
--restart=always -d \
--name oracle_11g \
-p 1521:1521 \
-p 10122:22 \
-v /etc/timezone:/etc/timezone \
-v /etc/localtime:/etc/localtime:ro \
registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g && \
sleep 1 && echo -e "\n`docker logs oracle_11g`\n"

1.4 进入容器、配置Oracle数据库

# 进入容器
docker exec -it oracle_11g /bin/bash
 
# ========= 切换到root，配置环境变量
su - root
密码：helowin
 
# vi /etc/profile
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$ORACLE_HOME/bin:$PATH

exit
source /etc/profile
 
-- 登录sqlplus并修改sys、system用户密码
sqlplus /nolog
conn /as sysdba
alter user system identified by system;
alter user sys identified by sys;
 
-- 刷新下表
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;


-- 删除空间
drop tablespace yjgk including contents and datafiles;
drop tablespace ydzf including contents and datafiles;

-- 创建空间
create tablespace yjgk datafile '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/yjgk.dbf' size 1M;
create tablespace ydzf datafile '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/ydzf.dbf' size 1M;

-- 删除用户
drop user yjgk cascade;
drop user ydzf cascade;

-- 创建用户、授权
create user yjgk identified by "1qaz!QAZ" default tablespace yjgk temporary tablespace TEMP;
grant connect, dba, resource to yjgk;

create user ydzf identified by "1qaz!QAZ" default tablespace ydzf temporary tablespace TEMP;
grant connect, dba, resource to ydzf;


-- 删除表
drop table yjgk.test;
drop table ydzf.test;

-- 创建表和注释
create table yjgk.test(id int not null primary key, name varchar2(20));
comment on table yjgk.test is '测试表';
comment on column yjgk.test.name is '姓名';

create table ydzf.test(id int not null primary key, name varchar2(20));
comment on table ydzf.test is '测试表';
comment on column ydzf.test.name is '姓名';

-- 插入数据
insert into yjgk.test values(1, '1');
insert into ydzf.test values(1, '1');

exit;

1.5 使用navicat创建用户

使用system登录

服务名选SID helowin

用户名密码使用 system system

新建表空间

生成的DBF文件的位置：

/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs

[oracle@5f9cd6ffc0c9 dbs]$ pwd
/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs
[oracle@5f9cd6ffc0c9 dbs]$ ls
123.sp  YJGK  hc_helowin.dat  init.ora  inithelowin.ora  lkHELOWIN  lkORCL  orapwhelowin  spfilehelowin.ora
[oracle@5f9cd6ffc0c9 dbs]$

新建用户

配置用户

【成员属于】勾选【connect，dba，resource】的【授予、作为默认】

【服务器权限】勾选【UNLIMITED TABLESPACE】的【授予】

用户创建完成后，默认会创建一个与用户同名的模式

可以编辑连接使用新用户登录

服务名选SID helowin

用户名密码使用 newuser newpassword

二、客户端操作

2.1 先安装oracle客户端

Instant Client for Linux x86-64 (64-bit)https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

https://download.oracle.com/otn/linux/instantclient/11204/oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm
https://download.oracle.com/otn/linux/instantclient/11204/oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm
https://download.oracle.com/otn/linux/instantclient/11204/oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm

rpm -ivh *.rpm


# vi /etc/profile
export ORACLE_HOME=/usr/lib/oracle/11.2/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH

source /etc/profile

配置客户端的exp与imp

使客户端支持exp与imp命令：
1、exp、imp 是在服务端 $ORACLE_HOME/bin 目录下, 拷贝到客户端相应目录下
2、在客户端创建目录 mkdir -p $ORACLE_HOME/rdbms/mesg/
3、expus.msb、impus.msb 是在服务端 $ORACLE_HOME/rdbms/mesg 目录下，拷贝到客户端相应目录下

如果报 With the Partitioning, OLAP and Data Mining optionsexp: symbol lookup error: exp: undefined symbol: sldext 错误
4、libclntsh.so.11.1 是在服务端 $ORACLE_HOME/lib 目录下，拷贝到客户端相应目录下(同名覆盖)

在对ORACLE数据库做exp备份导出时报EXP-00091: Exporting questionable statistics”错误
引起此问题的原因是数据库字符集和操作系统的NLS_LANG不一致导致
5、修改/etc/profile 文件的 export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
   查询数据库的字符集信息
   select userenv('language') from dual;  
   AMERICAN_AMERICA.AL32UTF8
   export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

2.2 sqlplus 远程连接oracle

2.2.1 使用tnsnames.ora连接oracle

命令：sqlplus usernaem/password@orcl

在client64下创建目录 network/admin，然后把server端的tnsnames.ora上传到这个目录下

[root@e3065198aed1 ~]# echo $ORACLE_HOME
/usr/lib/oracle/11.2/client64
[root@e3065198aed1 ~]# echo $TNS_ADMIN
/usr/lib/oracle/11.2/client64/network/admin
[root@e3065198aed1 ~]# cd $TNS_ADMIN
[root@e3065198aed1 admin]# pwd
/usr/lib/oracle/11.2/client64/network/admin
[root@e3065198aed1 admin]# ls
tnsnames.ora
[root@e3065198aed1 admin]# cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_HELOWIN =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.204.137)(PORT = 1521))


HELOWIN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.204.137))(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = helowin)
    )
  )

[root@e3065198aed1 admin]#

2.2.2 使用host-ip连接oracle

命令：sqlplus username/password@//host:port/sid

sqlplus system/system@192.168.204.137/helowin

sqlplus 'yjgk/"1qaz!QAZ"'@192.168.204.137/helowin

[root@e3065198aed1 ~]# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.17.0.4  netmask 255.255.0.0  broadcast 172.17.255.255
        ether 02:42:ac:11:00:04  txqueuelen 0  (Ethernet)
        RX packets 15467  bytes 94462323 (90.0 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 10938  bytes 898808 (877.7 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@e3065198aed1 ~]# sqlplus system/system@192.168.204.137/helowin

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 1 18:06:24 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[root@e3065198aed1 client64]#
[root@e3065198aed1 client64]#
[root@e3065198aed1 client64]#
[root@e3065198aed1 client64]# sqlplus 'yjgk/"1qaz!QAZ"'@192.168.204.137/helowin

SQL*Plus: Release 11.2.0.4.0 Production on Sat Apr 2 09:46:17 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[root@e3065198aed1 client64]#

2.3 客户端命令

# 如果你的密码中包含特殊字符,在Windows下就需要用三个双引号扩起来，如：
exp skytf/"""#$adf`%6^=dfom"""@skytf buffer=52428800 file=skytf.dmp

# 如果是在Linux系统下，则密码用一对双引号, 整体userid和password用对单引号括住，如：
exp 'skytf/"?`}:=*H"@skytf'"<akJWI-q-@#<>?`}:Q*H"@skytf' DIRECT=Y buffer=52428800 FEEDBACK=10000 statistics=none file=skytf.dmp

客户端连接数据库操作
1、sqlplus / as sysdba
 操作系统认证，不需要数据库服务器启动listener，也不需要数据库服务器处于可用状态。比如我们想要启动数据库就可以用这种方式进入sqlplus，然后通过startup命令来启动。
2、sqlplus username/password
 连接本机数据库，不需要数据库服务器的listener进程，但是由于需要用户名密码的认证，因此需要数据库服务器处于可用状态才行。
3、sqlplus usernaem/password@orcl
 通过网络连接，这是需要数据库服务器的listener处于监听状态。此时建立一个连接的大致步骤如下
   a. 查询sqlnet.ora，看看名称的解析方式，默认是TNSNAME
   b. 查询tnsnames.ora文件，从里边找orcl的记录，并且找到数据库服务器的主机名或者IP，端口和service_name。
   c. 如果服务器listener进程没有问题的话，建立与listener进程的连接。　
   d. 根据不同的服务器模式如专用服务器模式或者共享服务器模式，listener采取接下去的动作。默认是专用服务器模式，没有问题的话客户端就连接上了数据库的server process。
   e. 这时连接已经建立，可以操作数据库了。
4、sqlplus username/password@//host:port/sid
 用sqlplus远程连接oracle命令(例：sqlplus risenet/1@//192.168.130.99:1521/risenet)


导出导出命令（客户端没有命令时可以从服务端拷贝过来使用）
1、exp(exp help=y查看帮助):
 有三种主要的方式（完全、用户、表）
 1、完全：
   exp system/system buffer=64000 file=.\full.dmp full=y
   如果要执行完全导出，必须具有特殊的权限
 2、用户模式：
   exp sonic/snoic buffer=64000 file=.\sonic.dmp owner=sonic
   exp system/system buffer=64000 file=.\sonic.dmp owner=sonic
   这样用户sonic的所有对象被输出到文件中，多个用户逗号分割。
 3、表模式：
   exp sonic/snoic buffer=64000 file=.\sonic_tables.dmp owner=sonic tables=test
   这样用户sonic的表test就被导出
2、imp(imp help=y查看帮助):
 具有三种模式（完全、用户、表）
 1、完全：
   imp system/system buffer=64000 file=.\full.dmp full=y
 2、用户模式：
   imp sonic/sonic buffer=64000 file=.\full.dmp fromuser=sonic touser=sonic
   这样用户sonic的所有对象被导入到文件中。必须指定fromuser、touser参数，多个用户逗号分割这样才能导入数据。
 3、表模式：
   imp sonic/sonic buffer=64000 file=.\full.dmp owner=sonic tables=test
   这样用户sonic的表test就被导入。

导出导入操作

exp system/system@192.168.204.137/helowin buffer=64000 file=./yjgk_ydzf_backup.dmp owner=yjgk,ydzf


sqlplus system/system@192.168.204.137/helowin

drop user yjgk cascade;
drop user ydzf cascade;
create user yjgk identified by "1qaz!QAZ" default tablespace yjgk temporary tablespace TEMP;
grant connect, dba, resource to yjgk;
create user ydzf identified by "1qaz!QAZ" default tablespace ydzf temporary tablespace TEMP;
grant connect, dba, resource to ydzf;


imp system/system@192.168.204.137/helowin buffer=64000 file=./yjgk_ydzf_backup.dmp fromuser=yjgk,ydzf touser=yjgk,ydzf

# =============================================================
# 导出用户数据（含表）
# =============================================================



[root@7ae2404d833c ~]# exp system/system@192.168.204.137/helowin buffer=64000 file=./yjgk_ydzf_backup.dmp owner=yjgk,ydzf

Export: Release 11.2.0.1.0 - Production on Sat Apr 2 17:11:21 2022

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user YJGK
. exporting foreign function library names for user YDZF
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user YJGK
. exporting object type definitions for user YDZF
About to export YJGK's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export YJGK's tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
About to export YDZF's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export YDZF's tables via Conventional Path ...
. . exporting table                           TEST          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.




# =============================================================
#  恢复前，删除旧的用户（含数据）
# =============================================================




[root@7ae2404d833c ~]# sqlplus system/system@192.168.204.137/helowin

SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 2 17:11:41 2022

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user yjgk cascade;

User dropped.

SQL> drop user ydzf cascade;

User dropped.

SQL> create user yjgk identified by "1qaz!QAZ" default tablespace yjgk temporary tablespace TEMP;

User created.

SQL> grant connect, dba, resource to yjgk;

Grant succeeded.

SQL> create user ydzf identified by "1qaz!QAZ" default tablespace ydzf temporary tablespace TEMP;

User created.

SQL> grant connect, dba, resource to ydzf;

Grant succeeded.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options



# =============================================================
# 恢复用户（含数据）
# =============================================================



[root@7ae2404d833c ~]# imp system/system@192.168.204.137/helowin buffer=64000 file=./yjgk_ydzf_backup.dmp fromuser=yjgk,ydzf touser=yjgk,ydzf

Import: Release 11.2.0.1.0 - Production on Sat Apr 2 17:12:56 2022

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing YJGK's objects into YJGK
. importing YDZF's objects into YDZF
. importing YJGK's objects into YJGK
. . importing table                         "TEST"          1 rows imported
. importing YDZF's objects into YDZF
. . importing table                         "TEST"          1 rows imported
Import terminated successfully without warnings.
[root@7ae2404d833c ~]#

三、自动备份

备份脚本

#!/bin/bash
# crontab -e
# 0 2 * * 1 /opt/oracle_backup.sh
source /etc/profile
basepath=/opt
backuppath=$basepath/oracle_backup
mkdir -p $backuppath
timestr=$(date "+%Y%m%d%H%M%S")
 
# 导出指定用户的表
exp system/password@10.20.120.102/orcl buffer=64000 owner=ydzf,yjgk,ycyd,wqpfhs file=$backuppath/backup_$timestr.dmp &>$backuppath/log_$timestr.log
 
# 保留180天内的日志
find $backuppath -type f -ctime +180 -exec rm -rf {} \;
 
# 还原操作
# sqlplus system/password@10.20.23.120/orcl
# drop user yjgk cascade;
# drop user ydzf cascade;
# drop user ycyd cascade;
# drop user wqpfhs cascade;
# create user yjgk identified by "1qaz!QAZ" default tablespace yjgk temporary tablespace TEMP;
# grant connect, dba, resource to yjgk;
# create user ydzf identified by "1qaz!QAZ" default tablespace ydzf temporary tablespace TEMP;
# grant connect, dba, resource to ydzf;
# create user ycyd identified by "1qaz!QAZ" default tablespace ycyd temporary tablespace TEMP;
# grant connect, dba, resource to ycyd;
# create user wqpfhs identified by "1qaz!QAZ" default tablespace wqpfhs temporary tablespace TEMP;
# grant connect, dba, resource to wqpfhs;
# imp system/password@10.20.120.102/orcl buffer=64000 fromuser=ydzf,yjgk,ycyd,wqpfhs touser=ydzf,yjgk,ycyd,wqpfhs file=backup_file/backup_$timestr.dmp

备份计划

service cron start    //启动服务
service cron stop     //关闭服务
service cron restart  //重启服务
service cron reload   //重新载入配置
service cron status   //查看服务状态

# 编辑crontab定时任务
crontab -e
# 每周一凌晨2点执行一次
0 2 * * 1 /opt/oracle_backup/oracle11g_backup.sh
# 查询crontab任务
crontab -l

_凌云木_

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Linux部署Oracle

一、docker方式Docker容器oracle 11g部署（一） - 知乎一、获取取镜像执行命令： # docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g 下载的过程少长,镜像约为6.8G 下载完成后查看镜像： docker images 二、镜像重新命名[root@whdata3 ~]# docker t…https://zhuanlan.zhihu.com/p/362849510环境：centos71.1 ....
复制链接

扫一扫

专栏目录