Skip to main content
 

 RESTORE AND RECOVERING A DROPPED DATAFILE IN PDB:
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 31 12:27:21 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 pdbs;
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
 
SQL> alter session set container=ORCLPDB;
 
Session altered.
 
SQL> show pdbs;
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORCLPDB                        READ WRITE NO
            
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\USERS01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\KRISHNA1.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\TEST.DBF è DROPPING THIS ONE
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\KRISHNA2.DBF
 
7 rows selected.
 
SQL> select open_mode,log_mode,database_role,flashback_on from v$database;
 
OPEN_MODE            LOG_MODE     DATABASE_ROLE    FLASHBACK_ON
-------------------- ------------ ---------------- -----------------READ WRITE           ARCHIVELOG   PRIMARY          NO
 
 
 
SQL> alter session set nls_date_format='YYYYMMDD HH24:MI:SS';
 
Session altered.
 
SQL> select sysdate from dual;
 
SYSDATE
-----------------
20241031 12:34:30
 
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\USERS01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\KRISHNA1.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\TEST.DBF   - REMOVED THIS DATAFILE MANUALLY.
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\KRISHNA2.DBF
 
SHUTDOWN THE DATABASE
 
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
START THE DATABASE:
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.
Database opened.
 
SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
READ WRITE
 
SQL> show pdbs;
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        MOUNTED
 
– PDB IS MOUNTED TRYING TO START IT.
 
SQL> alter pluggable database ORCLPDB open;
alter pluggable database ORCLPDB open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 36 - see DBWR trace file
ORA-01110: data file 36: 'D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\TEST.DBF'
 
– UNABLE TO START PDB AS THE DATAFILE IS MISSING.
 
CHECKING BACKUP DETAILS: IN THIS CASE I HAVE COMPLETE DATABASE BACKUP AND ALL THE ARCHIVED LOGS
 
RMAN TARGET /
 
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Oct 31 14:40:33 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)
 
RMAN> LIST BACKUP;
 
using target database control file instead of recovery catalog
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    17.95M     DISK        00:00:00     25-OCT-24
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20241025T094225
        Piece Name: D:\ORACLE19\FB\ORCL\AUTOBACKUP\2024_10_25\O1_MF_S_1183282945_MKQCLKHG_.BKP
  SPFILE Included: Modification time: 25-OCT-24
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 3424243      Ckp time: 25-OCT-24
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    476.88M    DISK        00:00:03     25-OCT-24
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20241025T094518
        Piece Name: D:\ORACLE19\FB\ORCL\75BE35801D2841809AFC7FA48A59A487\BACKUPSET\2024_10_25\O1_MF_NNNDF_TAG20241025T094518_MKQCQZD2_.BKP
  List of Datafiles in backup set 4
  Container ID: 4099, PDB Name: UNKNOWN
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  27      Full 3424359    25-OCT-24              NO
  28      Full 3424359    25-OCT-24              NO
  29      Full 3424359    25-OCT-24              NO
  30      Full 3424359    25-OCT-24              NO
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    17.95M     DISK        00:00:01     25-OCT-24
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20241025T094526
        Piece Name: D:\ORACLE19\FB\ORCL\AUTOBACKUP\2024_10_25\O1_MF_S_1183283126_MKQCR6YJ_.BKP
  SPFILE Included: Modification time: 25-OCT-24
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 3424381      Ckp time: 25-OCT-24
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    17.95M     DISK        00:00:01     25-OCT-24
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20241025T124120
        Piece Name: D:\ORACLE19\FB\ORCL\AUTOBACKUP\2024_10_25\O1_MF_S_1183293680_MKQP2169_.BKP
  SPFILE Included: Modification time: 25-OCT-24
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 3437399      Ckp time: 25-OCT-24
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Incr 0  99.51M     DISK        00:00:18     25-OCT-24
        BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TOCLONE
        Piece Name: D:\ORACLE19\ASSISTANTS\DBCA\TEMPLATES\ORCL_ORCLPDB.DFB
  List of Datafiles in backup set 7
  Container ID: 3, PDB Name: ORCLPDB
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  9    0  Incr 3439359    25-OCT-24              NO
  10   0  Incr 3439359    25-OCT-24              NO
  11   0  Incr 3439359    25-OCT-24              NO
  12   0  Incr 3439359    25-OCT-24              NO
  14   0  Incr 3439359    25-OCT-24              NO
  15   0  Incr 3439359    25-OCT-24              NO
  16   0  Incr 3439359    25-OCT-24              NO
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    17.95M     DISK        00:00:00     25-OCT-24
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20241025T131626
        Piece Name: D:\ORACLE19\FB\ORCL\AUTOBACKUP\2024_10_25\O1_MF_S_1183295786_MKQR3TSW_.BKP
  SPFILE Included: Modification time: 25-OCT-24
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 3440083      Ckp time: 25-OCT-24
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    17.95M     DISK        00:00:00     25-OCT-24
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20241025T132628
        Piece Name: D:\ORACLE19\FB\ORCL\AUTOBACKUP\2024_10_25\O1_MF_S_1183296388_MKQRPNHM_.BKP
  SPFILE Included: Modification time: 25-OCT-24
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 3443269      Ckp time: 25-OCT-24
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Incr    79.67M     DISK        00:00:07     28-OCT-24
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20241028T162321
        Piece Name: D:\ORACLE19\INCR.BKP
  List of Datafiles in backup set 10
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Incr 4062220    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF
  3       Incr 4062220    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\SYSAUX01.DBF
  4       Incr 4062220    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\UNDOTBS01.DBF
  7       Incr 4062220    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\USERS01.DBF
  13      Incr 4062220    28-OCT-24              NO    D:\ORACLE19\DB19C\DBFILE\KRISHNA.DBF
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    1.36G      DISK        00:00:09     28-OCT-24
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20241028T162550
        Piece Name: D:\ORACLE19\FB\ORCL\BACKUPSET\2024_10_28\O1_MF_NNNDF_TAG20241028T162550_ML00BZ3L_.BKP
  List of Datafiles in backup set 11
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 4062324    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\SYSTEM01.DBF
  3       Full 4062324    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\SYSAUX01.DBF
  4       Full 4062324    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\UNDOTBS01.DBF
  7       Full 4062324    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\USERS01.DBF
  13      Full 4062324    28-OCT-24              NO    D:\ORACLE19\DB19C\DBFILE\KRISHNA.DBF
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12      Full    493.15M    DISK        00:00:03     28-OCT-24
        BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: TAG20241028T162550
        Piece Name: D:\ORACLE19\FB\ORCL\41ABD2353523466894167626E21ACD1B\BACKUPSET\2024_10_28\O1_MF_NNNDF_TAG20241028T162550_ML00CGD5_.BKP
  List of Datafiles in backup set 12
  Container ID: 3, PDB Name: ORCLPDB
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  31      Full 4062329    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
  32      Full 4062329    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
  33      Full 4062329    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
  34      Full 4062329    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\USERS01.DBF
  35      Full 4062329    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\KRISHNA1.DBF
  36      Full 4062329    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\TEST.DBF
  37      Full 4062329    28-OCT-24              NO    D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\KRISHNA2.DBF
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13      Full    467.15M    DISK        00:00:03     28-OCT-24
        BP Key: 13   Status: AVAILABLE  Compressed: NO  Tag: TAG20241028T162550
 
