Tag Archive: Oracle


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)

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. :)

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

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.