Moving a Partition to an Archival Schema and Tablespace

Here is a demo of moving a partition of the BALANCES table in the BALANCES schema to a separate Schema (ARCH), Tablespace (ARCHIVAL) and Table (BALANCES_ARCH).

SQL> connect hemant/hemant
Connected.
SQL> -- drop users
SQL> drop user BALANCES cascade;

User dropped.

SQL> drop user ARCH cascade;

User dropped.

SQL> -- set target file dest
SQL> alter system set db_create_file_dest='/tmp/';

System altered.

SQL> 
SQL> -- create tablespaces
SQL> drop tablespace balances including contents and datafiles;

Tablespace dropped.

SQL> create tablespace balances ;

Tablespace created.

SQL> 
SQL> drop tablespace archival including contents and datafiles;

Tablespace dropped.

SQL> create tablespace archival ;

Tablespace created.

SQL> 
SQL> -- create users
SQL> drop user BALANCES cascade;
drop user BALANCES cascade
          *
ERROR at line 1:
ORA-01918: user 'BALANCES' does not exist


SQL> create user BALANCES identified by BALANCES default tablespace balances;

User created.

SQL> grant create session to BALANCES;

Grant succeeded.

SQL> alter user BALANCES quota unlimited on balances;

User altered.

SQL> alter user BALANCES quota unlimited on archival;

User altered.

SQL> grant create table to BALANCES;

Grant succeeded.

SQL> 
SQL> drop user ARCH cascade;
drop user ARCH cascade
          *
ERROR at line 1:
ORA-01918: user 'ARCH' does not exist


SQL> create user ARCH identified by ARCH default tablespace archival;

User created.

SQL> grant create session to ARCH;

Grant succeeded.

SQL> alter user ARCH quota unlimited on archival;

User altered.

SQL> alter user ARCH quota unlimited on balances;

User altered.

SQL> grant create table to ARCH;

Grant succeeded.

SQL> 
SQL> -- rem create source table
SQL> connect BALANCES/BALANCES
Connected.
SQL> create table BALANCES
  2  (branch_code  varchar2(5),
  3   account_number number,
  4   fiscal_year  number,
  5   accounting_period number,
  6   balance number)
  7  partition by range (branch_code, fiscal_year, accounting_period)
  8  (partition ABC_2012_1 values less than ('ABC',2012,2),
  9   partition ABC_2012_2 values less than ('ABC',2012,3),
 10   partition ABC_2012_3 values less than ('ABC',2012,4),
 11   partition ABC_2012_4 values less than ('ABC',2012,5),
 12   partition ABC_2012_5 values less than ('ABC',2012,6),
 13   partition ABC_2012_6 values less than ('ABC',2012,7),
 14   partition XYZ_2012_1 values less than ('XYZ',2012,2),
 15   partition XYZ_2012_2 values less than ('XYZ',2012,3),
 16   partition XYZ_2012_3 values less than ('XYZ',2012,4),
 17   partition XYZ_2012_4 values less than ('XYZ',2012,5),
 18   partition XYZ_2012_5 values less than ('XYZ',2012,6),
 19   partition XYZ_2012_6 values less than ('XYZ',2012,7)
 20  )
 21  tablespace balances;

Table created.

SQL> create index BALANCES_NDX on
  2  BALANCES(BRANCH_CODE, ACCOUNT_NUMBER, FISCAL_YEAR, ACCOUNTING_PERIOD)
  3  LOCAL;

Index created.

SQL> 
SQL> 
SQL> 
SQL> --- rem create target archival table
SQL> connect ARCH/ARCH
Connected.
SQL> create table BALANCES_ARCH
  2  (branch_code  varchar2(5),
  3   account_number number,
  4   fiscal_year  number,
  5   accounting_period number,
  6   balance number)
  7  partition by range (branch_code, fiscal_year, accounting_period)
  8  (partition ABC_2012_1 values less than ('ABC',2012,2),
  9   partition ABC_2012_2 values less than ('ABC',2012,3),
 10   partition ABC_2012_3 values less than ('ABC',2012,4),
 11   partition ABC_2012_4 values less than ('ABC',2012,5),
 12   partition ABC_2012_5 values less than ('ABC',2012,6),
 13   partition ABC_2012_6 values less than ('ABC',2012,7),
 14   partition XYZ_2012_1 values less than ('XYZ',2012,2),
 15   partition XYZ_2012_2 values less than ('XYZ',2012,3),
 16   partition XYZ_2012_3 values less than ('XYZ',2012,4),
 17   partition XYZ_2012_4 values less than ('XYZ',2012,5),
 18   partition XYZ_2012_5 values less than ('XYZ',2012,6),
 19   partition XYZ_2012_6 values less than ('XYZ',2012,7)
 20  )
 21  tablespace archival;

