Forum Stats

  • 3,874,424 Users
  • 2,266,733 Discussions
  • 7,911,843 Comments

Discussions

How can I recover a past "oradata" directory contents?

jfmorin
jfmorin Member Posts: 8
edited Mar 16, 2009 6:07AM in Recovery Manager (RMAN)
Yesterday, I accidentally dropped a schema from an Oracle Express instance. I retrieved the whole oradata directory contents from a backup made last Wednesday.

I tried virtually everything to my knowledge to recover the lost schema from those files: replacing the whole oradata contents or just the system tablespace file (Oracle won't start properly), RECOVER and RESTORE commands within RMAN (it won't work either). Also, the Flashback option is not active, so I can't rely on it.

Is there a way I can rollback the schema to its Wednesday state? If so, how should I proceed?

If I can only rollback the whole database to its Wednesday state, it is OK as well since I have recent up-to-date (.dmp) files of the other schemas.

I have read about backup and recovery in the Oracle documentation, but maybe there is something I don't get... What I am trying to do is "retrieve/restore a schema (or tablespace or database) from another location to the current +oradata+ location." Maybe it's not the right way to proceed, but I am still a bit naive about the mysteries of backup and recovery in Oracle...

Can someone please tell me what to do?

Thanks in advance...

Jeff

Answers

  • Paul M.
    Paul M. Member Posts: 10,947 Gold Trophy
    edited Mar 14, 2009 9:59AM
    I retrieved the whole oradata directory contents from a backup made last Wednesday.

    If I can only rollback the whole database to its Wednesday state, it is OK
    You can do that, assuming your backup was made with the DB shut down normally. You can do as follows :

    - Shut down your DB, if active
    SQL> shutdown immediate

    - Restore the whole oradata directory

    - Enter Sql command line and execute
    SQL> conn / as sysdba
    SQL> startup mount
    SQL> recover database until cancel
    SQL> alter database open resetlogs;
  • jfmorin
    jfmorin Member Posts: 8
    I transferred the files to my oradata directory, then:

    (1) conn / as sysdba: I did not have sufficient privileges. I had to connect as sys/my_password as sysdba. Does it make any difference?
    (2) startup mount succeeded.
    (3) recover database until cancel issued the following messages. What does that mean? I entered a simple <RETURN> to the "Specify log" prompt below.

    ORA-00279: change 3736352 generated at 03/10/2009 14:00:35 needed for thread 1
    ORA-00289: suggestion :
    /usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/archivelog/2009_03_14/o1_mf_1_86_%u_.arc
    ORA-00280: change 3736352 for thread 1 is in sequence #86

    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

    ORA-00308: cannot open archived log
    '/usr/lib/oracle/xe/app/oracle/flash_recovery_area/XE/archivelog/2009_03_14/o1_m
    f_1_86_%u_.arc'
    ORA-27037: unable to obtain file status
    Linux Error: 2: No such file or directory
    Additional information: 3



    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: '/usr/lib/oracle/xe/oradata/XE/system.dbf'

    (4) alter database open resetlogs; issued the following error.

    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01194: file 1 needs more recovery to be consistent
    ORA-01110: data file 1: '/usr/lib/oracle/xe/oradata/XE/system.dbf'



    After that, I tried recover database until cancel again, answering AUTO to the "Specify log" prompt. It didn't make any difference.

    The errors in (3) and (4) above let me suppose that the oradata directory contents might not be sufficient. Besides, the +?/flash_recovery_area/XE/archivelog/+ has only an empty +2009_03_14/+ subdirectory, i.e. there are no actual archive log files.

    Is there any other way to proceed?

    Thanks for your help,

    Jeff
  • Paul M.
    Paul M. Member Posts: 10,947 Gold Trophy
    I hope you did read carefully my post ( You can do that, assuming your backup was made with the DB shut down normally ) : was it so ?
  • jfmorin
    jfmorin Member Posts: 8
    edited Mar 14, 2009 2:13PM
    I don't know, for I am not the person in charge of backup management on that server. I guess files might be backed up without shutting down the database. Anyway, I just sent the person in charge an e-mail asking him your question.

    If the database was not shut down, does this mean there is no way to perform the recovery I want to do?

    Thanks,

    Jeff

    Edited by: jfmorin on Mar 14, 2009 11:12 AM
  • jfmorin
    jfmorin Member Posts: 8
    I just received a reply from our infrastructure guy: he uses backuppc (on Linux) to make backups and, to his knowledge, the database is not stopped during those backups.

    Does this mean that everything is lost and these backups happen to be useless?!?

    Thanks,

    Jeff
  • oradba
    oradba Member Posts: 10,214
    I don't know backuppc,but I guess that's an OS utility,which backs up the filesystem. Indeed - in case of Oracle datafiles - such OS backups are useless in most cases,when the database is open. You say archivelog destination is empty,it COULD be the corresponding online log isn't yet archived. Do you still have the online logs? Issue 'select * from v$log;' to see the actual online log sequence numbers. Requested is sequence number 86,if you have it,try to apply it explicitly.

    Werner
  • jfmorin
    jfmorin Member Posts: 8
    The query select from v$log;* does not return any data, which seems to correspond to your supposition about the absence of any online logs.

    So my backup happens to be useless without those logs? Well... things are going to change at my office... right away!

    Thanks anyway Paul and Werner!

    Jeff
  • oradba
    oradba Member Posts: 10,214
    edited Mar 16, 2009 6:07AM
    OS backups are only useful,when the database is down (after a CLEAN shutdown,not a crash). Here you can recreate lost online logs, Paul explained how. You should go immediately for RMAN backups, this is a quick (but not dirty) guide:

    http://download.oracle.com/docs/cd/B19306_01/backup.102/b14193/toc.htm


    Werner
This discussion has been closed.