Oracle DB Link to SQL Server using Windows

Found several dozen posts on this but it still took me a while to figure out. Wanted to share my quest here. The following instructions are for Windows. I will try to figure out a Linux install next.

1. Create an ODBC connection (using 32bit ODBC) to your SQL Server instance

2. Create a SQL Server account on the SQL Server database you want to connect to.

3. Add an entry to your TNSNAMES.ORA file for the new SID

dg4odbc  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST = localhost)(PORT=1521))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  ) 

4. Modify your listener.ORA file. Add the Following SID DESC to your SID LIST

(SID_DESC=
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=C:oracleappproduct11.2.0dbhome_1)
         (PROGRAM=dg4odbc)

      )

5. Create a initSID.ora file in ORACLE_HOME/hs/admin and set the following parameters:

HS_FDS_CONNECT_INFO = <SQL SERVER DSN NAME>
HS_FDS_TRACE_LEVEL = 0
HS_OPEN_CURSORS = 300

You can use HD_FDS_TRACE_LEVEL = ODBC if while you are troubleshooting. 

6. Create your database link

CREATE PUBLIC DATABASE LINK "Link NAME" CONNECT TO "SQL USER" IDENTIFIED BY VALUES 'SQL USER PASSWORD' USING 'DG4ODBC'

7. Execute remote sql statement

 select * from <sql server table name>@<db link name from step 6>;

Crack open a Celebratory Mt. Dew and bask in the glory of this small victory.

Setting optimal UNDO retention

Run this query to find the optimal

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.STATUS = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';

Then run this to set the optimal

ALTER SYSTEM SET UNDO_RETENTION=5;

Deleting files older than X on Linux

The following is handy if you need to delete files older than X on a Linux server. I like to run the find command first before actually moving or deleting the files. Just to make sure the files I want to remove are returned from the find command.

find /fullpath -mtime +4 -ls

The -ls flag will list out the details of the file so you can verify that the mtime parameter returns the right files.

find /fullpath -mtime +4 -ls -exec mv {} /destination ;

After you verify that the find command returns the files you want then you are ready to use the -exec flag on the find command to actually execute the move/delete. I also like to use full path here on the find command, just to make sure I move the right files.

Using VMSTAT

vmstat = virtual memory stats

Procs

• r - the number of processes waiting for run time


• b - the number of processes in uninterruptible sleep

Shows you if there are processes blocking your system from running smoothly. The b column shows processes in sleep state. If these numbers are high then you have some waiting processes and may have some problems.

Example r = 5 means that 5 processes are currently waiting to execute. When the CPU is pegged at 100% the severity of the CPU starvation wont be reflected in the percentage of CPU utilization but the run queue will clearly show the impact as processes back up in the queue

b can be used to show CPU power…if the value is constantly greater than zero then you may have a CPU bottleneck. Use ps to list out the most CPU intensive processes.

Memory

• swpd - amount of virtual memory used


• free - freem memory in the system


• buff - the amount of memory used as buffers


• cache - the amount of memory used as cache.


• inact - the amount of inactive memory. (-a option)


• active - the amount of active memory. (-a option)

Swap

• si - memory pages in


• so - memory pages out

If you have a lot of activity in so it may indicate that you need more physical ram. the system is constantly paging memory out to disk. you need to take these stats over a period of time to get a feel for how your system performs.

If the values are constnaly greater than 0 then you have a memory issue. use ps to find out the most memory intensive processes.

IO

• bi - Blocks received from a block device (blocks/s).


• bo - Blocks sent to a block device (blocks/s).

System

• in - The number of interrupts per second, including the clock.


• cs - The number of context switches per second.

CPU

• us - Time spent running non-kernel code. (user time, including nice time)


• sy - Time spent running kernel code. (system time)


• id - Time spent idle. Prior to Linux 2.5.41, this includes IO-wait time.


• wa - Time spent waiting for IO. Prior to Linux 2.5.41, included in idle.


• st - Time stolen from a virtual machine. Prior to Linux 2.6.11, unknown.

If us is high there is a possibility that some of the user initiated processes are consuming high CPU

If wa is high then there is an issue with the disk storage subsystem and you can use iostat to find sources of I/O contention.

Disable Oracle Expire passwords

In order to prevent passwords from expiring on your DB users you can create a separate profile (or modify the exisisting DEFAULT profile) to disable the expire time. The DEFAULT profile was set to 180 in my environment. The accounts in question were not user accounts but were application accounts. Make sure you check with your security folks before throwing this down.

Create the New Profile.

CREATE PROFILE my_profile LIMIT
       PASSWORD_LIFE_TIME UNLIMITED;

