A Quick intro… Take a look at the following issues:

Symptom : Oracle Database Configuration Assistant (DBCA) stalled while creating the database
Symptom : Oracle IAS 10G  stalled during software installation with message “Please wait, this will take a moment”
Symptom : error while loading shared libraries: $ORACLE_HOME/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

If you ever had any of the previous symptons while trying to install Oracle Software,  create a instance / database with DBCA, create a listener or even run sqlplus… then you probably have SELinux mode set to “Enforcing”.

Security-Enhanced Linux (SELinux) is a Linux feature that provides a variety of security policies, including U.S. Department of Defense style mandatory access controls, through the use of Linux Security Modules (LSM) in the Linux kernel. It is not a Linux distribution, but rather a set of modifications that can be applied to Unix-like operating systems, such as Linux and BSD.

Under Oracle Enterprise Linux or Redhat Enterprise Linux, if needed we can switch  SELinux from the default “Enforcing” mode that it is running in, to the “Permissive” mode by running following commands as root user:

[root@server~]# getenforce
Enforcing
[root@server~]# setenforce 0
[root@server~]# getenforce
Permissive

The previous commandes changed the default mode to “permissive” and allows SELinux to continue running, and logging denial messages, but will not deny any operations. If you would like to return to Enforcing mode just run as root:

[root@server~]# setenforce 1
[root@server~]# getenforce
Enforcing

Another way to temporarily disable (0) or enable (1) SELinux is to run one of the following commands:

[root@server~]# echo 0 > /selinux/enforce
[root@server~]# echo 1 > /selinux/enforce
The previous commands are immediate, and will remain in effect until the next reboot. If you want to make “Permissive” mode permanent you must add “enforcing=0″ to the kernel boot line that usually is at /etc/grub.conf file. For instance:

# grub.conf generated by anaconda
#
default=0
timeout=5
splashimage=(hd0,0)/boot/grub/splash.xpm.gz
hiddenmenu
title Enterprise Linux (2.6.18-92.el5PAE)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.18-92.el5PAE ro root=LABEL=/ enforcing=0
        initrd /boot/initrd-2.6.18-92.el5PAE.img
.
.

Instead of editing grub we can configure SELinux by editing the file /etc/selinux/config and choose any of the following modes:

SELINUX=enforcing
or
SELINUX=permissive
or
SELINUX=disabled
After the next reboot the SELinux will comply to the permanent settings that we have choosen above.

Every Oracle Database must have a way of maintaining information that is used to rollback changes to the database. This information consists of records of the actions of transactions, primarily before they are committed. All these records are called undo.

When an user or application issue a rollback statement, undo records are used to undo changes that were made to the database by the  transaction that were not commited.

When we have a large database, most of the long batch transactions that demand lot of undo work or purge operations will increase a lot the undo tablespace size. Depending on the size of these operations your database can have an undo tablespace from few MB to dozens GB.

To save space from an once required large undo tablespace, we may resize it to make it smaller. In my databases sometimes the size of my undo tablespace achieves 20GB.

We can resize the database datafiles with:

SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs01.dbf’ RESIZE 500M;

Most of the times when I try to resize the undo tablespace I encounter ORA-3297 error: file contains used data beyond the requested RESIZE value. This means that some undo information stills stored above the datafile size we want to set. We can check the most high used block to check the minimum size that we can resize a particular datafile. For that we can query the dba_free_space dictionary view.

Another way to set our undo tablespace to the size that we want is to create another undo tablespace, set it the default one, take offline the old and then just drop the big old tablespace.

To check your undo tablespace info issue the following statement:

SQL> SELECT name,value FROM v$parameter WHERE name IN (’undo_management’,'undo_tablespace’);

NAME VALUE
------------------------
undo_management AUTO
undo_tablespace UNDOTBS01

SQL>
SQL> CREATE UNDO TABLESPACE undotbs02
DATAFILE ‘/u01/app/oracle/oradata/mydb/undotbs02.dbf’ SIZE 1024M
REUSE AUTOEXTEND ON NEXT 4096K MAXSIZE 10240M;

SQL> ALTER SYSTEM SET undo_tablespace = ‘UNDOTBS02′;
SQL> ALTER TABLESPACE undotbs01 OFFLINE;
SQL> DROP TABLESPACE undotbs01 INCLUDING CONTENTS AND DATAFILES;
SQL>

With these steps we created a new undo tablespace, set it as the system default undo tablespace and drop the old tablespace including the datafiles.

If you ever tried to start the Enterprise Manager with the command ‘emctl start dbconsole’ and you got stuck with the error:

OC4J Configuration issue. <ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<YOURHOST>_<YOURDB> not found.

.

for instance:

OC4J Configuration issue. /opt/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_db.neoface.pt_ORCL not found.
.

Then the cause may be that Database Control is not configured or misconfigured… This can be verified by checking if the following directories exist in your ORACLE_HOME:

<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<HOSTNAME>_<DBNAME>
<ORACLE_HOME>/<HOSTNAME>_<DBNAME>
.

If those directories do not exist, Database Control has not been configured for the database.

SOLUTION

To configure the enterprise manager database control for your database, issue the following commands as oracle user:

[oracle@neoface oracle]$ export ORACLE_HOME=<ORACLE_HOME>
[oracle@neoface oracle]$ export ORACLE_SID=<ORACLE_SID>
[oracle@neoface bin]$ cd $ORACLE_HOME/bin
[oracle@neoface bin]$ ./emca -config dbcontrol db -repos create

Now just follow the instructions on the screen… you’ll need the SYS, DBSNMP and SYSAUX passwords.

Sometimes you already have the SYSMAN, DBSNMP and SYSAUX accounts, and the previous EMCA command can fail to create the DBConsole repository the error ORA-20001 occurs.

ORA-20001 Sysman Already Exists While Running EMCA
.

The Solution is simple… Drop the existing repository

[oracle@neoface oracle]$ cd $ORACLE_HOME/bin
[oracle@neoface bin]$ ./emca -deconfig dbcontrol db -repos drop

After removing the repository try to created it with the commands I explained previously. After you install the EM repositiry, the DBConsole should be started and you should be able to access the DBConsole using the URL provided at the installation screen.

(UPDATE)

If the previous drop procedure fails and by checking the logs we see an ORA-01031 insufficient privileges, this may be because we don’t have a password file created. Just go to $ORACLE_HOME/dbs and check if we have a file called pwd<sid>.ora. If not, create it…

[oracle@neoface oracle]$ cd $ORACLE_HOME/bin
[oracle@neoface bin]$ ./orapwd file=$ORACLE_HOME/dbs/pwd<sid>.ora password=oracle entries=5

Hope it helps!

Moving, copying or cloning a database from one server to another with different directory structures can be easily accomplished with RMAN. Imagine that you have a database on one node and you want to copy it to another node without shuting down your database and move your datafiles to a different directory structure… This will be demonstrated here by using RMAN.

ASSUMPTIONS

Source Database

• 10.2.0.4 database online (sid neo) at server1 (app)
• archivelog mode is enabled
• db datafiles are in the directory /opt/oracle/oradata/neo2
• database will be backed up online with RMAN to /u01/backup

Destiny Database

• 10.2.0.4 Oracle Home installed without any database running at server2 (mynode2.com)
• db datafiles must be created / moved to different directory: /opt/oracle/oradata/neo
• only the manual backup created at server1 will be moved to server2

AT SERVER1

Logon as oracle user software owner at server1 and set your environment variables. Then open RMAN and backup the source database we want to copy /move / clone.

[oracle@neoface oracle]$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
[oracle@neoface oracle]$ export ORACLE_SID=neo
[oracle@neoface oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@neoface oracle]$ rman target /
RMAN> backup database plus archivelog;

The RMAN backup created the following files at /u01/backup:

cf_NEO_c-1689570411-20090106-00 (control file backup)
back_NEO_675389594_736_1
back_NEO_675389780_737_1
back_NEO_675390018_738_1
back_NEO_675390293_739_1

Copy those 5 backup files to server2

[oracle@neoface oracle]$ scp /u01/backup/back_NEO* root@mynode2.com:/u01/backup/

Create an initialization file (pfile) from the current spfile. Then copy it to the server2.

[oracle@neoface oracle]$ sqlplus “/ as sysdba”
SQL> create pfile from spfile;
SQL> exit;
[oracle@neoface oracle]$ scp /opt/oracle/product/10.2.0/db_1/dbs/initneo.ora oracle@mynode2.com:/opt/oracle/product/10.2.0/db_1/dbs/initneo.ora/

AT SERVER2

Logon at server2 to do the following steps:

• create the OS directories to hold the datafiles and the admin log files and pfile:
• edit the pfile to modify the instance name in parameters like bdump, udump, etc
• change the onwership of pfile to belong to oracle user
• connect to RMAN and startup the database  in nomount mode
• restore the control file from the backup
• mount the database
• validate catalog by crosschecking and cataloging the 4 backups pieces we copied
• rename the datafiles and redolog files and restoring the database

Switch to oracle user and create datafiles directories :

[root@mynode2 root] su – oracle
[oracle@mynode2 oracle]$ mkdir /opt/oracle/admin/neo -p
[oracle@mynode2 oracle]$ cd /opt/oracle/admin/neo
[oracle@mynode2 oracle]$ mkdir cdump udump bdump pfile

[oracle@mynode2 oracle]$ mkdir /opt/oracle/oradata/neo -p

Edit your pfile accordingly your new directory structure:

[oracle@mynode2 oracle]$ vi /opt/oracle/product/10.2.0/db_1/dbs/initneo.ora

Set environment variables and start working on RMAN:

[oracle@mynode2 oracle]$ export ORACLE_HOME=/opt/oracle/product/10.2.0/db_1
[oracle@mynode2 oracle]$ export ORACLE_SID=neo
[oracle@mynode2 oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@mynode2 oracle]$ rman target /
RMAN> startup nomount
RMAN> restore controlfile from ‘/u01/backup/cf_NEO_c-1689570411-20090106-00′;
RMAN> alter database mount ;
RMAN> exit

Now that the database is mounted, we’ll check the correct database SCN from the current log that we’ll use later to recover the database. Take note of your current SCN.

[oracle@mynode2 oracle]$ sqlplus “/ as sysdba”
SQL> select group#, first_change#, status, archived from v$log;

    GROUP# FIRST_CHANGE# STATUS           ARC
---------- ------------- ---------------- ---
     1     336565140     ACTIVE           YES
     2     336415067     CURRENT          NO
     3     336523814     INACTIVE         YES

SQL> exit;

[oracle@mynode2 oracle]$ rman target /

As we only copied to this server the backup we created at the beggining and we did not copy all the backups we had on server1 we must crosscheck the catalog against the OS files. Run the following commands at RMAN prompt :

RMAN> CROSSCHECK backup;
RMAN> CROSSCHECK copy;
RMAN> CROSSCHECK backup of database;
RMAN> CROSSCHECK backup of controlfile;
RMAN> CROSSCHECK archivelog all;

Now let’s catalog the 4 backup pieces that we copy to this server2:

RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675389594_736_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675389780_737_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675390018_738_1′;
RMAN> CATALOG backuppiece ‘/u01/backup/back_NEO_675390293_739_1′;

Next, as we changed the directory of our datafiles we must rename the redologs:
RMAN> ALTER DATABASE  rename file ‘/opt/oracle/oradata/neo2/redo01.log’ to ‘/opt/oracle/oradata/neo/redo01.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo02.log’ to ‘/opt/oracle/oradata/neo/redo02.log’;
RMAN> ALTER DATABASE rename file ‘/opt/oracle/oradata/neo2/redo03.log’ to ‘/opt/oracle/oradata/neo/redo03.log’;

If you use BLOCK CHANGE TRACKING to allow fast incremental backups,  and if you want to move the datafiles to different directory you must disable this feature and enabling it by specifying the new dir:

RMAN> ALTER DATABASE disable block change tracking;
RMAN> ALTER DATABASE enable block change tracking using file ‘/opt/oracle/oradata/neo/block_change_tracking.f’;

This will avoid errors like ORA-19751 and ORA-19750

Now let’s run the script that will restore our database, renaming the datafiles and recovering until the archivelog with SCN 336415067, the current one.

RMAN> run {
set newname for datafile 1 to “/opt/oracle/oradata/neo/system01.dbf”;
set newname for datafile 2 to “/opt/oracle/oradata/neo/undotbs01.dbf”;
set newname for datafile 3 to “/opt/oracle/oradata/neo/sysaux01.dbf”;
set newname for datafile 4 to “/opt/oracle/oradata/neo/data01.dbf”;
set newname for datafile 5 to “/opt/oracle/oradata/neo/index01.dbf”;
set newname for datafile 6 to “/opt/oracle/oradata/neo/users01.dbf”;
set newname for datafile 7 to “/opt/oracle/oradata/neo/streams.dbf”;
set newname for datafile 8 to “/opt/oracle/oradata/neo/data01brig.dbf”;
set newname for datafile 9 to “/opt/oracle/oradata/neo/index02.dbf”;
restore database;
switch datafile all;
recover database until scn 336415067;
}

RMAN> ALTER DATABASE open resetlogs;

I didn’t manage to avoid errors like ORA-01110 and  ORA-01180 at RMAN without using the  “until” clause in the “recover database” sentence instead, like most people use it, as the first instruction after the run command.

Later I’ll upload a video for you easily see it running flawlessly

Btw, HAPPY NEW YEAR!!!!

Less than 1 week to go until UKOUG 2008 in Birmingham ICC… My scheduled sessions are:

Monday
Advanced SQL for PL/SQL Programmers
Oracle Application Express Now and in the Future
Oracle Forms: Features and Future
Oracle 11g/10g Developers: What You Need to Know

Tuesday
Web 2.0 in Buisness or Education . Which is the better marriage?
Application Express Best Practices
Change Change Change
Oracle Forms – The New World
Weird PL/SQL
Database Development with Oracle SQL Developer: An Overview

Wednesday
Oracle SQL Developer: Focusing on a Few Advanced Features
Obtaining and Interpretting Execution Plans using DBMS_XPLAN
Migrating Oracle Forms to SOA and J2EE (Oracle ADF)
Advanced Oracle Application Express Tips and Techniques
Bringing Oracle Designer and CASE repositories back to life – a return on your investment
Visual Data Modeling with Oracle SQL Developer

Thursday
Mastering charts in Oracle Application Express (APEX)
Being Steven Feuerstein
Designing PL/SQL applications
Consolidate your Desktop Databases to Oracle 11g
(Re)Developing a logistic application in APEX in the real world
Securing APEX an application on a Microsoft platform
Performance Tuning ApEx Applications

Friday
Become a Fusion Developer with no Java: A JDeveloper 11g Masterclass (FTMS)
Advanced SQL Features

Hope to see any of you guys there…

Backing up terabyte size databases can be time consuming. When RMAN performs an incremental backup, Oracle must scan all database / tablespace to identify which blocks have been updated since last backup. This, of course, is very time and resource consuming. Fortunatly Oracle 10g brings the possibility to track changed blocks using a change tracking file. Enabling database change tracking adds a small overhead, but it largely improves the performance of incremental backups.

In the next sample code I’ll check the current change tracking status, alter database do enable this feature and add a file to record the changed database blocks

SELECT status FROM v$block_change_tracking;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/oradata/mydb/rman_change_tracking.ora' REUSE;

After this, when block is changed in the database, Oracle will record the fact in the rman_change_tracking.ora file. During incremental backup, RMAN checks this file to see which blocks need the backup instead of checking all the blocks of a tablespace / database. This dramatically reduces CPU cycles and speeds up incremental backup in the process.

Our tracking file will start with 10MB and grows in 10MB increments.

Block Change tracking can be disabled at any time with:

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

We can also rename or move the tracking file at any time issuing the command ALTER DATABASE RENAME FILE.

By reducing the backup time we can achieve the following objectives too:

• Increase the frequency of incremental backups
• Reduce recovery time and mean time to recover

In a distributed database system, each database must have a unique name… a global database name. Global database names uniquely identify a database in the system.

To query a database’s global name issue this instruction:

SQL> SELECT * FROM global_name;

GLOBAL_NAME
-----------
db1.world
.

A global database name is formed by two components: a database name and a domain, as I said before, uniquely identifies it from any other database. An example global name might be mydb.beyondoracle.com. There are 2 parameters that define the global database name. The DB_NAME (no more than 8 chars) and the DB_DOMAIN (Must follow Internet conventions).

To change our database global name, do this:

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO db1.beyondoracle.com

When we change / set the database to use global names (GLOBAL_NAMES parameter set to TRUE), all database links must be valid global names. When you create a database link with global names set to true, the database link will be formed by the database name plus the domain. In this environment Oracle enforces the requirement that the database.domain  portion of the database link name must match the complete global name of the remote database. For instance, if your remote database is called db1.beyondoracle.com then the database link you create to access this remote database, must have the same name… db1.beyondoracle.com!

When using or enabling global names, when we didn’t create the database link with the previous rules I talked about (db_name.db_domain), it’s common getting this error: ORA-02085: database link string connects to string.

When you create a database link we must mention the connect_string that you want to connect to. This connect_string is the tnsnames entry that you have on your database server tnsnames.ora file. For instance when you create this db link:

SQL> CREATE DATABASE LINK db1.beyondoracle.com
CONNECT TO myuser IDENTIFIED BY mypass USING ‘remotedb’;

Database Link Created

Then you must ensure that your database tnsnames.ora has the entry remotedb such as:

# connect string for database
remotedb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.beyondoracle.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db1.beyondoracle.com)
   )
  )
