In addition to Jonathan Lewis, this presentation became so special for me since I got a chance to meet my friend and an Oracle ACE Director , Francisco Munoz Alvarez in person. Though we interact almost every day on various web platforms but this was going to be the first ever chance when I was going to meet him in person and I am just so glad that it happened in this conference where he came to gave two presentations. It was for the first time that I saw him presenting and he was just so good!  We also did spend some time after the conference was over munching snacks and discussing tons of things . It was just so good to meet him and I hope we do meet again very soon!  Here is a pic of him presenting at the same conference,

In addition to Jonathan and Francisco, the conference also had the presentations from Mark Rittman, Iggy Fernandez, Murali Vallath and Vivek Sharma. It was a high doze of hard core technical content for 2 days . Overall, it was a very good event and I am glad that I did attend it with my friends Amardeep Sidhu, Neeraj Bhatia and Ankit Goel. We had a great time there and I hope, it would be the same in next year as well.

Its time for me to get going and do the last minute checks for ticket printouts and everything else. Next stop would be Hyderabad!

We know that the backups can be done in two ways, either by full or by incremental. The full backup is a must and is needed even for doing an incremental backup. The time taken for the full backup surely would be high and to bring it down, its suggested to do the incremental backup. There is a "fast incremental backup" also possible using the block change tracking file but that’s not the topic of this post today. For Change Tracking file and how it works, I would suggest to read the excellent paper from Alex which goes in real deep about it. So coming back to the backups, we know this as well that only the backup sets are elgible get backed up incrementally. The image copies are not elgible for being a part of the incremental backup and must be backed up full all the time. Actually, there is still chance to do the backup of image copies in the incremental manner but that’s only "level 0" which is actually nothing but the full backup only. So if we are expecting a lot of changes happening to our db and we have chosen to take the backup of the database via image copies, its a must to create new copies to capture the new values entered in the db.This is not a very good thing to do if you have many many datafiles which are also very large in size. Also, in the case of the recovery, it would be a tedious thing to first restore and then recover those copies! The best way would be that if somehow we could update the already backed up image copies with the current data and also can use them for recovery without restoring them! Wouldn’t that be faster? If your answer is yes, its possible from 10g onwards using the "recoverable image copies" via incremental backups.
The concept of "recoverable image copies" and incremental backup is that the image copy which you would be creating, would be constantly updated by oracle when the next time an incremental backup call for it would be issued! For doing this, the incremental backups that you would be creating , would be applied to the image copies, thus making them "refreshed" with the current work done within db. Since the resultant image copies would be having all the data available in them, they can be used right away for doing the recovery and on top of it, without even being required to be restored. We can "switch" to these "updated copies" and thus can start using them right away! Think about a situation that you have your datafiles stored on Disk 1 and it has got bad sectors now. There are updated image copies available for you on Disk2. With this mechanism, you can simply switch over to those copies and once you are done with the troubleshooting of your original drive, you can switch back to it! And in this whole process, the only thing that would be moved will be the file location pointers within the control file which would now start pointing towards the image copies! Isn’t that a "fast recovery" ?

Enough of talking, let’s see it in action. I am using a 11201 db on OEL5 here with the name called Sample! Its having couple of tablespaces which did come at the time of the creation of this db. I shall add to it one new tablespace which would be our candidate for testing. Here is the information about the db first,

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 29 09:03:54 2010 

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

Connected to: 

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production 

With the Partitioning, OLAP, Data Mining and Real Application Testing options 

SQL> select name from V$database; 

NAME 

--------- 

SAMPLE 

SQL> 

SQL> 

SQL> select name from V$datafile; 

NAME 

-------------------------------------------------------------------------------- 

/u01/app/oracle/oradata/sample/system01.dbf 

/u01/app/oracle/oradata/sample/sysaux01.dbf 

/u01/app/oracle/oradata/sample/undotbs01.dbf 

/u01/app/oracle/oradata/sample/users01.dbf

I shall now create a new tablespace and also a table within that tablespace which would contain some initial data. This table would go into the backup with this initial data and then we shall add more data to it which would be updated to the previously done backup using the incremenal methodology!Also, we shall take the backup as well.

SQL> create tablespace testtbs
  2  datafile 

