Feed aggregator

Fedora 37 and Oracle

Tim Hall - Tue, 2022-11-22 03:34

Fedora 37 was released recently. Here comes the standard warning. Here are the usual things I do when a new version of Fedora comes out. Why do I do this? As mentioned in the first link, Fedora is a proving ground for future versions of RHEL, and therefore Oracle Linux. I like to see what … Continue reading "Fedora 37 and Oracle"

The post Fedora 37 and Oracle first appeared on The ORACLE-BASE Blog.Fedora 37 and Oracle was first posted on November 22, 2022 at 10:34 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

This is the end...

Scott Spendolini - Mon, 2022-11-21 21:43

 ...of my blog at this URL. 

Please follow me over on spendolini.blog for a fresh, updated continuation of this blog.

"Find Definition References" for Page and Field Configurator

Jim Marion - Mon, 2022-11-21 13:33

A student recently asked:

Is there an Edit | Find Definition References equivalent for Page and Field Configurator?

Great question! In Application Designer, we can open a field and choose Edit | Find Definition References to find all usages of that field. Unfortunately, Page and Field Configurator does not have an equivalent. The good news, however, is Page and Field Configurator is metadata driven. In other words, we can create our own "Find Definition References" equivalent by writing SQL. Here is a short example to get you started:

SELECT *
FROM PS_EOCC_CONFIG_FLD
WHERE FIELDNAME = 'DESCR'

The EOCC_CONFIG_FLD record contains the Component name, Record name, and Field name, allowing us to effectively "Find Definition References" for any of those three items.

At JSMpros, we teach PeopleTools Tips like this every week. Be sure to check our website to see what we are offering next!

Row_number() sorts

Jonathan Lewis - Mon, 2022-11-21 11:47

An email on the Oracle-L list server a few days ago described a performance problem that had appeared after an upgrade from 11.2.0.4 to 19c (19.15). A long running statement (insert as select, running parallel 16) that had run to completion in 11g using about 20GB of temporary space (with 50GM read and written) had failed after running for a couple of hours in 19c and consuming 2.5 TB of temporary space, even when the 11g execution plan was recreated through an SQL Profile.

When I took a look at the SQL Monitor report for 19c it turned out that a large fraction of the work done was in an operation called WINDOW CHILD PUSHED RANK which was there to deal with a predicate:

row_number() over(partition by t.ds_no, t.c_nbr order by c.cpcl_nbr desc) = 1

Checking the succesful 11g execution, this operation had taken an input rowsource of 7 billion rows and produced an output rowsource of 70 million rows.

Checking the SQL Monitor report for the failed executions in 19c the “pure” 19c plan had reported 7 billion input rows, 6GB memory and 1TB temp space at the same point, the plan with the 11g profile had reported 10 billion rows, but the operation had not yet reported any output rows despite reporting 9GB as the maximum memory allocation and 1TB as the maximum temp space usage. (Differences in row counts were probably due to the report being run for different dates.)

So, the question to the list server was: “is this a bug in 19c?”

Modelling

It’s a little unfortunate that I couldn’t model the problem in 19c at the time because my 19c VM kept crashing; but I built a very simple model to allow me to emulate the window sort and rank() predicate in an 11g instance, then re-played the model in an instance of 21c.

For the model data I took 50 copies of the first 50,000 rows from view all_objects to produce a table of 2,500,000 rows covering 35,700 blocks and 279 MB, (55,000 / 430 in 21c); then I ran the query below and reported its execution plan with a basic call to dbms_xplan.display_cursor():

select
        /*+ dynamic_sampling(0) */
        owner, max(object_name)
from    (
        select 
                /*+ no_merge */
                owner, object_name 
        from    (
                select 
                        owner, object_name,
                        row_number() over (partition by object_name order by object_type desc) orank 
                from 
                        t1
                )  where orank= 1
        )
group by 
        owner
order by
        owner
/

-------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       |       | 29491 (100)|          |
|   1 |  SORT GROUP BY             |      |     8 |   184 |       | 29491   (9)| 00:02:28 |
|   2 |   VIEW                     |      |  2500K|    54M|       | 28532   (6)| 00:02:23 |
|*  3 |    VIEW                    |      |  2500K|   112M|       | 28532   (6)| 00:02:23 |
|*  4 |     WINDOW SORT PUSHED RANK|      |  2500K|    95M|   124M| 28532   (6)| 00:02:23 |
|   5 |      TABLE ACCESS FULL     | T1   |  2500K|    95M|       |  4821   (8)| 00:00:25 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ORANK"=1)
   4 - filter(ROW_NUMBER() OVER ( PARTITION BY "OBJECT_NAME" ORDER BY
              INTERNAL_FUNCTION("OBJECT_TYPE") DESC )<=1)

Oracle 21c produced the same execution plan – though the row estimate for the VIEW operations (numbers 2 and 3) was a more realistic 46,236 (num_distinct recorded for object_name) compared to the unchanged 2,500,000 from 11g. (Of course it should have been operation 4 that showed the first drop in cardinality.)

With my first build, the timings weren’t what I expected: under 21c the query completed in 3.3 seconds, under 11g it took 11.7 seconds. Most of the difference was due to a large (55MB) spill to temp space that appeared in 11g but not in 21c. This would have been because 11g wasn’t allowed a large enough PGA, so I set the workarea_size_policy to manual and the sort_area_size to 100M, which looks as if it should have been enough to cover the 11g requirement – it wasn’t and I had to grow the sort_area_size to 190 MB before the 11g operation completed in memory, allocating roughly 155MB. By comparison 21c reported an increase of only 19MB of PGA to run the query, claiming that it needed only 4.7MB to handle the critical operation.

For comparison purposes here are the two run-time execution plans, with rowsource execution stats (which messed the timing up a little) and the column projection information; 11g first:

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |      1 |        |       |       | 29491 (100)|      8 |00:00:03.96 |   35513 |       |       |          |
|   1 |  SORT GROUP BY             |      |      1 |      8 |   184 |       | 29491   (9)|      8 |00:00:03.96 |   35513 |  3072 |  3072 | 2048  (0)|
|   2 |   VIEW                     |      |      1 |   2500K|    54M|       | 28532   (6)|  28575 |00:00:04.07 |   35513 |       |       |          |
|*  3 |    VIEW                    |      |      1 |   2500K|   112M|       | 28532   (6)|  28575 |00:00:03.93 |   35513 |       |       |          |
|*  4 |     WINDOW SORT PUSHED RANK|      |      1 |   2500K|    95M|   124M| 28532   (6)|   1454K|00:00:08.82 |   35513 |   189M|  4615K|  168M (0)|
|   5 |      TABLE ACCESS FULL     | T1   |      1 |   2500K|    95M|       |  4821   (8)|   2500K|00:00:10.85 |   35513 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ORANK"=1)
   4 - filter(ROW_NUMBER() OVER ( PARTITION BY "OBJECT_NAME" ORDER BY INTERNAL_FUNCTION("OBJECT_TYPE") DESC )<=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "OWNER"[VARCHAR2,30], MAX("OBJECT_NAME")[30]
   2 - "OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,30]
   3 - "OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,30], "ORANK"[NUMBER,22]
   4 - (#keys=2) "OBJECT_NAME"[VARCHAR2,30], INTERNAL_FUNCTION("OBJECT_TYPE")[19], "OWNER"[VARCHAR2,30], ROW_NUMBER() OVER ( PARTITION BY
       "OBJECT_NAME" ORDER BY INTERNAL_FUNCTION("OBJECT_TYPE") DESC )[22]
   5 - "OWNER"[VARCHAR2,30], "OBJECT_NAME"[VARCHAR2,30], "OBJECT_TYPE"[VARCHAR2,19]

It’s an interesting oddity, and possibly a clue about the excess memory and temp space, that the A-Rows column for the Window Sort operation reports 1,454K rows output when it surely ought to be the final 45,982 at that point. It’s possible to imagine a couple of strategies that Oracle might be following to do the window sort that would reasult in the excess volume appearing, and I’ll leave it to the readers to use their imagination on that one.

And now 21c

--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |      1 |        |       |       | 48864 (100)|     12 |00:00:02.98 |   54755 |  54750 |       |       |          |
|   1 |  SORT GROUP BY             |      |      1 |     12 |   852 |       | 48864   (1)|     12 |00:00:02.98 |   54755 |  54750 |  5120 |  5120 | 4096  (0)|
|   2 |   VIEW                     |      |      1 |  46236 |  3205K|       | 48859   (1)|  45982 |00:00:02.97 |   54755 |  54750 |       |       |          |
|*  3 |    VIEW                    |      |      1 |  46236 |  6547K|       | 48859   (1)|  45982 |00:00:02.97 |   54755 |  54750 |       |       |          |
|*  4 |     WINDOW SORT PUSHED RANK|      |      1 |   2500K|   131M|   162M| 48859   (1)|  45982 |00:00:02.97 |   54755 |  54750 |  5297K|   950K| 4708K (0)|
|   5 |      TABLE ACCESS FULL     | T1   |      1 |   2500K|   131M|       | 15028   (1)|   2500K|00:00:00.28 |   54755 |  54750 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("ORANK"=1)
   4 - filter(ROW_NUMBER() OVER ( PARTITION BY "OBJECT_NAME" ORDER BY INTERNAL_FUNCTION("OBJECT_TYPE") DESC )<=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1; rowset=256) "OWNER"[VARCHAR2,128], MAX("OBJECT_NAME")[128]
   2 - (rowset=256) "OWNER"[VARCHAR2,128], "OBJECT_NAME"[VARCHAR2,128]
   3 - (rowset=256) "OWNER"[VARCHAR2,128], "OBJECT_NAME"[VARCHAR2,128], "ORANK"[NUMBER,22]
   4 - (#keys=2; rowset=256) "OBJECT_NAME"[VARCHAR2,128], "OBJECT_TYPE"[VARCHAR2,23], "OWNER"[VARCHAR2,128], ROW_NUMBER() OVER ( PARTITION BY
       "OBJECT_NAME" ORDER BY INTERNAL_FUNCTION("OBJECT_TYPE") DESC )[22]
   5 - (rowset=256) "OWNER"[VARCHAR2,128], "OBJECT_NAME"[VARCHAR2,128], "OBJECT_TYPE"[VARCHAR2,23]

In this case we see the A-rows from the Window Sort meeting our expectations – but that may be a beneficial side effect of the operation completing in memory.

Optimisation (?)

Given the dramatically different demands for memory for a query that ought to do the same thing in both versions it looks as if 21c may be doing something clever that 11g doesn’t do, or maybe doesn’t do very well, or maybe tries to do but has a bug that isn’t dramatic enough to be obvious unless you’re looking closely.

Here’s a script that I used to build the test data, with scope for a few variations in testing. You’ll notice that the “create table” includes an “order by” clause that is close to the sorting requirement of the over() clause that appears in the query. The results I’ve show so far were for data that didn’t have this clause in place.

rem
rem     Script:         analytic_sort_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2022
rem
rem     Last tested
rem             21.3.0.0
rem             11.2.0.4
rem

create table t1 nologging 
as
select 
        ao.*
from
        (select * from all_objects where rownum <= 50000) ao,
        (select rownum from dual connect by rownum <= 50)
order by
        object_name, object_type -- desc
/

--
--      Stats collection to get histograms
--

begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                method_opt  => 'for all columns size 254'
        );
