RMAN Recovery Scenario - system datafile corrupted or lost - What to do?
Scenarios:
1. Database with valid backups
2. Database without valid backups
System datafile corrupted or lost happened:
1. When database is up and running
2. When database is down
High Level Steps:
RMAN - Database backup:
rman target /
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> backup database plus archivelog;
RMAN> backup database format='/u01/backup/backup_%d_%T_%U';
RMAN> backup archivelog all format='/u01/backup/archive_%d_%T_%U';
RMAN> list backup of tablespace system;
SQL> shut immediate
SQL> shut abort;
SQL> startup mount;
RMAN> restore datafile 1;
$ls -ld system*
SQL> select file#,checkpoint_change#, status, recover from v$datafile_header;
RMAN> recover datafile 1;
SQL> select checkpoint_change#, status, recover FROM v$datafile_header;
SQL> alter database open;
Scenarios #01: Database with valid backups:
1. check the database status:
[root@oraclelab1 ~]# su - oracle
Last login: Sat Jan 22 21:47:29 IST 2022 on pts/4
[oracle@oraclelab1 ~]$ . oraenv
ORACLE_SID = [oracle] ? DB
The Oracle base has been set to /u01/app/oracle
[oracle@oraclelab1 ~]$ ps -ef|grep smon
oracle 19675 1 0 Jan22 ? 00:00:00 ora_smon_DB
oracle 20043 1 0 Jan22 ? 00:00:00 ora_smon_DB12C
oracle 22420 22308 0 06:54 pts/5 00:00:00 grep --color=auto smon
[oracle@oraclelab1 ~]$ env |grep ORA
ORACLE_SID=DB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 23 07:04:26 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
LOST_WRITE_PROTECT VARCHAR2(7)
SQL>
SQL> set pages 1000 lines 1000
SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 USERS /u01/app/oracle/oradata/DB/datafile/o1_mf_users_jy5ypw5m_.dbf
4 UNDOTBS1 /u01/app/oracle/oradata/DB/datafile/o1_mf_undotbs1_jy5ypv3f_.dbf
1 SYSTEM /u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf
3 SYSAUX /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB READ WRITE PRIMARY
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
2. Take valid RMAN backup:
[oracle@oraclelab1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 23 07:06:25 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB (DBID=1857892946)
RMAN> list backup;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> show all;
RMAN configuration parameters for database with db_unique_name DB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/snapcf_DB.f'; # default
RMAN> backup database format='/u01/backup/backup_%d_%T_%U';
Starting backup at 23-JAN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=271 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/DB/datafile/o1_mf_undotbs1_jy5ypv3f_.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DB/datafile/o1_mf_users_jy5ypw5m_.dbf
channel ORA_DISK_1: starting piece 1 at 23-JAN-22
channel ORA_DISK_1: finished piece 1 at 23-JAN-22
piece handle=/u01/backup/backup_DB_20220123_010k00a9_1_1 tag=TAG20220123T070753 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 23-JAN-22
Starting Control File and SPFILE Autobackup at 23-JAN-22
piece handle=/u01/app/oracle/fast_recovery_area/DB/DB/autobackup/2022_01_23/o1_mf_s_1094713689_jysdd14d_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-JAN-22
RMAN> backup archivelog all format='/u01/backup/archive_%d_%T_%U';
Starting backup at 23-JAN-22
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=1 RECID=1 STAMP=1094103016
input archived log thread=1 sequence=2 RECID=2 STAMP=1094113803
input archived log thread=1 sequence=3 RECID=3 STAMP=1094133935
input archived log thread=1 sequence=4 RECID=4 STAMP=1094157434
input archived log thread=1 sequence=5 RECID=5 STAMP=1094189426
input archived log thread=1 sequence=6 RECID=6 STAMP=1094261676
input archived log thread=1 sequence=7 RECID=7 STAMP=1094344831
input archived log thread=1 sequence=8 RECID=8 STAMP=1094377988
input archived log thread=1 sequence=9 RECID=9 STAMP=1094378078
input archived log thread=1 sequence=10 RECID=10 STAMP=1094378144
input archived log thread=1 sequence=11 RECID=11 STAMP=1094378184
input archived log thread=1 sequence=12 RECID=12 STAMP=1094378322
input archived log thread=1 sequence=13 RECID=13 STAMP=1094378469
input archived log thread=1 sequence=14 RECID=14 STAMP=1094378597
input archived log thread=1 sequence=15 RECID=15 STAMP=1094378606
input archived log thread=1 sequence=16 RECID=16 STAMP=1094378661
input archived log thread=1 sequence=17 RECID=17 STAMP=1094378707
input archived log thread=1 sequence=18 RECID=18 STAMP=1094378854
input archived log thread=1 sequence=19 RECID=19 STAMP=1094378905
input archived log thread=1 sequence=20 RECID=20 STAMP=1094378941
input archived log thread=1 sequence=21 RECID=21 STAMP=1094378969
input archived log thread=1 sequence=22 RECID=22 STAMP=1094379011
input archived log thread=1 sequence=23 RECID=23 STAMP=1094379041
input archived log thread=1 sequence=24 RECID=24 STAMP=1094379149
input archived log thread=1 sequence=25 RECID=25 STAMP=1094379171
input archived log thread=1 sequence=26 RECID=26 STAMP=1094379192
input archived log thread=1 sequence=27 RECID=27 STAMP=1094379216
input archived log thread=1 sequence=28 RECID=28 STAMP=1094379737
input archived log thread=1 sequence=29 RECID=29 STAMP=1094680063
input archived log thread=1 sequence=30 RECID=30 STAMP=1094681859
input archived log thread=1 sequence=31 RECID=31 STAMP=1094713697
channel ORA_DISK_1: starting piece 1 at 23-JAN-22
channel ORA_DISK_1: finished piece 1 at 23-JAN-22
piece handle=/u01/backup/archive_DB_20220123_030k00b1_1_1 tag=TAG20220123T070817 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 23-JAN-22
Starting Control File and SPFILE Autobackup at 23-JAN-22
piece handle=/u01/app/oracle/fast_recovery_area/DB/DB/autobackup/2022_01_23/o1_mf_s_1094713732_jysdfdyn_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-JAN-22
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.48G DISK 00:00:08 23-JAN-22
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20220123T070753
Piece Name: /u01/backup/backup_DB_20220123_010k00a9_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2779544 23-JAN-22 NO /u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf
3 Full 2779544 23-JAN-22 NO /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf
4 Full 2779544 23-JAN-22 NO /u01/app/oracle/oradata/DB/datafile/o1_mf_undotbs1_jy5ypv3f_.dbf
7 Full 2779544 23-JAN-22 NO /u01/app/oracle/oradata/DB/datafile/o1_mf_users_jy5ypw5m_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 10.23M DISK 00:00:00 23-JAN-22
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20220123T070809
Piece Name: /u01/app/oracle/fast_recovery_area/DB/DB/autobackup/2022_01_23/o1_mf_s_1094713689_jysdd14d_.bkp
SPFILE Included: Modification time: 22-JAN-22
SPFILE db_unique_name: DB
Control File Included: Ckp SCN: 2779558 Ckp time: 23-JAN-22
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3 5.29G DISK 00:00:26 23-JAN-22
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20220123T070817
Piece Name: /u01/backup/archive_DB_20220123_030k00b1_1_1
List of Archived Logs in backup set 3
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 1408558 15-JAN-22 1436072 16-JAN-22
1 2 1436072 16-JAN-22 1479078 16-JAN-22
1 3 1479078 16-JAN-22 1518728 16-JAN-22
1 4 1518728 16-JAN-22 1559996 16-JAN-22
1 5 1559996 16-JAN-22 1604393 17-JAN-22
1 6 1604393 17-JAN-22 1659301 18-JAN-22
1 7 1659301 18-JAN-22 1715521 19-JAN-22
1 8 1715521 19-JAN-22 1757983 19-JAN-22
1 9 1757983 19-JAN-22 1905011 19-JAN-22
1 10 1905011 19-JAN-22 1991041 19-JAN-22
1 11 1991041 19-JAN-22 2013226 19-JAN-22
1 12 2013226 19-JAN-22 2091746 19-JAN-22
1 13 2091746 19-JAN-22 2118880 19-JAN-22
1 14 2118880 19-JAN-22 2143458 19-JAN-22
1 15 2143458 19-JAN-22 2144645 19-JAN-22
1 16 2144645 19-JAN-22 2204008 19-JAN-22
1 17 2204008 19-JAN-22 2264529 19-JAN-22
1 18 2264529 19-JAN-22 2306719 19-JAN-22
1 19 2306719 19-JAN-22 2350220 19-JAN-22
1 20 2350220 19-JAN-22 2361424 19-JAN-22
1 21 2361424 19-JAN-22 2396319 19-JAN-22
1 22 2396319 19-JAN-22 2411241 19-JAN-22
1 23 2411241 19-JAN-22 2443861 19-JAN-22
1 24 2443861 19-JAN-22 2506626 19-JAN-22
1 25 2506626 19-JAN-22 2511949 19-JAN-22
1 26 2511949 19-JAN-22 2517311 19-JAN-22
1 27 2517311 19-JAN-22 2528910 19-JAN-22
1 28 2528910 19-JAN-22 2594624 19-JAN-22
1 29 2594624 19-JAN-22 2705642 22-JAN-22
1 30 2705642 22-JAN-22 2746183 22-JAN-22
1 31 2746183 22-JAN-22 2779581 23-JAN-22
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 10.23M DISK 00:00:00 23-JAN-22
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20220123T070852
Piece Name: /u01/app/oracle/fast_recovery_area/DB/DB/autobackup/2022_01_23/o1_mf_s_1094713732_jysdfdyn_.bkp
SPFILE Included: Modification time: 22-JAN-22
SPFILE db_unique_name: DB
Control File Included: Ckp SCN: 2779608 Ckp time: 23-JAN-22
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$
3. simulate the failure - by deleting the system datafile:
[oracle@oraclelab1 backup]$ ll
total 7099220
-rw-r-----. 1 oracle oinstall 5681071616 Jan 23 07:08 archive_DB_20220123_030k00b1_1_1
-rw-r-----. 1 oracle oinstall 1588527104 Jan 23 07:08 backup_DB_20220123_010k00a9_1_1
[oracle@oraclelab1 backup]$ cd /u01/app/oracle/oradata/DB/datafile/
[oracle@oraclelab1 datafile]$ ll
total 3072500
-rw-r-----. 1 oracle oinstall 901783552 Jan 23 07:08 o1_mf_sysaux_jy5yp1yv_.dbf
-rw-r-----. 1 oracle oinstall 975183872 Jan 23 07:08 o1_mf_system_jy5ynyvd_.dbf
-rw-r-----. 1 oracle oinstall 137371648 Jan 23 06:00 o1_mf_temp_jy5yr5jv_.tmp
-rw-r-----. 1 oracle oinstall 1137713152 Jan 23 07:08 o1_mf_undotbs1_jy5ypv3f_.dbf
-rw-r-----. 1 oracle oinstall 5251072 Jan 23 07:08 o1_mf_users_jy5ypw5m_.dbf
[oracle@oraclelab1 datafile]$ rm o1_mf_system_jy5ynyvd_.dbf
[oracle@oraclelab1 datafile]$
[oracle@oraclelab1 datafile]$ locate alert_DB.log
/u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log
[oracle@oraclelab1 datafile]$ tail -f /u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log
instance mounted in exclusive mode.
2022-01-23T07:16:42.156017+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_ora_24134.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf'
2022-01-23T07:16:42.157739+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_ora_24134.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 23 07:11:42 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> shut immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> set lines 1000 pages 1000
SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 USERS /u01/app/oracle/oradata/DB/datafile/o1_mf_users_jy5ypw5m_.dbf
4 UNDOTBS1 /u01/app/oracle/oradata/DB/datafile/o1_mf_undotbs1_jy5ypv3f_.dbf
1 SYSTEM /u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf
3 SYSAUX /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf
SQL> shut immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 754974720 bytes
Database Buffers 1660944384 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf'
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
4. Restore and Recover the system datafile using valid backups:
[oracle@oraclelab1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 23 07:18:22 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB (DBID=1857892946, not open)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf
3 860 SYSAUX *** /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf
4 1085 UNDOTBS1 *** /u01/app/oracle/oradata/DB/datafile/o1_mf_undotbs1_jy5ypv3f_.dbf
7 5 USERS *** /u01/app/oracle/oradata/DB/datafile/o1_mf_users_jy5ypw5m_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 131 TEMP 32767 /u01/app/oracle/oradata/DB/datafile/o1_mf_temp_jy5yr5jv_.tmp
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.48G DISK 00:00:08 23-JAN-22
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20220123T070753
Piece Name: /u01/backup/backup_DB_20220123_010k00a9_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 2779544 23-JAN-22 NO /u01/app/oracle/oradata/DB/datafile/o1_mf_system_jy5ynyvd_.dbf
3 Full 2779544 23-JAN-22 NO /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf
4 Full 2779544 23-JAN-22 NO /u01/app/oracle/oradata/DB/datafile/o1_mf_undotbs1_jy5ypv3f_.dbf
7 Full 2779544 23-JAN-22 NO /u01/app/oracle/oradata/DB/datafile/o1_mf_users_jy5ypw5m_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 10.23M DISK 00:00:00 23-JAN-22
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20220123T070809
Piece Name: /u01/app/oracle/fast_recovery_area/DB/DB/autobackup/2022_01_23/o1_mf_s_1094713689_jysdd14d_.bkp
SPFILE Included: Modification time: 22-JAN-22
SPFILE db_unique_name: DB
Control File Included: Ckp SCN: 2779558 Ckp time: 23-JAN-22
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3 5.29G DISK 00:00:26 23-JAN-22
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20220123T070817
Piece Name: /u01/backup/archive_DB_20220123_030k00b1_1_1
List of Archived Logs in backup set 3
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 1 1408558 15-JAN-22 1436072 16-JAN-22
1 2 1436072 16-JAN-22 1479078 16-JAN-22
1 3 1479078 16-JAN-22 1518728 16-JAN-22
1 4 1518728 16-JAN-22 1559996 16-JAN-22
1 5 1559996 16-JAN-22 1604393 17-JAN-22
1 6 1604393 17-JAN-22 1659301 18-JAN-22
1 7 1659301 18-JAN-22 1715521 19-JAN-22
1 8 1715521 19-JAN-22 1757983 19-JAN-22
1 9 1757983 19-JAN-22 1905011 19-JAN-22
1 10 1905011 19-JAN-22 1991041 19-JAN-22
1 11 1991041 19-JAN-22 2013226 19-JAN-22
1 12 2013226 19-JAN-22 2091746 19-JAN-22
1 13 2091746 19-JAN-22 2118880 19-JAN-22
1 14 2118880 19-JAN-22 2143458 19-JAN-22
1 15 2143458 19-JAN-22 2144645 19-JAN-22
1 16 2144645 19-JAN-22 2204008 19-JAN-22
1 17 2204008 19-JAN-22 2264529 19-JAN-22
1 18 2264529 19-JAN-22 2306719 19-JAN-22
1 19 2306719 19-JAN-22 2350220 19-JAN-22
1 20 2350220 19-JAN-22 2361424 19-JAN-22
1 21 2361424 19-JAN-22 2396319 19-JAN-22
1 22 2396319 19-JAN-22 2411241 19-JAN-22
1 23 2411241 19-JAN-22 2443861 19-JAN-22
1 24 2443861 19-JAN-22 2506626 19-JAN-22
1 25 2506626 19-JAN-22 2511949 19-JAN-22
1 26 2511949 19-JAN-22 2517311 19-JAN-22
1 27 2517311 19-JAN-22 2528910 19-JAN-22
1 28 2528910 19-JAN-22 2594624 19-JAN-22
1 29 2594624 19-JAN-22 2705642 22-JAN-22
1 30 2705642 22-JAN-22 2746183 22-JAN-22
1 31 2746183 22-JAN-22 2779581 23-JAN-22
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 10.23M DISK 00:00:00 23-JAN-22
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20220123T070852
Piece Name: /u01/app/oracle/fast_recovery_area/DB/DB/autobackup/2022_01_23/o1_mf_s_1094713732_jysdfdyn_.bkp
SPFILE Included: Modification time: 22-JAN-22
SPFILE db_unique_name: DB
Control File Included: Ckp SCN: 2779608 Ckp time: 23-JAN-22
RMAN> restore datafile 1;
Starting restore at 23-JAN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 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/DB/datafile/o1_mf_system_jy5ynyvd_.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/backup_DB_20220123_010k00a9_1_1
channel ORA_DISK_1: piece handle=/u01/backup/backup_DB_20220123_010k00a9_1_1 tag=TAG20220123T070753
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 23-JAN-22
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 datafile]$ cd /u01/app/oracle/oradata/DB/datafile/
[oracle@oraclelab1 datafile]$
[oracle@oraclelab1 datafile]$ ls -ltrh
total 3.0G
-rw-r-----. 1 oracle oinstall 132M Jan 23 06:00 o1_mf_temp_jy5yr5jv_.tmp
-rw-r-----. 1 oracle oinstall 1.1G Jan 23 07:13 o1_mf_undotbs1_jy5ypv3f_.dbf
-rw-r-----. 1 oracle oinstall 861M Jan 23 07:13 o1_mf_sysaux_jy5yp1yv_.dbf
-rw-r-----. 1 oracle oinstall 5.1M Jan 23 07:13 o1_mf_users_jy5ypw5m_.dbf
-rw-r-----. 1 oracle oinstall 931M Jan 23 07:19 o1_mf_system_jysf1q6o_.dbf
[oracle@oraclelab1 datafile]$
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 23 07:20:31 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select file#,checkpoint_change#, status, recover from v$datafile_header;
FILE# CHECKPOINT_CHANGE# STATUS REC
---------- ------------------ ------- ---
1 2779544 ONLINE YES
3 2779746 ONLINE NO
4 2779746 ONLINE NO
7 2779746 ONLINE NO
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 23 07:21:29 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB (DBID=1857892946, not open)
RMAN> recover datafile 1;
Starting recover at 23-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-JAN-22
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 23 07:21:38 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select file#,checkpoint_change#, status, recover from v$datafile_header;
FILE# CHECKPOINT_CHANGE# STATUS REC
---------- ------------------ ------- ---
1 2879775 ONLINE NO
3 2779746 ONLINE NO
4 2779746 ONLINE NO
7 2779746 ONLINE NO
SQL> alter database open;
Database altered.
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB READ WRITE PRIMARY
SQL>
Scenarios #02: Database without valid backups:
1. check the database status:
[root@oraclelab1 ~]# su - oracle
Last login: Sun Jan 23 06:54:05 IST 2022 on pts/3
[oracle@oraclelab1 ~]$ . oraenv
ORACLE_SID = [oracle] ? DB12C
The Oracle base has been set to /u01/app/oracle
[oracle@oraclelab1 ~]$ ps -ef|grep smon
oracle 19675 1 0 Jan22 ? 00:00:00 ora_smon_DB
oracle 20043 1 0 Jan22 ? 00:00:00 ora_smon_DB12C
oracle 22425 22157 0 06:54 pts/1 00:00:00 grep --color=auto smon
[oracle@oraclelab1 ~]$ env |grep ORA
ORACLE_SID=DB12C
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 23 07:04:20 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set pages 1000 lines 1000
SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7 USERS /u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_jy62yx4v_.dbf
4 UNDOTBS1 /u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_jy62yw2w_.dbf
1 SYSTEM /u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_jy62wzod_.dbf
3 SYSAUX /u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_jy62y2xq_.dbf
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB12C READ WRITE PRIMARY
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oraclelab1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 23 07:06:10 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB12C (DBID=1548106259)
RMAN> list backup;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN>
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 23 07:10:16 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
2. simulate the failure - by deleting the system datafile:
[oracle@oraclelab1 ~]$ cd /u01/app/oracle/oradata/DB12C/datafile
[oracle@oraclelab1 datafile]$ ll
total 3164808
-rw-r-----. 1 oracle oinstall 996155392 Jan 23 07:10 o1_mf_sysaux_jy62y2xq_.dbf
-rw-r-----. 1 oracle oinstall 975183872 Jan 23 07:10 o1_mf_system_jy62wzod_.dbf
-rw-r-----. 1 oracle oinstall 137371648 Jan 23 06:00 o1_mf_temp_jy6306gw_.tmp
-rw-r-----. 1 oracle oinstall 1137713152 Jan 23 07:10 o1_mf_undotbs1_jy62yw2w_.dbf
-rw-r-----. 1 oracle oinstall 5251072 Jan 23 07:10 o1_mf_users_jy62yx4v_.dbf
[oracle@oraclelab1 datafile]$ rm o1_mf_system_jy62wzod_.dbf
[oracle@oraclelab1 datafile]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 23 07:15:11 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_jy62wzod_.dbf'
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_jy62wzod_.dbf'
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oraclelab1 ~]$ locate alert_DB12C.log
/u01/app/oracle/diag/rdbms/db12c/DB12C/trace/alert_DB12C.log
[oracle@oraclelab1 ~]$ tail -f /u01/app/oracle/diag/rdbms/db12c/DB12C/trace/alert_DB12C.log
2022-01-23T07:15:25.670153+05:30
Errors in file /u01/app/oracle/diag/rdbms/db12c/DB12C/trace/DB12C_ora_23964.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_jy62wzod_.dbf'
2022-01-23T07:15:25.670390+05:30
Errors in file /u01/app/oracle/diag/rdbms/db12c/DB12C/trace/DB12C_ora_23964.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_jy62wzod_.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
3. Try to restore system database without backup which is not possible: this database is lost forever since does not have valid backup:
[oracle@oraclelab1 datafile]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 23 07:23:28 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB12C (DBID=1548106259, not open)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name DB12C
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/DB12C/datafile/o1_mf_system_jy62wzod_.dbf
3 950 SYSAUX *** /u01/app/oracle/oradata/DB12C/datafile/o1_mf_sysaux_jy62y2xq_.dbf
4 1085 UNDOTBS1 *** /u01/app/oracle/oradata/DB12C/datafile/o1_mf_undotbs1_jy62yw2w_.dbf
7 5 USERS *** /u01/app/oracle/oradata/DB12C/datafile/o1_mf_users_jy62yx4v_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 131 TEMP 32767 /u01/app/oracle/oradata/DB12C/datafile/o1_mf_temp_jy6306gw_.tmp
RMAN> list backup;
specification does not match any backup in the repository
RMAN> restore datafile 1;
Starting restore at 23-JAN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/23/2022 07:23:52
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 1 found to restore
RMAN>
Regards,
Mallik
No comments:
Post a Comment