Archive for the ‘DBA Thoughts’ Category:
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.
ORA-01555 Snapshot too old
Do you ever see the Oracle error “ORA-01555 Snapshot too old (Rollback has been overwritten)”? It is a pretty common error, but one I don’t see much anymore.
ORA-01555 error means the rollback segment that your long running query needed has been overwritten. Today I was helping out a friend who has been getting this error trying to execute a delete command. I directed him to a pretty good article on the subject over at orafaq.com. Take a look at the article, it has a lot of good information on properly dealing with the UNDO tablespace.
Oracle DBA Interview Questions
I had a friend ask me for some very basic Oracle DBA interview questions. I thought I would share.
1. Explain database instance ?
A database instance (server) is a set of memory structures and background processes that access a set of database files. The memory structures are used to store most queried data from database. This helps us to improve database performance by decreasing the amount of I/O performed against data file. The process can be shared by all users.
2. What is parallel server?
Multiple instances accessing the same database (Only in Multi-CPU environments).
3. What is Schema ?
The set of objects owned by user account is called the schema
4. What is an Index ? How it is implemented in Oracle Database ?
An index is a database structure used by the server to have direct access of a row in a table.An index is automatically created when a unique or primary key constraint clause is specified in create table command
5. What is a clusters? Explain
Group of tables physically stored together because they share common columns and are often used together is called Clusters.
6. What is a cluster key ?
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stores only once for multiple tables in the cluster.
7. What are the basic element of an oracle Database ?
It consists of one or more data files one or more control files two or more redo log files. The database contains multiple users/schemas one or more rollback segments one or more tablespaces Data dictionary tables User objects (tables,indexes,views etc) The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, redo log buffers,Shared SQL pool)
SMON
PMON
LGWR
DBWR
ARCH
CKPT
RECO
Dispatcher
User process with associated PGA
8. What is deadlock ? Explain.
Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises.
In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.
9. What is SGA ?
The System Global Area in a Oracle database is the area in memory to facilitates the transfer of information between users. It holds the most recently requested structural information about the database.
10. What is Shared SQL pool ?
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.
11. What is meant by Program Global Area (PGA) ?
It is area in memory that is used by a Single Oracle User process.
12. What is a data segment ?
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.
13. What are the factors causing the reparsing of SQL statements in SGA ?
Due to insufficient Shared SQL pool size
Difference between Active Dataguard and Logical Standby
What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?
Active dataguard is mostly about the physical standby.
- Use physical standby for testing without compromising protection of the production system. You can open the physical standby read/write – do some destructive things in it (drop tables, change data, whatever – run a test – perhaps with real application testing). While this is happening, redo is still streaming from production, if production fails – you are covered.
- Use physical standby for reporting while in managed recovery mode. Since physical standby supports all of the datatypes – and logical standby does not (11g added broader support, but not 100%) – there are times when logical standby isn’t sufficient.
- It also permits fast incremental backups when offloading backups to a physical standby database
