ilmarkerm

/*
Create a table that will store the group object ID-s that your application is interested in (group_id)
and some kind of string that your application can understand (granted_role).

create table access_groups (
    group_id varchar2(100) not null,
    granted_role varchar2(100) not null,
    primary key (group_id, granted_role)
) organization index;
*/

ilmarkerm

#!/bin/bash

# Convert system trusted CA bundle to Oracle Wallet format (PKCS#12)
# Ilmar Kerm 2023

destdir="/nfs/install_master/base_installers/tools/mozilla_wallet"
tmpwallet="/tmp/systemwallet.p12"
backupdays=62
export walletpassword="EnterOracleWalletPasswordHere"

ilmarkerm

-- Goal for this benchmark is just to commit a crazy amounts
-- to simulate "traditional" poorly written apps
-- It matters because other Oracle benchmarks, even if they stress redo, they do not commit often and make really large redo records
-- But I need small redo records and crazy amount of commits
-- To tests for issues like this https://access.redhat.com/solutions/5906661
-- This benchmark gives ma a lots of IOPS and tiny average IO sizes
-- Ilmar Kerm 2023

create table t (t timestamp not null) partition by hash (t) partitions 64;

ilmarkerm

declare
    c integer;
    r integer;
begin
    for rec in (select distinct con_name from v$active_services where con_id > 2 and lower(name) != lower(con_name)) loop
        c:= dbms_sql.open_cursor(security_level=>1);
        dbms_sql.parse(c=>c,
            statement=>'begin for rec in (select name from v$active_services where lower(name) != lower(con_name)) loop dbms_service.stop_service(service_name=>rec.name, stop_option=>''TRANSACTIONAL'', drain_timeout=>1); end loop; end;',
            language_flag=>dbms_sql.native,
            container=>rec.con_name);

ilmarkerm

--liquibase formatted sql

--changeset ilmker:table_audit_data_archive
--comment: Since autonomous database does not support FDA, need to create custom data versioning solution. You can make it immutable table if you want.
create table audit_data_archive (
    id raw(16) default sys_guid() not null, -- don't want it to be primary key to avoid global index on it.
    table_name varchar2(120) not null,
    table_id number(20) not null,
    table_action varchar(1) not null,
    change_time_utc timestamp default sys_extract_utc(systimestamp) not null,

ilmarkerm

#!/bin/bash

# Script for removing multipathed LUNs from Linux hosts without a reboot
# 2022 Ilmar Kerm

if [ $# -eq 0 ]; then
  echo "Usage: remove_lun.sh lunid"
  echo "Example: remove_lun.sh 36005076810818379800000000000000c"
  exit 1
fi

ilmarkerm

SELECT g.ts, c##mon.rollup_timestamp(g.ts, numtodsinterval(2, 'minute')) rollup_2min, c##mon.rollup_timestamp_tz(g.tstz, numtodsinterval(5, 'minute')) rollup_5min
FROM (SELECT sys_extract_utc(systimestamp+numtodsinterval(rownum, 'minute')) ts, systimestamp+numtodsinterval(rownum, 'minute') tstz
      FROM all_objects FETCH FIRST 20 ROWS ONLY) g;
      
TS                            ROLLUP_2MIN                   ROLLUP_5MIN                      
----------------------------- ----------------------------- ---------------------------------
2021-08-31 15:26:48.918821000 2021-08-31 15:26:00.000000000 2021-08-31 15:25:00.000000000 UTC
2021-08-31 15:27:48.918821000 2021-08-31 15:28:00.000000000 2021-08-31 15:30:00.000000000 UTC
2021-08-31 15:28:48.918821000 2021-08-31 15:28:00.000000000 2021-08-31 15:30:00.000000000 UTC
2021-08-31 15:29:48.918821000 2021-08-31 15:30:00.000000000 2021-08-31 15:30:00.000000000 UTC

ilmarkerm

SELECT count(value)
FROM alert_tnslsnr
WHERE event = 'establish' AND returncode != '0' AND comp_level_2 =~ /$listener/ AND service  =~ /$service/ AND cluster_name =~ /^$cluster$/ AND $timeFilter
GROUP BY cluster_name, returncode, service, time($__interval) fill(null)

ilmarkerm

#!/bin/bash

# This scripts parses the output from "lsnrctl show pid" and prints it either to screen to file
# Intended for "small" deployments where listener is started from RDBMS_HOME via systemd (not GI!)
# In unit file refferred as /home/oracle/bin/get_listener_pid.sh
# 2020 Ilmar Kerm

if [ -z "$ORACLE_HOME" ]; then
    echo "ORACLE_HOME environment variable is missing"
    exit 1

ilmarkerm

db="database_unique_name"

# List all configuration that GI has for an Oracle database
$GRID_HOME/bin/crsctl status res ora.$db.db -f

# List all instances
$GRID_HOME/bin/crsctl status res ora.$db.db -f | grep -E '^USR_ORA_INST_NAME@SERVERNAME' | cut -d= -f2 | sort

# Do something on each instance (rolling restart)
while IFS= read -r instance; do