EXP-00008 and ORA-00904

A colleague tried to use an Oracle 10 Windows Vista client to export a schema from an Oracle 11.1.0.6.0 database. The export failed and he came to me for help. I have reproduced the error below in a Command Prompt session:

C:\Users\j0294094>exp parfile=paramfile

 

Export: Release 10.2.0.4.0 - Production on Thu Dec 27 16:06:55 2012

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit

Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P15 character set (possible charset conversion)

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user CBS

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user CBS

About to export CBS's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export CBS's tables via Conventional Path ...

Etc

Etc

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

EXP-00008: ORACLE error 904 encountered

ORA-00904: "OLEVEL": invalid identifier

EXP-00000: Export terminated unsuccessfully

 

C:\Users\j0294094>

I believe he had this problem because the export utility was on a lower version than the database. One possible solution is to run the export on the UNIX server hosting the database, where the client and the database versions are the same. As you can see below, this runs successfully. However, I’m not sure if this is the best way forwards as Oracle no longer supports export for general use. I’m going to suggest to the developer that he considers using datapump instead:

Solaris > exp parfile=paramfile

 

Export: Release 11.1.0.6.0 - Production on Thu Dec 27 16:34:57 2012

 

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

 

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P15 character set (possible charset conversion)

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user CBS

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user CBS

About to export CBS's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export CBS's tables via Conventional Path ...

Etc

Etc

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

Solaris >

How to Move System Tablespace Datafiles

In an earlier post I explained how to move datafiles. The method I used then is NOT suitable for datafiles belonging to the system, undo or temporary tablespaces as they cannot be taken offline. I said I would publish a post about this in due course and here it is.

I created a database using dbca recently. I was in a hurry and forgot to put a slash at the end of the directory name where I wanted to put the datafiles so they ended up like this:

SQL> l
  1  select file_id, file_name
  2  from dba_data_files
  3* order by 1
SQL> /

   FILE_ID FILE_NAME
---------- ------------------------------------------
         1 /agasprd/qcsprod/qcs_systemsystem01.dbf
         2 /agasprd/qcsprod/qcs_systemsysaux01.dbf
         3 /agasprd/qcsprod/qcs_systemundotbs01.dbf
         4 /agasprd/qcsprod/qcs_systemusers01.dbf
         5 /agasprd/qcsprod/qcs_data/qc_data_a.dbf

SQL>

Files 1 to 4 were in the wrong place. File 5, which was created afterwards, is OK. I closed the database and backed it up. Then I renamed the files in UNIX:

UNIX > pwd
/agasprd/qcsprod
UNIX > mv qcs_systemsystem01.dbf qcs_system/system01.dbf
UNIX > mv qcs_systemsysaux01.dbf qcs_system/sysaux01.dbf
UNIX > mv qcs_systemundotbs01.dbf qcs_system/undotbs01.dbf
UNIX > mv qcs_systemusers01.dbf qcs_system/users01.dbf
UNIX >

I mounted the database:

UNIX > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 6 14:12:20 2012

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area  522092544 bytes
Fixed Size                  2149672 bytes
Variable Size             390075096 bytes
Database Buffers          121634816 bytes
Redo Buffers                8232960 bytes
Database mounted.

SQL>

... and renamed the files in the database 1 by 1:

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemsystem01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/system01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemsysaux01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/sysaux01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemundotbs01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/undotbs01.dbf'
SQL> /

Database altered.

SQL>

SQL> l
  1  alter database rename file
  2  '/agasprd/qcsprod/qcs_systemusers01.dbf' to
  3* '/agasprd/qcsprod/qcs_system/users01.dbf'
SQL> /

Database altered.

SQL>

Finally, I opened the database:

SQL> alter database open;

Database altered.

SQL> select file_id, file_name
  2  from dba_data_files
  3  order by 1;

   FILE_ID FILE_NAME
---------- ---------------------------------------------
        1 /agasprd/qcsprod/qcs_system/system01.dbf
        2 /agasprd/qcsprod/qcs_system/sysaux01.dbf
        3 /agasprd/qcsprod/qcs_system/undotbs01.dbf
        4 /agasprd/qcsprod/qcs_system/users01.dbf
        5 /agasprd/qcsprod/qcs_data/qc_data_a.dbf

SQL>

The tempfile was in the wrong place too but I dealt with that by recreating the temporary tablespace.

ORA-01090

