Hemant K Chitale
Video on the Maximum Availability Protection Mode in Oracle DataGuard (with RAC)
I've published a new video demonstrating Maximum Availability Protection Mode in Oracle DataGuard.
Testing updated ORA-942 Error Message in 23ai
Oracle 23ai now has a long-requested fix to the ORA-942 "table or view does not exist" error message. The error message would not print out *which* table or view was missing -- this was particularly troublesome with very long SQL statements and multiple tables and views being referenced.
A demo of the fixed behaviour in 23ai (followed later by the same code in 19c 19.22)
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> -- create the two tables SQL> drop table employees; drop table employees * ERROR at line 1: ORA-00942: table or view "HEMANT"."EMPLOYEES" does not exist Help: https://docs.oracle.com/error-help/db/ora-00942/ SQL> drop table departments; Table dropped. SQL> create table departments (dept_id number(12) primary key, dept_name varchar2(30)); Table created. SQL> create table employees (emp_id number(12) primary key, emp_name varchar2(54), dept_id number(12)); Table created. SQL> SQL> -- insert data SQL> insert into departments values (1,'Human Resources'); 1 row created. SQL> insert into employees values (1,'Somebody',1); 1 row created. SQL> commit; Commit complete. SQL> SQL> -- test query SQL> select d.dept_id, d.dept_name, e.emp_name 2 from departments d, employees e 3 where e.dept_id = d.dept_id 4 order by d.dept_id,e.emp_name 5 / DEPT_ID DEPT_NAME EMP_NAME ---------- ------------------------------ ------------------------------------------------------ 1 Human Resources Somebody SQL> SQL> -- drop a table SQL> drop table employees; Table dropped. SQL> SQL> -- RETEST query SQL> select d.dept_id, d.dept_name, e.emp_name 2 from departments d, employees e 3 where e.dept_id = d.dept_id 4 order by d.dept_id,e.emp_name 5 / from departments d, employees e * ERROR at line 2: ORA-00942: table or view "HEMANT"."EMPLOYEES" does not exist Help: https://docs.oracle.com/error-help/db/ora-00942/ SQL>
So, now the error message text for ORA-942 includes the name of the missing <schema>.<table_or_view>
Here is the same code in 19c (19.22)
SQL> --select banner from v$version; SQL> -- use BANNER_FULL in 19c to get RU level -- 19.22 SQL> select banner_full from v$version; BANNER_FULL ------------------------------------------------------------------------------------------------------------------------------------ Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.22.0.0.0 SQL> SQL> -- create the two tables SQL> drop table employees; drop table employees * ERROR at line 1: ORA-00942: table or view does not exist SQL> drop table departments; Table dropped. SQL> create table departments (dept_id number(12) primary key, dept_name varchar2(30)); Table created. SQL> create table employees (emp_id number(12) primary key, emp_name varchar2(54), dept_id number(12)); Table created. SQL> SQL> -- insert data SQL> insert into departments values (1,'Human Resources'); 1 row created. SQL> insert into employees values (1,'Somebody',1); 1 row created. SQL> commit; Commit complete. SQL> SQL> -- test query SQL> select d.dept_id, d.dept_name, e.emp_name 2 from departments d, employees e 3 where e.dept_id = d.dept_id 4 order by d.dept_id,e.emp_name 5 / DEPT_ID DEPT_NAME EMP_NAME ---------- ------------------------------ ------------------------------------------------------ 1 Human Resources Somebody SQL> SQL> -- drop a table SQL> drop table employees; Table dropped. SQL> SQL> -- RETEST query SQL> select d.dept_id, d.dept_name, e.emp_name 2 from departments d, employees e 3 where e.dept_id = d.dept_id 4 order by d.dept_id,e.emp_name 5 / from departments d, employees e * ERROR at line 2: ORA-00942: table or view does not exist SQL>
So, upto 19c, ORA-942 would simply say "table or view does not exist". In my example using sqlplus and a very short from list, the "*" does indicate where the error is -- but with other tools / clients and long FROM or sub-queries with FROM clauses, it may well be difficult to identify the missing table or view unless you trace the execution or run a test query against each table or view.
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).
Testing RENAME LOB (Segment) in 23ai
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).
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
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)
Video on Client Side Failover in RAC
Video on Host Names, SCAN and IP Addresses
Grid Infrastructure --- OCR and Voting Disks Location at Installation
In Oracle Grid Infrastructure, the OCR (Oracle Cluster Registry) and Voting "Disks" must be on Shared Storage accessible by all the nodes of the Cluster. Typically, these are on ASM.
In ASM, a DiskGroup is created for the disks that hold the OCR.
Normally, an ASM DiskGroup may use External Redundancy (Mirroring or other protection against Physical Disk or LUN failure is provided by the underlying Storage) or Normal Redundancy (Two-Way Mirroring, i.e. two Disks or LUN devices) or High Redundancy (Three-Way Mirroring with three Disks).
However, for the OCR and Voting "Disks" (i.e. Voting File), Normal Redundancy requires three Disks or LUN devices where three Voting Files and one OCR (Primary and Secondary copy) are created. High Redundancy requires five Disks or LUN devices where five Voting Files and once OCR (with one Primary and two Secondary copies) are created.
In Test or Lab environments, you might have created your OCR/Vote DiskGroup on ASM storage with External Redundancy so as to not have to provision 3 or 5 disks.
However, in the 19c Lab environment with 2 Virtual Box VMs that I recently built on my Home PC, I created 5 ASM Disks of 2GB each (using ASMLib instead of udev persistent naming) to hold the OCR + VOTE DiskGroup. I then selected High Redundancy for the consequent DiskGroup.
This is the Installer Screen :
This is the subsequent output from running root.sh from the Grid ORACLE_HOME towards the end of the installation :
[datetime] CLSRSC-482: Running command: '/u01/app/grid/product/19.3/bin/ocrconfig -upgrade grid grid'
CRS-4256: Updating the profile
Successful addition of voting disk 6c3ea5fbf0254fd5bfd489fc5c674409.
Successful addition of voting disk ff3b9da031064fccbfab4b57933f12e1.
Successful addition of voting disk 44e50015bcf24f7cbfc1b9348fdbe568.
Successful addition of voting disk de64da366c164f5cbfba2761df5948d5.
Successful addition of voting disk 4485ff5940384f85bf524a81090c6bd8.
Successfully replaced voting disk group with +OCR_VOTE.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 6c3ea5fbf0254fd5bfd489fc5c674409 (/dev/oracleasm/disks/OCR_VOTE_DISK_1) [OCR_VOTE]
2. ONLINE ff3b9da031064fccbfab4b57933f12e1 (/dev/oracleasm/disks/OCR_VOTE_DISK_2) [OCR_VOTE]
3. ONLINE 44e50015bcf24f7cbfc1b9348fdbe568 (/dev/oracleasm/disks/OCR_VOTE_DISK_3) [OCR_VOTE]
4. ONLINE de64da366c164f5cbfba2761df5948d5 (/dev/oracleasm/disks/OCR_VOTE_DISK_4) [OCR_VOTE]
5. ONLINE 4485ff5940384f85bf524a81090c6bd8 (/dev/oracleasm/disks/OCR_VOTE_DISK_5) [OCR_VOTE]
Located 5 voting disk(s).
Thus it did create 5 Voting "Disks" (Voting Files).
After the installation is completed, I verified this again
from the first node "srv1":
and from the second node "srv2" :
Note : Whether I create the DiskGroup with Normal or High Redundancy, it will still show only 1 OCR because there is only 1 Primary OCR location (Normal or High Redundancy will automatically create 1 or 2 Secondary OCR copy).
It is possible to add another location for OCR in this manner (where I add to the FRA DiskGroup):
Furthermore, each node of the Cluster has a Local Cluster Registry (that is called an OLR) :
If you are worried about Failure Groups for the OCR_VOTE DiskGroup, you can see that the FailureGroups are automatically created for this High Redundancy DiskGroup :
Installing and Running DBSAT on 21c
DBSAT is Oracle's "Database Security Assessment Tool" that you can get from Oracle Support Document "Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1)" .
This is the User Guide for the current release (3.1)
It does checks against "proven Oracle Database Security best practices, CIS benchmark recommendations and STIG rules".
See Oracle Support Document "Does DBSAT Scan for all of the STIG And CIS Benchmark Controls? (Doc ID 2651827.1)" for a disclaimer.
Here I demonstrate quick installation :
[oracle@node2 ~]$ cd /u01/app/oracle [oracle@node2 oracle]$ cd DB* [oracle@node2 DBSAT_Installer]$ pwd /u01/app/oracle/DBSAT_Installer [oracle@node2 DBSAT_Installer]$ ls -l total 45180 -rwxr-x---. 1 oracle dba 46264143 Mar 5 22:15 DBSAT.zip [oracle@node2 DBSAT_Installer]$ which unzip /bin/unzip [oracle@node2 DBSAT_Installer]$ [oracle@node2 DBSAT_Installer]$ unzip DBSAT.zip Archive: DBSAT.zip inflating: dbsat inflating: dbsat.bat inflating: sat_collector.sql inflating: sa.jar inflating: jython-standalone-2.7.3.jar inflating: xlsxwriter/app.py inflating: xlsxwriter/chart_area.py inflating: xlsxwriter/chart_bar.py inflating: xlsxwriter/chart_column.py .... .... deleted a few lines of output of the unzip command inflating: Discover/conf/sensitive_en.ini inflating: Discover/conf/sensitive_es.ini inflating: Discover/conf/sensitive_de.ini inflating: Discover/conf/sensitive_pt.ini inflating: Discover/conf/sensitive_it.ini inflating: Discover/conf/sensitive_fr.ini inflating: Discover/conf/sensitive_nl.ini inflating: Discover/conf/sensitive_el.ini [oracle@node2 DBSAT_Installer]$ --- create the default script from the User Guide [oracle@node2 DBSAT_Installer]$ cat > DBSAT_User.sql create user dbsat_user identified by dbsat_user; --If Database Vault is enabled, connect as DV_ACCTMGR to run this command grant create session to dbsat_user; grant select_catalog_role to dbsat_user; grant select on sys.registry$history to dbsat_user; grant read on sys.dba_audit_mgmt_config_params to dbsat_user; grant select on sys.dba_users_with_defpwd to dbsat_user; grant read on sys.dba_credentials to dbsat_user; grant execute on sys.dbms_sql to dbsat_user; grant audit_viewer to dbsat_user; // 12c and later grant capture_admin to dbsat_user;// 12c and later covers sys.dba_priv_captures, sys.priv_capture$, sys.capture_run_log$ [oracle@node2 DBSAT_Installer]$ --- verify the script [oracle@node2 DBSAT_Installer]$ cat DBSAT_User.sql create user dbsat_user identified by dbsat_user; --If Database Vault is enabled, connect as DV_ACCTMGR to run this command grant create session to dbsat_user; grant select_catalog_role to dbsat_user; grant select on sys.registry$history to dbsat_user; grant read on sys.dba_audit_mgmt_config_params to dbsat_user; grant select on sys.dba_users_with_defpwd to dbsat_user; grant read on sys.dba_credentials to dbsat_user; grant execute on sys.dbms_sql to dbsat_user; grant audit_viewer to dbsat_user; // 12c and later grant capture_admin to dbsat_user;// 12c and later covers sys.dba_priv_captures, sys.priv_capture$, sys.capture_run_log$ [oracle@node2 DBSAT_Installer]$ -- I then create this user in my custom PDB [oracle@node2 DBSAT_Installer]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Tue Mar 5 22:30:29 2024 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL> alter session set container=HEMANTPDB; Session altered. SQL> @DBSAT_User.sql User created. Grant succeeded. Grant succeeded. Grant succeeded. Grant succeeded. Grant succeeded. Grant succeeded. Grant succeeded. SQL>
I am now ready to run the Collector.
[oracle@node2 DBSAT_Installer]$ ./dbsat collect dbsat_user/dbsat_user@hemantpdb Database Security Assessment Tool version 3.1 (Jan 2024) This tool is intended to assist you in securing your Oracle database system. You are solely responsible for your system and the effect and results of the execution of this tool (including, without limitation, any damage or data loss). Further, the output generated by this tool may include potentially sensitive system configuration data and information that could be used by a skilled attacker to penetrate your system. You are solely responsible for ensuring that the output of this tool, including any generated reports, is handled in accordance with your company's policies. Usage: dbsat collect [ -n ] -lt database_connect_string > -lt output_file > dbsat report [ -a ] [ -n ] [ -g ] [ -x -lt section > ] [ -u -lt user > ] -lt input_file > dbsat discover [ -n ] -c -lt config_file > -lt output_file > Options: -a Report with all user accounts, including locked and schema-only, Oracle-supplied users -n No encryption for output -g Show all grants including Common Grants in a Pluggable Database -x Specify sections to exclude from report (may be repeated for multiple sections) -u Specify users to exclude from report -c Configuration file for discoverer [oracle@node2 DBSAT_Installer]$ ./dbsat collect dbsat_user/dbsat_user@hemantpdb hemantpdb_DBSAT_Report Database Security Assessment Tool version 3.1 (Jan 2024) This tool is intended to assist you in securing your Oracle database system. You are solely responsible for your system and the effect and results of the execution of this tool (including, without limitation, any damage or data loss). Further, the output generated by this tool may include potentially sensitive system configuration data and information that could be used by a skilled attacker to penetrate your system. You are solely responsible for ensuring that the output of this tool, including any generated reports, is handled in accordance with your company's policies. Connecting to the target Oracle database... SQL*Plus: Release 21.0.0.0.0 - Production on Tue Mar 5 22:34:39 2024 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 Setup complete. SQL queries complete. /bin/cat: /u01/app/oracle/product/21.3.0.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory Warning: Exit status 256 from OS rule: sqlnet.ora /bin/ls: cannot access /u01/app/oracle/product/21.3.0.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory Warning: Exit status 512 from OS rule: ls_sqlnet.ora /bin/cat: /u01/app/oracle/product/21.3.0.0/dbhome_1/network/admin/listener.ora: No such file or directory Warning: Exit status 256 from OS rule: listener.ora /bin/ls: cannot access /u01/app/oracle/product/21.3.0.0/dbhome_1/network/admin/listener.ora: No such file or directory Warning: Exit status 512 from OS rule: ls_listener.ora Warning: Exit status 256 from OS rule: dbcs_status /bin/cat: /u01/app/oracle/product/21.3.0.0/dbhome_1/network/admin/cman.ora: No such file or directory Warning: Exit status 256 from OS rule: cman.ora /bin/cat: /u01/app/oracle/product/21.3.0.0/dbhome_1/ldap/admin/fips.ora: No such file or directory Warning: Exit status 256 from OS rule: fips1.ora /bin/cat: /fips.ora: No such file or directory Warning: Exit status 256 from OS rule: fips2.ora /bin/ls: cannot access /diag: No such file or directory Warning: Exit status 512 from OS rule: diag_dest_base /bin/ls: cannot access /u01/app/oracle/product/21.3.0.0/dbhome_1/rdbms/log/diag: No such file or directory Warning: Exit status 512 from OS rule: diag_dest_home OS commands complete. Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 DBSAT Collector completed successfully. Calling /u01/app/oracle/product/21.3.0.0/dbhome_1/bin/zip to encrypt hemantpdb_DBSAT_Report.json... Enter password: Verify password: adding: hemantpdb_DBSAT_Report.json (deflated 86%) zip completed successfully. [oracle@node2 DBSAT_Installer]$
So, apparently it assumes the old convention of the network folders being under ORACLE_HOME.
[oracle@node2 DBSAT_Installer]$ rm hemantpdb_DBSAT_Report.json [oracle@node2 DBSAT_Installer]$ ORACLE_BASE=/u01/app/oracle;export ORACLE_BASE [oracle@node2 DBSAT_Installer]$ ./dbsat collect dbsat_user/dbsat_user@hemantpdb hemantpdb_DBSAT_Report Database Security Assessment Tool version 3.1 (Jan 2024) This tool is intended to assist you in securing your Oracle database system. You are solely responsible for your system and the effect and results of the execution of this tool (including, without limitation, any damage or data loss). Further, the output generated by this tool may include potentially sensitive system configuration data and information that could be used by a skilled attacker to penetrate your system. You are solely responsible for ensuring that the output of this tool, including any generated reports, is handled in accordance with your company's policies. Connecting to the target Oracle database... SQL*Plus: Release 21.0.0.0.0 - Production on Tue Mar 5 23:00:54 2024 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Last Successful login time: Tue Mar 05 2024 23:00:00 +08:00 Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 Setup complete. SQL queries complete. /bin/cat: /u01/app/oracle/product/21.3.0.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory Warning: Exit status 256 from OS rule: sqlnet.ora /bin/ls: cannot access /u01/app/oracle/product/21.3.0.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory Warning: Exit status 512 from OS rule: ls_sqlnet.ora /bin/cat: /u01/app/oracle/product/21.3.0.0/dbhome_1/network/admin/listener.ora: No such file or directory Warning: Exit status 256 from OS rule: listener.ora /bin/ls: cannot access /u01/app/oracle/product/21.3.0.0/dbhome_1/network/admin/listener.ora: No such file or directory Warning: Exit status 512 from OS rule: ls_listener.ora Warning: Exit status 256 from OS rule: dbcs_status /bin/cat: /u01/app/oracle/product/21.3.0.0/dbhome_1/network/admin/cman.ora: No such file or directory Warning: Exit status 256 from OS rule: cman.ora /bin/cat: /u01/app/oracle/product/21.3.0.0/dbhome_1/ldap/admin/fips.ora: No such file or directory Warning: Exit status 256 from OS rule: fips1.ora /bin/cat: /fips.ora: No such file or directory Warning: Exit status 256 from OS rule: fips2.ora /bin/ls: cannot access /u01/app/oracle/product/21.3.0.0/dbhome_1/rdbms/log/diag: No such file or directory Warning: Exit status 512 from OS rule: diag_dest_home OS commands complete. Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 DBSAT Collector completed successfully. Calling /u01/app/oracle/product/21.3.0.0/dbhome_1/bin/zip to encrypt hemantpdb_DBSAT_Report.json... Enter password: Verify password: adding: hemantpdb_DBSAT_Report.json (deflated 86%) zip completed successfully. [oracle@node2 DBSAT_Installer]$
I can afford to ignore the network/admin lookups under $ORACLE_HOME as they are not valid. I might go back and check the "diag_dest_home" check (e.g. review "sat_collector.sql")
[oracle@node2 DBSAT_Installer]$ PATH=/u01/app/21.3.0.0/grid/jdk/bin:$PATH;export PATH [oracle@node2 DBSAT_Installer]$ java -version java version "1.8.0_291" Java(TM) SE Runtime Environment (build 1.8.0_291-b09) Java HotSpot(TM) 64-Bit Server VM (build 25.291-b09, mixed mode) [oracle@node2 DBSAT_Installer]$ [oracle@node2 DBSAT_Installer]$ JAVA_HOME=/u01/app/21.3.0.0/grid/jdk;export JAVA_HOME [oracle@node2 DBSAT_Installer]$ ./dbsat report hemantpdb_DBSAT_Report Database Security Assessment Tool version 3.1 (Jan 2024) This tool is intended to assist you in securing your Oracle database system. You are solely responsible for your system and the effect and results of the execution of this tool (including, without limitation, any damage or data loss). Further, the output generated by this tool may include potentially sensitive system configuration data and information that could be used by a skilled attacker to penetrate your system. You are solely responsible for ensuring that the output of this tool, including any generated reports, is handled in accordance with your company's policies. DBSAT Reporter ran successfully. Calling /usr/bin/zip to encrypt the generated reports... Enter password: Verify password: zip warning: hemantpdb_DBSAT_Report_report.zip not found or empty adding: hemantpdb_DBSAT_Report_report.txt (deflated 76%) adding: hemantpdb_DBSAT_Report_report.html (deflated 83%) adding: hemantpdb_DBSAT_Report_report.xlsx (deflated 2%) adding: hemantpdb_DBSAT_Report_report.json (deflated 81%) zip completed successfully. [oracle@node2 DBSAT_Installer]$ [oracle@node2 DBSAT_Installer]$ unzip -l hemantpdb_DBSAT_Report_report.zip Archive: hemantpdb_DBSAT_Report_report.zip Length Date Time Name --------- ---------- ----- ---- 161417 03-05-2024 23:09 hemantpdb_DBSAT_Report_report.txt 261378 03-05-2024 23:09 hemantpdb_DBSAT_Report_report.html 36732 03-05-2024 23:09 hemantpdb_DBSAT_Report_report.xlsx 197620 03-05-2024 23:09 hemantpdb_DBSAT_Report_report.json --------- ------- 657147 4 files [oracle@node2 DBSAT_Installer]$ [oracle@node2 DBSAT_Installer]$ unzip hemantpdb_DBSAT_Report_report.zip hemantpdb_DBSAT_Report_report.txt Archive: hemantpdb_DBSAT_Report_report.zip [hemantpdb_DBSAT_Report_report.zip] hemantpdb_DBSAT_Report_report.txt password: inflating: hemantpdb_DBSAT_Report_report.txt [oracle@node2 DBSAT_Installer]$ [oracle@node2 DBSAT_Installer]$ more hemantpdb_DBSAT_Report_report.txt ### Oracle Database Security Assessment - Highly Sensitive ### * Assessment Date & Time * Date of Data Collection Date of Report Reporter Version ---------------------------------- ---------------------------------- --------------------- Tue Mar 05 2024 23:00:54 UTC+08:00 Tue Mar 05 2024 23:09:51 UTC+08:00 3.1 (Jan 2024) - b73a * Database Identity * Name Container (Type:ID) Platform Database Role Log Mode Created -------- ------------------- ---------------- ------------- ------------ ---------------------------------- DB21CRAC HEMANTPDB (PDB:3) Linux x86 64-bit PRIMARY NOARCHIVELOG Fri Jan 19 2024 15:12:46 UTC+08:00 ### Summary ### Section Pass Evaluate Advisory Low Risk Medium Risk High Risk Total Findings --------------------------- ---- -------- -------- -------- ----------- --------- -------------- Basic Information 0 0 0 0 0 1 1 User Accounts 7 8 1 3 2 1 22 Privileges and Roles 6 23 1 0 0 0 30 Authorization Control 0 3 1 0 0 0 4 Fine-Grained Access Control 0 0 5 0 0 0 5 Auditing 0 2 9 3 0 0 14 Encryption 0 3 1 0 0 0 4 Database Configuration 7 9 0 2 2 0 20 Network Configuration 0 0 0 0 0 1 1 Operating System 4 3 0 1 1 0 9 Total 24 51 18 9 5 3 110 ### Basic Information ### * Database Version * Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0
The TXT format of the report is 161,417 bytes and the HTML format is 261,378 bytes.
* Patch Check * - The Oracle Database should be patched Status: High Risk Summary: Oracle Database version is supported but latest patch is missing. Latest comprehensive patch has not been applied. Details: Latest patch not applied for a supported database version. Remarks: Unsupported commercial and database systems should not be used because fixes to newly identified bugs will not be implemented by the vendor. The lack of support can result in potential vulnerabilities. Systems at unsupported servicing levels or releases will not receive security updates for new vulnerabilities, which leaves them subject to exploitation. When maintenance updates and patches are no longer available, the database software is no longer considered supported and should be upgraded or decommissioned. It is vital to keep the database software up-to-date with security fixes as they are released. Oracle issues comprehensive patches in the form of Release Updates on a regular quarterly schedule. These updates should be applied as soon as they are available. References: Oracle Best Practice CIS Benchmark: Recommendation 1.1 DISA STIG: V-237697, V-237748, V-251802 * Users with Default Passwords * - User accounts should not have default passwords Status: High Risk Summary: Found 1 unlocked user account with default password. Details: Users with default password: HR Remarks: Default passwords for predefined Oracle accounts are well known and provide a trivial means of entry for attackers. Database or account administrators should also change well-known passwords for locked accounts. Having default passwords can lead to unauthorized data manipulation and theft of confidential information. Note that if a script creates the database and the SYS or SYSTEM user password remains unchanged, these users are considered to possess a default password. Your database may be at risk due to the password presence within the script. Change the password to improve security. References: Oracle Best Practice CIS Benchmark: Recommendation 4.1 DISA STIG: V-237698 * Network Encryption * - Check configurations used for Native Network Encryption Status: High Risk Summary: Found unencrypted connections. Clients can connect to the database using unencrypted communication channels. Details: Found 3 connections established over unencrypted channel. Remarks: Network encryption is crucial for protecting the confidentiality and integrity of communication between a database server and its clients. Connections to a database instance must be established using the encrypted channels. References: Oracle Best Practice CIS Benchmark: Recommendation 2.3.1, 2.3.2 DISA STIG: V-219841, V-220263, V-220291, V-237699, V-237700, V-237723
Of course, the Report goes on to detail the 110 "findings".
[oracle@node2 DBSAT_Installer]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Tue Mar 5 23:18:49 2024 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL> alter session set container=HEMANTPDB; Session altered. SQL> drop user dbsat_user; User dropped. SQL> quit Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 [oracle@node2 DBSAT_Installer]$
So, DBSAT does reference CIS and STIG in its report.
Installing and Running Oracle AHF ORACHK on a 12.2 DB Server
The Oracle Autonomous Health Framework is described in Support Document "Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAchk (Doc ID 2550798.1)"
In a recent video I have demonstrated running 24.1 orachk (with "-b" for "Best Practices Check) against a 21.3 RAC Cluster.
Here I demonstrate the installation and execution against a 12.2 non-RAC database.
When you download the 24.1 release of AHF (AHF-LINUX_v24.1.0.zip, approximately 410MB), you have to unzip it and then run ahf_setup. It is preferable to use the default location /opt/oracle.ahf (and precreate a "data" subfolder if it doesn't exist).
If your first attempt at installation returns an error :
[ERROR] : AHF-00074: Required Perl Modules not found : Data::Dumper
you can check the perl version and download and install this module (Note : In the listings below "AHF_Installer is the location where I have extracted the installation zip file).
[root@vbgeneric AHF_Installer]# /bin/perl -v This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi (with 34 registered patches, see perl -V for more detail) Copyright 1987-2012, Larry Wall Perl may be copied only under the terms of either the Artistic License or the GNU General Public License, which may be found in the Perl 5 source kit. Complete documentation for Perl, including FAQ lists, should be found on this system using "man perl" or "perldoc perl". If you have access to the Internet, point your browser at http://www.perl.org/, the Perl Home Page. [root@vbgeneric AHF_Installer]# yum install perl-Data-Dumper Loaded plugins: langpacks, ulninfo ol7_UEKR4 | 3.0 kB 00:00:00 ol7_latest | 3.6 kB 00:00:00 (1/5): ol7_latest/x86_64/group_gz | 136 kB 00:00:00 (2/5): ol7_UEKR4/x86_64/updateinfo | 130 kB 00:00:00 (3/5): ol7_latest/x86_64/updateinfo | 3.6 MB 00:00:00 (4/5): ol7_latest/x86_64/primary_db | 50 MB 00:00:02 (5/5): ol7_UEKR4/x86_64/primary_db | 37 MB 00:00:04 Resolving Dependencies --> Running transaction check ---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved =========================================================================================== Package Arch Version Repository Size =========================================================================================== Installing: perl-Data-Dumper x86_64 2.145-3.el7 ol7_latest 47 k Transaction Summary =========================================================================================== Install 1 Package Total download size: 47 k Installed size: 97 k Is this ok [y/d/N]: y Downloading packages: perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Warning: RPMDB altered outside of yum. Installing : perl-Data-Dumper-2.145-3.el7.x86_64 1/1 Verifying : perl-Data-Dumper-2.145-3.el7.x86_64 1/1 Installed: perl-Data-Dumper.x86_64 0:2.145-3.el7 Complete! [root@vbgeneric AHF_Installer]#
Then resume the installation (precreate the "data" folder if it doesn't exist)
[root@vbgeneric AHF_Installer]# mkdir /opt/oracle.ahf/data [root@vbgeneric AHF_Installer]# ./ahf_setup AHF Installer for Platform Linux Architecture x86_64 AHF Installation Log : /tmp/ahf_install_241000_6588_2024_02_27-13_48_51.log Starting Autonomous Health Framework (AHF) Installation AHF Version: 24.1.0 Build Date: 202402051317 Default AHF Location : /opt/oracle.ahf Do you want to install AHF at [/opt/oracle.ahf] ? [Y]|N : Y AHF Location : /opt/oracle.ahf AHF Data Directory stores diagnostic collections and metadata. AHF Data Directory requires at least 5GB (Recommended 10GB) of free space. Please Enter AHF Data Directory : /opt/oracle.ahf/data AHF Data Directory : /opt/oracle.ahf/data Do you want to add AHF Notification Email IDs ? [Y]|N : N Extracting AHF to /opt/oracle.ahf Setting up AHF CLI and SDK Configuring TFA Services Discovering Nodes and Oracle Resources Successfully generated certificates. Starting TFA Services Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service. Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service. .-------------------------------------------------------------------------------. | Host | Status of TFA | PID | Port | Version | Build ID | +-----------+---------------+------+-------+------------+-----------------------+ | vbgeneric | RUNNING | 8540 | 39049 | 24.1.0.0.0 | 240100020240205131724 | '-----------+---------------+------+-------+------------+-----------------------' Running TFA Inventory... Adding default users to TFA Access list... .-------------------------------------------------------. | Summary of AHF Configuration | +-----------------+-------------------------------------+ | Parameter | Value | +-----------------+-------------------------------------+ | AHF Location | /opt/oracle.ahf | | TFA Location | /opt/oracle.ahf/tfa | | Orachk Location | /opt/oracle.ahf/orachk | | Data Directory | /opt/oracle.ahf/data | | Repository | /opt/oracle.ahf/data/repository | | Diag Directory | /opt/oracle.ahf/data/vbgeneric/diag | '-----------------+-------------------------------------' Starting ORAchk Scheduler from AHF AHF binaries are available in /opt/oracle.ahf/bin AHF is successfully Installed Do you want AHF to store your My Oracle Support Credentials for Automatic Upload ? Y|[N] : N Moving /tmp/ahf_install_241000_6588_2024_02_27-13_48_51.log to /opt/oracle.ahf/data/vbgeneric/diag/ahf/ [root@vbgeneric AHF_Installer]#
orachk can then be executed. This execution is to check against "Best Practices" :
[root@vbgeneric AHF_Installer]# orachk -b List of running databases 1. orcl12c 2. None of above Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1]. 1 . . . . Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS . . . . . . . . . . . . . . . ------------------------------------------------------------------------------------------------------- Oracle Stack Status ------------------------------------------------------------------------------------------------------- Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name ------------------------------------------------------------------------------------------------------- vbgeneric No No Yes No No Yes orcl12c ------------------------------------------------------------------------------------------------------- Copying plug-ins . . . . . . . . *** Checking Best Practice Recommendations ( Pass / Warning / Fail ) *** . ============================================================ Node name - vbgeneric ============================================================ . . . . . . Collecting - Database Parameters for orcl12c database Collecting - Database Undocumented Parameters for orcl12c database Collecting - List of active logon and logoff triggers for orcl12c database Collecting - CPU Information Collecting - Disk I/O Scheduler on Linux Collecting - DiskMount Information Collecting - Kernel parameters Collecting - Maximum number of semaphore sets on system Collecting - Maximum number of semaphores on system Collecting - Maximum number of semaphores per semaphore set Collecting - Memory Information Collecting - OS Packages Collecting - Operating system release information and kernel version Collecting - Patches for RDBMS Home Collecting - Patches xml for RDBMS Home Collecting - RDBMS patch inventory Collecting - Table of file system defaults Collecting - number of semaphore operations per semop system call Collecting - Database Server Infrastructure Software and Configuration Collecting - Disk Information Collecting - Root user limits Collecting - Verify ORAchk scheduler configuration Collecting - Verify TCP Selective Acknowledgement is enabled Collecting - Verify no database server kernel out of memory errors Collecting - Verify the vm.min_free_kbytes configuration Data collections completed. Checking best practices on vbgeneric. ------------------------------------------------------------ INFO => Traditional auditing is enabled in database for orcl12c WARNING => Linux swap configuration does not meet recommendation WARNING => Hidden database initialization parameters should not be set per best practice recommendations for orcl12c FAIL => loopback interface MTU value needs to be set to 16436 INFO => Most recent ADR incidents for /u01/app/oracle/product/12.2/db_1 FAIL => Verify Database Memory Allocation INFO => Oracle GoldenGate failure prevention best practices FAIL => The vm.min_free_kbytes configuration is not set as recommended INFO => user_dump_dest has trace files older than 30 days for orcl12c INFO => At some times checkpoints are not being completed for orcl12c WARNING => One or more redo log groups are not multiplexed for orcl12c WARNING => Primary database is not protected with Data Guard (standby database) for real-time data protection and availability for orcl12c INFO => Important Storage Minimum Requirements for Grid & Database Homes CRITICAL => Operating system hugepages count does not satisfy total SGA requirements FAIL => Table AUD$[FGA_LOG$] should use Automatic Segment Space Management for orcl12c FAIL => Database parameter DB_LOST_WRITE_PROTECT is not set to recommended value on orcl12c instance INFO => umask for RDBMS owner is not set to 0022 FAIL => Database parameter DB_BLOCK_CHECKING on primary is not set to the recommended value. for orcl12c INFO => Operational Best Practices INFO => Database Consolidation Best Practices INFO => Computer failure prevention best practices INFO => Data corruption prevention best practices INFO => Logical corruption prevention best practices INFO => Database/Cluster/Site failure prevention best practices INFO => Client failover operational best practices WARNING => Oracle patch 30712670 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1 WARNING => Oracle patch 29867728 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1 WARNING => Oracle patch 31142749 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1 WARNING => Oracle patch 26749785 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1 WARNING => Oracle patch 29302565 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1 WARNING => Oracle patch 29259068 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1 WARNING => Oracle clusterware is not being used WARNING => RAC Application Cluster is not being used for database high availability on orcl12c instance WARNING => DISK_ASYNCH_IO is NOT set to recommended value for orcl12c WARNING => Flashback on PRIMARY is not configured for orcl12c INFO => Database failure prevention best practices WARNING => fast_start_mttr_target has NOT been changed from default on orcl12c instance FAIL => Active Data Guard is not configured for orcl12c WARNING => Perl Patch 31858212 is not found in 12.2.0.1 RDBMS_HOME. /u01/app/oracle/product/12.2/db_1 WARNING => Oracle patch 31602782 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1 WARNING => Oracle patch 33121934 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1 WARNING => Oracle patch 31211220 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1 INFO => Software maintenance best practices INFO => Oracle recovery manager(rman) best practices INFO => Database feature usage statistics for orcl12c WARNING => Consider investigating changes to the schema objects such as DDLs or new object creation for orcl12c WARNING => Consider investigating the frequency of SGA resize operations and take corrective action for orcl12c ------------------------------------------------------------ UPLOAD [if required] - /opt/oracle.ahf/data/vbgeneric/orachk/user_root/output/orachk_vbgeneric_orcl12c_022724_140315.zip [root@vbgeneric AHF_Installer]#
Thus, you can actually run the 24.1 orachk against even a 12.2 non-RAC (single instance) database.
A PDB as a Cluster Resource in RAC
In my previous post, I have demonstrated how a new service can be created for a Pluggable Database (PDB) in Oracle RAC.
Custom Services are configured as Resources. Thus, in that post, I have demonstrated the custom service "newservice" for the PDB "hemantpdb" being configured in the Cluster.
However, PDBs can also be configured and managed as resources. (see the reference in the Oracle Database Features documentation).
This is the current listing of resources in my 2-node 21c RAC Cluster :
[grid@node1 ~]$ crsctl status resource -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.chad ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.net1.network ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.ons ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.DATA.dg(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE node1 STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE node1 STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE node2 STABLE ora.RECO.dg(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.asm(ora.asmgroup) 1 ONLINE ONLINE node1 Started,STABLE 2 ONLINE ONLINE node2 Started,STABLE ora.asmnet1.asmnetwork(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.cdp1.cdp 1 ONLINE ONLINE node1 STABLE ora.cdp2.cdp 1 ONLINE ONLINE node1 STABLE ora.cdp3.cdp 1 ONLINE ONLINE node2 STABLE ora.cvu 1 ONLINE ONLINE node1 STABLE ora.db21crac.db 1 ONLINE ONLINE node2 Open,HOME=/u01/app/o racle/product/21.3.0 .0/dbhome_1,STABLE 2 ONLINE ONLINE node1 Open,HOME=/u01/app/o racle/product/21.3.0 .0/dbhome_1,STABLE ora.db21crac.hemantpdb.pdb 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.db21crac.newservice.svc 1 ONLINE ONLINE node1 STABLE ora.node1.vip 1 ONLINE ONLINE node1 STABLE ora.node2.vip 1 ONLINE ONLINE node2 STABLE ora.qosmserver 1 ONLINE ONLINE node1 STABLE ora.scan1.vip 1 ONLINE ONLINE node1 STABLE ora.scan2.vip 1 ONLINE ONLINE node1 STABLE ora.scan3.vip 1 ONLINE ONLINE node2 STABLE -------------------------------------------------------------------------------- [grid@node1 ~]$
Above, we can see
[oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 23 17:04:31 2024 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL> create pluggable database pdb2 admin user pdb2_admin identified by pdb2_admin; Pluggable database created. SQL> SQL> col name format a30 SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY HEMANTPDB READ WRITE PDB2 MOUNTED SQL> [grid@node1 ~]$ crsctl status resource -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.chad ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.net1.network ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.ons ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.DATA.dg(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE node1 STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE node1 STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE node2 STABLE ora.RECO.dg(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.asm(ora.asmgroup) 1 ONLINE ONLINE node1 Started,STABLE 2 ONLINE ONLINE node2 Started,STABLE ora.asmnet1.asmnetwork(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.cdp1.cdp 1 ONLINE ONLINE node1 STABLE ora.cdp2.cdp 1 ONLINE ONLINE node1 STABLE ora.cdp3.cdp 1 ONLINE ONLINE node2 STABLE ora.cvu 1 ONLINE ONLINE node1 STABLE ora.db21crac.db 1 ONLINE ONLINE node2 Open,HOME=/u01/app/o racle/product/21.3.0 .0/dbhome_1,STABLE 2 ONLINE ONLINE node1 Open,HOME=/u01/app/o racle/product/21.3.0 .0/dbhome_1,STABLE ora.db21crac.hemantpdb.pdb 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.db21crac.newservice.svc 1 ONLINE ONLINE node1 STABLE ora.node1.vip 1 ONLINE ONLINE node1 STABLE ora.node2.vip 1 ONLINE ONLINE node2 STABLE ora.qosmserver 1 ONLINE ONLINE node1 STABLE ora.scan1.vip 1 ONLINE ONLINE node1 STABLE ora.scan2.vip 1 ONLINE ONLINE node1 STABLE ora.scan3.vip 1 ONLINE ONLINE node2 STABLE -------------------------------------------------------------------------------- [grid@node1 ~]$
The new PDB, "pdb2" has been created but not opened yet (it is still only in the MOUNT state). However, if I try to manually add it, I get an error message that CARDINALITY wasn't specified for the first PDB that had been manually created from the SQL command line but not registered via srvctl. However, I *can* add the new PDB without having to specify CARDINALITY and then verify the status in the Cluster :
[oracle@node1 ~]$ srvctl add pdb -db DB21CRAC -pdb PDB2 -cardinality ALL -startoption open PRCZ-4036 : failed to create pluggable database PDB2 with '-cardinality' option because this option was not specified for the previously created pluggable databases for database DB21CRAC [oracle@node1 ~]$ srvctl config pdb -db DB21CRAC Pluggable database name: HEMANTPDB Application Root PDB: Cardinality: %CRS_SERVER_POOL_SIZE% Maximum CPU count (whole CPUs): 0 Minimum CPU count unit (1/100 CPU count): 0 Management policy: MANUAL Rank value: 0 Start Option: open Stop Option: immediate [oracle@node1 ~]$ srvctl add pdb -db DB21CRAC -pdb PDB2 -startoption open [oracle@node1 ~]$ srvctl config pdb -db DB21CRAC Pluggable database name: HEMANTPDB Application Root PDB: Cardinality: %CRS_SERVER_POOL_SIZE% Maximum CPU count (whole CPUs): 0 Minimum CPU count unit (1/100 CPU count): 0 Management policy: MANUAL Rank value: 0 Start Option: open Stop Option: immediate Pluggable database name: PDB2 Application Root PDB: Cardinality: %CRS_SERVER_POOL_SIZE% Maximum CPU count (whole CPUs): 0 Minimum CPU count unit (1/100 CPU count): 0 Management policy: AUTOMATIC Rank value: 0 Start Option: open Stop Option: immediate [grid@node1 ~]$ crsctl status resource -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.chad ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.net1.network ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.ons ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.DATA.dg(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE node1 STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE node1 STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE node2 STABLE ora.RECO.dg(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.asm(ora.asmgroup) 1 ONLINE ONLINE node1 Started,STABLE 2 ONLINE ONLINE node2 Started,STABLE ora.asmnet1.asmnetwork(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.cdp1.cdp 1 ONLINE ONLINE node1 STABLE ora.cdp2.cdp 1 ONLINE ONLINE node1 STABLE ora.cdp3.cdp 1 ONLINE ONLINE node2 STABLE ora.cvu 1 ONLINE ONLINE node1 STABLE ora.db21crac.db 1 ONLINE ONLINE node2 Open,HOME=/u01/app/o racle/product/21.3.0 .0/dbhome_1,STABLE 2 ONLINE ONLINE node1 Open,HOME=/u01/app/o racle/product/21.3.0 .0/dbhome_1,STABLE ora.db21crac.hemantpdb.pdb 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.db21crac.newservice.svc 1 ONLINE ONLINE node1 STABLE ora.db21crac.pdb2.pdb 1 OFFLINE OFFLINE STABLE 2 OFFLINE OFFLINE STABLE ora.node1.vip 1 ONLINE ONLINE node1 STABLE ora.node2.vip 1 ONLINE ONLINE node2 STABLE ora.qosmserver 1 ONLINE ONLINE node1 STABLE ora.scan1.vip 1 ONLINE ONLINE node1 STABLE ora.scan2.vip 1 ONLINE ONLINE node1 STABLE ora.scan3.vip 1 ONLINE ONLINE node2 STABLE -------------------------------------------------------------------------------- [grid@node1 ~]$
If I then OPEN the new PDB on both instances, I can query the status again :
[oracle@node1 ~]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 23 17:19:20 2024 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL> alter pluggable database pdb2 open; Pluggable database altered. SQL> [oracle@node2 ~]$ sqlplus / as sysdba SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 23 17:19:36 2024 Version 21.3.0.0.0 Copyright (c) 1982, 2021, Oracle. All rights reserved. Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 SQL> alter pluggable database pdb2 open; Pluggable database altered. SQL> [grid@node1 ~]$ crsctl status resource ora.db21crac.pdb2.pdb NAME=ora.db21crac.pdb2.pdb TYPE=ora.pdb.type TARGET=ONLINE , ONLINE STATE=ONLINE on node1, ONLINE on node2 [grid@node1 ~]$ crsctl status resource -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.chad ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.net1.network ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.ons ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.DATA.dg(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE node1 STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE node1 STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE node2 STABLE ora.RECO.dg(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.asm(ora.asmgroup) 1 ONLINE ONLINE node1 Started,STABLE 2 ONLINE ONLINE node2 Started,STABLE ora.asmnet1.asmnetwork(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.cdp1.cdp 1 ONLINE ONLINE node1 STABLE ora.cdp2.cdp 1 ONLINE ONLINE node1 STABLE ora.cdp3.cdp 1 ONLINE ONLINE node2 STABLE ora.cvu 1 ONLINE ONLINE node1 STABLE ora.db21crac.db 1 ONLINE ONLINE node2 Open,HOME=/u01/app/o racle/product/21.3.0 .0/dbhome_1,STABLE 2 ONLINE ONLINE node1 Open,HOME=/u01/app/o racle/product/21.3.0 .0/dbhome_1,STABLE ora.db21crac.hemantpdb.pdb 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.db21crac.newservice.svc 1 ONLINE ONLINE node1 STABLE ora.db21crac.pdb2.pdb 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.node1.vip 1 ONLINE ONLINE node1 STABLE ora.node2.vip 1 ONLINE ONLINE node2 STABLE ora.qosmserver 1 ONLINE ONLINE node1 STABLE ora.scan1.vip 1 ONLINE ONLINE node1 STABLE ora.scan2.vip 1 ONLINE ONLINE node1 STABLE ora.scan3.vip 1 ONLINE ONLINE node2 STABLE -------------------------------------------------------------------------------- [grid@node1 ~]$
I can now add a custom service "pdb2service" for pdb2 to use DB21CRAC2 (instance 2, instead of instance 1) as preferred and with SESSION failover (and compare it with "newservice" for hemantpdb that I had added in the previous blogpost) :
[oracle@node1 ~]$ srvctl add service -db DB21CRAC -service pdb2service -preferred DB21CRAC2 -available DB21CRAC1 \ > -tafpolicy BASIC -failovertype SESSION -pdb PDB2 [oracle@node1 ~]$ srvctl status service -db DB21CRAC -service pdb2service Service pdb2service is not running. [oracle@node1 ~]$ srvctl config service -db DB21CRAC -service pdb2service Service name: pdb2service Server pool: Cardinality: 1 Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Reset State: NONE Failover type: SESSION Failover method: Failover retries: Failover delay: Failover restore: NONE Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: BASIC Edition: Pluggable database name: PDB2 Hub service: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Failback : no Replay Initiation Time: 300 seconds Drain timeout: Stop option: Session State Consistency: DYNAMIC GSM Flags: 0 Service is enabled Preferred instances: DB21CRAC2 Available instances: DB21CRAC1 CSS critical: no [oracle@node1 ~]$ [oracle@node1 ~]$ srvctl config service -db DB21CRAC -service newservice Service name: newservice Server pool: Cardinality: 1 Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Reset State: NONE Failover type: SELECT Failover method: Failover retries: Failover delay: Failover restore: NONE Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: BASIC Edition: Pluggable database name: HEMANTPDB Hub service: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Failback : no Replay Initiation Time: 300 seconds Drain timeout: Stop option: Session State Consistency: DYNAMIC GSM Flags: 0 Service is enabled Preferred instances: DB21CRAC1 Available instances: DB21CRAC2 CSS critical: no Service uses Java: false [oracle@node1 ~]$ [oracle@node1 ~]$ srvctl start service -db DB21CRAC -service pdb2service [oracle@node1 ~]$ srvctl config service -db DB21CRAC -service pdb2service Service name: pdb2service Server pool: Cardinality: 1 Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Reset State: NONE Failover type: SESSION Failover method: Failover retries: Failover delay: Failover restore: NONE Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: BASIC Edition: Pluggable database name: PDB2 Hub service: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Failback : no Replay Initiation Time: 300 seconds Drain timeout: Stop option: Session State Consistency: DYNAMIC GSM Flags: 0 Service is enabled Preferred instances: DB21CRAC2 Available instances: DB21CRAC1 CSS critical: no Service uses Java: false [oracle@node1 ~]$ [oracle@node1 ~]$ srvctl status service -db DB21CRAC -service pdb2service Service pdb2service is running on instance(s) DB21CRAC2 [oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice Service newservice is running on instance(s) DB21CRAC1 [oracle@node1 ~]$ [grid@node1 ~]$ crsctl status resource -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.LISTENER.lsnr ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.chad ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.net1.network ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE ora.ons ONLINE ONLINE node1 STABLE ONLINE ONLINE node2 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.DATA.dg(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE node1 STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE node1 STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE node2 STABLE ora.RECO.dg(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.asm(ora.asmgroup) 1 ONLINE ONLINE node1 Started,STABLE 2 ONLINE ONLINE node2 Started,STABLE ora.asmnet1.asmnetwork(ora.asmgroup) 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.cdp1.cdp 1 ONLINE ONLINE node1 STABLE ora.cdp2.cdp 1 ONLINE ONLINE node1 STABLE ora.cdp3.cdp 1 ONLINE ONLINE node2 STABLE ora.cvu 1 ONLINE ONLINE node1 STABLE ora.db21crac.db 1 ONLINE ONLINE node2 Open,HOME=/u01/app/o racle/product/21.3.0 .0/dbhome_1,STABLE 2 ONLINE ONLINE node1 Open,HOME=/u01/app/o racle/product/21.3.0 .0/dbhome_1,STABLE ora.db21crac.hemantpdb.pdb 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.db21crac.newservice.svc 1 ONLINE ONLINE node1 STABLE ora.db21crac.pdb2.pdb 1 ONLINE ONLINE node1 STABLE 2 ONLINE ONLINE node2 STABLE ora.db21crac.pdb2service.svc 1 ONLINE ONLINE node2 STABLE ora.node1.vip 1 ONLINE ONLINE node1 STABLE ora.node2.vip 1 ONLINE ONLINE node2 STABLE ora.qosmserver 1 ONLINE ONLINE node1 STABLE ora.scan1.vip 1 ONLINE ONLINE node1 STABLE ora.scan2.vip 1 ONLINE ONLINE node1 STABLE ora.scan3.vip 1 ONLINE ONLINE node2 STABLE -------------------------------------------------------------------------------- [grid@node1 ~]$
So, I have two PDBs "hemantpdb" and "pdb2" and their corresponding services ("newservice" and "pdb2service") using alternate database instances as Preferred Instances. "hemantpdb"'s service "newservices" runs on node1 (database instance DB21CRAC1) and "pdb2"'s service "pdb2service" runs on node2 (database instance DB21CRAC2) while both PDBs are actually OPEN on both nodes.
Adding a PDB (and Service) to a RAC database -- 1 - service running on only 1 Instance
[oracle@node1 ~]$ srvctl status database -db DB21CRAC Instance DB21CRAC1 is running on node node1 Instance DB21CRAC2 is running on node node2 [oracle@node1 ~]$ [oracle@node1 ~]$ srvctl status service -d DB21CRAC -s hemantpdb PRKO-2017 : Service hemantpdb does not exist for database DB21CRAC. [oracle@node1 ~]$ srvctl status service -db DB21CRAC -service hemantpdb -pdb HEMANTPDB PRKO-2017 : Service hemantpdb does not exist for database DB21CRAC. [oracle@node1 ~]$ [grid@node1 ~]$ lsnrctl status LISTENER_SCAN1 LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 13-FEB-2024 15:06:11 Copyright (c) 1991, 2021, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))) STATUS of the LISTENER ------------------------ Alias LISTENER_SCAN1 Version TNSLSNR for Linux: Version 21.0.0.0.0 - Production Start Date 13-FEB-2024 14:51:13 Uptime 0 days 0 hr. 14 min. 57 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/21.3.0.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/node1/listener_scan1/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.115)(PORT=1521))) Services Summary... Service "0f488ad896262f80e0636f38a8c0fc18" has 2 instance(s). Instance "DB21CRAC1", status READY, has 1 handler(s) for this service... Instance "DB21CRAC2", status READY, has 1 handler(s) for this service... Service "DB21CRAC" has 2 instance(s). Instance "DB21CRAC1", status READY, has 1 handler(s) for this service... Instance "DB21CRAC2", status READY, has 1 handler(s) for this service... Service "DB21CRACXDB" has 2 instance(s). Instance "DB21CRAC1", status READY, has 1 handler(s) for this service... Instance "DB21CRAC2", status READY, has 1 handler(s) for this service... Service "hemantpdb" has 2 instance(s). Instance "DB21CRAC1", status READY, has 1 handler(s) for this service... Instance "DB21CRAC2", status READY, has 1 handler(s) for this service... The command completed successfully [grid@node1 ~]$ [oracle@node1 ~]$SQL> select inst_id, name, network_name from gv$services order by 1; INST_ID NAME NETWORK_NAME ---------- ---------------- ---------------- 1 hemantpdb hemantpdb 1 DB21CRAC DB21CRAC 1 SYS$BACKGROUND 1 DB21CRACXDB DB21CRACXDB 1 SYS$USERS 2 hemantpdb hemantpdb 2 DB21CRAC DB21CRAC 2 SYS$BACKGROUND 2 DB21CRACXDB DB21CRACXDB 2 SYS$USERS 10 rows selected. SQL> SQL> select inst_id, con_id, name, open_mode from gv$pdbs order by 1,2; INST_ID CON_ID NAME OPEN_MODE ---------- ---------- ---------------- ---------- 1 2 PDB$SEED READ ONLY 1 3 HEMANTPDB READ WRITE 2 2 PDB$SEED READ ONLY 2 3 HEMANTPDB READ WRITE SQL>
Which means that I have created a custom PDB called "HEMANTPDB" and there is a default service called "hemantpdb" on each instance. However, this services is NOT listed when I check via srvctl.
[oracle@node1 ~]$ srvctl add service -db DB21CRAC -service newservice -preferred DB21CRAC1 -available DB21CRAC2 -tafpolicy BASIC -failovertype SELECT -pdb HEMANTPDB [oracle@node1 ~]$ [oracle@node1 ~]$ srvctl config service -db DB21CRAC -service newservice Service name: newservice Server pool: Cardinality: 1 Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Global: false Commit Outcome: false Reset State: NONE Failover type: SELECT Failover method: Failover retries: Failover delay: Failover restore: NONE Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: BASIC Edition: Pluggable database name: HEMANTPDB Hub service: Maximum lag time: ANY SQL Translation Profile: Retention: 86400 seconds Failback : no Replay Initiation Time: 300 seconds Drain timeout: Stop option: Session State Consistency: DYNAMIC GSM Flags: 0 Service is enabled Preferred instances: DB21CRAC1 Available instances: DB21CRAC2 CSS critical: no [oracle@node1 ~]$ [oracle@node1 ~]$ srvctl start service -db DB21CRAC -service newservice [oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice Service newservice is running on instance(s) DB21CRAC1 [oracle@node1 ~]$
[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice Service newservice is running on instance(s) DB21CRAC1 [oracle@node1 ~]$ ps -ef |grep smon oracle 2951 1 0 16:05 ? 00:00:00 ora_smon_DB21CRAC1 root 3521 1 1 14:50 ? 00:00:48 /u01/app/21.3.0.0/grid/bin/osysmond.bin grid 4068 1 0 14:50 ? 00:00:00 asm_smon_+ASM1 oracle 4146 25526 0 16:05 pts/0 00:00:00 grep --color=auto smon [oracle@node1 ~]$ kill -9 2951 [oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice Service newservice is running on instance(s) DB21CRAC2 [oracle@node1 ~]$
In this case, connections using this service name ("newservice") will connect (failover) to the 2nd database instance running on node2 of the Cluster.
Playlist of Oracle RAC Videos
Video demonstration of RAC Client Query Session and Query Failover
I've put up a short video that shows a client session connected to a RAC SCAN IP address failing over to the surviving RAC instance when the database node goes down
Video demonstration of RAC Client Query Session and Query Failover
My Posts on Refreshable Clone PDBs
Refreshable Clone PDBs have been available since Oracle 12.2
These are 4 blog posts I had made between Nov-21 and Jan-22 :
1. Refreshable Clone PDB -- 1 Manual Refresh (30-Nov-21)
2. Refreshable Clone PDB -- 2 Datafile Names (04-Dec-21)
3. Refreshable Clone PDB -- 3 The ArchiveLog and ArchParLog files (05-Dec-21)
4. Refreshable Clone PDB -- 4 Converting it to a Read Write PDB (23-Jan-22)
Additionally, here is a recent write-up Refreshable PDB Clones in Data Guard Environments On-premises and on Oracle Cloud by Sinan Petrus Toma (@SinanPetrus)
Do DataFiles get updated when in BACKUP mode ?
The Oracle feature allowing the DBA to put a database in "BACKUP" mode is useful for environments where non-RMAN methods / storage snapshots etc are used to make database backups.
However, there seems to be misconception that datafiles are not updated when the database is in BACKUP mode.
Here is a quick demonstration to disprove it. I first place the database in BACKUP mode and then, from other sessions, start-off transactions and checkpoints and switch logfiles. I then check if the datafile headers are updated and whether the datafiles themselves are updated.
17:55:28 SQL> select current_scn from v$database; CURRENT_SCN ----------- 1240322 17:55:34 SQL> alter system checkpoint; System altered. 17:55:42 SQL> @get_dbf_chk_scns FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ---------- -------------------------------------------------------- ------------------ ------------------ 1 /opt/oracle/oradata/HEMANT/system.dbf 1240326 15-OCT-23 17:55:42 2 /opt/oracle/oradata/HEMANT/sysaux.dbf 1240326 15-OCT-23 17:55:42 3 /opt/oracle/oradata/HEMANT/undotbs.dbf 1240326 15-OCT-23 17:55:42 4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf 1240326 15-OCT-23 17:55:42 5 /opt/oracle/oradata/HEMANT/indx01.dbf 1240326 15-OCT-23 17:55:42 6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf 1240326 15-OCT-23 17:55:42 10 /opt/oracle/oradata/HEMANT/indx02.dbf 1240326 15-OCT-23 17:55:42 11 /opt/oracle/oradata/HEMANT/indx03.dbf 1240326 15-OCT-23 17:55:42 8 rows selected. 17:56:01 SQL> alter database begin backup; Database altered. 17:56:10 SQL> @get_dbf_chk_scns FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ---------- -------------------------------------------------------- ------------------ ------------------ 1 /opt/oracle/oradata/HEMANT/system.dbf 1240339 15-OCT-23 17:56:10 2 /opt/oracle/oradata/HEMANT/sysaux.dbf 1240339 15-OCT-23 17:56:10 3 /opt/oracle/oradata/HEMANT/undotbs.dbf 1240339 15-OCT-23 17:56:10 4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf 1240339 15-OCT-23 17:56:10 5 /opt/oracle/oradata/HEMANT/indx01.dbf 1240339 15-OCT-23 17:56:10 6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf 1240339 15-OCT-23 17:56:10 10 /opt/oracle/oradata/HEMANT/indx02.dbf 1240339 15-OCT-23 17:56:10 11 /opt/oracle/oradata/HEMANT/indx03.dbf 1240339 15-OCT-23 17:56:10 8 rows selected. 17:56:18 SQL>
The BEGIN BACKUP causes a Checkpoint. Now I start off transactions and log file switches and checkpoints from other sessions and monitor the state here.
17:59:15 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/archivelog/HEMANT Oldest online log sequence 118 Next log sequence to archive 120 Current log sequence 120 17:59:18 SQL> @get_dbf_chk_scns FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ---------- -------------------------------------------------------- ------------------ ------------------ 1 /opt/oracle/oradata/HEMANT/system.dbf 1240339 15-OCT-23 17:56:10 2 /opt/oracle/oradata/HEMANT/sysaux.dbf 1240339 15-OCT-23 17:56:10 3 /opt/oracle/oradata/HEMANT/undotbs.dbf 1240339 15-OCT-23 17:56:10 4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf 1240339 15-OCT-23 17:56:10 5 /opt/oracle/oradata/HEMANT/indx01.dbf 1240339 15-OCT-23 17:56:10 6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf 1240339 15-OCT-23 17:56:10 10 /opt/oracle/oradata/HEMANT/indx02.dbf 1240339 15-OCT-23 17:56:10 11 /opt/oracle/oradata/HEMANT/indx03.dbf 1240339 15-OCT-23 17:56:10 8 rows selected. 17:59:25 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf -rw-r-----. 1 oracle oinstall 5251072 Oct 15 17:56 /opt/oracle/oradata/HEMANT/HR_DATA.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx01.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx02.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx03.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 17:52 /opt/oracle/oradata/HEMANT/redo01.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 17:58 /opt/oracle/oradata/HEMANT/redo02.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 17:58 /opt/oracle/oradata/HEMANT/redo03.dbf -rw-r-----. 1 oracle oinstall 419438592 Oct 15 17:56 /opt/oracle/oradata/HEMANT/sysaux.dbf -rw-r-----. 1 oracle oinstall 566239232 Oct 15 17:56 /opt/oracle/oradata/HEMANT/system.dbf -rw-r-----. 1 oracle oinstall 104865792 Oct 15 17:56 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf -rw-r-----. 1 oracle oinstall 209723392 Oct 15 17:56 /opt/oracle/oradata/HEMANT/undotbs.dbf 17:59:38 SQL> 18:01:04 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/archivelog/HEMANT Oldest online log sequence 119 Next log sequence to archive 121 Current log sequence 121 18:01:17 SQL> @get_dbf_chk_scns FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ---------- -------------------------------------------------------- ------------------ ------------------ 1 /opt/oracle/oradata/HEMANT/system.dbf 1240339 15-OCT-23 17:56:10 2 /opt/oracle/oradata/HEMANT/sysaux.dbf 1240339 15-OCT-23 17:56:10 3 /opt/oracle/oradata/HEMANT/undotbs.dbf 1240339 15-OCT-23 17:56:10 4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf 1240339 15-OCT-23 17:56:10 5 /opt/oracle/oradata/HEMANT/indx01.dbf 1240339 15-OCT-23 17:56:10 6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf 1240339 15-OCT-23 17:56:10 10 /opt/oracle/oradata/HEMANT/indx02.dbf 1240339 15-OCT-23 17:56:10 11 /opt/oracle/oradata/HEMANT/indx03.dbf 1240339 15-OCT-23 17:56:10 8 rows selected. 18:01:25 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf -rw-r-----. 1 oracle oinstall 5251072 Oct 15 17:56 /opt/oracle/oradata/HEMANT/HR_DATA.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx01.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx02.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 17:56 /opt/oracle/oradata/HEMANT/indx03.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:01 /opt/oracle/oradata/HEMANT/redo01.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 17:58 /opt/oracle/oradata/HEMANT/redo02.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:00 /opt/oracle/oradata/HEMANT/redo03.dbf -rw-r-----. 1 oracle oinstall 419438592 Oct 15 17:56 /opt/oracle/oradata/HEMANT/sysaux.dbf -rw-r-----. 1 oracle oinstall 566239232 Oct 15 18:00 /opt/oracle/oradata/HEMANT/system.dbf -rw-r-----. 1 oracle oinstall 104865792 Oct 15 17:56 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf -rw-r-----. 1 oracle oinstall 209723392 Oct 15 18:01 /opt/oracle/oradata/HEMANT/undotbs.dbf 18:01:34 SQL> select current_scn from v$database; CURRENT_SCN ----------- 1240837 18:01:44 SQL> 18:01:44 SQL> !sleep 120 18:04:00 SQL> 18:04:04 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/archivelog/HEMANT Oldest online log sequence 122 Next log sequence to archive 124 Current log sequence 124 18:04:10 SQL> @get_dbf_chk_scns FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ---------- -------------------------------------------------------- ------------------ ------------------ 1 /opt/oracle/oradata/HEMANT/system.dbf 1240339 15-OCT-23 17:56:10 2 /opt/oracle/oradata/HEMANT/sysaux.dbf 1240339 15-OCT-23 17:56:10 3 /opt/oracle/oradata/HEMANT/undotbs.dbf 1240339 15-OCT-23 17:56:10 4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf 1240339 15-OCT-23 17:56:10 5 /opt/oracle/oradata/HEMANT/indx01.dbf 1240339 15-OCT-23 17:56:10 6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf 1240339 15-OCT-23 17:56:10 10 /opt/oracle/oradata/HEMANT/indx02.dbf 1240339 15-OCT-23 17:56:10 11 /opt/oracle/oradata/HEMANT/indx03.dbf 1240339 15-OCT-23 17:56:10 8 rows selected. 18:04:16 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf -rw-r-----. 1 oracle oinstall 5251072 Oct 15 18:03 /opt/oracle/oradata/HEMANT/HR_DATA.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:03 /opt/oracle/oradata/HEMANT/indx01.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:03 /opt/oracle/oradata/HEMANT/indx02.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:03 /opt/oracle/oradata/HEMANT/indx03.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:03 /opt/oracle/oradata/HEMANT/redo01.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:03 /opt/oracle/oradata/HEMANT/redo02.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:03 /opt/oracle/oradata/HEMANT/redo03.dbf -rw-r-----. 1 oracle oinstall 419438592 Oct 15 18:03 /opt/oracle/oradata/HEMANT/sysaux.dbf -rw-r-----. 1 oracle oinstall 566239232 Oct 15 18:03 /opt/oracle/oradata/HEMANT/system.dbf -rw-r-----. 1 oracle oinstall 104865792 Oct 15 18:03 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf -rw-r-----. 1 oracle oinstall 209723392 Oct 15 18:03 /opt/oracle/oradata/HEMANT/undotbs.dbf 18:04:24 SQL> select current_scn from v$database; CURRENT_SCN ----------- 1242436 18:04:34 SQL> 18:05:13 SQL> !sleep 120 18:07:15 SQL> 18:07:17 SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/archivelog/HEMANT Oldest online log sequence 124 Next log sequence to archive 126 Current log sequence 126 18:07:20 SQL> alter system archive log current; System altered. 18:07:30 SQL> @get_dbf_chk_scns FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ---------- -------------------------------------------------------- ------------------ ------------------ 1 /opt/oracle/oradata/HEMANT/system.dbf 1240339 15-OCT-23 17:56:10 2 /opt/oracle/oradata/HEMANT/sysaux.dbf 1240339 15-OCT-23 17:56:10 3 /opt/oracle/oradata/HEMANT/undotbs.dbf 1240339 15-OCT-23 17:56:10 4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf 1240339 15-OCT-23 17:56:10 5 /opt/oracle/oradata/HEMANT/indx01.dbf 1240339 15-OCT-23 17:56:10 6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf 1240339 15-OCT-23 17:56:10 10 /opt/oracle/oradata/HEMANT/indx02.dbf 1240339 15-OCT-23 17:56:10 11 /opt/oracle/oradata/HEMANT/indx03.dbf 1240339 15-OCT-23 17:56:10 8 rows selected. 18:07:36 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf -rw-r-----. 1 oracle oinstall 5251072 Oct 15 18:07 /opt/oracle/oradata/HEMANT/HR_DATA.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:07 /opt/oracle/oradata/HEMANT/indx01.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:07 /opt/oracle/oradata/HEMANT/indx02.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:07 /opt/oracle/oradata/HEMANT/indx03.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:07 /opt/oracle/oradata/HEMANT/redo01.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:06 /opt/oracle/oradata/HEMANT/redo02.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:07 /opt/oracle/oradata/HEMANT/redo03.dbf -rw-r-----. 1 oracle oinstall 419438592 Oct 15 18:07 /opt/oracle/oradata/HEMANT/sysaux.dbf -rw-r-----. 1 oracle oinstall 566239232 Oct 15 18:07 /opt/oracle/oradata/HEMANT/system.dbf -rw-r-----. 1 oracle oinstall 104865792 Oct 15 18:07 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf -rw-r-----. 1 oracle oinstall 209723392 Oct 15 18:07 /opt/oracle/oradata/HEMANT/undotbs.dbf 18:07:43 SQL> select current_scn from v$database; CURRENT_SCN ----------- 1242872 18:07:50 SQL> 18:07:50 SQL> alter system checkpoint; System altered. 18:08:23 SQL> select current_scn from v$database; CURRENT_SCN ----------- 1243044 18:08:29 SQL> @get_dbf_chk_scns FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ---------- -------------------------------------------------------- ------------------ ------------------ 1 /opt/oracle/oradata/HEMANT/system.dbf 1240339 15-OCT-23 17:56:10 2 /opt/oracle/oradata/HEMANT/sysaux.dbf 1240339 15-OCT-23 17:56:10 3 /opt/oracle/oradata/HEMANT/undotbs.dbf 1240339 15-OCT-23 17:56:10 4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf 1240339 15-OCT-23 17:56:10 5 /opt/oracle/oradata/HEMANT/indx01.dbf 1240339 15-OCT-23 17:56:10 6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf 1240339 15-OCT-23 17:56:10 10 /opt/oracle/oradata/HEMANT/indx02.dbf 1240339 15-OCT-23 17:56:10 11 /opt/oracle/oradata/HEMANT/indx03.dbf 1240339 15-OCT-23 17:56:10 8 rows selected. 18:08:35 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf -rw-r-----. 1 oracle oinstall 5251072 Oct 15 18:08 /opt/oracle/oradata/HEMANT/HR_DATA.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:08 /opt/oracle/oradata/HEMANT/indx01.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:08 /opt/oracle/oradata/HEMANT/indx02.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:08 /opt/oracle/oradata/HEMANT/indx03.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:08 /opt/oracle/oradata/HEMANT/redo01.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:08 /opt/oracle/oradata/HEMANT/redo02.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:07 /opt/oracle/oradata/HEMANT/redo03.dbf -rw-r-----. 1 oracle oinstall 419438592 Oct 15 18:08 /opt/oracle/oradata/HEMANT/sysaux.dbf -rw-r-----. 1 oracle oinstall 566239232 Oct 15 18:08 /opt/oracle/oradata/HEMANT/system.dbf -rw-r-----. 1 oracle oinstall 104865792 Oct 15 18:08 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf -rw-r-----. 1 oracle oinstall 209723392 Oct 15 18:08 /opt/oracle/oradata/HEMANT/undotbs.dbf 18:08:41 SQL>
I now, finally, issue an END BACKUP and check the status.
18:09:45 SQL> alter database end backup; Database altered. 18:09:51 SQL> select current_scn from v$database; CURRENT_SCN ----------- 1244942 18:09:58 SQL> @get_dbf_chk_scns FILE# NAME CHECKPOINT_CHANGE# CHECKPOINT_TIME ---------- -------------------------------------------------------- ------------------ ------------------ 1 /opt/oracle/oradata/HEMANT/system.dbf 1243041 15-OCT-23 18:08:23 2 /opt/oracle/oradata/HEMANT/sysaux.dbf 1243041 15-OCT-23 18:08:23 3 /opt/oracle/oradata/HEMANT/undotbs.dbf 1243041 15-OCT-23 18:08:23 4 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf 1243041 15-OCT-23 18:08:23 5 /opt/oracle/oradata/HEMANT/indx01.dbf 1243041 15-OCT-23 18:08:23 6 /opt/oracle/oradata/HEMANT/HR_DATA.dbf 1243041 15-OCT-23 18:08:23 10 /opt/oracle/oradata/HEMANT/indx02.dbf 1243041 15-OCT-23 18:08:23 11 /opt/oracle/oradata/HEMANT/indx03.dbf 1243041 15-OCT-23 18:08:23 8 rows selected. 18:10:05 SQL> !ls -l /opt/oracle/oradata/HEMANT/*dbf -rw-r-----. 1 oracle oinstall 5251072 Oct 15 18:09 /opt/oracle/oradata/HEMANT/HR_DATA.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:09 /opt/oracle/oradata/HEMANT/indx01.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:09 /opt/oracle/oradata/HEMANT/indx02.dbf -rw-r-----. 1 oracle oinstall 10493952 Oct 15 18:09 /opt/oracle/oradata/HEMANT/indx03.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:08 /opt/oracle/oradata/HEMANT/redo01.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:09 /opt/oracle/oradata/HEMANT/redo02.dbf -rw-r-----. 1 oracle oinstall 10486272 Oct 15 18:09 /opt/oracle/oradata/HEMANT/redo03.dbf -rw-r-----. 1 oracle oinstall 419438592 Oct 15 18:09 /opt/oracle/oradata/HEMANT/sysaux.dbf -rw-r-----. 1 oracle oinstall 566239232 Oct 15 18:09 /opt/oracle/oradata/HEMANT/system.dbf -rw-r-----. 1 oracle oinstall 104865792 Oct 15 18:09 /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf -rw-r-----. 1 oracle oinstall 209723392 Oct 15 18:09 /opt/oracle/oradata/HEMANT/undotbs.dbf 18:10:10 SQL>
So, while the database was in BACKUP mode (from 17:56:10 to 18:09:45) the datafiles were being updated continuously, Log Switches were happening and ArchiveLogs were being generated. I also had ALTER SYSTEM CHECKPOINT commands being issued.
Restoring a PDB from another CDB can increase your SCN
I start with a 19c CDB called "ORCLCDB" and PDB "ORCLPDB1" that is present in it.
SQL> col name format a16 SQL> select dbid, name, current_scn from v$database; DBID NAME CURRENT_SCN ---------- ---------------- ----------- 2778483057 ORCLCDB 20906515 SQL> select dbid, guid, name, open_mode from v$pdbs; DBID GUID NAME OPEN_MODE ---------- -------------------------------- ---------------- ---------- 2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED READ ONLY 4057974068 8812E29864F47615E0530100007FA424 ORCLPDB1 READ WRITE 181927604 C07D1F2BA98C23D0E0530100007F7D34 RMANCAT MOUNTED SQL> SQL> alter pluggable database orclpdb1 close; Pluggable database altered. 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 24 09:39:55 2023 Version 19.12.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCLCDB (DBID=2778483057) RMAN> backup pluggable database orclpdb1 tag for_migration; Starting backup at 24-SEP-23 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00034 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_tpcctab_jyl0mzsp_.dbf input datafile file number=00009 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf input datafile file number=00026 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_data_min_j2p8z0qn_.dbf input datafile file number=00032 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoojqn_.dbf channel ORA_DISK_1: starting piece 1 at 24-SEP-23 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00011 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf input datafile file number=00010 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf input datafile file number=00012 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf input datafile file number=00031 name=/opt/oracle/oradata/ORCLCDB/8812E29864F47615E0530100007FA424/datafile/o1_mf_hemant_d_jtkoo5nr_.dbf channel ORA_DISK_2: starting piece 1 at 24-SEP-23 channel ORA_DISK_1: finished piece 1 at 24-SEP-23 piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2023_09_24/o1_mf_nnndf_FOR_MIGRATION_ljz5825q_.bkp tag=FOR_MIGRATION comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_2: finished piece 1 at 24-SEP-23 piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/8812E29864F47615E0530100007FA424/backupset/2023_09_24/o1_mf_nnndf_FOR_MIGRATION_ljz5828m_.bkp tag=FOR_MIGRATION comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03 Finished backup at 24-SEP-23 Starting Control File and SPFILE Autobackup at 24-SEP-23 piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2023_09_24/o1_mf_s_1148377605_ljz585bw_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 24-SEP-23 RMAN> RMAN> quit Recovery Manager complete. oracle19c>sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 24 09:48:04 2023 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 pluggable database orclpdb1 unplug into '/opt/oracle/product/19c/dbhome_1/orclpdb1.xml'; Pluggable database altered. SQL> !ls -l /opt/oracle/product/19c/dbhome_1/orclpdb1.xml -rw-r--r--. 1 oracle oinstall 12583 Sep 24 09:50 /opt/oracle/product/19c/dbhome_1/orclpdb1.xml SQL> SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0 oracle19c>
Now I go to the target CDB CDB2 and identify that there is no conflicting PDB. I also find that it has a lower SCN.
SQL> col name format a16 SQL> select dbid, name, current_scn from v$database; DBID NAME CURRENT_SCN ---------- ---------------- ----------- 837065713 CDB2 20664227 SQL> select dbid, name, current_scn from v$database; DBID NAME CURRENT_SCN ---------- ---------------- ----------- 837065713 CDB2 20664231 SQL> SQL> select * from v$pdbs 2 / CON_ID DBID CON_UID GUID ---------- ---------- ---------- -------------------------------- NAME -------------------------------------------------------------------------------------------------------------------------------- OPEN_MODE RES OPEN_TIME CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY ---------- --- --------------------------------------------------------------------------- ---------- ---------- ---------- -------- SNAPSHOT_PARENT_CON_ID APP APP APP APPLICATION_ROOT_CON_ID APP PRO LOCAL_UNDO UNDO_SCN UNDO_TIME CREATION_ DIAGNOSTICS_SIZE ---------------------- --- --- --- ----------------------- --- --- ---------- ---------- --------- --------- ---------------- PDB_COUNT AUDIT_FILES_SIZE MAX_SIZE MAX_DIAGNOSTICS_SIZE MAX_AUDIT_SIZE LAST_CHANGE TEM ---------- ---------------- ---------- -------------------- -------------- ----------- --- TENANT_ID ------------------------------------------------------------------------------------------------------------------------------------ UPGRADE_LEVEL GUID_BASE64 ------------- ------------------------------ 2 2054948555 2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED READ ONLY NO 24-SEP-23 09.26.31.678 AM +08:00 1997190 957349888 8192 ENABLED NO NO NO NO NO 1 280 04-MAY-19 0 0 0 0 0 0 COMMON USER NO 1 iBKSY7mfS73gUwEAAH973wA= 3 2205532678 2205532678 E6BD9C73839C10BEE0550A00275FC834 PDBTMP MOUNTED 17541716 999292928 8192 ENABLED NO NO NO NO NO 1 280 21-AUG-22 0 0 0 0 0 0 COMMON USER NO 1 5r2cc4OcEL7gVQoAJ1/INAA=
I then have the backups and the "closed" datafiles from the source CDB copied over to the target. (The CREATE PLUGGABLE DATABASE command will not be using the RMAN Backups but the PDB Describle XML file and the "closed" datafiles).
oracle19c>pwd /tmp/From_Source oracle19c>ls -l total 1882384 -rw-r-----. 1 oracle oinstall 1146109952 Sep 24 09:56 o1_mf_nnndf_FOR_MIGRATION_ljz5825q_.bkp -rw-r-----. 1 oracle oinstall 758202368 Sep 24 09:56 o1_mf_nnndf_FOR_MIGRATION_ljz5828m_.bkp -rw-r-----. 1 oracle oinstall 23232512 Sep 24 09:55 o1_mf_s_1148377605_ljz585bw_.bkp -rw-r--r--. 1 oracle oinstall 12583 Sep 24 09:59 orclpdb1.xml oracle19c>
I now prepare to copy the PDB to the target CDB2.. First I check for compatibility.. And then I plug in the PDB with a new name "ORCLPDB1_NEW"
SQL> select dbid, name, current_scn from v$database; DBID NAME CURRENT_SCN ---------- ---------------- ----------- 837065713 CDB2 20664231 SQL> set serveroutput on SQL> declare 2 compatible constant varchar2(3) := 3 case dbms_pdb.check_plug_compatibility( 4 pdb_descr_file=>'/tmp/From_Source/orclpdb1.xml', 5 pdb_name=>'ORCLPDB1') 6 when true then 'YES' 7 else 'NO' 8 END; 9 begin 10 dbms_output.put_line(compatible); 11 end; 12 / YES PL/SQL procedure successfully completed. SQL> SQL> show parameter db_create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /opt/oracle/oradata SQL> SQL> create pluggable database orclpdb1_new 2 using '/tmp/From_Source/orclpdb1.xml' 3 copy; --- copy will copy the datafiles to the location based on db_create_file_dest and CDB database name Pluggable database created. SQL> SQL> select dbid, name, current_scn from v$database; DBID NAME CURRENT_SCN ---------- ---------------- ----------- 837065713 CDB2 20910195 SQL> SQL> select dbid, guid, name, open_mode from v$pdbs; DBID GUID NAME OPEN_MODE ---------- -------------------------------- ---------------- ---------- 2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED READ ONLY 2205532678 E6BD9C73839C10BEE0550A00275FC834 PDBTMP MOUNTED 4057974068 8812E29864F47615E0530100007FA424 ORCLPDB1_NEW READ WRITE SQL>
The SCN of the target CDB2 has now been set to the much higher 20910195 (that was 20664231 before I "plugged in" ORCLDB1 as ORCLDB1_NEW). This is because it "read" the SCN from the headers of the datafiles that were plugged in.
SQL> select dbid, name, current_scn from v$database; DBID NAME CURRENT_SCN ---------- ---------------- ----------- 2778483057 ORCLCDB 20910076 SQL> select dbid, guid, name, open_mode from v$pdbs; DBID GUID NAME OPEN_MODE ---------- -------------------------------- ---------------- ---------- 2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED READ ONLY 4057974068 8812E29864F47615E0530100007FA424 ORCLPDB1 MOUNTED 181927604 C07D1F2BA98C23D0E0530100007F7D34 RMANCAT MOUNTED SQL> SQL> drop pluggable database orclpdb1 including datafiles; Pluggable database dropped. SQL> SQL> select dbid, guid, name, open_mode from v$pdbs; DBID GUID NAME OPEN_MODE ---------- -------------------------------- ---------------- ---------- 2054948555 88129263B99F4BBDE0530100007F7BDF PDB$SEED READ ONLY 181927604 C07D1F2BA98C23D0E0530100007F7D34 RMANCAT MOUNTED SQL> SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.12.0.0.0 oracle19c>
So, I have "migrated" ORCLPDB1 from ORCLCDB to CDB2 and, in the process, have found that the SCN in CDB2 got reset to a much higher value, because it "inherited" the higher Checkpoint SCN that was in the datafiles of ORCLPDB1.
SQL> select con_id, file#, plugin_change#, checkpoint_change# 2 from v$datafile 3 order by 1,2 4 / CON_ID FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE# ---------- ---------- -------------- ------------------ 1 1 0 20658011 1 3 0 20658011 1 4 0 20658011 1 7 0 20658011 2 5 0 13768423 2 6 0 13768423 2 8 0 13768423 3 58 0 20657657 3 59 0 20657657 3 60 0 20657657 4 69 0 20908595 4 70 0 20908595 4 71 0 20908595 4 72 0 20908595 4 73 0 20908595 4 74 0 20908595 4 75 0 20908595 4 76 0 20908595 18 rows selected. SQL> alter system checkpoint; System altered. SQL> select con_id, file#, plugin_change#, checkpoint_change# 2 from v$datafile 3 order by 1,2 4 / CON_ID FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE# ---------- ---------- -------------- ------------------ 1 1 0 20912767 1 3 0 20912767 1 4 0 20912767 1 7 0 20912767 2 5 0 13768423 2 6 0 13768423 2 8 0 13768423 3 58 0 20657657 3 59 0 20657657 3 60 0 20657657 4 69 0 20912767 4 70 0 20912767 4 71 0 20912767 4 72 0 20912767 4 73 0 20912767 4 74 0 20912767 4 75 0 20912767 4 76 0 20912767 18 rows selected. SQL> col name format a16 SQL> select con_id, name, open_mode from v$pdbs; CON_ID NAME OPEN_MODE ---------- ---------------- ---------- 2 PDB$SEED READ ONLY 3 PDBTMP MOUNTED 4 ORCLPDB1_NEW READ WRITE SQL> alter pluggable database pdbtmp open; Pluggable database altered. SQL> select con_id, file#, plugin_change#, checkpoint_change# 2 from v$datafile 3 order by 1,2 4 / CON_ID FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE# ---------- ---------- -------------- ------------------ 1 1 0 20912767 1 3 0 20912767 1 4 0 20912767 1 7 0 20912767 2 5 0 13768423 2 6 0 13768423 2 8 0 13768423 3 58 0 20912888 3 59 0 20912888 3 60 0 20912888 4 69 0 20912767 4 70 0 20912767 4 71 0 20912767 4 72 0 20912767 4 73 0 20912767 4 74 0 20912767 4 75 0 20912767 4 76 0 20912767 18 rows selected. SQL> alter system checkpoint; System altered. SQL> select con_id, file#, plugin_change#, checkpoint_change# 2 from v$datafile 3 order by 1,2 4 / CON_ID FILE# PLUGIN_CHANGE# CHECKPOINT_CHANGE# ---------- ---------- -------------- ------------------ 1 1 0 20912967 1 3 0 20912967 1 4 0 20912967 1 7 0 20912967 2 5 0 13768423 2 6 0 13768423 2 8 0 13768423 3 58 0 20912967 3 59 0 20912967 3 60 0 20912967 4 69 0 20912967 4 70 0 20912967 4 71 0 20912967 4 72 0 20912967 4 73 0 20912967 4 74 0 20912967 4 75 0 20912967 4 76 0 20912967 18 rows selected. SQL> SQL> alter session set container=ORCLPDB1_NEW; Session altered. SQL> col name format a54 SQL> select name from v$datafile; NAME ------------------------------------------------------ /opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA 424/datafile/o1_mf_system_ljz7d02h_.dbf /opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA 424/datafile/o1_mf_sysaux_ljz7d02l_.dbf /opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA 424/datafile/o1_mf_undotbs1_ljz7d02m_.dbf /opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA 424/datafile/o1_mf_users_ljz7d02o_.dbf /opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA 424/datafile/o1_mf_data_min_ljz7d02p_.dbf /opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA 424/datafile/o1_mf_hemant_d_ljz7d02r_.dbf /opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA 424/datafile/o1_mf_hemant_d_ljz7d02s_.dbf /opt/oracle/oradata/CDB2/8812E29864F47615E0530100007FA 424/datafile/o1_mf_tpcctab_ljz7d02t_.dbf 8 rows selected. SQL>
CON_ID=1 is Root and the datafiles get the new SCN after a Checkpoint. Note these datafiles still had the "older" SCN 20658011 before I issued the Checkpoint.
The FILTER operation for an EXISTS query
Let's assume a business case where we want to track customers who have ever placed orders with us.
(The business case could be extended to identify customers who have registered with us at least 3 years ago but have not placed any order in the last 12months --- they could either be targets for an advertising or promotions/discount campaign or customers to be retired, depending on the nature of our business and products. But I'll keep the query simple here).
Name Null? Type _________________ ___________ _______________ CUST_ID NOT NULL NUMBER(6) CUST_NAME NOT NULL VARCHAR2(56) CUST_REGN_DATE NOT NULL DATE SQL> select count(*) from customers; COUNT(*) ___________ 100 SQL> desc sales_orders; Name Null? Type _____________ ___________ _____________ ORDER_ID NOT NULL NUMBER(12) SALE_DATE DATE CUST_ID NUMBER(6) PRODUCT_ID NUMBER(6) QUANTITY NUMBER(12) SQL> SQL> select count(*) from sales_orders; COUNT(*) ___________ 1979580 SQL> select count(distinct(cust_id)) from sales_orders; COUNT(DISTINCT(CUST_ID)) ___________________________ 99 SQL> SQL> create table sale_made_tracker (cust_id number(6,0), sales_made varchar2(3)); Table SALE_MADE_TRACKER created. SQL>
Given that we have 100 customers and 1.98million sales orders, we want to add an entry to the SALE_MADE_TRACKER for a customer with a sales order. However, this is an "on-demand" query that an analyst runs from a front-end, customer-by-customer for specific customers. Thus, it is not executed as a batch for all 100 customers and 1.98million sales orders.
insert into sale_made_tracker select cust_id, 'YES' from customers a where a.cust_name = :bind_cust_name and exists (select '1' from customers c, sales_orders s where s.cust_id=c.cust_id and c.cust_id = a.cust_id)
This allows the user to specify a customer name without knowing the cust_id when executing the query.
SQL> explain plan for 2 insert into sale_made_tracker 3 select cust_id, 'YES' 4 from customers a 5 where a.cust_name = :bind_cust_name 6 and exists 7 (select '1' 8 from customers c, sales_orders s 9 where s.cust_id=c.cust_id 10 and c.cust_id = a.cust_id) 11 / Explained. SQL> SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT _________________________________________________________________________________________________ Plan hash value: 1657757803 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 45 | 2447 (1)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | SALE_MADE_TRACKER | | | | | | 2 | NESTED LOOPS SEMI | | 1 | 45 | 2447 (1)| 00:00:01 | |* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 43 | 3 (0)| 00:00:01 | | 4 | VIEW PUSHED PREDICATE | VW_SQ_1 | 208 | 416 | 2444 (1)| 00:00:01 | | 5 | NESTED LOOPS | | 20828 | 528K| 2444 (1)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | SYS_C0012587 | 1 | 13 | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS FULL | SALES_ORDERS | 20828 | 264K| 2443 (1)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("A"."CUST_NAME"=:BIND_CUST_NAME) 6 - access("C"."CUST_ID"="A"."CUST_ID") 7 - filter("S"."CUST_ID"="A"."CUST_ID") Note ----- PLAN_TABLE_OUTPUT ___________________________________________________________ - dynamic statistics used: dynamic sampling (level=2) 25 rows selected. SQL> delete plan_table; 15 rows deleted. SQL> SQL> explain plan for 2 insert into sale_made_tracker 3 select cust_id, 'YES' 4 from customers a 5 where a.cust_name = :bind_cust_name 6 and exists 7 (select /*+ NO_UNNEST */ '1' 8 from customers c, sales_orders s 9 where s.cust_id=c.cust_id 10 and c.cust_id = a.cust_id) 11 / Explained. SQL> SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT _________________________________________________________________________________________________ Plan hash value: 2963538242 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 43 | 6 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | SALE_MADE_TRACKER | | | | | |* 2 | FILTER | | | | | | |* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 43 | 3 (0)| 00:00:01 | | 4 | NESTED LOOPS | | 1 | 26 | 3 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | SYS_C0012587 | 1 | 13 | 1 (0)| 00:00:01 | |* 6 | TABLE ACCESS FULL | SALES_ORDERS | 20828 | 264K| 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "SALES_ORDERS" "S","CUSTOMERS" "C" WHERE "C"."CUST_ID"=:B1 AND "S"."CUST_ID"=:B2)) 3 - filter("A"."CUST_NAME"=:BIND_CUST_NAME) 5 - access("C"."CUST_ID"=:B1) 6 - filter("S"."CUST_ID"=:B1) Note PLAN_TABLE_OUTPUT ___________________________________________________________ ----- - dynamic statistics used: dynamic sampling (level=2) 26 rows selected. SQL> delete plan_table; 7 rows deleted. SQL>
So, for the purpose of this demo of the FILTER operation for an EXISTS clause, I will use the second form of the query.
SQL> exec :bind_cust_name := 'HEMANT'
PL/SQL procedure successfully completed. SQL> SQL> insert /*+ gather_plan_statistics */ into sale_made_tracker 2 select cust_id, 'YES' 3 from customers a 4 where a.cust_name = :bind_cust_name 5 and exists 6 (select /*+ NO_UNNEST */ '1' 7 from customers c, sales_orders s 8 where s.cust_id=c.cust_id 9 and c.cust_id = a.cust_id) 10 / 1 row inserted. SQL> SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ___________________________________________________________________________________________________________ SQL_ID 1j3jggfsurwg4, child number 0 ------------------------------------- insert /*+ gather_plan_statistics */ into sale_made_tracker select cust_id, 'YES' from customers a where a.cust_name = :bind_cust_name and exists (select /*+ NO_UNNEST */ '1' from customers c, sales_orders s where s.cust_id=c.cust_id and c.cust_id = a.cust_id) Plan hash value: 2963538242 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | | 0 |00:00:00.01 | 195 | | 1 | LOAD TABLE CONVENTIONAL | SALE_MADE_TRACKER | 1 | | 0 |00:00:00.01 | 195 | |* 2 | FILTER | | 1 | | 1 |00:00:00.01 | 193 | |* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 1 | 1 |00:00:00.01 | 7 | | 4 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 186 | |* 5 | INDEX UNIQUE SCAN | SYS_C0012587 | 1 | 1 | 1 |00:00:00.01 | 1 | |* 6 | TABLE ACCESS FULL | SALES_ORDERS | 1 | 20828 | 1 |00:00:00.01 | 185 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ___________________________________________________________ 2 - filter( IS NOT NULL) 3 - filter("A"."CUST_NAME"=:BIND_CUST_NAME) 5 - access("C"."CUST_ID"=:B1) 6 - filter("S"."CUST_ID"=:B1) Note ----- - dynamic statistics used: dynamic sampling (level=2) 33 rows selected. SQL>Which shows that a total of 185 blocks were read from the SALES_ORDERS table for "HEMANT"'s orders. Plus 1 block for the Index Unique Scan and 7 blocks for the Full Table Scan of CUSOMERS (totally 193 blocks for the SELECT) {Ignore the A-Rows being 0 for the actual INSERT in the Plan output, the execution shows that 1 row was inserted)
SQL> exec :bind_cust_name := 'LARRY' PL/SQL procedure successfully completed. SQL> SQL> insert /*+ gather_plan_statistics */ into sale_made_tracker 2 select cust_id, 'YES' 3 from customers a 4 where a.cust_name = :bind_cust_name 5 and exists 6 (select /*+ NO_UNNEST */ '1' 7 from customers c, sales_orders s 8 where s.cust_id=c.cust_id 9 and c.cust_id = a.cust_id) 10 / 0 rows inserted. SQL> SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ___________________________________________________________________________________________________________ SQL_ID 1j3jggfsurwg4, child number 0 ------------------------------------- insert /*+ gather_plan_statistics */ into sale_made_tracker select cust_id, 'YES' from customers a where a.cust_name = :bind_cust_name and exists (select /*+ NO_UNNEST */ '1' from customers c, sales_orders s where s.cust_id=c.cust_id and c.cust_id = a.cust_id) Plan hash value: 2963538242 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | | 0 |00:00:00.06 | 8723 | | 1 | LOAD TABLE CONVENTIONAL | SALE_MADE_TRACKER | 1 | | 0 |00:00:00.06 | 8723 | |* 2 | FILTER | | 1 | | 0 |00:00:00.06 | 8723 | |* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 1 | 1 |00:00:00.01 | 7 | | 4 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.06 | 8716 | |* 5 | INDEX UNIQUE SCAN | SYS_C0012587 | 1 | 1 | 1 |00:00:00.01 | 1 | |* 6 | TABLE ACCESS FULL | SALES_ORDERS | 1 | 20828 | 0 |00:00:00.06 | 8715 | -------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- PLAN_TABLE_OUTPUT ___________________________________________________________ 2 - filter( IS NOT NULL) 3 - filter("A"."CUST_NAME"=:BIND_CUST_NAME) 5 - access("C"."CUST_ID"=:B1) 6 - filter("S"."CUST_ID"=:B1) Note ----- - dynamic statistics used: dynamic sampling (level=2) 33 rows selected. SQL>
Which shows that a total of 8,715 blocks were read for "LARRY"'s orders from the SALES_ORDERS table alone but actual rows returned was 0 -- so no rows were inserted into SALE_MADE_TRACKER.
SQL> select cust_id, cust_name 2 from customers 3 where cust_id in (1,2) 4* / CUST_ID CUST_NAME __________ ____________ 1 HEMANT 2 LARRY SQL> SQL> select cust_id, count(*) 2 from sales_orders 3 where cust_id in (1,2) 4 group by cust_id 5 order by 1 6* / CUST_ID COUNT(*) __________ ___________ 1 20502 SQL> SQL> select /*+ gather_plan_statistics FULL (s) */ count(*) 2 from sales_orders s 3* / COUNT(*) ___________ 1979580 SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ________________________________________________________________________________________________ SQL_ID 042jxgv8y5xny, child number 0 ------------------------------------- select /*+ gather_plan_statistics FULL (s) */ count(*) from sales_orders s Plan hash value: 1409771706 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 8715 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 8715 | | 2 | TABLE ACCESS FULL| SALES_ORDERS | 1 | 2082K| 1979K|00:00:00.02 | 8715 | --------------------------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) 19 rows selected. SQL>
While "HEMANT" has 20,502 sales orders, "LARRY", despite being a registered customer, has not issued any order.