Sunday, June 13, 2010

How to recover tablespace from previous backup?

Recovery Scenerio is:

A student has dropped a user/tablepspace. Fortunately he has a recent level 0 (full) backup. Now, the task is to restore that (dropped) tablespace. We created the same scenerio below to restore the user/tablespace.


Section-1: Let's gather the the current archived log information.

col name format a50
set linesize 200
set pagesize 200

select name,
        sequence#,
        first_change# as First_SCN_Change,
        to_char(first_time,'HH24:Mi:SS') as First_Time,
        next_change# Next_SCN_Change,
        to_char(next_time,'HH24:Mi:SS') as Next_Time
from
    v$archived_log;



As you can see current
    Last Sequene# --> 28
    Last SCN         --> 478859


Step -2:  Let's create a new tablespace.

CREATE TABLESPACE testing DATAFILE
'/u14/oradata/MarsDB/testing01.dbf' SIZE 20M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;




Lets verify new tbs and datafile associated are in database.





Section-3: Lets make few log switches and record the latest sequence# and SCN.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> select
            name,
            sequence#,
            first_change# as First_SCN_Change,
            to_char(first_time,'HH24:Mi:SS') as First_Time,
            next_change# Next_SCN_Change,
            to_char(next_time,'HH24:Mi:SS') as Next_Time
        from
            v$archived_log;

  
As you can see, the new changes after the tbs creation is at:
    Last Sequene# --> 38
    Last SCN         --> 481391



Section-4: Let's create a new user and populate it with few tables from scott.

SQL> CREATE USER testing IDENTIFIED BY "abc123"
 DEFAULT TABLESPACE "TESTING"
 TEMPORARY TABLESPACE "TEMP"
 PROFILE DEFAULT
 QUOTA UNLIMITED ON "TESTING";

 SQL> GRANT "CONNECT" TO testing;
 SQL> GRANT "RESOURCE" TO testing;
 SQL> ALTER USER testing DEFAULT ROLE all; 

User created.
Grant succeeded.
Grant succeeded.
User altered.

SQL> create table testing.emp1 as select * from scott.emp;
SQL> create table testing.emp2 as select * from scott.emp;
SQL> create table testing.emp3 as select * from scott.emp;
SQL> create table testing.emp4 as select * from scott.emp;
SQL> create table testing.emp5 as select * from scott.emp;
SQL> create table testing.emp6 as select * from scott.emp;
SQL> create table testing.emp7 as select * from scott.emp;
SQL> create table testing.emp8 as select * from scott.emp;
SQL> create table testing.emp9 as select * from scott.emp;

Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.



Section-5: Lets make few log switches again and verify the latest sequence# and SCN.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> select
            name,
            sequence#,
            first_change# as First_SCN_Change,
            to_char(first_time,'HH24:Mi:SS') as First_Time,
            next_change# Next_SCN_Change,
            to_char(next_time,'HH24:Mi:SS') as Next_Time
        from
            v$archived_log;



As you can see, after we make the changes above, new changes are at:
    Last Sequene# --> 48
    Last SCN         --> 481807


Section-6: Lets take a hot full backup of the database using RMAN.

export ORACLE_SID=MarsDB
rman target /



RMAN> backup database plus archivelog;


