Archive

Author Archive

Using SQL*Plus copy command in Oracle 11gR2

March 20, 2014 Leave a comment

There’s a unusual command in SQL*Plus that may be useful under certain circumstances: the COPY command (http://docs.oracle.com/cd/E11882_01/server.112/e16604/apb.htm).

Although this command will be obsoleted in future releases of SQL*Plus, Oracle has been saying the same since (at least) Oracle 9, and it’s still available in Oracle 11gR2.

I need to copy a table from one database to another database, and I don’t want to do an export/import of the table.

A simple copy of the table is enough for me:


miquel@db_2> copy from otheruser@db_1 create my_table using select * from other_table;
Enter FROM password:

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit when done. (copycommit is 0)
Maximum long size is 2000000000. (long is 2000000000)
Table MY_TABLE created.

55481 rows selected from otheruser@db_1.
 55481 rows inserted into MY_TABLE.
 55481 rows committed into MY TABLE at DEFAULT HOST connection.

It’s that simple!

Feel free to comment below.

Categories: 11gR2, oracle, SQL*Plus Tags:

Corrupt block found – Oracle 11g Release 2

January 31, 2014 Leave a comment

I’m getting errors of block corruption in the alert.log of an Oracle 11g Release 2 test database:


Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xFFFFFFFA7DBAF398] [PC:0x896E0BE, kcbgcur()+3026] [flags: 0x0, count: 1]

check value in block header: 0x46c2
 computed block checksum: 0x0
Reading datafile '/app/oracle/oradata/database2/sgirgea_01.dbf' for corruption at rdba: 0x0a808912 (file 42, block 35090)
Reread (file 42, block 35090) found same corrupt data (no logical check)
Corrupt Block Found
 TSN = 46, TSNAME = SGIRGEA
 RFN = 42, BLK = 35090, RDBA = 176195858
 OBJN = 0, OBJD = 258730, OBJECT = SGIRGEA, SUBOBJECT =
 SEGMENT OWNER = , SEGMENT TYPE = Temporary Segment
Hex dump of (file 42, block 35218) in trace file /app/oracle/diag/rdbms/database1/database1/trace/sid_w000_23395.trc
Corrupt block relative dba: 0x0a808992 (file 42, block 35218)
Bad header found during buffer read
Data in bad block:
 type: 35 format: 2 rdba: 0x01408992
 last change scn: 0x0000.000b6126 seq: 0x3 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x61262303
 check value in block header: 0xcffd
 computed block checksum: 0x0

In this case it’s not a data corruption,  I’ts due to a silly mistake in the creation of a tablespace.

I have two databases created on the same server (it’s a test server), and the datafiles of the two databases in different folders:

  • /app/oracle/oradata/database1: datafiles of the database 1.
  • /app/oracle/oradata/database2: datafiles of the database 2.

I’m testing an application with different database configurations. So, I created this user in database 1, with its own datafile, but by mistake I created the datafile on /app/oracle/oradata/database2, the folder for the other database’s datafiles.

Then, I did the same for the database 2, created the same user, with its own tablespace, and now, I created the datafile correctly in its own folder (/app/oracle/oradata/database2), with the same name as the other database’s datafile. This is an illustration of this mistake:

Oracle shared datafile

Oracle shared datafile

When I noticed the error in the alert log, I performed a backup of the user on both databases. Then, dropped the user in database 1 to recreate it again, but when I try to delete the tablespace in database 1 I get this error:


SQL@database1> drop tablespace sgirgea;
drop tablespace sgirgea
*
ERROR at line 1:
ORA-01122: database file 42 failed verification check
ORA-01110: data file 42: '/app/oracle/oradata/database2/sgirgea_01.dbf'
ORA-01210: data file header is media corrupt

Trying to alter the tablespace to bring it offline doesn’t works:


SQL@database1> alter tablespace sgirgea offline;
alter tablespace sgirgea offline
*
ERROR at line 1:
ORA-01122: database file 42 failed verification check
ORA-01110: data file 42: '/app/oracle/oradata/database2/sgirgea_01.dbf'
ORA-01210: data file header is media corrupt

I try to bring the tablespace offline with the database mounted, but I can not shutdown the database for the same reason:


SQL@database1> shutdown immediate;
ORA-01122: database file 42 failed verification check
ORA-01110: data file 42: '/app/oracle/oradata/database2/sgirgea_01.dbf'
ORA-01210: data file header is media corrupt

Prior to a shutdown abort, I tried to offline the datafile, instead of the tablespace:


