Archive for the ‘DBA Thoughts’ Category:
Finding Large Directories
Recently I was looking to clear some space on one of my Oracle servers. I looked for large files with this command:
find . -type f -size +100000k -exec ls -lh {} \; | awk ‘{ print $9 “: ” $5 }’
This looks for files over 100MB, but other than datafiles there were very few files found. I knew I needed to look for large directories. Directories that had a large amount of small files. This command worked nicely:
du -h / | grep ^[1-9][0-9][0-9.]*G | sort -rn
From there I was able to find the directories that I needed to clear out.
Remotely Disconnect a Terminal Services Session
okay admittly this is not Oracle related, but I have had to remotely disconnect a terminal services session more than one time. Needed to do it again today so I thought I would post the simple instructions.
So first you need to find the session that you want to kill. From a Windows command prompt type:
qwinsta /server:yourservername
So in my case I typed: qwinsta /server:10.10.11.125
Now I want to kill session #2 so I type:
rwinsta 2 /server:10.10.11.125
Pretty simple. Now I can term serv into the Windows server.
Oracle Enterprise Manager 11g
Oracle Enterprise Manager 11g is out and looks promising. Oracle Enterprise Manager allows you to monitor your databases from a single web based interface. You can see performance matrix as well as deploy patches and upgrades. I have only used EM to manage databases, but you can also use it for applications, middleware, hardware, as well as the OS.
EM is the base application and from there you can add different “packs” including
- Change Management Pack
- Configuration Management Pack
- Data Masking Pack
- Diagnostic Pack
- Provisioning Pack
- Tuning Pack
Compressing Partitioned Tables
I recently wrote a script to compress certain partitions in a table. Basically any partition that was loaded and analyzed I wanted to move to a new tablespace and compress the data.
Oracle RAC Project Starts Tomorrow
A I previously described I will started a new Oracle RAC project. If everything goes as planned I will get my hands on the 2 node setup tomorrow.
I have been busy researching 3PAR and enhancements to 11G. I will be installing 11G and implementing all new data warehouse ETL code.
Oracle Storage Research
I have been heavily researching Oracle storage best practices since starting my new Oracle RAC project. My new setup will specifically be for replacing a data warehouse that is currently 4.8T. The new data warehouse will start out significantly smaller. An entirely new ETL process will be deployed an then the historical data will be move over to it.
The new setup will be a (2) node RAC installation running Oracle 11G. The storage for this project will be a SAN and utilizing Oracle’s ASM architecture. Specifically we will be running 3Par’s InServ F400. We will be starting out with (40) 450GB 15K drives. We will be running dual 4 GBIT/s fiber channel adapters. Our SAN switches will be running 2 GBIT/s ports with an upgrade path to use 4 GBIT/s switch later.
Oracle RAC Project
I will be starting a new project where I will be using Oracle RAC 11G. The setup will be a two node RAC setup running on 3PAR’s F400
I will try to document as much as I can here on Square DBA, so stay tuned . . . .
SQL Worksheet gone in 11g client
I just reinstalled my PC and installed a clean OS. I do this every once in a while. I find just formatting and starting over is better that trying to clean the machine up.
My last load consisted of the Oracle 10g client which came with SQL Worksheet. I like SQL Worksheet because it’s not a full dumbed down GUI and yet it gives me a few point-n-click features. Well it looks like Oracle decided to dump a standalone version of SQL Worksheet for embedding it in SQL Developer. I am not a fan.
I feel confined with this new version. I can launch multiple sessions but they all appear within the SQL Developer framework. I was also viewing object using the Enterprise Manager client, well that is gone too. I know I can view all of my database object in SQL Developer, but I don’t like the change.
So now I am using Enterprise Manager Grid Control and SQL Developer. I will use these tools instead of installing the 10g client since Oracle is obviously going in that direction, but I am not doing it willingly.
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.
Upgrade an Oracle 10g database to Oracle 11g
Most of my databases are Oracle 10G instances. I have one instance that is Oracle 11G, but that was a fresh install. I have a test 10G database with some old production data in it. I am going to up upgrade it to 11G and I will post my experience. After the upgrade I will be setting up a test RMAN configuration. The plan is to work on backup and recovery. A co-worker and I will practice breaking a test database in various ways and trying to recover the database. It should be fun, stay tuned.
