User managed Cold backup & Hot backup mixup restoration scenario

How to restore a database which has a valid backup files taken from two different backup methods.

Suppose a database has 4 datafiles and On monday i have taken a backup of 2 datafiles using user managed cold backup (for some reasons didnt take backup of remaining 2 datafiles). And on tuesday i have taken the remaining 2 datafiles using user managed Hot backup.

And on friday i have lost the 3 datafiles of the database unfortunately. How to restore and recover using the backups taken using 2 different methods.

Please see the below steps

>> Monday taking backup of 1st 2 datafiles using User managed Cold backup.

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

SQL>host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSTEM01.DBF D:\backup_10g\SYSTEM01.DBF
        1 file(s) copied.

SQL>host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSAUX01.DBF D:\backup_10g\SYSAUX01.DBF
        1 file(s) copied.

>> On Monday evening there is some transactions happening.

SQL> conn scott/tiger
Connected.
SQL>
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE

SQL> create table emp_1 as select * from emp;

Table created.

SQL>
SQL>
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
EMP_1                          TABLE

SQL> conn sys/database as sysdba
Connected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL>
SQL> /

System altered.

SQL> /

System altered.

SQL> select name from v$archived_log;

     NAME
  --------- --- --- --- ------------ ---------------- ----------- --- --- --- ----------
  D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_3_6WNDSTXM_.ARC

  D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_2_6WNDSS09_.ARC

  D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_4_6WNDT03Q_.ARC

  D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_5_6WNDT5KD_.ARC

SQL>

>> On Tuesday we are taking the remaining 2 datafiles backup using user managed Hot backup method.

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL> host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\USERS01.DBF D:\backup_10g\USERS01.DBF
        1 file(s) copied.

SQL> alter tablespace users end backup;

Tablespace altered.

SQL> alter tablespace undotbs1 begin backup;

Tablespace altered.

SQL> host copy D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\UNDOTBS01.DBF d:\backup_10g\undotbs01.dbf
        1 file(s) copied.

SQL> alter tablespace undotbs1 end backup;

Tablespace altered.

>> Again there is some transaction happening on the weekdays until friday.


SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
EMP_1                          TABLE

SQL> create table emp_2 as select * from emp;

Table created.

SQL> commit;

Commit complete.

SQL> conn sys/database as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select * from v$archived_log;


 NAME
------- --- --- --- --- - --------- --- --- --- ------------ ---------------- ----------- --- --- ---
  D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_3_6WNDSTXM_.ARC
  D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_2_6WNDSS09_.ARC
  D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_4_6WNDT03Q_.ARC
  D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_5_6WNDT5KD_.ARC
  D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_6_6WNFCP12_.ARC
  D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_7_6WNFCQ8W_.ARC


6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL>

Assume the issue happens on Friday !!! the datafiles has been corrupted. So please see how it is restored in the below steps.

SQL> shutdown immediate
Database closed.
Database dismounted.

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

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             192940932 bytes
Database Buffers          411041792 bytes
Redo Buffers                7135232 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSTEM01.DBF'


SQL> select name from v$datafile;

NAME
------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\USERS01.DBF

SQL> host copy D:\backup_10g\system01.dbf D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSTEM01.DBF
        1 file(s) copied.

SQL> host copy D:\backup_10g\sysaux01.dbf D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSAUX01.DBF
        1 file(s) copied.

SQL> host copy D:\backup_10g\USERS01.DBF D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\USERS01.DBF
        1 file(s) copied.

SQL> host copy D:\backup_10g\undotbs01.dbf D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\UNDOTBS01.DBF
        1 file(s) copied.

SQL> recover database;
ORA-00279: change 569282 generated at 05/11/2011 12:09:32 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_2_%U_.ARC
ORA-00280: change 569282 for thread 1 is in sequence #2


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 569585 generated at 05/11/2011 12:26:56 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_3_%U_.ARC
ORA-00280: change 569585 for thread 1 is in sequence #3
ORA-00278: log file 'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_2_6WNDSS09_.ARC' no longer needed for this recovery


ORA-00279: change 569593 generated at 05/11/2011 12:26:58 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_4_%U_.ARC
ORA-00280: change 569593 for thread 1 is in sequence #4
ORA-00278: log file 'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_3_6WNDSTXM_.ARC' no longer needed for this recovery


ORA-00279: change 569596 generated at 05/11/2011 12:27:03 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_5_%U_.ARC
ORA-00280: change 569596 for thread 1 is in sequence #5
ORA-00278: log file 'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_4_6WNDT03Q_.ARC' no longer needed for this recovery


ORA-00279: change 569598 generated at 05/11/2011 12:27:09 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_6_%U_.ARC
ORA-00280: change 569598 for thread 1 is in sequence #6
ORA-00278: log file 'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL10G\ARCHIVELOG\2011_05_11\O1_MF_1_5_6WNDT5KD_.ARC' no longer needed for this recovery


Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> select status,name from v$datafile;

STATUS  NAME
------- --------------------------------------------------------------
SYSTEM  D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSTEM01.DBF
ONLINE  D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\UNDOTBS01.DBF
ONLINE  D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\SYSAUX01.DBF
ONLINE  D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\USERS01.DBF

SQL>

Recovered completely without any issues.Check the below list of tables for verification.

SQL> conn scott/tiger
Connected.
SQL>
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
EMP_2                          TABLE
EMP_1                          TABLE

6 rows selected.

SQL>

Regards,

Navaneeth