Unified Audit

Check Unified Audit records and enable policies in Oracle

check UA is enabled

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
------
FALSE

bring down db and listener

Go to the $ORACLE_HOME/rdbms/lib directory.

Enable the unified auditing executable.

    UNIX: Run the following command:

    make -f ins_rdbms.mk uniaud_on ioracle ORACLE_HOME=$ORACLE_HOME

bring up now listener and DB.

1. Check unified audit is enabled.

SQL> SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
------
TRUE

create AUDIT user

SQL> create user SEC_ADMIN identified by oracle123;

User created.

SQL> grant AUDIT_ADMIN to SEC_ADMIN;

Grant succeeded.

SQL> grant execute on SYS.DBMS_AUDIT_MGMT to SEC_ADMIN;

Grant succeeded.

SQL>  grant CREATE PROCEDURE,CREATE ROLE,CREATE SESSION,INHERIT ANY PRIVILEGES,SELECT ANY DICTIONARY to SEC_ADMIN;


2. Check the default / enabled polices of unified audit.

SQL> select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES;

POLICY_NAME
--------------------
ORA_LOGON_FAILURES
ORA_SECURECONFIG

connect using SEC_ADMIN user

3. Check all policy details

SQL> SELECT policy_name, enabled_option, entity_name, success, failure
FROM audit_unified_enabled_policies  2  ;

POLICY_NAME          ENABLED_OPTION  ENTITY_NAME          SUC FAI
-------------------- --------------- -------------------- --- ---
ORA_SECURECONFIG     BY USER         ALL USERS            YES YES
ORA_LOGON_FAILURES   BY USER         ALL USERS            NO  YES


Note: SUCCESS or FAILURE columns value tell WHENEVER SUCCESSFUL or WHENEVER NOT SUCCESSFUL clause is used during setting AUDITING.
like “AUDIT POLICY TESTPOLICY1 BY HR WHENEVER NOT SUCCESSFUL;”

create AUDIT Policy for user TEST1

SQL> create audit policy test1_pol
  actions all
  when q'~ sys_context('userenv', 'session_user') = 'TEST1' ~'
  evaluate per session;

Audit policy created.

-- enable audit policy

SQL> audit policy test1_pol;

Audit succeeded.

-- connect with other TEST1 user and perform some action

SQL> connect test1/test1;

SQL> INSERT INTO simulate_deadlock VALUES (1,'Manish','Sureka');

1 row created.

SQL> commit;

-- connect with SEC_ADMIN user to view AUDIT data

SQL> conn sec_admin/oracle123

-- Check the report for the enabled polices.
--Check today audit records

set lines 200
col SQL_TEXT for a30
col action_name for a20
col UNIFIED_AUDIT_POLICIES for a30
select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail
where EVENT_TIMESTAMP > sysdate -1;

empty AUDIT TRAIL

SQL> exec dbms_audit_mgmt.clean_audit_trail(dbms_audit_mgmt.audit_trail_unified,false);

PL/SQL procedure successfully completed.

SQL>

Oracle Analytics Server - BAR file migration

-bash-4.1$ cd /testap/obiee/fusion/mwr_OAS/user_projects/domains/bi/bitools/bin

