This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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; | |
*/ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
NewerOlder