drop table and constraints
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: drop table and constraints

  1. #1
    Join Date
    Feb 2001
    Posts
    51

    OK,

    I did my backup about 3hrs ago. Just now I had drop a table by accident. how do I recover this back?

    I'm new at this, so please give me a step-by-step.

    thanks.

  2. #2
    Join Date
    Feb 2001
    Posts
    41
    do you know what tablespace the table was from, or is your database running in archivelog mode. if it is running in archive log mode than issue the command> alter database recover tablespace mytb ;
    probably if it is drop very recently than it will do the changes from you redo's other wise executable will make you enter destination of all your archive logs.

    Best Regards,
    Harsh Shah

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    This is ROhit Nirkhe from India,OCP.Tell me whether u have taken the export of the table or u have performed backup of the database by taking individual tablespace backup.Let me know about the same so that I can very well tell u how u can recover the table.
    Write to me at rohitsn@orasearch.net


    Regards,
    Rohit Nirkhe,Oracle DBA,OCP



  4. #4
    Join Date
    Feb 2001
    Posts
    51

    Unhappy

    Yes, I am running in archive mode.

    this is what I get.

    SQL> alter database recover tablespace users;
    alter database recover tablespace users
    *
    ERROR at line 1:
    ORA-00283: recovery session canceled due to errors
    ORA-01124: cannot recover data file 2 - file is in use or recovery
    ORA-01110: data file 2: '/u03/oradata/gold/users01.dbf'

    what to do now?

  5. #5
    Join Date
    Feb 2001
    Posts
    2

    drop table

    restore the backup and do the point in time recovery (till last 3 hrs). u will get the earlier data.
    if export is available then import can also help if the earlier option is of no use
    [QUOTE][i]Originally posted by looser [/i]

    [B]
    OK,

    I did my backup about 3hrs ago. Just now I had drop a table by accident. how do I recover this back?

    I'm new at this, so please give me a step-by-step.

    thanks. [/B][/QUOTE]

  6. #6
    Join Date
    Feb 2001
    Posts
    51
    K, here's what I did,

    I copied the users01.dbf back.

    shutdown the database
    startup mount
    connect internal
    alter database datafile '/u03/oradata/gold/users01.dbf' online;
    recover datafile '/u03/oradata/gold/users01.dbf' ;

    SQL> recover database until time '2001-02-27:14:00:00';
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01194: file 1 needs more recovery to be consistent
    ORA-01110: data file 1: '/u02/oradata/gold/oradata/gold/system01.dbf' <is this normal to call for this file?
    so, what I did, was copied this system01.dbf file over and run through the same commands, and I got this.
    SQL> recover database until time '2001-02-27:14:00:00';
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: '/u02/oradata/gold/oradata/gold/system01.dbf'

    please tell me what I did wrong?

    thanks

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You are trying to perform point-in-time recovery, so you must restore your complete backup, not only one file.

    1. Restore all db files from your backup
    2. Perform a recovery up to specified time in the past.
    3. Open with resetlogs option.

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Feb 2001
    Posts
    51

    Ummm, I don't really want to restore the whole thing, if I can avoid it. All that is lost was just one table.

    but if I do have to do the whole restore, do I have to run through the same commands for all the files?

    or do I just do something like:
    alter database reset logfile;

    I wait for your responds.

    thanks

  9. #9
    Join Date
    Feb 2001
    Posts
    389
    OK,

    Did u do export or hot backup.
    If export , then it is easy u import at table level .
    If hot backup, then u have to restore this whole database backup, bring up the database , export at table level and bring the orig(latest) database backup and import from the export.

    Or if u have enough disk space, u can bring up the old database with new db_name and use export/import, Create as select , copy .

  10. #10
    Join Date
    Feb 2001
    Posts
    114
    If u have a backup already and the database is in archivelog mode,
    connect internal
    startup mount <instance_name>
    recover database until time 'yyyy-mm-dd:24HH:mi:ss';
    alter database open resetlogs;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width