- Open Command Line window, type command
sqlplus sys/ as sysdba
- Type command
shutdown immediate;
- Backup and delete local datafile(s) from C:\oraclexe\app\oracle\oradata\XE
- Backup and delete local log file(s) from C:\oraclexe\app\oracle\fast_recovery_area\XE\ONLINELOG
- Copy all dbf file(s) and tmp file (data file of temporary tablespace) to C:\oraclexe\app\oracle\oradata\XE
- Copy redo log file to C:\log (create folder if not exists)
- Open Command Line window, type command
sqlplus sys/ as sysdba
- Type command
startup nomount;
- 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;
- Type command
recover database using backup controlfile until cancel;
- It asks the log file path, type the path of log file in C:\log
- Type command
shutdown immediate;
- Type command
startup mount; then type command show parameter undo;
- If undo_management is not 'MANUAL', then type statement
alter system set undo_management='MANUAL' scope=spfile;
- Type command
shutdown immediate; then type command startup mount;
- Type statement
alter database open resetlogs;
- Type statement
alter tablespace TEMP_TBS add tempfile 'C:\oraclexe\app\oracle\oradata\XE<actual tmp file name>.TMP' reuse;
- Type sql
select name from v$tablespace to get correct undo_tablespace, find it is UNDO_TBS
- Type statement
alter system set undo_tablespace='UNDO_TBS' scope=spfile;
- Type statement
alter system set undo_management='AUTO' scope=spfile;
- Type command
shutdown immediate; and startup mount;
- Type command
alter database open;
Last active
August 22, 2020 08:50
-
-
Save liuanyou/c179bcc8b009e7a9d0c372bb112a64ae to your computer and use it in GitHub Desktop.
Oracle XE - recover database from dbf files
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.