Testing Open a PDB as a Hybrid Read Only PDB in 23ai

 Oracle 23ai now allows the DBA to open a PDB in Hybrid Read Only mode.  This mode allows Common Users (e.g. SYS or SYSTEM or others defined as Common Users from the Root CDB) to access a PDB in Read-Write mode while local (i.e. non-Common Users) can access the PDB only in Read-Only mode.

This facilitates live maintenance (e.g. patching or changes to the database / schema) being executed by a DBA or Common User while "normal" local users (eg. Application Accounts) can still query the database.

This is a quick demo :

SQL> -- open the PDB as "normal" Read Write
SQL> connect / as sysdba
Connected.
SQL> -- Version 23ai Free Edition
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

SQL>
SQL> alter pluggable database freepdb1 close;

Pluggable database altered.

SQL> alter pluggable database freepdb1 open;

Pluggable database altered.

SQL>
SQL> -- first demo a normal user in the PDB
SQL> connect hemant/hemant@freepdb1
Connected.
SQL> create table x_test (id number , data varchar2(15));

Table created.

SQL> insert into x_test values (1,'First');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First

SQL>
SQL>
SQL> -- now close and open the PDB in Hybrid Read Only mode
SQL> connect / as sysdba
Connected.
SQL> alter pluggable database freepdb1 close;

Pluggable database altered.

SQL> alter pluggable database freepdb1 open hybrid read only ;

Pluggable database altered.

SQL> -- test that SYSTEM (a Common User) can manipulate data -- e.g. INSERT
SQL> connect system/manager@freepdb1
Connected.
SQL> insert into hemant.x_test values(2,'System');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First
         2 System

SQL> -- even grant DBA to hemant
SQL> grant dba to hemant;

Grant succeeded.

SQL>
SQL> -- test hemant a non-common user
SQL> -- see if the user can execute INSERT and SELECT
SQL> connect hemant/hemant@freepdb1
Connected.
SQL> insert into hemant.x_test values(3,'Third');
insert into hemant.x_test values(3,'Third')
                   *
ERROR at line 1:
ORA-16000: Attempting to modify database or pluggable database that is open for
read-only access.
Help: https://docs.oracle.com/error-help/db/ora-16000/


SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First
         2 System

SQL> -- so SELECT works, but not INSERT
SQL>
SQL>
SQL> -- reopen PDB as normal "Read Write"
SQL> connect / as sysdba
Connected.
SQL> alter pluggable database freepdb1 close;

Pluggable database altered.

SQL> alter pluggable database freepdb1 open;

Pluggable database altered.

SQL> connect hemant/hemant@freepdb1
Connected.
SQL> insert into hemant.x_test values(4,'Fourth');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hemant.x_test order by 1;

        ID DATA
---------- ---------------
         1 First
         2 System
         4 Fourth

SQL>

Thus, when the PDB was first opened in "normal" mode (ie the default OPEN mode is OPEN READ WRITE), the local user  "HEMANT" could execute DDL and DML (create the table and Insert).  

When it was reopened in Hybrid Read Only Mode, the user could not make changes (insert the row with ID=3) but could still query the data (even if the user has been granted "DBA").  However,  the Common User "SYSTEM" was allowed to insert the row with ID=2, DATA='SYSTEM'.

Finally, reopening the PDB in "normal" OPEN READ WRITE mode, the user "HEMANT" could again insert a row (ID=4)

Testing RENAME LOB (Segment) in 23ai

Another new feature of 23ai is the ability to rename a LOB (Segment) in-place without having to use the MOVE clause.

A quick demo :

SQL> -- Version 23ai Free Edition
SQL> select banner from v$version;

BANNER
---------------------------------------------------------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

SQL>
SQL>
SQL> DROP TABLE my_lob_objects purge;

Table dropped.

SQL>
SQL> -- create the table with a LOB, column name "c",  lob segment name also "c"
SQL> CREATE TABLE my_lob_objects (object_id NUMBER primary key, c CLOB)
  2        lob (c) STORE AS SECUREFILE c
  3        ( TABLESPACE users
  4          DISABLE STORAGE IN ROW
  5          NOCACHE LOGGING
  6          RETENTION AUTO
  7          COMPRESS
  8        );

