-
oracle怎么 备份表数据 索引_Oracle备份恢复之Flashback--闪回数据归档
2020-12-22 01:14:21概述Oracle 引入闪回技术使得一些逻辑误操作不再需要利用归档日志和数据库备份进行时间点恢复,在前面介绍的诸多闪回技术中,除了Flashback Database依赖于闪回日志...Oracle11g中的闪回数据归档(Flashback Data Ar...概述
Oracle 引入闪回技术使得一些逻辑误操作不再需要利用归档日志和数据库备份进行时间点恢复,在前面介绍的诸多闪回技术中,除了Flashback Database依赖于闪回日志外,其它闪回技术都是依赖于Undo撤销数据,都与数据库初始化参数undo_retention密切相关,所以就存在一个限制就是undo中的信息不能被覆盖。
Oracle11g中的闪回数据归档(Flashback Data Archive)与前面所说的闪回技术实现机制不同,它是通过将变化数据存储到创建的闪回归档区中,得以与undo区别开来。 同时Flashback Data Archive 并不是记录数据库的所有变化,而只是记录了指定表的数据变化,是Flashback database的补充。
1.体系结构
2.配置闪回数据归档
准备一个Oracle 11g数据库使用FBDA功能是相当简单的,只需要经过几个简单的步骤即可:
(1) 创建或指定一个或多个表空间用于FBDA保留历史数据
(2) 随意指派一个FBDA作为数据库的默认FBDA
(3) 指派一个用户账户作为FBDA管理员,授予它FLASHBACK ARCHIVE ADMINISTER系统权限
(4) 授予FBDA权限给适合的用户账号
(5) 授予FLASHBACK和SELECT权限给合适的FBDA表用户
(6) 为FBDA用户授予DBMS_FLASHBACK存储过程EXECUTE权限
3.访问历史记录数据的基本工作流:
创建闪回数据归档:
1. 创建闪回数据归档:
CREATE FLASHBACK ARCHIVE fla1 TABLESPACE tbs1 QUOTA 10GRETENTION 5 YEAR;
2. 对 FLA1 归档中的表启用历史记录跟踪:
ALTER TABLE inventory FLASHBACK ARCHIVE fla1;
3. 查看历史记录数据:
SELECT product_number, product_name, count FROM inventory AS OF TIMESTAMPTO_TIMESTAMP ('2019-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
使用默认闪回归档:
1.创 建 默 认 闪 回 数 据 归 档
CREATE FLASHBACK ARCHIVE DEFAULT fla2TABLESPACE tbs1 QUOTA 10G RETENTION 2 YEAR;
2.对表启用历史记录跟踪:
ALTER TABLE stock _data FLASHBACK ARCHIVE;
注:因为使用了默认的闪回数据归档,所以不需要提供闪回数据归档名称。
3. 禁用历史记录跟踪:
ALTER TABLE stock _data NO FLASHBACK ARCHIVE;
4.维护闪回数据归档
1. 增加空间:
ALTER FLASHBACK ARCHIVE fla1 ADD TABLESPACE tbs3 QUOTA 5G;
2. 更改保留时间:
ALTER FLASHBACK ARCHIVE fla1 MODIFY RETENTION 2 YEAR;
3. 清除数据:
ALTER FLASHBACK ARCHIVE fla1 PURGE BEFORETIMESTAMP(SYSTIMESTAMP - INTERVAL '1' day);
4. 删除闪回数据归档:
DROP FLASHBACK ARCHIVE fla1;
篇幅有限,关于闪回归档就不做演示了,大家可以自己在虚拟机环境做相关测试。
后面会分享更多关于devops和DBA方面内容,感兴趣的朋友可以关注下!
-
数据归档
2017-08-03 17:48:24如果对于不停机的数据归档,需要考虑数据在不停写入。 1. 修改待归档表索引名 alter index PS_PSACCESSLOG rename to PS_PSACCESSLOG2; 2. 创建新表 -- Create table create table SYSADM.PSACCESSLOG_1 ...如果对于不停机的数据归档,需要考虑数据在不停写入。
1. 修改待归档表索引名
alter index PS_PSACCESSLOG rename to PS_PSACCESSLOG2;
2. 创建新表
-- Create table
create table SYSADM.PSACCESSLOG_1
(
OPRID VARCHAR2(30) not null,
LOGIPADDRESS VARCHAR2(40) not null,
LOGINDTTM TIMESTAMP(6),
LOGOUTDTTM TIMESTAMP(6),
PT_SIGNON_TYPE VARCHAR2(3) not null
)
tablespace PTTBL
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 40K
next 104K
minextents 1
maxextents unlimited
);3. 在新表上创建原名索引(如果停机归档,可在写入数据后创建索引)
-- Create/Recreate indexes
create index SYSADM.PS_PSACCESSLOG on SYSADM.PSACCESSLOG_1 (OPRID, LOGIPADDRESS, LOGINDTTM)
tablespace PSINDEX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 40K
next 104K
minextents 1
maxextents unlimited
);4. 将新表依原表 赋权
GRANT DELETE ON PSACCESSLOG_1 TO R_EP_DML ;
GRANT INSERT ON PSACCESSLOG_1 TO R_EP_DML ;
GRANT SELECT ON PSACCESSLOG_1 TO R_EP_DML ;
GRANT UPDATE ON PSACCESSLOG_1 TO R_EP_DML ;5. 重命名表名(将待归档表重命名归档表名,新表重命名原表名)此处对于业务上会有一定短时的影响
RENAME PSACCESSLOG TO PSACCESSLOG_20170717;
RENAME PSACCESSLOG_1 TO PSACCESSLOG;6. 循环写入新表归档时间点后的数据
begin
for cur in (select a.* from PSACCESSLOG_20170717 a where logindttm>=to_date('20170101','yyyymmdd')) loop
insert into PSACCESSLOG (oprid,logipaddress,Logindttm,logoutdttm,Pt_Signon_Type) values(cur.oprid,cur.logipaddress,cur.Logindttm,cur.logoutdttm,cur.Pt_Signon_Type);
commit;
end loop;
end;
/7. 搜集新表统计信息
EXEC dbms_stats.gather_table_stats(ownname => 'SYSADM',tabname => 'PSACCESSLOG',estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,cascade => true,method_opt => 'FOR ALL COLUMNS SIZE AUTO');
-
oracle:索引、视图和闪回数据归档
2017-11-07 20:30:56索引准则:当任何单个查询要检索的行少于或等于整个表行数的10%时,就应当创建索引。 B-树索引:索引的候选列应该是用来存储很大范围的值的列 位图索引:包含小范围值的列1.创建B-树索引注意:由于性能原因,应该...索引
准则:当任何单个查询要检索的行少于或等于整个表行数的10%时,就应当创建索引。
B-树索引:索引的候选列应该是用来存储很大范围的值的列
位图索引:包含小范围值的列1.创建B-树索引
注意:由于性能原因,应该将索引与表存储到不同的表空间中。
假设test表包含很多行
select id,type from test where type='01';
create index i_test_type on test(type);
唯一索引
create unique index i_test_id on test(id);
复合索引
create index i_test_id_type on test(id, type);
2.创建基于函数的索引
select id,type from test where type=UPPER('SFZ');
create index i_func_test_type on test(UPPER(type));
注意:使用基于函数的索引,必须要将初始化参数query_rewrite_enabled设置为true。
3.获取有关信息的索引
select index_name, table_name, uniqueness, status from user_indexes where table_name='test';
4.获取列索引的信息
select index_name, table_name, column_name from user_ind_columns where table_name='test';
5.修改索引
alter index i_test_id_type rename to i_test_id_and_type;
6.删除索引
drop index i_test_id_and_type;
7.创建位图索引
create bitmap index i_order on order(status);
视图
注意:视图并不存储行,他们始终存储在表中。视图返回存储在表中的行
1.创建视图
connect system/systemgrant create view to zoey; connect zoey/zoey create view test_view asselect * from test where type='02';
使用视图
select id from test_view;
注意:简单视图支持DML操作,复杂视图不支持DML操作。
2.创建具有check option约束的视图指定对视图执行的DML操作必须满足子查询的条件。
create view test_view2 asselect * from test where type='02' with check option constraint test_view2;
3.创建具有read only约束的视图(只读)
create view test_view3 asselect * from test where type='02' with read only constraint test_view2;
4.获取有关视图定义的信息
describe test_view3;
5.获取有关视图的约束信息
select constraint_name, constraint_type, status , deferrable, deferred from user_constraintswhere table_name='test';
6.修改视图
create or replace view test_view3 asselect * from test where type in ('02', '01');
7.删除视图
drop view test_view3;
五.闪回数据归档
create flashback archive test_archivetablespace example --归档在example创建 quota 1 M --限额是1M retention 1 DAy;--保留期限是1天
停止归档
alter zoey.test no flashback archive;
清除归档中的所有数据
alter flashback archive test_archive purge all;
删除闪回归档
drop flashback archive test_archive;
-
mysql从库大表数据归档_MySQL大表数据归档问题的解决办法
2021-01-19 23:16:29当有一张表数据量很大,真正项目只用到一个月内的数据,因此把一个月前的旧数据定期归档。解决方案如下:1 - 创建一个新表,表结构和索引与旧表一模一样create table table_archive like table_name;2 - 新建存储...当有一张表数据量很大,真正项目只用到一个月内的数据,因此把一个月前的旧数据定期归档。
解决方案如下:
1 - 创建一个新表,表结构和索引与旧表一模一样
create table table_archive like table_name;
2 - 新建存储过程,查询30天的数据并归档进新数据库,然后把30天前的旧数据从旧表里删除
delimiter $
create procedure sp()
begin
insert into table_archiveselect * fromtable_name where rectime < now() - interval 30 day;
delete from db_smc.table_name where rectime < now() - interval 30 day;
end
3 - 创建event,每天晚上凌晨00:00定时执行上面的存储过程
create event if not exists event_temp
on schedule every 1 day
on completion preserve
do call sp();
备注:
第一次执行存储过程的时候因为历史数据过大, 可能发生意外让该次执行没有成功。重新执行时会遇到报错error 1205 (hy000): lock wait timeout exceeded; try restarting transaction,应急解决方案如下:
1、执行show full processlist;查看所有mysql线程
2、执行select * from information_schema.innodb_trx\g; 查看是否有错误线程,线程id在show full processlist;的结果中状态为sleep
3、kill 进程id
如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!
-
数据归档,冷热数据分离
2019-01-15 15:51:002.在线创建索引和字段 3.在线事务 4.支持索引同步 2.tokudb安装步骤 1.yum install jemalloc -y 2.vim /etc/my.cnf #添加如下 [mysqld_safe] malloc-lib=/usr/lib64/libjemalloc.so... -
MySQL 大表数据归档解决办法
2018-07-18 21:01:23当数据库有一张表数据量很大,真正项目只用到一个月内的数据,因此把一个月前的旧数据定期归档。 解决方案如下: 1 - 创建一个新表,表结构和索引与旧表一模一样 create table table_archive like table_name; ... -
mysql 定期归档_MySQL 大表数据定期归档
2021-01-26 22:57:37数据库有一张表数据量很大,真正WEB项目只用到一个月内的数据,因此把一个月前的旧数据定期归档。1 - 创建一个新表,表结构和索引与旧表一模一样create table table_news like table_name;2 - 新建存储过程,查询30... -
mysql 创建归档_MySQL 大表数据定期归档
2021-01-19 13:07:15数据库有一张表数据量很大,真正WEB项目只用到一个月内的数据,因此把一个月前的旧数据定期归档。1 - 创建一个新表,表结构和索引与旧表一模一样create table table_news like table_name;2 - 新建存储过程,查询30... -
mysql冷热分离的技术_数据归档,冷热数据分离
2021-01-18 19:28:34在线创建索引和字段3.在线事务4.支持索引同步2.tokudb安装步骤1.yum install jemalloc -y2.vim/etc/my.cnf #添加如下[mysqld_safe]malloc-lib=/usr/lib64/libjemalloc.so.13.echo never >/sys/kerne... -
Percona pt-archiver重构版--大表数据归档工具
2018-08-07 18:01:36作为一个DBA,侧重点是对数据库的操作性能(大表增加字段/索引,QPS等)和存储容量加以考虑,我们会建议开发对数据库里的大表进行数据归档处理,例如将3个月内的订单表保留在当前表,历史数据切分后保存在归档表... -
关于 Oracle 索引以及 Bitmap 索引 和 B-tree 索引(归档)
2013-03-10 11:33:00Oracle 索引的数据结构 (2012-11-15 00:17) 关于 Oracle 索引 (2012-11-17 16:34) Oracle Bitmap 索引结构、如何存储及其优势 (2012-11-18 17:36) Oracle B-tree 索引结构、如何存储及其优势 (2012-11-17 23:36)... -
MySQL 大表数据定期归档
2019-01-02 11:56:00数据库有一张表数据量很大,真正WEB项目只用到一个月内的数据,因此把一个月前的旧数据定期归档。 1 - 创建一个新表,表结构和索引与旧表一模一样 create table table_news like table_name; 2 - 新建存储... -
mysql自动分区归档数据_RDS for MySQL 通过分区归档历史数据
2021-02-06 01:08:46RDS for MySQL 通过分区归档历史数据原始表分区用于分区维护的存储过程每月调用存储过程的事件随着数据的积累,数据量的增加,越来越多的表体积变的庞大,不但影响查询的执行时间,而且使得管理工作(比如添加删除... -
[MySQL] 大表数据定期归档
2016-08-26 09:55:42数据库有一张表数据量很大,真正WEB项目只用到一个月内的数据,因此把一个月前的旧数据定期归档。1 - 创建一个新表,表结构和索引与旧表一模一样create table table_new like table_old;2 - 新建存储过程,查询... -
从USERS表空间分离表数据和索引
2011-09-27 17:21:05前段时间某公司给做了个网站,装了个11g的数据库,安装后只有3个单独的日志文件,归档、闪回没有打开,网站的表数据和索引全建到USERS表空间,这种设计方法完全是错误的,因此需要对数据库进行整改。 具体为日志... -
优化案例--定期归档数据
2014-01-17 15:47:00很少有开发会考虑到数据归档的问题已经数据增长的问题, 当程序运行一段时间后,就会出现各种问题,部分问题可以 修改SQL语句或使用索引来解决,但如果SQL语句无法修改, 糟糕的SQL语句无法使用索引,归档历史... -
生产库已归档数据删除策略
2018-10-26 19:34:09上期文章分享了数据库归档策略,下面来分享一下生产库删除策略。 需求: 1.三张有关但是不存在外键的表: rxinfo:存储订单信息; rxExtendsInfo:存储订单扩展信息;...2.一次从归档库遍历当前数据是否已经归档(... -
数据去重的磁盘索引瓶颈
2014-01-16 11:33:21随着数据量的不断激增,数据去重技术通过删除重复数据节省存储空间和网络传输带宽的,在数据备份系统和文档归档系统中取得了广泛地应用。企业级数据要求高吞吐率,高速的索引查询,但是数据量的增加导致的索引表的... -
Bitmap 索引 vs. B-tree 索引:如何选择以及何时使用?(归档)
2013-03-10 11:47:00实际中,Bitmap 索引对系统有个建议,数据不会被很多并发系统频繁更新(不希望数据被系统频繁更新)。事实上,我想说,一个具有 100% 唯一值(unique values)列(该列可以作为主键)上的 Bitmap 索引具有与 B-tree ... -
数据增长,尽在掌控:赛门铁克有效归档解决方案概述
2020-03-03 17:26:08赛门铁克提供的有效归档解决方案采取了以下...其次,为归档编制有效的索引。某些搜索技术编制的索引比被索引项还要大。最后,数据泄露防护技术还可以帮助公司识别高价值的信息及其使用方式,这样就可以对其加以管理了。 -
RDS for MySQL 通过分区归档历史数据
2017-07-31 11:36:45RDS for MySQL 通过分区归档历史数据原始表分区用于分区维护的存储过程每月调用存储过程的事件随着数据的积累,数据量的增加,越来越多的表体积变的庞大,不但影响查询的执行时间,而且使得管理工作(比如添加删除...