RMAN RECOVERY - SYSTEM / NON SYSTEMStep 1 : RMAN Backup, Restoration and Recovery of a System Datafile : The system datafile recovery tablespace is different. The biggest difference lies within the fact that the system tablespace contains the data dictionary which needs to be updated all the time. Any recovery of the datafile which contains the database's data dictionary information cannot be done at all when the database is operational. Therefore, the restoration of the system datafile is done by bringing the database down and in the mount stage. The rest of the steps are the same as the normal datafile's recovery operation previously explored. Now see it in action. Step 2 : Recovering from a lost system data file : Due to media failure, Bob has lost the datafile of the system tablespace and received the following error message while querying data dictionary views: ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u01/app/oracle/oradata/dev/system01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Step 3: Check RMAN backup of tablespace :
RMAN> list backup of tablespace system;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1021.61M DISK 00:00:11 10-FEB-24
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20240210T132244
Piece Name: /u01/app/oracle/fast_recovery_area/dev/DEV/backupset/2024_02_10/o1_mf_nnndf_TAG20240210T132244_lwgbtf8d_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 1525521 10-FEB-24 NO /u01/app/oracle/oradata/dev/system01.dbf
Step 4 : Restoring tablespace from rman backup :
RMAN> restore datafile 1;
Starting restore at 10-FEB-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 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 00001 to /u01/app/oracle/oradata/dev/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/dev/DEV/backupset/2024_02_10/
o1_mf_nnndf_TAG20240210T132244_lwgbtf8d_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/dev/DEV/backupset/2024_02_10/
o1_mf_nnndf_TAG20240210T132244_lwgbtf8d_.bkp tag=TAG20240210T132244
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 10-FEB-24
Step 5 : Recovering DATAFILE WITH ID :
RMAN> recover datafile 1;
Starting recover at 10-FEB-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-FEB-24
Step 6 : Open the database :
RMAN> alter database open ;
Statement processed
II)NON SYSTEM DATAFILE RECOVER : Step 1: Create tablespace called TEST with one datafile test01.dbf :
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,STATUS from dba_data_files where TABLESPACE_NAME='TEST';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME STATUS
---------- ------------------------------ ---------
/home/oracle/test01.dbf
5 TEST AVAILABLE
Step 2:Take full backup of Database using rman :
[oracle@tesdb ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Feb 4 20:16:41 2024
Version 19.9.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: TES (DBID=3622758121)
RMAN> backup database plus archivelog;
Starting backup at 04-FEB-24
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1158944963
input archived log thread=1 sequence=6 RECID=2 STAMP=1158946199
input archived log thread=1 sequence=7 RECID=3 STAMP=1158946241
input archived log thread=1 sequence=8 RECID=4 STAMP=1158946282
input archived log thread=1 sequence=9 RECID=5 STAMP=1158946372
input archived log thread=1 sequence=10 RECID=6 STAMP=1160079420
channel ORA_DISK_1: starting piece 1 at 04-FEB-24
channel ORA_DISK_1: finished piece 1 at 04-FEB-24
piece handle=/u01/app/oracle/fast_recovery_area/TES/backupset/2024_02_04/o1_mf_annnn_TAG20240204T201701_lvz8v5oy_.bkp
tag=TAG20240204T201701 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 04-FEB-24
Starting backup at 04-FEB-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/TES/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/TES/sysaux01.dbf
input datafile file number=00005 name=/home/oracle/test01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/TES/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/TES/users01.dbf
channel ORA_DISK_1: starting piece 1 at 04-FEB-24
channel ORA_DISK_1: finished piece 1 at 04-FEB-24
piece handle=/u01/app/oracle/fast_recovery_area/TES/backupset/2024_02_04/o1_mf_nnndf_TAG20240204T201717_lvz8vofo_.bkp
tag=TAG20240204T201717 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 04-FEB-24
Starting backup at 04-FEB-24
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=7 STAMP=1160079452
channel ORA_DISK_1: starting piece 1 at 04-FEB-24
channel ORA_DISK_1: finished piece 1 at 04-FEB-24
piece handle=/u01/app/oracle/fast_recovery_area/TES/backupset/2024_02_04/o1_mf_annnn_TAG20240204T201732_lvz8w51z_.bkp
tag=TAG20240204T201732 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 04-FEB-24
Starting Control File and SPFILE Autobackup at 04-FEB-24
piece handle=/u01/app/oracle/fast_recovery_area/TES/autobackup/2024_02_04/o1_mf_s_1160079454_lvz8w6f4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 04-FEB-24
Step 3: For testing purpose create a Table called TT1 in TEST tablespace and perform some DML operation and commit :
SQL> create table tt1 (id number,name varchar2(6)) tablespace test;
Table created.
SQL> begin
2 for i in 1..7000 loop
3 insert into tt1 values(i,'aa');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Step 4: Now navigate to the location of where TEST tablespace was created and remove the datafile :
[oracle@tesdb ~]$ rm -rf test01.dbf
Step 5 : Now once you know that datafile of TEST is removed as process of recovery try to bring it offline and you will get error as follows:
SQL> alter tablespace TEST offline;
Step 6 : Now bring TEST tablespace offline with immediate option and check status :
SQL> alter tablespace TEST offline immediate;
Tablespace altered.
Step 7:Now connect with rman and check datafiles are backed up or not using file_id :
RMAN> list backup of datafile 5;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.27G DISK 00:00:08 04-FEB-24
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20240204T201717
Piece Name: /u01/app/oracle/fast_recovery_area/TES/backupset/2024_02_04/o1_mf_nnndf_TAG20240204T201717_lvz8vofo_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 2285956 04-FEB-24 NO /home/oracle/test01.dbf
Step 8 : While restoring tablespace TEST from rman backup :
RMAN> restore datafile 5;
Starting restore at 04-FEB-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 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 00005 to /home/oracle/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TES/backupset/2024_02_04/
o1_mf_nnndf_TAG20240204T201717_lvz8vofo_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TES/backupset/2024_02_04/
o1_mf_nnndf_TAG20240204T201717_lvz8vofo_.bkp tag=TAG20240204T201717
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 04-FEB-24
Step 9 : Recovering DATAFILE WITH ID :
RMAN> recover datafile 5;
Starting recover at 04-FEB-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-FEB-24
Step 10:Bring the tablespace TEST online and check status of tablespace.
SQL> alter tablespace test online;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEST ONLINE
6 rows selected.
Step 10: Using sql check whether datafile 5 are created for tablespace TEST :
SQL> select TABLESPACE_NAME,FILE_NAME,FILE_ID from dba_data_files where tablespace_name ='TEST';
TABLESPACE_NAME FILE_NAME FILE_ID
------------------------------ ------------------------------ -------
TEST /home/oracle/test01.dbf 5
Now using OS commands.
SQL> !ls -lrt /home/oracle/test01.dbf
-rw-r-----. 1 oracle oinstall 419438592 Feb 5 20:34 /home/oracle/test01.dbf
Step 12: Query the table which was created for tablespace TEST
SQL> select count(*) from tt1;
COUNT(*)
----------
7000
Reasons to make tablespace offline : Following tablespace cannot be taken offline : NORMAL : A tablespace can be taken offline normally if no error conditions exist for any of the datafiles of the tablespace. No datafile in the tablespace can be currently offline as the result of a write error and when you specify OFFLINE NORMAL, the database takes a checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL is the default. TEMPORARY : A tablespace can be taken offline temporarily, even if there are error conditions for one or more files of the tablespace. When you specify OFFLINE TEMPORARY, the database takes offline the datafiles that are not already offline, checkpointing them as it does so.If no files are offline, but you use the temporary clause, media recovery is not required to bring the tablespace back online. However, if one or more files of the tablespace are offline because of write errors, and you take the tablespace offline temporarily, the tablespace requires recovery before you can bring it back online. IMMEDIATE : A tablespace can be taken offline immediately, without the database taking a checkpoint on any of the datafiles. When you specify OFFLINE IMMEDIATE, media recovery for the tablespace is required before the tablespace can be brought online. You cannot take a tablespace offline immediately if the database is running in NOARCHIVELOG mode. |