Exadata Diagnostic Support Tools

 

Below are Tools available in Exadata, to upload logs to identify the issue. With updated models, tools may add / remove / merge. Please always refer to Oracle.com for latest update.

Note: This document is prepared by my friend Mr. Bakht Ali Khan (Oracle DBA).

1. SOSReport:

   ==========

   The is a tool to collect troubleshooting data on an Oracle Linux system.

   Among other Info its also includes about the installed rpm versions, syslog, network configuration, 

   mounted filesystems, disk partition details, loaded kernel modules and status of all services

   

   # rpm -qa | grep -i sos => To check rpm installation

   # yum install sos => Installing sos rpm

   # sosreport => Generating sos report

     --  

   # /opt/oracle.SupportTools/sundiag.sh -h

   

   How To Collect Sosreport on Oracle Linux (Doc ID 1500235.1)  

    

2. OSWatcher:

   ==========

   

   Oracle OSWatcher is a utility that collects data from commands such as vmstat, iostat, top, ps, netstat, 

   HP-UX sar, and Linux meminfo etc. OSWatcher archives the data files, automatically looks for issues, 

   and helps to determine the root cause of the issues, if possible.  

   

   Can download as standalone (osw<version>.tar) or Part of TFA support tool

   

   Doc - 301137.1 : OSWatcher (Includes: [Video])

   

3. ExaWatcher:

   ===========

   

   ExaWatcher produces a small set of charts when a collection is done. These charts are contained in the   Charts.ExaWatcher.<nodeName> directory within the collection. Charts can only be generated for the  following ExaWatcher collections:

   vmstat, iostat, mpstat, IBCardInfo, zonestat, meminfo, ldm, kstat etc

   The computer where you are viewing the charts must be connected to the internet.

   

   # ps -ef | grep -i ExaWatcher

   # cd /opt/oracle.ExaWatcher/

   # ./GetExaWatcherResults.sh --from 08/15/2022_13:00:00 --to 08/15/2022_14:00:00

     OR

   # /opt/oracle.ExaWatcher/GetExaWatcherResults.sh --from 10/14/2014_13:00:00 --to 10/14/2014_17:00:00

   # /opt/oracle.ExaWatcher/archive/ExtractedResults => (output directory)

   # cd  /opt/oracle.ExaWatcher/archive/ExtractedResults

   # ls -la

   # du -sh *

   

   File size should more than 10M or 100M

   

   Doc - 1617454.1 : ExaWatcher Utility On Exadata and SuperCluster Compute and Storage Nodes 

   

4. SUNDIAG:

   ========

   Oracle Exadata Diagnostic Information required for Disk Failures and some other Hardware issues.

   sundiag is an Exadata node/cell tool.  It works on both Linux and Solaris installations.

   Execution will create a date stamped tar.bz2 file in /tmp/sundiag_/tar.bz2. Upload this on SR.

   

   Run as root on the compute node or cell server:

      # /opt/oracle.SupportTools/sundiag.sh -h (Help to see detail)

      # /opt/oracle.SupportTools/sundiag.sh [ilom | snapshot] [osw <time ranges>]

     --

   # /opt/oracle.SupportTools/sundiag.sh

   # /opt/oracle.SupportTools/sundiag.sh ilom

   # /opt/oracle.SupportTools/sundiag.sh snapshot

   # /opt/oracle.SupportTools/sundiag.sh osw <from>-<to> e.g <date>_<time>-<date>_<time>

   # /opt/oracle.SupportTools/sundiag.sh osw 2014/03/31_15:00:00-2014/03/31_18:00:00 (Not < 9 hours)

   

x.  Procwatcher:

    ============

It is a tool to examine and monitor Oracle database and/or clusterware processes at an interval.   

The tool will collect stack traces of these processes using Oracle tools like oradebug short_stack 

and/or OS debuggers like pstack, gdb, dbx, or ladebug and collect SQL data if specified.

This tool can be used in conjunction with other tools or troubleshooting methods depending on the situation.

To install the script, simply download it put it in its own directory, unzip it, and give it execute permissions.

$ ./prw.sh stat - To check status

$ tfactl prw stat - To check the status if running inside of TFA.

$ ./prw.sh start - To start Procwatcher

$ ./prw.sh pack - To package up Procwatcher files to upload to support

Doc - 459694.1 : Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes

