Skip navigation.

DBA Blogs

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

Hemant K Chitale - 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

Check out 6.00.1x computer science class on edX!

Bobby Durrett's DBA Blog - Sat, 2015-07-25 11:15

I just finished the last program for a computer science class on edX and I urge you to try it.

I took this class:

MITx: 6.00.1x Introduction to Computer Science and Programming Using Python

I was more interested in how MIT taught the class than in the material itself because I already know the subjects covered.

The class taught the basics of programming – expressions, variables, loops, if statements, and functions.

It also had a large focus on bisection or binary search and the performance benefits of this type of search over sequentially reading through a list.

It also covered lists, hash tables, trees, stacks, and queues.

It discussed object-oriented programming.

The class concluded with the professor stating that the programming and computer science skills taught in this class are key to advancing your career, even if you do not work in a computer related job.

I interacted with a number of students in the class and found some that were in other fields and were having success taking the class.  Others were in business computing or IT and yet did not have a computer science background so they were good programmers but learning new concepts.  Many struggled with the class but, it is free, and is given often. The class starts up again August 26th.  Nothing stops you from taking it multiple times.

I tried to think about whether I should recommend this class to the people I work with as a method of helping develop my coworkers that do not have experience in these areas.  At first I thought that the subject is too academic and has no connection to their jobs. But, after thinking about it for a while, I now believe that just the opposite is true.

Searching for practical applications of the class, I first remembered the programs that we wrote that compared searching sequentially through a list to using binary search.  In one test case the sequential method took 15 seconds but the binary search took less than one second.  This reminded me so much of tuning Oracle SQL queries.  The sequential scan of the list was like a full table scan in Oracle.  The binary search was like looking up a single row using an index scan.  As I tune Oracle queries my computer science knowledge of binary search and binary trees makes it easy to understand index and full table scans.

In another example, we recently had slowness on a Weblogic portal server.  CPU was getting maxed out and the CPU spent most of its time in a Java ConcurrentHashMap object.  I don’t know the internals of Weblogic and I have never used a ConcurrentHashMap but I know how hashing works.  I know that hashing is very fast until your hash table fills up or if the hash function distributes the items in an unequal way. My knowledge of hashing helped me grasp why our portal server was using a lot of CPU despite my lack of access to its internals.

So, contrary to my original fear that the edX class was too academic and not practical I believe that the concepts covered are very practical.  If you do not know how binary search works or what a binary tree is you will benefit from 6.00.1x on edX.  If you can not explain how a hash table works and what causes hashing to slow down you can learn from 6.00.1x. And, if you have never written a computer program, although you may find the class difficult and have to take it more than once, you will benefit from 6.00.1x on edX.

– Bobby

 

Categories: DBA Blogs

Log Buffer #433: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-07-24 10:05

This Log Buffer Edition covers Oracle, SQL Server and MySQL blogs of the running week.

Oracle:

  • While checking the sources of the Cassandra/NetBeans integration into GitHub yesterday, something went very badly wrong and ALL the source files in my Maven project that disappeared!
  • AWR Reports, Performance Hub, historisches SQL Monitoring in 12c
  • Oracle Database Mobile Server 12c: Advanced data synchronization engine
  • ORA-39001, ORA-39000 and ORA-39142
  • ORA-15410: Disks in disk group do not have equal size

SQL Server:

  • SAN and NAS protocols and how they impact SQL Server
  • SQL Style Habits: Attack of the Skeuomorphs
  • Is It Worth Writing Unit Tests?
  • Large SQL Server Database Backup on an Azure VM and Archiving
  • Reporting Services: Drawing a Buffer on a Map

MySQL:

  • MySQL Tcpdump system : use percona-toolkit to analyze network packages
  • Replication in real-time from Oracle and MySQL into data warehouses and analytics
  • Altering tablespace of table – new in MySQL 5.7
  • MySQL QA Episode 8: Reducing Testcases for Engineers: tuning reducer.sh
  • MySQL upgrade 5.6 with innodb_fast_checksum=1

 

Learn more about Pythian’s expertise in Oracle , SQL ServerMySQL, as well as the author Fahd Mirza.

 

The post Log Buffer #433: A Carnival of the Vanities for DBAs appeared first on Pythian - Data Experts Blog.

Categories: DBA Blogs

AUDIT_SYS_OPERATIONS defaults to TRUE in #Oracle 12c

The Oracle Instructor - Fri, 2015-07-24 08:09

A small but remarkable change in Oracle Database 12c is the default value of AUDIT_SYS_OPERATIONS has changed to TRUE now. In other words, all actions done by the superuser sys are being audited now by default!

[oracle@uhesse ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 24 15:23:10 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,value from v$spparameter where isspecified='TRUE';

NAME                                     VALUE
---------------------------------------- --------------------------------------------------
memory_target                            1073741824
control_files                            /u01/app/oracle/oradata/prima/control01.ctl
db_block_size                            8192
compatible                               12.1.0.2
db_recovery_file_dest                    /u02/fra
db_recovery_file_dest_size               2147483648
undo_management                          auto
undo_tablespace                          undotbs1
remote_login_passwordfile                exclusive
db_name                                  prima
diagnostic_dest                          /u01/app/oracle

11 rows selected.


SQL> show parameter sys_oper

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     TRUE
SQL> select count(*) from scott.dept;

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

SQL> show parameter audit_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/rdbms/audit
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@uhesse ~]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit

