It should never happen but sometimes it happens. You just lost your datafiles as well as your fast recovery area (probably because most of the time these areas are on the same disks despite the recommendations).
Normal restore operations with RMAN are quite easy and secure as far as you have backupsets for database, archivelogs, and spfile/controlfile:
Step 1 – restore the spfile and start the instance
Step 2 – restore the controlfile and mount the database
Step 3 – restore the database (meaning the datafiles)
Step 4 – recover the database as far as possible (by applying archivelogs)
Step 5 – open the database in (no)resetlogs
If you cannot go through step 2 because you don’t have any controlfile backup, you can’t go further with RMAN, that’s it. But there is another way to get a controlfile back to work.
Not having the spfile is annoying, but it’s just a subset of instance parameters, not really important stuff for your data. You can eventually recreate a pfile (you will probably convert it to spfile later) by picking up the non-default parameters in the alert_SID.log, these are located just after the last start of the instance. Or you can create a very basic pfile with very few parameters: at least the db_unique_name, and for this example I need compatible parameter, and a temporary fast recovery area for easy restore of the archivelogs.
vi /u01/oradata/DBTEST1/initDBTEST1.ora *.db_name='DBTEST1' control_files='/u01/oradata/DBTEST1/control01.dbf' compatible=12.1.0.2 db_recovery_file_dest='/u01/oradata/fast_recovery_area/' db_recovery_file_dest_size=10G
Fortunately you remember where you put the backup and you found this:
oracle@vmoratest1:/oracle/backup/ [DBTEST1] ls -lrt total 189828 -rw-r-----. 1 oracle oinstall 4333568 Aug 20 14:47 DB_34tb1lfb_1_1 -rw-r-----. 1 oracle oinstall 98304 Aug 20 14:47 DB_36tb1lfd_1_1 -rw-r-----. 1 oracle oinstall 54304768 Aug 20 14:47 DB_33tb1lfb_1_1 -rw-r-----. 1 oracle oinstall 121438208 Aug 20 14:47 DB_32tb1lfb_1_1 -rw-r-----. 1 oracle oinstall 92672 Aug 20 14:49 ARC_3atb1lj7_1_1 -rw-r-----. 1 oracle oinstall 1730560 Aug 20 14:49 ARC_39tb1lj7_1_1 -rw-r-----. 1 oracle oinstall 5758464 Aug 20 14:49 ARC_38tb1lj7_1_1 -rw-r-----. 1 oracle oinstall 6619648 Aug 20 14:49 ARC_37tb1lj7_1_1
First of all, start the instance.
sqlplus / as sysdba SQL> startup nomount pfile='/u01/oradata/DBTEST1/initDBTEST1.ora';
After trying to restore the controlfile from backuppieces inside the backup directory, you found that no backup has a controlfile:
rman target / RMAN> restore controlfile from '/oracle/backup/DB_36tb1lfb_1_1'; Starting restore at 20-AUG-2018 15:53:08 using channel ORA_DISK_1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/20/2018 15:53:08 RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece RMAN> restore controlfile from '/oracle/backup/DB_32tb1lfb_1_1'; Starting restore at 20-AUG-2018 15:53:21 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/20/2018 15:53:21 ORA-19697: standby control file not found in backup set RMAN> restore controlfile from '/oracle/backup/ARC_3atb1lj7_1_1'; Starting restore at 20-AUG-2018 15:53:56 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/20/2018 15:53:56 ORA-19870: error while restoring backup piece /oracle/backup/ARC_3atb1lj7_1_1 ORA-19626: backup set type is archived log - can not be processed by this conversation ...
Having an instance started is always better than nothing. And through this instance you can have access to many things without actually having a real database. For example, you can use the dbms_backup_restore package: this package is able to restore datafiles without having any controlfile. Very useful for us now. You can easily restore a datafile from a backuppiece but you have to provide the datafile number. A few lines of PL/SQL code can help you to restore all the datafiles from all the available backuppieces.
cd /u01/oradata/DBTEST1/
vi resto.sql
set serveroutput on
declare
v_dev varchar2(30) ;
v_rest_ok boolean;
v_df_num number := 1;
v_df_max number := 30;
v_bck_piece varchar2(256) := '&1';
v_rest_folder varchar2(226) := '/u01/oradata/DBTEST1/';
v_rest_df varchar2(256);
begin
v_dev := dbms_backup_restore.deviceallocate;
while v_df_num <= v_df_max loop
v_rest_df := v_rest_folder||'DF_'||lpad(v_df_num,4,'0');
dbms_backup_restore.restoreSetDatafile;
dbms_backup_restore.restoreDataFileTo(dfnumber=>v_df_num,toname=>v_rest_df);
BEGIN
dbms_backup_restore.restoreBackupPiece(done=>v_rest_ok,handle=>v_bck_piece);
EXCEPTION
WHEN OTHERS
THEN
v_rest_ok := FALSE;
-- dbms_output.put_line('Datafile '||v_df_num||' is not in this piece');
END;
if v_rest_ok THEN
dbms_output.put_line('Datafile '||v_df_num||' is restored : '||v_rest_df);
end if;
v_df_num := v_df_num + 1;
end loop;
dbms_backup_restore.deviceDeallocate;
end;
/
exit;
Let’s iterate this anonymous PL/SQL block for each backuppiece in your backup folder:
for a in `find /oracle/backup/ -name DB*`; do sqlplus -s / as sysdba @resto $a; done; old 6: v_bck_piece varchar2(256) := '&1'; new 6: v_bck_piece varchar2(256) := '/oracle/backup/DB_32tb1lfb_1_1'; Datafile 1 is restored : /u01/oradata/DBTEST1/DF_0001.dbf Datafile 4 is restored : /u01/oradata/DBTEST1/DF_0004.dbf Datafile 9 is restored : /u01/oradata/DBTEST1/DF_0009.dbf PL/SQL procedure successfully completed. old 6: v_bck_piece varchar2(256) := '&1'; new 6: v_bck_piece varchar2(256) := '/oracle/backup/DB_33tb1lfb_1_1'; Datafile 2 is restored : /u01/oradata/DBTEST1/DF_0002.dbf Datafile 7 is restored : /u01/oradata/DBTEST1/DF_0007.dbf Datafile 8 is restored : /u01/oradata/DBTEST1/DF_0008.dbf PL/SQL procedure successfully completed. old 6: v_bck_piece varchar2(256) := '&1'; new 6: v_bck_piece varchar2(256) := '/oracle/backup/DB_36tb1lfd_1_1'; PL/SQL procedure successfully completed. old 6: v_bck_piece varchar2(256) := '&1'; new 6: v_bck_piece varchar2(256) := '/oracle/backup/DB_34tb1lfb_1_1'; Datafile 3 is restored : /u01/oradata/DBTEST1/DF_0003.dbf Datafile 5 is restored : /u01/oradata/DBTEST1/DF_0005.dbf Datafile 6 is restored : /u01/oradata/DBTEST1/DF_0006.dbf PL/SQL procedure successfully completed.
Well done! 9 datafiles were restored. Now look at your folder, you’ll find the 9 datafiles, actually all your database if your backup is reliable:
ls -lrt /u01/oradata/DBTEST1/ total 2017372 -rw-r--r--. 1 oracle oinstall 1035 Aug 20 23:05 resto.sql -rw-r--r--. 1 oracle oinstall 91 Aug 20 23:12 initDBTEST1.ora -rw-r-----. 1 oracle oinstall 734011392 Aug 20 23:15 DF_0001.dbf -rw-r-----. 1 oracle oinstall 5251072 Aug 20 23:15 DF_0004.dbf -rw-r-----. 1 oracle oinstall 52436992 Aug 20 23:15 DF_0009.dbf -rw-r-----. 1 oracle oinstall 576724992 Aug 20 23:15 DF_0002.dbf -rw-r-----. 1 oracle oinstall 52436992 Aug 20 23:15 DF_0007.dbf -rw-r-----. 1 oracle oinstall 52436992 Aug 20 23:15 DF_0008.dbf -rw-r-----. 1 oracle oinstall 487596032 Aug 20 23:15 DF_0003.dbf -rw-r-----. 1 oracle oinstall 52436992 Aug 20 23:15 DF_0005.dbf -rw-r-----. 1 oracle oinstall 52436992 Aug 20 23:15 DF_0006.dbf
You can now manually create the controlfile with these datafiles (you just have to remember the characterset of your database):
sqlplus / as sysdba
CREATE CONTROLFILE REUSE DATABASE "DBTEST1" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2073
LOGFILE
GROUP 1 '/u01/oradata/DBTEST1/redo01.rdo' SIZE 100M BLOCKSIZE 512,
GROUP 2 '/u01/oradata/DBTEST1/redo02.rdo' SIZE 100M BLOCKSIZE 512,
GROUP 3 '/u01/oradata/DBTEST1/redo03.rdo' SIZE 100M BLOCKSIZE 512
DATAFILE
'/u01/oradata/DBTEST1/DF_0001.dbf',
'/u01/oradata/DBTEST1/DF_0002.dbf',
'/u01/oradata/DBTEST1/DF_0003.dbf',
'/u01/oradata/DBTEST1/DF_0004.dbf',
'/u01/oradata/DBTEST1/DF_0005.dbf',
'/u01/oradata/DBTEST1/DF_0006.dbf',
'/u01/oradata/DBTEST1/DF_0007.dbf',
'/u01/oradata/DBTEST1/DF_0008.dbf',
'/u01/oradata/DBTEST1/DF_0009.dbf'
CHARACTER SET AL32UTF8 ;
Control file created.
ls -lrt /u01/oradata/DBTEST1/ total 2029804 -rw-r--r--. 1 oracle oinstall 1035 Aug 20 23:05 resto.sql -rw-r--r--. 1 oracle oinstall 91 Aug 20 23:12 initDBTEST1.ora -rw-r-----. 1 oracle oinstall 734011392 Aug 20 23:15 DF_0001.dbf -rw-r-----. 1 oracle oinstall 5251072 Aug 20 23:15 DF_0004.dbf -rw-r-----. 1 oracle oinstall 52436992 Aug 20 23:15 DF_0009.dbf -rw-r-----. 1 oracle oinstall 576724992 Aug 20 23:15 DF_0002.dbf -rw-r-----. 1 oracle oinstall 52436992 Aug 20 23:15 DF_0007.dbf -rw-r-----. 1 oracle oinstall 52436992 Aug 20 23:15 DF_0008.dbf -rw-r-----. 1 oracle oinstall 487596032 Aug 20 23:15 DF_0003.dbf -rw-r-----. 1 oracle oinstall 52436992 Aug 20 23:15 DF_0005.dbf -rw-r-----. 1 oracle oinstall 52436992 Aug 20 23:15 DF_0006.dbf -rw-r-----. 1 oracle oinstall 12730368 Aug 20 23:24 control01.dbf
What a relief to see pfile, controlfile and datafiles all together again!
Work is not yet finished because the datafiles are probably inconsistent. There is no need to mount the database as it’s already mounted, and it’s now possible to catalog all your backuppieces for some kind of RMAN catalog restore:
rman target / catalog start with '/oracle/backup/'; using target database control file instead of recovery catalog searching for all files that match the pattern /oracle/backup/ List of Files Unknown to the Database ===================================== File Name: /oracle/backup/DB_32tb1lfb_1_1 File Name: /oracle/backup/ARC_37tb1lj7_1_1 File Name: /oracle/backup/ARC_39tb1lj7_1_1 File Name: /oracle/backup/DB_33tb1lfb_1_1 File Name: /oracle/backup/DB_36tb1lfd_1_1 File Name: /oracle/backup/ARC_3atb1lj7_1_1 File Name: /oracle/backup/DB_34tb1lfb_1_1 File Name: /oracle/backup/ARC_38tb1lj7_1_1 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /oracle/backup/DB_32tb1lfb_1_1 File Name: /oracle/backup/ARC_37tb1lj7_1_1 File Name: /oracle/backup/ARC_39tb1lj7_1_1 File Name: /oracle/backup/DB_33tb1lfb_1_1 File Name: /oracle/backup/DB_36tb1lfd_1_1 File Name: /oracle/backup/ARC_3atb1lj7_1_1 File Name: /oracle/backup/DB_34tb1lfb_1_1 File Name: /oracle/backup/ARC_38tb1lj7_1_1
You now need to restore the archivelogs:
RMAN> restore archivelog all; Starting restore at 20-AUG-2018 23:43:47 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=23 device type=DISK channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=44 channel ORA_DISK_1: reading from backup piece /oracle/backup/ARC_39tb1lj7_1_1 channel ORA_DISK_1: piece handle=/oracle/backup/ARC_39tb1lj7_1_1 tag=TAG20180820T144911 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=45 channel ORA_DISK_1: reading from backup piece /oracle/backup/ARC_38tb1lj7_1_1 channel ORA_DISK_1: piece handle=/oracle/backup/ARC_38tb1lj7_1_1 tag=TAG20180820T144911 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=46 channel ORA_DISK_1: reading from backup piece /oracle/backup/ARC_37tb1lj7_1_1 channel ORA_DISK_1: piece handle=/oracle/backup/ARC_37tb1lj7_1_1 tag=TAG20180820T144911 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=47 channel ORA_DISK_1: reading from backup piece /oracle/backup/ARC_3atb1lj7_1_1 channel ORA_DISK_1: piece handle=/oracle/backup/ARC_3atb1lj7_1_1 tag=TAG20180820T144911 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 20-AUG-2018 23:43:52
Now it’s probably possible to recover the database:
sqlplus / as sysdba
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1386561 generated at 08/20/2018 14:47:07 needed for thread 1
ORA-00289: suggestion :
/u01/oradata/fast_recovery_area/DBTEST1/archivelog/2018_08_21/o1_mf_1_47_fqplx2l
n_.arc
ORA-00280: change 1386561 for thread 1 is in sequence #47
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 1386635 generated at 08/20/2018 14:49:10 needed for thread 1
ORA-00289: suggestion :
/u01/oradata/fast_recovery_area/DBTEST1/archivelog/2018_08_21/o1_mf_1_48_%u_.arc
ORA-00280: change 1386635 for thread 1 is in sequence #48
ORA-00278: log file
'/u01/oradata/fast_recovery_area/DBTEST1/archivelog/2018_08_21/o1_mf_1_47_fqplx2
ln_.arc' no longer needed for this recovery
ORA-00308: cannot open archived log
'/u01/oradata/fast_recovery_area/DBTEST1/archivelog/2018_08_21/o1_mf_1_48_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Last error is normal because Oracle didn’t know the sequence 48 never existed.
Now all the archivelogs are applied, fingers crossed for the last operation that is supposed to bring back the database to life:
SQL> alter database open resetlogs; Database altered. SQL> select instance_name, status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ DBTEST1 OPEN SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /u01/oradata/DBTEST1/DF_0001.dbf /u01/oradata/DBTEST1/DF_0002.dbf /u01/oradata/DBTEST1/DF_0003.dbf /u01/oradata/DBTEST1/DF_0004.dbf /u01/oradata/DBTEST1/DF_0005.dbf /u01/oradata/DBTEST1/DF_0007.dbf /u01/oradata/DBTEST1/DF_0006.dbf /u01/oradata/DBTEST1/DF_0009.dbf /u01/oradata/DBTEST1/DF_0008.dbf 9 rows selected.
Yes everything is OK!!! Apart from generic name for your datafiles, a single controlfile, no spfile, default-configured redologs and probably no temporary tablespace. But the database is up and running, and you feel like a hero. Or you just manage to keep your job 😉
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/JDU_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
Vimal Rathinasamy
27.12.2023Hi Jerome,
This is an excellent article. Keep it up and Thanks for sharing the same in such an elaborate and detailed way!!