【Oracle 12c数据库支持闪回库功能】实验
|
Oracle 12c数据库支持闪回库功能，r1仅支持cdb级别的闪回库，r2可以支持到pdb级别的闪回库，闪回到闪回点，时间点，scn几种方式。当然在闪回库操作时，share undo和local undo模式使用的语句是不同的。（share undo模式需要使用到辅助实例） 一、采用local undo模式 SYS@orcl>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'; ------------------------------------------------------------ ----- LOCAL_UNDO_ENABLED TRUE 数据库版本 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 数据库scott.it输入数据，记录scn； 16:55:24 SYS@orcl>conn scott/oracle@pdb1 Connected. 16:55:40 SCOTT@pdb1>select * from it; ID ---------- 1 2 16:55:46 SCOTT@pdb1>insert into it values(100); 1 row created. 16:55:57 SCOTT@pdb1>commit; Commit complete. 16:55:59 SCOTT@pdb1>select * from it; ID ---------- 1 2 100 16:56:25 SYS@pdb1>select current_scn from v$database; CURRENT_SCN ----------- 5120605 3、删除scott.it表，并闪回pdb1到scn 5120605 16:57:02 SYS@pdb1>drop table scott.it purge; Table dropped. 16:57:20 SYS@pdb1>shutdown immediate; Pluggable Database closed. 16:57:31 SYS@pdb1>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB1 MOUNTED 16:57:33 SYS@pdb1>conn / as sysdba Connected. 16:57:41 SYS@orcl>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 16:57:44 SYS@orcl>flashback pluggable database pdb1 to scn 5120605; Flashback complete. 16:58:07 SYS@orcl>alter pluggable database pdb1 open resetlogs; Pluggable database altered. 16:58:30 SYS@orcl>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 16:58:57 SYS@orcl>conn scott/oracle@pdb1 Connected. 16:59:15 SCOTT@pdb1>select count(*) from it; COUNT(*) ---------- 3 16:59:25 SCOTT@pdb1>select * from it; ID ---------- 1 2 100 二、采用share undo模式 1、将local undo 修改成share undo模式 查看local undo是否开启，默认是开启的； SYS@orcl>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'; ------------------------------------------------------------ ----- LOCAL_UNDO_ENABLED TRUE 以upgrade模式重启数据库； 20:33:32 SYS@orcl>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 20:33:38 SYS@orcl>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 20:34:20 SYS@orcl>startup upgrade; ORACLE instance started. Total System Global Area 2466250400 bytes Fixed Size 9137824 bytes Variable Size 587202560 bytes Database Buffers 1862270976 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. 关闭local undo； 20:34:50 SYS@orcl>alter database local undo off; Database altered. 20:35:01 SYS@orcl>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. 20:36:01 SYS@orcl>startup; ORACLE instance started. Total System Global Area 2466250400 bytes Fixed Size 9137824 bytes Variable Size 587202560 bytes Database Buffers 1862270976 bytes Redo Buffers 7639040 bytes Database mounted. Database opened. 20:36:41 SYS@orcl>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 20:36:47 SYS@orcl>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME LOCAL_UNDO_ENABLED ----------------------------------------------------------------- PROPERTY_VALUE FALSE 20:36:58 SYS@orcl>show parameter undo; NAME TYPE VALUE ------------------------------------ ----------- ----------------- temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 20:37:08 SYS@orcl>conn sys/oracle@pdb1 as sysdba Connected. 20:37:46 SYS@pdb1>select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP01 DATA 20:37:55 SYS@pdb1>drop tablespace undotbs1 including contents and datafiles; Tablespace dropped. 20:38:17 SYS@pdb1>select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX TEMP01 DATA 备注：share undo 转化为local undo模式，打开pdb01时会自动重建，无须人工干预，如果需要在pdb01中重建pdb则需要undo管理方式使用manual方式或者在pdb01中新建undo然后再替换的方式。 录入数据，记录scn 20:47:53 SCOTT@pdb1>insert into it values(200); 1 row created. 20:47:59 SCOTT@pdb1>commit; Commit complete. 20:48:01 SCOTT@pdb1>select * from it; ID ---------- 1 2 200 100 20:48:08 SCOTT@pdb1>conn sys/oracle@pdb1 as sysdba Connected. 20:48:29 SYS@pdb1>select current_scn from v$database; CURRENT_SCN ----------- 5181471 20:48:37 SYS@pdb1>drop table scott.it; Table dropped. 3、闪回数据库。 RMAN> flashback pluggable database pdb1 to scn 5181471 auxiliary destination '/home/oracle/aux'; [oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 14 21:24:31 2022 Version 19.3.0.0.0 Copyright (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.0 SYS@orcl>alter pluggable database pdb1 open resetlogs; Pluggable database altered. SYS@orcl>show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SYS@orcl>conn scott/oracle@pdb1 Connected. SCOTT@pdb1>select * from it; ID ---------- 1 2 200 100 总结：数据库闪回功能基于已有的备份数据以及归档日志将pdb库恢复到某个节点，12c上使用local undo和share undo方式使用命令是不一样的，数据库闪回操作意味着数据的丢失，所以操作时需要注意，是否可以只读的方式来看看闪回数据是否正常呢？ Yicheng16 -- The End -- （编辑：泰州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |