Skip navigation.

Hemant K Chitale

Syndicate content
I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale
Updated: 9 hours 47 min ago

RMAN -- 8 : Using a Recovery Catalog Schema

Sun, 2015-08-30 06:21
Besides retaining information about backups in the controlfile, Oracle allows the use of an (external) Recovery Catalog schema.  This schema is queryable via SQL in the same manner as querying any user / application schema.

Let's start with a database that already has backups present but created without a Recovery Catalog Schema.

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 30 19:48:30 2015

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


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

SYS>select to_char(completion_time,'DD-MON HH24:MI') Completed_at, datafile_blocks, blocks_read
2 from v$backup_datafile
3 where file#=1
4 order by completion_time;

COMPLETED_AT DATAFILE_BLOCKS BLOCKS_READ
--------------------- --------------- -----------
01-AUG 22:10 107648 107648
10-AUG 15:14 107648 107648
10-AUG 19:58 107648 107648
30-AUG 16:59 107648 107648

SYS>

I now create a Catalog Schema and register this database into that schema.  There are 4 steps to this.  The first is to create the database that will hold t he Catalog Schema (here, we presume that the database has been created before today's steps).  The next is to create the database account for the Catalog Schema (I create the account RCAT_OWNER).  The third step is to login to the Catalog Schema with RMAN and run the CREATE CATALOG command.  The final step is to create an RMAN connection from the TARGET database to the Catalog and REGISTER the database.

[oracle@localhost ~]$ sqlplus system/oracle@rcat

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 30 19:52:15 2015

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


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

SYSTEM>create user rcat_owner identified by rcat_owner
2 default tablespace users quota unlimited on users;

User created.

SYSTEM>grant create session to rcat_owner;

Grant succeeded.

SYSTEM>grant recovery_catalog_owner to rcat_owner;

Grant succeeded.

SYSTEM>
SYSTEM>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ rman catalog rcat_owner@rcat

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Aug 30 19:53:26 2015

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

recovery catalog database Password:
connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ rman target / catalog rcat_owner/rcat_owner@rcat

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Aug 30 19:55:59 2015

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

connected to target database: ORCL (DBID=1229390655)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

When my (TARGET) database ("ORCL") is registered, Oracle automatically does a RESYNC CATALOG. Can I now see my backups in the Catalog ?

RMAN> list backup of datafile 1;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
381 Full 863.89M DISK 00:03:38 10-AUG-15
BP Key: 391 Status: AVAILABLE Compressed: YES Tag: TAG20150810T195515
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/o1_mf_nnndf_TAG20150810T195515_bwk493jn_.bkp
List of Datafiles in backup set 381
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14186110 10-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
385 Full 238.12M DISK 00:01:09 30-AUG-15
BP Key: 395 Status: AVAILABLE Compressed: YES Tag: TAG20150830T165804
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_30/o1_mf_nnndf_TAG20150830T165804_by5kdwrb_.bkp
List of Datafiles in backup set 385
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14198051 30-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN>

Why does it show only the latest two backups ? What about the preceding two backups that I could see in V$BACKUP_DATAFILE ? Let me check those backups without a CATALOG connection.

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Aug 30 19:59:59 2015

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

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
254 Full 733.27M DISK 00:04:51 01-AUG-15
BP Key: 266 Status: AVAILABLE Compressed: YES Tag: TAG20150801T220612
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_01/o1_mf_nnndf_TAG20150801T220612_bvsnlnpn_.bkp
List of Datafiles in backup set 254
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14157609 01-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
262 Full 733.23M DISK 00:03:17 10-AUG-15
BP Key: 274 Status: AVAILABLE Compressed: YES Tag: TAG20150810T151144
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/o1_mf_nnndf_TAG20150810T151144_bwjmojs6_.bkp
List of Datafiles in backup set 262
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14158847 10-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
271 Full 863.89M DISK 00:03:38 10-AUG-15
BP Key: 283 Status: AVAILABLE Compressed: YES Tag: TAG20150810T195515
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/o1_mf_nnndf_TAG20150810T195515_bwk493jn_.bkp
List of Datafiles in backup set 271
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14186110 10-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
275 Full 238.12M DISK 00:01:09 30-AUG-15
BP Key: 287 Status: AVAILABLE Compressed: YES Tag: TAG20150830T165804
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_30/o1_mf_nnndf_TAG20150830T165804_by5kdwrb_.bkp
List of Datafiles in backup set 275
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14198051 30-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN>

When I disconnect from the Catalog Schema and do a local only (TARGET) connection, I can see 4 backups of the datafile.  So, what gives ?  Let me try an SQL query on V$BACKUP_DATAFILE.
(As an aside : Note above how there an be discrepancy in the listings showed by LIST BACKUP in the two scenarios (a) without a Recovery Catalog connection  and  (b) with a Recovery Catalog that was created after the last RESETLOGS).  This is something to remember.

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 30 20:01:54 2015

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


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

SYS>select to_char(completion_time,'DD-MON HH24:MI') Completed_at, datafile_blocks, blocks_read,
2 resetlogs_change#, resetlogs_time
3 from v$backup_datafile
4 where file#=1
5 order by completion_time
6 /

COMPLETED_AT DATAFILE_BLOCKS BLOCKS_READ RESETLOGS_CHANGE# RESETLOGS
--------------------- --------------- ----------- ----------------- ---------
01-AUG 22:10 107648 107648 14082620 04-JUL-15
10-AUG 15:14 107648 107648 14082620 04-JUL-15
10-AUG 19:58 107648 107648 14185666 10-AUG-15
30-AUG 16:59 107648 107648 14185666 10-AUG-15

SYS>

Notice that I have added two columns (RESETLOGS_CHANGE# and RESETLOGS_TIME) in the query.  Now, I see that the two older backups were from an *older* incarnation of the database.  They have a different RESETLOGS_CHANGE# / RESETLOGS_TIME.  So, those backups of the older incarnation are not cataloged into the Catalog Schema !

Can I do anything about this ?  It seems that the full RESYNC doesn't resync for backups of previous incarnations.   Can I reset my RETENTION POLICY and then do a RESYNC ?

SYS>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ rman target / catalog rcat_owner/rcat_owner@rcat

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Aug 30 20:08:06 2015

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

connected to target database: ORCL (DBID=1229390655)
connected to recovery catalog database

RMAN> show all;

RMAN configuration parameters for database with db_unique_name HEMANTDB are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/snapcf_orcl.f'; # default

RMAN> configure retention policy to recovery window of 36 days;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 36 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete

RMAN> list backup of datafile 1;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
381 Full 863.89M DISK 00:03:38 10-AUG-15
BP Key: 391 Status: AVAILABLE Compressed: YES Tag: TAG20150810T195515
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/o1_mf_nnndf_TAG20150810T195515_bwk493jn_.bkp
List of Datafiles in backup set 381
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14186110 10-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
385 Full 238.12M DISK 00:01:09 30-AUG-15
BP Key: 395 Status: AVAILABLE Compressed: YES Tag: TAG20150830T165804
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_30/o1_mf_nnndf_TAG20150830T165804_by5kdwrb_.bkp
List of Datafiles in backup set 385
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14198051 30-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN>

No, extending the Recovery Window still doesn't help.  Can I try something else ?  What about the CATALOG command ?

[oracle@localhost ~]$ ls -l /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_01/
total 894280
-rw-rw---- 1 oracle oracle 7786496 Aug 1 22:06 o1_mf_annnn_TAG20150801T220605_bvsnlfrc_.bkp
-rw-rw---- 1 oracle oracle 2421248 Aug 1 22:06 o1_mf_annnn_TAG20150801T220605_bvsnlk3q_.bkp
-rw-rw---- 1 oracle oracle 56320 Aug 1 22:06 o1_mf_annnn_TAG20150801T220605_bvsnll9n_.bkp
-rw-rw---- 1 oracle oracle 3595776 Aug 1 22:06 o1_mf_annnn_TAG20150801T220605_bvsnlmdx_.bkp
-rw-rw---- 1 oracle oracle 165888 Aug 1 22:12 o1_mf_annnn_TAG20150801T221209_bvsnxs75_.bkp
-rw-rw---- 1 oracle oracle 16896 Aug 1 22:14 o1_mf_annnn_TAG20150801T221404_bvso1f28_.bkp
-rw-rw---- 1 oracle oracle 768901120 Aug 1 22:11 o1_mf_nnndf_TAG20150801T220612_bvsnlnpn_.bkp
-rw-rw---- 1 oracle oracle 110075904 Aug 1 22:11 o1_mf_nnndf_TAG20150801T220612_bvsnvwjj_.bkp
-rw-rw---- 1 oracle oracle 21782528 Aug 1 22:12 o1_mf_nnndf_TAG20150801T220612_bvsnx9v4_.bkp
[oracle@localhost ~]$ ls -l /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/
total 1771844
-rw-rw---- 1 oracle oracle 786944 Aug 10 15:11 o1_mf_annnn_TAG20150810T151143_bwjmohc7_.bkp
-rw-rw---- 1 oracle oracle 28672 Aug 10 15:16 o1_mf_annnn_TAG20150810T151601_bwjmxk5v_.bkp
-rw-rw---- 1 oracle oracle 4140032 Aug 10 19:55 o1_mf_annnn_TAG20150810T195509_bwk48xxv_.bkp
-rw-rw---- 1 oracle oracle 526336 Aug 10 19:55 o1_mf_annnn_TAG20150810T195509_bwk4911j_.bkp
-rw-rw---- 1 oracle oracle 311296 Aug 10 19:55 o1_mf_annnn_TAG20150810T195509_bwk4923s_.bkp
-rw-rw---- 1 oracle oracle 32256 Aug 10 19:59 o1_mf_annnn_TAG20150810T195901_bwk4j5lt_.bkp
-rw-rw---- 1 oracle oracle 768851968 Aug 10 15:15 o1_mf_nnndf_TAG20150810T151144_bwjmojs6_.bkp
-rw-rw---- 1 oracle oracle 110075904 Aug 10 15:15 o1_mf_nnndf_TAG20150810T151144_bwjmvycy_.bkp
-rw-rw---- 1 oracle oracle 21938176 Aug 10 15:15 o1_mf_nnndf_TAG20150810T151144_bwjmx1sv_.bkp
-rw-rw---- 1 oracle oracle 905863168 Aug 10 19:58 o1_mf_nnndf_TAG20150810T195515_bwk493jn_.bkp
[oracle@localhost ~]$


RMAN> catalog start with '/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_01/';

searching for all files that match the pattern /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_01/
no files found to be unknown to the database

RMAN> list backup of datafile 1;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
381 Full 863.89M DISK 00:03:38 10-AUG-15
BP Key: 391 Status: AVAILABLE Compressed: YES Tag: TAG20150810T195515
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/o1_mf_nnndf_TAG20150810T195515_bwk493jn_.bkp
List of Datafiles in backup set 381
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14186110 10-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
385 Full 238.12M DISK 00:01:09 30-AUG-15
BP Key: 395 Status: AVAILABLE Compressed: YES Tag: TAG20150830T165804
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_30/o1_mf_nnndf_TAG20150830T165804_by5kdwrb_.bkp
List of Datafiles in backup set 385
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14198051 30-AUG-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN>
RMAN> catalog start with '/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/';

searching for all files that match the pattern /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_08_10/
no files found to be unknown to the database

Well, apparently, even the CATALOG command refuses to catalog backup pieces from an older incarnation !

So, it seems that :
1.  If you've done a RESETLOGS recently and even though the controlfile may show previous backups, if you create a Catalog Schema after the RESETLOGS, previous backups (i.e. of the preceding incarnation)  are not visible in the Catalog  (disconnecting from the Catalog does allow you to view the previous backups in RMAN !)
2. The CATALOG command also will not include the previous backups, it will only accept backups of the current incarnation.

This testing has been done with 11.2.0.2    Has the behaviour changed in 11.2.0.4 / 12.1.0.1 / 12.1.0.2 ?

.
.
.


Categories: DBA Blogs

RMAN -- 7 : Recovery Through RESETLOGS -- how are the ArchiveLogs identified ?

Mon, 2015-08-10 03:21
When Oracle introduced "official" support for Recovery through RESETLOGS in 10g, it introduced the "%r" component of the log_archive_format parameter.  (This was not present in 9.2)

Therefore, in 10.2, we religiously included "%r" in the log_archive_format parameter.

However, if you use the  FRA via USE_DB_RECOVERY_FILE_DEST, the resetlogs_id is not present and  Oracle uses OMF naming rules for the archivelogs.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 154
Next log sequence to archive 156
Current log sequence 156
SQL> show parameter log_archive_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> select name from v$archived_log where first_time > trunc(sysdate);

NAME
--------------------------------------------------------------------------------
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_152_bwjmnzt5_.arc
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_153_bwjmo4yp_.arc
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_154_bwjmoh29_.arc
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_155_bwjmxjyb_.arc

SQL>

Thus, you can see in the above listing the archivelog filenames, the RESETLOGS_ID is *not* part of the archivelog filename.  (THREAD# and SEQUENCE# do still appear)

How, then, does Oracle (or do we ??) distinguish between archivelogs from one resetlogs and another ?  The archivelog sequence numbers do get reset to 1.
What matters  is that RESETLOGS_ID, RESETLOGS_CHANGE# and RESETLOGS_TIME are also incorporated into V$ARCHIVED_LOG.

SQL> select * from v$database_incarnation order by incarnation#;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
1 1 13-AUG-09 0
PARENT 694825248 0 NO

2 754488 30-OCT-09 1 13-AUG-09
PARENT 701609923 1 NO

3 14082620 04-JUL-15 754488 30-OCT-09
CURRENT 884179148 2 NO


SQL> select resetlogs_id, count(*) from v$archived_log group by resetlogs_id;

RESETLOGS_ID COUNT(*)
------------ ----------
884179148 153

SQL>

My database's current Incarnation is 3 and all the archivelogs are for this Incarnation.  What if I were to do a RESETLOGS and go to a new Incarnation ?

First, I generate some more archivelogs in the current incarnation.

SQL> create table hemant.test_recovery_thru (id number);

Table created.

SQL> insert into hemant.test_recovery_thru select rownum from dual connect by level < 100001;

100000 rows created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

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

Then I shutdown the database and do a RESETLOGS on OPEN.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 159
Next log sequence to archive 161
Current log sequence 161
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>
SQL> select * from v$database_incarnation order by 1;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
1 1 13-AUG-09 0
PARENT 694825248 0 NO

2 754488 30-OCT-09 1 13-AUG-09
PARENT 701609923 1 NO

3 14082620 04-JUL-15 754488 30-OCT-09
PARENT 884179148 2 NO


INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES
------------ ----------------- --------- ----------------------- ---------
STATUS RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------- ------------ ------------------ --------------------------
4 14184835 10-AUG-15 14082620 04-JUL-15
CURRENT 887387582 3 NO


SQL>

I now have a new incarnation (4) created today. I next generate a couple of archivelogs and check the archivelogs

SQL> alter system archive log current;

System altered.

SQL> alter system archivelog current;
alter system archivelog current
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SQL> alter system archive log current;

System altered.

SQL> select resetlogs_id, sequence#
2 from v$archived_log
3 where first_time > trunc(sysdate)
4 order by 1,2;

RESETLOGS_ID SEQUENCE#
------------ ----------
884179148 152
884179148 153
884179148 154
884179148 155
884179148 156
884179148 157
884179148 158
884179148 159
884179148 160
884179148 161
887387582 1
887387582 2

12 rows selected.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL>
SQL> select resetlogs_id, name from v$archived_log where first_time > trunc(sysdate) order by first_time;

RESETLOGS_ID
------------
NAME
--------------------------------------------------------------------------------
884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_152_bwjmnzt5_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_153_bwjmo4yp_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_154_bwjmoh29_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_155_bwjmxjyb_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_156_bwjr2nmj_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_157_bwjr2s1s_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_158_bwjr9pg5_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_159_bwjrb06z_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_160_bwjrb582_.arc

884179148
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_161_bwjrfz1j_.arc

887387582
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_1_bwjrhogp_.arc

887387582
/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_08_10/o1_mf_1_2_bwjrj0gf_.arc


12 rows selected.

SQL>

Notice the first 10 archivelogs are from the RESETLOGS_ID corresponding to Incarnation 3. The Thread# and SEQUENCE# do appear to be part of the file names. But NOT the RESETLOGS_ID.
The last two files now have SEQUENCE# 1 and 2 but no indication of the RESETLOGS_ID.  The RESETLOGS_ID is in V$ARCHIVED_LOG, not in the actual filename.

.
.
.



Categories: DBA Blogs

RMAN -- 6 : RETENTION POLICY and CONTROL_FILE_RECORD_KEEP_TIME

Sat, 2015-08-01 08:12
Most people read the documentation on CONTROL_FILE_RECORD_KEEP_TIME and believe that this parameter *guarantees* that Oracle will retain backup records for that long.  (Some do understand that backup records may be retained longer, depending on the availability of slots (or "records") for the various types of metadata in the controlfile).

However, .... as you should know from real-world experience ... there is always a "BUT".

Please read Oracle Support Note "How to ensure that backup metadata is retained in the controlfile when setting a retention policy and an RMAN catalog is NOT used. (Doc ID 461125.1)" and Bug 6458068

Oracle may need to "grow" the controlfile when adding information about ArchiveLogs or BackupSets / BackupPieces.
An example is this set of entries that occurred when I had created very many archivelogs and backuppieces for them :
Trying to expand controlfile section 13 for Oracle Managed Files
Expanded controlfile section 13 from 200 to 400 records
Requested to grow by 200 records; added 9 blocks of records


To understand the contents of the controlfile see how this listing shows that I have space for 400 records of Backup Pieces and am currently using 232 records.  :

SQL> select * from v$controlfile_record_section where type like '%BACKUP%' order by 1;

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
BACKUP CORRUPTION 44 371 0 0 0 0
BACKUP DATAFILE 200 245 159 1 159 159
BACKUP PIECE 736 400 232 230 61 261
BACKUP REDOLOG 76 215 173 1 173 173
BACKUP SET 40 409 249 1 249 249
BACKUP SPFILE 124 131 36 1 36 36

6 rows selected.

SQL>


However, if I start creating new Backup Pieces without deleting older ones (without Oracle auto-deleting older ones) and Oracle hits the allocation of 400 records, it may try to add new records.  Oracle then prints a message (as shown above) into the alert.log.  Oracle may overwrite records older than control_file_record_keep_time.  If necesssary, it tries to expand the controlfile. If, however, there is not enough filesystem space (or space in the raw device or ASM DiskGroup) to expand the controlfile, it may have to ovewrite some records from the controlfile.  If it has to overwrite records that are older than control_file_record_keep_time, it provides no warning.  However, if it has to overwrite records that are not older than the control_file_record_keep_time, it *does* write a warning to the alert.log

I don't want to violate the Oracle Support policy and quote from the Note and the Bug but I urge you to read both very carefully.  The Note has a particular line about whether there is a relationship between the setting of the control_file_record_time and the Retention Policy.  In the Bug, there is one particularly line about whether the algorithm to extend / reuse / purge records in the controlfile is or is not related to the Retention Policy.  So it IS important to ensure that you have enough space for the controlfile to grow in case it needs to expand space for these records.

Also, remember that not all Retention Policies are defined in terms of days.  Some may be defined in terms of REDUNDANCY (the *number* of Full / L0 backups that are not to be obsoleted).  This does NOT relate to the number of days because Oracle can't predict how many backups you run in a day / in a week / in a month.  Take an organisation with a small database and runs 3 Full / L0 backups per day versus another with a very large database that runs Full / L0 backup only once a fortnight !  How many days of Full / L0 backups would each have to retain if the REDUNDANCY is set to, say, 3 ?

.
.
.




Categories: DBA Blogs

RMAN -- 5c : (Some More) Useful KEYWORDs and SubClauses

Sun, 2015-07-26 08:29
Here are a few more useful KEYWORDs and SubClauses


AS COPY  and   COPY OF
Unlike the BACKUPSET format that is the default for an RMAN Backup, Image Copy backups (those that would be akin to backups created as User Managed Backups without RMAN) can be created in RMAN using the AS COPY specifer.   COPY OF allows backups of such backup copies.

Thus, I take an Image Copy backup of a datafile while the database is OPEN :

RMAN> backup as copy datafile 7 ;       

Starting backup at 26-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
output file name=/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9s6b4o_.dbf tag=TAG20150726T214649 RECID=2 STAMP=886110422
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 26-JUL-15

Starting Control File and SPFILE Autobackup at 26-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_26/o1_mf_s_886110425_bv9s6trt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUL-15

RMAN>

Did you note how datafile 7 was copied to '/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9s6b4o_.dbf' by the BACKUP AS COPY command ?
Without me specifying a FORMAT, it created the copy in the datafile location under the FRA, not in the backupset location.
Next, I take a backup of this Image Copy backup.

RMAN> backup copy of datafile 7;

Starting backup at 26-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: including datafile copy of datafile 00007 in backup set
input file name=/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9s6b4o_.dbf
channel ORA_DISK_1: starting piece 1 at 26-JUL-15
channel ORA_DISK_1: finished piece 1 at 26-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_26/o1_mf_nnndf_TAG20150726T214939_bv9scm4h_.bkp tag=TAG20150726T214939 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-JUL-15

Starting Control File and SPFILE Autobackup at 26-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_26/o1_mf_s_886110582_bv9scp9w_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUL-15

RMAN>

This time the copy in the datafile location was backed up to a backupset location.  This new backup is not an Image Copy format of datafile 7.  Note the differences in the filenames.   The Image Copy done with BACKUP AS COPY has an OMF filename similar to that of the source datafile.  The BackupSet format includes the TAG as part of the BackupPiece filename.

Let's run some checks, from RMAN and SQLPlus :

SQL> select file_name from dba_data_files where file_id=7;

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

SQL>

RMAN> list backup of datafile 7 completed after "trunc(sysdate)";

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
91 Full 7.40M DISK 00:00:01 26-JUL-15
BP Key: 103 Status: AVAILABLE Compressed: YES Tag: TAG20150726T214939
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_26/o1_mf_nnndf_TAG20150726T214939_bv9scm4h_.bkp
List of Datafiles in backup set 91
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 14141418 26-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

RMAN> list copy of datafile 7;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 7 A 26-JUL-15 14141418 26-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9s6b4o_.dbf
Tag: TAG20150726T214649


RMAN>

The LIST BACKUP command shows me the BackupSet backup of the Image Copy. If I want to see the Image Copy that I created first, I must run the command LIST COPY. LIST BACKUP shows BackupSets, not Image Copies themselves. Image Copies are displayed by the LIST COPY.

What is the advantage of Image Copy Backups ?  There are a few.
1)  You can integrate this with your User Managed Backups methods.
2)  You can "clone" a database without having to run a RESTORE(yes, with BACKUP AS COPY DATABASE)
3)  You can selectively relocate one or more datafiles with additonal usage of the SWITCH DATAFILE TO COPY command (see my previous post "BACKUP AS COPY")