end;
/

--
-- reconnect here to maximise visibility of PGA allocation
--

connect xxxxxxxx/xxxxxxxx

set linesize 180
set trimspool on
set tab off

-- alter session set workarea_size_policy = manual;
-- alter session set sort_area_size = 199229440;

alter session set events '10046 trace name context forever, level 8';
-- alter session set statistics_level = all;
-- alter session set "_rowsource_execution_statistics"= true;

spool analytic_sort_2

select
        /*  monitoring */
        owner, max(object_name)
from    (
        select 
                /*+ no_merge */
                owner, object_name 
        from    (
                select 
                        owner, object_name,
                        row_number() over (partition by object_name order by object_type desc) orank 
                from 
                        t1
                )  where orank= 1
        )
group by 
        owner
order by
        owner
/

select * from table(dbms_xplan.display_cursor(format=>'cost bytes allstats last projection'));

alter session set events '10046 trace name context off';
alter session set "_rowsource_execution_statistics"= false;
alter session set statistics_level = typical;
alter session set workarea_size_policy = auto;

spool off

The results I’m going to comment on now are the ones I got after running the script as above, then reconnecting and flushing the shared pool before repeat the second half of the script (i.e. without recreating the table).

In 11g, going back to the automatic workarea sizing the session used 37MB of memory and then spilled (only) 3MB to temp. The run time was approximately 3 seconds – which is a good match for the “unsorted” 21c run time. As with the original tests, the value reported in A-rows is larger than we would expect (in this case suspiciously close to twice the correct values – but that’s more likely to be a coincidence than a clue). Interestingly, when I switched to the manual workarea_size_policy and set the sort_area_size to 190MB Oracle said “that’s the optimum memory” and used nearly all of it to complete in memory – for any value less than that (even down to 5MB) Oracle spilled just 3 MB to disk in a one-pass operation. So it looks as if Oracle “knows” it doesn’t need to sort the whole data set, but still uses as much memory as is available to do something before it starts to get clever.

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |      1 |        |       |       | 29491 (100)|      8 |00:00:01.76 |   35523 |   2145 |    331 |       |       |          |         |
|   1 |  SORT GROUP BY             |      |      1 |      8 |   184 |       | 29491   (9)|      8 |00:00:01.76 |   35523 |   2145 |    331 |  2048 |  2048 | 2048  (0)|         |
|   2 |   VIEW                     |      |      1 |   2500K|    54M|       | 28532   (6)|  28575 |00:00:02.00 |   35523 |   2145 |    331 |       |       |          |         |
|*  3 |    VIEW                    |      |      1 |   2500K|   112M|       | 28532   (6)|  28575 |00:00:01.83 |   35523 |   2145 |    331 |       |       |          |         |
|*  4 |     WINDOW SORT PUSHED RANK|      |      1 |   2500K|    95M|   124M| 28532   (6)|  57171 |00:00:02.10 |   35523 |   2145 |    331 |  2979K|   768K|   37M (1)|    3072 |
|   5 |      TABLE ACCESS FULL     | T1   |      1 |   2500K|    95M|       |  4821   (8)|   2500K|00:00:11.84 |   35513 |   1814 |      0 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

In 21c there’s essentially no difference between the sorted and unsorted tests, which suggests that with my data the session had started finding been able to apply its optimisation strategy at the earliest possible moment rather than waiting until it had no alternative but to spill to disc.

--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |      1 |        |       |       | 48864 (100)|     12 |00:00:00.98 |   54753 |  54748 |       |       |          |
|   1 |  SORT GROUP BY             |      |      1 |     12 |   852 |       | 48864   (1)|     12 |00:00:00.98 |   54753 |  54748 |  4096 |  4096 | 4096  (0)|
|   2 |   VIEW                     |      |      1 |  46236 |  3205K|       | 48859   (1)|  45982 |00:00:00.97 |   54753 |  54748 |       |       |          |
|*  3 |    VIEW                    |      |      1 |  46236 |  6547K|       | 48859   (1)|  45982 |00:00:00.97 |   54753 |  54748 |       |       |          |
|*  4 |     WINDOW SORT PUSHED RANK|      |      1 |   2500K|   131M|   162M| 48859   (1)|  45982 |00:00:00.97 |   54753 |  54748 |  5155K|   940K| 4582K (0)|
|   5 |      TABLE ACCESS FULL     | T1   |      1 |   2500K|   131M|       | 15028   (1)|   2500K|00:00:00.42 |   54753 |  54748 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

Assumption

Given the way that 11g reports a very small spill to disc, which stays fairly constant in size no matter how large or small the available PGA allocation is, when the input data is sorted to help the over() clause, and given how large the spill to disc can become when the data is not sorted, I feel that Oracle has an optimisation that discards input rows early in the analytic window sort. But we also have some evidence of a flaw in the code in versions prior to 21c that means Oracle fails to re-use memory that becomes available from rows that have been discarded.

Strategy

I’ve said in the past that if you’re using analytic functions you ought to minimise the size of the data you’re processing before you apply the analytic part. Another step that can help is to make sure you’ve got the data into a (fairly well) sorted order before you reach the analytic part.

In the case of versions of Oracle prior to 21c, it also seems to make sense (if you can arrange it) to minimise the reduce the amount of memory the session is allowed to use for a sort operation, as this will reduce the CPU used by the session and avoid grabbing excess redundant memory that could be used more effectively by other sessions.

Addendum

Just before publishing I found a way of keeping my 19.11.0.0 instance alive long enough to run the tests, then also ran them on an instance of 12.2.0.1. Both versions showed the same pattern of doing a large allocation of memory and large spill to disc when the data was not sorted, and a large allocation of memory but a small spill to disc when the data was sorted.

As a little sanity check I also exported the 19c data and imported it to 21c in case it was a simple variation in the data that allwoed made 21c to operate more efficiently than19c. The change in data made no difference to the way in which 21c handled it, in both cases it called for a small allocation of memory with no spill to disc.

Using Git with PL/SQL in a Shared Development Database

Gerger Consulting - Mon, 2022-11-21 06:18

 

With Gitora 6, developers can work on the same code base (i.e. a package, procedure etc…) in the same database without blocking each other.

Many development teams use a single database for development. Many of them use the same database for testing, as well. They achieve this by using different schemas for different purposes. Gitora 6 enables these teams to create Git repos for these schemas and pull updates between them.

With Gitora 6, you can even create a different schema for every developer and have them merge their code using Git.

Gitora 6 enables you to implement any modern development workflow in a single database.

How does Gitora 6 work?

Gitora 6 introduces a new repo type called Single Schema Repo (SSR). As its name suggests an SSR manages database objects from a single schema. The DDL scripts in SSR’s don’t contain the schema prefixes so that Gitora can execute them in other schemas in the same database.

This enables developers to work on the same package, procedure, function, view etc… (i.e. anything that can be created with the CREATE OR REPLACE command) at the same time, in the same database in different schemas.

An Example

Let’s go through an example: Let’s assume that the team is working on a logistics software and have a schema named LOGISTICS that stores all their database objects. The team can create a schema (or already have one) called LOGISTICS_TEST in the same database. Here are the steps the team needs to follow so that they can pull their changes to LOGISTICS_TEST.

  1. Create a single schema Gitora repo that manages the LOGISTICS schema. Let’s call it the REPO_LOGISTICS.
  2. Add all relevant database objects in the LOGISTICS schema to the LOGISTICS_REPO.
  3. Create another single schema Gitora repo that manages the LOGISTICS_TEST schema. Let’s call it the REPO_LOGISTICS_TEST
  4. Pull from the REPO_LOGISTICS to REPO_LOGISTICS_TEST

That’s it. That’s all there is to it. From this point on, any change you make to the code in the LOGISTICS schema can be pulled to the LOGISTICS_TEST schema using Gitora (and vice versa).

Single Schema Repos can also be used to create separate development environments in the same database for every developer in the team.

Multiple Developers, Same Database

Assuming we already have the LOGISTICS schema and the REPO_LOGISTICS repo from the previous example, here is how that would work:

  1. Create a schema for each developer: LOGISTICS_JOE, LOGISTICS_MARY, LOGISTICS_PAUL.
  2. Create a single schema Gitora repo for each schema. Let’s call them REPO_LOGISTICS_JOE, REPO_LOGISTICS_MARY, REPO_LOGISTICS_PAUL respectively.
  3. Pull from the REPO_LOGISTICS to REPO_LOGISTICS_JOE, REPO_LOGISTICS_MARY and REPO_LOGISTICS_PAUL.

From this point on, all three developers can work in their own schema and edit any package, procedure, view etc… freely, without overwriting each other’s changes or being affected by them. Using Gitora, they can create new branches in their own repo, for features or bugs they work on without affecting anyone else.

When the time comes to put it all together, they can use Gitora to merge their changes.

We wrote a few articles to get you started:

Creating Single Schema Repos.

How to implement agile development in a single database

How to manage changes to tables with Single Schema Repos

Before Gitora 6, agile database development with Gitora required multiple databases to implement. With Gitora 6 you can achieve it in a single database.

We are incredibly excited to release Gitora 6. We believe it will bring a massive productivity boost to Oracle database development teams worldwide. We can’t wait for you to try it.

Gitora 6 is available now. You can download it from this link.

Categories: Development

Calling JavaScript from Streamlit/Python

Andrejus Baranovski - Sun, 2022-11-20 14:30
Streamlit is Python framework, it helps to build UI with low code approach. Code runs on server and there is no functionality, which would allow to inject client side events with JavaScript. Luckily there is a 3rd-party Streamlit component, it allows to make JavaScript calls from server side Python code. Response from such JavaScript call is received within the same Streamlit execution, this makes it possible to use values returned from JavaScript in Python logic. In this example, I show one liner JavaScript call to get UI screen width, to be able to calculate layout structure adjustments in Python, based on the screen size.

 

VirtualBox 7.0.4 and Vagrant 2.3.3 – Another VirtualBox Fail (For Me)…

Tim Hall - Sun, 2022-11-20 04:12