# end of connect string

This tnsnames.ora connect string (remotedb) has nothing common with the database link name, but very often the same name is used. You can call your connect string anything you want!

Anytime you can check your database links by querying user_db_links or all_db_links.

SQL> SELECT * FROM user_db_links;

DB_LINK              USERNAME  PASSWORD  HOST                 CREATED
-----------          --------  --------  -------              -------------
db1.beyondoracle.com myuser              db1.beyondoracle.com 15-09-2008 15:22:56
.

To finish, remember that almost every oracle distributed environment using replication uses global names. There’s only some custom replication processes that don’t need it, but Oracle Replication technology, like Streams for instance, needs global names database compliance.

My prayers and thoughts go to Carl Backstrom and his family. Wherever you are, rest in peace!

Have you ever used backspace key or delete key when writing SQL*Plus statements? Have you ever wanted SQL history like you have on mysql or on any other unix/linux command line ? If yes, this post is for YOU!

Sometimes, some annoying behaviours that developers from big companies don’t watch, drive you crazy! This is the case of SQL*Plus utility.  SQL*Plus is an Oracle command line utility which runs SQL and PL/SQL commands interactively or from a script. Comparing SQL*Plus against other comand line utilities is like comparing MYSQL against Oracle… but in this case MYSQL wins! Even MYSQL has a powerful comand line tool than Oracle. Sometimes, depending on your environment (terminal, client), SQL*Plus behaves strangely when you want to do some complex (ironic) operations… like deleting a character or moving the cursor with the arrow keys.

