Oracle on Windows, troubleshooting suggestions

1.  Check the status of the Oracle services.  Are they started?

2.  From a command prompt, sqlplus / as sysdba, then enter startup.  Are there error messages?

3.  In the last instance I had to troubleshoot, the client had entered non-functional values into the Oracle config.  This meant the spfile was toast.  To recover from this:

• sqlplus / as sysdba
• create pfile='c:\temp\initorcl.ora' from spfile;
• exit
• Use notepad or your preferred text editor and clean up the bad values in the pfile you just created.  Save the file.
• sqlplus / as sysdba
• startup pfile='c:\temp\initorcl.ora'
• create spfile from pfile='c:\temp\initorcl.ora';
• shutdown immediate
• startup

4.  To restart your database without rebooting the server, you have several options.

• Restart the Oracle service.  You only need to restart the one for the instance.  Assuming your SID is named orcl, you just need to restart the OracleServiceORCL.
• My preferred way is to do this from the command prompt.  It's a lot easier to catch any problems this way.
• sqlplus / as sysdba
• shutdown immediate;
• startup;

5.  I also had a client with listener issues.  Again, the command prompt is your friend.

• lsnrctl status - shows the status of the listener.
• lsnrctl services - shows what services the listener knows about.
• lsnrctl stop - yep, stops it.
• lsnrctl start - you guessed it, starts it.

Content Filtering with OpenDNS

I recently upgraded our home wi-fi connection.  The old one tended to overheat and simply stop working.  As part of the new router installation, it asked me if I wanted to enable parental controls.  I'm all for that, so I answered yes.  And this led me to learn about http://www.opendns.com.  This has been awesome, and has allowed me to have tighter controls at home.  Is it fool proof?  Well, of course not.  But it certainly helps keep the filth down and helps me protect my family.