3  '/u01/app/oracle/oradata/sample/testtbs.dbf' size 100m; 

Tablespace created. 

SQL> create table emp tablespace testtbs as select * from scott.emp; 

Table created. 

Now, using RMAN, we shall initiate the backup via image copies. Take a closer look at the command, 



RMAN> configure device type disk parallelism 4; 

using target database control file instead of recovery catalog
  new RMAN configuration parameters: 

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET; 

new RMAN configuration parameters are successfully stored 

RMAN> backup incremental level 1 for recover of copy tag 'testtbs_incr' database; 

Starting backup at 29-JUL-10
  allocated channel: ORA_DISK_1 

channel ORA_DISK_1: SID=20 device type=DISK 

allocated channel: ORA_DISK_2 

channel ORA_DISK_2: SID=142 device type=DISK 

allocated channel: ORA_DISK_3 

channel ORA_DISK_3: SID=21 device type=DISK 

allocated channel: ORA_DISK_4 

channel ORA_DISK_4: SID=143 device type=DISK 

no parent backup or copy of datafile 1 found 

no parent backup or copy of datafile 2 found 

no parent backup or copy of datafile 5 found 

no parent backup or copy of datafile 3 found 

no parent backup or copy of datafile 4 found 

channel ORA_DISK_1: starting datafile copy 

input datafile file number=00001 name=/u01/app/oracle/oradata/sample/system01.dbf 

channel ORA_DISK_2: starting datafile copy 

input datafile file number=00002 name=/u01/app/oracle/oradata/sample/sysaux01.dbf 

channel ORA_DISK_3: starting datafile copy 

input datafile file number=00005 name=/u01/app/oracle/oradata/sample/testtbs.dbf 

channel ORA_DISK_4: starting datafile copy 

input datafile file number=00003 name=/u01/app/oracle/oradata/sample/undotbs01.dbf 

output file name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_undotbs1_653b1sl7_.dbf tag=TESTTBS_INCR RECID=1 STAMP=725645515 

channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:43 

channel ORA_DISK_4: starting incremental level 1 datafile backup set 

channel ORA_DISK_4: specifying datafile(s) in backup set 

output file name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_sysaux_653b1sk5_.dbf tag=TESTTBS_INCR RECID=2 STAMP=725645539 

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:43 

channel ORA_DISK_2: starting datafile copy 

input datafile file number=00004 name=/u01/app/oracle/oradata/sample/users01.dbf 

including current control file in backup set 

channel ORA_DISK_4: starting piece 1 at 29-JUL-10 

channel ORA_DISK_4: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_ncnn1_TESTTBS_INCR_653b34w8_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:01 

channel ORA_DISK_4: starting incremental level 1 datafile backup set 

channel ORA_DISK_4: specifying datafile(s) in backup set 

including current SPFILE in backup set 

channel ORA_DISK_4: starting piece 1 at 29-JUL-10 

output file name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_users_653b36by_.dbf tag=TESTTBS_INCR RECID=3 STAMP=725645544 

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:05 

channel ORA_DISK_4: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnsn1_TESTTBS_INCR_653b369y_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:04 

output file name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf tag=TESTTBS_INCR RECID=4 STAMP=725645551 

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:01:06 

output file name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_system_653b3bqy_.dbf tag=TESTTBS_INCR RECID=5 STAMP=725645591 

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36 

Finished backup at 29-JUL-10

There are couple of interesting things here. First, we are taking a backup via image copy format. The second thing, there is an option added here which says "recover of copy". Yes, I did say backup and that’s what we did too but since we are talking about incrementally updated copies, this command contains the trick. Before opening the cards, the last interesting bit is mentioning of the incremental level 1 right away when we haven’t taken the level 0 backup yet! Again, this is how this image copy mechanism works!

