Oracle Tips and Tricks — David Fitzjarrell

October 20, 2017

It’s From The Catalog

Filed under: General — dfitzjarrell @ 17:24

"Have you ever heard a blindfolded octopus unwrap a cellophane-covered bathtub?"
-- Norton Juster, The Phantom Tollbooth

Database upgrades aren’t terribly difficult in general, unless the upgrade is for an RMAN catalog database that has two catalogs with different versions. It’s completely ‘legal’ to have an 11.2.0.x RMAN catalog database with an additional 12.1.0.x catalog; this is where the going can get rough during an upgrade since the owner of the 12c catalog will likely have an after logon context trigger which will become invalid during the ‘startup upgrade’ step of the database upgrade. Once that trigger goes invalid the only accounts that can login to the database are SYS, SYSTEM and the catalog owner of the 11.2.0.x catalog.

All of the documentation on upgrading RMAN catalog databases and the associated catalogs address the one database/one catalog scenario, with that one catalog having the same version as the database. So what IF you have two catalogs of differing versions in an 11.2.0.x database? The upgrade can be completed successfully but it takes some additional steps. Let’s look at the differences between 11.2.0.x and 12.1.0.x (or 12.2.0.x) RMAN and then at those steps to see where this can differ from a ‘normal’ upgrade.

The trigger mentioned above is the first hurdle that must be addressed, and that’s an easy hurdle to jump. The name of that problematic trigger is VPC_CONTEXT_TRG and it sets a specific context for catalog owners. Prior to changing ORACLE_HOME locations to the 12.x version simply log in as the catalog owner of the 12.x catalog and disable the trigger:


SQL> alter trigger VPC_CONTEXT_TRG disable;

Trigger altered.

SQL>

Now the upgrade to 12.x can proceed and the invalid trigger won’t affect the second catalog owner.

Oracle, in the 12.x versions of RMAN, change the VPC configuration used in 11.2.0.x to a VPD configuration; because of this some additional steps need to be taken even with single-catalog repository databases. The first change in the procedure is a script, located in $ORACLE_HOME/rdbms/admin named dbmsrmansys.sql. This script adds privileges to the RECOVERY_CATALOG_OWNER role necessary to access and manage the VPD configuration and must be run before connecting to the RMAN catalog database otherwise the dreaded ORA-01031 “insufficient privileges” error will rear its ugly head. This will cover all recovery catalog owners in the repository database, regardless of the catalog version.

After that script is executed then each catalog owner needs to be granted the necessary privileges to access the VPD configuration so another script is run, this time for each catalog owner in the database:


sqlplus / as sysdba @?/rdbms/admin/dbmsrmanvpc.sql -vpd rman
sqlplus / as sysdba @?/rdbms/admin/dbmsrmanvpc.sql -vpd rman12c

The script exits after execution which is why the code listed above shows two calls to sqlplus. The output of that script is shown below:


SYS @ blerble > @?/rdbms/admin/dbmsrmanvpc.sql -vpd rman

Checking the operating user... Passed

Granting VPD privileges to the owner of the base catalog schema RMAN

========================================
VPD SETUP STATUS:
VPD privileges granted successfully!
Connect to RMAN base catalog and perform UPGRADE CATALOG.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Once this is done for all catalog owners in the database it’s time to start upgrading catalogs. Normally executing UPGRADE CATALOG; will prompt for a second submission of that command to verify that is exactly what you want to do. To get around this the NOPROMPT option is available so UPGRADE CATALOG need be entered only once:


Recovery Manager: Release 12.2.0.1.0 - Production on Fri Oct 20 15:15:31 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RCATT (DBID=4052274215)
recovery catalog database Password:
connected to recovery catalog database
PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RMAN database is too old

RMAN> upgrade catalog noprompt;

recovery catalog upgraded to version 12.02.00.01
DBMS_RCVMAN package upgraded to version 12.02.00.01
DBMS_RCVCAT package upgraded to version 12.02.00.01.

RMAN> exit


Recovery Manager complete.

This now needs to be done to the second (12.x) catalog, even if it’s at 12.2.0.1 so that the objects can be rebuilt.

A final script needs to be executed before the upgrade is actually completed:


SYS @ blerble > @?/rdbms/admin/dbmsrmanvpc.sql -all

Checking the operating user... Passed

----------------------------------------
Removing old VPC views in the base catalog of RMAN12...
----------------------------------------
Removing old VPC views in the base catalog of RMAN...
========================================
UPGRADE STATUS:
The VPC user schemas of these catalogs: RMAN12, RMAN
have been successfully upgraded to the new VPD model!

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

The final step is to log in as the 12.x catalog owner (in this case RMAN12) and enable the trigger that was disabled prior to the upgrade:


SQL> alter trigger VPC_CONTEXT_TRG enable;

Trigger altered.

SQL>

The database upgrade, along with the RMAN catalog upgrades, are complete. Verify this by logging in as a ‘regular’ user; the connection should be successful:


SQL> connect yerble
Enter password:
Connected.
SQL>

It may not be the easiest upgrade to perform but having the steps beforehand can certainly help.

And it’s much less annoying than that octopus.

