第八十九期 19c OCM之路-第二堂
第一堂搞完了,感觉其实内容是熟悉而陌生,毕竟上一次摸OCM都是17年的时候,继续第二堂。
第二堂标题是“Backup/Recovery”备份与恢复。本堂操作均需要开启归档,如果前一堂没有开启归档,本堂每题操作前需要开启归档,同时注意冷备的归档状态,以免后续需要使用。
考点1:Configure Recovery Catalog 配置恢复catalog
连接至EMREP数据库作为catalog数据库,catalog用户为rc_admin/oracle,将PRODCDB和PROD4注册到catalog中:
-- 环境准备
sqlplus sys/Oracle123@emrep as sysdba
create tables catatbs datafile '/u01/app/oracle/oradata/EMREP/catatbs01.dbf' size 10m autoextend on maxsize 2G;
create user rc_admin identified by oracle account unlock;
grant dba,recovery_catalog_owner to rc_admin,
alter user rc_admin default tablespace catatbs quota unlimited on catatbs;
rman catalog rc_admin/oracle@emrep
create catalog;
-- 操作
rman target sys/oracle@prodcdb catalog rc_admin/oracle@emrep
upgrade catalog;
upgrade catalog;
register databaase;
rman target sys/oracle@prod4 catalog rc_admin/oracle@emrep
upgrade catalog;
upgrade catalog;
register databaase;
考点2:Perform backup and backup configuration 备份及备份配置
(这里把网页考点2,3,4的位置调整一下,即是先备份后恢复)
- PROD4数据库开启块追踪:
rman target sys/oracle@prod4
configure backup optimization on;
-- configure controlfile autobackup on;
-- configure device type disk backup type to compressed backupset;
sqlplus sys/oracle@prod4 as sysdba
alter database enable block change tracking using file '/u01/app/oracle/oradata/PROD4/block.txt';
- 为PRODCDB创建一个包含所有PDB的备份:
rman target sys/oracle@prodcdb
backup full database plus archivelog;
- 备份pdbprod1中的sysaux表空间,并需要保留很长时间。同时需要解决串行备份的性能问题:
rman target sys/oracle@prodcdb catalog rc_admin/oracle@emrep
configure device type disk parallelism 4;
backup section size 100m tablespace pdbprod1:sysaux keep forever format '/u01/app/oracle/fast_recovery_area/PRODCDB/%d_%t_%U.bak';
- 对PROD4执行增量/差异备份:
rman target sys/oracle@prod4
backup incremental level 0 database;
backup incremental level 1 database;
backup incremental level 2 database;
- 其他备份
rman target sys/oracle@prod4
backup as backupset database tag "OCM01";
backup datafile 5 section size 25M [tag "OCM02size25m"];
rman target sys/oracle@prod4 catalog rc_admin/oracle@emrep
change backup tag "OCM02size25m" keep fover;
考点3:Configuring Flashback Database 配置闪回数据库
- PROD4开启闪回,FRA目录为/u01/app/oracle/flash,大小5GB:
sqlplus sys/oracle@prod4 as sysdba
alter system set db_recovery_file_dest='/u01/app/oracle/flash';
alter system set db_recovery_file_dest_size=5G;
-- alter system set db_flashback_retention_target=2880 scope=spfile;
shut immediate
startup mount
alter database flashback on;
alter database open;
- PROD4创建不会过期的还原点RP1:
sqlplus sys/oracle@prod4 as sysdba
create restore point RP1 guarantee flashback database;
考点4:Perform recovery and duplication 恢复与复制
- PROD4执行脚本xxx.sql,最终发现脚本内容误操作,请恢复相关表数据:
sqlplus sys/oracle@prod4 as sysdba
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; -- 获取当前时间戳
-- 执行题目要求脚本
shut immediate
startup mount
flashback database to timestamp to_timestamp('xxxx-xx-xx xx:xx:xx','yyyy-mm-dd hh24:mi:ss'); -- 闪回数据库到之前获取的时间
alter database open read only; -- 因为仅是需要将异常数据恢复,所以不适用open resetlog方式开启数据库
-- 找到变更数据exp出来
-- exp \'/ as sysdba\' tables=username.tabname file=xxx.dmp
shut immediate
startup mount
recover database;
alter database open resetlog;
-- 将对应表数据清除后,imp相关表数据,如遇主外键影响,删除外键导入数据后重建外键
-- 其实这里如果不涉及DDL操作,仅为DML操作,使用闪回查询也可以,需要根据题目具体情况选择
select to_char(current_scn) from v$database; -- 获取当前SCN
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; -- 获取当前时间戳
-- 执行题目要求脚本
create table username.tabname_old as select * from username.tabname as of timestamp to_timestamp('xxxx-xx-xx xx:xx:xx','yyyy-mm-dd hh24:mi:ss'); -- 基于时间点的闪回查询
create table username.tabname_old as select * from username.tabname as of scn xxxxxxxxxxxxxx; -- 基于SCN的闪回查询
-- 后续在替换数据
- PROD4中从回收站恢复表名为t1,列名包含name的表,恢复表名为t1_old:
sqlplus sys/oracle@prod4 as sysdba
show recyclebin;/select * from dba_recyclebin;
desc "BIN$....";
flashback table "BIN$...." to before drop remane to t1_old;
总结
第二堂内容应该是基本覆盖完了。
老规矩,知道写了些啥。