Today I’llexplain what you can do to improve SQL*Plus to behave properly when youit does not. I’ll also explain how you can easily make SQL*Plus have history of the commands you typed in previous sessions.

Backspace or Delete key behaviour

PROBLEM: It’s simple… SQL*Plus sometimes does not understand what your terminal says to him! Depending on your terminal settings this can affect keyboard behaviour. So if you pressing the BACKSPACE key will print up funny characters: [[D^ and pressing DELETE key will give you this: [[3~. But if you try some keyboard combinations you'll probably discover that CTRL+BACKSPACE gives you the correct BACKSPACE behaviour you expect.

SOLUTION: What you can do is to change the terminal line settings by using the stty command. If you haven't yet started SQL*Plus you can type the following command:

[oracle@server~]# stty erase [press CTRL-V followed by BACKSPACE]

If you are already on SQL*Plus then you can run an host command, as you may probably know, by using the ! exclamation signal. In this case after the erase statement just press the backspace!

SQL> !stty erase [BACKSPACE]

If you don’t want to bother with this behaviour in future you may just add a line to your user profile, (.profile, .bash_profile or .cshrc) to make it a permanent change.

Command Line History in SQL*Plus

PROBLEM: When we are used to Linux/Unix shells and we use other command line tools, like SQL*Plus , we miss some nice features like command history. How can he have command line history on SQL*Plus?

SOLUTION: What I’ll explain now is how to tweak your environment with rlwrap. rlwrap is a readline wrapper, a small utility that uses the GNU readline library to allow the editing of keyboard input for any other command. It maintains a separate input history for each command, and can TAB-expand words using all previously seen words and/or a user-specified file.

Let’s start…

[root@server~]# wget -c http://utopia.knoware.nl/~hlub/rlwrap/rlwrap-0.30.tar.gz
[root@server~]# tar xvpfz rlwrap-0.30.tar.gz
[root@server~]# cd rlwrap-0.30
[root@server rlwrap-0.30]# ./configure; make install

You can check that now you have it on your system:

[root@server rlwrap-0.30]# which rlwrap
/usr/local/bin/rlwrap

Next, just add the following line to your .bashrc file on your oracle user home, to add an alias for sqlplus, and we’re done!

alias sqlplus=’rlwrap sqlplus’

Logoff and logon and you can try it on your beautiful SQL*Plus … Just remember that this is OS account oriented. If you do some DBA work and then you connect as SCOTT, you’ll also see the history commands that you or anyone else typed logged on as oracle user on that OS.

Hope you like it… Have a nice day…

One common mistake in 10g, when we enable archivelog mode, happens when we use the flash recovery area default settings as the repository for our archived redo log’s… The mistake consequences will happen 20-30 days after the database archivelog mode been enabled.

By default Oracle 10g Database Configuration Assistant (DBCA) sets flash recovery area size to 2GB (db_recovery_file_dest_size parameter) and when we enable DB archivelog mode it will use the default db_recovery_file_dest parameter for the destination of our archivelogs unless we set any other directory as the destination for them.

A common small 10g database generates an average 100MB of archivelog files daily… Sometimes even at weekend days when most applications DML is almost zero. Why this happens? The reason is Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM). AWR collects detailed performance-related metrics from the database at regular intervals, known as snapshots. After each snapshot is taken, ADDM is invoked to thoroughly analyze the data and metrics deriving from the difference between snapshots, and then recommend necessary actions. This generates lot’s of DML and consequently lot’s of redo data.

So, after some days, your flash recovery area will reach the default 2GB because we have our default settings database archiving there… When this happens our database will be unable to archive due to flash recovery area went full. This happens even if we have lot’s of disk space!

In our alert log file we’ll see something like this:

ORA-16038: log one sequence 3144 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log string thread string: 'string: '/u01/app/oracle/oradata/mydb/redo01.log'
Sat Oct 11 10:43:56 2008
ARCH: Archival stopped, error occurred. Will continue retrying
Sat Oct 11 10:43:56 2008
ORACLE Instance neo - Archival Error

Oracle saves all the information about what we place in the flash recovery area in the RMAN repository/controlfile.  If it determines that there is not sufficient space in the recovery file destination, as set by db_recovery_file_dest_size then it will fail. Just deleting the old backups and archive logs from disk is not sufficient as it’s the rman repository/controlfile that holds the space used information.

How to fix this? If we google we see lot’s of places saying to execute “delete archivelog all” in RMAN. This is the fast easy, but dirty solution, as we don’t want to delete all our precious archivelogs by endanger the ability to do future database recovery. The fix can be done with any of the following solutions:

Solution 1 – Delete unwanted archive log files at OS side, then crosscheck archivelogs to marks the controlfile that the archives have been deleted and deleting expired ones.

[oracle@app oracle]$ cd /u01/app/oracle/flash_recovery_area/mydb/
[oracle@app oracle]$ rm archivelogs*
[oracle@app oracle]$ rman target /
connected to target database: MYDB (DBID=1649570311)
RMAN> crosscheck archivelog all
RMAN> delete expired archivelog all

Solution 2 – Connect RMAN to backup and then delete your archivelogs… this is a much better solution.

[oracle@app oracle]$ rman target /
connected to target database: MYDB (DBID=1649570311)
RMAN> backup archivelog until logseq <log#> delete all input;
or
RMAN> backup archivelog until time 'sysdate-15' delete all input;

Solution 3 – increase dynamically (without shutdown/startup database) the parameter db_recovery_file_dest_size.

[oracle@app oracle]$ sqlplus "/ as sysdba"
SQL> alter system set db_recovery_file_dest_size=4G

Then, if needed (usually with solution 1 and 2), just shutdown and startup your database and you’ll get you database again up. We may have to  shutdown abort if the database does not shutdowns normally.

To avoid the same problem in future when the archivelog size reachs the db_recovery_file_dest_size we should set the following parameters to set an alternate place to archive the redo logs.

log_archive_dest_1=’LOCATION=use_db_recovery_file_dest NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2′
log_archive_dest_2=’LOCATION=/other_destination_for_archiving’
log_archive_dest_state_1=’enable’
log_archive_dest_state_2=’alternate’
db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area/mydb/’
db_recovery_file_dest_size=2G

Have a nice loving day

Here it is the big announcement that make justice to the all hype about Oracle Openworld 2008. Oracle released it’s first hardware product and it’s built by HP. Oracle Exadata Storage Server is an easy-to-deploy and scalable storage module.

And… it’s a WOOOWWW. It’s like a dream come true! Oracle Parallel Query Server is now part of the hardware! Oracle removed it from the database and it’s now part of that big machine with 2 intel processors (8 cores), 12 Terabytes of storage raw data. This beauty does not return blocks of data… it returns the query results. This is HUGE!!!

Imagine what this means for Datawarehouse Terabyte Databases and what this means for Grid Computing! But the bonus is that this appliance is not readonly optimized! This works well either for OLTP and OLAP databases!

This makes all that hype worthful! The Exadata I/O is sequential and has scan rates of 80 MB/s per disk drive, almost 1GB per Storage Cell is easily achieved. With 14 Exadata Storage Cells, the data-scanning rate is 14 GB/s. Only a little more than a minute to scan a Terabyte on tables and indexes.

UPDATE1:

Oracle Exadata Storage Server is based on the HP ProLiant DL180 G5 server. Customers will have the choice of SAS or SATA drives and may get storage capacity up to 12 Terabytes. The Oracle Exadata software is pre-installed.

You can see the machine specs here. Kevin Closson has a great article about it… read it here.