./exportarchive.sh Service_Instance_Name Location_of_BAR_File  <Optional Parameters> 

 
-bash-4.1$ ./exportarchive.sh ssi /shared_fsy/fusion/obiee/Barfile_backup encryptionpassword=test123
Export archive is running...
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
************  BIP inside beginExport ************
List credential (index: connect_string username)
[EL Info]: 2020-09-24 18:43:49.648--ServerSession(1830747664)--EclipseLink, version: Eclipse Persistence Services - 2.6.7.v20190604-418f1a1c56
[EL Info]: 2020-09-24 18:43:49.684--ServerSession(874240738)--EclipseLink, version: Eclipse Persistence Services - 2.6.7.v20190604-418f1a1c56
[EL Info]: connection: 2020-09-24 18:43:49.782--ServerSession(1830747664)--/file:/testap/obiee/fusion/mwr_OAS/bi/modules/oracle.bi.infer/bi-infer-data.jar_INFER_PLUGIN_PU_url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=devtestscan.fsn.com)(PORT=1523)))(CONNECT_DATA=(SERVICE_NAME=fusiondb)))_user=FSN1_BIPLATFORM login successful
[EL Info]: connection: 2020-09-24 18:43:49.86--ServerSession(874240738)--/file:/testap/obiee/fusion/mwr_OAS/bi/modules/oracle.bi.majel/bi-majel-data.jar_tenantPU_url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=devtestscan.fsn.com)(PORT=1523)))(CONNECT_DATA=(SERVICE_NAME=fusiondb)))_user=FSN1_BIPLATFORM login successful
[EL Info]: connection: 2020-09-24 18:43:51.088--ServerSession(1830747664)--/file:/testap/obiee/fusion/mwr_OAS/bi/modules/oracle.bi.infer/bi-infer-data.jar_INFER_PLUGIN_PU_url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=devtestscan.fsn.com)(PORT=1523)))(CONNECT_DATA=(SERVICE_NAME=fusiondb)))_user=FSN1_BIPLATFORM logout successful
[EL Info]: connection: 2020-09-24 18:43:51.108--ServerSession(874240738)--/file:/testap/obiee/fusion/mwr_OAS/bi/modules/oracle.bi.majel/bi-majel-data.jar_tenantPU_url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=devtestscan.fsn.com)(PORT=1523)))(CONNECT_DATA=(SERVICE_NAME=fusiondb)))_user=FSN1_BIPLATFORM logout successful
Export Archive  completed Successfully
Archive location : /shared_fsy/fusion/obiee/Barfile_backup/1600973247977/ssi.bar
Log dir :/testap/obiee/fusion/mwr_OAS/user_projects/domains/bi/bilogs/metadata/exportbar_20200924_1838194
You have new mail in /var/spool/mail/obifusion
 

Transfer the BAR file to target OAS server.

[obifusion@test-x2 bin]$ pwd
/test2ap_oas/obiee/fusion/mwr_OAS/user_projects/domains/bi/bitools/bin

./importarchive.sh Service_Instance_Name Location_of_BAR_File_including_name  <Optional Parameters> 

 
[obifusion@test-x2 bin]$ ./importarchive.sh ssi /test2ap_oas/obiee/fusion/1600973247977/ssi.bar encryptionpassword=test123
Import archive is running...
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
[EL Info]: 2020-09-24 21:36:14.564--ServerSession(1708098355)--EclipseLink, version: Eclipse Persistence Services - 2.6.7.v20190604-418f1a1c56
[EL Info]: connection: 2020-09-24 21:36:14.941--ServerSession(1708098355)--/file:/test2ap_oas/obiee/fusion/mwr_OAS/bi/modules/oracle.bi.infer/bi-infer-data.jar_INFER_PLUGIN_PU_url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.25.132.187)(PORT=1524)))(CONNECT_DATA=(SERVICE_NAME=fusiondb3)))_user=FSN2_BIPLATFORM login successful
[EL Info]: 2020-09-24 21:36:15.064--ServerSession(1257740559)--EclipseLink, version: Eclipse Persistence Services - 2.6.7.v20190604-418f1a1c56
[EL Info]: connection: 2020-09-24 21:36:15.464--ServerSession(1257740559)--/file:/test2ap_oas/obiee/fusion/mwr_OAS/bi/modules/oracle.bi.majel/bi-majel-data.jar_tenantPU_url=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.25.132.187)(PORT=1524)))(CONNECT_DATA=(SERVICE_NAME=fusiondb3)))_user=FSN2_BIPLATFORM login successful
Writing to ssi/actioBarImport/8c466613-2b90-4568-9853-f56fb407b414
Writing to ssi/actioBarImport/8c466613-2b90-4568-9853-f56fb407b414.checksum
Import Archive completed Successfully
Log dir :/test2ap_oas/obiee/fusion/mwr_OAS/user_projects/domains/bi/bilogs/metadata/importbar_20200924_2130477
[obifusion@test-x2 bin]$

