Home » RDBMS Server » Backup & Recovery » Recovery from 'create tables as' (Oracle 11g R2, AIX )
Recovery from 'create tables as' [message #506595] Tue, 10 May 2011 03:14 Go to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member

Sirs,

Hi again...

Another recovery problem where one of our developers
recreated tables using 'create table as' (via shell scripts)
but on the created (new) ones he dropped a very
important column.

We had a full backup last friday but if only you could
help me to just bring back the original (old) tables.
We don't want to restore from backup since it would take
a day of restoration.

dB is in archivelog & flash recovery mode.
I tried issuing this SQL command, but it failed:
"CREATE TABLE tabl_hist AS SELECT *
FROM orig_tabl AS OF TIMESTAMP '2011-05-09 10:15:00';"

Herewith is the shell script I've mentioned:
===========================================
ORACLE_SID=<SID>
export ORACLE_SID

# 1. create table copy
# 2. drop old table source_id
# 3. drop old table
# 4. create target table with select distinct

for i in `cat for_deduping`
do

echo "-------------------------------------------"
echo "Starting $i " >> dedup.log

sqlplus / as sysdba <<ENDOFSQL

set serveroutput on
create table ${i}_history as select * from ${i};
** alter table ${i}_history drop column source_id; **
drop table ${i};
create table ${i} as select distinct * from ${i}_history;
select count(*) from ${i}_history;
select count(*) from ${i};

ENDOFSQL

echo "-------------------------------------------"

done

=========
Thanks in advance!
Re: Recovery from 'create tables as' [message #506596 is a reply to message #506595] Tue, 10 May 2011 03:19 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
reym21 wrote on Tue, 10 May 2011 10:14

I tried issuing this SQL command, but it failed


Failed how? That will most likely be the reason why it can't be done.
Re: Recovery from 'create tables as' [message #506598 is a reply to message #506596] Tue, 10 May 2011 03:54 Go to previous messageGo to next message
John Watson
Messages: 8702
Registered: January 2010
Location: Global Village
Senior Member
I think it will have failed because the table was dropped and created during the flashback query period. I would say your best option is to use the database flashback that I think you say you have enabled:

shutdown abort
startup mount
flashback to (perhaps) 2011-05-09 10:15:00
open read only
export the table
shutdown abort
startup mount
complete recovery
open
import the table

This will be astronomically faster than doing a restore. But you'll need to work fast, before your db_flashback_retention_target expirtes.
Re: Recovery from 'create tables as' [message #506599 is a reply to message #506596] Tue, 10 May 2011 03:57 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member

It says here:

SQL> select sysdate from dual;

SYSDATE
---------
10-MAY-11

SQL> create table ownr.Table1_HISTORY as select *
2 from ownr.Table1 as of timestamp to_date('09-MAY-11');

create table ownr.Table1_HISTORY as select *
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
Re: Recovery from 'create tables as' [message #506600 is a reply to message #506598] Tue, 10 May 2011 03:59 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member

Thanks so much for this!

We'll try it first.

Regards.
Re: Recovery from 'create tables as' [message #506601 is a reply to message #506599] Tue, 10 May 2011 04:06 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
reym21 wrote on Tue, 10 May 2011 10:57

ORA-00955: name is already used by an existing object


Of course, when ownr.Table1_HISTORY already exist, you can't create it again. Try creating it as ownr.Table1_HISTORY_1 or whatever.
Re: Recovery from 'create tables as' [message #506722 is a reply to message #506601] Tue, 10 May 2011 22:05 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member

ThomasG,

After changing my code, here's my new error:

SQL> create table ownr.Table1_HISTORY_X
2 from ownr.Table1 as of timestamp to_date('09-MAY-11');

ERROR at line 2:
ORA-01466: unable to read data - table definition has changed

Thanks.
Re: Recovery from 'create tables as' [message #506725 is a reply to message #506722] Tue, 10 May 2011 23:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68165
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot flashback accross a DDL.

Regards
Michel
Re: Recovery from 'create tables as' [message #506729 is a reply to message #506725] Wed, 11 May 2011 00:27 Go to previous message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member

Sirs,

Thanks for all your help.

We will be restoring from a May 7, 2011 full backup.

Regards...
Previous Topic: hot backup in noarchivelog mode
Next Topic: rman backup fails on windows when using batch file
Goto Forum:
  


Current Time: Mon May 16 14:08:30 CDT 2022