Skip to main content

RESTORE AND RECOVERING A SYSTEM DATAFILE OF CDB$ROOT CONTAINER DATABASE 19C

 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.

 

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)

 

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_ML00BZ3L_.BKP

channel ORA_DISK_1: piece handle=D:\ORACLE19\FB\ORCL\BACKUPSET\2024_10_28\O1_MF_NNNDF_TAG20241028T162550_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

 

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 LearningJ

 

x

x

Comments

Post a Comment

Popular posts from this blog

ORA-28007: THE PASSWORD CANNOT NE REUSED

             - Here will see how to deal with  => ORA-28007: the password cannot be reused -  I got a request from client user, stating his account has been locked. When I check the status of the account I found the below. connect to SYS user & execute the below query of that database. SQL> select password,username,account_status,profile from dba_users where username='TEST'; PASSWORD               USERNAME   ACCOUNT_STATUS                 PROFILE ------------------             ----------------  -------------------------------           -------------------- AB2Aa8AC9971521e3     TEST       EXPIRED(GRACE)&LOCKED    NONAPP_USERS So, then I have unlocked the account and checked the same again & I found the below results. ...
Post a Comment
Read more

ORA-39014: One or more workers have prematurely exited.ORA-00018: maximum number of sessions exceeded

ERROR: I was Performing a full database import and during the import I faced the below error. ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 6 with process name "DW07" prematurely terminated ORA-31672: Worker process DW07 died unexpectedly. Job "SYSTEM"."SYS_IMPORT_FULL_04" stopped due to fatal error at 00:59:40 ORA-39014: One or more workers have prematurely exited. SOLUTION:  Run the import with fewer parallel processes, like PARALLEL=2 instead of 8. I was able to run the import successfully. NOTE 1: This errors occurs when there are less session allocation in the database. check the session,process parameters and increase them accordingly. To avoid such errors again. NOTE 2 : Note: Increasing processes parameter increases the amount of shared memory that needs to be reserved & the OS must be configured to support the larger amount of shared memory. So here we first need to increase the Memory & SG...
Post a Comment
Read more