Change your user to the new profile

ALTER USER scott PROFILE my_profile;

You can set other options i your new profile if you want. The cool thing is that the options you do not set will automatically revert to the DEFAULT profile. Thus its a good idea to leave your DEFAULT PROFILE alone and create custom profiles.

Some other options you can change.

• FAILED_LOGIN_ATTEMPTS - failed login attempts before the account is locked


• PASSWORD_LIFE_TIME - limits the number of days the same password can be used for authentication


• PASSWORD_REUSE_TIME - number of days before a password can be reused


• PASSWORD_REUSE_MAX - number of password changes required before the current password can be reused


• PASSWORD_LOCK_TIME - number of days an account will be locked after maximum failed login attempts


• PASSWORD_GRACE_TIME - number of days after the grace period begins during which a warning is issued and login is allowed


• PASSWORD_VERIFY_FUNCTION - password complexity verification script

Current Running Queries Oracle

Several different ways to get this data. The first one is a query to get the current running quries.

select sesion.sid,
      sess.username,
      optimizer_mode,
      hash_value,
      address,
      cpu_time,
      elapsed_time,
      sql_text
 from v$sqlarea sqlarea, v$session sess
where sess.sql_hash_value = sqlarea.hash_value
  and sess.sql_address    = sqlarea.address
  and sess.username is not null

The next one will display the SQL of the PIDs using the Most CPU. You get the PID number by running the top command

select proc.spid, sess.username, s.sql_text
  from v$process proc, v$session sess, v$sqlarea s
 where proc.addr = sess.paddr
   and sess.sql_hash_value = s.hash_value
   and proc.spid in (22725,553)

chown -R lesson learned the hard way

chown -R is a risky command to run. Especially if you are sitting in root. Make sure you are in the directory you want to recurse through…or you will be recursing yourself.

The following is more of a brain dump then a guide. Maybe you can glean some good stuff out of it.

I was able to Crtl+C out of it quick enough to prevent most of the damage, and fortunately I was sitting on a development server and not the production sever. One side effect is that all the Oracle Databases in a given Oracle Home stopped. When the permissions where changed on the oracle binary in the Home that caused a problem.

So find the oracle binary (you can use oraenv to set the home and then CD $ORACLE_HOME)…In Retrospect I am not sure that I needed to run this, but it is a good idea to check the permissions on the oracle binary. If your system is working fine do not change this!

ls -l oracle

might look something like this: 

-rwxr-xr-x 1 oracle oinstall 

You need to fix that with this command

chmod 6751 oracle

Resulting in this: 

-rwsr-s--x   1 oracle   dba        

Then you can try to open your database. If you are unfortunate like me then you will get the folllowing error:

ORA-27123:unable to attach to shared memory segment 
Linux error :22: Invalid argument 
Additional information:1 
Additional information:2457610

From here you need to use the sysresv tool along with the ipcs tool

• run the . oraenv command and set it to the SID you want to work with


• run the sysresv -i command


• This tool will tell you if the oracle instance is still in memory. If you get a yes/no prompt that typically means the instance is shutdown and you need to enter yes to clear it from memory.

The output will look similar to this

 IPC Resources for ORACLE_SID "X816" :
   Shared Memory:
   ID              KEY
   16837           0xe4efa8dc
   Semaphores:
   ID              KEY
   12714018        0x09d48346
   Oracle Instance not alive for sid "X816"

• run the ipcrm -m command. You will use the ID from the sysresv command you executed above (16837) in our intance.

Then you can run a startup on your database. If you are unfortnate like me then you will receive the following error:  

ORA-00845: MEMORY TARGET not supported on this system.

This error was raised because /dev/shm (my tmpfs mount) was out of space. I noticed a lot of orphaned files (labled by SID) in this folder. So I moved them over to a temporary folder and bought enough room to start each one of my database instances.

Linux watch command

Use the watch command to watch the results of a command change at a given internal. Example:

watch -n 5 -d vmstat

The watch command will the vmstat command every 5 seconds and will highlight on screen the values that have changed.

Adding Disk to Linux

This procedure can be vastly different than the steps below. This is just a quick overivew on how to add a new disk to Linux.

Patitioning
#list devices
fdisk -l

#invoke fdisk for the desired drive
fdisk /dev/[device]

#Create a new partion
Command (m for help): n

#Select primary partition and size to use the entire disk
Command (m for help): p
Partition number (1-4): 1
First cylinder (1-1305): 1
Last cylinder or +size or +sizeM or +sizeK (1-1305): 1305

 #Commit changes
Command (m for help): w

 Formatting
#show existing file systems (for finding the fs type if you are just duplicating existing configuration)

df -hT