Procwatcher is Ideal for:

    Session level hangs or severe contention in the database/instance. See Note: 1352623.1

    Severe performance issues. See Note: 1352623.1

    Instance evictions and/or DRM timeouts.

    Clusterware or DB processes stuck or consuming high CPU (must set EXAMINE_CLUSTER=true and run as root for clusterware processes)

    ORA-4031 and SGA memory management issues. (Set sgamemwatch=diag or sgamemwatch=avoid4031 (not the default). See Note: 1355030.1

    ORA-4030 and DB process memory issues. (Set USE_SQL=true and process_memory=y).

    RMAN slowness/contention during a backup. (Set USE_SQL=true and rmanclient=y).

   5. ORAchk:

   =======

   

6. Exachk:

   =======

   Exachk is an Oracle Exadata diagnostic tool that comes with different levels of verification and

   collects hardware, software, firmware, Network Fabric Switches and configuration data on Exa systems.

   In addition to the automatic correction feature, individual checks have explanations, recommendations, 

   and manual verification commands so that customers and administrators can evaluate the risks of 

   and self-correct reported conditions.

 

   # ps -ef | grep exachk

   # cd /opt/oracle.SupportTools

   # ./exachk -d status

   # ./exachk -v

   

   How do I install only ORAchk or EXAchk without TFA?

   We can install with the following command, which will not install or alter any existing TFA installlation:

   # ahf_setup (Installation clude TFA, ORAchk, Exachk etc)

   # tfactl version -all

   # ahf_setup -extract orachk (Installation included only ORAchk)

   # ahf_setup -extract exachk (Installation included only Exachk)

   

   TFA, ORAchk & EXAchk work just the same as before. AHF is simply a combined installer for them.

   TFA is still the primary tool for diagnostic collection and management. ORAchk/EXAchk is still 

   the primary tool for proactively checking your system.

   The move to AHF is intended to make it easier, with a single installation with all tools in a single location.

   

7. ADR:

   ====

   The Automatic Diagnostics Repository (ADR) is a hierarchical file-based repository for handling diagnostic information.

   

   sql> select name,value from v$diag_info;

   

   $ adrci    

   $ adrci> SHOW PROBLEM 

   $ adrci> SHOW INCIDENT 

   $ adrci> SHOW CONTROL

   

   Doc - 422893.1 : Understanding Automatic Diagnostic Repository 

8. Oracle Trace File Analyzer (TFA):

   =================================

   Oracle Trace File Analyzer (TFA) provides a number of diagnostic tools in a single bundle, making it easy 

   to gather diagnostic information about the Oracle database and clusterware, which in turn helps with problem 

   resolution when dealing with Oracle Support.  

   

9. Autonomous Health Framework (AHF):   

   ==================================

   The Trace File Analyzer (TFA) is now part of the Autonomous Health Framework (AHF) described. AHF is collection of

   many tools i.e. EXAchk, ORAchk etc

 Oracle AHF is a collection of components that analyzes the diagnostic data collected, and proactively identifies issues 

   before they affect the health of your Database, clusters or your Oracle Real Application Clusters databases.

   

x. References:

 Doc - 1539451.1 : How to shutdown the Exadata database nodes and storage cells in a rolling fashion so certain hardware tasks can be performed. 

 Doc - 1093890.1 : Steps To Shutdown/Startup The Exadata & RDBMS Services and Cell/Compute Nodes On An Exadata Configuration

Doc - 19c E95727-09 - April 2021 : Clusterware Administration and Deployment Guide 

Doc - 1683842.1 : SRDC - EEST Sundiag

Doc -  761868.1 : Oracle Exadata Diagnostic Information required for Disk Failures and some other Hardware issues

Doc - 2799587.1 : Gathering sundiag in Exadata image version 20.1.11 and 20.1.12 fail with syntax error

 Doc - 1070954.1 : Oracle Exadata Database Machine EXAchk

Doc - 2673298.1 : How To Restart Exachk Daemon

Doc - 2550798.1 : Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAchk 

 

Oracle Audit Vault and Database Firewall (AVDF) 20.7 - Part 2

Agent installation for Oracle Database 

----Install Agent 

Step#1

chown oracle:oinstall agent.jar

export JAVA_HOME=$ORACLE_HOME/jdk

export PATH=$JAVA_HOME/bin:$PATH

Step#2

Make directory:

/u01/app/oracle/avdf_agent

Step#3

Download agent.jar file from AVDF console

Step#4

[oracle@billdb jdk]$ cd /u01/app/oracle/avdf_agent

[oracle@billdb avdf_agent]$ java -jar agent.jar -d /u01/app/oracle/avdf_agent

Agent installed successfully.

If deploying hostmonitor please refer to product documentation for additional installation steps.

[oracle@billdb avdf_agent]$

Start agent:

Use the activation key mentioned in step#3

BILLDB::OIX2-KFAA-IY8M-PLW#-0SA9

[oracle@billdb bin]$ ./agentctl start -k

Enter Activation Key:

Checking for updates...

Agent is updating. This operation may take a few minutes. Please wait...

Agent updated successfully.

Agent started successfully.

[oracle@billdb bin]$

Verify agent status:

[oracle@billdb bin]$ pwd

/u01/app/oracle/avdf_agent/bin

[oracle@billdb bin]$ ./agentctl status

Agent is running.

[oracle@billdb bin]$

Step#5

Login as superuser and verify agent status

Step#6

Perform below steps in target DB (BILLDB)

sqlplus as sysdba

create user avdfuser identified by Oracle_123;

[oracle@billdb config]$ pwd

/u01/app/oracle/avdf_agent/av/plugins/com.oracle.av.plugin.oracle/config

[oracle@billdb config]$ sqlplus / as sysdba

Version 19.3.0.0.0

SQL> show user

USER is "SYS"

SQL> show parameter db_name

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name      string billdb

--SETUP for granting Audit Collection and Audit Policy Management privileges

SQL> start /u01/app/oracle/avdf_agent/av/plugins/com.oracle.av.plugin.oracle/config/oracle_user_setup.sql AVDFUSER SETUP

Session altered.

Enter username for granting privileges:

Setting username to 'AVDFUSER'

Enter mode (SETUP/SPA/ENTITLEMENT):

SETUP for granting Audit Collection and Audit Policy Management privileges

SPA for granting Stored Procedure Auditing privileges

ENTITLEMENT for granting Entitlement retrieval privileges

Setting mode to 'SETUP'

Granting privileges to "AVDFUSER" ... Done.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@billdb config]$ 

--SPA for granting Stored Procedure Auditing privileges

[oracle@billdb config]$ sqlplus / as sysdba

SQL> start /u01/app/oracle/avdf_agent/av/plugins/com.oracle.av.plugin.oracle/config/oracle_user_setup.sql AVDFUSER SPA

Session altered.

Enter username for granting privileges:

Setting username to 'AVDFUSER'

Enter mode (SETUP/SPA/ENTITLEMENT):

SETUP for granting Audit Collection and Audit Policy Management privileges

SPA for granting Stored Procedure Auditing privileges

ENTITLEMENT for granting Entitlement retrieval privileges

Setting mode to 'SPA'

Granting privileges to "AVDFUSER" ... Done.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

--ENTITLEMENT for granting Entitlement retrieval privileges

[oracle@billdb config]$ sqlplus / as sysdba

start /u01/app/oracle/avdf_agent/av/plugins/com.oracle.av.plugin.oracle/config/oracle_user_setup.sql AVDFUSER ENTITLEMENT

Session altered.

Enter username for granting privileges:

Setting username to 'AVDFUSER'

Enter mode (SETUP/SPA/ENTITLEMENT):

SETUP for granting Audit Collection and Audit Policy Management privileges

SPA for granting Stored Procedure Auditing privileges

ENTITLEMENT for granting Entitlement retrieval privileges

Setting mode to 'ENTITLEMENT'

Granting privileges to "AVDFUSER" ... Done.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Step#7

select granted_role from dba_role_privs where grantee='AVDFUSER';

GRANTED_ROLE

--------------------------------------------------------------------------------

AUDIT_ADMIN

AUDIT_VIEWER

RESOURCE

select privilege from dba_sys_privs where grantee='AVDFUSER';

PRIVILEGE

----------------------------------------

AUDIT ANY

AUDIT SYSTEM

CREATE SESSION

Step#8

col owner format a10

col table_name format a15

col tablespace_name format a12

col interval format a20

set lines 300

select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS';

SQL> 

create tablespace avdf_aud_data

datafile '/u01/app/oracle/oradata/BILLDB//avdf_aud_data01.dbf' size 2048m;

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(

audit_trail_type => dbms_audit_mgmt.audit_trail_unified,

audit_trail_location_value => 'AVDF_AUD_DATA');

END;

/

BEGIN

DBMS_AUDIT_MGMT.INIT_CLEANUP(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

DEFAULT_CLEANUP_INTERVAL => 1 );

END;

/

BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,

AUDIT_TRAIL_PURGE_INTERVAL => 1,

