27 December, 2021

DataPump Export using SQL Developer

 I have published a video demonstration of using SQL Developer to run a DataPump export.

Although I did not choose the "Export read-consistent view of data" (at 04:00 in the video), I strongly recommend using this option when exporting multiple tables and/or multiple schemas in a database with concurrent transactions while the export is running.

For example :

Transaction T1 might Insert into Table Y and then Delete from Table X. If DataPump does an export of Table X before the Delete and/or an export of Table Y after the Insert, the exported data would be inconsistent as far as these two tables are concerned.

Transaction T1 might Update Table G Some minutes later, Transaction T2 might Insert/Update/Delete data in Table H based on the (committed) rows in Table G. If DataPump exports Tables G and H before the DML on Table H (and after the Update at Table G), you would get inconsistent data in the export dump because the data in the dump would reflect two different points in time and not be read-consistent.

This can also happen with Materialized View Refresh's.





05 December, 2021

Refreshable Clone PDB -- 3 The ArchiveLog and ArchParLog files

 In my previous blog post about Datafile Name mapping for a Refreshable Clone PDB, I had presented only a portion of the trace file for the ALTER PLUGGABLE DATABASE RO_PDB REFRESH command.

Here is the full listing of that trace file (I have already explained the AFN mapping of FILE#s)


Trace file /opt/oracle/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_6159.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Build label:    RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715
ORACLE_HOME:    /opt/oracle/product/19c/dbhome_1
System name:    Linux
Node name:      ora19cs2
Release:        4.14.35-1902.10.4.1.el7uek.x86_64
Version:        #2 SMP Mon Jan 27 14:13:38 PST 2020
Machine:        x86_64
Instance name: CDB2
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 6159, image: oracle@ora19cs2


*** 2021-12-04T17:39:25.431585+08:00 (RO_PDB(5))
*** SESSION ID:(377.22911) 2021-12-04T17:39:25.431608+08:00
*** CLIENT ID:() 2021-12-04T17:39:25.431613+08:00
*** SERVICE NAME:(CDB2) 2021-12-04T17:39:25.431617+08:00
*** MODULE NAME:(sqlplus@ora19cs2 (TNS V1-V3)) 2021-12-04T17:39:25.431622+08:00
*** ACTION NAME:() 2021-12-04T17:39:25.431626+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-12-04T17:39:25.431630+08:00
*** CONTAINER ID:(5) 2021-12-04T17:39:25.431634+08:00

JIT: pid 6159 requesting stop

*** 2021-12-04T17:39:48.052938+08:00 (RO_PDB(5))
Started Serial Media Recovery
This is Foreign Recovery:- HotClone:1 Pre-Plugin:0 HotClone_for_Refresh:1. The Redo going to be applied in Current PDB(Target) will be from Foreign PDB(Source) and there will be file# conversion. Source AFN is from Foreign DB and Target AFN is on Current(local) DB. Dumping Source PDB:3 to Target PDB:5 AFN mapping.
Source AFN:26 = Target AFN:47
Source AFN:32 = Target AFN:49
Source AFN:12 = Target AFN:46
Source AFN:31 = Target AFN:48
Source AFN:9 = Target AFN:43
Source AFN:11 = Target AFN:45
Source AFN:10 = Target AFN:44
Dumping database incarnation table:
Resetlogs 0 scn and time: 0x0000000000f22944 11/30/2021 22:58:02
Dumping PDB pathvec - index 0
   0000 : pdb 5, dbinc 3, pdbinc 0
          db rls 0x0000000000f22944 rlc 1090018682
          incscn 0x0000000000000000 ts 0
          br scn 0x0000000000000000 ts 0
          er scn 0x0000000000000000 ts 0
   0001 : pdb 5, dbinc 2, pdbinc 0
          db rls 0x0000000000e88404 rlc 1084312848
          incscn 0x0000000000000000 ts 0
          br scn 0x0000000000000000 ts 0
          er scn 0x0000000000000000 ts 0
Recovery target incarnation = 3, activation ID = 0
Influx buffer limit = 39160 min(50% x 78320, 100000)
Start recovery at thread 1 ckpt scn 16121330 logseq 37 block 41766

*** 2021-12-04T17:39:48.430170+08:00 (RO_PDB(5))
Media Recovery add redo thread 1

*** 2021-12-04T17:39:48.494955+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_37_jtpcbxyv_.arc

*** 2021-12-04T17:39:48.868915+08:00 (RO_PDB(5))
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T17:39:51.447683+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/foreign_archivelog/ORCLPDB1/2021_12_04/o1_mf_1_38_jtpfttmz_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T17:40:04.041793+08:00 (RO_PDB(5))
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 89402Kb
-- Redo read_disk statistics --
Read rate (SYNC): 89402Kb in 15.61s => 5.59 Mb/sec
Total redo bytes: 89402Kb Longest record: 24Kb, moves: 78/213168 moved: 0Mb (0%)
Longest LWN: 2052Kb, reads: 3089
Last redo scn: 0x0000000000f7e789 (16246665)
Change vector header moves = 31243/386616 (8%)
----------------------------------------------

*** 2021-12-04T17:40:04.041915+08:00 (RO_PDB(5))
Media Recovery drop redo thread 1

*** 2021-12-04T17:40:07.389420+08:00 (RO_PDB(5))
KCBR: Number of read descriptors = 1024
KCBR: Media recovery blocks read (ASYNC) = 650
KCBR: Influx buffers flushed = 40 times
KCBR: Reads = 30 reaps (5 null, 8 wait), 2 all
KCBR: Redo cache copies/changes = 15025/15025

*** 2021-12-04T17:40:07.389466+08:00 (RO_PDB(5))
Completed Media Recovery
----- Abridged Call Stack Trace -----
(deleted by me)


The interesting parts are :
"Start recovery at thread 1 ckpt scn 16121330 logseq 37 block 41766"
and
"Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_37_jtpcbxyv_.arc"
and
"Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/foreign_archivelog/ORCLPDB1/2021_12_04/o1_mf_1_38_jtpfttmz_.arc"

It identifies the SCN from which it needs to recover transactions from the source CDB (ORCLCDB) and PDB (ORCLPDB1).  In a MultiTenant environment, all the PDBs share the same Redo Thread -- so any ArchiveLog may have transactions of multple PDBs.
For the purpose of refresh RO_PDB in CDB2, it identifies the entries from the source CDB ArchiveLogs.  For Log Sequence#28, it explicitly identifies the ORCLPDB1 (not the whole CDB) entries as "foreign archivelog".  However, this "foreign archivelog" is later automaticallly purged by ORCLCDB as I can see in the ORCLCDB alert log entry

2021-12-04T17:40:07.555215+08:00
Deleted Oracle managed file /opt/oracle/FRA/ORCLCDB/ORCLCDB/foreign_archivelog/ORCLPDB1/2021_12_04/o1_mf_1_38_jtpfttmz_.arc


However, sometimes the Refresh requires to read Redo Entries where an ArchiveLog has not been completely generated.  For example, a later Refresh at 22:41 required a partial ArchiveLog for Sequence#41

Start recovery at thread 1 ckpt scn 16246668 logseq 0 block 0

*** 2021-12-04T22:41:32.365818+08:00 (RO_PDB(5))
Media Recovery add redo thread 1

*** 2021-12-04T22:41:32.370629+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_38_jtpzhl2s_.arc

*** 2021-12-04T22:41:32.470154+08:00 (RO_PDB(5))
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T22:41:39.259689+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_39_jtpzhxck_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T22:41:42.187854+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/FRA/ORCLCDB/ORCLCDB/archivelog/2021_12_04/o1_mf_1_40_jtpzhy18_.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!

*** 2021-12-04T22:41:42.773949+08:00 (RO_PDB(5))
Media Recovery Log /opt/oracle/product/19c/dbhome_1/dbs/archparlog_1_41_f1dfc534_1084312848.arc
Log read is SYNCHRONOUS though disk_asynch_io is enabled!


Sequence#41 was still an Online Redo Log at ORCLCDB and had not yet been archived (In fact, the source ORCLPDB FRA was full and ORCLPDB was unable to Archive Log files). So, it seems that for Sequence#41, a Partial Extract was obtained so as to Refresh the Clone PDB.
This doesn't seem to be documented.

04 December, 2021

Refreshable Clone PDB -- 2 Datafile Names

 In my previous post, I demonstrated creating a Refreshable Clone PDB.  How do you handle datafile names ? You can use the FILE_NAME_CONVERT to change file names based on substring matching of the file names.  Or you could simply rely on the good-old (old nowadays !) feature / parameter DB_CREATE_FILE_DEST

In the ORCLPDB1 Pluggable Database at ORCLDB (Source),  these are my datafiles :

SQL> connect sys/manager@orclcdb as sysdba
Connected.
SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/oracle/oradata
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> col file_name format a98
SQL> select file_id, file_name
  2  from dba_data_files
  3  order by file_id
  4  /

   FILE_ID FILE_NAME
---------- --------------------------------------------------------------------------------------------------
         9 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
        10 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
        11 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
        12 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
        26 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf
        31 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf
        32 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf

7 rows selected.

SQL>



Note that DB_CREATE_FILE_DEST can be modified at any time. Here you can see that it was  modified before the the last 3 tablespaces/datafiles were added to the database.  (The datafiles are automatically sized at an initial 100MB, autoextensible to 32GB).

So, what are the file names in the RO_PDB at CDB2 ?


SQL> connect sys/manager@cdb2 as sysdba
Connected.
SQL> alter pluggable database ro_pdb open read only;

Pluggable database altered.

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/oracle/oradata
SQL> alter session set container=RO_PDB;

Session altered.

SQL> col file_name format a98
SQL> select file_id, file_name
  2  from dba_data_files
  3  order by file_id
  4  /

no rows selected

SQL>


HEY ! Why I can't I see the data files in RO_PDB ?  This is likely Oracle Bug#30536162.  See MOS Document "Some Data File Missing From DBA_DATA_FILES or CDB_DATA_FILES in Refreshable PDB (Doc ID 2610743.1)".   The workaround is to use V$DATAFILE at the Instance Level.
Let me check that in both instances.

At the source :

SQL> connect sys/manager@ORCLCDB as sysdba
Connected.
SQL> select file#, name
  2  from v$datafile
  3  where con_id =
  4    (select con_id
  5     from v$pdbs
  6     where name = 'ORCLPDB1')
  7  order by 1
  8  /

 FILE# NAME
------ --------------------------------------------------------------------------------------------------
     9 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
    10 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
    11 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
    12 /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
    26 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf
    31 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf
    32 /opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf

7 rows selected.

SQL>


And, at the target :

SQL> connect sys/manager@cdb2 as sysdba
Connected.
SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /opt/oracle/oradata
SQL>
SQL> select file#, name
  2  from v$datafile
  3  where con_id =
  4    (select con_id
  5     from v$pdbs
  6     where name = 'RO_PDB')
  7  order by 1
  8  /

     FILE# NAME
---------- --------------------------------------------------------------------------------------------------
        43 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_system_jtncqq6j_.dbf
        44 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_sysaux_jtncqq6n_.dbf
        45 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_undotbs1_jtncqq6o_.dbf
        46 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_users_jtncqq6r_.dbf
        47 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_data_min_jtncqq6s_.dbf
        48 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_hemant_d_jtncqq6t_.dbf
        49 /opt/oracle/oradata/CDB2/D240086D48C012D0E0550A00275FC834/datafile/o1_mf_hemant_d_jtncqq6v_.dbf

7 rows selected.

SQL>


Unlike the source ORCLPDB1 in ORCLCDB, the datafiles in RO_PDB at CDB2 are all OMF files because I had configured DB_CREATE_FILE_DEST before creating RO_PDB.

Why are the FILE_IDs (FILE# in V$DATAFILE) different between the Source PDB and the Target PDB ?  This is because the Source Container Database and Target Container Database may have different numbers of Pluggable Databases, each with different numbers of datafiles.  So, when the Target Pluggable Database is created from the Source PDB, it is allocated FILE# values as they are available in the target Container Database.

For example, these are the numbers at the Source (4 CON_IDs, 18 Datafiles) :

SQL> select con_id, file#
  2  from v$datafile
  3  order by 1,2
  4  /

    CON_ID  FILE#
---------- ------
         1      1
         1      3
         1      4
         1      7
         2      5
         2      6
         2      8
         3      9
         3     10
         3     11
         3     12
         3     26
         3     31
         3     32
         4     27
         4     28
         4     29
         4     30

18 rows selected.

SQL>
SQL> select con_id
  2  from v$pdbs
  3  where name = 'ORCLPDB1'
  4  /

    CON_ID
----------
         3

SQL>


And these are at the Target (5 CON_ID, 23 datafiles)

SQL> select con_id, file#
  2  from v$datafile
  3  order by 1,2
  4  /

    CON_ID      FILE#
---------- ----------
         1          1
         1          3
         1          4
         1          7
         2          5
         2          6
         2          8
         3          9
         3         10
         3         11
         3         12
         3         26
         4         27
         4         28
         4         29
         4         30
         5         43
         5         44
         5         45
         5         46
         5         47
         5         48
         5         49

23 rows selected.

SQL> select con_id
  2  from v$pdbs
  3  where name = 'RO_PDB'
  4  /

    CON_ID
----------
         5

SQL>


The Source ORCLPDB1 is CON_ID=3 at ORCLCDB and the Target RO_PDB is CON_ID=5 at CDB2.

If I refresh RO_PDB from the source

SQL> alter pluggable database ro_pdb close;

Pluggable database altered.

SQL> alter pluggable database ro_pdb refresh;

Pluggable database altered.

SQL> alter pluggable database ro_pdb open read only;

Pluggable database altered.

SQL>


I find this in the session's trace file at CDB2 (the Target doing the Refresh) :

Trace file /opt/oracle/diag/rdbms/cdb2/CDB2/trace/CDB2_ora_6159.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
Build label:    RDBMS_19.12.0.0.0DBRU_LINUX.X64_210715
ORACLE_HOME:    /opt/oracle/product/19c/dbhome_1
System name:    Linux
Node name:      ora19cs2
Release:        4.14.35-1902.10.4.1.el7uek.x86_64
Version:        #2 SMP Mon Jan 27 14:13:38 PST 2020
Machine:        x86_64
Instance name: CDB2
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 6159, image: oracle@ora19cs2


*** 2021-12-04T17:39:25.431585+08:00 (RO_PDB(5))
*** SESSION ID:(377.22911) 2021-12-04T17:39:25.431608+08:00
*** CLIENT ID:() 2021-12-04T17:39:25.431613+08:00
*** SERVICE NAME:(CDB2) 2021-12-04T17:39:25.431617+08:00
*** MODULE NAME:(sqlplus@ora19cs2 (TNS V1-V3)) 2021-12-04T17:39:25.431622+08:00
*** ACTION NAME:() 2021-12-04T17:39:25.431626+08:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-12-04T17:39:25.431630+08:00
*** CONTAINER ID:(5) 2021-12-04T17:39:25.431634+08:00

JIT: pid 6159 requesting stop

*** 2021-12-04T17:39:48.052938+08:00 (RO_PDB(5))
Started Serial Media Recovery
This is Foreign Recovery:- HotClone:1 Pre-Plugin:0 HotClone_for_Refresh:1. The Redo going to be applied in Current PDB(Target) will be from Foreign PDB(Source) and there will be file# conversion. Source AFN is from Foreign DB and Target AFN is on Current(local) DB. Dumping Source PDB:3 to Target PDB:5 AFN mapping.
Source AFN:26 = Target AFN:47
Source AFN:32 = Target AFN:49
Source AFN:12 = Target AFN:46
Source AFN:31 = Target AFN:48
Source AFN:9 = Target AFN:43
Source AFN:11 = Target AFN:45
Source AFN:10 = Target AFN:44
.... and there is more information in the trace file which I will cover in my next Blog Post


The Source FILE# values (from ORCLPDB1 at ORCLCDB) are : 26, 32, 12, 31, 9, 11, 10
The corresponding Target FILE# values (in RO_PDB at CDB2) are : 47, 49, 46, 48, 43, 45, 44

To match them, check that the single SYSTEM tablespace datafile  is FILE#9 in ORCLPDB1 and FILE#43 in RO_PDB.
The single USERS tablespace datafile is FILLE#12 in ORCLPDB1 and FILE#46 in RO_PDB.
The two HEMANT_DATA tablespace datafile are FILE#s 31, 32 in ORCLPDB1 and FILE#s 48, 49 in RO_PDB.
Quite unfortunately, it seems that the mapping information in the Trace File is not ordered by FILE#

The trace file for the ALTER PLUGGABLE DATABASE RO_PDB has more information which I will cover in the next Blog Post.

30 November, 2021

Refreshable Clone PDB -- 1 Manual Refresh

 The facility of creating Clone PDBs that are Refreshable either "on-demand" or to a set schedule was introduced in 12.2.  The Refreshable PDB can be opened only as a Read-Only database

This is somewhat similar to having a Standby Database that can be opened Read-Only for queries and refreshed as and when on-demand (without using the Active Data Guard License)

Here is a quick demo in 19.12 on Linux

First, on the Source database ORCLCDB I verify that I have the required configuration of Local Undo and ArchiveLog mode and then setup a Common account with the requisite privileges:



oracle19c>sqlplus sys/manager@ORCLCDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 30 23:01:41 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> col property_value format a16
SQL> select property_value, log_mode, force_logging
  2  from database_properties, v$database
  3  where property_name = 'LOCAL_UNDO_ENABLED'
  4  /

PROPERTY_VALUE   LOG_MODE     FORCE_LOGGING
---------------- ------------ ---------------------------------------
TRUE             ARCHIVELOG   YES

SQL>
SQL> alter pluggable database orclpdb1 open  -- verify or open the source pdb
  2  /
alter pluggable database orclpdb1 open  -- verify or open the source pdb
*
ERROR at line 1:
ORA-65019: pluggable database ORCLPDB1 already open


SQL>
SQL> create user c##for_pdb_clones identified by for_pdb_clones
  2  default tablespace users temporary tablespace temp
  3  container=ALL   -- a common user for all PDBs
  4  /

User created.

SQL> grant create session, create pluggable database, sysoper -- privileges that are required (not DBA Role)
  2  to c##for_pdb_clones
  3  container=ALL  -- across all PDBs
  4  /

Grant succeeded.

SQL>
SQL> select con_id, privilege, common
  2  from cdb_sys_privs  -- check across all PDBs
  3  where grantee = 'C##FOR_PDB_CLONES'
  4  order by 1,2
  5  /

    CON_ID PRIVILEGE                                COM
---------- ---------------------------------------- ---
         1 CREATE PLUGGABLE DATABASE                YES
         1 CREATE SESSION                           YES
         3 CREATE PLUGGABLE DATABASE                YES
         3 CREATE SESSION                           YES

SQL>


Next, on the Target Database CDB2 (where I want to create the Clone PDB), I setup a Database Link and then issue the CREATE PLUGGABLE DATABASE command



oracle19c>sqlplus sys/manager@CDB2 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 30 23:06:41 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> create database link for_pdb_clones  -- create in the Root CDB
  2  connect to c##for_pdb_clones identified by for_pdb_clones using 'ORCLCDB'
  3  /

Database link created.

SQL> create pluggable database ro_pdb  -- the cloned pdb will be Read-Only
  2  from orclpdb1@for_pdb_clones -- create from pluggable database orclpd1
  3  refresh mode manual -- not specifying an auto-refresh interval
  4  -- file_name_convert=('ORCLCDB','CDB2')  -- not required as I am using OMF with db_create_file_dest
  5  /

Pluggable database created.

SQL>
SQL> alter pluggable database ro_pdb open -- this should be opened Read-Only
  2  /
alter pluggable database ro_pdb open -- this should be opened Read-Only
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode


SQL>
SQL> alter pluggable database ro_pdb open read only -- this will succeed
  2  /

Pluggable database altered.

SQL>


So, now the Source PDB ORCLPDB1 has been cloned to CDB2 as a new Pluggable Database RO_PDB.

How do I refresh it ?  I will update the Source and then query the RO_PDB at CDB2


SQL> connect hemant/hemant@ORCLPDB1
ERROR:
ORA-28002: the password will expire within 7 days


Connected.
SQL> create table list_of_objects
  2  as
  3  select * from dba_objects
  4  /

Table created.

SQL> select count(*) from list_of_objects
  2  /

  COUNT(*)
----------
     73645

SQL>
SQL> connect hemant/hemant@ro_pdb
ERROR:
ORA-28032: Your password has expired and the database is set to read-only


Warning: You are no longer connected to ORACLE.
SQL>
--- cannot connect to the Read Only database with an expired password
SQL> connect sys/manager@CDB2 as sysdba
Connected.
SQL> alter session set container=RO_PDB;

Session altered.

SQL> select count(*)
  2  from dba_objects
  3  where owner = 'HEMANT'
  4  /

  COUNT(*)
----------
        32

SQL> select max(created)
  2  from dba_objects
  3  where owner = 'HEMANT'
  4  /

MAX(CREAT
---------
04-SEP-21

SQL>
SQL> desc hemant.list_of_objects
ERROR:
ORA-04043: object hemant.list_of_objects does not exist


SQL>


So, the RO_PDB does not have the new table and does not even allow login with an Expired Password. I will first update my password at the source ORCLPDB1 and then refresh RO_PDB and query again.

SQL> connect hemant/hemant@ORCLPDB1
ERROR:
ORA-28002: the password will expire within 7 days


Connected.
SQL> password hemant
Changing password for hemant
Old password:
New password:
Retype new password:
Password changed
SQL>
SQL> connect sys/manager@CDB2 as sysdba
Connected.
SQL> alter pluggable database ro_pdb close;  -- must CLOSE to allow a REFRESH

Pluggable database altered.

SQL> alter pluggable database ro_pdb refresh;  -- REFRESH command

Pluggable database altered.

SQL> alter pluggable database ro_pdb open read only;

Pluggable database altered.

SQL>
SQL> connect hemant/newhemant@ro_pdb
Connected.
SQL> select count(*) from list_of_objects;

  COUNT(*)
----------
     73645

SQL>


The Refresh of the RO_PDB is a simple process. I simply CLOSE it, issue an ALTER ... REFRESH command and then OEPN READ ONLY again.
Now, RO_PDB allows connections with the new Password and has the updated data *as of the time the REFRESH command is issued*.   Any new data populated in the source or any change made in the source are not available in RO_PDB  until a fresh REFRESH is executed.

20 November, 2021

14 November, 2021

Indexing Documents in Oracle

 In the previous post, I had demonstrated how to load documents into an Oracle Database.  

(Note : I am referring to documents as in PDF files, PowerPoint presentations, Word documents and text files in the traditional sense, not "JSON documents").

Here I demonstrate how to use Oracle Text to create indexes and make such documents searchable.

I assume that Oracle Text (the "CTXSYS" schema) has been installed when the database was created.

First, I grant the appropriate privileges.



SQL> alter session set container=pdb1;

Session altered.

SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_DOC TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_QUERY TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_REPORT TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_THES TO MYDOCDB;

Grant succeeded.

SQL> GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO MYDOCDB;

Grant succeeded.

SQL>


Next, I create a CONTEXT Index on my documents table.

SQL> connect mydocdb/mydocdb@pdb1
Connected.
SQL> CREATE INDEX my_documents_ctx_idx ON my_documents(file_content)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  /

Index created.

SQL>


My documents are now indexed and searchable. The syntax for a context index query is slightly different.

SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'DBA Role') > 0
  4  /

DOC_TITLE                                  DOC_DESCRIPTION                            FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Oracle 12c Security                        Security Mechanisms in Oracle 12c          PDF
Monitoring and Diagnostics without OEM     Presentation at Oracle SOS Conference      PDF

SQL>
SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'sqlplus hemant/hemant@orclpdb1') > 0
  4  /

DOC_TITLE                                  DOC_DESCRIPTION                            FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Flashback Database and DG DR Testing       How to use Flasback Database in DataGuard  DOCX
                                           for DR Testing


SQL>
SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'Flex ASM') > 0
  4  /