Starting backup at 13-JUN-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=149 devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=721595376
input archive log thread=1 sequence=3 recid=2 stamp=721596450
input archive log thread=1 sequence=4 recid=3 stamp=721596451
input archive log thread=1 sequence=5 recid=4 stamp=721596459
input archive log thread=1 sequence=6 recid=5 stamp=721596459
input archive log thread=1 sequence=7 recid=6 stamp=721596462
input archive log thread=1 sequence=8 recid=7 stamp=721596462
input archive log thread=1 sequence=9 recid=8 stamp=721596462
input archive log thread=1 sequence=10 recid=9 stamp=721596465
input archive log thread=1 sequence=11 recid=10 stamp=721596465
input archive log thread=1 sequence=12 recid=11 stamp=721596471
input archive log thread=1 sequence=13 recid=12 stamp=721596471
input archive log thread=1 sequence=14 recid=13 stamp=721596477
input archive log thread=1 sequence=15 recid=14 stamp=721596477
input archive log thread=1 sequence=16 recid=15 stamp=721596478
input archive log thread=1 sequence=17 recid=16 stamp=721596478
input archive log thread=1 sequence=18 recid=17 stamp=721596481
input archive log thread=1 sequence=19 recid=18 stamp=721596481
input archive log thread=1 sequence=20 recid=19 stamp=721597318
input archive log thread=1 sequence=21 recid=20 stamp=721597320
input archive log thread=1 sequence=22 recid=21 stamp=721597326
input archive log thread=1 sequence=23 recid=22 stamp=721597326
input archive log thread=1 sequence=24 recid=23 stamp=721597872
input archive log thread=1 sequence=25 recid=24 stamp=721597876
input archive log thread=1 sequence=26 recid=25 stamp=721597882
input archive log thread=1 sequence=27 recid=26 stamp=721598275
input archive log thread=1 sequence=28 recid=27 stamp=721598329
input archive log thread=1 sequence=29 recid=28 stamp=721599464
input archive log thread=1 sequence=30 recid=29 stamp=721599466
input archive log thread=1 sequence=31 recid=30 stamp=721599475
input archive log thread=1 sequence=32 recid=31 stamp=721599475
input archive log thread=1 sequence=33 recid=32 stamp=721599480
input archive log thread=1 sequence=34 recid=33 stamp=721599607
input archive log thread=1 sequence=35 recid=34 stamp=721599611
input archive log thread=1 sequence=36 recid=35 stamp=721599616
input archive log thread=1 sequence=37 recid=36 stamp=721599617
input archive log thread=1 sequence=38 recid=37 stamp=721599628
input archive log thread=1 sequence=39 recid=38 stamp=721599820
input archive log thread=1 sequence=40 recid=39 stamp=721599829
input archive log thread=1 sequence=41 recid=40 stamp=721599835
input archive log thread=1 sequence=42 recid=41 stamp=721599841
input archive log thread=1 sequence=43 recid=42 stamp=721599841
input archive log thread=1 sequence=44 recid=43 stamp=721599844
input archive log thread=1 sequence=45 recid=44 stamp=721599844
input archive log thread=1 sequence=46 recid=45 stamp=721599850
input archive log thread=1 sequence=47 recid=46 stamp=721599851
input archive log thread=1 sequence=48 recid=47 stamp=721599856
input archive log thread=1 sequence=49 recid=48 stamp=721599859
input archive log thread=1 sequence=50 recid=49 stamp=721600004
channel ORA_DISK_1: starting piece 1 at 13-JUN-10
channel ORA_DISK_1: finished piece 1 at 13-JUN-10
piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600005_07lg5fg5_1_1.bak tag=TAG20100613T202645 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 13-JUN-10

Starting backup at 13-JUN-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u14/oradata/MarsDB/system01.dbf
input datafile fno=00003 name=/u14/oradata/MarsDB/sysaux01.dbf
input datafile fno=00002 name=/u14/oradata/MarsDB/undotbs01.dbf
input datafile fno=00005 name=/u14/oradata/MarsDB/testing01.dbf
input datafile fno=00004 name=/u14/oradata/MarsDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 13-JUN-10
channel ORA_DISK_1: finished piece 1 at 13-JUN-10
piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600014_08lg5fge_1_1.bak tag=TAG20100613T202654 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 13-JUN-10

Starting backup at 13-JUN-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=51 recid=50 stamp=721600070
channel ORA_DISK_1: starting piece 1 at 13-JUN-10
channel ORA_DISK_1: finished piece 1 at 13-JUN-10
piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600070_09lg5fi6_1_1.bak tag=TAG20100613T202750 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 13-JUN-10