I found a great article on the web about OpenDNS (http://www.godandscience.org/general/opendns_internet_filter.html).  He really does a great job of explaining how it works and what it can provide.

To add to what Mr. Deem wrote, I'd add the following warning / suggestions.

1. Any smart phone user can bypass these controls by using the cellular network.
2. If the smart phone has the ability to be a wi-fi hotspot, then this can allow other devices, such as laptops, tablets and game consoles, to also bypass the controls.
3. Social networking sites, in particular Twitter and Vine, can also let filth in.
4. Poor password management can allow the controls to be turned off.
5. A careless neighbor with a non secure wi-fi broadcasting can also be used to bypass your filters.
6. Email is often used for the sharing of files.
7. File sharing sites are not only sources of objectionable material, but can also be used to distribute illegal software.
8. Don't forget most modern game consoles, TVs and DVD players can access the internet

Bottom line?  As society continues to degrade around us, the last and best line of defense is an open communication between all family members.  It's not only acceptable, but good practice, to simply take any device in the home, at any time, and audit what it has been doing.  It is our duty to talk to each other, to protect each other and to shield each other.  Have strategies for what do when, not if, something objectionable worms its way into your home.  It will happen.  But instead of teaching our family to feel ashamed, to feel defeated or to feel like they have done something wrong, teach them to immediately bring up what happened.  You can learn from these experiences and further fortify your home.  If a website comes up that you find questionable, you can block it yourself!  You can inform OpenDNS, and they can inspect it.  You can talk about the circumstances that brought it up.  Was it during a web search?  Continue to learn and to fortify!  Keep the dialog open!

Migrating from Access to Oracle

Two Options.  One provides a lot more detail if you need it (constraints, indexes, ddl for tables, control files for Sql Loader, etc.).  The other simply creates a delimited text file that can then be imported using SQL Loader.

Option A - Access Export.
Pretty simple strategy here.

1. Open the Access file.
2. Go to the Tables section.
3. Double click on the table to open it.
4. Right click on the table, then choose Export > Text File.
5. In the dialog box, choose a file name.  Make sure the 3 boxes are all unchecked.  Click OK.
6. Choose Delimited, then click Next.
7. Choose Comma delimited, with a Text Qualifier of a double quote.
8. Click on Next, then Finish.
9. In Oracle, create a table that matches the Access one. 
10. Create the control file for Sql Loader.
11. Now use Sql Loader to load the data into your new Oracle table.

Option B - Sql Loader.
This did not work as advertised for me.  Here are the steps I took to finally be able to migrate a single table from Access to Oracle, using the Sql Developer utility to do this.

1. Use SqlDeveloper 3, not the newest 4.  4 does not seem to map any data types, so it fails miserably.
2. Connect to the target instance.
3. Right click, Migration Repository, then Associate Migration Repository.
4. Tools, Migration, Microsoft Access Exporter, Run Microsoft Access 2007 Exporter....
5. This should bring up Access.  Pretty simple.  Pick the mdb to migrate, make sure the "migrate data" is checked.  I used c:\temp for the output directory.
6. Now, back in SqlDeveloper, Tools, Migration, Migrate.  Pick your target connection, give it a name, choose an output directory.  Whenever you have the choice, pick "Offline"!  This is key, as we are going to hack some files when we are done.
7. Once the migration finishes, it should bring up the script in a worksheet.  Just find the part with the create table ddl, and run that to create your target table.
8. Now that you have an empty table, shell out to a command prompt and drill down until you find a control file for Sql Loader.  Put in the correct location of the data file.  For me, I put this all under c:\temp, so after drilling around a bit it wasn't too hard to locate the data and ctl files.
9. Also, check your date formats.  For me, it was not really close.  The dates were in a mm/dd/yyyy hh24:mi:ss format, but the control file had them masked completely wrong.
10. Now, use Sql Loader to load the data into your newly created table.

Ugly, but it did finally work.

Mike

Drop db links and jobs in other schemas

A quick workaround to working in other schemas when you don't know the password.

############################################################################

if [ "${1}" = "" ] ; then
  echo "Usage ${0} sid,  exiting..."
  exit 1
fi
. /home/oracle/.bashrc.CLIENT

TARGET_SID=${1}
PRIVATE_FILE=/home/oracle/.private

MASTER_USER=system
MASTER_PASS=`cat $PRIVATE_FILE|grep -i "${TARGET_SID}:"|grep ":SYSTEM:"|head -1|awk -F: '{print $3}'`
echo $MASTER_USER
echo $MASTER_PASS

# Find 'em and Drop 'em
sqlplus -s "$MASTER_USER/$MASTER_PASS@$TARGET_SID" <<EOF
set pages 9999
set lines 300
set trim on
set trims on
set feedback off
set termout off
set verify off
set head off
set newpage none
set echo off
set term off
spool OUT/create_procedures.sql

select 'create or replace procedure '||owner||'.drop_dblink_'||rownum||' as '||chr(10)||'begin'||chr(10)||'execute immediate ''drop database link '||db_link||''';'||chr(10)||'end;'||chr(10)||'/'||chr(10)
from dba_db_links
where owner not in ('SYS', 'SYSTEM', 'SYSMAN')
order by owner, db_link;


spool off;

spool OUT/run_procedures.sql

select 'exec '||owner||'.drop_dblink_'||rownum||';'||chr(10)||'commit;'||chr(10)
from dba_db_links
where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'STRMADMIN')
order by owner, db_link;

spool off;

spool OUT/drop_procedures.sql

select 'drop procedure  '||owner||'.drop_dblink_'||rownum||';'
from dba_db_links
where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'STRMADMIN')
order by owner, db_link;

spool off;

@ OUT/create_procedures.sql
@ OUT/run_procedures.sql
@ OUT/drop_procedures.sql
exit
EOF


########################################################################

if [ "${1}" = "" ] ; then
  echo "Usage ${0} sid,  exiting..."
  exit 1
fi
. /home/oracle/.bashrc.CLIENT

TARGET_SID=${1}
PRIVATE_FILE=/home/oracle/.private

MASTER_USER=system
MASTER_PASS=`cat $PRIVATE_FILE|grep -i "${TARGET_SID}:"|grep ":SYSTEM:"|head -1|awk -F: '{print $3}'`
echo $MASTER_USER
echo $MASTER_PASS

# Find 'em and Break 'em
sqlplus -s "$MASTER_USER/$MASTER_PASS@$TARGET_SID" <<EOF
set pages 9999
set lines 300
set trim on
set trims on
set feedback off
set termout off
set verify off
set head off
set newpage none
set echo off
set term off
spool OUT/create_procedures.sql

select 'create or replace procedure '||schema_user||'.drop_job_'||job||' as '||chr(10)||'begin'||chr(10)||'dbms_job.broken ('||job||', true);'||chr(10)||'end;'||chr(10)||'/'||chr(10)
from dba_jobs
where schema_user not in ('SYS', 'SYSTEM', 'SYSMAN')
and broken = 'N';


spool off;

spool OUT/run_procedures.sql

select 'exec '||schema_user||'.drop_job_'||job||';'||chr(10)||'commit;'||chr(10)
from dba_jobs
where schema_user not in ('SYS', 'SYSTEM', 'SYSMAN')
and broken = 'N';

spool off;

spool OUT/drop_procedures.sql

select 'drop procedure  '||schema_user||'.drop_job_'||job||';'
from dba_jobs
where schema_user not in ('SYS', 'SYSTEM', 'SYSMAN')
and broken = 'N';

spool off;

@ OUT/create_procedures.sql
@ OUT/run_procedures.sql
@ OUT/drop_procedures.sql
exit
EOF

Desktop Linux

I've been running Ubuntu on my desktop, since leaving Novell back in 2012.  I was feeling a bit nostalgic the other day, and decided to build out a VM with the latest version of OpenSUSE.  Let me share a few bullet points with you about this experience.

1.  The install of OpenSUSE took twice as long.

2.  Boot time is incredibly slow with OpenSUSE.  Honestly, I can start booting it, go get a drink, startup my Ubuntu VM, reboot it a couple of times, get up and go to the bathroom, come back and OpenSUSE will still be booting.

3.  Response time is even slower.  It's painful actually.

4.  The update process is HORRIBLE.  When OpenSUSE first popped up and told me I had 174 updates, I thought, OK, no big deal, right?  Well the graphical update went into some sort of infinite loop.  I had to kill it after about the 6th time it restarted itself.  In the end, I opted for a simple "zypper patch" as root at the command line.  That  sucker has been running for over 2 hours.  By comparison, Ubuntu updates rarely take in excess of 5 minutes.

As much as I miss the good old Novell days, I think I am converted to running Ubuntu.

I must say, running OpenSUSE in seamless mode does  rock.  It is the one thing I've found it does better than Ubuntu.

Access SQL Server from Linux

Let's face it.  Even since back before they started labeling the sql server releases as 2000 whatever, there has been a need to access sql server from other operating systems.  I'm sure java would be a slick way to go, but for an  administrator used to living at a bash prompt, that just won't cut it.

Thankfully, as part of some consulting work, I stumbled upon FreeTDS.  It's pretty slick.
1.  Download FreeTDS from http://www.freetds.org/

2.  Install FreeTDS
 - copy the compressed file to the target server.
 - uncompress the software bundle.
 - cd into the FreeTDS source.
 - ./configure
 - make
 - make install
 - the default is to install to /usr/local

3.  Configure FreeTDS
 - cd /usr/local/etc
 - vi freetds.conf
 - add in a line for the target server
 # A typical Microsoft server
 [EvolveDB]
     host = 137.65.51.90
     port = 1433
     tds version = 7.0

4.  Query your server
 tsql -S <your name from the freetds.conf file> -U <username> -P <password>
 If my user was admin and my password was friday, server name EvolveDB
 tsql -S EvolveDB -U admin -P friday

5.  Use tsql in batch mode
 tsql is very limited.  Simply use redirects to either run scripts or capture output.
 For example, using the above connection, and running a script named mike.sql and capturing that to mike.out
 tsql -S EvolveDB -U admin -P friday < mike.sql > mike.out

I've found that tsql does some funky things with the output.  For example, you can pipe it directly to awk, but if you toss sed in there as well to just remove spaces, it blitzes the tabs as well.  I've had the best luck sticking a ~ in as a delimiter, then using that with awk to parse the columns back out.

Test writing to an Oracle directory

Let's say you want to make sure you can write to a directory you created.

1.  select * from dba_directories where directory_path = '/usr/tmp';

2.  In my case, I had three.  I decided to test the ODPDIR directory.

3.
DECLARE
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'ODPDIR', 'ODPDIR.txt', 'W' );
  utl_file.put_line( l_file, 'Here is some text' );
  utl_file.fclose( l_file );
END;
/

4.  I ssh into my server, cd to /usr/tmp, and I see a file called ODPDIR.txt has been created.  Whoot!