DOC_TITLE                                  DOC_DESCRIPTION                            FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Oracle 12c Availability                    High Availability Options in Oracle 12c    PDF

SQL>
SQL>
SQL> -- unfortunately, some queries on my plain-text SQL file don't work as expected
SQL>
SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'doc_number number generated always as identity,') > 0
  4  /
select doc_title, doc_description, file_type
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 48


SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'doc_number number generated always as identity') > 0
  4  /

no rows selected

SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'doc_number number generated always') > 0
  4  /

no rows selected

SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, '(doc_number number generated always') > 0
  4  /
select doc_title, doc_description, file_type
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-50901: text query parser syntax error on line 1, column 36


SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'drop table my_documents') > 0
  4  /

no rows selected

SQL>


Also, apparently the inclusion of "(" and "," and such are tricky when creatimg a query.

Note : The "CONTAINS ... >0" clause is to check the SCORE returned by the filter in the CONTAINS section and to see that it is greater than zero.

Here I have created a CTXSYS.CONTEXT index and it works well for the PDF and DOCX files.  This type of Index is *not* automatically updated when you load a new document.  To update the Index you must call the "CTX_DDL.SYNC_INDEX" procedure.

Using the same method as in my previous blog post, I load a new PPT file (callled "Flashback.ppt").


