Showing posts with label Backup and Recovery. Show all posts
Showing posts with label Backup and Recovery. Show all posts

Q&As: Hot Backups


(Q) Which statements about Open (hot/online) database backups are correct?

  • ( ) To perform HOT backups, the database MUST be in ARCHIVELOG mode
  • ( ) To perform a user-managed HOT backup, you MUST use BEGIN Backup.... END Backup.
  • ( ) You CAN perform closed database backups using RMAN.
  • ( ) You CAN run a Data Pump Export to perform a CONSISTENT LOGICAL backup of the database.
  • ( ) INCREMENTAL backups are supported, using RMAN.

Q&As: Cold database backups

(Q) Which statements about Closed (cold/offline) database backups are correct?

  • ( ) You CAN perform closed database backups using RMAN.
  • ( ) To perform closed db backups with RMAN, the instance MUST be MOUNTED
  • ( ) You CAN perform user-managed closed database backups (OFFLINE backups), following the steps below:
    • Run shell script to collect location of all datafiles, control files, redo log files, and parameter files
    • If using server parameter file, run command to backup spfile
    • Shutdown the database normal or immediate
    • Run OS script to copy all datafiles, control files, redo logs and parameter files to backup area
    • Startup the database
  • ( ) You CANNOT run a Data Pump Export on a closed database.

Q&As: Block Media Recovery


(Q) What is Block Media Recovery?

Recovery of specified blocks within a data file
RMAN> RECOVER ... BLOCK
Leaves the affected datafiles ONLINE and recovers only the damaged/corrupted data blocks.


$ rman
RMAN> connect target /
connected to target database: ORCL11G (DBID=123456789)

RMAN> recover datafile 2 block 13;
...

     (optionally)
RMAN> recover … 
        From backupset …
        Exclude flashback log

     (To recover ALL blocks logged in V$DATABASE_BLOCK_CORRUPTION)
RMAN> recover corruption list;

Q&As: Fast Recovery Area (II)


(Q) What initialization parameters are used to configure a Fast Recovery Area?

  • DB_RECOVERY_FILE_DEST - specifies default location for fast recovery area.
  • DB_RECOVERY_FILE_DEST_SIZE - specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the fast recovery area
  • (optional) DB_FLASHBACK_RETENTION_TARGET -Specifies the upper limit (minutes) on how far back in time the database may be flashed back.


(Q) Can you configure the Fast Recovery Area on an ASM disk group?

  • Yes


(Q) How large should you configure the Fast Recovery Area?


  • At an ABSOLUTE MINIMUM, it should be large enough to containt the archived redo logs not yet on tape.
  • Scenario:
    • If you use incrementally updated backups and set the backup retention policy to REDUNDANCY 1, you can determine the size of the fast recovery area as below:



Q&As: Backup with Recovery Manager



(Q) Which statements about using RMAN to perform backups are correct?


  • ( ) You CAN perform incremental backups with RMAN
  • ( ) RMAN detects corrupt blocks and logs in V$DATABASE_BLOCK_CORRUPTION
  • ( ) Automatically establishes name and locations of all files to be backed up
  • ( ) Backups are recorded in the CONTROL FILE, which is the main repository of RMAN metadata.
  • ( ) Optionally, you CAN store backup metadata in a recovery catalog, which should be a schema in a database different of the one backed up.
  • ( ) The primary storage for RMAN repository information for a database is always in the control file of the database. 
  • ( ) RMAN supports proxy copy, a feature that allows a media manager to manage completely the transfer of data between disk and backup media
  • ( ) RMAN backups init parameter file
  • ( ) RMAN DOES NOT backup password and networking files.
  • ( ) RMAN supports a platform-independent language for backups

Q&As: Incremental Backup



(Q) What are the characteristics of Incremental Backups?


  • Store only blocks changed since a previous incremental (Level 0 or Level 1) backup
  • Provide more compact backups and faster recovery
  • Less redo logs are applied during recovery
  • If you enable block change tracking, then full table scans on input files will not be performed during recovery
  • You can take a Level 0 or Level 1 Incremental Backup

$ rman
RMAN> CONNECT TARGET /
RMAN> BACKUP
       INCREMENTAL LEVEL 1 CUMULATIVE
       SKIP INACCESSIBLE
       DATABASE;

The example above:
  • Backs up all blocks changed since the most recent LEVEL 0 incremental backup.
  • If no LEVEL 0 backup exists, RMAN makes a LEVEL 0 automatically.
  • Inaccessible files are skipped

Oracle Scheduler: Use credentials for a detached job that performs cold backups



Here are the steps to create a daily detached job that performs a cold backup of an oracle 11g database.
This example is a bit more detailed than the one provided in the administration guide.

Steps:
(1) Create the shell script that invokes RMAN.
  • This script will be executed by the detached job.
  • The script should be executable by the user who has backup privileges in the OS. (usually oracle).
(2) create the RMAN script
(3) Write a PL/SQL block that performs the following steps:
3.1 create a credential object with username/password of the OS user who should have permission to run the shell script.
3.2 create a program object, which should use the credential just created to run the shell script.
3.3 alter the program object setting its attribute to detached.
3.4 create a job object


(1) Create the script that invokes RMAN
  • Create a shell script that calls an RMAN script to perform a cold backup.
  • The shell script is located in $ORACLE_BASE/scripts/coldbackup.sh.
  • It must be executable by the user who installed Oracle Database (typically the user oracle).
#!/bin/sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
$ORACLE_HOME/bin/rman TARGET / @$ORACLE_HOME/scripts/coldbackup.rman trace /u01/app/oracle/backup/coldbackup.out &
exit 0

(2)Create the RMAN Script
  • Create an RMAN script that performs the cold backup and then ends the job.
  • At the end, the script should call the Scheduler routine to inform the job completion status
  • The script is located in $ORACLE_BASE/scripts/coldbackup.rman.
run {
# Shut down database for backups and put into MOUNT mode
shutdown immediate
startup mount

# Perform full database backup
backup full format "/u01/app/oracle/backup/%d_FULL_%U" (database) ;

# Open database after backup
alter database open;

# Call notification routine to indicate job completed successfully
sql " BEGIN DBMS_SCHEDULER.END_DETACHED_JOB_RUN(''sys.backup_job'', 0, null); END; ";
}

(3) Create credentials, detached program and job with inline schedule
Submit the following PL/SQL block:
-- 3.1 create a credential for the job to use
-- 3.2 create the program object, specifying the shell script created earlier as the external executable. Specify it as detached program.
-- 3.3 create the job, specifying the credential object created earlier.
Begin 
 dbms_scheduler.create_credential('credrman', 'oracle', '');

 dbms_scheduler.create_program (
   program_name   => 'sys.backup_program',
   program_type   => 'executable',
   program_action => '/u01/app/oracle/scripts/coldbackup.sh',
   enabled        => TRUE,
   detached       => TRUE);
   
 dbms_scheduler.create_job (
   job_name     => 'sys.backup_job',
   program_name => 'sys.backup_program',
   repeat_interval => 'FREQ=DAILY; BYHOUR=13; BYMINUTE=20',
   credential_name => 'credrman');
   
 dbms_scheduler.enable('sys.backup_job');
