Views

Showing posts with label Backup. Show all posts
Showing posts with label Backup. Show all posts

Monday, September 25, 2017

Oracle Backup and Restore Case Scenarios - Case Study 6 (Magic Restore)

Oracle Backup and Restore Case Scenarios - Case Study 6

Case 6: Magic Restore: Recover a lost datafile without backup

If a datafile has been dropped mistakenly before the backup, it can be brought back. RMAN restores it from online log and archive log. This is done through RMAN new feature 
RMAN Automatic  Datafile Creation.

SQL> select file_id, file_name ,tablespace_name,AUTOEXTENSIBLE,status  from dba_data_files order by file_id;

FILE_ID FILE_NAME                        TABLESPACE_NAME    AUT STATUS
-------- ------------------------------------------------- ------------------ --- ----
1  /u01/app/oracle/oradata/ORADB1/system01.dbf       SYSTEM   YES AVAILABLE
2  /u01/app/oracle/oradata/ORADB1/sysaux01.dbf       SYSAUX   YES AVAILABLE
3  /u01/app/oracle/oradata/ORADB1/undotbs01.dbf      UNDOTBS1 YES AVAILABLE
4  /u01/app/oracle/oradata/ORADB1/users01.dbf        USERS    YES AVAILABLE
5  /u01/app/oracle/oradata/ORADB1/example01.dbf      EXAMPLE  YES AVAILABLE


CREATE NEW TABLESPACE tbs_restore

SQL> CREATE TABLESPACE tbs_restore DATAFILE '/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf' SIZE 20M ONLINE;

Tablespace created.



TAKE RMAN FULL BACKUP OF DATABASE