Let me demonstrate advantage 3 with a tablespace.

SQL> select file_name from dba_data_files where tablespace_name = 'HEMANT';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf

SQL>

RMAN> backup as copy tablespace HEMANT;

Starting backup at 26-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
output file name=/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tkkj1_.dbf tag=TAG20150726T220953 RECID=4 STAMP=886111799
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
output file name=/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tkrl9_.dbf tag=TAG20150726T220953 RECID=5 STAMP=886111807
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
output file name=/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tkzo0_.dbf tag=TAG20150726T220953 RECID=6 STAMP=886111825
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
output file name=/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tlrr3_.dbf tag=TAG20150726T220953 RECID=7 STAMP=886111843
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
output file name=/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tm7z2_.dbf tag=TAG20150726T220953 RECID=8 STAMP=886111860
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 26-JUL-15

Starting Control File and SPFILE Autobackup at 26-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_26/o1_mf_s_886111863_bv9tmq38_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUL-15

RMAN>
RMAN> sql 'alter tablespace HEMANT offline';

sql statement: alter tablespace HEMANT offline

RMAN> switch tablespace HEMANT to copy;

datafile 6 switched to datafile copy "/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tkkj1_.dbf"
datafile 7 switched to datafile copy "/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tkrl9_.dbf"
datafile 8 switched to datafile copy "/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tkzo0_.dbf"
datafile 9 switched to datafile copy "/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tlrr3_.dbf"
datafile 11 switched to datafile copy "/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tm7z2_.dbf"

