Friday, August 22, 2014

CHEF: New dev progress on oracle cookbook

Greetings,

After the summer holidays, the oracle cookbook will undergo some major changes. With Dominique (THE co-author), we'll be streamlining the cookbook to more LWRP/HRWP style. v1.3.0 will include some tidy-up on the cookbook, but v2.0.0 will bust the bank. My personal goal is to get the grid infrastructure install and ASM disk usage incorporated to the cookbook (v1.3.0). It's one of the steps closer to the RAC. Also going to glance the patching of existing Oracle installations through (amazing) Chef installations, is on the queue. I'm also working to convert my simple ksh Oracle backup script to Ruby. This will be a circle end of the DBA work I've been doing for the past years. So, stay tuned for more from the oracle cookbook! Check the Road map on README.md.

We'll also change the documentation structure of the cookbook. The README.md is getting way too big for the getting the relevant information for the audience, so we'll be splitting it to smaller doc files.

Br, Ari aka @dmoarir or arir on IRC

Friday, May 23, 2014

CHEF: oracle cookbook v1.2.0 now released!

New version 1.2.0 of the oracle cookbook now available at:

http://community.opscode.com/cookbooks/oracle
and
https://github.com/aririikonen/oracle

New in v1.2.0

  • Support for Oracle 12c databases with DBEXPRESS configured out of the box
  • Fixed few issues on recording oracle version and timestamp of the installation (get_version.rb)

Try it out and please report any issues to GitHub.
https://github.com/aririikonen/oracle/issues

Next I'll start to bake the Oracle 12c Grid Infrastructure installation.

Monday, April 21, 2014

CHEF: What's new in oracle cookbook v1.1.2?

After discussing with Eric Wolfe (@atomic_penquin) at #ChefConf2014, I added a recipe to handle the OS setup for Oracle databases on CentOS/RedHat. ora_os_setup.rb will perform the following tasks:

  • Create the 'oracle' os user based on the encrypted data bag information
  • Install dependency rpm packages for the Oracle binaries
  • Configure kernel parameters

Basically it is just a reduced default.rb. Tested it, but if you find any issues with it, please create an issue/pull request on GitHub.

or

Friday, April 11, 2014

CHEF: Installing Oracle database with Virtualbox (or vmware workstation, vagrant(vmware plugin)), chef-solo and test kitchen

"As hot as the microwave popcorn after the beep!"

My friend Scott Russell (sc0ttruss) has created a really nifty bash script to install an Oracle 11.2 database without the full and sometimes heavy chef environment solely for testing purposes.

This bash script uses chef-solo, vagrant (virtualbox or vmware ws (vagrant-plugin)), test-kitchen, bento images to pop up an Oracle VM (CentOS) in less than 35 minutes (run time). Of course run time depends heavily on the HW you are running. It works with the latest Oracle v1.1.1 cookbook.

Testkichenscripts:

He also has a nice blog post about it here:

Check it out! Try it out!

Friday, April 4, 2014

CHEF: Announcing relaunched oracle cookbook v1.1.0!

I'm happy to let you know, that the v1.1.0 of the cookbook has been released. I've released under my own user-id on GitHub. Please check it out at:

Oracle chef cookbook v1.1.0

New features in version v1.1.0


  • Configure the EM dbconsole (Enterprise Manager Database Control)
  • Install Oracle Client and patch it to the latest patch

Few notes:

The EM dbconsole is configured for every database. You can turn it off from attributes/default.rb, if you don't want it configured by default. It does extend the install time, but I also modified the default_template.dbt to have less options on by default. The older template is now named as midrange_template.dbt. For v1.1.1 I will make the template file name it as a node attribute, so that you can override it from a role for example.

For the Oracle client, please modify the templates/default/tnsnames.ora to best match your database layout. Currently it is only has an example.

Please try it out, contribute and read the "Roadmap" chapter on the README.md for planned features.

Monday, March 10, 2014

CHEF: echa-oracle cookbook update coming out soon (v1.1.0)

Update 4/8/2014: v1.1.1 is out! https://github.com/aririikonen/oracle

Just wanted to post an announcement about the echa-oracle cookbook. There's going to be an update to version 1.1.0 soon. I've added a possibility to install Oracle Client 11.2 as well. Please, stay tuned.

