Determining the Current Flashback Database Window
At any given time, the earliest point in time to which you can actually rewind your database by using Flashback Database can be determined by querying the V$FLASHBACK_DATABASE_LOG view as shown in this example:
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM V$FLASHBACK_DATABASE_LOG;
Monitoring Flashback Logs
Monitoring the flashback logs can be monitored to help understand the overhead and the nature of flashback log generation.
SQL> select * from v$flashback_database_stat ;
Determine Transactional Flashback
Definitions
Current Undo Size – Current space for the undo tablespace in MB
Current undo_retention Setting – value of undo_retention database initialization parameter
Optimal undo_retention Setting – current capability of undo retention in seconds based on undo transactional activity and current undo size.
Desired undo_retention Setting – value in seconds to meeting undo/transactional flashback requirements
Steps to Gather Data for undo/transactional Flashback
1. Locate/Create see_optimal_undo_retention.
See Appendix A: see_optimal_undo_retention.sql
2. Execute see_optimal_undo_retention.sql
SQL> @see_optimal_undo_retention.sql
3. Enter Desired Undo Retention in Seconds
259200 -- 72 hours or 3 days in seconds
4. Evaluate output results
Output from see_optimal_undo_retention.sql
Enter Desired Undo Retention in Seconds ->259200
old 2: v_desired_undo_retention NUMBER := &desired_undo_retention ;
new 2: v_desired_undo_retention NUMBER := 259200 ;
Current undo Size --> 16912MB
Current undo_retention Setting --> 25200 Seconds
Optimal undo_retention Setting --> 221129.88 Seconds
Desired undo_retention Setting --> 259200 Seconds
Undo Size for desired retention --> 10021.36MB
Evaluating output results from see_optimal_undo_retention.sql
Is the current undo_retention optimal?
To evaluate if the current undo_retention is being met and see if undo_retention can be increased look at the current undo_retention setting and the optimal undo_retention_setting over several executions over a period of time. If the optimal undo_retention_setting is consistently more then the current undo_retention_setting then undo_retention can be increased.
Can the database meet the desired undo_retention?
The main consideration from the output is boiled down to the current value of the desired undo_retention setting and the optimal undo retention setting. When the optimal undo retention setting is lower then the desired undo_retention setting it means that the database is not able to meet the desired retention. Evaluate the undo size for desired retention to determine the undo tablespace size require to minimally meet the desired undo_retention.
Monitor Undo Retention
With undo_retention set we need to ensure that the undo_retention is being met by the database. Therefore the undo_retention capability must be consistently monitored.
1. Locate/create verify_undo_retention_target.sql
See Appendix B: verify_undo_retention_target.sql
2. Execute the verify_undo_retention.sql script
SQL> @verify_undo_retention_target
3. Evaluate output from script
Example Output when Undo Retention is being met
SQL> @verify_undo_retention_target
Example Output when Undo Retention is no being met
SQL> @verify_undo_retention_target
Undo Retention Target can not be Met! Undo Adjustments Needed!
---------------------------------------------------------------
Current undo Size --> 30MB
Current undo_retention Setting --> 2000 Seconds
Current undo_retention Capable --> 1106.1 Seconds
Undo Size for desired retention --> 54.24MB
Performance Tuning for Flashback Database
Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead.
To achieve good performance for large production databases with Flashback Database enabled, Oracle recommends:
· Use a fast file system for your flash recovery area, preferably without operating system file caching. Files the database creates in the flash recovery area, including flashback logs, are typically large. Operating system file caching is typically not effective for these files, and may actually add CPU overhead for reading from and writing to these files. Thus, it is recommended to use a file system that avoids operating system file caching, such as ASM, or the Solaris 2.8 file system with direct I/O.
· Configure enough disk spindles for the file system that will hold the flash recovery area. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively.
· If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance
· For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.
The overhead of turning on logging for Flashback Database depends on the read-write mix of the database workload. The more write-intensive the workload, the higher the overhead caused by turning on logging for Flashback Database. (Queries do not change data and thus do not contribute to logging activity for Flashback Database.)
Appendix A: see_optimal_undo_retention.sql
---------------------------------------------------------------------------------
-- Script: see_optimal_undo_retention.sql
-- Author: Michael Messina
--
-- Description: Will calculate based on undo utilization and undo size
-- the optimal value for undo_retention.
---------------------------------------------------------------------------------
set serveroutput on size 1000000
set feedback off
ACCEPT desired_undo_retention PROMPT "Enter Desired Undo Retention in Seconds ->"
DECLARE
v_desired_undo_retention NUMBER := &desired_undo_retention ;
v_block_size NUMBER ;
v_undo_size NUMBER ;
v_undo_blocks_per_sec NUMBER ;
v_optimal_undo_retention NUMBER ;
v_current_undo_retention NUMBER ;
v_undo_size_desired_ret NUMBER ;
BEGIN
-- get the current undo retention setting
select TO_NUMBER(value)
INTO v_current_undo_retention
FROM v$parameter
WHERE name = 'undo_retention' ;
-- Calculate Actual Undo Size
SELECT SUM(a.bytes)
INTO v_undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
-- Calcuate the Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
INTO v_undo_blocks_per_sec
FROM v$undostat ;
-- Get the database block size
SELECT TO_NUMBER(value)
INTO v_block_size
FROM v$parameter
WHERE name = 'db_block_size';
v_optimal_undo_retention := v_undo_size/(v_block_size * v_undo_blocks_per_sec) ;
v_undo_size_desired_ret := ((v_block_size * v_undo_blocks_per_sec) * (v_desired_undo_retention)) / 1024 / 1024 ;
DBMS_OUTPUT.PUT_LINE (' Current undo Size --> ' TO_CHAR(v_undo_size/1024/1024) 'MB') ;
DBMS_OUTPUT.PUT_LINE (' Current undo_retention Setting --> ' TO_CHAR(v_current_undo_retention) ' Seconds') ;
DBMS_OUTPUT.PUT_LINE (' Optimal undo_retention Setting --> ' TO_CHAR(ROUND(v_optimal_undo_retention,2)) ' Seconds') ;
DBMS_OUTPUT.PUT_LINE (' Desired undo_retention Setting --> ' TO_CHAR(ROUND(v_desired_undo_retention,2)) ' Seconds') ;
DBMS_OUTPUT.PUT_LINE ('Undo Size for desired retention --> ' TO_CHAR(ROUND(v_undo_size_desired_ret,2)) 'MB') ;
END ;
/
Appendix B: verify_undo_retention_target.sql
---------------------------------------------------------------------------------
-- Script: verify_undo_retention_target.sql
-- Author: Michael Messina
--
-- Description: Will calculate based on undo utilization and undo size
-- the optimal value for undo_retention and compare against
-- current undo_retention value.
---------------------------------------------------------------------------------
set serveroutput on size 1000000
set feedback off
DECLARE
v_block_size NUMBER ;
v_undo_size NUMBER ;
v_undo_blocks_per_sec NUMBER ;
v_optimal_undo_retention NUMBER ;
v_current_undo_retention NUMBER ;
v_undo_size_desired_ret NUMBER ;
BEGIN
-- get the current undo retention setting
select TO_NUMBER(value)
INTO v_current_undo_retention
FROM v$parameter
WHERE name = 'undo_retention' ;
-- Calculate Actual Undo Size
SELECT SUM(a.bytes)
INTO v_undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
-- Calculate the Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
INTO v_undo_blocks_per_sec
FROM v$undostat ;
-- Get the database block size
SELECT TO_NUMBER(value)
INTO v_block_size
FROM v$parameter
WHERE name = 'db_block_size';
v_optimal_undo_retention := v_undo_size/(v_block_size * v_undo_blocks_per_sec) ;
if v_current_undo_retention > v_optimal_undo_retention then
v_undo_size_desired_ret := ((v_block_size * v_undo_blocks_per_sec) * (v_current_undo_retention)) / 1024
/ 1024 ;
DBMS_OUTPUT.PUT_LINE ('Undo Retention Target can not be Met! Undo Adjustments Needed!') ;
DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------------------') ;
DBMS_OUTPUT.PUT_LINE (' Current undo Size --> ' TO_CHAR(v_undo_size/1024/1024) 'MB')
;
DBMS_OUTPUT.PUT_LINE (' Current undo_retention Setting --> ' TO_CHAR(v_current_undo_retention) ' S
econds') ;
DBMS_OUTPUT.PUT_LINE (' Current undo_retention Capable --> ' TO_CHAR(ROUND(v_optimal_undo_retention,2
)) ' Seconds') ;
DBMS_OUTPUT.PUT_LINE ('Undo Size for desired retention --> ' TO_CHAR(ROUND(v_undo_size_desired_ret,2)
) 'MB') ;
end if ;
END ;
/
Wednesday, January 28, 2009
Oracle Flashback Table
Summary
Oracle Flashback Table provides the capability to essentially recover a single table or multiple tables to a prior specified point in time without taking impacting any part of the databases availability. Flashback Table allows the contents of one or more individual tables to revert to their state at some past SCN or time. Flashback Table can eliminate the need to perform auxiliary point-in-time recovery operations. Flashback Table can restore tables to a prior point in time while maintaining all associated indexes, triggers and constraints. This is most valuable when a large amount of data in the table has been removed or updated by a process and you want to bring the table back to the state prior to the process execution.
Flashback Table uses the undo tablespace block data to execute the restoration of the table. You do not have to restore any data from backups, and the rest of your database remains available while the Flashback Table operation is being performed.
Flashback Table does however require row movement to be enabled and the flashback object privilege or the flashback any table system privilege in addition to select, insert, update, delete and alter permissions on the table.
** Note: This is a permanent process, once the table is flashed back you can not bring it back forward again, therefore be careful about the flash back table operations you perform.
Performing Flashback Table
Simple example will utilize the scott.emp table with the following records:
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Setting up for the example we will remove some rows from the scott.emp table.
SQL> select to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') from dual ;
TO_CHAR(SYSDATE,'MM
-------------------
01/23/2009 08:38:01
SQL> delete from scott.emp where empno = 7369 ;
1 row deleted.
SQL> delete from scott.emp where empno = 7782 ;
1 row deleted.
SQL> commit ;
Commit complete.
1. Verify the data is missing. In the example setup above we will check scott.emp for the missing records.
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
Or a way to show what is missing or updated using a flashback query.
SQL> select * from scott.emp as of timestamp to_timestamp('01/23/2009 08:38:00','mm/dd/yyyy hh24:mi:ss') minus select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
2. Identify the time or SCN the table has to go back to.
3. Flashback the table
SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
Table altered.
SQL> FLASHBACK TABLE SCOTT.EMP TO TIMESTAMP
2 TO_TIMESTAMP('2009-01-23 08:38:00', 'YYYY-MM-DD HH24:MI:SS')
3 ;
Flashback complete.
4. Check the table and verify it is back to prior state.
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
5. Show that the operation can not be rolled back.
SQL> rollback ;
Rollback complete.
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
6. Even if we exit the session and log back in the changes are still in place
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus mrmessin@orcl
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 23 08:56:43 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Oracle Flashback Table provides the capability to essentially recover a single table or multiple tables to a prior specified point in time without taking impacting any part of the databases availability. Flashback Table allows the contents of one or more individual tables to revert to their state at some past SCN or time. Flashback Table can eliminate the need to perform auxiliary point-in-time recovery operations. Flashback Table can restore tables to a prior point in time while maintaining all associated indexes, triggers and constraints. This is most valuable when a large amount of data in the table has been removed or updated by a process and you want to bring the table back to the state prior to the process execution.
Flashback Table uses the undo tablespace block data to execute the restoration of the table. You do not have to restore any data from backups, and the rest of your database remains available while the Flashback Table operation is being performed.
Flashback Table does however require row movement to be enabled and the flashback object privilege or the flashback any table system privilege in addition to select, insert, update, delete and alter permissions on the table.
** Note: This is a permanent process, once the table is flashed back you can not bring it back forward again, therefore be careful about the flash back table operations you perform.
Performing Flashback Table
Simple example will utilize the scott.emp table with the following records:
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Setting up for the example we will remove some rows from the scott.emp table.
SQL> select to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') from dual ;
TO_CHAR(SYSDATE,'MM
-------------------
01/23/2009 08:38:01
SQL> delete from scott.emp where empno = 7369 ;
1 row deleted.
SQL> delete from scott.emp where empno = 7782 ;
1 row deleted.
SQL> commit ;
Commit complete.
1. Verify the data is missing. In the example setup above we will check scott.emp for the missing records.
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
Or a way to show what is missing or updated using a flashback query.
SQL> select * from scott.emp as of timestamp to_timestamp('01/23/2009 08:38:00','mm/dd/yyyy hh24:mi:ss') minus select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
2. Identify the time or SCN the table has to go back to.
3. Flashback the table
SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
Table altered.
SQL> FLASHBACK TABLE SCOTT.EMP TO TIMESTAMP
2 TO_TIMESTAMP('2009-01-23 08:38:00', 'YYYY-MM-DD HH24:MI:SS')
3 ;
Flashback complete.
4. Check the table and verify it is back to prior state.
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
5. Show that the operation can not be rolled back.
SQL> rollback ;
Rollback complete.
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
6. Even if we exit the session and log back in the changes are still in place
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus mrmessin@orcl
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 23 08:56:43 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Oracle Flashback Query
Summary
Oracle Flashback Query feature utilizes the undo segments within the undo tablespace to execute a query as the query would have returned at a prior point in time using a timestamp or SCN number. This feature is powerful in the event of erroneously delete or update rows. How far back to a previous point in time Flashback Query is capable of executing directly depends on the transaction rate of you database in relationship to the size of the undo tablespace. While the undo_retention database initialization parameter is used to target the undo retention time that makes flashback query possible there must be enough undo tablespace to handle the transactional activity of the database to meet the undo_retention target. The undo_retention is a target and if the transactional activity of the database is high enough and the database needs undo space to continue executing transaction it will overwrite undo information needed to meet the retention. Keep this in mind when setting up and planning your undo retention capabilities.
Executing a Flashback Query
Procedure assumes that the flashback capabilities were setup and tuned as outline in Setup Oracle Transactional Flashback Capability (Automatic Undo)
Simple examples will utilize the scott.emp table with the following records:
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Delete Example
Setting up for the example we will remove some rows from the scott.emp table.
SQL> select to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') from dual ;
TO_CHAR(SYSDATE,'MM
-------------------
01/21/2009 09:53:21
SQL> delete from scott.emp where empno = 7369 ;
1 row deleted.
SQL> delete from scott.emp where empno = 7782 ;
1 row deleted.
SQL> commit ;
Commit complete.
1. Verify the data is missing. In the example setup above we will check scott.emp for the missing records.
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
2. Next step is to identify what prior point in time the table has the records you are looking for. This will more then likely require input from the customer that reporting the missing data.
In the example case we will want to go back before 01/21/2009 9:53am.
3. Execute the query with flashback syntax using a timestamp
SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 09:53:00','mm/dd/yyyy hh24:mi:ss') minus select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
OR
SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 09:53:00','mm/dd/yyyy hh24:mi:ss') ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
** 7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
** 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
** As you can see the rows deleted are there……
4. Execute Repair and put the deleted rows back
SQL> insert into scott.emp values ('7369','SMITH','CLERK','7902', TO_DATE('17-DEC-1980','DD-MON-YYYY'),800,NUL
L,20) ;
1 row created.
SQL> insert into scott.emp values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-1981','DD-MON-YYYY'),2450,N
ULL,10) ;
1 row created.
SQL> commit ;
Commit complete.
Update Example
Setting up for the example we will update some rows from the scott.emp table.
SQL> select to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') from dual ;
TO_CHAR(SYSDATE,'MM
-------------------
01/22/2009 10:36:18
SQL> update scott.emp set sal=10000 where empno=7369 ;
1 row updated.
SQL> update scott.emp set sal=10000 where empno=7782 ;
1 row updated.
SQL> commit ;
Commit complete.
1. Verify the data is updated
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 10000 20
7782 CLARK MANAGER 7839 09-JUN-81 10000 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
2. Next step is to identify what prior point in time the table has the records correctly. This will more then likely require input from the customer that reporting the missing data.
3. Execute the query with flashback syntax using a timestamp
SQL> select * from scott.emp as of timestamp to_timestamp('01/22/2009 10:36:00','mm/dd/yyyy hh24:mi:ss') ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
OR Show only the rows that are different.
SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 10:36:00','mm/dd/yyyy hh24:mi:ss')
2 minus
3 select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
4. Execute Repair and put the deleted rows back
SQL> update scott.emp set sal=800 where empno=7369 ;
1 row updated.
SQL> update scott.emp set sal=2450 where empno=7782 ;
1 row updated.
SQL> commit ;
Commit complete.
Oracle Flashback Query feature utilizes the undo segments within the undo tablespace to execute a query as the query would have returned at a prior point in time using a timestamp or SCN number. This feature is powerful in the event of erroneously delete or update rows. How far back to a previous point in time Flashback Query is capable of executing directly depends on the transaction rate of you database in relationship to the size of the undo tablespace. While the undo_retention database initialization parameter is used to target the undo retention time that makes flashback query possible there must be enough undo tablespace to handle the transactional activity of the database to meet the undo_retention target. The undo_retention is a target and if the transactional activity of the database is high enough and the database needs undo space to continue executing transaction it will overwrite undo information needed to meet the retention. Keep this in mind when setting up and planning your undo retention capabilities.
Executing a Flashback Query
Procedure assumes that the flashback capabilities were setup and tuned as outline in Setup Oracle Transactional Flashback Capability (Automatic Undo)
Simple examples will utilize the scott.emp table with the following records:
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Delete Example
Setting up for the example we will remove some rows from the scott.emp table.
SQL> select to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') from dual ;
TO_CHAR(SYSDATE,'MM
-------------------
01/21/2009 09:53:21
SQL> delete from scott.emp where empno = 7369 ;
1 row deleted.
SQL> delete from scott.emp where empno = 7782 ;
1 row deleted.
SQL> commit ;
Commit complete.
1. Verify the data is missing. In the example setup above we will check scott.emp for the missing records.
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
2. Next step is to identify what prior point in time the table has the records you are looking for. This will more then likely require input from the customer that reporting the missing data.
In the example case we will want to go back before 01/21/2009 9:53am.
3. Execute the query with flashback syntax using a timestamp
SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 09:53:00','mm/dd/yyyy hh24:mi:ss') minus select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
OR
SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 09:53:00','mm/dd/yyyy hh24:mi:ss') ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
** 7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
** 7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
** As you can see the rows deleted are there……
4. Execute Repair and put the deleted rows back
SQL> insert into scott.emp values ('7369','SMITH','CLERK','7902', TO_DATE('17-DEC-1980','DD-MON-YYYY'),800,NUL
L,20) ;
1 row created.
SQL> insert into scott.emp values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-1981','DD-MON-YYYY'),2450,N
ULL,10) ;
1 row created.
SQL> commit ;
Commit complete.
Update Example
Setting up for the example we will update some rows from the scott.emp table.
SQL> select to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss') from dual ;
TO_CHAR(SYSDATE,'MM
-------------------
01/22/2009 10:36:18
SQL> update scott.emp set sal=10000 where empno=7369 ;
1 row updated.
SQL> update scott.emp set sal=10000 where empno=7782 ;
1 row updated.
SQL> commit ;
Commit complete.
1. Verify the data is updated
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 10000 20
7782 CLARK MANAGER 7839 09-JUN-81 10000 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
2. Next step is to identify what prior point in time the table has the records correctly. This will more then likely require input from the customer that reporting the missing data.
3. Execute the query with flashback syntax using a timestamp
SQL> select * from scott.emp as of timestamp to_timestamp('01/22/2009 10:36:00','mm/dd/yyyy hh24:mi:ss') ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
OR Show only the rows that are different.
SQL> select * from scott.emp as of timestamp to_timestamp('01/21/2009 10:36:00','mm/dd/yyyy hh24:mi:ss')
2 minus
3 select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
4. Execute Repair and put the deleted rows back
SQL> update scott.emp set sal=800 where empno=7369 ;
1 row updated.
SQL> update scott.emp set sal=2450 where empno=7782 ;
1 row updated.
SQL> commit ;
Commit complete.
Setup Oracle Flashback Database
Requirements for Flashback Database
1. Your database must be running in ARCHIVELOG mode.
2. You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
3. For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.
4. To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter
Setups for Setup Oracle Flashback Database
1. Check that the db_flashback_retention_target, db_recovery_file_dest and db_recovery_file_dest_size are set
SQL> select name, value from v$parameter where name like '%flash%';
NAME VALUE
-------------------------------- --------------------------------------- db_flashback_retention_target 1440
SQL> select name, value from v$parameter where name like '%recovery%';
NAME VALUE
------------------------ --------------------------------------- db_recovery_file_dest /opt/oracle/flash_recovery_area
db_recovery_file_dest_size 2147483648
2. Make sure the database is in archive log mode.
SQL> select name, log_mode from v$database ;
NAME LOG_MODE
--------- ------------
ORCL NOARCHIVELOG
3. If database not in archive log mode turn on Archive log mode.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1299988 bytes
Variable Size 268437996 bytes
Database Buffers 92274688 bytes
Redo Buffers 6250496 bytes
Database mounted.
SQL> alter database archivelog ;
Database altered.
SQL> alter database open ;
Database altered.
4. Turn the flashback on
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1299988 bytes
Variable Size 268437996 bytes
Database Buffers 92274688 bytes
Redo Buffers 6250496 bytes
Database mounted.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> alter database open ;
Database altered.
1. Your database must be running in ARCHIVELOG mode.
2. You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
3. For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.
4. To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter
Setups for Setup Oracle Flashback Database
1. Check that the db_flashback_retention_target, db_recovery_file_dest and db_recovery_file_dest_size are set
SQL> select name, value from v$parameter where name like '%flash%';
NAME VALUE
-------------------------------- --------------------------------------- db_flashback_retention_target 1440
SQL> select name, value from v$parameter where name like '%recovery%';
NAME VALUE
------------------------ --------------------------------------- db_recovery_file_dest /opt/oracle/flash_recovery_area
db_recovery_file_dest_size 2147483648
2. Make sure the database is in archive log mode.
SQL> select name, log_mode from v$database ;
NAME LOG_MODE
--------- ------------
ORCL NOARCHIVELOG
3. If database not in archive log mode turn on Archive log mode.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1299988 bytes
Variable Size 268437996 bytes
Database Buffers 92274688 bytes
Redo Buffers 6250496 bytes
Database mounted.
SQL> alter database archivelog ;
Database altered.
SQL> alter database open ;
Database altered.
4. Turn the flashback on
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1299988 bytes
Variable Size 268437996 bytes
Database Buffers 92274688 bytes
Redo Buffers 6250496 bytes
Database mounted.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> alter database open ;
Database altered.
Setup Oracle Transactional Flashback Capability (Automatic Undo)
1. Check if Automatic Undo Management is begin used, undo tablespace is set and undo_retention is set?
SQL> select name, value from v$parameter where name like 'undo%';
NAME VALUE
---------------------- -----------------------------------------
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900
2. If Not Using Automatic Undo Setup Automatic Undo
Create undo tablespace
create undo tablespace undotbs1 datafile ‘c:\oracle\data\ORCL\undotbl1.dbf’ size 500M ;
Set undo_tablespace to undo tablespace created
Alter system set undo_tablespace=undotbs1 scope=spfile ;
Set undo_retention to time period for transactional flashbacks alter system set undo_retention=900 scope=spfile ;
3. If had to setup auto undo bounce database
SQL> shutdown immediate
SQL> startup
4. Update transactional flashback sizing and retention over time
See Sizing Transactional Flashback Capabilities - Sizing the undo tablespace and undo_retention
Oracle Flashback Transaction Query
Summary
Flashback Transaction Query has a lot of the same functionality as Log Miner without the additional overhead associated with Log Miner mining redo logs for undo information. Flashback Transaction Query utilizes an access path to undo data which makes it faster than LogMiner at extracting the undo information. Unlike flashback query, flashback transaction query requires the select any transaction system privilege. How far back to a previous point in time Flashback Transaction Query is capable of executing directly depends on the transaction rate of you database in relationship to the size of the undo tablespace. While the undo_retention database initialization parameter is used to target the undo retention time that makes flashback query possible there must be enough undo tablespace to handle the transactional activity of the database to meet the undo_retention target. The undo_retention is a target and if the transactional activity of the database is high enough and the database needs undo space to continue executing transaction it will overwrite undo information needed to meet the retention. Keep this in mind when setting up and planning your undo retention capabilities.
See Monitoring and Tuning Flashback Capabilities
Executing Flashback Transaction Query
Procedure assumes that the transaction flashback capabilities were setup and tuned as outline in Setup Transactional Flashback Capability (Automatic Undo)
Simple example will utilize the scott.emp table with the following records:
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
1. Record current scn prior to making any changes
SQL> select current_scn scn from sys.v_$database ;
SCN
----------
1068761
2. Change some records with various inserts, updates and deletes.
SQL> delete from scott.emp where empno = 7369 ;
1 row deleted.
SQL> delete from scott.emp where empno = 7782 ;
1 row deleted.
SQL> update scott.emp set sal=10000 where empno = 7876 ;
1 row updated.
SQL> update scott.emp set sal=0 where empno = 7839 ;
1 row updated.
SQL> commit ;
Commit complete.
3. Verify data changes
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 0 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 10000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
4. Review Undo for Changes Made
SQL> select /*undo_change#,*/ undo_sql
2 from flashback_transaction_query
3 where table_name = 'EMP' and
4 table_owner = 'SCOTT' and
5 logon_user = 'SYS' and
6 commit_scn > 1068761
7 order by undo_change# ;
UNDO_SQL
----------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');
5. Undo Changes
See Example Script Below Appendix A: Fix Script Example using Flashback Transaction Query
SQL> @flashback_transaction_query.sql
Enter value for scnnumber: 1068761
old 8: commit_scn > &scnnumber
new 8: commit_scn > 1068761
undo_change#: 1
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
undo_change#: 2
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
undo_change#: 3
insert into
"SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81',
'DD-MON-RR'),'2450',NULL,'10');
undo_change#: 4
insert into
"SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80',
'DD-MON-RR'),'800',NULL,'20');
PL/SQL procedure successfully completed.
SQL> commit ;
Commit complete.
Or execute the SQL statements from the prior query
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');
6. Verify Changes are Un-done
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Appendix A: Fix Script Example using Flashback Transaction Query
SET SERVEROUTPUT ON SIZE 1000000
begin
for r in (
select /*undo_change#,*/ undo_sql
from flashback_trasnaction_query
where table_name = 'EMP' and
table_owner = 'SCOTT' and
logon_user = 'SYS' and
commit_scn > &scnnumber
order by undo_change#
) loop
dbms_output.put_line('r.undo_change#: ' r.undo_change#);
dbms_output.put_line(' ' r.undo_sql);
execute immediate
substr(r.undo_sql, 1, length(r.undo_sql)-1);
end loop;
end ;
/
Flashback Transaction Query has a lot of the same functionality as Log Miner without the additional overhead associated with Log Miner mining redo logs for undo information. Flashback Transaction Query utilizes an access path to undo data which makes it faster than LogMiner at extracting the undo information. Unlike flashback query, flashback transaction query requires the select any transaction system privilege. How far back to a previous point in time Flashback Transaction Query is capable of executing directly depends on the transaction rate of you database in relationship to the size of the undo tablespace. While the undo_retention database initialization parameter is used to target the undo retention time that makes flashback query possible there must be enough undo tablespace to handle the transactional activity of the database to meet the undo_retention target. The undo_retention is a target and if the transactional activity of the database is high enough and the database needs undo space to continue executing transaction it will overwrite undo information needed to meet the retention. Keep this in mind when setting up and planning your undo retention capabilities.
See Monitoring and Tuning Flashback Capabilities
Executing Flashback Transaction Query
Procedure assumes that the transaction flashback capabilities were setup and tuned as outline in Setup Transactional Flashback Capability (Automatic Undo)
Simple example will utilize the scott.emp table with the following records:
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
1. Record current scn prior to making any changes
SQL> select current_scn scn from sys.v_$database ;
SCN
----------
1068761
2. Change some records with various inserts, updates and deletes.
SQL> delete from scott.emp where empno = 7369 ;
1 row deleted.
SQL> delete from scott.emp where empno = 7782 ;
1 row deleted.
SQL> update scott.emp set sal=10000 where empno = 7876 ;
1 row updated.
SQL> update scott.emp set sal=0 where empno = 7839 ;
1 row updated.
SQL> commit ;
Commit complete.
3. Verify data changes
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 0 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 10000 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
4. Review Undo for Changes Made
SQL> select /*undo_change#,*/ undo_sql
2 from flashback_transaction_query
3 where table_name = 'EMP' and
4 table_owner = 'SCOTT' and
5 logon_user = 'SYS' and
6 commit_scn > 1068761
7 order by undo_change# ;
UNDO_SQL
----------------------------------------------------------------------------------------------------
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');
5. Undo Changes
See Example Script Below Appendix A: Fix Script Example using Flashback Transaction Query
SQL> @flashback_transaction_query.sql
Enter value for scnnumber: 1068761
old 8: commit_scn > &scnnumber
new 8: commit_scn > 1068761
undo_change#: 1
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
undo_change#: 2
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
undo_change#: 3
insert into
"SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81',
'DD-MON-RR'),'2450',NULL,'10');
undo_change#: 4
insert into
"SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO")
values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80',
'DD-MON-RR'),'800',NULL,'20');
PL/SQL procedure successfully completed.
SQL> commit ;
Commit complete.
Or execute the SQL statements from the prior query
update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAAMlsAAEAAAAAgAAI';
update "SCOTT"."EMP" set "SAL" = '1100' where ROWID = 'AAAMlsAAEAAAAAgAAK';
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('09-JUN-81', 'DD-MON-RR'),'2450',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('17-DEC-80', 'DD-MON-RR'),'800',NULL,'20');
6. Verify Changes are Un-done
SQL> select * from scott.emp ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Appendix A: Fix Script Example using Flashback Transaction Query
SET SERVEROUTPUT ON SIZE 1000000
begin
for r in (
select /*undo_change#,*/ undo_sql
from flashback_trasnaction_query
where table_name = 'EMP' and
table_owner = 'SCOTT' and
logon_user = 'SYS' and
commit_scn > &scnnumber
order by undo_change#
) loop
dbms_output.put_line('r.undo_change#: ' r.undo_change#);
dbms_output.put_line(' ' r.undo_sql);
execute immediate
substr(r.undo_sql, 1, length(r.undo_sql)-1);
end loop;
end ;
/
Wednesday, January 14, 2009
RMAN Backup Encryption
Summary
Oracle backups are necessary to ensure that data is not lost in the event of hardware failures. RMAN is the Oracle recommended way to backup Oracle databases. RMAN has significantly improve Oracle database backups through multi-threaded hot backups, compression, simplified recovery and others. However backups are a way the database data could be exposed. Oracle backups by default are not encrypted and therefore unprotected should the backup be exposed to outside copies that are capable of being restored sue to lack of protection. So how to protect RMAN backups from being able to be restored by unauthorized persons and exposing the data? The answer is encrypting the RMAN backups. RMAN backup encryption requires the use of the Advanced Security Option (ASO). Advanced Security Option requires an additional license therefore before using check your Oracle license and ensure you are authorized to use the Advanced Security Option. Keep in mind that image and datafile copies can not be encrypted.
Oracle offers several flavors for encrypting the RMAN backups:
1. Transparent Encryption -> Requires Wallet on backup and recovery. (Do Not Lose the Wallet!)
2. Password Only -> Required Password on backup and recovery (Do Not Lose Password!)
3. Dual (Password or Transparent Encryption) -> Can be backed up or restored using the password or a wallet. This works well where database is restored locally where the wallet exists, but has a need to be able to be restored off site where the wallet does not exist.
When restoring encrypted backups Oracle RMAN always assumes transparent encryption using a wallet. Therefore during a restore operation and using transparent data encryption ensure the wallet is open, when using password only the password must be supplied, when using dual either the wallet must be open or the password must be supplied.
Setup and Create a Wallet for TDE
1. Set the encryptrion wallet location in the sqlnet.ora file on the database server
Unix server sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /cnd7bsw/oracle/network/admin/encrypt)
)
)
WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /cnd7bsw/oracle/network/admin/authent)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0
2. Create the wallet
o Ensure the oracle account running the database has permissions on the encryption wallet directory defined in the sqlnet.ora file or an ORA-28368: cannot auto-create wallet will occur.
o sqlplus “/ AS SYSDBA”
o SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “mypass” ;
Opening and Closing the Wallet
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY password>
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE
Encrypting the RMAN Backup
1. Check the available algorithms for encryption.
SELECT * FROM v$rman_encryption_algorithms ORDER BY algorithm_name ;
ALGORITHM_ID ALGORITHM_NAME
------------ ----------------------------------------------------------------
ALGORITHM_DESCRIPTION IS_ RES
---------------------------------------------------------------- --- ---
1 AES128
AES 128-bit key YES NO
2 AES192
AES 192-bit key NO NO
3 AES256
AES 256-bit key NO NO
2. Set the Backup Encryption Type via RMAN
Connect to the target database and RMAN catalog database if being used. Within RMAN set the encryption type via an RMAN configure command. This setting will be stored within the database control file and the RMAN catalog if one is being used.
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES192';
3. Configure the encryption for the backup
Transparent Backup Encryption (Requires Wallet)
CONFIGURE ENCRYPTION FOR DATABASE ON ;
Turn Encryption off
CONFIGURE ENCRYPTION FOR DATABASE OFF ;
Password Only Encryption
SET ENCRYPTION ON IDENTIFIED BY password ONLY ;
Dual Password/Transparent Encryption
SET ENCRYPTION ON IDENTIFIED BY password ;
4. Execute the backup Database Backup Encrypted
Transparent Encryption** Wallet Must be opened
RMAN> RUN {
# Set the RMAN Encryption
CONFIGURE ENCRYPTION FOR DATABASE ON ;
# Backup the database
BACKUP DATABASE ;
}
Database Backup Encrypted using Password Encryption Only
RMAN> RUN {
# Set password Encryption
SET ENCRYPTION ON IDENTIFIED BYONLY ;
# Backup the database
BACKUP DATABASE ;
}
Database Backup Encrypted using Password Encryption or Wallet Encryption
RMAN> RUN {
# Set password Encryption
SET ENCRYPTION ON IDENTIFIED BY;
# Backup the database
BACKUP DATABASE ;
}
Tablespace Backup Encryption
RMAN> RUN {
# First, clear the current RMAN encryption settings ...
CONFIGURE ENCRYPTION FOR DATABASE OFF ;
# ... then activate encryption for specific tablespaces
CONFIGURE ENCRYPTION FOR TABLESPACE example ON;
CONFIGURE ENCRYPTION FOR TABLESPACE tbs_encrypted ON;
BACKUP TABLESPACE example, tbs_encrypted;
}
Restore Encrypted RMAN Backup
Transparent Encryption Restore
RMAN> RUN {
RESTORE DATABASE ;
RECOVER DATABASE ;
}
Password Encryption Restore
RMAN> RUN {
SET DECRYPTION IDENTIFIED BY;
RESTORE DATABASE ;
RECOVER DATABASE ;
}
Oracle backups are necessary to ensure that data is not lost in the event of hardware failures. RMAN is the Oracle recommended way to backup Oracle databases. RMAN has significantly improve Oracle database backups through multi-threaded hot backups, compression, simplified recovery and others. However backups are a way the database data could be exposed. Oracle backups by default are not encrypted and therefore unprotected should the backup be exposed to outside copies that are capable of being restored sue to lack of protection. So how to protect RMAN backups from being able to be restored by unauthorized persons and exposing the data? The answer is encrypting the RMAN backups. RMAN backup encryption requires the use of the Advanced Security Option (ASO). Advanced Security Option requires an additional license therefore before using check your Oracle license and ensure you are authorized to use the Advanced Security Option. Keep in mind that image and datafile copies can not be encrypted.
Oracle offers several flavors for encrypting the RMAN backups:
1. Transparent Encryption -> Requires Wallet on backup and recovery. (Do Not Lose the Wallet!)
2. Password Only -> Required Password on backup and recovery (Do Not Lose Password!)
3. Dual (Password or Transparent Encryption) -> Can be backed up or restored using the password or a wallet. This works well where database is restored locally where the wallet exists, but has a need to be able to be restored off site where the wallet does not exist.
When restoring encrypted backups Oracle RMAN always assumes transparent encryption using a wallet. Therefore during a restore operation and using transparent data encryption ensure the wallet is open, when using password only the password must be supplied, when using dual either the wallet must be open or the password must be supplied.
Setup and Create a Wallet for TDE
1. Set the encryptrion wallet location in the sqlnet.ora file on the database server
Unix server sqlnet.ora
ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /cnd7bsw/oracle/network/admin/encrypt)
)
)
WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY= /cnd7bsw/oracle/network/admin/authent)
)
)
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0
2. Create the wallet
o Ensure the oracle account running the database has permissions on the encryption wallet directory defined in the sqlnet.ora file or an ORA-28368: cannot auto-create wallet will occur.
o sqlplus “/ AS SYSDBA”
o SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY “mypass” ;
Opening and Closing the Wallet
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY password>
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE
Encrypting the RMAN Backup
1. Check the available algorithms for encryption.
SELECT * FROM v$rman_encryption_algorithms ORDER BY algorithm_name ;
ALGORITHM_ID ALGORITHM_NAME
------------ ----------------------------------------------------------------
ALGORITHM_DESCRIPTION IS_ RES
---------------------------------------------------------------- --- ---
1 AES128
AES 128-bit key YES NO
2 AES192
AES 192-bit key NO NO
3 AES256
AES 256-bit key NO NO
2. Set the Backup Encryption Type via RMAN
Connect to the target database and RMAN catalog database if being used. Within RMAN set the encryption type via an RMAN configure command. This setting will be stored within the database control file and the RMAN catalog if one is being used.
RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES192';
3. Configure the encryption for the backup
Transparent Backup Encryption (Requires Wallet)
CONFIGURE ENCRYPTION FOR DATABASE ON ;
Turn Encryption off
CONFIGURE ENCRYPTION FOR DATABASE OFF ;
Password Only Encryption
SET ENCRYPTION ON IDENTIFIED BY password ONLY ;
Dual Password/Transparent Encryption
SET ENCRYPTION ON IDENTIFIED BY password ;
4. Execute the backup Database Backup Encrypted
Transparent Encryption** Wallet Must be opened
RMAN> RUN {
# Set the RMAN Encryption
CONFIGURE ENCRYPTION FOR DATABASE ON ;
# Backup the database
BACKUP DATABASE ;
}
Database Backup Encrypted using Password Encryption Only
RMAN> RUN {
# Set password Encryption
SET ENCRYPTION ON IDENTIFIED BY
# Backup the database
BACKUP DATABASE ;
}
Database Backup Encrypted using Password Encryption or Wallet Encryption
RMAN> RUN {
# Set password Encryption
SET ENCRYPTION ON IDENTIFIED BY
# Backup the database
BACKUP DATABASE ;
}
Tablespace Backup Encryption
RMAN> RUN {
# First, clear the current RMAN encryption settings ...
CONFIGURE ENCRYPTION FOR DATABASE OFF ;
# ... then activate encryption for specific tablespaces
CONFIGURE ENCRYPTION FOR TABLESPACE example ON;
CONFIGURE ENCRYPTION FOR TABLESPACE tbs_encrypted ON;
BACKUP TABLESPACE example, tbs_encrypted;
}
Restore Encrypted RMAN Backup
Transparent Encryption Restore
RMAN> RUN {
RESTORE DATABASE ;
RECOVER DATABASE ;
}
Password Encryption Restore
RMAN> RUN {
SET DECRYPTION IDENTIFIED BY
RESTORE DATABASE ;
RECOVER DATABASE ;
}
Subscribe to:
Posts (Atom)
