笺注:Docker的安装可参考 CentOS8_在Docker中使用Nginx的反向代理
查看Docker的版本信息:
[root@centos8 ~]# docker -v
Docker version 20.10.21, build baeda1f
[root@centos8 ~]#
已经设置开机自动启动Docker:
[root@centos8 ~]# systemctl is-enabled docker
enabled
[root@centos8 ~]#
从公网下载镜像:( 以下是下载Oracle11gR2的镜像 )
[root@centos8 ~]# docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
Using default tag: latest
latest: Pulling from helowin/oracle_11g
ed5542b8e0e1: Pull complete
a3ed95caeb02: Pull complete
1e8f80d0799e: Pull complete
Digest: sha256:4c12b98372dfcbaafcd9564a37c8d91456090a5c6fb07a4ec18270c9d9ef9726
Status: Downloaded newer image for registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest
registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest
[root@centos8 ~]#
查看宿主机的所有镜像:
[root@centos8 ~]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g latest 3fa112fd3642 7 years ago 6.85GB
[root@centos8 ~]#
使用镜像“registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest”创建并启动容器“Rhel6_1”:
[root@centos8 ~]# docker run -itd --name Rhel6_1 --restart=always -p 1521:1521 -v /opt/share:/home/dir_1 registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest
71ba8a1c10fecc5f7a859cfdae081b47eae22d1d429041a74478b50cd0195cdc
[root@centos8 ~]#
注释:
-itd : 以交互模式情况下后台运行。
--name : 指定容器名称。
-p 端口映射 : 第一个1521是宿主机的端口,暴露给外部直接访问;第二个1521是容器的端口。
--restart=always : 让容器随宿主机的启动而启动。
-v 挂载目录 : 这里是把宿主机的目录/opt/share挂载到容器的目录/home/dir_1;假如目录不存在,就会自动创建。
registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest : 镜像名称:版本号
在宿主机查看容器“Rhel6_1”是否会随宿主机的启动而启动:(会自动启动)
[root@centos8 ~]# docker inspect Rhel6_1 | grep -i -A2 "RestartPolicy"
"RestartPolicy": {
"Name": "always",
"MaximumRetryCount": 0
[root@centos8 ~]#
在宿主机查看正在运行的容器:
[root@centos8 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
71ba8a1c10fe registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest "/bin/sh -c '/home/o…" 4 minutes ago Up 3 minutes 0.0.0.0:1521->1521/tcp, :::1521->1521/tcp Rhel6_1
[root@centos8 ~]#
注释:宿主机的TCP 1521端口,映射到容器“Rhel6_1”的TCP 1521端口。
###
进入容器“Rhel6_1”:
[root@centos8 ~]# docker exec -it Rhel6_1 /bin/bash
[oracle@71ba8a1c10fe /]$
[oracle@71ba8a1c10fe /]$ pwd
/
[oracle@71ba8a1c10fe /]$ ls
bin boot dev etc home lib lib64 media mnt opt proc root sbin selinux srv sys tmp usr var
[oracle@71ba8a1c10fe /]$
查看容器“Rhel6_1”的系统版本信息:
[oracle@71ba8a1c10fe /]$ cat /etc/redhat-release
CentOS release 6.5 (Final)
[oracle@71ba8a1c10fe /]$ uname -r
4.18.0-193.el8.x86_64
[oracle@71ba8a1c10fe /]$
切换到系统管理员root:(默认密码 helowin )
[oracle@71ba8a1c10fe /]$ su - root
Password:
[root@71ba8a1c10fe ~]#
编辑系统用户oracle的环境变量:
[root@71ba8a1c10fe ~]# vi /home/oracle/.bashrc
追加:
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH
刷新系统用户oracle的环境变量:
[root@71ba8a1c10fe ~]# source /home/oracle/.bashrc
[root@71ba8a1c10fe ~]#
切换到系统用户oracle:
[root@71ba8a1c10fe ~]# su - oracle
[oracle@71ba8a1c10fe ~]$
[oracle@71ba8a1c10fe ~]$ pwd
/home/oracle
[oracle@71ba8a1c10fe ~]$ ls -alh
total 20K
drwx------. 1 oracle oinstall 53 Apr 12 18:31 .
drwxr-xr-x. 1 root root 33 Apr 12 18:06 ..
-rw-------. 1 oracle oinstall 6.7K Apr 12 20:55 .bash_history
-rw-r--r--. 1 oracle oinstall 18 Jul 18 2013 .bash_logout
-rw-r--r--. 1 oracle oinstall 606 Dec 29 2015 .bash_profile
-rw-r--r--. 1 oracle oinstall 253 Apr 12 18:31 .bashrc
drwxr-xr-x. 3 oracle oinstall 18 Aug 23 2014 .oracle
drwxr-xr-x. 1 oracle oinstall 20 Aug 27 2014 app
[oracle@71ba8a1c10fe ~]$
[oracle@71ba8a1c10fe ~]$ cat .bashrc
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# User specific aliases and functions
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH
[oracle@71ba8a1c10fe ~]$
查看Oracle的监听状态:(默认会自动启动)
[oracle@71ba8a1c10fe ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 12-APR-2023 18:33:32
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 12-APR-2023 18:06:53
Uptime 0 days 0 hr. 26 min. 39 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/diag/tnslsnr/71ba8a1c10fe/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=71ba8a1c10fe)(PORT=1521)))
Services Summary...
Service "helowin" has 1 instance(s).
Instance "helowin", status READY, has 1 handler(s) for this service...
Service "helowinXDB" has 1 instance(s).
Instance "helowin", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@71ba8a1c10fe ~]$
登录Oracle,进行测试:
[oracle@71ba8a1c10fe ~]$ whoami
oracle
[oracle@71ba8a1c10fe ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 12 18:35:15 2023
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn /as sysdba;
Connected.
SQL>
SQL> Show user;
USER is "SYS"
SQL>
查看数据库的当前状态:(默认会自动启动)
SQL> Select status from v$instance;
STATUS
------------
OPEN
SQL>
查看数据库版本信息:
SQL> Select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
查看数据库编码:
SQL> Select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL>
查看实例名(SID):
SQL> Select instance_name from v$instance;
INSTANCE_NAME
----------------
helowin
SQL>
查看用户表空间:
SQL> Select TABLESPACE_NAME from Dba_Tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
6 rows selected.
SQL>
查看用户表空间的物理文件的存放位置:
SQL> Select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/helowin/system01.dbf
/home/oracle/app/oracle/oradata/helowin/sysaux01.dbf
/home/oracle/app/oracle/oradata/helowin/undotbs01.dbf
/home/oracle/app/oracle/oradata/helowin/users01.dbf
/home/oracle/app/oracle/oradata/helowin/example01.dbf
SQL>
创建表空间(zhuohua_ts):
SQL> CREATE TABLESPACE zhuohua_ts DATAFILE '/home/oracle/app/oracle/oradata/helowin/zhuohua.dbf' SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL>
再次,查看用户表空间:
SQL> Select TABLESPACE_NAME from Dba_Tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
ZHUOHUA_TS
7 rows selected.
SQL>
查看用户表空间(zhuohua_ts)的物理文件的存放位置:
SQL> Select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/helowin/system01.dbf
/home/oracle/app/oracle/oradata/helowin/sysaux01.dbf
/home/oracle/app/oracle/oradata/helowin/undotbs01.dbf
/home/oracle/app/oracle/oradata/helowin/users01.dbf
/home/oracle/app/oracle/oradata/helowin/example01.dbf
/home/oracle/app/oracle/oradata/helowin/zhuohua.dbf
6 rows selected.
SQL>
创建数据库用户(happy),密码(mima),并指明此用户的默认表空间(zhuohua_ts):
SQL> Create user happy identified by mima default tablespace zhuohua_ts;
User created.
SQL>
给数据库用户(happy)进行赋权:
SQL> Grant connect, resource,dba to happy;
Grant succeeded.
SQL>
查看当前用户:
SQL> Show user;
USER is "SYS"
SQL>
切换到用户happy:
SQL> connect happy/mima;
Connected.
SQL>
SQL> Show user;
USER is "HAPPY"
SQL>
使用当前用户创建表:
SQL> Create table TABLE_1(ID int,NAME varchar2(10));
Table created.
SQL>
查看当前用户所拥有的表:
SQL> Select t.table_name from user_tables t;
TABLE_NAME
------------------------------
TABLE_1
SQL>
查看表TABLE_1的表结构:
SQL> desc TABLE_1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(10)
SQL>
###
Windows客户端使用Navicat连接容器“Rhel6_1”里的Oracle:
注释:数据库用户为happy,实例名(SID)为helowin,端口为1521
可以看到前面创建的表:
往表TABLE_1一次性插入多条记录:
Insert into TABLE_1 values (1,'小李');
Insert into TABLE_1 values (2,'小Lucky');
查询记录:
Select * from TABLE_1;
###
远程Linux客户端(CentOS6/Redhat6)使用Python3脚本获取容器“Rhel6_1”里的Oracle里的指定表数据:(结果只取一个值)
[root@Zabbix_server_01 ~]# cat xx.py
#coding=utf-8
import cx_Oracle
def query_data():
db=cx_Oracle.connect('happy/mima@192.168.168.154:1521/helowin')
cursor = db.cursor()
Sql_1 = "Select * from TABLE_1 where NAME = '小Lucky'"
cursor.execute(Sql_1)
Result_1 = cursor.fetchall()
for Key_1 in Result_1:
ID = Key_1[0]
NAME = Key_1[1]
print(ID)
print(type(ID))
db.commit() #把执行任务提交到数据库
db.close()
def func_main():
try:
query_data()
except Exception as e:
print(f"输出失败,原因: {e}")
if __name__ == "__main__":
func_main()
[root@Zabbix_server_01 ~]#
脚本运行的结果:
[root@Zabbix_server_01 ~]# python3 xx.py
2
<class 'int'>
[root@Zabbix_server_01 ~]#
######
在容器“Rhel6_1”里备份数据库:
[oracle@71ba8a1c10fe ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 12 20:27:26 2023
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> Connect system/helowin;
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL>
SQL> Show user;
USER is "SYSTEM"
SQL>
根据提示信息,修改数据库用户system的密码为mima2:
SQL> Conn /as sysdba;
Connected.
SQL>
SQL> Show user;
USER is "SYS"
SQL>
SQL> Alter user system identified by mima2;
User altered.
SQL>
切换到用户system:
SQL> Connect system/mima2;
Connected.
SQL>
SQL> Show user;
USER is "SYSTEM"
SQL>
###
切换到系统管理员root:(默认密码 helowin )
[oracle@71ba8a1c10fe /]$ su - root
Password:
[root@71ba8a1c10fe ~]#
创建备份文件的存放目录:
[root@71ba8a1c10fe ~]# mkdir -p /backup/
[root@71ba8a1c10fe ~]# chmod -R 777 /backup/
[root@71ba8a1c10fe ~]#
备份数据库表结构的脚本:
[root@71ba8a1c10fe ~]# cat backup_tables.sh
#!/bin/bash
rq=`date '+%Y%m%d%H%M'`
su - oracle -c "exp system/mima2 file=/backup/all_$rq.dmp log=/backup/tables.log full=y rows=n"
注释:
会备份所有表空间;
也包含了所有用户,以及用户的权限、所拥有的表;
但用户的表是没有数据的;
数据库用户system的密码为mima2
备份数据库用户happy的表、表数据:
[root@71ba8a1c10fe ~]# cat backup_happy_data.sh
#!/bin/bash
rq=`date '+%Y%m%d%H%M'`
su - oracle -c "exp happy/mima file=/backup/happy_$rq.dmp log=/backup/happy_data.log grants=y"
注释:
包含了指定用户所拥有的表、及其表中的数据;
用户happy的密码为mima
运行脚本备份数据库表结构:
[root@71ba8a1c10fe ~]# bash backup_tables.sh
运行脚本备份数据库用户happy的表、表数据:
[root@71ba8a1c10fe ~]# bash backup_happy_data.sh
备份完成:
[root@71ba8a1c10fe ~]# find /backup/ -type f
/backup/tables.log
/backup/all_202304122049.dmp
/backup/happy_data.log
/backup/happy_202304122049.dmp
[root@71ba8a1c10fe ~]#
[root@71ba8a1c10fe ~]# du -ah /backup/
4.0K /backup/tables.log
72K /backup/all_202304122049.dmp
4.0K /backup/happy_data.log
16K /backup/happy_202304122049.dmp
96K /backup/
[root@71ba8a1c10fe ~]#
######
######
使用镜像“registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest”创建并启动容器“Rhel6_2”:
[root@centos8 ~]# docker run -itd --name Rhel6_2 --restart=no -p 1522:1521 -v /opt/share:/home/dir_1 registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest
d6b576a864f27cbcf2adec8439e0d63339d91aabf8f76dacb01b89e8c24249a0
[root@centos8 ~]#
注释:
-itd : 以交互模式情况下后台运行。
--name : 指定容器名称。
-p 端口映射 : 1522是宿主机的端口,暴露给外部直接访问;1521是容器的端口。
--restart=no : 不让容器随宿主机的启动而启动;缺省时,就是不让容器随宿主机的启动而启动。
-v 挂载目录 : 这里是把宿主机的目录/opt/share挂载到容器的目录/home/dir_1;假如目录不存在,就会自动创建。
registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest : 镜像名称:版本号
在宿主机查看容器“Rhel6_2”是否会随宿主机的启动而启动:(不会自动启动)
[root@centos8 ~]# docker inspect Rhel6_2 | grep -i -A2 "RestartPolicy"
"RestartPolicy": {
"Name": "no",
"MaximumRetryCount": 0
[root@centos8 ~]#
在宿主机查看所有的容器:(无论是否正在运行)
[root@centos8 ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d6b576a864f2 registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest "/bin/sh -c '/home/o…" 2 minutes ago Up About a minute 0.0.0.0:1522->1521/tcp, :::1522->1521/tcp Rhel6_2
71ba8a1c10fe registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g:latest "/bin/sh -c '/home/o…" 24 hours ago Up 8 minutes 0.0.0.0:1521->1521/tcp, :::1521->1521/tcp Rhel6_1
[root@centos8 ~]#
注释:宿主机的TCP 1522端口,映射到容器“Rhel6_2”的TCP 1521端口。
###
进入容器“Rhel6_2”:
[root@centos8 ~]# docker exec -it Rhel6_2 /bin/bash
[oracle@d6b576a864f2 /]$
[oracle@d6b576a864f2 /]$ whoami
oracle
[oracle@d6b576a864f2 /]$
编辑系统用户oracle的环境变量:
[oracle@d6b576a864f2 /]$ vi ~/.bashrc
追加:
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH
[oracle@d6b576a864f2 /]$ cat ~/.bashrc
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# User specific aliases and functions
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH
[oracle@d6b576a864f2 /]$
刷新系统用户oracle的环境变量:
[oracle@d6b576a864f2 /]$ source ~/.bashrc
[oracle@d6b576a864f2 /]$
登录Oracle,进行测试:
[oracle@d6b576a864f2 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 13 18:11:46 2023
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> Connect system/helowin;
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL>
根据提示信息,修改数据库用户system的密码为mima2:
SQL> Conn /as sysdba;
Connected.
SQL>
SQL> Show user;
USER is "SYS"
SQL>
SQL> Alter user system identified by mima2;
User altered.
SQL>
切换到用户system:
SQL> Connect system/mima2;
Connected.
SQL>
SQL> Show user;
USER is "SYSTEM"
SQL>
###
切换到系统管理员root:(默认密码 helowin )
[oracle@d6b576a864f2 ~]$ su - root
Password:
[root@d6b576a864f2 ~]#
确认已上传容器“Rhel6_1”的备份文件到容器“Rhel6_2”:
[root@d6b576a864f2 ~]# find /backup/ -type f
/backup/all_202304122049.dmp
/backup/happy_202304122049.dmp
[root@d6b576a864f2 ~]#
设置目录权限:
[root@d6b576a864f2 ~]# chmod -R 777 /backup/
[root@d6b576a864f2 ~]#
先还原数据库的表结构:
[root@d6b576a864f2 ~]# su - oracle
[oracle@d6b576a864f2 ~]$ imp system/mima2 full=Y file=/backup/all_202304122049.dmp
注释:数据库用户system的密码为mima2
再还原数据库用户happy的表、表数据:
[oracle@d6b576a864f2 ~]$ imp happy/mima full=Y ignore=Y file=/backup/happy_202304122049.dmp
注释:数据库用户happy的密码为mima
登录Oracle,并切换到用户happy:
[oracle@d6b576a864f2 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 13 18:30:49 2023
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> Connect happy/mima;
Connected.
SQL>
SQL> Show user;
USER is "HAPPY"
SQL>
查看当前用户所拥有的表:
SQL> Select t.table_name from user_tables t;
TABLE_NAME
------------------------------
TABLE_1
SQL>
###
Windows客户端使用Navicat连接容器“Rhel6_2”里的Oracle:
注释:数据库用户为happy,实例名(SID)为helowin,端口为1522
可以看到前面创建的表:
查询记录:
Select * from TABLE_1;
往表TABLE_1一次性插入多条记录:
Insert into TABLE_1 values (3,'小明');
Insert into TABLE_1 values (4,'大Lucky');
Select * from TABLE_1;
###
远程Linux客户端(CentOS6/Redhat6)使用Python3脚本获取容器“Rhel6_2”里的Oracle里的指定表数据:(结果只取一个值)
[root@Zabbix_server_01 ~]# cat yy.py
#coding=utf-8
import cx_Oracle
def query_data():
db=cx_Oracle.connect('happy/mima@192.168.168.154:1522/helowin')
cursor = db.cursor()
Sql_1 = "Select * from TABLE_1 where ID=4"
cursor.execute(Sql_1)
Result_1 = cursor.fetchall()
for Key_1 in Result_1:
ID = Key_1[0]
NAME = Key_1[1]
print(NAME)
print(type(NAME))
db.commit() #把执行任务提交到数据库
db.close()
def func_main():
try:
query_data()
except Exception as e:
print(f"输出失败,原因: {e}")
if __name__ == "__main__":
func_main()
[root@Zabbix_server_01 ~]#
脚本运行的结果:
[root@Zabbix_server_01 ~]# python3 yy.py
大Lucky
<class 'str'>
[root@Zabbix_server_01 ~]#
笺注:
Docker宿主机的防火墙其实无需特意打开任何TCP端口:
[root@centos8 ~]# firewall-cmd --zone=public --list-ports
[root@centos8 ~]#
######
######
启用数据库的闪回功能:
登录Oracle:
[oracle@d6b576a864f2 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 5 14:29:18 2023
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> Conn /as sysdba;
Connected.
SQL> Show user;
USER is "SYS"
SQL>
此数据库支持闪回功能:
SQL> Select * from v$OPTION where parameter like 'Flashback%';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
Flashback Table
TRUE
Flashback Database
TRUE
Flashback Data Archive
TRUE
SQL>
此时数据库的闪回功能尚未启用:
SQL> Select log_mode,open_mode,flashback_on from v$database;
LOG_MODE OPEN_MODE FLASHBACK_ON
------------ -------------------- ------------------
NOARCHIVELOG READ WRITE NO
SQL>
尝试启用数据库的闪回功能:
SQL> Alter database flashback on;
Alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.
SQL>
根据错误提示进行解决:
SQL> Alter database archivelog;
Alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
SQL>
根据错误提示进行解决:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2213776 bytes
Variable Size 402655344 bytes
Database Buffers 1191182336 bytes
Redo Buffers 7360512 bytes
Database mounted.
SQL>
再次,尝试启用数据库的闪回功能:
SQL> Alter database archivelog;
Database altered.
SQL> Alter database flashback on;
Database altered.
SQL>
此时数据库的闪回功能已经启用:
SQL> Select log_mode,open_mode,flashback_on from v$database;
LOG_MODE OPEN_MODE FLASHBACK_ON
------------ -------------------- ------------------
ARCHIVELOG MOUNTED YES
SQL>
在宿主机重启容器“Rhel6_2”:
[root@centos8 ~]# docker restart Rhel6_2
Rhel6_2
[root@centos8 ~]#
###
测试容器“Rhel6_2”的Oracle11gR2的Flashback功能:
Windows客户端使用Navicat连接容器“Rhel6_2”里的Oracle:
注释:数据库用户为happy,实例名(SID)为helowin,端口为1522
当前具体时间:
[oracle@d6b576a864f2 ~]$ date
Mon Jun 5 15:01:31 CST 2023
[oracle@d6b576a864f2 ~]$
查看表TABLE_1的当前表数据:
Select * from TABLE_1;
删除表TABLE_1的N条数据:
Delete from TABLE_1 WHERE NAME LIKE '%Lucky';
更改表TABLE_1的N条数据:
Update TABLE_1 SET Name = 'Zhuohua' WHERE ID = 3;
往表TABLE_1插入一条记录:
Insert into TABLE_1 values (5,'Oracle');
再次,查看表TABLE_1的当前表数据:
Select * from TABLE_1;
###
选择一个时间点,查看表TABLE_1中的数据是否为自己想要的:
Select * from TABLE_1 as of timestamp to_timestamp('2023-06-05 15:01:00','yyyy-mm-ddhh24:mi:ss');
启用表TABLE_1的行移动功能:
Alter table TABLE_1 enable row movement;
恢复表TABLE_1的数据到指定的时间点:
Flashback table TABLE_1 to timestamp to_timestamp('2023-06-05 15:01:00','yyyy-mm-ddhh24:mi:ss');
表TABLE_1的数据恢复成功:
备注:不会影响其他的表。
数据恢复成功后,关闭表TABLE_1的行移动功能:
Alter table TABLE_1 disable row movement;
相关文章:
Oracle Linux6.9安装Oracle11gR2
Python3脚本管理Oracle11gR2
Oracle11gR2恢复已删除或更改的数据
Oracle11gR2的备份与还原
CentOS8_在Docker中安装Oracle19c
CentOS8_在Docker中限制容器可用的CPU个数和内存量 |