VirtualBox 7.0.4 VirtualBox 7.0.4 has been released. The downloads and changelog are in the normal places. From my previous posts on VirtualBox 7.0.x you will know I’ve been having problems with it. They all seem to come down to networking. I can often, but not always, start up and existing VM, but if I try to build a new VM … Continue reading "VirtualBox 7.0.4 and Vagrant 2.3.3 – Another VirtualBox Fail (For Me)…"

The post VirtualBox 7.0.4 and Vagrant 2.3.3 – Another VirtualBox Fail (For Me)… first appeared on The ORACLE-BASE Blog.VirtualBox 7.0.4 and Vagrant 2.3.3 – Another VirtualBox Fail (For Me)… was first posted on November 20, 2022 at 11:12 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Recreate a Patroni replica using pgBackRest

Yann Neuhaus - Thu, 2022-11-17 02:28

In my last blog we had a look on how to bootstrap a complete Patroni Cluster from pgBackRest. But there is also the possibility to recreate only one Patroni node using pgBackRest. In this blog we will have a look on all the things we need to change to recreate a node successfully from backup.

First of all, let’s assume we have a three node Patroni Setup using etcd as key value store and pgBackRest is used to backup the database.

 postgres@hero2:/home/postgres/ [PG1] patronictl list
+--------+----------------+---------+---------+----+-----------+
| Member | Host           | Role    | State   | TL | Lag in MB |
+ Cluster: PG1 (7166642010148587394) ---------+----+-----------+
| hero1  | 192.168.22.251 | Leader  | running |  1 |           |
| hero2  | 192.168.22.252 | Replica | running |  1 |         0 |
| hero3  | 192.168.22.253 | Replica | running |  1 |         0 |
+--------+----------------+---------+---------+----+-----------+

 postgres@hero2:/home/postgres/ [PG1] etcdctl member list
1c83efe87807cd7b, started, hero2, http://192.168.22.252:2380, http://192.168.22.252:2379, false
b059fedab560f470, started, hero3, http://192.168.22.253:2380, http://192.168.22.253:2379, false
ecee86d6079e2735, started, hero1, http://192.168.22.251:2380, http://192.168.22.251:2379, false

To start we check if there is a backup available in our pgBackRest repository.

postgres@backup_hero:/home/postgres/ [pg14] pgbackrest info
stanza: hero2hero
    status: ok
    cipher: none

    db (current)
        wal archive min/max (14): 000000010000000000000001/00000001000000000000000C

        full backup: 20221116-165815F
            timestamp start/stop: 2022-11-16 16:58:15 / 2022-11-16 16:59:04
            wal start/stop: 00000001000000000000000A / 00000001000000000000000C
            database size: 105.0MB, database backup size: 105.0MB
            repo1: backup set size: 30.8MB, backup size: 30.8MB

So as everything looks healthy we can start to change the configuration of the Patroni cluster. We have to make this change in two steps.

In the first step edit the configuration using edit-conf. Add the recovery_conf section between use_slots and retry_timeout. Safe your changes.

postgres@hero2:/home/postgres/ [PG1] patronictl edit-config
  use_slots: true
recovery_conf:
  restore_command: pgbackrest --stanza=hero2hero archive-get %f %p
  restore_target_timeline: latest
retry_timeout: 10
ttl: 30

As the next step, change the patroni.yml. We add the create_replica_methods to the file. As we define pgbackrest and basebackup, it will try to recreate the replica from pgBackRest first and if this is not possible it will recreate from basebackup of the master.

This change should be done at least on the node you want to recreate using pgBackRest but it’s better to do it on all three nodes to make sure that you are prepared in any case. Be careful with this change and make sure you add enough spaces at the beginning of the line. Add the section create_replica_methods below the parameters section.

postgresql:
  ..
  ..
  parameters:
    unix_socket_directories: '/tmp'
  create_replica_methods:
    - pgbackrest
    - basebackup
  pgbackrest:
    command: pgbackrest --stanza=hero2hero restore
    keep_data: True
    no_params: True
  basebackup:
    checkpoint: 'fast'

Once all the changes are done, you need to reload patroni

sudo systemctl reload patroni

Now we can destroy one replica and recreate it.

