管理多租户环境之PDB快照
admin
2024-04-15 20:25:10
0次
PDB 快照
快照是数据库基于时间点的完整镜像，主要用于快速恢复和PDB复制。
利用PDB快照创建新PDB的语法如下：
[oracle@oracle-db-19c ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 2 09:45:45 2022
Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0SQL> show con_nameCON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB1 READ WRITE NO4 PDB2 MOUNTED5 CNDBAPDB MOUNTED6 CNDBAPDB3 MOUNTED7 CNDBAPDB2 MOUNTED8 CNDBAPDB4_FRESH MOUNTED
SQL> show con_name;CON_NAME
------------------------------
CDB$ROOT
SQL>SQL> alter session set container=PDB1;Session altered.SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------3 PDB1 READ WRITE NO
SQL> alter pluggable database snapshot CNDBAPDB5_20221202;Pluggable database altered.SQL>
SQL> conn / as sysdba
Connected.
SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB1 READ WRITE NO4 PDB2 MOUNTED5 CNDBAPDB MOUNTED6 CNDBAPDB3 MOUNTED7 CNDBAPDB2 MOUNTED8 CNDBAPDB4_FRESH MOUNTED
SQL>
SQL> CREATE PLUGGABLE DATABASE CNDBAPDB6 FROM PDB1 USING SNAPSHOT CNDBAPDB5_20221202;Pluggable database created.SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 PDB1 READ WRITE NO4 PDB2 MOUNTED5 CNDBAPDB MOUNTED6 CNDBAPDB3 MOUNTED7 CNDBAPDB2 MOUNTED8 CNDBAPDB4_FRESH MOUNTED9 CNDBAPDB6 MOUNTED
SQL> 可以手动生成快照，也可以指定每隔多长时间自动生成一次快照，最多可以存储8个快照。
创建PDB快照
1. 设置自动创建PDB快照
可以设置每隔一段时间自动创建一个PDB。
EVERY snapshot_interval [ MINUTES | HOURS ]
查看当前PDB快照模式
SQL>
SQL> SELECT SNAPSHOT_MODE "S_MODE",SNAPSHOT_INTERVAL/60 "SNAP_INT_HRS" FROM DBA_PDBS;S_MODE SNAP_INT_HRS
------ ------------
MANUALSQL> 设置每隔24小时就创建一个快照，需要在相应的PDB下执行命令：
SQL>
SQL> ALTER PLUGGABLE DATABASE SNAPSHOT MODE EVERY 24 HOURS;Pluggable database altered.SQL>再次查看快照模式，代码如下：
SQL> SELECT SNAPSHOT_MODE "S_MODE",SNAPSHOT_INTERVAL/60 "SNAP_INT_HRS" FROM DBA_PDBS;S_MODE SNAP_INT_HRS
------ ------------
AUTO 24SQL> 2.手动创建PDB快照
SQL> show con_name;CON_NAME
------------------------------
CDB$ROOT
SQL> SQL> alter session set container=PDB1;Session altered.SQL> show pdbs;CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------3 PDB1 READ WRITE NO
SQL> alter pluggable database snapshot CNDBAPDB5_20221202;Pluggable database altered.SQL> 查看快照信息
SQL> set LINESIZE 150
SQL> col con_name format a10
SQL> col snapshot_name format a30
SQL> col full_snapshot_path format a55
SQL> col snap_scn default
SQL> select con_id,con_name,snapshot_name,snapshot_scn as snap_scn,full_snapshot_path from dba_pdb_snapshots order by snap_scn;CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH
------ ---------- ------------------------------ ---------- -------------------------------------------------------3 PDB1 CNDBAPDB5_20221202 18991475 /u02/oradata/snap_2714456025_18991475.pdb3 PDB1 SNAP_2714456025_1122376588 18997049 /u02/oradata/snap_2714456025_18997049.pdbSQL>删除快照
- 可以一个一个地删除快照，也可以一次性删除所有快照。
SQL>
SQL> select con_id,con_name,snapshot_name,snapshot_scn as snap_scn,full_snapshot_path from dba_pdb_snapshots order by snap_scn;CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH
------ ---------- ------------------------------ ---------- -------------------------------------------------------3 PDB1 CNDBAPDB5_20221202 18991475 /u02/oradata/snap_2714456025_18991475.pdb3 PDB1 SNAP_2714456025_1122376588 18997049 /u02/oradata/snap_2714456025_18997049.pdbSQL> ALTER PLUGGABLE DATABASE DROP SNAPSHOT SNAP_2714456025_1122376588;Pluggable database altered.SQL> select con_id,con_name,snapshot_name,snapshot_scn as snap_scn,full_snapshot_path from dba_pdb_snapshots order by snap_scn;CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH
------ ---------- ------------------------------ ---------- -------------------------------------------------------3 PDB1 CNDBAPDB5_20221202 18991475 /u02/oradata/snap_2714456025_18991475.pdbSQL> - 删除所有快照，将MAX_PDB_SNAPSHOTS参数设置为0就会自动删除该PDB的所有快照，删除速度也快很多。
SQL> select con_id,con_name,snapshot_name,snapshot_scn as snap_scn,full_snapshot_path from dba_pdb_snapshots order by snap_scn;CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN FULL_SNAPSHOT_PATH
------ ---------- ------------------------------ ---------- -------------------------------------------------------3 PDB1 CNDBAPDB5_20221202 18991475 /u02/oradata/snap_2714456025_18991475.pdbSQL> ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=0;Pluggable database altered.SQL> select con_id,con_name,snapshot_name,snapshot_scn as snap_scn,full_snapshot_path from dba_pdb_snapshots order by snap_scn;no rows selectedSQL> ALTER PLUGGABLE DATABASE SET MAX_PDB_SNAPSHOTS=8;Pluggable database altered.SQL> 监控CDB和PDB
- 容器数据对象，包括：
- v$视图
- GV$视图
- CDB_视图
- DBA_HIST*视图
容器数据对象用于显示关于多租户的相关信息。每个容器数据对象通过CON_ID 来区分不同的容器
- {CDB|DBA}_PDBS
显示与CDB相关的PDB的信息，包括每个PDB的状态
SQL> set pagesize 200
SQL> set linesize 200
SQL> desc CDB_PDBS;Name Null? Type----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------PDB_ID NOT NULL NUMBERPDB_NAME NOT NULL VARCHAR2(128)DBID NOT NULL NUMBERCON_UID NOT NULL NUMBERGUID RAW(16)STATUS VARCHAR2(10)CREATION_SCN NUMBERVSN NUMBERLOGGING VARCHAR2(9)FORCE_LOGGING VARCHAR2(39)FORCE_NOLOGGING VARCHAR2(3)APPLICATION_ROOT VARCHAR2(3)APPLICATION_PDB VARCHAR2(3)APPLICATION_SEED VARCHAR2(3)APPLICATION_ROOT_CON_ID NUMBERIS_PROXY_PDB VARCHAR2(3)CON_ID NOT NULL NUMBERUPGRADE_PRIORITY NUMBERAPPLICATION_CLONE VARCHAR2(3)FOREIGN_CDB_DBID NUMBERUNPLUG_SCN NUMBERFOREIGN_PDB_ID NUMBERCREATION_TIME NOT NULL DATEREFRESH_MODE VARCHAR2(6)REFRESH_INTERVAL NUMBERTEMPLATE VARCHAR2(3)LAST_REFRESH_SCN NUMBERTENANT_ID VARCHAR2(255)SNAPSHOT_MODE VARCHAR2(6)SNAPSHOT_INTERVAL NUMBERCREDENTIAL_NAME VARCHAR2(262)SQL> desc DBA_PDBS;Name Null? Type----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------PDB_ID NOT NULL NUMBERPDB_NAME NOT NULL VARCHAR2(128)DBID NOT NULL NUMBERCON_UID NOT NULL NUMBERGUID RAW(16)STATUS VARCHAR2(10)CREATION_SCN NUMBERVSN NUMBERLOGGING VARCHAR2(9)FORCE_LOGGING VARCHAR2(39)FORCE_NOLOGGING VARCHAR2(3)APPLICATION_ROOT VARCHAR2(3)APPLICATION_PDB VARCHAR2(3)APPLICATION_SEED VARCHAR2(3)APPLICATION_ROOT_CON_ID NUMBERIS_PROXY_PDB VARCHAR2(3)CON_ID NOT NULL NUMBERUPGRADE_PRIORITY NUMBERAPPLICATION_CLONE VARCHAR2(3)FOREIGN_CDB_DBID NUMBERUNPLUG_SCN NUMBERFOREIGN_PDB_ID NUMBERCREATION_TIME NOT NULL DATEREFRESH_MODE VARCHAR2(6)REFRESH_INTERVAL NUMBERTEMPLATE VARCHAR2(3)LAST_REFRESH_SCN NUMBERTENANT_ID VARCHAR2(255)SNAPSHOT_MODE VARCHAR2(6)SNAPSHOT_INTERVAL NUMBERCREDENTIAL_NAME VARCHAR2(262)SQL> - CDB_PROPERTIES 显示CDB中每个容器的属性
SQL>
SQL> desc CDB_PROPERTIESName Null? Type----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------PROPERTY_NAME VARCHAR2(128)PROPERTY_VALUE VARCHAR2(4000)DESCRIPTION VARCHAR2(4000)CON_ID NUMBERSQL> - {CDB|DBA}_PDB_HISTORY 显示每个PDB的历史记录信息
SQL>
SQL> desc DBA_PDB_HISTORYName Null? Type----------------------------------------- -------- ----------------------------PDB_NAME NOT NULL VARCHAR2(128)PDB_ID NOT NULL NUMBERPDB_DBID NOT NULL NUMBERPDB_GUID NOT NULL RAW(16)OP_SCNBAS NOT NULL NUMBEROP_SCNWRP NOT NULL NUMBEROP_TIMESTAMP NOT NULL DATEOPERATION NOT NULL VARCHAR2(16)DB_VERSION NOT NULL NUMBERCLONED_FROM_PDB_NAME VARCHAR2(128)CLONED_FROM_PDB_DBID NUMBERCLONED_FROM_PDB_GUID RAW(16)DB_NAME VARCHAR2(128)DB_UNIQUE_NAME VARCHAR2(128)DB_DBID NUMBERCLONETAG VARCHAR2(128)DB_VERSION_STRING VARCHAR2(204)SQL> desc CDB_PDB_HISTORYName Null? Type----------------------------------------- -------- ----------------------------PDB_NAME NOT NULL VARCHAR2(128)PDB_ID NOT NULL NUMBERPDB_DBID NOT NULL NUMBERPDB_GUID NOT NULL RAW(16)OP_SCNBAS NOT NULL NUMBEROP_SCNWRP NOT NULL NUMBEROP_TIMESTAMP NOT NULL DATEOPERATION NOT NULL VARCHAR2(16)DB_VERSION NOT NULL NUMBERCLONED_FROM_PDB_NAME VARCHAR2(128)CLONED_FROM_PDB_DBID NUMBERCLONED_FROM_PDB_GUID RAW(16)DB_NAME VARCHAR2(128)DB_UNIQUE_NAME VARCHAR2(128)DB_DBID NUMBERCLONETAG VARCHAR2(128)DB_VERSION_STRING VARCHAR2(204)CON_ID NUMBERSQL> - CDB_HIST_PDB_INSTANCE | DBA_HIST_PDB_INSTANCE 显示PDB和实例的负载信息库。
SQL> desc CDB_HIST_PDB_INSTANCEName Null? Type----------------------------------------- -------- ----------------------------DBID NOT NULL NUMBERINSTANCE_NUMBER NOT NULL NUMBERSTARTUP_TIME NOT NULL TIMESTAMP(3)CON_DBID NOT NULL NUMBEROPEN_TIME NOT NULL TIMESTAMP(3)OPEN_MODE VARCHAR2(16)PDB_NAME VARCHAR2(128)CON_ID NUMBERSNAP_ID NUMBERSTARTUP_TIME_TZ TIMESTAMP(3) WITH TIME ZONEOPEN_TIME_TZ TIMESTAMP(3) WITH TIME ZONESQL> desc DBA_HIST_PDB_INSTANCEName Null? Type----------------------------------------- -------- ----------------------------DBID NOT NULL NUMBERINSTANCE_NUMBER NOT NULL NUMBERSTARTUP_TIME NOT NULL TIMESTAMP(3)CON_DBID NOT NULL NUMBEROPEN_TIME NOT NULL TIMESTAMP(3)OPEN_MODE VARCHAR2(16)PDB_NAME VARCHAR2(128)CON_ID NUMBERSNAP_ID NUMBERSTARTUP_TIME_TZ TIMESTAMP(3) WITH TIME ZONEOPEN_TIME_TZ TIMESTAMP(3) WITH TIME ZONESQL> - {CDB|DBA}_PDB_SAVED_STATES 显示CDB中当前保存的PDB的状态的信息。
SQL>
SQL> desc CDB_PDB_SAVED_STATESName Null? Type----------------------------------------- -------- ----------------------------CON_ID NOT NULL NUMBERCON_NAME NOT NULL VARCHAR2(128)INSTANCE_NAME NOT NULL VARCHAR2(128)CON_UID NOT NULL NUMBERGUID RAW(16)STATE VARCHAR2(14)RESTRICTED VARCHAR2(3)SQL> desc DBA_PDB_SAVED_STATESName Null? Type----------------------------------------- -------- ----------------------------CON_ID NOT NULL NUMBERCON_NAME NOT NULL VARCHAR2(128)INSTANCE_NAME NOT NULL VARCHAR2(128)CON_UID NOT NULL NUMBERGUID RAW(16)STATE VARCHAR2(14)RESTRICTED VARCHAR2(3)SQL> - {CDB|DBA}_APPLICATIONS 记录应用程序容器中的所有应用程序。
SQL>
SQL> desc CDB_APPLICATIONSName Null? Type----------------------------------------- -------- ----------------------------APP_NAME VARCHAR2(128)APP_ID NUMBERAPP_VERSION VARCHAR2(30)APP_STATUS VARCHAR2(12)APP_IMPLICIT VARCHAR2(1)APP_CAPTURE_SERVICE VARCHAR2(64)APP_CAPTURE_MODULE VARCHAR2(64)APP_CAPTURE_ERROR VARCHAR2(1)CON_ID NUMBERSQL> desc DBA_APPLICATIONSName Null? Type----------------------------------------- -------- ----------------------------APP_NAME VARCHAR2(128)APP_ID NUMBERAPP_VERSION VARCHAR2(30)APP_STATUS VARCHAR2(12)APP_IMPLICIT VARCHAR2(1)APP_CAPTURE_SERVICE VARCHAR2(64)APP_CAPTURE_MODULE VARCHAR2(64)APP_CAPTURE_ERROR VARCHAR2(1)SQL> - CDB_APP_STATEMENTS | DBA_APP_STATEMENTS 记录应用程序容器中应用程序安装、升级和补丁操作的所有语句。
SQL> DESC CDB_APP_STATEMENTSName Null? Type----------------------------------------- -------- ----------------------------ORIGIN_CON_ID NUMBERSTATEMENT_ID NOT NULL NUMBERCAPTURE_TIME NOT NULL DATEAPP_STATEMENT CLOBAPP_NAME VARCHAR2(128)APP_STATUS VARCHAR2(12)PATCH_NUMBER NUMBERVERSION_NUMBER NUMBERSESSION_ID NUMBEROPCODE NOT NULL NUMBERCON_ID NUMBERSQL> DESC DBA_APP_STATEMENTSName Null? Type----------------------------------------- -------- ----------------------------ORIGIN_CON_ID NUMBERSTATEMENT_ID NOT NULL NUMBERCAPTURE_TIME NOT NULL DATEAPP_STATEMENT CLOBAPP_NAME VARCHAR2(128)APP_STATUS VARCHAR2(12)PATCH_NUMBER NUMBERVERSION_NUMBER NUMBERSESSION_ID NUMBEROPCODE NOT NULL NUMBERSQL>- {CDB|DBA}_APP_PATCHES 记录应用程序容器中所有应用程序的补丁信息
SQL>
SQL> DESC CDB_APP_PATCHESName Null? Type----------------------------------------- -------- ----------------------------APP_NAME VARCHAR2(128)PATCH_NUMBER NUMBERPATCH_MIN_VERSION VARCHAR2(30)PATCH_STATUS VARCHAR2(10)PATCH_COMMENT VARCHAR2(4000)PATCH_CHECKSUM NUMBERCON_ID NUMBERSQL> DESC DBA_APP_PATCHESName Null? Type----------------------------------------- -------- ----------------------------APP_NAME VARCHAR2(128)PATCH_NUMBER NUMBERPATCH_MIN_VERSION VARCHAR2(30)PATCH_STATUS VARCHAR2(10)PATCH_COMMENT VARCHAR2(4000)PATCH_CHECKSUM NUMBERSQL> - {CDB | DBA}_CDB_RSRC_PLANS 显示所有关于CDB资源计划的信息
SQL> desc CDB_CDB_RSRC_PLANSName Null? Type----------------------------------------- -------- ----------------------------PLAN_ID NOT NULL NUMBERPLAN VARCHAR2(128)COMMENTS VARCHAR2(2000)STATUS VARCHAR2(128)MANDATORY VARCHAR2(3)CON_ID NUMBERSQL> desc DBA_CDB_RSRC_PLANSName Null? Type----------------------------------------- -------- ----------------------------PLAN_ID NOT NULL NUMBERPLAN VARCHAR2(128)COMMENTS VARCHAR2(2000)STATUS VARCHAR2(128)MANDATORY VARCHAR2(3)SQL>- CDB_CDB_RSRC_PLAN_DIRECTIVES | DBA_CDB_RSRC_PLAN_DIRECTIVES
SQL> desc DBA_CDB_RSRC_PLAN_DIRECTIVESName Null? Type----------------------------------------- -------- ----------------------------PLAN VARCHAR2(128)PLUGGABLE_DATABASE VARCHAR2(128)PROFILE VARCHAR2(128)DIRECTIVE_TYPE VARCHAR2(30)SHARES NUMBERUTILIZATION_LIMIT NUMBERPARALLEL_SERVER_LIMIT NUMBERMEMORY_MIN NUMBERMEMORY_LIMIT NUMBERCOMMENTS VARCHAR2(2000)STATUS VARCHAR2(128)MANDATORY VARCHAR2(3)SQL> desc CDB_CDB_RSRC_PLAN_DIRECTIVESName Null? Type----------------------------------------- -------- ----------------------------PLAN VARCHAR2(128)PLUGGABLE_DATABASE VARCHAR2(128)PROFILE VARCHAR2(128)DIRECTIVE_TYPE VARCHAR2(30)SHARES NUMBERUTILIZATION_LIMIT NUMBERPARALLEL_SERVER_LIMIT NUMBERMEMORY_MIN NUMBERMEMORY_LIMIT NUMBERCOMMENTS VARCHAR2(2000)STATUS VARCHAR2(128)MANDATORY VARCHAR2(3)CON_ID NUMBERSQL> - PDB_ALERTS 记录PDB的告警信息
SQL>
SQL> desc PDB_ALERTSName Null? Type----------------------------------------- -------- ----------------------------TIME NOT NULL TIMESTAMP(6)NAME NOT NULL VARCHAR2(128)CAUSE_NO NOT NULL NUMBERTYPE_NO NOT NULL NUMBERERROR NUMBERLINE NOT NULL NUMBERMESSAGE NOT NULL VARCHAR2(4000)STATUS NUMBERACTION VARCHAR2(4000)SQL> - PDB_PLUG_IN_VIOLATIONS 显示
SQL> desc PDB_PLUG_IN_VIOLATIONSName Null? Type----------------------------------------- -------- ----------------------------TIME NOT NULL TIMESTAMP(6)NAME NOT NULL VARCHAR2(128)CAUSE VARCHAR2(64)TYPE VARCHAR2(9)ERROR_NUMBER NUMBERLINE NOT NULL NUMBERMESSAGE NOT NULL VARCHAR2(4000)STATUS VARCHAR2(9)ACTION VARCHAR2(4000)CON_ID NUMBERSQL> 相关内容
热门资讯
linux入门---制作进度条
了解缓冲区 我们首先来看看下面的操作： 我们首先创建了一个文件并在这个文件里面添加了...
C++ 机房预约系统（六）：学...
8、 学生模块 8.1 学生子菜单、登录和注销 实现步骤： 在Student.cpp的...
A.机器学习入门算法（三）：基...
机器学习算法（三）：K近邻(k-nearest neigh...
数字温湿度传感器DHT11模块...
模块实例https://blog.csdn.net/qq_38393591/article/deta...
有限元三角形单元的等效节点力
文章目录前言一、重新复习一下有限元三角形单元的理论1、三角形单元的形函数（Nÿ...
Redis 所有支持的数据结构...
Redis 是一种开源的基于键值对存储的 NoSQL 数据库，支持多种数据结构。以下是...
win下pytorch安装—c...
安装目录一、cuda安装1.1、cuda版本选择1.2、下载安装二、cudnn安装三、pytorch...
MySQL基础-多表查询
文章目录MySQL基础-多表查询一、案例及引入1、基础概念2、笛卡尔积的理解二、多表查询的分类1、等...
keil调试专题篇
调试的前提是需要连接调试器比如STLINK。 然后点击菜单或者快捷图标均可进入调试模式。 如果前面...
MATLAB | 全网最详细网...
一篇超超超长，超超超全面网络图绘制教程，本篇基本能讲清楚所有绘制要点&#...
IHome主页 - 让你的浏览...
随着互联网的发展，人们越来越离不开浏览器了。每天上班、学习、娱乐，浏览器...
TCP 协议
一、TCP 协议概念 TCP即传输控制协议（Transmission Control ...
营业执照的经营范围有哪些
营业执照的经营范围有哪些 经营范围是指企业可以从事的生产经营与服务项目，是进行公司注册...
C++ 可变体(variant...
一、可变体(variant) 基础用法 Union的问题： 无法知道当前使用的类型是什...
血压计语音芯片，电子医疗设备声...
语音电子血压计是带有语音提示功能的电子血压计，测量前至测量结果全程语音播报...
MySQL OCP888题解0...
文章目录1、原题1.1、英文原题1.2、答案2、题目解析2.1、题干解析2.2、选项解析3、知识点3...
【2023-Pytorch-检...
（肆十二想说的一些话）Yolo这个系列我们已经更新了大概一年的时间，现在基本的流程也走走通了，包含数...
实战项目：保险行业用户分类
这里写目录标题1、项目介绍1.1 行业背景1.2 数据介绍2、代码实现导入数据探索数据处理列标签名异...
记录--我在前端干工地(thr...
这里给大家分享我在网上总结出来的一些知识，希望对大家有所帮助 前段时间接触了Th...
43 openEuler搭建A...
文章目录43 openEuler搭建Apache服务器-配置文件说明和管理模块43.1 配置文件说明...