Infrastructure at your Service

Jérôme Dubar

Restoring a database without having any controlfile backup

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 ;-)

 

Leave a Reply

Jérôme Dubar
Jérôme Dubar