Category: How To


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

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

How To: Fix Nick Berlette’s excellent Redirect Plugin for WordPress 2.6 and higher

Filed Under: How To, Wordpress by Jason L Froebe — Leave a comment
February 19, 2010

The static pages (like Store) are working again.  I’ve had to fix the redirect plugin by Nick Berlette.  If you want it to work with your modern WordPress blog (2.6 or higher) you just need to change a tiny bit.
Remove this:

require_once($_SERVER[‘DOCUMENT_ROOT’] . ‘/wp-config.php’);

Replace with:

$root = dirname(dirname(dirname(dirname(__FILE__))));

if (file_exists($root.‘/wp-load.php’)) {
    require_once($root.‘/wp-load.php’);
} else {
    require_once($root.‘/wp-config.php’);
}

It should work fine now :)

Tags: , , , , , ,
Comment

Sun’s VirtualBox 3.1.x: Getting VRDP (remote desktop) to authenticate properly with Linux PAM

Filed Under: How To, Linux, OS, virtualbox by Jason L Froebe — 4 Comments
January 22, 2010

I rebuilt an Ubuntu 9.10 server this past week, ripping off VMware and replacing it with VirtualBox 3.1.2. Setting up VirtualBox as a headless server was very easy with VBoxTool. However, I ran into a problem that I was unable to connect using remote desktop (rdesktop) as any user but the user that started the virtual machine.

Jan 21 22:43:13 vm-holder unix_chkpwd[16040]: check pass; user unknown
Jan 21 22:43:13 vm-holder unix_chkpwd[16040]: password check failed for user (jason)
Jan 21 22:43:13 vm-holder VBoxHeadless: pam_unix(vrdpauth:auth): authentication failure; logname=virtualbox uid=1001 euid=1001 tty= ruser= rhost=  user=jason

This is, currently, an undocumented security feature of VirtualBox 3.1x to prevent just anyone from accessing the virtual machine console. For most folk, this might be a very good thing but if you have a team of sysadmins that should have access to the virtual machine consoles, you probably don’t want them to use the same login.

If that is the case, you can add the user(s) that should have access the virtual machine console to the shadow group on the host Linux machine. Be warned though that the user(s) that are added to the shadow group should not be able to log into the host machine else they will be able to read the shadow file where all the passwords to the box are stored. If the users need access to the host box, then they should have a login for host access (not part of the shadow group) and another for virtual machine console access.

Adding linux user jason_vrdp to the shadow group:

(root) # usermod -G shadow,virtualbox jason_vrdp

Prevent jason_vrdp from logging in to the host or anyone from sudo’ing to it:

(root) # usermod --shell /bin/false jason_vrdp

That’s it :)

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