[oracle@uhesse audit]$ cat prima_ora_6204_20150724152310753136143795.aud
Audit file /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/audit/prima_ora_6204_20150724152310753136143795.aud
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      uhesse
Release:        3.8.13-68.2.2.el7uek.x86_64
Version:        #2 SMP Tue May 12 14:38:58 PDT 2015
Machine:        x86_64
Instance name: prima
Redo thread mounted by this instance: 1
Oracle process number: 41
Unix process pid: 6204, image: oracle@uhesse (TNS V1-V3)

Fri Jul 24 15:23:10 2015 +02:00
LENGTH : '160'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '2113606181'
[Output shortened...]
Fri Jul 24 15:23:56 2015 +02:00
LENGTH : '185'
ACTION :[31] 'select count(*) from scott.dept'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[10] '2113606181'

Something you might need to know as a DBA, don’t you think? :-)


Tagged: 12c New Features, security
Categories: DBA Blogs

Lessons Learned with Kubernetes

Pythian Group - Tue, 2015-07-21 13:00
Kubernetes Logo Trend Towards Kubernetes

Three trends in computing have come together to make container orchestration the next obvious evolution of internet service delivery.  The first is the trend to pack an increasing number of segregated services into larger and larger servers for efficiency gains.  The second trend is the rapid build->test->release cycle of modern microservices that can see hundreds or thousands of updates each day.  And, the third trend is infrastructure-as-code which abstracts the actual hardware of servers and networking equipment away into text files that describe the desired infrastructure.  These files can be tested and version controlled in exactly the same way as code, and deployed just as quickly.  At the convergence point sits Kubernetes from Google which uses flat files to describe the infrastructure and containers needed to deliver a service, which can be built, tested, and deployed incredibly quickly.

Pythian has been working with container orchestration using Kubernetes since it was announced to the public in June of 2014.  We have used it to deploy microservices faster while also speeding up the development cycle.  With the advent of V1.0, we decided to revisit some of what we learned implementing internally and with clients on Kubernetes.

Develop Locally

Google, and others provide hosted Kubernetes solutions that are fast and easy to use.  In fact, you can use them for your whole build->test->deploy workflow.  Keep in mind, that with hosted Kubernetes, the containers are exposed to the internet from very early in your development cycle.  If that’s not desirable, or if local development is important, go faster with a local cluster.  Kubernetes can run on as few as three VMs and the vagrant install is well supported.  Our workflow involves sharing the yaml files among the team and developing everything locally before pushing blessed containers for deployment on a production cluster.

Pay Attention to API Versions in Examples

Since the kubernetes team has been developing their api in public for the last year, there have been a number of fairly large breaking changes to the API.  Now that v1 of the API is stable, we can depend on it. However, many of the tutorials and examples online use earlier versions.  Be sure to check which version the example uses before trying to experiment with it.

Get to know Volumes at Cluster Scale

In Kubernetes, volumes are an outgrowth of the Docker concept of a volume, or a filesystem that can be mounted and isn’t tied to the lifecycle of specific container.  Kubernetes re-imagines them at cluster scale and through plugins, allows containers to mount all kinds of things as file systems.  One plugin adds a git repository as a mountable filesystem, which opens the door to some particularly interesting use cases.

Leverage Etcd

At the heart of the Kubernetes cluster is a distributed, shared-state system called etcd.  Built on the RAFT protocol, it stores key->value pairs in a tiered structure and supports an easy REST api.  Etcd also provides a level of access control sufficient to securely store shared secrets for use throughout the cluster, but not available to all etcd consumers.  This feature underpins the concept of a Secret in Kubernetes.  But, your application can also talk directly to the etcd cluster in Kubernetes.  Using confd, your application can use the Kubernetes etcd instance as a data storage layer.  For example, here’s a simple url shortener gist using just nginx, confd, and etcd.
Happy experimentation!

Schedule a free assessment with a Pythian Kubernetes expert.

Learn more about Pythian’s Cloud expertise.

If this sounds like the kind of thing you’d like to work on, we’re hiring too!

The post Lessons Learned with Kubernetes appeared first on Pythian - Data Experts Blog.

Categories: DBA Blogs

Reading System Logs: SQL Server – Part 2

Pythian Group - Mon, 2015-07-20 13:52

Greetings!

4355536275_430b18f9d5_nLast time I talked about reading System Logs on the SQL Server box, explaining why it is really important that DBA(s) should scan through the logs once a day on a critical production system. As I mentioned in my previous blog post , sometimes there are messages logged in as information, and at times it can be treated as an early warning before the system gets actual error messages – a sign of warning or an error. That is why it is important to read the information level messages. Let me tell you yet another case that I had where the disk sub system issue was reported as an information in system logs.

In this case, the system was suffering with the high disk I/O. The disk that we had replaced was used for writing backups. For a few days we observed that writing backups were longer than it was before.  The number of databases were the same and the size of these databases were not drastically increased, though the time it was taking to write backups had increased significantly. Looking at the system logs I noticed some messages related to the disk. Searching for those messages lead me to some links pointing toward a disk issue, link among them. After working with others in storage admin they confirmed the issue too, and now they are procuring a new disk.

So, here is what I would say. When you start your day, spare few minutes to read the system logs.  At Pythian, we have our home grown monitoring tool Avail which does this job for us reporting information, warnings and errors as a report.

