B2k

B2k

Awm king

Nobin

Gamer

 

Simplified ER Diagram Basics

 An ER (Entity-Relationship) diagram is a visual representation used to model the structure of a database system. It illustrates the entities within the system and the relationships among them. ER diagrams help to explain the logical structure of databases. The primary components of an ER diagram include entities, attributes, and relationships. 

Restoring and Recovering Oracle Database 19c from a level 0 and a level 1 RMAN backup.

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.

OR directly using TNS Alias: In this case, orcl is the tns alisa of the container

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 sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 7 22:34: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> select * from level1.level1_table;
        ID NAME
---------- --------------------
         1 First
         2 Second
SQL>

---Thanks for Reading it all, now you try it, please.

Append of Spool while running sql script

This is my Script:

spooltest.sql

------------------------

spool C:\Users\DoICT_Borhanuddin\Desktop\record.txt append

alter session set container=starpdb;

startup

spool off;

-------------------------

Adding an append Keyword at the end of the spool line will let you keep the old text instead of overwriting it every time you run the script.

Checking Roles of a user in oracle.

From SYS (To see all the privileges sys can provide to a user):

select * from role_sys_privs; 

From DBA user: 

select * from dba_role_privs where grantee='USER';

From the user itself:

select * from user_role_privs;

Miscellaneous:

SELECT * FROM DBA_TAB_PRIVS where owner='USER1' and grantee like'TEST_USER%';

SELECT * FROM ALL_TAB_PRIVS where grantee like'USER';

Check Datafile Usage and Resize a Datafile Oracle 10g Onward.

Due to space constraints or some other issue, we may need to resize a datafile in Oracle.
If the datafile is mostly unused (Partially used but not full).

1. Firstly, we need to check the datafile usage in each tablespace.

SQL>SELECT  a.tablespace_name, a.file_name, a.bytes allocated_bytes, b.free_bytes 

FROM dba_data_files a, (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b 

WHERE  a.file_id=b.file_id ORDER BY a.tablespace_name;

2. Then we may need to resize the datafile.

SQL>alter database datafile '/u01/app/oracle/oradata/prod_02.dbf' resize 150M;