Search This Blog

Showing posts with label RMAN. Show all posts
Showing posts with label RMAN. Show all posts

How to restore database when controlfile backup missing ?

when customer have all backup pieces of database except controlfile backup which is mandatory to mount the database and start restore.


Missing controlfile backup

1. See the current database structure

RMAN> report schema; 
using target database control file instead of recovery catalog
Report of database schema

List of Datafiles
==================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 510 SYSTEM *** +DATA/oradb/datafile/system.257.775126603
2 595 UNDOTBS1 *** +DATA/oradb/datafile/undotbs1.256.775126561
3 250 SYSAUX *** +DATA/oradb/datafile/sysaux.258.775126637
4 28 USERS *** +DATA/oradb/datafile/users.259.775126653
5 50 USERS *** +DATA/oradb/datafile/users.262.776000421

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 29 TEMP 32767 /u01/app/oracle/oradata/oradb/oradb/datafile/o1_mf_temp_7lqq1qko_.tmp

RMAN> exit


Recovery Manager complete.


2. Create a dummy instance/ can use existing database to extract datafile 1 from backup piece and restore datafile 1 from backup piece

SQL> DECLARE
  devtype varchar2(256);
  done boolean;
  BEGIN
  devtype := dbms_backup_restore.DeviceAllocate(type=>null, ident=>'d1');
  dbms_backup_restore.RestoreSetDatafile;
  dbms_backup_restore.RestoreDatafileTo(dfnumber => 1,toname => '/u03/datafile1.dbf'); 
  dbms_backup_restore.RestoreBackupPiece(done => done,handle =>'/u03/backup/2cn5blrn_1_1', params => null);
  dbms_backup_restore.DeviceDeallocate;
 END;
/


3. Create controlfile with datafile 1 

SQL>!cat /u03/1.ctl
CREATE CONTROLFILE REUSE DATABASE "oradb" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_1_7lqq1m62_.log' SIZE 50M,
GROUP 2 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_2_7lqq1myr_.log' SIZE 50M,
GROUP 3 '/d01/app/oracle/oradata/oradb/oradb/onlinelog/o1_mf_3_7lqq1nr0_.log' SIZE 50M
DATAFILE '/u03/datafile1.dbf'
CHARACTER SET WE8ISO8859P1;

SQL> @/u03/1.ctl 

Control file created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/datafile1.dbf

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Launch your Online Business with a $5.99 .COM!