Excerpts from the System Log:

Log Name:      System
Source:        Server Administrator
Date:          6/18/2015 10:55:55 PM
Event ID:      2271
Task Category: Storage Service
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      SQLServer
Description:
The Patrol Read corrected a media error.:  Physical Disk 0:0:10 Controller 0, Connector 0

photo credit: Ubicación del disco duro (antiguo) a desmontar via photopin (license)

 

Learn more about our expertise in SQL Server.

The post Reading System Logs: SQL Server – Part 2 appeared first on Pythian - Data Experts Blog.

Categories: DBA Blogs

RMAN 11g : How to Restore / Duplicate to a More Recent Patchset

Pythian Group - Mon, 2015-07-20 13:24

In an Oracle DBA’s life, you’ll be regularly asked to work on applying a new patchset on a database and then you will apply it starting from the development database to the production database and this process can be quite long depending on the organization you are working for.

In an Oracle DBA’s life, you’ll be regularly asked to refresh a development database (or any environment before the production) with the production data for development, test or whatever needs. For years now, RMAN has helped us a lot to perform this kind of task easily.

And what should happen always happens and one day you will be asked to refresh your more recent patchset DEV database (let’s say 11.2.0.4) with your PROD data (let’s say that it’s running against an 11.2.0.3 version). And let’s call a spade a spade, that could be a bit tricky — and specially if you discover that the versions are different once the RESTORE / DUPLICATE is terminated because you have launched the usual refresh scripts forgetting this little detail…

A solution could be to ask some GB to the sys admin team, copy an 11.2.0.3 ORACLE_HOME from another server, quickly clone it on the DEV server, start a RMAN DUPLICATE / RESTORE DATABASE from the 11.2.0.3 PROD to the 11.2.0.3 DEV and then upgrade it to 11.2.0.4. But this will probably be quite long and in the case that adding some GB to a server requires some procedures, validations, etc… it could take many days to refresh the DEV database which is obviously not what everybody wants. And this possibility does not exists if you face the issue after the RESTORE / DUPLICATE is finished.

Hopefully, there’s a way to achieve this goal by directly RESTORE / DUPLICATE a database to a more recent patchset (note that this method is also working for 10g databases). Let’s explore the two cases you can face doing a direct RESTORE / DUPLICATE to a more recent patchset database.

 

RESTORE / DUPLICATE DATABASE case

Whether we are restoring or duplicating the production database from a backup, here is what will happen on the DEV database:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/11/2015 22:38:59
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 17 and starting SCN of 2232530
RMAN

Here, we can’t open the database with the RESETLOGS option due to the patchset version difference. We have to use a slightly different command:

SQL> alter database open resetlogs upgrade ;
Database altered.
SQL>

Now the database is opened in upgrade mode, we can now apply the 11.2.0.4 patchset and open it.

SQL> @?/rdbms/admin/catupgrd

...

SQL> startup
ORACLE instance started.

Total System Global Area 626327552 bytes
Fixed Size 2255832 bytes
Variable Size 243270696 bytes
Database Buffers 377487360 bytes
Redo Buffers 3313664 bytes
Database mounted.
Database opened.
SQL>

This one is in fact quick and easy.

 

DUPLICATE FROM ACTIVE DATABASE case

Starting from 11g, we have the cool DUPLICATE FORM ACTIVE DATABASE feature that we can also use to perform this kind of refresh. When you perform a DUPLICATE FROM ACTIVE DATABASE operation from a 11.2.0.3 to a 11.2.0.4 version, the procedure is different from the previous one as the RESETLOGS will begin but will not be able to finish properly and you will face this error :

RMAN-08161: contents of Memory Script:
{
 Alter clone database open resetlogs;
}
RMAN-08162: executing Memory Script

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
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 03/25/2015 20:22:56
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 24341
Session ID: 1 Serial number: 9

At this stage, it’s not possible to open the database in UPGRADE mode nor RECOVER the database and not even generate a BACKUP CONTROLFILE TO TRACE.

SQL> recover database using backup controlfile until cancel ;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

SQL>

So we have to recreate the controlfile. By using these queries, we can easily create a new CREATE CONTROLFILE statement (or we could generate a BACKUP CONTROLFILE TO TRACE from the source database and then adapt it for the destination database).

SQL> select name from v$datafile order by file#;
SQL> select group#, member from v$logfile;
SQL> select name, bytes from v$tempfile order by file#;

And then recreate the controlfile:

SQL> CREATE CONTROLFILE REUSE DATABASE "TST11204" RESETLOGS ARCHIVELOG
 2 MAXLOGFILES 16
 3 MAXLOGMEMBERS 3
 4 MAXDATAFILES 100
 5 MAXINSTANCES 8
 6 MAXLOGHISTORY 292
 7 LOGFILE
 8 GROUP 1 '/u01/app/oracle/data/orcl11204/redo01.log' SIZE 50M BLOCKSIZE 512,
 9 GROUP 2 '/u01/app/oracle/data/orcl11204/redo02.log' SIZE 50M BLOCKSIZE 512,
 10 GROUP 3 '/u01/app/oracle/data/orcl11204/redo03.log' SIZE 50M BLOCKSIZE 512
 11 DATAFILE
 12 '/u01/app/oracle/data/orcl11204/system01.dbf',
 13 '/u01/app/oracle/data/orcl11204/sysaux01.dbf',
 14 '/u01/app/oracle/data/orcl11204/undotbs01.dbf',
 15 '/u01/app/oracle/data/orcl11204/users01.dbf'
