Category: Oracle


Wanted: Entry level or Mid-level Oracle DBA (Chicago, IL)

Filed Under: Databases, Jobs, Oracle by Jason L Froebe — Leave a comment
November 28, 2011

Position Responsibilities

• Manage database configurations, schemas, and space • Manage database and system alerts. • Support new application code releases / builds in various production and non-production environments • Provide performance tuning, problem research/resolution, code reviews and deployments, SQL support, and data movement • Manage database changes for releases and new projects • Provide SQL and application tuning assistance • Build Backups / Recovery and High Availability • Manage disaster recovery support and testing • Monitor database health and alerts • Provide production on-call support on rotational basis • Manage database and product deployments and repeated database maintenance and monitoring tasks • Troubleshooting database issues • Open & follow-up on change request tickets for internal users • Open and follow-up on service requests with Oracle vendor support

Position Requirements

• Degree or equivalent experience in either Oracle or Sybase. • BS in Computer Science or equivalent specializing in Oracle or Sybase • Good understanding of relational methodology • Good understanding of SQL Plus or equivalent • Good communication skills and the ability to work as a team member • Good analytical and problem solving skills. • Self starter with willingness to do independent research and trouble shooting. • Willingness to learn new database skills in a fast paced environment • Education or Experience with Unix shell scripts helpful • Good understanding of relational methodology • Must be comfortable in both self-managed and team driven environment • Experience with Microsoft Office Tools helpful

Interested? (say I referred you on the application form)

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 — Leave a 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

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

How to disable the “uninstall password” to uninstall Symantec Antivirus

Filed Under: How To, Oracle, OS, Windows by Jason L Froebe — 5 Comments
March 10, 2010

I was trying to install Oracle 11g client on to a WinXP box but Symantec Antivirus wouldn’t let me because it thought it was infected with over 800 viruses.  Same file was marked clean on another box.  So, I picked several of the viruses it said was infected with and looked for any evidence of them on the WinXP box.  No dice.  This led me to believe that Symantec Antivirus itself was somehow mucked up.

When I tried to uninstall Symantec Antivirus, it asked for a password.  When I put in the correct password, it refused it.  So how to uninstall it with a messed up password?  Easy..

Open up regedit (as an administrator) and change HKEY_LOCAL_MACHINE\SOFTWARE\INTEL\LANDesk\VirusProtect6\CurrentVersion\AdministratorOnly\Security\UseVPUninstallPassword to “0″ (zero).

I was able to uninstall and reinstall Symantec Antivirus.

Tags: , , , , , , , ,
Comment

Andrew E. Bruno: Database Design with Dia

Filed Under: ASE, Databases, How To, IBM DB2, Microsoft, My Databases, MySQL, Oracle, PostgreSQL, SQL Server, SQLite, Sybase by Jason L Froebe — 3 Comments
November 5, 2009

Back in December Diaof 2007, Andrew E. Bruno wrote an excellent article on how to use the open source program Dia to design databases.  He used the resultant design to create a MySQL database but the same principles could be used to create just about any relational database from the design.  Dia is available for Linux, *nix, and Windows.

Database Design with Dia

In this post I’m going to give a quick how-to on creating database schemas with a wonderful tool called Dia. I’ve often found having a nice visual representation of a database to be quite helpful but can’t stand keeping it up to date. As soon as you add a new column or change the design around you end up having to sync your visual diagram with your SQL files. It’s tedious having to manage the various SQL for building the database and this can be a larger pain when trying to support different database platforms each with their own SQL syntax. So before you create your next database read on and see how Dia can make your life a bit easier.

Dia is a program for creating diagrams and for this exercise we’ll be creating UML diagrams from within Dia. We’re also going to use a perl script called tedia2sql which will transform our Dia files directly to SQL for our target database. What’s also nice about creating database schemas this way is that you can generate SQL for multiple target databases without the maintenance overhead.

read more….

Tags: , , , , , , ,
Comment