Table created.

SQL>
SQL> -- query the data dictionary
SQL> select table_name, column_name, segment_name, tablespace_name from user_lobs;

TABLE_NAME       COLUMN_NAME      SEGMENT_NAME         TABLESPACE_NAME
---------------- ---------------- -------------------- ----------------
MY_LOB_OBJECTS   C                C                    USERS

SQL>
SQL> -- insert three rows
SQL> insert into my_lob_objects values (1, dbms_random.string('X',100));

1 row created.

SQL> insert into my_lob_objects values (2, dbms_random.string('X',100));

1 row created.

SQL> insert into my_lob_objects values (3, dbms_random.string('X',100));

1 row created.

SQL>
SQL> -- verify the column name when querying the table
SQL> select * from my_lob_objects;

 OBJECT_ID C
---------- --------------------------------------------------------------------------------
         1 IBGOGKA9QKK56O746IJL3C56ZK9LEO0G1W4LWBN11T8EWCFTTLUW9TPIVQAU8BPSGPQ2ZV57BS0ZPK0S
         2 7K04DVVYDQB1URIQ1OQ2458M8ZOURHWW50XIZDMVGAZH6XVN2KKN4PIGKPY5CSVIQ9KU45LHZPJB33AA
         3 2G5194Z7TSR3XG0K698G587AOZOJ8VN6KFCTCH3074TNCOWCSMOPRJLRGTLIZMDD73XAY4KDD14IW4MG

SQL>
SQL> -- now rename the column
SQL> alter table my_lob_objects rename column c to clob_col;

Table altered.

SQL>
SQL> -- query the data dictionary
SQL> select table_name, column_name, segment_name, tablespace_name from user_lobs;

TABLE_NAME       COLUMN_NAME      SEGMENT_NAME         TABLESPACE_NAME
---------------- ---------------- -------------------- ----------------
MY_LOB_OBJECTS   CLOB_COL         C                    USERS

SQL>
SQL> -- now rename the lob segment
SQL> alter table my_lob_objects rename lob(clob_col) c to my_lob_objects_clob;

Table altered.

SQL>
SQL> -- query the data dictionary
SQL> select table_name, column_name, segment_name, tablespace_name from user_lobs;

TABLE_NAME       COLUMN_NAME      SEGMENT_NAME         TABLESPACE_NAME
---------------- ---------------- -------------------- ----------------
MY_LOB_OBJECTS   CLOB_COL         MY_LOB_OBJECTS_CLOB  USERS

SQL>
SQL> -- verify the column name when querying the table
SQL> select * from my_lob_objects;

 OBJECT_ID CLOB_COL
---------- --------------------------------------------------------------------------------
         1 IBGOGKA9QKK56O746IJL3C56ZK9LEO0G1W4LWBN11T8EWCFTTLUW9TPIVQAU8BPSGPQ2ZV57BS0ZPK0S
         2 7K04DVVYDQB1URIQ1OQ2458M8ZOURHWW50XIZDMVGAZH6XVN2KKN4PIGKPY5CSVIQ9KU45LHZPJB33AA
         3 2G5194Z7TSR3XG0K698G587AOZOJ8VN6KFCTCH3074TNCOWCSMOPRJLRGTLIZMDD73XAY4KDD14IW4MG

SQL>
SQL> -- identify the segment
SQL> select tablespace_name, segment_name, segment_type, bytes/1024 Size_KB
  2  from user_segments
  3  where segment_name = 'MY_LOB_OBJECTS_CLOB'
  4  /

TABLESPACE_NAME  SEGMENT_NAME         SEGMENT_TYPE         SIZE_KB
---------------- -------------------- ------------------ ---------
USERS            MY_LOB_OBJECTS_CLOB  LOBSEGMENT              2304

SQL>

First I create a Table where the Column and LOB (Segment) are both called "C".  In recent versions, SECUREFILE is the default and recommended for LOBs (e.g. with the COMPRESS, DEDUPLICATION and ENCRYPTION advantages).

Then I insert 3 rows.

I then rename the column "C" to "CLOB_COL".

Next, I rename the LOB (Segment) to "MY_LOB_OBJECTS_CLOB".  I include the Table Name because the LOB segment is an independent segment that I might query in USER_SEGMENTS (where Table Name) is not available.  This RENAME LOB clause is new in 23ai and does not require the use of MOVE LOB.