What is happening is that we have asked the RMAN to take our database via image copy format with a supplied tag to it from us. But we have also mentioned that this backup, if is already done , should be just updating the previously done backup. So if we would had done a backup before, the recover command would had kicked and would had "updated" our previously dont image copy backup but if there is no level 0 backup is there to start with, Oracle would first take a level 0 backup only. This is also visible from the command output as available which does say that there was no parent backup found. So if there was no level 0 backup to start with, Oracle would take this level of backup on day 1. On the next day, the backup command would create  level 1 backup whose base would be the previous day’s level 0 backup. At this moment, for both day 1 and 2, only backup command is the one which would be working. Now, we do have a level 0 and a level 1 backup available with us. On day 3,the backup command would again create an incremental level 1 backup ranging the changes done from day 2 to day 3. Also, the Recover command would kick in now and would update the day 2 image copies with the incremental backup and the same process would keep on going on the subsequent days. For the identification of the datafiles which need to be updated like this, the use of the TAG is a must as this is the way through which the backup and recover commands would work with each other. So at any point, you have  updated image copies, incremental backups and the archived files to do the recovery! You would use the image copy, apply the incremental backup over it and wuold use the archive log files to update them with the latest work done in the db.

So, we have a backup that we have taken now as level 0 backup. Let’s add some data to our table EMP.

SQL> select count(*) from emp;
  COUNT(*) 

---------- 

14 



SQL> insert into emp select * from emp; 

14 rows created. 

SQL> / 

28 rows created. 

SQL> / 

56 rows created. 

SQL> / 

112 rows created. 

SQL> / 

224 rows created. 

SQL> / 

448 rows created. 

SQL> / 

896 rows created. 

SQL> / 

1792 rows created. 

SQL> / 

3584 rows created. 

SQL> / 

7168 rows created. 

SQL> / 

14336 rows created. 

SQL> / 

28672 rows created. 

SQL> / 

57344 rows created. 

SQL> commit; 

Commit complete. 

SQL> select name from V$datafile
    2  ; 

NAME
  -------------------------------------------------------------------------------- 

/u01/app/oracle/oradata/sample/system01.dbf 

/u01/app/oracle/oradata/sample/sysaux01.dbf 

/u01/app/oracle/oradata/sample/undotbs01.dbf 

/u01/app/oracle/oradata/sample/users01.dbf 

/u01/app/oracle/oradata/sample/testtbs.dbf 

SQL> select count(*) from emp; 

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

    114688 

SQL> 

So we have enough rows inserted into the table to make sure that in the next backup, there would be surely some new data that would be added to the backup files. Let’s some check some more things about the available tablespace and also about the backups that we have created.

Since we are wiling to update our image copy with the incremental backup, we can verify that its indeed update by the comparisons of the checkpoint scn of it before and after the recovery. If we check the copy of the tablespace TESTTBS that we have created, it would show the current checkpoint scn of it,

RMAN> list copy of tablespace testtbs; 

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

Key     File S Completion Time Ckp SCN    Ckp Time       ------- ---- - --------------- ---------- --------------- 

4       5    A 29-JUL-10       804275     29-JUL-10              Name: /u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

        Tag: TESTTBS_INCR

So the number is "804275". We shall use it to see whether we have got the image copies updated or not. Since we have added a lot from our intial backup, we shall kick in the backup again. This time, as we do have a level 0 backup already with us, the backup done would be a level 1 backup.

RMAN> backup incremental level 1 for recover of copy tag 'testtbs_incr' database; 

Starting backup at 29-JUL-10
  using channel ORA_DISK_1 

using channel ORA_DISK_2 

using channel ORA_DISK_3 

using channel ORA_DISK_4 

channel ORA_DISK_1: starting incremental level 1 datafile backup set 

channel ORA_DISK_1: specifying datafile(s) in backup set 

input datafile file number=00001 name=/u01/app/oracle/oradata/sample/system01.dbf 

channel ORA_DISK_1: starting piece 1 at 29-JUL-10 

channel ORA_DISK_2: starting incremental level 1 datafile backup set 

channel ORA_DISK_2: specifying datafile(s) in backup set 

input datafile file number=00002 name=/u01/app/oracle/oradata/sample/sysaux01.dbf 

input datafile file number=00004 name=/u01/app/oracle/oradata/sample/users01.dbf 

channel ORA_DISK_2: starting piece 1 at 29-JUL-10 

channel ORA_DISK_3: starting incremental level 1 datafile backup set 

channel ORA_DISK_3: specifying datafile(s) in backup set 

input datafile file number=00005 name=/u01/app/oracle/oradata/sample/testtbs.dbf 

input datafile file number=00003 name=/u01/app/oracle/oradata/sample/undotbs01.dbf 

