| how to recover one table without any backup [message #403972] |
Tue, 19 May 2009 23:01  |
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 #404011 is a reply to message #403972] |
Wed, 20 May 2009 01:17  |
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 |
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 #411596 is a reply to message #403972] |
Sun, 05 July 2009 21:58 |
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 #411833 is a reply to message #404009] |
Tue, 07 July 2009 01:51 |
trantuananh24hg
Messages: 744
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?
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
