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

When PeopleSoft runs sql queries and it runs longer then the service timeout for the service operation, the service will terminate its connection to that query, however, it may stay running on the database. This can result in long running queries on the database draining valuable CPU/memory from the database server. Oracle has the ability to check for “dead” connections and terminate them.

Adding the parameter sqlnet.expire_timeout to the sqlnet.ora file that is housed on your database server ($ORACLE_HOME/network/admin directory), will send a probe to verify that connections are active versus dead due to some client termination. If a connection is determined to be terminated or no longer in use, an error is returned which will cause the server process to exit. The parameter is specified in a time interval of minutes, and PeopleSoft recommends a 10 minute interval.

sqlnet.expire_timeout=10

Some considerations to this are you must be using TPC/IPC connections for this to work, so if you are using the parameter UseLocalOracleDB=1 in your configurations you are not using TPC/IPC. Most client use TPC/IPC so this should not be an issue. This will also put some additional overhead on the server to execute the probes, however, if you have large/long running sql queries running against the database, the performance hit should be offset by the gains in killing the unnecessary (dead) connection.

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};

In PeopleSoft you can use datamover to add SQL security.  The reason for this is even though you log into PeopleSoft as a specific user once the user is verified it actually uses the user that is associated with the psaccessprfl access profile.  This means that in oracle typically the user sysadm is the user behind the access profile, and since the sysadm user is typically built using the PSADMIN role profile, you have priviledges to run a lot of commands from datamover.

A good example of this is when you build a table in PeopleSoft but you can’t see it in SQL because the user account you have in Oracle does not access to the new table.  So in order to grant access to this you can do a couple of different statements.  The simplest is to grant select access on the new table to a specific SQL user account:

grant select on new_table to sql_user;

Often times there are roles setup that are assigned to a user, if you do a:

select * from dba_roles;

You can see all the roles that are on the system.  Typically there is a role that will be setup that will allow “select” access and another role that is setup to allow “select, update, delete, insert”, sometimes referred to as SUDI access.  So lets say for example there is a role called SUDI_ALL_TABS, you can issue the statement in datamover:

grant select, update, delete, insert on new_table to SUDI_ALL_TABS;

Note: The new_table needs to have the ps_ qualifier on it, as this is not a typical function of datamover and therefore needs the fully qualified table name.