ORA-00257

I restored a database to a remote site today as part of a test.

 

In the screen shot below, I was logged on to the UNIX server as user oracle. I then tried to connect to the restored database using OS authentication. This failed so I connected as SYS instead.

 

I investigated the cause in a separate session (not shown here). For some reason, the archive area had incorrect permissions (it was owned by root, not oracle) so the redo logs could not be copied there. I changed the permissions on the archive directory so that oracle could write to it (this is not shown here either).

 

Then I was able to switch log file groups and connect to the database as ORACLE.

 

Note the error message, which says: Connect internal only. This is left over from Oracle 8 when you could use the command connect internal to login to the database as SYS:

 

UNIX > sqlplus /

 

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jan 25 10:49:21 2012

 

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

 

ERROR:

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

 

Enter user-name: / as sysdba

 

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 >
  

At this point I changed the ownership of the archive area in a separate UNIX session using chown. Then I returned to this SQL session and continued as shown below:
  

SQL > alter system switch logfile;

 

System altered.

 

SQL > alter system switch logfile;

 

System altered.

 

SQL > conn /

Connected.

SQL > show user

USER is "ORACLE"

SQL >

Another Example Using Intersect

In the course of helping a colleague today, I had to find a database user who had 2 roles assigned. You can do this with a couple of subqueries but it is easier with an INTERSECT. I was logged in as user ORACLE when I did this and I was surprised to see ORACLE in the list alongside USER_2. It seems that, if you create a role, it is automatically assigned to you. I did not know this. You can see this demonstrated at the end of the example. I ran it on an Oracle 9 database but the same thing happens in Oracle 10 and 11:

 

SQL> conn /

Connected.

SQL> show user

USER is "ORACLE"

SQL> create role role_a

  2  /

 

Role created.

 

SQL> create role role_b

  2  /

 

Role created.

 

SQL> grant role_a to user_1

  2  identified by user_1

  3  /

 

Grant succeeded.

 

SQL> grant role_a, role_b to user_2

  2  identified by user_2

  3  /

 

Grant succeeded.

 

SQL> grant role_b to user_3

  2  identified by user_3

  3  /

 

Grant succeeded.

 

SQL> select distinct grantee from dba_role_privs

  2  where grantee in

  3  (select grantee from dba_role_privs

  4   where granted_role = 'ROLE_A')

  5  and grantee in

  6  (select grantee from dba_role_privs

  7   where granted_role = 'ROLE_B')

  8  /

 

GRANTEE

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

ORACLE

USER_2

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'ROLE_A'

  3  intersect

  4  select grantee from dba_role_privs

  5  where granted_role = 'ROLE_B'

  6  /

 

GRANTEE

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

ORACLE

USER_2

 

SQL> create role blah

  2  /

 

Role created.

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'BLAH'

  3  /

 

GRANTEE

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

ORACLE

 

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 = ""

remote_os_authent

The Oracle RDBMS used to have a parameter called remote_os_authent. This specified whether or not you could connect to an instance remotely using OS authentication. Setting it to true was a security risk, especially if you used OS authentication for database users which had the DBA role. For example, you might have an externally identified user in your database called ORACLE and grant the DBA role to that user. A malicious user with admin rights on a remote machine could create a user called oracle on that machine and use it to connect to your database as an administrator without providing a password. In version 11, the Oracle RDBMS deprecated this parameter but have retained it (for now) for backward compatibility. The example below illustrates this. I ran it on a UNIX server as a UNIX user called oracle. First I connected to the database as SYS and set remote_os_authent to true in the server parameter file:

 

SQL> conn / as sysdba

Connected.

SQL> alter system set

  2  remote_os_authent = true

  3  scope = spfile

  4  /

 

System altered.

 

SQL>

 

Then I bounced the database. The Oracle RDBMS displayed an error message when it saw the deprecated parameter:

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area  158703616 bytes

Fixed Size                  2086736 bytes

Variable Size              83888304 bytes

Database Buffers           67108864 bytes

Redo Buffers                5619712 bytes

Database mounted.

Database opened.

SQL>

 

I reconnected to the database remotely and reset remote_os_authent in the server parameter file:

 

SQL> conn /@test11

Connected.

SQL> show user

USER is "ORACLE"

SQL> alter system reset remote_os_authent

  2  scope = spfile

  3  /

 

System altered.

 

SQL>

 

Then I bounced the database again. This time there was no error message:

 

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  158703616 bytes

Fixed Size                  2086736 bytes

Variable Size              88082608 bytes

Database Buffers           62914560 bytes

Redo Buffers                5619712 bytes

Database mounted.

Database opened.

SQL>

 

Changing the remote_os_authent parameter stopped the remote connection working:

 

SQL> conn /@test11

ERROR:

ORA-01017: invalid username/password; logon denied

 

Warning: You are no longer connected to ORACLE.

SQL>