Oracle Troubleshoting

# KONEKSI LAMBAT
# beberapa penyebab antara lain memory issue
# kasus yg saya alami solved dengan menhapus FILE LOG
# matikan service oracle sebelum hapus LOG
# LOG allert (FILE log<nomor urut>.xml) di folder C:\app\<skema>\diag\tnslsnr\<pc name>\listener\alert
# LOG listener (FILE listener.LOG) di folder C:\app\<skema>\diag\tnslsnr\<pc name>\listener\trace
# jalanlan kembali service oracle

# sqlplus/sqlldr error:
# "The program can't start because MSVCR100.dll is missing from your computer. Try reinstalling the program to fix this problem."
# Fix: search MSVCR100.dll IN C:\app\client\<user>\product\12.1.0\client_1
# IF found, copy msvcr100.dll AND msvcp100.dll TO C:\app\client\<user>\product\12.1.0\client_1\bin
# IN my CASE, I copy both files FROM C:\app\client\<user>\product\12.1.0\client_1\oui\lib\win32

# sqlldr cannot START because oranfsodm12.dll IS missing
# IN "Oracle Database Client" Oracle DATABASE 12c RELEASE 1 (12.1.0.2.0) FOR Microsoft Windows (x64)
# Fix: IN bin folder, copying oraodm12.dll AND renaming it TO oranfsodm12.dll could be a fix

# Error saat LOAD INFILE
# Expecting filename OR "*", found "20200822".
# INFILE 20200822_ulamm_trx032020_072020.txt
# Fix: jangan awali dengan angka pada nama FILE, RENAME menjadi ulamm_20200822_trx032020_072020.txt

# ORA-28000: the account IS locked
# untuk unlock
sqlplus  /nolog
conn sys AS sysdba;
ALTER USER USER_NAME ACCOUNT UNLOCK;
# kemudian test di NEW terminal
sqlplus / AS sysdba
conn username/password  //which username u gave before unlock

# Error ORA-28040: No matching authentication protocol
# solusi dari client, coba UPDATE oracle client
# jika tidak berhasil, pakai solusi server. tambahkan parameter di FILE sqlnet.ora kemudian restart oracle
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
# setelah restart, jika muncul error ORA-01017: invalid username/password; logon denied
# solusinya ALTER USER expire kemudian unlock kembali
# cek CDB atau PDB
show con_name
CON_NAME
------------------------------
ORCLPDB1
# jika SESSION CDB dan usernya dibuat adalah USER PDB, maka pindah dulu ke PDB
ALTER SESSION SET container=pdb1
# ALTER USER menjadi expire
ALTER USER hr password expire;
# unlock USER
ALTER USER hr IDENTIFIED BY <PASSWORD> account unlock;

# error ORA-12638: Credential retrieval failed, fix:
# buka FILE sqlnet.ora, ubah SQLNET.AUTHENTICATION_SERVICES= (NTS) menjadi SQLNET.AUTHENTICATION_SERVICES= (NONE)

# error "ORA-24454: client host name is not set" WHEN connecting FROM Ubuntu instance
sudo /bin/bash -c "echo '127.0.0.1 ${HOSTNAME}' >> /etc/hosts"

# INSERT single quote masalah, tambahkan single quote satu lagi di depan single quote
# contoh REPLACE dari javascript
# a.REPLACE(/\'/g, "\''")