CHARACTER SET AL32UTF8
 16 ;

Control file created.

SQL>

To finish the recover and open the database in UPGRADE mode, we would need to apply the current redolog (and not any archivelog — we don’t have any archivelog as the RESETLOGS didn’t happen yet).

SQL> select * from v$logfile ;

 GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ------------------------------------------------------- ---
 3 STALE ONLINE /u01/app/oracle/data/orcl11204/redo03.log NO

 2 STALE ONLINE /u01/app/oracle/data/orcl11204/redo02.log NO

 1 STALE ONLINE /u01/app/oracle/data/orcl11204/redo01.log NO

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE until cancel ;
ORA-00279: change 2059652 generated at 03/25/2015 20:22:54 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/data/TST11204/archivelog/2015_03_25/o1_mf_1_1_%u_.arc
ORA-00280: change 2059652 for thread 1 is in sequence #1

Specify log: {<ret>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/data/orcl11204/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs upgrade ;

Database altered.

SQL>

Now we can apply the 11.2.0.4 patchset:


SQL> @?/rdbms/admin/catupgrd

...

SQL>

And check that everything is good:


SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select comp_name, version, status from dba_registry ;

COMP_NAME VERSION STATUS
--------------------------------------------- ------------------------------ -----------
OWB 11.2.0.3.0 VALID
Oracle Application Express 3.2.1.00.12 VALID
Oracle Enterprise Manager 11.2.0.4.0 VALID
OLAP Catalog 11.2.0.4.0 INVALID
Spatial 11.2.0.4.0 VALID
Oracle Multimedia 11.2.0.4.0 VALID
Oracle XML Database 11.2.0.4.0 VALID
Oracle Text 11.2.0.4.0 VALID
Oracle Expression Filter 11.2.0.4.0 VALID
Oracle Rules Manager 11.2.0.4.0 VALID
Oracle Workspace Manager 11.2.0.4.0 VALID
Oracle Database Catalog Views 11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 INVALID
JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Oracle XDK 11.2.0.4.0 VALID
Oracle Database Java Packages 11.2.0.4.0 VALID
OLAP Analytic Workspace 11.2.0.4.0 INVALID
Oracle OLAP API 11.2.0.4.0 VALID

18 rows selected.

SQL>

&nbsp;

 

This saved me a lot of time, have a good day :)

 

Discover more about our expertise in Oracle.

The post RMAN 11g : How to Restore / Duplicate to a More Recent Patchset appeared first on Pythian - Data Experts Blog.

Categories: DBA Blogs

DevOps: Applied Empathy

Pythian Group - Mon, 2015-07-20 12:09

I enjoyed participating in a panel on DevOps culture by Electric Cloud last week. Our commendable hosts kept things light and productive despite the number of participants and breadth of topic.

It wouldn’t be a culture discussion if we had failed to review the motivations for that (DevOps) culture: namely the evolve-or-die progression of tech business in general and software systems of customer engagement in particular. So the logic goes, any non-trivial business is now (also) a software business – thus, being “good at software” (and rapidly deriving high quality, constantly improving, large-scale services from it) is a fundamental market success factor and must be(come) part of the corporate DNA.

I doubt the above is controversial, but the degree to which it feels true probably depends on the scale of opportunity in your sector(s) and the governing regulatory regime(s). Those factors have a big impact not only on the intensity of motivation, but the challenges and resistance to change that must be overcome in a successful program of transformation.

The discussion reminded me that empathy is important for more than just being nice. It’s also a great way to understand what motivates people and how to incorporate their success drivers into transformation efforts.

top of the world in a typical software engineering org

Consider Katniss, for example – she has to deliver to Rex (and consumers like you and me) the “and sandwich”, Velocity (new features) and Operational Excellence, or we (consumers) will find a service that does. She may prioritize Velocity at times over other initiatives, and the stress on Bill grows under this pressure. If, as agent of transformational change, you propose methods of increasing Velocity to Bill – you are likely to face rejection – Bill’s already drowning at the present pace.

If, on the other hand, one approaches Bill to explain that pervasive, intelligent automation strategies can give his team their weekends back, and make his team a proactive contributor and valued partner in growth of the business, one will likely find a different sort of audience.

All this means, to me, is that DevOps is a useful context for improving a complex sort of collaboration that’s called a software product lifecycle. Understanding the motivations and needs of the players in an organization  is a key enabler for planning and executing successful programs of change.

 

Discover more about our expertise in DevOps and the author Aaron Lee.

The post DevOps: Applied Empathy appeared first on Pythian - Data Experts Blog.

Categories: DBA Blogs

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

Hemant K Chitale - 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

Log Buffer #432: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-07-17 13:24

Yet again, this log buffer edition brings some rich blog posts from Oracle, SQL Server and MySQL.

Oracle:

  • Installing Oracle XE, ORDS and Apex on CentOS
  • Major Growth is Expected in the DBaaS Space. Are Your Skills Ready?
  • How to Hide Actions in OBPM 12c Workspace
  • You can configure auto refresh for ADF BC cached LOV and this works out of the box, no special coding is needed.
  • Search and Replace in Oracle SQL Developer Data Modeller