SQL@database1> alter database datafile 42 offline;
alter database datafile 42 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

But it’s not possible because the database is in NOARCHIVELOG mode, as this is a test environment.

Finally, I tried an offline drop of the datafile and it worked. Then just dropped the tablespace just including contents, not the datafile (without the “including contents and datafiles” clause, only “including contents“):


SQL@database1> alter database datafile 42 offline drop;

Database altered.

SQL@database1> drop tablespace sgirgea including contents;

Tablespace dropped.

Then I checked for the datafile and it’s still there, and it’s correctly attached to database2, and everything works fine on database 2. Just have to recreate the same user in database 1 to continue testing.

Please, feel free to leave your comments below.

EXP-00008: ORACLE error 1455 encountered

January 31, 2014 Leave a comment

I’m trying to export a user on a Oracle 11g Relase 2 database (11.2.0.3) using my local client, Oracle 10.2.0.4.

With certain users I’m getting this error:


$ exp system@sid file=sgirgeaext_"$(date +%Y%m%d)".dmp log=sgirgeaext_exp_"$(date +%Y%m%d)".log consistent=Y statistics=none owner=sgirgeaext

Export: Release 10.2.0.4.0 - Production on Fri Jan 31 11:13:33 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SGIRGEAEXT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SGIRGEAEXT
About to export SGIRGEAEXT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SGIRGEAEXT's tables via Conventional Path ...
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

I tried also not to exporting indexes (indexes=N), as some other blogs suggested, but no luck.

This is due to the different versions of my client export and the database target.

I tried the same export using the 11gR2 client and the export finished without errors.

Categories: 10gR2, 11gR2, oracle Tags: , ,

2013 in review

January 7, 2014 Leave a comment

Thanks to all of you who have read and commented on my (our) blog!

Happy 2014!

Miquel

 

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 94,000 times in 2013. If it were an exhibit at the Louvre Museum, it would take about 4 days for that many people to see it.

Click here to see the complete report.

Categories: Uncategorized Tags:

Error when creating oracle 11gR2 database with dbca: ORA-21561: OID generation failed

September 11, 2013 2 comments

I’m creating a new Oracle 11g release 2 database on Red Hat Enterprise Linux Server release 6.1 using the Database Configuration Assistant (dbca).

When I’ve finished all the configuration steps, create a database with dbca and I get this error:


$ dbca

ORA-21561: OID generation failed

The error is in the /etc/hosts file, as this is a virtual machine generated from a template (we’re using VMWare) that we reuse to create new virtual database machines.

I’ve to assign the correct IP and now the database creation is successful.

Categories: linux, oracle Tags:

Errors starting Oracle netca or dbca in Linux

September 2, 2013 Leave a comment

I’m configuring a new database on a recently created VMWare virtual server.

When I try to launch netca (or dbca, or whatever else), I get this error:


[oracle@sxxx admin]$ netca
 java.lang.NoClassDefFoundError: oracle/sysman/oii/oiil/OiilNativeException
 at oracle.net.ca.NetCAUtils.getOracleBase(NetCAUtils.java:69)
 at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:218)
 at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
 at oracle.net.ca.NetCA.main(NetCA.java:427)

Error: oracle/sysman/oii/oiil/OiilNativeException
 Oracle Net Services configuration failed. The exit code is 1

 [oracle@sxxx admin]$ dbca
 Exception in thread "main" java.lang.NoClassDefFoundError: oracle/sysman/oii/oiil/OiilNativeException
 at oracle.sysman.assistants.util.attributes.InitParamAttributes.initialize(InitParamAttributes.java:571)
 at oracle.sysman.assistants.util.attributes.InitParamAttributes.<init>(InitParamAttributes.java:541)
 at oracle.sysman.assistants.util.step.StepContext.<init>(StepContext.java:325)
 at oracle.sysman.assistants.dbca.backend.Host.<init>(Host.java:823)
 at oracle.sysman.assistants.dbca.ui.UIHost.<init>(UIHost.java:258)
 at oracle.sysman.assistants.dbca.ui.InteractiveHost.<init>(InteractiveHost.java:54)
 at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:164)
 at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:112)
 at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:180)
 

First of all, I reviewed all my environment variables, then launched xclock to check that my X are well configured, and all that stuff was ok.

The problem is that the filesystem where the oracle binaries were installed were moved to another filesystem by our sysadmins, and the permissions of the entire oracle folder and subfolders were changed to root:root.Simply revert back permissions to oracle:dba solved this silly error.

Never trust on your sysadmins ;-D

Categories: linux, oracle Tags: ,

