Database RAC Dataguard RMAN Performance Tuning Feedback
Database Migration & Upgradation
DataGuard
ASM & RAC
Exadata
RMAN Backup & Recovery
Oracle Cloud AWS Azure Google Cloud
DevOps Ansible Kubernetes Terraform
Shell Scripting Powershell Scripting
Infomratica Others

Sunday, January 23, 2022

RMAN Recovery Scenario - system datafile corrupted or lost - What to do?

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

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Automation Script | Archivelog Generation Hourly Monitoring

1. List out all the running databases and pic one database where we want to monitore the archive log generation from last 1 month. [oracle@o...