SQL Server:

  • Polyglot Persistence is a fancy term meaning that when storing data, it is best to use multiple data storage technologies, chosen based upon the way data is being utilized by individual applications or components of a single application.
  • HOW TO: Recreate SQL Server Statistics In a Different Environment
  • New SQL Server Management Studio 2015/June – with Check for Updates!
  • Power BI General Availability Announced: July 24, 2015
  • Ensuring Columns Are Always Present In A Table Returned By Power Query

MySQL:

  • Using MySQL sandbox for upgrade testing
  • Bypassing SST in Percona XtraDB Cluster with binary logs
  • Un-obvious “Unknown column in ‘field list’” error
  • Bypassing SST in Percona XtraDB Cluster with incremental backups
  • innodb_fast_checksum=1 and upgrading to MySQL 5.6

 

Learn more about Pythian’s expertise in Oracle , SQL ServerMySQL, as well as the author Fahd Mirza.

 

The post Log Buffer #432: A Carnival of the Vanities for DBAs appeared first on Pythian - Data Experts Blog.

Categories: DBA Blogs

Real Life DBA: Finding Errors in SQL Server

Pythian Group - Fri, 2015-07-17 13:00

I tend to spend time on a few different forums at times, mostly the StackExchange network, and find folks asking questions about various errors seen in the error log of an SQL Server instance. Most DBAs are aware that errors that SQL Server provides can be vague at times, so it takes a bit of knowing where to look for more information. In some situations it is after the fact before you can get to the instance to start your analysis so you have to focus on those areas that contain historical information. A few of those places to check are:

  1. Various DMVs, depending on the error, but things like connectivity errors you can search the DMV sys.dm_os_ring_buffers.
  2. System_Health XEvent session has various things like deadlock reports and some of the things you find in the ring buffer are written to this session as well.
  3. Default Trace, this has been around since 2005 but being that Profiler and Traces are on the depreciation list it is a unknown how long it will stay around, but it can help while it is there.

In the field, we have our Avail Monitoring that customers can utilize for monitoring an instance. It will monitor the error log for a SQL Server instance, and we will get a page when something is found that it does not know about. This is an error message I actually received today for a client:

Error9100_avail_page

That error messages only tells me the SPID that it occurred under, which we know someone else could be using that SPID by the time I get to the server. It does not provide the login or even database it pertains to. So, I went searching around and decided to go check the default trace, and actually found this bit of information:

Error9100_1 Error9100_2

You can see from this the SPID matches and I was able to get the login that caused the error and what was being done. When you get an issue with SQL Server that is vague or does not provide enough information, go back to the basics and recall what options you have in front of you.

 

Discover more about our expertise in SQL Server.

The post Real Life DBA: Finding Errors in SQL Server appeared first on Pythian - Data Experts Blog.

Categories: DBA Blogs

Monitoring and Diagnostics without Oracle Enterprise Manager

Hemant K Chitale - 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

Error referenced ‘irman ioracle’ during binary installation

DBASolved - Thu, 2015-07-16 19:27

This week I decided to redo a few of my virtual box machines and build a few new ones for testing. Getting the Oracle Linux 6.6 installed was a breeze; however, when I started to install the Oracle Database 12c binaries I was hitting errors during the linking phase. This had me puzzled for a bit to say the least. I kept getting this error:

After driving myself nuts, I decided to look closer to what is going on. The file listed in the error message contained a reference to ‘irman ioracle’. The actual message was:

So how did I get past this issue? The message is referring to a linking issue of the Oracle binaries. The issue is due to the libjavavm12.a file not being placed in the $ORACLE_HOME/lib. In order to fix this, I had to run:

cp /opt/app/oracle/product/12.1.0.2/dbhome_1/javavm/jdk/jdk6/lib/libjavavm12.a /opt/app/oracle/product/12.1.0.2/dbhome_1/lib/

Once this was ran, the installation completed without error and other configuration assistants within the binaries were able to complet successfully.

Enjoy!

about.me: http://about.me/dbasolved


Filed under: Database
Categories: DBA Blogs

Shift Command in Shell Script in AIX and Linux

Pakistan's First Oracle Blog - Tue, 2015-07-14 21:42
Shell in Unix never ceases to surprise. Stumbled upon 'shift 2' command in AIX few hours ago and it's very useful.

'Shift n' command shifts the parameters passed to a shell script by 'n' numbers to the left.

For example:

if you have a shell script which takes 3 parameters like:

./mytest.sh arg1 arg2 arg3

and you use shift 2 in your shell script, then the values of arg1 and arg2 will be lost and the value of arg3 will get assigned to arg1.

For example:

if you have a shell script which takes 2 parameters like:

./mytest arg1 and arg2

and you use shift 2, then values of both arg1 and arg2 will be lost.

Following is a working example of shift command in AIX:

testsrv>touch shifttest.sh

testsrv>chmod a+x shifttest.sh

testsrv>vi shifttest.sh

testsrv>cat shifttest.sh
#!/bin/ksh
SID=$1
BACKUP_TYPE=$2
echo "Before Shift: $1 and $2 => SID=$SID and BACKUPTYPE=$BACKUP_TYPE"
shift 2
echo "After Shift: $1 and $2 => SID=$SID and BACKUPTYPE=$BACKUP_TYPE"


