Hemant K Chitale

Subscribe to Hemant K Chitale feed
I am an Oracle Database Specialist in Singapore. Please note that this site uses cookies.

Updated: 11 hours 18 min ago

Grid Infrastructure --- OCR and Voting Disks Location at Installation

Sun, 2024-03-24 04:30

 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":

[root@srv1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84232
         Available space (kbytes) :     407452
         ID                       : 1183403784
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@srv1 ~]# crsctl query css votedisk
##  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).
[root@srv1 ~]#


and from the second node "srv2" :


[root@srv2 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84232
         Available space (kbytes) :     407452
         ID                       : 1183403784
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@srv2 ~]# crsctl query css votedisk
##  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).
[root@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):

[root@srv1 ~]# ocrconfig -add +FRA
[root@srv1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84232
         Available space (kbytes) :     407452
         ID                       : 1183403784
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded
         Device/File Name         :       +FRA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@srv1 ~]#

[root@srv2 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      84232
         Available space (kbytes) :     407452
         ID                       : 1183403784
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded
         Device/File Name         :       +FRA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@srv2 ~]#


Furthermore, each node of the Cluster has a Local Cluster Registry (that is called an OLR) :

[root@srv1 ~]# ocrcheck -local
Status of Oracle Local Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      83144
         Available space (kbytes) :     408540
         ID                       : 1343496565
         Device/File Name         : /u01/app/grid_base/crsdata/srv1/olr/srv1_19.olr
                                    Device/File integrity check succeeded

         Local registry integrity check succeeded

         Logical corruption check succeeded

[root@srv1 ~]#

[root@srv2 ~]# ocrcheck -local
Status of Oracle Local Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     491684
         Used space (kbytes)      :      83132
         Available space (kbytes) :     408552
         ID                       : 1914984123
         Device/File Name         : /u01/app/grid_base/crsdata/srv2/olr/srv2_19.olr
                                    Device/File integrity check succeeded

         Local registry integrity check succeeded

         Logical corruption check succeeded

[root@srv2 ~]#

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 :

SQL> l
  1  select g.name, d.disk_number, d.label, d.failgroup
  2  from v$asm_diskgroup g right join v$asm_disk d
  3  on g.group_number = d.group_number
  4  where g.name = 'OCR_VOTE'
  5* order by 1,2
SQL> /

NAME         DISK_NUMBER LABEL              FAILGROUP
------------ ----------- ------------------ ----------------
OCR_VOTE               0 OCR_VOTE_DISK_1    OCR_VOTE_0000
OCR_VOTE               1 OCR_VOTE_DISK_2    OCR_VOTE_0001
OCR_VOTE               2 OCR_VOTE_DISK_3    OCR_VOTE_0002
OCR_VOTE               3 OCR_VOTE_DISK_4    OCR_VOTE_0003
OCR_VOTE               4 OCR_VOTE_DISK_5    OCR_VOTE_0004

SQL>





Categories: DBA Blogs

Installing and Running DBSAT on 21c

Tue, 2024-03-05 09:33

 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. 
Since this is a RAC install, the listener.ora is from the Grid Home and tnsnames.ora (in 19c) is not under $ORACLE_HOME.

I'll just re-run with ORACLE_BASE set for the diag_dest_base



