Home » RDBMS Server » Backup & Recovery » Incomplete recovery for truncate table situation (10gr2, centos 5.3)
Incomplete recovery for truncate table situation [message #403942] Tue, 19 May 2009 11:54 Go to next message
kytemanaic
Messages: 55
Registered: February 2009
Member
this is the following that I do

first I truncate a table and take note of the SCN
SQL> select * from cdr;                                                          

    CDR_ID CONNECT_E USER_ID
---------- --------- --------------------
        19 20-APR-09 frankpachot
        20 20-MAY-09 michelcadot

SQL> truncate cdr;                                                               
truncate cdr
           *
ERROR at line 1:
ORA-03290: Invalid truncate command - missing CLUSTER or TABLE keyword


SQL> truncate table cdr;                                                         

Table truncated.

SQL> exit


Timestamp to SCN

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF3';      

Session altered.

SELECT SYSTIMESTAMP,      LOCALTIMESTAMP    ,      DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER AS scnS scn
  2    FROM   dual;                                                              

SYSTIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
       SCN
----------
20-MAY-09 12.07.58.290886 AM +08:00
20-MAY-2009 00:07:58.290
    712077


SCN after truncate

SYSTIMESTAMP
---------------------------------------------------------------------------
LOCALTIMESTAMP
---------------------------------------------------------------------------
       SCN
----------
20-MAY-09 12.13.06.015679 AM +08:00
20-MAY-2009 00:13:06.015
    712271


Here's what I did in RMAN

RUN
{ 
    SET UNTIL SCN 712077;    
  # Alternatives:
  # SET UNTIL TIME 'Nov 15 2004 09:00:00';
  # SET UNTIL SEQUENCE 9923;  
  RESTORE DATABASE;
  RECOVER DATABASE;
9> }                                                                             

executing command: SET until clause

Starting restore at 20-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring datafile 00001
input datafile copy recid=12 stamp=687233181 filename=/u01/app/oracle/flash_recov
.....

archive log filename=/u01/app/oracle/flash_recovery_area/TOMKYTE/archivelog/2009_05_19/o1_mf_1_30_515nml6w_.arc thread=1 sequence=30
media recovery complete, elapsed time: 00:00:06
Finished recover at 20-MAY-09

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

RMAN> list INC
INCARNATION  INCLUDE      INCLUDING    INCREMENTAL  
RMAN> list INC
INCARNATION  INCLUDE      INCLUDING    INCREMENTAL  
RMAN> list INCARNATION ;                                                         


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TOMKYTE  3946756354       PARENT  1          30-JUN-05
2       2       TOMKYTE  3946756354       PARENT  446075     10-MAY-09
3       3       TOMKYTE  3946756354       PARENT  710178     19-MAY-09
4       4       TOMKYTE  3946756354       CURRENT 712090     20-MAY-09
 


SQL> select * from cdr;                                                          

    CDR_ID CONNECT_E USER_ID
---------- --------- --------------------
        19 20-APR-09 frankpachot
        20 20-MAY-09 michelcadot

SQL> 


the above shows that the table is recover to the state before truncate, now correct me if I'm wrong........truncate does not not create logs.

so how can the table recover to the state before truncate?

any assistance is greatly appreciated!

with reference from

http://www.orafaq.com/forum/m/262064/137592/?srch=restore+truncate#msg_262064

michel said
"
truncate is just operations on dictionary (SYS tables) this is why it is fast and this is also why it is logged: operations on SYS tables are always logged.
"

is this the reason why I'm able to restore the table before truncate?

thanks a lot!
Re: Incomplete recovery for truncate table situation [message #403945 is a reply to message #403942] Tue, 19 May 2009 13:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the above shows that the table is recover to the state before truncate, now correct me if I'm wrong........truncate does not not create logs.

Of course it does, not on your data but on dictionary modifications.
It is easy to see it: use LogMiner and you will see it will find your truncate.

Quote:
how can the table recover to the state before truncate?


You recover before the truncate, so the truncate was not executed.

Quote:
michel said
"
truncate is just operations on dictionary (SYS tables) this is why it is fast and this is also why it is logged: operations on SYS tables are always logged.
"

Hey! I didn't see it but say the same thing above.

Quote:
is this the reason why I'm able to restore the table before truncate?

No, the reason is that you recover BEFORE the truncate happened. It will be true for any operation, if you recover before the scn the operation happened then the operation didn't appear after the recover.

Regards
Michel
Re: Incomplete recovery for truncate table situation [message #403946 is a reply to message #403942] Tue, 19 May 2009 13:05 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Truncate is still a DDL and so is fully recoverable.
And you go to a point before truncate.
Previous Topic: Need your assitance to CREATE an 10g RMAN level 1 backup script
Next Topic: Solaris x86 with Oracle RAC 10g Enterprise Edition Release 10.2.0.3.0
Goto Forum:
  


Current Time: Thu Dec 08 18:34:50 CST 2016

Total time taken to generate the page: 0.22957 seconds