Download Oracle software with the command line

When downloading software to my database server, I used to first download locally and later copy to my Unix box… but wouldn’t be convenient to download it directly on the database server? Quite often, you get no X and no Browser and no Internet access on your datacenter. Therefore, we’ll use wget to the purpose.… Continue reading Download Oracle software with the command line

Pluggable and externally identified users without using remote authentication

Yesterday I was shocked to find a note on metalink that recommends a huge security hole using a deprecated Parameter 2042219.1 : create user c##oracle identified externally + set remote_os_authent=true This is extremly sad. It is such a non-sense to recommend such a flaw. It makes me really angry 👿 Okay, for my readers I… Continue reading Pluggable and externally identified users without using remote authentication

SCP + sudo

Sometimes you like to copy files from A to B and you have sudo rights on A and B and you do a lot of “cp” to /tmp and chmod and chown’s. This is annoying… Firstly, I dislike tempfiles. they use space they generate bugs when run in parallel they often are prone to code… Continue reading SCP + sudo

cannot open database in NOARCHIVELOG

SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount quiet ORACLE instance started. Database mounted. SQL> alter database noarchivelog; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-00258: manual archiving in NOARCHIVELOG mode must identify log First time I see this. Let’s try to… Continue reading cannot open database in NOARCHIVELOG

ipcalc in powershell

Last day I wrote how to do it in AIX or Linux ip calc with ifconfig It isn’t that different in PowerShell, the fun is to the calculation yourself. For translating 0.0.0.0 in 0, we can use [IPADDRESS]. Let’s try… $ip = [IPADDRESS]( (Get-NetIPAddress -AddressFamily “IPv4” -InterfaceAlias “Ethernet*”). ipaddress) $prefix = ( Get-NetIPAddress -AddressFamily “IPv4″… Continue reading ipcalc in powershell

sqlplus: error while loading shared libraries: libsqlplus.so: wrong ELF class: ELFCLASS64

This error usually while you do something wrong. Wait, what’s an error when you do everything right? Okay, here it is: You install the instantclient 32 rpm oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64 On that server, you switch home using oraenv $ . oraenv ORACLE_SID = [oracle] ? DB01 The Oracle base has been set to /u01/app/oracle You start sqlplus… Continue reading sqlplus: error while loading shared libraries: libsqlplus.so: wrong ELF class: ELFCLASS64

network ip calculation with ifconfig

Most *nix are different. I’ll start with a plain Linux output ifconfig eth0 eth0: flags=4163 mtu 1500 inet 93.184.216.34 netmask 255.255.255.0 broadcast 93.184.216.255 to get the network ip, I just bitwise-and the inet and the netmask. To do it with the shell, I convert the ip to an integer and use the & (AND) operator… Continue reading network ip calculation with ifconfig

ODBC and EZCONNECT or my way out of active directory

The traditional way of connecting Excel (or Access) to Oracle (and other databases) is to use ODBC and TNSNAMES. You install an Oracle client, you create a connection and specify your tnsnames connection alias as server, here below DB01 tnsnames.ora DB01 = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(host=srv01)(Port=1521) ) (CONNECT_DATA= (SERVICE_NAME=DB01) ) ) But the file is often… Continue reading ODBC and EZCONNECT or my way out of active directory

Connect to ActiveDirectory with ldapsearch on Unix

In ancient times, ldapsearch could query ActiveDirectory without issues. In this examples, I used openldap client 2.4. Other tools may have other parameters. $ ldapsearch -H ldap://example.com:389 -b dc=example,dc=com cn=”Laurent C. Schneider” mail mail: laurent.c.schneider@example.com In Active Directory (AD) it is no longer the default since Windows Server 2003, unless you change dSHeuristics to 0000002… Continue reading Connect to ActiveDirectory with ldapsearch on Unix

free Oracle cloud forever

I could not miss this ! After offering free apex for non-productive usage (apex.oracle.com), free sql environment for playing (livesql.oracle.com), Oracle now offers free for ever infrastructure and database. With a few clicks, a credit card (that won’t be charged) and a few minutes of patience, you will be able to have your own Linux… Continue reading free Oracle cloud forever

