MAN Recovery Scenario - sysaux 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;
Not Mandatory to shutdown:
SQL> shut immediate
SQL> shut abort;
SQL> startup mount;
col occupant_name format a30
col occupant_desc format a40
col schema_name format a15
set linesize 200
select occupant_name,occupant_desc,schema_name,space_usage_kbytes/1024 from v$sysaux_occupants;
Scenario 1: (When DB was up and running & has valid backup)
drop tablespace sysaux; >>> Not possible
alter tablespace sysaux read only; >>> Not possible
alter tablespace sysaux offline; >>> Not possible
RMAN> alter database datafile 3 offline;
RMAN> restore datafile 3;
$ls -ld sysaux*
SQL> select file#,checkpoint_change#, status, recover from v$datafile_header;
RMAN> recover datafile 3;
RMAN> alter database datafile 3 online;
SQL> select checkpoint_change#, status, recover FROM v$datafile_header;
SQL> alter database open;
Scenario 2: (When DB was down and No valid backups)
SQL> startup; >>> Not possible
drop tablespace sysaux; >>> Not possible
alter tablespace sysaux read only; >>> Not possible
alter tablespace sysaux offline; >>> Not possible
RMAN> alter database datafile 3 offline;
RMAN> restore datafile 3; >>> which will fail since we don’t have valid backup
RMAN> recover datafile 3; >>> which will fail since we don’t have valid backup
RMAN> alter database datafile 3 online; >>> Fails with unable to find a file
sqlplus / as sysdba
alter database open;
select name, open_mode from v$database; >>> Able to open the DB
Scenario 1: (When DB was up and running & has valid backup)
1. Check the database status:
[oracle@oraclelab1 ~]$ ps -ef|grep smon
oracle 8505 1 0 Feb03 ? 00:00:09 ora_smon_DEVDB
oracle 8665 1 0 13:28 ? 00:00:00 ora_smon_DB
oracle 9206 1 0 13:28 ? 00:00:00 ora_smon_DB12C
oracle 11704 9511 0 13:54 pts/1 00:00:00 grep --color=auto smon
[oracle@oraclelab1 ~]$
[oracle@oraclelab1 ~]$ . oraenv
ORACLE_SID = [DEVDB] ? DEVDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclelab1 ~]$ env |grep ORA
ORACLE_SID=DEVDB
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
[oracle@oraclelab1 ~]$
2. Take valid RMAN backup:
[oracle@oraclelab1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 10 13:55:42 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEVDB (DBID=996518900)
RMAN> list backup;
using target database control file instead of recovery catalog
specification does not match any backup in the repository
RMAN> backup database format='/u01/backup/backup_%d_%T_%U';
Starting backup at 10-FEB-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 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=00003 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/DEVDB/bigtbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/DEVDB/test4.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/DEVDB/test4_1.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/DEVDB/test5.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DEVDB/test2.dbf
channel ORA_DISK_1: starting piece 1 at 10-FEB-22
channel ORA_DISK_1: finished piece 1 at 10-FEB-22
piece handle=/u01/backup/backup_DEVDB_20220210_e30lg705_1_1 tag=TAG20220210T135620 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 10-FEB-22
Starting Control File and SPFILE Autobackup at 10-FEB-22
piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_02_10/o1_mf_s_1096293396_k09m1wdr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-FEB-22
RMAN> backup archivelog all format='/u01/backup/archive_%d_%T_%U';
Starting backup at 10-FEB-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=308 RECID=306 STAMP=1096293417
channel ORA_DISK_1: starting piece 1 at 10-FEB-22
channel ORA_DISK_1: finished piece 1 at 10-FEB-22
piece handle=/u01/backup/archive_DEVDB_20220210_e50lg719_1_1 tag=TAG20220210T135657 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-22
Starting Control File and SPFILE Autobackup at 10-FEB-22
piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_02_10/o1_mf_s_1096293418_k09m2ldq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-FEB-22
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
415 Full 2.09G DISK 00:00:10 10-FEB-22
BP Key: 415 Status: AVAILABLE Compressed: NO Tag: TAG20220210T135620
Piece Name: /u01/backup/backup_DEVDB_20220210_e30lg705_1_1
List of Datafiles in backup set 415
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf
2 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/test2.dbf
3 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf
4 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf
5 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf
7 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf
8 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf
9 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/test4.dbf
10 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/test5.dbf
11 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/test4_1.dbf
12 Full 6903774 10-FEB-22 NO /u01/app/oracle/oradata/DEVDB/bigtbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
416 Full 11.80M DISK 00:00:00 10-FEB-22
BP Key: 416 Status: AVAILABLE Compressed: NO Tag: TAG20220210T135636
Piece Name: /u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_02_10/o1_mf_s_1096293396_k09m1wdr_.bkp
SPFILE Included: Modification time: 03-FEB-22
SPFILE db_unique_name: DEVDB
Control File Included: Ckp SCN: 6903787 Ckp time: 10-FEB-22
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
417 375.50K DISK 00:00:00 10-FEB-22
BP Key: 417 Status: AVAILABLE Compressed: NO Tag: TAG20220210T135657
Piece Name: /u01/backup/archive_DEVDB_20220210_e50lg719_1_1
List of Archived Logs in backup set 417
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 308 6902434 10-FEB-22 6903818 10-FEB-22
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
418 Full 11.80M DISK 00:00:00 10-FEB-22
BP Key: 418 Status: AVAILABLE Compressed: NO Tag: TAG20220210T135658
Piece Name: /u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2022_02_10/o1_mf_s_1096293418_k09m2ldq_.bkp
SPFILE Included: Modification time: 03-FEB-22
SPFILE db_unique_name: DEVDB
Control File Included: Ckp SCN: 6903831 Ckp time: 10-FEB-22
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$
3. Simulate the failure - by deleting the sysaux datafile:
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 13:57:34 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>
SQL> set pages 1000 lines 1000
SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ---------------------------------------------------------------------
SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf
SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf
UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf
USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf
TEST1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf
TEST2 /u01/app/oracle/oradata/DEVDB/test2.dbf
TEST3 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf
TEST4 /u01/app/oracle/oradata/DEVDB/test4.dbf
TEST4 /u01/app/oracle/oradata/DEVDB/test4_1.dbf
TEST5 /u01/app/oracle/oradata/DEVDB/test5.dbf
BIGTBS /u01/app/oracle/oradata/DEVDB/bigtbs01.dbf
11 rows selected.
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@oraclelab1 ~]$ rm /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf
[oracle@oraclelab1 ~]$ ls -ld /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf
ls: cannot access /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf: No such file or directory
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 13:59:10 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>
SQL> create user mallik11 identified by mallik11;
User created.
SQL>
SQL> grant dba to mallik11;
Grant succeeded.
SQL> conn mallik11/mallik11;
Connected.
SQL> show user
USER is "MALLIK11"
SQL>
SQL>
SQL> create table test1 (SLNO number(10));
Table created.
SQL> select * from test1;
no rows selected
SQL> insert into test1 values(1);
insert into test1 values(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 3:
'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> create table test2 (SLNO number(10));
Table created.
SQL> insert into test2 values(1);
insert into test2 values(1)
*
ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 3:
'/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
4. Check alert log & verify:
[oracle@oraclelab1 backup]$ locate alert_DEVDB.log
/u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log
[oracle@oraclelab1 backup]$ tail -100f /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log
2022-02-10T14:02:25.265719+05:30
Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_12572.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 1 new persistent data failures
2022-02-10T14:03:27.630686+05:30
Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_12654.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2022-02-10T14:08:06.341642+05:30
SQL> select occupant_name , occupant_desc , schema_name , space_usage_kbytes/1024 from v$sysaux_occupants ;
SQL> drop tablespace sysaux;
drop tablespace sysaux
*
ERROR at line 1:
ORA-13501: Cannot drop SYSAUX tablespace
SQL> alter tablespace sysaux read only;
alter tablespace sysaux read only
*
ERROR at line 1:
ORA-13505: SYSAUX tablespace can not be made read only
SQL>
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEST1 ONLINE
TEST2 ONLINE
TEST3 ONLINE
TEST4 ONLINE
TEST5 ONLINE
BIGTBS ONLINE
11 rows selected.
SQL> alter tablespace sysaux offline;
alter tablespace sysaux offline
*
ERROR at line 1:
ORA-13560: SYSAUX tablespace cannot be brought offline; shut down if necessary
SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;
ALTER TABLESPACE SYSAUX OFFLINE NORMAL
*
ERROR at line 1:
ORA-13560: SYSAUX tablespace cannot be brought offline; shut down if necessary
SQL>
SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ ---------------------------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf
3 SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf
4 UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf
7 USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf
5 TEST1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf
2 TEST2 /u01/app/oracle/oradata/DEVDB/test2.dbf
8 TEST3 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf
9 TEST4 /u01/app/oracle/oradata/DEVDB/test4.dbf
11 TEST4 /u01/app/oracle/oradata/DEVDB/test4_1.dbf
10 TEST5 /u01/app/oracle/oradata/DEVDB/test5.dbf
12 BIGTBS /u01/app/oracle/oradata/DEVDB/bigtbs01.dbf
11 rows selected.
SQL> alter database datafile 3 offline;
Database altered.
SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------------------ -----------------------------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf
3 SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf
4 UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf
7 USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf
5 TEST1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf
2 TEST2 /u01/app/oracle/oradata/DEVDB/test2.dbf
8 TEST3 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf
9 TEST4 /u01/app/oracle/oradata/DEVDB/test4.dbf
11 TEST4 /u01/app/oracle/oradata/DEVDB/test4_1.dbf
10 TEST5 /u01/app/oracle/oradata/DEVDB/test5.dbf
12 BIGTBS /u01/app/oracle/oradata/DEVDB/bigtbs01.dbf
11 rows selected.
SQL>
SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME, status from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME STATUS
--------------------------------------- ------------------------------------------------------------------- --------------------------
1 SYSTEM /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_jxwqbjc1_.dbf AVAILABLE
3 SYSAUX /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf AVAILABLE
4 UNDOTBS1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_jxwqbjc5_.dbf AVAILABLE
7 USERS /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_users_jxwqbjc8_.dbf AVAILABLE
5 TEST1 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test1_jxwqbzfw_.dbf AVAILABLE
2 TEST2 /u01/app/oracle/oradata/DEVDB/test2.dbf AVAILABLE
8 TEST3 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_test3_jxwqbzg1_.dbf AVAILABLE
9 TEST4 /u01/app/oracle/oradata/DEVDB/test4.dbf AVAILABLE
11 TEST4 /u01/app/oracle/oradata/DEVDB/test4_1.dbf AVAILABLE
10 TEST5 /u01/app/oracle/oradata/DEVDB/test5.dbf AVAILABLE
12 BIGTBS /u01/app/oracle/oradata/DEVDB/bigtbs01.dbf AVAILABLE
11 rows selected.
SQL>
[oracle@oraclelab1 ~]$ ls -ld /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf
ls: cannot access /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf: No such file or directory
[oracle@oraclelab1 ~]$
5. Restore and Recover the sysaux datafile using valid backups:
[oracle@oraclelab1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 10 14:16:18 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEVDB (DBID=996518900)
RMAN> restore datafile 3;
Starting restore at 10-FEB-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 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 00003 to /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/backup_DEVDB_20220210_e30lg705_1_1
channel ORA_DISK_1: piece handle=/u01/backup/backup_DEVDB_20220210_e30lg705_1_1 tag=TAG20220210T135620
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 10-FEB-22
RMAN>
[oracle@oraclelab1 ~]$ cd /u01/app/oracle/oradata/DEVDB/datafile/
[oracle@oraclelab1 datafile]$ ls -ltrh
total 3.0G
-rw-r-----. 1 oracle oinstall 21M Feb 9 22:00 o1_mf_temp_jxwqgc0c_.tmp
-rw-r-----. 1 oracle oinstall 101M Feb 10 13:56 o1_mf_test1_jxwqbzfw_.dbf
-rw-r-----. 1 oracle oinstall 101M Feb 10 13:56 o1_mf_test3_jxwqbzg1_.dbf
-rw-r-----. 1 oracle oinstall 7.6M Feb 10 14:07 o1_mf_users_jxwqbjc8_.dbf
-rw-r-----. 1 oracle oinstall 336M Feb 10 14:15 o1_mf_undotbs1_jxwqbjc5_.dbf
-rw-r-----. 1 oracle oinstall 941M Feb 10 14:15 o1_mf_system_jxwqbjc1_.dbf
-rw-r-----. 1 oracle oinstall 1.5G Feb 10 14:16 o1_mf_sysaux_k09n7c1v_.dbf
[oracle@oraclelab1 datafile]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 10 14:18:02 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEVDB (DBID=996518900)
RMAN> recover datafile 3;
Starting recover at 10-FEB-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 10-FEB-22
RMAN> alter database datafile 3 online;
Statement processed
RMAN>
[oracle@oraclelab1 backup]$ locate alert_DEVDB.log
/u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log
[oracle@oraclelab1 backup]$ tail -100f /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/alert_DEVDB.log
2022-02-10T14:08:06.341642+05:30
drop tablespace sysaux
ORA-13501 signalled during: drop tablespace sysaux...
2022-02-10T14:08:17.041134+05:30
alter tablespace sysaux read only
ORA-13505 signalled during: alter tablespace sysaux read only...
2022-02-10T14:08:49.537798+05:30
Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_13076.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2022-02-10T14:09:07.656647+05:30
alter tablespace sysaux offline
ORA-13560 signalled during: alter tablespace sysaux offline...
2022-02-10T14:09:32.657823+05:30
ALTER TABLESPACE SYSAUX OFFLINE NORMAL
ORA-13560 signalled during: ALTER TABLESPACE SYSAUX OFFLINE NORMAL...
2022-02-10T14:14:14.576930+05:30
alter database datafile 3 offline
Completed: alter database datafile 3 offline
2022-02-10T14:14:14.782478+05:30
Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_smon_8505.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
2022-02-10T14:14:14.804047+05:30
Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_14086.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2022-02-10T14:14:20.823781+05:30
Unified Audit record write to audit trail table failed due to ORA-1658. Writing the record to OS spillover file.
2022-02-10T14:15:53.693433+05:30
Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_14266.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_jxwqbjbr_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2022-02-10T14:16:43.599308+05:30
Full restore complete of datafile 3 /u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_k09n7c1v_.dbf. Elapsed time: 0:00:08
checkpoint is 6903774
last deallocation scn is 6879007
2022-02-10T14:16:54.139721+05:30
Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_14361.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_k09n7c1v_.dbf'
ORA-01208: data file is an old version - not accessing current version
Checker run found 1 new persistent data failures
2022-02-10T14:17:54.334324+05:30
Errors in file /u01/app/oracle/diag/rdbms/devdb/DEVDB/trace/DEVDB_mz00_14448.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_k09n7c1v_.dbf'
ORA-01208: data file is an old version - not accessing current version
2022-02-10T14:18:09.496943+05:30
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover
if needed datafile 3
2022-02-10T14:18:09.500057+05:30
Media Recovery Start
2022-02-10T14:18:09.501718+05:30
Serial Media Recovery started
2022-02-10T14:18:09.545055+05:30
Recovery of Online Redo Log: Thread 1 Group 2 Seq 308 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_2_jw044ygn_.log
Mem# 1: /u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_2_jw044zmf_.log
2022-02-10T14:18:09.551263+05:30
Recovery of Online Redo Log: Thread 1 Group 3 Seq 309 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/DEVDB/onlinelog/o1_mf_3_jw044yj7_.log
Mem# 1: /u01/app/oracle/fast_recovery_area/DEVDB/onlinelog/o1_mf_3_jw044zm5_.log
2022-02-10T14:18:09.922722+05:30
Media Recovery Complete (DEVDB)
Completed: alter database recover
if needed datafile 3
2022-02-10T14:18:41.070655+05:30
alter database datafile 3 online
Completed: alter database datafile 3 online
2022-02-10T14:25:59.965374+05:30
6. Post verification:
[oracle@oraclelab1 datafile]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 14:18:49 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> conn mallik11/mallik11;
Connected.
SQL> insert into test2 values(1);
1 row created.
SQL>
Scenario 2: (When DB was down and No valid backups)
1. Check the database status:
[oracle@oraclelab1 ~]$ ps -ef|grep smon
oracle 8505 1 0 Feb03 ? 00:00:09 ora_smon_DEVDB
oracle 8665 1 0 13:28 ? 00:00:00 ora_smon_DB
oracle 9206 1 0 13:28 ? 00:00:00 ora_smon_DB12C
oracle 11704 9511 0 13:54 pts/1 00:00:00 grep --color=auto smon
[oracle@oraclelab1 ~]$. oraenv
ORACLE_SID = [DEVDB] ? DB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oraclelab1 ~]$
[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 ~]$
2. check rman level no valid backups
[oracle@oraclelab1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 10 13:36:04 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>
2. Simulate the failure by shutting down the DB and deleting the sysaux datafile:
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 13:56:37 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 TABLESPACE_NAME,FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ -------------------------------------------------------------------
USERS /u01/app/oracle/oradata/DB/datafile/o1_mf_users_jy5ypw5m_.dbf
UNDOTBS1 /u01/app/oracle/oradata/DB/datafile/o1_mf_undotbs1_jy5ypv3f_.dbf
SYSTEM /u01/app/oracle/oradata/DB/datafile/o1_mf_system_jysf1q6o_.dbf
SYSAUX /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf
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
[oracle@oraclelab1 ~]$ rm -rf /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf
[oracle@oraclelab1 ~]$ ls -ld /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf
ls: cannot access /u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf: No such file or directory
[oracle@oraclelab1 ~]$
3. Try to start the DB which will report missing sysaux datafile:
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 13:59:43 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 788529152 bytes
Database Buffers 1627389952 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3:
'/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
SQL>
[root@oraclelab1 ~]# locate alert_DB.log
/u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log
[root@oraclelab1 ~]# tail -100f /u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log
Starting background process PXMN
2022-02-10T13:59:52.902334+05:30
PXMN started with pid=30, OS id=12234
Starting background process FENC
2022-02-10T13:59:52.914050+05:30
FENC started with pid=31, OS id=12236
Starting background process MMON
2022-02-10T13:59:52.927196+05:30
MMON started with pid=32, OS id=12238
Starting background process MMNL
2022-02-10T13:59:52.943815+05:30
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
2022-02-10T13:59:52.944173+05:30
MMNL started with pid=33, OS id=12240
starting up 1 shared server(s) ...
Starting background process TMON
2022-02-10T13:59:52.992130+05:30
TMON started with pid=35, OS id=12246
Setting CPU count to 2
ORACLE_BASE from environment = /u01/app/oracle
2022-02-10T13:59:53.109922+05:30
ALTER DATABASE MOUNT
2022-02-10T13:59:55.713915+05:30
Using default pga_aggregate_limit of 2048 MB
2022-02-10T13:59:57.203072+05:30
.... (PID:12247): Redo network throttle feature is disabled at mount time
2022-02-10T13:59:57.223266+05:30
Successful mount of redo thread 1, with mount id 1860126297
2022-02-10T13:59:57.224119+05:30
Database mounted in Exclusive Mode
Lost write protection disabled
.... (PID:12247): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:18157]
Completed: ALTER DATABASE MOUNT
2022-02-10T13:59:57.358743+05:30
ALTER DATABASE OPEN
2022-02-10T13:59:57.427045+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_mz00_12260.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2022-02-10T13:59:57.436879+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_dbw0_12211.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Ping without log force is disabled:
instance mounted in exclusive mode.
4. Try to restore and recover sysaux datafile which is not possible since don't have valid backup:
SQL> drop tablespace sysaux;
drop tablespace sysaux
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter tablespace sysaux read only;
alter tablespace sysaux read only
*
ERROR at line 1:
ORA-01109: database not open
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
select TABLESPACE_NAME,STATUS from dba_tablespaces
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only
SQL> alter tablespace sysaux offline;
alter tablespace sysaux offline
*
ERROR at line 1:
ORA-01109: database not open
SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;
ALTER TABLESPACE SYSAUX OFFLINE NORMAL
*
ERROR at line 1:
ORA-01109: database not open
SQL>
[oracle@oraclelab1 rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 10 14:21:49 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> alter database datafile 3 offline;
using target database control file instead of recovery catalog
Statement processed
RMAN> restore datafile 3;
Starting restore at 10-FEB-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/10/2022 14:22:08
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN> recover datafile 3;
Starting recover at 10-FEB-22
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/10/2022 14:22:15
RMAN-06094: datafile 3 must be restored
RMAN> exit
Recovery Manager complete.
[oracle@oraclelab1 ~]$
5. Try to make sysaux datafile offline and it will fail however still we are able to open the database but which is no use since we are unable to restore the sysaux datafile
RMAN> alter database datafile 3 online;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 02/10/2022 15:19:33
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
RMAN>
[oracle@oraclelab1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 10 14:22:22 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>
SQL> alter database open;
Database altered.
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
DB READ WRITE
SQL>
[root@oraclelab1 ~]# locate alert_DB.log
/u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log
[root@oraclelab1 ~]# tail -100f /u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log
2022-02-10T13:59:57.358743+05:30
ALTER DATABASE OPEN
2022-02-10T13:59:57.427045+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_mz00_12260.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2022-02-10T13:59:57.436879+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_dbw0_12211.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Ping without log force is disabled:
instance mounted in exclusive mode.
2022-02-10T13:59:57.458755+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_ora_12258.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
2022-02-10T13:59:57.461572+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_ora_12258.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
Checker run found 1 new persistent data failures
2022-02-10T14:00:26.229324+05:30
TMON (PID:12246): STARTING ARCH PROCESSES
Starting background process ARC0
2022-02-10T14:00:26.248838+05:30
ARC0 started with pid=38, OS id=12385
Starting background process ARC1
2022-02-10T14:00:26.263011+05:30
ARC1 started with pid=39, OS id=12387
Starting background process ARC2
Starting background process ARC3
2022-02-10T14:00:26.280617+05:30
ARC2 started with pid=40, OS id=12389
2022-02-10T14:00:26.297428+05:30
ARC3 started with pid=41, OS id=12391
TMON (PID:12246): ARC0: Archival started
TMON (PID:12246): ARC1: Archival started
TMON (PID:12246): ARC2: Archival started
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
SQL>
Try to add new datafile which will not allow:
SQL> alter tablespace SYSAUX add datafile'/u01/app/oracle/oradata/DB/datafile/sysaux01.dbf' size 1G autoextend on next 1024M;
alter tablespace SYSAUX add datafile'/u01/app/oracle/oradata/DB/datafile/sysaux01.dbf' size 1G autoextend on next 1024M
*
ERROR at line 1:
ORA-28369: cannot add files to encryption-ready tablespace when offline
SQL>
Drop the datafile:
SQL> alter database datafile 3 offline drop;
Database altered.
SQL>
SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
SQL>
[root@oraclelab1 ~]# locate alert_DB.log
/u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log
[root@oraclelab1 ~]# tail -100f /u01/app/oracle/diag/rdbms/db/DB/trace/alert_DB.log
2022-02-10T14:21:57.024476+05:30
alter database datafile 3 offline
Completed: alter database datafile 3 offline
2022-02-10T14:22:08.724551+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_mz00_14914.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 1 new persistent data failures
2022-02-10T14:22:28.298242+05:30
alter database open
2022-02-10T14:22:28.304166+05:30
Ping without log force is disabled:
instance mounted in exclusive mode.
Endian type of dictionary set to little
2022-02-10T14:22:28.320603+05:30
Redo log for group 1, sequence 43 is not located on DAX storage
Thread 1 opened at log sequence 43
Current log# 1 seq# 43 mem# 0: /u01/app/oracle/oradata/DB/onlinelog/o1_mf_1_jy5yqwtr_.log
Current log# 1 seq# 43 mem# 1: /u01/app/oracle/fast_recovery_area/DB/DB/onlinelog/o1_mf_1_jy5yqygd_.log
Successful open of redo thread 1
2022-02-10T14:22:28.334462+05:30
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
Undo initialization recovery: err:0 start: 609920243 end: 609920247 diff: 4 ms (0.0 seconds)
[14937] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 609920247 end: 609920316 diff: 69 ms (0.1 seconds)
Undo initialization finished serial:0 start:609920243 end:609920324 diff:81 ms (0.1 seconds)
Verifying minimum file header compatibility for tablespace encryption..
Verifying file header compatibility for tablespace encryption completed for pdb 0
Database Characterset is AL32UTF8
No Resource Manager plan active
FastStart is disabled because SYSAUX was offline during DB startup. Restart DB with SYSAUX online
joxcsys_required_dirobj_exists: directory object exists with required path /u01/app/oracle/product/19.0.0.0/dbhome_1/javavm/admin/, pid 14937 cid 0
replication_dependency_tracking turned off (no async multimaster replication found)
XDB UNINITIALIZED: XDB$SCHEMA not accessible
2022-02-10T14:22:29.321862+05:30
Starting background process AQPC
2022-02-10T14:22:29.342739+05:30
AQPC started with pid=44, OS id=14948
Starting background process CJQ0
2022-02-10T14:22:29.657602+05:30
CJQ0 started with pid=46, OS id=14952
Completed: alter database open
2022-02-10T14:22:30.221550+05:30
ORA-376 encountered when generating server alert SMG-4120
QPI: opatch file present, opatch
QPI: qopiprep.bat file present
Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 2327
ORA-06512: at "SYS.DBMS_QOPATCH", line 854
ORA-06512: at "SYS.DBMS_QOPATCH", line 937
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_QOPATCH", line 932
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-06512: at "SYS.DBMS_QOPATCH", line 918
ORA-06512: at "SYS.DBMS_QOPATCH", line 2286
ORA-06512: at "SYS.DBMS_QOPATCH", line 817
ORA-06512: at "SYS.DBMS_QOPATCH", line 2309
===========================================================
Dumping current patch information
===========================================================
Unable to obtain current patch information due to error: 20001
===========================================================
2022-02-10T14:24:02.226404+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_mz01_15142.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
2022-02-10T14:25:02.504644+05:30
Errors in file /u01/app/oracle/diag/rdbms/db/DB/trace/DB_mz01_15226.trc:
ORA-01110: data file 3: '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/DB/datafile/o1_mf_sysaux_jy5yp1yv_.dbf'
Regards,
Mallik
Thanks for sharing this wonderful information
ReplyDeleteAWS Online Training in India
AWS Online Training in Hyderabad
You are welcome
Delete