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 !

Partial restore and recover of Oracle database

There was the need to restore part (system, sysaux, undo tablespaces and small data tablespace) of large Oracle RAC database (5,5TB) from tape backup, made 2 weeks before. Backup was made on standby site (that is backup consisted  fully of files of standby database), primary db during the restore time was not awailable.

RMAN repository was not used. The new auxiliary instance with small sga and other related parameters was created for that purpose. One of the possible steps of restoration presented below.

1. We have to restore datafiles from backup. It's possible to use existing standby controlfile, if it "knows" about interesting backup's details. If not, older standby controlfile has to be restored from backup, for example:

RMAN> restore controlfile to '/ora/oradata/aaa_temp/control_aaa_temp_01.ctl' from 'c-2517399282-20170801-00';

or

RMAN> restore controlfile to '/ora/oradata/aaa_temp/control_aaa_temp_01.ctl' from autobackup;

2. Then mount auxiliary instance with recovered controlfile and restore only interested datafiles in the similar way like this rman script does (because we have cluster database, we have to restore ALL undo tablespaces) :

run {
  allocate channel ch00 device type sbt_tape ;
  allocate channel ch01 device type disk ;


  # set recover database condition
  set until time "to_date('01.08.2017 08:00:00','dd.mm.yyyy hh24:mi:ss')" ;

  # set newname for interested datafiles
  set newname for  datafile 1 to '/rab/oradata/aaa_temp/system.282.870241429'  ;
  set newname for  datafile 8 to '/rab/oradata/aaa_temp/sysaux.268.870241429'  ;
  set newname for  datafile 9 to '/rab/oradata/aaa_temp/undotbs1.283.870241429'  ;
  set newname for  datafile 10 to '/rab/aaa_temp/undotbs2.270.870241429'  ;
  set newname for  datafile 11 to '/rab/aaa_temp/undotbs3.285.870241429'  ;
  set newname for  datafile 18 to '/rab/aaa_temp/undotbs4.284.870241429'  ;
  set newname for  datafile 19 to '/rab/aaa_temp/userdata.ora'  ;

  restore datafile 1,8,9,10,11,18 ;

  set archivelog destination to '/temporary_stage' ;
  restore archivelog until time "to_date('01.08.2017 08:00:00','dd.mm.yyyy hh24:mi:ss')" ;

  # next statement will not run because controlfile is a standby control file
  # recover database skip forever tablespace ;

  release channel ch00 ;
  release channel ch01 ;
}

3. At this point we need backup controlfile from primary db, or we have to create it from scratch. Because primary database was unavailable, we had to, using sql statement 'alter database backup controlfile to trace as '...' ;' create sql pattern for creating new controlfile for recovered datafiles with resetlogs section. It's also possible to modify quantity and size of redo log files and other parameters influencing to final size of new controlfile. Next is example of new controlfile creation script :

shutdown immediate
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "AAA" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 8
    MAXLOGMEMBERS 1
    MAXDATAFILES 10
    MAXINSTANCES 4
    MAXLOGHISTORY 100
LOGFILE
  GROUP 1 (
    '/rab/oradata/aaa_temp/group_3.2964.914404769'
  ) SIZE 1024M,
  GROUP 2 (
    '/rab/oradata/aaa_temp/group_6.2994.892222399'
  ) SIZE 1024M
DATAFILE
  '/rab/oradata/aaa_temp/system.282.870241429',
  '/rab/oradata/aaa_temp/sysaux.268.870241429',
  '/rab/oradata/aaa_temp/undotbs1.283.870241429',
  '/rab/aaa_temp/undotbs2.270.870241429',
  '/rab/aaa_temp/undotbs3.285.870241429',
  '/rab/aaa_temp/undotbs4.284.870241429',
  '/rab/aaa_temp/userdata.ora'
CHARACTER SET RU8PC866
;ALTER DATABASE ADD LOGFILE THREAD 2
  GROUP 3 (
    '/rab/oradata/aaa_temp/group_14.757.914404773'
  ) SIZE 1024M REUSE,
  GROUP 4 (
    '/rab/oradata/aaa_temp/group_15.25922.914404775'
  ) SIZE 1024M REUSE;
ALTER DATABASE ADD LOGFILE THREAD 3
  GROUP 5 (
    '/rab/oradata/aaa_temp/group_28.25921.914404779'
  ) SIZE 1024M REUSE,
  GROUP 6 (
    '/rab/oradata/aaa_temp/group_30.4341.914404783'
  ) SIZE 1024M REUSE;
ALTER DATABASE ADD LOGFILE THREAD 4
  GROUP 7 (
    '/rab/oradata/aaa_temp/group_35.4226.914404787'
  ) SIZE 1024M REUSE,
  GROUP 8 (
    '/rab/oradata/aaa_temp/group_36.642.914404791'
  ) SIZE 1024M REUSE ;

4. Catalog archivelog files and recover part of database with the similar rman script :

run {
  allocate channel ch00 device type sbt_tape ;
  allocate channel ch01 device type disk ;

  set until time "to_date('01.08.2017 08:00:00','dd.mm.yyyy hh24:mi:ss')" ;
  catalog start with 'temporary_stage' noprompt ;
  recover database ;

  release channel ch00 ;
  release channel ch01 ;
}


5. Now you can open database with resetlogs option and have a lot of fun :-).

P.S. There will be datafiles with 'MISSING' word in their names. If you don't need dictionary data linked with objects in these missing files (for example, AWR or ASH data), remove them with help of the following dynamic sql :

> spool drop_missing.sql
> select distinct 'drop tablespace '|| b.name||' including contents and datafiles ;' from v$datafile a,v$tablespace b where a.ts#=b.ts# and a.name like '%MISSING%'  ;
> spool off
> start missing.sql

P.P.S. Accessibility of primary database controlfile gives more opportunities to achieve the same results: a ) rman command 'recover database skip forever tablespace' ; b) sql command 'alter database datafile n offline for drop' etc.
P.P.P.S. Improtant thing mentioned before - if you have cluster database,  for partial database restoration you have to restore all undo tablespaces, otherwise ora-00600 and other 'unexplained', 'wierd' errors will occur.

sqlplus easy connect could not connect on linux ORA-12504 ORA-01017

When you try to connect to database using sqlplus with collaboration of easy connect feature, you may get a bit confused by not having successfully logged in when you don't enter user's password inline. In other case (with inline password), the following command will be ok :

sqlplus username/password@IP_OR_HOSTNAME:PORT/SERVICE_NAME

and following will not:

sqlplus username@IP_OR_HOSTNAME:PORT/SERVICE_NAME

In such situation you'll get sqlplus breaf help text. The workaround is using single or double quotes with escape symbols :

sqlplus username@\"IP_OR_HOSTNAME:PORT/SERVICE_NAME\"

or

sqlplus username@\'IP_OR_HOSTNAME:PORT/SERVICE_NAME\'

You may catch errors ORA-12504 ORA-01017 if you place quotes in wrong place. For example, you'll get ORA-01017 in the following case :

sqlplus \'username@IP_OR_HOSTNAME:PORT/SERVICE_NAME\'

or  ORA-12504 when you'll not use escape quoting, like in :

sqlplus username@"IP_OR_HOSTNAME:PORT/SERVICE_NAME"

Good luck !