postgres@hero2:/home/postgres/ [pg14] sudo systemctl stop patroni
postgres@hero2:/home/postgres/ [pg14] rm -rf /u02/pgdata/14/PG1/*
postgres@hero2:/home/postgres/ [pg14] ls -al /u02/pgdata/14/PG1/
total 0
drwxr-x---. 2 postgres postgres  6 Nov 16 17:16 .
drwxr-x---. 3 postgres postgres 17 Sep  5 10:20 ..
postgres@hero2:/home/postgres/ [pg14] sudo systemctl start patroni

This does not really show as if the replica is really recreated from pgBackRest, but if we check the logfile, we get a self explaining message, that pgBackRest was used (see last line)

postgres@hero2:/home/postgres/ [pg14] sudo journalctl -u patroni -f
-- Logs begin at Fri 2022-09-16 08:50:46 CEST. --
Nov 16 17:16:37 hero2 systemd[1]: Starting dbi services patroni service...
Nov 16 17:16:37 hero2 systemd[1]: Started dbi services patroni service.
Nov 16 17:16:38 hero2 patroni[38394]: 2022-11-16 17:16:38,085 INFO: Selected new etcd server http://192.168.22.253:2379
Nov 16 17:16:38 hero2 patroni[38394]: 2022-11-16 17:16:38,094 INFO: No PostgreSQL configuration items changed, nothing to reload.
Nov 16 17:16:38 hero2 patroni[38394]: 2022-11-16 17:16:38,116 INFO: Lock owner: hero1; I am hero2
Nov 16 17:16:38 hero2 patroni[38394]: 2022-11-16 17:16:38,120 INFO: trying to bootstrap from leader 'hero1'
Nov 16 17:16:42 hero2 patroni[38394]: 2022-11-16 17:16:42,532 INFO: replica has been created using pgbackrest
..
..
..

The implementation of that behavior is quite easy and fast to setup. As already mentioned, keep in mind to put enough spaces in the patroni.yml otherwise the replica won’t be created using pgBackRest. So in case you still see this entry in your logfile

INFO: replica has been created using basebackup
INFO: bootstrapped from leader 'hero1'

Have a look at your patroni.yml once again!

L’article Recreate a Patroni replica using pgBackRest est apparu en premier sur dbi Blog.

Supported database services

Tom Kyte - Wed, 2022-11-16 16:46
Will this service be available for Exacs?
Categories: DBA Blogs

How to call external sql script from within a PL/SQL block

Tom Kyte - Wed, 2022-11-16 16:46
Hi Tom, This is probably super simple once you show me how, but I haven't been able to find the answer in the documentation. How do I call an external SQL script from within PL/SQL? I know I can embed the actual SQL text inside the PL/SQL block, but I would rather keep the SQLs in a separate file to be called externally when I need to outside the PL/SQL code. E.g. BEGIN -- Check for some condition. -- if condition true then -- execute foo.sql END; / In SQL*PLUS, we execute external scripts using one @, e.g. @foo.sql. And within a script, we execute other scripts with two @, e.g. @@bar.sql. Not sure how to do the same within a PL/SQL block. Thanks, -Peter
Categories: DBA Blogs

Index MIN/MAX Optimization

Tom Kyte - Wed, 2022-11-16 16:46
Dear Team, I have a logging table with the following structure: <code> SQL> desc t Name Null? Type ----------------------------------------------------- -------- ------------------------------------ LOG_ID NUMBER TRACEID VARCHAR2(250 CHAR) TYPE VARCHAR2(250 CHAR) NODE VARCHAR2(250 CHAR) URL VARCHAR2(4000 CHAR) TOKEN VARCHAR2(4000 CHAR) METHOD VARCHAR2(250 CHAR) TIMESTAMPREQ VARCHAR2(100 CHAR) BODY CLOB RESPONSE CLOB TIMESTAMPRES VARCHAR2(100 CHAR) REC_DATE DATE </code> In addition to other indexes, there is one index on columns (Trunc(Rec_Date), Type, Node, Method). I try to get min or max of trunc(rec_date) but surprisingly it doesn't uses the index min/max optimization. Below is the command and trace file output: <code> SELECT min(trunc(rec_date)) FROM t where trunc(rec_date) is not null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.09 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 94.40 567.28 256176 507776 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 94.43 567.38 256176 507777 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 242 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT AGGREGATE (cr=507776 pr=256176 pw=0 time=0 us starts=567286694) 4176565 4176565 4176565 TABLE ACCESS FULL T (cr=507776 pr=256176 pw=0 time=86468 us starts=709057596 cost=86468 size=34748199 card=3860911) SELECT min(trunc(rec_date)) FROM t where rec_date is not null call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 5.84 17.41 5 259917 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 5.84 17.41 5 259918 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 242 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT AGGREGATE (cr=259917 pr=5 pw=0 time=0 us starts=17415568) 4184139 4184139 4184139 TABLE ACCESS FULL T (cr=259917 pr=5 pw=0 time=86242 us starts=106246737 cost=86242 size=34748199 card=3860911) </code> Can you please help me understanding why t...
Categories: DBA Blogs

CREATE VIEW . . . BEQUEATH DEFINER vs. WITH GRANT OPTION

Tom Kyte - Wed, 2022-11-16 16:46
I have a view, cust.viewname, that references a table in a different schema I'll call "data". According to Oracle documentation for CREATE VIEW, the optional clause BEQUEATH DEFINER causes the view to run using the permissions of the view owner. This is the default if not specified. The view schema has SELECT privs to a table in the data schema, but not WITH GRANT OPTION. The view owner has access to the data table, but when I try to create the view, it throws <b>ORA-01720: grant option does not exist for 'DATA.TABLENAME'</b> <code>CREATE OR REPLACE VIEW cust.viewname BEQUEATH DEFINER AS SELECT field1, field2, ..., fieldN FROM data.tablename;</code> If the view operates as the owner, and the view owner has access to the referenced objects, then why should grants to objects in other schemas require WITH GRANT OPTION? Is there a way around WITH GRANT OPTION? Our cloud provider refuses to allow WITH GRANT OPTION, but we have an ongoing need to create views in CUST that reference objects in other schemas and will be used by users other than CUST. Thanks in advance.
Categories: DBA Blogs

Lost Space

Jonathan Lewis - Wed, 2022-11-16 06:56

I’ve just discovered that the space management bitmaps for the tablespace I normally use in my 21c tests are broken. In a tablespace that’s supposed to be completely empty a query of dba_free_space shows 4 gaps totalling several thousand blocks:

SQL> select * from dba_free_space where tablespace_name = 'TEST_8K_ASSM';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
TEST_8K_ASSM                           13        128     327680         40           13
TEST_8K_ASSM                           13        216    1376256        168           13
TEST_8K_ASSM                           13       1792     720896         88           13
TEST_8K_ASSM                           13       1896     196608         24           13
TEST_8K_ASSM                           13       9600  969932800     118400           13

Of course I ran my script to drop all segments and purge the recyclebin when I first saw this, but that didn’t help, and a query against dba_segments showed no segments, and a query against seg$ showed nothing in the file. So somehow the bits are bust.

Fortunately there’s a dbms_space_admin package with a procedure tablespace_verify() that I’ve wanted to test for some time – the documentation is a little sparse about how it works. So here’s a cut-and-paste of my first (and second) call to the procedure, executing from the SYS schema and passing in the tablespace name:

SQL> execute dbms_space_admin.tablespace_verify ('TEST_8K_ASSM')
BEGIN dbms_space_admin.tablespace_verify ('TEST_8K_ASSM'); END;

*
ERROR at line 1:
ORA-20000: BitMap entry partially used with no Extent Map entry
TSN 6: Range RelFno 13: ExtNo: 32702 BeginBlock: 0 EndBlock: 4194303
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 83
ORA-06512: at line 1

SQL> execute dbms_space_admin.tablespace_verify ('TEST_8K_ASSM')
BEGIN dbms_space_admin.tablespace_verify ('TEST_8K_ASSM'); END;

*
ERROR at line 1:
ORA-20000: BitMap entry partially used with no Extent Map entry
TSN 6: Range RelFno 13: ExtNo: 32766 BeginBlock: 0 EndBlock: 4194303
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 83
ORA-06512: at line 1


The output isn’t promising – but we can, at least, see that it’s the right RelFno, and the Extno: seems to have moved on by 64 (which is a nice number in an abstract, computational way), but what might the Extno: be? And I know that I’ve only got 128,000 blocks in the file and it’s not set to auto-extend so that EndBlock: value is a little worrying.

Just to add a little more confusion – the next few calls reported the ExtNo: as 0, then stuck at 32,766. So it probably wasn’t walking the files bitmap blocks as I first guessed.

What to do next? In my case I could throw the tablespace away – there was nothing in it, and even if there were I could have recreated it very easily – so I was happy to try the next dbms_space_admin feature: tablespace_fix_bitmaps(). Here’s the declaration:

  procedure tablespace_fix_bitmaps(
        tablespace_name         in    varchar2 ,
        dbarange_relative_file  in    positive ,
        dbarange_begin_block    in    positive ,
        dbarange_end_block      in    positive ,
        fix_option              in    positive
  );
  --
  --  Marks the appropriate dba range (extent) as free/used in bitmap
  --  Input arguments:
  --   tablespace_name         - name of tablespace
  --   dbarange_relative_file  - relative fileno of dba range (extent)
  --   dbarange_begin_block    - block number of beginning of extent
  --   dbarange_end_block      - block number (inclusive) of end of extent
  --   fix_option              - TABLESPACE_EXTENT_MAKE_FREE or
  --                             TABLESPACE_EXTENT_MAKE_USED

Again the documentation is a little sparse, so I’m just going to cross my fingers and hope for the best – proceeding a little cautiously. Looking at the report of free space I can infer from the first two lines that the bits for blocks 168 (128 + 40) to 215 (216 – 1) are marked as used. So I’ll try to pass that information into the procedure call:

set serveroutput on
set linesize 132
set trimspool on
set tab off


begin
        dbms_space_admin.tablespace_fix_bitmaps(
                tablespace_name         => 'TEST_8K_ASSM',
                dbarange_relative_file  => 13,
                dbarange_begin_block    => 168,
                dbarange_end_block      => 215,
                fix_option              => dbms_space_admin.TABLESPACE_EXTENT_MAKE_FREE
        );
end;
/

PL/SQL procedure successfully completed.

SQL> select * from dba_free_space where tablespace_name = 'TEST_8K_ASSM';

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
TEST_8K_ASSM                           13        128    2097152        256           13
TEST_8K_ASSM                           13       1792     720896         88           13
TEST_8K_ASSM                           13       1896     196608         24           13
TEST_8K_ASSM                           13       9600  969932800     118400           13

Comparing the new results from dba_free_space we can see that we’ve eliminated the “used” chunk that was between the first two free chunks and now have a single free chunk stretching from block 128 to block 383. So now we rinse and repeat – and we could use dba_free_space to help by generating a list of begin and end blocks – we might even consider writing a query to drive a cursor loop (being very careful to allow for multi-file tablespaces, which I haven’t done):

select
        relative_fno, block_id, block_id + blocks begin_block,
        lead(block_id) over (order by relative_fno, block_id) - 1  end_block
from
        dba_free_space
where
        tablespace_name = 'TEST_8K_ASSM'
order by
        relative_fno, block_id
/

 RELATIVE_NO   BLOCK_ID BEGIN_BLOCK  END_BLOCK
------------ ---------- ----------- ----------
          13        128         384       1791
          13       1792        1880       1895
          13       1896        1920       9599
          13       9600      128000

After three more calls to tablespace_fix_bitmaps() this is the result I got from my query against dba_free_space – followed by a call to tablespace_verify():

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
TEST_8K_ASSM                           13        128 1047527424     127872           13

SQL>  execute dbms_space_admin.tablespace_verify ('TEST_8K_ASSM')

PL/SQL procedure successfully completed.

Summary

After finding a tablespace that should have shown nothing but free space along its whole length (and checking the recyclebin, and the underlying seg$ table) I called dbms_space_admin.tablespace_verify() to see what it thought was going on and it reported an inconsistency between the tablespace (file) bitmap and segment bitmaps (in this case because there were no segment bitmaps when the file bitmap said there ought to be).

Starting from a query against dba_free_space I worked out the ranges of blocks that were marked in the file bitmap as used when they shouldn’t have been, and called dbms_space_admin.tablespace_fix_bitmaps() for each range.

After fixing all the bad ranges I called tablespace_verify() again to see if it had any more complaints,, and got an empty report.

Footnotes

The documentation is not user-friendly, and it would be nice to have some comments in the manaul (or dbmsspc.sql script) describing possible outputs. On the other hand I managed to avoid reading the documentation carefully enough anyway, because it wasn’t until I started searching MOS for better documentation that I realised I should have used the ASSM version of verify

execute dbms_space_admin.assm_tablespace_verify ('TEST_8K_ASSM', dbms_space_admin.ts_verify_bitmaps)

This procedure might have reported sensible information for the Extno, BeginBlock and EndBlock. But it was too late to find out – I’ll just wait for the next corruption to happen.

There is one circumstance where you might see multiple chunks in dba_free_space when there are no segments allocated, but with no gaps between chunks – if Oracle has to “grow” the bitmap for a file then the separate chunks of the bitmap report their freespace separately.

Another possibility for multiple free space chunks when there are no (ordinary) segments is if you’ve moved the tablespace bitmap or converted a dictionary managed tablespace to a locally managed tablespace – again a rare occurrence – in which case the tablespace bitmap will be in a “nearly-hidden” segment.

Oracle Identity Access Manager for .NET

Tom Kyte - Tue, 2022-11-15 22:26
Hello, I need some guidance on using Oracle's Identity Access Manager and an OAuth Provider in an ASP.NET Core/Framework application? Could you please point me in the right direction, either examples or docs. Thanks.
Categories: DBA Blogs

opt_estimate 4a

Jonathan Lewis - Tue, 2022-11-15 05:21

I wrote a batch of notes about the opt_estimate() hint a couple of years ago, including one where I explained the option for using the hint to specify the number of rows in a query block. I’ve just come across a particular special case for that strategy that others might find a use for. It’s something to do whant using the “select from dual … connect by” trick for multiplying rows.

Here’s a little data to model the idea – I’ve used the all_tables view to generate some “well-known” data since I want to add a tiny bit of complexity to the query while still leaving it easy to understand the index. The results from this demonstration come from Oracle 21.3.0.0, and I’ve included the hint /*+ no_adaptive_plan */ to stop Oracle from getting too clever during optimisation.

rem
rem     Script:         opt_estimate_dual.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2022
rem
rem     Last tested 
rem             21.3.0.0
rem

create table tables_table as select * from all_objects where object_type = 'TABLE';
create table objects_table as select * from all_objects;

alter table objects_table add constraint ot_pk primary key(object_id);

begin
        dbms_stats.gather_table_stats(
                ownname    => user,
                tabname    => 'tables_table',
                method_opt => 'for columns size 60 owner'
        );
end;
/

set serveroutput off

with driver as (
        select  /*+ materialize */
                tt.owner, tt.object_id, v1.rn
        from    tables_table tt,
                (
                select
                        /*+  opt_estimate(query_block scale_rows=10) */
                        rownum rn
                from    dual
                connect by
                        level <= 10
                ) v1
        where
                tt.owner = 'OUTLN'
)
select  /*+ no_adaptive_plan */
        dr.rn, dr.owner, dr.object_id,
        ot.object_id, ot.owner, ot.object_type, ot.object_name
from
        driver dr,
        objects_table   ot
where
        ot.object_id = dr.object_id
/

select * from table(dbms_xplan.display_cursor(format => 'hint_report'));


In my system tables_table holds 727 rows and objects_table holds 58383 rows. Three rows in tables_table correspond to tables owned by user ‘OUTLN’ which means I expect the driver CTE (common table expression / “with” subquery) to generate 30 rows and, given the join on unique id, the query to return 30 rows.

I’ve used the /*+ materialize */ hint to force Oracle to create an in-memory temporary table for the driver CTE, the /*+ no_adaptive_plan */ hint to stop Oracle from getting too clever during optimisation, and the critical /*+ opt_estimate() */ hint to help the optimizer understand the effect of my “connect by” on dual. Here’s the execution plan I get if I omit that last hint:

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |    14 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6632_31D19D4 |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN                  |                            |     3 |    78 |     9   (0)| 00:00:01 |
|   4 |     VIEW                                 |                            |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |      COUNT                               |                            |       |       |            |          |
|   6 |       CONNECT BY WITHOUT FILTERING       |                            |       |       |            |          |
|   7 |        FAST DUAL                         |                            |     1 |       |     2   (0)| 00:00:01 |
|   8 |     BUFFER SORT                          |                            |     3 |    39 |     9   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS FULL                   | TABLES_TABLE               |     3 |    39 |     7   (0)| 00:00:01 |
|  10 |   NESTED LOOPS                           |                            |     3 |   453 |     5   (0)| 00:00:01 |
|  11 |    NESTED LOOPS                          |                            |     3 |   453 |     5   (0)| 00:00:01 |
|  12 |     VIEW                                 |                            |     3 |   276 |     2   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6632_31D19D4 |     3 |    78 |     2   (0)| 00:00:01 |
|* 14 |     INDEX UNIQUE SCAN                    | OT_PK                      |     1 |       |     0   (0)|          |
|  15 |    TABLE ACCESS BY INDEX ROWID           | OBJECTS_TABLE              |     1 |    59 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   9 - filter("TT"."OWNER"='OUTLN')
  14 - access("OT"."OBJECT_ID"="DR"."OBJECT_ID")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   0 -  STATEMENT
           -  no_adaptive_plan

   2 -  SEL$1
           -  materialize


I’ve highlighted operations 4 and 8 in the plan: operation 4 is the view of dual that has generated 10 rows – unfortunately the optimizer has only considered the stats of the dual table, and hasn’t factored in the effects of the “connect by with rownum”. Operation 8 shows us that the optimizer has (correctly, thanks to the histogram I requested) estimated 3 rows for the tablescan of tables_table. The result of these two estimates is that operation 3 reports an estimate of 3 ( = 3 * 1 ) rows to be used in probing objects_table.

This is the plan after enabling the /*+ opt_estimate() */ hint:

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |    45 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6633_31D19D4 |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN                  |                            |    30 |   780 |    13   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL                    | TABLES_TABLE               |     3 |    39 |     7   (0)| 00:00:01 |
|   5 |     BUFFER SORT                          |                            |    10 |   130 |     6   (0)| 00:00:01 |
|   6 |      VIEW                                |                            |    10 |   130 |     2   (0)| 00:00:01 |
|   7 |       COUNT                              |                            |       |       |            |          |
|   8 |        CONNECT BY WITHOUT FILTERING      |                            |       |       |            |          |
|   9 |         FAST DUAL                        |                            |     1 |       |     2   (0)| 00:00:01 |
|  10 |   NESTED LOOPS                           |                            |    30 |  4530 |    32   (0)| 00:00:01 |
|  11 |    NESTED LOOPS                          |                            |    30 |  4530 |    32   (0)| 00:00:01 |
|  12 |     VIEW                                 |                            |    30 |  2760 |     2   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6633_31D19D4 |    30 |   780 |     2   (0)| 00:00:01 |
|* 14 |     INDEX UNIQUE SCAN                    | OT_PK                      |     1 |       |     0   (0)|          |
|  15 |    TABLE ACCESS BY INDEX ROWID           | OBJECTS_TABLE              |     1 |    59 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("TT"."OWNER"='OUTLN')
  14 - access("OT"."OBJECT_ID"="DR"."OBJECT_ID")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
   0 -  STATEMENT
           -  no_adaptive_plan

   2 -  SEL$1
           -  materialize


There are three things that stand out in this report.

  • I’ve highlighted operations 4 and 6: operation 4 is the tablescan of tables_table that correctly estimates 3 rows; operation 6 is the view operation that now correctly estimates 10 rows.
  • With the correct estimate for the view the estimate for the join to objects_table is now correct and the join order for the merge join cartesian at operation 3 has been reversed.
  • The Hint Report tells us that the opt_estimate() hint is not (always) an optimizer hint! This is a real pain because when the opt_estimate() hints you’ve tried to use don’t appear to work it’s not easy to work out what you’ve done wrong.

To make a point, I can take the demo a little further by changing the /*+ opt_estimate() */ hint to scale_rows=120. Here’s the body of the resulting plan:

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |   369 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D663A_31D19D4 |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN                  |                            |   360 |  9360 |    13   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                    | TABLES_TABLE               |     3 |    39 |     7   (0)| 00:00:01 |
|   5 |     BUFFER SORT                          |                            |   120 |  1560 |     6   (0)| 00:00:01 |
|   6 |      VIEW                                |                            |   120 |  1560 |     2   (0)| 00:00:01 |
|   7 |       COUNT                              |                            |       |       |            |          |
|   8 |        CONNECT BY WITHOUT FILTERING      |                            |       |       |            |          |
|   9 |         FAST DUAL                        |                            |     1 |       |     2   (0)| 00:00:01 |
|  10 |   HASH JOIN                              |                            |   360 | 54360 |   356   (1)| 00:00:01 |
|  11 |    VIEW                                  |                            |   360 | 33120 |     2   (0)| 00:00:01 |
|  12 |     TABLE ACCESS FULL                    | SYS_TEMP_0FD9D663A_31D19D4 |   360 |  9360 |     2   (0)| 00:00:01 |
|  13 |    TABLE ACCESS FULL                     | OBJECTS_TABLE              | 58383 |  3363K|   354   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

The earlier plans used a nested loop join into objects_table. In this plan we can see at operation 10 that the optimizer has selected a hash join because the larger row estimate for the CTE has increased the cost of the query beyond the inflection point between nested loop and hash joins.

Summary

If you need to use the “connect by” in an inline view then you may find that the optimizer gets a very bad estimate of the number of rows the view definition will generate and that an /*+ opt_estimate() */ hint in the view using the “scale_rows=nnn” option will produce better estimates of cardinality, hence a better plan.

Footnote

In this particular case where I’ve used the dual table by itself in an inline view I could have used the rows=NNN” option to get the same effect.

In any case I could have added a /*+ qb_name() */ hint to the inline view, and includes a qualifying “@qb” in the /*+ opt_estimate() */ hint.

Using hints is hard, especially when they’re not documented. There is a lot more to learn about this hint; for example, telling the optimizer about the size of a rowsource doesn’t help if it’s going to use its estimate of distinct values in the next steps of the plan – a correction you’ve managed to introduce at one point may disappear in the very next optimizer calculation.

This catalogue lists more articles on the opt_estimate() hint and its relatives.

Analyze and DBMS_STATS

Tom Kyte - Tue, 2022-11-15 04:06
Tom, Could you please tell me if there are any other important differences, advantages with DBMS_STATS over ANALYZE other than the points listed below. 1. DBMS_STATS can be done in parallel 2. Monitoring can be done and stale statistics can be collected for changed rows using DBMS_STATS. Thanks, Suresh
Categories: DBA Blogs

How to recover all users

