Role of SCN in Instance Recovery & Media Recovery

In last post we have discussed the different types of checkpoints and association of SCN. Also we have examined the status of datafile headers wrt End (checkpoint) SCN after a clean shutdown. In this post we are examining role of SCN in instance recovery and media recovery.

SCN after an Instance Crash

In this session we are going to demonstrate the behavior of the checkpoints after an instance crash, the following SQL creates a table (which performs an implicit commit) and inserts a row of data into it without a commit:

SQL> create table dummytable (x number) tablespace test;
Table created.

SQL> insert into dummytable values(100);
1 row created.


During subsequent startup after instance crash Oracle will realize before opening database that value for Last_Change# column for datafiles are not showing SCN value , which means END Checkpoint SCN was not done, which would be indication for Oracle that instance recovery is required.

Let’s mimic instance failure and then startup mount to examine the situation

Read more of this post

Advertisements

Syncing Standby Database Using SCN Based Backup

In Oracle dataguard environment sometime we face issues with slow network pushing us into situation where the changes are piled up and normal redo shipping speed is not sufficient to cover the gap within required business timelines or might be archives/redo never shipped to standby database because of unavailability of standby database and you don’t have enough space on primary site to hold all required archives for recovery. In those situations you might take a decision clear space on primary site after moving archives to tape. Later on when standby will be available FAL service would not able to fetch the gap. One option is to restore those archives to standby site and register those archives while another option, which is of intermediate complexity in case we don’t have identical physical structure in both sites.

Today we will demonstrate recovery of standby database with SCN based incremental backup in environment where we have shared filesystem on primary site while standby database is hosting files in ASM.

  1. Check the minimum SCN on standby database from where the changes need to be applied.

    Standby Site

    SQL> select min (checkpoint_change#) from v$datafile_header order by 1;

    MIN(CHECKPOINT_CHANGE#)
    ———————–
    109182027

    Read more of this post

DB migration to ASM Storage

Why ASM? It’s debatable point between DBAs and Storage experts. Industry wide Oracle users could be agreed on few points:

  1. Reduction in administration overhead.
  2. Better I/O as compared to placement of datafiles over filesystem.
  3. Online expansion and migration to faster disks.
  4. Storage management similar to RAC databases.

ASM providing placement of data in chucks (called Allocation Units) along with beauty of automatically rebalances across available disks. Today I am here to share demo of DB migration from filesystem to ASM. Normally there could be few possible ways to achieve migration activity. This is tough to say the best way for migration without actually understanding business requirement.

  • Migration of physical database
    • Take image copy of database in ASM storage and switch database to copy.
    • Take RMAN backup and restore it in ASM disks followed by switch copy.
    • Duplicate database into ASM diskgroup.
  • Migration with physical replication
    • Dataguard or Transportable tablespace
  • Migration with logical replication setup
    • Any replication method (Golden Gate, Streams, shareplex etc) to setup parallel environment and perform switchover.

Migration using physical standby could involve less downtime and easy performing. Not everyone can afford this approach as it involves extra hardware resources.

Today we will demonstrate migration from filesystem to ASM using RMAN. Apart from straight we will also try to restore spfile and control file from autobackup. This method is one the scenario where we lost our spfile or controlfile. Once controlfile would be available, so the information of backups as well. Although backups could also be registered if required. Read more of this post

What happened when tablespace/database placed into backup mode?

User managed backup or physical backup without RMAN is still one of the popular method for backup and still part of mostly disk to disk backup methods (BCV or SRDF copy). Today I am going to discuss what happens when we place tablespace/database into backup mode.

Fractured Block

Unit of I/O for any system is block size and for oracle database it’s 2K/4K/8K/16K/32K while for any operating system it could be lower than that. One risk in making online backups is the possibility of inconsistent data within a block. Assume that OS I/O block size is 512 bytes while we used DB block size as 8K.

This means to copy utility of OS would take 16 I/Os to take backup of one DB block, while during the backup, DBWR could write 8K in one I/O.

So assume a scenario where copy utility is busy taking backup of 8th portion of particular block while DBWR came to overwrite same DB block (i.e. all 16 OS block pieces in one DB block). Because copy utility was still busy on taking backup of remaining blocks so it took new image from OS piece 9th onwards. From database perspective that backup copy of db block (collection of 16 OS blocks) has been fractured as its half or the OS blocks are old and remaining half are new. The result is called a fractured block, meaning that the data contained in this block is not consistent at a given SCN.

Oracle’s internal protection mechanism against fractured block

To overcome this danger Oracle introduced a protection feature which will be activate once you trigger ‘alter tablespace/database begin backup’ command. Following internal operations happened with immediate effect after triggering begin backup for tablespace Read more of this post

Can I Clear Current Redo Logfile while it’s required for recovery??

Normally we need to clear redo log if its corrupted and oracle is not able to reuse it. It may already be archived or may not be. In both cases we need to clear it to allow oracle instance to re-use it. You have to make active redolog to inactive by manual checkpoint or by switching logfile (if possible).

Commands would be

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group number>;

ALTER DATABASE CLEAR LOGFILE GROUP <group number>;

In case of corruption in current redo log following two scenarios could take place

1) Database instance crashed with Ora- 600 error; You can’t do much as now you need to check your last successful backup and all archives/incremental backup to perform incomplete recovery.

or

2)  Database still up and running (chances are less but possible); So could we try to clear the corrupted current redo log??

Let’s try to mimic the situation (except corruption)

Read more of this post

Pierre blog

Pierre Forstmann Oracle Database blog

flashdba

Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music