[oracle@localhost ORADB1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 25 14:56:29 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADB1 (DBID=2294695355)

RMAN> backup database plus archivelog;


Starting backup at 25-SEP-2017 14:56:40
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=48 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=1 STAMP=955328309
input archived log thread=1 sequence=14 RECID=2 STAMP=955328309
input archived log thread=1 sequence=15 RECID=3 STAMP=955328320
input archived log thread=1 sequence=16 RECID=4 STAMP=955383829
input archived log thread=1 sequence=17 RECID=5 STAMP=955383888
input archived log thread=1 sequence=18 RECID=13 STAMP=955400510
input archived log thread=1 sequence=19 RECID=11 STAMP=955400510
input archived log thread=1 sequence=20 RECID=12 STAMP=955400510
channel ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:56:41
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=14 STAMP=955466468
input archived log thread=1 sequence=2 RECID=15 STAMP=955541254
input archived log thread=1 sequence=3 RECID=16 STAMP=955552300
channel ORA_DISK_2: starting piece 1 at 25-SEP-2017 14:56:41
channel ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:56:48
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145641_dwklykl7_.bkp tag=TAG20170925T145641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=17 STAMP=955637365
input archived log thread=1 sequence=5 RECID=18 STAMP=955637511
input archived log thread=1 sequence=6 RECID=19 STAMP=955637569
input archived log thread=1 sequence=7 RECID=20 STAMP=955637800
channel ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:56:48
channel ORA_DISK_2: finished piece 1 at 25-SEP-2017 14:56:48
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145641_dwklykmf_.bkp tag=TAG20170925T145641 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:56:49
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145641_dwklyrq7_.bkp tag=TAG20170925T145641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-SEP-2017 14:56:49

Starting backup at 25-SEP-2017 14:56:49
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORADB1/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB1/users01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORADB1/example01.dbf
channel ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:56:50
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/ORADB1/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORADB1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf
channel ORA_DISK_2: starting piece 1 at 25-SEP-2017 14:56:50
channel ORA_DISK_2: finished piece 1 at 25-SEP-2017 14:57:45
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklywhn_.bkp tag=TAG20170925T145649 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:58:05
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklyt3t_.bkp tag=TAG20170925T145649 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 25-SEP-2017 14:58:05

Starting backup at 25-SEP-2017 14:58:05
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=21 STAMP=955637885
channel ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:58:05
channel ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:58:06
piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145805_dwkm15dy_.bkp tag=TAG20170925T145805 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-SEP-2017 14:58:06

Starting Control File and SPFILE Autobackup at 25-SEP-2017 14:58:06
piece handle=/u01/backup/rman/ctl_c-2294695355-20170925-01 comment=NONE
Finished Control File and SPFILE Autobackup at 25-SEP-2017 14:58:07

RMAN>


Add Datafile to new tablespace tbs_restore

SQL> ALTER TABLESPACE tbs_restore ADD DATAFILE '/u01/app/oracle/oradata/ORADB1/tbs_restore_02.dbf' SIZE 20M AUTOEXTEND ON;

Tablespace altered.

Simulate Failure by removing datafiles for tbs_resore tablespace from disk

[oracle@localhost ORADB1]$ ls -ltr tbs_restore*
-rw-r-----. 1 oracle oinstall 20979712 Sep 25 15:02 tbs_restore_01.dbf
-rw-r-----. 1 oracle oinstall 20979712 Sep 25 15:02 tbs_restore_02.dbf
[oracle@localhost ORADB1]$ rm tbs_restore*
[oracle@localhost ORADB1]$ ls -ltr tbs_restore*
ls: cannot access tbs_restore*: No such file or directory

Bring the tablespace offline.

SQL> alter tablespace tbs_restore offline ;
alter tablespace tbs_restore offline
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Tablespace wont go offline as datafiles have been dropped. Make it offline using IMMEDIATE clause.

SQL> alter tablespace tbs_restore offline immediate;

Tablespace altered.

Restore datafile 6 (tbs_restore_01.dbf) which was backed up.

RMAN> restore datafile 6;

Starting restore at 25-SEP-2017 15:05:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklywhn_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklywhn_.bkp tag=TAG20170925T145649
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-SEP-2017 15:05:32


Restore the tablespace tbs_restore.
You can see that datafile 7 which was not backed up has been restored.
First is preview session to see when RMAN is creating the datafile not backed up.
(Preview output is captured from different session, so timings are different.)
This RMAN feature was introduced in 10g and enhanced in 12c

RMAN> restore tablespace tbs_restore preview;

Starting restore at 25-SEP-2017 15:56:07
using channel ORA_DISK_1
using channel ORA_DISK_2

datafile 7 will be created automatically during restore operation

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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ --------------------
28      Full    81.22M     DISK        00:00:29     25-SEP-2017 15:45:39
        BP Key: 28   Status: AVAILABLE  Compressed: YES  Tag: TAG20170925T154538
        Piece Name: /u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T154538_dwkotbyx_.bkp
  List of Datafiles in backup set 28
  File LV Type Ckp SCN    Ckp Time             Name
  ---- -- ---- ---------- -------------------- ----
  6       Full 1298356    25-SEP-2017 15:45:38 /u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf

List of Archived Log Copies for database with db_unique_name ORADB1
=====================================================================

Key     Thrd Seq     S Low Time           
------- ---- ------- - --------------------
23      1    10      A 25-SEP-2017 15:45:29
        Name: /u01/app/oracle/oradata/ORADB1/archive/1_10_955400510.dbf

Media recovery start SCN is 1298356
Recovery must be done beyond SCN 1298686 to clear datafile fuzziness
Finished restore at 25-SEP-2017 15:56:07

RMAN>

RMAN> restore tablespace tbs_restore;

Starting restore at 25-SEP-2017 15:06:04
using channel ORA_DISK_1
using channel ORA_DISK_2

creating datafile file number=7 name=/u01/app/oracle/oradata/ORADB1/tbs_restore_02.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 25-SEP-2017 15:06:04

Recover tablespace tbs_store.

RMAN> RECOVER TABLESPACE tbs_restore;

Starting recover at 25-SEP-2017 15:06:30
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 25-SEP-2017 15:06:31

RMAN>

Make the tablespace online.

SQL> alter tablespace tbs_restore online;

Tablespace altered.

Check and verify whether both datafiles are restored back.

SQL> select file_id, file_name ,tablespace_name,AUTOEXTENSIBLE,status  from dba_data_files order by file_id;

FILE_ID FILE_NAME                        TABLESPACE_NAME    AUT STATUS
-------- ------------------------------------------------- ------------------ --- ----
1  /u01/app/oracle/oradata/ORADB1/system01.dbf        SYSTEM      YES AVAILABLE
2  /u01/app/oracle/oradata/ORADB1/sysaux01.dbf        SYSAUX      YES AVAILABLE
3  /u01/app/oracle/oradata/ORADB1/undotbs01.dbf       UNDOTBS1    YES AVAILABLE
4  /u01/app/oracle/oradata/ORADB1/users01.dbf         USERS       YES AVAILABLE
5  /u01/app/oracle/oradata/ORADB1/example01.dbf       EXAMPLE     YES AVAILABLE
6  /u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf  TBS_RESTORE NO  AVAILABLE
7  /u01/app/oracle/oradata/ORADB1/tbs_restore_02.dbf  TBS_RESTORE YES AVAILABLE
Posted by at No comments:
Labels: , , , ,

Friday, September 22, 2017

Oracle Backup and Restore Case Scenarios - Case Study 4


Oracle Backup and Restore Case Scenarios - Case Study 4

Case 4: Restore a lost Tablespace.

Steps followed:
1.       Set Controlfile Autobackup ON
2.       Take backup of database using RMAN
3.       Simulate failure by dropping a tablespace
4.       Try to restore tablespace which will fail
5.       Shutdown database
6.       Check the time of tablespace drop
7.       Startup database in mount mode
8.       Restore the control file which is generated before the tablespace drop time
9.       Mount the database
10.   Check the schema whether control file has the dropped tablespace
11.   Restore database to point in time before the tablespace drop time
12.   Recover database
13.   Open database in RESETLOGS mode
14.   Check whether dropped tablespace is back in database


Connect to sqlplus

[oracle@localhost ORADB1]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 22 15:32:57 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Create a sample tablespace to drop and restore

SQL> CREATE TABLESPACE tbs_restore DATAFILE '/u01/app/oracle/oradata/ORADB1/tbs_restore01.dbf'  SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

Tablespace created.

Check the datafiles and tablespace.

SQL> set pages 180 lines 99
col file_name for a55
col TABLESPACE_NAME for a18
select file_name,tablespace_name,AUTOEXTENSIBLE,status  from dba_data_files;

--------------------------------------------- ------------------
/u01/app/oracle/oradata/ORADB1/example01.dbf  EXAMPLE  YES AVAILABLE
/u01/app/oracle/oradata/ORADB1/users01.dbf    USERS    YES AVAILABLE
/u01/app/oracle/oradata/ORADB1/undotbs01.dbf  UNDOTBS1 YES AVAILABLE
/u01/app/oracle/oradata/ORADB1/sysaux01.dbf   SYSAUX   YES AVAILABLE
/u01/app/oracle/oradata/ORADB1/system01.dbf  SYSTEM   YES AVAILABLE
/u01/app/oracle/oradata/ORADB1/tbs_restore01.dbf TBS_RESTORE  YES AVAILABLE


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

Configure CONTROLFILE Autobackup ON

[oracle@localhost ORADB1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 22 16:13:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADB1 (DBID=2294695355)

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "/u01/backup/rman/ctl_%F";

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "/u01/backup/rman/ctl_%F";
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
new RMAN configuration parameters are successfully stored

Checking RMAN configurations

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORADB1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "/u01/backup/rman/ctl_%F";
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_ORADB1.f'; # default

Take a full database backup.

RMAN> run {
allocate channel c1 device type disk format '/u01/backup/rman/FULL_%u_%p_%s' ;
backup database PLUS ARCHIVELOG format '/u01/backup/rman/Arch_%u_%p_%s';
}

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=41 device type=DISK

Starting backup at 22-SEP-2017 17:11:33
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=1 STAMP=955328309
input archived log thread=1 sequence=14 RECID=2 STAMP=955328309
input archived log thread=1 sequence=15 RECID=3 STAMP=955328320
input archived log thread=1 sequence=16 RECID=4 STAMP=955383829
input archived log thread=1 sequence=17 RECID=5 STAMP=955383888
input archived log thread=1 sequence=18 RECID=6 STAMP=955386693
channel c1: starting piece 1 at 22-SEP-2017 17:11:33
channel c1: finished piece 1 at 22-SEP-2017 17:11:36
piece handle=/u01/backup/rman/Arch_05sf42q5_1_5 tag=TAG20170922T171133 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-SEP-2017 17:11:36

Starting backup at 22-SEP-2017 17:11:36
channel c1: starting compressed full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORADB1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ORADB1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ORADB1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORADB1/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/ORADB1/tbs_restore01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORADB1/users01.dbf
channel c1: starting piece 1 at 22-SEP-2017 17:11:36
channel c1: finished piece 1 at 22-SEP-2017 17:12:31
piece handle=/u01/backup/rman/FULL_06sf42q8_1_6 tag=TAG20170922T171136 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:55
Finished backup at 22-SEP-2017 17:12:31

Starting backup at 22-SEP-2017 17:12:31
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=7 STAMP=955386752
channel c1: starting piece 1 at 22-SEP-2017 17:12:32
channel c1: finished piece 1 at 22-SEP-2017 17:12:33
piece handle=/u01/backup/rman/Arch_07sf42s0_1_7 tag=TAG20170922T171232 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 22-SEP-2017 17:12:33

Starting Control File and SPFILE Autobackup at 22-SEP-2017 17:12:33
piece handle=/u01/backup/rman/ctl_c-2294695355-20170922-01 comment=NONE
Finished Control File and SPFILE Autobackup at 22-SEP-2017 17:12:34
released channel: c1


RMAN>

[oracle@localhost rman]$ pwd
/u01/backup/rman
[oracle@localhost rman]$ ls -ltr
total 664468
-rw-r-----. 1 oracle oinstall  16861184 Sep 22 16:23 Arch_01sf400l_1_1
-rw-r-----. 1 oracle oinstall 312582144 Sep 22 16:24 FULL_02sf400p_1_2
-rw-r-----. 1 oracle oinstall      9216 Sep 22 16:24 Arch_03sf402g_1_3
-rw-r-----. 1 oracle oinstall  10158080 Sep 22 16:24 ctl_c-2294695355-20170922-00
-rw-r-----. 1 oracle oinstall  17725952 Sep 22 17:11 Arch_05sf42q5_1_5
-rw-r-----. 1 oracle oinstall 312893440 Sep 22 17:12 FULL_06sf42q8_1_6
-rw-r-----. 1 oracle oinstall     16896 Sep 22 17:12 Arch_07sf42s0_1_7
-rw-r-----. 1 oracle oinstall  10158080 Sep 22 17:12 ctl_c-2294695355-20170922-01

Connect to Sqlplus

[oracle@localhost rman]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 22 17:14:02 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Simulate failure by dropping the tablespace.

SQL> drop tablespace tbs_restore including contents and datafiles;

Tablespace dropped.

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

Connect to RMAN

[oracle@localhost rman]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 22 20:41:28 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADB1 (DBID=2294695355)

Try to restore tablespace which will fail.
The restore fails because after tablespace drop, controlfile structure has changed. RMAN does not identify the tablespace and hence fails to restore.

RMAN> restore tablespace tbs_restore;

Starting restore at 22-SEP-2017 20:42:27
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=39 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/22/2017 20:42:28
RMAN-20202: Tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "TBS_RESTORE"

Shutdown database.

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN>

Check the backup directory to see the control file backup generated.

[oracle@localhost rman]$ ls -ltr
total 674388
-rw-r-----. 1 oracle oinstall  16861184 Sep 22 16:23 Arch_01sf400l_1_1
-rw-r-----. 1 oracle oinstall 312582144 Sep 22 16:24 FULL_02sf400p_1_2
-rw-r-----. 1 oracle oinstall      9216 Sep 22 16:24 Arch_03sf402g_1_3
-rw-r-----. 1 oracle oinstall  10158080 Sep 22 16:24 ctl_c-2294695355-20170922-00
-rw-r-----. 1 oracle oinstall  17725952 Sep 22 17:11 Arch_05sf42q5_1_5
-rw-r-----. 1 oracle oinstall 312893440 Sep 22 17:12 FULL_06sf42q8_1_6
-rw-r-----. 1 oracle oinstall     16896 Sep 22 17:12 Arch_07sf42s0_1_7
-rw-r-----. 1 oracle oinstall  10158080 Sep 22 17:12 ctl_c-2294695355-20170922-01
-rw-r-----. 1 oracle oinstall  10158080 Sep 22 17:19 ctl_c-2294695355-20170922-02

Start database in NOMOUNT mode.

[oracle@localhost rman]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 22 20:44:53 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size               2257840 bytes
Variable Size          520096848 bytes
Database Buffers       310378496 bytes
Redo Buffers             2371584 bytes
SQL>

Check the alert log timing of tablespace drop.

Fri Sep 22 17:14:06 2017
drop tablespace tbs_restore including contents and datafiles
Deleted file /u01/app/oracle/oradata/ORADB1/tbs_restore01.dbf
Completed: drop tablespace tbs_restore including contents and datafiles

We need to restore control file before above time.
And do Point in time recovery before above time.

[oracle@localhost rman]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Sep 22 20:53:51 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORADB1 (not mounted)

RMAN> restore controlfile from '/u01/backup/rman/ctl_c-2294695355-20170922-01';

Starting restore at 22-SEP-2017 20:54:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORADB1/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORADB1/control02.ctl
Finished restore at 22-SEP-2017 20:54:18

Put database in mount mode.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Check schema to make sure correct control file is restored.
We can see that tablespace TBS_RESTORE is present in schema.

RMAN> report schema;

Starting implicit crosscheck backup at 22-SEP-2017 20:55:36
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 22-SEP-2017 20:55:37

Starting implicit crosscheck copy at 22-SEP-2017 20:55:37
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 22-SEP-2017 20:55:37

searching for all files in the recovery area
cataloging files...
no files cataloged

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORADB1

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    760      SYSTEM               ***     /u01/app/oracle/oradata/ORADB1/system01.dbf
2    580      SYSAUX               ***     /u01/app/oracle/oradata/ORADB1/sysaux01.dbf
3    110      UNDOTBS1             ***    /u01/app/oracle/oradata/ORADB1/undotbs01.dbf
4    5        USERS                ***     /u01/app/oracle/oradata/ORADB1/users01.dbf
5    346      EXAMPLE              ***    /u01/app/oracle/oradata/ORADB1/example01.dbf
6    0        TBS_RESTORE          ***/u01/app/oracle/oradata/ORADB1/tbs_restore01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767     /u01/app/oracle/oradata/ORADB1/temp01.dbf

RMAN>

You need to set the time to a time before the tablespace drop.
Restore and recover database in RMAN run block.

RMAN> run {
set until time "to_date('SEP-22-2017 17:14:00','MON-DD-YYYY HH24:Mi:SS')";
restore database;
recover database;
}

executing command: SET until clause

Starting restore at 22-SEP-2017 20:59:16
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORADB1/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORADB1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORADB1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORADB1/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORADB1/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ORADB1/tbs_restore01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/rman/FULL_06sf42q8_1_6
channel ORA_DISK_1: piece handle=/u01/backup/rman/FULL_06sf42q8_1_6 tag=TAG20170922T171136
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 22-SEP-2017 21:00:21

Starting recover at 22-SEP-2017 21:00:21
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/oradata/ORADB1/redo01.log
archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/oradata/ORADB1/redo02.log
archived log file name=/u01/app/oracle/oradata/ORADB1/redo01.log thread=1 sequence=19
archived log file name=/u01/app/oracle/oradata/ORADB1/redo02.log thread=1 sequence=20
media recovery complete, elapsed time: 00:00:00
Finished recover at 22-SEP-2017 21:00:23

RMAN>

Opening the database in RESETLOGS mode.

RMAN> alter database open resetlogs;

database opened

Check whether tablespace has been restored and back in database.

SQL> select file_name,tablespace_name,AUTOEXTENSIBLE,status  from dba_data_files where tablespace_name='TBS_RESTORE';

FILE_NAME                                         TABLESPACE_NAME   AUT STATUS
----------------------------------------------- ----------------- ---- ----------
/u01/app/oracle/oradata/ORADB1/tbs_restore01.dbf TBS_RESTORE      YES  AVAILABLE



Posted by at No comments:
Labels: , , ,
Subscribe to: Posts (Atom)