The first screen print below shows 3 sessions on the same UNIX machine.

 

In the first session, I connected to ORCL as an externally identified user at 14:10:46.

 

In the second session, I connected to ORCL as SYS at 14:12:07 and issued a shutdown command. There are several ways to do this e.g. shutdown normal, shutdown transactional, shutdown immediate, shutdown abort and shutdown timeout. The default, if none of these options are specified, is shutdown normal. This causes the shutdown command to wait until all other sessions have logged out and this is what happened here.

 

Once you have run a shutdown command, Oracle does not allow other users to connect to the database. In the third session below, I tried to connect to database ORCL as an externally identified user at 14:14:13. This connection failed with an ORA-01090. As usual, click on the images to enlarge them and bring them into focus:

   

The second screen print below shows sessions 1 and 2 above a little later. The externally identified user logged out of the database in session 1 at 14:18:51. This allowed the shutdown command in session 2 to complete:

 

Swapping

You can see if any swapping has taken place since a UNIX server was started by running vmstat –s. If you see values other than zero on the swap ins or swap outs lines, then swapping has occurred:

Solaris > vmstat -s

     4123 swap ins

      118 swap outs

    12369 pages swapped in

    35254 pages swapped out

93593074720 total address trans. faults taken

16175338065 page ins

67075526 page outs

167117261315 pages paged in

153673896 pages paged out

157281194355 total reclaims

157268182842 reclaims from free list

        0 micro (hat) faults

93593074720 minor (as) faults

12433082155 major faults

22790008329 copy-on-write faults

30659757855 zero fill page faults

228888590 pages examined by the clock daemon

      112 revolutions of the clock hand

146623357 pages freed by the clock daemon

251218086 forks

  8106297 vforks

197058709 execs

994409866209 cpu context switches

584512421794 device interrupts

135390836607 traps

2614749602197 system calls

328390806433 total name lookups (cache hits 99%)

7844654930 user   cpu

8057496148 system cpu

25835471029 idle   cpu

        0 wait   cpu

Solaris >

DB time from V$SESSTAT

This was tested on Oracle 11.2. The DB time entry in V$MYSTAT (or V$SESSTAT) just seems to record the amount of time a session spends doing something in the database, irrespective of the amount of CPU time used. First reconnect to the database to zeroise the figures:

SQL> conn /

Connected.

SQL> select name, m.value/100

  2  from v$mystat m, v$sysstat s

  3  where m.statistic# = s.statistic#

  4  and name in

  5  ('DB time',

  6   'CPU used by this session')

  7  /

NAME                                M.VALUE/100

----------------------------------- -----------

CPU used by this session                    .01

DB time                                       0

SQL>

If you sleep in the OS for a few seconds:

SQL> host sleep 5

... this does not increment DB time or CPU time by much:

SQL> select name, m.value/100

  2  from v$mystat m, v$sysstat s

  3  where m.statistic# = s.statistic#

  4  and name in

  5  ('DB time',

  6   'CPU used by this session')

  7  /

NAME                                M.VALUE/100

----------------------------------- -----------

CPU used by this session                    .02

DB time                                     .02

SQL>

However, if you sleep in the database:

SQL> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

SQL>

... CPU time hardly changes but DB time goes up by the full 5 seconds:

SQL> select name, m.value/100

  2  from v$mystat m, v$sysstat s

  3  where m.statistic# = s.statistic#

  4  and name in

  5  ('DB time',

  6   'CPU used by this session')

  7  /

NAME                                M.VALUE/100

----------------------------------- -----------

CPU used by this session                    .04

DB time                                    5.05

SQL>

DBMS_FILE_TRANSFER

I read about this command, which allows you to copy OS files in SQL*Plus, and decided to try it out on Oracle 11.2. First I created a file:

 

Solaris > pwd

/export/home/oracle/andrew/dir1

Solaris > echo "Andrew was here" > file1

Solaris >

 

Then I tried to use the command but it failed:

 

SQL> create or replace directory source_dir as

  2  '/export/home/oracle/andrew/dir1'

  3  /

 

Directory created.

 

SQL> create or replace directory target_dir as

  2  '/export/home/oracle/andrew/dir2'

  3  /

 

Directory created.

 

SQL> begin

  2  dbms_file_transfer.copy_file(

  3  source_directory_object=>'source_dir',

  4  source_file_name=>'file1',

  5  destination_directory_object=>'target_dir',

  6  destination_file_name=>'file2');

  7  end;

  8  /