C:\load_docs_to_oracle>sqlldr mydocdb/mydocdb@pdb1 control=my_documents.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 14 17:04:55 2021
Version 19.12.0.0.0

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

Path used:      Conventional
Commit point reached - logical record count 1

Table MY_DOCUMENTS:
  1 Row successfully loaded.

Check the log file:
  my_documents.log
for more information about the load.

C:\load_docs_to_oracle>


And then sync the index and query again

SQL> select doc_number, doc_title, file_type
  2  from my_documents
  3  order by doc_number
  4  /

DOC_NUMBER DOC_TITLE                                  FILE_TYPE
---------- ------------------------------------------ ----------------
         1 Oracle 12c Availability                    PDF
         2 Oracle 12c Security                        PDF
         3 Monitoring and Diagnostics without OEM     PDF
         4 Setting up Data Guard in 11gR2             DOCX
         5 Flashback Database and DG DR Testing       DOCX
         6 Create Docs Table                          SQL
         7 Flashback                                  PPT

7 rows selected.

SQL> exec CTX_DDL.SYNC_INDEX('my_documents_ctx_idx','4M');

PL/SQL procedure successfully completed.

SQL>
SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'Thomas Kyte') > 0
  4  /

DOC_TITLE                                  DOC_DESCRIPTION                            FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Flashback                                  Tom Kyte's Presentation on Flashback       PPT

SQL> select doc_title, doc_description, file_type
  2  from my_documents
  3  where contains(file_content, 'Rowids therefore will change') > 0
  4  /

DOC_TITLE                                  DOC_DESCRIPTION                            FILE_TYPE
------------------------------------------ ------------------------------------------ ----------------
Flashback                                  Tom Kyte's Presentation on Flashback       PPT

SQL>


The call to CTX_DDL.SYNC_INDEX contains a memory parameter '4M' specifying the memory to be used for synchronization.

Besides the CTXSYS.CONTEXT IndexType, Oracle also allows a "CTXSYS.CTXCAT" IndexType which can be used to index small documents or text fragements.  The CTXSYS.CTXCAT Index is updated synchronously when a new document is loaded into the table (i.e. a call to CTX_DDL.SYNC_INDEX is not required).

Typically, with a document database with moderate to large documents, you would use CTXSYS.CONTEXT IndexType and configure a scheduler job to call CTX_DDL.SYNC_INDEX periodically (e.g. every 30minutes or every 4hours) to update the Index for new documents that are loaded infrequently and do not need to be searched immediately.