I then verify the new Segment Name for the LOB as well.

Yes, the 2,304KB "size" seems excessive but this will make sense (with the COMPRESS attribute) when the LOB grows much much larger as new rows with long Character-Strings are inserted.

Testing DEFAULT ON NULL FOR UPDATE in 23ai

 Testing  a new feature in 23ai that allows you to define a DEFAULT value for a column if a user/front-end/application sends a NULL :

[oracle@oel9 ~]$ sqlplus hemant/hemant@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Thu May 9 14:54:25 2024
Version 23.4.0.24.05

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


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

SQL> create table my_new_employees(
  2  employee_id number(12) primary key,
  3  employee_name varchar2(48),
  4  department_id number(12)
  5  )
  6  /

Table created.

SQL>
SQL> insert into my_new_employees
  2  values (1,'Hemant',NULL)
  3  /

1 row created.

SQL>
SQL> select * from my_new_employees;

EMPLOYEE_ID EMPLOYEE_NAME                                    DEPARTMENT_ID
----------- ------------------------------------------------ -------------
          1 Hemant

SQL>
SQL> update my_new_employees
  2  set department_id=100  -- setting a non-NULL value
  3  where employee_id=1
  4  /

1 row updated.

SQL> select * from my_new_employees;

EMPLOYEE_ID EMPLOYEE_NAME                                    DEPARTMENT_ID
----------- ------------------------------------------------ -------------
          1 Hemant                                                     100

SQL>
SQL> alter table my_new_employees
  2  modify (department_id default on null for insert and update 512);

Table altered.

SQL> insert into my_new_employees
  2  values (2,'Larry');    -- I am not specifying a value for DEPARTMENT_ID 
insert into my_new_employees
            *
ERROR at line 1:
ORA-00947: not enough values
Help: https://docs.oracle.com/error-help/db/ora-00947/


SQL> insert into my_new_employees
  2  values(2,'Larry', NULL); -- I explicitly specify NULL for DEPARTMENT_ID

1 row created.

SQL> select * from my_new_employees order by 1;

EMPLOYEE_ID EMPLOYEE_NAME                                    DEPARTMENT_ID
----------- ------------------------------------------------ -------------
          1 Hemant                                                     100
          2 Larry                                                      512  -- it got set to 512 ON INSERT

SQL>
SQL> update my_new_employees
  2  set employee_name = 'Hemant Chitale', department_id=NULL -- I explicitly specify NULL for DEPARMTENT_ID
  3  where employee_id=1
  4  /

1 row updated.

SQL> select * from my_new_employees order by 1;

EMPLOYEE_ID EMPLOYEE_NAME                                    DEPARTMENT_ID
----------- ------------------------------------------------ -------------
          1 Hemant Chitale                                             512  -- it got set to 512 ON UPDATE
          2 Larry                                                      512

SQL>
SQL> commit;

Commit complete.

SQL> select * from my_new_employees order by 1;

EMPLOYEE_ID EMPLOYEE_NAME                                    DEPARTMENT_ID
----------- ------------------------------------------------ -------------
          1 Hemant Chitale                                             512
          2 Larry                                                      512

SQL>

So, now the column has a proper DEFAULT value when an UPDATE statement sends a NULL.  This allows the developer to avoid having to write a Front-End Value Check or a Database Table Trigger to convert an incoming NULL to a DEFAULT value.

Testing DB_FLASHBACK_LOG_DEST in 23ai

 Pre-23ai, Database Flashback Logs always were created in the DB_RECOVERY_FILE_DEST location (i.e.. the well-known "Fast Recovery Area" also known as "FRA").

However, these would share space with ArchiveLogs (if you've configured ArchiveLogs to the same location) and RMAN Backups (if you aren't specifically writing RMAN Backups to a different location).

23ai introduced the DB_FLASHBACK_LOG_DEST (and corresponding DB_FLASHBACK_LOG_DEST_SIZE) parameter to allow you to separate your Database Flashback Logs to a dedicated location.

The 23ai  New Features documentation has this to say :

