1. Check your DB_RECOVERY_FILE_DEST_SIZE
C:\Users\DoICT_Borhanuddin>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 7 21:36:06 2024
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> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\app\mijan
db_recovery_file_dest_size big integer 20G
SQL>
2. Connect to RMAN using Default OS Authentication
Microsoft Windows [Version 10.0.22631.3447]
(c) Microsoft Corporation. All rights reserved.
C:\Users\DoICT_Borhanuddin>rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue May 7 21:39:36 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=1664860452)
RMAN>
RMAN> exit
Recovery Manager complete.
C:\Users\DoICT_Borhanuddin>rman target sys@orcl
Recovery Manager: Release 19.0.0.0.0 - Production on Tue May 7 21:41:01 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1664860452)
RMAN>
3. Doing some maintenance work: Checking and deleting existing backups using tag to take a fresh new one.
RMAN>RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
67 B F A DISK 04-MAY-24 1 1 NO TAG20240504T212147
68 B F A DISK 04-MAY-24 1 1 NO TAG20240504T212147
69 B F A DISK 04-MAY-24 1 1 NO TAG20240504T212147
70 B F A DISK 04-MAY-24 1 1 NO TAG20240504T212147
RMAN>
RMAN> DELETE BACKUP tag TAG20240504T212147;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
67 67 1 1 AVAILABLE DISK D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_04\O1_MF_NNNDF_TAG20240504T212147_M3DNND8G_.BKP
68 68 1 1 AVAILABLE DISK D:\APP\MIJAN\ORCL\BAAE8A5B4DDB47D8B9ECE9B3CABCB352\BACKUPSET\2024_05_04\O1_MF_NNNDF_TAG20240504T212147_M3DNNHM0_.BKP
69 69 1 1 AVAILABLE DISK D:\APP\MIJAN\ORCL\BF62D05FB9064055A95844E6BF03E118\BACKUPSET\2024_05_04\O1_MF_NNNDF_TAG20240504T212147_M3DNNLSY_.BKP
70 70 1 1 AVAILABLE DISK D:\APP\MIJAN\ORCL\B774F18DD59F4AC7BD46C89E7A2544B3\BACKUPSET\2024_05_04\O1_MF_NNNDF_TAG20240504T212147_M3DNNP2F_.BKP
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_04\O1_MF_NNNDF_TAG20240504T212147_M3DNND8G_.BKP RECID=67 STAMP=1168118508
deleted backup piece
backup piece handle=D:\APP\MIJAN\ORCL\BAAE8A5B4DDB47D8B9ECE9B3CABCB352\BACKUPSET\2024_05_04\O1_MF_NNNDF_TAG20240504T212147_M3DNNHM0_.BKP RECID=68 STAMP=1168118511
deleted backup piece
backup piece handle=D:\APP\MIJAN\ORCL\BF62D05FB9064055A95844E6BF03E118\BACKUPSET\2024_05_04\O1_MF_NNNDF_TAG20240504T212147_M3DNNLSY_.BKP RECID=69 STAMP=1168118514
deleted backup piece
backup piece handle=D:\APP\MIJAN\ORCL\B774F18DD59F4AC7BD46C89E7A2544B3\BACKUPSET\2024_05_04\O1_MF_NNNDF_TAG20240504T212147_M3DNNP2F_.BKP RECID=70 STAMP=1168118518
Deleted 4 objects
RMAN>
You can use the following too.
RMAN> DELETE NOPROMPT BACKUP;
RMAN> DELETE NOPROMPT ARCHIVELOG ALL;
RMAN> list backup;
The specification does not match any backup in the repository
4. Checking all RMAN configurations like Control file autobackup and default device type disk or not.
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
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 'D:\SOFTWARE\ORACLE_19C\DATABASE\SNCFORCL.ORA'; # default
RMAN>
5. Take a full level 0 backup including archivelogs.
RMAN> backup incremental level 0 database plus archivelog;
Starting backup at 07-MAY-24
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=4 RECID=119 STAMP=1168118486
input archived log thread=1 sequence=12 RECID=127 STAMP=1168382985
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_ANNNN_TAG20240507T224945_M3NPX9MM_.BKP tag=TAG20240507T224945 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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=115 STAMP=1168035268
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_ANNNN_TAG20240507T224945_M3NPXBPO_.BKP tag=TAG20240507T224945 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-MAY-24
Starting backup at 07-MAY-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00003 name=D:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00004 name=D:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00007 name=D:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_NNND0_TAG20240507T224947_M3NPXD0B_.BKP tag=TAG20240507T224947 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
input datafile file number=00009 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
input datafile file number=00011 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
input datafile file number=00017 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\NEW_APP_01.DBF
input datafile file number=00018 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM02.DBF
input datafile file number=00012 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BAAE8A5B4DDB47D8B9ECE9B3CABCB352\BACKUPSET\2024_05_07\O1_MF_NNND0_TAG20240507T224947_M3NPXH5W_.BKP tag=TAG20240507T224947 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSAUX01.DBF
input datafile file number=00013 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSTEM01.DBF
input datafile file number=00015 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\UNDOTBS01.DBF
input datafile file number=00016 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BF62D05FB9064055A95844E6BF03E118\BACKUPSET\2024_05_07\O1_MF_NNND0_TAG20240507T224947_M3NPXJ8F_.BKP tag=TAG20240507T224947 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF
input datafile file number=00005 name=D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF
input datafile file number=00008 name=D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\UNDOTBS01.DBF
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\B774F18DD59F4AC7BD46C89E7A2544B3\BACKUPSET\2024_05_07\O1_MF_NNND0_TAG20240507T224947_M3NPXKDV_.BKP tag=TAG20240507T224947 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-MAY-24
Starting backup at 07-MAY-24
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=13 RECID=128 STAMP=1168382994
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_ANNNN_TAG20240507T224954_M3NPXLLG_.BKP tag=TAG20240507T224954 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-MAY-24
Starting Control File and SPFILE Autobackup at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\AUTOBACKUP\2024_05_07\O1_MF_S_1168382995_M3NPXMT4_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 07-MAY-24
RMAN>
6. Connect to the container and make some changes in a pluggable database.
C:\Users\DoICT_Borhanuddin>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 7 21:36:06 2024
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> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
SQL> alter session set container=orclpdb;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB MOUNTED
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB
SQL> alter pluggable database orclpdb open;
Pluggable database altered.
SQL> create user level1 identified by oracle default tablespace users;
User created.
SQL> grant dba to level1;
Grant succeeded.
SQL> alter session set current_schema=level1;
Session altered.
SQL> create table level1.level1_table(id integer, name varchar(20)) tablespace users;
Table created.
SQL> insert into level1.level1_table
2 values(1,'First');
1 row created.
SQL> insert into level1.level1_table
2 values(2,'Second');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from level1.level1_table;
ID NAME
---------- --------------------
1 First
2 Second
SQL>
7. Now Connect to RMAN and take a level1 backup
RMAN> backup incremental level 1 database plus archivelog;
Starting backup at 07-MAY-24
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=4 RECID=119 STAMP=1168118486
input archived log thread=1 sequence=12 RECID=127 STAMP=1168382985
input archived log thread=1 sequence=13 RECID=128 STAMP=1168382994
input archived log thread=1 sequence=14 RECID=129 STAMP=1168383226
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_ANNNN_TAG20240507T225346_M3NQ4TYD_.BKP tag=TAG20240507T225346 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
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=115 STAMP=1168035268
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_ANNNN_TAG20240507T225346_M3NQ4W22_.BKP tag=TAG20240507T225346 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-MAY-24
Starting backup at 07-MAY-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00003 name=D:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00004 name=D:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00007 name=D:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_NNND1_TAG20240507T225349_M3NQ4XGD_.BKP tag=TAG20240507T225349 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
input datafile file number=00009 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
input datafile file number=00011 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
input datafile file number=00017 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\NEW_APP_01.DBF
input datafile file number=00018 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM02.DBF
input datafile file number=00012 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BAAE8A5B4DDB47D8B9ECE9B3CABCB352\BACKUPSET\2024_05_07\O1_MF_NNND1_TAG20240507T225349_M3NQ4YLS_.BKP tag=TAG20240507T225349 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSAUX01.DBF
skipping datafile 00014 because it has not changed
input datafile file number=00013 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSTEM01.DBF
skipping datafile 00013 because it has not changed
input datafile file number=00015 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\UNDOTBS01.DBF
skipping datafile 00015 because it has not changed
input datafile file number=00016 name=D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\USERS01.DBF
skipping datafile 00016 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF
skipping datafile 00006 because it has not changed
input datafile file number=00005 name=D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF
skipping datafile 00005 because it has not changed
input datafile file number=00008 name=D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\UNDOTBS01.DBF
skipping datafile 00008 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
Finished backup at 07-MAY-24
Starting backup at 07-MAY-24
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=15 RECID=130 STAMP=1168383231
channel ORA_DISK_1: starting piece 1 at 07-MAY-24
channel ORA_DISK_1: finished piece 1 at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_ANNNN_TAG20240507T225351_M3NQ4ZY2_.BKP tag=TAG20240507T225351 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-MAY-24
Starting Control File and SPFILE Autobackup at 07-MAY-24
piece handle=D:\APP\MIJAN\ORCL\AUTOBACKUP\2024_05_07\O1_MF_S_1168383233_M3NQ516O_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 07-MAY-24
RMAN>
9. Now check your RMAN backups with level 0 and level 1 with two different tag.
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
84 B 0 A DISK 07-MAY-24 1 1 NO TAG20240507T224947
85 B 0 A DISK 07-MAY-24 1 1 NO TAG20240507T224947
86 B 0 A DISK 07-MAY-24 1 1 NO TAG20240507T224947
87 B 0 A DISK 07-MAY-24 1 1 NO TAG20240507T224947
92 B 1 A DISK 07-MAY-24 1 1 NO TAG20240507T225349
93 B 1 A DISK 07-MAY-24 1 1 NO TAG20240507T225349
RMAN>
10. Now Check your Datafile and delete all of them.
C:\Users\DoICT_Borhanuddin>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 7 23:00:54 2024
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> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show user
USER is "SYS"
SQL> select name from V$datafile;
NAME
--------------------------------------------------
D:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
D:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
D:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF
D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF
D:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\UNDOTBS01.DBF
D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\USERS01.DBF
D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSTEM01.DBF
D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSAUX01.DBF
D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\UNDOTBS01.DBF
D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\USERS01.DBF
D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\NEW_APP_01.DBF
D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM02.DBF
17 rows selected.
SQL>
11. Now exit from SQL prompt and Try to reconnect, oracle has not yet detected the loss!
Try a shutdown immediate and Check the alert log file.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
C:\Users\DoICT_Borhanuddin>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 7 23:07:16 2024
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> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: 'D:\APP\ORACLE\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.
SQL>
------------------------Alert log started------------------------------
2024-05-07T23:07:32.398028+06:00
Shutting down ORACLE instance (immediate) (OS id: 19720)
Stopping background process SMCO
2024-05-07T23:07:33.446960+06:00
Shutting down instance: further logons disabled
2024-05-07T23:07:34.871341+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 1: 'D:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\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.
2024-05-07T23:07:35.027580+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 3: 'D:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.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.
2024-05-07T23:07:35.215797+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 4: 'D:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.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.
2024-05-07T23:07:35.403208+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 7: 'D:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\USERS01.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.
2024-05-07T23:07:35.590707+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 9: 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\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.
2024-05-07T23:07:35.746957+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 10: 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSAUX01.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.
2024-05-07T23:07:35.965811+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 11: 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\UNDOTBS01.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.
2024-05-07T23:07:36.137767+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 12: 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\USERS01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\USERS01.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.
2024-05-07T23:07:36.325622+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 13: 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSTEM01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\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.
2024-05-07T23:07:36.481917+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 14: 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSAUX01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSAUX01.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.
2024-05-07T23:07:36.653776+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 15: 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\UNDOTBS01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\UNDOTBS01.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.
2024-05-07T23:07:36.841461+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 16: 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\USERS01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\USERS01.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.
2024-05-07T23:07:37.013297+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 17: 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\NEW_APP_01.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\NEW_APP_01.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.
2024-05-07T23:07:37.232031+06:00
Errors in file D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace\orcl_mz00_20284.trc:
ORA-01110: data file 18: 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM02.DBF'
ORA-01565: error in identifying file 'D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM02.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.
Checker run found 14 new persistent data failures
---------------------Alter log while trying a shutdown immediate-----------------------
12. Now try to do a shutdown abort and startup the container in mount state;
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
Shutting down ORACLE instance (abort) (OS id: 19720)
License high water mark = 14
2024-05-07T23:13:49.234657+06:00
USER (ospid: 19720): terminating the instance
2024-05-07T23:14:18.853518+06:00
Instance terminated by USER, pid = 19720
2024-05-07T23:14:18.962644+06:00
Instance shutdown complete (OS id: 19720)
SQL> SQL> startup mount;
ORACLE instance started.
Total System Global Area 2466247704 bytes
Fixed Size 9270296 bytes
Variable Size 838860800 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7503872 bytes
Database mounted.
SQL>
13. Now connect to RMAN and Try to restore and recover using the following script.
C:\Users\DoICT_Borhanuddin>rman target sys@orcl
Recovery Manager: Release 19.0.0.0.0 - Production on Tue May 7 23:18:57 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1664860452, not open)
RMAN>
RMAN> list backup of database summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
84 B 0 A DISK 07-MAY-24 1 1 NO TAG20240507T224947
85 B 0 A DISK 07-MAY-24 1 1 NO TAG20240507T224947
86 B 0 A DISK 07-MAY-24 1 1 NO TAG20240507T224947
87 B 0 A DISK 07-MAY-24 1 1 NO TAG20240507T224947
92 B 1 A DISK 07-MAY-24 1 1 NO TAG20240507T225349
93 B 1 A DISK 07-MAY-24 1 1 NO TAG20240507T225349
RMAN>
RMAN> run
{
RESTORE DATABASE from tag TAG20240507T224947;
RECOVER DATABASE from tag TAG20240507T225349;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}
Starting restore at 07-MAY-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=127 device type=DISK
skipping datafile 5; already restored to file D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\SYSTEM01.DBF
skipping datafile 6; already restored to file D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\SYSAUX01.DBF
skipping datafile 8; already restored to file D:\APP\ORACLE\ORADATA\ORCL\PDBSEED\UNDOTBS01.DBF
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:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00007 to D:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_NNND0_TAG20240507T224947_M3NPXD0B_.BKP
channel ORA_DISK_1: piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_NNND0_TAG20240507T224947_M3NPXD0B_.BKP tag=TAG20240507T224947
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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 00009 to D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00010 to D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00011 to D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00012 to D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00017 to D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\NEW_APP_01.DBF
channel ORA_DISK_1: restoring datafile 00018 to D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM02.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\MIJAN\ORCL\BAAE8A5B4DDB47D8B9ECE9B3CABCB352\BACKUPSET\2024_05_07\O1_MF_NNND0_TAG20240507T224947_M3NPXH5W_.BKP
channel ORA_DISK_1: piece handle=D:\APP\MIJAN\ORCL\BAAE8A5B4DDB47D8B9ECE9B3CABCB352\BACKUPSET\2024_05_07\O1_MF_NNND0_TAG20240507T224947_M3NPXH5W_.BKP tag=TAG20240507T224947
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00013 to D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00014 to D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00015 to D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00016 to D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB2\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\MIJAN\ORCL\BF62D05FB9064055A95844E6BF03E118\BACKUPSET\2024_05_07\O1_MF_NNND0_TAG20240507T224947_M3NPXJ8F_.BKP
channel ORA_DISK_1: piece handle=D:\APP\MIJAN\ORCL\BF62D05FB9064055A95844E6BF03E118\BACKUPSET\2024_05_07\O1_MF_NNND0_TAG20240507T224947_M3NPXJ8F_.BKP tag=TAG20240507T224947
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 07-MAY-24
Starting recover at 07-MAY-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: D:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
destination for restore of datafile 00003: D:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF
destination for restore of datafile 00004: D:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
destination for restore of datafile 00007: D:\APP\ORACLE\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_NNND1_TAG20240507T225349_M3NQ4XGD_.BKP
channel ORA_DISK_1: piece handle=D:\APP\MIJAN\ORCL\BACKUPSET\2024_05_07\O1_MF_NNND1_TAG20240507T225349_M3NQ4XGD_.BKP tag=TAG20240507T225349
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00009: D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM01.DBF
destination for restore of datafile 00010: D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSAUX01.DBF
destination for restore of datafile 00011: D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\UNDOTBS01.DBF
destination for restore of datafile 00012: D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\USERS01.DBF
destination for restore of datafile 00017: D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\NEW_APP_01.DBF
destination for restore of datafile 00018: D:\APP\ORACLE\ORADATA\ORCL\ORCLPDB\SYSTEM02.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\MIJAN\ORCL\BAAE8A5B4DDB47D8B9ECE9B3CABCB352\BACKUPSET\2024_05_07\O1_MF_NNND1_TAG20240507T225349_M3NQ4YLS_.BKP
channel ORA_DISK_1: piece handle=D:\APP\MIJAN\ORCL\BAAE8A5B4DDB47D8B9ECE9B3CABCB352\BACKUPSET\2024_05_07\O1_MF_NNND1_TAG20240507T225349_M3NQ4YLS_.BKP tag=TAG20240507T225349
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 07-MAY-24
Starting recover at 07-MAY-24
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-MAY-24
Statement processed
RMAN>Recovery Manager complete.14. Connect to a container and check whether you've get your data back.C:\Users\DoICT_Borhanuddin>sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Tue May 7 22:34:06 2024Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.3.0.0.0SQL>SQL> show con_nameCON_NAME------------------------------CDB$ROOTSQL> show userUSER is "SYS"SQL> alter session set container=orclpdb;Session altered.SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------3 ORCLPDB MOUNTEDSQL> show con_nameCON_NAME------------------------------ORCLPDBSQL> alter pluggable database orclpdb open;Pluggable database altered.SQL> select * from level1.level1_table;ID NAME---------- --------------------1 First2 SecondSQL>
---Thanks for Reading it all, now you try it, please.
No comments:
Post a Comment