The IndexType automically excludes certain popular words called "STOPWORD"s that may be in "STOPLIST"s.  These are very commonly used words that do not need to be indexed (e.g. words like "the" or "this" or "that".  Such STOPLISTs can be customised as well.
So, your implementation of the indexes is customisable.


See the Oracle Text Application Developers Guide and  Oracle Text Reference for more examples and a detailed study.


07 November, 2021

Loading Documents into Oracle

A short demonstration of loading documents into an Oracle Database.

SQL> @create_docs_table
SQL> spool create_docs_table
SQL>
SQL> drop table my_documents purge;

Table dropped.

SQL> create table my_documents
  2  (doc_number number generated always as identity,
  3  doc_title varchar2(100),
  4  doc_description varchar2(256),
  5  os_file_name varchar2(256),
  6  file_content blob,
  7  file_type varchar2(16))
  8  lob(file_content) store as securefile (compress high keep_duplicates)
  9  /

Table created.

SQL>
SQL> spool off
SQL>


I have created table with a SecureFile LOB to hold the documents.
Next, I use sqlloader to load the documents, based on a "list file".


C:\load_docs_to_oracle>type List_of_Files_to_Upload.csv
Oracle 12c Availability,High Availability Options in Oracle 12c,12c_Availability.pdf,PDF
Oracle 12c Security,Security Mechanisms in Oracle 12c,12c_Security.pdf,PDF
Monitoring and Diagnostics without OEM,Presentation at Oracle SOS Conference,Monitoring and Diagnostics without OEM.pdf,PDF
Setting up Data Guard in 11gR2,My document on DG setup in 11gR2 ,Setting up DataGuard in 11gR2.docx,DOCX
Flashback Database and DG DR Testing,How to use Flasback Database in DataGuard for DR Testing,Using FLASHBACK DATABASE for Destructive DR Testing.docx,DOCX
Create Docs Table,Source Code for Script to create DOCS Table,create_docs_table.sql,SQL

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>type my_documents.ctl
load data
        infile 'List_of_Files_to_Upload.csv'
        into table my_documents
        fields terminated by ','
(
doc_title ,
doc_description ,
os_file_name,
file_content lobfile(os_file_name) terminated by EOF,
file_type
)

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>sqlldr mydocdb/mydocdb@pdb1 control=my_documents.ctl

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 7 17:01:33 2021
Version 19.3.0.0.0

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

Path used:      Conventional
Commit point reached - logical record count 6

Table MY_DOCUMENTS:
  6 Rows successfully loaded.

Check the log file:
  my_documents.log
for more information about the load.

C:\load_docs_to_oracle>


C:\load_docs_to_oracle>type my_documents.log

SQL*Loader: Release 19.0.0.0.0 - Production on Sun Nov 7 17:01:33 2021
Version 19.3.0.0.0

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

Control File:   my_documents.ctl
Data File:      List_of_Files_to_Upload.csv
  Bad File:     List_of_Files_to_Upload.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     250 rows, maximum of 1048576 bytes
Continuation:    none specified
Path used:      Conventional

Table MY_DOCUMENTS, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DOC_TITLE                           FIRST     *   ,       CHARACTER
DOC_DESCRIPTION                      NEXT     *   ,       CHARACTER
OS_FILE_NAME                         NEXT     *   ,       CHARACTER
FILE_CONTENT                      DERIVED     *  EOF      CHARACTER
    Dynamic LOBFILE.  Filename in field OS_FILE_NAME
FILE_TYPE                            NEXT     *   ,       CHARACTER


Table MY_DOCUMENTS:
  6 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 258000 bytes(250 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             6
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Sun Nov 07 17:01:33 2021
Run ended on Sun Nov 07 17:01:35 2021

Elapsed time was:     00:00:01.17
CPU time was:         00:00:00.06

C:\load_docs_to_oracle>



Note : In "List_of_Files_to_Upload.csv" the incoming file names are *not* enclosed in quotes and there is no space-character before the filename.

Once you have loaded the documents, SQLDeveloper gives you the option of either opening the file (e.g. PDF files opened by Acrobat Reader) or downloading the file that you query.  (In my installation, DOCX and SQL files have to be downloaded for viewing as the Exernal Editor is not configured for these file-extensions -- the DOCX is identified as a "jar" file).

[click on the images below to view them better]

SQL Developer view




External Editor Preferences in SQL Developer







17 October, 2021

My Posts on Standby Database[s] -- Data Guard

Although I have, in previous posts, covered Data Guard in 12c (and 12cR1 RAC), this is a list of recent Blog Posts on Data Guard. 





















My Posts on Database Flashback

 

My Blog Posts on Database Flashback

(The first 5 demonstrations are in 11gR2.  The last demonstration is in 19c)


(not in the series)



My Posts on Parallel Execution


My series of blog posts on Parallel Execution

(These demonstrations were against Oracle 11gR2.  19c may have new features / changes / improvements)













26 September, 2021

Resuming Standby Databases after a RESETLOGS (without Data Loss from Online Redo Logs !!) at the Primary

 Supposing that you lose the Online Redo Logs of the Primary [after it has been SHUTDOWN NORMAL/IMMEDIATE] and you have to  OPEN RESETLOGS, how do you handle the Standbys ?

This scenario demonstrated below works only if you did not have to issue a RESTORE DATABASE and RECOVER DATABASE at the Primary.  In such a case you would have to FLASHBACK all the Standbys to a time/SCN earlier than the Recovery time OR you'd have to Rebuild the Standbys.

In my demonstration below, the RECOVER DATABASE was only from the ArchiveLogs that have already been shipped or applied to the Standby -- upto Sequence#505You must verify that the last active Online Redo Log at the Primary has been Archived and Shipped (not necessarily applied) to the Standby[s] before you release your Primary for storage/server maintenance.  If they were not Shipped automatically, you can manually copy them to the Standbys and use the ALTER DATABASE REGISTER LOGFILE command at the Standbys,

Let's assume that the Primary had been shutdown for storage/server maintenance  (but the Standbys were yet running).  The Standby's are *not* shutdown in my scenario.

Then, during the storage/server maintenance, the disk/filesystem for the  Online Redo Logs was lost/corrupt.  This necessitated an OPEN RESETLOGS.

The key thing is than an OPEN RESETLOGS creates a new Incarnation of the database.  What happens at the Standbys subsequently ?

Let's assume that the Primary created a Controlfile Autobackup and then did SHUTDOWN IMMEDIATE before the storage/server maintenance.



Starting Control File and SPFILE Autobackup at 26-SEP-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_09_26/o1_mf_s_1084311179_jo0xnh8o_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-SEP-21

RMAN>
RMAN> quit


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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 26 21:34:25 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

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


entries from the alert log show that SEQUENCE#505 was the last Online Redo Log that was active 
and was also archived at shutdown (as is done in recent versions)

2021-09-26T21:33:34.741801+08:00
Thread 1 advanced to log sequence 505 (LGWR switch),  current SCN: 15237281
  Current log# 1 seq# 505 mem# 0: /opt/oracle/oradata/ORCLCDB/redo01.log
2021-09-26T21:33:35.533318+08:00
NET  (PID:13253): Archived Log entry 1295 added for T-1.S-504 ID 0xa7521ccd LAD:1
2021-09-26T21:34:40.355271+08:00
Shutting down ORACLE instance (immediate) (OS id: 13897)
...
...
ALTER DATABASE CLOSE NORMAL
Stopping Emon pool
2021-09-26T21:34:54.826891+08:00
alter pluggable database all close immediate
Completed: alter pluggable database all close immediate
alter pluggable database all close immediate
Completed: alter pluggable database all close immediate
2021-09-26T21:34:55.811566+08:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
Stopping Emon pool
Closing sequence subsystem (1373431005).
2021-09-26T21:34:56.082543+08:00
Stopping change tracking
2021-09-26T21:35:00.702496+08:00
TT04 (PID:4521): Shutdown in progress, stop CF update
2021-09-26T21:35:01.439450+08:00
Thread 1 advanced to log sequence 506 (thread close)
2021-09-26T21:35:01.537622+08:00
LGWR (PID:4387): Waiting for ORLs to be archived
2021-09-26T21:35:01.694980+08:00
ARC0 (PID:4457): Archived Log entry 1298 added for T-1.S-505 ID 0xa7521ccd LAD:1
2021-09-26T21:35:01.709616+08:00
LGWR (PID:4387): ORLs successfully archived
2021-09-26T21:35:02.711736+08:00
Shutting down archive processes
2021-09-26T21:35:02.712053+08:00
TT00 (PID:4453): Gap Manager exiting
2021-09-26T21:35:03.712018+08:00
Archiving is disabled
2021-09-26T21:35:03.712297+08:00
ARC3 (PID:4464): ARCH shutting down




However, it so happens that the Online Redo Logs are "lost" during server/storage maintenance.  Maybe, even the Controlfiles are "lost".  I decide to RESTORE CONTROLFILE and  to OPEN RESETLOGS.
In the  commands below, I deliberately use the SQL*Plus command line so that I can show the ArchiveLog Sequences#s that are applied before I can OPEN RESETLOGS  (RMAN command line would hide the details) and I know that I can CANCEL and stop after Sequence#505 

oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 26 21:39:59 2021
Version 19.12.0.0.0

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1207955544 bytes
Fixed Size                  9134168 bytes
Variable Size             872415232 bytes
Database Buffers          318767104 bytes
Redo Buffers                7639040 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info


SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Sep 26 21:40:34 2021
Version 19.12.0.0.0

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

connected to target database: ORCLCDB (not mounted)

RMAN> restore controlfile from autobackup;

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

recovery area destination: /opt/oracle/FRA/ORCLCDB
database name (or database unique name) used for search: ORCLCDB
channel ORA_DISK_1: AUTOBACKUP /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_09_26/o1_mf_s_1084311179_jo0xnh8o_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_09_26/o1_mf_s_1084311179_jo0xnh8o_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/opt/oracle/oradata/ORCLCDB/control01.ctl
output file name=/opt/oracle/oradata/ORCLCDB/control02.ctl
Finished restore at 26-SEP-21

RMAN>
RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/26/2021 21:41:17
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN>
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 09/26/2021 21:41:35
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/opt/oracle/oradata/ORCLCDB/system01.dbf'

RMAN>
RMAN> quit


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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 26 21:54:27 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 15237057 generated at 09/26/2021 21:28:26 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_503_1036108814.dbf
ORA-00280: change 15237057 for thread 1 is in sequence #503


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 15237253 generated at 09/26/2021 21:33:26 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_504_1036108814.dbf
ORA-00280: change 15237253 for thread 1 is in sequence #504
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_503_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 15237281 generated at 09/26/2021 21:33:33 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_505_1036108814.dbf
ORA-00280: change 15237281 for thread 1 is in sequence #505
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_504_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}

ORA-00279: change 15238147 generated at 09/26/2021 21:34:58 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/ORCLCDB/1_506_1036108814.dbf
ORA-00280: change 15238147 for thread 1 is in sequence #506
ORA-00278: log file '/opt/oracle/archivelog/ORCLCDB/1_505_1036108814.dbf' no longer needed for this recovery


Specify log: {RET=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> set pages60
SQL> set linesize132
SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME     PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TI STATUS  RESETLOGS_ID PRIOR_INCARNATION#
------------ ----------------- ------------------ ----------------------- ------------------ ------- ------------ ------------------
FLASHBACK_DATABASE_ALLOWED     CON_ID
-------------------------- ----------
           1                 1 17-APR-19 00:55:59                       0                    PARENT    1005785759                  0
NO                                  0

           2           1920977 04-MAY-19 23:21:26                       1 17-APR-19 00:55:59 PARENT    1007421686                  1
NO                                  0

           3           4797184 27-MAR-20 00:00:14                 1920977 04-MAY-19 23:21:26 PARENT    1036108814                  2
NO                                  0

           4          15238148 26-SEP-21 22:00:48                 4797184 27-MAR-20 00:00:14 CURRENT   1084312848                  3
NO                                  0


SQL>


So, my Primary Database has now switched to INCARNATION#4 with a RESETLOGS as of 26-Sep-21 22:00:48 with RESETLOGS_ID "1084312848"

Let me check my Standby alert logs.
First, STDBYDB which shows that Sequence#505 was applied (ie. "Recovery of Onlin Redo Log ... Seq 505 .." message completed by showing the next message "Media Recovery Waiting for T-1.5-506" 


2021-09-26T21:33:28.002521+08:00
 rfs (PID:4667): Standby controlfile consistent with primary
2021-09-26T21:33:28.339263+08:00
 rfs (PID:4667): Selected LNO:4 for T-1.S-504 dbid 2778483057 branch 1036108814
2021-09-26T21:33:28.667001+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 504 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-09-26T21:33:33.555294+08:00
ARC3 (PID:3201): Archived Log entry 108 added for T-1.S-504 ID 0xa7521ccd LAD:1
2021-09-26T21:33:33.827814+08:00
PR00 (PID:3847): Media Recovery Waiting for T-1.S-505
2021-09-26T21:33:33.991340+08:00
 rfs (PID:4667): Standby controlfile consistent with primary
2021-09-26T21:33:34.547950+08:00
 rfs (PID:4667): Selected LNO:4 for T-1.S-505 dbid 2778483057 branch 1036108814
2021-09-26T21:33:35.283450+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 505 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-09-26T21:35:00.057442+08:00
ARC1 (PID:3197): Archived Log entry 109 added for T-1.S-505 ID 0xa7521ccd LAD:1
2021-09-26T21:35:00.289539+08:00
PR00 (PID:3847): Media Recovery Waiting for T-1.S-506
2021-09-26T21:35:02.732262+08:00
 rfs (PID:4502): Possible network disconnect with primary database


At this point, 21:35:02, it shows a "network disconnect with the primary database" which is when the Primary had fnished archiving Sequence#505 and was continuing the shutdown.
Subsequently, when the Primary goes through the OPEN RESETLOGS, the Standby shows 
[note the "rfs (PID:20837): A new recovery destination branch has been registered" and " rfs (PID:20837): Standby in the future of new recovery destination branch(resetlogs_id) 1084312848" and "rfs (PID:20837): New Archival REDO Branch(resetlogs_id): 1084312848 Prior: 1036108814" messages" which match the RESETLOGS_ID information from the Primary's v$database_incarnation (listed above)  :

2021-09-26T22:01:32.924024+08:00
 rfs (PID:20634): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:14755)
2021-09-26T22:01:44.591773+08:00
 rfs (PID:20820): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is SYNC (PID:14565)
 rfs (PID:20820): New archival redo branch: 1084312848 current: 1036108814
 rfs (PID:20820): Primary database is in MAXIMUM AVAILABILITY mode
 rfs (PID:20820): Changing standby controlfile to RESYNCHRONIZATION level
 rfs (PID:20820): Standby controlfile consistent with primary
 rfs (PID:20820): No SRLs available for T-1
Clearing online log 5 of thread 1 sequence number 0
2021-09-26T22:01:49.532864+08:00
 rfs (PID:20820): Selected LNO:5 for T-1.S-3 dbid 2778483057 branch 1084312848
2021-09-26T22:01:49.535634+08:00
Clearing online log 4 of thread 1 sequence number 0
2021-09-26T22:01:56.788073+08:00
 rfs (PID:20837): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:14751)
2021-09-26T22:01:59.406342+08:00
 rfs (PID:20837): Selected LNO:4 for T-1.S-2 dbid 2778483057 branch 1084312848
2021-09-26T22:02:00.964530+08:00
 rfs (PID:20837): A new recovery destination branch has been registered
 rfs (PID:20837): Standby in the future of new recovery destination branch(resetlogs_id) 1084312848
 rfs (PID:20837): Incomplete Recovery SCN:0x0000000000e88403
 rfs (PID:20837): Resetlogs SCN:0x0000000000e88404
 rfs (PID:20837): SBPS:0x00000000004c4f04
 rfs (PID:20837): New Archival REDO Branch(resetlogs_id): 1084312848  Prior: 1036108814
 rfs (PID:20837): Archival Activation ID: 0xaa3191f1 Current: 0xa7521ccd
 rfs (PID:20837): Effect of primary database OPEN RESETLOGS
 rfs (PID:20837): Managed Standby Recovery process is active
2021-09-26T22:02:00.965240+08:00
Incarnation entry added for Branch(resetlogs_id): 1084312848 (STDBYDB)
2021-09-26T22:02:02.317070+08:00
Setting recovery target incarnation to 4
2021-09-26T22:02:02.421791+08:00
PR00 (PID:3847): MRP0: Incarnation has changed! Retry recovery...
2021-09-26T22:02:02.422333+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_pr00_3847.trc:
ORA-19906: recovery target incarnation changed during recovery
PR00 (PID:3847): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Stopping change tracking
2021-09-26T22:02:02.735890+08:00
Errors in file /opt/oracle/diag/rdbms/stdbydb/STDBYDB/trace/STDBYDB_pr00_3847.trc:
ORA-19906: recovery target incarnation changed during recovery
2021-09-26T22:02:03.908863+08:00
 Started logmerger process
2021-09-26T22:02:04.088714+08:00
PR00 (PID:20863): Managed Standby Recovery starting Real Time Apply
2021-09-26T22:02:05.658988+08:00
ARC1 (PID:3197): Archived Log entry 110 added for T-1.S-2 ID 0xaa3191f1 LAD:1
2021-09-26T22:02:06.477510+08:00
max_pdb is 5
2021-09-26T22:02:07.696019+08:00
Parallel Media Recovery started with 4 slaves
2021-09-26T22:02:08.510123+08:00
Stopping change tracking
2021-09-26T22:02:10.459429+08:00
PR00 (PID:20863): Media Recovery Waiting for T-1.S-1
PR00 (PID:20863): Fetching gap from T-1.S-1 to T-1.S-1
2021-09-26T22:02:10.779495+08:00
 rfs (PID:20986): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:14753)
