KRISHNA KUMAR VALUPADASU
RESTORE AND RECOVERING A SYSTEM DATAFILE OF CDB$ROOT CONTAINER DATABASE 19C
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 31 17:20:29 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------
D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF -- DELETED SYSTEM DATAFILE
D:\ORACLE19\DB19C\ORADATA\ORCL\SYSAUX01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\USERS01.DBF
D:\ORACLE19\DB19C\DBFILE\KRISHNA.DBF
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SHUTDOWN THE DATABASE:
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1073739928 bytes
Fixed Size 9275544 bytes
Variable Size 591396864 bytes
Database Buffers 465567744 bytes
Redo Buffers 7499776 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF'
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 31 17:20:29 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
Total System Global Area 1073739928 bytes
Fixed Size 9275544 bytes
Variable Size 591396864 bytes
Database Buffers 465567744 bytes
Redo Buffers 7499776 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF'
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
- Production
Version 19.3.0.0.0
DATABASE ALERT SHOWS FOLLOWING DETAILS:
Completed: ALTER DATABASE MOUNT
ALTER DATABASE OPEN
Errors in file
D:\ORACLE19\DB19C\diag\rdbms\orcl\orcl\trace\orcl_mz00_16156.trc:
ORA-01110: data file 1: 'D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF'
ORA-01565: error in identifying file
'D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file
D:\ORACLE19\DB19C\diag\rdbms\orcl\orcl\trace\orcl_dbw0_16516.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Ping without log force is disabled:
instance mounted in exclusive mode.
Errors in file
D:\ORACLE19\DB19C\diag\rdbms\orcl\orcl\trace\orcl_ora_26792.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF'
Errors in file
D:\ORACLE19\DB19C\diag\rdbms\orcl\orcl\trace\orcl_ora_26792.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1157 signalled during: ALTER DATABASE OPEN...
CONNECT TO RMAN TO RESTORE AND RECOVER THE DROPPED SYSTEM DATAFILE:
rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Oct 31 17:22:29
2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights
reserved.
connected to target database: ORCL (DBID=1709447759, not open)
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
RMAN> RUN {
2> RESTORE DATAFILE 'D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF';
3> RECOVER DATAFILE 'D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF';
4> }
Starting restore at 31-OCT-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=251 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
D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: reading from backup piece
D:\ORACLE19\FB\ORCL\BACKUPSET\2024_10_28\O1_MF_NNNDF_TAG20241028T162550_ML0
0BZ3L_.BKP
channel ORA_DISK_1: piece
handle=D:\ORACLE19\FB\ORCL\BACKUPSET\2024_10_28\O1_MF_NNNDF_TAG20241028T162
550_ML00BZ3L_.BKP tag=TAG20241028T162550
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 31-OCT-24
Starting recover at 31-OCT-24
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file
D:\ORACLE19\FB\ORCL\ARCHIVELOG\2024_10_28\O1_MF_1_7_ML0RK3KB_.ARC
archived log for thread 1 with sequence 8 is already on disk as file
D:\ORACLE19\FB\ORCL\ARCHIVELOG\2024_10_29\O1_MF_1_8_ML38CCB0_.ARC
archived log for thread 1 with sequence 9 is already on disk as file
D:\ORACLE19\FB\ORCL\ARCHIVELOG\2024_10_30\O1_MF_1_9_ML3L4G6B_.ARC
archived log for thread 1 with sequence 10 is already on disk as file
D:\ORACLE19\FB\ORCL\ARCHIVELOG\2024_10_31\O1_MF_1_10_ML7TY1HR_.ARC
archived log file
name=D:\ORACLE19\FB\ORCL\ARCHIVELOG\2024_10_28\O1_MF_1_7_ML0RK3KB_.ARC
thread=1 sequence=7
archived log file
name=D:\ORACLE19\FB\ORCL\ARCHIVELOG\2024_10_29\O1_MF_1_8_ML38CCB0_.ARC
thread=1 sequence=8
media recovery complete, elapsed time: 00:00:12
Finished recover at 31-OCT-24
RMAN> exit
Recovery Manager complete.
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 31 17:25:34 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select open_mode,log_mode from v$database;
OPEN_MODE LOG_MODE
-------------------- ------------
MOUNTED ARCHIVELOG
SQL> alter database open;
Database altered.
SQL> select open_mode,log_mode from v$database;
OPEN_MODE LOG_MODE
-------------------- ------------
READ WRITE ARCHIVELOG
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB MOUNTED
SQL> alter pluggable database ORCLPDB open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------
D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF -- RESTORED AND RECOVERED
D:\ORACLE19\DB19C\ORADATA\ORCL\SYSAUX01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\UNDOTBS01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\USERS01.DBF
D:\ORACLE19\DB19C\DBFILE\KRISHNA.DBF
Happy Learning☺
KRISHNA KUMAR VALUPADASU
