Validating backups and database files using RMAN

The most critical tasks for every DBA are database backup and recovery procedures. Knowledge of all Oracle backup and recovery scenarios is practically the most essential skill. For almost every problem there is some solution, but if you can't restore/recover data you are in deep trouble. To avoid possible problems it is important to regularly monitor backups and to perform validations using various...

Read more →

New design and best wishes...

First of all to wish Merry Christmas and Happy New Year to all readers of this blog. I wish you even more success in your business and family life in the year 2010. About new design... I had some free time to play with my blog so I decided to change design. Implementation of new design took me more time then I expected, because of my humble CSS/HTML knowledge, but at the and I am pleased with new...

Read more →

Point in Time Recovery fails with ORA-01841 on Oracle 10.2 SE

Yesterday I've decided to do point in time recovery for Oracle 10.2.0.4 SE database. I've used the same script that was working before on other databases - but my operation failed with ORA-01841 error. After spending some time on Google I found that this error was mostly related to illegal year entered. ORA-01841: (full) year must be between -4713 and +9999, and not be 0 Cause: Illegal year entered Action:...

Read more →

Transportable Tablespace Import Fails With ORA-19721

This blog post is linked with post before : Cross-platform migration - large Oracle 9i db from Solaris 64 to Linux 64 SOURCE: Solaris 64bit, Oracle 10.2.0.4 TARGET: Linux 32bit, Oracle 10.2.0.4 I was playing a little with transportable tablespace imports and experienced transportable tablespace import failure with ORA-19721 error. After dropping contents of DATA1 tablespace I've decided to import...

Read more →

Cross-platform migration - large Oracle 9i db from Solaris 64bit to Linux 32bit

Question for this post is - what is the best way to migrate >500GB Oracle 9.2.0.6 database from Solaris 64 to Linux 64 operating system? As imp/exp or impdp/expdp would probably be very slow option better choice is cross-platform migration. In this case we have to upgrade db from 9i to 10g and then migrate tablespaces using cross-platform tablespace conversion with transport tablespace feature. Why...

Read more →

Tracing AUTOEXTEND tablespace

In this blog post I will try to examine how AUTOEXTEND operation can affect on performance. Hemant K Chitale already wrote nice article on this subject : AUTOEXTEND ON Next Size, where he wrote about Oracle "default" Autoextend NEXT size. This topic "Autoextend or not?" from Oracle-L mailing list is great addition to this blog post. You can read there about different usages of AUTOEXTEND option in...

Read more →

Create a Database Link in another user's schema

From documentation: Restriction on Creating Database Links You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in...

Read more →

Cursor_sharing=FORCE causing "ORA-01008: not all variables bound" error on Oracle 10.2.0.4

Today we experienced application failure caused by error: Server Error in '/Complaints' Application. ORA-01008: not all variables bound ORA-02063: preceding line from APPDB Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: SyStem.Data.OleDb.OleDbEXception:...

Read more →

Datafile Recovery After Deleting Datafile On OS

It is not so rare case that due to the user or hardware error you finish with missing critical database file. Then you can thank God that you have fresh backup and needed skills to perform datafile recovery. I'll perform datafile recovery in this small and simple demo case. OS: Linux 32-bit DB: Oracle 10.2.0.4 ARCHIVELOG MODE ON First let's make database backup using RMAN interface: $ rman...

Read more →

SQL Tuning - using USE_HASH hint - dblink issue

Colleague noticed that execution of his job lasted too long so he asked me to check out his query - maybe I could improve performance. So I'll blog about my steps diagnosing and resolving this issue. { I've changed table names because they are not important for this example } Oracle 9.2.0.6.0 Solaris 64 SQL> select count(*) from user1.table1; COUNT(*) ---------- 597259 SQL> select count(*)...

Read more →

Book Review: "Beginning Oracle Database 11G Administration, Iggy Fernandez

Beginning Oracle Database 11G Administration First of all do not let yourself fooled by the title which says 11G. Knowledge that can be obtained by reading this book is applicable to nearly all current versions of Oracle databases. This is an excellent book for any beginner Oracle database administrator. Through chapters Iggy does not enter into too many details but in addition to each chapter...

Read more →

How to install Oracle Client 11g on Windows 7?

I've never liked Windows Vista and I thought that Windows 7 is the same operating system just with new popular name. But lately I've read a lot good comments about Windows 7. To be honest Windows XP is still my favorite OS from Microsoft but I decided to try out Windows 7. So I downloaded and installed Windows 7 RC1. First thing to try out after installing operating system is installation of Oracle...

Read more →

How to edit CRONTAB file on Solaris

I rarely edit crontab files on our Solaris machines but then when I need to add or edit something I always experience the same problem and always searching for an answer 5-10 minutes. This time I will write solution in blog post for quick reminder. Problem is, when I execute "crontab -e" to edit current crontab file I don't get editor window. I get something like this: bash-2.05$ crontab -e 135 My...

Read more →

How to avoid ORA-01652 after executing "alter table compress..."

Strange thing happened to me few months ago on one of our biggest databases. DB Oracle 10.2.0.2.0 on Solaris 9 (64-bit). This database contains several range partitioned tables where date column is partitioning key. During my regular maintenance tasks I usually compress old partitions to save up disk space and speed up querying. For some time everything was working smoothly without any ORA errors...

Read more →

How to create Database Link without editing tnsnames.ora file

A month ago one of our developers needed access to some objects from one schema to another using database link. To enable database link he tried to create entry in tnsnames.ora file but had a problem with insufficient permissions. As a developer he has limited privileges on Unix machines so he can't edit and save tnsnames.ora file. But there is solution for this little problem. You can create functional...

Read more →

ORA-00845: MEMORY_TARGET not supported on this system

After my Linux 32-bit server restarted I've tried to startup database (11.1.0.7.0) and received error: SQL> startup ORA-00845: MEMORY_TARGET not supported on this system Quick look to alert log: WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 457179136 bytes. /dev/shm is either not mounted or is mounted with...

Read more →

Truncating, Rotating, Flushing Listener.log file

Listener Log file audits trail information which enables us to analyze network statistics, client connection requests, service registration events or other stuff. So it is obvious that listener log can contain very useful information for troubleshooting network related problems. This file is usually small and easy to diagnose but in cases of very active databases it could grew very fast to several...

Read more →

Trace session using login trigger

We have a Web application which is connecting to database, executes some statements and closes after a few secs. It is very hard to trace this sessions and catch what were they doing on database without some help. To help myself I wrote simple login trigger which starts tracing on login and inserts information about tracefile into the info table. First to create info table: SQL> CREATE TABLE...

Read more →

Remove OLD trace files

It is not so rare to experience lots of trace files in user or background dump deestinations. For instance Oracle 11.1.0.6 creates trace file almost for every session and after a while directory where trace files are located is loaded with lots of *.trc and *.trm files. As Oracle 11.1.0.6 puts all system and user traces in same directory it is difficult to search for custom 10046 or 10053 traces. Generation...

Read more →

SGA sizing and ASMM

During regular DB checks I've noticed that we had some performance problems due to the insufficient buffer cache pool which was 16 MB. Version of database was 10.2.0.4 with ASMM turned off. As db cache advisor was turned on I checked gathered statistics in v$db_cache_advice table. SQL> select size_for_estimate, size_factor, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READ_TIME from v$db_cache_advice; SIZE_FOR_ESTIMATE...

Read more →