channel ORA_DISK_3: starting piece 1 at 29-JUL-10 

channel ORA_DISK_4: starting incremental level 1 datafile backup set 

channel ORA_DISK_4: specifying datafile(s) in backup set 

including current control file in backup set 

channel ORA_DISK_4: starting piece 1 at 29-JUL-10 

channel ORA_DISK_4: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_ncnn1_TESTTBS_INCR_653bl7yd_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:08 

channel ORA_DISK_4: starting incremental level 1 datafile backup set 

channel ORA_DISK_4: specifying datafile(s) in backup set 

including current SPFILE in backup set 

channel ORA_DISK_4: starting piece 1 at 29-JUL-10 

channel ORA_DISK_4: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnsn1_TESTTBS_INCR_653blj2q_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:04 

channel ORA_DISK_2: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bl43m_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:27 

channel ORA_DISK_1: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bl456_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:39 

channel ORA_DISK_3: finished piece 1 at 29-JUL-10 

piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bm4x8_.bkp tag=TESTTBS_INCR comment=NONE 

channel ORA_DISK_3: backup set complete, elapsed time: 00:00:49 

Finished backup at 29-JUL-10

So the backup is done. This backup would be used to update the previously created image copies. As this is the current backup, there would be a difference in the checkpoint scn of it. Let’ssee the current backup of our tablespace and compare the checkpoint scn that we have,

RMAN> list backup of tablespace testtbs; 

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
  ------- ---- -- ---------- ----------- ------------ --------------- 

7       Incr 1  6.05M      DISK        00:00:42     29-JUL-10              BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TESTTBS_INCR 

        Piece Name: /u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bm4x8_.bkp 

  List of Datafiles in backup set 7 

  File LV Type Ckp SCN    Ckp Time  Name 

  ---- -- ---- ---------- --------- ---- 

  5    1  Incr 804762     29-JUL-10 /u01/app/oracle/oradata/sample/testtbs.dbf

So the checkpoint scn of the backup of the tablespace TESTTBS is 804762 which is indeed higher than our previously done image copy. Just for the sake of comparison, let’s check one more time the image copy that we created.

RMAN> list copy of tablespace testtbs; 

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

Key     File S Completion Time Ckp SCN    Ckp Time       ------- ---- - --------------- ---------- --------------- 

4       5    A 29-JUL-10       804275     29-JUL-10              Name: /u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

        Tag: TESTTBS_INCR 

Indeed its different than the backup! Its time now to recover this image copy with the current backup and archive logs,

RMAN> recover copy of tablespace testtbs with tag 'testtbs_incr'; 

Starting recover at 29-JUL-10
  using channel ORA_DISK_1 

using channel ORA_DISK_2 

using channel ORA_DISK_3 

using channel ORA_DISK_4 

channel ORA_DISK_1: starting incremental datafile backup set restore 

channel ORA_DISK_1: specifying datafile copies to recover 

recovering datafile copy file number=00005 name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bm4x8_.bkp 

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bm4x8_.bkp tag=TESTTBS_INCR 

channel ORA_DISK_1: restored backup piece 1 

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 

Finished recover at 29-JUL-10

Let’s compare both backup and image copy now with each other on the basis of their checkpoint scn’s,

RMAN> list backup of tablespace testtbs; 

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
  ------- ---- -- ---------- ----------- ------------ --------------- 

7       Incr 1  6.05M      DISK        00:00:42     29-JUL-10              BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TESTTBS_INCR 

        Piece Name: /u01/app/oracle/flash_recovery_area/SAMPLE/backupset/2010_07_29/o1_mf_nnnd1_TESTTBS_INCR_653bm4x8_.bkp 

  List of Datafiles in backup set 7 

  File LV Type Ckp SCN    Ckp Time  Name 

  ---- -- ---- ---------- --------- ---- 

  5    1  Incr 804762     29-JUL-10 /u01/app/oracle/oradata/sample/testtbs.dbf 

RMAN> list copy of tablespace testtbs; 

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

Key     File S Completion Time Ckp SCN    Ckp Time       ------- ---- - --------------- ---------- --------------- 

6       5    A 29-JUL-10       804762     29-JUL-10              Name: /u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

        Tag: TESTTBS_INCR