AUDIT_TRAIL_PURGE_NAME => 'CLEANUP_OS_DB_AUDIT_RECORDS',

USE_LAST_ARCH_TIMESTAMP => TRUE );

END;

/

PL/SQL procedure successfully completed.

SQL> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner='AUDSYS';

AUDSYS

AUD$UNIFIED

INTERVAL '1' MONTH

RANGE             1048575 AVDF_AUD_DATA

Step#9

Login as superaudit user

Step#10

Issues Faced:

Retrieving Audit Setting Using AVDF Fails With 'Failed to connect to DB' Error (Doc ID 2589826.1)

Added target DB tns entry in AVDF Appliance machine.

1. Connect to the AV Server "support" account via a terminal session (like putty)

2. Execute: su - root

3. Execute: su - oracle

4. Execute: /usr/local/dbfw/bin/javafwk stop

5. Execute: /usr/local/dbfw/bin/javafwk start

6. Check to see if java process is executing: ps -ef | grep java.

7. If there is a java process go to step 9

8. If there no java process, please create a Service Request with Support.

9. Connect to the AV Console as the AV Admin and stop and start the AV Audit Trails.

Oracle Audit Vault and Database Firewall (AVDF) 20.7 - Part 1

 

Installation of Oracle AVDF 20.7 Installation on Oracle Virtual Box

