Note_Tech

All technological notes.

Project maintained by simonangel-fong Hosted on GitHub Pages — Theme by mattgraham

Flashback - Flashback Database

Back

Flashback Database

diagram_flashback_db

  • When writes are issued to disk and Flashback Database is enabled:
    • old block version is written to the flashback log
    • the new block version is written to the data file

diagram_flashback_db

Architecture

diagram_flashback_db

Flashback CDB and PDB

CDB:

PDB:

CONN sys@pdbl
-- close pdb
ALTER PLUGGABLE DATABASE CLOSE;
-- flashback pdb to a scn
FLASHBACK PLUGGABLE DATABASE pdbl TO SCN 411010;
-- open pdb resetlogs
ALTER PLUGGABLE DATABASE pdbl OPEN RESETLOGS;
  • OPEN RESETLOGS creates a new incarnation for the PDB.
  • The CDB root must be opened when the PDB OPEN RESETLOGS is performed, except that recovering a PDB to a point in time does not affect all parts of the CDB. The whole CDB and all other PDBs are opened.
  • After recovering a PDB to a specified point in time, when you open the PDB by using the RESETLOGS option, a new incarnation of the PDB is created.

Monitoring Flashback Database Information

-- View the fast recovery area disk quota:
SELECT estimated_flashback_size, flashback_size
FROM V$FLASHBACK_DATABASE_LOG;

-- Determine the current flashback window:
SELECT oldest _flashback_scn, oldest_flashback_time
FROM V$FLASHBACK DATABASE_LOG;

-- Monitor logging in the Flashback Database logs:
SELECT *
FROM V$FLASHBACK DATABASE_STAT;

Example:

example_monitor

Column Description
NAME Fast recovery area name, indicating location string
SPACE_LIMIT Disk limit specified in the DB_RECOVERY_FILE_DEST_SIZE parameter
SPACE_USED Space used by fast recovery area files (in bytes)
SPACE_RECLAIMABLE Amount of space that can be reclaimed by deleting obsolete, redundant, and other low-priority files through the space management algorithm
NUMBER_OF_FILES Number of files

Configuration for Flashback Database

SELECT TUNED_UNDORETENTION
FROM V$UNDOSTAT
WHERE rownum = 1;

show parameter undo_retention
-- NAME           TYPE    VALUE
-- -------------- ------- -----
-- undo_retention integer 14400

ALTER SYSTEM SET UNDO_RETENTION=900 SCOPE=BOTH;

Guaranteed retention

diagram_guaranteed_retention

-- Enable guaranteed retention
ALTER TABLESPACE undotbsl RETENTION GUARANTEE;

-- Disable guaranteed retention
ALTER TABLESPACE undotbsl RETENTION NOGUARANTEE;

Enabling Flashback Database

-- rman, moun the database
-- to time
FLASHBACK DATABASE TO TIME = "TO_DATE('2009-05-27 16:00:00", 'YYYY-MM-DD HH24:MI:SS')";
-- to scn
FLASHBACK DATABASE TO SCN=23565;
-- to sequence and thread
FLASHBACK DATABASE TO SEQUENCE=223 THREAD=1;

-- sql
-- to timestampe
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24) ;
-- to scn
FLASHBACK DATABASE TO SCN 53943;
--  to restore point
FLASHBACK DATABASE TO RESTORE POINT bd_load;

-- To review changes: Open in read-only mode.
-- To finalize: Open in read/write mode with RESETLOGS.

Guaranteed Restore Points

CREATE RESTORE POINT before upgrade
GUARANTEE FLASHBACK DATABASE;

PDB Flashback and Clean Restore Point

Normal and Guaranteed Restore Points

Clean Restore Points

-- sql
CONNECT / AS SYSDBA
ALTER PLUGGABLE DATABASE pdbl CLOSE;
-- create clean restore point
CREATE CLEAN RESTORE POINT start_stepl
FOR PLUGGABLE DATABASE pdbl
GUARANTEE FLASHBACK DATABASE;

ALTER PLUGGABLE DATABASE pdbl OPEN;
/*
some issues
*/
ALTER PLUGGABLE DATABASE pdbl CLOSE;

-- rman
rman target /
FLASHBACK PLUGGABLE DATABASE pdbl TO RESTORE POINT start_stepl;
ALTER PLUGGABLE DATABASE pdbl OPEN RESETLOGS:

Best Practices

Lab: Enabling Flashback Logging

sqlplus / as sysdba

SELECT flashback_on
FROM v$database;

lab_conf

sqlplus / as sysdba
-- enable flashback
ALTER DATABASE FLASHBACK ON;

-- query
SELECT flashback_on
FROM v$database;

lab_conf lab_conf

rman target "'/ as sysbackup'"

BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
delete noprompt obsolete;

list failure;
exit

-- create guarenteed restore point
CREATE RESTORE POINT rpl GUARANTEE FLASHBACK DATABASE;

-- confirm
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;

lab_conf

Lab: Performing Flashback Database

sqlplus / as sysdba

ALTER SESSION set container=orclpdb;

SELECT current_scn
FROM v$database;
-- 8767254

SELECT sum (salary)
FROM hr.employees;

SELECT count (*)
FROM hr.employees
WHERE department_id=90;

update hr.employees
set department_id = 90
where job_id = 'IT_PROG';
-- 5 rows updated.

update hr.employees e
set salary = least(e.salary,
(select (min_salary + max_salary)/2 * 1.10
from hr.jobs j
where j.job_id = e.job_id))
where job_id not like 'AD_%';
-- 103 rows updated.

COMMIT;

SELECT current_scn
FROM v$database;
-- 8767447

SELECT sum(salary)
FROM hr.employees;
-- 679092.4

SELECT count(*)
FROM hr.employees
WHERE department_id=90;
-- 8

ALTER SESSION set container=cdb$root;

ALTER PLUGGABLE DATABASE orclpdb CLOSE;

FLASHBACK PLUGGABLE DATABASE orclpdb TO SCN 8767254;

-- Attempt to open the pluggable database orclpdbl.
ALTER PLUGGABLE DATABASE orclpdb OPEN;
/* Error starting at line : 87 in command -
ALTER PLUGGABLE DATABASE orclpdb OPEN
Error report -
ORA-01113: file 109 needs media recovery
ORA-01110: data file 109: '/u01/app/oracle/oradata/ORCL/orclpdb/bctbs01.dbf'
01113. 00000 -  "file %s needs media recovery"
*Cause:    An attempt was made to online or open a database with a file that
           is in need of media recovery.
*Action:   First apply media recovery to the file. */


-- optional: open in read-only mode to verify
ALTER PLUGGABLE DATABASE orclpdb OPEN READ ONLY;
ALTER SESSION set container=orclpdb;
-- do som query to verify

-- Open the pluggable database orclpdbl using the reset logs option.
ALTER PLUGGABLE DATABASE orclpdb OPEN RESETLOGS;

ALTER SESSION set container = orclpdb;
show con_name

SELECT sum(salary)
FROM hr.employees;
-- 691416

SELECT count(*)
FROM hr.employees
WHERE department_id=90;
-- 3

ALTER SESSION set container=cdb$root;

-- drop restore point
DROP RESTORE POINT rpl;
-- note: rpl is in cdb root.

-- confirm
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;