Oracle Illustrated

Feb 21 2010   7:26AM GMT

Migrating from 9i to 11g – Flashback

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

This is one of my favorite topics !!

Flashback feature – available since Oracle 9i. There are quite a few new features in Oracle 10g.

The new and improved flashback technique performs recovery in a faster pace. Flashback features offers the capability to query past versions of schema objects, query historical data perform change analysis or perform self-service repair to recover from logical corruptions everything when the database is online.

Advantages:

– 24X7 database availability
– Saves time

Includes

Flashback Database

Flash Recovery Area
Enable Flashback Logging through Enterprise Manager or by issuing SQL command

It is faster than the traditional recovery. Time taken to restore the database is usually based on the number of transaction that needs to be recovered than the size of the database. The older recovery methods uses REDO LOG files to recover the database. Flashback database introduces a new type of log called FLASHBACK DATABASE LOG.

How does it work?

Oracle database periodically logs previous images of blocks into the flashback database logs. These blocks that are stored in the flashback database is used to quickly recover during the flashback phase.

This is DBA activity –

1. The database should be in archive mode –

By executing ARCHIVE LOG LIST command at SQL Plus prompt it will let us know whether the database is in archive mode or not

ARCHIVE LOG LIST;

Database log mode No Archive Mode
Automatic archival Disabled
Archive destination F:\log\archive
Oldest online log sequence 1
Current log sequence 2

If it is in “No Archive Mode” then following needs to be done to set it in archive mode

a. Shut down the database:

SQL> SHUTDOWN IMMEDIATE;

b. Open init.ora file and set following parameters:

log_archive_dest_1=’LOCATION=F:\log\archive’
log_archive_dest_2=’LOCATION=F:\log\archive1’ /* Only if there are two log files – optional */
log_archive_format=’%t_%s.ARCH’

c. Start database in mount exclusive mode

STARTUP MOUNT EXCLUSIVE PFILE=init.ora;

d. Start the database in ARCHIVELOG mode as follows:

ALTER DATABASE ARCHIVELOG;

e. Open the database

ALTER DATABASE OPEN;

2. Assign flashback recovery log file path, size and log retention values in init.ora file:

DB_RECOVERY_FILE_DEST=F:\log\archive \flasharea

Set the following :
DB_RECOVERY_FILE_DEST_SIZE
DB_FLASHBACK_RETENTION_TARGET

3. SYSDBA can only do any of this – open db in MOUNT EXCLUSIVE mode

SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;

4. check whether flashback is enabled
select log_mode, flashback_on from v$database;
LOG_MODE FLASHBACK ON
———— ——————-
ARCHIVELOG YES

To disable flashback database – ALTER DATABASE FLASHBACK OFF

Now, how to flashback the database

FLASHBACK DATABASE TO TIMESTAMP (SYSDATE);

We can also use SCN number to flashback the database – FLASHBACK DATABASE TO SCN

Oracle 9i
Not possible – only traditional recovery is possible.

Oracle 11g

Flashback Database (10g)
Database needs to be in Archive mode.

Recovery area param needs to be changed – DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE

select flashback_on from v$database;
NO

To enable it the following needs to be done.

STARTUP MOUNT EXCLUSIVE;
ALTER DATABASE FLASHBACK ON;

Set up DB_FLASHBACK_RETENTION_TARGET

FLASHBACK DATABASE TO TIME = TO_DATE (‘02/13/10 12:00:00’,’MM/DD/YY HH:MI:SS’);

— Purely DBA related

****************************************************************************

Flashback Drop

Recycle Bin – automatically enabled with Oracle Database 10g

To check the details

NAME TYPE VALUE
———————————— ———– ——————
buffer_pool_recycle string
db_recycle_cache_size big integer 0
recyclebin string on

Sometimes it happens that we accidently drop objects in the database. Flashback drop provides an option to recover the dropped objects like tables, triggers, indexes, constraints etc. When a table is dropped it is not actually dropped – it is just renamed and the renamed table name is available in the recycle bin. We can either drop the table permanently using purge or recover it using flashback. To drop the table without making it part of recycle bin then we can issue DROP TABLE PURGE command. USER / DBA_RECYCLEBIN contains the list of dropped objects.

I really wasted lot of time working on FLASHBACK DROP as SYSTEM user – it doesn’t work under SYSTEM / SYSDBA user

Flashback Drop (10g) – Does not work form SYSDBA user

DROP TABLE test_readonly;

SHOW recyclebin

— Note when a table is dropped it is just renamed. It is not droped when the RECYCLE mode is ON. So the space occupied is there as it is.

SQL> select object_name, original_name, operation from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION
—————————— ——————————– ———
BIN$uCJ5kSosTM+s7chj6cJeGQ==$0 TEST_READONLY DROP
BIN$wl9CUjW7RsapzkrsoHDm+w==$0 TEST_READONLY DROP

Note: TEST_READONLY was dropped, recreated then dropped again.

To retreive the table that was dropped – issue the following command.

SQL> flashback table “BIN$uCJ5kSosTM+s7chj6cJeGQ==$0″ to before drop;

Flashback complete.

Elapsed: 00:00:02.71
SQL> select * from test_Readonly;

A
———-
1

Elapsed: 00:00:00.06
SQL>

Lets try to retrieve the other table also – it should obviously result in error

