Skip to content

Instantly share code, notes, and snippets.

@liuanyou
Last active August 22, 2020 08:50
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save liuanyou/c179bcc8b009e7a9d0c372bb112a64ae to your computer and use it in GitHub Desktop.
Save liuanyou/c179bcc8b009e7a9d0c372bb112a64ae to your computer and use it in GitHub Desktop.
Oracle XE - recover database from dbf files
  1. Open Command Line window, type command

    sqlplus sys/ as sysdba

  2. Type command

    shutdown immediate;

  3. Backup and delete local datafile(s) from C:\oraclexe\app\oracle\oradata\XE
  4. Backup and delete local log file(s) from C:\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG
  5. Copy all dbf file(s) and tmp file (data file of temporary tablespace) to C:\oraclexe\app\oracle\oradata\XE
  6. Copy redo log file to C:\log (create folder if not exists)
  7. Open Command Line window, type command

    sqlplus sys/ as sysdba

  8. Type command

    startup nomount;

  9. Use below script to create control file
    CREATE CONTROLFILE
      SET DATABASE XE
      LOGFILE
        GROUP 1 'C:\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\<new log file name 1>.LOG' SIZE 50M,
        GROUP 2 'C:\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG\<new log file name 2>.LOG' SIZE 50M
      RESETLOGS
      DATAFILE
        'C:\oraclexe\app\oracle\oradata\XE\<actual dbf file name>.DBF',
        'C:\oraclexe\app\oracle\oradata\XE\<actual dbf file name>.DBF',
        'C:\oraclexe\app\oracle\oradata\XE\<actual dbf file name>.DBF',
        'C:\oraclexe\app\oracle\oradata\XE\<actual dbf file name>.DBF',
        'C:\oraclexe\app\oracle\oradata\XE\<actual dbf file name>.DBF'
      MAXLOGFILES 50
      MAXLOGMEMBERS 2
      MAXLOGHISTORY 400
      MAXDATAFILES 200
      MAXINSTANCES 6
      ARCHIVELOG
      CHARACTER SET AL32UTF8;
      
    
  10. Type command

    recover database using backup controlfile until cancel;

  11. It asks the log file path, type the path of log file in C:\log
  12. Type command

    shutdown immediate;

  13. Type command

    startup mount; then type command show parameter undo;

  14. If undo_management is not 'MANUAL', then type statement

    alter system set undo_management='MANUAL' scope=spfile;

  15. Type command

    shutdown immediate; then type command startup mount;

  16. Type statement

    alter database open resetlogs;

  17. Type statement

    alter tablespace TEMP_TBS add tempfile 'C:\oraclexe\app\oracle\oradata\XE<actual tmp file name>.TMP' reuse;

  18. Type sql

    select name from v$tablespace to get correct undo_tablespace, find it is UNDO_TBS

  19. Type statement

    alter system set undo_tablespace='UNDO_TBS' scope=spfile;

  20. Type statement

    alter system set undo_management='AUTO' scope=spfile;

  21. Type command

    shutdown immediate; and startup mount;

  22. Type command

    alter database open;

@s3t14d4rm4
Copy link

Sorry for bothering, but do you happen to know whether the above works for Oracle XE 18c or not?
There is something wrong with my control files, so that they cannot be read during startup.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment