Oracle Label Security (OLS) clean up in Oracle Database 11.2-19c 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-19c
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 – 19c . 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.sql
intentionally 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 12.2.0.1
At first, please see MOS Note: 2253235.1 (Catnools.sql Does Not Exist in 12.2.0.1 ) where the author explains that the missing catnools.sql is intentionally since 12.2.0.1. I don’t know what the motivation is since I think that you should have the possibility to remove a component when you desire to. But I won’t comment this further on the blog.
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.
CDB 19.12.0
Again for Oracle 19c, please see MOS Note: 2253235.1 (Catnools.sql Does Not Exist in 12.2.0.1 ) where the author explains that the missing catnools.sql is intentionally since 12.2.0.1. I don’t know what the motivation is since I think that you should have the possibility to remove a component when you desire to. But I won’t comment this further on the blog.
But the removal process for OLS is very different in Oracle 19c. At first, there is still no catnools.sql. And the MOS notes asking you to “unlink” simply OLS are not correct either. Maybe the authors haven’t recognized that you can’t unlink OLS in 19c anymore. The chopt option is simply not there anymore.
In addition, there is now a dependency with Data Vault (DV). If you try to remove OLS as I showed in 12.2.0.1, you will end up with a ton of invalid objects, and many related to DV. So you need to remove DV at first.
This removal is a bit strange as well since the ?/rdbms/admin/dvremov.sql in 19c does not work explicitly for container databases. Luckily I found the 21c documentation example – and I have a 21c installation as well.
So this is what I did in order to remove DV at first.
Copy the Oracle 21c dvremov.sql into your 19c Oracle Home. Of course, this step is not essential as you can call it from your 21c home as well. But I like to have scripts in place:
cp $ORACLE_HOME/rdbms/admin/dvremov.sql $OH19/rdbms/admin
The execute it in the PDBs at first, afterwards in CDB$ROOT. Keep in mind that all PDBs need to be open. This step will fail if you didn’t use the 21c dvremov.sql script but the 19c version.
cd $ORACLE_HOME/rdbms/admin $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b dvremov_pdbs -d '''.''' dvremov.sql $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b dvremov_cdb -d '''.''' dvremov.sql
Good news: The script works flawless in 19c, and it does not leave any leftovers.
Now we can remove Label Security with the steps we used in 12.2.0.1. And again, you need to copy in either the 12.1.0.2 version of catnools.sql, or potentially the 11.2.0.4 version will work, too.
cp $OH12102/rdbms/admin/catnools.sql $ORACLE_HOME/rdbms/admin
Then initiate the removal process:
cd $ORACLE_HOME/rdbms/admin $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b catnools_pdbs -d '''.''' catnools.sql $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b catnools_cdb -d '''.''' catnools.sql
And of course, there are leftovers since nobody paid attention to make this script work in 19c. It still is a 12.1.0.2 script at the end of the day.
SQL> select con_id, object_name, object_type , owner from CDB_OBJECTS where status='INVALID' order by 1,2; CON_ID OBJECT_NAME OBJECT_TYPE OWNER ------ ------------------------ -------------------- ------------------ 1 CONFIGURE_OLS PROCEDURE SYS 1 IS_OLS_SUPPORTED FUNCTION SYS 1 LBAC_EXP PACKAGE BODY SYS 1 OLS_ENFORCEMENT PACKAGE BODY SYS 1 VALIDATE_OLS PROCEDURE SYS 2 CONFIGURE_OLS PROCEDURE SYS 2 IS_OLS_SUPPORTED FUNCTION SYS 2 LBAC_EXP PACKAGE BODY SYS 2 OLS_ENFORCEMENT PACKAGE BODY SYS 2 VALIDATE_OLS PROCEDURE SYS 3 CONFIGURE_OLS PROCEDURE SYS 3 IS_OLS_SUPPORTED FUNCTION SYS 3 LBAC_EXP PACKAGE BODY SYS 3 OLS_ENFORCEMENT PACKAGE BODY SYS 3 VALIDATE_OLS PROCEDURE SYS 4 CONFIGURE_OLS PROCEDURE SYS 4 IS_OLS_SUPPORTED FUNCTION SYS 4 LBAC_EXP PACKAGE BODY SYS 4 OLS_ENFORCEMENT PACKAGE BODY SYS 4 VALIDATE_OLS PROCEDURE SYS 5 CONFIGURE_OLS PROCEDURE SYS 5 IS_OLS_SUPPORTED FUNCTION SYS 5 LBAC_EXP PACKAGE BODY SYS 5 OLS_ENFORCEMENT PACKAGE BODY SYS 5 VALIDATE_OLS PROCEDURE SYS 25 rows selected.
So we need to create a cleanup_old.sql script:
drop PROCEDURE SYS.CONFIGURE_OLS; drop PROCEDURE SYS.VALIDATE_OLS; drop FUNCTION SYS.IS_OLS_SUPPORTED; drop PACKAGE BODY SYS.LBAC_EXP; drop PACKAGE BODY SYS.OLS_ENFORCEMENT;
Then execute it:
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b cleanup_ols -d '''.''' cleanup_ols.sql
And please remember that you will need the 12.1.0.2 catnools.sql and the 21c dvremov.sql scripts. And please don’t ask me why it needs to be this complicated …
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
On Non-CDB 12.2 RAC database with enabled XML DB, customer complained about the error messages during the execution of some user packages during which getting below error.
ERROR- ORA-04088: error during execution of trigger’LBACSYS.LBAC$BEFORE_ALTER’
Select comp_name, status from dba_registry.
COMP_NAME STATUS
—————————— ——————————————–
Oracle Database Catalog Views VALID
Oracle Database Packages and Types INVALID
Oracle Real Application Clusters VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle XML Database INVALID
Oracle Multimedia VALID
@?/rdbms/admin/utlrp.sql
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.XDB_PI_TRIG
ORA-06512: at “SYS.UTL_RECOMP”, line 923
ORA-04088: error during execution of trigger ‘LBACSYS.LBAC$BEFORE_ALTER’
ORA-00604: error occurred at recursive SQL level 3
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS
ORA-04067: not executed, package body “LBACSYS.LBAC_EVENTS” does not exist
ORA-06508: PL/SQL: could not find program unit being called: “LBACSYS.LBAC_EVENTS”
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called: “LBACSYS.LBAC_EVENTS”
ORA-06512: at line 2
ORA-06512: at “SYS.UTL_RECOMP”, line 27
ORA-06512: at “SYS.UTL_RECOMP”, line 61
ORA-06512: at “SYS.UTL_RECOMP”, line 787
ORA-06512: at line 4
# copied catnools.sql from 12.1 home and tried to clean lbacsys schema.
SQL> @?/rdbms/admin/catnools.sql
PL/SQL procedure successfully completed.
select value$ into l_ols_config_status from lbacsys.ols$props where NAME = ‘OLS_CONFIGURED_FLAG’;
*
ERROR at line 5:
ORA-06550: line 5, column 55:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 3:
PL/SQL: SQL Statement ignored
#Tried manually execute catnools.sql lines
SQL> DROP TRIGGER LBACSYS.lbac$after_drop;
DROP TRIGGER LBACSYS.lbac$after_drop
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.XDB_PI_TRIG
ORA-04088: error during execution of trigger ‘LBACSYS.LBAC$BEFORE_ALTER’
ORA-00604: error occurred at recursive SQL level 2
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS
ORA-04067: not executed, package body “LBACSYS.LBAC_EVENTS” does not exist
ORA-06508: PL/SQL: could not find program unit being called:
“LBACSYS.LBAC_EVENTS”
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called:
“LBACSYS.LBAC_EVENTS”
ORA-06512: at line 2
#using DBCA does not help, got same error in GUI.
#Disabled cluster mode and start one of the instance in a upgrade mode
srvctl stop database -db
startup nomount;
alter system set cluster_database=FALSE scope=spfile sid=’*’;
shutdown immediate;
startup upgrade;
:->sqlplus “/as sysdba”
SQL> @?/rdbms/admin/utlrp.sql
…
PL/SQL procedure successfully completed.
…
OBJECTS WITH ERRORS
——————-
53
….
ERRORS DURING RECOMPILATION
—————————
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> SQL> SQL>
##Still a lot of invalid objects.
SQL> SELECT owner, object_type, object_name FROM all_objects WHERE status = ‘INVALID’ and owner=’LBACSYS’ order by 1 ;
OWNER OBJECT_TYPE OBJECT_NAME
——————————————————————————————————————————– ———————– ——————————————————————————————————————————–
LBACSYS PACKAGE TO_LABEL_LIST
LBACSYS FUNCTION PRIVS_TO_CHAR
LBACSYS FUNCTION NUMERIC_LABEL_TO_LBAC
LBACSYS FUNCTION LBAC_LABEL_TO_NUMERIC
LBACSYS PACKAGE BODY TO_LABEL_LIST
LBACSYS FUNCTION PRIVS_TO_CHAR_N
LBACSYS PACKAGE BODY LBAC_SESSION
LBACSYS PACKAGE BODY LBAC_SERVICES
LBACSYS PACKAGE BODY LBAC_UTL
LBACSYS PROCEDURE SESSINFO_CLEANUP
LBACSYS PACKAGE BODY LBAC_LGSTNDBY_UTIL
OWNER OBJECT_TYPE OBJECT_NAME
——————————————————————————————————————————– ———————– ——————————————————————————————————————————–
LBACSYS PACKAGE BODY SA_AUDIT_ADMIN
LBACSYS PACKAGE BODY LBAC_SYSDBA
LBACSYS PACKAGE BODY LBAC_POLICY_ADMIN
LBACSYS PACKAGE BODY SA_COMPONENTS
LBACSYS FUNCTION TO_LBAC_LABEL_INTERNAL
LBACSYS FUNCTION TO_LBAC_LABEL
LBACSYS FUNCTION TO_LBAC_DATA_LABEL_INTERNAL
LBACSYS FUNCTION TO_LBAC_DATA_LABEL
LBACSYS FUNCTION TO_NUMERIC_DATA_LABEL
LBACSYS FUNCTION LBAC_LABEL_TO_CHAR
LBACSYS PACKAGE BODY LBAC$SA_LABELS
OWNER OBJECT_TYPE OBJECT_NAME
——————————————————————————————————————————– ———————– ——————————————————————————————————————————–
LBACSYS PACKAGE LBAC$SA
LBACSYS PACKAGE BODY LBAC$SA
LBACSYS PACKAGE BODY SA_SESSION
LBACSYS PACKAGE SA_UTL
LBACSYS PACKAGE BODY SA_UTL
LBACSYS FUNCTION NUMERIC_MERGE_LABEL
LBACSYS FUNCTION NUMERIC_GREATEST_LBOUND
LBACSYS FUNCTION NUMERIC_LEAST_UBOUND
LBACSYS FUNCTION NUMERIC_DOMINATES
LBACSYS FUNCTION OLS_LABEL_DOMINATES
LBACSYS FUNCTION NUMERIC_STRICTLY_DOMINATES
OWNER OBJECT_TYPE OBJECT_NAME
——————————————————————————————————————————– ———————– ——————————————————————————————————————————–
LBACSYS FUNCTION NUMERIC_DOMINATED_BY
LBACSYS FUNCTION NUMERIC_STRICTLY_DOMINATED_BY
LBACSYS PACKAGE SA_USER_ADMIN
LBACSYS PACKAGE BODY SA_USER_ADMIN
LBACSYS PACKAGE BODY OLS$DATAPUMP
LBACSYS PACKAGE BODY LBAC_EVENTS
39 rows selected.
SQL> alter package lbacsys.lbac_events compile body;
Warning: Package Body altered with compilation errors.
# rexecute of catnool does not help however at this time manual execution of lines works.
SQL> @$ORACLE_HOME/rdbms/admin/catnools.sql
PL/SQL procedure successfully completed.
select value$ into l_ols_config_status from lbacsys.ols$props where NAME = ‘OLS_CONFIGURED_FLAG’;
*
ERROR at line 5:
ORA-06550: line 5, column 55:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 3:
PL/SQL: SQL Statement ignored
SQL> DROP TRIGGER LBACSYS.lbac$after_drop;
Trigger dropped.
SQL> DROP TRIGGER LBACSYS.lbac$after_create;
Trigger dropped.
SQL> DROP TRIGGER LBACSYS.lbac$before_alter;
Trigger dropped.
SQL> DECLARE
CURSOR lbacroles IS
SELECT granted_role
FROM dba_role_privs
WHERE grantee = ‘LBACSYS’
AND granted_role like ‘%_DBA’
AND admin_option = ‘YES’;
CURSOR lbaccontexts IS
SELECT namespace
FROM dba_context
WHERE schema = ‘LBACSYS’;
CURSOR lbacsynonyms IS
SELECT synonym_name
FROM dba_synonyms
WHERE table_owner = ‘LBACSYS’;
rolename VARCHAR2(30);
BEGIN
— drop roles
FOR r IN lbacroles LOOP
dbms_output.put_line(‘Dropping role ‘ || r.granted_role );
BEGIN
EXECUTE IMMEDIATE ‘DROP ROLE ‘ ||
dbms_assert.enquote_name(r.granted_role,FALSE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Unable to drop role ‘ || r.granted_role);
END;
END LOOP;
— drop contexts
FOR c IN lbaccontexts LOOP
dbms_output.put_line(‘Dropping context ‘ || c.namespace);
BEGIN
EXECUTE IMMEDIATE ‘DROP CONTEXT ‘ ||
dbms_assert.enquote_name(c.namespace,FALSE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Unable to drop context ‘ || c.namespace);
END;
END LOOP;
— drop synonyms
FOR s IN lbacsynonyms LOOP
dbms_output.put_line(‘Dropping public synonym ‘ || s.synonym_name);
BEGIN
EXECUTE IMMEDIATE ‘DROP PUBLIC SYNONYM ‘ ||
dbms_assert.enquote_name(s.synonym_name,FALSE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Unable to drop synonym ‘ || s.synonym_name);
END;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL> EXECUTE LBACSYS.LBAC_CACHE.UPDATE_PROPS_TABLE(0, TRUE);
PL/SQL procedure successfully completed.
SQL> DROP USER LBACSYS CASCADE;
User dropped.
SQL> DELETE FROM exppkgact$ WHERE PACKAGE = ‘LBAC_UTL’;
0 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> SELECT owner, object_type, object_name FROM all_objects WHERE status = ‘INVALID’ and owner=’LBACSYS’ order by 1 ;
no rows selected
#enable cluster mode and start Database normally.
SQL> alter system set cluster_database=TRUE scope=spfile sid=’*’;
System altered.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
:->srvctl start database -db
:->sqlplus “/as sysdba”
SQL> set lin 300
SQL> select comp_name, status from dba_registry ;
COMP_NAME STATUS
————————————-
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Real Application Clusters VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle XML Database VALID
Oracle Multimedia VALID
8 rows selected.
Thanks 🙂
Mike
Hi Mike,
about 19c supported solutions.
You detail above only for unsupported solution on CDB 19.12.
What about 19.X non-CDB safe removal? Can I use straight only dvremove.sql contained in 19c OH for Vault & “EXEC LBACSYS.OLS_ENFORCEMENT.DISABLE_OLS;” for Label?
FYI: For dvremove.sql I’ve yet tried, before to see this article updates with 19c. It leaves invalid these:
* SYS.CDB_DV_STATUS VIEW
* PUBLIC CDB_DV_STATUS SYNONYM
that I think it may be safely removed reviewing its DDL and the fact that I don’t have a CDB database (I’m gonna verifying it too with an SR).
Thanks,
Federica
Hi Federica,
the gap here is between “supported” and “unsuppoerted”. I often tried to find a way to get rid of stuff realizing that teams had believed since 12.1 that everything is “CDB only” and no removal procedures are necessary anymore. The best approach is to check with Oracle Support and ask about the correct way.
If you have a leftover synonym and view, it usually is easy to get rid of it.
Cheers,
Mike
Anyone know if 21c version of dvremov.sql works on 18c? Would be nice to clone an 18c pdb to a new instance for upgrade that doesn’t include Data Vault.
Haven’t tried it but saw your other post – thanks,
Mike
FYI, I was able to run dvremov.sql on an 18c pluggable database and Data Vault was removed. You have to edit out the following from dvremov.sql first:
begin
execute immediate
‘SELECT status FROM sys.dba_dv_status WHERE name
= ”DV_APP_PROTECTION”’ INTO linkdvoff;
EXCEPTION
WHEN OTHERS THEN
–ignore if view does not exist.
— Bug 31118201: when DV is removed in root and this script is
— called from PDB, referencing dba_dv_status will fail with 65047.
IF SQLCODE IN ( -942, -65047) THEN NULL;
ELSE RAISE;
END IF;
IF (linkdvoff LIKE ‘ENABLED’) THEN
RAISE ora_error;
END IF;
end;
Thanks for the hint 🙂
Cheers
Mike
Hi Mike,
Somehow/when Oracle changed the deïnstallation of Oracle Database Vault. It was not possible in 19c and after a new search I stumbled on:
C.2 Deinstalling Oracle Database Vault https://docs.oracle.com/en/database/oracle/oracle-database/19/dvadm/postinstallation-oracle-database-vault-procedures.html#GUID-5B68C4CE-A162-48FF-AD1A-475B8D3AD112
Any experience with removing Oracle Database Vault and Oracle Label Security in 19c?
Hi Laurens,
this is why I did not write about DV removal yet. Please check with Oracle Support for the right steps.
Cheers
Mike
Thx. Will do and try to remember to put the outcome here.
Thanks 🙂
Mike
From Oracle Support:
Dear Customer,
Please find the inline answers for your qn
Questions on 1):
We have an Oracle 19 CDB Oracle Database environment.
A) Is there also a way to deinstall Label Security and Database Vault in Oracle 19 CDB Oracle Database environment?
ANS: Note : From 12.2.0.1 onwards (up to 19c DB) Database Vault can be disabled/re-enabled, but Database Vault can’t be uninstalled in CDB environment ( dvremov.sql can’t be run in CDB environment).
B) If Yes, then what are the best practices in deïnstalling them both, order, instruction? NA
Questions on 2):
The result of the query on V$OPTION in the CDB and PDB are both:
SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;
PARAMETER VALUE CON_ID
—————————— ——- ———-
Oracle Database Vault FALSE 0
We have also executed the query for ‘Oracle Label Security’ and the output for CDB and PDB are both:
SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Label Security’;
PARAMETER VALUE CON_ID
—————————— ——- ———-
Oracle Label Security FALSE 0
ANS: By default or during DBCA you might have selected both DV/OLS so its visible on dba_Registry.
C) Does this prove the Oracle Database Vault and Oracle Label Security is truly deinstalled?
We find the following in dba_Registry for CDB and PDB :
select COMP_ID,COMP_NAME, STATUS, VERSION from dba_Registry where COMP_ID in (‘DV’,’OLS’);
COMP_ID COMP_NAME STATUS VERSION
——- —————————— ——- ———–
OLS Oracle Label Security VALID 19.0.0.0.0
DV Oracle Database Vault VALID 19.0.0.0.0
This confuses us.
ANS: By default or during DBCA you might have selected both DV/OLS so its visible on dba_Registry but the v$option shows whether its enabled or disabled ,In your case the components is installed but its not enabled.
D) Does this prove the Oracle Database Vault and Oracle Label Security is installed?
ANS: Yes its intslled but not enabled at DB layer
E) Do we need licenses for ‘Oracle Database Vault’ and ‘Oracle Label Security’ just because this is present in “DBA_REGISTRY” ?
No its not needed , When Oracle Database Vault is configured and enabled, it configures and enables Oracle Label Security. Oracle Label Security is solely restricted for use by the Oracle Database Vault software. If you want to create Oracle Label Security policies, then you must purchase a full-use license for Oracle Label Security.
F) If Yes, then the answers on question A and B are very important to us.
ANS :NA
G) Do we NOT need licenses for ‘Oracle Database Vault’ and ‘Oracle Label Security’ since the value is “FALSE” in V$OPTION ?
ANS : NA
Thanks for the update!
Cheers,
Mike
Had a few issues doing this on Windows and Oracle 19.17. Got some help from Mike (thank you), list my changes here if someone else have the same issue. My change was to use -c instead of the -C and specifying the pdbs (I have only one) in the first execution, and then the same again for the cdb$root. I also changed the directory from ”’.”’ to %ORACLE_HOME%/rdbms/admin
cd %ORACLE_HOME%/rdbms/admin
%ORACLE_HOME%/perl/bin/perl catcon.pl -n 1 -c ‘LK1916’ -e -b dvremov_lk1916 -d %ORACLE_HOME%\rdbms\admin dvremov.sql
%ORACLE_HOME%/perl/bin/perl catcon.pl -n 1 -c ‘CDB$ROOT’ -e -b dvremov_cdb -d %ORACLE_HOME%\rdbms\admin dvremov.sql
— Remove OLS
cd $ORACLE_HOME/rdbms/admin
%ORACLE_HOME%/perl/bin/perl catcon.pl -n 1 -c ‘lk1916’ -e -b catnools_lk1916 -d %ORACLE_HOME%\rdbms\admin catnools.sql
%ORACLE_HOME%/perl/bin/perl catcon.pl -n 1 -c ‘CDB$ROOT’ -e -b catnools_cdb -d %ORACLE_HOME%\rdbms\admin catnools.sql
%ORACLE_HOME%/perl/bin/perl catcon.pl -n 1 -e -b cleanup_ols -d %ORACLE_HOME%\rdbms\admin cleanup_ols.sql