Monday, July 22, 2013

CHEF: echa-oracle updated to v1.0.4, Patch 16619892 - 11.2.0.3.7 Patch Set Update

Updated the cookbook to use the latest Oracle patch which was released on 7/16/2013, Patch 16619892 - 11.2.0.3.7 Patch Set Update. Downloaded the new patch from https://support.oracle.com and placed it to the HTTPS media site (check the cookbook README.md for more details). At the same go decided to update the opatch version from 11.2.0.3.3 to 11.2.0.3.4.

Procedure itself was straight forward. Depends of course if you are overriding the default attributes from the ora_quickstart role or just using the default attributes.

1. Changed the attributes/default.rb.
default[:oracle][:rdbms][:latest_patch][:url] = 'https://secure.server.localdomain/path/to/p16619892_112030_Linux-x86-64.zip' 
default[:oracle][:rdbms][:latest_patch][:dirname] = '16619892'
2. Changed the ora_quickstart.rb role.
name "ora_quickstart"description "Role applied to Oracle quickstart test machines."run_list 'recipe[echa-oracle]', 'recipe[echa-oracle::logrotate_alert_log]', 'recipe[echa-oracle::logrotate_listener]', 'recipe[echa-oracle::createdb]'override_attributes :oracle => {:rdbms => {:latest_patch => {:url => 'https://secure.server.localdomain/path/to/p16619892_112030_Linux-x86-64.zip'}, :opatch_update_url => 'https://secure.server.localdomain/path/to/p6880880_112000_Linux-x86-64.zip', :install_files => ['https://secure.server.localdomain/path/to/p10404530_112030_Linux-x86-64_1of7.zip', 'https://secure.server.localdomain/path/to/p10404530_112030_Linux-x86-64_2of7.zip']}}
3. Uploaded the role to Hosted Chef.
knife role from file roles/ora_quickstart.rb
4. After changing metadata.rb, CHANGELOG.md and README.md, committed & pushed to GitHub.

5. Uploaded the new cookbook v1.0.4 to Hosted Chef.
knife cookbook upload echa-oracle --freeze
6. Ran a test on EC2, which proved to be successful. (Old post, but still using the same procedure for testing - CHEF: echa-oracle v1.0.0 cookbook tests on EC2)

For further details about the cookbook check from Opscode community site at http://community.opscode.com/cookbooks/oracle.

Tuesday, June 25, 2013

CHEF: echa-oracle v1.0.0 cookbook tests on DigitalOcean

Update on 4/4/2014: The cookbook has been re-launched at https://github.com/aririikonen/oracle, also available from Chef community site at http://community.opscode.com/cookbooks/oracle.

