Home » RDBMS Server » Backup & Recovery » how to recover one table without any backup (oracle 10.2.0.4 solaris)
how to recover one table without any backup [message #403972] Tue, 19 May 2009 23:01 Go to next message
fairdaus
Messages: 14
Registered: May 2009
Location: KL
Junior Member
hi..
just one question

i have one db. running on solaris 10 db version 10.2.0.4 and log_mode= ARCHIVELOG and FLASHBACK_ON=OFF, no coldbackup or hot backup.

suddenly one dba was delete one table (employee) ( size=20 MB)

so my question how to recovery that table

tq
Re: how to recover one table without any backup [message #403974 is a reply to message #403972] Tue, 19 May 2009 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>so my question how to recovery that table
You do not recover table.
Re: how to recover one table without any backup [message #403975 is a reply to message #403974] Tue, 19 May 2009 23:07 Go to previous messageGo to next message
fairdaus
Messages: 14
Registered: May 2009
Location: KL
Junior Member
how to restore back that table
Re: how to recover one table without any backup [message #403976 is a reply to message #403975] Tue, 19 May 2009 23:09 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
fairdaus wrote on Tue, 19 May 2009 21:07
how to restore back that table

>no coldbackup or hot backup.
Restore from what?
Re: how to recover one table without any backup [message #404009 is a reply to message #403972] Wed, 20 May 2009 01:16 Go to previous messageGo to next message
fairdaus
Messages: 14
Registered: May 2009
Location: KL
Junior Member
hi.

this link solve my problem

www.oracle.com/technology/obe/10gr2_db_single/ha/flashback/flashback_otn.htm

tq
Re: how to recover one table without any backup [message #404011 is a reply to message #403972] Wed, 20 May 2009 01:17 Go to previous messageGo to next message
previnfelix
Messages: 2
Registered: May 2009
Junior Member
You could try this provided that the table is still in the recycle bin

SQL> select FLASHBACK_ON, LOG_MODE from v$database;

FLASHBACK_ON LOG_MODE
------------------ ------------
NO NOARCHIVELOG

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
X TABLE

SQL> drop table x;

Table dropped.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$CC3TKE2PQxGgkjBP+pVkhA==$0 TABLE

SQL> select OWNER,OBJECT_NAME,ORIGINAL_NAME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='X';

OWNER OBJECT_NAME ORIGI CAN
------------------------------ -------------------- ----- ---
ALI BIN$CC3TKE2PQxGgkjBP X YES
+pVkhA==$0

SQL> flashback table x to before drop;

Flashback complete.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
X TABLE

Re: how to recover one table without any backup [message #411442 is a reply to message #403972] Fri, 03 July 2009 11:34 Go to previous messageGo to next message
sangram_ocp
Messages: 12
Registered: February 2009
Location: Mumbai
Junior Member
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jul 3 21:57:57 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

SQL> select * from demo1;

NO
----------
4.0200E+10
SQL> drop table demo1;

Table dropped.

SQL> flashback table demo1 to before drop;
SP2-0734: unknown command beginning "flashback ..." - rest of line ignored.

As per the above post this should work. Right.?
Please reply.
Re: how to recover one table without any backup [message #411443 is a reply to message #403972] Fri, 03 July 2009 11:36 Go to previous messageGo to next message
sangram_ocp
Messages: 12
Registered: February 2009
Location: Mumbai
Junior Member
SQL> select OWNER,OBJECT_NAME,ORIGINAL_NAME,CAN_UNDROP from dba_recyclebin;

no rows selected

SQL> select FLASHBACK_ON, LOG_MODE from v$database;

FLASHBACK_ON LOG_MODE
------------------ ------------
NO NOARCHIVELOG
Re: how to recover one table without any backup [message #411444 is a reply to message #411443] Fri, 03 July 2009 12:02 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You indicate both 9i and 10g. What version are you running these commands on ? Try to use a 10g client.

What does show parameter recyclebin indicate ?

[Updated on: Fri, 03 July 2009 12:04]

Report message to a moderator

Re: how to recover one table without any backup [message #411596 is a reply to message #403972] Sun, 05 July 2009 21:58 Go to previous messageGo to next message
previnfelix
Messages: 2
Registered: May 2009
Junior Member

hi

I guess the problem lies in the sqlplus client. 9i sqlplus client does not know of flashback and thus gives the error. Note that is not an ORA error. Here's an example

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jul 6 09:04:51 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> flashback
SP2-0042: unknown command "flashback" - rest of line ignored.
SQL>

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 6 10:40:27 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> flashback;
flashback
*
ERROR at line 1:
ORA-00905: missing keyword


SQL>

Re: how to recover one table without any backup [message #411658 is a reply to message #411596] Mon, 06 July 2009 04:46 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Hence,

ebrian wrote on Fri, 03 July 2009 13:02
You indicate both 9i and 10g. What version are you running these commands on ? Try to use a 10g client.

Re: how to recover one table without any backup [message #411833 is a reply to message #404009] Tue, 07 July 2009 01:51 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
fairdaus wrote on Wed, 20 May 2009 13:16
hi.

this link solve my problem

www.oracle.com/technology/obe/10gr2_db_single/ha/flashback/flashback_otn.htm




What's about the following statement?
SQL> drop table X purge;


Will you restore it from recycle bin? Of course, you will not if you have not had any backup before.

So that, the above link helped you if client did not really purge and the server side DB used Recyclebin option.

[Updated on: Tue, 07 July 2009 01:52]

Report message to a moderator

Previous Topic: RMAN-06433: error installing recovery catalog
Next Topic: PLS-00553: character set name is not recognized
Goto Forum:
  


Current Time: Sat Dec 03 18:33:23 CST 2016

Total time taken to generate the page: 0.04587 seconds