testsrv>./shifttest.sh orc daily

Before Shift: orc and daily => SID=orc and BACKUPTYPE=daily
After Shift:  and  => SID=orc and BACKUPTYPE=daily


Note that the values of arguments passed has been shifted to left, but the values of variables has remained intact.
Categories: DBA Blogs

Sorry for the radio silence but this post will make up for it ( or maybe not ha ha ) makes me grumpy

Grumpy old DBA - Mon, 2015-07-13 17:07
It's been a busy year apologies for the lack of posts.  I have learned a couple of good things oracle related and probably forgotten more but most of the stuff I work on is now somewhat confidential that doesn't make it easy to blog about.

Last week at the Federal Reserve Bank of Cleveland we had a very important visitor.  It was Janet Yellen the chairwoman of the well everything the chair of the Federal Reserve.

She was delivering a speech at the well known Cleveland City Club but stopped our bank.  My area was involved in doing two brief demo's of what we are working on she sat on the other side of the table across from me.  I did not have a speaking part ( just eye candy I guess ha ha ) but the people in my area who did speak were also in pictures.  Maybe next time?

I am kind of thinking about a presentation on sql plan baselines as being something to work on this winter?  There are a number of good ones out there already but the critical part of using a baseline is figuring out where to get one from ( and quickly ha ha ).


Categories: DBA Blogs

RMAN -- 5 : Useful KEYWORDs and SubClauses

Hemant K Chitale - 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

Log Buffer #431: A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2015-07-10 08:46

This Log buffer edition covers Oracle, SQL Server and MySQL blog posts about new features, tips, tricks and best practices.

Oracle:

  • Traditionally, assigning specific processes to a certain set of CPUs has been done by using processor sets (and resource pools). This is quite useful, but it requires the hard partitioning of processors in the system. That means, we can’t restrict process A to run on CPUs 1,2,3 and process B to run on CPUs 3,4,5, because these partitions overlap.
  • Parallel_Degree_Limit, Parallel_Max_Degree, Maximum DOP? Confused?
  • JDeveloper 12c – ORA-01882: time zone region not found
  • Using a Parallel Gateway without a Merge in OBPM
  • Secure multi-threaded live migration for kernel zones

SQL Server:

  • How to Unlock Your Team’s Creative Power with SQL Prompt and a VCS
  • In-Memory OLTP – Common Workload Patterns and Migration Considerations
  • The Poster of the Plethora of PowerShell Pitfalls
  • Temporarily Change SQL Server Log Shipping Database to Read Only
  • Monitoring Availability Groups with Redgate SQL Monitor

MySQL:

  • Introducing MySQL Performance Analyzer
  • MySQL 5.7.8 – Now featuring super_read_only and disabled_storage_engines
  • Become a MySQL DBA – Webinar series: Which High Availability Solution?
  • How to obtain the MySQL version from an FRM file
  • MySQL Enterprise Audit: Parsing Audit Information From Log Files, Inserting Into MySQL Table

 

Learn more about Pythian’s expertise in Oracle , SQL ServerMySQL, as well as the author Fahd Mirza.

The post Log Buffer #431: A Carnival of the Vanities for DBAs appeared first on Pythian - Data Experts Blog.

Categories: DBA Blogs

Pillars of PowerShell: SQL Server – Part 1

Pythian Group - Thu, 2015-07-09 13:37
Introduction

This is the sixth and final post in the series on the Pillars of PowerShell, at least part one of the final post. The previous posts in the series are:

  1. Interacting
  2. Commanding
  3. Debugging
  4. Profiling
  5. Windows OS

PowerShell + SQL Server is just cool! You will see folks talk about the ability to perform a task against multiple servers at a time, automate implementing a configuration or database change, or just obtaining a bit of consistency when doing certain processes. I tend to use it just because I can, and it is fun to see what I can do. There are a some instances where I have used it for a specific purpose where it saved me time, but overall I just chose to use it. I would say that on average there are going to be things you can do in PowerShell that could be done in T-SQL, and in those cases you use the tool that fits your needs.

Interacting with SQL Server PowerShell

There are a three main ways to interact with SQL Server using PowerShell that I have seen:

  1. SQL Server PowerShell (SQLPS)
  2. SQL Server Server Management Object (SMO)
  3. Native .NET coding

I am not going to touch on the third option in this series because it is not something I use enough to discuss. I will say, it is not the first choice for me to use it, but it does serve a purpose at times.

To try and provide enough information to introduce you to working with PowerShell and SQL Server, I broke this into two parts. Part one, we are going to look at SQL Server PowerShell (SQLPS) and using the SQL Server Provider (SQLSERVER:\). In part two we will go over SMO and what can be accomplished.

SQLPS, to me, offers you quick access to do the one-liner type tasks against SQL Server. It is just a preference really on which option you go with, so if it works for you just use it. There are some situations that using the SQL Server Provider actually requires you to mix in using SMO (e.g. creating a schema or database role). It also offers up a few cmdlets that are added onto (and improved upon) with each release of SQL Server.

Loading/Importing

The first thing to understand is how to get the product module into your PowerShell session. As with most products, some portion of the software has to exist on the machine you are working on, or the machine your script is going to be executed on. SQL Server PowerShell and SMO are installed by default if you install the SQL Server Management Tools (aka SSMS and such) for SQL Server 2008 and higher. I will only mention that they can also be found in the SQL Server Feature Pack if you need a more “standalone” type setup on a remote machine.