UPGRADE OPATCH to 13.9.4.2.2

UPGRADE OPATCH to 13.9.4.2.2

Installation of bug correction patches for Oracle WLS / FMW 12.2.1.4 is done with the new OPatch 13.9.4.2.2

1. Download p28186730_139422_Generic.zip

[abtid1@indelbtidfap01 Middleware]$ unzip p28186730_139422_Generic.zip
Archive:  p28186730_139422_Generic.zip
   creating: 6880880/
  inflating: 6880880/README.txt
  inflating: 6880880/opatch_generic.jar
  inflating: 6880880/version.txt
[abtid1@indelbtidfap01 Middleware]$ cd 6880880
[abtid1@indelbtidfap01 6880880]$ ls
README.txt  opatch_generic.jar  version.txt


2. Install the software with the following command:

    UNIX Only:
        java -jar <PATCH_LOCATION>/6880880/opatch_generic.jar -silent oracle_home=$ORACLE_HOME

ORACLE_HOME is set to installed Middleware home.

[abtid1@indelbtidfap01 6880880]$ java -jar /btid1ap/bi/abtid1/Middleware/6880880/opatch_generic.jar -silent oracle_home=$ORACLE_HOME
Launcher log file is /tmp/OraInstall2020-06-01_02-20-23AM/launcher2020-06-01_02-20-23AM.log.
Extracting the installer . . . . Done
Checking if CPU speed is above 300 MHz.   Actual 2095.148 MHz    Passed
Checking swap space: must be greater than 512 MB.   Actual 16383 MB    Passed
Checking if this platform requires a 64-bit JVM.   Actual 64    Passed (64-bit not required)
Checking temp space: must be greater than 300 MB.   Actual 9321 MB    Passed
Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2020-06-01_02-20-23AM
Installation Summary


Disk Space : Required 35 MB, Available 92,882 MB
Feature Sets to Install:
        Next Generation Install Core 13.9.4.0.0
        OPatch 13.9.4.2.2
        OPatch Auto OPlan 13.9.4.2.2
        OPatch Auto FMW 13.9.4.2.2
Session log file is /tmp/OraInstall2020-06-01_02-20-23AM/install2020-06-01_02-20-23AM.log

Loading products list. Please wait.
 1%
 40%

.....................

[abtid1@indelbtidfap01 6880880]$ opatch version
OPatch Version: 13.9.4.2.2

[abtid1@indelbtidfap01 6880880]$ opatch lspatches
26355633;One-off
26287183;One-off
26261906;One-off
26051289;One-off

OPatch succeeded.

opatchauto - Prerequisite check "CheckActiveFilesAndExecutables" failed.

Opatchauto failed with error.

 Prerequisite check "CheckActiveFilesAndExecutables" failed.

Following active executables are used by opatch process :
 /btkrthdb/db/12.1.0.2/lib/libclntsh.so.12.1

Symptom.

[May 22, 2020 8:09:39 AM] [INFO]    Finish fuser command /sbin/fuser /btkrthdb/db/12.1.0.2/lib/libclntsh.so.12.1 at Fri May 22 08:09:39 UTC 2020
[May 22, 2020 8:09:39 AM] [INFO]    Files in use by a process: /btkrthdb/db/12.1.0.2/lib/libclntsh.so.12.1 PID( 80610 )
[May 22, 2020 8:09:39 AM] [INFO]    Following active executables are not used by opatch process :
                                   
                                   
                                    Following active executables are used by opatch process :
                                    /btkrthdb/db/12.1.0.2/lib/libclntsh.so.12.1
