随笔分类

随笔档案

阅读排行榜

推荐排行榜

Oracle 19C 基于 Docker 部署

1、Docker、Docker-Compose安装

1 2	`https://www.cnblogs.com/a120608yby/p/9883175.html` `https://www.cnblogs.com/a120608yby/p/14582853.html`

2、基于Docker-Compose部署Oracle

# vim docker-compose.yml
version: '3.4'
 
services:
  oracledb:
    image: container-registry.oracle.com/database/enterprise:19.3.0.0
    container_name: oracledb
    restart: unless-stopped
    ports:
      - 1521:1521
      - 5500:5500
    networks:
      - ops_default
    environment:
      ORACLE_PWD: "Admin-123456"
      ORACLE_EDITION: enterprise
    volumes:
      - ./oradata:/opt/oracle/oradata
      - /etc/localtime:/etc/localtime:ro
 
networks:
  ops_default:
    external: true

3、启动服务

1	`docker compose up -d`

4、查看服务启动状态

1	`docker-compose` `ps`

5、访问

1	`连接地址：部署主机的IP:1521/orclcdb`

6、表空间查询

SELECT b.file_name 物理文件名,
       b.tablespace_name 表空间,
       b.bytes / 1024 / 1024 大小m,
       (b.bytes - SUM(nvl(a.bytes,
                          0))) / 1024 / 1024 已使用m,
       substr((b.bytes - SUM(nvl(a.bytes,
                                 0))) / (b.bytes) * 100,
              1,
              5) 利用率
  FROM dba_free_space a,
       dba_data_files b
 WHERE a.file_id = b.file_id
 GROUP BY b.tablespace_name,
          b.file_name,
          b.bytes
 ORDER BY b.tablespace_name

7、创建表空间及用户

# 切换到oracle用户
su - oracle
 
# 登录sys用户
sqlplus / as sysdba
 
# 创建临时表空间
create temporary tablespace temp1 tempfile '/app/oracle/oradata/orcl/temp1.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
 
# 创建数据表空间
create tablespace data1 logging datafile '/app/oracle/oradata/orcl/data1.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;
 
# 创建用户并指定表空间
create user user1 identified by password default tablespace data1 temporary tablespace temp1;
 
# 给用户授予权限
grant connect,resource,dba to user1;

8、创建备份目录对象并备份

# 创建备份目录对象
CREATE OR REPLACE DIRECTORY backup_dir AS '/home/oracle/backup';
 
# 赋予读写该目录的权限
GRANT READ, WRITE ON DIRECTORY backup_dir TO user1;
 
# 查看已经创建的目录对象和对应的路径
SELECT * FROM DBA_DIRECTORIES;
 
# 删除不需要的目录对象
DROP DIRECTORY backup_dir;
 
# 备份脚本
#!/bin/bash
#
USER="user1"
PASS="password"
DB="orclcdb"
DATE=$(date +%Y%m%d)
DUMPFILE="$USER-$DATE.dmp"
LOGFILE="$USER-$DATE.log"
 
mkdir -p $DIR
expdp $USER/$PASS@$DB directory=backup_dir dumpfile=$DUMPFILE logfile=$LOGFILE schemas=$USER
gzip $DIR/$DUMPFILE
 
find $DIR -name "*.gz" -mtime +7 -exec rm -f {} ;

9、数据恢复

# 切换到oracle用户
su - oracle
 
# 登录sys用户
sqlplus / as sysdba
 
# 创建与备份时一致的目录对象
CREATE OR REPLACE DIRECTORY backup_dir AS '/home/oracle/backup';
 
# 赋予读写该目录的权限
GRANT READ, WRITE ON DIRECTORY backup_dir TO user1;
 
# 导入数据
impdp usre1/password@orclcdb directory=backup_dir dumpfile=user1-20230921.dmp logfile=user1-20230921.log