Thursday, May 30, 2019

Create database link between Oracle databases with non-supported client/server connectivity

Lets consider two databases - db_a (Oracle 12.2) and db_b (Oracle 9.2). The way of connection is from A to B. I hadn't managed to connect from B to A using below steps. If you know how, please, share in the comments.

We need proxy database in order to organize supported cascading database links. In my case it was 11.2 database (let name it proxy_db).

What we need to do ? The action plan as in the following :

1. Create empty schema proxy_user in the proxy_db. 

2. Create database link proxy_to_db_b connecting newly created schema (1) to the schema in the target (9.2) database. 

3. Create private synonyms using database link (2) for all (or for all needed) objects of schema of the target (9.2) database.

4. Create database link db_a_to_proxy_db in the source (12.2) database. 

Now you can address tables via table_name@db_a_to_proxy_db clause. BUT, if you're using database links to target (9.2) database inside PL/SQL, you need to do more, as in the following, otherwise you'll not be able to compile your PL/SQL objects.

5. Create empty schema (metadata only and needed PL/SQL objects) target_user in the source database 12.2 (db_a).
6. Create database link (in source 12.2 database) from app schema to schema from item (5) using database link name as in item (2) (proxy_to_db_b). 

Now you will be able to modify and compile PL/SQL which depends (via db_link) on objects in the destination (9.2) database.

Look at My Oracle Support note 453754.1 for additional information. This document also says you
you can use dynamic SQL inside PL/SQL to work around (the database link is not resolved at compile time but at run time).

Good Luck

Tuesday, February 12, 2019

Random number inside sed substitution - example

Let's take an example sql file 1.sql, which contains only one line :

# cat 1.sql
  
CREATE BITMAP INDEX "USER"."xxx_A_xxx_xxx_xxx_ELEMENT_" ON "USER"."A_USER-2004" ("XXX")


We need to replace _ELEMENT_ to number value. Here is one of the ways to do it :


# sed "s/_ELEMENT_/_$RANDOM/ ; s/.*/echo '&'/e" 1.sql
   CREATE BITMAP INDEX "USER"."xxx_A_xxx_xxx_xxx_27237" ON "USER"."A_USER-2004" ("XXX")


Good Luck !

P.S. It seems the sed's info page is quite misleading about substitution flag /e... ;-)

Thursday, January 31, 2019

How to get input and make output to the same file on Linux

There can be several options. Suppose we have text file a, filled up with literal 'a', and our objection is change its contents to 'b', in other words replace every 'a' to 'b'

1. Using GNU sed to edit file in place (with backup of file a applying extension [SUFFIX] to name):

# sed -i[SUFFIX] 's/a/b/g' a

or without backup of file a :

# sed -i 's/a/b/g' a

P.S. On Solaris, it's possible to use gsed instead of sed

2. Using interim file :

# sed 's/a/b/g' a > interim_file && mv interim_file a

3. Playing with file descriptors. One possible variant is :

# exec 3<>a && sed 's/a/b/g' a >&3 && exec 3<&-

4. Using shell process substitution :

# sed 's/a/b/g' a > >(sleep 1 && cat > a)

Sleep is necessary to make some interval after sed finish its work

5. Many other alternatives, using ed, cat&echo etc.

Good Luck !

Tuesday, November 6, 2018

RMAN-12008: could not locate backup piece; BUT file actually exists

Hi !

If you get this error during restoration operation of your DB and mentioned backuppiece is actually on the place and it's not corrupted, then try to disable cluster_database parameter (alter system set cluster_database=false or reset it) and repeat failed operation.

Good Luck !!!

Thursday, September 27, 2018

How to decrypt db_link password in Oracle 10g

Here is example PL/SQL script. Password for db_link was been gotten from ku$_dblink_view.

set serveroutput on
declare
 db_link_password varchar2(100);
begin
 db_link_password := '';

 dbms_output.put_line ('Plain password: ' || utl_raw.cast_to_varchar2 ( dbms_crypto.decrypt ( substr (db_link_password, 19) , dbms_crypto.des_cbc_pkcs5 , substr (db_link_password, 3, 16) ) ) );
end;
/


Good Luck !

Tuesday, August 21, 2018

How to connect to Oracle 9i from SQL Developer 18

If you encounter to the following message ...

An error was encountered performing the requested operation:

ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region  not found
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.
Vendor code 604

... you can fix it by adding

AddVMOption -Duser.language=en
AddVMOption -Duser.region=us
AddVMOption -Duser.timezone="EET"

to your /ide/bin/ide.conf file.

Good Luck !!!

Tuesday, December 19, 2017

Unable to free disk space after online moving of datafile by ALTER DATABASE MOVE statement on Linux


Many times I encountered into situation, when, after successful moving of large datafile to new place, amount of free space in old place is not increasing.

To resolve :

1. Find oracle processes continuing using deleted datafile. In my cases, it always were user' (not system') processes.

:> lsof | grep deleted_file_name_with_path

2. Find file descriptor number for deleted datafile of each process. It's also possible to verify file descriptor number via /proc filesystem.

3. Run gdb (GNU debugger) and close open file descriptor for deleted datafile.

4. Repeat above steps for each oracle process, holding deleted datafile.

5. Control free disk space on the filesystem.

Example :

1. Searching for PID and FD

:> lsof | grep delet

oracle_11 119147           oracle  258u      REG                8,3 286157651968   67109389 /u01/oradata/ofsaaprd/aaa_from_test.ora (deleted)

Here :
- 119147 - PID of the oracle process ;
- 258 - interesting file descriptor number

2. Run gdb

:> gdb -p 119147

3. Close FD in gdb

(gdb) call close (258)

If you get the error

'close' has unknown return type; cast the call to its declared return type

then try 

(gdb) call (size_t) close (258)


4. Quit gdb

(gdb) q

An example of it run in the command line  :

while read line ; do ls -l /proc/$line/fd | grep dele | awk '{print "call (size_t) close ("$9")\n"}' > /tmp/gdb.cmd ; echo "quit" >> /tmp/gdb.cmd ; gdb -b atch -x /tmp/gdb.cmd -p $line ; done < <(lsof | grep delet | grep path_pattern | awk '{print $2}' | sort -u )

Good Luck !!!