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... ;-)

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 !

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 !!!

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 !

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 !!!

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 !!!

Default values for background_file_dest,user_dump_dest in Oracle 12c on Linux

Oracle has introduced diagnostic_dest parameter with default value of $ORACLE_BASE. It's declared it changes the location of trace files of background and user processes (therefore, background_dump_dest and user_dump_dest are deprecated now). And despite of explicit settings of their values, the trace files are going to diagnostic_dest related directories (exception when instance is starting, one trace file of user process, participating in creating Oracle instance, is created in user_dump_dest directory).

It's not important at all (couse parameters are actually deprecated), but there are some little details that could explain settings of their default values.

The values are set the method similar with setting a value of audit_file_dest parameter (with little exception).

1. If the directory

$ORACLE_BASE/admin/$ORACLE_SID/bdump

or

$ORACLE_BASE/admin/$ORACLE_SID/udump

exist, the value of corresponding parameter is set to it.

2. If (1) is not true, then their value is set to

$ORACLE_HOME/rdbms/log

Shown above doesn't relate to Oracle 11g, where default values of these parameter linked to diagnostic_dest.

Good Luck !