SQL> select object_name, original_name, operation from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION
—————————— ——————————– ———
BIN$wl9CUjW7RsapzkrsoHDm+w==$0 TEST_READONLY DROP

Elapsed: 00:00:00.00
SQL> flashback table “BIN$wl9CUjW7RsapzkrsoHDm+w==$0″ to before drop;
flashback table “BIN$wl9CUjW7RsapzkrsoHDm+w==$0″ to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object

Elapsed: 00:00:00.07
SQL>


To drop a table without storing it in recyclebin –

SQL> DROP TABLE test_Readonly purge;

Table dropped.

Elapsed: 00:00:00.40
SQL> select * from user_recyclebin;

no rows selected

Elapsed: 00:00:00.00

To purge the table that was dropped

PURGE TABLE “BIN$wl9CUjW7RsapzkrsoHDm+w==$0”

Table purged.

Elapsed: 00:00:02.92

To purge the recyclebin

PURGE recyclebin;

Recyclebin purged.

****************************************************************************

UNDO Tablespace dependent Flashback Technologies

Following are possible with UNDO tablespace dependent flashback technology. Description about each can be found in below table.

Flashback Query
Flashback Table
Flashback Versions Query
Flashback Transactions Query

Flashback Table (10g)

When there is an application error or human error we prefer to flashback one or more tables to specific time point. Flashback table enables us to do this. To perform flashback table row movement must be enabled on the table. Also, FLASHBACK ANY TABLE privilege should be granted to that particular user to perform this operation. (GRANT FLASHBACK ANY TABLE TO ).

ALTER TABLE EMP ENABLE ROW MOVEMENT

ALTER TABLE DEPT ENABLE ROW MOVEMENT

FLASHBACK TABLE emp, dept TO TIMESTAMP
to_timestamp (’12-Feb-2010 16:00:00′,’dd-mon-yyyy hh24:mi:ss’)

Flashback complete

Flashback query (10g)

Flashback query (10g)

Enables to read the query as it is in the past.
Enables to quickly compare current vs past data – this is specifically useful for large tables. Sometimes we may need to compare two large tables. This option enables to compare the data without creating a new backup table.
Enables to recover deleted or changed data.

select a.ename, a.sal new_sal, b.sal old_sal,
(b.sal – a.sal) sal_diff
from emp a, emp AS OF TIMESTAMP
to_timestamp (’12-Feb-2010 16:00:00′,’dd-mon-yyyy hh24:mi:ss’) b
where a.empno = b.empno ;

— Same as in 9i
Flashback Version query (10g)

With flash back version query row level flashback is enabled.

Version is transaction based

Commit – Creates a version
Rollback – does not create a version

Flashback Version Query enables to show all the versions of data between two SCN’s or Timestamp’s. This requires UNDO (UNDO_RETENTION dependent).

Query table with new SQL operator => “VERSIONS BETWEEN” part of the FROM clause
Optionally include new pseudo-columns => VERSIONS_STARTTIME / _STARTSCN
Starting TIMESTAMP or SCN when row version was created
=> VERSIONS_ENDTIME / _ENDSCN
Ending TIMESTAMP or SCN when row version get completed
=> VERSIONS_OPERATION
Type of operation that created row version ((I)nsert, (U)update or (D)elete)
=> VERSIONS_XID (Unique transaction identifier for a row version)

insert into emp select rownum*5, ename, job, mgr, hiredate, sal, comm, deptno from emp

select versions_starttime, versions_endtime, versions_xid,
DECODE(versions_operation,’I’,’INSERT’,’U’,’UPDATE’,’D’,’DELETE’,’ORIGINAL’) as operation,
empno, deptno
from emp versions between
timestamp to_timestamp(’17-Feb-2010 21:00:00′,’dd-mon-yyyy hh24:mi:ss’)
and to_timestamp(’17-Feb-2010 21:00:00′,’dd-mon-yyyy hh24:mi:ss’)
order by VERSIONS_STARTTIME
/

ORIGINAL 7369 20
ORIGINAL 7499 30
ORIGINAL 7521 30
ORIGINAL 7566 20
ORIGINAL 7654 30
ORIGINAL 7698 30
ORIGINAL 7934 10
ORIGINAL 7788 20
ORIGINAL 7839 10
ORIGINAL 7844 30
ORIGINAL 7876 20
ORIGINAL 7900 30
ORIGINAL 7902 20
ORIGINAL 7782 10

14 rows selected.

Note: We cannot use temporary table / external tables with versioning query.

Flashback Transaction query (10g)

Sometimes, we realize that the data in a table has been inappropriately changed. To get this rectified, we can use multiple flashback queries and flashback version queries to view row data at specific point in time and its changes

delete from emp where empno in (select rownum*5 from emp);

14 rows deleted.

SELECT xid, operation, table_name, undo_sql FROM
flashback_transaction_query
WHERE xid IN (SELECT versions_xid FROM emp
VERSIONS BETWEEN TIMESTAMP
to_timestamp(’17-Feb-2010 21:00:00′,’dd-mon-yyyy hh24:mi:ss’)
and to_timestamp(’17-Feb-2010 21:00:00′,’dd-mon-yyyy hh24:mi:ss’)
WHERE versions_xid IS NOT NULL)
ORDER BY commit_timestamp
/

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: