Friday, 10 December 2010

Flashback Explained


This is a very quick guide to Oracle Flashback.

Any form of restoring, such as Flashback, should always be done after reading the docs. for a final refresher - 10 minutes up-front may save hours later!

There are 2 basic types of flashback:
1. Flashback
2. Flashback Database

Plain "Flashback" is used to do one of these:
1. Perform queries that return past data
2. Perform queries that return metadata that shows a detailed history of changes to the database
3. Recover tables or rows to a previous point in time
4. Automatically track and archive transactional data changes
5. Roll back a transaction and its dependent transactions while the database remains online

"Flashback Database" is, in effect, a rewind button for the entire database. You simply tell Oracle how far back you want to go, and ta-da! it's done. It is a database-wide thing and can be thought of as an alternative to an RMAN restore/recovery, only faster, as no need to restore from tape, etc. It can be very useful recovering from unpleasant effects of patching, code releases, or anything that could screw the database. Flashback Database uses Flashback Logs & Archive Redo Logs, which are available to the database at all times. You need to decide how much space you allocate to flashback logs & archive redo logs, as disk can soon fill, if you aren't careful.

An example of Flashback Database:
The basic idea is to flashback the database to time T1, then ALTER OPEN DATABASE RESETLOGS, again, just like you do with an RMAN incomplete recovery, to have the database as it was at time T1. The database is now usable and ready for business - but everything that happened in the database since time T1 has now gone.
e.g. Assuming it is 16:30 on September 2nd. Someone did something insane in the database at 21:34 the day before (the 1st) utterly wrecking the database and the only sensible course of action left open is to flashback the database:
RMAN> FLASHBACK DATABASE TO TIME TO_DATE('01-SEP-2010 21:33:00','DD-MON-YYYY HH24:MI:SS')";

Note: As well as timestamp-based flashback, it can also be based on SCN or Restore Point. See the docs. for details. I recommend that you create a restore point before patching/upgrading, as it is then really easy to put the database back, should the patch/upgrade fail.
SCN-based flashback has a potential problem if the database has been flashed back or undergone PITR - again, search the docs. for "ambiguous scn".

If you want to check that the database data is what you want it to be - i.e. that you have flashed back to the right time - than issue:
RMAN> SQL 'ALTER DATABASE OPEN READ ONLY';

Assuming that you are happy with the database, you then do this:
RMAN> ALTER DATABASE OPEN RESETLOGS;

You don't have to do RESETLOGS, you can issue RECOVER, and the database fastforwards to the time you began the flashback - i.e. no data at all is lost, and you are back where you started. The docs. say that you could also export data before the RESETLOGS in order to get at "lost" data, but I recommend plain Flashback for that, as it is far less disruptive.

Plain "Flashback" refers to letting you view past states of database objects, or to return database objects to a previous state, without using point-in-time media recovery. This type of Flashback makes use of Undo, not Flashback Logs or Archive Redo Logs. Obviously, Undo configuration is key for this to work as expected, and you will have to work out how much disk space your undo file/s require, as well as enabling Automatic Undo Management, and reviewing such things as RETENTION GUARANTEE. Check out the docs.

As you can see, both types of flashback rely on disk space being available - you really do need to make sure that you provide enough disk space, and think through guaranteed retention, housekeeping any restore points, etc. to get what you want from flashback, and avoid problems

An example of plain Flashback:
An email arrives in the DBA mail queue. User XYZ has done it again and needs to "correct a small mishap", i.e. he has wrecked a table. It happened at 10:02 on December 3rd.
Assuming table name is USER01.TABLE01:
SQL> CREATE TABLE USER01.RESTORED_DATA AS
     (SELECT *
     FROM USER01.TABLE01
     AS OF TIMESTAMP
     TO_TIMESTAMP('03-DEC-2010 09:30:00', 'DD-MON-YYYY HH:MI:SS')
     );


User XYZ can now use the data, in table USER01.RESTORED_DATA,  to rectify his mistake.

Monday, 6 December 2010

DDL for Scheduler Jobs

DBMS_METADATA is great for getting any object DDL, but with scheduler jobs it's not so intuitive.
Rather than JOB, or whatever, it takes PROCOBJ:

Example:  
fakeuser@dev01>BEGIN
  2  DBMS_SCHEDULER.create_job (
  3  job_name        => 'my_fake_job',
  4  job_type        => 'PLSQL_BLOCK',
  5  job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
  6  start_date      => SYSTIMESTAMP,
  7  repeat_interval => 'freq=hourly; byminute=0',
  8  end_date        => NULL,
  9  enabled         => TRUE,
 10  comments        => 'My fake job.');
 11  end;
 12  /

PL/SQL procedure successfully completed.

fakeuser@dev01>select dbms_metadata.get_ddl('PROCOBJ',JOB_NAME) FROM USER_SCHEDULER_JOBS;

DBMS_METADATA.GET_DDL('PROCOBJ',JOB_NAME)
--------------------------------------------------------------------------------


BEGIN
dbms_scheduler.create_job('"MY_FAKE_JOB"',
job_type=>'PLSQL_BLOCK', job_action=>
'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('06-DEC-2010 01.26.37.186707000 PM +00:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'freq=hourly; byminute=0'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
'My fake job.'
);
dbms_scheduler.enable('"MY_FAKE_JOB"');
COMMIT;
END;