end;

You can check the status of the created job with the query below:
SQL> select owner, job_name, job_style, program_name, schedule_type, 
       to_char(start_date, 'yyyy-mm-dd hh:mi:ss') start_date_, enabled, state, 
       run_count runs, failure_count failures, 
from dba_scheduler_jobs
where job_name = 'BACKUP_JOB';

JOB_NAME     JOB_STYLE   PROGRAM_NAME     SCHEDULE_TYPE  START_DATE_         ENABLED STATE     RUNS FAILURES
------------ ----------- ---------------- ------------- -------------------- ------- --------- ---- --------
BACKUP_JOB   REGULAR     BACKUP_PROGRAM   CALENDAR       2011-12-09 01:12:49 TRUE    SCHEDULED 0    0       


Some troubelshooting:
  • If a credential object is not created and assigned to the job, it will fail.
  • The result will be an error entry in the alert file and a trace generated:
-//- alert.log  -//-
(...)
Fri Dec 09 14:25:51 2011
Errors in file /u01/app/oracle/diag/rdbms/orcl11r2/orcl11r2/trace/orcl11r2_j000_20165.trc:
ORA-12012: error on auto execute of job 12693
ORA-27369: job of type EXECUTABLE failed with exit code: 274670
(...)

  • If a credential object is not created, Oracle will try to run the external program using the credentials specified in the $ORACLE_HOME/rdbms/admin/externaljob.ora file.
  • The credentials specified in that file (in a unix/linux installation) are user:nobody, group:nobody.
  • The consequence is that the job will likely fail with the error 'ORA-27369: job of type EXECUTABLE failed with exit code: 274670.'
  • This somewhat criptical error message suggests (as discussed here) that the problem is due to OS permissions in the execution of the external program.



SQL> select status, error#, run_duration, credential_name
     from dba_scheduler_job_run_details 
     where job_name='BACKUP_JOB';
STATUS                         ERROR#                 RUN_DURATION CREDENTIAL_NAME
------------------------------ ---------------------- ------------ ------------------ 
FAILED                         274670                 0 0:0:2.0 
SUCCEEDED                      0                      0 0:7:26.0   CREDRMAN                                                                                                                                                                                           

SQL> select log_id, operation, status, credential_name 
     from dba_scheduler_job_log 
     where job_name='BACKUP_JOB';
LOG_ID                 OPERATION                      STATUS     CREDENTIAL_NAME                                                   
---------------------- ------------------------------ ---------- ------------------ 
192                    RUN                            FAILED
194                    RUN                            SUCCEEDED  CREDRMAN                                                          

Oracle Flashback Technology (III) - Flashback Data Archive



Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:



Using Flashback Data Archive (Oracle Total Recall)
  • With Data Archive you can store and track transactional changes to a record over its lifetime.
  • It permanently stores undo information on flashback archives, allowing you to keep the transactional history of a object since its creation.
  • Flashback archives are enabled on individual tables and are located in tablespaces, and have a name, a specified retention period and a space quota on the tablespace.
  • A database can have multiple flashback archives.
  • when a DML transaction commits on a flashback archive enabled table, the Flashback Data Archiver (FBDA) process stores the pre-image of the rows into a flashback archive.
  • FBDA also manages the data within the flashback archives (purging data beyond retention period).
  • Historical data can be queried using the Flashback Query AS OF clause.
  • Useful for compliance with record stage policies and audit requirements.


To enable flashback archiving for a table:
  • You need FLASHBACK ARCHIVE privilege on a flashback data archive
  • Table cannot be clustered, nested, temporary, remote or external
  • Table cannot have LONG nor nested columns


Create a Flashback Data Archive
(1) Create a new tablespace (you may also use an existing one)

SQL> create tablespace fda_ts
   datafile '/u01/app/oracle/oradata/test112/fda1_01.dbf'
   size 1m autoextend on next 1m;

SQL> select tablespace_name, status, contents, retention
  from dba_tablespaces
  where tablespace_name ='FDA_TS';

TABLESPACE_NAME                STATUS    CONTENTS  RETENTION   
------------------------------ --------- --------- ----------- 
FDA_TS                         ONLINE    PERMANENT NOT APPLY   

(2) Create Flashback archvies:

SQL> create flashback archive default fda_1m tablespace fda_ts   -- Must be SYSDBA to create DEFAULT FDA
  quota 1G retention 1 month;                                -- To change use ALTER FLASHBACK ARCHIVE...SET DEFAULT

SQL> create flashback archive fda_2yr tablespace fda_ts retention 2 year;
  
SQL> create flashback archive fda_10d tablespace fda_ts retention 10 day;

Managing Flashback Data Archives:
(1) Manage FDA tablespaces:

ALTER FLASHBACK ARCHIVE...
   ...SET DEFAULT;
   ... ADD TABLESPACE... QUOTA...;
   ... MODIFY TABLESPACE...
   ... REMOVE TABLESPACE...

(2) Manage retention period:

ALTER FLASHBACK ARCHIVE fda_name MODIFY RETENTION n [Year | Month | day ];

(3) Purge historical data

ALTER FLASHBACK ARCHIVE...
   ...PURGE ALL;                          -- Purge ALL historical data
   ...PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL 'n' DAY);
   ...PURGE BEFORE SCN scn_num;

(4) Drop FDA:

DROP FLASHBACK ARCHIVE fda_name;           -- Drops FDA. Keeps tablespace.

Enabling FDAs on objects:
  • FDA is disabled by default
  • User needs FLASHBACK ARCHIVE privilege to create enable flashback archive on object.
SQL> conn sys/pwd as sysdba;

SQL> grant flashback archive on fda_1m to userA;

SQL> conn userA/pwd;

SQL> Create table emp 
  (empno number primary key,
   ename varchar2(20),
   salary number) 
  flashback archive fda_1m;


-- To Disable Flashback archive on table
SQL> ALTER TABLE emp NO flashback archive;

Information about Flashback data Archives:
DBA_FLASHBACK_ARCHIVE, DBA_FLASHBACK_ARVHIE_TS and DBA_FLASHBACK_ARCHIVE_TABLES
SQL> select owner_name, flashback_archive_name, retention_in_days, status, 
       to_char(last_purge_time, 'dd-mon-yy hh24:mi:ss')
from dba_flashback_archive;