Tom Kyte - Mon, 2022-11-14 09:46
I was deleting some dump file inside oracle user, accidentally I hit rm -rf /* , then permission denied error came, after that i am unable to login db, all db's property file deleted, now i am login to oracle user its coming link -bash-4.2$. inside oracle home only trace file is there other's are deleted, please help me to recover.
Categories: DBA Blogs

APEX 22.2 : Vagrant and Docker Updates

Tim Hall - Mon, 2022-11-14 05:10

I know it’s hard to believe that anything happened last week other than the implosion of Twitter, but APEX 22.2 was also released. As normal, this resulted in a bunch of updates to my builds. Vagrant All relevant Vagrant builds were updated to include APEX 22.2. Many had been updated recently to bring them in … Continue reading "APEX 22.2 : Vagrant and Docker Updates"

The post APEX 22.2 : Vagrant and Docker Updates first appeared on The ORACLE-BASE Blog.APEX 22.2 : Vagrant and Docker Updates was first posted on November 14, 2022 at 12:10 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Oracle Analytics Cloud November 2022 Update: the New Features, Ranked

Rittman Mead Consulting - Mon, 2022-11-14 05:06

The November 2022 Update for Oracle Analytics Cloud came out few days ago and in this blog post I'm going to have a look at all the new features it includes. If you are also interested in a comprehensive list of the defects fixed by the update, please refer to Doc ID 2832903.1.

10. Non-SSL, Kerberos Connections to Hive

Non-SSL connections to a Hive database using Kerberos network authentication protocol are now supported.

9. Enhanced Data Profiling

The random sampling depth and methodology that drives augmented features such as quality insights and semantic recommendations have been improved.

8. Toggle Sample Data Previews in Metadata View

This feature allows users to switch off sample data previews in Metadata view to stop generating the sample values displayed in the Sample Values column and improve their user experience when previews are not required. The toggle switch is displayed at the bottom right of the Metadata view (Figure 1).

Figure 1. The toggle to switch off sample data previews.7. Blurred Thumbnails

Workbook thumbnails displayed on the home page are now blurred to protected sensitive data from being exposed to users that don't have the same access as data authors (Figure 2).

Figure 2. Blurred workbook thumbnails.

Unfortunately, the blur effect is not sufficient to make performance tile content completely indistinguishable. For additional security, administrators can disable workbook thumbnails altogether by switching off the Save Workbook Thumbnail option in the System Settings section of Data Visualization Console (Figure 3).

Figure 3. The new option to disable workbook thumbnails altogether.

When thumbnails are globally allowed, content authors can show or hide the thumbnail for an individual workbook as required. Click Menu on the workbook toolbar, select Workbook Properties and set Save thumbnails to On or Off (Figure 4).

Figure 4. Showing or hiding the thumbnail for an individual workbook.6. Transform Data to Dates More Easily

Unrecognized dates can be transformed more easily using single-click Convert to Date recommendations.

5. Control Filter Interactions from the Filters Bar

When your workbook contains many filters, the Limit Values By icon in the filters bar can be used to toggle between limited filter selection values and unlimited filter selection values (Figure 5).

Figure 5. Control workbook filter interactions from the filter bar.4. Customize the Workbook Header Bar

Authors can show or hide the header bar, and customize it. To customize the header bar color, text, font, and image, go to the Properties panel in the Present page of the workbook and select the Presentation tab (Figure 6). End users can then view the header bar as configured by the author.

Figure 6. The options to customize the workbook header bar.3. Filter Data Using a Slider

Slider dashboard filter can be added to a canvas to animate visualizations and show dynamically how your data changes over a given dimension such as time (Clip 1).

0:00
/
Clip 1. Filtering data using a slider dashboard filter.

The feature is similar to a section displayed as slider in Analytics, but more powerful: it generates more efficient queries, with a single object you can interact with multiple visualizations at the same time, and it supports tables and pivot tables as well. To make the most of it, I recommend to set up a custom End for Values Axis of your visualizations.

2. Select Columns for Auto Insights

By default, Oracle Analytics profiles all columns in a dataset when it generates insights. Data columns to use for Auto Insights can now be selected by content authors to fine-tuning the insights that Oracle Analytics generates for you and focus only on the most useful ones (Figure 7).

Figure 7. Selecting columns for Auto Insights.1. Export Table Visualizations to Microsoft Excel

Formatted data from table and pivot table visualizations (up to 25,000 rows) can now be exported to the Microsoft Excel (XLSX) format (Figure 8).

Figure 8. Exporting table visualizations to Microsoft Excel.

This feature is currently available for preview. Administrators can enable it by switching on the Preview Excel Export option in the System Settings section of Data Visualization Console (Figure 9).

Figure 9. The new preview option to enable Excel export.Conclusion

The November 2022 Update includes several new features (and fixes) for Oracle Analytics Cloud that significantly improve Data Visualization and make it more user friendly.

If you are looking into Oracle Analytics Cloud and want to find out more, please do get in touch or DM us on Twitter @rittmanmead. Rittman Mead can help you with a product demo, training and assist within the migration process.

Categories: BI & Warehousing

ODA – HDD manuel firmware patching

Yann Neuhaus - Mon, 2022-11-14 04:50

I recently patched ODAs X7-2-HA and X8-2-HA from version 19.8 to version 19.12 and then 19.16 at one of our customer environment. I faced some HDD firmware that was not patched during storage patching, and I had to manually patched them. Through this blog I wanted to share how I performed this, hoping that it might help you.

All the HDD firmware disks were in A3Y1 version and the last available one was A680. There was no new version between 19.12 and 19.16, so I decided to manually patched them at the end, once the ODAs were running 19.16 version.

The describe component command will show that following disks need to be updated: c1d0,c1d1,c1d2,c1d3,c1d4,c1d5,c1d6,c1d7,c1d8,c1d9,c1d10,c1d11,c1d12,c1d13,c1d14,c2d0,c2d1,c2d2,c2d3,c2d4,c2d5,c2d6,c2d7,c2d8,c2d9,c2d10,c2d11,c2d12,c2d13,c2d14.

[root@ODA01n0 ~]# odacli describe-component
System Version
---------------
19.16.0.0.0

System node Name
---------------
ODA01n0

Local System Version
---------------
19.16.0.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
...
...
...
LOCAL CONTROLLER FIRMWARE {
[c3]
                                          80000690              up-to-date
[c4,c5]
                                          214.2.271.9           up-to-date
}
SHARED CONTROLLER FIRMWARE
                                          16.00.08.00           up-to-date
FIRMWAREEXPANDER
                                          0310                  up-to-date
LOCAL DISK FIRMWARE
                                          N2010121              up-to-date
SHARED DISK FIRMWARE {
[c0d0,c0d1]
                                          N2010121              up-to-date
[c1d0,c1d1,c1d2,c1d3,c1d4,c1d5,c1d6,
c1d7,c1d8,c1d9,c1d10,c1d11,c1d12,c1d13,
c1d14,c2d0,c2d1,c2d2,c2d3,c2d4,c2d5,
c2d6,c2d7,c2d8,c2d9,c2d10,c2d11,c2d12,
c2d13,c2d14]                              A3Y1                  A680
[c1d15,c1d16,c1d17,c1d18,c1d19,c1d20,
c1d21,c1d22,c1d23,c2d15,c2d16,c2d17,
c2d18,c2d19,c2d20,c2d21,c2d22,c2d23]      A170                  up-to-date
}
HMP
                                          2.4.8.9.601           up-to-date
System node Name
---------------
ODA01n1

Local System Version
---------------
19.16.0.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
...
...
...
LOCAL CONTROLLER FIRMWARE {
[c3]
                                          80000690              up-to-date
[c4,c5]
                                          214.2.271.9           up-to-date
}
SHARED CONTROLLER FIRMWARE
                                          16.00.08.00           up-to-date
FIRMWAREEXPANDER
                                          0310                  up-to-date
LOCAL DISK FIRMWARE
                                          N2010121              up-to-date
SHARED DISK FIRMWARE {
[c0d0,c0d1]
                                          N2010121              up-to-date
[c1d0,c1d1,c1d2,c1d3,c1d4,c1d5,c1d6,
c1d7,c1d8,c1d9,c1d10,c1d11,c1d12,c1d13,
c1d14,c2d0,c2d1,c2d2,c2d3,c2d4,c2d5,
c2d6,c2d7,c2d8,c2d9,c2d10,c2d11,c2d12,
c2d13,c2d14]                              A3Y1                  A680
[c1d15,c1d16,c1d17,c1d18,c1d19,c1d20,
c1d21,c1d22,c1d23,c2d15,c2d16,c2d17,
c2d18,c2d19,c2d20,c2d21,c2d22,c2d23]      A170                  up-to-date
}
HMP
                                          2.4.8.9.601           up-to-date

This can be easily confirmed with fwupdate command. And we can see that the concerned disks are the HDD ones. The SSD ones are all good and already updated to the last version.

[root@ODA01n0 ~]# fwupdate list disk

==================================================
CONTROLLER
==================================================
ID    Type   Manufacturer   Model     Product Name              FW Version     BIOS Version   EFI Version    FCODE Version  Package Version  NVDATA Version    XML Support
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c0    HDC    Intel          0xa182    0x4866                    -              -              -              -              -                -                 N/A

DISKS
===============
ID        Manufacturer   Model               ATA Model                     Chassis Slot   Type   Media   Size(GiB) FW Version ATA FW Ver XML Support
----------------------------------------------------------------------------------------------------------------------------------------------------
c0d0      INTEL          SSDSCKJB480G7       INTEL_SSDSCKJB480G7           -       -      sata   SSD     447       0121       N2010121   N/A
c0d1      INTEL          SSDSCKJB480G7       INTEL_SSDSCKJB480G7           -       -      sata   SSD     447       0121       N2010121   N/A

==================================================
CONTROLLER
==================================================
ID    Type   Manufacturer   Model     Product Name              FW Version     BIOS Version   EFI Version    FCODE Version  Package Version  NVDATA Version    XML Support
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c1    SAS    LSI Logic      0x0097    ORCL-EXT-SAS3             16.00.08.00    08.37.00.00    18.00.00.00    01.00.68.00    -                14.01.00.12       N/A

DISKS
===============
ID        Manufacturer   Model               ATA Model                     Chassis Slot   Type   Media   Size(GiB) FW Version ATA FW Ver XML Support
----------------------------------------------------------------------------------------------------------------------------------------------------
c1d0      HGST           H7210A520SUN010T    -                             0       0      sas    HDD     9124      A3Y1       -          N/A
c1d1      HGST           H7210A520SUN010T    -                             0       1      sas    HDD     9124      A3Y1       -          N/A
c1d2      HGST           H7210A520SUN010T    -                             0       2      sas    HDD     9124      A3Y1       -          N/A
c1d3      HGST           H7210A520SUN010T    -                             0       3      sas    HDD     9124      A3Y1       -          N/A
c1d4      HGST           H7210A520SUN010T    -                             0       4      sas    HDD     9124      A3Y1       -          N/A
c1d5      HGST           H7210A520SUN010T    -                             0       5      sas    HDD     9124      A3Y1       -          N/A
c1d6      HGST           H7210A520SUN010T    -                             0       6      sas    HDD     9124      A3Y1       -          N/A
c1d7      HGST           H7210A520SUN010T    -                             0       7      sas    HDD     9124      A3Y1       -          N/A
c1d8      HGST           H7210A520SUN010T    -                             0       8      sas    HDD     9124      A3Y1       -          N/A
c1d9      HGST           H7210A520SUN010T    -                             0       9      sas    HDD     9124      A3Y1       -          N/A
c1d10     HGST           H7210A520SUN010T    -                             0       10     sas    HDD     9124      A3Y1       -          N/A
c1d11     HGST           H7210A520SUN010T    -                             0       11     sas    HDD     9124      A3Y1       -          N/A
c1d12     HGST           H7210A520SUN010T    -                             0       12     sas    HDD     9124      A3Y1       -          N/A
c1d13     HGST           H7210A520SUN010T    -                             0       13     sas    HDD     9124      A3Y1       -          N/A
c1d14     HGST           H7210A520SUN010T    -                             0       14     sas    HDD     9124      A3Y1       -          N/A
c1d15     HGST           HBCAC2DH2SUN3.2T    -                             0       15     sas    SSD     2981      A170       -          N/A
c1d16     HGST           HBCAC2DH2SUN3.2T    -                             0       16     sas    SSD     2981      A170       -          N/A
c1d17     HGST           HBCAC2DH2SUN3.2T    -                             0       17     sas    SSD     2981      A170       -          N/A
c1d18     HGST           HBCAC2DH2SUN3.2T    -                             0       18     sas    SSD     2981      A170       -          N/A
c1d19     HGST           HBCAC2DH2SUN3.2T    -                             0       19     sas    SSD     2981      A170       -          N/A
c1d20     HGST           HBCAC2DH4SUN800G    -                             0       20     sas    SSD     745       A170       -          N/A
c1d21     HGST           HBCAC2DH4SUN800G    -                             0       21     sas    SSD     745       A170       -          N/A
c1d22     HGST           HBCAC2DH4SUN800G    -                             0       22     sas    SSD     745       A170       -          N/A
c1d23     HGST           HBCAC2DH4SUN800G    -                             0       23     sas    SSD     745       A170       -          N/A

==================================================
CONTROLLER
==================================================
ID    Type   Manufacturer   Model     Product Name              FW Version     BIOS Version   EFI Version    FCODE Version  Package Version  NVDATA Version    XML Support
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c2    SAS    LSI Logic      0x0097    ORCL-EXT-SAS3             16.00.08.00    08.37.00.00    18.00.00.00    01.00.68.00    -                14.01.00.12       N/A

DISKS
===============
ID        Manufacturer   Model               ATA Model                     Chassis Slot   Type   Media   Size(GiB) FW Version ATA FW Ver XML Support
----------------------------------------------------------------------------------------------------------------------------------------------------
c2d0      HGST           H7210A520SUN010T    -                             0       0      sas    HDD     9124      A3Y1       -          N/A
c2d1      HGST           H7210A520SUN010T    -                             0       1      sas    HDD     9124      A3Y1       -          N/A
c2d2      HGST           H7210A520SUN010T    -                             0       2      sas    HDD     9124      A3Y1       -          N/A
c2d3      HGST           H7210A520SUN010T    -                             0       3      sas    HDD     9124      A3Y1       -          N/A
c2d4      HGST           H7210A520SUN010T    -                             0       4      sas    HDD     9124      A3Y1       -          N/A
c2d5      HGST           H7210A520SUN010T    -                             0       5      sas    HDD     9124      A3Y1       -          N/A
c2d6      HGST           H7210A520SUN010T    -                             0       6      sas    HDD     9124      A3Y1       -          N/A
c2d7      HGST           H7210A520SUN010T    -                             0       7      sas    HDD     9124      A3Y1       -          N/A
c2d8      HGST           H7210A520SUN010T    -                             0       8      sas    HDD     9124      A3Y1       -          N/A
c2d9      HGST           H7210A520SUN010T    -                             0       9      sas    HDD     9124      A3Y1       -          N/A
c2d10     HGST           H7210A520SUN010T    -                             0       10     sas    HDD     9124      A3Y1       -          N/A
c2d11     HGST           H7210A520SUN010T    -                             0       11     sas    HDD     9124      A3Y1       -          N/A
c2d12     HGST           H7210A520SUN010T    -                             0       12     sas    HDD     9124      A3Y1       -          N/A
c2d13     HGST           H7210A520SUN010T    -                             0       13     sas    HDD     9124      A3Y1       -          N/A
c2d14     HGST           H7210A520SUN010T    -                             0       14     sas    HDD     9124      A3Y1       -          N/A
c2d15     HGST           HBCAC2DH2SUN3.2T    -                             0       15     sas    SSD     2981      A170       -          N/A
c2d16     HGST           HBCAC2DH2SUN3.2T    -                             0       16     sas    SSD     2981      A170       -          N/A
c2d17     HGST           HBCAC2DH2SUN3.2T    -                             0       17     sas    SSD     2981      A170       -          N/A
c2d18     HGST           HBCAC2DH2SUN3.2T    -                             0       18     sas    SSD     2981      A170       -          N/A
c2d19     HGST           HBCAC2DH2SUN3.2T    -                             0       19     sas    SSD     2981      A170       -          N/A
c2d20     HGST           HBCAC2DH4SUN800G    -                             0       20     sas    SSD     745       A170       -          N/A
c2d21     HGST           HBCAC2DH4SUN800G    -                             0       21     sas    SSD     745       A170       -          N/A
c2d22     HGST           HBCAC2DH4SUN800G    -                             0       22     sas    SSD     745       A170       -          N/A
c2d23     HGST           HBCAC2DH4SUN800G    -                             0       23     sas    SSD     745       A170       -          N/A

The following disks needs to be taken care of. Of course disks are displayed with both controllers. So we really have to only manually patch 15 HDD disks.

[root@ODA01n0 ~]# fwupdate list disk | grep -i A3Y1
c1d0      HGST           H7210A520SUN010T    -                             0       0      sas    HDD     9124      A3Y1       -          N/A
c1d1      HGST           H7210A520SUN010T    -                             0       1      sas    HDD     9124      A3Y1       -          N/A
c1d2      HGST           H7210A520SUN010T    -                             0       2      sas    HDD     9124      A3Y1       -          N/A
c1d3      HGST           H7210A520SUN010T    -                             0       3      sas    HDD     9124      A3Y1       -          N/A
c1d4      HGST           H7210A520SUN010T    -                             0       4      sas    HDD     9124      A3Y1       -          N/A
c1d5      HGST           H7210A520SUN010T    -                             0       5      sas    HDD     9124      A3Y1       -          N/A
c1d6      HGST           H7210A520SUN010T    -                             0       6      sas    HDD     9124      A3Y1       -          N/A
c1d7      HGST           H7210A520SUN010T    -                             0       7      sas    HDD     9124      A3Y1       -          N/A
c1d8      HGST           H7210A520SUN010T    -                             0       8      sas    HDD     9124      A3Y1       -          N/A
c1d9      HGST           H7210A520SUN010T    -                             0       9      sas    HDD     9124      A3Y1       -          N/A
c1d10     HGST           H7210A520SUN010T    -                             0       10     sas    HDD     9124      A3Y1       -          N/A
c1d11     HGST           H7210A520SUN010T    -                             0       11     sas    HDD     9124      A3Y1       -          N/A
c1d12     HGST           H7210A520SUN010T    -                             0       12     sas    HDD     9124      A3Y1       -          N/A
c1d13     HGST           H7210A520SUN010T    -                             0       13     sas    HDD     9124      A3Y1       -          N/A
c1d14     HGST           H7210A520SUN010T    -                             0       14     sas    HDD     9124      A3Y1       -          N/A
c2d0      HGST           H7210A520SUN010T    -                             0       0      sas    HDD     9124      A3Y1       -          N/A
c2d1      HGST           H7210A520SUN010T    -                             0       1      sas    HDD     9124      A3Y1       -          N/A
c2d2      HGST           H7210A520SUN010T    -                             0       2      sas    HDD     9124      A3Y1       -          N/A
c2d3      HGST           H7210A520SUN010T    -                             0       3      sas    HDD     9124      A3Y1       -          N/A
c2d4      HGST           H7210A520SUN010T    -                             0       4      sas    HDD     9124      A3Y1       -          N/A
c2d5      HGST           H7210A520SUN010T    -                             0       5      sas    HDD     9124      A3Y1       -          N/A
c2d6      HGST           H7210A520SUN010T    -                             0       6      sas    HDD     9124      A3Y1       -          N/A
c2d7      HGST           H7210A520SUN010T    -                             0       7      sas    HDD     9124      A3Y1       -          N/A
c2d8      HGST           H7210A520SUN010T    -                             0       8      sas    HDD     9124      A3Y1       -          N/A
c2d9      HGST           H7210A520SUN010T    -                             0       9      sas    HDD     9124      A3Y1       -          N/A
c2d10     HGST           H7210A520SUN010T    -                             0       10     sas    HDD     9124      A3Y1       -          N/A
c2d11     HGST           H7210A520SUN010T    -                             0       11     sas    HDD     9124      A3Y1       -          N/A
c2d12     HGST           H7210A520SUN010T    -                             0       12     sas    HDD     9124      A3Y1       -          N/A
c2d13     HGST           H7210A520SUN010T    -                             0       13     sas    HDD     9124      A3Y1       -          N/A
c2d14     HGST           H7210A520SUN010T    -                             0       14     sas    HDD     9124      A3Y1       -          N/A

[root@ODA01n0 ~]# fwupdate list disk | grep -i A3Y1 | wc -l
30

Let’s look for firmware file location. We will use the most recent one stored in /opt/oracle/oak/pkgrepos/firmwaredisk/hgst/h7210a520sun010t directory.

[root@ODA01n0 ~]# find / -name H7210A520.A680.fw
/opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HGST/H7210A520SUN010T/A680/Base/H7210A520.A680.fw
/opt/oracle/oak/pkgrepos/firmwaredisk/hgst/h7210a520sun010t/a680/H7210A520.A680.fw
...

[root@ODA01n0 ~]# ls -ltrh /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HGST/H7210A520SUN010T/A680/Base/H7210A520.A680.fw
-rwxrwxrwx. 1 root root 2.1M Jul 19  2020 /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HGST/H7210A520SUN010T/A680/Base/H7210A520.A680.fw

[root@ODA01n0 ~]# ls -ltrh /opt/oracle/oak/pkgrepos/firmwaredisk/hgst/h7210a520sun010t/a680/H7210A520.A680.fw
-rwxrwxr-x 1 root root 2.1M Aug 27  2021 /opt/oracle/oak/pkgrepos/firmwaredisk/hgst/h7210a520sun010t/a680/H7210A520.A680.fw

This can be confirmed as well from the dcs-agent.log file during ODA 19.16 patching. The patching process will extract the new firmware file in this directory : /opt/oracle/oak/pkgrepos/firmwaredisk/hgst/h7210a520sun010t

2022-11-03 12:48:32,824 DEBUG [TaskZJsonRpcExt_2450 : JobId=1b4658f3-3f12-473c-8b7f-f7d7cea3dee5] [] c.o.d.a.u.s.StorageUtils:  package version to be applied :: A680
2022-11-03 12:48:32,824 DEBUG [TaskZJsonRpcExt_2450 : JobId=1b4658f3-3f12-473c-8b7f-f7d7cea3dee5] [] c.o.d.a.u.s.StorageUtils:  Installed package version :: null
2022-11-03 12:48:32,824 DEBUG [TaskZJsonRpcExt_2450 : JobId=1b4658f3-3f12-473c-8b7f-f7d7cea3dee5] [] c.o.d.c.u.XDKXmlParseHelper: XDK : XDKXmlParseHelper : getNodesByTagName : File Location=/opt/oracle/oak/pkgrepos/firmwaredisk/hgst/h7210a520sun010t/a680/componentmetadata.xml
2022-11-03 12:48:32,824 DEBUG [TaskZJsonRpcExt_2450 : JobId=1b4658f3-3f12-473c-8b7f-f7d7cea3dee5] [] c.o.d.c.u.XDKXmlParseHelper: XDK : XDKXmlParseHelper : getDocumentFromXmlFile() : File Location=/opt/oracle/oak/pkgrepos/firmwaredisk/hgst/h7210a520sun010t/a680/componentmetadata.xml
2022-11-03 12:48:32,824 DEBUG [TaskZJsonRpcExt_2450 : JobId=1b4658f3-3f12-473c-8b7f-f7d7cea3dee5] [] c.o.d.c.u.XDKXmlParseHelper: XDK : Valid XML : /opt/oracle/oak/pkgrepos/firmwaredisk/hgst/h7210a520sun010t/a680/componentmetadata.xml
2022-11-03 12:48:32,824 DEBUG [TaskZJsonRpcExt_2450 : JobId=1b4658f3-3f12-473c-8b7f-f7d7cea3dee5] [] c.o.d.c.u.XDKXmlParseHelper: XDK : Total nodes that matches tagName=OPC_PATCHING_METADATA is 1

From this log file we can also easily understand why the HDD disks were not patched. Note the logs :

Installed package version :: null. 

As it is impossible for the patching process to get the current firmware version, none patching will occur.

We now just have to run firmware patching command for the following disks : c1d0,c1d1,c1d2,c1d3,c1d4,c1d5,c1d6,c1d7,c1d8,c1d9,c1d10,c1d11,c1d12,c1d13,c1d14

Here as example the patching process for c1d0.

root@ODA01n0 ~]# fwupdate update disk-firmware -n c1d0 -f /opt/oracle/oak/pkgrepos/firmwaredisk/hgst/h7210a520sun010t/a680/H7210A520.A680.fw

The following actions will be taken:
==========================================================
ID        Priority Action      Status      Old Firmware Ver.   Proposed Ver.       New Firmware Ver.   System Reboot
---------------------------------------------------------------------------------------------------------------------------
c1d0      1        Check FW    Success     A3Y1                Not Provided        N/A                 N/A
Do you wish to process the above actions? [y/n]? y
Update of c1d0 to Not Provided.
Updating c1d0: Success

Verifying all priority 1 updates
   No metadata provided, so version verification can not be completed

Execution Summary
==========================================================
ID        Priority Action      Status      Old Firmware Ver.   Proposed Ver.       New Firmware Ver.   System Reboot
---------------------------------------------------------------------------------------------------------------------------
c1d0      1        Update      Success     A3Y1                Not Provided        A680                N/A

At the end, all HDD disks are running A680 firmware version:

[root@ODA01n0 ~]# fwupdate list disk

==================================================
CONTROLLER
==================================================
ID    Type   Manufacturer   Model     Product Name              FW Version     BIOS Version   EFI Version    FCODE Version  Package Version  NVDATA Version    XML Support
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c0    HDC    Intel          0xa182    0x4866                    -              -              -              -              -                -                 N/A

DISKS
===============
ID        Manufacturer   Model               ATA Model                     Chassis Slot   Type   Media   Size(GiB) FW Version ATA FW Ver XML Support
----------------------------------------------------------------------------------------------------------------------------------------------------
c0d0      INTEL          SSDSCKJB480G7       INTEL_SSDSCKJB480G7           -       -      sata   SSD     447       0121       N2010121   N/A
c0d1      INTEL          SSDSCKJB480G7       INTEL_SSDSCKJB480G7           -       -      sata   SSD     447       0121       N2010121   N/A

==================================================
CONTROLLER
==================================================
ID    Type   Manufacturer   Model     Product Name              FW Version     BIOS Version   EFI Version    FCODE Version  Package Version  NVDATA Version    XML Support
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c1    SAS    LSI Logic      0x0097    ORCL-EXT-SAS3             16.00.08.00    08.37.00.00    18.00.00.00    01.00.68.00    -                14.01.00.12       N/A

DISKS
===============
ID        Manufacturer   Model               ATA Model                     Chassis Slot   Type   Media   Size(GiB) FW Version ATA FW Ver XML Support
----------------------------------------------------------------------------------------------------------------------------------------------------
c1d0      HGST           H7210A520SUN010T    -                             0       0      sas    HDD     9124      A680       -          N/A
c1d1      HGST           H7210A520SUN010T    -                             0       1      sas    HDD     9124      A680       -          N/A
c1d2      HGST           H7210A520SUN010T    -                             0       2      sas    HDD     9124      A680       -          N/A
c1d3      HGST           H7210A520SUN010T    -                             0       3      sas    HDD     9124      A680       -          N/A
c1d4      HGST           H7210A520SUN010T    -                             0       4      sas    HDD     9124      A680       -          N/A
c1d5      HGST           H7210A520SUN010T    -                             0       5      sas    HDD     9124      A680       -          N/A
c1d6      HGST           H7210A520SUN010T    -                             0       6      sas    HDD     9124      A680       -          N/A
c1d7      HGST           H7210A520SUN010T    -                             0       7      sas    HDD     9124      A680       -          N/A
c1d8      HGST           H7210A520SUN010T    -                             0       8      sas    HDD     9124      A680       -          N/A
c1d9      HGST           H7210A520SUN010T    -                             0       9      sas    HDD     9124      A680       -          N/A
c1d10     HGST           H7210A520SUN010T    -                             0       10     sas    HDD     9124      A680       -          N/A
c1d11     HGST           H7210A520SUN010T    -                             0       11     sas    HDD     9124      A680       -          N/A
c1d12     HGST           H7210A520SUN010T    -                             0       12     sas    HDD     9124      A680       -          N/A
c1d13     HGST           H7210A520SUN010T    -                             0       13     sas    HDD     9124      A680       -          N/A
c1d14     HGST           H7210A520SUN010T    -                             0       14     sas    HDD     9124      A680       -          N/A
c1d15     HGST           HBCAC2DH2SUN3.2T    -                             0       15     sas    SSD     2981      A170       -          N/A
c1d16     HGST           HBCAC2DH2SUN3.2T    -                             0       16     sas    SSD     2981      A170       -          N/A
c1d17     HGST           HBCAC2DH2SUN3.2T    -                             0       17     sas    SSD     2981      A170       -          N/A
c1d18     HGST           HBCAC2DH2SUN3.2T    -                             0       18     sas    SSD     2981      A170       -          N/A
c1d19     HGST           HBCAC2DH2SUN3.2T    -                             0       19     sas    SSD     2981      A170       -          N/A
c1d20     HGST           HBCAC2DH4SUN800G    -                             0       20     sas    SSD     745       A170       -          N/A
c1d21     HGST           HBCAC2DH4SUN800G    -                             0       21     sas    SSD     745       A170       -          N/A
c1d22     HGST           HBCAC2DH4SUN800G    -                             0       22     sas    SSD     745       A170       -          N/A
c1d23     HGST           HBCAC2DH4SUN800G    -                             0       23     sas    SSD     745       A170       -          N/A

==================================================
CONTROLLER
==================================================
ID    Type   Manufacturer   Model     Product Name              FW Version     BIOS Version   EFI Version    FCODE Version  Package Version  NVDATA Version    XML Support
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c2    SAS    LSI Logic      0x0097    ORCL-EXT-SAS3             16.00.08.00    08.37.00.00    18.00.00.00    01.00.68.00    -                14.01.00.12       N/A

DISKS
===============
ID        Manufacturer   Model               ATA Model                     Chassis Slot   Type   Media   Size(GiB) FW Version ATA FW Ver XML Support
----------------------------------------------------------------------------------------------------------------------------------------------------
c2d0      HGST           H7210A520SUN010T    -                             0       0      sas    HDD     9124      A680       -          N/A
c2d1      HGST           H7210A520SUN010T    -                             0       1      sas    HDD     9124      A680       -          N/A
c2d2      HGST           H7210A520SUN010T    -                             0       2      sas    HDD     9124      A680       -          N/A
c2d3      HGST           H7210A520SUN010T    -                             0       3      sas    HDD     9124      A680       -          N/A
c2d4      HGST           H7210A520SUN010T    -                             0       4      sas    HDD     9124      A680       -          N/A
c2d5      HGST           H7210A520SUN010T    -                             0       5      sas    HDD     9124      A680       -          N/A
c2d6      HGST           H7210A520SUN010T    -                             0       6      sas    HDD     9124      A680       -          N/A
c2d7      HGST           H7210A520SUN010T    -                             0       7      sas    HDD     9124      A680       -          N/A
c2d8      HGST           H7210A520SUN010T    -                             0       8      sas    HDD     9124      A680       -          N/A
c2d9      HGST           H7210A520SUN010T    -                             0       9      sas    HDD     9124      A680       -          N/A
c2d10     HGST           H7210A520SUN010T    -                             0       10     sas    HDD     9124      A680       -          N/A
c2d11     HGST           H7210A520SUN010T    -                             0       11     sas    HDD     9124      A680       -          N/A
c2d12     HGST           H7210A520SUN010T    -                             0       12     sas    HDD     9124      A680       -          N/A
c2d13     HGST           H7210A520SUN010T    -                             0       13     sas    HDD     9124      A680       -          N/A
c2d14     HGST           H7210A520SUN010T    -                             0       14     sas    HDD     9124      A680       -          N/A
c2d15     HGST           HBCAC2DH2SUN3.2T    -                             0       15     sas    SSD     2981      A170       -          N/A
c2d16     HGST           HBCAC2DH2SUN3.2T    -                             0       16     sas    SSD     2981      A170       -          N/A
c2d17     HGST           HBCAC2DH2SUN3.2T    -                             0       17     sas    SSD     2981      A170       -          N/A
c2d18     HGST           HBCAC2DH2SUN3.2T    -                             0       18     sas    SSD     2981      A170       -          N/A
c2d19     HGST           HBCAC2DH2SUN3.2T    -                             0       19     sas    SSD     2981      A170       -          N/A
c2d20     HGST           HBCAC2DH4SUN800G    -                             0       20     sas    SSD     745       A170       -          N/A
c2d21     HGST           HBCAC2DH4SUN800G    -                             0       21     sas    SSD     745       A170       -          N/A
c2d22     HGST           HBCAC2DH4SUN800G    -                             0       22     sas    SSD     745       A170       -          N/A
c2d23     HGST           HBCAC2DH4SUN800G    -                             0       23     sas    SSD     745       A170       -          N/A

And we are now all good with the describe-component result:

[root@ODA01n0 ~]# odacli describe-component
System Version
---------------
19.16.0.0.0

System node Name
---------------
ODA01n0

Local System Version
---------------
19.16.0.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
...
...
...
LOCAL CONTROLLER FIRMWARE {
[c3]
                                          80000690              up-to-date
[c4,c5]
                                          214.2.271.9           up-to-date
}
SHARED CONTROLLER FIRMWARE
                                          16.00.08.00           up-to-date
FIRMWAREEXPANDER
                                          0310                  up-to-date
LOCAL DISK FIRMWARE
                                          N2010121              up-to-date
SHARED DISK FIRMWARE {
[c0d0,c0d1]
                                          N2010121              up-to-date
[c1d0,c1d1,c1d2,c1d3,c1d4,c1d5,c1d6,
c1d7,c1d8,c1d9,c1d10,c1d11,c1d12,c1d13,
c1d14,c2d0,c2d1,c2d2,c2d3,c2d4,c2d5,
c2d6,c2d7,c2d8,c2d9,c2d10,c2d11,c2d12,
c2d13,c2d14]                              A680                  up-to-date
[c1d15,c1d16,c1d17,c1d18,c1d19,c1d20,
c1d21,c1d22,c1d23,c2d15,c2d16,c2d17,
c2d18,c2d19,c2d20,c2d21,c2d22,c2d23]      A170                  up-to-date
}
HMP
                                          2.4.8.9.601           up-to-date
System node Name
---------------
ODA01n1

Local System Version
---------------
19.16.0.0.0

Component                                Installed Version    Available Version
---------------------------------------- -------------------- --------------------
...
...
...
LOCAL CONTROLLER FIRMWARE {
[c3]
                                          80000690              up-to-date
[c4,c5]
                                          214.2.271.9           up-to-date
}
SHARED CONTROLLER FIRMWARE
                                          16.00.08.00           up-to-date
FIRMWAREEXPANDER
                                          0310                  up-to-date
LOCAL DISK FIRMWARE
                                          N2010121              up-to-date
SHARED DISK FIRMWARE {
[c0d0,c0d1]
                                          N2010121              up-to-date
[c1d0,c1d1,c1d2,c1d3,c1d4,c1d5,c1d6,
c1d7,c1d8,c1d9,c1d10,c1d11,c1d12,c1d13,
c1d14,c2d0,c2d1,c2d2,c2d3,c2d4,c2d5,
c2d6,c2d7,c2d8,c2d9,c2d10,c2d11,c2d12,
c2d13,c2d14]                              A680                  up-to-date
[c1d15,c1d16,c1d17,c1d18,c1d19,c1d20,
c1d21,c1d22,c1d23,c2d15,c2d16,c2d17,
c2d18,c2d19,c2d20,c2d21,c2d22,c2d23]      A170                  up-to-date
}
HMP
                                          2.4.8.9.601           up-to-date

L’article ODA – HDD manuel firmware patching est apparu en premier sur dbi Blog.

Pages

Subscribe to Oracle FAQ aggregator