Starting Control File and SPFILE Autobackup at 13-JUN-10
piece handle=/u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-02 comment=NONE
Finished Control File and SPFILE Autobackup at 13-JUN-10





Step:7 Now lets check the backup information.


RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    6.80M      DISK        00:00:00     13-JUN-10
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20100613T201730
        Piece Name: /u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-00
  Control File Included: Ckp SCN: 480717       Ckp time: 13-JUN-10
  SPFILE Included: Modification time: 13-JUN-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    7.08M      DISK        00:00:00     13-JUN-10
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20100613T201913
        Piece Name: /u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-01
  Control File Included: Ckp SCN: 481338       Ckp time: 13-JUN-10
  SPFILE Included: Modification time: 13-JUN-10

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       11.73M     DISK        00:00:06     13-JUN-10
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20100613T202645
        Piece Name: /u99/MarsDB/backup/full_MARSDB_20100613_721600005_07lg5fg5_1_1.bak

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    2       467451     13-JUN-10 473922     13-JUN-10
  1    3       473922     13-JUN-10 476883     13-JUN-10
  1    4       476883     13-JUN-10 476888     13-JUN-10
  1    5       476888     13-JUN-10 476892     13-JUN-10
  1    6       476892     13-JUN-10 476894     13-JUN-10
  1    7       476894     13-JUN-10 476896     13-JUN-10
  1    8       476896     13-JUN-10 476898     13-JUN-10
  1    9       476898     13-JUN-10 476900     13-JUN-10
  1    10      476900     13-JUN-10 476902     13-JUN-10
  1    11      476902     13-JUN-10 476904     13-JUN-10
  1    12      476904     13-JUN-10 476907     13-JUN-10
  1    13      476907     13-JUN-10 476909     13-JUN-10
  1    14      476909     13-JUN-10 476912     13-JUN-10
  1    15      476912     13-JUN-10 476914     13-JUN-10
  1    16      476914     13-JUN-10 476919     13-JUN-10
  1    17      476919     13-JUN-10 476922     13-JUN-10
  1    18      476922     13-JUN-10 476926     13-JUN-10
  1    19      476926     13-JUN-10 476932     13-JUN-10
  1    20      476932     13-JUN-10 477635     13-JUN-10
  1    21      477635     13-JUN-10 477637     13-JUN-10
  1    22      477637     13-JUN-10 477641     13-JUN-10
  1    23      477641     13-JUN-10 477643     13-JUN-10
  1    24      477643     13-JUN-10 478548     13-JUN-10
  1    25      478548     13-JUN-10 478551     13-JUN-10
  1    26      478551     13-JUN-10 478554     13-JUN-10
  1    27      478554     13-JUN-10 478819     13-JUN-10
  1    28      478819     13-JUN-10 478859     13-JUN-10
  1    29      478859     13-JUN-10 480732     13-JUN-10
  1    30      480732     13-JUN-10 480734     13-JUN-10
  1    31      480734     13-JUN-10 480738     13-JUN-10
  1    32      480738     13-JUN-10 480740     13-JUN-10
  1    33      480740     13-JUN-10 480750     13-JUN-10
  1    34      480750     13-JUN-10 481369     13-JUN-10
  1    35      481369     13-JUN-10 481373     13-JUN-10
  1    36      481373     13-JUN-10 481384     13-JUN-10
  1    37      481384     13-JUN-10 481386     13-JUN-10
  1    38      481386     13-JUN-10 481391     13-JUN-10
  1    39      481391     13-JUN-10 481733     13-JUN-10
  1    40      481733     13-JUN-10 481761     13-JUN-10
  1    41      481761     13-JUN-10 481764     13-JUN-10
  1    42      481764     13-JUN-10 481770     13-JUN-10
  1    43      481770     13-JUN-10 481775     13-JUN-10
  1    44      481775     13-JUN-10 481779     13-JUN-10
  1    45      481779     13-JUN-10 481785     13-JUN-10
  1    46      481785     13-JUN-10 481793     13-JUN-10
  1    47      481793     13-JUN-10 481797     13-JUN-10
  1    48      481797     13-JUN-10 481807     13-JUN-10
  1    49      481807     13-JUN-10 481809     13-JUN-10
  1    50      481809     13-JUN-10 481935     13-JUN-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    95.71M     DISK        00:00:49     13-JUN-10
        BP Key: 8   Status: AVAILABLE  Compressed: YES  Tag: TAG20100613T202654
        Piece Name: /u99/MarsDB/backup/full_MARSDB_20100613_721600014_08lg5fge_1_1.bak
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 481943     13-JUN-10 /u14/oradata/MarsDB/system01.dbf
  2       Full 481943     13-JUN-10 /u14/oradata/MarsDB/undotbs01.dbf
  3       Full 481943     13-JUN-10 /u14/oradata/MarsDB/sysaux01.dbf
  4       Full 481943     13-JUN-10 /u14/oradata/MarsDB/users01.dbf
  5       Full 481943     13-JUN-10 /u14/oradata/MarsDB/testing01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
