Redo vs. Rollback vs. Undo
I find there is always some confusion when talking about Redo, Rollback and Undo. They all sound like pretty much the same thing or at least pretty close.
Redo = Every Oracle database has a set of (two or more) redo log files. The redo log records all changes made to data, including both uncommitted and committed changes. In addition to the online redo logs Oracle also stores archive redo logs. All redo logs are used in recovery situations.
Rollback = More specifically rollback segments. Rollback segments store the data as it was before changes were made. This is in contrast to the redo log which is a record of the insert/update/deletes.
Undo = Rollback segments. They both are really one in the same. Undo data is stored in the undo tablespace. Undo is helpful in building a read consistent view of data.
Wordpress Updgraded
I upgraded Wordpress to version 2.8. Let me know if you see any issues.
Tags: wordpress
Killing Oracle Sessions
Do you need to kill an Oracle session? There are several way to do this. In SQLPlus simply run:
ALTER SYSTEM KILL SESSION 'sid,serial#';
You can also run:
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
You can also kill sessions from the UNIX level with this command:
kill -9 spid
This is dangerous and should not normally be done. Pick the wrong OS process and you could crash your instance. There may also be time you need to bulk kill Oracle sessions. Simply selecting again v$session will get you the data needed.
select 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' IMMEDIATE;' from v$session where username = 'DWUSER' and STATUS = 'ACTIVE';
Then just run the output in SQLPlus. Again be sure you are only killing the sessions you want. Killing the wrong session could terminate your database instance.
How To Repair UNDO log corruption
Today I had a corrupted segment in my UNDO tablespace. While I have had datafile corruption before I have not seen UNDO segment corruption. This fix could have been a more drastic, but as it was it ended up being pretty easy.
I first found out there was a problem when one of my databases emailed me that “ORA-01034: ORACLE not available , ORA-27101: shared memory realm does not exist”. Well that is never good. I logged into the server and sure enough the database was down. I logged into SQLPlus and started the database backup. Everything start fine so I went to look for the cause. The first place I went look is the alert log. As I expected I saw errors like this one.
ORA-00600: internal error code, arguments: [kdourp_inorder2], [44], [0], [48], [44], [], [], []
No DBA ever likes to see ORA-00600. It’s too generic tell exactly what is going on and generally means something simple broke or something is terribly wrong. Now that the database was backup I started to tail the alert log and noticed more ORA 600 errors, then followed by ORA-00474: SMON process terminated with error and PMON: terminating instance due to error 474. Ouch database crashed again. It was time to really dig into the issue.
Continuing to review the alert log I went back to the earliest point where the ORA-00600 errors started and found these errors scattered in the alert log.
ORA-00600: internal error code, arguments: [kdourp_inorder2], [44], [0], [48], [44], [], [], [] ORA-08007: Further changes to this block by this transaction not allowed Doing block recovery for file 21 block 456408 SMON: Parallel transaction recovery slave got internal error SMON: Downgrading transaction recovery to serial
It looked like I had corruption somewhere. So using this SQL I was able to determine where my issue was:
select segment_name, status from dba_rollback_segs where tablespace_name='undotbs_corrupt' and status = ‘NEEDS RECOVERY’;
My UNDO table space has the corruption. Because I was able to open my database things were looking pretty good. Is I had pending transaction in my UNDO table space things would have been a bit more tricky. My action pla was to get a second UNDO table space up as quickly as possible and take offline the current UNDO tablespace. This will stop the database from crashing.
I created the tablespace with this SQL
create undo tablespace undotbs2 datafile ‘/u02/oracle/oradata/test/undotbs2.dbf’ size 500m;
Alter the database to use the new UNDO tablespace.
alter system set undo_tablespace=undotbs2 scope=both;
Then take the original UNDO tablespace offline
alter tablespace undotbs1 offline;
Next drop the corrupted segment.
drop rollback segment "_SYSSMU22$";
Then finally drop the old UNDO tablespace
alter tablespace undotbs1 offline;
Update data in one table with data from another table
Have you ever need to update data in one table with data from another table? I do all the time. One way I perform the update is with functions that I built. I pass a column into the function and it returns a result that use in my update statement. This works well if I am not updating a large numbers rows. If I am updating a large number of rows this can be pretty slow. So instead I run a statement like this:
UPDATE o1dw.stg_acme a SET (switch_id) = (SELECT switch_id from o1dw.switch_info b WHERE b.SWITCH_IP = a.client_ip and b.SWITCH_GROUP = 'SAC') WHERE EXISTS (SELECT 1 from o1dw.switch_info b WHERE b.SWITCH_IP = a.client_ip and b.SWITCH_GROUP = 'SAC')
This method is much more efficient. This update takes about 12 minutes on 3.5 million rows.
Old MetaLink link to be Retired
The old MetaLink URL will be retired. You can no longer access MetaLink via metalink.oracle.com.
Oracle Global Customer Support is pleased to announce that later this year My Oracle Support will be upgraded to enable even more Oracle customers to use Oracle’s next generation support platform, and Classic MetaLink will be retired.
My Oracle Support
1) Click the “Settings” link in upper right hand corner
2) Select “Account & Privileges”
3) Click the pencil icon next to Username/E-mail
Upgrade Oracle to 11.1.0.7
My only 11G instance was running 11.1.0.6 and needed to be upgrade it to 11.1.0.7 to fix a couple of bugs. Apparently this patchkit also includes some new functionality. Before starting the upgrade I needed to do a bit of housekeeping. This instance is relatively small so I performed a full database backup as well as a full database export. I also created a pfile for good measure.
create pfile from spfile ;
I like to have a list of invalid objects to I can compare the before and after.
select object_name, owner, object_type from all_objects where status like ‘INVALID’;
Next I checked the prerequisites for this upgrade. The time_zone check is a very important check that needs to be made.
select version from v$timezone_file;
I am running version 4 so I am okay to upgrade. Check out MetaLink Note 568125.1 if you are running anything other than version 4. Next I ran the script utlu111i.sql in the $OH/rdbms/admin directory. It is designed to let you know if there are issues to address prior to the upgrade. Next I shutdown my Oracle instance and performed the upgrade through the GUI installer. All went well. Then it was time to upgrade my instance.
sqlplus /nolog startup upgrade spool upgrade.log @?/rdbms/admin/catupgrd.sql spool off
This process took about 90 minutes to run so plan enough time for your upgrade. The script was run without errors. The final step of the script shutdown the database. I started the database normally, then shut it down normally, then started it up again normally. I generally take these extra steps just to ensure everything looks okay. To check the post-upgrade status this script is helpful
@?/rdbms/admin/utlu111s.sql
Next I needed to fix some invalid object which I did with this script.
@?/rdbms/admin/utlrp.sql
The final step to the upgrade process is to take a full database backup. Now I am fully upgraded to the latest version of Oracle. To check my version I ran.
select * from v$version
And then
select comp_name,status,version from dba_registry;
ORA-12518 TNS listener could not hand off client connection
Have you ever seen the error “ORA-12518 TNS listener could not hand off client connection”? I did yesterday and it was a pretty simple fix.
It turns out the processes for a particular instance was set too low. You can see how many processes are running via this command:
select count(*) from v$process;
If you want to see what your threshold is set to run:
show parameter processes;
To change the number of processe run this command:
ALTER SYSTEM SET processes=250 SCOPE=spfile;
Unfortunately this requires you to restart the database for the new value takes affect. To see the new value in the spfile you can run this command:
SELECT value FROM v$spparameter WHERE name='processes';
Disable Oracle’s password expiry
In 11G Oracle started setting the password expiry by default. This was not the case for earlier versions of Oracle. I understand that additional security benefit, but this is annoying for application logins. I have disabled this for the default profile. Here is how.
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Oracle to acquire SUN?
Is Oracle going to buy Sun?
On April 20, 2009, Oracle announced it has entered into an agreement to acquire Sun Microsystems (Sun). The proposed transaction is subject to Sun stockholder approval, certain regulatory approvals and customary closing conditions. Until the deal closes, each company will continue to operate independently, and it is business as usual.
The acquisition combines best-in-class enterprise software and mission-critical computing systems. Oracle plans to engineer and deliver an integrated system—applications to disk—where all the pieces fit and work together so customers do not have to do it themselves. Customers benefit as their system integration costs go down while system performance, reliability and security go up.
Sure looks like Sun will now be an Oracle product.