Table created.

SQL> create index BALANCES_ARCH_NDX on
  2  BALANCES_ARCH(BRANCH_CODE, ACCOUNT_NUMBER, FISCAL_YEAR, ACCOUNTING_PERIOD)
  3  LOCAL;

Index created.

SQL> 
SQL> 
SQL> -- insert data into source
SQL> connect BALANCES/BALANCES
Connected.
SQL> insert into balances
  2  select decode(mod(rownum,2),0,'ABC',1,'XYZ'),rownum,2012,1,1000
  3  from dual
  4  connect by level < 10
  5  /

9 rows created.

SQL> insert into balances
  2  select decode(mod(rownum,2),0,'ABC',1,'XYZ'),rownum,2012,2,2000
  3  from dual
  4  connect by level < 10
  5  /

9 rows created.

SQL> insert into balances
  2  select decode(mod(rownum,2),0,'ABC',1,'XYZ'),rownum,2012,3,3000
  3  from dual
  4  connect by level < 10
  5  /

9 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> REM REM REM
SQL> -- select branch_code, account_number, fiscal_year, accounting_period, count(*)
SQL> -- from balances
SQL> -- group by
SQL> -- branch_code, account_number, fiscal_year, accounting_period
SQL> -- order by 1,2,3,4
SQL> -- /
SQL> REM REM REM
SQL> 
SQL> -- create intermediate table with index
SQL> connect BALANCES/BALANCES
Connected.
SQL> create table balances_exch_tbl tablespace balances as select * from balances where 1=2;

Table created.

SQL> create index balances_exch_tbl_ndx on balances_exch_tbl(branch_code, account_number, fiscal_year, accounting_period)
  2  tablespace balances;

Index created.

SQL> grant select, alter on balances_exch_tbl to ARCH;

Grant succeeded.

SQL> 
SQL> -- verify row counts in source and archival partitions
SQL> connect BALANCES/BALANCES
Connected.
SQL> select count(*) from BALANCES partition (ABC_2012_1);

  COUNT(*)
----------
         4

SQL> connect ARCH/ARCH
Connected.
SQL> select count(*) from BALANCES_ARCH partition (ABC_2012_1);

  COUNT(*)
----------
         0

SQL> 
SQL> -- exchange for account_period 1 for ABC
SQL> connect BALANCES/BALANCES
Connected.
SQL> alter table BALANCES exchange partition ABC_2012_1 with table balances_exch_tbl including indexes without validation;

Table altered.

SQL> connect ARCH/ARCH
Connected.
SQL> alter table BALANCES_ARCH exchange partition ABC_2012_1 with table balances.balances_exch_tbl including indexes without validation;

Table altered.

SQL> alter table BALANCES_ARCH move partition ABC_2012_1 tablespace archival;

Table altered.

SQL> connect BALANCES/BALANCES
Connected.
SQL> alter table balances_exch_tbl move tablespace balances;

Table altered.

SQL> alter index balances_exch_tbl_ndx rebuild tablespace balances;

Index altered.

SQL> 
SQL> 
SQL> -- verify row counts in source and archival partitions
SQL> connect BALANCES/BALANCES
Connected.
SQL> select count(*) from BALANCES partition (ABC_2012_1);

  COUNT(*)
----------
         0

SQL> connect ARCH/ARCH
Connected.
SQL> select count(*) from BALANCES_ARCH partition (ABC_2012_1);

  COUNT(*)
----------
         4

SQL> 
SQL> -- query for segment tablespaces
SQL> connect hemant/hemant
Connected.
SQL> select owner, segment_name, segment_type, partition_name, tablespace_name
  2  from dba_segments
  3  where segment_name like 'BALANC%'
  4  order by 1,2,3,4
  5  /