One thing you should get in the habit of doing with your scripts is verifying certain things that can cause more errors than are desired, one of those is dealing with modules. If the module is not loaded when the script is run your script is just going to spit out a ton of red text. If the prerequisites are not there to begin with, there is no point in continuing. You can verify that a version of the SQLPS module is installed on your machine by running the following command:

Get-Module -ListAvailable -Name SQL*

If you are running SQL Server 2012 or 2014 you will see something like this:

SQLModule1

This works in a similar fashion when you want to verify if the SQL Server 2008 snap-in is loaded:

SQLSnapin1

I generally do not want to have to remember or type out these commands all the time when I am doing things on the fly, so I will add this bit of code to my PowerShell Profile:

Push-Location
Import-Module SQLPS -DisableNameChecking -ErrorAction 'Stop'
Pop-Location

#Load SQL Server 2008 by uncommenting next line
#Add-PSSnapin *SQL* -ErrorAction 'Stop'

One cool thing that most cmdlets you use in PowerShell contain is the -ErrorAction parameter. There are a few different values you can use for this parameter, and you can find those by checking the help on about_CommonParamters. If your script is one that is going to be interactive or run manually I would use -ErrorAction ‘Inquire‘ instead, try it out on a machine that does not have the module installed to see what happens. Once you have the module or snap-in loaded you will be able to access the SQL Server PowerShell Provider.

One side note, there actually is a “sqlps.exe” utility that is easily accessible in most cases via the right-click menu in SSMS (e.g. right-click on the “Databases” node in Object Explorer). If you open this, you are thrust into the SQLPS provider and the “directory” of the node you opened from in SSMS. However convenient as that may seem, it is something that was added to the depreciation list with SQL Server 2012, so there’s not much point in talking about it. It has its own little quirks that most folks steer clear of using it anymore.

Being Specific

The code I use in my profile is going to load the most current version of the module found on my system, at least it should. It may not do as you think it will every time. In some circumstances when you are developing scripts on your own system you may need to only import a specific version; especially if you are in a mixed version environment for SQL Server. You can load a specific version of the module by utilizing Get-Module to find your version, and just pass it to Import-Module.

Get-Module -ListAvailable -Name SQLPS | select name, path
#110 = SQL Server 2012, 120 = SQL Server 2014, 130 = SQL Server 2016
Push-Location
Get-Module -ListAvailable -Name SQLPS |
     where {$_.path -match "110"} | Import-Module
Pop-Location

# To show that it was indeed loaded
Get-Module -Name SQLPS | select name, path

#If you want to switch to another one, you need to remove it
Remove-Module SQLPS
Authentication

By default when you browse the SQLPS provider (or most providers actually), it is going to utilize the account that is running the PowerShell session, Windows Authentication. If you find yourself working with an instance that you require SQL Login authentication, don’t lose hope. You can connect to an instance via the SQL Server Provider with a SQL Login. There is an MSDN article that provides a complete function that you can use to create a connection for such a purpose. It does not show a version of the article for SQL Server 2008 but I tested this with SQL Server 2008 R2 and it worked fine.

SQLSnapin_Authentication

One important note I will make that you can learn from the function in that article: the password is secure and not stored or processed in plain text.

SQLPS Cmdlets

SQLPS as noted previously offers a handful of cmdlets for performing a few administrative tasks against SQL Server instances. The majority of the ones you will find with SQL Server 2012 for example revolve around Availability Groups (e.g. disabling, creating, removing, etc.). The other unmentionables include Backup-SqlDatabase and Restore-SqlDatabase, these do exactly what you think but with a few limitations. The backup cmdlet can actually only perform a FULL, LOG, or FILE level backup (not sure why they did not offer support of a differential backup). Anyway, they could be useful for automating backups of production databases to “refresh” development or testing environments as the backup cmdlet does support doing a copy only backup. Another way is if you deal with Express Edition you can utilize this cmdlet and a scheduled task to backup those databases.

Update 7/13/2015: One correction, where I should have checked previously, but the Backup cmdlet for 2012 and above does include an “-Incremental” parameter for performing differential backups.

The other main cmdlet you get with SQLPS is what most people consider the replacement to the sqlcmd utility, Invoke-Sqlcmd. The main thing you get from the cmdlet is a smarter output in the sense that PowerShell will more appropriately detect the data type coming out, compared to the utility that just had everything as a string.

SQLPS One-liners

Working with the SQL Server Provider you will traverse this provider as you would a drive on your computer. So you can use the cmdlet Get-ChildItem or do as most folks and use the alias dir. The main thing to understand is the first few “directories” to access a given SQL Server instance. There are actually multiple root directories under the provider that you can see just by doing “dir SQLSERVER:\“. You can see by the description what each one is for, the one we are interested in is the “Database Engine”

SQLProvider2

Once you get beyond the root directory it can require a bit of patience as the provider is slow to respond or return information. If we want to dig into an instance of SQL Server you just need to understand the structure of the provider, it will generally follow this syntax: <Provider>:\<root>\<hostname>\<instance name>\. The instance name will be “DEFAULT” if you are dealing with a SQL Server default instance. If you have a named instance you just add the name of the instance (minus the server name).