Install Lotus Notes 9 on Ubuntu 13.04 64 bits

June 21, 2013 8 comments

First of all, try the installation process specified in the section “Modify the deb package and install again”. This post is the full log of my installation, but I have had to reinstall again following the steps of this section because the system became unstable.

Lets try to install the new Lotus Notes 9 in my fresh installation of Ubuntu 13.04 64 bits.

First of all, a quick view of Usable software backlog blog, a must of every Lotus Notes on Ubuntu user: http://usablesoftware.wordpress.com/2013/03/21/quick-guide-installing-notes-9-on-ubuntu-13-04-beta1-64bit/

I tried to install and I get a lot of dependencies (intentionally suppressed some lines of the output):


$ sudo apt-get install ia32-libs libgnomeprint2.2-0:i386 libg  nomeprintui2.2-0:i386 libgnomevfs2-bin:i386 libgnome2-0:i386 libgnomeui-0:i386 libjpeg62:i386

S'instaŀlaran els següents paquets extres:
bluez-alsa:i386 esound-common gcc-4.7-base:i386 glib-networking:i386 gstreamer0.10-plugins-base:i386
gstreamer0.10-plugins-good:i386 gstreamer0.10-x:i386 gtk2-engines:i386 gtk2-engines-murrine:i386
[...]
libxt6 libxt6:i386 libxtst6 libxtst6:i386 libxv1 libxv1:i386 libxxf86vm1 libxxf86vm1:i386 mysql-common
odbcinst odbcinst1debian2 odbcinst1debian2:i386 xaw3dg:i386 zlib1g:i386
Paquets suggerits:
murrine-themes:i386 kde-config-gtk-style:i386 libpam-ldap:i386 libpam-winbind:i386 libnss-ldap:i386 nas:i386
[...]
libsasl2-modules-sql:i386 libsasl2-modules-gssapi-mit:i386 libsasl2-modules-gssapi-heimdal:i386 speex:i386
Paquets recomanats:
libtxc-dxtn0:i386 xml-core:i386
Es SUPRIMIRAN els paquets següents:
libgnomecups1.0-1 libgnomeprint2.2-0
S'instaŀlaran els paquets NOUS següents:
bluez-alsa:i386 esound-common gcc-4.7-base:i386 glib-networking:i386 gstreamer0.10-plugins-base:i386
[...]
libxt6:i386 libxtst6:i386 libxv1:i386 libxxf86vm1:i386 mysql-common odbcinst odbcinst1debian2
odbcinst1debian2:i386 xaw3dg:i386 zlib1g:i386
S'actualitzaran els paquets següents:
libasound2 libcupsfilters1 libdbus-1-3 libegl1-mesa libegl1-mesa-drivers libgl1-mesa-dri libgl1-mesa-glx
libglapi-mesa libgnutls26 libgudev-1.0-0 libtiff5 libudev1 libx11-6 libx11-xcb1 libxcb-dri2-0 libxcb-glx0
libxcb-render0 libxcb-shm0 libxcb1 libxcursor1 libxext6 libxfixes3 libxi6 libxinerama1 libxml2 libxp6
libxrandr2 libxrender1 libxt6 libxtst6 libxv1 libxxf86vm1
32 actualitzats, 281 nous a instaŀlar, 2 a suprimir i 79 no actualitzats.
S'ha d'obtenir 93,8 MB d'arxius.
Després d'aquesta operació s'empraran 292 MB d'espai en disc addicional.
Voleu continuar [S/n]?

Once installed this bunch of packages, I try to install notes with –force-depends option, as suggested Charlie in the comments (I can see unsatisfied dependencies on some essential packages, as grep, see below for details) :


$ sudo dpkg --force-depends -i ibm-notes-9.0.i586.deb

S'està preparant per a reemplaçar ibm-notes 9.0-20130309.0942 (emprant ibm-notes-9.0.i586.deb)…
S'està desempaquetant el reemplaçament de ibm-notes…

dpkg: ibm-notes: problemes de dependències, però es configurarà igualment tal i com heu demanat:
ibm-notes depèn de gdb.
ibm-notes depèn de grep.
ibm-notes depèn de libcupsys2.
ibm-notes depèn de libgconf2-4.
ibm-notes depèn de libgnome-desktop-2 | libgnome-desktop-2-7 | libgnome-desktop-2-11 | libgnome-desktop-2-17 | libgnome-desktop-3-2.
ibm-notes depèn de libpam0g.
ibm-notes depèn de libxkbfile1.

