Wednesday, January 25, 2012

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 >

No comments: