Oracle, Web, Script, SQLserver, Tips & Trick

May 8, 2019

How to connect Form/Report dev6i to Oracle Database 12c

Filed under: dev6i,oracle,oracle:12c — sikathabis @ 2:21 am
Tags: , , ,

We have database 10.2.0.5, want to convert to 12.2.0.1, the step are:
– exp from 10.2.0.5
– imp to 12.2.0.1
import Oracle 12.2.0.1 has been completed.

And then the first problem is oracle form crash when connecting to oracle 12.2.0.1

After searching, the solution come from this website:
https://www.youtube.com/watch?v=V0Achdev4gA

CONNECT AS SYSDBA
CONNECT SYSTEM USER (SYSTEM/(OWN YOUR DATABASE PASSWORD))
SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SHUTDOWN;
STARTUP MOUNT;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;

After solving the first problem, then the second problem is coming, ORA-28040: No matching authentication protocol

For second problem, this reference is the solution:
http://logic.edchen.org/how-to-resolve-ora-28040-no-matching-authentication-protocol/

Adding SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 to sqlnet.ora in 12.1 database server (not the old client).

Connecting successfulle but still got malformed TTC package message, will solved later ..

SQL*Plus: Release 8.0.6.0.0 – Production on Wed May 8 15:24:46 2019

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Enter password:
ERROR:
ORA-03137: malformed TTC packet from client rejected: [kpoal8Check-5] [0]
[0x000000000] [0x000000000] [0] [0] [2080] []

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL>



Advertisements

October 9, 2014

How to change tablespace on indexes using script

Filed under: oracle — sikathabis @ 8:29 am
Tags: , ,

In this case:

  • I have tablespaces VBS_MASTER, VBS_MASTER_IDX, VBS_HIST, VBS_HIST_IDX
  • I want to move all indexes on user DBADMIN tablespace _IDX to without _IDX  (exp: VBS_HIST_IDX –> VBS_HIST)

Script running on user DBADMIN:

spool movetablespace.sql
 select 'ALTER INDEX ' ||INDEX_NAME||' REBUILD TABLESPACE '|| replace(TABLESPACE_NAME,'_IDX','')||';' as "--TITLE"
 from dba_indexes
 where owner='DBADMIN' and tablespace_name like '%IDX';
 spool off
 @movetablespace.sql

September 29, 2014

Cara upgrade Oracle Database 10g 10.2.0.1 menjadi 10.2.0.5

Filed under: oracle,oracle:10g — sikathabis @ 12:01 am
Tags: , ,

Secara garis besar dari dokumen readme.htm

  1. Matikan lsnrctl, matikan enterprise manager console, matikan database
  2. Installasi dari patch yg didownload (setup.exe), kemudian oracle homenya pilih ke lokasi oracle yg mau di patch
  3. Untuk upgrade database terdiri dari Interaktif mode dan manual mode:
    • Untuk interaktif Jalankan dbua pada CMD ~ README.htm#CJAGAIBC (jika ini tidak berhasil lakukan cara dibawah ini
    • Untuk manual (liat di readme.htm utk yg manual) ~ README.htm#CJGGABGI
      1. 10.5.2.1 Run the pre-ugrade information tool ini berfungsi utk pengecekan awal
      2. 10.5.2.2 Upgrading a release 10.2 Database terdiri dari nomor 1 s/d 15, tapi tidak semua dijalankan.  Jalankan nomor 1, 4, 5, 7, 10, 11 dan 15 (jika di perlukan)

Untuk detailnya silahkan baca dari readme.htm atau extract patch 10.2.0.5, ada file readme.htm

Attachment: Readme.htm, hilangkan extension .jpg

December 23, 2013

How to kill oracle.exe on windows

Filed under: oracle,windows — sikathabis @ 7:06 am
Tags: , ,

Use tasklist to show all process on windows

Then taskkill to kill pid, for example:

kill process oracle.exe

reference : http://www.dba-oracle.com/t_kill_process_windows.htm

September 12, 2013

How to exclude command from Oracle Streaming

Filed under: oracle — sikathabis @ 3:33 am
Tags: ,