S'està configurant ibm-notes (9.0-20130309.0942)…
S'estan processant els activadors per a bamfdaemon…
Rebuilding /usr/share/applications/bamf-2.index...
S'estan processant els activadors per a desktop-file-utils…
S'estan processant els activadors per a gnome-menus…

And it works fine, with no extra karmic libs as with other installations (https://myotragusbalearicus.wordpress.com/2012/01/12/install-lotus-notes-8-5-3-on-ubuntu-11-10-64-bits-with-gnome3/).

Collateral damages

When I try to install other packages, I’m getting errors. When I try to fix them, I get this error (slightly different from Usable Software’s blog):


$ sudo apt-get install gimp
Reading package lists... Done
Building dependency tree
Reading state information... Done
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
gimp : Depends: libgimp2.0 (>= 2.8.4) but it is not going to be installed
Depends: libgimp2.0 (<= 2.8.4-z) but it is not going to be installed
Depends: gimp-data (>= 2.8.4) but it is not going to be installed
Depends: gimp-data (<= 2.8.4-z) but it is not going to be installed
Depends: libbabl-0.1-0 (>= 0.1.10) but it is not going to be installed
Depends: libgegl-0.2-0 (>= 0.2.0) but it is not going to be installed
Depends: libwebkitgtk-1.0-0 (>= 1.3.10) but it is not going to be installed
ibm-notes:i386 : Depends: gdb:i386 but it is not going to be installed
Depends: grep:i386 but it is not going to be installed
Depends: libcupsys2:i386 but it is not installable
Depends: libgconf2-4:i386 but it is not going to be installed
Depends: libgnome-desktop-2:i386 but it is not installable or
libgnome-desktop-2-7:i386 but it is not installable or
libgnome-desktop-2-11:i386 but it is not installable or
libgnome-desktop-2-17:i386 but it is not going to be installed or
libgnome-desktop-3-2:i386 but it is not installable
Depends: libpam0g:i386 but it is not going to be installed
Depends: libxkbfile1:i386 but it is not going to be installed
Recommends: ttf-xfree86-nonfree:i386 but it is not installable
E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution).

And If I try the suggested -f (–fix-broken) option, I get into panic mode:


$ sudo apt-get -f install
Reading package lists... Done
Building dependency tree
Reading state information... Done
Correcting dependencies... Done
The following packages were automatically installed and are no longer required:
gdb:i386 libpython2.7:i386 libpython2.7-minimal:i386 libpython2.7-stdlib:i386 libpython3-stdlib:i386 libpython3.3-minimal:i386
libpython3.3-stdlib:i386 libreadline6:i386
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
gdb:i386 grep:i386 libpython2.7:i386 libpython2.7-minimal:i386 libpython2.7-stdlib:i386 libpython3-stdlib:i386
libpython3.3-minimal:i386 libpython3.3-stdlib:i386 libreadline6:i386
Suggested packages:
gdb-doc:i386 gdbserver:i386
The following packages will be REMOVED:
gdb grep ibm-notes:i386
The following NEW packages will be installed:
gdb:i386 grep:i386 libpython2.7:i386 libpython2.7-minimal:i386 libpython2.7-stdlib:i386 libpython3-stdlib:i386
libpython3.3-minimal:i386 libpython3.3-stdlib:i386 libreadline6:i386
WARNING: The following essential packages will be removed.
This should NOT be done unless you know exactly what you are doing!
grep
0 upgraded, 9 newly installed, 3 to remove and 87 not upgraded.
Need to get 9956 kB of archives.
After this operation, 829 MB disk space will be freed.
You are about to do something potentially harmful.
To continue type in the phrase 'Yes, do as I say!'
?] no
Abort.

So, I decided to remove and purge lotus-notes package and fix packages again (now it’s ok, 0 packages to remove):


$ sudo dpkg --purge ibm-notes:i386
(Reading database ... 181976 files and directories currently installed.)
Removing ibm-notes ...
Purging configuration files for ibm-notes ...
Processing triggers for bamfdaemon ...
Rebuilding /usr/share/applications/bamf-2.index...
Processing triggers for desktop-file-utils ...
Processing triggers for gnome-menus ...

$ sudo apt-get -f install
Reading package lists... Done
Building dependency tree
Reading state information... Done
0 upgraded, 0 newly installed, 0 to remove and 87 not upgraded.

Modify the deb package and install again

For me this has worked. So If you have the same scenario as me (Lotus Notes 9.0 and Ubuntu 13.04 64 bits), it’s preferable to modify the deb package and install with no dependencies (see the Collateral damages section). Maybe you’ll have to install some dependencies if it does not work for you (I already installed it at the begginning of the post, but maybe some of them are unnecessary).