2021-09-26T22:02:12.270336+08:00
 rfs (PID:20986): Opened log for T-1.S-1 dbid 2778483057 branch 1084312848
2021-09-26T22:02:12.385769+08:00
 rfs (PID:20986): Archived Log entry 111 added for B-1084312848.T-1.S-1 ID 0xaa3191f1 LAD:2
2021-09-26T22:02:13.251841+08:00
PR00 (PID:20863): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_1_1084312848.dbf
2021-09-26T22:02:14.796992+08:00
PR00 (PID:20863): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_2_1084312848.dbf
2021-09-26T22:02:22.220965+08:00
PR00 (PID:20863): Media Recovery Waiting for T-1.S-3 (in transit)
2021-09-26T22:02:23.679612+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 3 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo02.dbf
2021-09-26T22:02:33.322073+08:00
ARC2 (PID:3199): Archived Log entry 112 added for T-1.S-3 ID 0xaa3191f1 LAD:1
2021-09-26T22:02:34.236382+08:00
PR00 (PID:20863): Media Recovery Waiting for T-1.S-4
2021-09-26T22:02:34.559830+08:00
 rfs (PID:20820): Changing standby controlfile to MAXIMUM AVAILABILITY level
2021-09-26T22:02:35.020783+08:00
 rfs (PID:20820): Selected LNO:4 for T-1.S-4 dbid 2778483057 branch 1084312848
2021-09-26T22:02:35.329328+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 4 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-09-26T22:10:24.128439+08:00
PR00 (PID:20863): Media Recovery Waiting for T-1.S-5
2021-09-26T22:10:24.194710+08:00
ARC0 (PID:3193): Archived Log entry 113 added for T-1.S-4 ID 0xaa3191f1 LAD:1
2021-09-26T22:10:25.894496+08:00
 rfs (PID:20820): Standby controlfile consistent with primary
2021-09-26T22:10:26.415829+08:00
 rfs (PID:20820): Selected LNO:4 for T-1.S-5 dbid 2778483057 branch 1084312848
2021-09-26T22:10:32.885773+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 5 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2021-09-26T22:10:33.484509+08:00
ARC2 (PID:3199): Archived Log entry 114 added for T-1.S-5 ID 0xaa3191f1 LAD:1
2021-09-26T22:10:33.653161+08:00
PR00 (PID:20863): Media Recovery Waiting for T-1.S-6 (in transit)
2021-09-26T22:10:33.666695+08:00
 rfs (PID:20820): Standby controlfile consistent with primary