OWNER_NAME    FLASHBACK_ARCHIVE_NAME  RETENTION_IN_DAYS      STATUS  LAST_PURGE_TIME           
------------- ----------------------- ---------------------- ------- ------------------------- 
SYS           FDA_1M                  30                     DEFAULT 25-oct-11 13:34:14 
SYS           FDA_2YR                 730                            25-oct-11 13:34:54 
SYSTEM        FDA_10D                 10                             25-oct-11 13:38:05

SQL> select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NAME  FLASHBACK_ARCHIVE#     TABLESPACE_NAME                QUOTA_IN_MB 
----------------------- ---------------------- ------------------------------ ------------
FDA_1M                  1                      FDA_TS                         1024        
FDA_2YR                 2                      FDA_TS                                     
FDA_10D                 3                      FDA_TS                                     


SQL> select * from dba_flashback_archive_tables;

TABLE_NAME  OWNER_NAME                     FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME   STATUS   
----------- ------------------------------ ---------------------- ------------------- -------- 
EMP         SYSTEM                         FDA_1M                 SYS_FBA_HIST_75434  ENABLED  


Example: Viewing table history.
(1) Insert data on emp
(2) Keep record of some points in time
(3) Query the historical data on emp
SQL> select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') start_time, 
       current_scn start_scn
     from v$database;

START_TIME         START_SCN              
------------------ ---------------------- 
25-oct-11 14:22:25 1498655       

SQL> select * from emp;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 

-- PL/SQL block performs a number of DMLs on emp and prints timestamps
set serveroutput on
declare
 procedure get_timestamp
 is
   v_time varchar2(25);
   v_scn  integer;
 begin
   select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') start_time, 
        current_scn start_scn into v_time, v_scn
   from v$database;
   dbms_output.put_line('timestamp: ' || v_time);
   dbms_output.put_line('SCN:       ' || v_scn);
end;
 
begin
  insert into emp values (1, 'John', 2000);
  commit;
  dbms_lock.sleep(2);
  get_timestamp();
  for i in 1 .. 10 
  loop
   update emp set salary =salary*1.05 where empno=1;
   commit;
   dbms_lock.sleep(2);
   if i=5 then
     insert into emp values (2, 'Mary', 3000);
     update emp set salary = 2500 where empno =1;
     commit;
     dbms_lock.sleep(2);
     update emp set ename = initcap(ename);
     commit;
     insert into emp values (3, 'Gary', 1500);
     delete from emp where empno=2;
     commit;
     get_timestamp();   
   end if;
  end loop;
  dbms_lock.sleep(2);
  get_timestamp();
end;
/

anonymous block completed
timestamp: 25-oct-11 14:22:27
SCN:       1498659
timestamp: 25-oct-11 14:22:39
SCN:       1498683
timestamp: 25-oct-11 14:22:51
SCN:       1498700

SQL> select * from emp;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
3                      Gary                 1500         

SQL> select to_char(systimestamp, 'dd-mon-yy hh24:mi:ss') end_time, 
       current_scn end_scn
     from v$database;
END_TIME           END_SCN                
------------------ ---------------------- 
25-oct-11 14:22:51 1498701


(a) Select data at a point in time
SQL> select *  from emp as of scn 1498683;

EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 2500                   
3                      Gary                 1500       

SQL> select * 
     from emp as of timestamp to_timestamp('25-oct-11 14:22:51', 'dd-mon-yy hh24:mi:ss');
EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
3                      Gary                 1500  


(b) select all versions of a row betwen two points in time

SQL> select *
     from emp
       versions between scn 1498659 and 1498700
     where empno =1;
EMPNO                  ENAME                SALARY                 
---------------------- -------------------- ---------------------- 
1                      John                 3190.70390625          
1                      John                 2000                   
1                      John                 2100                   
1                      John                 2205                   
1                      John                 2315.25                
1                      John                 2431.0125              
1                      John                 2552.563125            
1                      John                 2500                   
1                      John                 2500                   
1                      John                 2625                   
1                      John                 2756.25                
1                      John                 2894.0625              
1                      John                 3038.765625            
1                      John                 3190.70390625          

 14 rows selected 


SQL> select versions_xid xid, versions_startscn start_scn,
            versions_endscn end_scn, versions_operation operation,
            empno, ename, salary
     from emp
        versions between scn 1498659 and 1498700
     where empno =1;

XID              START_SCN   END_SCN                OPERATION EMPNO   ENAME                SALARY                 
---------------- ----------- ---------------------- --------- ------- -------------------- ---------------------- 
03000F008B040000 1498633     1498674                I         1       John                 3190.70390625          
05001F00AA040000 1498657     1498661                I         1       John                 2000                   
030003008B040000 1498661     1498664                U         1       John                 2100                   
02000A007E040000 1498664     1498667                U         1       John                 2205                   
01000D003A030000 1498667     1498670                U         1       John                 2315.25                
0400090075030000 1498670     1498672                U         1       John                 2431.0125              
06000B0094040000 1498672     1498674                U         1       John                 2552.563125            
0900080096040000 1498674     1498678                U         1       John                 2500                   
03001F008B040000 1498678     1498685                U         1       John                 2500                   
09001F0097040000 1498685     1498688                U         1       John                 2625                   
080010006C050000 1498688     1498691                U         1       John                 2756.25                
0700190078030000 1498691     1498694                U         1       John                 2894.0625              
03001A008B040000 1498694     1498697                U         1       John                 3038.765625            
05001E00AB040000 1498697                            U         1       John                 3190.70390625          

 14 rows selected 







Oracle Flashback Technology (II) - Flashback Transactions




Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:





Oracle Flashback Transaction
  • Allow you to rollback changes made by one or more transactions.
  • Flashback Transaction generates and executes the DMLs necessary to undo a given transactions and (optionally) all its dependent transactions.
  • Requisites:
    • ARCHIVELOG mode
    • At least one archive log already generated
    • Minimal and PK (plus FK) supplemental logging enabled
    Privileges required:
    • FLASHBACK and SELECT on the object or
    • FLASHBACK ANY TABLE.

Configuring the database to use Flashback Transaction

(1) Check the database log mode

SQL> Select log_mode from v$database;

LOG_MODE     
------------ 
NOARCHIVELOG

(2) Change Log mode to archivelog:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

SQL> Select log_mode from v$database;

LOG_MODE     
------------ 
ARCHIVELOG

(3) Switch logfile

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

(4) Check log files

SQL> select group#, thread#, bytes/1024/1024 Mb, members, archived, status
     from v$log;

GROUP#                 THREAD#                MB                     MEMBERS                ARCHIVED STATUS           
---------------------- ---------------------- ---------------------- ---------------------- -------- ---------------- 
1                      1                      50                     1                      YES      ACTIVE           
2                      1                      50                     1                      NO       CURRENT          
3                      1                      50                     1                      YES      INACTIVE       

SQL> select name, archived, applied, status, completion_time, compressed
     from v$archived_log;