Advertisements

October 16, 2017

Roll With It

Filed under: General — dfitzjarrell @ 11:07

"I know one thing for certain; it is much harder to tell whether you are lost than whether you were lost,
for, on many occasions, where you are going is exactly where you are. On the other hand, if you often find
that where you've been is not at all where you should have gone, and, since it's much more difficult to
find your way back from someplace you've never left, I suggest you go there immediately and then decide."
-- Norton Juster, The Phantom Tollbooth

Waiting is, well, inconvenient at times because it interferes with getting work done yet there are times when there is nothing to do, really, except wait. Of course this usually happens during an important update to a critical application table, when the transaction fails on error or, horror of horrors, the network ‘gives way’ and the session is disconnected, forcing Oracle to rollback all of that work you just did. Unfortunately that rollback seems to take forever to complete. The reason for that is that large, long-running transactions can generate large volumes of UNDO. And UNDO is how Oracle restores the data to its starting point should the transaction fail or be cancelled (which, by golly, just happened). This, of course, generates long rollback times that can seem interminable when you are waiting for the rollback to complete before other work can begin. But, monitoring the rollback progress isn’t difficult since V$TRANSACTION, in conjunction with V$SESSION, can report on the UNDO blocks that have yet to be applied. Let’s see how that can be done.

V$TRANSACTION records various pieces of information about active and cancelled transactions, with one of those pieces being the blocks of UNDO that are used (either allocated or applied, depending on whether the transaction is running or being rolled back). That information can be used to great advantage when monitoring the progress of a rollback. The queries used are shown below:


set echo on pagesize 100 linesize 132

select
case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
                                          else 'Not Rolling Back'
       end   as "Roll Status"
from v$transaction
where addr in (select taddr from v$session where username = upper('&&1'));

select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
from v$transaction t, v$session s
where t.addr = s.taddr
group by s.sid, s.serial#, s.client_info, t.addr;

The first query returns the rollback status of the transaction; the second returns the used UNDO blocks being generated by the transaction or read to undo the work that has already been done. The client_info column may or may not be populated (it isn’t in these examples) but it is provided to help differentiate different sessions an application user may have running so that the transaction or transactions of interest can be monitored. Looking at an example should help; a long-running transaction is in progress in session #1; looking at the UNDO blocks being allocated (from a second session) we see the USED_UBLK column increasing in value and that the transaction is not rolling back:


BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      30106                                                                  000007FF8EF7DC88              430

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      30106                                                                  000007FF8EF7DC88              739

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

no rows selected

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

no rows selected

BING @ quanghoo >
...
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             6475

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Not Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             6755

BING @ quanghoo >

At the time the transaction is cancelled the used UNDO block total was:


--
-- Transaction still active and generating UNDO
--
BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             7914

BING @ quanghoo >

From the second session we can now monitor the progress of the rollback, using the same queries as before. This time the first query reports a rollback is in progress:


--
-- Transaction cancelled
--
-- Successive executions of these queries return a decreasing used undo block count
-- thus tracking the rollback progress
--
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133      18316                                                                  000007FF8EF38688             5110

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688             3491

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688             1363

BING @ quanghoo >
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

Roll Status
----------------
Rolling Back

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

       SID    SERIAL# CLIENT_INFO                                                      ADDR          SUM(T.USED_UBLK)
---------- ---------- ---------------------------------------------------------------- ---------------- ----------------
       133          0                                                                  000007FF8EF38688               70

BING @ quanghoo >

--
-- Transaction rollback complete
--
BING @ quanghoo > @rollback_lrg_update_ex2 bung
BING @ quanghoo > connect bing/%^&%^&
Connected.
BING @ quanghoo >
BING @ quanghoo > set echo on pagesize 100 linesize 132
BING @ quanghoo >
BING @ quanghoo > select
  2  case when bitand(flag,power(2,7)) > 0 then 'Rolling Back'
  3                                            else 'Not Rolling Back'
  4         end   as "Roll Status"
  5  from v$transaction
  6  where addr in (select taddr from v$session where username = upper('&&1'));
old   6: where addr in (select taddr from v$session where username = upper('&&1'))
new   6: where addr in (select taddr from v$session where username = upper('bung'))

no rows selected

BING @ quanghoo >
BING @ quanghoo > select s.sid, s.serial#, s.client_info, t.addr, sum(t.used_ublk)
  2  from v$transaction t, v$session s
  3  where t.addr = s.taddr
  4  group by s.sid, s.serial#, s.client_info, t.addr;

no rows selected

BING @ quanghoo >

The used UNDO block count eventually goes away (since the transaction is neither active nor rolling back) which signals the end of the rollback phase. Now other work can proceed against the table or tables involved in the previous transaction. And, having this data available usually makes the wait seem shorter as progress is registered for the rollback phase.

Waiting seems to be the hardest part, especially when no apparent progress can be monitored. Now, for rollbacks, a method is available to produce usable information regarding the progress of the rollback, which can make everyone’s life (at least for those who are waiting on a rollback to finish) a bit easier.

Which doesn’t make you lost, just … impatient.

Blog at WordPress.com.