2021-09-26T22:10:33.983459+08:00
 rfs (PID:20820): Selected LNO:4 for T-1.S-6 dbid 2778483057 branch 1084312848
2021-09-26T22:10:34.715704+08:00
2021-09-26T22:10:34.715704+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 6 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


The STDBYDB instance is already at Sequence#6 of the new Incarnation because the Primary database has been released for use and is already receiving new transactions and generating redo and ArchiveLogs which it is now shipping to all the Standbys.

The other current Standby STDB2 also shows that is now running with the new Incarnation and Sequence#6 :


2021-09-26T22:10:33.848217+08:00
PR00 (PID:20945): Media Recovery Waiting for T-1.S-6 (in transit)
2021-09-26T22:10:34.910903+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 6 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBNEW/stdbredo02.dbf


What about the Stanndy Database STDB2 that I had set to lag by 1hour. 
I present the alert log entries that shows it is aware of (Received and Archived, and in some cases, Applied, older Archives going back to the time before the Primary was shutdown).


2021-09-26T21:24:03.036631+08:00
ARC0 (PID:3517): Archive log for T-1.S-499 available in 59 minute(s)
2021-09-26T21:24:15.615120+08:00
 rfs (PID:4480): Selected LNO:4 for T-1.S-501 dbid 2778483057 branch 1036108814
2021-09-26T21:24:16.085817+08:00
ARC1 (PID:3523): Archived Log entry 246 added for T-1.S-500 ID 0xa7521ccd LAD:1
2021-09-26T21:24:16.085881+08:00
ARC1 (PID:3523): Archive log for T-1.S-500 available in 60 minute(s)
2021-09-26T21:27:03.617096+08:00
db_recovery_file_dest_size of 10240 MB is 10.63% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2021-09-26T21:28:21.459249+08:00
 rfs (PID:4480): Selected LNO:5 for T-1.S-502 dbid 2778483057 branch 1036108814
2021-09-26T21:28:25.263818+08:00
ARC2 (PID:3525): Archived Log entry 247 added for T-1.S-501 ID 0xa7521ccd LAD:1
2021-09-26T21:28:25.264142+08:00
ARC2 (PID:3525): Archive log for T-1.S-501 available in 60 minute(s)
2021-09-26T21:28:29.572537+08:00
 rfs (PID:4480): Selected LNO:4 for T-1.S-503 dbid 2778483057 branch 1036108814
2021-09-26T21:28:29.580802+08:00
ARC3 (PID:3527): Archived Log entry 248 added for T-1.S-502 ID 0xa7521ccd LAD:1
2021-09-26T21:28:29.580964+08:00
ARC3 (PID:3527): Archive log for T-1.S-502 available in 59 minute(s)
2021-09-26T21:33:30.720113+08:00
 rfs (PID:4480): Selected LNO:5 for T-1.S-504 dbid 2778483057 branch 1036108814
2021-09-26T21:33:30.737222+08:00
ARC0 (PID:3517): Archived Log entry 249 added for T-1.S-503 ID 0xa7521ccd LAD:1
2021-09-26T21:33:30.737298+08:00
ARC0 (PID:3517): Archive log for T-1.S-503 available in 60 minute(s)
2021-09-26T21:33:35.794308+08:00
 rfs (PID:4480): Selected LNO:4 for T-1.S-505 dbid 2778483057 branch 1036108814
2021-09-26T21:33:35.825498+08:00
ARC1 (PID:3523): Archived Log entry 250 added for T-1.S-504 ID 0xa7521ccd LAD:1
2021-09-26T21:33:35.825563+08:00
ARC1 (PID:3523): Archive log for T-1.S-504 available in 60 minute(s)
2021-09-26T21:35:02.021214+08:00
 rfs (PID:4493): Selected LNO:4 for T-1.S-505 dbid 2778483057 branch 1036108814
2021-09-26T21:35:02.225079+08:00
ARC2 (PID:3525): Archived Log entry 251 added for T-1.S-505 ID 0xa7521ccd LAD:1
2021-09-26T21:35:02.226252+08:00
ARC2 (PID:3525): Archive log for T-1.S-505 available in 59 minute(s)
2021-09-26T21:35:02.733024+08:00
 rfs (PID:4488): Possible network disconnect with primary database
2021-09-26T22:01:13.848495+08:00
 rfs (PID:20585): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ASYNC (PID:20568)
 rfs (PID:20585): New archival redo branch: 1084312848 current: 1036108814
 rfs (PID:20585): Primary database is in MAXIMUM PERFORMANCE mode
2021-09-26T22:01:13.995667+08:00
 rfs (PID:20587): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is ARCH (PID:14747)
 rfs (PID:20587): New archival redo branch: 1084312848 current: 1036108814
2021-09-26T22:01:15.034084+08:00
 rfs (PID:20585): No SRLs available for T-1
2021-09-26T22:01:15.317462+08:00
 rfs (PID:20587): No SRLs available for T-1
Clearing online log 5 of thread 1 sequence number 0
2021-09-26T22:01:22.282624+08:00
 rfs (PID:20587): Selected LNO:5 for T-1.S-1 dbid 2778483057 branch 1084312848
2021-09-26T22:01:22.283691+08:00
Clearing online log 4 of thread 1 sequence number 0
2021-09-26T22:01:22.380789+08:00
 rfs (PID:20603): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is Foreground (PID:14755)
2021-09-26T22:01:24.283184+08:00
 rfs (PID:20587): A new recovery destination branch has been registered
 rfs (PID:20587): Standby in the future of new recovery destination branch(resetlogs_id) 1084312848
 rfs (PID:20587): Incomplete Recovery SCN:0x0000000000e6e7e1
 rfs (PID:20587): Resetlogs SCN:0x0000000000e88404
 rfs (PID:20587): SBPS:0x00000000004c4f04
 rfs (PID:20587): New Archival REDO Branch(resetlogs_id): 1084312848  Prior: 1036108814
 rfs (PID:20587): Archival Activation ID: 0xaa3191f1 Current: 0xa7521ccd
 rfs (PID:20587): Effect of primary database OPEN RESETLOGS
 rfs (PID:20587): Managed Standby Recovery process is active
2021-09-26T22:01:24.283999+08:00
Incarnation entry added for Branch(resetlogs_id): 1084312848 (STDB2)
2021-09-26T22:01:25.510452+08:00
Setting recovery target incarnation to 4
2021-09-26T22:01:27.818244+08:00
PR00 (PID:3913): MRP0: Incarnation has changed! Retry recovery...
2021-09-26T22:01:28.056186+08:00
Errors in file /opt/oracle/diag/rdbms/stdb2/STDB2/trace/STDB2_pr00_3913.trc:
ORA-19906: recovery target incarnation changed during recovery
Recovery interrupted!
2021-09-26T22:01:29.960402+08:00
Stopping change tracking
2021-09-26T22:01:29.962316+08:00
Errors in file /opt/oracle/diag/rdbms/stdb2/STDB2/trace/STDB2_pr00_3913.trc:
ORA-19906: recovery target incarnation changed during recovery
2021-09-26T22:01:30.588055+08:00
 rfs (PID:20585): Selected LNO:4 for T-1.S-2 dbid 2778483057 branch 1084312848
2021-09-26T22:01:30.942431+08:00
 Started logmerger process
2021-09-26T22:01:32.562040+08:00
PR00 (PID:20631): Managed Standby Recovery not using Real Time Apply
2021-09-26T22:01:34.537732+08:00
ARC2 (PID:3525): Archived Log entry 252 added for T-1.S-1 ID 0xaa3191f1 LAD:1
2021-09-26T22:01:34.537903+08:00
ARC2 (PID:3525): Archive log for T-1.S-1 available in 59 minute(s)
2021-09-26T22:01:34.915506+08:00
max_pdb is 5
2021-09-26T22:01:36.394057+08:00
Parallel Media Recovery started with 4 slaves
2021-09-26T22:01:38.128953+08:00
Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 15238147
Stopping change tracking
2021-09-26T22:01:39.202866+08:00
PR00 (PID:20631): Media Recovery Waiting for B-1036108814.T-1.S-495
2021-09-26T22:01:55.182487+08:00
 rfs (PID:20587): Opened log for T-1.S-495 dbid 2778483057 branch 1036108814
2021-09-26T22:01:57.676763+08:00
 rfs (PID:20585): Selected LNO:5 for T-1.S-3 dbid 2778483057 branch 1084312848
2021-09-26T22:01:58.495693+08:00
ARC3 (PID:3527): Archived Log entry 253 added for T-1.S-2 ID 0xaa3191f1 LAD:1
2021-09-26T22:01:58.495890+08:00
ARC3 (PID:3527): Archive log for T-1.S-2 available in 59 minute(s)
2021-09-26T22:01:59.879131+08:00
 rfs (PID:20587): Archived Log entry 254 added for B-1036108814.T-1.S-495 ID 0xa7521ccd LAD:3
2021-09-26T22:02:00.978032+08:00
PR00 (PID:20631): Media Recovery Delayed for 11 minute(s) T-1.S-495
2021-09-26T22:02:06.096910+08:00
 rfs (PID:20950): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:14753)
2021-09-26T22:02:06.097083+08:00
 rfs (PID:20948): krsr_rfs_atc: Identified database type as 'PHYSICAL STANDBY': Client is FAL (PID:14751)
2021-09-26T22:02:07.293676+08:00
 rfs (PID:20587): Opened log for T-1.S-497 dbid 2778483057 branch 1036108814
2021-09-26T22:02:07.649778+08:00
 rfs (PID:20950): Opened log for T-1.S-498 dbid 2778483057 branch 1036108814