Goldengate 19c on AIX for Oracle database

Now Oracle Goldengate 19.1.0.2 can manage 19c AIX Oracle DB instance. Linux has been out for a long time. Sparc is also available. For Windows, HPUX, patience … download.oracle.com $ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.2 OGGCORE_19.1.0.0.0_PLATFORMS_190823.0013_FBO AIX 7, ppc, 64bit (optimized), Oracle 19c on Aug 25 2019 22:10:20 Operating system character… Continue reading Goldengate 19c on AIX for Oracle database

on parsing arguments in shell

While most programming languages are accepting arguments as an array of strings, shell doesn’t arglist.c #include int main(int argc, char **argv) { int i; for (i=1; argc>i; i++) printf(“$%d=%s\n”,i,argv[i]); } $ make arglist cc arglist.c -o arglist $ ./arglist one two three four $1=one $2=two $3=three $4=four To do the same in shell, it requires… Continue reading on parsing arguments in shell

Published
Categorized as unix Tagged ,

Dump TNSNAMES.ORA from ActiveDirectory

Having all connections string in ActiveDirectory is nice, but maybe you need sometimes to push it to an external system (e.g. DMZ or Linux). echo “# AD” > tnsnames.ora $o = New-Object DirectoryServices.DirectorySearcher $o.Filter = ‘objectclass=orclNetService’ foreach ($p in $o.FindAll().Properties) { [String]($p.name+”=”+$p.orclnetdescstring) >> tnsnames.ora } goodies

Active Dataguard : read only with apply

A common frustration with standby is that your database is doing nothing else than applying logs. One may want to run some reports on it. Usually, the database is MOUNTED and not OPEN. This means, apart from selecting from DUAL and performance views like v$$managed_standby or v$session, there is little you can do. Possibly, you… Continue reading Active Dataguard : read only with apply

Select from cdb_* views

There is no privileges strong enough for you to view all objects in all databases Let’s try as sys: SQL> select con_id, count(*) from cdb_objects group by con_id; CON_ID COUNT(*) ———- ———- 1 22749 3 22721 as non-sys SQL> create user c##u identified by ***; User created. SQL> grant create session, select any dictionary to… Continue reading Select from cdb_* views

changing container in plsql

One of the today’s challenge, since Oracle 12c deprecated non-cdb, is to make the dba scripts CDB-aware. If you are lucky enough to have no 11g around, you can mostly replace DBA_* by CDB_* OLD: SQL> select count(*) from dba_users; COUNT(*) ———- 121 NEW: non-cdb SQL> select con_id, count(*) from cdb_users group by con_id; CON_ID… Continue reading changing container in plsql

Standard date format

