Monday, December 31, 2012

Torque Stats with a CTE

Using a Common Table Expression in SQL to analyze some Performance stats from the OBD scanner.



;WITH cteRawData AS (
SELECT CAST(TripAvgMPG as float) TripAvgMPG,
runid,
ROW_NUMBER() OVER(PARTITION by runid order by CAST(DeviceTime as datetime)  DESC) as RowNum
from rawdata
)
SELECT c.TripAvgMPG,
c.RowNum,
c.runid,
AVG(CAST(r.SpeedOBD as float)) AvgSpeedOBD,
MAX(CAST(r.SpeedOBD as float)) MAXSpeedOBD,
MAX(CAST(r.DeviceTime as DateTime)) DeviceTime,
AVG(CAST(r.AbsThrottlePositionPCT as float)) AVGAbsThrottlePositionPCT,
AVG(CAST(r.EngineRPM as Float)) AvgEngineRPM,
CONVERT(varchar(10), DATEADD(second,MAX(CAST(TripTimeMoving as float)) - MIN(CAST(TripTimeMoving as float)),0),108) TripTimeMoving,
CONVERT(varchar(10), DATEADD(second,MAX(CAST(TripTimeOverall as float)) - MIN(CAST(TripTimeOverall as float)),0),108) TripTimeOverall,
CONVERT(varchar(10), DATEADD(second,MAX(CAST(TripTimeStationary as float)) - MIN(CAST(TripTimeStationary as float)),0),108) TripTimeStationary
FROM cteRawData c
JOIN rawdata r ON c.runid = r.runid
WHERE c.RowNum = 1
GROUP BY c.TripAvgMPG,c.RowNum,c.runid
order by c.TripAvgMPG

Friday, July 13, 2012

Monday, July 2, 2012

Oracle User expired password

Use this command to extract the password and generate the ALTER user statement needed to un-expire an oracle password:


select 'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password  from    dba_users where username = 'username';

Thursday, June 28, 2012

Wednesday, June 20, 2012

Converting 3ga to MP3 using VLC

Love the VideoLan media player. Seems like it can play just about any type of media file. Found a new feature that I like today: File Conversion. I was able to take a 3ga file (recording from my Droid) and convert it to a MP3.

  1. Open the file in VLAN

  2. Click on the Media folder and click the Conver/Save option.

  3. Add your 3ga file to the file selection

  4. Click the arrow on the Convert/Save Button and choose Convert

  5. Pick your destination file

  6. In the Profile Drop down box scroll down to the Audio - MP3 profile.

  7. Click Start.

  8. Bam! 

Monday, June 11, 2012

Thursday, May 10, 2012

Pushingbox.com Web Service

Just discovered http://www.pushingbox.com. Man this is a great free service. As a matter of fact, I am not sure how long this service will be free. The website exposes an API that allows you to send emails or push notifications  to cell phones. The API can be called from CURL, php or a python script. I downloaded the CURL exe and tried it out with some simple VBScripts.


I wanted to setup a monitor to watch the indoor temperature on my house and send me an email notification. I will add other monitors using data from my weather station going forward, but just wanted to setup this quick test.


1. Sign up at the PushingBox.com web site. I just used my google sign in. One gripe about the site is that its a little slow


2. Click on My Services and Add a service. You can add email and such. I went ahead and download Notifry from the Google Play Store. Once you install Notifry you have to get it setup and they give you a big long API ID. You have to type that in on the add service page when you add Notifry



Pro Tip: make sure you disable the Autmoatic talk feature on Notifry…otherwise when you send your first test your phone will automatically read the message outloud upon receipt…that was a nice surprise when I first tried it.


3. Click on My Scenarios. Give your Scenario a name. I called my Indoor Temperature Monitor and click Create. Here you will be able to setup your message. I am using a variable in my message which is designated using the $variablename$ syntax. This is because I wanted my message to tell me how hot the house is. 



4. Once you have created a scenario you will be given a device ID. You will need this device id in the CURL call or whatever method you are using to call the API. Capture that DeviceID for later use.



5. Test the API by visiting the device id in a web page: http://api.pushingbox.com/pushingbox?devid=v1234567890&indoortemp=91


Replace the v1234567890 with your DeviceID and the indoortemp with your variable name.


If all works according to plan you should receive a push notification on your mobile device.


If that does not work, make sure you have your device setup correctly. Switch over to email on the services and try to test receiving an email.


6. Now you are ready to script it up. Again I used CURL but you can use PHP or Python or whatever.


Here is my VBScript:


set fso = CreateObject("Scripting.FileSystemObject")
set f = fso.OpenTextFile("temp.txt",1,True)
Set WshShell = CreateObject("WScript.Shell")

while not f.AtEndOfStream
lineread = f.ReadLine
lineArray = Split(lineread,"|",-1)
currtemp = lineArray(1)

if currTemp > 80 then
Set oExec = WshShell.Exec("c:scriptscurl ""http://api.pushingbox.com/pushingbox?devid=v12345678990&indoortemp=" & currTemp & """")

Do While oExec.Status = 0
WScript.Sleep 100
Loop
end if
wend

I just hardcoded a test file in this case. I will play with my weather station output this evening. This will just check to see if the temperature in this text file is over 80 degrees. Here is a sample of the text file:


IndoorTemp|89


I will have to modify that to parse out the temperature text file…but you get the gist. So now if my house gets over 80 degrees I will get a push notification. Pretty cool.

Wednesday, May 2, 2012

Mount ISO AIX 6.1

To mount ISO


loopmount -i /iso/dvd.iso -l loop0 -o "-V cdrfs -o ro" -m /mnt/dvd

To umount ISO


loopumount -l loop0 -m /mnt/dvd

If you get a message stating that Specified Loopback device is not found in ODM then you can run this command to make a loopback device.


mkdev -c loopback -s node -t loopback

Thursday, April 26, 2012

du command

I was finally able to harness the power of the du command. The -s flag has a cryptic definition in the man page (nothing new for man there). But using the -s flag allows you to summarize the file size for directories.


This command will give you a summary of the directory sizes in root. You can of course target down to other folders like so

Update 8/17/2015

Often I need to sort the output by size. Here you go.

Monday, April 16, 2012

Oracle Index Information

Some queries to help find out infomration about specific indexes.


This will show you the indexes for a specified table


select	i.index_name
, i.tablespace_name
, ceil(s.bytes / 1048576) "Size MB"
from dba_indexes i
, dba_segments s
where i.index_name = s.segment_name
and table_name like '&table'
order by 2, 1



This will show you the columns of a specific index


select 	column_name
from dba_ind_columns
where index_name = '&index'
order by column_position 

This will give you some extra details about the index


select index_name, num_rows, last_analyzed from dba_ind_statistics where table_name = 'Table Name';

Wednesday, April 11, 2012

Tracking Down Busy Oracle Sessions

This query will show the current users and the OS PID…you can use this to then run a top command to watch the session.


ACTIVE users



select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
, v$process p
 Where s.paddr = p.addr
 and s.status = 'ACTIVE'
 and s.osuser <> 'oracle'
 order by to_number(p.spid)

ALL Users (except oracle)



select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
 , v$process p
 Where s.paddr = p.addr
 and s.osuser <> 'oracle'
 order by to_number(p.spid)

From there you can use the OS PID to run a top command to see what kind of resources the session is consuming



top -p PID

Or you can go the opposite way. Run a top command and then track down the Oracle Sessions using the PID


Find Active Users



select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
 , v$process p
 Where s.paddr = p.addr
 and p.spid IN (22057, 28919, 21972)
 order by to_number(p.spid)

Tuesday, April 10, 2012

Monday, April 9, 2012

Recreate Oracle Controlfile

Use the following example if you need to create a controlfile script. The script can be used to recreate a controlfile.


 


alter database backup controlfile to trace;

Once you run this head over to your trace files directory and locate the script. Find the following line in the control file script:


CREATE CONTROLFILE REUSE DATABASE "SID" RESETLOGS ARCHIVELOG

From here you can edit the path of the datafiles and redologfiles if necessary. Startup the database in nomount mode and run the script you created as sysdba.


Check on the status of the instance.


 


select status from v$instance;

Query to see the control files.


select name from v$controlfile;

It is a good idea to generate this trace file from time to time. If/when you lose a control file you can use this to rebuild it from scratch.

Monday, April 2, 2012

EXPDP using QUERY option

Took me a while to get this command escaped correctly. The query has a varchar column so you need to make sure and escape the command correctly. Also make sure you set your environment variables using ORAENV before you execute this. I left the credentials off this command so you will be prompted for credentials if you run this as is.


expdp directory=data_pump_dir dumpfile=SY00500T.dmp logfile=SY00500T.log 
tables=schema.tablename query=schema.tablename:"WHERE ENTITY_NAME='AR00200T'
AND SEQ > 150000000"

Wednesday, March 28, 2012

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;

Monday, March 26, 2012

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.

Friday, March 23, 2012

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.

Thursday, March 22, 2012

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

Wednesday, March 21, 2012

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)

Tuesday, March 20, 2012

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.

Monday, March 19, 2012

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.

Thursday, March 15, 2012

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

Wednesday, March 14, 2012

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

Monday, March 12, 2012

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

Wednesday, March 7, 2012

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;

Monday, March 5, 2012

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 

Friday, March 2, 2012

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;

Wednesday, February 29, 2012

Tracking Down Oracle Deadlocks

The Dreaded ORA-00060 error. DEADLOCK DETECTED Fortunately this is not an Oracle problem, but is an application problem. Now the fun part of tracking it down.


1. Locate the Trace file so you can gather some details.


2. Find the resource names in the Trace file…


Resource Name        
TM-00014ef4-00000000 
TM-00014ef4-00000000 


Hopefully this will give you some relavant object information. This will allow you to query the all_objects table looking for the objects.


3. Query the all_objects table




select object_name, object_type from all_objects
where object_id in ( to_number( '14ef4', 'xxxxx' ), to_number( '14ef4', 'xxxxx' ) );



From here you get to do some more digging. The trace file will contain a lot of information, mostly cryptic but hopefully it can help point you in the right direction.

Monday, February 27, 2012

makewhatis

So that took longer than it should. There is a command called whatis that gives you a single line description of a command:



whatis grep


grep (1) - print lines matching a pattern
grep (1p) - search a file for a pattern
grep (rpm) - The GNU versions of grep pattern matching utilities



A nice little tool to use. I just installed CentOS 5 in a VM and noticed that all my whatis commands were returning nothing appopriate…I quickly found the makewhatis command on some articles but could not locate it for CentOS 5 at first. Finally found the location:


/usr/sbin/makewhatis



This command builds the database and now my whatis commands are working.

Un-expire Oracle User account

There is no real un-expire option for an Oracle user account. Say you have an account that goes into expire status…you have to alter the user using the identified by clause to set the password. Problem is if you do not know the original password you have to change it…this could cause big problems.


However here is a cool workaround to remedy the problem:


First find the user int he sys.user$ table using this query:



select password, spare4 from sys.user$ where name='USERNAME';

You should see some results similar to below:


PASSWORD-1FDF39535EDB8EDD
SPARE4-S:5BEFDD3B650E6910F42EAB51EBDFF612205E92620B56166AF480551F41B6


Now take the results and run the ALTER statement on the user: 


alter user USERNAME identified by values 
'S:5BEFDD3B650E6910F42EAB51EBDFF612205E92620B56166AF480551F41B6;1FDF39535EDB8EDD'; 

You concatenate the password field and the spare4 field with a semicolon in between.

Thursday, February 23, 2012