declare
vStreamTag        VARCHAR2(32) := SUBSTR(USERENV(‘SESSIONID’),1,18)||TO_CHAR(SYSDATE,’YYYYMMDDHH24MISS’);
BEGIN
SYS.DBMS_STREAMS.SET_TAG(HEXTORAW(vStreamTag));
–enter command that you want to pass in the oracle streaming

commit;

END;

June 3, 2013

NID-00135: There are 1 active threads

Filed under: oracle — sikathabis @ 3:34 am
Tags: ,

Ketika akan merubah database names dan terjadi error NID-00135: There are 1 active threads

Yang perlu anda lakukan adalah:

  • shut immediate
  • startup mount
  • alter database open
  • shut immediate
  • startup mount

Lalu jalankan kembali NID-nya

reference: http://burzaco.wordpress.com/2010/03/31/nid-00135-there-are-1-active-threads/

May 29, 2013

Oracle hang analysis script

Filed under: oracle — sikathabis @ 5:02 pm
Tags: , ,
-- ################################################
-- # Creator: Vincent Fenoll
-- # Created: 2011/10/27
-- # Name: Connect to Hanged DB
-- ################################################
-- #
-- # Compatible: Oracle 10g 11g
-- #
-- ################################################
-- #
-- # How to connect to the sga when DB is hanging
-- # and connection is not permitted
-- #
-- ################################################

-- There is an option that is helpful when the databse is hanging and we can't connect 
-- to database but we need to execute commands like oradebug

-- With sqlplus -prelim 
-- we can connect to the sga but not to the database, in this case no session is created.

1) sqlplus -prelim / as sysdba

2) sqlplus /nolog
set _prelim on
conn / as sysdba

Example:

$sqlplus -prelim /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 26 19:35:42 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 12
Hang Analysis in C:\oracle\admin\orcl\udump\orcl_ora_5564.trc
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name
C:\oracle\admin\orcl\udump\orcl_ora_5598.trc

links: http://www.oracle-scripts.net/?p=325
another tips http://orachat.com/oracle-hanganalyze/

another tips for EM: how to fix hang analysis on enterprise manager

April 8, 2013

How to resize all datafile on a tablespace (oracle)

Filed under: oracle — sikathabis @ 6:42 am
Tags: ,

Example resize all datafile on tablespace VBS_HISTORY.ORA to 2375M

spool genresize.sql
select 'ALTER DATABASE DATAFILE '''||FILE_NAME||''' RESIZE 2375M;'
from dba_data_files
where tablespace_name='VBS_HISTORY.ORA';
spool off
@genresize.sql

April 4, 2013

Mencari kata/kalimat pada semua procedure/function/package di Oracle

Filed under: oracle — sikathabis @ 6:57 am
Tags: ,

Sebagai contoh sederhana

Target:

Mencari kata ‘6730’ pada semua procedure/function/package di user DBADMIN

Solusi:

SELECT *
FROM   DBA_SOURCE
WHERE  OWNER = 'DBADMIN'
AND    TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE BODY', 'PACKAGE')
and text like '%6730%';

Contoh hasil:

contohnya

March 16, 2013

Melihat log archived dan startup database utk ODG

Filed under: oracle — sikathabis @ 1:12 pm
Tags: ,

Log Archived

tail -50f /backup/dump/VBSUPG/bdump/alert_VBSDRC.log

Startup database utk Oracle Data Guard (DRC)

startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect;

Next Page »

Blog at WordPress.com.

Pureblood

DONT WANNA BE NUMBER ONE

Learning is not a spectator sport

Connor McDonald on SQL and the Oracle Database

Teknologi Pendidikan

Just another WordPress.com weblog

BLOG KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Tonk Kosonk Baonk Bunyinya

Penampungan Serapah Tanpa Sumpah

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Desa Yang Mencintai Desa dan Kebudayaannya

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server

arihdyacaesar

"Tuhan tidak menuntut kita untuk menang. Tuhan hanya meminta kita untuk berjuang tanpa henti.” -ean-

neinframe

Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

Angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

Don Charisma

because anything is possible with Charisma