管理多租户环境之PDB快照
创始人
2024-03-12 20:18:59
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>
上一篇：搜索技术——遗传算法
下一篇：Bootstrap学习（十一）
相关内容
热门资讯
AWSECS：访问外部网络时出...
如果您在AWS ECS中部署了应用程序，并且该应用程序需要访问外部网络，但是无法正常访问，可能是因为...
AWSElasticBeans...
在Dockerfile中手动配置nginx反向代理。例如，在Dockerfile中添加以下代码：FR...
银河麒麟V10SP1高级服务器...
银河麒麟高级服务器操作系统简介： 银河麒麟高级服务器操作系统V10是针对企业级关键业务...
北信源内网安全管理卸载
北信源内网安全管理是一款网络安全管理软件，主要用于保护内网安全。在日常使用过程中，卸载该软件是一种常...
AWR报告解读
WORKLOAD REPOSITORY PDB report (PDB snapshots) AW...
AWS管理控制台菜单和权限
要在AWS管理控制台中创建菜单和权限，您可以使用AWS Identity and Access Ma...
ToDesk 远程工具安装及...
目录 前言 ToDesk 优势 ToDesk 下载安装 ToDesk 功能展示 文件传输 设备链接 ...
群晖外网访问终极解决方法：IP...
写在前面的话 受够了群晖的quickconnet的小水管了，急需一个新的解决方法&#x...
不能访问光猫的的管理页面
光猫是现代家庭宽带网络的重要组成部分，它可以提供高速稳定的网络连接。但是，有时候我们会遇到不能访问光...
Azure构建流程（Power...
这可能是由于配置错误导致的问题。请检查构建流程任务中的“发布构建制品”步骤，确保正确配置了“Arti...