[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")


I need to add Java to my path. I know that I have it in the Grid installation so I can use that to generate the report.



[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.

The 3 "High Risk" entries (with corresponding CIS and STIG references) are :


* 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".


And, of course, I DROP the user after I generate the report.


[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.








Categories: DBA Blogs

Installing and Running Oracle AHF ORACHK on a 12.2 DB Server

Tue, 2024-02-27 00:20

 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.

The complete report is in HTML format in the final ZIP file.  

Here's the header :



Categories: DBA Blogs

A PDB as a Cluster Resource in RAC

Fri, 2024-02-23 03:40

 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 
the database resource "ora.db21crac.db"
the pdb resource "ora.db21crac.hemantpdb.pdb"
the service resource "ora.db21crac.newservice.svc"  (which I created in my previous post)

Now, I will add a new PDB "pdb2"


[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.

Categories: DBA Blogs

Adding a PDB (and Service) to a RAC database -- 1 - service running on only 1 Instance

Tue, 2024-02-13 02:10
On my existing, RAC database I have :

[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. 

This is because srvctl queries the cluster for information about services.  Service"hemantpdb" is created by default when I run CREATE PLUGGABLE DATABASE HEMANTPDB.


So, I can add a new service and configure Transparent Application Failover for SELECT failover but with only the first instance(DB21CRAC1) as the only one to start the service on initially{and the second instance (DB21CRAC2) as the alternate} :

[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 ~]$




If the database instance DB21CRAC1 fails (crashes), the ClusterWare starts the service on DB21CRAC2.  Here I kill the DB21CRAC process and then verify that the service has restarted on DB1CRAC2  :

[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.

Categories: DBA Blogs

Playlist of Oracle RAC Videos

Sat, 2024-02-10 02:23

 This is a link to my YouTube playlist of demonstrations on Oracle RAC (12c and 21c)



Categories: DBA Blogs

Video demonstration of RAC Client Query Session and Query Failover

Sun, 2024-01-21 03:33

 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

Categories: DBA Blogs

My Posts on Refreshable Clone PDBs

Sat, 2023-12-30 03:44

 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)


Categories: DBA Blogs

Do DataFiles get updated when in BACKUP mode ?

Sun, 2023-10-15 05:33

 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.

The  last Checkpoint command issued from the same session that issued the BEGIN BACKUP  that was marked as completed (obviously, all previous Checkpoints where also completed) when the  END BACKUP command from the same session.

So a BEGIN BACKUP
a.  Issues a Checkpoint
b. Does *not* stop updates to datafiles
The last Checkpoint is marked as completed when an END BACKUP is issued -- but that doesn't mean that datafiles are not being updated.

Categories: DBA Blogs

Restoring a PDB from another CDB can increase your SCN

Sat, 2023-09-23 22:08

 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.


I can go back to the source and drop the PDB.

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.

I can verify that all the datafiles (that are OPEN) in CDB2 can get stamped with the new SCN.

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.
CON_ID=2  is the SEED PDB so it does not get updated.
CON_ID=3 is PDTMP  at 20657657 and is stamped to 2091288 after I OPEN it.
CON_ID=4 is the ORCLPDB1_NEW that I plugged in today.
All the non-SEED datafiles are stamped again (to 20912967) after another Checkpoint.

Categories: DBA Blogs

The FILTER operation for an EXISTS query

Wed, 2023-08-09 04:26

 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.

The developer likes to use Bind Variables and comes up with a query like this :


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.

When testing this in 23c, I've had to add the NO_UNNEST Hint so that I could see the FILTER operation (similar to what I saw in a current 12.1 database):


 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.



If I run the query for customer name "HEMANT" :

 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)



But if I run the query for  customer name "LARRY" :
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.

Both "HEMANT" and "LARRY" exist in the CUSTOMERS table.


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.

The EXISTS query for "HEMANT" had to read only 185 blocks from the SALES_ORDERS table until it was *stopped* by the FILTER operation because the FILTER here stops the subquery as soon as the first row is returned (which you can also see as actual rows being "1" from the SALES_ORDERS table) -- as soon as the subquery returns TRUE.

"LARRY" has no orders so the EXISTS query ran through the whole SALES_ORDERS table to read 8,715 blocks.


Side note on the use of the Bind Variable here :  The fact that the developer used Bind Variables means that the Estimated Rows from the SALES_ORDERS table  in the Execution Plan was always going to be the same -- no matter which customer name was queried for -- because it prevents the use of Histograms (if they existed) on the tale.

Categories: DBA Blogs

Recent Video PlayLists

Mon, 2023-01-09 08:19

 I've added a few videos to my YouTube Channel in the past two years.


Oracle MultiTenant Database Configurations


Oracle DataGuard


Categories: DBA Blogs

Instance Parameters for Pluggable Databases

Sun, 2022-10-30 09:38
Since 12c Oracle has permitted Pluggable Databases with the MultiTenant Architecture. Normally, instance parameters (those in the init<SID>.ora or spfile<SID>.ora) apply to the whole Instance -- thus to all the Pluggable Databases.

However, certain parameters can be changed at the PDB (Pluggable Database) level.

Oracle stores the values for these changed parameters in the PDB_SPFILE$ table in the Root Container.

Here is a simple demonstration (in 19c) :


-- connect to the root and check certain parameter
SQL> connect / as sysdba
Connected.
SQL> show parameter optimizer_features_enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 19.1.0
SQL> show parameter sort_area_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536
SQL> show parameter workarea_size_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL>


-- override them at the PDB level for ORCLPDB1
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> alter system set optimizer_features_enable='12.2.0.1';

System altered.

SQL> alter system set sort_area_size=10485760;
alter system set sort_area_size=10485760
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

SQL> -- note that changing S_A_S has to be done as "DEFERRED" to apply to subsequent sessions
SQL> alter system set sort_area_size=10485760 deferred;

System altered.

SQL> alter system set workarea_size_policy='MANUAL';

System altered.

SQL>
SQL> show parameter optimizer_features_enable;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 12.2.0.1
SQL> show parameter workarea_size_policy;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string MANUAL
SQL>

-- connect to the PDB and verify that they have changed for the user session
SQL> connect hemant/hemant@orclpdb1
ERROR:
ORA-28002: the password will expire within 7 days


Connected.
SQL> show parameter optimizer_features_enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 12.2.0.1
SQL> show parameter sort_area_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 10485760
SQL> show parameter workarea_size_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string MANUAL
SQL>


-- return to the root CDB and check that it retains the old (default) values
SQL> connect / as sysdba
Connected.
SQL> show parameter optimizer_features_enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 19.1.0
SQL> show parameter sort_area_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536
SQL> show parameter workarea_size_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
workarea_size_policy string AUTO
SQL>


-- identify from the CDB where the overriding values are
SQL> connect / as sysdba
Connected.
SQL> get show_orclpdb1_params.sql
1 select pdb.dbid, pdb.con_uid, sp.name, sp.value$
2 from v$pdbs pdb, pdb_spfile$ sp
3 where pdb.name = 'ORCLPDB1'
4 and pdb.con_uid=sp.pdb_uid
5* order by sp.name
SQL> /

DBID CON_UID NAME VALUE$
---------- ---------- ---------------------------------------- ----------------------------------------
4057974068 4057974068 deferred_segment_creation FALSE
4057974068 4057974068 nls_date_format ''
4057974068 4057974068 optimizer_features_enable '12.2.0.1'
4057974068 4057974068 sort_area_size 10485760
4057974068 4057974068 workarea_size_policy 'MANUAL'

SQL>


(The two parameters "deferred_segment_creation" and "nls_date_format") are in my YouTube demonstration).

Thus, we can see that Oracle stores PDB parameters in a table called PDB_SPFILE$ in the Root Container.
These will apply whenever the Pluggable Database is re-opened -- and thus override the parameters for the Root that are from the Server Parameter File (spfile<SID>.ora)

I have presented a short Video Demonstration as well on YouTube.

 


Categories: DBA Blogs

SCN Synchronization when using a Database Link

Wed, 2022-09-07 09:36

 In my previous blog post about querying across a Database Link, I had stated "when running a query (just a SELECT statement) requires the two databases to synchronise SCN.  Whichever is the database with the lower SCN has to increment its SCN to the other database's (higher) SCN "


Here is a quick demonstration :


-- First I verify the current SCN in two databases "ORCLCDB" and "CDB2"
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB 07-SEP-22 10.10.49.643026 PM +08:00 19617903

ORCLCDB SQL>

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
CDB2 07-SEP-22 10.10.52.378969 PM +08:00 18562711

CDB2 SQL>
-- there is a wide gap between the two -- with ORCLCDB being ahead by approximately 1million
============================================================================================



-- now I connect a client to a PDB in ORCLCDB
-- and run a SELECT query that queries across a DBLink a table in a PDB in CDB2
-- this query is NOT a user transaction, only a SELECT statement
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> set time on
22:11:33 SQL>
22:11:48 SQL> select count(*) from tmp_data@pdbtmp_data;

COUNT(*)
----------
73181

22:12:05 SQL>


-- Now I check the SCNs in the two databases
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB 07-SEP-22 10.12.19.955477 PM +08:00 19620089

ORCLCDB SQL>

CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
CDB2 07-SEP-22 10.12.23.499409 PM +08:00 19620089

CDB2 SQL>
-- I can see that CDB2's SCN has "mysteriously" caught up with ORCLCDB
-- in fact they are exactly the same
-- in the real world there would still be some difference by the time I query again
-- because other transactions in either or both the databases would have incremented the SCN(s)
================================================================================================



-- now I disconnect from the client
-- this *closes* the database link
22:12:58 SQL> disconnect
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

-- and reconnect to the client
22:13:04 SQL> connect hemant/hemant@orclpdb1
Connected.
22:13:14 SQL>

-- I compare the SCNs in the two databases
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB 07-SEP-22 10.13.22.738662 PM +08:00 19620223

ORCLCDB SQL>

CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
CDB2 07-SEP-22 10.13.30.874874 PM +08:00 19620229

CDB2 SQL>
-- the gap is very small
-- in fact some internal operations in CDB2 have slightly incremented it ahead
===============================================================================



-- I run a PLSQL procedure in my client that does 1million COMMITs in the PDB
-- to increase the SCN only in ORCLCDB
22:14:06 SQL> execute onemillioncommits;

PL/SQL procedure successfully completed.

22:15:03 SQL>
22:15:07 SQL>


-- I now compare the two SCNs
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB 07-SEP-22 10.15.21.713518 PM +08:00 20654338

ORCLCDB SQL>
CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
CDB2 07-SEP-22 10.15.23.779855 PM +08:00 19620296

CDB2 SQL>
-- and find that there is again a huge gap between the two databases, with ORCLCDB ahead again
==============================================================================================



-- I query across the Database Link again from my client session
22:15:46 SQL> select count(*) from tmp_data@pdbtmp_data;

COUNT(*)
----------
73181

22:15:50 SQL>


-- and now compare the two SCNs
ORCLCDB SQL>select name, systimestamp, current_scn from v$database;
NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
ORCLCDB 07-SEP-22 10.16.02.362146 PM +08:00 20654373

ORCLCDB SQL>
CDB2 SQL>select name, systimestamp, current_scn from v$database;

NAME SYSTIMESTAMP CURRENT_SCN
--------- ------------------------------------ -----------
CDB2 07-SEP-22 10.16.14.122201 PM +08:00 20654453

CDB2 SQL>
-- now the gap is very small
-- some other operations in ORCLCDB have already incremented the SCN after the query
====================================================================================


So, simply running a SELECT statement (not even a User-Initiated COMMIT causes the two databases to synchronise SCNs.  The database with the lower SCN "inherits" the higher SCN.

So, the next time you see database SCNs mysteriously jump (e.g. in V$DATABASE.CURRENT_SCN  or in V$ARCHIVED_LOG.FIRST_CHANGE#  or in V$DATAFILE.CHECKPOINT_SCN) very significantly, check to see if you have queries to/from the database that use one or more Database Links.

Categories: DBA Blogs

Querying across a Database Link

Tue, 2022-08-23 10:17

 In this demonstration, assume that I have a local table called "TARGET_DATA" with a list of Countries and Products.  A new company is being acquired and that organisations's product list has been loaded into a table called TMP_DATA in a different database PDBTMP.

So, I run a MINUS query to find the local products that are not in the new company's product list -- but this query is run across a Database Link.

Here is how I create the Database Link, run the MINUS query and then obtain a tkprof of the trace file generated for the query.


SQL> create database link pdbtmp_data
2 connect to data_load identified by data_load using 'pdbtmp';

Database link created.

SQL>
SQL> alter session set tracefile_identifier='MY_DBLINK_QRY';

Session altered.

SQL> alter session set statistics_level='ALL';

Session altered.

SQL>
SQL> exec dbms_session.session_trace_enable(waits=>TRUE,binds=>FALSE);

PL/SQL procedure successfully completed.

SQL>
SQL> select * from target_data
2 minus
3 select * from tmp_data@pdbtmp_data
4 /

...
...

600 rows selected.

SQL> select 'x' from dual;

'
-
x

SQL> show array
arraysize 15
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
oracle19c>


-- tkprof of the trace file

select * from target_data
minus
select * from tmp_data@pdbtmp_data

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.24 0 0 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 41 0.17 0.30 453 455 0 600
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 43 0.19 0.55 453 455 1 600

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
600 600 600 MINUS (cr=455 pr=453 pw=0 time=306868 us starts=1)
71682 71682 71682 SORT UNIQUE (cr=455 pr=453 pw=0 time=142899 us starts=1 cost=872 size=2876679 card=73761)
73761 73761 73761 TABLE ACCESS FULL TARGET_DATA (cr=455 pr=453 pw=0 time=80877 us starts=1 cost=130 size=2876679 card=73761)
71143 71143 71143 SORT UNIQUE (cr=0 pr=0 pw=0 time=148559 us starts=1 cost=831 size=2927240 card=73181)
73181 73181 73181 REMOTE TMP_DATA (cr=0 pr=0 pw=0 time=96385 us starts=1 cost=80 size=2927240 card=73181)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 135 0.00 0.00
SQL*Net message to client 41 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 21 0.00 0.04
SQL*Net message to dblink 4 0.00 0.00
SQL*Net message from dblink 4 0.00 0.00
SQL*Net more data from dblink 352 0.01 0.05
SQL*Net message from client 41 8.84 8.89
********************************************************************************
SQL ID: 04vfkrajpkrnj Plan Hash: 1388734953

select 'x'
from
dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=1 us starts=1 cost=2 size=0 card=1)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 11.31 11.31



********************************************************************************


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 0.24 0 0 1 0
Execute 3 0.01 0.01 0 676 0 1
Fetch 43 0.17 0.30 453 455 0 601
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 48 0.20 0.56 453 1131 1 602

Misses in library cache during parse: 2
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 137 0.00 0.00
SQL*Net message to client 44 0.00 0.00
SQL*Net message from client 44 16.85 37.06
db file sequential read 1 0.00 0.00
single-task message 1 0.02 0.02
SQL*Net message from dblink 15 0.05 0.15
SQL*Net message to dblink 14 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file scattered read 21 0.00 0.04
SQL*Net more data from dblink 352 0.01 0.05




The query returns 600 rows but with an ARRAYSIZE of 15 for the sqlplus session, it results in 40 round trips (SQL*Net message to/from client)  (plus 1 additional for the parse) resulting a total of 41 SQL*Net messages.
However, the 73,181 rows from the remote database (PDBTMP) appear as 4 DBLink messages (SQL*Net message to/from dblink) and 352 "more data from dblink" waits.  So, there were many more network packets from PDBTMP to my local database.
But the total count of dblink messages is 15 (from dblink) and 14 (to dblink) -- an excess of about 11 over the initial 4.  Which means that there were some additional "chatty" interaction between my local database session and the one created in PDBTMP.


I also trace the session that is created by the Database Link operation in PDBTMP.  The tkprof for the trace file of that session is below :

SQL ID: bv2zwsyu4hq3k Plan Hash: 0

SELECT /*+ FULL(P) +*/ *
FROM
"TMP_DATA" P


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.05 4 101 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.04 0.05 4 101 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.14 0.14
********************************************************************************
SQL ID: 33v4pbtw6zjxt Plan Hash: 3633341221

SELECT "COUNTRY","PRODUCT_NAME"
FROM
"TMP_DATA" "TMP_DATA"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.05 0.08 450 455 0 73181
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.05 0.08 450 455 0 73181

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
73181 73181 73181 TABLE ACCESS FULL TMP_DATA (cr=455 pr=450 pw=0 time=66315 us starts=1 cost=124 size=2927240 card=73181)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 19 0.00 0.03
SQL*Net message from client 4 20.26 20.31
SQL*Net more data to client 352 0.00 0.00



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ----------- ---------- ---------- ----------
Parse 2 0.04 0.05 4 101 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4 0.05 0.08 450 455 0 73181
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.09 0.13 454 556 0 73181

Misses in library cache during parse: 2

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 1 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 11 0.00 0.00
SQL*Net message from client 11 20.26 20.47
db file sequential read 1 0.00 0.00
db file scattered read 19 0.00 0.03
SQL*Net more data to client 352 0.00 0.00




Note how the "select * from tmp_data" portion passed to PDBTMP is rewritten as 
SELECT /*+ FULL(P) +*/ *
FROM
"TMP_DATA" P
- and then as
SELECT "COUNTRY","PRODUCT_NAME"
FROM
"TMP_DATA" "TMP_DATA"

The remote database also shows that it sent 4 messages to/from "client" (in this case the client was my database session in my local database, not the sqlplus program) and 352 "more data" packets.  These numbers match what we see in the local trace file.
However, this also shows an additional 11 messages to/from "client".
There are some additional lines in the raw trace file which indicate what these overheads are.

One of them is for the logon trigger in PDBTMP that I have created to ensure that the DBLink connection generates a trace file.
The others are for recursive parse and other calls in PDBTMP (where parse success is sent to my "client" session in my local database).  Calls like :
select decode(upper(failover_method), NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(upper(failover_type), NULL, 1,  'NONE', 1, 'SESSION', 2, 'SELECT',  4, 'TRANSACTION',  8, 'AUTO' , 32,  1 ), failover_retries, failover_delay, flags, nvl(replay_initiation_timeout, 900), decode(upper(session_state_consistency), 'STATIC', 16, 0), failover_restore from sys.service$ where name = :1

insert into link_logons$(logon_time , source_id) VALUES ( SYSTIMESTAMP AT TIME ZONE 'UTC', :srcid )

XCTEND rlbk=0, rd_only=0, tim=....

XCTEND rlbk=1, rd_only=1, tim=...

XCTEND rlbk=0, rd_only=1, tim=...


So, a Database Link query has overheads. Also, when running a query (just a SELECT statement) requires the two databases to synchronise SCN.  Whichever is the database with the lower SCN has to increment its SCN to the other database's (higher) SCN  A distributed query is effectively a *transaction* which requires implicit COMMIT/ROLLBACK.

(also notice the "insert into link_logon$"  which seems to be an audit implementation in the remote database PDBTMP).


Bonus : This is the ON LOGON TRIGGER in PDTMP which causes it to write to a Trace File :


SQL> select dbms_metadata.get_ddl('TRIGGER','DATA_LOAD_LOGON_TRIG') from dual;

DBMS_METADATA.GET_DDL('TRIGGER','DATA_LOAD_LOGON_TRIG')
--------------------------------------------------------------------------------

CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."DATA_LOAD_LOGON_TRIG"
after logon on database
WHEN (user = 'DATA_LOAD') begin
execute immediate 'alter session set tracefile_identifier=''DATA_LOAD''';
execute immediate 'alter session set statistics_level=''ALL''';
dbms_session.session_trace_enable(waits=>TRUE,binds=>FALSE);
end;
ALTER TRIGGER "SYS"."DATA_LOAD_LOGON_TRIG" ENABLE


Such a trigger is useful when you want to trace every logon by a particular user but don't know when the logon will occur.
Categories: DBA Blogs

Direct Path Inserts and Locks

Sun, 2022-08-14 04:40

 In my previous post I had demonstrated how a Direct Path Insert in one session blocks another Direct Path Insert from another session, except when the two sessions explicitly name separate target Partitions.

Here, I will lock at how Oracle creates Locks for such operations.  In the output below, the SQLPrompt is set to either 'Sesn_1' or 'Sesn_2' or 'SYSTEM' (the DBA session) to indicate which session is executing the SQL statement.

First, running the Direct Path Insert without specifying the target Partition name.



SYSTEM>l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from dba_objects
3 where owner = 'HEMANT'
4 and object_name in ('MY_PART_TABLE')
5 and object_type in ('TABLE','TABLE PARTITION')
6* order by 1, 2 nulls first, 4
SYSTEM>/

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
MY_PART_TABLE TABLE 81817
MY_PART_TABLE P_100 TABLE PARTITION 81818 81827
MY_PART_TABLE P_200 TABLE PARTITION 81819 81828
MY_PART_TABLE P_300 TABLE PARTITION 81820 81820

SYSTEM>
SYSTEM>truncate table hemant.my_part_table;

Table truncated.

SYSTEM>
SYSTEM>select count(*) from v$locked_object;

COUNT(*)
----------
0

SYSTEM>



Sesn_1>select distinct sid from v$mystat;

SID
----------
138

Sesn_1>
Sesn_1>l
1 insert /*+ APPEND */ into my_part_table
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_1>/

50 rows created.

Sesn_1>



Sesn_2>select distinct sid from v$mystat;

SID
----------
384

Sesn_2>
Sesn_2>l
1 insert /*+ APPEND */ into my_part_table
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_2>/
---------- Sesn_2 is now in a Wait



SYSTEM>select event from v$session where sid=384;

EVENT
----------------------------------------------------------------
enq: TM - contention

SYSTEM>
SYSTEM>select session_id, object_id, locked_mode
2 from v$locked_object
3 /

SESSION_ID OBJECT_ID LOCKED_MODE
---------- ---------- -----------
138 81817 6
384 81817 0

SYSTEM>
---------- Object ID 81817 is the Table itself, locked by Session 138 -- Sesn_1
SYSTEM>select sid, type, id1, id2, lmode, request, block
2 from v$lock
3 where sid in (138,384)
4 order by 1
5 /

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
138 TM 81817 0 6 0 1
138 AE 134 4057974068 4 0 0
138 TX 393246 7880 6 0 0
384 AE 134 4057974068 4 0 0
384 TM 81817 0 0 6 0

SYSTEM>
---------- Session 138 has TM Lock Mode6 on Object 81817 -- the Table itself
---------- Session 384 (Sesn_2, Waiting) is requesting a TM Lock Mode6



So, it is clear that Session 1 (SID 138) had a TM Lock (Mode->6) on the Table, blocking Session 2 (384) (which is requesting the same Mode->6 lock) when the Direct Path Insert specified the Table name alone.
The TX lock by SID 138  is the Transaction Row Exclusive Lock.  What is blocking Session 2 (SID 384) is that it is requesting a Mode->6 lock on the same Object (the Table) as already held by Session 1 (SID 138)

Then, when I issue a Rollback (or Commit) from Sesn_1, the Insert by Sesn_2 goes through.

Before this next test, I rollback both the Inserts

Next, with the target Partition named



Sesn_1>l
1 insert /*+ APPEND */ into my_part_table partition (p_100)
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_1>/

50 rows created.

Sesn_1>



Sesn_2>l
1 insert /*+ APPEND */ into my_part_table partition (p_200)
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_2>/

50 rows created.

Sesn_2>



SYSTEM>select event from v$session where sid=384;

EVENT
----------------------------------------------------------------
SQL*Net message from client

SYSTEM>
SYSTEM>select session_id, object_id, locked_mode
2 from v$locked_object
3 /

SESSION_ID OBJECT_ID LOCKED_MODE
---------- ---------- -----------
138 81817 3
138 81818 6
384 81817 3
384 81819 6

SYSTEM>
SYSTEM>l
1 select sid, type, id1, id2, lmode, request, block
2 from v$lock
3 where sid in (138,384)
4* order by 1,2,3
SYSTEM>/

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
138 AE 134 4057974068 4 0 0
138 TM 81817 0 3 0 0
138 TM 81818 0 6 0 0
138 TX 65540 6296 6 0 0
384 AE 134 4057974068 4 0 0
384 TM 81817 0 3 0 0
384 TM 81819 0 6 0 0
384 TX 458781 6211 6 0 0

8 rows selected.

SYSTEM>
---------- Both sessions SIDs 138 (Sesn_1) and 384 (Sesn_2) have the Table Object (81817) locked in Mode3, not Mode6
---------- But the Partitions (Objects 81818 and 81819) are locked in Mode6
---------- Neither is SID 138 Blocking any other session nor is SID 384 being blocked


Now we see that the Table (object 81817) is locked in Mode->3 and not Mode->6  by by *both sessions* while the respective Partitions P_100 (object 81818) and P_200 (81819) are locked in Mode->6 without session 384 (Sesn_2) waiting for a block.

Thus, as the Table itself is not locked in Mode->6, Sesn_2 is allowed a Direct Path Insert into another Partition -- only so as long as it explicitly names the Target Partition. (If Sesn_2 attempts to do a Direct Path Insert without naming a Target Partition, it will, again begin waiting on Sesn_1 without the REQUEST being evident in v$lock)

Mode 6 is Lock Table in Exclusive Mode in the first case.
In the second case, the Table is locked in Mode 3 which is Row-Exclusive, not Table level.

The "TX" is a Transaction Enqueue 

The "AE" Lock type is "Edition Enqueue" --- which we are not concerned with in this test, as it relates to Editioning (which I am not currently using) and will appear even if Editioning is not being used.


Categories: DBA Blogs

Direct Path Insert into a Partitioned Table

Fri, 2022-08-12 09:45

 Normally a Direct Path Insert that is not committed blocks other concurrent Direct Path Inserts.  This behaviour also extends to Partitioned Tables.

Thus :


--------- From Session 1 : inserting only into the first Partition p_100
SQL> l
1 create table my_part_table (id_col number, data_col varchar2(25))
2 partition by range (id_col)
3 (partition p_100 values less than (101),
4 partition p_200 values less than (201),
5 partition p_300 values less than (301)
6* )
SQL>
SQL> /

Table created.

SQL>
SQL> l
1 insert /*+ APPEND */ into my_part_table
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

50 rows created.

SQL>

---------- From Session 2 : where we know that the rows will actually be inserted into a different Partition p_200
SQL> l
1 insert /*+ APPEND */ into my_part_table
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

-- Yet, Session 2 is blocked and has to wait untill Session 1 does a COMMIT or ROLLBACK


Even though the 2 sessions will be inserting into separate Partitions (i.e. separate Segments). the first session blocks  the other Direct Path Insert.

However, this blocking can be avoided by explicitly naming the target Partition

Thus :


--------- From Session 1 : inserting only into the first Partition p_100 explicitly named 
SQL> l
1 insert /*+ APPEND */ into my_part_table partition (p_100)
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
---------- From Session 2 : inserting into the second Parition p_200 explicitlly named
SQL> l
1 insert /*+ APPEND */ into my_part_table partition (p_200)
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
-------- Of course, both sessions can't requery until they COMMIT or ROLLBACK
--- note : Session 2 can query and see rows inserted by Session 1 and vice-versa, but not their own rows
SQL> select count(*) from my_part_table;
select count(*) from my_part_table
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL>
SQL> commit; -- issued by both sessions so as to be able to see their own rows as well

Commit complete.

SQL>
SQL> select count(*) from my_part_table;

COUNT(*)
----------
100

SQL>
SQL> select count(*) from my_part_table partition (p_100);

COUNT(*)
----------
50

SQL> select count(*) from my_part_table partition (p_200);

COUNT(*)
----------
50

SQL>


Thus, although there is general advice not to explicitly name a target Partition, I find this method useful if I have multiple concurrent Direct Path Inserts.




Categories: DBA Blogs

The format of the ROWID

Sun, 2022-08-07 04:31

 A ROWID is a method of identifying the physical location of a row in an Oracle Database.  An Index on a Table captures the ROWIDs for the rows holding the index key values and these entries in the Index are how an Index lookup redirects a query to the row (i.e. physical location) in the table.

A ROWID (called an Extended ROWID) consists of 4 components :

    -    DataObject Number

    -    DataFile Number Relative to the Tablespace

    -    DataBlock Number (within the DataFile)

    -    RowNumber within the DataBlock


A Partitioned Table actually consists of multiple segments.  Each segment has a different DataObject Number.

Here is a quick demo of the difference between a normal (Non-Partitioned) Table and a Partitioned Table :


The Normal Table :



SQL> create table NONPARTITIONED (id_col number, data_col varchar2(1000)) pctfree 99 tablespace HEMANT_DATA;

Table created.

SQL>
SQL> insert into NONPARTITIONED
2 values (1, dbms_random.string('X',999));

1 row created.

SQL> insert into NONPARTITIONED
2 values (2,'This is the second row');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
2 from NONPARTITIONED
3 order by id_col
4 /

ROWID ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT70AAgAAAACTAAA 1 CBXBRIP5ZNQ9VPZNC4HHVJJH
AAAT70AAgAAAACXAAA 2 This is the second row

SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from NONPARTITIONED
6* order by id_col
SQL> /

ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81652 32 147
2 81652 32 151

SQL>


Because I created the Table with PCTFREE 99 and inserted a long string in the first row, the second row was created in a different block.  Both Blocks are in the same Relative File Number (32) and belong to the same Object (ObjectNumber 81652).  Is this really the Object ID ?



The Partitioned Table :


SQL> l
1 create table PARTITIONED (id_col number, data_col varchar2(1000))
2 partition by range (id_col)
3 (
4 partition P_1 values less than (2) tablespace HEMANT_DATA,
5 partition P_2 values less than (3) tablespace HEMANT_DATA,
6 partition P_3 values less than (4) tablespace HEMANT_DATA,
7 partition P_MAX values less than (MAXVALUE) tablespace HEMANT_DATA
8 )
9* tablespace HEMANT_DATA
SQL> /

Table created.

SQL>
SQL> insert into PARTITIONED
2 values (1, dbms_random.string('X',999));

1 row created.

SQL> insert into PARTITIONED
2 values (2,'This is the second row');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
2 from PARTITIONED
3 order by id_col
4 /

ROWID ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT77AAfAAAAJ3AAA 1 RFU3DNMCD6GXL2ZNV9DDGBG2
AAAT78AAfAAAAZ3AAA 2 This is the second row

SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from PARTITIONED
6* order by id_col
SQL> /

ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81659 31 631
2 81660 31 1655

SQL>


In this case, the two rows are in different Blocks not because of the PCTFREE (which has defaulted to 10) but because they are in different Segments -- as you can see from the ObjectNumbers being different for the two rows.
(You might have also noticed that these were created in a separate datafile, FILENUMBER 31 instead of 32 {as was for the first table}, but that is because Oracle tries to allocate new segments across different datafiles)

In the ROWID format the ObjectNumber is actually the *Data Object Number* that identifies the  Segment, not the Object Number of the Table.

Thus, to verify the Segments of the two tables, I can query and check :


SQL> l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from user_objects
3 where object_name in ('NONPARTITIONED','PARTITIONED')
4 and object_type in ('TABLE','TABLE PARTITION')
5* order by 1, 2 nulls first, 4
SQL> /

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED TABLE 81652 81652
PARTITIONED TABLE 81658
PARTITIONED P_1 TABLE PARTITION 81659 81659
PARTITIONED P_2 TABLE PARTITION 81660 81660
PARTITIONED P_3 TABLE PARTITION 81661 81661
PARTITIONED P_MAX TABLE PARTITION 81662 81662

6 rows selected.

SQL>



Thus, for the NONPARTITIONED Table, the Object_ID and Data_Object_ID and that returned by DBMS_ROWID are all the same -- 81652.  
But the logical entry for the PARTITIONED Table has an Object_ID of 81658 but, without any segment and, therefore, without a Data_Object_ID.
The rows in this Partitioned Table are actually created in the two different Partition Segments with the corresponding Data_Object_ID  (81659 and 81660).



We know that when we rebuild a Table, the ROWID changes.  But this is actually because a new Segment is allocated.  

Thus, if I were to do a MOVE of the "Normal" Table :



SQL> alter table NONPARTITIONED move;

Table altered.

SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from NONPARTITIONED
6* order by id_col
SQL> /

ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81663 32 155
2 81663 32 156

SQL>
SQL> l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from user_objects
3 where object_name in ('NONPARTITIONED','PARTITIONED')
4 and object_type in ('TABLE','TABLE PARTITION')
5* order by 1, 2 nulls first, 4
SQL> /

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED TABLE 81652 81663
PARTITIONED TABLE 81658
PARTITIONED P_1 TABLE PARTITION 81659 81659
PARTITIONED P_2 TABLE PARTITION 81660 81660
PARTITIONED P_3 TABLE PARTITION 81661 81661
PARTITIONED P_MAX TABLE PARTITION 81662 81662

6 rows selected.

SQL>


Executing a MOVE of the Non-Partitioned Table resulted in a change of the *Data Object Number* (i.e DATA_OBJECT_ID) (from 81652 to 81663) without changing the OBJECT_ID.



For a couple of more interesting aspects of ROWIDs, see this YouTube video "Think you know how the ROWID works? Think again!" by Connor McDonald




Categories: DBA Blogs

Index Statistic NUM_ROWS excludes Rows with NULL value

Sun, 2022-06-12 05:03

 A quick demonstration of the difference between the Table Statistic NUM_ROWS and the Index Statistic NUM_ROWS



SQL> desc customers
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
CUST_ID NOT NULL NUMBER
CUST_NAME VARCHAR2(128)
CUST_CITY VARCHAR2(128)
CUST_START_DATE DATE

SQL> select index_name, column_position, column_name
2 from user_ind_columns
3 where table_name = 'CUSTOMERS'
4 order by 1,2
5 /

INDEX_NAME COLUMN_POSITION COLUMN_NAME
---------------- --------------- ----------------
CUST_CITY_NDX 1 CUST_CITY
CUST_ID_NDX 1 CUST_ID

SQL>
SQL> select count(*) from customers;

COUNT(*)
----------
1299647

SQL>
SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL>
SQL> select last_analyzed, sample_size, num_rows
2 from user_tables
3 where table_name = 'CUSTOMERS'
4 /

LAST_ANAL SAMPLE_SIZE NUM_ROWS
--------- ----------- ----------
12-JUN-22 1299647 1299647

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
2 from user_indexes
3 where table_name = 'CUSTOMERS'
4 order by index_name
5 /

INDEX_NAME LAST_ANAL SAMPLE_SIZE NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_NDX 12-JUN-22 1179648 1179648
CUST_ID_NDX 12-JUN-22 1299647 1299647

SQL>
SQL> select count(*)
2 from customers
3 where cust_city is null
4 /

COUNT(*)
----------
119999

SQL>
SQL> select 1299647-1179648 from dual;

1299647-1179648
---------------
119999

SQL>
SQL> create index cust_id_city_ndx
2 on customers(cust_id, cust_city)
3 /

Index created.

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
2 from user_indexes
3 where table_name = 'CUSTOMERS'
4 order by index_name
5 /

INDEX_NAME LAST_ANAL SAMPLE_SIZE NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_NDX 12-JUN-22 1179648 1179648
CUST_ID_CITY_NDX 12-JUN-22 1299647 1299647
CUST_ID_NDX 12-JUN-22 1299647 1299647

SQL>
SQL> drop index cust_id_city_ndx;

Index dropped.

SQL>
SQL> create index cust_city_id_ndx
2 on customers(cust_city, cust_id)
3 /

Index created.

SQL>
SQL> select index_name, last_analyzed, sample_size, num_rows
2 from user_indexes
3 where table_name = 'CUSTOMERS'
4 order by index_name
5 /

INDEX_NAME LAST_ANAL SAMPLE_SIZE NUM_ROWS
---------------- --------- ----------- ----------
CUST_CITY_ID_NDX 12-JUN-22 1299647 1299647
CUST_CITY_NDX 12-JUN-22 1179648 1179648
CUST_ID_NDX 12-JUN-22 1299647 1299647

SQL>


In recent versions, a CREATE INDEX implicitly includes a Gather Stats call on the new Index by default, so the two new Indexes also had updated statistics.

The CUST_CITY column has 119999 rows with NULLs.  So, the Statistics on the Index CUST_CITY_NDX on this column did not include these new rows.

The two new Indexes that I created (CUST_ID_CITY_NDX and CUST_CITY_ID_NDX) were composite indexes where at least one column (CUST_ID - which is a Primary Key) is a NOT NULL.
Therefore, Statistics on these Indexes did include all the rows as, for every row in the Table, at least 1 column had a Not NULL value.

Categories: DBA Blogs

PDB Recovery Isolation in 21c

Sun, 2022-05-08 10:14

 In my previous blog post, where I demonstrated PDB Flashback, I had alluded to the "PDB Recovery Isolation" feature of 21c.

Here is a demonstration.

On the Primary, I issue only two commands to create a new PDB from an *existing, active* PDB (a PDB that is in Read-Write mode and is not  PDB$SEED) :  The source PDBHKC is active and you will notice the Archive Log Sequence# being incremented significantly as transactions are in-flight and continuing while PDBKC is being CREATEd and OPENed.


SQL> create pluggable database pdbnew from pdbhkc;

Pluggable database created.

SQL> alter pluggable database pdbnew open;

Pluggable database altered.

SQL> quit


These are the relevant Primary Database Alert entries (not every entry) :

2022-05-08T22:32:56.582810+08:00
create pluggable database pdbnew from pdbhkc

2022-05-08T22:33:02.783531+08:00
PDBNEW(6):Endian type of dictionary set to little

2022-05-08T22:33:03.103173+08:00
****************************************************************
Pluggable Database PDBNEW with pdb id - 6 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x000000000000011d
****************************************************************
PDBNEW(6):Media Recovery Start
2022-05-08T22:33:03.293420+08:00
PDBNEW(6):Serial Media Recovery started
PDBNEW(6):max_pdb is 6
2022-05-08T22:33:03.393288+08:00
PDBNEW(6):Recovery of Online Redo Log: Thread 1 Group 1 Seq 73 Reading mem 0
PDBNEW(6): Mem# 0: /u02/oradata/DB213H1/redo01.log
2022-05-08T22:33:03.541053+08:00
Thread 1 advanced to log sequence 75 (LGWR switch), current SCN: 4931897
Current log# 3 seq# 75 mem# 0: /u02/oradata/DB213H1/redo03.log
2022-05-08T22:33:03.541508+08:00
PDBNEW(6):Recovery of Online Redo Log: Thread 1 Group 2 Seq 74 Reading mem 0
PDBNEW(6): Mem# 0: /u02/oradata/DB213H1/redo02.log
2022-05-08T22:33:03.598468+08:00
PDBNEW(6):Incomplete Recovery applied until change 4931866 time 05/08/2022 22:33:02
PDBNEW(6):NET (PID:16256): Media Recovery Complete [dbsdrv.c:15048]
2022-05-08T22:33:03.615889+08:00
NET (PID:16388): Archived Log entry 119 added for B-1102494684.T-1.S-74 ID 0x7fbfe6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:03.786205+08:00
PDBNEW(6):Pluggable database PDBNEW pseudo opening
PDBNEW(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PDBNEW(6):Autotune of undo retention is turned on.
PDBNEW(6):Undo initialization recovery: Parallel FPTR failed: start:15704441 end:15704446 diff:5 ms (0.0 seconds)
PDBNEW(6):Undo initialization recovery: err:0 start: 15704439 end: 15704511 diff: 72 ms (0.1 seconds)
PDBNEW(6):[16256] Successfully onlined Undo Tablespace 2.
PDBNEW(6):Undo initialization online undo segments: err:0 start: 15704512 end: 15704530 diff: 18 ms (0.0 seconds)
PDBNEW(6):Undo initialization finished serial:0 start:15704439 end:15704538 diff:99 ms (0.1 seconds)
PDBNEW(6):Database Characterset for PDBNEW is AL32UTF8
PDBNEW(6):Pluggable database PDBNEW pseudo closing
PDBNEW(6):JIT: pid 16256 requesting stop
PDBNEW(6):Closing sequence subsystem (15704602605).
PDBNEW(6):Buffer Cache flush started: 6
PDBNEW(6):Buffer Cache flush finished: 6
Completed: create pluggable database pdbnew from pdbhkc


2022-05-08T22:33:18.209272+08:00
alter pluggable database pdbnew open
2022-05-08T22:33:18.210926+08:00
PDBNEW(6):Pluggable database PDBNEW opening in read write
PDBNEW(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PDBNEW(6):Autotune of undo retention is turned on.
PDBNEW(6):Endian type of dictionary set to little
PDBNEW(6):Undo initialization recovery: Parallel FPTR complete: start:15718778 end:15718780 diff:2 ms (0.0 seconds)
PDBNEW(6):Undo initialization recovery: err:0 start: 15718778 end: 15718780 diff: 2 ms (0.0 seconds)
PDBNEW(6):[16256] Successfully onlined Undo Tablespace 2.
PDBNEW(6):Undo initialization online undo segments: err:0 start: 15718780 end: 15718873 diff: 93 ms (0.1 seconds)
PDBNEW(6):Undo initialization finished serial:0 start:15718778 end:15718875 diff:97 ms (0.1 seconds)
PDBNEW(6):Database Characterset for PDBNEW is AL32UTF8
PDBNEW(6):Deleting old file#13 from file$
PDBNEW(6):Deleting old file#14 from file$
PDBNEW(6):Deleting old file#15 from file$
PDBNEW(6):Adding new file#22 to file$(old file#13). fopr-1, newblks-35840, oldblks-19200
PDBNEW(6):Adding new file#23 to file$(old file#14). fopr-1, newblks-47360, oldblks-15360
PDBNEW(6):Adding new file#24 to file$(old file#15). fopr-1, newblks-12800, oldblks-12800
PDBNEW(6):Successfully created internal service PDBNEW at open
****************************************************************
Post plug operations are now complete.
Pluggable database PDBNEW with pdb id - 6 is now marked as NEW.
****************************************************************
PDBNEW(6):SUPLOG: Set PDB SUPLOG SGA at PDB OPEN, old 0x18, new 0x0 (no suplog)


2022-05-08T22:33:21.760166+08:00
Thread 1 advanced to log sequence 81 (LGWR switch), current SCN: 4933127
Current log# 3 seq# 81 mem# 0: /u02/oradata/DB213H1/redo03.log


2022-05-08T22:33:23.162867+08:00
PDBNEW(6):Opening pdb with no Resource Manager plan active
Pluggable database PDBNEW opened read write
Completed: alter pluggable database pdbnew open


(The "Deleting old file#" and "Adding new file#" entries are simply about updating the controlfile)
Note that Log Sequence# has moved from 72 to 81 during the course of the CREATE and OPEN of the new PDB.


These are the Standby Alert Log entries :


2022-05-08T22:32:57.237583+08:00
ARC0 (PID:1354): Archived Log entry 55 added for B-1102494684.T-1.S-73 ID 0x7f7ae6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:32:57.237902+08:00
rfs (PID:12549): Opened LNO:5 for DBID:3870017436 B-1102494684.T-1.S-74 [krsr.c:18143]
2022-05-08T22:32:57.386776+08:00
PR00 (PID:1438): Media Recovery Waiting for T-1.S-74 (in transit) [krsm.c:6185]
2022-05-08T22:32:57.388789+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 74 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
2022-05-08T22:33:02.900342+08:00
Recovery created pluggable database PDBNEW
PDBNEW(6):Tablespace-SYSTEM during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #22 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_system_k7ho4s6r_.dbf'.
PDBNEW(6):Tablespace-SYSAUX during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #23 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_sysaux_k7ho4s6s_.dbf'.
PDBNEW(6):Tablespace-UNDOTBS1 during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #24 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_undotbs1_k7ho4s6t_.dbf'.
PDBNEW(6):Tablespace-TEMP during PDB create offlined since source is in r/w mode or this is a refresh clone
2022-05-08T22:33:03.709388+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-75 [krsr.c:18143]
2022-05-08T22:33:03.724813+08:00
ARC2 (PID:1362): Archived Log entry 56 added for B-1102494684.T-1.S-74 ID 0x7fbbe6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:04.051241+08:00
PR00 (PID:1438): Media Recovery Waiting for T-1.S-75 (in transit) [krsm.c:6185]
2022-05-08T22:33:04.052945+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 75 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_4_k5yt8y5f_.log
2022-05-08T22:33:07.786948+08:00
rfs (PID:12549): Opened LNO:5 for DBID:3870017436 B-1102494684.T-1.S-76 [krsr.c:18143]
2022-05-08T22:33:07.811804+08:00
ARC0 (PID:1354): Archived Log entry 57 added for B-1102494684.T-1.S-75 ID 0x7f7ae6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:07.926136+08:00
PR00 (PID:1438): Media Recovery Waiting for T-1.S-76 (in transit) [krsm.c:6185]
2022-05-08T22:33:07.927698+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 76 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
2022-05-08T22:33:08.525489+08:00
PDBNEW(6):Full restore complete of datafile 24 /u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_undotbs1_k7ho53ny_.dbf. Elapsed time: 0:00:01
PDBNEW(6): checkpoint is 4932003
PDBNEW(6): last deallocation scn is 3928601
PDBNEW(6):Datafile #24 has been copied to the standby.
2022-05-08T22:33:11.424337+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-77 [krsr.c:18143]
2022-05-08T22:33:11.492712+08:00
PR00 (PID:1438): Media Recovery Waiting for T-1.S-77 (in transit) [krsm.c:6185]
2022-05-08T22:33:11.494117+08:00
ARC2 (PID:1362): Archived Log entry 58 added for B-1102494684.T-1.S-76 ID 0x7fbbe6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:11.552303+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 77 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_4_k5yt8y5f_.log
2022-05-08T22:33:12.363978+08:00
PDBNEW(6):Full restore complete of datafile 23 /u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_sysaux_k7ho54o6_.dbf. Elapsed time: 0:00:04
PDBNEW(6): checkpoint is 4932003
PDBNEW(6): last deallocation scn is 3862144
PDBNEW(6):Datafile #23 has been copied to the standby.
2022-05-08T22:33:14.572554+08:00
rfs (PID:12549): Opened LNO:5 for DBID:3870017436 B-1102494684.T-1.S-78 [krsr.c:18143]
2022-05-08T22:33:14.572781+08:00
Trying to expand controlfile section 11 for Oracle Managed Files
Expanded controlfile section 11 from 56 to 112 records
Requested to grow by 56 records; added 2 blocks of records
2022-05-08T22:33:14.585789+08:00
ARC0 (PID:1354): Archived Log entry 59 added for B-1102494684.T-1.S-77 ID 0x7f7ae6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:14.650210+08:00
PR00 (PID:1438): Media Recovery Waiting for T-1.S-78 (in transit) [krsm.c:6185]
2022-05-08T22:33:14.698805+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 78 Reading mem 0
Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
2022-05-08T22:33:15.936076+08:00
PDBNEW(6):Full restore complete of datafile 22 /u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_system_k7ho58jz_.dbf. Elapsed time: 0:00:03
PDBNEW(6): checkpoint is 4932003
PDBNEW(6): last deallocation scn is 3860354
PDBNEW(6):Datafile #22 has been copied to the standby.
2022-05-08T22:33:15.948328+08:00
PDBNEW(6):PDB Side Media Recovery started for pdbid(6)
PDBNEW(6):.... (PID:12667): Managed Recovery starting Real Time Apply [krsm.c:15865]
PDBNEW(6):max_pdb is 6
2022-05-08T22:33:16.031099+08:00
PDBNEW(6):Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_75_k7ho53pg_.arc
2022-05-08T22:33:16.045816+08:00
PDBNEW(6):Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_76_k7ho57fz_.arc
2022-05-08T22:33:16.076227+08:00
PDBNEW(6):Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_77_k7ho5bgp_.arc
PDBNEW(6):.... (PID:12667): Media Recovery Waiting for T-1.S-78 (in transit) [krsm.c:6185]
2022-05-08T22:33:16.106119+08:00
PDBNEW(6):Media Recovery of Online Log [Thread=1, Seq=78]
2022-05-08T22:33:16.106586+08:00
PDBNEW(6):Recovery of Online Redo Log: Thread 1 Group 5 Seq 78 Reading mem 0
PDBNEW(6): Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
PDBNEW(6):PSR[6]:influxScn=4932081,nxtCkptScn=4932081,minScn=4932004,krdsini=0,krsmrs=9082,nof=0,kcvrlc=1102494684,kcvrls=2601843
PDBNEW(6):The merge request has been submitted by side recovery for pdbid 6
2022-05-08T22:33:16.114934+08:00
PR00 (PID:1438): Background Media Recovery cancelled with status 16037 [krd.c:27039]
PSR: Background Media Recovery will be reactivated in an attempt to take over PDB side recovery sessions
2022-05-08T22:33:16.119431+08:00
Errors in file /u01/app/oracle/diag/rdbms/db213h1_stdby/DB213H1/trace/DB213H1_pr00_1438.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:1438): Managed Recovery not using Real Time Apply [krsm.c:15876]
Recovery interrupted!
Recovered data files to a consistent state at change 4932082
Stopping change tracking
2022-05-08T22:33:16.347544+08:00
Errors in file /u01/app/oracle/diag/rdbms/db213h1_stdby/DB213H1/trace/DB213H1_pr00_1438.trc:
ORA-16037: user requested cancel of managed recovery operation
2022-05-08T22:33:17.205906+08:00
PDBNEW(6):.... (PID:12667): Side Recovery Complete [krds.c:1584]
2022-05-08T22:33:17.250805+08:00
ARC3 (PID:1364): Archived Log entry 60 added for B-1102494684.T-1.S-78 ID 0x7fd0e6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:17.251185+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-79 [krsr.c:18143]
2022-05-08T22:33:20.411643+08:00
ARC1 (PID:1360): Archived Log entry 61 added for B-1102494684.T-1.S-79 ID 0x7fd1e6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:20.411942+08:00
rfs (PID:12549): Opened LNO:5 for DBID:3870017436 B-1102494684.T-1.S-80 [krsr.c:18143]
2022-05-08T22:33:21.944250+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-81 [krsr.c:18143]
2022-05-08T22:33:21.944465+08:00
ARC2 (PID:1362): Archived Log entry 62 added for B-1102494684.T-1.S-80 ID 0x7fbbe6abae9c LAD:1 [krse.c:4933]


I have extracted below the important entries on the Standby that show the behaviour of PDB Recovery Isolation --- that Oracle at the Standby handles Recovery of the new PDB separately



2022-05-08T22:33:02.900342+08:00
Recovery created pluggable database PDBNEW
PDBNEW(6):Tablespace-SYSTEM during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #22 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_system_k7ho4s6r_.dbf'.
PDBNEW(6):Tablespace-SYSAUX during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #23 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_sysaux_k7ho4s6s_.dbf'.
PDBNEW(6):Tablespace-UNDOTBS1 during PDB create offlined since source is in r/w mode or this is a refresh clone
PDBNEW(6):File #24 added to control file as '/u02/oradata/DB213H1_STDBY/DE81F2476C7F3F80E0538338A8C0063E/datafile/o1_mf_undotbs1_k7ho4s6t_.dbf'.
PDBNEW(6):Tablespace-TEMP during PDB create offlined since source is in r/w mode or this is a refresh clone

PDBNEW(6):PDB Side Media Recovery started for pdbid(6)
PDBNEW(6):.... (PID:12667): Managed Recovery starting Real Time Apply [krsm.c:15865]
PDBNEW(6):max_pdb is 6

PDBNEW(6):Recovery of Online Redo Log: Thread 1 Group 5 Seq 78 Reading mem 0
PDBNEW(6): Mem# 0: /u02/oradata/DB213H1_STDBY/onlinelog/o1_mf_5_k5yt8yjw_.log
PDBNEW(6):PSR[6]:influxScn=4932081,nxtCkptScn=4932081,minScn=4932004,krdsini=0,krsmrs=9082,nof=0,kcvrlc=1102494684,kcvrls=2601843
PDBNEW(6):The merge request has been submitted by side recovery for pdbid 6
2022-05-08T22:33:16.114934+08:00
PR00 (PID:1438): Background Media Recovery cancelled with status 16037 [krd.c:27039]
PSR: Background Media Recovery will be reactivated in an attempt to take over PDB side recovery sessions
2022-05-08T22:33:16.119431+08:00
Errors in file /u01/app/oracle/diag/rdbms/db213h1_stdby/DB213H1/trace/DB213H1_pr00_1438.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:1438): Managed Recovery not using Real Time Apply [krsm.c:15876]
Recovery interrupted!
Recovered data files to a consistent state at change 4932082
Stopping change tracking
2022-05-08T22:33:16.347544+08:00
Errors in file /u01/app/oracle/diag/rdbms/db213h1_stdby/DB213H1/trace/DB213H1_pr00_1438.trc:
ORA-16037: user requested cancel of managed recovery operation
2022-05-08T22:33:17.205906+08:00
PDBNEW(6):.... (PID:12667): Side Recovery Complete [krds.c:1584]

2022-05-08T22:33:17.250805+08:00
ARC3 (PID:1364): Archived Log entry 60 added for B-1102494684.T-1.S-78 ID 0x7fd0e6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:17.251185+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-79 [krsr.c:18143]
2022-05-08T22:33:20.411643+08:00
ARC1 (PID:1360): Archived Log entry 61 added for B-1102494684.T-1.S-79 ID 0x7fd1e6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:20.411942+08:00
rfs (PID:12549): Opened LNO:5 for DBID:3870017436 B-1102494684.T-1.S-80 [krsr.c:18143]
2022-05-08T22:33:21.944250+08:00
rfs (PID:12549): Opened LNO:4 for DBID:3870017436 B-1102494684.T-1.S-81 [krsr.c:18143]
2022-05-08T22:33:21.944465+08:00
ARC2 (PID:1362): Archived Log entry 62 added for B-1102494684.T-1.S-80 ID 0x7fbbe6abae9c LAD:1 [krse.c:4933]
2022-05-08T22:33:37.537823+08:00
all data files of pdbid 6 are brought online.
Started logmerger process
2022-05-08T22:33:37.592170+08:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal
PR00 (PID:12697): Managed Recovery starting Real Time Apply [krsm.c:15865]
max_pdb is 6
2022-05-08T22:33:37.736778+08:00
Parallel Media Recovery started with 2 slaves
2022-05-08T22:33:37.858641+08:00
Stopping change tracking
2022-05-08T22:33:37.914220+08:00
PR00 (PID:12697): Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_78_k7ho5f5y_.arc [krd.c:9408]
2022-05-08T22:33:38.024387+08:00
PR00 (PID:12697): Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_79_k7ho5jc9_.arc [krd.c:9408]
2022-05-08T22:33:38.548017+08:00
PR00 (PID:12697): Media Recovery Log /u01/app/oracle/DB213H1_STDBY/archivelog/2022_05_08/o1_mf_1_80_k7ho5kvg_.arc [krd.c:9408]
2022-05-08T22:33:38.764339+08:00
PR00 (PID:12697): Media Recovery Waiting for T-1.S-81 (in transit) [krsm.c:6185]
2022-05-08T22:33:38.764667+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 81 Reading mem 0




First, it identifies that the Source PDB is in Read-Write mode.
Then it begins a "Side Media Recovery" for the new PDB PDBNEW(6).
Later, it temporarily halts recovery of the full Standby CDB to merge the  "Side Recovery".
Finally, it resumes "normal" Recovery of the Standby from ArchiveLog Sequence 78 onwards.


Categories: DBA Blogs

Pages