As expected, both have the same checkpoint SCN now and this does mean that the image copy is incrementally updated with our previous backup and is now containing the most current information. So we have an incrementally updated image copy available with us. Its time now to put this into action and simulate a datafile loss due to a faulty hardware. Due to this, the file is removed(we won’t remove it though) and also is needed to be recovered ASAP for which the updated image copy would help us. Let’s create the error first by renaming the file,

SQL> !mv /u01/app/oracle/oradata/sample/testtbs.dbf /u01/app/oracle/oradata/sample/testtbs.dbf.orig 

SQL> alter database datafile 5 offline; 

Database altered. 

SQL> alter database datafile 5 online;
  alter database datafile 5 online 

* 

ERROR at line 1: 

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file 

ORA-01110: data file 5: '/u01/app/oracle/oradata/sample/testtbs.dbf' 

So the file is not found and thus we have to recover it. Since we have the updated copy, we won’t need to restore the image copy and we can simply switch over to it. Let’s do it,

Though the file is switched to the image copy but if we would try to bring this file online ,it won’t be possible as we would need to apply the archive logs to be applied over it to make it consistent with the rest of the database. Let’s do this now,

RMAN> switch datafile 5 to copy; 

datafile 5 switched to datafile copy "/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf" 

RMAN> recover datafile 5 ; 

Starting recover at 29-JUL-10
  using channel ORA_DISK_1 

using channel ORA_DISK_2 

using channel ORA_DISK_3 

using channel ORA_DISK_4 

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

Finished recover at 29-JUL-10

So the recovery is complete. Let’s bring the file online now and verify our data from the table that’s its matching with our original count or not. If the recovery is complete, it must match!

SQL> alter database datafile 5 online; 

Database altered. 

SQL> select count(*) from emp; 

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

    114688

Bingo!!

So using the image copy, we have very simply and quickly have recovered our lost datafile. Let’s verify this by looking at the file name and path. If its indeed switched, the path and name must reflect towards the backup location of the file,

RMAN> report schema; 

Report of database schema for database with db_unique_name SAMPLE 

List of Permanent Datafiles
  =========================== 

File Size(MB) Tablespace           RB segs Datafile Name 

---- -------- -------------------- ------- ------------------------ 

1    680      SYSTEM               ***     /u01/app/oracle/oradata/sample/system01.dbf 

2    450      SYSAUX               ***     /u01/app/oracle/oradata/sample/sysaux01.dbf 

3    75       UNDOTBS1             ***     /u01/app/oracle/oradata/sample/undotbs01.dbf 

4    5        USERS                ***     /u01/app/oracle/oradata/sample/users01.dbf 

5    100      TESTTBS              ***     /u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

List of Temporary Files
  ======================= 

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name 

---- -------- -------------------- ----------- -------------------- 

1    29       TEMP                 32767       /u01/app/oracle/oradata/sample/temp01.dbf

So we can see that the control file is now pointing for the tablespace TESTTBS to the backup location only which just confirms our recovery using the incrementally updated image copies!

In case, you are willing to bring the file back to its original location, like in our case, to the ORADATA folder, its very simple. All what we need to do is to create a backup copy of this datafile at the required location and switch it back to that destination. Let’s do that as well.

Before doing the backup of the datafile, we shall add some more data to the datafile and will verify once the switching is over that it has come or not. So first, let’s insert some more data to the table,

SQL> insert into emp select * from emp; 

114688 rows created. 

SQL> commit; 

Commit complete. 

SQL> select count(*) from emp; 

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

    229376

Now, we shall take the backup of the datafile as image copy to our required destination.

RMAN> backup as copy to destination '/u01/app/oracle/oradata/sample' datafile 5; 

Starting backup at 29-JUL-10
  using channel ORA_DISK_1 

using channel ORA_DISK_2 

using channel ORA_DISK_3 

using channel ORA_DISK_4 

channel ORA_DISK_1: starting datafile copy 

input datafile file number=00005 name=/u01/app/oracle/flash_recovery_area/SAMPLE/datafile/o1_mf_testtbs_653b37bz_.dbf 

output file name=/u01/app/oracle/oradata/sample/SAMPLE/datafile/o1_mf_testtbs_653cbrlg_.dbf tag=TAG20100729T163328 RECID=7 STAMP=725646812 

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 

