Oracle 18c新特性-PDB快照轮播(Snapshot Carousel)

时间:2020-04-30 14:49来源:计算机教程
SQL SELECT r.CON_ID, p.PDB_NAME, PROPERTY_NAME, PROPERTY_VALUE AS value, DESCRIPTION FROM CDB_PROPERTIES r, CDB_PDBS p WHERE r.CON_ID = p.CON_ID AND PROPERTY_NAME LIKE 'MAX_PDB%' AND description like 'maximum%' ORDER BY PROPERTY_NAME; CON_I
SQL SELECT r.CON_ID, p.PDB_NAME, PROPERTY_NAME, PROPERTY_VALUE AS value, DESCRIPTION FROM CDB_PROPERTIES r, CDB_PDBS p WHERE r.CON_ID = p.CON_ID AND PROPERTY_NAME LIKE 'MAX_PDB%' AND description like 'maximum%' ORDER BY PROPERTY_NAME; CON_ID PDB_NAME PROPERTY_NAME VALUE DESCRIPTION---------- -------------------- -------------------- -------------------- ---------------------------------------------------------------------- 3 ORCLPDB1 MAX_PDB_SNAPSHOTS maximum number of snapshots for a given PDB

数据库环境

演示了上述功能之后,还有一个功能,就是我们可以根据快照创建pdb。

在上面的示例中,我们创建了一个单一的基线样本,并且指定了相应的时间范围,基线的名称及保留期限等。那么在这个时间范围内的相应的快照会被保留,同时这个基线可以用于后续在发现性能问题的时候进行比对。

这个功能对于测试人员非常有用,例如移动电信现在有BCV环境,专门用于经分抽数及测试,而这个BCV环境是底层同步的一个克隆。使用18c数据库,我们就可以直接创建快照,然后使用快照克隆一个新数据库形成BCV环境给经分抽数及测试使用了。

BEGIN DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE ( old_baseline_name = 'peak_baseline', new_baseline_name = 'peak_mondays', dbid = 41924548);END;/--验证结果SQL SELECT baseline_id, 2 baseline_name, 3 baseline_type, 4 expiration, 5 creation_time 6 FROM dba_hist_baseline;BASELINE_ID BASELINE_NAME BASELINE_TYPE EXPIRATION CREATION_----------- ----------------------- ------------- ---------- --------- 1 peak_mondays STATIC 10 14-APR-17 0 SYSTEM_MOVING_WINDOW MOVING_WINDOW 07-APR-17
[oracle@oracle-18c-vagrant ORCLPDB3]$ ls -lrt snap*-rw-r--r--. 1 oracle oinstall 179134736 Oct 8 23:13 snap_2091710291_2830531.pdb-rw-r--r--. 1 oracle oinstall 179167158 Oct 8 23:18 snap_2091710291_2832545.pdb-rw-r--r--. 1 oracle oinstall 179285979 Oct 8 23:23 snap_2091710291_2833104.pdb-rw-r--r--. 1 oracle oinstall 179284365 Oct 8 23:28 snap_2091710291_2833687.pdb-rw-r--r--. 1 oracle oinstall 179302205 Oct 8 23:33 snap_2091710291_2834965.pdb-rw-r--r--. 1 oracle oinstall 179315484 Oct 8 23:38 snap_2091710291_2836695.pdb-rw-r--r--. 1 oracle oinstall 115724288 Oct 8 23:43 snap_2091710291_2837222.pdb
SQL @/rdbms/admin/awrinfo.sqlThis script will report general AWR information~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Specify the Report File Name~~~~~~~~~~~~~~~~~~~~~~~~~~~~The default report file name is awrinfo.txt. To use this name,press return to continue, otherwise enter an alternative.Enter value for report_name:Using the report name awrinfo.txtNo errors. -- Author : LeshamiNo errors. -- Blog : ~~~~~~~~~~~~~~~ -- QQ : 645746311 AWR INFO Report~~~~~~~~~~~~~~~Report generated at19:48:53 on Apr 14, 2017 ( Friday ) in Timezone  08:00Warning: Non Default AWR Setting!--------------------------------------------------------------------------------Snapshot interval is 60 minutes and Retention is 8 days DB_ID DB Name HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR------------ ------- ----------------------- ---- ----------------- ------------ ---* 41924548 ORA11G ydq - Linux x86 64-bit 1 15:18:26 (04/07) 617410 NO########################################################(I) AWR Snapshots Information########################################################*****************************************************(1a) SYSAUX usage - Schema breakdown (dba_segments)*****************************************************|| Total SYSAUX size 617.4 MB ( 2% of 32,768.0 MB MAX with AUTOEXTEND ON )|| Schema SYS occupies 206.6 MB ( 33.5% )| Schema XDB occupies 157.7 MB ( 25.5% )| Schema APEX_030200 occupies 85.4 MB ( 13.8% )| Schema MDSYS occupies 73.9 MB ( 12.0% )
SQL alter session set container=CDB$ROOT;Session altered.SQL create pluggable database ORCLPDB4 from ORCLPDB3 using snapshot SNAP_2091710291_989020084 create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB4';Pluggable database createdSQL alter pluggable database ORCLPDB4 open;Pluggable database altered.SQL alter session set container=ORCLPDB4;Session altered.SQL select count(1) from a1; COUNT(1)---------- 72897

