Oracle 23ai: Managing Flashback Database Logs Outside the Fast Recovery Area

 

- Overview:

• In previous database releases, you could store flashback database logs only in the fast recovery area.
• Oracle 23ai database introduces the option to store flashback database logs in a separate location.
• With this new option, you can 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.
• A new database instance initialization parameter (DB_FLASHBACK_LOG_DEST) has been introduced to specify a separate location for the flashback database logs. This parameter is a system modifiable on CDB level.

In this blog, I'll demonstrate the steps to set a separate location for the flashback database logs.

Prerequisites:

• Oracle Database 23ai running in archive log mode.

Setting Steps:

1. Connect to a root container using SYS user and confirm the database is running in archive log mode and flashback is ON. 

- Notice that FRA destination is +DATA, and flashback logs were generated in FRA (+DATA).

- Currently DB_FLASHBACK_LOG_DEST is not configured.

2. Before assigning a value for the initialization parameter (DB_FLASHBACK_LOG_DEST), you must specify the size of flashback area by setting the initialization parameter (DB_FLASHBACK_LOG_DEST_SIZE) to a nonzero value. 

3. When modifying the DB_FLASHBACK_LOG_DEST parameter with the ALTER SYSTEM statement, you must disable and reenable flashback logging.

- Notice that flashback destination is +RECO.

SQL> alter system set db_flashback_log_dest_size=100G scope=both;

SQL> alter system set db_flashback_log_dest='+RECO' scope=both;

- Archive log files are still generated in FRA (+DATA).

Oracle 23ai: Read-Only Users and Sessions

 

- Overview:

• Oracle 23ai database introduces a new feature to control whether a user or session is enabled for read-write operations, irrespective of the privileges of the user that is connected to the database. 
• The READ_ONLY user only applies on a PDB local database user.
• The READ_ONLY session applies to any type of user for any type of container.
• With the capability to disable and re-enable the read-write capabilities of any user or session without revoking and re-granting privileges provides you with more flexibility to temporarily control the privileges of users or sessions for testing, administration, or application development purposes.
• It also gives you a simple way to control the read-write behavior within different parts of an application that are used by the same user or session.
• You can set the access of a local user to a PDB to READ ONLY or READ WRITE with the ALTER USER or CREATE USER statement.
• After READ ONLY access is enabled for a PDB user, whenever that user connects to the PDB, the session operates as if the database is open in read-only mode and the user cannot perform any write operation.
• A new column (READ_ONLY) has been added to dictionary views *_USERS to show if READ_ONLY is enabled/disabled for a database user.

In this blog, I'll demonstrate enable/disable READ ONLY for a PDB local database user.

Prerequisites:

• Oracle Database 23ai Free Developer Release.
• A pluggable database with HR database schema.

A Demo Steps:

1. Connect to a PDB using HR local database user and run a DML statement.

- Dictionary view *_USERS currently shows that READ_ONLY is disabled for HR user.

2. Connect to a PDB using SYSTEM common user and enable READ ONLY for HR user using below SQL command.

SQL> alter user &USERNAME read only;

3. Reconnect to a PDB using HR local database user and run a DML statement.

- Dictionary view *_USERS currently shows that READ_ONLY is enabled for HR user.

- You will get below error message.

SQL Error: ORA-28194: Can perform read operations only

4. Using SYSTEM database session, disable READ ONLY for HR user using below SQL command.

SQL> alter user &USERNAME read write;

5. Reconnect to a PDB using HR local database user and run a DML statement. DML statement will run without error.

- Dictionary view *_USERS currently shows that READ_ONLY is disabled for HR user.

Oracle 23ai: Hybrid Read-Only Mode for Pluggable Databases

 

- Overview:

• Oracle 23ai database introduces a new feature to open Pluggable database in a new mode called hybrid read-only.
• Hybrid read-only mode enables a PDB to operate as either read-write or read-only, depending on the user who is connected to the PDB.
• For Oracle Multitenant database common users (like SYSTEM, or C##<USER>), the PDB will be in read-write mode.
• For Pluggable database local users (like HR, or SCOTT), the PDB will be restricted to read-only mode.
• Hybrid read-only mode enables you to patch and maintain an application in a safe mode for open PDBs without the risk of local users, including higher privileged common users, interfering with the ongoing maintenance operation of the PDB.

In this blog, I'll demonstrate opening a PDB in a hybrid read-only mode, then test a DML command from  common and local users.

Prerequisites:

• Oracle Database 23ai Free Developer Release.
• A pluggable database with HR database schema.

Step #1: Open a PDB in Hybrid Read-Only Mode

- Connect to the root container as SYS, close the PDB, then open the PDB in hybrid read-only mode.

SQL> alter pluggable database &PDB_NAME close immediate instances=all;

SQL> alter pluggable database &PDB_NAME OPEN HYBRID READ ONLY instances=all;

Note: The pluggable database open mode will be READ WRITE even after opening in hybrid read-only mode.

Step #2: Testing a DML from a PDB Database Local User

- Connect to the PDB as HR and run a DML statement.

- You will get below error message

SQL Error: ORA-16000: Attempting to modify database or pluggable database that is open for read-only access.

Step #3: Testing a DML from a Common Database User

- Connect to the PDB as system HR and run a DML statement.

- A DML statement will be executed without error.