NAME                                                      ARCHIVED APPLIED   STATUS COMPLETION_TIME           COMPRESSED 
--------------------------------------------------------- -------- --------- ------ ------------------------- ---------- 
/u01/.../flash_recovery_area/SID/archivelog/date/name.arc YES      NO        A      08-DEC-10                 NO         

(5) Enable Minimal and PK (and FK) supplemental logging 

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

(3) Check the current setting for supplemental logging
  (V$DATABASE, DBA_LOG_GROUPS, DBA_LOG_GROUP_COLUMNS)

SQL> Select supplemental_log_data_min, supplemental_log_data_all,
            supplemental_log_data_UI,  supplemental_log_data_FK
     from v$database;

SUPPLEMENTAL_LOG_DATA_MIN SUPPLEMENTAL_LOG_DATA_ALL SUPPLEMENTAL_LOG_DATA_UI SUPPLEMENTAL_LOG_DATA_FK 
------------------------- ------------------------- ------------------------ ------------------------ 
YES                       NO                        NO                       YES                     

Using Flashback Transaction
  • Use DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure to rollback a transaction and its dependent transactions.
  • Can be done with the database online.
Restrictions
  • DDL operations cannot be flashed back
  • Transactions that use LOB datatypes (BFILE, BLOB, CLOB, NCLOB) cannot be flashed back
  • Transactions cannot use features or datatypes not supported by the Log Miner
  • Ensure that undo segments are large enough to maintain undo information for the period you may need to flashback.

Example 1: Flashback transactions that have no dependent transactions.
  1. Drop and create tables dept and emp
  2. Transaction 1 and Transaction 2 insert into dept and emp
  3. To flashback Transaction 2 you need:
    • Find out the transaction ID (XID): you can use OEM and Log Miner, or use a flashback transaction query.
    • Identify the transaction to be flashed back and use dbms_flashback.transaction_backout

(1) Drop and recreate dept and emp tables.
SQL> drop table emp;
SQL> drop table dept;

-- Check the current database SCN
SQL> select current_scn scn_before_tblcreation 
     from v$database;

SCN_BEFORE_TBLCREATION 
---------------------- 
1473663               


SQL> create table dept
    (deptno number primary key,
     deptname varchar2(20));
  
SQL> create table emp
     (empno number primary key, 
      empname varchar2(10),
      deptno number, 
      salary number,
      constraint fk_dept foreign key(deptno)
                         references dept(deptno));

-- Check the current database SCN
SQL> select current_scn scn_before_transactions 
     from v$database;

SCN_BEFORE_TRANSACTIONS 
----------------------- 
1473743   
(2) Insert data into emp and dept
-- Transaction 1: Insert new row (id=1)
insert into dept values (1, 'accounting');
insert into dept values (2, 'sales');
insert into emp values (1, 'John', 1, 2000);
commit;

-- Transaction 2: Insert new rows (ids=2,3)
insert into emp values (2, 'Mary', 1, 2500);
insert into emp values (3, 'Jack', 2, 2000);
insert into dept values (3, 'IT');
commit;

-- Check the current database SCN
SQL> select current_scn scn_after_transactions 
     from v$database;

SCN_AFTER_TRANSACTIONS 
---------------------- 
1473781 

-- Check inserted data
SQL> select * from dept;

DEPTNO                 DEPTNAME             
---------------------- -------------------- 
1                      accounting           
2                      sales                
3                      IT                   

SQL> select * from emp;

EMPNO                  EMPNAME    DEPTNO                 SALARY                 
---------------------- ---------- ---------------------- ---------------------- 
1                      John       1                      2000                   
2                      Mary       1                      2500                   
3                      Jack       2                      2000  
(3) Now use the flashback transaction query to find information about all transactions in a time or SCN interval
SQL> select versions_xid xid, versions_startscn start_scn,
            versions_endscn end_scn, versions_operation operation,
            empno, empname, deptno, salary
     from emp
        versions between scn 1473743 and 1473781;

XID              START_SCN   END_SCN  OPERATION EMPNO   EMPNAME    DEPTNO   SALARY  
---------------- ----------- -------- --------- ------- ---------- -------- -------- 
05000D0099040000 1473777              I         3       Jack       2         2000   
05000D0099040000 1473777              I         2       Mary       1         2500   -- Transaction 2
07000A006A030000 1473747              I         1       John       1         2000   

Identify the transaction you need to flashback. For example, if you choose to flashback transaction 2 only its three insert statements will be flashed back. Also, transaction 2 has no dependents, since:
  • No other subsequent transaction modified any data touched by transaction 2
  • No other subsequent transaction modified data linked through referential integrity with the rows touched by transaction 2.

To flashback a transaction, use DBMS_FLASHBACK.TRANSACTION_BACKOUT (numtxns, xids, options).
The procedure accepts one or more transaction IDs (XIDs).
It has also an OPTIONS parameter to indicate how dependent transactions should be treated. The possible values are:
  • NOCASCADE - The transaction should have no dependencies. If a dependency is found, this raises an error.
  • NOCASCADE_FORCE - The transaction is backed out without affecting dependent transactions. This option succeeds only if no constraints are violated in the process.
  • NONCONFLICT_ONLY - This option backout only the changes to the non-conflicting rows of the given transactions.
  • CASCADE - The transaction and all it's dependencies will be removed.
SQL> conn / as sysdba;

SQL> declare
 v_xid sys.xid_array;
begin
 v_xid := sys.xid_array('05000D0099040000');
 dbms_flashback.transaction_backout( numtxns => 1,
                                     xids    => v_xid,
                                     options => dbms_flashback.cascade);
end;
/
anonymous block completed
  • TRANSACTION_BACKOUT analyzes the transactional dependencies, performs DML operations, and generates reports.
  • It also DOES NOT commit the actions performed.
  • To make the transaction backout permanent, you need to explicitly commit the transaction.
(4) Check the effects of the flashback. Query the table contents. Commit the flashback transaction.
SQL> select * from dept;

DEPTNO                 DEPTNAME             
---------------------- -------------------- 
1                      accounting           
2                      sales                


SQL> select * from emp;

EMPNO                  EMPNAME    DEPTNO                 SALARY                 
---------------------- ---------- ---------------------- ---------------------- 
1                      John       1                      2000           

SQL> commit;

TRANSACTION_BACKOUT Reports
The backout procedure populates two static data dictionary views.
  • DBA_FLASHBACK_TXN_STATE
  • Shows information about the transactions backedout by the procedure.
    • COMPENSATING_XID - Transaction of ID of the compensating transaction. You will need to explictly commit this transaction in order to make the backout permanent.
    • XID - Backedout transaction
    • DEPENDENT_XID - Dependent transaction
(a) Query DBA_FLASHBACK_TXN_STATE view

SQL> select *
from dba_flashback_txn_state
where xid = '05000D0099040000';

