| Recovery from 'create tables as' [message #506595] |
Tue, 10 May 2011 03:14  |
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 #506598 is a reply to message #506596] |
Tue, 10 May 2011 03:54  |
John Watson
Messages: 8930
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 #506601 is a reply to message #506599] |
Tue, 10 May 2011 04:06 |
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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
