Oracle Label Security (OLS) clean up in Oracle Database 11.2-12.2 is almost as simple and straight forward as removing OWM or APEX. You can do it online without downtime. But in older releases a relink operation did cause downtime. Oracle Label Security is an extra cost option. More information is available on oracle.com.
Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2
Oracle Label Security is dependent on Oracle Database Vault (DV). In case DV is present you must remove it first if you plan to remove Oracle Label Security.
Before you start removing anything from your database please make sure you read my introductory blog post about how to Remove and Clean Up Components from Oracle 11.2 – 12.2 . You’ll find links to monitor proper component removal there as well as in the SQL Scripts Repository on this blog.
Oracle Database 11.2.0.4
The removal process is documented in the official Oracle Documentation.
$ sqlplus / as sysdba SQL> @?/rdbms/admin/catnools.sql
Afterwards there will be 21 objects invalid.
SQL> col object_name format a30 SQL> col object_type format a15 SQL> set pages 2000 SQL> set line 200 SQL> select object_type, object_name from dba_objects where status='INVALID'; OBJECT_TYPE OBJECT_NAME --------------- ------------------------------ VIEW DBA_AUDIT_TRAIL SYNONYM DBA_AUDIT_TRAIL VIEW USER_AUDIT_TRAIL SYNONYM USER_AUDIT_TRAIL VIEW DBA_AUDIT_SESSION SYNONYM DBA_AUDIT_SESSION VIEW USER_AUDIT_SESSION SYNONYM USER_AUDIT_SESSION VIEW DBA_AUDIT_STATEMENT SYNONYM DBA_AUDIT_STATEMENT VIEW USER_AUDIT_STATEMENT SYNONYM USER_AUDIT_STATEMENT VIEW DBA_AUDIT_OBJECT SYNONYM DBA_AUDIT_OBJECT VIEW USER_AUDIT_OBJECT SYNONYM USER_AUDIT_OBJECT VIEW DBA_AUDIT_EXISTS SYNONYM DBA_AUDIT_EXISTS VIEW DBA_COMMON_AUDIT_TRAIL SYNONYM DBA_COMMON_AUDIT_TRAIL PACKAGE BODY MGMT_DB_LL_METRICS 21 rows selected.
Recompile to clean up.
$ sqlplus / as sysdba SQL> @?/rdbms/admin/utlrp.sql
Finally unlink the option once downtime is acceptable. This is optional but I recommend it.
$ chopt disable lbac
Oracle Database 12.1.0.2
Oracle Label Security clean up in Oracle Database 12.1.0.2 is absolutely flawless and completes without any errors (at least in a standard installation where OLS isn’t configured).
$ sqlplus / as sysdba SQL> @?/rdbms/admin/catnools.sql
That’s it. No errors, no recompilation, no restart required.
Oracle Database 12.2.0.1
The removal of Oracle Label Security in Oracle Database 12.2.0.1 is a bit more tricky. The removal script catnools.sql does not exist. As a workaround I’m using the catnools.sql from an Oracle Database 12.1.0.2 installation. You’ll find more information in the Oracle Label Security Admin Guide.
The official take (thanks to Andreas Groetz for the hint to the document) on this is in MOS Note:2253235.1 – catnools.sql does not exist in 12.2.0.1:
- There’s no
catnools.sqlintentionally as the removal is not supported anymore - Therefore you should follow this procedure to disable Label security in your database:
EXEC LBACSYS.OLS_ENFORCEMENT.DISABLE_OLS;
- A restart of the database is required according to the documentation – and the procedure should be executed on all nodes in a RAC environment (??)
The same procedure – but for each PDB separately – will apply in case of Oracle Multitenant.
Below are the unsupported ways to clean up OLS completely.
non-CDB
If you’d like to go the old way (which does not seem to be supported anymore), you must copy or access the catnools.sql from an Oracle Database 12.1.0.2 installation.
$ cp $OH12102/rdbms/admin/catnools.sql $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba SQL> @$ORACLE_HOME12102/rdbms/admin/catnools.sql
You’ll find two invalid packages and package bodies afterwards. This is expected as I used an older catnools.sql from a previous installation. Clean it up with:
SQL> drop package LBAC_EXP; SQL> drop package OLS_ENFORCEMENT;
CDB
The OLS removal from a Multitenant database works the same way with one exception: you must use catcon.pl and you must remove OLS from all PDBs including the PDB$SEED first before removing it from CDB$ROOT. Otherwise you will see ORA-06598: insufficient INHERIT PRIVILEGES privilege errors.
At first you must copy or access the catnools.sql from an Oracle Database 12.1.0.2 installation.
$ cp $OH12102/rdbms/admin/catnools.sql $ORACLE_HOME/rdbms/admin
Afterwards start to remove OLS from all pluggable databases including the PDB$SEED:
$ cd $ORACLE_HOME/rdbms/admin $ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b catnools_pdbs -d '''.''' catnools.sql
In addition remove OLS now from CDB$ROOT:
$ cd $ORACLE_HOME/rdbms/admin $ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b catnools_cdb -d '''.''' catnools.sql
This leads to a number of invalid objects:
CON_ID OWNER OBJECT_TYPE OBJECT_NAME STATUS
------ ------------ ------------ ------------------------------ --------
1 SYS PACKAGE BODY LBAC_EXP INVALID
1 SYS PACKAGE BODY OLS_ENFORCEMENT INVALID
2 SYS PACKAGE BODY LBAC_EXP INVALID
2 SYS PACKAGE BODY OLS_ENFORCEMENT INVALID
3 SYS PACKAGE BODY LBAC_EXP INVALID
3 SYS PACKAGE BODY OLS_ENFORCEMENT INVALID
4 SYS PACKAGE BODY LBAC_EXP INVALID
4 SYS PACKAGE BODY OLS_ENFORCEMENT INVALID
8 rows selected.
Clean them up with a simple script containing:
$ vi clean_ols.sql
drop package LBAC_EXP;
drop package OLS_ENFORCEMENT;
Finally execute clean_ols.sql with catcon.pl.
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b clean_ols -d '''.''' clean_ols.sql
Afterwards OLS is completely removed from all containers and there are no invalid objects anymore.
Component Clean Up Series
- Remove and Clean Up Components from Oracle 11.2 – 12.2 – General Guidelines and Precautions
- APEX – Oracle Application Express Clean Up
- OWM – Oracle Workspace Manager Clean Up
- DV – Oracle Database Vault Clean Up
- OLS – Oracle Label Security Clean Up
- SDO – Oracle Spatial Data Option Clean Up
- CONTEXT – Oracle Text Clean Up
- ORDIM – Oracle Multi Media Clean Up
- XOQ – Oracle OLAP API Clean Up
- APS – Oracle OLAP Analytical Workspace Clean Up
- AMD – Oracle OLAP Catalog Clean Up
- OWB – Oracle Warehouse Builder Clean Up
- EXF/RUL – Oracle Expression Filters and Rules Manager Clean Up
- EM – Enterprise Manager Database Control Clean Up
- JAVAVM/XML – Oracle Java Virtual Machine and XDK Clean Up
- XDB – Oracle XML Database Clean Up
Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/
Hi Mike,
I was looking into removing OLS from my 12.2 CDB but according to “Catnools.sql Does Not Exist in 12.2.0.1 (Doc ID 2253235.1)” catnools.sql has been remove from 12.2 intentionally because it´s not supported to remove OLS.
How do you see this topic?
br
Andreas
Andreas,
many thanks for the hint – I wasn’t aware that there’s a DISABLE procedure in 12.2. I updated the blog post with your input. My way still will work of course. But if the official way provides a bullet proof environment as well in terms of license questions all should be fine (and of course the documented way is much easier and shorter than mine). I just wonder why you need to restart the database. And why you should repeat the action on all RAC nodes. The call IMHO gets executed in the database – and there’s only one package call EXEC LBACSYS.OLS_ENFORCEMENT.DISABLE_OLS;.
Furthermore I wonder where the user psmith_ols is coming from 😉 You may not need to login with this one I’d guess …
Thanks again!
Mike
Hey Mike great site. i never understood why it needed to become so difficult uninstalling products from 12.1 onwards.
A word of caution with the unofficial uninstall process for OLS:
We created a new db at 12.2 and ols got installed by mistake. We uninstalled it again using the undocumented method.
When we came to datapump from our existing 12.1 db (which didnt have ols) the import failed with the following errors:
Master table “SYS”.”IMPDP_DBNAME” successfully loaded/unloaded
Starting “SYS”.”IMPDP_DBNAME”: “/******** AS SYSDBA” parfile=impdp.par
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PROCESS_MARKER_STEP [TABLE_DATA:”SYS”.”IMPDP_DBNAME”]
BEGIN LBACSYS.OLS$DATAPUMP.system_callout_imp(:1); END;
ORA-31625: Schema LBACSYS is needed to import this object, but is unaccessible
ORA-01435: user does not exist
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.KUPW$WORKER”, line 12098
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
ORA-06512: at “SYS.KUPW$WORKER”, line 9737
ORA-06512: at “SYS.KUPW$WORKER”, line 9716
ORA-06512: at “SYS.KUPW$WORKER”, line 23641
—– PL/SQL Call Stack —–
object line object
handle number name
0xbd4fa818 32239 package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
0xbd4fa818 12119 package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
0xbd4fa818 23937 package body SYS.KUPW$WORKER.PROCESS_MARKER_STEP
0xbd4fa818 5123 package body SYS.KUPW$WORKER.LOAD_METADATA
0xbd4fa818 13108 package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
0xbd4fa818 2311 package body SYS.KUPW$WORKER.MAIN
0xbe386e20 2 anonymous block
Checking existence of new_seqno row
Type completion row exist count is: 0
Inserting new row at seqno: 100000002
Created type completion for seqno 100000002
Process order range is 491 to 491
In PROCESS_MARKER_STEP with marker 1
Looking for system callouts
Found system callout LBACSYS.OLS$DATAPUMP with tag LABEL_SECURITY, level 1 and connect_type SOFT
In function CONNECT_USER with connect type SOFT current connect type NONE current user SYS new user LBACSYS
In procedure DETERMINE_FATAL_ERROR with ORA-31625: Schema LBACSYS is needed to import this object, but is unaccessible
The workaround to get it to proceed was to create the lbacsys user. The import then gave one failure message but then continued to import everything else cleanly:
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
ORA-39342: Internal error – failed to import internal objects tagged with LABEL_SECURITY due to ORA-06550: line 1, column
7:
PLS-00201: identifier ‘LBACSYS.OLS$DATAPUMP’ must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
.
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
That user can then be locked but suggests somewhere its holding a memory of label security being installed.
James,
first of all, sorry for hitting such a trap. I was not aware of it.
And I wonder where this is coming from. Let me check this with my mates.
Thanks for the workaround!
Cheers,
Mike
James,
could you please provide us the logs of the removal of OLS?
That would be important – or, if you have opened an SR, send me the SR number?
email: mike.dietrich –at– oracle.com
Cheers,
Mike
Hi Mike
we hit the same here, after removing the OLS on a 18.6 “the classic way”. was james able to solve the issue?
Thx
Gabriel
Sorry for the double-post: to solve the issue, i reinstalled the OLS via DBCA. the error is gone.
Greetings
Gabriel