Finished backup at 29-JUL-10

Before we can switch, the datafile must be offline so let’s do that followed by the switching of the file and recovering it. We shall query the table after bringing the datafile online to verify that our newly inserted data has come or not.

RMAN> sql "alter database datafile 5 offline"; 

sql statement: alter database datafile 5 offline 

RMAN> switch datafile 5 to copy; 

datafile 5 switched to datafile copy "/u01/app/oracle/oradata/sample/SAMPLE/datafile/o1_mf_testtbs_653cbrlg_.dbf" 

RMAN> recover datafile 5; 

Starting recover at 29-JUL-10
  using channel ORA_DISK_1 

using channel ORA_DISK_2 

using channel ORA_DISK_3 

using channel ORA_DISK_4 

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

Finished recover at 29-JUL-10 

RMAN> sql "alter database datafile 5 online"; 

sql statement: alter database datafile 5 online

So the recovery is done. Let’s check the file name and path to verify that its switched back. We shall use RMAN to do so,

RMAN> report schema; 

Report of database schema for database with db_unique_name SAMPLE 

List of Permanent Datafiles
  =========================== 

File Size(MB) Tablespace           RB segs Datafile Name 

---- -------- -------------------- ------- ------------------------ 

1    680      SYSTEM               ***     /u01/app/oracle/oradata/sample/system01.dbf 

2    450      SYSAUX               ***     /u01/app/oracle/oradata/sample/sysaux01.dbf 

3    75       UNDOTBS1             ***     /u01/app/oracle/oradata/sample/undotbs01.dbf 

4    5        USERS                ***     /u01/app/oracle/oradata/sample/users01.dbf 

5    100      TESTTBS              ***     /u01/app/oracle/oradata/sample/SAMPLE/datafile/o1_mf_testtbs_653cbrlg_.dbf 

List of Temporary Files
  ======================= 

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name 

---- -------- -------------------- ----------- -------------------- 

1    29       TEMP                 32767       /u01/app/oracle/oradata/sample/temp01.dbf 

Since we had mentioned the location to the ORADATA folder, the file has been shifted over it. Let’s check the table row count as a final verification,

SQL> select count(*) from emp; 

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

    229376

Perfect!!!!

Though being a very powerful and relatively easy feature, I still find most of the dba’s unaware about it or if aware too, unsure how to use it. Hope the above demo would help to understand this feature somewhat and for complete understanding, nothing can beat oracle documentation,

http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmbckba.htm#i1034163

I always have believed in Steve Jobs saying “stay hungry, stay foolish” and looking at these legends, it just again strikes me that how much lesser knowledge I have! Hats off to you guys!

So in this discussion, since we were using good’ol, terminal prompt, I just recalled that there is an option in this client tool to set the case to whatever we want! The default setting of this is set to “mixed” . The delegates immediately checked the parameter and changed it to UPPER case and then also to LOWER. Well, nothing changed . See for yourself,

SQL> set sqlcase upper
  SQL> show sqlcase 

sqlcase UPPER 

SQL> select * from scott.dept; 

    DEPTNO DNAME          LOC
  ---------- -------------- ------------- 

        10 ACCOUNTING     NEW YORK 

        20 RESEARCH       DALLAS 

        30 SALES          CHICAGO 

        40 OPERATIONS     BOSTON 

SQL> set sqlcase lower
  SQL> select * from scott.dept; 

    DEPTNO DNAME          LOC
  ---------- -------------- ------------- 

        10 ACCOUNTING     NEW YORK 

        20 RESEARCH       DALLAS 

        30 SALES          CHICAGO 

        40 OPERATIONS     BOSTON

So what does this setting did actually? Well since its a client tool, the change of the case send the query , including everything with it, like literals and all, in the proposed case. But how do we go and check and confirm it?

Since sql*plus is a client tool, the best way to check such thing was to enable client side network tracing. For this, we entered the following entries into the sqlnet.ora

TRACE_LEVEL_CLIENT= support : This parameter would enable the tracing for the OCI client connections and the level is set to gather the maximum information. This is not something which should be used in a normal environment/condition because the information collected would be huge!

TRACE_DIRECTORY_CLIENT=/tmp:  This parameter would set the directory where the trace file should go.