## 四、生成AWR报告

当然还有一个很好用的功能就是我们在创建pdb的过程中把他设置成15分钟产生一个快照。当然配置这个功能有一个先决条件:CDB必须处于本地undo模式.

SQL @$ORACLE_HOME/rdbms/admin/awrrpti.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 ORCLPDB1 SNAP_2953839490_989014667 2821702 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2821702.pdb 3 ORCLPDB1 PDB_SNAP 2823303 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2823303.pdb 3 ORCLPDB1 SNAP_2953839490_989015970 2823679 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2823679.pdb[oracle@oracle-18c-vagrant ORCLPDB1]$ ls -lshtotal 1.3G4.0K drwxr-x---. 8 oracle oinstall 4.0K Oct 8 22:39 ORCLCDB171M -rw-r--r--. 1 oracle oinstall 171M Oct 8 22:18 snap_2953839490_2821702.pdb171M -rw-r--r--. 1 oracle oinstall 171M Oct 8 22:38 snap_2953839490_2823303.pdb171M -rw-r--r--. 1 oracle oinstall 171M Oct 8 22:40 snap_2953839490_2823679.pdb371M -rw-r-----. 1 oracle oinstall 371M Oct 8 22:39 sysaux01.dbf271M -rw-r-----. 1 oracle oinstall 271M Oct 8 22:39 system01.dbf 56K -rw-r-----. 1 oracle oinstall 63M Oct 1 23:21 temp01.dbf101M -rw-r-----. 1 oracle oinstall 101M Oct 8 22:39 undotbs01.dbf5.1M -rw-r-----. 1 oracle oinstall 5.1M Oct 8 22:39 users01.dbf
--查看当前的保留策略,如下,为缺省值,采集间隔为1小时,保留8天SQL select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL---------- -------------------- ------------------ ---------- 41924548  00000 01:00:00.0  00008 00:00:00.0 DEFAULT--下面将其修改为保留5天,采集间隔为30分钟,topnsql为50条BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention = 10*24*60, interval = 30, topnsql = 50, dbid = 41924548);END;/--查看修改后的结果SQL select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL---------- -------------------- ------------------ ---------- 41924548  00000 00:30:00.0  00010 00:00:00.0 50

我们可以做个实验测试一下。在生成快照之前创建一个表插入点数据,这里上一次快照是23:43分。

六、更多参考

以上所述是小编给大家介绍的Oracle 18c新特性-PDB快照轮播,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持!

DBA_HIST_IOSTAT_DETAIL 按未见类型和功能来统计的历史I/O信息

SQL create pluggable database ORCLPDB3 from ORCLPDB1 file_name_convert=('ORCLPDB1','ORCLPDB3') snapshot mode every 5 minutes;Pluggable database created.

使用以下过程手工创建快照,创建完成后可在数据字典DBA_HIST_SNAPSHOT查看所有快照相关信息。

默认情况下是手动创建快照,我们可以把它改成自动创建。

在上面的示例中,创建了一个范围为160-161的,名字为peak_baseline的基线,保留时间为10天。在超过10天后,该基线会被删除,相应的快照也被删除。如果为指定失效期,则该基线以及相应的快照被永久保留。

vnsc5858威尼斯城官网,手动创建快照,可以自己指定名字,也可以使用系统自动生成的名字。通过查看DBA_PDB_SNAPSHOTS视图,可以找到快照存放的路径及创建快照的SCN号。

--生成比较的AWR报告

SQL alter pluggable database set max_pdb_snapshots=8;Pluggable database alteredSQL alter pluggable database snapshot pdb_snap;Pluggable database altered.SQL SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN; CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH---------- -------------------- ------------------------- ---------- ------------- --------------------------------------------------------------------------- 3 ORCLPDB1 PDB_SNAP 2825283 1539010328 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2825283.pdbSQL ![oracle@oracle-18c-vagrant ~]$ mkdir -p /opt/oracle/oradata/ORCLCDB/ORCLPDB2SQL create pluggable database ORCLPDB2 from ORCLPDB1 using snapshot PDB_SNAP create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2'; create pluggable database ORCLPDB2 from ORCLPDB1 using snapshot PDB_SNAP create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2'*ERROR at line 1:ORA-65040: operation not allowed from within a pluggable database SQL alter session set container=CDB$ROOT;Session altered.SQL create pluggable database ORCLPDB2 from ORCLPDB1 using snapshot PDB_SNAP create_file_dest='/opt/oracle/oradata/ORCLCDB/ORCLPDB2'; Pluggable database created.SQL show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO 5 ORCLPDB2 MOUNTEDSQL alter pluggable database ORCLPDB2 open; Pluggable database altered.