RMAN> recover tablespace HEMANT;

Starting recover at 26-JUL-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 26-JUL-15

RMAN> sql 'alter tablespace HEMANT online';

sql statement: alter tablespace HEMANT online

RMAN>

SQL> select file_name from dba_data_files where tablespace_name = 'HEMANT';

FILE_NAME
--------------------------------------------------------------------------------
/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tkkj1_.dbf
/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tkrl9_.dbf
/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tkzo0_.dbf
/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tlrr3_.dbf
/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9tm7z2_.dbf

SQL>

Note how all the datafiles of the tablespace were copied and then the active copy of the datafiles has been switched to the new location (/NEW_FS/oracle/FRA/HEMANTDB/datafile/). Have the old datafiles (/home/oracle/app/oracle/oradata/HEMANTDB/datafile/) been deleted ?
Let's see :

RMAN> list copy of tablespace HEMANT;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
9 6 A 26-JUL-15 14142997 26-JUL-15
Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf

10 7 A 26-JUL-15 14142997 26-JUL-15
Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

2 7 A 26-JUL-15 14141418 26-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9s6b4o_.dbf
Tag: TAG20150726T214649

11 8 A 26-JUL-15 14142997 26-JUL-15
Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf

12 9 A 26-JUL-15 14142997 26-JUL-15
Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf

13 11 A 26-JUL-15 14142997 26-JUL-15
Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf


RMAN>
RMAN> delete copy of tablespace HEMANT;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
9 6 A 26-JUL-15 14142997 26-JUL-15
Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf

10 7 A 26-JUL-15 14142997 26-JUL-15
Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

2 7 A 26-JUL-15 14141418 26-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9s6b4o_.dbf
Tag: TAG20150726T214649

11 8 A 26-JUL-15 14142997 26-JUL-15
Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf

12 9 A 26-JUL-15 14142997 26-JUL-15
Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf

13 11 A 26-JUL-15 14142997 26-JUL-15
Name: /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf RECID=9 STAMP=886111907
deleted datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf RECID=10 STAMP=886111907
deleted datafile copy
datafile copy file name=/NEW_FS/oracle/FRA/HEMANTDB/datafile/o1_mf_hemant_bv9s6b4o_.dbf RECID=2 STAMP=886110422
deleted datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf RECID=11 STAMP=886111907
deleted datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf RECID=12 STAMP=886111907
deleted datafile copy
datafile copy file name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf RECID=13 STAMP=886111907
Deleted 6 objects


RMAN>

(Note how datafile 7 had two datafile copies). I could delete the old copies of the datafiles.

 Note : The example with tablespace HEMANT uses OMF files. If I had non-OMF files, I could use the "%b" FORMAT modifier -- as demonstrated here.



ARCHIVELOG LIKE
The LIKE Keyword allows you to identify individual or groups of ArchiveLogs.

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name HEMANTDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
14 1 628 A 13-JUN-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_628_bsgrjztp_.arc

18 1 629 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_629_bsgrk0tw_.arc

16 1 630 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_630_bsgrk48j_.arc

22 1 631 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_631_bsgv6f02_.arc

17 1 631 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_631_bsgrk49w_.arc

23 1 632 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_632_bsgv6f1y_.arc

15 1 632 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_632_bsgrk8od_.arc

24 1 633 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_633_bsgv6f36_.arc

25 1 1 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_08/o1_mf_1_1_bst8r4yr_.arc

26 1 2 A 08-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_08/o1_mf_1_2_bstbf4nw_.arc

27 1 3 A 08-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_26/o1_mf_1_3_bv9vbq7c_.arc

28 1 4 A 26-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_26/o1_mf_1_4_bv9vbr1p_.arc

29 1 5 A 26-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_26/o1_mf_1_5_bv9vbtwz_.arc


RMAN> list archivelog like '%2015_07_26%';

List of Archived Log Copies for database with db_unique_name HEMANTDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
27 1 3 A 08-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_26/o1_mf_1_3_bv9vbq7c_.arc

28 1 4 A 26-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_26/o1_mf_1_4_bv9vbr1p_.arc

29 1 5 A 26-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_26/o1_mf_1_5_bv9vbtwz_.arc


RMAN> list archivelog like '%_6%';

List of Archived Log Copies for database with db_unique_name HEMANTDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
14 1 628 A 13-JUN-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_628_bsgrjztp_.arc

18 1 629 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_629_bsgrk0tw_.arc

16 1 630 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_630_bsgrk48j_.arc

22 1 631 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_631_bsgv6f02_.arc

17 1 631 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_631_bsgrk49w_.arc

23 1 632 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_632_bsgv6f1y_.arc

15 1 632 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_632_bsgrk8od_.arc

24 1 633 A 04-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_633_bsgv6f36_.arc

27 1 3 A 08-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_26/o1_mf_1_3_bv9vbq7c_.arc

28 1 4 A 26-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_26/o1_mf_1_4_bv9vbr1p_.arc

29 1 5 A 26-JUL-15
Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_26/o1_mf_1_5_bv9vbtwz_.arc


RMAN>

I can take advantage of this to backups of selective archivelogs.

RMAN> backup as compressed backupset archivelog like '%2015_07_26%';

Starting backup at 26-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=27 STAMP=886112599
input archived log thread=1 sequence=4 RECID=28 STAMP=886112600
input archived log thread=1 sequence=5 RECID=29 STAMP=886112602
channel ORA_DISK_1: starting piece 1 at 26-JUL-15
channel ORA_DISK_1: finished piece 1 at 26-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_26/o1_mf_annnn_TAG20150726T222529_bv9vgs8r_.bkp tag=TAG20150726T222529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 26-JUL-15

Starting Control File and SPFILE Autobackup at 26-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_26/o1_mf_s_886112730_bv9vgtc5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-JUL-15

RMAN>

This is useful if you have been switching the archivelog destination to different locations during the course of the day.
.
.
.

Categories: DBA Blogs

RMAN -- 5b : (More) Useful KEYWORDs and SubClauses

Sun, 2015-07-19 09:37
Here are a few more useful KEYWORDs and SubClauses

SECTION SIZE 
SECTION SIZE allows you to split a single Database file into multiple sections.  Note : A Database File  span BackupPieces.  The difference between BackupPieces and Sections is that the former are serially done while Sections can be done in-parallel.  (SECTION SIZE cannot be used with MAXPIECESIZE).  Here, I first show a datafile in 4 BackupPieces defined by SECTION SIZE.

SQL> select file_id, size_mb
2 from
3 (select file_id, bytes/1048576 size_mb
4 from dba_data_files
5 order by 2 desc)
6 where rownum = 1;

FILE_ID SIZE_MB
---------- ----------
2 1259

SQL>

RMAN> backup datafile 2 section size 400M;

Starting backup at 19-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 device type=DISK
channel ORA_DISK_1: starting compressed 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
backing up blocks 1 through 51200
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf5020_.bkp tag=TAG20150719T223055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting compressed 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
backing up blocks 51201 through 102400
channel ORA_DISK_1: starting piece 2 at 19-JUL-15
channel ORA_DISK_1: finished piece 2 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf5sw2_.bkp tag=TAG20150719T223055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed 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
backing up blocks 102401 through 153600
channel ORA_DISK_1: starting piece 3 at 19-JUL-15
channel ORA_DISK_1: finished piece 3 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf76wl_.bkp tag=TAG20150719T223055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting compressed 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
backing up blocks 153601 through 161152
channel ORA_DISK_1: starting piece 4 at 19-JUL-15
channel ORA_DISK_1: finished piece 4 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf8y7p_.bkp tag=TAG20150719T223055 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-JUL-15

Starting Control File and SPFILE Autobackup at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885508385_btqf92c7_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-15

RMAN>
RMAN> list backup of datafile 2 completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68 Full 452.02M DISK 00:02:09 19-JUL-15
List of Datafiles in backup set 68
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 14135401 19-JUL-15 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

Backup Set Copy #1 of backup set 68
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:02:09 19-JUL-15 YES TAG20150719T223055

List of Backup Pieces for backup set 68 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
71 1 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf5020_.bkp
72 2 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf5sw2_.bkp
73 3 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf76wl_.bkp
74 4 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T223055_btqf8y7p_.bkp

RMAN>


Note how the blocks of the datafile are allocated to each Section.  Section 1 is for Blocks 1 to 51200.  Section 2 is for Blocks 51201 to 102400.  And so on for subsequent Sections.
Next, I run two channels for in-parallel backups of the sections, still comprising of one BackupSet for the datafile.

RMAN> run
2> {
3> allocate channel d1 device type disk;
4> allocate channel d2 device type disk;
5> backup datafile 2 section size 400M;
6> }

released channel: ORA_DISK_1
allocated channel: d1
channel d1: SID=40 device type=DISK

allocated channel: d2
channel d2: SID=44 device type=DISK

Starting backup at 19-JUL-15
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 1 through 51200
channel d1: starting piece 1 at 19-JUL-15
channel d2: starting compressed full datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 51201 through 102400
channel d2: starting piece 2 at 19-JUL-15
channel d1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvrr_.bkp tag=TAG20150719T224411 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:26
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 102401 through 153600
channel d1: starting piece 3 at 19-JUL-15
channel d2: finished piece 2 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvvv_.bkp tag=TAG20150719T224411 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:51
channel d2: starting compressed full datafile backup set
channel d2: specifying datafile(s) in backup set
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
backing up blocks 153601 through 161152
channel d2: starting piece 4 at 19-JUL-15
channel d2: finished piece 4 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfzglf_.bkp tag=TAG20150719T224411 comment=NONE
channel d2: backup set complete, elapsed time: 00:00:03
channel d1: finished piece 3 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfyo8v_.bkp tag=TAG20150719T224411 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:50
Finished backup at 19-JUL-15

Starting Control File and SPFILE Autobackup at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885509127_btqg07qq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-15
released channel: d1
released channel: d2

RMAN> list backupset 72;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72 Full 452.02M DISK 00:01:13 19-JUL-15
List of Datafiles in backup set 72
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 14136475 19-JUL-15 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

Backup Set Copy #1 of backup set 72
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:01:13 19-JUL-15 YES TAG20150719T224411

List of Backup Pieces for backup set 72 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
81 1 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvrr_.bkp
82 2 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvvv_.bkp
84 3 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfyo8v_.bkp
83 4 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfzglf_.bkp

RMAN>

Thus, we have BackupSet 72 consisting of the 4 BackupPieces.  The BackupPieces were created using 2 Channels running in-parallel with different ranges of Blocks.



FORMAT (and the FRA)
If you use the FRA with db_recovery_file_dest, Oracle tracks usage against the limit specified by db_recovery_file_dest_size.
However, if you use the FORMAT clause, such backups are *not* tracked as being part of the FRA.  This also means that Oracle would under-report usage of the FRA and wouldn't be able to identify when the FRA nears the limit.

For example, I first report the FRA usage and then run Backups without and and then with the FORMAT clause.  Note how the FRA usage report reflects the Backups without the FORMAT clause only.

SQL> set linesize 132
SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /NEW_FS/oracle/FRA
db_recovery_file_dest_size big integer 8G

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .27 0 10
BACKUP PIECE 32.96 22.4 34
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

SQL>

RMAN> backup as compressed backupset tablespace hemant filesperset=1;

Starting backup at 19-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh2vkb_.bkp tag=TAG20150719T230355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh2yoc_.bkp tag=TAG20150719T230355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh31vh_.bkp tag=TAG20150719T230355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh34ys_.bkp tag=TAG20150719T230355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh382g_.bkp tag=TAG20150719T230355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-JUL-15

Starting Control File and SPFILE Autobackup at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885510251_btqh3c9d_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-15

RMAN>

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .27 0 10
BACKUP PIECE 33.54 22.92 40
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

SQL>

RMAN> backup as compressed backupset tablespace hemant format '/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/%U' filesperset=1;

Starting backup at 19-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/2vqcfk8t_1_1 tag=TAG20150719T230709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/30qcfk90_1_1 tag=TAG20150719T230709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/31qcfk93_1_1 tag=TAG20150719T230709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/32qcfk96_1_1 tag=TAG20150719T230709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/33qcfk9a_1_1 tag=TAG20150719T230709 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-JUL-15

Starting Control File and SPFILE Autobackup at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885510445_btqh9f6x_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-15