9       29.00K     DISK        00:00:01     13-JUN-10
        BP Key: 9   Status: AVAILABLE  Compressed: YES  Tag: TAG20100613T202750
        Piece Name: /u99/MarsDB/backup/full_MARSDB_20100613_721600070_09lg5fi6_1_1.bak

  List of Archived Logs in backup set 9
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    51      481935     13-JUN-10 481991     13-JUN-10

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    7.08M      DISK        00:00:02     13-JUN-10
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20100613T202752
        Piece Name: /u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-02
  Control File Included: Ckp SCN: 481997       Ckp time: 13-JUN-10
  SPFILE Included: Modification time: 13-JUN-10


Now the backups are done, lets verify the backups in the backup location.

As you can see my backups are at --> /u99/MarsDB/backup/



Section-8: Move backups to SAFE location.

For the purpose of this document, lets called this location as a remote location (although it is on the same server) and the actual path is at /u99/MarsDB/backup_copies

Lets move the backups to a remote location.

cp /u99/MarsDB/backup/* /u99/MarsDB/backup_copies/.



Section-9: Lets drop the User and Tablespace.

Before dropping the tablespace, lets make few log switches and check the seq# and SCN# again.

SQL> alter system switch logfile;
System altered.

SQL> /
System altered.

SQL> /
System altered.

SQL> select name,
        sequence#,
        first_change# as First_SCN_Change,
        to_char(first_time,'HH24:Mi:SS') as First_Time,
        next_change# Next_SCN_Change,
        to_char(next_time,'HH24:Mi:SS') as Next_Time
from
    v$archived_log;



As you can see the current SCN before we move to drop the objects is:
    Last Sequene# --> 54
    Last SCN         --> 482647

Lets drop user.

SQL> drop user testing cascade;
User dropped.

SQL> alter system switch logfile;
System altered.

SQL> select
            name,
            sequence#,
            first_change# as First_SCN_Change,
            to_char(first_time,'HH24:Mi:SS') as First_Time,
            next_change# Next_SCN_Change,
            to_char(next_time,'HH24:Mi:SS') as Next_Time
        from
            v$archived_log
    where
            sequence#=(select max(sequence#) from v$archived_log);


As you can see, after dropping the user, the current numbers are at:
    Last Sequene# --> 55
    Last SCN         --> 483069

Let's Drop Tablespace

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

SQL> alter system switch logfile;
System altered.

SQL> select
            name,
            sequence#,
            first_change# as First_SCN_Change,
            to_char(first_time,'HH24:Mi:SS') as First_Time,
            next_change# Next_SCN_Change,
            to_char(next_time,'HH24:Mi:SS') as Next_Time
        from
            v$archived_log
    where
            sequence#=(select max(sequence#) from v$archived_log);


As you can see, after dropping the tablespace, the current changes are at:
    Last Sequene# --> 56
    Last SCN         --> 483562

At this point, we can move on to our recovery process.



Section-10: shutdown database;

SQL> shutdown immediate;

Section-11: Restore database using the backup.

The reason we want to use the backup from remote location (/u99/MarsDB/backup_copies) instead of the actual RMAN backup (/u99/MarsDB/backup) location is because anytime a physical structure of the database is change, backup of control files gets updated. When we dropped the tablespace, physical structure was changed and hence a updated controlfile was triggered to the original backup location. We don't want to use the updated controlfile as it is not the file we want to use to restore the backups from.

For our demonstration, check the disk space used by both location. Original backup location which is known by RMAN. The size is bigger than remote location.

student1.com:(MarsDB)$ cd /u99/MarsDB/backup_copies/
student1.com:(MarsDB)$ du
131724  .
student1.com:(MarsDB)$ cd /u99/MarsDB/backup
student1.com:(MarsDB)$ du
139000  .

Let's check what files we have in these two locations.

student1.com:(MarsDB)$ ls -ltrh/u99/MarsDB/backup_copies/
total 129M
-rw-r-----  1 oracle oinstall  12M Jun 13 20:34 full_MARSDB_20100613_721600005_07lg5fg5_1_1.bak
-rw-r-----  1 oracle oinstall 7.1M Jun 13 20:34 control_n_spfile_c-1975244670-20100613-02
-rw-r-----  1 oracle oinstall 7.1M Jun 13 20:34 control_n_spfile_c-1975244670-20100613-01
-rw-r-----  1 oracle oinstall 6.9M Jun 13 20:34 control_n_spfile_c-1975244670-20100613-00
-rw-r-----  1 oracle oinstall  30K Jun 13 20:34 full_MARSDB_20100613_721600070_09lg5fi6_1_1.bak
-rw-r-----  1 oracle oinstall  96M Jun 13 20:34 full_MARSDB_20100613_721600014_08lg5fge_1_1.bak

student1.com:(MarsDB)$ ls -ltrh cd /u99/MarsDB/backup
total 136M
-rw-r-----  1 oracle oinstall 6.9M Jun 13 20:17 control_n_spfile_c-1975244670-20100613-00
-rw-r-----  1 oracle oinstall 7.1M Jun 13 20:19 control_n_spfile_c-1975244670-20100613-01
-rw-r-----  1 oracle oinstall  12M Jun 13 20:26 full_MARSDB_20100613_721600005_07lg5fg5_1_1.bak
-rw-r-----  1 oracle oinstall  96M Jun 13 20:27 full_MARSDB_20100613_721600014_08lg5fge_1_1.bak
-rw-r-----  1 oracle oinstall  30K Jun 13 20:27 full_MARSDB_20100613_721600070_09lg5fi6_1_1.bak
-rw-r-----  1 oracle oinstall 7.1M Jun 13 20:27 control_n_spfile_c-1975244670-20100613-02
-rw-r-----  1 oracle oinstall 7.1M Jun 13 20:50 control_n_spfile_c-1975244670-20100613-03
student1.com:(MarsDB)$


As you can see, there are three copies of controlfile in remote location and 4 copies of controlfile in rman backup location. Let's start the restore process using backups from the remote location.

student1.com:(none)$ export ORACLE_SID=MarsDB
student1.com:(MarsDB)$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Jun 13 21:14:18 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database (not started)

RMAN> set DBID=1975244670
executing command: SET DBID

RMAN> startup force nomount;
Oracle instance starte
Total System Global Area     167772160 bytes
Fixed Size                     1266368 bytes
Variable Size                100666688 bytes
Database Buffers              58720256 bytes
Redo Buffers                   7118848 bytes



Let's give out the controlfile location to RMAN. In this case, the original control files are at remote which is at /u99/MarsDB/backup_copies

RMAN> set controlfile autobackup format for device type disk to '/u99/MarsDB/backup_copies/control_n_spfile_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog



Let's restore controlfile.

RMAN> restore controlfile from autobackup;

Starting restore at 13-JUN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u05/flash_recovery_area
database name (or database unique name) used for search: MARSDB
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20100613
channel ORA_DISK_1: autobackup found: /u99/MarsDB/backup_copies/control_n_spfile_c-1975244670-20100613-02
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u02/oradata/MarsDB/control01.ctl
output filename=/u03/oradata/MarsDB/control02.ctl
output filename=/u04/oradata/MarsDB/control03.ctl
Finished restore at 13-JUN-10





Let's Mount the database

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1



Good. Database is mounted. Let's Restore db from last backup. However before we do that. Lets put the backup from remote backup location to the RMAN's known (original) location. So, lets remove (or copy to some other location known to you) everything from RMAN's known (original) backup location and put back the backup sets from our remote location to the original location.

        rm * /u99/MarsDB/backup
mv * /u99/MarsDB/backup_copies/* /u99/MarsDB/backup/

  
and now lets restore the database.

RMAN> restore database;

Starting restore at 13-JUN-10
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/13/2010 21:35:12
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

I got the following error because we removed the latest backupsets and put back the old backupsets into the RMAN's original location. Performing a crosscheck should overcome this.


RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-00 recid=5 stamp=721599450
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u99/MarsDB/backup/control_n_spfile_c-1975244670-20100613-01 recid=6 stamp=721599553
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600005_07lg5fg5_1_1.bak recid=7 stamp=721600006
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600014_08lg5fge_1_1.bak recid=8 stamp=721600014
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600070_09lg5fi6_1_1.bak recid=9 stamp=721600071
Crosschecked 5 objects

Let's try restoring again.

RMAN> restore database;

Starting restore at 13-JUN-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u14/oradata/MarsDB/system01.dbf
restoring datafile 00002 to /u14/oradata/MarsDB/undotbs01.dbf
restoring datafile 00003 to /u14/oradata/MarsDB/sysaux01.dbf
restoring datafile 00004 to /u14/oradata/MarsDB/users01.dbf
restoring datafile 00005 to /u14/oradata/MarsDB/testing01.dbf
channel ORA_DISK_1: reading from backup piece /u99/MarsDB/backup/full_MARSDB_20100613_721600014_08lg5fge_1_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u99/MarsDB/backup/full_MARSDB_20100613_721600014_08lg5fge_1_1.bak tag=TAG20100613T202654
channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 13-JUN-10




Good, restore is completed. Now let's find out the scn we want to recover upto. Go back few steps (section-9) in the document to find out the SCN right before the user/tablespace was dropped. Then we can use the same scn to recover database upto that point in time. The SCN is 482647


Section-12 Recover Database (Use point in time recovery of the database);

RMAN> recover database until scn 482647;

Starting recover at 13-JUN-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 51 is already on disk as file /u99/MarsDB/archive/Arc_1_51_721595136.arc
archive log filename=/u99/MarsDB/archive/Arc_1_51_721595136.arc thread=1 sequence=51
archive log filename=/u99/MarsDB/archive/Arc_1_52_721595136.arc thread=1 sequence=52
archive log filename=/u99/MarsDB/archive/Arc_1_53_721595136.arc thread=1 sequence=53
archive log filename=/u99/MarsDB/archive/Arc_1_54_721595136.arc thread=1 sequence=54
media recovery complete, elapsed time: 00:00:02
Finished recover at 13-JUN-10



Media Recovery is complete. Let's verify if the dropped tablespace is back in the database;

SQL> select name from V$tablespace;
SQL> select name from v$datafile;

and the output I see is,




Section-13: Open database

RMAN> alter database open resetlogs;
database opened




Section-14: Verify tables that were dropped are back into the database;

export ORACLE_SID=MarsDB
sqlplus testing/abc123
SQL> select * from cat;
SQL> set linesize 200
SQL? select * from emp1;



Thats it for now. Hope this guide is helpful to you all.

--Moid M




Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Followers