Oracle DBA Tips and Techniques

By Arjun Raja

Archive for September, 2009

Flashback database to scn

Posted by Arjun Raja on September 9, 2009

FLASHBACK DATABASE TO SCN –

Time to FLASHBACK DATABASE TO – 06:30 a.m on 31 July.

Get oldest flashback time –

Col myscn format 99999999999
SELECT OLDEST_FLASHBACK_SCN MYSCN ,to_char(OLDEST_FLASHBACK_TIME,’DD-MON-YYYY HH24:MI:SS’) FROM V$FLASHBACK_DATABASE_LOG;

If there are logs up until 06:30 on 31 Jul, you can flashback.

Get the SCN number to flashback to –

col first_change# format 99999999999
select first_change# ,to_char(first_time,’DD-MON-YYYY HH24:MI:SS’),sequence# from V$LOG_HISTORY ORDER BY 1 ;

23856899540 31-JUL-2009 04:30:09 119695
23856965501 31-JUL-2009 06:30:10 119696
23857031498 31-JUL-2009 08:30:09 119697
23859233947 31-JUL-2009 10:00:29 119704
23859242647 31-JUL-2009 10:31:34 119705

Note down the SCN at the time you want to flashback to which is the FIRST_CHANGE# in the V$LOG_HISTORY view –

Restore a few archivelogs prior to and after the flashback time – the log sequence is in the last column above….

Ex – to restore archivelogs from 119695 to 119702

rman target / catalog rman/rman@rman

RMAN > restore archivelog from sequence 119695 until sequence 119705;

Once the logs are restored make sure no automatic RMAN backup job of archivelogs run which delete old archivelogs from disk –

Next flashback the database –

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

FLASHBACK DATABASE TO SCN 23856965501;

Once FLASHBACK is complete-

Alter database open read only –

If satisfied with the data –

Shutdown immediate;

Startup mount;

Alter database open resetlogs;

To get rid of old flashback logs –

Shutdown immediate;

Startup mount;

Alter database flashback off ; – Oracle automatically deletes old flashback logs.

Alter database flashback on – restart flashback.

ALTER DATABASE OPEN;

Posted in High Availability | Tagged: | Leave a Comment »

DELETE DUPLICATES IN TABLE

Posted by Arjun Raja on September 9, 2009

DELETE DUPLICATES IN A TABLE –

EX – TABLE TEST

SQL> desc test;

COL_1 NUMBER(10)
COL_2 DATE

SQL>

select count(*) from test WHERE ROWID IN
(select rowid from test
minus
select max(rowid) from test
group by
col_1,col_2);

delete from test WHERE ROWID IN
(select rowid from test
minus
select max(rowid) from test
group by
col_1,col_2);

x——–x———-x———x———-x

Posted in Administration | Tagged: | Leave a Comment »