09 December, 2020

Oracle Database 21c

Oracle Database 21c is now available in the Oracle Cloud.

21c is an Innovation Release so it can be used for testing of the new features.  For Long Term Support, customers still now upgrading should be upgrading to 19c.

Update : I had published this blog post on 09-Dec-20 as soon as I saw Oracle's post that 21c was available.  However, I now find that Oracle has updated the date of their post to 13-Jan-21.
I can understand web (blog) posts being updated but the actual publication date itself was changed by Oracle !
You can see Mike Dietriche's post dated 08-Dec-20   Even comments on Oracle's post supposedly of 13-Jan-21 are dated from 08-Dec-20 !  So, it is evident that the release of 21c was announced on 08-Dec-20.

21 November, 2020

Configuring Transparent Data Encryption -- 1 : For a Tablespace

 Oracle allows TDE (Transparent Data Encryption) for specific (i.e. selected) columns or a full Tablespace.


(I have also presented the steps for TDE in SQL Server).


Here is a quick demo of TDE for a Tablespace.

First, I setup a target tablespace with some data



SQL> connect hemant/hemant
Connected.
SQL> create tablespace TDE_TARGET_TBS datafile '/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf' size 100M;

Tablespace created.

SQL>
SQL>  create table TDE_TARGET_TABLE
  2  (id_col number,
  3  data_col varchar2(50))
  4  tablespace TDE_TARGET_TBS
  5  /

Table created.

SQL> insert into TDE_TARGET_TABLE
  2  select rownum,
  3  'MY DATA CONTENT : ' || rownum
  4  from dual
  5  connect by level "less than" 1001   -- the "less than" symbol replaced by string to preserve formatting for HTML
  6  /

1000 rows created.

SQL> commit;

Commit complete.

SQL>


oracle19c>dd if=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf ibs=8192 skip=128 > /tmp/dump_of_TDE_TARGET_TBS.TXT
12673+0 records in
202768+0 records out
103817216 bytes (104 MB) copied, 1.10239 s, 94.2 MB/s
oracle19c>
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS.TXT |grep 'CONTENT' |head -5
MY DATA CONTENT : 944,
MY DATA CONTENT : 945,
MY DATA CONTENT : 946,
MY DATA CONTENT : 947,
MY DATA CONTENT : 948,
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS.TXT |grep 'CONTENT' | wc -l
1000
oracle19c>


I inserted 1000 rows with the text "MY DATA CONTENT" and it is visible as plain-text when I dump the datafile.   

Note how not all the inserted rows appear to be in physical order -- the "last" 57 "records" (i.e. rows) seem to appear before the first "record" (row) as I show in this video recording of a viewing of the dump.  Never assume physical ordering of data in a datafile or when retrieving output (for ordering the results of a SELECT statement, *always* use the ORDER BY clause)








So, I now intend to encrypt the tablespace.

Step 1 : Specify the ENCRYPTION WALLET LOCATION
In earlier releases, this is specified in the sqlnet.ora file like this :

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=
   (METHOD=FILE)
    (METHOD_DATA=
     (DIRECTORY=/home/oracle/wallet)))  -- or this could be any other folder, or defaulting to $ORACLE_BASE/admin/db_unique_name/wallet


However, in 19c, Oracle recommends using the KEYSTORE_CONFIGURATION attribute of the TDE_CONFIGURATION initialization parameter after setting the WALLET_ROOT.


SQL> show parameter tde

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde     boolean     FALSE
tde_configuration                    string
SQL> show parameter wallet

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet                           string
wallet_root                          string
SQL>
SQL> alter system set wallet_root='/opt/oracle/product/19c/dbhome_1/TDE_WALLETS' scope=SPFILE;

System altered.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size                  8895920 bytes
Variable Size             318767104 bytes
Database Buffers          872415232 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter wallet

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet                           string
wallet_root                          string      /opt/oracle/product/19c/dbhome
                                                 _1/TDE_WALLETS
SQL>
SQL>  alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=SPFILE;

System altered.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size                  8895920 bytes
Variable Size             318767104 bytes
Database Buffers          872415232 bytes
Redo Buffers                7880704 bytes
Database mounted.
Database opened.
SQL> show parameter wallet

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet                           string
wallet_root                          string      /opt/oracle/product/19c/dbhome
                                                 _1/TDE_WALLETS
SQL> show parameter tde

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde     boolean     FALSE
tde_configuration                    string      KEYSTORE_CONFIGURATION=FILE
SQL>


In the case of this database, since I had not earlier configured these parameters, I had to do a manual restart for them to take effect (Note : "WALLET_ROOT" must have been configured before "TDE_CONFIGURATION" can be set, that is why I had to do an additional restart between setting the two parameters).
I have deliberately configured WALLET_ROOT to a non-default/standard location.


Step 2 : Create the KEYSTORE (under WALLET_ROOT)

This is where I actually  create the Wallet.  The syntax specifies KEYSTORE location, but can default to WALLET_ROOT as I have already defined it. I can also create an Auto-Login Keystore

SQL> administer key management create keystore identified by mysecretpassword;

keystore altered.

SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 4
-rw-------. 1 oracle oinstall 2555 Nov 21 18:38 ewallet.p12

SQL>
SQL> administer key management create LOCAL auto_login keystore
  2  from keystore '/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde'
  3  identified by mysecretpassword
  4  /

keystore altered.

SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 8
-rw-------. 1 oracle oinstall 2600 Nov 21 18:49 cwallet.sso
-rw-------. 1 oracle oinstall 2555 Nov 21 18:38 ewallet.p12

SQL>
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN_NO_MASTER_KEY             LOCAL_AUTOLOGIN      SINGLE    NONE     UNDEFINED          0


SQL>


In this case, "ewallet.p12" is the Password Protected Keystore and "cwallet.sso" is the Auto-Login Keystore (created LOCALly only, not for remote servers/clients).


Step 3 : OPEN the Keystore  (only if it is NOT already OPEN) 

I can see that the Keystore is already OPEN (from the query on v$encryption_wallet) but I could attempt OPENing it with :


SQL> administer key management set keystore open
  2  identified by mysecretpassword
  3  /
administer key management set keystore open
*
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open


SQL>
SQL> !oerr ora 28354
28354, 0000, "Encryption wallet, auto login wallet, or HSM is already open"
// *Cause:  Encryption wallet, auto login wallet, or HSM was already opened.
// *Action: None.
//

SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN_NO_MASTER_KEY             LOCAL_AUTOLOGIN      SINGLE    NONE     UNDEFINED          0


SQL>


Step 4 : Setup the Master Encryption Key

Now, I setup the Master Key (and also backup the existing key file)


SQL> administer key management set key
  2  using tag 'For_Tablespace_TDE'
  3  force keystore  -- because I am using LOCAL_AUTOLOGIN
  4  identified by mysecretpassword
  5  with backup using 'tde_key_backup'
  6  /

keystore altered.

SQL>
SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 20
-rw-------. 1 oracle oinstall 4232 Nov 21 19:04 cwallet.sso
-rw-------. 1 oracle oinstall 2555 Nov 21 19:04 ewallet_2020112111043216_tde_key_backup.p12
-rw-------. 1 oracle oinstall 4171 Nov 21 19:04 ewallet.p12