To provide a real-world example, Avail Monitoring is the custom tool Pythian developed to monitor the SQL Server environments of our customers (or Oracle or MySQL…you get the point). One of the features it includes, among many, is monitoring for failed jobs. We customize the monitoring around the customer’s requirements so some job failures will page us immediately when it occurs, while others may allow a few extra failures before we are notified to investigate. This is all done without any intervention required by the customer and I know from that notification what job failed. Well right off you are going to want to check the job history for that job to see what information shows up, and I can use SQLPS Provider to do just that:

# To see the job history
dir SQLSERVER:\SQL\MANATARMS\SQL12\JobServer\Jobs | where {$_.name -eq "Test_MyFailedJob"} | foreach {$_.EnumHistory()} | select message, rundate -first 5 | format-list
SQLProvider3
# if I needed to start the job again
$jobs = dir SQLSERVER:\SQL\MANATARMS\SQL12\JobServer\Jobs
$jobs | where {$_.name -eq "Test_MyFailedJob"} | foreach {$_.Start()}

You might think that is a good bit to typing, but consider how long it can take for me to do the same thing through SSMS…I can type much faster than I can click with a mouse.

Anyway to close things out, I thought I would show one cool thing SQLPS can be used for the most: scripting out stuff. Just about every “directory” you go into with the provider is going to offer a method named “Script()”.

$jobs | where {$_.name -eq "Test_MyFailedJob"} | foreach {$_.Script()}

I will get the T-SQL equivalent of the job just like SSMS provides, this can be used to document your jobs or used when refreshing a development server.

Summary

I hope you got the idea of what SQLPS can do from the information above, one-liners are always fun to discover. The SQL Server Provider is not the most used tool out there by DBAs, but it can be a life-saver at times. In the next post we will dig into using SMO and the awesome power it offers.

 

Discover more about our expertise in SQL Server

The post Pillars of PowerShell: SQL Server – Part 1 appeared first on Pythian - Data Experts Blog.

Categories: DBA Blogs

Reading System Logs on SQL Server

Pythian Group - Thu, 2015-07-09 12:54

HDDRecently, while I was working on a backup failure issue, I found that it was failing for a particular database. When I ran the backup manually to a different folder it would complete successfully, but not on the folder that it was directed to when the backup jobs were originally configured .  This makes me suspicious about hard disk corruption. In the end, I fixed the backup issues in the interim so that in the future I would not get paged, as well as lowering the risk of having no backup in place.

Upon reviewing the Windows Event logs, it was revealed that I was right about suspecting a faulty hard drive. The log reported some messages related to the SCSI codes, especially the SCSI Sense Key 3 which means SCSI had a Medium error. Eventually, the hard drive was replaced by the client and the database has been moved to another drive.  In the past month, I have had about 3 cases where I have observed that the serious messages related to storage are reported as information. I have included one case here for your reference, which may help you in case you see such things in your own logs.

CASE 1 – Here is what I found in the SQL Server error log:

  • Error: 18210, Severity: 16, State: 1
  • BackupIoRequest::WaitForIoCompletion: read failure on backup device ‘G:\MSSQL\Data\SomeDB.mdf’.
  • Msg 3271, Level 16, State 1, Line 1
  • A non-recoverable I/O error occurred on file “G:\MSSQL\Data\SomeDB.mdf:” 121 (The semaphore timeout period has expired.).
  • Msg 3013, Level 16, State 1, Line 1
  • BACKUP DATABASE is terminating abnormally.

When I ran the backup command manually I found that it ran fine until a specific point (i.e. 55%) before it failed again with the above error. Further, I decided to run DBCC CHECKDB which reports when a particular table has a consistency error at a particular page. Here are the reported errors:

Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:157134) with latch type SH. 121(The semaphore timeout period has expired.) failed.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:157134) allocated to object ID 645577338, index ID 0, partition ID 72057594039304192, alloc unit ID 72057594043301888 (type In-row data) 
was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header. The repair level on the DBCC statement caused this repair to be bypassed.

Of course, repairing options did not help as I had anticipated initially, since the backup was also failing when it reached at 55%. The select statement also failed to complete when I queried the object 645577338.  The only option that I was left with was to recreate the new table and drop the original table. After this had been done, the full back up succeeded. As soon as this was completed we moved the database to another drive.

I was still curious regarding these errors, so I started looking at Windows Error Logs – System folder, filtering it to show only Errors and Warnings.  However, this did not show me anything that attracted me to read further. Thus, I removed the filter, and carefully reviewed the logs.  To my surprise, the logs show entries for a bad sector, but, this was in the Information section of Windows Event Viewer, System folder.

Event Type: Information
Event Source: Server Administrator
Event Category: Storage Service
Event ID: 2095
Date: 6/10/2015
Time: 1:04:18 AM
User: N/A
Computer: SQLServer
Description: SCSI sense data Sense key: 3 Sense code:11 Sense qualifier: 0:  Physical Disk 0:2 Controller 0, Connector 0.

There could be a different error, warning or information printed on your server depending what the issue is. Upon further review there is still much to be said in order to explain codes and descriptions.

You may have noticed that I have referred to this as CASE 1, which means, I will blog one or two more case(s) in the future. Stay tuned!

Photo credit: Hard Disk KO via photopin (license)

Learn more about our expertise in SQL Server.

The post Reading System Logs on SQL Server appeared first on Pythian - Data Experts Blog.

Categories: DBA Blogs