[May 22, 2020 8:09:39 AM] [INFO]    Prerequisite check "CheckActiveFilesAndExecutables" failed.
                                    The details are:
                                   
                                   
                                    Following active executables are not used by opatch process :
                                   
                                   
                                    Following active executables are used by opatch process :
                                    /btkrthdb/db/12.1.0.2/lib/libclntsh.so.12.1
[May 22, 2020 8:09:39 AM] [SEVERE]  OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
[May 22, 2020 8:09:39 AM] [INFO]    Finishing UtilSession at Fri May 22 08:09:39 UTC 2020
[May 22, 2020 8:09:39 AM] [INFO]    Log file location: /btkrthdb/db/12.1.0.2/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-22_08-09-07AM_1.log
(END)

-------------------------------------
As per above mentioned log file, there are still some processes using one of the files that is being patched. Here executable libclntsh.so.12.1 is still in use.
Find out which os user and pid is holding.

[obtkrth@indelbtd11 ~]$ fuser /btkrthdb/db/12.1.0.2/lib/libclntsh.so.12.1
/btkrthdb/db/12.1.0.2/lib/libclntsh.so.12.1: 80610m

[obtkrth@indelbtd11 ~]$ lsof /btkrthdb/db/12.1.0.2/lib/libclntsh.so.12.1
COMMAND   PID     USER  FD   TYPE  DEVICE SIZE/OFF    NODE NAME
tnslsnr 80610 obtkrth mem    REG 253,230 58855367 3822203 /btkrthdb/db/12.1.0.2/lib/libclntsh.so.12.1
-------------------------------------


Now Kill this process to proceed with patching.

[obtkrth@indelbtd11 ~]$ kill -9 80610
[obtkrth@indelbtd11 ~]$ lsof /btkrthdb/db/12.1.0.2/lib/libclntsh.so.12.1
[obtkrth@indelbtd11 ~]$

Now no more process, continue with patching.

Tableau Upgrade from 2019.2.5 to 2019.3.6

1.       Login to TSM service and bring down it from dos prompt.

C:\Users\admtableau> call tsm login -s https://indlbtap11.btorg.com-u admtableau -p Password123

C:\Users\admtableau> tsm stop

2.       Create a Backup of Tableau server (DATA + Installation directory)

C:\Users\admtableau> call tsm settings export -f D:\Backup\Tableau_Settings.json

C:\Users\admtableau> call tsm maintenance backup -file Tableau_Content_bkp -d --username admtableau --password Password123

3.       Current version is 2019.2.5

4.       Execute installer of 2019.3.6 as Administrator

 

It will locate and check the existing installation directory and extracts the system verification.

 

It will install the new binaries in the same existing installation directory.

 

This installation will be done at the same location with the earlier version no need to specify installation directory.

 

Select Automatically launch the upgrade script and click OK.

Entire log of the above command prompt-

Upgrading Tableau Server to version 20193.20.0323.1703. See log file at D:\Table

au_Server\logs\app-upgrade.log for progress.

Install directory D:\Tableau_Server

Config file: "D:\Tableau_Server\data\tabsvc\config\workgroup.yml"

Validating Tableau Server version.

Verifying that the new packages have been installed on all cluster nodes.

Stopping service...

Verifying Tableau Server is stopped.

Verifying that there is only one deployed Tableau Server Coordination Service en

semble.

Verifying that Tableau Server has been initialized.

Verifying licensing state.

Product usage data is enabled - by default the option to help us improve Tableau

 Server by sending us Tableau Server usage data has been enabled. You can disabl

e this on the TSM Maintenance page, or using the TSM command line. For more info

rmation about product usage data, see the help: https://onlinehelp.tableau.com/c

urrent/server/en-us/usage_data_server.htm

Reconnecting to asynchronous job...

Job id is '10', timeout is 60 minutes.

6% - Retrieving the topology to deploy.