First of all, modify the dependencies of the ibm-notes-9.0.i586.deb package (see http://usablesoftware.wordpress.com/2012/05/04/install-lotus-notes-8-5-3-on-ubuntu-12-04-64bit/ for instructions on unpacking and repacking the deb package):

  • Extract the deb file
  • Go to DEBIAN folder
  • Edit the control file
  • Clear the pre-depends and depends lines:
Pre-Depends: libgnomeprint2.2-0, libgnomeprintui2.2-0
Depends: gdb, coreutils, unzip, bash, procps, grep, sed, libart-2.0-2, libasound2, libatk1.0-0, libbonobo2-0, libbonoboui2-0, libc6, libcupsys2, libfontconfig1, libfreetype6, libgcc1, libgconf2-4, libgtk2.0-0, libglib2.0-0, libgnome2-0, libgnomecanvas2-0, libgnome-desktop-2 | libgnome-desktop-2-7 | libgnome-desktop-2-11 | libgnome-desktop-2-17 | libgnome-desktop-3-2, libgnomeui-0, libgnomevfs2-0, libglib2.0-0, libice6, libjpeg62, liborbit2, libpam0g, libpango1.0-0, libpng12-0, libpopt0, libsm6, libstdc++6, libx11-6, libxcursor1, libxext6, libxft2, libxi6, libxkbfile1, libxml2, libxp6, libxrender1, libxss1, libxt6, libxtst6, libz1

Change it for:

Pre-Depends:
Depends:
  • Now go to the root folder again rename the original deb file.
  • Generate the deb package again (install the package dpkg-dev if you don’t have the dpkg-deb command) and reinstall it(now you don’t need to force dependencies):

$ dpkg-deb -b ibm-notes-9.0.i586
dpkg-deb: building package `ibm-notes' in `ibm-notes-9.0.i586.deb'.
$ chmod +x ibm-notes-9.0.i586.deb

$ sudo dpkg -i ibm-notes-9.0.i586.deb
Selecting previously unselected package ibm-notes.
(Reading database ... 161789 files and directories currently installed.)
Unpacking ibm-notes (from ibm-notes-9.0.i586.deb) ...
Setting up ibm-notes (9.0-20130309.0942) ...
Processing triggers for bamfdaemon ...
Rebuilding /usr/share/applications/bamf-2.index...
Processing triggers for desktop-file-utils ...
Processing triggers for gnome-menus ...

And now you can run Lotus Notes 9 Social Edition:

Lotus Notes 9 Social Edition

Lotus Notes 9

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

April 18, 2013 Leave a comment

One of our developers has problems with a select of different tables:


SELECT ROWID,
 TMP_TABLE_ALIAS.*
FROM
 (select a.*,
 B.*,
 (SELECT MAX(TMP_CODEMP)
 FROM ATE_TABLE
 WHERE TMP_IDTRE = TRE_ID
 and TMP_FI is null
 ) AS TRE_CODEMP,
 (SELECT MAX(REV_DATA) FROM AR_TABLE WHERE REV_ID = TRE_ID
 ) AS TRE_DATREV
 FROM AT_TABLE a,
 AP_TABLE B
 WHERE PER_ID = TRE_ID (+)
 ) TMP_TABLE_ALIAS;

This statement works on the development environment, but in the production environment it fails with:


ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table 01445

The database versions is the same in both environments: Oracle 10g Release 2 (10.2.0.5).

After reviewing the tables in the production environment, I noticed that one of the tables has no primary key, and that caused the ORA-01445. Even with a unique index the select fails, it’s mandatory to declare primary keys for the tables when they are involved in joins.

Compress a folder with tar

February 15, 2013 1 comment

To archive and compress a folder with tar use this command:


$ tar -czvf folder.tar.gz folder

Be careful with the parameters order: tar -czvf is not the same as tar -cvfz. The fist one is ok, but the second one ends with an error:

$ tar -cvfz folder.tar.gz folder
tar: folder.tar.gz: Cannot stat: No such file or directory
folder/
folder/file1.txt
folder/file2.txt
folder/file3.txt
tar: Exiting with failure status due to previous errors

An ls will show a z file. The reason is that the -f parameter takes the next argument as the filename to archive, thus the z file in our folder. So you have to use the tar -czvf syntax.

Categories: linux Tags: , ,
Follow

Get every new post delivered to your Inbox.

Join 29 other followers