Jul 21, 2008
Data pump export error ORA-39095 with FILESIZE and PARALLEL clauses
Oracle provides nice data pump features to move data around for large database. Taking the whole database to a single dump causes problem in handling big files and managing disk efficiently. With this good intention in mind, I tried to export our large data and objects in smaller chunks.
I issued the following command, which ended up with errors, later discovered that it triggered a bug -
expdp user/pass \
EXCLUDE=TABLE:"LIKE'ACTIVITY'" \
DUMPFILE=EXPDIR:prod-20080721_01%U.dmp,EXPDIR:prod-20080721_02%U.dmp \
FILESIZE=4G \
PARALLEL=2 \
JOB_NAME=PROD_20080721
LOGFILE=EXPDIR1: split-dump.log
This means - export the user schema without the table ACTIVITY, create several dump files with maximum size 4 GB (size would help me to move data around) and create as many dumpfiles as needed with the convention and perform the task in 2 threads.
It supposed to generate files like follows -
prod-20080721_0101.dmp
prod-20080721_0201.dmp
prod-20080721_0102.dmp
prod-20080721_0202.dmp
...
and so forth as needed, because the the substitution variable %U, which indicates that multiple files (01 to 99) would be generated using the specified filename as a template.
Well, I thought that I would go and grab a cup of coffee, and by that time the export job would be completed. But the reality was different, just after few minutes, I saw this error message on my console -
ERROR: ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes Job "USER"."PROD_20080721" stopped due to fatal error at 00:37:29
What I did?
Like others, I googled. Unfortunately, I was not satisfied with the documents I found at that time telling about the error. All the available documents were saying that this error only results when the specified dump file size is not enough to export all the data - which was not the case with me!
Again with a fresh mind, I googled to understand the unexpected problem – but did not find any satisfactory explanation. There are lots of documents saying the possible solutions for this error but did not find any case like my one. Interestingly noticed, in some blogs, people are interested to give solution without specifying the problem :-)
Anyways, this cause could be appropriate if I had not mentioned the substitution variable %U. My dump size was about 30 GB and as I mentioned 2 dump files with substitution variable %U, export process would have been able to generate 2*99 files with 4 GB file size each. So, the “ORA-39095: Dump file space has been exhausted” did not make sense to me in this case. FYI, there were around 80 GB free space left on the DUMPDIR location – more than enough for the task.
Now it's time for the master attempt. I searched in metalink.oracle.com to find any possible bug related to this. Bingo! It was there!!
Here are few lines from Metalink -
Symptoms
When trying to export using PARALLEL clause with one dumpfile, or a number of dumpfile less than the parallelism value.
The export may fail with the following error:
ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes
Cause
When using one dumpfile -or a number less than parallelism value-, several slave processes wait for the file locked by the other process to write. And so we are not benefiting from the parallelism anyway.Sometimes the slave process locking the file does not release the lock after finishing as it's supposed to release the lock when the dump process ends, and it'll not end cause the other processes are waiting to write to the file.
Possible Solutions
1- Use a number of dumpfiles equal to, or more than the parallelism value.
2- Avoid using PARALLEL clause.
So, this was an obvious bug I was facing. I concluded the workaround as follows-
* I have to mention many files – the number of files must be a bit more than the parallelism mentioned. For example, if I mention parallelism 2, then I should mention 4 files with the substitution variable %U
* When the problem encounters – I could add dump files and continue the job
* I should not use the parallelism clause
To accomplished the half done task, I went for the option 2. Data pump feature allows to restart a job and add additional stuffs in the middle of a job – a nice one! It involves few simple commands -
1. I queried database to find the job status -
SQL> select job_name, state from dba_datapump_jobs;
JOB_NAME STATE
------------------------------ ------------------------------
PROD_20080721 NOT RUNNING
2. I had to attach the job with the export process
# expdp user/pass attach=PROD_20080721
3. I added an additional file
Export> ADD_FILE=EXPDIR:prod-20080721_03%U.dmp
Export> CONTINUE_CLIENT
However, I would recommend to use the following simple version (without PARALLEL clause) of export to avoid troubles. This would work for database up to around 290 GB size -
expdp user/pass \
exclude=TABLE:\"like\'ACTIVITY\'\" \
DUMPFILE=EXPDIR1:prod-20080721_%U.dmp \
FILESIZE=3G \
JOB_NAME=EXPORT_0721 \
LOGFILE=EXPDIR1:splited-dump.log
That was the story of using oracle expdp with filesize and parallel clause! Please let me know if this helps.
Jun 30, 2008
Bulk Delete: Object analysis and High water mark adjustment in Oracle
Today I did that kind of stuffs to clean one summary table - I deleted approximately 1.5 million records!
So, what else need to do after that bulk deletes? Well, there are several useful things that need to considered for the performance.
* Reset the Hight water mark of the table
* Analyze the table to collect stats
* Analyze/Rebuild index
High Water Mark
The High water mark is the maximum space ever claimed/touched by an object (i, e. table or index ). Above the High water mark - all the blocks are available for others.
When a table is created, at least one extent (by the way, extent is a collection of contiguous blocks, and block is the smallest unit to store data) is allocated to the table. When rows are inserted into the table, the High water mark will be bumped up as more blocks will be needed to store newly inserted data. Now, for example, I have 20 million records in a table which claimed 1000 extents to store that data. So in that case, the high water mark is set up to that 1000 extents. Now assuming that I have deleted 15 million records from the table later on remaining 5 million in that table. Obviously the remaining 5 million records won't need that whole 1000 extents claimed/occupied earlier. Oracle does not fee up the space - so the high water mark will be up to the 1000 extents. Why Oracle keep the High water mark as it is - that's another story.
So, The High water is an indicator within a data segment. It demarcates the claimed used-unused space, or space that has been formatted for user data.
The space below this mark cannot be released even if there is no user data in it. But there are ways to tell Oracle to set the high water mark if we want to release unused space up to the High water mark.
Segment shrink reset High water mark for the object. It is an online operation, which means that the table will be open to the database user and DML statements are allowed at the same time.
So, I went ahead and did the bulk deletes for the summary tables. Before the deletes - I checked blocked occupied currently the index clustering factor
SQL>SELECT 'FPS_HISTORY', COUNT( DISTINCT( SUBSTR( dbms_rowid.rowid_to_restricted(ROWID,1),1,8))) AS BLOCKS FROM FPS_HISTORY;
FPS_HISTORY BLOCKS
----------- -------------------------------
FPS_HISTORY 207798
SQL> select index_name,clustering_factor from user_indexes where table_name like 'FPS_HISTORY';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ ---------------------------------------------------
FPS_ARC_DT_I 829036
FPS_HISTORY_ALL_I 1465327
After the deletes -
SQL> SELECT 'FPS_HISTORY', COUNT( DISTINCT( SUBSTR( dbms_rowid.rowid_to_restricted(ROWID,1),1,8))) AS BLOCKS
FROM FPS_HISTORY;
FPS_HISTORY BLOCKS
----------- -----------------------------
FPS_HISTORY 20177
The block occupied by table data now is less as I deleted. But the index clustering factor did not change - so I analyzed indexes.
SQL> analyze index FPS_ARC_DT_I compute statistics;
SQL> analyze index FPS_HISTORY_ALL_I compute statistics;
SQL> select index_name,clustering_factor from user_indexes where table_name like 'FPS_HISTORY';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ --------------------------------------------------
FPS_ARC_DT_I 80800
FPS_HISTORY_ALL_I 88830
After the deletes when I analyzed/rebuild indexes, the clustering factor reduced significantly! It would help a lot for query performance.
Now, I did shrink the table to set High water mark and release space. I issued the following 2 commands to do that -
SQL> ALTER TABLE fps_history ENABLE ROW MOVEMENT;
SQL> ALTER TABLE fps_history SHRINK SPACE;
Jun 24, 2008
Tablespace Block size and SQL performance
-----BEFORE, TABLE AND INDEXES ARE ON 16K BLOCK
SQL> DELETE FROM lb_worklist WHERE form_id= 'TL-TRCNY-68J3B9EH53' AND login_id= 77865;
1 row deleted.
Elapsed: 00:00:00.01
Execution Plan
------------------------------
Plan hash value: 492469788
------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------
| 0 | DELETE STATEMENT | | 1 | 63 | 4 (0)| 00:00:01 |
| 1 | DELETE | LB_WORKLIST | | | | |
|* 2 | INDEX RANGE SCAN| LB_WORK_FRM_LOGIN_I | 1 | 63 | 3 (0)| 00:00:01 |
------------------------------
Predicate Information (identified by operation id):
------------------------------
2 - access("FORM_ID"='TL-TRCNY
Statistics
------------------------------
0 recursive calls
8 db block gets
3 consistent gets
0 physical reads
1120 redo size
842 bytes sent via SQL*Net to client
779 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT * FROM lb_worklist WHERE login_id = 77865 and test = 0;
5618 rows selected.
Elapsed: 00:00:00.29
Execution Plan
------------------------------
Plan hash value: 3804693804
------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------
| 0 | SELECT STATEMENT | | 149 | 16241 | 104 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| LB_WORKLIST | 149 | 16241 | 104 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | LB_WORKLIST_LOGIN_TEST | 149 | | 3 (0)| 00:00:01 |
------------------------------
Predicate Information (identified by operation id):
------------------------------
2 - access("LOGIN_ID"=77865 AND "TEST"=0)
Statistics
------------------------------
0 recursive calls
0 db block gets
2203 consistent gets
0 physical reads
0 redo size
516047 bytes sent via SQL*Net to client
4583 bytes received via SQL*Net from client
376 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5619 rows processed
---- AFTER, TABLE AND INDEXES ARE ON 2K BLOCK
SQL> DELETE FROM lb_worklist WHERE form_id= 'TL-TRCNY-68J394SX3W' AND login_id= 77865;
1 row deleted.
Elapsed: 00:00:00.00
Execution Plan
------------------------------
Plan hash value: 492469788
------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------
| 0 | DELETE STATEMENT | | 1 | 63 | 5 (0)| 00:00:01 |
| 1 | DELETE | LB_WORKLIST | | | | |
|* 2 | INDEX RANGE SCAN| LB_WORK_FRM_LOGIN_I | 1 | 63 | 4 (0)| 00:00:01 |
------------------------------
Predicate Information (identified by operation id):
------------------------------
2 - access("FORM_ID"='TL-TRCNY
Statistics
------------------------------
0 recursive calls
10 db block gets
4 consistent gets
0 physical reads
1108 redo size
842 bytes sent via SQL*Net to client
779 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT * FROM lb_worklist WHERE login_id = 77865 and test = 0;
5619 rows selected.
Elapsed: 00:00:00.29
Execution Plan
------------------------------
Plan hash value: 3804693804
------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------
| 0 | SELECT STATEMENT | | 149 | 16241 | 115 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| LB_WORKLIST | 149 | 16241 | 115 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | LB_WORKLIST_LOGIN_TEST | 149 | | 5 (0)| 00:00:01 |
------------------------------
Predicate Information (identified by operation id):
------------------------------
2 - access("LOGIN_ID"=77865 AND "TEST"=0)
Statistics
------------------------------
0 recursive calls
0 db block gets
2802 consistent gets
0 physical reads
0 redo size
516145 bytes sent via SQL*Net to client
4583 bytes received via SQL*Net from client
376 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5619 rows processed
However, Tables and Indexes on larger block size might increase the possibility of buffer cache latch contention. It completely depends on business logics and application design.
Jun 19, 2008
Database Replication for Business Continuation
I want to start by giving a definition of Database Replication - Database replication is the creation and maintenance of multiple copies of the same database - all database is synchronized possible in real time. That means, when changes occur in one database, it will be replicated to other database(s) instantly or some acceptable maximum time delay.
Now the question comes, why do we need such thing? You are thinking right, Database Replication is a part of Business Continuity plan - a way to recover from any malicious attack or recover from disaster. Anyways, we do also have this type of setup for our Applications.
We actively maintain two Sites - you may call data centers. For example, they are addressed as PRI and SEC Sites. Each Site contains some equipments like -
* Database Server
* Database Backup Server
* Web Servers
* Monitoring Server
* EMC Storage
* Load Balancer etc
Each Site has this type of setup and can independently runs application. But what happens if one Site become useless by any disaster or for any outage? That’s why we have another Site to continue the business. Usually the usages of multiple Sites is not only limited for recovering from disaster but also sharing loads and dividing data between data centers.
Now the necessity comes for a Database Replication product which will keep the users’ data in sync among multiple Sites’ Database servers so that we could switch to other site at any time.
From last few months, we are looking for an alternative database replication mechanism that would suit best for us. We have been running SharePlex for data replication purpose for the last 3 years. By this time, our system usages increased more than twice in just last year. Now we need more stuffs than what SharePlex is providing right now. SharePlex was good with our basic requirements. We are not comfortable with the performance and other configuration issues now a days. So, we are planning to explore new available technologies and most likely will switch over to a better one.
We found two alternatives to be considered for us -
1. Oracle Streams
2. GoldenGate Software
Earlier, we did lots of experiments with Oracle Streams for about 1.5 months. You can guess, there are lots of outcomes. I might tell about Oracle Streams another day. I am writing now to tell a bit about my GoldenGate experience!
GoldenGate Software (GGS) is a popular one used for data replication and keep several database in sync. They are supporting a number of databases including Oracle, Teradata, DB2, Sybase, SQL Server. It seems that GGS are in market for long time, probably before Streams introduced by Oracle, now they are running version 9.5.
How GGS works? The summary is - GGS has EXTRACT process which captures changes in source database. The changes are kept in files called TRAIL. The changes can be directly sent to the target/destination database’s REPLICAT process to apply the changes in destination database. GGS EXTRACT reads data from oracle online redo log file - so it’s independent of oracle process. I forgot to mention, GGS has several processes, MANAGER is one of the important process which managers all other stuffs.
So, this is how Database Replication plays important role in a system.
May 19, 2008
Understanding Database Character Set and Encoding Techniques
What’s wrong with this?
Windows uses a different character set and encoding techniques than we do in our applications. We can not convert some special characters available in windows and other character sets. For example, if users write a note using single quote, double quote, currency symbol and things like these, we can not convert those things and store it properly as it is in the database for our current encoding techniques. And next time when users are provided this data - they see some strange symbol/garbage instead.
Stop! If you are thinking what this encoding and character sets are - I would highly recommend you to read this nice article http://www.joelonsoftware.com/articles/Unicode.html
Anyway, currently we are using West European character set in the database. We need to change it to Unicode in order to support special characters from users’ input.There are ways to change character set of database - it’s not a very hard job for a DBA. But there are catches. What will happen for those special characters/symbols those already inserted?
To do the changes in database character set, oracle has some procedures like scanning the current data (with the Oracle CSSCAN tool) to make sure that the data is stored properly in the current character set. When we did this step to check the compatibilities, we had been reported that the we would be loosing some characters during the character set conversion in database. Oracle calls this - Loosy Conversion!!
I was trying to understand the actual behavior of these character encoding/conversion. For this, I went through hundreds of pages to understand the encoding mechanisms and the explanations appropriate for our applications. The findings below -
* ASCII character set are defined in the decimal range from 0 - 127
* WE8ISO8859P1 character set which we are using as database character set, the special characters are defined in the decimal range from 160 to 255
* WE8MSWIN1252 character set are being used by windows users, defines the special characters in the decimal range 128 to 255
* As we do not set any NLS_LANG parameter in jdbc template, it is default UTF (UTF-8/UTF-16, depends on version)
* As we do not set NLS_LANG environment variable, it is default “AMERICAN_AMERICA.US7ASCII” (LANGUAGE_TERRITORY.CHARSET). However, it is only applicable if we interact with sqlplus or when other client’s NLS is not set.
** Case-1 **
Now, for example, user is inserting the copyright character (decimal value 169) from ms word. There will be a series of conversions like -
It will first converted from windows character set WE8MSWIN1252 to UTF8, and finally when it will be inserted to database using jdbc, will be converted to database character set WE8ISO8859P1.
I simulated this behavior setting NLS_LANG to “AMERICAN_AMERICA.UTF8″ (because this is the default settings for jdbc)
SQL> select dump(convert(convert(’©’,'UTF8′,’WE8MSWIN1252′),’WE8ISO8859P1′,’UTF8′)) from dual;
DUMP(CONVERT(CONVERT(’©’,'UTF8′,’WE8MSWIN1252′),
————————————————
Typ=1 Len=1: 169
The value 169 which is the decimal code for copyright symbol will be inserted into database as it is. When selecting the character from database, the original character is retrieved in the following fashion.
SQL> select convert(convert(convert(’©’,'UTF8′,’WE8MSWIN1252′),’WE8ISO8859P1′,’UTF8′),’WE8MSWIN1252′,’WE8ISO8859P1′) from dual;
CON
—
©
** Case-2 **
If we do the same simulation for Euro Symbol which is represented decimal value 128 in windows character set, the actual inserted value in database will be decimal value 191 instead of 128. This is because, the database character set does not have any representation for value 128 (WE8ISO8859P1 only understands special char value in the range 160-255) and for this it replace this value with a replacement character - inverted question mark, decimal value 191.
SQL> select dump(convert(convert(’€’,'UTF8′,’WE8MSWIN1252′),’WE8ISO8859P1′,’UTF8′)) from dual;
DUMP(CONVERT(CONVERT(’¿’,'UTF8′,’WE8MSWIN1252′),
————————————————
Typ=1 Len=1: 191
Eventually when we retrieve this value from database, we see the inverted question mark from UI.
SQL> select convert(convert(convert(’€’,'UTF8′,’WE8MSWIN1252′), ’WE8ISO8859P1′,’UTF8′),’WE8MSWIN1252′,’WE8ISO8859P1′) from dual;
CON
—
¿
So, my understanding is, the special characters within the range 128 to 159, inserted from windows clients are not recognizable by the database character set WE8ISO8859P1 and thus replaced by replacement character (inverted question mark) in database. And these characters are NOT recoverable also. That’s why, we would have loosy conversion from character set WE8ISO8859P1 to AL32UTF8.
May 11, 2008
Database Replication with GoldenGate software
We are looking for an alternative database replication mechanism that would suit best for us. We are exploring new technologies available to serve replication purpose.
Now, we found two alternatives to be considered as we need more control for an "Active-Active" mode -
1. Oracle Streams
2. GoldenGate Software
I did several extensive experiments with Oracle Streams for about 1.5 months. You can guess, there are lots of outcomes. This post is for telling a bit about my GoldenGate experience!
GoldenGate Software (GGS) is a popular one used for data replication and keep several database in sync. They are supporting a number of database including Oracle, Teradata, DB2, Sybase, SQL Server. It seems that GGS are in market for long time, probably before Streams introduced by Oracle, now they are running version 9.5.
The basic concepts of GGS are - GGS has EXTRACT process which captures changes in source database. The changes are kept in files called TRAIL. The changes can be directly sent to the target/destination database's REPLICAT process to apply the changes in destination database. GGS EXTRACT reads data from oracle online redo log file - so it's independent of oracle process. I forgot to mention, GGS has several processes, MANAGER is one of the important process which managers all other stuffs.
My first "one way" replication test configuration (obvious these are not all for production db) with GGS were as follows -
Source Neptune: Create EXTRACT which will capture changes and send the changes to remote host's TRAIL files
USERID ggs, PASSWORD ggs
RMTHOST saturn, MGRPORT 7820
RMTTRAIL /export/home/oracle/ggs/dirdat/rt
TABLE DEV.LOGIN;
ADD RMTTRAIL /export/home/oracle/ggs/dirdat/rt, EXTRACT extprod, MEGABYTES 50
Destination Saturn: Create REPLICAT which will read changes from TRAIL files and apply the changes in current database
EDIT PARAMS repprod
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/REPSOE_DSC.rpt, PURGE
USERID ggs, PASSWORD ggs
MAP DEV.*, TARGET DEV.*;
ADD REPLICAT repprod, EXTTRAIL /export/home/oracle/ggs/dirdat/rt, NODBCHECKPOINT
May 4, 2008
Oracle: Index auto dropped while importing. Where my index gone?
We have multiple production servers. So, taking a dump from one server and import it to another is quite common for initial synchronization. Like this, one day, one of my colleague found that schema on p01 database had one less index than s01 database. One index was not created in p01 while imported table "TITLE". I investigated and found that it happened for an interesting feature of oracle!
The "TITLE" table has primary key ID. You know, when primary key is created, one system generated index is created with the primary key column to enforce the constraint.
During the release 7.2, we created a composite index named "TITLE_ID_TITLE" on table "TITLE" in database server s01 using two columns including the primary key column "ID" and another column, "TITLE" (TITLE table has a column named TITLE).
Now, when oracle was importing indexes for table TITLE on p01, it found that the composite index "TITLE_ID_TITLE" is enough to enforce the primary key - so it automatically discarded the system generated index which was created with the primary key.
That's the ghost story behind this!
May 2, 2008
SpringSource launches new Application Platform
I was reading the overview of it and was amazed by reading the following development and production benefits. It is our dream to modularize the big applications that we are developing and do the updates in production server in real time without shutting down the system for long time.
I am just pasting the benefits SpringSource are talking about -
Production Benefits
- Real time application and server updates
- Better resource utilization
- Side by side resource versioning
- Longer server uptime
- Run new and old web application
- Faster iterative development
- Small server footprint
- More manageable applications
- Greater code reuse
- Resolve transitive dependencies
http://blog.springsource.com/main/2008/04/30/introducing-the-springsource-application-platform/
The overall introduction is given on the official site of SpringSource -
http://www.springsource.com/web/guest/products/suite/applicationplatform
I have the strong desire to explore more on this!