RMAN>

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .27 0 10
BACKUP PIECE 33.65 23.5 41
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0

7 rows selected.

SQL>

Note how the first backup (which did NOT specify a FORMAT clause) consisted of 5 BackupPieces for the Datafiles plus 1 for the AutoBackup. The NUMBER_OF_FILES for BACKUP PIECE in v$flash_recovery_area_usage was updaetd from 34 to 40.
However, while the second backup with the FORMAT clause (actually pointing to the same physical directory) created 5 Datafile BackupPieces and 1 AutoBackup again, the count in v$flash_recovery_area_usage did NOT increment by 6  (the increment by 1 is for the AutoBackup).

Thus, for this second, with FORMAT clause, backup, while a LIST BACKUP command would show it and the backup would be usable for Restore scenarios, v$flash_recovery_area_usage does not track it.  Therefore, Oracle hasn't incremented the PERCENT_SPACE_USED either.  This PERCENT_SPACE_USED is very important for Oracle to automatically purge older (i.e. OBSOLETE) backups when space usage hits critical limits.



TAG
The TAG clause allows us to specifically define our own Tags.  Note my previous backups showed a TAG that indicates merely date and time as in :

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
28 B F A DISK 23-JUN-15 1 1 NO TAG20150623T170721
29 B F A DISK 23-JUN-15 1 1 NO TAG20150623T170846
30 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
31 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
32 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
33 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
34 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
35 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
36 B F A DISK 04-JUL-15 1 1 NO TAG20150704T131927
37 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
38 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
39 B F A DISK 08-JUL-15 1 1 NO TAG20150708T211118
40 B F A DISK 08-JUL-15 1 1 NO TAG20150708T215526
60 B F A DISK 12-JUL-15 1 1 YES TAG20150712T222911
61 B F A DISK 12-JUL-15 1 1 NO TAG20150712T222957
62 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
63 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
64 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
65 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
66 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
67 B F A DISK 12-JUL-15 1 1 NO TAG20150712T223226
68 B F A DISK 19-JUL-15 4 1 YES TAG20150719T223055
69 B F A DISK 19-JUL-15 1 1 NO TAG20150719T223305
71 B F A DISK 19-JUL-15 1 1 NO TAG20150719T224023
72 B F A DISK 19-JUL-15 4 1 YES TAG20150719T224411
73 B F A DISK 19-JUL-15 1 1 NO TAG20150719T224527
74 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230053
75 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230119
76 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
77 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
78 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
79 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
80 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
81 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230411
82 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
83 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
84 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
85 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
86 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
87 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230725

RMAN>

However, I can use the TAG clause to identify specific Backups distinctively. Later, for the RESTORE command, the known TAG becomes useful as I can RESTORE FROM TAG. Here's how I create backups with specific TAGs.

RMAN> backup as compressed backupset tablespace HEMANT TAG 'Hemant_19Jul15';

Starting backup at 19-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUL-15
channel ORA_DISK_1: finished piece 1 at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_HEMANT_19JUL15_btqhy6js_.bkp tag=HEMANT_19JUL15 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 19-JUL-15

Starting Control File and SPFILE Autobackup at 19-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885511125_btqhyoo9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUL-15

RMAN>
RMAN> list backup of tablespace hemant completed after "(sysdate-5/1440)";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
88 Full 33.82M DISK 00:00:07 19-JUL-15
BP Key: 100 Status: AVAILABLE Compressed: YES Tag: HEMANT_19JUL15
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_HEMANT_19JUL15_btqhy6js_.bkp
List of Datafiles in backup set 88
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 14138686 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
7 Full 14138686 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
8 Full 14138686 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
9 Full 14138686 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
11 Full 14138686 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf

RMAN> list backup of tablespace HEMANT summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
37 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
38 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
60 B F A DISK 12-JUL-15 1 1 YES TAG20150712T222911
62 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
63 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
64 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
65 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
66 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
74 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230053
76 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
77 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
78 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
79 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
80 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
82 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
83 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
84 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
85 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
86 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
88 B F A DISK 19-JUL-15 1 1 YES HEMANT_19JUL15

RMAN>

Notice how the latest backup of Tablespace HEMANT is tagged by the TAG I had specified during the BACKUP run.




completed after "..date/time clause"
Note how, in the listings above, I have used the 'completed after "trunc(sysdate)" '  and  'completed after "(sysdate-5/1440)" to specify a Date/Time as a filter.

Here's another example to show Backups completed in the month of July-2015. (The two backups of 23-Jun-2015 are excluded).

RMAN> list backup summary completed after "to_date('JUL-15','MON-RR')";


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
30 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
31 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
32 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
33 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
34 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
35 B F A DISK 04-JUL-15 1 1 YES TAG20150704T121859
36 B F A DISK 04-JUL-15 1 1 NO TAG20150704T131927
37 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
38 B F A DISK 08-JUL-15 1 1 YES TAG20150708T211100
39 B F A DISK 08-JUL-15 1 1 NO TAG20150708T211118
40 B F A DISK 08-JUL-15 1 1 NO TAG20150708T215526
60 B F A DISK 12-JUL-15 1 1 YES TAG20150712T222911
61 B F A DISK 12-JUL-15 1 1 NO TAG20150712T222957
62 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
63 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
64 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
65 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
66 B F A DISK 12-JUL-15 1 1 YES TAG20150712T223210
67 B F A DISK 12-JUL-15 1 1 NO TAG20150712T223226
68 B F A DISK 19-JUL-15 4 1 YES TAG20150719T223055
69 B F A DISK 19-JUL-15 1 1 NO TAG20150719T223305
71 B F A DISK 19-JUL-15 1 1 NO TAG20150719T224023
72 B F A DISK 19-JUL-15 4 1 YES TAG20150719T224411
73 B F A DISK 19-JUL-15 1 1 NO TAG20150719T224527
74 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230053
75 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230119
76 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
77 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
78 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
79 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
80 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230355
81 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230411
82 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
83 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
84 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
85 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
86 B F A DISK 19-JUL-15 1 1 YES TAG20150719T230709
87 B F A DISK 19-JUL-15 1 1 NO TAG20150719T230725
88 B F A DISK 19-JUL-15 1 1 YES HEMANT_19JUL15
89 B F A DISK 19-JUL-15 1 1 NO TAG20150719T231845

RMAN>

This shows that I can use Date Format masks in my filter.

Here's another example (note : if you use 'between', it seems that the two date formats must match, else no records are returned as in the first listing below):

RMAN> list backup completed between
2> "to_date('19-JUL-2015 22:45:00','DD-MON-YYYY HH24:MI:SS')"
3> and
4> "to_date(sysdate)";

specification does not match any backup in the repository

RMAN> list backup completed between
2> "to_date('19-JUL-2015 22:45:00','DD-MON-YYYY HH24:MI:SS')"
3> and
4> "to_date('19-JUL-2015 23:15:00','DD-MON-YYYY HH24:MI:SS')";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72 Full 452.02M DISK 00:01:13 19-JUL-15
List of Datafiles in backup set 72
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 14136475 19-JUL-15 /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

Backup Set Copy #1 of backup set 72
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:01:13 19-JUL-15 YES TAG20150719T224411

List of Backup Pieces for backup set 72 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
81 1 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvrr_.bkp
82 2 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfxvvv_.bkp
84 3 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfyo8v_.bkp
83 4 AVAILABLE /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T224411_btqfzglf_.bkp

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
73 Full 9.36M DISK 00:00:00 19-JUL-15
BP Key: 85 Status: AVAILABLE Compressed: NO Tag: TAG20150719T224527
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885509127_btqg07qq_.bkp
SPFILE Included: Modification time: 19-JUL-15
SPFILE db_unique_name: HEMANTDB
Control File Included: Ckp SCN: 14136581 Ckp time: 19-JUL-15

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
74 Full 33.82M DISK 00:00:23 19-JUL-15
BP Key: 86 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230053
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230053_btqgx5vx_.bkp
List of Datafiles in backup set 74
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 14137744 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
7 Full 14137744 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
8 Full 14137744 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
9 Full 14137744 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
11 Full 14137744 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
75 Full 9.36M DISK 00:00:00 19-JUL-15
BP Key: 87 Status: AVAILABLE Compressed: NO Tag: TAG20150719T230119
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885510079_btqgxzd8_.bkp
SPFILE Included: Modification time: 19-JUL-15
SPFILE db_unique_name: HEMANTDB
Control File Included: Ckp SCN: 14137759 Ckp time: 19-JUL-15

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
76 Full 7.41M DISK 00:00:02 19-JUL-15
BP Key: 88 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230355
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh2vkb_.bkp
List of Datafiles in backup set 76
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 14137868 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
77 Full 7.40M DISK 00:00:02 19-JUL-15
BP Key: 89 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230355
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh2yoc_.bkp
List of Datafiles in backup set 77
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 14137870 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
78 Full 7.38M DISK 00:00:02 19-JUL-15
BP Key: 90 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230355
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh31vh_.bkp
List of Datafiles in backup set 78
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
8 Full 14137872 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
79 Full 7.43M DISK 00:00:02 19-JUL-15
BP Key: 91 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230355
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh34ys_.bkp
List of Datafiles in backup set 79
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
9 Full 14137874 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
80 Full 7.91M DISK 00:00:01 19-JUL-15
BP Key: 92 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230355
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/o1_mf_nnndf_TAG20150719T230355_btqh382g_.bkp
List of Datafiles in backup set 80
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 14137876 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
81 Full 9.36M DISK 00:00:00 19-JUL-15
BP Key: 93 Status: AVAILABLE Compressed: NO Tag: TAG20150719T230411
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885510251_btqh3c9d_.bkp
SPFILE Included: Modification time: 19-JUL-15
SPFILE db_unique_name: HEMANTDB
Control File Included: Ckp SCN: 14137883 Ckp time: 19-JUL-15

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
82 Full 7.41M DISK 00:00:01 19-JUL-15
BP Key: 94 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230709
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/2vqcfk8t_1_1
List of Datafiles in backup set 82
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 14138235 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
83 Full 7.40M DISK 00:00:01 19-JUL-15
BP Key: 95 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230709
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/30qcfk90_1_1
List of Datafiles in backup set 83
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
7 Full 14138237 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
84 Full 7.38M DISK 00:00:02 19-JUL-15
BP Key: 96 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230709
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/31qcfk93_1_1
List of Datafiles in backup set 84
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
8 Full 14138239 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
85 Full 7.43M DISK 00:00:02 19-JUL-15
BP Key: 97 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230709
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/32qcfk96_1_1
List of Datafiles in backup set 85
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
9 Full 14138241 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
86 Full 7.91M DISK 00:00:01 19-JUL-15
BP Key: 98 Status: AVAILABLE Compressed: YES Tag: TAG20150719T230709
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_19/33qcfk9a_1_1
List of Datafiles in backup set 86
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 14138243 19-JUL-15 /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
87 Full 9.36M DISK 00:00:00 19-JUL-15
BP Key: 99 Status: AVAILABLE Compressed: NO Tag: TAG20150719T230725
Piece Name: /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_19/o1_mf_s_885510445_btqh9f6x_.bkp
SPFILE Included: Modification time: 19-JUL-15
SPFILE db_unique_name: HEMANTDB
Control File Included: Ckp SCN: 14138250 Ckp time: 19-JUL-15

RMAN>

Thus, you can identify a list of backups by time ranges as well !
.
.
.

Categories: DBA Blogs

Monitoring and Diagnostics without Oracle Enterprise Manager

Fri, 2015-07-17 07:22
Here's a copy of a presentation I made at Singapore Oracle Sessions - III ,14-Jul-2015 on the topic of Monitoring and Diagnostics without Oracle Enterprise Manager.

.
.
.

Categories: DBA Blogs

RMAN -- 5 : Useful KEYWORDs and SubClauses

Sun, 2015-07-12 04:55
I begin a subseries on useful KEYWORDs and SubClauses in the RMAN Command Set.