Oracle Audit Vault and Database Firewall (AVDF) is a complete Database Activity Monitoring (DAM) solution that combines native audit data with network-based SQL traffic capture. AVDF includes an enterprise quality audit data warehouse, host-based audit data collection agents, powerful reporting and analysis tools, alert framework, audit dashboard, and a multi-stage Database Firewall. The Database Firewall uses a sophisticated grammar analysis engine to inspect SQL statements before they reach the database and determines with high accuracy whether to allow, log, alert, substitute, or block the incoming SQL. AVDF comes with collectors for Oracle Database, Oracle MySQL, Microsoft SQL Server, PostgreSQL, IBM Db2 (on LUW), SAP Sybase, Oracle Key Vault, Microsoft Active Directory, Linux, Windows, AIX, Solaris, and HPUX. A Quick-JSON collector simplifies ingesting audit data from databases like MongoDB. In addition to the provided collectors, AVDF's extensible framework allows simple configuration-based audit collection from JDBC-accessible databases and REST, JSON, or XML sources, making collection from most other systems easy. A full featured Java SDK allows creation of collectors for applications or databases that don't use a standard technology to record their audit trail [https://www.oracle.com/].

Features:

1.    Fine-Grained, Customizable Reporting, and Alerting
2.    Enterprise Audit Data Consolidation and Lifecycle Management
3.    Deployment Flexibility and Scalability

Figure AVDF Architecture (https://docs.oracle.com)

LAB Environment:

OS: OE Linux 7.9

Virtual Box: 6.1.36

Note: For newer versions, the installation screen may change.

Step#1: Media selection from Oracle (https://edelivery.oracle.com/) 

**Valid CSI-based account required

Type AVDF 20.7 and Press Search

Step#2: VM (AVDF appliance) Creation in Virtual BOX

Recommendations 450 GB disk space

8GB RAM

*** if above storage and memory not available, installation will not continue.

AVDF ISO image selection from downloaded media, and Press Ok

Step#3: AVDF Installation

Note: It will take approximately 3+ hours, on local desktop/laptop machine

AVDF Appliance installation screen and Press ENTER

No input required, Just watch and see

Step#4: Root Password and IP address requirement

Important Screen, verify the information before proceeding

Press Ok, no input required for next few screens

Almost last step, Installation successfully completed, Congratulation!

Enter root password entered previously

Verify OS level Services

ASM and DB instance

First-time logon as Root user

Step#5: Password change for Superuser

Enter the password for various users, and note their password it will be required later on in AVDF administration.

In the next blog, we will discuss agent installation and configurations.

Credit goes to below URLs:

URLs:

https://docs.oracle.com/en/database/oracle/audit-vault-database-firewall/20/sigig/install.html#GUID-A295ABF9-E2D5-4E52-BB4E-A7B68119B065

https://christian-gohmann.de/2020/12/21/installation-of-database-firewall-20-1/

https://asktom.oracle.com/pls/apex/f?p=100:551::::551:P551_CLASS_ID,P551_INVITED:17425,N&cs=16B63BD8CBB6E642C1CA65A1829F3ED7F

https://www.youtube.com/watch?v=dFeyVmfAu78

https://gavinsoorma.com.au/knowledge-base/oracle-audit-vault-database-firewall-20-4-part-1

https://eclipsys.ca/oracle-audit-vault-20-4-part-1-installation/

ASM Disk adding in Virtual Box 6.1

 

How to add new ASM disk in virtual box

Step#1

Create shared disk

Step#2

Mount on Node 1 and Node2

Step#3

Step#4

Format Newly added disk

fdisk -l

fdisk /dev/sdh           *** disk shown by fdisk -l

n, p, default, default, default, w

Step#5

Perform on node 1

oracleasm scandisks

oracleasm listdisks

oracleasm createdisk  ASM_IMRAN  /dev/sdh1

Step#7

Perform  on node 2

oracleasm scandisks

oracleasm listdisks

Step#8

Using ASMCA, Create Disk group, and mount it on all nodes

Removing:

sysasm:

SQL> drop diskgroup DGIK including contents

[root@dbwr1 ~]# oracleasm deletedisk ASM_IMRAN

Clearing disk header: done

Dropping disk: done

[root@dbwr1 ~]# oracleasm listdisks

ASMDISK_DATA

ASMDISK_OCR

ASMDISK_RECO

[root@dbwr1 ~]# 

[root@dbwr1 ~]#

Oracle Database Vault in 12.2.0.1 - Part 5

 Database Vault  RULESET (Complete) Example without GUI.

Note: This code is for test education purposes only and the idea is taken from the book "Oracle 11g Anti-hacker's Cookbook" Chapter 7.

Create RULESET for "end of the month reporting". It will only allow creating a view in the HR schema on the last day of every month only.

sqlplus c##dbowner1/PAkistan123##@BILL_PDB1

--- Create Rule: Evaluate to HR user sessions only

BEGIN

DVSYS.DBMS_MACADM.CREATE_RULE(

rule_name => 'Rule_Evaluate_HR_user',

rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''HR'''

);

END;

/

--- Create Rule: Evaluate to Last Month of the day

BEGIN

DVSYS.DBMS_MACADM.CREATE_RULE(

rule_name  => 'Rule_Evaluate_Is_lastdayofmonth',

rule_expr =>'(LAST_DAY(TRUNC(SYSDATE)) - TRUNC(SYSDATE)) = 0'  

);

END;

/

------ Create Ruleset as Ruleset_Month_End

----- This ruleset restrict VIEW creation in HR schema only due to sub-rule "Rule_Evaluate_HR_user"

BEGIN

DVSYS.DBMS_MACADM.CREATE_RULE_SET(

rule_set_name => 'Ruleset_Month_End',

description => 'Create views for end of the month reporting',

enabled => DVSYS.DBMS_MACUTL.G_YES,

eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL, -- all rules must be true,

audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL, -- no audit

fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,

fail_message => 'You are not allowed to create reports until the end of the month',

fail_code => 20999,

handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,

handler => NULL

);

END;

/

--- Add Rules created above to to Ruleset

BEGIN

  DBMS_MACADM.ADD_RULE_TO_RULE_SET(

     rule_set_name => 'Ruleset_Month_End',

        rule_name => 'Rule_Evaluate_HR_user',

        rule_order => 1

);

 DBMS_MACADM.ADD_RULE_TO_RULE_SET(

        rule_set_name => 'Ruleset_Month_End',

        rule_name => 'Rule_Evaluate_Is_lastdayofmonth',

        rule_order => 1

);

END;

/

--- Add Command to Ruleset "Ruleset_Month_End" to restrict CREATE View command

BEGIN

  DBMS_MACADM.CREATE_COMMAND_RULE (

     command => 'CREATE VIEW',

     rule_set_name => 'Ruleset_Month_End',

     object_owner => 'HR',

     object_name => '%',

     enabled => DBMS_MACUTL.G_YES);

END;

/

--- Logon as HR and Verify if HR user allowed to create View 

sqlplus HR/PAkistan123##@BILL_PDB1

SQL> create or replace view salaries_and_commissions as select

first_name,last_name,salary,commission_pct from employees where

commission_pct is not null;  2    3

first_name,last_name,salary,commission_pct from employees where

                                                *

ERROR at line 2:

ORA-47306: 20999: You are not allowed to create reports until the end of the

month

SQL> select sysdate from dual;

SYSDATE

---------

12-AUG-22 >>>>> It is not month end

---------------------------------

For other users like scott etc

---------------------------------

SQL> show user

USER is "SCOTT"

SQL> select sysdate from dual;

SYSDATE

---------

12-AUG-22

SQL>  create view vtst2 as select * from stest;

View created.

--- Connect as SYS user, and Change date to Last month

SQL> CONN SYS/PAkistan123##@BILL_PDB1 AS SYSDBA

Connected.

SQL> ALTER SYSTEM SET FIXED_DATE='31-AUG-2022';

System altered.

---  Logon as HR again, and verify if View creation is allowed

sqlplus hr/PAkistan123##@BILL_PDB1

SQL> select sysdate from dual;

SYSDATE

---------

31-AUG-22

SQL> create or replace view salaries_and_commissions as select

first_name,last_name,salary,commission_pct from employees where

commission_pct is not null;  2    3

View created.

==============================

---  The user with the DBV_ACCTMGR role can only grant the connect role

SQL> conn sys/PAkistan123##@BILL_PDB1 as sysdba

Connected.

SQL> grant create table to scott;

Grant succeeded.

SQL>

Views to verify:

select * from DVSYS.DBA_DV_RULE_SET WHERE RULE_SET_NAME IN ('Ruleset_Month_End')

SELECT * FROM DVSYS.DBA_DV_COMMAND_RULE WHERE RULE_SET_NAME IN ('Ruleset_Month_End');

SELECT * FROM DVSYS.DBA_DV_COMMAND_RULE WHERE COMMAND IN ('CREATE TABLE');

SELECT * FROM DVSYS.DBA_DV_RULE WHERE NAME in ('Rule_Evaluate_HR_user','Rule_Evaluate_Is_lastdayofmonth')

select * from DVSYS.DBA_DV_REALM;

select * from DVSYS.DBA_DV_REALM_AUTH;

select * from DVSYS.DBA_DV_REALM_OBJECT;

Credit Goes to:

https://netsoftmate.com/enable-database-vault-on-rac-database/

https://www.oreilly.com/library/view/oracle-11g-anti-hackers/9781849685269/ch07s02.html

Oracle Database Vault in 12.2.0.1 - Part 4

 Database Vault RULESET usage examples without GUI.

Note: This code is for test education purposes only.

Ruleset Definition:

As their name denotes, rulesets are a collection of rules that in turn consist of logical statements, which might evaluate to true or false. Because of their capacity for evaluation, rulesets can be associated with command rules, realm authorization, and factor assignment, as well as secure application roles. According to the true and false value of the rule, the value of the rule set itself is either "true" or "false". There are two choices: ALL or ANY. When defining command rules or authorizing a user to access a certain field, a certain set of rules can be set as a condition. When the user executes the SQL command defined in the command rule, Vault first calculates the associated rule set. If the rule set value is "true", the command is allowed to be executed, and if the value of the rule set is "false", the execution is rejected

sqlplus /nolog

conn c##dbowner1/PAkistan123##@BILL_PDB1

Create Ruleset...Reporting from HR Views

BEGIN

DVSYS.DBMS_MACADM.CREATE_RULE_SET(

rule_set_name => 'Reporting from HR Views',

description => 'Reporting from HR Views',

enabled => DVSYS.DBMS_MACUTL.G_YES,

eval_options => DBMS_MACUTL.G_RULESET_EVAL_ALL, -- all rules must be true,

audit_options => DBMS_MACUTL.G_RULESET_AUDIT_FAIL, -- no audit

fail_options => DBMS_MACUTL.G_RULESET_FAIL_SILENT,

fail_message => 'You are not allowed to report from this view',

fail_code => 20998,

handler_options => DBMS_MACUTL.G_RULESET_HANDLER_OFF,

handler => NULL

);

END;

/

PL/SQL procedure successfully completed.

--------------------------------

SQL> conn c##dbmanager1/PAkistan123##@BILL_PDB1

Connected.

SQL> create user hayat identified by PAkistan123##;

User created.

SQL> grant create session,connect to hayat;

Grant succeeded.

--------------------------------

Evaluate HAYAT user

BEGIN

DVSYS.DBMS_MACADM.CREATE_RULE(

rule_name => 'Rule_Hayat',

rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''HAYAT'''

);

END;

/

PL/SQL procedure successfully completed.

--------------------------------

SQL> conn HR/PAkistan123##@BILL_PDB1

Connected.

SQL>

SQL>

SQL> grant select on emp_details_view to hayat;

Grant succeeded.

--------------------------------

BEGIN

 DVSYS.DBMS_MACADM.DELETE_COMMAND_RULE(

  command      => 'SELECT',

  object_owner => 'HR',

  object_name  => 'EMP_DETAILS_VIEW');

END;

/

--------------------------------

SQL> CONN hayat/PAkistan123##@BILL_PDB1

Connected.

SQL> select first_name,last_name from hr.emp_Details_view where employee_id=100;

FIRST_NAME           LAST_NAME

-------------------- -------------------------

Steven               King

--------------------------------

SQL> conn scott/PAkistan123##@BILL_PDB1

Connected.

SQL> select first_name,last_name from hr.emp_Details_view where employee_id=100;

select first_name,last_name from hr.emp_Details_view where employee_id=100

                                    *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn hr/PAkistan123##@BILL_PDB1

Connected.

SQL> grant select on emp_details_view to scott;

Grant succeeded.

SQL> conn c##dbowner1/PAkistan123##@BILL_PDB1

--if HR_REALM exist for SCOTT user

BEGIN

 DBMS_MACADM.DELETE_AUTH_FROM_REALM(

  realm_name => 'HR_Realm',

  grantee    => 'SCOTT');

END;

/

BEGIN

DVSYS.DBMS_MACADM.CREATE_RULE(

rule_name => 'Rule_Scott',

rule_expr => 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''SCOTT'''

);

END;

/

SQL> conn scott/PAkistan123##@BILL_PDB1

SQL> select first_name,last_name from hr.emp_Details_view where employee_id=100;

FIRST_NAME           LAST_NAME

-------------------- -------------------------

Steven               King

SQL>  SELECT NAME FROM DVSYS.DBA_DV_REALM;

NAME

--------------------------------------------------------------------------------

Database Vault Account Management

HR_Realm 

Oracle Database Vault

Oracle Default Component Protection Realm

Oracle Default Schema Protection Realm

Oracle Enterprise Manager

Oracle System Privilege and Role Management Realm

--Delete Existing REALM Created by SupportPartnet:

EXEC DBMS_MACADM.DELETE_REALM('HR_Realm');

  

  SELECT NAME, AUDIT_OPTIONS, ENABLED FROM DVSYS.DBA_DV_REALM 

  WHERE AUDIT_OPTIONS = '1';

Enable DROP TABLE command rule at Database Level

---------------------------------------------------------------------------

SQL>  conn c##dbowner1/PAkistan123##@BILL_PDB1

Connected.

SQL> begin

dbms_macadm.update_command_rule(command=>'DROP TABLE',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'%',enabled=>dbms_macutl.g_no);

end;

/

PL/SQL procedure successfully completed.

SQL> conn hr/PAkistan123##@BILL_PDB1

Connected.

SQL>  create table test1 (r number);

Table created.

SQL> drop table test1;

Table dropped.

Important Views:

set lines 132 pages 2300

col os_username format a15

col username format a15

col userhost format a15

col ACTION_COMMAND format a45

col audit_option format a10

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

select audit_option,os_username,username,userhost,timestamp,action_command from dvsys.audit_trail$;

Part 5 (RULESET Complete end to end example): 

https://hayatkhan75.blogspot.com/2022/08/oracle-database-vault-in-12201-part-5.html

Credit Goes to:

http://oradbakar.blogspot.com/2011/12/database-vault-step-by-step-example.html

https://docs.oracle.com/cd/E18283_01/server.112/e16544/apis_dbms_macadm.htm

https://blog.actorsfit.com/a?ID=01450-3eca16ce-66a2-47e5-8671-1a072f790062

https://www.oreilly.com/library/view/oracle-11g-anti-hackers/9781849685269/ch07s02.html

Oracle Database Vault in 12.2.0.1 - Part 3

 

Database Vault COMMAND RULE usage examples without GUI.

Note: This code is for test education purposes only.

User Case#1 [Restrict access to DROP TABLE in any schema]

SQL> Connect c##dbowner1/PAkistan123##@BILL_PDB1

SQL> SHOW USER

USER is "C##DBOWNER1"

SQL> begin

dbms_macadm.create_command_rule(command=>'DROP TABLE',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'%',enabled=>dbms_macutl.g_yes);

end;  

/

PL/SQL procedure successfully completed.

Command=>DROP TABLE

Rule Set=>Disable

Object Owner=>%

Object Name"=>%

Status=>Enabled

---Now try and create table and than drop it

Conn HR/Password:::::@BILL_PDB1

SQL> create table test (empno number, ename varchar2(30));

Table created.

SQL> drop table test;

drop table test

*

ERROR at line 1:

ORA-47400: Command Rule violation for DROP TABLE on HR.TEST

User Case#2  [Restrict access to View  EMP_DETAILS_VIEW ]

Conn HR/Password:::::

create or replace view test_Command_rule as select first_name,last_name from employees;

SQL> Connect c##dbowner1/PAkistan123##@BILL_PDB1

SQL> begin

dbms_macadm.create_command_rule(command=>'SELECT',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'EMP_DETAILS_VIEW',enabled=>dbms_macutl.g_yes);

end;  /

PL/SQL procedure successfully completed.

Conn HR/Password:::::

SQL> select first_name,last_name from emp_details_view where employee_id=100;

select first_name,last_name from emp_details_view where employee_id=100

                                 *

ERROR at line 1:

ORA-01031: insufficient privileges

---- DROP the command rule and verify 

SQL> BEGIN

 DVSYS.DBMS_MACADM.DELETE_COMMAND_RULE(

  command      => 'SELECT',

  object_owner => 'HR',

  object_name  => 'EMP_DETAILS_VIEW');

END;

/

PL/SQL procedure successfully completed.

Conn HR/Password:::::

SQL> select first_name,last_name from emp_details_view where employee_id=100;

FIRST_NAME           LAST_NAME

-------------------- -------------------------

Steven               King

User Case#3 [Restrict access to CREATE TABLE command]

SQL> Connect c##dbowner1/PAkistan123##@BILL_PDB1

begin

dbms_macadm.create_command_rule(command=>'CREATE TABLE',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'%',enabled=>dbms_macutl.g_yes);

end;

/

Conn HR/Password:::::

SQL> CREATE TABLE TEST2 (StudentID number);

CREATE TABLE TEST2 (StudentID number)

*

ERROR at line 1:

ORA-47400: Command Rule violation for CREATE TABLE on HR.TEST2

Disable Command Rule and Allow Table Creation

SQL> BEGIN

 DVSYS.DBMS_MACADM.UPDATE_COMMAND_RULE(

  command         => 'CREATE TABLE',

  rule_set_name   => 'Disabled',

  object_owner    => 'HR',

  object_name     => '%',

  enabled         => DBMS_MACUTL.G_NO);

END;

/  

PL/SQL procedure successfully completed.

Conn HR/Password:::::

SQL>  CREATE TABLE TEST2 (StudentID number);

Table created.

Disable Command Rule and Allow Table Drop Activity at Database Level

SQL>  conn c##dbowner1/PAkistan123##@BILL_PDB1

Connected.

SQL> begin

dbms_macadm.update_command_rule(command=>'DROP TABLE',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'%',

enabled=>dbms_macutl.g_no);

end;

/  

PL/SQL procedure successfully completed.

SQL>

SQL> conn hr/PAkistan123##@BILL_PDB1

Connected.

SQL>  create table test1 (r number);

Table created.

SQL> drop table test1;

Table dropped.

SQL>

Enable Create Table:

begin

dbms_macadm.update_command_rule(command=>'CREATE TABLE',rule_set_name=>'Disabled',object_owner=>'HR',object_name=>'%',

enabled=>dbms_macutl.g_no);

end;  

/

---Important Views:

SELECT command,rule_set_name ,enabled,object_owner,object_name from DVSYS.DBA_DV_COMMAND_RULE

WHERE COMMAND IN ('CREATE TABLE','DROP TABLE');

SELECT RULE_SET_NAME, HANDLER_OPTIONS, HANDLER FROM DVSYS.DBA_DV_RULE_SET

WHERE RULE_SET_NAME = 'Disabled';

 

SELECT RULE_SET_NAME, RULE_NAME, RULE_EXPR FROM DVSYS.DBA_DV_RULE_SET_RULE

WHERE RULE_NAME = 'Disabled';

RULESET can be studied in part 4: https://hayatkhan75.blogspot.com/2022/08/oracle-database-vault-in-12201-part-4.html

Credit Goes:

http://oradbakar.blogspot.com/2011/12/database-vault-step-by-step-example.html

https://docs.oracle.com/cd/E18283_01/server.112/e16544/apis_dbms_macadm.htm

https://blog.actorsfit.com/a?ID=01450-3eca16ce-66a2-47e5-8671-1a072f790062

https://www.oreilly.com/library/view/oracle-11g-anti-hackers/9781849685269/ch07s02.html

Oracle Database Vault in 12.2.0.1 - Part 2

Database Vault REALM is created in this blog using the command instead of GUI.

Note: This code is for test education purposes only.

User Case: 

A realm HR_Realm created. Access to all HR tables and object types is granted.

SQL> CONN c##dbowner1/PAkistan123##@BILL_PDB1

Connected.

SQL> EXEC  dvsys.dbms_macadm.create_realm ( realm_name =>'HR_Realm',description =>'Restrict DBA access to HR mode',enabled =>'Y', audit_options => 1 );

PL/SQL procedure successfully completed.

---------------------------------------------

Add the HR schema all object to the new realm

---------------------------------------------

SQL> EXEC dvsys.dbms_macadm.add_object_to_realm ( realm_name =>'HR_Realm',object_owner =>'HR',object_name =>'%',object_type =>'%');

PL/SQL procedure successfully completed.

SQL> conn c##dbmanager1/PAkistan123##@BILL_PDB1

Connected.

SQL>  create user scott identified by PAkistan123##;

User created.

SQL> grant connect, create session to scott;

Grant succeeded.

GRANT SELECT ANY TABLE TO SCOTT;

---------------------------------------------

Authorize a user, such as scott, as a Participant | Owner to access the realm HR_REALM

---------------------------------------------

SQL> CONN c##dbowner1/PAkistan123##@BILL_PDB1

Connected.

SQL> BEGIN

DVSYS.DBMS_MACADM.UPDATE_REALM_AUTH(

realm_name => 'HR_Realm',

grantee => 'SCOTT',

rule_set_name =>NULL,

auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);

END;

/

OR

SQL> EXEC dbms_macadm.add_auth_to_realm(realm_name => 'HR_Realm', grantee => 'SCOTT');

PL/SQL procedure successfully completed.

-- Add objects authorization to REALM

EXEC DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(realm_name => 'HR_Realm',object_owner => 'HR',object_name => '%',object_type => '%');

-------------------------------------------------

Verify REALM usage using SOCTT

------------------------------------------------

SQL> conn scott/PAkistan123##@BILL_PDB1

Connected.

SQL> select count(*) from hr.employees;

  COUNT(*)

----------

       107

---------------------------------------------

Revoke authorization 

----------------------------------------------

EXEC dbms_macadm.delete_auth_from_realm(realm_name => 'HR_Realm', grantee => 'SCOTT');

Now try as Scott user:

select count(*) from hr.employees

                        *

ERROR at line 1:

ORA-01031: insufficient privileges

Now Reassign:

SQL>  EXEC dbms_macadm.add_auth_to_realm(realm_name => 'HR_Realm', grantee => 'SCOTT');

PL/SQL procedure successfully completed.

SQL>

Now try again as Scott user:

SQL> /

select count(*) from hr.employees

  COUNT(*)

----------

       107

================================================================

Now execute:

SQL> EXEC DVSYS.DBMS_MACADM.DELETE_OBJECT_FROM_REALM(realm_name=>'HR_Realm',object_owner=>'HR',object_name=>'%',object_type=>'%');

PL/SQL procedure successfully completed.

SQL>

---The reason is that, SCOTT have select any privileges

----Very important: SCOTT user have SELECT ANY TABLE privilege, but important thing it is authorized using HR_REALM

SQL> conn scott/PAkistan123##@BILL_PDB1

Connected.

SQL> select count(*) from hr.employees;

  COUNT(*)

----------

       107

EXEC DVSYS.DBMS_MACADM.ADD_OBJECT_TO_REALM(realm_name => 'HR_Realm',object_owner => 'HR',object_name => 'EMPLOYEES',object_type => '%');

Check Part 3 Command Rule Creation: https://hayatkhan75.blogspot.com/2022/08/oracle-database-vault-in-12201-part-3.html

Credit goes:

https://blog.actorsfit.com/a?ID=01450-3eca16ce-66a2-47e5-8671-1a072f790062

https://mithun168.wordpress.com/2019/02/25/oracle-db-vault-realm-creation/

https://www.oreilly.com/library/view/oracle-11g-anti-hackers/9781849685269/ch07s02.html

https://www.thegeekdiary.com/how-realms-work-in-oracle-database-vault/

https://www.dbi-services.com/blog/oracle-database-vault-realm-in-a-pluggable-database/

How to Change SCAN and VIP IP Oracle RAC 19.3.

 

How to change SCAN, VIP IP address in 19c RAC (Two nodes)

Important Note: Command only executed in a test environment. In Production, execute at your own risk. The authors (Hayat Mohammad Khan) claim no responsibility for the below commands.

--As a grid user

--Grid home: /u01/app/19c/grid

[oracle@dbwr2 ~]$ . grid.env

[oracle@dbwr2 ~]$ oifcfg getif

enp0s3  192.168.50.0  global  public

enp0s8  192.168.10.0  global  cluster_interconnect,asm

--for dns below scan ips configured

[root@dbwr1 network-scripts]# cat /etc/racdns

changed from:

192.168.50.93 dbwr-scan.localdomain dbwr-scan

192.168.50.94 dbwr-scan.localdomain dbwr-scan

192.168.50.95 dbwr-scan.localdomain dbwr-scan

changed to 

192.168.50.96 dbwr-scan.localdomain dbwr-scan

192.168.50.97 dbwr-scan.localdomain dbwr-scan

192.168.50.98 dbwr-scan.localdomain dbwr-scan

--Updated /etc/hosts file

comment old IPs

#192.168.50.93 dbwr-scan.localdomain dbwr-scan

#192.168.50.94 dbwr-scan.localdomain dbwr-scan

#192.168.50.95 dbwr-scan.localdomain dbwr-scan

Enter new IPs

192.168.50.96 dbwr-scan.localdomain dbwr-scan

192.168.50.97 dbwr-scan.localdomain dbwr-scan

192.168.50.98 dbwr-scan.localdomain dbwr-scan

As a Grid User perform below

=========================

[root@dbwr1 bin]# ./srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node dbwr1

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is running on node dbwr1

SCAN Listener LISTENER_SCAN3 is enabled

----- SCAN-NAME is currently resolving old scan IPs

SCAN listener LISTENER_SCAN3 is running on node dbwr1

[root@dbwr1 bin]# ./srvctl config scan

SCAN name: dbwr-scan, Network: 1

Subnet IPv4: 192.168.50.0/255.255.255.0/enp0s3, static

Subnet IPv6: 

SCAN 1 IPv4 VIP: 192.168.50.93

SCAN VIP is enabled.

SCAN 2 IPv4 VIP: 192.168.50.94

SCAN VIP is enabled.

SCAN 3 IPv4 VIP: 192.168.50.95

SCAN VIP is enabled.

----public and private network usage IPs scheme

[oracle@dbwr1 ~]$  oifcfg getif

enp0s3  192.168.50.0  global  public

enp0s8  192.168.10.0  global  cluster_interconnect,asm

[oracle@dbwr1 ~]$

----- stop scan services

[root@dbwr1 bin]# ./srvctl stop scan_listener

[root@dbwr1 bin]# ./srvctl stop scan

[root@dbwr1 bin]# ./srvctl status scan

SCAN VIP scan1 is enabled

SCAN VIP scan1 is not running

SCAN VIP scan2 is enabled

SCAN VIP scan2 is not running

SCAN VIP scan3 is enabled

SCAN VIP scan3 is not running

[root@dbwr1 bin]# 

[root@dbwr1 bin]# ./srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is not running

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is not running

SCAN Listener LISTENER_SCAN3 is enabled

SCAN listener LISTENER_SCAN3 is not running

[root@dbwr1 bin]#

---------------stop and start dns server after IP change

[root@dbwr1 ~]#  service dnsmasq stop

Redirecting to /bin/systemctl stop dnsmasq.service

[root@dbwr1 ~]#  service dnsmasq start

Redirecting to /bin/systemctl start dnsmasq.service

[root@dbwr1 ~]# cat /etc/hosts

--------------Verify that new IPs are taken

[root@dbwr1 ~]# nslookup dbwr-scan

Server: 127.0.0.1

Address: 127.0.0.1#53

Name: dbwr-scan.localdomain

Address: 192.168.50.98

Name: dbwr-scan.localdomain

Address: 192.168.50.96

Name: dbwr-scan.localdomain

Address: 192.168.50.97

Name: dbwr-scan.localdomain

Address: 192.168.50.98

Name: dbwr-scan.localdomain

Address: 192.168.50.97

Name: dbwr-scan.localdomain

Address: 192.168.50.96

** server can't find dbwr-scan.localdomain: REFUSED

------------ Modify scan IPs in RAC cluster

cd /u01/app/19c/grid/bin

[root@dbwr1 bin]# ./srvctl modify scan -n dbwr-scan

[root@dbwr1 bin]# ./srvctl config scan

SCAN name: dbwr-scan, Network: 1

Subnet IPv4: 192.168.50.0/255.255.255.0/enp0s3, static

Subnet IPv6: 

SCAN 1 IPv4 VIP: 192.168.50.96

SCAN VIP is enabled.

SCAN 2 IPv4 VIP: 192.168.50.97

SCAN VIP is enabled.

SCAN 3 IPv4 VIP: 192.168.50.98

SCAN VIP is enabled.

[root@dbwr1 bin]# 

[root@dbwr1 bin]# ./srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node dbwr1

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is running on node dbwr1

SCAN Listener LISTENER_SCAN3 is enabled

SCAN listener LISTENER_SCAN3 is running on node dbwr1

============ Changing VIP Address =========================== 

--192.168.50.83 is old VIP of node1 (dbwr1 is host name)

[root@dbwr1 bin]# ./srvctl config nodeapps -a

Network 1 exists

Subnet IPv4: 192.168.50.0/255.255.255.0/enp0s3, static

Subnet IPv6: 

Ping Targets: 

Network is enabled

Network is individually enabled on nodes: 

Network is individually disabled on nodes: 

VIP exists: network number 1, hosting node dbwr1

VIP Name: dbwr1-vip.localdomain

VIP IPv4 Address: 192.168.50.83

VIP IPv6 Address: 

VIP is enabled.

VIP is individually enabled on nodes: 

VIP is individually disabled on nodes: 

VIP exists: network number 1, hosting node dbwr2

VIP Name: dbwr2-vip

VIP IPv4 Address: 192.168.50.84

VIP IPv6 Address: 

VIP is enabled.

VIP is individually enabled on nodes: 

VIP is individually disabled on nodes: 

--- stop db and nodeapps service 

[root@dbwr1 bin]# ./srvctl stop database -d oradbwr

[root@dbwr1 bin]# ./srvctl stop nodeapps -f

[root@dbwr1 bin]# 

[oracle@dbwr1 ~]$ oifcfg getif

enp0s3  192.168.50.0  global  public

enp0s8  192.168.10.0  global  cluster_interconnect,asm

[oracle@dbwr1 ~]$ 

[root@dbwr1 bin]# ./crsctl stop cluster -all

CRS-2673: Attempting to stop 'ora.crsd' on 'dbwr1'

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'dbwr1'

CRS-2673: Attempting to stop 'ora.chad' on 'dbwr1'

CRS-33673: Attempting to stop resource group 'ora.asmgroup' on server 'dbwr1'

CRS-2673: Attempting to stop 'ora.GI.dg' on 'dbwr1'

CRS-2673: Attempting to stop 'ora.DATA.dg' on 'dbwr1'

CRS-2673: Attempting to stop 'ora.RECO.dg' on 'dbwr1'

CRS-2677: Stop of 'ora.DATA.dg' on 'dbwr1' succeeded

CRS-2677: Stop of 'ora.RECO.dg' on 'dbwr1' succeeded

CRS-2677: Stop of 'ora.GI.dg' on 'dbwr1' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'dbwr1'

CRS-2677: Stop of 'ora.asm' on 'dbwr1' succeeded

CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'dbwr1'

CRS-2677: Stop of 'ora.chad' on 'dbwr1' succeeded

CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'dbwr1' succeeded

CRS-2673: Attempting to stop 'ora.asmnet1.asmnetwork' on 'dbwr1'

CRS-2677: Stop of 'ora.asmnet1.asmnetwork' on 'dbwr1' succeeded

CRS-33677: Stop of resource group 'ora.asmgroup' on server 'dbwr1' succeeded.

CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'dbwr1' has completed

CRS-2677: Stop of 'ora.crsd' on 'dbwr1' succeeded

CRS-2673: Attempting to stop 'ora.ctssd' on 'dbwr1'

CRS-2673: Attempting to stop 'ora.evmd' on 'dbwr1'

CRS-2673: Attempting to stop 'ora.storage' on 'dbwr1'

CRS-2677: Stop of 'ora.storage' on 'dbwr1' succeeded

CRS-2673: Attempting to stop 'ora.asm' on 'dbwr1'

CRS-2677: Stop of 'ora.ctssd' on 'dbwr1' succeeded

CRS-2677: Stop of 'ora.evmd' on 'dbwr1' succeeded

CRS-2677: Stop of 'ora.asm' on 'dbwr1' succeeded

CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'dbwr1'

CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'dbwr1' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on 'dbwr1'

CRS-2677: Stop of 'ora.cssd' on 'dbwr1' succeeded

[root@dbwr1 bin]# 

---change VIP address in /etc/hosts or DNS

# Virtual

Change from:

192.168.50.83 dbwr1-vip.localdomain dbwr1-vip

192.168.50.84 dbwr2-vip.localdomain dbwr2-vip

Change VIP From 83 TO 85

192.168.50.85 dbwr1-vip.localdomain dbwr1-vip

192.168.50.84 dbwr2-vip.localdomain dbwr2-vip

----

root@dbwr1 network-scripts]# pwd

/etc/sysconfig/network-scripts

enp0s3:4: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500

        inet 192.168.50.85  netmask 255.255.255.0  broadcast 192.168.50.255

        ether 08:00:27:c4:c9:54  txqueuelen 1000  (Ethernet)

[oracle@dbwr1 ~]$ /sbin/ifconfig enp0s3

enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500

        inet 192.168.50.73  netmask 255.255.255.0  broadcast 192.168.50.255

        inet6 fe80::2fe:60bf:4e6f:a9fc  prefixlen 64  scopeid 0x20<link>

        inet6 fe80::58db:9472:e1f7:37b2  prefixlen 64  scopeid 0x20<link>

        ether 08:00:27:c4:c9:54  txqueuelen 1000  (Ethernet)

        RX packets 2887  bytes 343925 (335.8 KiB)

        RX errors 0  dropped 0  overruns 0  frame 0

        TX packets 2752  bytes 254973 (248.9 KiB)

        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[oracle@dbwr1 ~]$ /sbin/ifconfig enp0s3:4

enp0s3:4: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500

        inet 192.168.50.85  netmask 255.255.255.0  broadcast 192.168.50.255

        ether 08:00:27:c4:c9:54  txqueuelen 1000  (Ethernet)

------------------

-- VIP IP change to 192.168.50.85 from 192.168.50.83 (hostname dbwr1)

[root@dbwr2 bin]# ./srvctl config nodeapps -a

Network 1 exists

Subnet IPv4: 192.168.50.0/255.255.255.0/enp0s3, static

Subnet IPv6: 

Ping Targets: 

Network is enabled

Network is individually enabled on nodes: 

Network is individually disabled on nodes: 

VIP exists: network number 1, hosting node dbwr1

VIP Name: dbwr1-vip.localdomain

VIP IPv4 Address: 192.168.50.85

VIP IPv6 Address: 

VIP is enabled.

VIP is individually enabled on nodes: 

VIP is individually disabled on nodes: 

VIP exists: network number 1, hosting node dbwr2

VIP Name: dbwr2-vip

VIP IPv4 Address: 192.168.50.84

VIP IPv6 Address: 

VIP is enabled.

VIP is individually enabled on nodes: 

VIP is individually disabled on nodes: 

[root@dbwr2 bin]# 

--srvctl modify nodeapps -n dbwr1 -A 192.168.50.85/255.255.255.0/enp0s3:4

--srvctl modify nodeapps -n racnode1 -A 10.1.1.40/255.255.255.0/eth1

[root@dbwr2 bin]# ./srvctl start nodeapps

PRKO-2421 : Network resource is already started on node(s): dbwr1,dbwr2

PRKO-2420 : VIP dbwr1-vip.localdomain is already started on nodes: dbwr1

PRKO-2420 : VIP dbwr2-vip is already started on nodes: dbwr2

PRKO-2422 : ONS is already started on node(s): dbwr1,dbwr2

[root@dbwr1 bin]# ./srvctl status database -d oradbwr

Instance oradbwr1 is not running on node dbwr1

Instance oradbwr2 is not running on node dbwr2

SQL> select instance_name, status from gv$instance;

INSTANCE_NAME STATUS

---------------- ------------

oradbwr2 OPEN

oradbwr1 OPEN

Some other useful Commands:

srvctl modify scan_listener -update

$GRID_HOME/bin/srvctl modify scan_listener -u

/sbin/ifconfig -a | egrep '(eth|Mask)'

$GRID_HOME/bin/crsctl modify type ora.scan_vip.type -attr "ATTRIBUTE=SCAN_NAME,DEFAULT_VALUE=<scan>.oracle.com"

Credit Goes:

https://www.funoracleapps.com/2018/06/how-to-change-various-ips-in-rac-cluster.html

https://cdmana.com/2021/08/20210809182210880t.html

http://what-when-how.com/Tutorial/topic-816rrqij6/Oracle-Exadata-Recipes-A-Problem-Solution-Approach-306.html

Oracle MOC:

How to Modify Public Network Information including VIP in Oracle Clusterware (Doc ID 276434.1)

How to Modify SCAN Setting or SCAN Listener Port after Installation (Doc ID 972500.1)