2021-09-26T22:02:07.997282+08:00
 rfs (PID:20948): Opened log for T-1.S-496 dbid 2778483057 branch 1036108814
2021-09-26T22:02:08.520189+08:00
 rfs (PID:20587): Archived Log entry 255 added for B-1036108814.T-1.S-497 ID 0xa7521ccd LAD:3
2021-09-26T22:02:09.302061+08:00
 rfs (PID:20950): Archived Log entry 256 added for B-1036108814.T-1.S-498 ID 0xa7521ccd LAD:3
2021-09-26T22:02:09.707710+08:00
 rfs (PID:20948): Archived Log entry 257 added for B-1036108814.T-1.S-496 ID 0xa7521ccd LAD:3
2021-09-26T22:02:10.761235+08:00
 rfs (PID:20587): Opened log for T-1.S-499 dbid 2778483057 branch 1036108814
2021-09-26T22:02:11.253039+08:00
 rfs (PID:20587): Archived Log entry 258 added for B-1036108814.T-1.S-499 ID 0xa7521ccd LAD:3
2021-09-26T22:02:12.823920+08:00
 rfs (PID:20948): Opened log for T-1.S-500 dbid 2778483057 branch 1036108814
2021-09-26T22:02:13.951243+08:00
 rfs (PID:20948): Archived Log entry 259 added for B-1036108814.T-1.S-500 ID 0xa7521ccd LAD:3
2021-09-26T22:02:15.024761+08:00
 rfs (PID:20587): Opened log for T-1.S-501 dbid 2778483057 branch 1036108814
2021-09-26T22:02:15.534976+08:00
 rfs (PID:20950): Opened log for T-1.S-502 dbid 2778483057 branch 1036108814
2021-09-26T22:02:21.798580+08:00
 rfs (PID:20950): Archived Log entry 260 added for B-1036108814.T-1.S-502 ID 0xa7521ccd LAD:3
2021-09-26T22:02:23.673255+08:00
 rfs (PID:20587): Archived Log entry 261 added for B-1036108814.T-1.S-501 ID 0xa7521ccd LAD:3
2021-09-26T22:02:37.301066+08:00
 rfs (PID:20585): Selected LNO:4 for T-1.S-4 dbid 2778483057 branch 1084312848
2021-09-26T22:02:37.452512+08:00
ARC0 (PID:3517): Archived Log entry 262 added for T-1.S-3 ID 0xaa3191f1 LAD:1
2021-09-26T22:02:37.452615+08:00
ARC0 (PID:3517): Archive log for T-1.S-3 available in 59 minute(s)
2021-09-26T22:10:31.699175+08:00
 rfs (PID:20585): Selected LNO:5 for T-1.S-5 dbid 2778483057 branch 1084312848
2021-09-26T22:10:31.774120+08:00
ARC1 (PID:3523): Archived Log entry 263 added for T-1.S-4 ID 0xaa3191f1 LAD:1
2021-09-26T22:10:31.774245+08:00
ARC1 (PID:3523): Archive log for T-1.S-4 available in 59 minute(s)
2021-09-26T22:10:35.934765+08:00
 rfs (PID:20585): Selected LNO:4 for T-1.S-6 dbid 2778483057 branch 1084312848
2021-09-26T22:10:35.938540+08:00
ARC2 (PID:3525): Archived Log entry 264 added for T-1.S-5 ID 0xaa3191f1 LAD:1
2021-09-26T22:10:35.938646+08:00
ARC2 (PID:3525): Archive log for T-1.S-5 available in 60 minute(s)
2021-09-26T22:12:59.695487+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_495_1036108814.dbf
PR00 (PID:20631): Media Recovery Delayed for 19 minute(s) T-1.S-496
2021-09-26T22:21:12.441616+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_496_1036108814.dbf
2021-09-26T22:21:15.172035+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_497_1036108814.dbf
2021-09-26T22:21:16.215487+08:00
PR00 (PID:20631): Media Recovery Delayed for 20 minute(s) T-1.S-498
2021-09-26T22:22:10.156957+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_498_1036108814.dbf
PR00 (PID:20631): Media Recovery Delayed for 21 minute(s) T-1.S-499
2021-09-26T22:23:15.521168+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_499_1036108814.dbf
PR00 (PID:20631): Media Recovery Delayed for 22 minute(s) T-1.S-500
2021-09-26T22:24:17.854072+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_500_1036108814.dbf
PR00 (PID:20631): Media Recovery Delayed for 26 minute(s) T-1.S-501
2021-09-26T22:28:31.972725+08:00
PR00 (PID:20631): Media Recovery Log /opt/oracle/archivelog/STDB2/1_502_1036108814.dbf
PR00 (PID:20631): Media Recovery Waiting for B-1036108814.T-1.S-503
2021-09-26T22:35:19.026115+08:00
 rfs (PID:20585): Selected LNO:5 for T-1.S-7 dbid 2778483057 branch 1084312848
2021-09-26T22:35:19.052219+08:00
ARC3 (PID:3527): Archived Log entry 265 added for T-1.S-6 ID 0xaa3191f1 LAD:1
2021-09-26T22:35:19.052325+08:00
ARC3 (PID:3527): Archive log for T-1.S-6 available in 59 minute(s)