In previous releases, you could store flashback database logs only in the fast recovery area. Now you can optionally designate a separate location for flashback logging. For example, if you have write-intensive database workloads, then flashback database logging can slow down the database if the fast recovery area is not fast enough. In this scenario, you can now choose to write the flashback logs to faster disks. Using a separate destination also eliminates the manual administration to manage the free space in the fast recovery area.

Managing flashback database logs outside the fast recovery area lowers the operational costs related to space management and guarantees the best performance for workloads that are typically impacted by flashback logging on traditional storage.

And it provides a link to the documentation on the parameter.

You might think that DB_FLASHBACK_LOG_DEST is now completely independent of DB_RECOVERY_FILE_DEST.

Here is my test run where I configured DB_FLASHBACK_LOG_DEST  without configuring DB_RECOVERY_FILE_DEST :

h-4.4$ cd /opt/oracle
sh-4.4$ mkdir FBL
sh-4.4$ mkdir FRA
sh-4.4$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Sun May 5 10:26:26 2024
Version 23.4.0.24.05

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


Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

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

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             402653184 bytes
Database Buffers         1191182336 bytes
Redo Buffers                4530176 bytes
Database mounted.
SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/oracle/product/23ai/dbhom
                                                 eFree/dbs/spfileFREE.ora
SQL>
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter db_flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_log_dest                string
db_flashback_log_dest_size           big integer 0
SQL> alter system set db_flashback_log_dest_size=10G;

System altered.

SQL> alter system  set db_flashback_log_dest='/opt/oracle/FBL';

System altered.

SQL> create restore point MY_FIRST_RP ;

Restore point created.

SQL> alter system archive log current;

System altered.

SQL>
SQL> create table x as select * from cdb_objects;

Table created.

SQL> insert into x select * from x;

141420 rows created.

SQL> delete x;

282840 rows deleted.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL>
SQL> alter database flashback on;  -- only here I enable Flashback

Database altered.

==============================================
alert log messages :
2024-05-05T10:38:35.262274+00:00
alter database flashback on
2024-05-05T10:38:35.423698+00:00
Allocating 8388608 bytes in shared pool for flashback generation buffer.
Allocated 8388608 bytes in shared pool for flashback generation buffer
Flashback Database Enabled at SCN 3124894
===============================================

SQL> create restore point MY_FIRST_RP;  -- testing if I can create another RP with the same name
create restore point MY_FIRST_RP
*
ERROR at line 1:
ORA-38778: Restore point 'MY_FIRST_RP' already exists.
Help: https://docs.oracle.com/error-help/db/ora-38778/


SQL> drop restore point MY_FIRST_RP;

Restore point dropped.

SQL> create restore point MY_FIRST_RP;

Restore point created.

SQL> drop table x;

Table dropped.

SQL> create table x as select * from cdb_objects;

Table created.

SQL>
SQL> alter system archive log current;

System altered.

SQL> delete x;

141420 rows deleted.

SQL> insert into x select * from cdb_objects;

141421 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL>
SQL> select substr(name,1,32), scn, time from v$restore_point;  -- identify the RP that has been created

SUBSTR(NAME,1,32)
--------------------------------------------------------------------------------------------------------------------------------
       SCN TIME
---------- ---------------------------------------------------------------------------
MY_FIRST_RP
   3124955 05-MAY-24 10.39.30.000000000 AM


SQL> select * from v$flashback_database_log;  -- identify the FBDB Logs Size

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE     CON_ID
-------------------- --------- ---------------- -------------- ------------------------ ----------
             3124893 05-MAY-24             1440      419430400                        0          0