DBA_HIST_WR_CONTROL

SQL select snapshot_mode,snapshot_interval/60 from dba_pdbs;SNAPSH SNAPSHOT_INTERVAL/60------ --------------------MANUALSQL alter pluggable database snapshot mode every 4 hours;Pluggable database altered.SQL select snapshot_mode,snapshot_interval/60 from dba_pdbs;SNAPSH SNAPSHOT_INTERVAL/60------ --------------------AUTO 4

--指定数据库实例生成AWR报告

要返回手动模式,输入mode manual即可。

SQL @/rdbms/admin/awrrpt.sql
SQL alter session set container=ORCLPDB3;Session altered.SQL create table a1 as select * from dba_objects;Table created.SQL select count(1) from a1; COUNT(1)---------- 72897[oracle@oracle-18c-vagrant ORCLPDB3]$ ls -lrt snap*-rw-r--r--. 1 oracle oinstall 179134736 Oct 8 23:13 snap_2091710291_2830531.pdb-rw-r--r--. 1 oracle oinstall 179167158 Oct 8 23:18 snap_2091710291_2832545.pdb-rw-r--r--. 1 oracle oinstall 179285979 Oct 8 23:23 snap_2091710291_2833104.pdb-rw-r--r--. 1 oracle oinstall 179284365 Oct 8 23:28 snap_2091710291_2833687.pdb-rw-r--r--. 1 oracle oinstall 179302205 Oct 8 23:33 snap_2091710291_2834965.pdb-rw-r--r--. 1 oracle oinstall 179315484 Oct 8 23:38 snap_2091710291_2836695.pdb-rw-r--r--. 1 oracle oinstall 179353508 Oct 8 23:43 snap_2091710291_2837222.pdb-rw-r--r--. 1 oracle oinstall 5767168 Oct 8 23:48 snap_2091710291_2839217.pdbSQL SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN; CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH---------- -------------------- ------------------------- ---------- ------------- --------------------------------------------------------------------------- 7 ORCLPDB3 SNAP_2091710291_989017984 2830531 1539011592 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2830531.pdb 7 ORCLPDB3 SNAP_2091710291_989018284 2832545 1539011887 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2832545.pdb 7 ORCLPDB3 SNAP_2091710291_989018584 2833104 1539012188 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2833104.pdb 7 ORCLPDB3 SNAP_2091710291_989018884 2833687 1539012489 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2833687.pdb 7 ORCLPDB3 SNAP_2091710291_989019184 2834965 1539012787 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2834965.pdb 7 ORCLPDB3 SNAP_2091710291_989019484 2836695 1539013087 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2836695.pdb 7 ORCLPDB3 SNAP_2091710291_989019784 2837222 1539013386 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2837222.pdb 7 ORCLPDB3 SNAP_2091710291_989020084 2839217 1539013686 /opt/oracle/oradata/ORCLCDB/ORCLPDB3/snap_2091710291_2839217.pdb
SQL @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

总结

BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id = 162, high_snap_id = 164, dbid = 41924548);END;/

这样我们就根据快照创建了一个新的PDB,假设当前PDB出现了数据覆盖,我们就可以使用这个历史快照的PDB来恢复数据。

五、AWR相关的重要视图和数据字典

SQL alter pluggable database drop snapshot SNAP_2953839490_989015970;Pluggable database altered.SQL SELECT CON_ID, CON_NAME, SNAPSHOT_NAME, SNAPSHOT_SCN AS snap_scn, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN; CON_ID CON_NAME SNAPSHOT_NAME SNAP_SCN SNAPSHOT_TIME FULL_SNAPSHOT_PATH---------- -------------------- ------------------------- ---------- ------------- --------------------------------------------------------------------------- 3 ORCLPDB1 SNAP_2953839490_989014667 2821702 1539008272 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2821702.pdb 3 ORCLPDB1 PDB_SNAP 2823303 1539009503 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/snap_2953839490_2823303.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, SNAPSHOT_TIME, FULL_SNAPSHOT_PATH FROM DBA_PDB_SNAPSHOTS ORDER BY SNAP_SCN;no rows selected

--生成SQL语句AWR报告