Further smoke testing for echa-oracle v1.0.0 cookbook. This time on DigitalOcean CentOS 6.4 x64 VM. (check out https://www.digitalocean.com, low cost VM's)

Pre-reqs (mainly the same as for EC2, post below):
More straight forward steps:
  • Whipped out a 2G MEM VM (CentOS 6.4 x64)
  • Modified /etc/hosts to add a valid FQDN
  • Bootstrapped with:
knife bootstrap FQDN -r 'role[ora_quickstart]' -j '{"oracle" :{"rdbms" :{"dbs" :{"FOO" :false}}}}'

Result:

Success.

Notes (also check below for EC2 notes):
  • Deployment will take some time, especially installing the Oracle binaries, latest patch and db creation, so don't be alarmed if it seems to be stuck, it isn't. Just check 'top' on the VM to verify this.
  • Swap missing again, so you'll need to add it later on.
  • Check that /dev/shm is big enough.
Sample output:

CHEF: echa-oracle v1.0.0 cookbook tests on EC2

Update on 4/4/2014: The cookbook has been re-lauched at https://github.com/aririikonen/oracle (v1.1.x), also available from Chef community site at http://community.opscode.com/cookbooks/oracle.

Been a while since I've posted something. This time it is about Chef and the new echa-oracle cookbook. At the time writing this post, echa-oracle cookbook has not been released yet, but I'll link the GitHub repo here once it is available.

Wanted to make sure, that before releasing the echa-oracle cookbook, I'll run a few smoke tests against some instances on EC2 and post the results here.

Pre-reqs:
Steps:

Made sure the knife ec2 commands were working fine with

knife ec2 server list

Bootstrapped a new flavor m1.small instance with ora_quickstart role. ora_quickstart role is described in detail on the echa-oracle cookbook README.md.

knife ec2 server create -I ami-9bf6e0ef -S pem_name -i ~/.ssh/pem_file_name.pem -x ec2-user -G sec_group -f m1.small --ebs-size 20 -r "role[ora_quickstart]" -j '{"oracle" : {"rdbms": {"dbs": {"FOO" : false}}}}'

Result:

Success.

Notes:
  • Deployment will take some time, especially installing the Oracle binaries, latest patch and db creation, so don't be alarmed if it seems to be stuck, it isn't. Just check 'top' on the instance to verify this.
  • Used a Red Hat Enterprise Linux 6.4 AMI (ami-9bf6e0ef)
  • By default, an EC2 instance doesn't come with swap. Depending on the flavor, you need to add 2x memory size swap on the VM. It is a normal Oracle recommendation, but you can get further details by checking the 11.2. documentation.
  • I had to use --ebs-size 20 parameter to get a bigger 20GB disk for the deployment. m1.small comes with 7GB as a default configuration.
  • Already mentioned in the echa-oracle cookbook README.md, but resize the /dev/shm as well. You might face some issues with a running Oracle database, if it is not big enough.
  • If you don't want to create a database(s) at the same deployment, you can leave the -j '{"oracle" : {"rdbms": {"dbs": {"FOO" : false}}}}' option out. Although you are able to create the database(s) later on by adding the node attributes directly with knife node edit.
Sample output of the test deployment:

https://gist.github.com/aririikonen/5936747

Friday, October 22, 2010

Run cron jobs the first Saturday of the month

Running tasks on the first Saturday of the week. Example runs the partition maintenance on Oracle Enterprise Manager - Grid Control every first Saturday of the month.

# OEM Partition Maintenance Run
00 04 * * 0 [ `date +\%e` -le 7 ] && [ -f /opt/oracle/scripts/gc_part_maint.ksh ] && /opt/oracle/scripts/gc_part_maint.ksh > /dev/null 2>&1

Friday, March 12, 2010

Listener log parsing for unique connections

A small one liner to check out the unique IP addresses (or hostnames) from the listener.log.

cat /opt/oracle/10R2/network/log/listener.log | awk -F"=" {'print $10'} | sed -e 's/......$//g' | sort -k 1,1 -u
cat /opt/oracle/10R2/network/log/listener.log | awk -F"=" {'print $12'} | sed -e 's/......$//g' | sort -k 1,1 -u

Thursday, March 11, 2010

Parsing alert log (gegrep) - Solaris 10

You can easily parse the Oracle alert log with gegrep to find out the times as well as the parsed line.

# Show two lines before the matching string
gegrep -B 2 ORA- alert_DB1.log

# Show how many times the database has been started up and when
gegrep -B 1 "Completed: ALTER DATABASE OPEN" alert_DB1.log

Tuesday, March 9, 2010

Restore database to a different path - set newname for datafile script (UNIX/Linux)

This script helps you to set the new name for datafiles during restoration to a different path. Script will ask two inputs: new path and restoration SCN.


set serveroutput on;
set linesize 300

declare
  cursor df is
    select name from v$datafile
    union all
    select name from v$tempfile
    union all
    select member as name from v$logfile;
   
  cursor df2 is
    select case when instr (file_name, '/') > 0
         then
         substr (file_name, instr (file_name, '/', -1) + 1)
         end as file_name
    from (select name as file_name from v$datafile
    union all
    select name as file_name from v$tempfile
    union all
    select member as file_name from v$logfile);

  newPath varchar2(300);
  newScn number(15);
  s_df varchar2(300);
  s_df2 varchar2(300);
 
begin
--  dbms_output.put_line('Enter the set newname path (omit trailing ''/''):');
  newPath := '&PATH';
--  dbms_output.put_line('Enter the SCN:');
  newScn := &SCN;
  dbms_output.put_line('run');
  dbms_output.put_line('{');
  dbms_output.put_line('set until scn = '||newScn||';');

  open df;
  open df2;

  fetch df into s_df;
  fetch df2 into s_df2;

  while df%FOUND loop
    dbms_output.put_line('set newname for datafile '''||s_df||''' to '''||newPath||'/'||s_df2||''';');
    fetch df into s_df;
    fetch df2 into s_df2;
  end loop;

  dbms_output.put_line('restore database;');
  dbms_output.put_line('switch all;');
  dbms_output.put_line('}');

  close df;
  close df2;
end;
/

Example output:

SQL>@plsql_set_newname_for_datafile.sql
Enter value for path: /path/to/the/new/localtion
old  27:   newPath := '&PATH';
new  27:   newPath := '/path/to/the/new/localtion';
Enter value for scn: 1234123
old  29:   newScn := &SCN;
new  29:   newScn := 1234123;
run
{
set until scn = 1234123;
set newname for datafile '/current/datafile/path/system01.dbf' to '/path/to/the/new/localtion/system01.dbf';
set newname for datafile '/current/datafile/path/undotbs01.dbf' to '/path/to/the/new/localtion/undotbs01.dbf';
set newname for datafile '/current/datafile/path/sysaux01.dbf' to '/path/to/the/new/localtion/sysaux01.dbf';
set newname for datafile '/current/datafile/path/users01.dbf' to '/path/to/the/new/localtion/users01.dbf';
set newname for datafile '/current/datafile/path/temp01.dbf' to '/path/to/the/new/localtion/temp01.dbf';
set newname for datafile '/current/datafile/path/redo01_1.rdo' to '/path/to/the/new/localtion/redo01_1.rdo';
set newname for datafile '/current/datafile/path/redo02_1.rdo' to '/path/to/the/new/localtion/redo02_1.rdo';
set newname for datafile '/current/datafile/path/redo03_1.rdo' to '/path/to/the/new/localtion/redo03_1.rdo';
restore database;
switch all;
}

PL/SQL procedure successfully completed.

How to easily rename all Oracle database datafiles (UNIX/Linux)

The following script helps to rename the Oracle database datafiles to a different location. It will generate executable output as 'alter database rename file..'. You just need to specify the path (without the trailing '/'), where you want to rename the datafiles.

set serveroutput on;
set linesize 300

declare
  cursor df is
    select name from v$datafile
    union all
    select name from v$tempfile
    union all
    select member as name from v$logfile;
   
  cursor df2 is
    select case when instr (file_name, '/') > 0
         then
         substr (file_name, instr (file_name, '/', -1) + 1)
         end as file_name
    from (select name as file_name from v$datafile
    union all
    select name as file_name from v$tempfile
    union all
    select member as file_name from v$logfile);

  newPath varchar2(300);
  s_df varchar2(300);
  s_df2 varchar2(300);
 
begin
--  dbms_output.put_line('Enter the renamed database path (omit trailing ''/''):');
  newPath := '&PATH';

  open df;
  open df2;

  fetch df into s_df;
  fetch df2 into s_df2;

  while df%FOUND loop
    dbms_output.put_line('alter database rename file '''||s_df||''' to '''||newPath||'/'||s_df2||''';');
    fetch df into s_df;
    fetch df2 into s_df2;
  end loop;

  close df;
  close df2;
end;
/


You get for example the following output.

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 9 14:19:51 2010

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>@plsql_alter_database_rename_file.sql
Enter value for path: /path/to/the/new/localtion
old  26:   newPath := '&PATH';
new  26:   newPath := '/path/to/the/new/localtion';
alter database rename file '/current/datafile/path/system01.dbf' to '/path/to/the/new/localtion/system01.dbf';
alter database rename file '/current/datafile/path/undotbs01.dbf' to '/path/to/the/new/localtion/undotbs01.dbf';
alter database rename file '/current/datafile/path/sysaux01.dbf' to '/path/to/the/new/localtion/sysaux01.dbf';
alter database rename file '/current/datafile/path/users01.dbf' to '/path/to/the/new/localtion/users01.dbf';
alter database rename file '/current/datafile/path/temp_small01.dbf' to '/path/to/the/new/localtion/temp_small01.dbf';
alter database rename file '/current/datafile/path/redo14_2.rdo' to '/path/to/the/new/localtion/redo14_2.rdo';
alter database rename file '/current/datafile/path/redo15_2.rdo' to '/path/to/the/new/localtion/redo15_2.rdo';
alter database rename file '/current/datafile/path/redo16_2.rdo' to '/path/to/the/new/localtion/redo16_2.rdo';
alter database rename file '/current/datafile/path/redo13_1.rdo' to '/path/to/the/new/localtion/redo13_1.rdo';
alter database rename file '/current/datafile/path/redo14_1.rdo' to '/path/to/the/new/localtion/redo14_1.rdo';
alter database rename file '/current/datafile/path/redo15_1.rdo' to '/path/to/the/new/localtion/redo15_1.rdo';
alter database rename file '/current/datafile/path/redo16_1.rdo' to '/path/to/the/new/localtion/redo16_1.rdo';

PL/SQL procedure successfully completed.

Wednesday, November 4, 2009

Example of Incrementally Updated RMAN backups (start phase)

This example shows how RMAN marks the first incremental backup as '1' and not '0'. RMAN does not change the backup to become an incremental '0', as many people would think.


RMAN parameters

RMAN> show all;

RMAN configuration parameters for database with db_unique_name DB11G are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/oracle/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/opt/oracle/backup/DB11G_%U.rbk';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/11R1/dbs/snapcf_DB11G.f'; # default

RMAN> exit


Run the first incremental backup


[oracle@server backup]$ rman target /
Recovery Manager: Release 11.1.0.7.0 - Production on Thu Nov 5 00:31:57 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: DB11G (DBID=170286104)

RMAN> RUN
{
RECOVER COPY OF DATABASE
WITH TAG 'incr_update';
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE;
}2> 3> 4> 5> 6> 7> 8> 9>

Starting recover at 05-NOV-09
using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=937 device type=DISK
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
Finished recover at 05-NOV-09
Starting backup at 05-NOV-09
using channel ORA_DISK_1

no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
no parent backup or copy of datafile 5 found
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/opt/oracle/oradata/DB11G/sysaux01.dbf
output file name=/opt/oracle/backup/DB11G_data_D-DB11G_I-170286104_TS-SYSAUX_FNO-2_09kti14r.rbk tag=INCR_UPDATE RECID=1 STAMP=702088421
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/opt/oracle/oradata/DB11G/system01.dbf
output file name=/opt/oracle/backup/DB11G_data_D-DB11G_I-170286104_TS-SYSTEM_FNO-1_0akti176.rbk tag=INCR_UPDATE RECID=2 STAMP=702088479
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/opt/oracle/oradata/DB11G/undotbs01.dbf
output file name=/opt/oracle/backup/DB11G_data_D-DB11G_I-170286104_TS-UNDOTBS1_FNO-3_0bkti197.rbk tag=INCR_UPDATE RECID=3 STAMP=702088530
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/opt/oracle/oradata/DB11G/example01.dbf
output file name=/opt/oracle/backup/DB11G_data_D-DB11G_I-170286104_TS-EXAMPLE_FNO-4_0ckti1ak.rbk tag=INCR_UPDATE RECID=4 STAMP=702088544
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/opt/oracle/oradata/DB11G/users01.dbf
output file name=/opt/oracle/backup/DB11G_data_D-DB11G_I-170286104_TS-USERS_FNO-5_0dkti1b4.rbk tag=INCR_UPDATE RECID=5 STAMP=702088548
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 05-NOV-09

Starting Control File and SPFILE Autobackup at 05-NOV-09
piece handle=/opt/oracle/backup/c-170286104-20091105-00 comment=NONE
Finished Control File and SPFILE Autobackup at 05-NOV-09
Recovery Manager complete.

Do some changes to the database

[oracle@server backup]$ dba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Nov 5 00:36:05 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test (foo varchar2(10));

Table created.

SQL> insert into test values ('testing');

1 row created.

SQL> commit;

Commit complete.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Run another incrementally updated backup


[oracle@server backup]$ rman target /
Recovery Manager: Release 11.1.0.7.0 - Production on Thu Nov 5 00:38:09 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: DB11G (DBID=170286104)

RMAN> RUN
{
RECOVER COPY OF DATABASE
WITH TAG 'incr_update';
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE;
}2> 3> 4> 5> 6> 7> 8> 9>

Starting recover at 05-NOV-09
using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=963 device type=DISK
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
Finished recover at 05-NOV-09

Starting backup at 05-NOV-09

using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/oradata/DB11G/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/oradata/DB11G/system01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/DB11G/undotbs01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/DB11G/example01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/DB11G/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-NOV-09
channel ORA_DISK_1: finished piece 1 at 05-NOV-09
piece handle=/opt/oracle/backup/DB11G_0fkti1g6_1_1.rbk tag=TAG20091105T003830 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-NOV-09

Starting Control File and SPFILE Autobackup at 05-NOV-09
piece handle=/opt/oracle/backup/c-170286104-20091105-01 comment=NONE
Finished Control File and SPFILE Autobackup at 05-NOV-09

RMAN> exit
Recovery Manager complete.

Do more updated to the database

[oracle@server backup]$ dba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Nov 5 00:38:58 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into test values ('testing2');

1 row created.

SQL> commit;

Commit complete.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Run the third incrementally updated backup


[oracle@server backup]$ rman target /
Recovery Manager: Release 11.1.0.7.0 - Production on Thu Nov 5 00:39:31 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: DB11G (DBID=170286104)

RMAN> RUN
{
RECOVER COPY OF DATABASE
WITH TAG 'incr_update';
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE;
}2> 3> 4> 5> 6> 7> 8> 9>

Starting recover at 05-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=946 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy file number=00001 name=/opt/oracle/backup/DB11G_data_D-DB11G_I-170286104_TS-SYSTEM_FNO-1_0akti176.rbk
recovering datafile copy file number=00002 name=/opt/oracle/backup/DB11G_data_D-DB11G_I-170286104_TS-SYSAUX_FNO-2_09kti14r.rbk
recovering datafile copy file number=00003 name=/opt/oracle/backup/DB11G_data_D-DB11G_I-170286104_TS-UNDOTBS1_FNO-3_0bkti197.rbk
recovering datafile copy file number=00004 name=/opt/oracle/backup/DB11G_data_D-DB11G_I-170286104_TS-EXAMPLE_FNO-4_0ckti1ak.rbk
recovering datafile copy file number=00005 name=/opt/oracle/backup/DB11G_data_D-DB11G_I-170286104_TS-USERS_FNO-5_0dkti1b4.rbk
channel ORA_DISK_1: reading from backup piece /opt/oracle/backup/DB11G_0fkti1g6_1_1.rbk
channel ORA_DISK_1: piece handle=/opt/oracle/backup/DB11G_0fkti1g6_1_1.rbk tag=TAG20091105T003830
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished recover at 05-NOV-09

Starting backup at 05-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/oradata/DB11G/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/oradata/DB11G/system01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/DB11G/undotbs01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/DB11G/example01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/DB11G/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-NOV-09
channel ORA_DISK_1: finished piece 1 at 05-NOV-09
piece handle=/opt/oracle/backup/DB11G_0hkti1ih_1_1.rbk tag=TAG20091105T003944 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-NOV-09

Starting Control File and SPFILE Autobackup at 05-NOV-09
piece handle=/opt/oracle/backup/c-170286104-20091105-02 comment=NONE
Finished Control File and SPFILE Autobackup at 05-NOV-09

RMAN> exit
Recovery Manager complete.

Report the INCREMENTAL_LEVEL from V$BACKUP_SET
[oracle@server backup]$ dba
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Nov 5 00:39:55 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> r
1 select START_TIME, BACKUP_TYPE, INCREMENTAL_LEVEL
2 from v$backup_set
3 where start_time > sysdate-1
4* order by 1

START_TIME B INCREMENTAL_LEVEL
-------------------- - -----------------
05-NOV-2009 00:35:49 D
05-NOV-2009 00:38:30 I 1
05-NOV-2009 00:38:33 D
05-NOV-2009 00:39:45 I 1
05-NOV-2009 00:39:48 D

As you can see. The level has not changed to '0' by Oracle RMAN, even though this was the start of an incrementally updated backup.