13% - Retrieving the configuration to deploy.

20% - Validating the new topology.

26% - Determining if server needs to be started.

33% - Disabling all services.

40% - Waiting for the services to stop.

46% - Updating nodes to new topology.

53% - Waiting for topology to be applied.

60% - Updating nodes to new configuration.

66% - Disabling all services.

73% - Waiting for the services to stop.

80% - Reconfiguring services.

86% - Waiting for services to reconfigure.

93% - Enabling all services.

100% - Waiting for the services to start.

Backing up repository.

Upgrading TSM services.

Updating configuration.

Reconnecting to asynchronous job...

Job id is '11', timeout is 10 minutes.

Updating Tableau Server Coordination Service.

Updating Tableau Server Client File Service.

Generating new credentials.

Reconnecting to asynchronous job...

Job id is '12', timeout is 10 minutes.

16% - Generating password123s.

33% - Generating search server ssl certificate.

50% - Generating Elastic Server SSL certificate.

66% - Generating key store.

83% - Promoting configuration.

100% - Waiting for services to reconfigure.

Updating remaining services.

Updating Tableau Server Service Manager.

Restoring repository.

Updating repository version in Tableau Server Coordination Service.

Updating repository.

Reconnecting to asynchronous job...

Job id is '13', timeout is 120 minutes.

10% - Enabling the database services.

20% - Waiting for the database services to enable.

30% - Putting the repository into local trust mode.

40% - Creating roles and databases.

50% - Running migrations against the primary database.

60% - Setting the next active repository.

70% - Taking the repository out of local trust mode.

80% - Disabling database services.

90% - Waiting for database services to disable.

100% - Upgrading database

Updating asset keys.

Reconnecting to asynchronous job...

Job id is '14', timeout is 120 minutes.

100% - Checking if asset key upgrade is needed.

Rebuilding search index.

Reconnecting to asynchronous job...

Job id is '15', timeout is 120 minutes.

14% - Disabling all services.

28% - Waiting for the services to stop.

42% - Starting backup restore services for search server reset.

57% - Resetting search server.

71% - Starting search server.

85% - Configuring search server.

100% - Stopping backup restore services.

Reconnecting to asynchronous job...

Job id is '16', timeout is 120 minutes.

25% - Enabling the services required for indexing.

50% - Connecting to Vizportal Maintenance.

75% - Rebuilding the search index.

100% - Disabling the services used for indexing.

Updating data directory version.

Reconnecting to asynchronous job...

Job id is '17', timeout is 40 minutes.

6% - Retrieving the topology to deploy.

13% - Retrieving the configuration to deploy.

20% - Validating the new topology.

26% - Determining if server needs to be started.

33% - Disabling all services.

40% - Waiting for the services to stop.

46% - Updating nodes to new topology.

53% - Waiting for topology to be applied.

60% - Updating nodes to new configuration.

66% - Disabling all services.

73% - Waiting for the services to stop.

80% - Reconfiguring services.

86% - Waiting for services to reconfigure.

93% - Enabling all services.

100% - Waiting for the services to start.

Running post-upgrade steps on all cluster nodes.

Reconnecting to asynchronous job...

Job id is '18', timeout is 10 minutes.

33% - Installing backup services.

66% - Running post-upgrade.

100% - Uninstalling backup services.

Verifying licensing state.

Tableau Server has been upgraded to version 20193.20.0323.1703.

C:\Windows\system32>

Start Tableau Server. You can do this from the TSM web UI or from a command prompt:

 


 
 

From a command prompt:

    Open a command prompt as administrator. Enter this command:

C:\Users\admtableau> call tsm login -s https://indlbtap11.btorg.com -u admtableau  -p Password123

C:\Users\admtableau>tsm start

Starting service...

Job id is '19', timeout is 30 minutes.

Service was started successfully.

C:\Users\admtableau> 

 

Login to TSM web UI and re register.