Category Archives: RMAN

12c, backup/recovery, flashback, RMAN

Oracle 12c – Steps to perform a Flashback Database Operation

Leave a comment

The flashback database feature allows you to reset the database to earlier point in time to resolve logical corruption.    It is faster than point in time recovery and does not require backup and restore operations.  Flashback database operations is available from RMAN or SQL with command: FLASHBACK DATABASE.   To enable flashback database the FAST RECOVERY AREA must be configured and FLASHBACK RETENTION TARGET set.   The DB_FLASHBACK_RETENTION_TARGET states how far in the past the database can be recovered to and Flashback must be setup in advance of need. (See article Enabling Flashback Database)  The database periodically copies blocks from every datafile into recovery area and maintains logs of changes to those blocks.  Additionally flashback logs, redo logs must be available for entire span of retention target.   FLASHBACK DATABASE window is the time that is covered by flashback logs.  FLASHBACK will delete oldest logs first if it runs low on space.

 

FLASHBACK has these limits:

  1. It can only undo changes to datafile made by Oracle Database, not media failure or deletion of file.
  2. It cannot undo shrink operations.
  3. If control file is recovered or recreated all flashback data is invalid.
  4. IF NOLOGGING is used, block corruption is likely if flashback is used.

 

Restore points can be used instead of time or SCN, Restore points are alias of scn or time.

Commands that support restore point are RECOVER DATABASE and FLASHBACK DATABASE in RMAN   and FLASHBACK TABLE in SQL.  Guaranteed restore points never age out of control file and must be explicitly dropped, it ensures that you can use flashback database to a single SCN even if flashback logging is not enabled.  When enabled it enforces retention of flashback logs all the way to guaranteed SCN.  This article will demonstrate database flashback in RMAN.

 

  1. Logon to server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Thu Jan  5 10:45:29 EST 2015 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$

 

  1. Start RMAN and connect to your database.

 

[oracle@linux2 ~]$ rman

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 10 09:18:41 2015

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

RMAN> connect target /

connected to target database: ORCL (DBID=1457738340)

RMAN>

 

  1. Use timestamp_to_scn function to get SCN for 48 hours ago.

 

RMAN> select timestamp_to_scn(systimestamp-2) from dual;

TIMESTAMP_TO_SCN(SYSTIMESTAMP-2)

——————————–

                         4236799

RMAN>

 

  1. Shutdown the database with immediate option if open.

 

RMAN> shutdown immediate

using target database control file instead of recovery catalog

database closed

database dismounted

Oracle instance shut down

RMAN>

 

  1. Startup the database with mount.

 

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area     549453824 bytes

Fixed Size                     2926616 bytes

Variable Size                272631784 bytes

Database Buffers             268435456 bytes

Redo Buffers                   5459968 bytes

RMAN>

 

  1. Execute the FLASHBACK DATABASE command with SCN retrieved above.

 

flashback database to scn 4236799;

RMAN> flashback database to scn 4236799;

Starting flashback at 10-JAN-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=22 device type=DISK

 

starting media recovery

 

archived log for thread 1 with sequence 2761 is already on disk as file /u04/oradata/ORCL/flash_recovery_area/ORCL/archivelog/2015_01_08/o1_mf_1_2761_d74og7tj_.arc

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

Finished flashback at 10-JAN-17

 

RMAN>

 

  1. Open the database with command ‘ALTER DATABASE OPEN READ ONLY;’

 

RMAN> alter database open read only;

Statement processed

RMAN>

 

  1. If not errors, shutdown the database with immediate option.

 

RMAN> shutdown immediate

database closed

database dismounted

Oracle instance shut down

RMAN>

 

  1. Startup the database with mount option.

 

RMAN> startup mount

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area     549453824 bytes

Fixed Size                     2926616 bytes

Variable Size                272631784 bytes

Database Buffers             268435456 bytes

Redo Buffers                   5459968 bytes

 

RMAN>

 

  1. Open the database with resetlogs option: ‘ALTER DATABASE OPEN RESETLOGS;’

 

RMAN> ALTER DATABASE OPEN RESETLOGS;

Statement processed

RMAN>

 

  1. You should take a full backup at the end of any recovery.
  2. This completes flashing back a database in RMAN.

 

Larry Catt

OCP

12c, backup/recovery, RMAN

Oracle 12c -RMAN Backup Overview

Leave a comment

Oracle provides the Recovery Manager utility (RMAN) to perform two types of database backups: Image Backup and Backup Set.    RMAN command BACKUP AS COPY command creates an image copy or bit for bit copy of the data files, archived redo log and controlfiles.

 

RMAN> BACKUP AS COPY DEVICE TYPE DISK DATABASE;

 

You can set the default backup to image copy with CONFIGURE DEVICE TYPE command

 

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;

 

Backup sets are the other type of RMAN backups, each backup set has one or more binary files called backup pieces, in proprietary format that can be restored by RMAN.  You can limit the size of size of backup pieces by the MAXPIECESIZE option.   Use the RMAN command BACKUP AS BACKUPSET to create backup copy of database.

 

RMAN> BACKUP AS BACKUPSET DATABASE.

 

You can set the default backup to backup set with the CONIFGURE DEVICE TYPE command.

 

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

 

You can backup the entire database with the command:

 

RMAN> BACKUP DATABASE;

 

Add PLUS ARCHIVELOGS clause will cause a log switch that allows for full media recovery to the point of starting the backup.

 

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

 

Incremental Backups are part of a backup set that only contains changes to the database since the last full backup.   The benefit of an incremental backup is size of the backup and time required to tack the backup.   Incremental Backups are only good if combined with the last full backup and any incremental backup between the last full backup.

 

You can create an incremental backup with the RMAN command BACKUP INCREMENTAL in three ways

 

Level 0 – Identical to full back

Level 1 Differential – backups all changed blocks since most recent incremental backup.

Level 1 Cumulative – backups all changed block since most recent Level 0 incremental backup

 

Restoring incremental backup use level 0 at start and apply level 1 cumulative or all differential.

 

Example of incremental Level 1:

 

RMAN> backup incremental level 1 cumulative database;

 

RMAN> backup incremental level 1 database;

 

 

Larry Catt

OCP