#find block size of a file system
tune2fs -l /dev/[partition name] | grep ‘Block size’

#mkfs.ext3 -b [block size] /dev/[partition name]

 Mounting

#First create a mount point
mkdir /u01

#then mount it
mount /dev/[partiton name] /u01

#verify the fs is writeable
touch /u01/test.file ; ls /u01

#set the partition to be mounted on boot by adding a line to /etc/fstab.  This is the example fstab file…

#/dev/VolGroup00/LogVol00  /                   ext3      defaults              1 1
#LABEL=/boot                   /boot              ext3      defaults              1 2
#tmpfs                            /dev/shm         tmpfs     defaults              0 0
#devpts                          /dev/pts          devpts    gid=5,mode=620   0 0
#sysfs                           /sys                 sysfs      defaults              0 0
#proc                             /proc               proc      defaults               0 0
#/dev/VolGroup00/LogVol01 swap             swap     defaults               0 0

vi /etc/fstab
/dev/[partition name] /u01                       ext3       defaults           0 3

Linux CPU Information

You can cat the /proc/cpuinfo file to get information about your CPUs. Here are some commands.

List everything in cpuinfo

cat /proc/cpuino

Count Physical Processors

 cat /proc/cpuinfo | grep 'physical id' | sort -u

Count Cores and such

cat /proc/cpuinfo | grep processor | wc -l

Look at CPU stats (since midnight)

sar -u

Look at current CPU Stats (the -d 2 5 says run this every 2 seconds for 5 times)

sar -u -d 2 5

ORA-19815 Recovery File Size

Problem:

ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 bytes is 99.46% used, and has 28102144 remaining bytes available.

Solution (if you have available disk space):

alter system set db_recovery_file_dest_size=<size>; 

Query to show the size of the recovery area and how full.

set lines 100
col name format a60
select	name
,	floor(space_limit / 1024 / 1024) "Size MB"
,	ceil(space_used  / 1024 / 1024) "Used MB"
from	v$recovery_file_dest
order by name

Oracle Slow Session Check

The following queries will allow you to find the sessions currently logged into the database that have accumulated the most time on CPU or for certain wait events. Use them to identify potential sessions to trace using 10046. These queries are filtering the sessions based on logon times less than 4 hours and the last call occurring within 30 minutes. This is to find more currently relevant sessions instead of long running ones that accumulate a lot of time but aren’t having a performance problem. You may need to adjust these values to suit your environment.

Find Sessions with the Highest CPU Consumption

-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;

Find Sessions with Highest Waits for a Certain Type

-- sessions with the highest time for a certain wait
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name' 
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

10g or higher: Find Sessions with the Highest DB Time

-- sessions with highest DB Time usage
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND  sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;

Clone Oracle User

Needed to clone an Oracle user from one database to another database. Turns out this was not as easy as I had planned. We will be using the DBMS_METADATA function to get data to create the new user.

On the Source server:

select DBMS_METADATA.GET_DDL('USER','your user') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','your user') FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','your user') FROM DUAL; 
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','your user') FROM DUAL; 
SELECT DBMS_METADATA.GET_granted_DDL(‘TABLESPACE_QUOTA’, ‘your user’) FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL ('DEFAULT_ROLE', 'your user') from DUAL;

Then execute the output from this command on the target server

CREATE USER "your user" IDENTIFIED BY VALUES 'encoded password' 
DEFAULT TABLESPACE "your tablespace" TEMPORARY TABLESPACE "TEMP" 

GRANT "CONNECT" TO "your user"

GRANT SELECT ON "your object" TO "your user";
GRANT SELECT ON "your object" TO "your user";
... //rest of your objects 

Enable Archive Log 11g

I setup a test box and wanted to do some RMAN testing. Noticed that my database was not set to archive log mode. I wanted to test some archive log backup and restores so needed to remedy this first.

This command will enable Archive Logging on your database

alter system set log_archive_start = TRUE scope=spfile

This command will set the destination for the archive logs

alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/orcl/arch' scope=spfile;

Then we shut the database down and then start it up and mount it

shutdown immediate
startup mount

Then we enable archive logging and open the database

alter database archivelog;
alter database open; 

Run the archive log list command to check the settings

archive log list

Then force a log switch and check the directory to make sure you have an archive log.

alter system switch logfile;

Check your log destination directory you should see an archive log in the directory. You can run a show parameter command to see the archive parameters

show parameter archive

Tablespace out of space

So your tablespaces will eventually run out of space.

Query to show space usage on tablespaces

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

Add some data files:

alter tablespace tablespaceName
add datafile 'file location'
size 2G
autoextend on
next 100M
maxsize 4G;