OWNER      SEGMENT_NAME          SEGMENT_TYPE     PARTITION_NAME        TABLESPACE_NAME
---------- --------------------- ---------------- --------------------- ---------------------
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_1            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_2            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_3            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_4            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_5            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  ABC_2012_6            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_1            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_2            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_3            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_4            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_5            ARCHIVAL
ARCH       BALANCES_ARCH         TABLE PARTITION  XYZ_2012_6            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  ABC_2012_2            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  ABC_2012_3            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  ABC_2012_4            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  ABC_2012_5            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  ABC_2012_6            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_1            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_2            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_3            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_4            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_5            ARCHIVAL
ARCH       BALANCES_ARCH_NDX     INDEX PARTITION  XYZ_2012_6            ARCHIVAL
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_1            BALANCES
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_2            BALANCES
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_3            BALANCES
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_4            BALANCES
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_5            BALANCES
BALANCES   BALANCES              TABLE PARTITION  ABC_2012_6            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_1            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_2            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_3            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_4            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_5            BALANCES
BALANCES   BALANCES              TABLE PARTITION  XYZ_2012_6            BALANCES
BALANCES   BALANCES_EXCH_TBL     TABLE                                  BALANCES
BALANCES   BALANCES_EXCH_TBL_NDX INDEX                                  BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_1            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_2            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_3            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_4            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_5            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  ABC_2012_6            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_1            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_2            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_3            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_4            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_5            BALANCES
BALANCES   BALANCES_NDX          INDEX PARTITION  XYZ_2012_6            BALANCES

49 rows selected.

SQL> 

The account HEMANT is the DBA account. BALANCES owns the Source BALANCES table in the BALANCES Tablespace. ARCH owns the the Archival BALANCES_ARCH table in the ARCHIVAL Tablespace.

Backup and Recovery with intermediate NOARCHIVELOG

In a recent forums post, there was a question about whether one can taken an L0 backup, then switch the database to NOARCHIVELOG mode for transactions, switch back to ARCHIVELOG mode and take an L1 backup.  The query being whether the L0 and L1 backups could be used for a Recovery.

Here, I take an L0 backup :

