Yearly Archives: 2021

Datapatch and Tablespace Full

This entry was posted in OPatch Oracle Oracle 19c on by

Yesterday on a simple 19.13 patching session, got a nice error during datapatch: ORA-01691: usable to extend lob segment in Tablespace SYSTEM ! Got a bit scared, but simple added more space to the tablespace, run datapatch again and all was ok. Maybe patch/datapatch should check also for free space in the SYSTEM Tablespace ?

Here the error:

DBD::Oracle::st execute failed: ORA-01691: unable to extend lob segment SYS.SYS_LOB0000323098C00008$$ by 128 in tablespace SYSTEM
ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute) [for Statement "BEGIN
           INSERT INTO sys.dba_registry_sqlpatch_ru_info
             (patch_id,
              patch_uid,
              patch_descriptor,
              ru_version,
              ru_build_description,
              ru_build_timestamp,
              patch_directory)
           VALUES
             (:patch_id,
              :patch_uid,
              :patch_descriptor,
              :ru_version,
              :ru_build_description,
              TO_TIMESTAMP(:ru_build_timestamp, 'YYMMDDHH24MISS'),
              :patch_directory);
           COMMIT;
         END;" with ParamValues: :patch_descriptor=OCIXMLTypePtr=SCALAR(0x468b0a0), :patch_directory='PK........▒LOS▒.
▒. ..▒[..
...33192793.xml▒][.۸.~..
▒.v▒▒▒.x.▒▒q*.o▒▒*.'s▒▒▒(.-qL▒l.ji▒▒/@Im        8 .$.I▒<▒0j▒.>\.▒.▒~.▒▒▒K▒vE▒▒▒.~K..▒6▒▒b▒▒▒a▒?=F.▒..▒]▒\▒5▒▒▒▒.?|▒▒y4f▒▒{X▒▒▒..▒"▒▒|?d.▒..I▒.E.▒▒▒▒=▒▒▒▒▒..▒▒▒.▒▒5.Wg▒w.ۺ▒..▒v}Q%}.▒..▒▒.▒▒.-▒▒▒▒▒\%▒▒׿▒vE.▒▒xsU▒▒)%ħa@.r▒▒.▒Kۢ▒.▒}..y▒▒.~j2▒▒â.=▒▒▒.▒▒▒O.▒▒<▒wm▒▒▒d▒.▒vw▒▒▒▒....▒o޼.▒}▒▒▒.I.▒x▒.▒▒.▒▒▒▒.▒▒.▒x▒o▒)▒▒wo.vxۿ{|\▒,.i]5▒6▒▒..▒▒.b.g▒b▒▒7Ew▒▒▒▒./▒.▒▒o.▒/▒.▒.▒▒6▒#▒.▒.▒...▒▒..▒▒▒▒ߵ▒l▒▒▒▒...▒▒.▒.▒▒.a(▒.▒?|▒.▒
X▒▒{+▒▒v▒=.▒{▒▒▒▒9Íui▒▒.H.▒▒▒▒▒▒▒▒S.▒^▒|▒9.▒%▒,▒▒▒SU▒.▒. zZ.▒..c;.▒vحY▒B...▒▒▒▒/▒▒.▒▒▒▒.▒▒▒..H{.7}f.▒..▒j.▒mۿ▒.▒S.▒7▒.:§.▒+p▒%▒..w▒.▒.▒{7▒.▒k▒..▒6▒6▒▒>v▒bwMU▒..▒Y_▒+▒IG▒<.|4▒▒\▒4.▒i▒*▒▒;G▒-V.ε▒▒.p.▒▒▒▒
G̍1▒.▒]▒▒▒.1O▒v▒^.▒▒▒.▒:.Wy._q7▒▒.*▒&▒▒`/▒I߹...pW.
▒▒▒▒C.v▒j▒[▒▒.▒▒~▒▒.▒.bo▒▒.U▒..▒V▒▒
.`n▒▒N7▒.`nyup▒▒.w▒.]▒▒B▒.▒▒5D7▒.w.#w.▒.▒m▒▒ܬ▒.p▒.3p.▒▒.▒.l▒.▒▒Z;▒0.\bm▒J▒...', :patch_id="33192793", :patch_uid="24462514", :ru_build_description="Release_Update", :ru_build_timestamp="211004165050", :ru_version="19.13.0.0.0"] at /u01/oracle/db19s/sqlpatch/sqlpatch.pm line 5876, <LOGFILE> line 110.

New in Oracle 19.12 – Log switch SCN in alertlog

This entry was posted in Oracle 19c Patchset Update and tagged oracle 19c on by

Just patched few DBs and noticed this change:

Before:

2021-08-26T05:30:52.463731+02:00
Thread 1 advanced to log sequence 101101 (LGWR switch)
  Current log# 1 seq# 101101 mem# 0: +U01/ANJODB_SITE1/ONLINELOG/group_1.282.988988971

And with 19.12:

2021-08-27T07:17:16.811288+02:00
Thread 1 advanced to log sequence 101176 (LGWR switch),  current SCN: 132131048333
  Current log# 1 seq# 101176 mem# 0: +U01/ANJODB_SITE1/ONLINELOG/group_1.282.988988971

Nice touch from Oracle. 🙂

GI installation – ASM does not start on 19.12

This entry was posted in ASM Oracle Oracle 19c on by

Today a colleague tells me that installing GI on /u00/app/grid/19.12.0 fails, while it works by changing the ORACLE_HOME location. The error in the logs is with the ASM disk group creation:

INFO:  [Aug 16, 2021 4:06:17 PM] Command /u00/app/grid/19.12.0/bin/asmca -silent -oui_internal -configureASM -diskString '/dev/xvd*' -diskGroupName U01 -diskList /dev/xvdg -redundancy EXTERNAL -au_size 4
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn.handleProcess() entered.
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn: getting configAssistantParmas.
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn: checking secretArguments.
INFO:  [Aug 16, 2021 4:06:17 PM] ... GenericInternalPlugIn: starting read loop.
INFO:  [Aug 16, 2021 4:06:22 PM] SYS_PASSWORD_PROMPT
INFO:  [Aug 16, 2021 4:06:22 PM] Processing: SYS_PASSWORD_PROMPT for argument tag -sysAsmPassword
INFO:  [Aug 16, 2021 4:06:22 PM] Skipping line:*******************
INFO:  [Aug 16, 2021 4:06:22 PM] ASMSNMP_PASSWORD_PROMPT
INFO:  [Aug 16, 2021 4:06:22 PM] Processing: ASMSNMP_PASSWORD_PROMPT for argument tag -asmMonitorPassword
INFO:  [Aug 16, 2021 4:06:22 PM] End of argument passing to stdin
INFO:  [Aug 16, 2021 4:06:22 PM] Skipping line:*******************
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] [FATAL] [DBT-30056] Labeling of disks failed.
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: [FATAL] [DBT-30056] Labeling of disks failed.
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15227: could not perform label set/clear operation
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15227: could not perform label set/clear operation
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15031: disk specification '/dev/xvdg' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15031: disk specification '/dev/xvdg' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] [FATAL] [DBT-30002] Disk group U01 creation failed.
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: [FATAL] [DBT-30002] Disk group U01 creation failed.
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15018: diskgroup cannot be created
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15018: diskgroup cannot be created
INFO:  [Aug 16, 2021 4:06:59 PM] ORA-15031: disk specification 'AFD:U011' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line: ORA-15031: disk specification 'AFD:U011' matches no disks
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:06:59 PM] Skipping line:
INFO:  [Aug 16, 2021 4:07:00 PM] Completed Plugin named: asmca
INFO:  [Aug 16, 2021 4:07:00 PM] ConfigClient.saveSession method called
INFO:  [Aug 16, 2021 4:07:00 PM] Automatic Storage Management Configuration Assistant failed.

While I still did not get the original reason of the problem (might be with the Gold Image creation of the GI), I found that it can be worked around with:

chown -R grid:oinstall $ORACLE_BASE/diag

How to change PDB open mode without disconnecting existing sessions: the ambiguous FORCE

This entry was posted in Multitenant Oracle Oracle 19c on by

When the database is in READ ONLY mode, the way to change it to READ WRITE is by issuing

SQL> ALTER DATABASE OPEN READ WRITE;
Database altered.

However, when working on a PDB, the similar syntax does not work:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE;
ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE
*
ERROR at line 1:
ORA-65019: pluggable database pdb1 already open

This is strange, and to my surprise, the correct syntax to change a PDB from READ ONLY to READ WRITE is:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE FORCE;
Pluggable database altered.

I would expect that the “FORCE” would do the same as on the STARTUP command here:

SQL> STARTUP FORCE OPEN READ WRITE;
ORACLE instance started.

Total System Global Area 2147481656 bytes
Fixed Size                  8898616 bytes
Variable Size             872415232 bytes
Database Buffers         1258291200 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

On the normal STARTUP command, the FORCE keyword performs a shutdown abort, before starting back the database. It is correctly described in the documentation:

If the database is open, then FORCE shuts down the database with a SHUTDOWN ABORT statement before re-opening it.

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/starting-up-and-shutting-down.html#GUID-CCA52747-05CA-4ED3-BE6D-E2E684C4D87D

The ALTER PLUGGABLE DATABASE explains the different behaviour of the FORCE keyword in this case:

Specify this keyword [FORCE] to change the open mode of a PDB from READ WRITE to READ ONLY, or from READ ONLY to READ WRITE. The FORCE keyword allows users to remain connected to the PDB while the open mode is changed.

When you specify FORCE to change the open mode of a PDB from READ WRITE to READ ONLY, any READ WRITE transaction that is open when you change the open mode will not be allowed to perform any more DML operations or to COMMIT.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-PLUGGABLE-DATABASE.html#GUID-A29491AD-8F0F-4E52-9D94-57FC3FF8FBC7

So, at the end it is good news. It is possible to change the PDB open mode without killing the users’ sessions. Just that “FORCE” keyword seems a bit too strong for my taste.

Opatchauto does not work without DB created. And Oracle support… well

This entry was posted in My Oracle Support OPatch Oracle Oracle 19c Patchset Update on by

Few months ago I hit an interesting case. After installing Oracle Grid Infrastructure and Database software on a server, the new Release Update come out and I wanted to use opatchauto to apply it automatically on both homes.

It doesn’t work. 

sudo ${ORACLE_HOME}/OPatch/opatchauto apply -phBaseDir /tmp/RU_19c_JAN2021/32126842 -prepare-clone -silent /tmp/RU_19c_JAN2021/clone.properties
...
OPATCHAUTO-72128: Cannot execute out of place patching prepare session.
OPATCHAUTO-72128: Clone home properties /tmp/RU_19c_JAN2021/clone.properties specified has entry for non requested home(s) [/u00/app/oracle/product/19.8.0].
OPATCHAUTO-72128: Please correct properties file and re-run out of place patching prepare operation.
OPatchAuto failed.

The setup is all correct, opatchauto is the latest version (13.9.4.5.0, as of February 2021), the inventory has the two homes:

<HOME NAME="OraGI19Home1" LOC="/u00/app/grid/19.8.0" TYPE="O" IDX="1" CRS="true"/>
<HOME NAME="OraDB19Home1" LOC="/u00/app/oracle/product/19.8.0" TYPE="O" IDX="2"/>

The clone.properties file has the name of these homes:

/u00/app/grid/19.8.0=/u00/app/grid/19.10.0
/u00/app/oracle/product/19.8.0=/u00/app/oracle/product/19.10.0

The only “problem” is that no database is created. In fact, the workaround is just to register a fake DB on the CRS:

srvctl add db XX -oraclehome /u00/app/oracle/product/19.8.0

Then the opatchauto works.

I open a SR with Oracle. Inform about the OPatch bug and ask what is the meaning of the OPATCHAUTO-72128 error, because there is no documentation of Opatchauto.

The normal ping-pong with Oracle support starts, they ask me to run with maximum debug level before they can do something. I say that the complete use case is already described and they can try in-house and can/should open a bug.

No success. The support person says that if the customer doesn’t do more work, Oracle doesn’t care about the bug and problem will remain unfixed. I inform that I will blog about this bug, so that the community is aware.

Final answer from this SR:

“Knowledge content not created because the issue was resolved by the customer with no further information provided. Also no further ODM headings were used for the same reason.”

Find the most used indexes

This entry was posted in Oracle Performance on by

At a customer I was asked to check for missing indexes and add them. Some days later the application automatically dropped them. The customer come back to me and asked to check which indexes were the most important.

As maybe not all my added indexes were needed and used, instead of just sending (again) the document with their definition, I checked which indexes were actively used since adding the new indexes.

I come up with this query:

select distinct object_owner,object_name from dba_hist_sql_plan where plan_hash_value in (select plan_hash_value from 
(
/* Day after adding new indexes */
with snapshot as (select min(snap_id) snap_id from dba_hist_snapshot where begin_interval_time>=to_date('27-MAY-2021','DD-MON-YYYY')),
/* Plans using new indexes */
plans as (select plan_hash_value from dba_hist_sql_plan where object_name like '%\_ANJO\_%' escape '\')
/* Plans using new indexes after my intervention */
select plan_hash_value, sum(executions_delta) execs from dba_hist_sqlstat where snap_id> (select snap_id from snapshot)
  and plan_hash_value in (select plan_hash_value from plans)
  group by plan_hash_value
  having sum(executions_delta)>10
))
and object_name like '%\_ANJO\_%' escape '\' 
order by 1,2;

I’ve limited the search to the indexes I added (having _ANJO_ in their name).

Surprise, all of the indexes I added were actively used. Hopefully they will add them again for good.

Recover OEM repository from old schema backup

This entry was posted in OMS on by

Things not always go right, and recently I had to recover the OEM repository using a one month old datapump backup. Only SYSMAN schema was corrupted, and I did not touch the other schemas.

I probably still miss doing some things and this post is mostly a note to make it faster for other people going through the process and not having a DB backup…

A full restore of the database would be certainly a much better and efficient solution than the one below. 🙂

To recover the corrupted SYSMAN schema where the OEM repository resides, first I drop it and then I did:

# Restore user
impdp schemas=SYSMAN directory=BACKUP_DIR dumpfile=expdp_sysman_april2021.dmp include=USER

# Restore system grants:
SQL> grant execute on dbms_crypto to sysman;
SQL> grant execute on dbms_lock to sysman;
SQL> grant execute on dbms_rls to sysman;
SQL> grant execute on dbms_aq to sysman;
SQL> grant execute on dbms_aqadm to sysman;
SQL> grant execute on dbms_alert to sysman;

# Restore other object grants 
impdp directory=BACKUP_DIR dumpfile=expdp_sysman_april2021.dmp include=GRANT

# Restore data
impdp schemas=SYSMAN directory=BACKUP_DIR dumpfile=expdp_sysman_april2021.dmp 

# Restart queues
/* select 'exec DBMS_AQADM.START_QUEUE(queue_name => ''SYSMAN.'||name||''');' from dba_queues where enqueue_enabled like '%NO%' and owner='SYSMAN' order by 1; */
SQL> exec dbms_aqadm.start_queue('SYSMAN.MGMT_ADMINMSG_BUS',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_CNTR_QUEUE',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_LOADERJOB_QUEUE',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_EVENT_BUS',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_NOTIFY_Q',true,true);
SQL> exec dbms_aqadm.start_queue('SYSMAN.EM_GROUP_EVENT_Q',true,true);
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.EM_JOB_STATUS_UPD_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_HOST_PING_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_LOADER_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_NOTIFY_INPUT_Q');
SQL> exec DBMS_AQADM.START_QUEUE('SYSMAN.MGMT_TASK_Q');
SQL> commit;

# Perform partitionion maintenance (as SYSMAN):
SQL> exec gc_interval_partition_mgr.partition_maintenance;
SQL> exec mgmt_audit_admin.add_audit_partition;

Start OEM again:

emctl start oms

Unblock the agents (Setup – Manage Cloud Control – Agents – select blocked agents + “unblock”) and on each monitored host, clear the agent state, restart it and test:

emctl clearstate agent
emctl stop agent
emctl start agent
emctl control agent runCollection $(hostname -f):host Response
emctl status agent

After this, OEM was running again without problems.

CPU pinning overview directly from physical server using OracleVM / Xen

This entry was posted in Oracle OracleVM on by

When using Oracle VM, there is the the possibility to use the ‘ovm_vmcontrol’ tool to check and set the CPU Pinning. However the output is not the best for a overview picture.

Today I just wrote a short script to print this overview, which can be run directly on the Physical Server which we are interested at:

[root@lxsrv0001 ~]# for vm in $(xm vcpu-list | egrep '^000' | cut -c1-32 | uniq); do 
  echo "${vm} - $(grep OVM_simple_name /OVS/Repositories/*/VirtualMachines/${vm}/vm.cfg | cut -d'=' -f2) - $(xm vcpu-list | grep -c ${vm}) CPUs - Pin to $(xm vcpu-list | grep -m1 ${vm} | rev | cut -d' ' -f1 | rev)"; 
done

The output will be something like

0004fb000006000031f0e14272fa90d1 - 'vmsrv01' - 6 CPUs - Pin to 0-5
0004fb0000060000b7fcb78fa7888d37 - 'vmsrv03' - 6 CPUs - Pin to 0-5
0004fb00000600004568a88b95d9ea3f - 'vmsrv05' - 4 CPUs - Pin to 6-10
0004fb00000600004ae9d1f0c8e4b8fb - 'vmsrv07' - 2 CPUs - Pin to 11-12

This can always be useful when we need to fast check the CPU pinning for the whole Physical Server.

What is the Dell BSAFE Micro-Edition Suite MES ?

This entry was posted in Oracle Oracle 19c PSU Security on by

On the April 2021 Oracle Critical Patch Update, the top vulnerability affects “Oracle Database – Enterprise Edition Security (Dell BSAFE Micro Edition Suite)”.

But what exactly is this Dell MES ? The answer comes on MES v4.1.6 to v4.5 update 18c / 19c databases (Doc ID 2746801.1) note on My Oracle Support. It explains:

” The BSAFE MES is the underlying encryption libraries used by the Database and associated technologies and products to encrypt data at rest and in transit.  Upgrading to MES 4.5 resolves a number of vulnerabilities and provides the latest and most secure encryption ciphers.”

Please note that already on the January 2021 Oracle Release Update (19.10 or 18.13 there was an update of this component and “Anonymous RC4 Cipher” is not supported and does not work anymore.

Other ciphers are deprecated and should not be used anymore, as they are not fully secure:

  • Unix Crypt (MD5crypt) Password verified (used with Oracle Internet Directory OID)
  •  MD4, MD5, DES, and RC4-related algorithms used for instance in Network encryption (recommended to use AES)
  • SHA-1 used in DBMS_CRYPTO and Checksum in SQLNET (recommended to use SHA-2)
  • TLS 1.0 and TLS 1.1 used to network connection authentication to the database using certificates (recommended to use TLS 1.2)

In my case I had problems not with the database, but with an old OEM Agent, that could not communicate anymore to OEM (after the January RU on OEM). The solution was to remove all deprecated algoritms and find common accepted ones between to old agent and patched OEM.

Go to MOS directly from Google results – part 2

This entry was posted in My Oracle Support on by

We know that Metalink search engine is not the fastest one. Luckily My Oracle Support is now partly accessible directly from Google Search.

Using a shortcut, we can quickly limit our search to My Oracle Support website. Then using the URL Redirector as explained here, one jumps directly to the Oracle note.

On Chrome (similar on Firefox and Edge) it is possible to set Search Engine shortcuts.

Just go to chrome://settings/searchEngines and add a new search engine as below :

For easy copy/paste:

Search engine: Oracle Support
Keyword: mos
URL: https://www.google.com/search?q=site%3Asupport.oracle.com+%s

Now we have a quick way into MOS:

🙂