RESTORING AND RECOVERING THE DROPPED DATAFILE USING RMAN:
 
RUN {
    SET NEWNAME FOR DATAFILE 'D:\Oracle19\DB19C\oradata\ORCL\orclpdb\TEST.DBF' TO 'D:\Oracle19\DB19C\oradata\ORCL\orclpdb\TEST.DBF';
    RESTORE DATAFILE 'D:\Oracle19\DB19C\oradata\ORCL\orclpdb\TEST.DBF';
    SWITCH DATAFILE ALL;
    RECOVER DATAFILE 'D:\Oracle19\DB19C\oradata\ORCL\orclpdb\TEST.DBF';
}
 
RMAN> RUN {
2>     SET NEWNAME FOR DATAFILE 'D:\Oracle19\DB19C\oradata\ORCL\orclpdb\TEST.DBF' TO 'D:\Oracle19\DB19C\oradata\ORCL\orclpdb\TEST.DBF';
3>     RESTORE DATAFILE 'D:\Oracle19\DB19C\oradata\ORCL\orclpdb\TEST.DBF';
4>     SWITCH DATAFILE ALL;
5>     RECOVER DATAFILE 'D:\Oracle19\DB19C\oradata\ORCL\orclpdb\TEST.DBF';
6> }
 
executing command: SET NEWNAME
 
Starting restore at 31-OCT-24
using channel ORA_DISK_1
 
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 00036 to D:\Oracle19\DB19C\oradata\ORCL\orclpdb\TEST.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE19\FB\ORCL\41ABD2353523466894167626E21ACD1B\BACKUPSET\2024_10_28\O1_MF_NNNDF_TAG20241028T162550_ML00CGD5_.BKP
channel ORA_DISK_1: piece handle=D:\ORACLE19\FB\ORCL\41ABD2353523466894167626E21ACD1B\BACKUPSET\2024_10_28\O1_MF_NNNDF_TAG20241028T162550_ML00CGD5_.BKP tag=TAG20241028T162550
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 file name=D:\ORACLE19\FB\ORCL\ARCHIVELOG\2024_10_28\O1_MF_1_7_ML0RK3KB_.ARC thread=1 sequence=7
media recovery complete, elapsed time: 00:00:01
Finished recover at 31-OCT-24
 
SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
READ WRITE
 
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> alter session set container=ORCLPDB;
 
Session altered.
 
SQL> select file_name from dba_data_files;
 
FILE_NAME
--------------------------------------------------------------------D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\USERS01.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\KRISHNA1.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\TEST.DBF
D:\ORACLE19\DB19C\ORADATA\ORCL\ORCLPDB\KRISHNA2.DBF
 
7 rows selected.
 
 
Happy LearningJ
 
 
 
 
 

Comments

Post a Comment

Popular posts from this blog

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

ORA-01143: cannot disable media recovery - file 1 needs media recovery

I got a request from the client - To flashback the database to the existing restore point & disable flashback and archive log mode for database UATB. Here I came a cross error - ORA-01143. I followed the below steps. 1. SQL> select name from v$database; NAME ------------ UATB 2. SQL> SELECT NAME FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ORDER BY TIME; NAME --------- UATB_COPY Here I'm going to restore the database to the above restore point. NOTE: The flashback database restore has to be done in MOUNT stage of the database. SQL> select name from v$database; NAME --------- UATB SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  612368384 bytes Fixed Size                  1250428 bytes Variable Size             167775108 bytes Database Buffers          436207616 bytes Redo Buffers          
2 comments
Read more