如果要删除快照,则有两种方法,第一种就是使用alter pluggable database删除,第二种是修改max_pdb_snapshots参数,当max_pdb_snapshots参数设置成0时,将删除全部快照。

重复的基线样本指的是在将来某个特定的时间范围内,Oracle会参照这个设定的样本自动为我们创建基线。比如,可以创建一个重复的基线样本,使得在2017年每周一9:00-11:00自动生成基线。

可以看到每隔5分钟自动创建一个快照

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

SQL alter pluggable database snapshot mode manual;Pluggable database altered.SQL alter pluggable database snapshot;Pluggable database altered.
BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE ( start_time = TO_DATE ('2017-04-14 17:00:00', 'yyyy-mm-dd hh24:mi:ss'), end_time = TO_DATE ('2017-04-14 19:00:00', 'yyyy-mm-dd hh24:mi:ss'), baseline_name = 'baseline_140414', template_name = 'template_140414', expiration = 10, dbid = 41924548);END;/--如果创建基线样本时间小于当前时间,则收到如下错误ERROR at line 1:ORA-13537: invalid input for create baseline template (end_time, end_time is less than SYSDATE)ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 768ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 818ORA-06512: at line 2

23点48分产生了新的快照,此时我们使用新的快照做一个克隆,登陆到ORCLPDB4数据库后,我们会发现是有数据的。

三、AWR空间占用

Oracle 18c推出的一个新功能就是PDB快照轮播,最多可以创建8个pdb的快照,而这8个构成的一组快照就被称作快照轮播。当达到第8个快照限制时,新的快照就会覆盖最旧的快照,就像redo log一样。PDB的快照主要作用是当出现了问题可以闪回到快照的时间点,另一个功能是基于快照来查询历史数据。

SQL alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';BEGIN DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE ( day_of_week = 'monday', hour_in_day = 9, duration = 2, expiration = 30, start_time = '2017-04-14 09:00:00', end_time = '2017-12-31 11:00:00', baseline_name_prefix = 'baseline_2017_mondays_', template_name = 'template_2017_mondays', dbid = 41924548);END;/-- Author : Leshami-- Blog :  QQ(Weixin) : 645746311--查看已经创建的基线样本SQL select t.template_name, 2 t.template_type, 3 t.start_time, 4 t.end_time, 5 t.day_of_week, 6 t.hour_in_day, 7 t.duration 8 from dba_hist_baseline_template t;TEMPLATE_NAME TEMPLATE_ START_TIME END_TIME DAY_OF_WE HOUR_IN_DAY DURATION--------------------- --------- ------------------- ------------------- --------- ----------- --------template_140414 SINGLE 2017-04-14 17:00:00 2017-04-14 19:00:00template_2017_mondays REPEATING 2017-04-14 09:00:00 2017-12-31 11:00:00 MONDAY 17 3

--RAC环境下生成AWR报告

2. 删除快照

--单实例下生成AWR报告

1. 手动创建快照

v$active_session_history : 显示活跃的数据库会话的活动,每秒采样一次

--查看缺省的window_sizeSELECT baseline_name, baseline_type, moving_window_sizeFROM dba_hist_baselineWHERE baseline_name = 'SYSTEM_MOVING_WINDOW';BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE------------------------ ------------- ------------------SYSTEM_MOVING_WINDOW MOVING_WINDOW 8BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE ( window_size = 7, dbid = 41924548);END;/--window_size为天,只能够小于等于当前快照保留时间,否则报错,如下:ERROR at line 1:ORA-13541: system moving window baseline size (864000)greater than retention (691200)ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 686ORA-06512: at line 2

共享服务器的统计信息

在上面的示例中我们删除之前创建的基线样本,通过指定样本名称即可。

AWR控制信息

快照信息

DBA_HIST_BASELINE_DETAILS

AWR是Automatic Workload Repository的简称,中文叫着自动工作量资料档案库。对于AWR的管理,主要是针对快照和基线的管理而言。比如设定快照的间隔,删除快照,设定快照的保留时间。那对于基线而言则是基线的创建,删除,自动创建等。本文主要描述这2部分的内容。

基线的创建依赖于快照,因此在创建之前应该得到所需时间范围的snap_id,可以通过查询数据字典DBA_HIST_SNAPSHOT获得,同时在基线创建完成后可以从数据字典DBA_HIST_BASELINE查询基线相关信息。

创建单个基线模板

根据历史数据预测在不同的cache size下的物理读

创建重复基线样本

基线模板相关信息

基线样本的删除

SQL @$ORACLE_HOME/rdbms/admin/awrgrpt.sql

总结

编辑:计算机教程 本文来源:Oracle 18c新特性-PDB快照轮播(Snapshot Carousel)

关键词: