Restore database without backup with original database files

Today i come across one of the OTN forums question regarding "How to restore database without backup with original database files"

As mentioned in the question is:

OS windows

Oracle Ver: 10.1.0.5.0

by mistake user deleted the registry and uninstall the oracle software from OS. but he took the backup of original database files (redo.control.datafile)

the database was up and running while he deleted all registry and uninstall oracle software via Oracle Universal Installer.

Not in the particular case but also we can re-build database if we have intact original database files.

the following steps will be helpful

1.  Install Oracle software 

2. Install any patchset or cpu ( same as before uninstalled)

3. create oracle services via ORADIM ( SID should be same as original database SID)

4. create password files

5. create pfile ( with the required parameters) You can create one dummy database and take the pfile from that database for reference.

6. create the needful directories ( like oradata/admin/bdump/udump/cdump) etc

7. restore original database files from backup to oradata folder 

NOTE: It is better if file structure should be same as before uninstall otherwise we need to change the location of controlfiles then relocate redolog and datafiles to the new locations

8. connect to the idle instance with created pfile 

9. start database in steps ( to that we can verify and change locations of the files (if required)

10. startup nomount

11. alter database mount

12. alter database open

if you can open successful then write me email at askdbataj

What is HOT / COLD backup?

There is two types of backup we can take for Oracle Database.

1. COLD/OFFLINE/CONSISTENT backup

What is cold backup and why we say "cold" backup?

When database is DOWN, no activity running on database, no one accessing the database. that time taken database backup called "COLD BACKUP". We can also say "OFFLINE" database backup.

In short:

COLD backup equal to OFFINE backup

COLD backup equal to CONSISTENT backup

For COLD/OFFLINE/COSISTENT database backup we must need to SHUTDOWN Oracle Database with the following option.

1. SQL>shutdown normal

2. SQL>shutdown immediate

3. SQL>shutdown transactional;

4. SQL>shutdown abort;

5. CMD>net stop OracleService

For Example of cold backup on any OS platforms.

1. conn with sysdba user.

2. shutdown oracle database

3. copy init.ora, all datafiles, all redologs files, all controlfile to backup location

4. startup oracle database

NOTE: Database doesn't require ARCHIVELOG mode for COLD backup.

2. What is HOT/ONLINE/INCONSISTENT backup?

When database is open, user accessing the database that time we taken backup is called "HOT,ONLINE, inconsistent" backup.

NOTE: Database must require ARCHIVELOG mode for HOT backup.

For HOT backup we have two options

1. RMAN Recovery Manager (Server Managed Backup)

2. User Managed Backup (User Managed backup)

How to Recover Dropped Tablespace

Oracle Ver: 10gr1/Win 2003
Backup Method: User Managed (hot Bkp)

SQL> conn sys/oracle as sysdbaConnected.

SQL> alter database begin backup;
Database altered.

SQL> ---copy all datafiles to bkp location

SQL> alter database end backup;
Database altered.

SQL> alter system switch logfile;
System altered.

SQL> alter database backup controlfile to 'd:\bkp\control01.ctl';
Database altered.

SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.
SQL> ---oops by mistake drop production tablespace

SQL> conn scott/tiger
Connected.

SQL> select count(*) from a;
select count(*) from a
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn sys/oracle as sysdba
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> ---delete all datafiles & controlfile.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 789100 bytes
Variable Size 94631316 bytes
Database Buffers 197132288 bytes
Redo Buffers 1048576 bytes

SQL> --restore controlfile from bkp

SQL> alter database mount;
Database altered.

SQL> --restore all datafiles from bkp

SQL> --now perform time based recovery.

SQL> --check ALERT.LOG file for exact time when tablespace was dropped.

SQL> --perform TIME BASED recovery just before dropped tablespace.

SQL> recover database using backup controlfile UNTIL TIME '2008-03-25 08:47:00';
ORA-00279: change 461791 generated at 03/25/2008 08:43:31 needed for thread 1
ORA-00289: suggestion :D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00002_0650232706.001
ORA-00280: change 461791 for thread 1 is in sequence #2
Specify log: {=suggested filename AUTO CANCEL}
auto

ORA-00279: change 461827 generated at 03/25/2008 08:44:45 needed for thread 1
ORA-00289: suggestion :D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00003_0650232706.001
ORA-00280: change 461827 for thread 1 is in sequence #3
ORA-00278: log file'D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00002_0650232706.001' no longer needed for this recovery
ORA-00308: cannot open archived log'D:\ORACLE\PRODUCT\10.1.0\DB_1\RDBMS\ARC00003_0650232706.001'
ORA-27041: unable to open fileOSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> --Open database with RESETLOGS

SQL> alter database open resetlogs;
Database altered.

SQL> --now check dropped tablespace is exist

SQL> select name from v$datafile where name like '%TEST%';
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBF

SQL> conn scott/tiger
Connected.

SQL> select count(*) from a;
COUNT(*)
----------
10000

=======================================================

Oracle 10gr1/Win2003
Backup Method: RMAN

C:\>rman target sys
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1178009698)
RMAN> run
2> {
3> backup database plus archivelog;
4> backup current controlfile;
5> }
Starting backup at 25-MAR-08
current log archived using target database controlfile instead of recovery catalog

ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 25-MAR-08
channel ORA_DISK_1: finished piece 1 at 25-MAR-08
piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0BJC4UJ5_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupsetincluding current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 25-MAR-08
channel ORA_DISK_1: finished piece 1 at 25-MAR-08
piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0CJC4UJK_1_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-MAR-08