[oracle@linux64 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 7 21:30:18 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup database plus archivelog;


Starting backup at 07-FEB-13
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=196 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=25 RECID=57 STAMP=806794196
input archived log thread=1 sequence=26 RECID=58 STAMP=806794237
channel ORA_DISK_1: starting piece 1 at 07-FEB-13
channel ORA_DISK_1: finished piece 1 at 07-FEB-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_07/o1_mf_annnn_TAG20130207T213038_8k7c3y83_.bkp tag=TAG20130207T213038 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-FEB-13

Starting backup at 07-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00014 name=/oradata/orcl/add_tbs_01.dbf
input datafile file number=00015 name=/oradata/orcl/add_tbs_02.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 07-FEB-13
...
...
...
Starting backup at 07-FEB-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=27 RECID=59 STAMP=806794355
channel ORA_DISK_1: starting piece 1 at 07-FEB-13
channel ORA_DISK_1: finished piece 1 at 07-FEB-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_07/o1_mf_annnn_TAG20130207T213235_8k7c7n08_.bkp tag=TAG20130207T213235 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-FEB-13

Starting Control File Autobackup at 07-FEB-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_02_07/o1_mf_n_806794357_8k7c7ofw_.bkp comment=NONE
Finished Control File Autobackup at 07-FEB-13

RMAN> 

Next, I take the database into NOARCHIVELOG mode and run some transactions.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             281021528 bytes
Database Buffers           25165824 bytes
Redo Buffers                6336512 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> 
SQL> connect hemant/hemant
Connected.
SQL> create table xyzabc as select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> insert into xyzabc select * from xyzabc;

76637 rows created.

SQL> /

153274 rows created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> delete xyzabc;

306548 rows deleted.

SQL> alter system switch logfile;

System altered.

SQL> rollback;

Rollback complete.

SQL> alter system switch logfile;

System altered.

SQL> 

I have forced multiple log switches to ensure that the online redo log files would not be sufficient for a Recovery.

I now switch the database back to ARCHIVELOG mode.

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             281021528 bytes
Database Buffers           25165824 bytes
Redo Buffers                6336512 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     35
Next log sequence to archive   37
Current log sequence           37
SQL> 

The last ArchiveLog that I had backed up with the database was SEQUENCE#27. The oldest Online Redo Log is now SEQUENCE#35. All the intermediate Redo/ArchiveLogs are "lost". I now run an Incremental Backup.

[oracle@linux64 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 7 21:42:25 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup incremental level 1 cumulative database;

Starting backup at 07-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
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 incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set

Oracle has smartly switched to a Level0 backup -- a full backup ! Question : What gives ? Why cannot I take an Incremental Backup ?
.
.
.


UPDATE : 
Re-running the scenario with a slight change :

[oracle@linux64 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Feb 8 23:41:40 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup incremental level 0 database plus archivelog;


Starting backup at 08-FEB-13
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=195 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=38 RECID=61 STAMP=806801256
input archived log thread=1 sequence=39 RECID=62 STAMP=806888537
channel ORA_DISK_1: starting piece 1 at 08-FEB-13
channel ORA_DISK_1: finished piece 1 at 08-FEB-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_08/o1_mf_annnn_TAG20130208T234218_8kb76v9g_.bkp tag=TAG20130208T234218 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-FEB-13

Starting backup at 08-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00014 name=/oradata/orcl/add_tbs_01.dbf
input datafile file number=00015 name=/oradata/orcl/add_tbs_02.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 08-FEB-13
...
...
...
Starting backup at 08-FEB-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=40 RECID=63 STAMP=806888681
channel ORA_DISK_1: starting piece 1 at 08-FEB-13
channel ORA_DISK_1: finished piece 1 at 08-FEB-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_08/o1_mf_annnn_TAG20130208T234442_8kb7cb6c_.bkp tag=TAG20130208T234442 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-FEB-13

Starting Control File Autobackup at 08-FEB-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_02_08/o1_mf_n_806888683_8kb7cdk7_.bkp comment=NONE
Finished Control File Autobackup at 08-FEB-13

RMAN> 



SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             281021528 bytes
Database Buffers           25165824 bytes
Redo Buffers                6336512 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> 

SQL> connect hemant/hemant
Connected.
SQL> drop table xyzabc;

Table dropped.

SQL> create table xyzabc as select * from dba_objects;

Table created.

SQL> alter system switch logfile;

System altered.

SQL> insert into xyzabc select * from xyzabc;

76637 rows created.

SQL> /

153274 rows created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> delete xyzabc;

306548 rows deleted.

SQL> alter system switch logfile;

System altered.

SQL> rollback;

Rollback complete.

SQL> alter system switch logfile;

System altered.

SQL> 

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             281021528 bytes
Database Buffers           25165824 bytes
Redo Buffers                6336512 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     48
Next log sequence to archive   50
Current log sequence           50
SQL> 


[oracle@linux64 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Feb 8 23:51:03 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup incremental level 1 database;

Starting backup at 08-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=67 device type=DISK
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=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00014 name=/oradata/orcl/add_tbs_01.dbf
input datafile file number=00015 name=/oradata/orcl/add_tbs_02.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 08-FEB-13
...
...
...
Starting Control File Autobackup at 08-FEB-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_02_08/o1_mf_n_806889118_8kb7rys6_.bkp comment=NONE
Finished Control File Autobackup at 08-FEB-13

RMAN> sql 'alter system archive log current';

sql statement: alter system archive log current

RMAN> backup archivelog all;

Starting backup at 09-FEB-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=38 RECID=61 STAMP=806801256
input archived log thread=1 sequence=39 RECID=62 STAMP=806888537
input archived log thread=1 sequence=40 RECID=63 STAMP=806888681
input archived log thread=1 sequence=50 RECID=64 STAMP=806890242
input archived log thread=1 sequence=51 RECID=65 STAMP=806890246
channel ORA_DISK_1: starting piece 1 at 09-FEB-13
channel ORA_DISK_1: finished piece 1 at 09-FEB-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_02_09/o1_mf_annnn_TAG20130209T001046_8kb8w6jp_.bkp tag=TAG20130209T001046 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-FEB-13

Starting Control File Autobackup at 09-FEB-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_02_09/o1_mf_n_806890247_8kb8w7pv_.bkp comment=NONE
Finished Control File Autobackup at 09-FEB-13

RMAN> 

This time, I *am* able to take a Level 1 Incremental Backup. The difference : In the first run, although I began by saying that I took an L0 backup, what I did was a Full Backup. In the second run, I did start with an L0 backup. Oracle does not recognise a Full Backup as a backup that can be the base for an L1 backup. !