This has recognised the ResetLogs at 22:01 and has received the new Log Sequences (until Sequence#7) but still hasn't applied Sequence498  from the old Incarnation because that is not yet 1hour old [Sequence#497 has been applied to this Standby as at the time I view the alert log].  All  the new Incarnation Log Sequences# 1 to 6 and onwards will be applied when each is 1hour old.

Since STDB2, seems to have stopped applying ArchiveLogs afer Sequence#502, I stopped and restarted delayed recovery and re-registered ArchiveLogs for Sequence#503 to #505 (that had actually already been Received and Archived between 21:33 and 21:36 earlier).


oracle19c>sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 26 23:07:38 2021
Version 19.12.0.0.0

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database using archived logfile disconnect from session;

Database altered.

SQL> qui
SP2-0042: unknown command "qui" - rest of line ignored.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 26 23:11:34 2021
Version 19.12.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter database register logfile '/opt/oracle/archivelog/STDB2/1_503_1036108814.dbf';

Database altered.

SQL> alter database register logfile '/opt/oracle/archivelog/STDB2/1_504_1036108814.dbf';

Database altered.

SQL> alter database register logfile '/opt/oracle/archivelog/STDB2/1_505_1036108814.dbf';

Database altered.

SQL>


2021-09-26T23:07:57.810826+08:00
Managed Standby Recovery Canceled (STDB2)
Completed: alter database recover managed standby database cancel
2021-09-26T23:08:13.863811+08:00
alter database recover managed standby database using archived logfile disconnect from session
2021-09-26T23:08:13.865416+08:00
Attempt to start background Managed Standby Recovery process (STDB2)
Starting background process MRP0
2021-09-26T23:08:13.877996+08:00
MRP0 started with pid=36, OS id=14015
2021-09-26T23:08:13.879036+08:00
Background Managed Standby Recovery process started (STDB2)
2021-09-26T23:08:18.889440+08:00
 Started logmerger process
2021-09-26T23:08:18.917894+08:00
PR00 (PID:14043): Managed Standby Recovery not using Real Time Apply
max_pdb is 5
2021-09-26T23:08:19.572566+08:00
Parallel Media Recovery started with 4 slaves
2021-09-26T23:08:19.748754+08:00
Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 15238147
Stopping change tracking
2021-09-26T23:08:19.885274+08:00
Completed: alter database recover managed standby database using archived logfile disconnect from session
2021-09-26T23:08:19.969571+08:00
PR00 (PID:14043): Media Recovery Waiting for B-1036108814.T-1.S-503
2021-09-26T23:12:02.832089+08:00
alter database register logfile '/opt/oracle/archivelog/STDB2/1_503_1036108814.dbf'
2021-09-26T23:12:02.832185+08:00
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
2021-09-26T23:12:04.988846+08:00
.... (PID:15189): Resynchronizing from T-1-S-10 to T-1.S-503
Completed: alter database register logfile '/opt/oracle/archivelog/STDB2/1_503_1036108814.dbf'
2021-09-26T23:12:13.842691+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_503_1036108814.dbf
2021-09-26T23:12:15.812907+08:00
PR00 (PID:14043): Media Recovery Waiting for B-1036108814.T-1.S-504
2021-09-26T23:12:22.647534+08:00
alter database register logfile '/opt/oracle/archivelog/STDB2/1_504_1036108814.dbf'
2021-09-26T23:12:22.647609+08:00
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
2021-09-26T23:12:24.805303+08:00
.... (PID:15189): Resynchronizing from T-1-S-503 to T-1.S-504
Completed: alter database register logfile '/opt/oracle/archivelog/STDB2/1_504_1036108814.dbf'
2021-09-26T23:12:30.299323+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_504_1036108814.dbf
PR00 (PID:14043): Media Recovery Waiting for B-1036108814.T-1.S-505
2021-09-26T23:12:36.167593+08:00
alter database register logfile '/opt/oracle/archivelog/STDB2/1_505_1036108814.dbf'
2021-09-26T23:12:36.167671+08:00
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
2021-09-26T23:12:38.271665+08:00
.... (PID:15189): Resynchronizing from T-1-S-504 to T-1.S-505
Completed: alter database register logfile '/opt/oracle/archivelog/STDB2/1_505_1036108814.dbf'
2021-09-26T23:12:41.883099+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_505_1036108814.dbf
2021-09-26T23:12:43.883970+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_1_1084312848.dbf
2021-09-26T23:12:44.047740+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_2_1084312848.dbf
2021-09-26T23:12:44.473162+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_3_1084312848.dbf
2021-09-26T23:12:44.701811+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_4_1084312848.dbf
2021-09-26T23:12:52.961033+08:00
PR00 (PID:14043): Media Recovery Log /opt/oracle/archivelog/STDB2/1_5_1084312848.dbf
PR00 (PID:14043): Media Recovery Delayed for 59 minute(s) T-1.S-6


This is a bug I've noticed in the past. If a Standby has Received and Archived a logfile but hasn't applied it when it should have (Sequence#503 to 505 should have been applied between 22:33 and 22:36 as they were received at 21:33 to 22:35-- you can see the earlier set of alert log entries that show that they were Received and Archived by this database instance), you might shutdown, startup and resume Recovery or simply stop and start Recovery.  If that doesn't work, re-register the ArchiveLogs with the "ALTER DATABASE REGISTER LOGFILE" command again and re-attempt Recovery.

Now, all 3 of my Standby databases are in sync with the Primary (ie. STDBYDB and STDBNEW are current while STDB2 is lagging by 1hour, as designed).

Thus it is possible to do a RESETLOGS on a Primary as long as you ensure that 
1.  The Primary had Archived the last Online Redo Log before Shutdown
2.  The Shutdown was Normal or Immediate
3.  All the Standbys have, at least, Received all the ArchiveLogs (if there was a delay in shipping ArchiveLogs, you can manually copy them to the Standbys and use the ALTER DATABASE REGISTER LOGFILE command at the Standbys)
4.  No actual recovery from Online Redo Logs {that were not shipped to the Standby} was required -- this is the case when the Shutdown is Normal or Immediate

04 September, 2021

ADG and DML Redirection -- with Multiple Standbys

 Building on my previous blog post on Active Data Guard with DML Redirection, here are a few more tests.

First, on the Primary :



22:03:50 SQL> select name, db_unique_name, database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
ORCLCDB   ORCLCDB                        PRIMARY

Elapsed: 00:00:00.02
22:04:08 SQL> alter pluggable database orclpdb1 open ;
alter pluggable database orclpdb1 open
*
ERROR at line 1:
ORA-65019: pluggable database ORCLPDB1 already open


Elapsed: 00:00:00.50
22:04:19 SQL>
22:05:02 SQL> alter session set container=ORCLPDB1;

Session altered.

Elapsed: 00:00:00.04
22:05:10 SQL> create table hemant.test_adg_dml as select * from dba_objects where 1=2;

Table created.

Elapsed: 00:00:01.16
22:05:31 SQL> insert into hemant.test_adg_dml select * from dba_objects;

73661 rows created.

Elapsed: 00:00:09.29
22:06:03 SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
22:06:12 SQL>


Now, one the first Standby as Active Data Guard and with DML Redirection

22:02:08 SQL> select name, db_unique_name, database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
ORCLCDB   STDBYDB                        PHYSICAL STANDBY

Elapsed: 00:00:00.00
22:07:24 SQL> alter database recover managed standby database cancel;

Database altered.

Elapsed: 00:00:01.01
22:07:34 SQL> alter database open read only;

Database altered.

Elapsed: 00:00:34.66
22:08:23 SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

Elapsed: 00:00:10.76
22:08:39 SQL> alter database recover managed standby database disconnect from session;

Database altered.

Elapsed: 00:00:07.04
22:08:48 SQL> connect hemant/hemant@stdbypdb1
Connected.
22:09:12 SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

Session altered.

Elapsed: 00:00:00.00
22:09:28 SQL> select count(*) from hemant.test_adg_dml;

  COUNT(*)
----------
     73661

Elapsed: 00:00:01.60
22:09:48 SQL> insert into hemant.test_adg_dml select * from dba_objects;

73661 rows created.

Elapsed: 00:00:10.71
22:10:13 SQL> select count(*) from hemant.test_adg_dml;

  COUNT(*)
----------
    147322

Elapsed: 00:00:00.01
22:10:24 SQL> commit;

Commit complete.

Elapsed: 00:00:00.09
22:10:27 SQL>


Now, on another Standby with ADG and DML Redirection :

22:11:52 SQL> select name, db_unique_name, database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
ORCLCDB   STDBNEW                        PHYSICAL STANDBY

Elapsed: 00:00:00.03
22:11:53 SQL> alter database recover managed standby database cancel;

Database altered.

Elapsed: 00:00:06.01
22:12:06 SQL> alter database open read only;

Database altered.

Elapsed: 00:00:32.27
22:12:42 SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

Elapsed: 00:00:14.89
22:12:59 SQL> alter database recover managed standby database disconnect from session;

Database altered.

Elapsed: 00:00:06.06
22:13:08 SQL>
22:20:58 SQL> connect hemant/hemant@stdbnewpdb1
Connected.
22:21:04 SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

Session altered.

Elapsed: 00:00:00.01
22:21:18 SQL> select count(*) from hemant.test_adg_dml;

  COUNT(*)
----------
    147322

Elapsed: 00:00:00.02
22:21:27 SQL> insert into hemant.test_adg_dml select * from dba_objects;

73661 rows created.

Elapsed: 00:00:08.60
22:21:50 SQL> select count(*) from hemant.test_adg_dml;

  COUNT(*)
----------
    220983

Elapsed: 00:00:00.01
22:22:06 SQL> commit;

Commit complete.

Elapsed: 00:00:00.21
22:22:08 SQL>
22:22:43 SQL> select name, db_unique_name, database_role, open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCLCDB   STDBNEW                        PHYSICAL STANDBY READ ONLY WITH APPLY

Elapsed: 00:00:00.01
22:22:44 SQL>


So, this proves that we can run ADG and DML Redirection concurrently from multiple Standbys.

Let me test Locking.

I run a DML from STDBYDB and then try a similar DML from the Primary.


22:24:35 SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
----------
ORCLPDB1
READ ONLY


Elapsed: 00:00:00.00
22:24:40 SQL> show user
USER is "HEMANT"
22:24:45 SQL> delete hemant.test_adg_dml;

220983 rows deleted.

Elapsed: 00:00:17.20
22:25:16 SQL>
--- no COMMIT has been issued yet !


-- now this is on the Primary !
22:26:18 SQL> select name, db_unique_name, database_role, open_mode from v$database
22:26:25   2  /

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCLCDB   ORCLCDB                        PRIMARY          READ WRITE

Elapsed: 00:00:00.39
22:26:27 SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
----------
ORCLPDB1
READ WRITE


Elapsed: 00:00:00.12
22:26:33 SQL> delete hemant.test_adg_dml;


This goes into an indefinite WAIT on the Primary
--- from another session, I use utllockt to identify the Blocker and Waiter at the Primary
WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1          LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
135               None
   391            Transaction       Exclusive      Exclusive      65541             5609

22:29:26 SQL> select s.sid, s.sql_id, sq.sql_text
22:30:47   2  from v$session s, v$sql sq
22:30:53   3  where s.sid in (135,391)
22:31:08   4  and s.sql_id=sq.sql_id
22:31:12   5  /

       SID SQL_ID
---------- -------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
       135 9utz9bdavk3bz
DELETE FROM "HEMANT"."TEST_ADG_DML" "A1"

       391 4kmzmr764b4k6
delete hemant.test_adg_dml


22:31:13 SQL>
--- SID=135 is the remote session from the STDBYDB instance


To reconfirm, this, I deliberately, change the SQL Text and try again

--- this from STDBYDB
22:33:19 SQL> rollback;

Rollback complete.

Elapsed: 00:00:52.76
22:34:27 SQL>
22:34:51 SQL> select name, db_unique_name, database_role, open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCLCDB   STDBYDB                        PHYSICAL STANDBY READ ONLY WITH APPLY

Elapsed: 00:00:00.02
22:34:53 SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
----------
ORCLPDB1
READ ONLY


Elapsed: 00:00:00.00
22:35:03 SQL>
22:35:50 SQL> delete /*+ Hint here from STDYBDB */ from hemant.test_adg_dml;

220983 rows deleted.

Elapsed: 00:00:06.78
22:36:27 SQL>

--- this from ORCLCDB
22:34:37 SQL> rollback;

Rollback complete.

Elapsed: 00:00:08.44
22:34:53 SQL>
22:36:41 SQL> select name, db_unique_name, database_role, open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCLCDB   ORCLCDB                        PRIMARY          READ WRITE

Elapsed: 00:00:00.24
22:37:21 SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
----------
ORCLPDB1
READ WRITE


Elapsed: 00:00:00.00
22:37:30 SQL> DELETE  /*+ I am the Primary */ hemant.test_adg_dml target_table;


Again goes into an Indefnite WAIT at the Primary
-- now check using utllockt at the Primary
WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1          LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
135               None
   391            Transaction       Exclusive      Exclusive      393247            7239


SQL> select s.sid, sq.sql_text
  2  from v$session s, v$sql sq
  3  where s.sid in (135, 391)
  4  and s.sql_id = sq.sql_id
  5  /

       SID
----------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
       135
DELETE FROM "HEMANT"."TEST_ADG_DML" "A1"

       391
DELETE  /*+ I am the Primary */ hemant.test_adg_dml target_table


SQL>

---- apparently, when the Standby sends the SQL to the Primary it is rewritten as UPPER CASE
---- and the Invalid Hint that is a Comment is removed
---- nevertheless, this proves that SID=135 from the Standby is the Blocker and SID=391 on the Primary is the Waiter

So, this proves that DML Redirection from an ADG Standby does take locks and prevents concurrent DML against the same rows on the Primary (and, by extension, on any other ADG Standby)


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.


Update :  If the Primary goes down / shuts down, an ADG with DML Redirection will show
ORA-03150: end-of-file on communication channel for database link
ORA-02063: preceding line from ADGREDIRECT
at the next call