COMPENSATING_XID XID              DEPENDENT_XID    BACKOUT_MODE     USERNAME                       
---------------- ---------------- ---------------- ---------------- ------------------------------ 
0800160056050000 05000D0099040000                  CASCADE          SYSTEM                

(b) Use the compensating_xid returned to query the DBA_FLASHBACK_TXN_REPORT view:

sql> select xid_report
     from dba_flashback_txn_report
     where compensating_xid = '0800160056050000';

XID_REPORT   
------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?> 
<COMP_XID_REPORT XID="0800160056050000" NAME="_SYS_COMP_TXN_1114255_TIM_1319522919"> 
 <TRANSACTION XID="05000D0099040000"> 
 <CHARACTERISTICS> 
 </CHARACTERISTICS> 
 <UNDO_SQL> 
  <USQL\="yes"> 
   delete from "SYSTEM"."DEPT" where "DEPTNO" = '3' and "DEPTNAME" = 'IT' 
  </USQL>
  <USQL exec="yes">
   delete from "SYSTEM"."EMP" where "EMPNO" = '3' and "EMPNAME" = 'Jack' and "DEPTNO" = '2' and "SALARY" = '2000'
  </USQL> 
  <USQL exec="yes"> 
   delete from "SYSTEM"."EMP" where "EMPNO" = '2' and "EMPNAME" = 'Mary' and "DEPTNO" = '1' and "SALARY" = '2500' 
  </USQL>  
 </UNDO_SQL> 
 <DEPENDENT_XIDS> 
 </DEPENDENT_XIDS>
 </TRANSACTION>
<EXECUTED_UNDO_SQL>
<EXEC_USQL>delete from "SYSTEM"."DEPT" where "DEPTNO" = '3' and "DEPTNAME" = 'IT' 
</EXEC_USQL> 
<EXEC_USQL>delete from "SYSTEM"."EMP" where "EMPNO" = '3' and "EMPNAME" = 'Jack' and "DEPTNO" = '2' and "SALARY" = '2000' 
</EXEC_USQL>
<EXEC_USQL>delete from "SYSTEM"."EMP" where "EMPNO" = '2' and "EMPNAME" = 'Mary' and "DEPTNO" = '1' and "SALARY" = '2500'
</EXEC_USQL>             
</EXECUTED_UNDO_SQL>
</COMP_XID_REPORT>  


SQL Server: Moving a Database



Three mechanisms for moving a database (within the same server, or between servers):
  1. Backup and Restore
  2. Copy Database Wizard
  3. Detach/Attach

Detaching/Attaching databases
  • Data files and transaction logs can be detached and then reattached to the same or different SQL Server instance.
  • You may transfer databases across 32-bit and 64-bit environments.

Note that:
  • Make sure you have a backup of the database before attaching it to a newer version server.
  • Once you attach a SQL Server 2000 or 2005 database to a 2008 SQL Server, you will not be able to attach it back into previous version servers.
  • After attaching a database to a new server, you will need to add users from the old server, if they are to continue to have access to the database.

You CANNOT detach a dataabse when:
  • The database is replicated AND published. Before detaching you need to disable publishing (run sp_replicationdboption)
  • Database contains a database snapshot.
  • Database is being mirrored.
  • While there are database sessions.
  • Database is suspect. Needs to be put into emergency mode.
  • Database is a system database.

Moving a database:

(a) Detach the database as follows:
    -- (1) Set database in single_user mode
USE MASTER;
ALTER DATABASE AdventureWorks2008R2
    SET single_user;
GO
    --(2) Check access mode 
SELECT name, user_access_desc
    FROM sys.databases
    WHERE name = 'adventureworks2008r2';
    --(3) Detach database. Set option to skip update statistics for true.
    -- By default, the detach operation retains any out-of-date optimization statistics  
    -- If you want updated optimization statistics to be gathered, set the parameter to 'false'
            .
EXEC sp_detach_db 'AdventureWorks2008R2', 'true';

    --(4) check that the database is no longer listed in the catalog
SELECT name     FROM sys.databases;

(b) MOVE/COPY data and log files to the new location
(c) Re-attach the database as follows:
USE MASTER;
GO
EXEC sp_attach_db @dbname = 'AdventureWorks2008R2' ,
    @filename1 = 'c:\mssqldrive\e\AdventureWorks2008R2_Data.mdf',
    @filename2 = 'c:\mssqldrive\e\AdventureWorks2008R2_Log.ldf';
    -- (2) Check that the database is in the catalog and the new file locations:

EXEC ssp_helpdb Adventureworks2008r2; 
name db_size owner dbid created status compatibility_level -------------------- --------- -------------- ---- ---------- ----------------- -------------------- AdventureWorks2008R2 181.88 MB Pholder\sqltest 9 May 10 2010 Status=ONLINE,... 100 name fileid filename filegroup size ... usage ------------------------- ------ ------------------------------------------ --------- -------- ------ AdventureWorks2008R2_Data 1 g:\sqldata\..\AdventureWorks2008R2_Data.mdf PRIMARY 184192 KB ... data only AdventureWorks2008R2_Log 2 g:\sqldata\..\AdventureWorks2008R2_Log.ldf NULL 2048 KB ... log only

Attaching a database
  • Use sp_attach_db only if the database was previously detached with sp_detach_db, or copied.
  • If you need to specify more than 16 files, use instead
    • CREATE DATABASE dbname FOR ATTACH or
    • CREATE DATABASE dbname FOR_ATTACH_REBUILD_LOG
(more information: here)

Oracle Backup and Recovery solutions


Basic solutions available for a backup and recovery strategy:





Backup techniques: comparison

Feature Recovery Manager User-managed Data Pump exp
Closed database backup Yes. Needs instance Mounted Yes No
Open database backup Need use of BEGIN/END BACKUP Need use of BEGIN/END BACKUP Requires rollback or undo segments
Incremental backup Yes No No
Corrupt block detection Indentifies corrupt blocks and logs in V$DATABASE_BLOCK_CORRUPTION No Identifies corrupt blocks in the export log.
Automatic specification of files to include in a backup Establishes name and location of all files to be backed up Files must be located and copied manually n/a
Backup repository Backups recorded in the control file. You Can store backup metadata in a recovery catalog. DBA needs to keep records. n/a
Backup to a media manager Yes. RMAN also supports proxy copy. Backup to tape is manual or controlled by media manager. Not supported.
Backup init.ora file Yes Yes No
Backup password and networking files No Yes No
Platform-independent language for backups Yes No yes















Flashback features: Logical and Physical levels