SQL> select * from v$flashback_log_dest; -- identify the FB Log Dest (why isn't the view named V$FLASHBACK_DATABASE_LOG_DEST ?)

NAME
------------------------------------------------------------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED NUMBER_OF_FILES     CON_ID
----------- ---------- --------------- ----------
/opt/oracle/FBL
 1.0737E+10  419430400               2          0


SQL>
SQL> !sh
sh-4.4$ cd /opt/oracle/FBL
sh-4.4$ du -sh *
401M    FREE
sh-4.4$ cd FREE
sh-4.4$ ls
flashback
sh-4.4$ cd flashback
sh-4.4$ ls -l
total 409620
-rw-r----- 1 oracle oinstall 209723392 May  5 10:41 o1_mf_m3grfc8t_.flb
-rw-r----- 1 oracle oinstall 209723392 May  5 10:38 o1_mf_m3grfg1v_.flb
sh-4.4$
sh-4.4$ cd $ORACLE_HOME/dbs
sh-4.4$ ls -l arch1*
-rw-r----- 1 oracle oinstall  98164736 May  5 10:31 arch1_2_1167168121.dbf
-rw-r----- 1 oracle oinstall 106480640 May  5 10:33 arch1_3_1167168121.dbf
-rw-r----- 1 oracle oinstall  37506048 May  5 10:40 arch1_4_1167168121.dbf
-rw-r----- 1 oracle oinstall  52515840 May  5 10:40 arch1_5_1167168121.dbf
sh-4.4$
sh-4.4$ exit
exit

SQL> select count(*) from x;

  COUNT(*)
----------
    141421

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

Total System Global Area 1603726344 bytes
Fixed Size                  5360648 bytes
Variable Size             419430400 bytes
Database Buffers         1174405120 bytes
Redo Buffers                4530176 bytes
Database mounted.
SQL> flashback database to restore point MY_FIRST_RP;   -- try to Flashback the Database
flashback database to restore point MY_FIRST_RP
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
Help: https://docs.oracle.com/error-help/db/ora-38760/


============================================
alert log messages :
2024-05-05T10:45:28.380285+00:00
Successful mount of redo thread 1, with mount id 1440201864
2024-05-05T10:45:28.380506+00:00
Allocating 8388608 bytes in shared pool for flashback generation buffer.
Allocated 5807328 bytes in shared pool for flashback generation buffer
RVWR could not begin generation of flashback log data because
DB_RECOVERY_FILE_DEST is not set.
2024-05-05T10:45:28.392865+00:00
Errors in file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_rvwr_2343.trc:
ORA-38776: cannot begin flashback generation - recovery area is disabled
2024-05-05T10:45:28.392899+00:00
WARNING: Cannot open the flashback thread for this instance due to the above error.
WARNING: Flashback thread open failed - to resolve this, either correct the reported error or turn off database flashbac
k.
2024-05-05T10:45:28.393060+00:00
Database mounted in Exclusive Mode
Lost write protection mode set to "auto"
Completed: ALTER DATABASE   MOUNT
2024-05-05T10:46:04.458087+00:00
flashback database to restore point MY_FIRST_RP
ORA-38760 signalled during: flashback database to restore point MY_FIRST_RP...
2024-05-05T10:50:43.887137+00:00
==============================================


Explanation of the Error :
===========================
38776, 00000, "cannot begin flashback generation - recovery area is disabled"
// *Cause: During a database mount, the RVWR process discovered that the
//         recovery area was disabled.  DB_RECOVERY_FILE_DEST must have
//         been set null or removed from the INIT.ORA file while the database
//         was unmounted.
// *Action: Flashback database requires the recovery area to be enabled.
//          Either enable the recovery area by setting the
//          DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization
//          parameters, or turn off flashback database with the
//          ALTER DATABASE FLASHBACK OFF command.

So, Oracle 

1.  allows me to create a Restore Point

2.  generates Flashback Log

3.  confirms that they exist

BUT DOES NOT ALLOW ME TO FLASHBACK THE DATABASE.

DB_RECOVERY_FILE_DEST is still mandatory (even if you have ArchiveLogs being written elsewhere -- my ArchiveLogs were going to the default location under $ORACLE_HOME/dbs).

If you look at the documentation (column "Required" in Table 5-4) it does say that DB__RECOVERY_FILE_DEST is a required parameter -- although it doesnt explicitly say that this is required for the FLASHBACK LOGS.  Also, my database was happy to generate ArchiveLogs in another, default, location ($ORACLE_HOME/dbs).

The CREATE RESTORE POINT command didn't give me any warning that the Restore Point would not be usable -- I would treat this as a Bug.

Video on monitoring (a single instance) DataGuard Standby for a RAC (2-node) Cluster

 I've just uploaded a new video on monitoring DataGuard where the Standby is a Single Instance environment for a 2-node RAC Cluster.

The script used in the demo are in this ZIP  (script files with extension TXT)