Let’s start with Powershell Get-Date -format “o” 2019-03-08T17:41:02.7346332+01:00 The “O” or “o” standard format specifier represents a custom date and time format string using a pattern that preserves time zone information and emits a result string that complies with ISO 8601 docs.microsoft.com Now Linux date “+%Y%m%dT%H:%M:%S.%3N%z” In SQL to_char(current_timestamp, ‘YYYYMMDD”T”HH24:MI:SS.FF3TZH:TZM’) for my XML fans extractvalue(xmlelement(t,… Continue reading Standard date format

Published
Categorized as unix Tagged

Audit pluggable database

In the old now-deprecated maybe-soon-desupported non-cdb infrastructure, AUDIT’ing was done right after connect / as sysdba. In single-tenant (or multi-tenant), things get complicated. Once again, the doc must be read at least twice 😉 If you issue an audit statement in the root, then the database performs auditing across the entire CDB, that is, in… Continue reading Audit pluggable database

19c

19c is a mini-release. Remember it is a new name for the second 12cR2 patchset, after 12.2.0.2/18c https://mikedietrichde.com/2019/02/13/oracle-database-19-2-for-exadata-is-now-available-for-download/ was the first to mention it. By looking up in the doc I found . Distinct listagg . Desupport sqlplus product profile . listener.log log rotation Go to the doc to find more https://docs.oracle.com/en/database/oracle/oracle-database/19/whats-new.html

Oracle 19c

2014/05/distinct-listagg I wrote : Too bad the DISTINCT keyword was not implemented what a good surprise to see this working in Oracle 19 : 19C LISTAGG DISTINCT

Unix ODBC Sybase

very similar to Unix ODBC Oracle instead of tnsnames, the connections are defined in $SYBASE/interfaces. the odbc.ini must exists as well in $SYBASE. if you test with unixODBC-devel, keep in mind to use /usr/bin/isql and not $SYBASE_OCS/bin/isql $ODBCSYSINI/odbc.ini [syb] Driver = Sybase16 DSN = syb ServerName=SYB01 $ODBCINI/odbcinst.ini [Sybase16] Description = Adaptive Server Enterprise Driver =… Continue reading Unix ODBC Sybase

Unix ODBC Oracle

To connect via ODBC, check https://laurentschneider.com/wordpress/tag/odbc This article is related to Unix/Linux. Often you have a fat client written in C, while java uses JDBC instead of ODBC. Okay, it’s pretty easy, if you have an oracle client, you probably already have libsqora.so.xx.1 in your LD_LIBRARY_PATH. In this case you can connect using ODBC. What… Continue reading Unix ODBC Oracle

Published
Categorized as linux, unix Tagged

Privileges on a view

Granting too many privileges on a view could be disastrous. A view is often used as a security element; you grant access to only a subset of columns and rows to one user. Mostly only SELECT. If you want to grant update to only some rows, the security could be enhanced with the WITH CHECK… Continue reading Privileges on a view

super-long-lines in CLOB

Sometimes you use sqlplus and your line is longer than your linesize SQL> select n||’;’||x from t2; 1;one 2;twoPxMQztzLaqjWjGKOXIVIVrrHC fJkTLbRgCPiENfWrrCjUMRSkmCfLUY RdVASFacGtyEnUplOAXspDJZmSLPfg ziKYQBKoHdkqGXoBCgkKuVOdxEvhQy lZLQROFxIxOzqeQeFMGXcGkuJwnGGg zvNSuCFMVxyQgboLAUpDcYnBsuVyXX vFtWPICwqmcEdmzRRkOzzPethrqjRR aBSOTOZiYfxcYSqbqFlGuVOBRdxjFh ZcxJBvSCVXwONmS prVzTykEfSsePyYwyLVoyYrVLynUzs MLFWQxwUKNsVcYzUOAhslNldnBpITS rxPlpJbLSjJqgxNxsGVsrYhkWAMufk QnRayieEkSDYrNqyLejJuggADNxcgV tszjJIYKCxPweNGhXsOFKGbMkTBPCf DXwjBNgQYswbaNWBOEtSTHjIhdLAyM nbhyhRKKdfaTTpTgHqQelVWmnkBHjA ZTrGqdtlYAgoXNHnoryxHxVVyaMiGR SjdVlRwMas 3;three SQL> There are two extra line breaks, one after ONmS and one after wMas An easy solution is… Continue reading super-long-lines in CLOB

Published
Categorized as sqlplus Tagged

in doubt transaction

Distributed transactions allows you to have multiple DML’s over multiple databases within a single transaction For instance, one local and one remote insert into t values(1); insert into t@db02 values(2); commit; If you lose connection to db02 and wants to commit, your database server may/does not know about the state of the remote transaction. The… Continue reading in doubt transaction

Published
Categorized as dba

How to migrate non-cdb to pdb

In case you are desesperately looking for an upgrade from non-cdb to pdb with two clicks, here is the answer from Mike : you can’t Nope – not via "upgrade". You can use Data Pump, Transportable Tablespaces or Full Transportable Export/Import as a direct option but you can't upgrade and plugin in one pass unfortunately… Continue reading How to migrate non-cdb to pdb