Oracle Flashback features
requires flashback data archiving. OFF for all tables (default).
Turn it on for individual tables
Operate at the logical level, using undo data.
Oracle flashback query Specify target time. Run queries. Results displayed as they would have appeared at the target time.
Oracle flashback version query View all version of rows that existed in a table within a time range
Oracle flashback transaction query View changes made by a single transaction
Oracle flashback transaction Reverse a transaction. Wow.
Oracle flashback table Recover table or set of tables to a point in time.
Oracle flashback drop Reverse the effects of a drop table.
Flashback database (RMAN: FLASHBACK DATABASE)
operates at the physical level. Uses flashback logs.
Can be used against specific data files.
More efficient than database point-in-time recovery.
Faster because does not require datafile restor.
Uses flashback logs. Not enabled by default.
Need a fast recovery area congifured.
You can set guaranteed restore points (alias for an SCN).





















































Oracle DBMS_FLASHBACK package




Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:




Using DBMS_FLASHBACK package
  • Released with Oracle 9i
  • Provides the same functionality as Oracle Flashback Query (Oracle 10g)
  • Acts as a time machine:
    • 1. set time to a previous point in time.
    • 2. perform queries without the "AS OF" and "VERSIONS BETWEEN" clauses
    • 3 return time to the "present"

Requirements:
  • You need EXECUTE privilege on DBMS_FLASHBACK package
  • The UNDO_RETENTION parameter defines how far back in time an object can be flashbacked.
  • UNDO_RETENTION can be set in the init.ora or with:
  • ALTER SYSTEM SET UNDO_RETENTION = num_seconds;

Using the DBMS_FLASHBACK package:
  1. Specify a past time with DBMS_FLASHBACK.ENABLE_AT_TIME or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER.
  2. Perform regular queries (queries without flashback-feature syntax such as AS OF).
    Do not perform DDL or DML operations.
  3. Return to the present by invoking DBMS_FLASHBACK.DISABLE.
(a) Check current data in the emp table

SQL> select * from emp;

 EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900      

SQL> select current_scn, to_char(systimestamp, 'MM-DD-YY HH24:MI:SS') date_time
     from v$database;
CURRENT_SCN            DATE_TIME         
---------------------- ----------------- 
1432709                12-08-10 10:58:45  

(b) modify data in emp table

SQL> Insert into emp values (3, 'Mark', 5000);
SQL> commit;

SQL> Update emp set salary = 0 where empno=1;
SQL> commit;

SQL> Delete from emp where empno=2;
SQL> update emp set empname = 'Lenny' where empno=100;
SQL> commit;

SQL> select * from emp;

EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
1                      Jack       0                      
100                    Lenny      900                    
3                      Mark       5000   

SQL> select current_scn, to_char(systimestamp, 'MM-DD-YY HH24:MI:SS') date_time
     from v$database;
CURRENT_SCN            DATE_TIME         
---------------------- ----------------- 
1432739                12-08-10 10:59:30 

To view and restore the original data, you can
(1) create a cursor to hold the original data
(2) go back in time with dbms_flashback.enable_at_system_change_number(n);
(3) Open and fetch the data in the cursor.
(4) return to the present with dbms_flashback.enable;
(5) Insert the data in the cursor in the restore table
(c) Create a table to restore original data

SQL> create table emp_restore
     as select * from emp where 1 = 0;


set serveroutput on
DECLARE
  cursor c_emp is 
     select * from emp;
  v_row c_emp%rowtype;
BEGIN
  dbms_output.put_line('enabling dbms_flashback..');
  dbms_flashback.enable_at_system_change_number(1432709);
  Open c_emp;
  dbms_output.put_line('disabling dbms_flashback..');  
  dbms_flashback.disable;
  
  loop 
    fetch c_emp into v_row;
    exit when c_emp%notfound;
    insert into emp_restore values
      (v_row.empno, 
       v_row.empname,
       v_row.salary);
  end loop;
  close c_emp;
  commit;
END;
/
enabling dbms_flashback..
disabling dbms_flashback..

SQL> select * from emp_restore; 

EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900 

Oracle Flashback Technology (I)




Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:



  • Oracle Flashback features use the Automatic Undo Management to obtain metadata and transaction historical data.
  • Undo data is persistent and survives database shutdown.
  • You can use the Flashback options to
    • recover data from user errors,
    • compare table data at two points in time,
    • view transaction actions (the set of actions performed in a given transaction).
    • Undo table drops
    • Revert the entire database to a previous point in time.

Configuring the database for Flashback options
Database must be configured for Automatic Undo Management (default on 11g).
For this you need:
  • (1) Size undo tablespace; and
  • (2) Enable automatic undo management

(Step 1) Create or resize the undo tablespace appropriately
The Undo tablespace must have enough space to keep the data required for flashback operations
(a) check the size of the existing undo tablespaces

SQL> select t.tablespace_name, t.status, t.contents, t.extent_management, 
            t.allocation_type,   t.retention, s.sizemb 
     from dba_tablespaces t, 
           (select tablespace_name, sum(bytes)/1024/1024 sizemb  
              from dba_data_files
              group by tablespace_name) s
     where t.tablespace_name = s.tablespace_name
     and t.contents = 'UNDO';

TABLESPACE_NAME                STATUS    CONTENTS  EXTENT_MANAGEMENT ALLOCATION_TYPE RETENTION   SIZEMB                 
------------------------------ --------- --------- ----------------- --------------- ---------   ---------------------- 
UNDOTBS1                       ONLINE    UNDO      LOCAL             SYSTEM          NOGUARANTEE 60      

(b) Check the current retention period: 
Query v$undostat to check the amount of time (seconds) for which undo will not be recycled.
    
SQL> select to_char(begin_time, 'dd-MON  hh:mi') begin_time, 
       to_char(end_time, 'dd-MON  hh:mi') end_time, 
       tuned_undoretention undoretention_sec
from v$undostat;

BEGIN_TIME    END_TIME      UNDORETENTION_SEC      
------------- ------------- ---------------------- 
19-OCT  09:51 19-OCT  09:58 1911     --  At current workload, undo is preserved for about 30 minutes
19-OCT  09:41 19-OCT  09:51 1608                   
...
19-OCT  03:11 19-OCT  03:21 1787                   
19-OCT  03:01 19-OCT  03:11 1183       
(Step 2) Enable automatic undo management (undo parameters)
  • UNDO_MANAGEMENT = [ AUTO (default) | MANUAL ]
  • UNDO_TABLESPACE - specifies the name of the undo tablespace
  • UNDO_RETENTION - specifies the minimum amount of time that Oracle attempts to retain old undo information before overwriting it.
    • The value for UNDO_RETENTION is ignored for a fixed size undo tablespace
    • If AUTOEXTEND is enabled, database attempts to honor UNDO_RETENTION.
    • 'snapshot too old' errors indicate that undo retention is not enough for flashback operations.
    • To accomodate Oracle Flashback features, you can set the UNDO_RETENTION parameter to a value equal to the longest expected Oracle Flashback operation.
    • If retention is very important, use ALTER TABLESPACE...RETENTION GUARANTEE. Here the database never overwrites unexpired undo data even if it means that transactions fail due to lack of undo space.
    • To set: ALTER SYSTEM SET UNDO_RETENTION = 2400;
