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

If you are loading large volumes of data and you have archive logging turned on sometimes you will run into the ORA-00257 error.  This error occurs when the upper limit of the archive log dedicated space is hit.  You need to clean up some/all of the logs in order to set the database free to work again.

A relatively straightforward way to do this is to use the rman tool:

. oraenv (set your sid/oracle_home)
rman target /
crosscheck archivelog all;  (you should see all the logs)
delete archivelog all; (removes all the logs)
crosscheck archivelog all; (you should see no logs)
exit;

Now you should be able to access the database again without the ORA-00257 error.  Sometimes it maybe necessary to turn the archive logging off during these large loads, see the post on turning off archive logging.

Note: When in ARCHIVELOG mode, the database will make copies of all online redo logs after they are filled. These copies are called archived redo logs. The archived redo logs are created via Oracle’s Archive Process (ARCH). This process copies the archived redo log files to one or more archive log destination directories. In order for this process to be used additional setup is required primarily the redo log destination directories. In non-production environments there can be some major down sides to running in ARCHIVELOG mode.  The most common error you will encounter with Archive Logging turned on is the ORA-00257 error.  It should also be noted that absolutely nothing will process within the system until the archive logs are freed.  This can be a serious issue to an environment when doing maintenance, upgrades, or large data loads.  In those scenarios it is recommended to make backups of the environment and turn archive logging off for the duration of the high traffic work, then again backup and turn archive logging back on if needed.

Sometimes it is helpful to be able to rename a table in PeopleSoft. I find when dealing with duplicates in tables during upgrades, you can rename a table to a temporary table and then re-create the table in question and then simply do an insert into the new table from the temporary table using a select distinct statement.

SQL SERVER:
sp_rename ‘TABLENAME’, ‘NEWTABLENAME’

ORACLE:
alter table tablename rename to newtablename

This error is just one that drives you crazy. PeopleSoft has been making the move from a 32bit application to a 64bit application. Almost everywhere you will find the that the application runs in 64bit mode, except for in windows. The client application is still a 32-bit client. So if you are using Oracle for example, you need to ensure that the Oracle client software on the windows machine is 32-bit. This is also true with the tuxedo service, when downloading patches make sure that you download the 32-bit patches.

So if you encounter this error, make sure that the 32-bit libraries are installed and are being referred to for the client. DBBIN variable specifically, and if necessary you may need to add the binary path to the add to path variable in the configuration.

When working in one database you can make reference to data in another database by creating a database link.

To create an Oracle database link, the quickest way is to just create a public database link using a user id in the other database that has permissions to reference the data you need. {LINKNAME} = the name for the link you want to create, {LINKUSER} = the user on the remote database that has access, and {LINKUSERPASSWORD} is that users password.  {TNSNAMES-DB-ENTRY} is the database entry name from the TNSNAMES.ora file.

create public database link {LINKNAME} connect to {LINKUSER} identified by {LINKUSERPASSWORD using ‘{TNSNAMES-DB-ENTRY}’;

To Reference the data from the linked database, simply add the link identifier to the table you are selecting – for example:

select * from remotetable@{LINKNAME};

To Remove the database link:

drop public database link {LINKNAME};

Outer joins is just one of the items that is required from time to time, but since I typically never recommend using them with PeopleSoft, I always forget the syntax.  Since my days go back to Oracle 7, I remember the messy (+) notation and so I always end up looking it up.  The good news is Oracle superseded the (+) notation by going to ISO 99 standard as of Oracle 9i, however it still supports (+) notations.

There are three types of joins you can do, left outer, right outer and full outer join. In a left outer join, all the records of the table listed on the left will be returned with the combined results of the join to the table listed on the right, however, if the right listed table does not have a matching row, NULLS will be returned for the columns of the right listed table.  The right outer join does the same thing just in reverse, whereas the full outer join basically does a UNION of the left & right outer joins.

Some database platforms do not support full outer joins so you can basically do it by doing a left outer join combined with with a right outer join using the union statement.

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A LEFT OUTER JOIN DEPARTMENT D ON A.DEPTID = D.DEPTID;

If you want to do the Oracle (+) syntax:

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A, DEPARTMENT D WHERE A.DEPTID = E.DEPTID (+);