SQL>
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN                           LOCAL_AUTOLOGIN      SINGLE    NONE     NO                 0


SQL>
SQL> select key_id, creation_time, keystore_type, tag from v$encryption_keys;

KEY_ID
------------------------------------------------------------------------------
CREATION_TIME                                                               KEYSTORE_TYPE
--------------------------------------------------------------------------- -----------------
TAG
------------------------------------------------------------------------------------------------------------------------------------
AaHRyuP8yE+ivzI/hZHcOdoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
21-NOV-20 07.04.32.347090 PM +08:00                                         SOFTWARE KEYSTORE
For_Tablespace_TDE


SQL>


Now I am ready the encrypt my Tablespace.


Step 5 :  Online Encryption of Tablespace
This is a 19c feature, earlier versions required Offline Encryption.
This method creates a new datafile with encrypted data

SQL> alter tablespace TDE_TARGET_TBS
  2  encryption online
  3  using 'AES192'
  4  encrypt file_name_convert = ('/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf','/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf')
  5  /

Tablespace altered.

SQL>

oracle19c>pwd
/opt/oracle/oradata/HEMANT
oracle19c>ls -l TDE*
-rw-r-----. 1 oracle oinstall 104865792 Nov 21 19:42 TDE_TARGET_TBS_encrypted.dbf
oracle19c>
oracle19c>dd if=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf ibs=8192 skip=128 > /tmp/dump_of_TDE_TARGET_TBS_encrypted.TXT
12673+0 records in
202768+0 records out
103817216 bytes (104 MB) copied, 0.414517 s, 250 MB/s
oracle19c>
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS_encrypted.TXT |grep 'CONTENT' |head -5
oracle19c>

SQL> l
  1  select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status
  2  from v$tablespace t, v$encrypted_tablespaces e
  3* where t.ts#=e.ts#
SQL> /

NAME                                  TS# ENCRYPT ENC KEY_VERSION STATUS
------------------------------ ---------- ------- --- ----------- ----------
TDE_TARGET_TBS                          8 AES192  YES           1 NORMAL

SQL>



Oracle has replaced the TDE_TARGET_TBS.dbf file with TDE_TARGET_TBS_encrypted.dbf file. The new file does NOT have any plain-text values "CONTENT"

For subsequent Tablespaces, Steps 1 to 4 would not be required.



14 November, 2020

Active Data Guard and DML Redirection

 Active Data Guard (also known as "ADG") allows you to open a Standby Database in Read Only mode and query it while Media Recovery (i.e. Redo Apply from the Primary) is concurrently running.

Caveat : ADG requires purchase of additional licences to use this feature on a Standby Database.

If you issue the commands from sqlplus and not from dgmrl, you must first stop Media Recovery before you OPEN the database and then re-enable it on the Standby :


SQL> select FORCE_LOGGING, FLASHBACK_ON,DATABASE_ROLE,PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE
  2  from v$database;

FORCE_LOGGING                           FLASHBACK_ON       DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
--------------------------------------- ------------------ ---------------- -------------------- --------------------
OPEN_MODE
--------------------
YES                                     NO                 PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
MOUNTED


SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select FORCE_LOGGING, FLASHBACK_ON,DATABASE_ROLE,PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE
  2  from v$database;

FORCE_LOGGING                           FLASHBACK_ON       DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
--------------------------------------- ------------------ ---------------- -------------------- --------------------
OPEN_MODE
--------------------
YES                                     NO                 PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE
READ ONLY


SQL>
SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

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

Database altered.

SQL>


Thereafter, you would be able to query the Standby Database.  DML (INSERT, UPDATE, DELETE) and DDL (CREATE, DROP, ALTER etc) are *not* allowed by default.  

19c does have a DML Redirection feature ADG_REDIRECT_DML which I demonstrate below :

SQL> connect hemant/hemant@stdbypdb1
Connected.
SQL> select * from x;

D
-
X

SQL> delete x;
delete x
       *
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access


SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

Session altered.

SQL> delete x;

1 row deleted.

SQL> commit;

Commit complete.

SQL>


The actual execution of the DML will take some time as Oracle has to actually push it to the Primary database for execution and then wait for the Redo Apply to replicate back to the Standby.

PLSQL also can be executed with ADG_REDIRECT_PLSQL     which I am not demonstrating here.

Note :  According to Oracle's documentation the DML Redirect feature is for "read-mostly applications, which occasionally execute DMLs, on the standby database."    .  I wouldn't advise trying this for all sessions at the Instance ("alter system") level but only to be used occasionally at session level.

08 November, 2020

Compressing VARCHAR2 and CHAR column data

 Most applications that I see use VARCHAR2 columns.  However, there are some (including many built by "in house database designers" that use CHAR columns).  CHAR columns are for Fixed-Length character strings.  Any inserted values that are shorter than the defined length are right-padded with blanks.  Comparison semantics are also different in that VARCHAR2 uses non-padded comparison while CHAR uses blank-padding for comparison.

In this demonstration, I'd like to focus on compressibility of columns defined as VARCHAR2 and CHAR.

These scripts are executed in 19c on Linux.


First with VARCHAR2


SQL> create tablespace VC2_TBS datafile '/opt/oracle/oradata/HEMANT/VC2_TBS.dbf' size 2G;

Tablespace created.

SQL>
SQL> create table VC2_TABLE
  2  (id_col number(8), data_col_1 varchar2(50), data_col_2 varchar2(50), data_col_3 varchar2(50))
  3  pctfree 0
  4  tablespace VC2_TBS
  5  /

Table created.

SQL>
SQL> declare
  2  l_c number;
  3  begin
  4  for l_c in 1 .. 100
  5  loop
  6  insert     into VC2_TABLE
  7  select rownum, dbms_random.string('X',25), dbms_random.string('X',15), dbms_random.string('X',10)
  8  from dual
  9  connect by level "less than sign" 100001;  -- "less than sign" replaced by words to preserve HTML formatting
 10  commit;
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats('','VC2_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
  2  from user_tables
  3  where table_name = 'VC2_TABLE'
  4  /

COMPRESS COMPRESS_FOR                       NUM_ROWS       BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
DISABLED                                  10,000,000       78,437          58

SQL>
SQL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'VC2_TABLE'
  4  /

BYTES/1048576
-------------
          616

SQL>
SQL> pause For RMAN BACKUP AS COMPRESSED BACKUPSET
For RMAN BACKUP AS COMPRESSED BACKUPSET

RMAN> backup as compressed backupset tablespace VC2_TBS;

Starting backup at 08-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/VC2_TBS.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-20
channel ORA_DISK_1: finished piece 1 at 08-NOV-20
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1 tag=TAG20201108T221753 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 08-NOV-20


oracle19c>ls -l /opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1
-rw-r-----. 1 oracle oinstall 386490368 Nov  8 22:18 /opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1
oracle19c>
-- approx 369MB  RMAN Compressed Backup

SQL>
SQL> alter table VC2_TABLE
  2  move compress
  3  /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','VC2_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
  2  from user_tables
  3  where table_name = 'VC2_TABLE'
  4  /

COMPRESS COMPRESS_FOR                       NUM_ROWS       BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
ENABLED  BASIC                            10,000,000       79,027          58

SQL>
SQL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'VC2_TABLE'
  4  /

BYTES/1048576
-------------
          624

SQL>


So these are the facts for the table with 3 VARCHAR2(50) columns with random 25, 15 and 10 character-strings. 
Note : I deliberately chose dbms_random.string to ensure that I'd get very few (if any) repeatable values that are compressible themselves.

10million rows of Average Row Length of 58bytes
616MB Segment (78,437 blocks equal 613MB approx).
RMAN Compressed Size of the Datafile 369MB (approx 60% of the Segment Size)
BASIC Compression Size of the Table :  624MB  (i.e. no compression achieved because I used random strings)



Next with CHAR


SQL> create tablespace CHAR_TBS datafile '/opt/oracle/oradata/HEMANT/CHAR_TBS.dbf' size 2G;  -- subsequently resized to 4000M

Tablespace created.

SQL>
SQL> create table CHAR_TABLE
  2  (id_col number(8), data_col_1 char(50), data_col_2 char(50), data_col_3 char(50))
  3  pctfree 0
  4  tablespace CHAR_TBS
  5  /

Table created.

SQL>
SQL> declare
  2  l_c number;
  3  begin
  4  for l_c in 1 .. 100
  5  loop
  6  insert     into CHAR_TABLE
  7  select rownum, dbms_random.string('X',25), dbms_random.string('X',15), dbms_random.string('X',10)
  8  from dual
  9  connect by level "less than" 100001;  -- "less than sign" replaced by words to preserve HTML formatting
 10  commit;
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats('','CHAR_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
  2  from user_tables
  3  where table_name = 'CHAR_TABLE'
  4  /

COMPRESS COMPRESS_FOR                       NUM_ROWS       BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
DISABLED                                  10,000,000      204,116         158

SQL>
SQL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'CHAR_TABLE'
  4  /

BYTES/1048576
-------------
         1600

SQL>
SQL> pause For RMAN BACKUP AS COMPRESSED BACKUPSET
For RMAN BACKUP AS COMPRESSED BACKUPSET


RMAN> backup as compressed backupset tablespace CHAR_TBS;

Starting backup at 08-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/CHAR_TBS.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-20
channel ORA_DISK_1: finished piece 1 at 08-NOV-20
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1 tag=TAG20201108T225127 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 08-NOV-20


oracle19c>ls -l /opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1
-rw-r-----. 1 oracle oinstall 441876480 Nov  8 22:51 /opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1
oracle19c>
-- appox 421MB  RMAN Compressed Backup

SQL>
SQL> alter table CHAR_TABLE
  2  move compress
  3  /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','CHAR_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
  2  from user_tables
  3  where table_name = 'CHAR_TABLE'
  4  /

COMPRESS COMPRESS_FOR                       NUM_ROWS       BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
ENABLED  BASIC                            10,000,000      204,766         158

SQL>
SQL> select bytes/1048576
  2  from user_segments
  3  where segment_name = 'CHAR_TABLE'
  4  /

BYTES/1048576
-------------
         1600

SQL>

So these are the facts for the table with 3 CHAR(50) columns with random 25, 15 and 10 character-strings. 
 Note : I deliberately chose dbms_random.string to ensure that I'd get very few (if any) repeatable values that are compressible themselves. 

10million rows of Average Row Length of 158bytes (up from 58 bytes for the VARCHAR2 columns) 
1600MB Segment (204,116 blocks equal 1595MB approx). 
RMAN Compressed Size of the Datafile 421MB  (approx 26% of the Segment Size)
BASIC Compression Size of the Table : 1600MB (i.e. no compression achieved, because I used random strings ? -- shouldn't the right-padded blanks be compressed ?

 So, in this test comparing random strings in VARCHAR2 and CHAR, quite obviously the table with CHAR columns took up much more space.
BASIC Compression didn't achieve anything (again : because I used random strings?)

RMAN default compression ("CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ") achieved better compression with CHAR

Of course, since I used random strings and you might have actual data that is compressible and the length of the actual strings inserted into the CHAR columns may be different in your production / test table, you would see different levels of compression achieved.

The point is that compression success depends not just on the type of data but can be different whether you use BASIC compression in the tablespace or the default BASIC in RMAN.

Note :  Given RMAN's default behaviour of "unused block compression" do not expect RMAN to have to backup all the blocks in entire 2GB or 4000MB datafile  -- particularly because I specifically create the tablespace just before the test and it doesn't have any other objects (segments).


01 November, 2020

An SQL Server Blog

 As I have recently started working with SQL Server, I have created a new SQL Server Blog.

I will be periodically updating both my Oracle and SQL Server blogs.


29 October, 2020

Getting your SQL Statement's SQL_ID

 SQL*Plus now can provide you the SQL_ID of the last statement executed in your own session with SET FEEDBACK SQL_ID.

A quick demo :


SQL> set feedback on sql_id
SQL> select count(*) from my_target where factory='SYS';

  COUNT(*)
----------
     52217

1 row selected.

SQL_ID: g1mk14hdxc1ww
SQL> select * from table(dbms_xplan.display_cursor('g1mk14hdxc1ww'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g1mk14hdxc1ww, child number 0
-------------------------------------
select count(*) from my_target where factory='SYS'

Plan hash value: 1690349505

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     6 (100)|          |
|   1 |  SORT AGGREGATE   |               |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| MY_TARGET_NDX |  2683 | 13415 |     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("FACTORY"='SYS')


19 rows selected.

SQL_ID: 5dyyqqwuyu01v
SQL>


After I executed my query against the "my_target" table, SQL*Plus provided my the SQL_ID ("g1mk14hdxc1ww").  I could then immediately get the Execution Plan for the statement, without having to query for the SQL_ID in V$SQL or V$SQLAREA.

This demonstration is with SQL*Plus 19.0 against a 19c Database.  (I think "set feedback on sql_id" was introduced in SQL*Plus 18)




11 October, 2020

On-Premises SQL Server to Oracle ADB on the Cloud -- 2

 Having configured connectivity between an On-Premises SQL Server Database to an Oracle ADB Database in the Oracle Cloud in the previous post, I will now copy data from SQL Server to Oracle


  • Create the Target Table in Oracle ADB 

SQL Developer


  • Verify Existing Rows in SQL Server


SQL Server Management Studio

  • Insert into Oracle with Select from SQL Server

SQL Server Management Studio


  • Verify Rows Inserted into Oracle

SQL Developer


  • Create New Row in SQL Server and Verify that it is NOT present in Oracle
SQL Server Management Studio


The last screenshot is literally a NOT IN query between SQL Server On-Premises and Oracle in the Cloud.












10 October, 2020

On-Premises SQL Server to Oracle ADB on the Cloud - 1

Setting up a connection between an On-Premises SQL Server Database Instance to an Oracle ADB on the Oracle Cloud

Strong Caveat :  This is only a POC.  Most organisations would NOT allow an open direct connection between an On-Premises Database and and External Site (whether a Database or any other Service).  

1. On my Free-Tier Oracle ADB, I login as the ADMIN user and configure a new database account "ss_user"

SQL Developer to Oracle ADB


2. I install Oracle Client and configure connectivity to the ADB database with the Wallet information

(for detailed instructions see the Oracle Cloud documentation here).  {I used an 18c Client on Windows to connect to 19c ADB, simply because I already and 18c client and didn't want to wait to download the 19c client}

sqlnet.ora



tnsnames.ora



3.  Optionally re-register the OraOLEDB18.DLL file  (using CMD as Administrator)  (you might also need to reboot your Windows or restart the SQL Server Instance)

CMD as Administrator




4.  Define the Linked Server in SQL Server (using SSMS)


Oracle OLEDB Provider




Linked Server Configuration Pag


Linked Server Security Configuration


5.  Test Connectivity

Test Connectivity Option for Linked Server



6  Run queries against from SSMS


SSMS Query and Results Pane


See the next blog post on actual transaction and query (insert and select from On-Premises SQL Server to Oracle ADB in the cloud)

03 October, 2020

Extracting DDL using SQL Developer

 The "DDL" command in SQL Developer 20.2 new mimics the command in sqlcl


Here I extract the definition my table OBJECTS_LIST and it's index



Here I extract the code for a Stored Procedure





29 September, 2020

Verifying an RMAN Backup -- Part 2

UPDATE :  I say "Verify" in this (and the previous blog post)  and not VALIDATE because VALIDATE has a different meaning in RMAN.  See these posts RESTORE DATABASE VALIDATE and BACKUP VALIDATE


Continuing on my previous blog post,  the question being "when you receive an RMAN Backup from another DBA, how do you confirm that the database can be restored and recovered to a Consistent Point In Time ?"

The quick steps, without actually running a RESTORE DATABASE command are :

1. Create a dummy parameter file with
    a. DB_NAME the same as the source database
    b. A different DB_UNIQUE_NAME (particulary if you have an existing database on the target server with the same DB_NAME)
    c. CONTROL_FILE specifying a "temporary" location -- you will be removing the control files and restoring them to the actual desired target location when you choose to do a Full Restore
2. Restore the Controlfile
3. Remove all entries about RMAN Backups from the Controlfile (as it has a history of recent backups and may even be a Controlfile backup newer than the Database backup that is provided to you, capturing more recent backups
4. Catalog the set of Backup Pieces that you receive
5. Query the catalog that you now create in the Controlfile to check the ArchiveLogs vis-a-vis the Datafiles in the set of Backup Pieces.


So, I'll demonstrate them again in 19c and a Non-CDB database here.  The source Database DB_NAME is "HEMANT" so I create in RTST parameter file with DB_NAME='HEMANT' and DB_UNIQUE_NAME='RTST'

I then restore the Controlfile, remove all entries of previous backups, CATALOG the Backup Pieces that I have received and then query the Controlfile.  (The CATALOG START WITH updates the Controlfile with information from the Backup Pieces, although the REPORT SCHEMA command is from the database structure in the controlfile).



oracle19c>echo $ORACLE_SID
RTST
oracle19c>cat $ORACLE_HOME/dbs/initRTST.ora
db_name = 'HEMANT'
db_unique_name = 'RTST'
control_files='/tmp/RTST_control.ctl'
#enable_pluggable_database=true
oracle19c>
oracle19c>cd HEMANT_DB_Backup
oracle19c>pwd
/home/oracle/HEMANT_DB_Backup
oracle19c>ls -l
total 140504
-rw-r-----. 1 oracle oinstall  4390912 Sep 29 22:01 0cvbkgui_1_1
-rw-r-----. 1 oracle oinstall 58507264 Sep 29 22:01 0evbkh0d_1_1
-rw-r-----. 1 oracle oinstall  6381568 Sep 29 22:01 0fvbkh0k_1_1
-rw-r-----. 1 oracle oinstall 51978240 Sep 29 22:01 0gvbkh0r_1_1
-rw-r-----. 1 oracle oinstall  2179072 Sep 29 22:01 0hvbkh12_1_1
-rw-r-----. 1 oracle oinstall  1622016 Sep 29 22:01 0ivbkh13_1_1
-rw-r-----. 1 oracle oinstall  4863488 Sep 29 22:01 0jvbkh14_1_1
-rw-r-----. 1 oracle oinstall  2187264 Sep 29 22:01 0kvbkh14_1_1
-rw-r-----. 1 oracle oinstall 11763712 Sep 29 22:01 c-432411782-20200929-06
oracle19c>
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Sep 29 22:05:03 2020
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area     268434280 bytes

Fixed Size                     8895336 bytes
Variable Size                201326592 bytes
Database Buffers              50331648 bytes
Redo Buffers                   7880704 bytes

RMAN> restore controlfile from '/home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06';

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

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/RTST_control.ctl
Finished restore at 29-SEP-20

RMAN>
RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> 
RMAN> delete backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
12      12      1   1   AVAILABLE   DISK        /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
13      13      1   1   AVAILABLE   DISK        /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
14      14      1   1   AVAILABLE   DISK        /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
15      15      1   1   AVAILABLE   DISK        /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
16      16      1   1   AVAILABLE   DISK        /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
17      17      1   1   AVAILABLE   DISK        /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
18      18      1   1   AVAILABLE   DISK        /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
19      19      1   1   AVAILABLE   DISK        /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
20      20      1   1   AVAILABLE   DISK        /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
21      21      1   1   AVAILABLE   DISK        /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05

Do you really want to delete the above objects (enter YES or NO)? YES

RMAN-06207: warning: 10 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
RMAN-06214: Backup Piece    /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
RMAN-06214: Backup Piece    /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
RMAN-06214: Backup Piece    /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
RMAN-06214: Backup Piece    /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
RMAN-06214: Backup Piece    /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
RMAN-06214: Backup Piece    /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
RMAN-06214: Backup Piece    /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
RMAN-06214: Backup Piece    /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
RMAN-06214: Backup Piece    /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05


RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Crosschecked 10 objects


RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
12      12      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
13      13      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
14      14      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
15      15      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
16      16      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
17      17      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
18      18      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
19      19      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
20      20      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
21      21      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Deleted 10 EXPIRED objects


RMAN>
RMAN> list backup;

specification does not match any backup in the repository

RMAN> catalog start with '/home/oracle/HEMANT_DB_Backup/';

searching for all files that match the pattern /home/oracle/HEMANT_DB_Backup/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

RMAN>
RMAN> report schema;

RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RTST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    400      SYSTEM               ***     /opt/oracle/oradata/HEMANT/system.dbf
2    400      SYSAUX               ***     /opt/oracle/oradata/HEMANT/sysaux.dbf
3    200      UNDOTBS1             ***     /opt/oracle/oradata/HEMANT/undotbs.dbf
4    10       USERS                ***     /opt/oracle/oradata/HEMANT/users01.dbf
5    10       INDX                 ***     /opt/oracle/oradata/HEMANT/indx01.dbf
6    10       USERS                ***     /opt/oracle/oradata/HEMANT/users02.dbf
7    10       USERS                ***     /opt/oracle/oradata/HEMANT/users03.dbf
8    10       USERS                ***     /opt/oracle/oradata/HEMANT/users04.dbf
9    10       USERS                ***     /opt/oracle/oradata/HEMANT/users05.dbf
10   10       INDX                 ***     /opt/oracle/oradata/HEMANT/indx02.dbf
11   10       INDX                 ***     /opt/oracle/oradata/HEMANT/indx03.dbf

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 29 22:07:56 2020
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> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select file#, checkpoint_change#, checkpoint_time, completion_time
  2  from v$backup_datafile
  3  order by 1
  4  /

     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME    COMPLETION_TIME
---------- ------------------ ------------------ ------------------
         0             463290 29-SEP-20 11:49:14 29-SEP-20 11:49:15
         1             456249 29-SEP-20 11:48:29 29-SEP-20 11:48:34
         2             457590 29-SEP-20 11:48:36 29-SEP-20 11:48:39
         3             458680 29-SEP-20 11:48:43 29-SEP-20 11:48:47
         4             459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
         5             459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
         6             459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
         7             459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
         8             459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
         9             459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
        10             458680 29-SEP-20 11:48:43 29-SEP-20 11:48:43
        11             456249 29-SEP-20 11:48:29 29-SEP-20 11:48:29

12 rows selected.

SQL>
SQL> select file#, checkpoint_change#, checkpoint_time, completion_time
  2  from v$backup_datafile
  3  order by 2
  4  /

     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME    COMPLETION_TIME
---------- ------------------ ------------------ ------------------
         1             456249 29-SEP-20 11:48:29 29-SEP-20 11:48:34
        11             456249 29-SEP-20 11:48:29 29-SEP-20 11:48:29
         2             457590 29-SEP-20 11:48:36 29-SEP-20 11:48:39
         3             458680 29-SEP-20 11:48:43 29-SEP-20 11:48:47
        10             458680 29-SEP-20 11:48:43 29-SEP-20 11:48:43
         4             459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
         7             459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
         5             459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
         8             459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
         6             459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
         9             459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
         0             463290 29-SEP-20 11:49:14 29-SEP-20 11:49:15

12 rows selected.

SQL>
SQL> select sequence#, first_change#, next_change#-1, next_time
  2  from v$backup_archivelog_details
  3  order by sequence#
  4  /

 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#-1 NEXT_TIME
---------- ------------- -------------- ------------------
       170        442901         448665 29-SEP-20 11:45:17
       171        448666         450050 29-SEP-20 11:45:51
       172        450051         451367 29-SEP-20 11:47:30
       173        451368         454869 29-SEP-20 11:48:19
       174        454870         457557 29-SEP-20 11:48:33
       175        457558         457611 29-SEP-20 11:48:41
       176        457612         459744 29-SEP-20 11:48:48
       177        459745         459767 29-SEP-20 11:48:52

8 rows selected.

SQL>


In this case, file#=0  is actually the Controlfile --- so it has the highest Checkpoint SCN and Time.  As I noted in my previous post, it doesn't matter that the Controlfile is "newer" than the Datafiles.  We need to check the Datafiles with the ArchiveLogs. So, we see that the datafiles have slightly different Checkpoint SCNs (the backup was created with FILESPERSET=2 so every pair of datafiles has a Checkpoint).  The highest Datafile Checkpoint is 459779.  But the ArchiveLogs end at 459767.  Therefore, this database cannot be RECOVERed to a Consistent Point In Time.

Should I try doing a RESTORE and RECOVER, nevertheless ?

I first revert to ORACLE_SID=HEMANT and use the initHEMANT.ora parameter file that I obtained from the source server.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>ORACLE_SID=HEMANT;export ORACLE_SID
oracle19c>ls -l $ORACLE_HOME/dbs/initHEMANT.ora
-rw-r--r--. 1 oracle oinstall 693 Sep 28 23:05 /opt/oracle/product/19c/dbhome_1/dbs/initHEMANT.ora
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Sep 29 22:25:13 2020
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN>
RMAN> startup nomount;

Oracle instance started

Total System Global Area    1207958960 bytes

Fixed Size                     8895920 bytes
Variable Size                318767104 bytes
Database Buffers             872415232 bytes
Redo Buffers                   7880704 bytes

RMAN> restore controlfile from '/home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06';

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

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/oracle/oradata/HEMANT/control01.ctl
output file name=/opt/oracle/oradata/HEMANT/control02.ctl
Finished restore at 29-SEP-20

RMAN>
RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> crosscheck backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Crosschecked 10 objects


RMAN> delete noprompt expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
12      12      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
13      13      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
14      14      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
15      15      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
16      16      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
17      17      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
18      18      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
19      19      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
20      20      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
21      21      1   1   EXPIRED     DISK        /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Deleted 10 EXPIRED objects


RMAN>
RMAN> catalog start with '/home/oracle/HEMANT_DB_Backup/';

searching for all files that match the pattern /home/oracle/HEMANT_DB_Backup/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

RMAN>
RMAN> restore database;

Starting restore at 29-SEP-20
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/HEMANT/system.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00002 to /opt/oracle/oradata/HEMANT/sysaux.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00003 to /opt/oracle/oradata/HEMANT/undotbs.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/HEMANT/indx02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00004 to /opt/oracle/oradata/HEMANT/users01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/HEMANT/users03.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1 tag=TAG20200929T114829
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 00005 to /opt/oracle/oradata/HEMANT/indx01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /opt/oracle/oradata/HEMANT/users04.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1 tag=TAG20200929T114829
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 00006 to /opt/oracle/oradata/HEMANT/users02.dbf
channel ORA_DISK_1: restoring datafile 00009 to /opt/oracle/oradata/HEMANT/users05.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-SEP-20

RMAN>
RMAN> list backup of archivelog all;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
22      4.19M      DISK        00:00:00     29-SEP-20
        BP Key: 22   Status: AVAILABLE  Compressed: YES  Tag: TAG20200929T114730
        Piece Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1

  List of Archived Logs in backup set 22
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    170     442901     29-SEP-20 448666     29-SEP-20
  1    171     448666     29-SEP-20 450051     29-SEP-20
  1    172     450051     29-SEP-20 451368     29-SEP-20

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
28      4.64M      DISK        00:00:01     29-SEP-20
        BP Key: 28   Status: AVAILABLE  Compressed: YES  Tag: TAG20200929T114852
        Piece Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1

  List of Archived Logs in backup set 28
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    173     451368     29-SEP-20 454870     29-SEP-20
  1    174     454870     29-SEP-20 457558     29-SEP-20
  1    175     457558     29-SEP-20 457612     29-SEP-20
  1    176     457612     29-SEP-20 459745     29-SEP-20
  1    177     459745     29-SEP-20 459768     29-SEP-20

RMAN>
RMAN> recover database until sequence 178;

Starting recover at 29-SEP-20
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/29/2020 22:31:03
RMAN-06556: datafile 6 must be restored from backup older than SCN 459768

RMAN>
RMAN> restore archivelog all;

Starting restore at 29-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/29/2020 22:35:48
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 178 and starting SCN of 459768 found to restore

RMAN> restore archivelog until sequence 178;

Starting restore at 29-SEP-20
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/29/2020 22:36:17
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 178 and starting SCN of 459768 found to restore

RMAN>
RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name HEMANT
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
9       1    178     A 29-SEP-20
        Name: /opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf


RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
validation failed for archived log
archived log file name=/opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf RECID=9 STAMP=1052394543
Crosschecked 1 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
List of Archived Log Copies for database with db_unique_name HEMANT
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
9       1    178     X 29-SEP-20
        Name: /opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf RECID=9 STAMP=1052394543
Deleted 1 EXPIRED objects


RMAN>
RMAN> restore archivelog all;

Starting restore at 29-SEP-20
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/29/2020 22:37:59
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 178 and starting SCN of 459768 found to restore

RMAN> restore archivelog until sequence 177;

Starting restore at 29-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=170
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=171
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=172
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1 tag=TAG20200929T114730
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=173
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=174
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=175
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=176
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=177
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1 tag=TAG20200929T114852
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-SEP-20

RMAN>
RMAN> exit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 29 22:48:53 2020
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> alter database recover using backup controlfile until cancel;
alter database recover using backup controlfile until cancel
*
ERROR at line 1:
ORA-00279: change 456249 generated at 09/29/2020 11:48:29 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf
ORA-00280: change 456249 for thread 1 is in sequence #174


SQL> 
SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 457558 generated at 09/29/2020 11:48:33 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf
ORA-00280: change 457558 for thread 1 is in sequence #175
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf' no longer needed for this recovery


SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 457612 generated at 09/29/2020 11:48:41 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf
ORA-00280: change 457612 for thread 1 is in sequence #176
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf' no longer needed for this recovery


SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 459745 generated at 09/29/2020 11:48:48 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf
ORA-00280: change 459745 for thread 1 is in sequence #177
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf' no longer needed for this recovery


SQL>  alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf';
 alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 459768 generated at 09/29/2020 11:48:52 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf
ORA-00280: change 459768 for thread 1 is in sequence #178
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf' no longer needed for this recovery


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-01153: an incompatible media recovery is active


SQL> alter database recover cancel;
alter database recover cancel
*
ERROR at line 1:
ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 6 was not restored from a sufficiently old backup
ORA-01110: data file 6: '/opt/oracle/oradata/HEMANT/users02.dbf'


SQL>



ArchiveLog Sequence#178 had been created in the source server before the controlfile backup but is not in the Backup Pieces I received.  So, Oracle refuses to allow me to RECOVER the database. 
A RESTORE is succesful, but the RECOVER fails.  The database cannot be OPENed.
Datafiles 6 and 9 have a higher Checkpoint SCN than the highest available in the ArchiveLogs.

Unfortunately, the default behaviour of Oracle is only to report the first Datafiles that has a higher SCN, it doesn't report all of them --- the database might have had 10 or 100 Datafiles that are "newer" than the ArchiveLogs.  That is why the SQL queries on V$BACKUP_DATAFILE and V$BACKUP_ARCHIVELOG_DETAILS that I have demonstrated earlier in this post are useful.


This is what the alert log shows :


2020-09-29T22:29:17.560085+08:00
Full restore complete of datafile 1 /opt/oracle/oradata/HEMANT/system.dbf.  Elapsed time: 0:00:06
  checkpoint is 456249
2020-09-29T22:29:23.415906+08:00
Full restore complete of datafile 2 /opt/oracle/oradata/HEMANT/sysaux.dbf.  Elapsed time: 0:00:05
  checkpoint is 457590
  last deallocation scn is 450639
2020-09-29T22:29:25.874043+08:00
Full restore complete of datafile 10 /opt/oracle/oradata/HEMANT/indx02.dbf.  Elapsed time: 0:00:00
  checkpoint is 458680
  last deallocation scn is 3
2020-09-29T22:29:29.812208+08:00
Full restore complete of datafile 3 /opt/oracle/oradata/HEMANT/undotbs.dbf.  Elapsed time: 0:00:04
  checkpoint is 458680
  last deallocation scn is 3
2020-09-29T22:29:33.129942+08:00
Full restore complete of datafile 4 /opt/oracle/oradata/HEMANT/users01.dbf.  Elapsed time: 0:00:01
  checkpoint is 459759
  last deallocation scn is 3
Full restore complete of datafile 7 /opt/oracle/oradata/HEMANT/users03.dbf.  Elapsed time: 0:00:01
  checkpoint is 459759
  last deallocation scn is 3
Full restore complete of datafile 5 /opt/oracle/oradata/HEMANT/indx01.dbf.  Elapsed time: 0:00:00
  checkpoint is 459765
  last deallocation scn is 3
Full restore complete of datafile 8 /opt/oracle/oradata/HEMANT/users04.dbf.  Elapsed time: 0:00:01
  checkpoint is 459765
  last deallocation scn is 3
2020-09-29T22:29:35.182200+08:00
Full restore complete of datafile 6 /opt/oracle/oradata/HEMANT/users02.dbf.  Elapsed time: 0:00:01
  checkpoint is 459779
  last deallocation scn is 3
Full restore complete of datafile 9 /opt/oracle/oradata/HEMANT/users05.dbf.  Elapsed time: 0:00:01
  checkpoint is 459779
  last deallocation scn is 3
2020-09-29T22:34:44.026271+08:00
alter database recover using backup controlfile
2020-09-29T22:34:44.026373+08:00
Media Recovery Start
 Started logmerger process
2020-09-29T22:34:44.322629+08:00
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: alter database recover using backup controlfile...
2020-09-29T22:35:34.263529+08:00
*************************************************************


2020-09-29T22:49:27.004784+08:00
alter database recover using backup controlfile until cancel
2020-09-29T22:49:27.004864+08:00
Media Recovery Start
 Started logmerger process
2020-09-29T22:49:27.132583+08:00
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: alter database recover using backup controlfile until cancel...
2020-09-29T22:50:52.692824+08:00
alter database recover using backup controlfile
2020-09-29T22:50:52.692943+08:00
Media Recovery Start
ORA-275 signalled during: alter database recover using backup controlfile...
2020-09-29T22:52:19.356431+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf'
2020-09-29T22:52:19.356498+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf'...
2020-09-29T22:52:36.435252+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf'
2020-09-29T22:52:36.435374+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf'...
2020-09-29T22:52:51.906865+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf'
2020-09-29T22:52:51.906956+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf'...
2020-09-29T22:53:18.228572+08:00
 alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf'
2020-09-29T22:53:18.228668+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf
ORA-279 signalled during:  alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf'...
2020-09-29T22:53:25.958701+08:00
alter database open resetlogs
2020-09-29T22:53:26.113916+08:00
Recovery interrupted!
ORA-10877 signalled during: alter database open resetlogs...
2020-09-29T22:53:35.846419+08:00
2020-09-29T22:53:35.846419+08:00
alter database recover cancel
ORA-1112 signalled during: alter database recover cancel...
2020-09-29T22:54:03.274546+08:00
alter database open resetlogs
2020-09-29T22:54:03.306918+08:00
Signalling error 1152 for datafile 6!
ORA-1152 signalled during: alter database open resetlogs...


So, even if I manually RESTORE the ArchiveLogs and then apply each one with the RECOVER LOGFILE command, Oracle still doesn't allow an OPEN RESETOGS because Sequence#178 is missing.


25 September, 2020

Verifying an RMAN Backup

 In general, most database backups with RMAN always include the "correct" set of ArchiveLogs.  This is done either with :
1. BACKUP DATABASE PLUS ARCHIVELOG
or
2. ALTER SYSTEM ARCHIVE LOG CURRENT ;  followed by BACKUP ARCHIVELOG

But if you receive a Backup from another DBA, can you validate that you have all the ArchiveLogs required to RECOVER DATABASE upto a consistent point (SEQUENCE# or TIME ?) ?
If you use an RMAN Catalog schema, you can query that RMAN Catalog schema for information.
But if there is no RMAN Catalog schema, all the information you need is in the Controlfile backup
One technique that can be used is
1. Create a dummy parameter file with
    a. DB_NAME the same as the source database
    b. A different DB_UNIQUE_NAME (particulary if you have an existing database on the target server with the same DB_NAME)
    c. CONTROL_FILE specifying a "temporary" location -- you will be removing the control files and restoring them to the actual desired target location when you choose to do a Full Restore
2. Restore the Controlfile
3. Remove all entries about RMAN Backups from the Controlfile (as it has a history of recent backups and may even be a Controlfile backup newer than the Database backup that is provided to you, capturing more recent backups
4. Catalog the set of Backup Pieces that you receive
5. Query the catalog that you now create in the Controlfile to check the ArchiveLogs vis-a-vis the Datafiles in the set of Backup Pieces.

At the end of the exercise, I can discard the "temporary" parameter file that I used and also remove the Contolfile that I have restored.
If I find that the Backup is Good (i.e. ArchiveLogs contain enough Redo (SCNs) to RECOVER the datafiles, I can do a proper RESTORE DATABASE and RECOVER DATABASE or DUPLICATE DATABASE from the Backup.


Let's say that I receive Backup Pieces, organised as would be an FRA :

$pwd
/u01/app/Backup_from_Source/ORCL12C
$ls -l
total 32
drwxr-x--- 3 oracle oinstall 4096 Sep 18  2017 49BFE9E2D73E2038E0530100007F846C
drwxr-x--- 3 oracle oinstall 4096 Sep 18  2017 49BFF8A6BB912582E0530100007F8BE4
drwxr-x--- 3 oracle oinstall 4096 Jun  5  2017 4F793A6D323D1344E0530100007FABC7
drwxr-x--- 3 oracle oinstall 4096 Sep 18  2017 53F8012866211264E0530100007FD493
drwxr-x--- 3 oracle oinstall 4096 Jan 13  2018 5C9E4689632518EBE0530100007F03C5
drwxr-x--- 3 oracle oinstall 4096 Jun 17 22:36 A84987FDF4C51164E0530100007FEB9C
drwxr-x--- 3 oracle oinstall 4096 Sep 24 17:45 autobackup
drwxr-x--- 3 oracle oinstall 4096 Sep 24 17:45 backupset
$


I first create a parameter file as :

$cat initRTST.ora
db_name = 'ORCL12C'
db_unique_name = 'RTST'
control_files='/tmp/RTST_control.ctl'
enable_pluggable_database=true
$


Then, with ORACLE_SID set to RTST, I restore and mount the Controlfile

$ORACLE_SID=RTST;export ORACLE_SID
$ls -l /tmp/RT*
ls: cannot access /tmp/RT*: No such file or directory
$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 25 23:03:06 2020

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.2/db_1/dbs/initRTST.ora';
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size		    8792152 bytes
Variable Size		  251660200 bytes
Database Buffers	   50331648 bytes
Redo Buffers		    7983104 bytes
SQL> 
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Sep 25 23:03:50 2020

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

connected to target database: ORCL12C (not mounted)

RMAN> restore controlfile from '/u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp';

Starting restore at 25-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=179 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/RTST_control.ctl
Finished restore at 25-SEP-20

RMAN> 
RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> 


Next, I "clear" information about all other backups from the controlfile.

RMAN> delete noprompt backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=182 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
54      54      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp
55      55      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp
56      56      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp
57      57      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp
58      58      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp
59      59      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp
60      60      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp
61      61      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp
62      62      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp
63      63      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
64      64      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp
65      65      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp
66      66      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
67      67      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
68      68      1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp RECID=63 STAMP=1051983568
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp RECID=64 STAMP=1051983703
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp RECID=65 STAMP=1051983676
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp RECID=66 STAMP=1051983711
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp RECID=67 STAMP=1051983712
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp RECID=68 STAMP=1051983737
Deleted 6 objects

RMAN-06207: warning: 9 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece    /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp
RMAN-06214: Backup Piece    /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp
RMAN-06214: Backup Piece    /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp
RMAN-06214: Backup Piece    /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp
RMAN-06214: Backup Piece    /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp
RMAN-06214: Backup Piece    /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp
RMAN-06214: Backup Piece    /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp
RMAN-06214: Backup Piece    /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp
RMAN-06214: Backup Piece    /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp


RMAN> 
RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp RECID=54 STAMP=1051891224
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp RECID=55 STAMP=1051891279
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp RECID=56 STAMP=1051891314
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp RECID=57 STAMP=1051891339
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp RECID=58 STAMP=1051891355
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp RECID=59 STAMP=1051891372
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp RECID=60 STAMP=1051891375
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp RECID=61 STAMP=1051891431
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp RECID=62 STAMP=1051891432
Crosschecked 9 objects


RMAN> delete noprompt expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
54      54      1   1   EXPIRED     DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp
55      55      1   1   EXPIRED     DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp
56      56      1   1   EXPIRED     DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp
57      57      1   1   EXPIRED     DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp
58      58      1   1   EXPIRED     DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp
59      59      1   1   EXPIRED     DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp
60      60      1   1   EXPIRED     DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp
61      61      1   1   EXPIRED     DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp
62      62      1   1   EXPIRED     DISK        /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp RECID=54 STAMP=1051891224
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp RECID=55 STAMP=1051891279
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp RECID=56 STAMP=1051891314
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp RECID=57 STAMP=1051891339
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp RECID=58 STAMP=1051891355
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp RECID=59 STAMP=1051891372
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp RECID=60 STAMP=1051891375
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp RECID=61 STAMP=1051891431
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp RECID=62 STAMP=1051891432
Deleted 9 EXPIRED objects


RMAN> list backup;

specification does not match any backup in the repository

RMAN> 


Now I am ready the catalog the Backup Pieces that I have received

RMAN> catalog start with '/u01/app/Backup_from_Source/ORCL12C';

searching for all files that match the pattern /u01/app/Backup_from_Source/ORCL12C

List of Files Unknown to the Database
=====================================
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp

RMAN> 


Note how the CATALOG command found 3 PDBs in the Backup.
I can now query from RMAN to get information

RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69      Full    163.55M    DISK        00:01:06     24-SEP-20      
        BP Key: 69   Status: AVAILABLE  Compressed: YES  Tag: TAG20200924T173928
        Piece Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
  List of Datafiles in backup set 69
  Container ID: 2, PDB Name: PDB$SEED
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  5       Full 1443131    02-MAR-17              NO    /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
  6       Full 1443131    02-MAR-17              NO    /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
  8       Full 1443131    02-MAR-17              NO    /u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
70      Full    503.88M    DISK        00:01:46     24-SEP-20      
        BP Key: 70   Status: AVAILABLE  Compressed: YES  Tag: TAG20200924T173928
        Piece Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
  List of Datafiles in backup set 70
  Container ID: 3, PDB Name: ORCL
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  9       Full 3285704    24-SEP-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
  10      Full 3285704    24-SEP-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
  11      Full 3285704    24-SEP-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
  12      Full 3285704    24-SEP-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
  13      Full 3285704    24-SEP-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
  14      Full 3285704    24-SEP-20              NO    /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71      Full    18.22M     DISK        00:00:01     24-SEP-20      
        BP Key: 71   Status: AVAILABLE  Compressed: NO  Tag: TAG20200924T174150
        Piece Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
  SPFILE Included: Modification time: 24-SEP-20
  SPFILE db_unique_name: ORCL12C
  Control File Included: Ckp SCN: 3286161      Ckp time: 24-SEP-20

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72      Full    18.22M     DISK        00:00:00     24-SEP-20      
        BP Key: 72   Status: AVAILABLE  Compressed: NO  Tag: TAG20200924T174333
        Piece Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp
  SPFILE Included: Modification time: 24-SEP-20
  SPFILE db_unique_name: ORCL12C
  Control File Included: Ckp SCN: 3286305      Ckp time: 24-SEP-20

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
73      Full    161.83M    DISK        00:00:19     24-SEP-20      
        BP Key: 73   Status: AVAILABLE  Compressed: YES  Tag: TAG20200924T173928
        Piece Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
  List of Datafiles in backup set 73
  Container ID: 4, PDB Name: NEWPDB
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  41      Full 3286164    24-SEP-20              NO    /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf
  42      Full 3286164    24-SEP-20              NO    /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf
  43      Full 3286164    24-SEP-20              NO    /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf
  44      Full 3286164    24-SEP-20              NO    /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_my_user__hgnbjwg7_.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
74      23.55M     DISK        00:00:03     24-SEP-20      
        BP Key: 74   Status: AVAILABLE  Compressed: YES  Tag: TAG20200924T174142
        Piece Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp

  List of Archived Logs in backup set 74
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    73      3030419    17-JUN-20 3033316    17-JUN-20
  1    74      3033316    17-JUN-20 3033319    17-JUN-20
  1    75      3033319    17-JUN-20 3033326    17-JUN-20
  1    76      3033326    17-JUN-20 3033329    17-JUN-20
  1    77      3033329    17-JUN-20 3033340    17-JUN-20
  1    78      3033340    17-JUN-20 3033343    17-JUN-20
  1    79      3033343    17-JUN-20 3033358    17-JUN-20
  1    80      3033358    17-JUN-20 3035646    17-JUN-20
  1    81      3035646    17-JUN-20 3035675    17-JUN-20
  1    82      3035675    17-JUN-20 3036658    17-JUN-20
  1    83      3036658    17-JUN-20 3038913    09-JUL-20
  1    84      3038913    09-JUL-20 3057240    13-JUL-20
  1    85      3057240    13-JUL-20 3163574    23-SEP-20
  1    86      3163574    23-SEP-20 3165215    23-SEP-20
  1    87      3165215    23-SEP-20 3165221    23-SEP-20
  1    88      3165221    23-SEP-20 3165687    23-SEP-20
  1    89      3165687    23-SEP-20 3165755    23-SEP-20
  1    90      3165755    23-SEP-20 3165858    23-SEP-20
  1    91      3165858    23-SEP-20 3167178    23-SEP-20
  1    92      3167178    23-SEP-20 3168603    23-SEP-20
  1    93      3168603    23-SEP-20 3284332    24-SEP-20
  1    94      3284332    24-SEP-20 3285739    24-SEP-20
  1    95      3285739    24-SEP-20 3285960    24-SEP-20
  1    96      3285960    24-SEP-20 3286131    24-SEP-20

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
75      Full    327.08M    DISK        00:00:31     24-SEP-20      
        BP Key: 75   Status: AVAILABLE  Compressed: YES  Tag: TAG20200924T173928
        Piece Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp
  List of Datafiles in backup set 75
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 3286067    24-SEP-20              NO    /u01/app/oracle/oradata/orcl12c/system01.dbf
  3       Full 3286067    24-SEP-20              NO    /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
  7       Full 3286067    24-SEP-20              NO    /u01/app/oracle/oradata/orcl12c/users01.dbf
  15      Full 3286067    24-SEP-20              NO    /u01/app/oracle/oradata/orcl12c/undotbs2.dbf

RMAN> 


From "eyeballing" the output, I can see that :
a. the highest Checkpoint SCN for datafiles is 3286164 (for PDB "NEWPDB")
but
b. the highest ArchiveLog SCN is 3286130 (3286131-1) from Sequence#96.
Quite obviously, I do not have enough Redo Information in the ArchiveLogs to be able to RECOVER to a consistent SCN.

Of course, the RMAN LIST BACKUP listing is quite short here.  What if it was very long  ?  How would I "query" ?  Using SQL, of course.

$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 25 23:18:31 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 session set nls_date_format='DD-MON-RR HH24:MI';

Session altered.

SQL> 
SQL> select df.con_id, max(df.checkpoint_change#)
  2  from v$backup_datafile df, v$database d
  3  where df.resetlogs_change#=d.resetlogs_change#
  4  and df.con_id > 0
  5  group by df.con_id
  6  /

    CON_ID MAX(DF.CHECKPOINT_CHANGE#)
---------- --------------------------
         1	              3286305
         2	              1443131
         3	              3285704
         4	              3286164

SQL> 
SQL> select df.con_id, max(df.checkpoint_time)
  2  from v$backup_datafile df, v$database d
  3  where df.resetlogs_change#=d.resetlogs_change#
  4  and df.con_id > 0
  5  group by df.con_id
  6  /   

    CON_ID MAX(DF.CHECKPOI
---------- ---------------
	 1 24-SEP-20 17:43
	 2 02-MAR-17 07:57
	 3 24-SEP-20 17:39
	 4 24-SEP-20 17:41

SQL> 

SQL> select arc.thread#, max(arc.next_change#)-1
  2  from v$backup_archivelog_details arc, v$database d
  3  where arc.resetlogs_change#=d.resetlogs_change#
  4  group by arc.thread#
  5  /

   THREAD# MAX(ARC.NEXT_CHANGE#)-1
---------- -----------------------
	 1		   3286130
  
SQL> 
SQL> select arc.thread#, max(arc.next_time)-1/1440      
  2  from v$backup_archivelog_details arc, v$database d
  3  where arc.resetlogs_change#=d.resetlogs_change#
  4  group by arc.thread#
  5  /         

   THREAD# MAX(ARC.NEXT_TI
---------- ---------------
	 1 24-SEP-20 17:40

SQL> 
SQL> select arc.thread#, max(arc.sequence#)
  2  from v$backup_archivelog_details arc, v$database d
  3  where arc.resetlogs_change#=d.resetlogs_change#
  4  group by arc.thread#
  5  /

   THREAD# MAX(ARC.SEQUENCE#)
---------- ------------------
	 1	           96

SQL> 



The information I get is that CON_ID=4 (which is NEWPDB) has at least one datafile at a higher Checkpoint SCN and Time then the last ArchiveLog in the backup.
Therefore, I would not be able to do an OPEN RESETLOGS after a full RESTORE + RECOVER because Oracle will expect some more Redo to be applied (from at least Sequence #97).

Why do I query for CON_ID > 0 ?  Because CON_ID=0 is for the CDB, not the actual Root (which is CON_ID=1)

Why I do filter for RESETLOGS_CHANGE#?  Because I want to query for the current Incarnation of the database, as reflected in the Controlfile.  

Such a method is also useful to determine what point in time you an do the Incomplete Recovery till.  For example, if Sequence#97 had been included in the Backup, I could have been able to write my RECOVER DATABASE command with UNTIL SEQUENCE 98.  (97+1)  The timestamps are also available, and I could do UNTIL TIME 24-Sep-20 17:41