Wednesday, January 25, 2012


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 - Production on Wed Jan 25 10:49:21 2012
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
ORA-00257: archiver error. Connect internal only, until freed.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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 /
SQL > show user

No comments: