Category: Databases


Upgrading Sybase ASE 12.5.4 -> 15.0.3 esd 4 & bug # 643188

Filed Under: ASE, Databases, Sybase by Jason L Froebe — 3 Comments
November 26, 2010

Sybase

We’ve been upgrading a number of Sybase ASE databases from the end-of-life’d 12.5.x to v15.0.3 esd 4 the last few weeks.   My coworker (Degang He) ran into an interesting error when the upgrade process was failing on sysprocedures.

Database ‘my_db’: beginning upgrade step [ID 26]: alter table (table sysprocedures)
Command has been aborted.

Msg 3461, Level 20, State 1:
Server ‘my_server’, Line 1:
Database ‘my_db’: upgrade could not install required upgrade item ’26′. Please refer to previous error messages to determine the problem. Fix the problem, then try again.

Msg 3451, Level 20, State 1:
Server ‘my_server’, Line 1:
Database ‘my_db’: upgrade has failed for this database. Please refer to previous messages for the cause of the failure, correct the problem and try again.

Msg 3454, Level 20, State 1:
Server ‘my_server’, Line 1:

Database ‘my_db’: ASE could not completely upgrade this database; upgrade item 1501 could not be installed.

We looked on Sybase’s Solved Cases and Google but didn’t find anything that would really fit the problem.  So we called up Sybase Technical Support.  Somdev Sharma of Sybase was able to determine that this was a bug in the ASE 15x codeline and provided a workaround:

CR #643188:

Title Online database failed with 3461 error during upgrade , ‘max parallel degree’ >1

Workaround:

Before online the database, disable parallel sort via sp_configure ‘max parallel degree’,1

Sanitized:

Online database failed with 3461, 3451 and 3454 errors during upgrade sysprocedures table (upgrade  item ’26′). If ‘max parallel degree’ > 1, then please disable parallel sort with sp_configure ‘max parallel degree’, 1.

After disabling parallel sort, the database was upgraded successfully.

Tags: , , , , , ,
Comment

How to avoid most reorgs of DOL tables in Sybase ASE (Jeff Tallman)

Filed Under: ASE, Databases, Sybase by Jason L Froebe — 4 Comments
May 14, 2010

On the sybase-l mailing list,

Sybase

Jeff Tallman replied to a question on how to avoid unnecessary reorgs. He graciously agreed to let me post his response here :)

You can avoid most (if not all) of the reorgs by doing:

  1. setting the exp_row_size to something that covers about 90% of the space each row takes up
  2. changing enable housekeeper GC to a 4 or 5

Both of these are a *MUST* do for DOL (datapages or datarows). See manuals on ‘enable housekeeper GC’ for correct setting of 4 or 5 (refers to whether deletes are batch or OLTP).

You can also watch for housekeeper overflows in monEngine/monOpenObjectActivity……and if you see any HKGC pending – wait a few before shutting down.

Jeff wrote up an excellent article, Locking Redux – APL vs. DOL and Tuning, that goes into detail why this is the case.

Tags: , , , , , , , , , , ,
Comment

How to change the default NLS_DATE_FORMAT (Date format) in Oracle 10g/11g

Filed Under: Databases, How To, Oracle by Jason L Froebe — 1 Comment
May 6, 2010

It is really really easy to change the default NLS_DATE_FORMAT setting but to be honest, you should set it at a session level IMHO.

We basically just need to run “ALTER SYSTEM SET NLS_DATE_FORMAT=’YYYY-MM-DD’ SCOPE=SPFILE” as a user with sysdba privileges. If you started the Oracle instance without a spfile (it should be located at $ORACLE_HOME/dbs/spfile[instance name].ora), you will receive the ORA-32001 error.

SQL> ALTER SYSTEM SET NLS_DATE_FORMAT=‘YYYY-MM-DD’ SCOPE=SPFILE;
ALTER SYSTEM SET NLS_DATE_FORMAT=‘YYYY-MM-DD’ SCOPE=SPFILE
*
ERROR at line 1:
ORA-32001: WRITE TO SPFILE requested but no SPFILE specified at startup

Just create a new spfile, restart:

SQL> SELECT INSTANCE_NAME FROM v$instance;

INSTANCE_NAME
—————-
UAT2

SQL> CREATE spfile=‘/oracle/10g/dbs/spfileUAT2.ora’ FROM pfile=‘/oracle/10g/dbs/initUAT2.ora’;

*restart*

SQL> SELECT INSTANCE_NAME FROM v$instance;

INSTANCE_NAME
—————-
UAT2

SQL> ALTER SYSTEM SET NLS_DATE_FORMAT=‘YYYY-MM-DD’ SCOPE=SPFILE;

System altered.

*restart*

SQL> SELECT value FROM v$nls_parameters WHERE parameter =‘NLS_DATE_FORMAT’;

VALUE
—————————————————————-
YYYY-MM-DD

That’s it. :)

Tags: , , , , , , , , , , ,
Comment

VMware 2.0.2 running on Win2k3 -> VMware ESX 4.0 .. done :)

Filed Under: ASE, Databases, OS, Sybase, virtualization, VMware, Windows by Jason L Froebe — Leave a comment
April 24, 2010

I was able to move a VMware Server 2.0 (v7) vm to VMware ESX..  it was a *live* copy where I performed a Windows Volume Shadow copy of the vm files.  Everything worked for the most part but because the database, Sybase ASE 15.0.3, was running when the shadow copy was made, we had corruption in one database.  Restore from backup and all is good.

Now we need to get an updated license file from Sybase as the NIC mac address has changed..   You can *not* use the mac address from the VMware Server on ESX.  grr.

Twenty hours for the volume shadow copy to complete plus another 12 hours to scp the files to the esx box (esx console access is sloooow).   Keep in mind that the host VMware Server box was rebooting itself randomly so I really couldn’t leave it alone.  Then 3 hours to convert/clone the vmdk files and 2 hours to correct the database…  I’m tired.

It turned out to be an issue with allocating 3.75GB to a VM that was causing the rebooting.  Dropping it to 2 GB resolved the rebooting… who knew?  Nothing in Google and VMware Support wasn’t able to find anything on their side.

Tags: , , , , , , , , , , ,
Comment

How to: How to copy an Oracle tablespace across platforms – quick and dirty ;-)

Filed Under: Databases, How To, Oracle by Jason L Froebe — Leave a comment
April 5, 2010
  • On the source machine:
    • Set the tablespace(s) into read only mode
      • SQL> Alter tablespace <tablespace> read only
  • Export the tablespace meta data using export
    • % expdp system/<password> DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES = <list of tablespaces separated by commas> TRANSPORT_FULL_CHECK=Y
    • If the dumpdir is not set up, you will get an ‘invalid’ directory error:
      • SQL> CREATE DIRECTORY dmpdir as ‘/somedir’;
      • SQL> GRANT read,write on DIRECTORY to system;
  • Export the data converting on the fly:
    • Determine the platform name for the destination machine:
      • SQL> SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM
    • % rman TARGET /
      • RMAN> CONVERT TABLESPACE <list of tablespaces separated by commas> to PLATFORM ‘<platform name from previous step>’  FORMAT=’/somedir/%U’;
  • Put the tablespaces into read/write mode
    • SQL> alter tablespace <tablespace> READ WRITE;
    • Transfer the files to the destination machine (Setup the dumpdir if you haven’t already)
      • Create the oracle user(s) with the same names as on the source oracle (if you don’t you will need to remap the ownership using the REMAP_SCHEMA for the impdb )
      • Import the tablespace schema and data (repeat for each tablespace)
        • % impdp  system/<password> DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_DATAFILES = <Full path to each tablespace datafile separated by commas>
  • Put the tablespaces in to read/write mode
    • SQL> alter tablespace <tablespace> READ WRITE;
Tags: , , , , , , , ,
Comment