DIAG_ADR_ENABLED=OFF : This parameter would not let the tracing information go to the 11g specific Automatic Diagnostic Repository(ADR) .

So after setting all these parameters, this is how the sqlnet.ora would look.

TRACE_LEVEL_CLIENT=support 

TRACE_DIRECTORY_CLIENT=/tmp 

DIAG_ADR_ENABLED=OFF 

 

After that, we connected from sql*plus with the user Scott again and issued the same command, select * from dept , first in lower case and then in upper case. Doing this would create immediately a file in the /tmp with a name like cli_25111.trc. Since we had used the tracing level to support, the file is very huge. So I am going to chop off lots of content from it. Here are the sections which actually we were looking for in the file,

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
  [29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 12 73 65  |......se| 

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 6C 65 63 74 20 2A 20 66  |lect.*.f| 

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 72 6F 6D 20 64 65 70 74  |rom.dept| 

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 01 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 01 00 00 00  |........| 

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:40:57:613] nsbasic_bsd: 00 00 00 00              |....    | 

[29-JUN-2010 17:40:57:613] nsbasic_bsd: exit (0) 

[29-JUN-2010 17:40:57:613] nsbasic_brc: entry: oln/tot=0 

[29-JUN-2010 17:40:57:624] nsbasic_brc: type=6, plen=436 

[29-JUN-2010 17:40:57:624] nsbasic_brc: what=1, tot =436

<output trimmed>

[29-JUN-2010 17:41:06:731] nsbasic_bsd: FE FF FF FF FE FF FF FF  |........|
  [29-JUN-2010 17:41:06:731] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 12 53 45 4C  |.....SEL| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 45 43 54 20 2A 20 46 52  |ECT.*.FR| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 4F 4D 20 44 45 50 54 01  |OM.DEPT.| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 01 00 00 00 00  |........| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........| 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: 00 00 00                 |...     | 

[29-JUN-2010 17:41:06:732] nsbasic_bsd: exit (0) 

[29-JUN-2010 17:41:06:732] nsbasic_brc: entry: oln/tot=0 

[29-JUN-2010 17:41:06:742] nsbasic_brc: type=6, plen=436 

[29-JUN-2010 17:41:06:742] nsbasic_brc: what=1, tot =436

So we can see that the case used by both the queries from the sql*plus tool is changed. Nothing really great? Well did I say that it would be ? It was a just something which came across and was interesting so thought to share it!

And here is my ACE profile page’s snapshot!

Its an amazing feeling to have this award, I must say! I am really happy and having a feeling of proud that I am a part of such esteemed group.  Its just great to see that you are in those 280 names in this entire world who have got this award and are a part of this club. Its just so overwhelming to see that I am in that elite club whose member list contains names like Jonathan Lewis, Tim Hall, Hans Forbrich, Francisco Munoz and many many more Gurus who are like the pillars and ideals of millions within Oracle community. Words fail me to express what I am feeling at the moment but I must say, I am really really happy!

Thank you so much Francisco for nominating me for ACE award and thanks Oracle ACE program for considering me worthy enough to be awarded by this award!

Books, though being a very integral part in the learning, need to be very carefully chosen. I normally mention that even though one read 10 (bad) books about Oracle database, he/she still may not learn even a single thing. It takes a lot of effort to write a good book and when such kind of book becomes a part of your library, it becomes an asset of yours. At least, this is true for me for sure since I do purchase a lot of Oracle related books and because I spend my own personal money for this purpose, I take utmost care when I am making a purchasing decision. Today, I want to share with you one such book which I would surely recommend in my next DBA session,

Packt Publishing is doing a great work by giving opportunities to many authors to share their knowledge with the rest of the Oracle community. I got an offer by Packt Publishing earlier also to review their title Middleware Management with Oracle Enterprise Manager Grid Control 10g R5 by Debu Panda! You can read that review from here . Once again, I have got an opportunity by Packt to review another new title from them, Oracle Database 11g – Underground Advice for Database Administrators by April C.Sims. April has been working as a senior DBA with Utah University and also maintains an active Oracle related blog.