This is on the less-commonly used KEYWORDs  (Thus, I don't show CONFIGURE CONTROLFILE AUTOBACKUP ON in this post or SET UNTIL in the next post).

(Note that specifications defined with CONFIGURE become "persistent" but can be overridden in the specific BACKUP or RESTORE runs)



COMPRESSED BACKUPSET
By default BackupSets are non-Compressed.  However, COMPRESSED BACKUPSET can be specified in either the BACKUP command OR in the CONFIGURE command.
RMAN> configure device type disk parallelism 2 backup type to compressed backupset;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4

RMAN>

RMAN> backup tablespace hemant;

Starting backup at 12-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=29 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-15
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
channel ORA_DISK_2: starting piece 1 at 12-JUL-15
channel ORA_DISK_2: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T182836_bt4jbnxh_.bkp tag=TAG20150712T182836 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T182836_bt4jbnx1_.bkp tag=TAG20150712T182836 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 12-JUL-15

Starting Control File and SPFILE Autobackup at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_12/o1_mf_s_884888932_bt4jc47o_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUL-15

RMAN>
Note how I did not have to specify "AS COMPRESSED BACKUPSET" in the BACKUP command. The CONFIGURE command had configured the DISK device type backups to be COMPRESSED BACKUPSETs as "the default".  I can override the *configured* COMPRESSED BACKUPSET by including the "AS BACKUPSET" Keywords in the BACKUP command. (Out-of-the-box, RMAN is NOT configured for COMPRESSED BACKUPSET !)



PARALLELISM in the deviceConf SubClause of the CONFIGURE command
This allows RMAN to automatically create multiple Channels when running a Backup.  Therefore, individual CONFIGURE CHANNEL commands are not required.  Note : each Channel creates a separate BackupSet !

With PARALLELISM 2, we see 2 Channels ORA_DISK_1 and ORA_DISK_2 created and used :
RMAN> configure device type disk parallelism 2 backup type to backupset;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN>
RMAN> backup tablespace HEMANT;

Starting backup at 12-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=29 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-15
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
channel ORA_DISK_2: starting piece 1 at 12-JUL-15
channel ORA_DISK_2: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T182024_bt4hv96z_.bkp tag=TAG20150712T182024 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:36
channel ORA_DISK_1: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T182024_bt4hv973_.bkp tag=TAG20150712T182024 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 12-JUL-15

Starting Control File and SPFILE Autobackup at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_12/o1_mf_s_884888462_bt4hwn9c_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUL-15

RMAN>

With PARALLELISM 4, we see 4 Channels ORA_DISK_1 to ORA_DISK_4 being created :
RMAN> configure device type disk parallelism 4 backup type to backupset;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2

RMAN>
RMAN> backup tablespace HEMANT;

Starting backup at 12-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=29 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=32 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=28 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-15
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_2: starting piece 1 at 12-JUL-15
channel ORA_DISK_3: starting full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
channel ORA_DISK_3: starting piece 1 at 12-JUL-15
channel ORA_DISK_1: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T182301_bt4j05dl_.bkp tag=TAG20150712T182301 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_2: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T182301_bt4j05f7_.bkp tag=TAG20150712T182301 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_3: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T182301_bt4j05jz_.bkp tag=TAG20150712T182301 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:03
Finished backup at 12-JUL-15

Starting Control File and SPFILE Autobackup at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_12/o1_mf_s_884888584_bt4j08mc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUL-15

RMAN>

In the latter case, although 4 Channels were created, RMAN actually used only 3 Channels.  So, RMAN can still automatically adjust the allocation of datafiles across the Channels.



MAXSETSIZE in the BACKUP command.
A BACKUP run (whether at Database level or for specific Tablespace(s) or Datafiles or ArchiveLogs), by default, creates BackupSets.  However, a BACKUP run can create multiple BackupSets.
This can happen when :
a.  The number of datafiles (or ArchiveLogs) in the Backup is "large"  (e.g. by default, unless FILESPERSET is specified, a max of 64 datafiles make up one BackupSet).
b.  Multiple Channels are used for the BACKUP run
c.  The PARALLEL Keyword is used  (as demonstrated above)
The MAXSETSIZE is actually a way to specify the maximum size of a BackupSet.  A datafile cannot span BackupSets, so the MAXSETSIZE must be, at least, as large as the largest datafile in the database.

SQL> select max(bytes)/1048576 from dba_data_files;

MAX(BYTES)/1048576
------------------
1259

SQL>

RMAN> backup as compressed backupset database maxsetsize 1200M;

Starting backup at 12-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 07/12/2015 18:14:37
RMAN-06183: datafile or datafile copy /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf (file number 2) larger than MAXSETSIZE

RMAN>

Thus, we can see that Oracle automatically checks the largest datafile against the MAXSETSIZE. Note, also, that it does not estimate a Compressed size for this check, even though I've specified "AS COMPRESSED BACKUPSET".



MAXPIECESIZE
Just as a Backup can consist of multiple BackupSets, a BackupSet can consist of multiple BackupPieces.  A BackupSet can consist of multiple datafiles and a datafile may be split across multiple BackupPieces. However, unlike the MAXSETSIZE that can be specified with the BACKUP command, MAXPIECESIZE is specified with the CONFIGURE CHANNEL. (However, unlike the MAXSETSIZE which runs a check before beginning the backup, this limit is applied when creating backuppieces !)

RMAN> configure device type disk parallelism 1 backup type to compressed backupset;

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2

RMAN> configure channel device type disk maxpiecesize 25M;

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 25 M;
new RMAN configuration parameters are successfully stored


RMAN> configure channel device type disk maxpiecesize 5M;

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 25 M;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 5 M;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

RMAN> backup datafile 6;

Starting backup at 12-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-15
channel ORA_DISK_1: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T184704_bt4kf8jm_.bkp tag=TAG20150712T184704 comment=NONE
channel ORA_DISK_1: starting piece 2 at 12-JUL-15
channel ORA_DISK_1: finished piece 2 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T184704_bt4kf9jn_.bkp tag=TAG20150712T184704 comment=NONE
channel ORA_DISK_1: starting piece 3 at 12-JUL-15
channel ORA_DISK_1: finished piece 3 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T184704_bt4kfbjs_.bkp tag=TAG20150712T184704 comment=NONE
channel ORA_DISK_1: starting piece 4 at 12-JUL-15
channel ORA_DISK_1: finished piece 4 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T184704_bt4kfcjy_.bkp tag=TAG20150712T184704 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 12-JUL-15

Starting Control File and SPFILE Autobackup at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_12/o1_mf_s_884890028_bt4kfdn9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUL-15

RMAN>

SQL> !du -sh /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T184704*
3.1M /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T184704_bt4kf8jm_.bkp
3.1M /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T184704_bt4kf9jn_.bkp
3.1M /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T184704_bt4kfbjs_.bkp
68K /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T184704_bt4kfcjy_.bkp

SQL>

The configured MAXPIECESIZE limit applied to the compressed piecesize. !



FILESPERSET
I have mentioned FILESPERSET earlier in this post and in another blog post.  This Keyword allows us to limit a BackupSet to a certain number of files.

RMAN> backup as compressed backupset tablespace HEMANT filesperset=6;

Starting backup at 12-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-15
channel ORA_DISK_1: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T222911_bt4yfrd6_.bkp tag=TAG20150712T222911 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 12-JUL-15

Starting Control File and SPFILE Autobackup at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_12/o1_mf_s_884903397_bt4yh670_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUL-15

RMAN>

With FILEPERSET=6, all 5 datafiles are in one BackupSet (as long as the total size is less than the limit on a BackupSet Size / MAXSETSIZE).

Now, let's rerun the backup with FILESPERSET=1

RMAN> backup as compressed backupset tablespace HEMANT filesperset=1;

Starting backup at 12-JUL-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-15
channel ORA_DISK_1: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T223210_bt4ymbh5_.bkp tag=TAG20150712T223210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-15
channel ORA_DISK_1: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T223210_bt4ymfnl_.bkp tag=TAG20150712T223210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-15
channel ORA_DISK_1: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T223210_bt4ymjq8_.bkp tag=TAG20150712T223210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-15
channel ORA_DISK_1: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T223210_bt4ymmt5_.bkp tag=TAG20150712T223210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf
channel ORA_DISK_1: starting piece 1 at 12-JUL-15
channel ORA_DISK_1: finished piece 1 at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_12/o1_mf_nnndf_TAG20150712T223210_bt4ympxr_.bkp tag=TAG20150712T223210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 12-JUL-15

Starting Control File and SPFILE Autobackup at 12-JUL-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_12/o1_mf_s_884903546_bt4ymtdr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-JUL-15

RMAN>
Now, I can have each datafile go into a separate BackupSet. The advantage of using FILESPERSET in one context was shown in a previous post. Another advantage is obvious when we have to restore a single datafile. If we had 16 datafiles in a BackupSet, and we need to restore a single datafile, RMAN still has to read the entire BackupSet of 16 datafiles to be able to restore that single datafile.  If each BackupSet was limited in size by using MAXSETSIZE and/or FILESPERSET, restoring a single datafile can be faster as the Restore has to read a smaller BackupSet.

.
.
.
Categories: DBA Blogs

RMAN -- 4b : Recovering from an Incomplete Restore with OMF Files

Wed, 2015-07-08 07:51
Following up on my previous post (which had the datafiles as non-OMF), here is a case with OMF files.

SQL> select file_name from dba_data_files
2 where tablespace_name = 'HEMANT';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst84r1w_.dbf
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst850ts_.dbf
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst85312_.dbf
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst85njw_.dbf
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst85qsq_.dbf

SQL>
SQL> !rm /home/oracle/app/oracle/oradata/HEMANTDB/datafile/*hemant*dbf

SQL> shutdown immediate;
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst84r1w_.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL>

I have removed the datafiles for a tablespace. Note that the datafiles are all OMF.  I then attempt to restore the tablespace.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 8 21:15:21 2015

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 390073016 bytes
Database Buffers 58720256 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> select file# from v$datafile
2 where ts# = (select ts# from v$tablespace where name = 'HEMANT')
3 order by 1;

FILE#
----------
6
7
8
9
11

SQL>
SQL> alter database datafile 6 offline;

Database altered.

SQL> alter database datafile 7 offline;

Database altered.

SQL> alter database datafile 8 offline;

Database altered.

SQL> alter database datafile 9 offline;

Database altered.

SQL> alter database datafile 11 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jul 8 21:22:02 2015

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

connected to target database: ORCL (DBID=1229390655)

RMAN>
RMAN> restore tablespace HEMANT;

Starting restore at 08-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8szss_.dbf
channel ORA_DISK_1: restoring datafile 00009 to /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8szxb_.dbf
channel ORA_DISK_1: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_08/o1_mf_nnndf_TAG20150708T211100_bst8c58p_.bkp
channel ORA_DISK_1: piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_08/o1_mf_nnndf_TAG20150708T211100_bst8c58p_.bkp tag=TAG20150708T211100
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8szv5_.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8szwh_.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8t089_.dbf
channel ORA_DISK_1: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_08/o1_mf_nnndf_TAG20150708T211100_bst8c58n_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
ORA-01092: ORACLE instance terminated. Disconnection forced
ORACLE error from target database:
ORA-03135: connection lost contact
Process ID: 3615
Session ID: 29 Serial number: 21

[oracle@localhost ~]$

Once again, the database has crashed in the midst of the RESTORE. Let's check the datafile names.

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 8 21:25:12 2015

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 394267320 bytes
Database Buffers 54525952 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> set pages60
SQL> select file#, name from v$datafile where file# in (6,7,8,9,11) order by 1;

FILE#
----------
NAME
--------------------------------------------------------------------------------
6
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8szv5_.dbf

7
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

8
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8szwh_.dbf

9
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf

11
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8t089_.dbf


SQL>
SQL> select file#, name from v$datafile_header where file# in (6,7,8,9,11) order by 1;

FILE#
----------
NAME
--------------------------------------------------------------------------------
6


7
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

8


9
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf

11



SQL>

[To understand why I queried both V$DATAFILE and V$DATAFILE_HEADER, see my previous post "Datafiles not Restored  --  using V$DATAFILE and V$DATAFILE_HEADER".]

So, datafiles 7 and 9 have been restored. We can see that in the RESTORE log as well -- "backup piece 1" in the RESTORE had datafiles 7 and 9 and was the only one to complete. Let's check the datafile names. Datafiles 7 and 9 are differently named from what they were earlier.  Earlier, they were "%bst85%", now they are "%bst90%".

So, if we want to re-run the restore, we can use SET NEWNAME for datafiles 7 and 9 to allow Oracle to check that they are already restored.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jul 8 21:32:12 2015

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

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> run
2> {set newname for datafile 7 to '/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf';
3> set newname for datafile 9 to '/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf';
4> restore tablespace HEMANT;}

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 08-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

skipping datafile 7; already restored to file /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf
skipping datafile 9; already restored to file /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8szv5_.dbf
channel ORA_DISK_1: restoring datafile 00008 to /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8szwh_.dbf
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst8t089_.dbf
channel ORA_DISK_1: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_08/o1_mf_nnndf_TAG20150708T211100_bst8c58n_.bkp
channel ORA_DISK_1: piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_08/o1_mf_nnndf_TAG20150708T211100_bst8c58n_.bkp tag=TAG20150708T211100
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 08-JUL-15

RMAN>

YES ! Datafiles 7 and 9 were identified as "already restored".
Let's re-check the datafiles and then RECOVER them.

RMAN> exit


Recovery Manager complete.
[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 8 21:37:29 2015

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


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

SQL>
SQL> select name from v$datafile
2 where ts#=(select ts# from v$tablespace where name = 'HEMANT')
3 minus
4 select name from v$datafile_header
5 where ts#=(select ts# from v$tablespace where name = 'HEMANT')
6 /

no rows selected

SQL>
SQL> select * from v$datafile_header where name is null;

no rows selected

SQL>
SQL> recover datafile 6;
Media recovery complete.
SQL> recover datafile 7;
Media recovery complete.
SQL> recover datafile 8;
Media recovery complete.
SQL> recover datafile 9;
Media recovery complete.
SQL> recover datafile 11;
Media recovery complete.
SQL> alter tablespace HEMANT online;
alter tablespace HEMANT online
*
ERROR at line 1:
ORA-01109: database not open


SQL> alter database open;

Database altered.

SQL> alter tablespace HEMANT online;

Tablespace altered.

SQL>
SQL> select owner, segment_name, bytes/1048576 from dba_segments where tablespace_name = 'HEMANT';

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
HEMANT
LARGE_TABLE
272


SQL> select count(*) from hemant.large_table;

COUNT(*)
----------
2404256

SQL>

Yes, I have been able to verify that all the datafiles have been restored.  I have been able to bring the tablespace online and query the data in it.

SQL> set pages60
SQL> select file_id, file_name from dba_data_files where tablespace_name = 'HEMANT';

FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
6
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4vt_.dbf

7
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jct_.dbf

8
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x0_.dbf

9
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst90jf1_.dbf

11
/home/oracle/app/oracle/oradata/HEMANTDB/datafile/o1_mf_hemant_bst9o4x5_.dbf


SQL>

And, yes the datafile names (%bst90%) are different from what they were earlier (%bst84% and %bst85%).

(Reference :  See Oracle Support Note Doc ID 1621319.1)
.
.
.

Categories: DBA Blogs

RMAN -- 4 : Recovering from an Incomplete Restore

Fri, 2015-07-03 23:22
What do you do if a RESTORE fails mid-way ?  Do you need to rerun the whole restore ?  If it is a very large database, it could take [many ?] hours.

RMAN is "smart" enough to detect datafiles that have been restored and not re-attempt a restore.

UPDATE 08-Jul-15 :  For a database with Oracle Managed Files  (OMF), please see Oracle Support Note Doc ID 1621319.1  {Thus, see my next blog post on Recovering from an Incomplete Restore with OMF Files}

This example below is for non-OMF files.

Here, I begin a database restore.

 
RMAN> restore controlfile from autobackup;

Starting restore at 04-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /NEW_FS/oracle/FRA
database name (or database unique name) used for search: HEMANTDB
channel ORA_DISK_1: AUTOBACKUP /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_04/o1_mf_s_884175832_bsgqysyq_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_04/o1_mf_s_884175832_bsgqysyq_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 04-JUL-15

RMAN>
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>
RMAN> restore database;

Starting restore at 04-JUL-15
Starting implicit crosscheck backup at 04-JUL-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 04-JUL-15

Starting implicit crosscheck copy at 04-JUL-15
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 04-JUL-15

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_628_bsgrjztp_.arc
File Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_632_bsgrk8od_.arc
File Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_630_bsgrk48j_.arc
File Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_631_bsgrk49w_.arc
File Name: /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_629_bsgrk0tw_.arc
File Name: /NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_07_04/o1_mf_s_884175832_bsgqysyq_.bkp

using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqonjj_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_2: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqsccg_.bkp
channel ORA_DISK_2: piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqsccg_.bkp tag=TAG20150704T121859
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:02:34
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_2: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqwt4s_.bkp
channel ORA_DISK_2: piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqwt4s_.bkp tag=TAG20150704T121859
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:35
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_2: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqonjl_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
ORA-01092: ORACLE instance terminated. Disconnection forced
ORACLE error from target database:
ORA-03135: connection lost contact
Process ID: 3777
Session ID: 1 Serial number: 9

[oracle@localhost ~]$

After having restored a few datafiles, the restore failed on being disconnected from the database. (The  server or database instance has crashed).  Since the controlfile has been restored, I can bring up the database in MOUNT mode and then re-attempt a RESTORE DATABASE.

[oracle@localhost ~]$ rman target sys/oracle@orcl

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jul 4 12:56:41 2015

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

connected to target database: ORCL (DBID=1229390655, not open)

RMAN>
RMAN> restore database;

Starting restore at 04-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK

skipping datafile 3; already restored to file /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
skipping datafile 4; already restored to file /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqonjj_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_2: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqonjl_.bkp
channel ORA_DISK_1: piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqonjj_.bkp tag=TAG20150704T121859
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf
channel ORA_DISK_1: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqxovh_.bkp
channel ORA_DISK_1: piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqxovh_.bkp tag=TAG20150704T121859
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqxjv6_.bkp
channel ORA_DISK_1: piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqxjv6_.bkp tag=TAG20150704T121859
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_2: piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_07_04/o1_mf_nnndf_TAG20150704T121859_bsgqonjl_.bkp tag=TAG20150704T121859
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:04:02
Finished restore at 04-JUL-15

RMAN>

RMAN detects that datafiles 3 (undotbs01.dbf) and 4 (users01.dbf) had already been restored.
 If you look at the previous RESTORE run, you can see that these were restored by Channel ORA_DISK_2. The first channel ORA_DISK_1 had started restoring system01.dbf but hadn't completed restoring the datafile when the restore crashed. That restore of datafile 1 (system01.dbf) had to be redone.

 (Another thing to note : Oracle doesn't necessarily restore datafiles in the order of file_id (file#) ! There really is no ORDER BY for a RESTORE)

RMAN> recover database;

Starting recover at 04-JUL-15
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archived log for thread 1 with sequence 628 is already on disk as file /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_628_bsgrjztp_.arc
archived log for thread 1 with sequence 629 is already on disk as file /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_629_bsgrk0tw_.arc
archived log for thread 1 with sequence 630 is already on disk as file /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_630_bsgrk48j_.arc
archived log for thread 1 with sequence 631 is already on disk as file /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_631_bsgrk49w_.arc
archived log for thread 1 with sequence 632 is already on disk as file /NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_632_bsgrk8od_.arc
archived log for thread 1 with sequence 633 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo03.log
archived log file name=/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_628_bsgrjztp_.arc thread=1 sequence=628
archived log file name=/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_629_bsgrk0tw_.arc thread=1 sequence=629
archived log file name=/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_630_bsgrk48j_.arc thread=1 sequence=630
archived log file name=/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_631_bsgrk49w_.arc thread=1 sequence=631
archived log file name=/NEW_FS/oracle/FRA/HEMANTDB/archivelog/2015_07_04/o1_mf_1_632_bsgrk8od_.arc thread=1 sequence=632
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo03.log thread=1 sequence=633
media recovery complete, elapsed time: 00:00:02
Finished recover at 04-JUL-15

RMAN>
RMAN> alter database open resetlogs;

database opened

RMAN>


UPDATE 07-Jul-15 :  Also see my earlier (year 2012) post "Datafiles not Restored -- using V$DATAFILE and V$DATAFILE_HEADER"  which also shows retrying a RESTORE DATABASE after a failure of restoring a datafile.  There, a single file in a BackupSet failed to restore.  Oracle didn't continue and try the other datafiles in that BackupSet.  I could either fix the error and retry the entire BackupSet (RESTORE DATABASE would have identified the right BackupSet containing those files) OR I could, as I did in that scenario, individually restore DataFiles from the BackupSet.

It can be a good idea to have your database backup consist of multiple BackupSets, using either multiple CHANNELs or FILESPERSET during the BACKUP.


You could also note, as an aside, that Log Sequence 633 was an online redo log file. RMAN automatically verifies that the online redo log files designated by the controlfile are present and uses them.

.
.
.

Categories: DBA Blogs

RMAN - 3 : The DB_UNIQUE_NAME in Backups to the FRA

Tue, 2015-06-23 03:14
When you run RMAN Backups to the FRA without using the FORMAT clause, Oracle automatically generates filenames for the BackupPieces.  The folder name is derived from the system date.  But what is the parent folder for backups ?  Is it simply the DB_RECOVERY_FILE_DEST ?  Actuallly, the DB_UNIQUE_NAME comes into play as well.

For example :

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 23 16:57:19 2015

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

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 831.23M DISK 00:03:32 07-JUN-15
BP Key: 1 Status: AVAILABLE Compressed: YES Tag: TAG20150607T165914
Piece Name: /NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_nnndf_TAG20150607T165914_bq81z2y6_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14068320 07-JUN-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 366.89M DISK 00:01:56 07-JUN-15
BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20150607T170754
Piece Name: /NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_nnndf_TAG20150607T170754_bq82hc5f_.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 14068721 07-JUN-15 /home/oracle/app/oracle/oradata/orcl/system01.dbf

RMAN>

[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 23 16:58:34 2015

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


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

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /NEW_FS/oracle/FRA
db_recovery_file_dest_size big integer 8G
SQL>

We can see that the DB_RECOVERY_FILE_DEST is defined as "/NEW_FS/oracle/FRA". However, the backups go into a "backupset" folder under "/NEW_FS/oracle/FRA/ORCL/". The "ORCL" is part of the path to the folder holding the backups. How is this "ORCL" derived ?

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl
SQL>

By default, the DB_UNIQUE_NAME is the same as DB_NAME. Let's see what happens after I change the DB_UNIQUE_NAME.

SQL> 
SQL> !ls -l /NEW_FS/oracle/FRA/
total 4
drwxrwx--- 5 oracle oracle 4096 Jun 7 17:10 ORCL

SQL>
SQL> alter system set db_unique_name='HEMANTDB' scope=SPFILE;

System altered.

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

Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 385878712 bytes
Database Buffers 62914560 bytes
Redo Buffers 6008832 bytes
Database mounted.
Database opened.
SQL> !ls -l /NEW_FS/oracle/FRA/
total 4
drwxrwx--- 5 oracle oracle 4096 Jun 7 17:10 ORCL

SQL>

After resetting the DB_UNIQUE_NAME, Oracle doesn't immediately create the folder for the new DB_UNIQUE_NAME until and unless I run an RMAN Backup.

RMAN> exit

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-03135: connection lost contact
Process ID: 3344
Session ID: 67 Serial number: 13


Recovery Manager complete.
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 23 17:07:14 2015

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

connected to target database: ORCL (DBID=1229390655)

RMAN> backup datafile 1;

Starting backup at 23-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=38 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 23-JUN-15
channel ORA_DISK_1: finished piece 1 at 23-JUN-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/backupset/2015_06_23/o1_mf_nnndf_TAG20150623T170721_brl8g9od_.bkp tag=TAG20150623T170721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 23-JUN-15

Starting Control File and SPFILE Autobackup at 23-JUN-15
piece handle=/NEW_FS/oracle/FRA/HEMANTDB/autobackup/2015_06_23/o1_mf_s_883156126_brl8k0w4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 23-JUN-15

RMAN>

SQL> !ls -l /NEW_FS/oracle/FRA/
total 8
drwxrwx--- 3 oracle oracle 4096 Jun 23 17:07 HEMANTDB
drwxrwx--- 5 oracle oracle 4096 Jun 7 17:10 ORCL

SQL>
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string orcl
SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string HEMANTDB
SQL>

Notice how Oracle created the "HEMANTDB" folder under the designated DB_RECOVERY_FILE_DEST. It then created the "backupset" and "autobackup" folders also as subfolders under this.  BackupSet BackupPieces and Controlfile Autobackups are now going to the new path.  The backups are go to folders under {DB_RECOVERY_FILE_DEST}/{DB_UNIQUE_NAME}
.
.
.



Categories: DBA Blogs

RMAN -- 2 : ArchiveLog Deletion Policy

Sat, 2015-06-13 08:54
Most Internet references about defining the ArchiveLog Deletion Policy relate to the necessity to preserve ArchiveLogs for Standby databases.

For example, the configuration here prevents deletion unless an ArchiveLog has been applied on a Standby :

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/snapcf_orcl.f'; # default

RMAN>

But it is possible to also configure it differently. For example, thus for a database without a Standby, I can configure it to prevent deletion unless a Backup of the ArchiveLog has been made (to disk in this case)  :

RMAN> configure archivelog deletion policy to backed up 1 times to device type disk;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/snapcf_orcl.f'; # default

RMAN>

Let's see how this plays.
RMAN> sql 'alter system archive log current ';

sql statement: alter system archive log current

RMAN> delete archivelog all;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=52 device type=DISK
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_623_bqrjp5gx_.arc thread=1 sequence=623
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_624_bqrjpsb3_.arc thread=1 sequence=624
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_625_bqrjq8kj_.arc thread=1 sequence=625
RMAN-08138: WARNING: archived log not deleted - must create more backups
archived log file name=/NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_626_bqrjqfdq_.arc thread=1 sequence=626

RMAN>

RMAN raised a WARNING that indicates that deletion of the ArchiveLog is not permitted until a Backup has been taken.  Thus, you can protect your ArchiveLogs from deletion by RMAN commands if they have not been backed up.
NOTE : This does NOT prevent non-RMAN commands (e.g. cron jobs with shell scripts) from deleting ArchiveLogs !

Let me backup and then delete the ArchiveLogs.

RMAN> backup as compressed backupset archivelog all;

Starting backup at 13-JUN-15
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=623 RECID=9 STAMP=882312517
channel ORA_DISK_1: starting piece 1 at 13-JUN-15
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=624 RECID=10 STAMP=882312537
input archived log thread=1 sequence=625 RECID=11 STAMP=882312552
input archived log thread=1 sequence=626 RECID=12 STAMP=882312557
channel ORA_DISK_2: starting piece 1 at 13-JUN-15
channel ORA_DISK_1: finished piece 1 at 13-JUN-15
piece handle=/NEW_FS/oracle/FRA/ORCL/backupset/2015_06_13/o1_mf_annnn_TAG20150613T225210_bqrjwtfd_.bkp tag=TAG20150613T225210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=627 RECID=13 STAMP=882312730
channel ORA_DISK_1: starting piece 1 at 13-JUN-15
channel ORA_DISK_2: finished piece 1 at 13-JUN-15
piece handle=/NEW_FS/oracle/FRA/ORCL/backupset/2015_06_13/o1_mf_annnn_TAG20150613T225210_bqrjwtg3_.bkp tag=TAG20150613T225210 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: finished piece 1 at 13-JUN-15
piece handle=/NEW_FS/oracle/FRA/ORCL/backupset/2015_06_13/o1_mf_annnn_TAG20150613T225210_bqrjwvp1_.bkp tag=TAG20150613T225210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JUN-15

Starting Control File and SPFILE Autobackup at 13-JUN-15
piece handle=/NEW_FS/oracle/FRA/ORCL/autobackup/2015_06_13/o1_mf_s_882312732_bqrjwwsc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 13-JUN-15

RMAN> delete archivelog all;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=52 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
9 1 623 A 07-JUN-15
Name: /NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_623_bqrjp5gx_.arc

10 1 624 A 13-JUN-15
Name: /NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_624_bqrjpsb3_.arc

11 1 625 A 13-JUN-15
Name: /NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_625_bqrjq8kj_.arc

12 1 626 A 13-JUN-15
Name: /NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_626_bqrjqfdq_.arc

13 1 627 A 13-JUN-15
Name: /NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_627_bqrjwt3k_.arc


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_623_bqrjp5gx_.arc RECID=9 STAMP=882312517
deleted archived log
archived log file name=/NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_624_bqrjpsb3_.arc RECID=10 STAMP=882312537
deleted archived log
archived log file name=/NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_625_bqrjq8kj_.arc RECID=11 STAMP=882312552
deleted archived log
archived log file name=/NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_626_bqrjqfdq_.arc RECID=12 STAMP=882312557
deleted archived log
archived log file name=/NEW_FS/oracle/FRA/ORCL/archivelog/2015_06_13/o1_mf_1_627_bqrjwt3k_.arc RECID=13 STAMP=882312730
Deleted 5 objects


RMAN>

Now, I am able to delete the ArchiveLogs as I have at least 1 backup (on disk) of each.

.
.
.

Categories: DBA Blogs

RMAN -- 1 : Backup Job Details

Sun, 2015-06-07 03:57
Here's a post on how you could be misled by a simple report on the V$RMAN_BACKUP_JOB_DETAILS view.

Suppose I run RMAN Backups through a shell script.  Like this :

[oracle@localhost Hemant]$ ls -l *sh
-rwxrw-r-- 1 oracle oracle 336 Jun 7 17:30 Backup_DB_Plus_ArchLogs.sh
[oracle@localhost Hemant]$ cat Backup_DB_Plus_ArchLogs.sh
ORACLE_SID=orcl;export ORACLE_SID

rman << EOF
connect target /

spool log to Backup_DB_plus_ArchLogs.LOG

backup as compressed backupset database ;

sql 'alter system switch logfile';
sql 'alter system archive log current' ;

backup as compressed backupset archivelog all;

backup as compressed backupset current controlfile ;

EOF

[oracle@localhost Hemant]$
[oracle@localhost Hemant]$
[oracle@localhost Hemant]$
[oracle@localhost Hemant]$ ./Backup_DB_Plus_ArchLogs.sh

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jun 7 17:31:06 2015

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

RMAN>
connected to target database: ORCL (DBID=1229390655)

RMAN>
RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> RMAN> [oracle@localhost Hemant]$
[oracle@localhost Hemant]$

I then proceed to check the results of the run in V$RMAN_BACKUP_JOB_DETAILS.

SQL> l
1 select to_char(start_time,'DD-MON HH24:MI') StartTime, to_char(end_time,'DD-MON HH24:MI') EndTime,
2 input_type, status
3 from v$rman_backup_job_details
4* where start_time > trunc(sysdate)+17.5/24
SQL> /

STARTTIME ENDTIME INPUT_TYPE STATUS
--------------------- --------------------- ------------- -----------------------
07-JUN 17:31 07-JUN 17:31 DB FULL FAILED

SQL>

It says that I ran one FULL DATABASE Backup that failed. Is that really true ?  Let me check the RMAN spooled log.

[oracle@localhost Hemant]$ cat Backup_DB_plus_ArchLogs.LOG

Spooling started in log file: Backup_DB_plus_ArchLogs.LOG

Recovery Manager11.2.0.2.0

RMAN>
RMAN>
Starting backup at 07-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=60 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=59 device type=DISK
RMAN-06169: could not read file header for datafile 6 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/07/2015 17:31:08
RMAN-06056: could not access datafile 6

RMAN>
RMAN>
sql statement: alter system switch logfile

RMAN>
sql statement: alter system archive log current

RMAN>
RMAN>
Starting backup at 07-JUN-15
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=615 RECID=1 STAMP=881773851
channel ORA_DISK_1: starting piece 1 at 07-JUN-15
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=616 RECID=2 STAMP=881773851
input archived log thread=1 sequence=617 RECID=3 STAMP=881773853
input archived log thread=1 sequence=618 RECID=4 STAMP=881774357
input archived log thread=1 sequence=619 RECID=5 STAMP=881774357
channel ORA_DISK_2: starting piece 1 at 07-JUN-15
channel ORA_DISK_2: finished piece 1 at 07-JUN-15
piece handle=/NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_annnn_TAG20150607T173112_bq83v12b_.bkp tag=TAG20150607T173112 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=620 RECID=6 STAMP=881775068
input archived log thread=1 sequence=621 RECID=7 STAMP=881775068
input archived log thread=1 sequence=622 RECID=8 STAMP=881775071
channel ORA_DISK_2: starting piece 1 at 07-JUN-15
channel ORA_DISK_1: finished piece 1 at 07-JUN-15
piece handle=/NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_annnn_TAG20150607T173112_bq83v10y_.bkp tag=TAG20150607T173112 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_2: finished piece 1 at 07-JUN-15
piece handle=/NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_annnn_TAG20150607T173112_bq83v292_.bkp tag=TAG20150607T173112 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUN-15

Starting Control File and SPFILE Autobackup at 07-JUN-15
piece handle=/NEW_FS/oracle/FRA/ORCL/autobackup/2015_06_07/o1_mf_s_881775075_bq83v3nr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-JUN-15

RMAN>
RMAN>
Starting backup at 07-JUN-15
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 07-JUN-15
channel ORA_DISK_1: finished piece 1 at 07-JUN-15
piece handle=/NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_ncnnf_TAG20150607T173117_bq83v6vg_.bkp tag=TAG20150607T173117 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-JUN-15

Starting Control File and SPFILE Autobackup at 07-JUN-15
piece handle=/NEW_FS/oracle/FRA/ORCL/autobackup/2015_06_07/o1_mf_s_881775080_bq83v88z_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-JUN-15

RMAN>
RMAN>

Recovery Manager complete.
[oracle@localhost Hemant]$

Hmm. There were *three* distinct BACKUP commands in the script file.  The first was BACKUP ... DATABASE ..., the second was BACKUP ... ARCHIVELOG ... and the third was BACKUP ... CURRENT CONTROLFILE.  All three were executed.
Only the first BACKUP execution failed.  The subsequent  two BACKUP commands succeeded.  They were for ArchiveLogs and the Controlfile.
And *yet* the view V$RMAN_BACKUP_JOB_DETAILS shows that I ran  a FULL DATABASE BACKUP that failed.  It tells me nothing about the ArchiveLogs and the ControlFile backups that did succeed !


What if I switch my strategy from using a shell script to an rman script ?

[oracle@localhost Hemant]$ ls -ltr *rmn
-rw-rw-r-- 1 oracle oracle 287 Jun 7 17:41 Backup_DB_plus_ArchLogs.rmn
[oracle@localhost Hemant]$ cat Backup_DB_plus_ArchLogs.rmn
connect target /

spool log to Backup_DB_plus_ArchLogs.TXT

backup as compressed backupset database ;

sql 'alter system switch logfile';
sql 'alter system archive log current' ;

backup as compressed backupset archivelog all;

backup as compressed backupset current controlfile;

exit

[oracle@localhost Hemant]$
[oracle@localhost Hemant]$
[oracle@localhost Hemant]$
[oracle@localhost Hemant]$ rman @Backup_DB_plus_ArchLogs.rmn

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jun 7 17:42:17 2015

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

RMAN> connect target *
2>
3> spool log to Backup_DB_plus_ArchLogs.TXT
4>
5> backup as compressed backupset database ;
6>
7> sql 'alter system switch logfile';
8> sql 'alter system archive log current' ;
9>
10> backup as compressed backupset archivelog all;
11>
12> backup as compressed backupset current controlfile;
13>
14> exit[oracle@localhost Hemant]$




SQL> l
1 select to_char(start_time,'DD-MON HH24:MI') StartTime, to_char(end_time,'DD-MON HH24:MI') EndTime,
2 input_type, status
3 from v$rman_backup_job_details
4 where start_time > trunc(sysdate)+17.5/24
5* order by start_time
SQL> /

STARTTIME ENDTIME INPUT_TYPE STATUS
--------------------- --------------------- ------------- -----------------------
07-JUN 17:31 07-JUN 17:31 DB FULL FAILED
07-JUN 17:42 07-JUN 17:42 DB FULL FAILED

SQL>

[oracle@localhost Hemant]$
[oracle@localhost Hemant]$ cat Backup_DB_plus_ArchLogs.TXT

connected to target database: ORCL (DBID=1229390655)

Spooling started in log file: Backup_DB_plus_ArchLogs.TXT

Recovery Manager11.2.0.2.0

Starting backup at 07-JUN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=50 device type=DISK
RMAN-06169: could not read file header for datafile 6 error reason 4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/07/2015 17:42:19
RMAN-06056: could not access datafile 6

Recovery Manager complete.
[oracle@localhost Hemant]$

Now, this time, once the first BACKUP command failed, RMAN seems to have bailed out. It didn't even try executing the subsequent BACKUP commands !

How can V$RMAN_BACKUP_JOB_DETAILS differentiate from the two failed backups ?

SQL> l
1 select to_char(start_time,'DD-MON HH24:MI') StartTime, to_char(end_time,'DD-MON HH24:MI') EndTime,
2 input_bytes/1048576 Input_MB, output_bytes/1048576 Output_MB,
3 input_type, status
4 from v$rman_backup_job_details
5 where start_time > trunc(sysdate)+17.5/24
6* order by start_time
SQL> /

STARTTIME ENDTIME INPUT_MB OUTPUT_MB INPUT_TYPE STATUS
--------------------- --------------------- ---------- ---------- ------------- -----------------------
07-JUN 17:31 07-JUN 17:31 71.5219727 34.878418 DB FULL FAILED
07-JUN 17:42 07-JUN 17:42 0 0 DB FULL FAILED

SQL>

The Input Bytes does indicate that some files were backed up in the first run. Yet, it doesn't tell us how much of those were ArchiveLogs and how much were the ControlFile.


Question 1 : How would you script your backups ?  (Hint : Differentiate between the BACKUP DATABASE and the BACKUP ARCHIVELOG runs).

Question 2 : Can you improve your Backup Reports ?

Yes, the RMAN LIST BACKUP command is useful.  But you can't select the columns, format the output or add text  as you would with a query on V$ views.

[oracle@localhost oracle]$ NLS_DATE_FORMAT=DD_MON_HH24_MI_SS;export NLS_DATE_FORMAT
[oracle@localhost oracle]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jun 7 17:51:41 2015

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

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup completed after "trunc(sysdate)+17.5/24";

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
17 375.50K DISK 00:00:01 07_JUN_17_31_13
BP Key: 17 Status: AVAILABLE Compressed: YES Tag: TAG20150607T173112
Piece Name: /NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_annnn_TAG20150607T173112_bq83v12b_.bkp

List of Archived Logs in backup set 17
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------------- ---------- ---------
1 616 14068910 07_JUN_17_10_49 14068920 07_JUN_17_10_51
1 617 14068920 07_JUN_17_10_51 14068931 07_JUN_17_10_53
1 618 14068931 07_JUN_17_10_53 14069550 07_JUN_17_19_17
1 619 14069550 07_JUN_17_19_17 14069564 07_JUN_17_19_17

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
18 1.03M DISK 00:00:00 07_JUN_17_31_14
BP Key: 18 Status: AVAILABLE Compressed: YES Tag: TAG20150607T173112
Piece Name: /NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_annnn_TAG20150607T173112_bq83v292_.bkp

List of Archived Logs in backup set 18
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------------- ---------- ---------
1 620 14069564 07_JUN_17_19_17 14070254 07_JUN_17_31_08
1 621 14070254 07_JUN_17_31_08 14070265 07_JUN_17_31_08
1 622 14070265 07_JUN_17_31_08 14070276 07_JUN_17_31_11

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
19 13.72M DISK 00:00:02 07_JUN_17_31_14
BP Key: 19 Status: AVAILABLE Compressed: YES Tag: TAG20150607T173112
Piece Name: /NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_annnn_TAG20150607T173112_bq83v10y_.bkp

List of Archived Logs in backup set 19
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------------- ---------- ---------
1 615 14043833 12_JUN_23_28_21 14068910 07_JUN_17_10_49

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Full 9.36M DISK 00:00:00 07_JUN_17_31_15
BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20150607T173115
Piece Name: /NEW_FS/oracle/FRA/ORCL/autobackup/2015_06_07/o1_mf_s_881775075_bq83v3nr_.bkp
SPFILE Included: Modification time: 07_JUN_17_28_15
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 14070285 Ckp time: 07_JUN_17_31_15

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 1.05M DISK 00:00:02 07_JUN_17_31_19
BP Key: 21 Status: AVAILABLE Compressed: YES Tag: TAG20150607T173117
Piece Name: /NEW_FS/oracle/FRA/ORCL/backupset/2015_06_07/o1_mf_ncnnf_TAG20150607T173117_bq83v6vg_.bkp
Control File Included: Ckp SCN: 14070306 Ckp time: 07_JUN_17_31_17

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22 Full 9.36M DISK 00:00:00 07_JUN_17_31_20
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20150607T173120
Piece Name: /NEW_FS/oracle/FRA/ORCL/autobackup/2015_06_07/o1_mf_s_881775080_bq83v88z_.bkp
SPFILE Included: Modification time: 07_JUN_17_31_18
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 14070312 Ckp time: 07_JUN_17_31_20

RMAN>

So, the RMAN LIST BACKUP can provide details that V$RMAN_BACKUP_JOB_DETAILS cannot provide. Yet, it doesn't tell us that a Backup failed.
.
.
.

Categories: DBA Blogs

Parallel Execution -- 6 Parallel DML Restrictions

Sun, 2015-05-24 09:18
Here's link to the 11.2 documentation on Restrictions on Parallel DML

So, some of the implications are :

1.  You cannot do Parallel DML if a Trigger is present on the target table

2.  Certain integrity constraints disable Parallel DML.

3,  Parallel DML cannot be part of a Distributed Transaction.

Oracle silently converts the Parallel DML to a Serial DML without raising a warning / error.

.
.
.

Categories: DBA Blogs

Parallel Execution -- 5b Parallel INSERT Execution Plan

Sat, 2015-05-16 09:19
As noted in my previous post, Oracle does NOT enable Parallel DML by default.  You need to explicitly enable it with ALTER SESSION ENABLE PARALLEL DML.

Can you use the Execution Plan of an INSERT statement to identify if the INSERT was executed in Parallel ?

Here's a brief demonstration :

SQL*Plus: Release 11.2.0.2.0 Production on Sat May 16 22:27:49 2015

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


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

HEMANT>truncate table another_large_table;

Table truncated.

HEMANT>select degree from user_tables where table_name = 'ANOTHER_LARGE_TABLE';

DEGREE
----------------------------------------
1

HEMANT>alter table another_large_table parallel 4;

Table altered.

HEMANT>insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE
2 select /*+ PARALLEL */ * from large_table;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuuygy5k8nfrh, child number 0
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuuygy5k8nfrh, child number 1
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 16


27 rows selected.

HEMANT>
HEMANT>select count(*) from another_large_table;

COUNT(*)
----------
4802944

HEMANT>

Notice the "LOAD TABLE CONVENTIONAL" ?  Parallel Execution Servers were used for querying the source table LARGE_TABLE but the actual INSERT was executed as a non-parallel INSERT.  Another piece of evidence that the INSERT was not executed using PX Servers is that I was able to query the table without an ORA-12838 error.

Let me try again with the correct ALTER SESSION command.

HEMANT>truncate table another_large_table;

Table truncated.

HEMANT>alter session enable parallel dml;

Session altered.

HEMANT>insert /*+ PARALLEL */ into another_large_table tgt
2 select /*+ PARALLEL */ * from large_table src;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9scm06z0m9vz6, child number 0
-------------------------------------
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL
*/ * from large_table src

Plan hash value: 474933689

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: 9scm06z0m9vz6, child number: 1 cannot be found


HEMANT>select count(*) from another_large_table;
select count(*) from another_large_table
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


HEMANT>

Here, the ORA-12838 is evidence that the INSERT was Parallel. But that evidence is only visible from the same session. What if we had to use another session to check the Execution ? We'd then use the DBMS_XPLAN.DISPLAY_CURSOR method.  Here we notice the PX COORDINATOR at ID=1 appearing *above* the LOAD AS SELECT.  There is no LOAD TABLE CONVENTIONAL step.  These are evidence that the LOAD was executed by PX Servers.
Note : Ignore the "P->S" in ID=2.



Question : Why does the first (non-parallel insert) statement have 2 child cursors.  And the 2nd child cursor actually does indicate an Auto DoP.  I have evidence that child cursor 0 is executed and child cursor 1, although parsed, shows as 0 executions in V$SQL :

SYS>select sql_id, child_number, executions, sql_text
2 from v$sql
3 where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
4 order by 1,2;

SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6 0 1
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh 0 1
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh 1 0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYS>
SYS>l
1 select sql_id, child_number, executions, px_servers_executions, sql_text
2 from v$sql
3 where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
4* order by 1,2
SYS>/

SQL_ID CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6 0 1 0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh 0 1 0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh 1 0 4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

Interesting, isn't it ? This time, the PX_SERVERS_EXECUTIONS doesn't seem to be correct.  So, either EXECUTIONS is correct or PX_SERVERS_EXECUTIONS is correct.  These need to be re-verified in 11.2.0.4.

UPDATE 18-May-15 :  In 11.2.0.4,  I see 1 child cursor (not 2) with the correct EXECUTIONS and PX_SERVERS_EXECUTIONS being reported.  I will need to rerun my tests in the same 11.2.0.2 environment.


Re-testing in the same 11.2.0.2 environment :
First, the insert that does NOT get executed as a Parallel INSERT :

[oracle@localhost ~]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.2.0 Production on Sun May 24 22:58:28 2015

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


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

HEMANT>truncate table ANOTHER_LARGE_TABLE;

Table truncated.

HEMANT>select degree from user_tables where table_name = 'ANOTHER_LARGE_TABLE';

DEGREE
----------------------------------------
4

HEMANT>insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE
2 select /*+ PARALLEL */ * from large_table;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuuygy5k8nfrh, child number 0
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('fuuygy5k8nfrh',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fuuygy5k8nfrh, child number 1
-------------------------------------
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */
* from large_table

Plan hash value: 2350597358

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL | LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: Computed Degree of Parallelism is 16


27 rows selected.

HEMANT>select count(*) from another_large_table;

COUNT(*)
----------
4802944

HEMANT>
SYSTEM>select sql_id, child_number, executions, px_servers_executions, sql_text
2 from v$sql
3 where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
4 order by 1,2;

SQL_ID CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
fuuygy5k8nfrh 0 1 0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh 1 0 4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>

Next, the actual Parallel Insert.

HEMANT>commit;

Commit complete.

HEMANT>alter session enable parallel dml;

Session altered.

HEMANT>insert /*+ PARALLEL */ into another_large_table tgt
2 select /*+ PARALLEL */ * from large_table src;

4802944 rows created.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9scm06z0m9vz6, child number 0
-------------------------------------
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL
*/ * from large_table src

Plan hash value: 474933689

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1314 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWC | |
|* 5 | TABLE ACCESS FULL| LARGE_TABLE | 4802K| 448M| 1314 (1)| 00:00:16 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing


27 rows selected.

HEMANT>select * from table(dbms_xplan.display_cursor('9scm06z0m9vz6',1));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: 9scm06z0m9vz6, child number: 1 cannot be found


HEMANT>select count(*) from another_large_table;
select count(*) from another_large_table
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


HEMANT>
SYSTEM>l
1 select sql_id, child_number, executions, px_servers_executions, sql_text
2 from v$sql
3 where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
4* order by 1,2
SYSTEM>
SYSTEM>/

SQL_ID CHILD_NUMBER EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ------------ ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6 0 1 0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh 0 1 0
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table

fuuygy5k8nfrh 1 0 4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>
SYSTEM>l
1 select sql_id, executions, px_servers_executions, sql_text
2 from v$sqlstats
3 where sql_id in ('fuuygy5k8nfrh','9scm06z0m9vz6')
4* order by 1,2
SYSTEM>/

SQL_ID EXECUTIONS PX_SERVERS_EXECUTIONS
------------- ---------- ---------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------
9scm06z0m9vz6 1 0
insert /*+ PARALLEL */ into another_large_table tgt select /*+ PARALLEL */ * from large_table src

fuuygy5k8nfrh 1 4
insert /*+ PARALLEL */ into ANOTHER_LARGE_TABLE select /*+ PARALLEL */ * from large_table


SYSTEM>

The behaviour of the V$ views in the 11.2.0.2 environment doesn't seem correct. (Note : V$SQLSTATS doesn't differentiate by CHILD_NUMBER as does V$SQL).
.
.



Categories: DBA Blogs