"Long Term Support Release" vs "Innovation Release"

Too often, IT departments implement and then get stuck on software releases that do not get long term support.  There is no harm upgrading to a new release that is an intermediate if you plan to subsequently upgrade to the proper release version that has long term support.

Mike Dietrich has explained this in his blog post here.

Full Recovery of Standby Database over the network

Say that your Standby database is lagging behind the Primary database.

You could
a.  Fetch and apply all the ArchiveLogs required to cover the lag
b.  Take an Incremental Backup from the Primary and apply it to the Standby (the syntax being "backup as compressed backupset incremental from SCN=xxxx format '......' ) and then restore it on the Standby
c.  Since 12c, do a complete Refresh of the Standby over the network
(at the end of this post, I have added a link to a 19c demo of Restore+Recovery of a single datafile from the Primary to the Standby)


My Primary reports :

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            79

SQL> 

While my Standby reports :

SQL> select high_Sequence# from v$archive_gap;

HIGH_SEQUENCE#
--------------
            67

SQL> 

So, I attempt to refresh the Standby with :

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 17 22:55:22 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     419430400 bytes

Fixed Size                     8793496 bytes
Variable Size                167772776 bytes
Database Buffers             234881024 bytes
Redo Buffers                   7983104 bytes

RMAN> restore database from service ORCL12C;

Starting restore at 17-JUN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK

skipping datafile 5; already restored to SCN 1443131
skipping datafile 6; already restored to SCN 1443131
skipping datafile 8; already restored to SCN 1443131
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /STANDBY/database/STDB/datafile/o1_mf_system_2gude3k1_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /STANDBY/database/STDB/datafile/o1_mf_sysaux_2hude3l5_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /STANDBY/database/STDB/datafile/o1_mf_users_2qude3pc_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_system_2jude3nb_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_sysaux_2fude3iu_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_undotbs1_2iude3mi_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_users_2mude3op_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_apex_199_2pude3pb_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_apex_199_2rude3pe_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00015 to /STANDBY/database/STDB/datafile/o1_mf_undotbs2_2oude3p4_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 17-JUN-20

RMAN> 
RMAN> exit


Recovery Manager complete.
$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 17 22:58:55 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> 

The alert log on the Standby even shows the new PDB that I had created on the Primary while the Standby was down.  (See my previous BlogPost where I had created NEWPDB)

2020-06-17T22:59:13.347236+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_67_hgncbh2m_.arc
2020-06-17T22:59:13.497650+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_68_hgnbvsy2_.arc
2020-06-17T22:59:13.566410+08:00
Completed: alter database recover managed standby database disconnect from session
2020-06-17T22:59:13.659512+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_69_hgncbj0v_.arc
2020-06-17T22:59:13.759502+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_70_hgncbj5z_.arc
2020-06-17T22:59:13.981225+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_71_hgncbjf6_.arc
Recovery created pluggable database NEWPDB
2020-06-17T22:59:21.246470+08:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_system_2lude3oa_.dbf
NEWPDB(4):Successfully added datafile 41 to media recovery
NEWPDB(4):Datafile #41: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf'
2020-06-17T22:59:30.184914+08:00
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_sysaux_2kude3nr_.dbf
NEWPDB(4):Successfully added datafile 42 to media recovery
NEWPDB(4):Datafile #42: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf'
2020-06-17T22:59:32.983486+08:00
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_undotbs1_2nude3p1_.dbf
NEWPDB(4):Successfully added datafile 43 to media recovery
NEWPDB(4):Datafile #43: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf'
2020-06-17T22:59:36.310405+08:00
(4):Successfully added datafile 44 to media recovery
(4):Datafile #44: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_my_user__hgncypmz_.dbf'
(4):Resize operation completed for file# 42, old size 337920K, new size 348160K
(4):Resize operation completed for file# 42, old size 348160K, new size 368640K
2020-06-17T22:59:39.083144+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_72_hgncbjf0_.arc
2020-06-17T22:59:39.235507+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_73_hgnc90l0_.arc
2020-06-17T22:59:39.547295+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_74_hgncbdhw_.arc
2020-06-17T22:59:39.734181+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_75_hgncbdn2_.arc
2020-06-17T22:59:39.856485+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_76_hgncbgvb_.arc
2020-06-17T22:59:40.007157+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_77_hgncbgr5_.arc
2020-06-17T22:59:40.129214+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_78_hgncbmo4_.arc
2020-06-17T22:59:40.266298+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_79_hgncbmmz_.arc
Media Recovery Waiting for thread 1 sequence 80 (in transit)
2020-06-17T23:02:19.174414+08:00
Archived Log entry 18 added for thread 1 sequence 80 rlc 937554761 ID 0x2dc76487 LAD2 :
2020-06-17T23:02:19.575536+08:00
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process (PID:6137)
RFS[2]: No standby redo logfiles created
2020-06-17T23:02:19.597702+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_80_hgncrmr3_.arc
RFS[2]: Opened log for T-1.S-81 dbid 768045447 branch 937554761
2020-06-17T23:02:20.948511+08:00
Media Recovery Waiting for thread 1 sequence 81 (in transit)
2020-06-17T23:02:31.317814+08:00
Archived Log entry 19 added for thread 1 sequence 81 rlc 937554761 ID 0x2dc76487 LAD2 :
RFS[2]: No standby redo logfiles created
RFS[2]: Opened log for T-1.S-82 dbid 768045447 branch 937554761
2020-06-17T23:02:31.710951+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_81_hgnd3vlo_.arc
Media Recovery Waiting for thread 1 sequence 82 (in transit)

And I can check whether the datafiles are being updated at the Standby

SQL> select current_scn, database_role from v$database;

CURRENT_SCN    DATABASE_ROLE
----------- ----------------
    3035674 PHYSICAL STANDBY

SQL> select file#, checkpoint_change# from v$datafile order by 1;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1            3035675
         3            3035675
         5            1443131
         6            1443131
         7            3035675
         8            1443131
         9            3035675
        10            3035675
        11            3035675
        12            3035675
        13            3035675
        14            3035675
        15            3035675
        41            3035675
        42            3035675
        43            3035675
        44            3035675

17 rows selected.

SQL> 

What are datafiles 5, 6 and 8 ? Even the RMAN RESTORE command had shown them to "already restored to SCN 1443131"  -- which is a much lower SCN ?

SQL> select p.con_id, p.name, p.open_mode, d.file#, d.checkpoint_change#
  2  from v$pdbs p, v$datafile d
  3  where p.con_id=d.con_id
  4  order by d.file#
  5  /

    CON_ID NAME         OPEN_MODE       FILE# CHECKPOINT_CHANGE#
---------- ------------ ---------- ---------- ------------------
         2 PDB$SEED     MOUNTED             5            1443131
         2 PDB$SEED     MOUNTED             6            1443131
         2 PDB$SEED     MOUNTED             8            1443131
         3 ORCL         MOUNTED             9            3035675
         3 ORCL         MOUNTED            10            3035675
         3 ORCL         MOUNTED            11            3035675
         3 ORCL         MOUNTED            12            3035675
         3 ORCL         MOUNTED            13            3035675
         3 ORCL         MOUNTED            14            3035675
         4 NEWPDB       MOUNTED            41            3035675
         4 NEWPDB       MOUNTED            42            3035675
         4 NEWPDB       MOUNTED            43            3035675
         4 NEWPDB       MOUNTED            44            3035675
 
13 rows selected.

SQL> 

Those 3 datafiles are of the SEED PDB which does not get updated as it is not opened READ-WRITE.


Note : Here is a 19c demo of Restore+Recovery of a single datafile from Primary to the Standby

Datafile in a PDB added but not backed up

(I think I've covered this with a pre-12c non-PDB example earlier, but this demo is with a 12c PDB)

What happens if you add a datafile to a PDB and then lose it without having a backup of the datafile ?

Here is a quick demo

$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:10:19 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter db_create_file_dest

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest       string
SQL> alter session set db_create_file_dest='/u01/app/oracle/oradata';

Session altered.

SQL> create pluggable database newpdb admin user newpdb_adm identified by newpdb_adm;

Pluggable database created.

SQL> 
SQL> alter pluggable database newpdb open;

Pluggable database altered.

SQL> alter session set container=newpdb;

Session altered.

SQL> create user hemant_newpdb identified by hemant_newpdb;

User created.

SQL> grant dba to hemant_newpdb;

Grant succeeded.

SQL> 

SQL> !vi $ORACLE_HOME/network/admin/tnsnames.ora  --- added the entry for newpdb

SQL> 
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:16:29 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
SQL> alter session set db_create_file_dest='/u01/app/oracle/oradata';

Session altered.

SQL> create tablespace my_user_data;

Tablespace created.

SQL> select file_name from dba_data_files
  2  where tablespace_name  = 'MY_USER_DATA'
  3  /

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf

SQL> select file_id, file_name
  2  from dba_data_files
  3  order by 1
  4  /

   FILE_ID
----------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
 41
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_system_hgh0ddlg_.dbf

 42
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_sysaux_hgh0ddlw_.dbf

 43
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_undotbs1_hgh0ddlw_.dbf

 44
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf


SQL> create table my_user_data_tbl tablespace my_user_data as select * from dba_objects;

Table created.

SQL> select count(*) from my_user_data_tbl;

  COUNT(*)
----------
     72623

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

So, I now have a new PDB, a custom tablespace in the PDB and a table with data in that Tablespace.

I have NOT yet taken a backup of the PDB.

For the purpose of this demo, I will corrupt the datafile.

$cat  >/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
abcdef 
junk data
overwriting the datafile
$ls -l /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
-rw-r----- 1 oracle oinstall 43 Jun 15 22:22 /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
$cat /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
abcdef 
junk data
overwriting the datafile
$

Any attempt to read or write the Datafile will fail.

$sync;sync
$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:23:38 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system flush buffer_cache;

System altered.

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:23:59 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Mon Jun 15 2020 22:16:29 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from my_user_data_tbl;
select count(*) from my_user_data_tbl
                     *
ERROR at line 1:
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 44: '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 130


SQL> exit 
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$      

The sync and flush buffer_cache commands ensure that Oracle does not try to read the table blocks from memory but actually has to attempt to read the Datafile.

Now it reports that the Datafile has an error.

However, I have not taken a backup of the Datafile.

I go ahead to use RMAN to "restore" and "recover" the Datafile.

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 15 22:30:32 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL12C (DBID=768045447)

RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
171        HIGH     OPEN      15-JUN-20     One or more non-system datafiles are corrupt

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
171        HIGH     OPEN      15-JUN-20     One or more non-system datafiles are corrupt

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. Automatic repairs may be available if you shutdown the database and restart it in mount mode

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 44  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm/reco_3798852352.hm

RMAN> 
RMAN> quit


Recovery Manager complete.
$cat /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm/reco_3798852352.hm
   # restore and recover datafile
   sql 'NEWPDB' 'alter database datafile 44 offline';
   restore ( datafile 44 );
   recover datafile 44;
   sql 'NEWPDB' 'alter database datafile 44 online';
$
$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 15 22:31:44 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL12C (DBID=768045447)

RMAN> sql 'NEWPDB' 'alter database datafile 44 offline';

using target database control file instead of recovery catalog
sql statement: alter database datafile 44 offline

RMAN> restore ( datafile 44 );

Starting restore at 15-JUN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=289 device type=DISK

creating datafile file number=44 name=/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 15-JUN-20

RMAN> recover datafile 44;

Starting recover at 15-JUN-20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 15-JUN-20

RMAN> sql 'NEWPDB' 'alter database datafile 44 online';

sql statement: alter database datafile 44 online

RMAN> exit


Recovery Manager complete.
$

Note that the message "restore not done; all files read only, offline, excluded, or already restored" can be misleading.  This occurs when RMAN actually *creates* a Datafile because there is no backup of the Datafile.

I can now query the data.

$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:34:40 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Mon Jun 15 2020 22:24:00 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from my_user_data_tbl;

  COUNT(*)
----------
     72623

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$
$cd /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile
$ls -l
total 829540
-rw-r----- 1 oracle oinstall 104865792 Jun 15 22:32 o1_mf_my_user__hgh0qyh7_.dbf
-rw-r----- 1 oracle oinstall 377495552 Jun 15 22:23 o1_mf_sysaux_hgh0ddlw_.dbf
-rw-r----- 1 oracle oinstall 262152192 Jun 15 22:40 o1_mf_system_hgh0ddlg_.dbf
-rw-r----- 1 oracle oinstall  67117056 Jun 15 22:14 o1_mf_temp_hgh0ddlw_.dbf
-rw-r----- 1 oracle oinstall 104865792 Jun 15 22:40 o1_mf_undotbs1_hgh0ddlw_.dbf

Here are the interesting messages from the alert log file :

2020-06-15T22:31:53.166607+08:00
NEWPDB(4):alter database datafile 44 offline
NEWPDB(4):Completed: alter database datafile 44 offline
2020-06-15T22:32:01.797043+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_m000_5900.trc:
ORA-01110: data file 44: '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
2020-06-15T22:32:03.419839+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_m000_5900.trc:
ORA-01122: database file 44 failed verification check
ORA-01110: data file 44: '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
2020-06-15T22:32:15.922471+08:00
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover
 if needed datafile 44
2020-06-15T22:32:15.939087+08:00
Media Recovery Start
2020-06-15T22:32:15.947485+08:00
Serial Media Recovery started
2020-06-15T22:32:16.148455+08:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 71 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl12c/redo02.log
2020-06-15T22:32:16.536560+08:00
Media Recovery Complete (orcl12c)
Completed: alter database recover
 if needed datafile 44
2020-06-15T22:32:22.582888+08:00

Unfortunately, the alert log does not show the "creating datafile" of the RESTORE command from RMAN.  It does show that the RECOVER command actually read from the Online Redo Log file.  In fact, if there had been multiple ArchiveLogs generated since the creation of the Datafile and the attempt to RECOVER the datafile, it would read from all those ArchiveLogs at this phase.  (Of course, if any ArchiveLog was missing, the RECOVER phase would fail).

So, if you lose any Datafile, ensure that you have all the ArchiveLogs generated since the Datafile was created and RMAN would recreate and recover the datafile for you.



UPDATE 16-Jun-20 :  Yesterday, I had demonstrated the RECOVER using only the Online Redo Log file.  Here is the same demo where the RECOVER uses ArchiveLogs as well.

Since yesterday, a few ArchiveLogs have been created.

I also add to the table :

$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 16 22:23:40 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Mon Jun 15 2020 22:34:40 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> insert into my_user_data_tbl select * from my_user_data_tbl;

72623 rows created.

SQL> /

145246 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$

Now I corrupt the datafile as before :

$cat  >/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
Corrupt the file again
and again
$cat /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
Corrupt the file again
and again
$
$sync;sync
$sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 16 22:27:15 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system flush buffer_cache;

System altered.

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 16 22:27:29 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Tue Jun 16 2020 22:23:40 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from my_user_data_tbl;
select count(*) from my_user_data_tbl
                     *
ERROR at line 1:
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 44: '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 130


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$

Now I go into RMAN

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Jun 16 22:28:48 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL12C (DBID=768045447)

RMAN> list backup of datafile 44;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> sql 'NEWPDB' 'alter database datafile 44 offline';

sql statement: alter database datafile 44 offline

RMAN> restore ( datafile 44 );

Starting restore at 16-JUN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device type=DISK

creating datafile file number=44 name=/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 16-JUN-20

RMAN> recover datafile 44;

Starting recover at 16-JUN-20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 71 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_71_hgko9xjy_.arc
archived log for thread 1 with sequence 72 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_72_hgkofp8l_.arc
archived log for thread 1 with sequence 73 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_73_hgkofpn2_.arc
archived log for thread 1 with sequence 74 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_74_hgkofsjc_.arc
archived log for thread 1 with sequence 75 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_75_hgkofsky_.arc
archived log for thread 1 with sequence 76 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_76_hgkokj9r_.arc
archived log for thread 1 with sequence 77 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_77_hgkokjvh_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_71_hgko9xjy_.arc thread=1 sequence=71
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_72_hgkofp8l_.arc thread=1 sequence=72
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_73_hgkofpn2_.arc thread=1 sequence=73
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_74_hgkofsjc_.arc thread=1 sequence=74
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_75_hgkofsky_.arc thread=1 sequence=75
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-JUN-20

RMAN> sql 'NEWPDB' 'alter database datafile 44 online';

sql statement: alter database datafile 44 online

RMAN> 
RMAN> quit


Recovery Manager complete.
$

Note how RMAN shows that ArchiveLogs 71 to 77 are still available on disk (if they had already been backed-up and deleted, I would have restored them using RESTORE ARCHIVELOG ...).
Then it shows that it applied ArchiveLogs 71 to 75.

Let's look at the alert log :

2020-06-16T22:29:48.344825+08:00
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover
 if needed datafile 44
2020-06-16T22:29:48.347256+08:00
Media Recovery Start
2020-06-16T22:29:48.369410+08:00
Serial Media Recovery started
ORA-279 signalled during: alter database recover
 if needed datafile 44
...
alter database recover logfile '/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_71_hgko9xjy_.arc'
2020-06-16T22:29:48.694688+08:00
Media Recovery Log /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_71_hgko9xjy_.arc
ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_71_hgko9xjy_.arc'...
alter database recover logfile '/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_72_hgkofp8l_.arc'
2020-06-16T22:29:49.017502+08:00
Media Recovery Log /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_72_hgkofp8l_.arc
ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_72_hgkofp8l_.arc'...
alter database recover logfile '/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_73_hgkofpn2_.arc'
2020-06-16T22:29:49.047588+08:00
Media Recovery Log /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_73_hgkofpn2_.arc
ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_73_hgkofpn2_.arc'...
alter database recover logfile '/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_74_hgkofsjc_.arc'
2020-06-16T22:29:49.059974+08:00
Media Recovery Log /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_74_hgkofsjc_.arc
ORA-279 signalled during: alter database recover logfile '/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_74_hgkofsjc_.arc'...
alter database recover logfile '/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_75_hgkofsky_.arc'
2020-06-16T22:29:49.097870+08:00
Media Recovery Log /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_75_hgkofsky_.arc
2020-06-16T22:29:49.103703+08:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 76 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl12c/redo01.log
2020-06-16T22:29:49.217227+08:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 77 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl12c/redo02.log
2020-06-16T22:29:49.442896+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 78 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl12c/redo03.log
2020-06-16T22:29:49.511976+08:00
Media Recovery Complete (orcl12c)
Completed: alter database recover logfile '/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2020_06_16/o1_mf_1_75_hgkofsky_.arc'
2020-06-16T22:29:54.072385+08:00

The alert log shows that Log Sequences 76 to 78 were the Online Redo Logs that were applied to complete the Recovery.  Note that although 76 and 77 had already been archived, since they were still available as Online Redo Logs, the RECOVER preferred to use the Online Redo Logs instead of the archive copies of these.

INSERTing a row with or without NULL explicitly ?

Here's something I've wanted to explore.

Say you have a table with a column that is not NOT NULL -- i.e. it does allow NULLs.

When you issue an INSERT statement, you can either
a. Explicitly specify a NULL value
b. Exclude the column from the list of values

Is there a difference ?

This test is in 12.2

$sqlplus hemant/hemant

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 10 22:19:26 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Wed Jun 10 2020 22:17:58 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create table my_test_table
  2  (pk_col number not null primary key,
  3   not_null_data varchar2(15) not null,
  4   nullable_data varchar2(15)
  5  )
  6  /

Table created.

SQL> 

Then, from two separate sessions (with Instance Shutdowns in-between), with Tracing enabled and disabled using DBMS_SESSION.SET_SQL_TRACE, I run two different INSERT statements :

SQL> insert into my_test_table
  2  values (1,'First Row',NULL);

1 row created.

and

SQL> insert into my_test_table 
  2  (pk_col,not_null_data)
  3  values (2,'Second Row');

1 row created.

Now check the Trace Files generated.

The first INSERT with values for all the colums but not explicitly naming the columns shows :

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.02          2          3          1           0
Execute      4      0.01       0.06         12        830         45           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.01       0.09         14        833         46           3

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       68      0.01       0.01          0          0          0           0
Execute   1475      0.08       0.13          0        156          0           0
Fetch     1825      0.02       0.07          9       5630          0       12171
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     3368      0.12       0.22          9       5786          0       12171

Misses in library cache during parse: 18
Misses in library cache during execute: 44

    5  user  SQL statements in session.
   60  internal SQL statements in session.
   65  SQL statements in session.

On the other hand, the second INSERT explicitly naming the two not null columns and excluding the nullable column shows :

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          1          1          0           0
Execute      4      0.00       0.04          7        757          7           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.05          8        758          7           3

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.00          0          0          0           0
Execute    124      0.01       0.03          0          0          0           0
Fetch      134      0.00       0.08          9        422          0         921
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      261      0.01       0.12          9        422          0         921

Misses in library cache during parse: 2
Misses in library cache during execute: 6

    5  user  SQL statements in session.
   20  internal SQL statements in session.
   25  SQL statements in session.

This is quite a huge difference between the two types of INSERT statements in terms of the RECURSIVE call overheads.

The 3 Non-Recursive Parse Calls for 
(a) DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE)
(b) COMMIT -- after the INSERT
(c) DBMS_SESSION.SET_SQL_TRACE(sql_trace=>FALSE)