SQL> conn sys/oracle as sysdba

Connected.

SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.

SQL> ---oops dropped production tablespace

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

C:\>REM delete all controlfile and datafiles
C:\>rman target sys/oracle
Recovery Manager: Release 10.1.0.5.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 293601280 bytes
Fixed Size 789100 bytes

Variable Size 94631316 bytes

Database Buffers 197132288 bytes

Redo Buffers 1048576 bytes

NOTE: restore controlfile from backupset.

RMAN> restore controlfile from 'D:\oracle\product\10.1.0\Db_1\database\0CJC4UJK_1_1';
Starting restore at 25-MAR-08

allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISK
channel ORA_DISK_1: restoring controlfile

channel ORA_DISK_1: restore complete

output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL

output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL

output filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL

Finished restore at 25-MAR-08

RMAN> alter database mount;
database mounted

released channel: ORA_DISK_1
RMAN> run

2> {

3> set UNTIL TIME "to_date('2008-03-25 09:19:44','YYYY-MM-DD HH24:MI:SS')";

4> restore database;

5> recover database;

6> }
executing command: SET until clause
Starting restore at 25-MAR-08

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=160 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF

restoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF

restoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF

restoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF

restoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST01.DBF

channel ORA_DISK_1: restored backup piece 1

piece handle=D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\0BJC4UJ5_1_1 tag=TAG20080325T091205

channel ORA_DISK_1: restore complete

Finished restore at 25-MAR-08
Starting recover at 25-MAR-08using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG

archive log thread 1 sequence 3 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG

archive log filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG thread=1 sequence=2

archive log filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG thread=1 sequence=3

media recovery complete

Finished recover at 25-MAR-08

RMAN> alter database open resetlogs;

database opened

NOTE:

1. I am using Controlfile Instead of Recovery catalog for RMAN repository

2. Don't use AUTOBACKUP controlfile option becuase we need backup controlfile for incomplete recovery not current controlfile.

Backup And Recovery Scenario.

Backup Method
Type
Version Available
Requirements

Recovery Manager (RMAN)

Physical

Oracle version 8.0 and higher

Third-party media manager (only if backing up to tape)

Operating System

Physical

All versions

Operating system backup utility (for example, UNIX dd command)

Export

Logical

All versions

N/A