Oracle: Cleaning up SYS_EXPORT_SCHEMA_xx jobs

Sometimes expdp jobs fail for any number of reasons. Re-running expdp, I noticed that the number had increased since the prior run; e.g., SYS_EXPORT_SCHEMA_02 instead of SYS_EXPORT_SCHEMA_01. As it turns out, these are potentially orphaned jobs, so clean ’em up!

Generally, copied from Anar Godjaev’s excellent blog post:

SQL> select owner_name, job_name, operation, job_mode, state, attached_sessions from dba_datapump_jobs;

SQL> drop table {owner_name}.SYS_EXPORT_SCHEMA_01;
SQL> purge table {owner_name}.SYS_EXPORT_SCHEMA_01;

Advertisements

Oracle: ORA-00257 archiver error. Connect internal only, until freed

Work around for this issue is to increase the amount of space allocated to db_recovery_file_dest; e.g.,

$ sqlplus sys as sysdba
SQL> show parameter db_recovery_file;
db_recovery_file_dest string /opt/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 20G

Compare this with the output of ‘du -sh’; e.g.,

$ du -sh /opt/oracle/flash_recovery_area
21G /opt/oracle/flash_recovery_area

21 vs 20, so set it to something a bit bigger; e.g.,

SQL> alter system set db_recovery_file_dest_size = 30G;

Extracted from this link on Remedian.com.


SOLVED: VirtualBox shows only 32-bit guest versions on my (64-bit) Windows 10?

I quickly pressed Windows Key + q to open the Search box and typed in: turn windows features on or off Turn windows features on or off I scanned a few options but one in particular was salient: Hyper-V was enabled.

So I installed the 64-bit version of Docker for Windows after configuring a shiny new VirtualBox CentOS 7 guest. The latter’d ran just fine previously, but was now causing a BSOD, and I wasn’t even able to create new 64-bit guests.

As it turns out, installing Docker enables Hyper-V, but uninstalling Docker doesn’t disable Hyper-V; i.e. these virtualization technologies are incompatible. The fix to this is quoted above: disable Hyper-V.

http://www.fixedbyvonnie.com/2014/11/virtualbox-showing-32-bit-guest-versions-64-bit-host-os/

Skipping incompatible lclntsh

Received this error whilst make-ing oci8 for PHP. It was a fresh install of 64-bit CentOS 5.4 w/ Oracle XE 10g installed; e.g.

$ sudo make
.
.
.
/usr/bin/ld: skipping incompatible /usr/lib/oracle/10.2.0.4/client/lib/libclntsh.so when searching for -lclntsh
/usr/bin/ld: cannot find -lclntsh
$

So I checked for /usr/lib/oracle/10.2.0.4/client/lib/libclntsh.so — it was there, but for some reason it was “incompatible”. Google helped once again. The problem was that the 10g RPM was 32-bit, but I was compiling a 64-bit version of oci8. how vague is that?

To resolve the issue, I installed the 64-bit -basic, -devel versions of Oracle Instant Client. Obviously I was being a smart-ass, since I thought that the XE RPM would’ve been sufficient, not!

When running the configure script, prepend with the path to Oracle Instsnt Client:

$ phpize
$ ./configure -with-oci8=instantclient,/usr/lib/oracle/11.2/client64/lib
$ sudo make
$ sudo make install

Hopefully this helps the next one confounded by “skipping incompatible lcntsh”.

ORA-01653: unable to extend table SYSTEM.AUD$

This set of commands helped me resolve ORA-01653 quickly. First we need to find out the path to the appropriate datafile(s):

$ sqlplus sys as sysdba
SQL> select name from v$datafile where name like '%system%';

NAME
--------------------------------------------------------------------------------
/oradata/dgs3/system01.dbf

1 rows selected.

There’s only one datafile, so we’ll need to check if the /oradata (or /) has sufficient space:

$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 50G 47G 2.0M 100% /
/dev/sdg 50G 32G 16G 67% /mnt/sdg

/dev/sda1 is full, hence the error. Oracle supports multiple datafiles, so we’ll do just that:

$ sqlplus sys as sysdba
SQL> alter tablespace system add datafile '/mnt/sdg/oradata/dgs3/system02.dbf' size 1g;

OCIEnvNlsCreate() failed again

So I’ve downloaded and installed Fedora 12 for the work laptop for awhile now. Previously it was Linux Mint 7/8, but I haven’t actually used the machine yet, as I encountered that darned OCIEnvNlsCreate() error message again. Same setup, just Oracle Express 10 on Linux, except the distro is Fedora this time.

So I was looking for a place to set environment variables — Apache’s SetEnv directive DOES NOT work — and there is no equivalent of Debian’s /etc/apache2/envvars in Fedora, at least I’m told. The weird thing is, plenty of people have encountered the problem before, but never found the solution — or didn’t bother to post the solution online.

I finally found the solution in an Oracle-hosted article about Oracle, PHP and Linux. It’s not really well-described, so I thought to post my solution here instead. Insert the call to oracle_env.sh somewhere in /etc/init.d/httpd. I put mine above the start() function definition:

. /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh

oracle_env.sh file should contain something similar to the following:

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_HOME
ORACLE_SID=XE
export ORACLE_SID
NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh`
export NLS_LANG
PATH=$ORACLE_HOME/bin:$PATH
export PATH
if [ $?LD_LIBRARY_PATH ]
then
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
else
LD_LIBRARY_PATH=$ORACLE_HOME/lib
fi
export LD_LIBRARY_PATH

Once you’re done, save the file (you need to be root) and then restart httpd:

sudo /etc/init.d/httpd restart

Changing hostname

Should you update /etc/hostname — I did — be sure to update the default tnsnames.ora and listener.ora.

Mine were found in /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin.

Otherwise you’ll get an ORA-12541 message.