Procedure to disable database vault in Oracle Snapshot Standby

Procedure to disable database vault in Oracle Snapshot Standby

Step 1) Verify if the db is in snapshot standby mode

set lines 300
set pages 3000
select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
select process,status,client_process,sequence# from gv$managed_standby;

Actual output:

SQL> select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 NAME      SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------------------------ --------- --------------------
GGSRC04T  READ WRITE           SNAPSHOT STANDBY GGSRC04TSB1                    GGSRC04T  NOT ALLOWED

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
LNS       CONNECTED    LNS               0
DGRD      ALLOCATED    N/A               0
8 rows selected.


Step 2) Check if the database vault is enabled in the DB

col parameter for a30
col value for a20
select parameter,value from v$option where upper(parameter) like '%VAULT%';

Actual output:

SQL> select parameter,value from v$option where upper(parameter) like '%VAULT%';
PARAMETER                      VALUE
------------------------------ --------------------
Oracle Database Vault          TRUE


Step 3) Connect as database vault owner, disable the database vault using the below command

conn dbv_owner/dbv_owner1#
exec dbms_macadm.disable_dv;

Actual output:

SQL> conn dbv_owner/dbv_owner1#
Connected.

SQL> exec dbms_macadm.disable_dv;
PL/SQL procedure successfully completed.
SQL>

alert log:

2023-09-04 11:19:45.918000 +01:00
Database Vault is disabled.
2023-09-04 11:20:24.269000 +01:00
Shutting down instance (immediate) (OS id: 25339)

Step 4) Restart the DB

shu immediate;
startup;
set lines 300
set pages 3000
select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
select process,status,client_process,sequence# from gv$managed_standby;

Actual output:

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

SQL> startup;
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size                  8625856 bytes
Variable Size            1442840896 bytes
Database Buffers         1761607680 bytes
Redo Buffers                8151040 bytes
Database mounted.
Database opened.

SQL> select name,open_mode,database_role,db_unique_name,name ,SWITCHOVER_STATUS from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    DB_UNIQUE_NAME                 NAME      SWITCHOVER_STATUS
--------- -------------------- ---------------- ------------------------------ --------- --------------------
GGSRC04T  READ WRITE           SNAPSHOT STANDBY GGSRC04TSB1                    GGSRC04T  NOT ALLOWED

SQL> select process,status,client_process,sequence# from gv$managed_standby;
PROCESS   STATUS       CLIENT_P  SEQUENCE#
--------- ------------ -------- ----------
ARCH      CONNECTED    ARCH              0
DGRD      ALLOCATED    N/A               0
DGRD      ALLOCATED    N/A               0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
ARCH      CONNECTED    ARCH              0
LNS       CONNECTED    LNS               0
DGRD      ALLOCATED    N/A               0
8 rows selected.

Step 5) Verify if the vault is still active

col parameter for a30
col value for a20
select parameter,value from v$option where upper(parameter) like '%VAULT%';

Actual output:

SQL> select parameter,value from v$option where upper(parameter) like '%VAULT%';
PARAMETER                      VALUE
------------------------------ --------------------
Oracle Database Vault          FALSE
SQL>

YouTube link:

Thanks