begin

*

ERROR at line 1:

ORA-19505: failed to identify file

"/export/home/oracle/andrew/dir1/file1"

ORA-27046: file size is not a multiple of logical

block size

Additional information: 1

ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84

ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193

ORA-06512: at line 2

 

SQL>

 

I found some Oracle 10.2 documentation for the command and it said:

The size of the copied file must be a multiple of 512 bytes. I checked the size of my test file but it was only 16 bytes:

 

Solaris > pwd

/export/home/oracle/andrew/dir1

Solaris > ls -l

total 2

-rw-r--r--   1 oracle   dba           16 Aug 13 12:17 file1

Solaris >

 

I recreated the file with a size of 512 bytes:

 

Solaris > pwd

/export/home/oracle/andrew/dir1

Solaris > dd if=/dev/zero of=file1 bs=1 count=512

512+0 records in

512+0 records out

Solaris > ls -l

total 2

-rw-r--r--   1 oracle   dba          512 Aug 13 12:28 file1

Solaris >

 

When I tried the command again, it worked:

 

SQL> begin

  2  dbms_file_transfer.copy_file(

  3  source_directory_object=>'source_dir',

  4  source_file_name=>'file1',

  5  destination_directory_object=>'target_dir',

  6  destination_file_name=>'file2');

  7  end;

  8  /

PL/SQL procedure successfully completed.

SQL>

 

... and UNIX could see no difference between the input and output files:

 

Solaris > pwd

/export/home/oracle/andrew/dir1

Solaris > diff file1 \

> /export/home/oracle/andrew/dir2/file2

Solaris >

Reply to: Reply to Andrew Reid

Send

Error Handling in SQL*Plus

If you want to know if there has been an error in a SQL*Plus session, you need to do this explicitly. Here is a UNIX shell script:

ORACLE 11 > cat error1.ksh

sqlplus / << failure

select to_number('A') from dual

/

exit

failure

echo "Return code after failure = $?"

ORACLE 11 >

When you run it, there is a failure in the SQL*Plus session but UNIX is not aware of this:

ORACLE 11 > ./error1.ksh

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 14:41:54 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>   2  select to_number('A') from dual

                 *

ERROR at line 1:

ORA-01722: invalid number

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Return code after failure = 0

ORACLE 11 >

If you wish to tell UNIX there has been an error, you should add whenever sqlerror exit failure to the SQL*Plus session:

ORACLE 11 > cat error2.ksh

sqlplus / << failure

whenever sqlerror exit failure

select to_number('A') from dual

/

exit

failure

echo "Return code after failure = $?"

ORACLE 11 >

Then the return code will be non zero so UNIX will know about the error:

ORACLE 11 > ./error2.ksh

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 14:45:44 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL>   2  select to_number('A') from dual

                 *

ERROR at line 1:

ORA-01722: invalid number

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Return code after failure = 1

ORACLE 11 >

SQL*Plus host Command

The SQL*Plus host command allows you to run operating system commands from within a SQL*Plus session:

SQL> host date
Wed Feb  8 10:56:34 GMT 2012

SQL>

Alternatively, you can use an exclamation mark:

SQL> ! echo "Andrew was here"
Andrew was here

SQL>

... and, if you do, it is not necessary to leave a space between the exclamation mark and the OS command:

SQL> !pwd
/home/oracle/examples/host

SQL>

os_authent_prefix

In Oracle 10, the default value for os_authent_prefix was ops$:

 

SQL> l

  1  select value, isdefault

  2  from v$parameter

  3* where name = 'os_authent_prefix'

SQL> /

 

VALUE      ISDEFAULT

---------- ---------

ops$       TRUE

 

SQL>

 

So, if you created an ops$oracle user with a password:

 

SQL> grant dba to ops$oracle

  2  identified by andrew

  3  /

 

Grant succeeded.

 

SQL>

 

And you were logged onto the server hosting your database as UNIX user oracle:

 

TEST10 > whoami

oracle

TEST10 >

 

... you could logon to the database externally:

 

TEST10 > sqlplus /

 

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jan 3 14:41:28 2012

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

 

SQL>

 

... or by using the password, which may not have been what you intended:

 

SQL> conn ops$oracle/andrew

Connected.

SQL>

 

To stop this happening, you could add the following line to your database’s parameter file:

 

os_authent_prefix = ""