(a) check instance parameters

SQL> select name, value, isdefault, description
     from v$parameter
     where name like '%undo%';

NAME            VALUE    ISDEFAULT DESCRIPTION
--------------- ------   --------- ---------------------------------------------------
undo_management AUTO     TRUE      instance runs in SMU mode if TRUE, else RBU mode -- default: AUTO (11g), Manual (before 11g).
undo_tablespace UNDOTBS1 FALSE     use/switch undo tablespace
undo_retention  900      TRUE      undo retention in seconds


Notes on Flashback Query
  • Flashback query can be used with remote tables (table@dblink)
  • Queries on V$ views ALWAYS return current data
  • Flashback query can be used with static dictionary views
  • To query past data at a precise time, use an SCN.
  • With timestamp, the actual time queried might be up to 3 seconds earlier than the time you specify.
  • Oracle Flashback Drop - Reverse the effects of DROP TABLE

Using FLASHBACK QUERY
  • Use SELECT.. AS OF [ TIMESTAMP timestamp| SCN scn ]
  • The query returns committed data as it existed at that point.
  • To use flashback query on a table, the user needs to have FLASHBACK and SELECT privileges on that object.
Case:
(1) Some rows from emp table are deleted by mistake at 11 AM.
(2) Error discovered at 11:20AM.
(3) Use Flashback query to see the data as it was before the 11am delete.
(1) Check table rows at 11:00AM
SQL> select current_scn, to_char(systimestamp, 'mm-dd-yy  HH24:mi:ss') time
     from v$database;

CURRENT_SCN            TIME               
---------------------- ------------------ 
1385809                12-08-10  11:00:22

SQL> select employee_id, last_name,  salary
     from hr.emp
     where department_id =30;

EMPLOYEE_ID            LAST_NAME                 SALARY                 
---------------------- ------------------------- ---------------------- 
114                    Raphaely                  11000                  
...
119                    Colmenares                2500    


(2) At 11:20, rows are incorrectly deleted from emp.

SQL> delete from hr.emp where department_id=30;
SQL> commit;
SQL> select current_scn, to_char(systimestamp, 'mm-dd-yy  HH24:mi:ss') time
     from v$database;
CURRENT_SCN            TIME               
---------------------- ------------------ 
1386301                12-08-10  11:20:03

SQL> select employee_id, last_name,  salary
     from hr.emp
     where department_id =30;

EMPLOYEE_ID            LAST_NAME                 SALARY                 
---------------------- ------------------------- ---------------------- 

(3) Use flashback query to see emp as it was at 11 AM

SQL> select employee_id, last_name, salary
     from hr.emp AS OF TIMESTAMP to_timestamp('12-08-10 11:00:00', 'mm-dd-yy HH24:MI:SS')
     where department_id = 30;

EMPLOYEE_ID            LAST_NAME                 SALARY                 
---------------------- ------------------------- ---------------------- 
114                    Raphaely                  11000                  
...
119                    Colmenares                2500    

(4) Alternatively you can use the SCN 

SQL> select employee_id, last_name, salary
     from hr.emp AS OF SCN 1385809
     where department_id = 30;

EMPLOYEE_ID            LAST_NAME                 SALARY                 
---------------------- ------------------------- ---------------------- 
114                    Raphaely                  11000                  
...
119                    Colmenares                2500 

(5) Restore the data copying it from the earlier version of the table:

SQL> insert into hr.emp 
       (select * 
        from hr.emp AS OF TIMESTAMP to_timestamp('12-08-10 11:00:00', 'mm-dd-yy HH24:MI:SS')
        where department_id = 30 );

6 rows inserted.

Using Flashback Version Query
  • You can retrieve the different versions of a given row that existed during a given time interval.
  • Use VERSIONS { BETWEEN {SCN | TIMESTAMP} start AND end}
  • Returns one row for each version of the existed at any time in the specified interval
(a) check the initial values of the row data, current scn and timestamp
SQL> select employee_id, first_name, last_name, salary, commission_pct
     from hr.emp
     where employee_id = 116;

EMPLOYEE_ID            FIRST_NAME           LAST_NAME                 SALARY                 COMMISSION_PCT         
---------------------- -------------------- ------------------------- ---------------------- ---------------------- 
116                    Shelli               Baida                     2900                                          
                     
SQL> select current_scn, to_char(systimestamp, 'mm-dd-yy  HH24:mi:ss') time
     from v$database;

URRENT_SCN            TIME               
---------------------- ------------------ 
1400261                12-08-10  16:15:20

(b) update salary, and commission
SQL> update hr.emp
     set salary = salary*1.5
     where employee_id = 116;
SQL> commit;

SQL> update hr.emp
     set commission_pct = .12
     where employee_id = 116;
SQL> commit;

(c) Query table emp pseudocolumns (generated by the supplemental logging)
SQL> select versions_startscn as ver_startscn, 
            versions_starttime as ver_startime, 
            versions_endscn as ver_endscn, 
            versions_endtime as ver_endtime,
            versions_xid as ver_xid, 
            versions_operation as oper,
            employee_id as emp_id, salary, commission_pct as comm_pct
      from hr.emp
        versions between timestamp
             to_timestamp('12-08-10 16:14:00', 'mm-dd-yy HH24:MI:SS')
        and  to_timestamp('12-08-10 16:16:30', 'mm-dd-yy HH24:MI:SS')
     where employee_id = 116;

VER_STARTSCN VERS_STARTTIME        VER_ENDSCN  VER_ENDTIME            VER_XID          OPER EMP_ID  SALARY   COMM_PCT 
------------ --------------------- ----------- ---------------------- ---------------- ---- ------- -------- -------- 
1400294      08-DEC-10 04.16.13 PM  1400301    08-DEC-10 04.16.25 PM  0A00100044030000 U    116     4350     0.12 
1400273      08-DEC-10 04.15.49 PM  1400294    08-DEC-10 04.16.13 PM  0A00030043030000 U    116     4350
                                    1400273    08-DEC-10 04.15.49 PM                        116     2900
The pseudo columns are:
VERSIONS_[ STARTSCN | STARTTIME ] Display the SCN or TIMESTAMP when row took on this value.
If the row was created before the lower bound SCN ot TIMESTAMP, a NULL is returned.
VERSIONS_[ ENDSCN | ENDTIME ] Display the last SCN and TIMESTAMP when row had this value.
If the value is still current at the upper bound SCN or TIMESTAMP a NULL is returned.
VERSIONS_XID ID of the transaction that created the row version.
VERSIONS_OPERATION Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)


