dba: Oracle Backup and Restore Case Scenarios

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

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

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

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

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