Wednesday, June 17, 2015

ORA-32021: parameter value longer than 255 characters (log_archive_dest)


Problem:
alter system set log_archive_dest_2='service="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stagedb.test.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db2.test.com)(SERVER=DEDICATED)))", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="db2",  valid_for=(all_logfiles,primary_role)' scope=both;

ORA-32021: parameter value longer than 255 characters

Solution:
Splitting with two ' ', ' '
alter system set log_archive_dest_2='service="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stagedb.test.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=db2.test.com)(SERVER=DEDICATED)))"','LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="db2",  valid_for=(all_logfiles,primary_role)' scope=both;

Monday, May 24, 2010

Tip#22 Fix for ORA-01882

ORA-01882: timezone region %s not found

Recently we got this error while running via TOAD

select * from dba_scheduler_jobs;

The error message itself turns out not very informative.

01882, 00000, "timezone region %s not found"
// *Cause: The specified region name was not found.
// *Action: Please contact Oracle Customer Support.

In short, the error is because there are 7 timezone region IDs changed from version 3 and above. If you have old Timezone data from Version 2 that using one of these IDs the error raises.

In our case, server had time zone files were already upgraded so running same query on the DB server was working fine. So to fix it we had fix it on the client side,

1) Download patch 5731535 for a 10.2.0.X client ( 10.2.0.1 to 10.2.0.3)
2) Copy the 2 .dat files located at Client_patch\5731535\files\oracore\zoneinfo and the readme.txt file into the %ORACLE_HOME%\oracore\zoneinfo directory on your oracle client.
3) restart the TOAD