Using Flashback Transaction Query
  • Extension to the Flashback Query Version option
  • Used to retrieve information for a given transaction or for all transactions in a given time interval.
  • The transaction IDs (listed in the VERSIONS_XID column) are used to query the FLASHBACK_TRANSACTION_QUERY view
  • The database must have at least minimal supplemental logging enabled
(a) Query the transaction that updated salary of employee_id 116.
col logon_user format a10
col table_name format a10
col table_owner format a10
col operation format a10
col undo_sql format a50

SQL> select logon_user, operation, table_name, 
       to_char(start_timestamp, 'mm-dd-yy hh24:MI:ss') time, row_id, undo_sql
     from flashback_transaction_query
     where xid = hextoraw('0A00030043030000');

LOGON_USER OPERATION  TABLE_NAME TIME               ROW_ID              UNDO_SQL
---------- ---------- ---------- ------------------ ------------------- --------------------------------------------------
SYSTEM     UPDATE     EMP       12-08-10 16:15:46  AAASNxAAEAAAAJHAAB  update "HR"."EMP" set "SALARY" = '2900' where
                                                                         ROWID = 'AAASNxAAEAAAAJHAAB';
SYSTEM     BEGIN

Example: check the various actions performed by a given transaction
SQL> select current_scn from v$database;

CURRENT_SCN            
---------------------- 
1424010

SQL> create table emp
     (empno number, 
      empname varchar2(10),
      salary number);

SQL> select current_scn from v$database;
SQL> insert into emp values (1, 'John', 20000);
SQL> commit;

SQL> update emp set salary = salary*1.2 where empno=1;
SQL> commit;

SQL> delete from emp where empno = 1;
SQL> commit;

SQL> insert into emp values (1, 'Jack', 45000);   -- transaction performs multiple actions 
SQL> update emp set salary = salary*1.05;
SQL> commit;

SQL> update emp set salary = salary + 3320;
SQL> commit;

SQL> select current_scn from v$database;

CURRENT_SCN            
---------------------- 
1424312

(a) To retrieve the various versions of the row: 
SQL> select versions_xid xid, versions_startscn start_scn,
        versions_endscn end_scn, versions_operation operation,
        empname, salary
     from emp
       versions between scn minvalue and maxvalue
     where empno = 1;
/
XID              START_SCN              END_SCN                OPERATION EMPNAME    SALARY                 
---------------- ---------------------- ---------------------- --------- ---------- ---------------------- 
0200060044040000 1424201                                       U         Jack       50570                  
0600080064040000 1424197                1424201                I         Jack       47250                  
05000A006F040000 1424192                                       D         John       24000                  
                                        1424192                          John       24000


(b) The transaction '0600080064040000' inserted the new record with a wrong name. 
To audit all the changes made by this transaction: 

SQL> select xid, start_scn, commit_scn, operation, logon_user, undo_sql
     from flashback_transaction_query
     where xid = hextoraw('0600080064040000')

XID              START_SCN  COMMIT_SCN OPERATION  LOGON_USER UNDO_SQL
---------------- ---------- ---------- ---------- ---------- --------------------------------------------------
0600080064040000 1424195    1424197    UPDATE     SYSTEM     update "SYSTEM"."EMP" set "SALARY" = '45000' where
                                                             ROWID = 'AAASXdAABAAAVBJAAB';

0600080064040000 1424195    1424197    INSERT     SYSTEM    delete from "SYSTEM"."EMP" where ROWID = 'AAASXdAA
                                                            BAAAVBJAAB';

0600080064040000    1424195    1424197 BEGIN   SYSTEM


Using Flashback Table

Flashback Table x Flashback Drop
  • FLASHBACK TABLE to SCN uses undo data to revert DMLs:
    • Delete rows inserted since scn_num
    • Update back rows modified since scn_num
    • Insert rows deleted since scn_num
  • FLASHBACK TABLE to BEFORE DROP uses a recycle bin to restore dropped table.
  • None of them, however, recover TRUNCATED rows.
  • For this you need to use FLASHBACK DATABASE

Allows point-in-time recovery for a table:
  • restore point specified by timestamp or SCN
  • table can stay online while operation executes
  • maintains all table attributes (indexes, triggers, constraints, etc)
  • maintains any remote state in a distributed environment
  • maintains data integrity specified by constraints

Requisites:
  • User must have FLASHBACK ANY TABLE or FLASHBACK object privilege on the table
  • Must have SELECT, INSERT, DELTE and ALTER privileges on the table
  • Must have SELECT ANY DICTIONARY or FLASHBACK ANY TABLE system privilege or SELECT_CATALOG_ROLE role

For an object to be eligible to be flashed back:
  • Object MUST NOT be: part of a cluster, materialized views, AQ tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual partitions or subpartitions.
  • Table structure must not have been changed between now and the point of restore
  • Row movement must be enabled (rowids will change)
  • Enough undo data to perform flashback exists on the undo tablespaces.

(a) enable row movement

SQL> alter table emp enable row movement;

SQL> select * from emp;
EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
1                      Jack       50570        

(b) check current scn (before flashback)

SQL> select current_scn from v$database;
CURRENT_SCN            
---------------------- 
1426179

(c) Insert and update data row data

SQL> Insert into emp values (2, 'Mary', 2000);
SQL> commit;
SQL> select current_scn from v$database;

CURRENT_SCN            
---------------------- 
1426185 

SQL> insert into emp values(100, 'Joseph', 450);
SQL> commit;
SQL> select current_scn from v$database;

CURRENT_SCN            
---------------------- 
1426200   

SQL> update emp set salary = salary*2;
SQL> commit;
SQL> select current_scn from v$database;

CURRENT_SCN            
---------------------- 
1426208 

(d) ensure that enough undo data exists to flashback the table

SQL>  select name, value/60 minutes_retained
      from v$parameter
      where name = 'undo_retention';

NAME              MINUTES_RETAINED       
----------------- ---------------------- 
undo_retention    80                     
 
(e) determine whtehr the table has dependencies on other tables. 
If dependencies exist, these may need to be flashbacked also

SQL>  select other.owner, other.table_name
    from sys.all_constraints this
         sys.all_constraints other
    where this.owner = 'SYSTEM'
    and   this.table_name = 'EMP'
    and   this.r_owner = other.owner
    and   this.r_constraint_name = other.constraint_name
 and   this.constraint_type = 'R';


(f) Check new data

SQL>  select * from emp;

 EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900  


(g) flashback table to point in time (SCN) before  data modification

SQL> flashback table emp to scn 1426179;

SQL> select * from emp;

EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
1                      Jack       50570                  


(h) flashback table forward to after data modification

SQL> flashback table emp to scn 1426208;

SQL>  select * from emp;
 EMPNO                  EMPNAME    SALARY                 
---------------------- ---------- ---------------------- 
2                      Mary       4000                   
1                      Jack       101140                 
100                    Joseph     900