RMAN Practices - Datafile restore Scenario
In this article i will be covering below topics.
Restore/Recover NON-SYSTEM datafile having Good backup
In this test we will restore/recover Non-System datafiles which is having good backup in past.
Check for Backup as below.
[oracle@racsetn1 ~]$ rman target /
connected to target database: TNT (DBID=1427195521)
RMAN> restore validate datafile 9;
Starting restore at 26-OCT-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=154 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=279 instance=TNT1 device type=DISK
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece +FRA/TNT/BACKUPSET/2022_10_26/nnndf0_level0_0.796.1119119299
channel ORA_DISK_1: piece handle=+FRA/TNT/BACKUPSET/2022_10_26/nnndf0_level0_0.796.1119119299 tag=LEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
Finished restore at 26-OCT-22
RMAN> list backup of datafile 9;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
249 Full 1.05M DISK 00:00:01 26-OCT-22
BP Key: 304 Status: AVAILABLE Compressed: NO Tag: LEVEL0
Piece Name: +FRA/TNT/BACKUPSET/2022_10_26/nnndf0_level0_0.796.1119119299
List of Datafiles in backup set 249
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
9 Full 3149704 26-OCT-22 NO +DATA/TNT/DATAFILE/ts_nolog.296.1119117123
RMAN> exit
Let we delete datafile 9 as below.
[grid@racsetn1 ~]$ srvctl stop database -d TNT
[grid@racsetn1 ~]$ asmcmd
ASMCMD> cd DATA/TNT/DATAFILE/
ASMCMD> ls
RO_TS.294.1117385373
SYSAUX.283.1115903017
SYSTEM.282.1115902991
TS_NOLOG.295.1117461389
TS_NOLOG.296.1119117123
UNDOTBS1.284.1115903031
UNDOTBS2.290.1115904057
USERS.285.1115903033
ASMCMD> rm ts_nolog.295.1117461389
ASMCMD> exit
Let we try to start database and see if we get any error.
[grid@racsetn1 ~]$ srvctl start database -d TNT
PRCR-1079 : Failed to start resource ora.tnt.db
CRS-5017: The resource action "ora.tnt.db start" encountered the following error:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '+DATA/TNT/DATAFILE/ts_nolog.295.1117461389'
. For details refer to "(:CLSN00107:)" in "/grid_home/app/oracle/diag/crs/racsetn2/crs/trace/crsd_oraagent_oracle.trc".
CRS-5017: The resource action "ora.tnt.db start" encountered the following error:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '+DATA/TNT/DATAFILE/ts_nolog.295.1117461389'
. For details refer to "(:CLSN00107:)" in "/grid_home/app/oracle/diag/crs/racsetn1/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.tnt.db' on 'racsetn2' failed
CRS-2632: There are no more servers to try to place resource 'ora.tnt.db' on that would satisfy its placement policy
CRS-2674: Start of 'ora.tnt.db' on 'racsetn1' failed
[grid@racsetn1 ~]$ logout
[oracle@racsetn1 trace]$
Alert log as below.
This instance was first to open
2022-10-26T22:11:56.599914+05:30
Errors in file /ora_home/app/oracle/diag/rdbms/tnt/TNT1/trace/TNT1_dbw0_6697.trc:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '+DATA/TNT/DATAFILE/ts_nolog.295.1117461389'
ORA-17503: ksfdopn:2 Failed to open file +DATA/TNT/DATAFILE/ts_nolog.295.1117461389
ORA-15012: ASM file '+DATA/TNT/DATAFILE/ts_nolog.295.1117461389' does not exist
Ping without log force is disabled:
not an Exadata system.
2022-10-26T22:11:56.870947+05:30
as we can see DB is not starting .
So we need to start DB in mount mode manually as below.
[oracle@racsetn1 trace]$ sqlplus "/as sysdba"
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2751463160 bytes
Fixed Size 8900344 bytes
Variable Size 637534208 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);
FILE# STATUS ERROR REC TABLESPACE_NAME NAME
--------------------------------------------------------------------------------
8 ONLINE FILE NOT FOUND
SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
------------
MOUNTED
SQL> SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME FROM V$RECOVER_FILE;
FILE# ERROR ONLINE_ CHANGE# TIME
------- ---------- ---------
8 FILE NOT FOUND ONLINE 0
SQL> SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#; 2 3 4 5
Let we make this file OFFLINE and start restore/recover operation as below.
SQL> alter database datafile 8 offline;
Database altered.
SQL> select name,open_mode,database_role from gv$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TNT MOUNTED PRIMARY
SQL> exit
Now we need to RESTORE/RECOVER datafile 8 from backup and make it ONLINE as below.
[oracle@racsetn1 trace]$ rman target /
connected to target database: TNT (DBID=1427195521, not open)
RMAN> restore datafile 8;
Starting restore at 26-OCT-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=148 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=272 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=398 instance=TNT1 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 00008 to +DATA/TNT/DATAFILE/ts_nolog.295.1117461389
channel ORA_DISK_1: reading from backup piece +FRA/TNT/BACKUPSET/2022_10_07/nnndf0_tag20221007t150649_0.741.1117465611
channel ORA_DISK_1: piece handle=+FRA/TNT/BACKUPSET/2022_10_07/nnndf0_tag20221007t150649_0.741.1117465611 tag=TAG20221007T150649
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 26-OCT-22
RMAN> recover datafile 8;
Starting recover at 26-OCT-22
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
archived log for thread 1 with sequence 23 is already on disk as file +FRA/TNT/ARCHIVELOG/2022_10_07/thread_1_seq_23.754.1117471379
archived log for thread 1 with sequence 24 is already on disk as file +FRA/TNT/ARCHIVELOG/2022_10_22/thread_1_seq_24.759.1118785855
...
...
...
...
archived log file name=+FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_2.797.1119132271 thread=1 sequence=2
archived log file name=+FRA/TNT/ARCHIVELOG/2022_10_26/thread_1_seq_3.798.1119132277 thread=1 sequence=3
media recovery complete, elapsed time: 00:00:19
Finished recover at 26-OCT-22
RMAN> alter database datafile 8 online;
Statement processed
RMAN> exit
Recovery Manager complete.
Now let we recycle database and open the same,
[grid@racsetn1 ~]$ srvctl stop database -d TNT
[grid@racsetn1 ~]$ srvctl start database -d TNT
[grid@racsetn1 ~]$ logout
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
SQL> @name
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
TNT READ WRITE PRIMARY MAXIMUM PERFORMANCE
TNT READ WRITE PRIMARY MAXIMUM PERFORMANCE
Restore/Recover deleted datafile having NO Backup at all.
In this case we will drop datafile which will have no backup as below.
Create sample tablespace and Table as below.
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> @name
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
TNT READ WRITE PRIMARY MAXIMUM PERFORMANCE
TNT READ WRITE PRIMARY MAXIMUM PERFORMANCE
SQL> create tablespace TNT_TEST DATAFILE '+DATA' size 100m;
Tablespace created.
SQL> create table TNT_TEST (ind int) tablespace TNT_TEST;
Table created.
SQL> INSERT INTO TNT_TEST VALUES (10);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TNT_TEST;
IND
----------
10
SQL> EXIT
Check if we have any backup or Copy for this Datafile as below.
[oracle@racsetn1 ~]$ rman target /
connected to target database: TNT (DBID=1427195521)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TNT
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 920 SYSTEM YES +DATA/TNT/DATAFILE/system.282.1115902991
2 100 RO_TS NO +DATA/TNT/DATAFILE/ro_ts.294.1117385373
3 650 SYSAUX NO +DATA/TNT/DATAFILE/sysaux.283.1115903017
4 335 UNDOTBS1 YES +DATA/TNT/DATAFILE/undotbs1.284.1115903031
5 50 UNDOTBS2 YES +DATA/TNT/DATAFILE/undotbs2.290.1115904057
7 16 USERS NO +DATA/TNT/DATAFILE/users.285.1115903033
8 100 TS_NOLOG NO +DATA/TNT/DATAFILE/ts_nolog.295.1119133025
9 100 TS_NOLOG NO +DATA/TNT/DATAFILE/ts_nolog.296.1119117123
10 100 TNT_TEST NO +DATA/TNT/DATAFILE/tnt_test.297.1119134057
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 35 TEMP 32767 +DATA/TNT/TEMPFILE/temp.289.1115903147
RMAN> exit
Recovery Manager complete.
[oracle@racsetn1 ~]$ rman target /
connected to target database: TNT (DBID=1427195521)
RMAN> list backup of datafile 10;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> list copy of datafile 10;
specification does not match any datafile copy in the repository
RMAN> exit
Recovery Manager complete.
[oracle@racsetn1 ~]$
Let we delete file from ASM as below.
[grid@racsetn1 ~]$ srvctl stop database -d TNT
[grid@racsetn1 ~]$ asmcmd
ASMCMD> cd +DATA/TNT/DATAFILE/
ASMCMD> rm TNT_TEST.297.1119134057
ASMCMD> exit
[grid@racsetn1 ~]$
Let we try to start DB as below and see if any erorr we are getting.
[grid@racsetn1 ~]$ srvctl start database -d TNT
PRCR-1079 : Failed to start resource ora.tnt.db
CRS-5017: The resource action "ora.tnt.db start" encountered the following error:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '+DATA/TNT/DATAFILE/tnt_test.297.1119134057'
. For details refer to "(:CLSN00107:)" in "/grid_home/app/oracle/diag/crs/racsetn1/crs/trace/crsd_oraagent_oracle.trc".
CRS-5017: The resource action "ora.tnt.db start" encountered the following error:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '+DATA/TNT/DATAFILE/tnt_test.297.1119134057'
. For details refer to "(:CLSN00107:)" in "/grid_home/app/oracle/diag/crs/racsetn2/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.tnt.db' on 'racsetn1' failed
CRS-2632: There are no more servers to try to place resource 'ora.tnt.db' on that would satisfy its placement policy
CRS-2674: Start of 'ora.tnt.db' on 'racsetn2' failed
In alert log as below.
not an Exadata system.
2022-10-26T22:40:53.154442+05:30
Errors in file /ora_home/app/oracle/diag/rdbms/tnt/TNT1/trace/TNT1_ora_21886.trc:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '+DATA/TNT/DATAFILE/tnt_test.297.1119134057'
2022-10-26T22:40:53.162824+05:30
Errors in file /ora_home/app/oracle/diag/rdbms/tnt/TNT1/trace/TNT1_ora_21886.trc:
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10: '+DATA/TNT/DATAFILE/tnt_test.297.1119134057'
ORA-1157 signalled during: ALTER DATABASE OPEN /* db agent *//* {1:47367:3547} */...
2022-10-26T22:40:57.016056+05:30
License high water mark = 1
2022-10-26T22:40:57.016456+05:30
Hence we need to start database in Mount mode manually as below
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2751463160 bytes
Fixed Size 8900344 bytes
Variable Size 637534208 bytes
Database Buffers 2097152000 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES'
OR (RECOVER IS NULL AND ERROR IS NOT NULL); 2 3 4
FILE# STATUS ERROR REC TABLESPACE_NAME NAME
---------- ------- ----------------------------------------------------------------- --- -------------------- ----------
10 ONLINE FILE NOT FOUND
Let we restore and recover datafile 10 as below.
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
SQL> alter database datafile 10 OFFLINE;
Database altered.
SQL> exit
[oracle@racsetn1 ~]$ rman target /
connected to target database: TNT (DBID=1427195521, not open)
RMAN> restore datafile 10;
Starting restore at 26-OCT-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=147 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=273 instance=TNT1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=397 instance=TNT1 device type=DISK
creating datafile file number=10 name=+DATA/TNT/DATAFILE/tnt_test.297.1119134057
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 26-OCT-22
RMAN> recover datafile 10;
Starting recover at 26-OCT-22
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-OCT-22
RMAN> report schema;
Report of database schema for database with db_unique_name TNT
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 920 SYSTEM *** +DATA/TNT/DATAFILE/system.282.1115902991
2 100 RO_TS *** +DATA/TNT/DATAFILE/ro_ts.294.1117385373
3 650 SYSAUX *** +DATA/TNT/DATAFILE/sysaux.283.1115903017
4 335 UNDOTBS1 *** +DATA/TNT/DATAFILE/undotbs1.284.1115903031
5 50 UNDOTBS2 *** +DATA/TNT/DATAFILE/undotbs2.290.1115904057
7 16 USERS *** +DATA/TNT/DATAFILE/users.285.1115903033
8 100 TS_NOLOG *** +DATA/TNT/DATAFILE/ts_nolog.295.1119133025
9 100 TS_NOLOG *** +DATA/TNT/DATAFILE/ts_nolog.296.1119117123
10 100 TNT_TEST *** +DATA/TNT/DATAFILE/tnt_test.297.1119134723
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 35 TEMP 32767 +DATA/TNT/TEMPFILE/temp.289.1115903147
RMAN> alter database datafile 10 online;
using target database control file instead of recovery catalog
Statement processed
RMAN> exit
Recovery Manager complete.
Cross verify details as below.
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter database open;
Database altered.
SQL> select count(*) from TNT_TEST;
COUNT(*)
----------
1
SQL> SELECT * FROM TNT_TEST;
IND
----------
10
SQL> SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES'
OR (RECOVER IS NULL AND ERROR IS NOT NULL); 2 3 4
no rows selected
SQL> SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
FROM V$RECOVER_FILE; 2
no rows selected
SQL> SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#; 2 3 4 5
no rows selected
SQL>
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444