The very first thing that I liked about the book is that it was really concise. I mean, there are not too many chapters,just created to increase the count. Also the distribution of the content within the chapters has also come up very nice. April starts with telling us(which is very very important) that what we should an should NOT do when we are dealing with Oracle database. There are tons of times, I have seen people making things more serious, more worse just by not stopping at the first step where they went wrong and also, by not asking for help when they really need it! In the first chapter, April brings us these key points! A must thing to tell if you ask me!

The second chapter deals the standards that are related to Oracle database. I have seen many databases made and maintained like a complete mess, where the name of the database is something like SR1 which completely fails to tell us anything meaningful about the database, the tablespace and datafile names coming up like mytbs and mydatafile1.dbf. Now surely they are yours but you don’t need to put that in the names too since someone, who would come from outside won’t be able to understand it all. The same is true for the installation which happen where a DBA would like to happen, some times in a properly maintained way and sometimes, just where ever he likes! Sure enough that Oracle would work but in the longer run, this may become a complete mess to deal with. April, in her second chapter, makes us aware about these key points and also tells us how we can deal with them in a better by making us remind that there is an OFA standard given to the Oracle community long time back, which can help in such of kind of things. Also, she tells us about some tools and some references which would help help us whenever we would get struck anywhere.

Chapter 3 is my favorite chapter since I am completely addicted to know how things work within Oracle database internally. In this chapter, April takes us to some not-so-well-explained domains of Oracle like Oracle block dumps and how to read and use them, explains us the working of the internal parts of Oracle database architecture. In addition to these, she also explains to some new goodies offered by 11g like Automatic Memory Management! Overall, its a very interesting chapter and has become my personal favorite as well!

The next chapter deals with something which is now becoming a hot topic everywhere, Maximum Availability! In this chapter, April explains quite well how MAA architecture works and also its related components like ASM, Flashback , Flashback Logs etc. April also explains to us how we can use Grid Control for the same task. This chapter is the starting point for the next two chapters which deal with the technical stuff related to MAA in a more deeper manner.

Subsequent  chapters 5 and 6, as I said just now, go deeper into the MAA and in these, April shares with us some of the key components of it like Dataguard and Flashback which help a lot to achieve the minimum downtime approach requested by many shops. April covers up almost all what is needed to become highly available. A good description of complex technologies like Dataguard and also, how to do recoveries using both manual and 11g specific automated ways, give a very valuable information. April also explains quite extensively a very important and good tool, RMAN and its various uses, including the details of how to use it to make standby database in a very effective manner.

Since 11g is now the most latest release of Oracle database, there are and would be lots of migrations that would be happening from the pervious versions to 11g. The chapter 7 deals with the same topic and in it, April explains to us various ways to achieve a flawless migration to 11g with different approaches. Using this chapter as a reference and also the database upgrade guide, I believe , one would know everything that he needs to do a successful up gradation to 11g.

The last chapter, is a collection of information of various useful tools that can come very handy when it comes to performance tuning. Staring from ORION, ADDM, ASH , April explains to us Statspack, AWR and also the performance pages of the Enterprise Manager which are very helpful in order to both diagnose and troubleshoot performance related issues. April, in this chapter, not only talks about database related tuning tool but also touches the query tuning as well, explaining various things related to it like SPM , how statistics and optimizer works! Overall, the chapter comes up as a good reference for the performance tuning related material.

In addition to the material written by April based on her insights and experiences with Oracle database, she has also put up in almost every topic, a very extensive listing of Oracle’s My Oracle Support(MOS) document note ids. Its always very difficult to find the right document, especially when you need it the most, so this listing is very useful IMO! Those who are familiar with MOS, must agree to this that its not an easy task to find the right note for the right topic so kudos to April for doing efforts and collecting these documents id’s for us.

As I said before too, a good book is always going to remain useful and this book from April, is one of those few good ones! The book content is rich and also the language chosen is simple, making it a very good read even when it deals with such a complex technology like Oracle database. A good work done by April and a recommended book!

The review, can’t be called complete without me saying thanks to Packt Publishing for two things. One, for giving me an opportunity to get my hands on this book and review it and second, for getting this book out for Oracle community! As much effort it takes to write a good piece of literature, the same goes for getting it furnished and bringing it out for the public and Packt is going a great work in it! Hope we shall see more good Oracle related titles coming from Packt in the future as well!