Oracle RMAN : Incremental Backups

In Previous articles we have discussed, RMAN Introduction  and started with RMAN commands and first backup. Now we will discuss more about incremental backups.

• For large databases taking full backup is not possible every time as it will consume lot of space, time and resources and may cause performance degradation while database is running.
• Incremental backups are designed to for such scenarios and are among best options.
• As we already discussed here incremental backups are backups which includes only modified blocks since last full incremental backup taken.
• There is base backup level designated with level 0 and subsequent backups after it are labeled level 1.
• For level 1 there another two options
• Differential incremental backups
• Cumulative incremental backups
• Theoretically these are already discussed in detail in RMAN introduction

• Now will discuss commands to take these type of backups

Differential backup at LEVEL 0 for the database

RMAN> backup incremental level 0 database;

Starting backup at 09-JAN-19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=23 devtype=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLE\ORADATA\TECH_DB\SYSAUX.DBF

input datafile fno=00001 name=C:\ORACLE\ORADATA\TECH_DB\SYSTEM.DBF

input datafile fno=00004 name=C:\ORACLE\ORADATA\TECH_DB\USERS.DBF

input datafile fno=00005 name=C:\ORACLE\ORADATA\TECH_DB\TECHON_TBS.DBF

input datafile fno=00002 name=C:\ORACLE\ORADATA\TECH_DB\UNDO.DBF

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NNND0_TAG20190

109T205403_G3D4JQTM_.BKP tag=TAG20190109T205403 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NCSN0_TAG20190

109T205403_G3D4M4X5_.BKP tag=TAG20190109T205403 comment=NONE

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

Finished backup at 09-JAN-19

RMAN>

For LEVEL 1

RMAN> backup incremental level 1 database;

Starting backup at 09-JAN-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLE\ORADATA\TECH_DB\SYSAUX.DBF

input datafile fno=00001 name=C:\ORACLE\ORADATA\TECH_DB\SYSTEM.DBF

input datafile fno=00004 name=C:\ORACLE\ORADATA\TECH_DB\USERS.DBF

input datafile fno=00005 name=C:\ORACLE\ORADATA\TECH_DB\TECHON_TBS.DBF

input datafile fno=00002 name=C:\ORACLE\ORADATA\TECH_DB\UNDO.DBF

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NNND1_TAG20190

109T205803_G3D4R3OL_.BKP tag=TAG20190109T205803 comment=NONE

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

channel ORA_DISK_1: starting incremental level 1 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NCSN1_TAG20190

109T205803_G3D4S8LC_.BKP tag=TAG20190109T205803 comment=NONE

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

Finished backup at 09-JAN-19

RMAN>

For taking cumulative incremental backup

RMAN> backup incremental level 1 cumulative database;

Starting backup at 09-JAN-19

using channel ORA_DISK_1

channel ORA_DISK_1: starting incremental level 1 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLE\ORADATA\TECH_DB\SYSAUX.DBF

input datafile fno=00001 name=C:\ORACLE\ORADATA\TECH_DB\SYSTEM.DBF

input datafile fno=00004 name=C:\ORACLE\ORADATA\TECH_DB\USERS.DBF

input datafile fno=00005 name=C:\ORACLE\ORADATA\TECH_DB\TECHON_TBS.DBF

input datafile fno=00002 name=C:\ORACLE\ORADATA\TECH_DB\UNDO.DBF

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NNND1_TAG20190

109T210011_G3D4W49C_.BKP tag=TAG20190109T210011 comment=NONE

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

channel ORA_DISK_1: starting incremental level 1 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 09-JAN-19

channel ORA_DISK_1: finished piece 1 at 09-JAN-19

piece handle=C:\ORACLE\APP\ORACLE\FLASH_RECOVERY_AREA\TECH_DB\BACKUPSET\2019_01_09\O1_MF_NCSN1_TAG20190

109T210011_G3D4X96M_.BKP tag=TAG20190109T210011 comment=NONE

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

Finished backup at 09-JAN-19

RMAN>

To make most of incremental backups, you can use different combined approaches

• Taking full level 0 backup on tape drives as it will be once in a week.
• Taking incremental level 1 backup on disk
• Use of compression

Comment below if any additional information is required.

Read More

Oracle : Starting with RMAN Commands

In previous article we have discussed introduction to RMAN, Now will learn RMAN commands and taking backup of database and understanding its output.

RMAN is very easy tool to interact with, it understand simple English language. If we want to take backup then just type backup in RMAN prompt along with whether backup of database or tablespace, so we want to take backup of database then backup database will do the needful.

RUN Block
In order to execute number of RMAN commands together like restore, recover and open then we need to use RUN block. This will run commands sequentially.
for example

RMAN> run

2> {

3> restore database;

4> recover database;

5> alter database open;

 6> }

This run block first restore the database, then recover database and at last it will open database.

Taking First Full Backup
Now will take full database backup and explain each line in details. Backup of database will get started on disk at FRA (Fast Recovery Area) location. Those who do not know FRA, I will write another post but for here understand it is defined with bellow two parameters
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
Now invoke RMAN client and execute below command to start backup

RMAN> backup database plus archivelog;

This is command to start database backup with archive log

Starting backup at 03-JAN-19

This indicates, backup starting at mentioned date

current log archived

This line shows current log is archived

using target database control file instead of recovery catalog

This line indicates, RMAN is using target control file and not recovery catalog

allocated channel: ORA_DISK_1

This indicates, we are using disk channel 

channel ORA_DISK_1: sid=21 devtype=DISK

channel ORA_DISK_1: starting archive log backupset

This indicates starting of archived log backup set, its list and sequence is shown in subsequent below lines

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=2 recid=1 stamp=992908371

input archive log thread=1 sequence=36 recid=2 stamp=996615449

channel ORA_DISK_1: starting piece 1 at 03-JAN-19

channel ORA_DISK_1: finished piece 1 at 03-JAN-19

piece handle=C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\BACKUPSET\2019_01_03\O1_MF_ANNNN_TAG20190

103T213732_G2WDT5NH_.BKP tag=TAG20190103T213732 comment=NONE

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

Finished backup at 03-JAN-19

This indicates backup of archived log is finished, it automatically generates tag, format for tag is TAGYYYYMMDDTHHMMSS

Starting backup at 03-JAN-19

using channel ORA_DISK_1

This is start of full database backup, its take single disk channel as we have not mentioned any. 

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF

input datafile fno=00001 name=C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF

input datafile fno=00004 name=C:\ORACLEXE\ORADATA\XE\USERS.DBF

input datafile fno=00005 name=C:\ORACLEXE\ORADATA\XE\TECHON_TBS.DBF

input datafile fno=00002 name=C:\ORACLEXE\ORADATA\XE\UNDO.DBF

This are the datafiles which are getting backup 

channel ORA_DISK_1: starting piece 1 at 03-JAN-19

channel ORA_DISK_1: finished piece 1 at 03-JAN-19

piece handle=C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\BACKUPSET\2019_01_03\O1_MF_NNNDF_TAG20190

103T213734_G2WDT7CR_.BKP tag=TAG20190103T213734 comment=NONE

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

At this point creation of backup set for full database is complete

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 03-JAN-19

channel ORA_DISK_1: finished piece 1 at 03-JAN-19

piece handle=C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\BACKUPSET\2019_01_03\O1_MF_NCSNF_TAG20190

103T213734_G2WDVOWG_.BKP tag=TAG20190103T213734 comment=NONE

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

Finished backup at 03-JAN-19

control file and spfile are being backed up here. This will automatically gets backed up.

Starting backup at 03-JAN-19

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archive log backupset

channel ORA_DISK_1: specifying archive log(s) in backup set

input archive log thread=1 sequence=37 recid=3 stamp=996615503

channel ORA_DISK_1: starting piece 1 at 03-JAN-19

channel ORA_DISK_1: finished piece 1 at 03-JAN-19

piece handle=C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\BACKUPSET\2019_01_03\O1_MF_ANNNN_TAG20190

103T213823_G2WDVRL6_.BKP tag=TAG20190103T213823 comment=NONE

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

Finished backup at 03-JAN-19

Current redo log is archived and backed up it as well.

This is finish of complete database backup
[Also read : RMAN introduction, User Managed Backups, RMAN Disk backup]

Read More

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

I was trying to convert my noarchivelog database to archive log, I got below error.
My database was running so I started my database in mount stage using force mount and then tried alter database archivelog, it failed with error ORA-00265

SQL> startup force mount

ORACLE instance started.

Total System Global Area  805306368 bytes

Fixed Size                  1289996 bytes

Variable Size             218104052 bytes

Database Buffers          583008256 bytes

Redo Buffers                2904064 bytes

Database mounted.

SQL>

SQL> alter database archivelog;

alter database archivelog

*

ERROR at line 1:

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

Now I tried to close database cleanly using shutdown immediate and opened in mount mode and fired command to convert to archivelog but unfortunately it failed again.

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area  805306368 bytes

Fixed Size                  1289996 bytes

Variable Size             218104052 bytes

Database Buffers          583008256 bytes

Redo Buffers                2904064 bytes

Database mounted.

SQL> alter database archivelog;

alter database archivelog

*

ERROR at line 1:

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

Now this time I decided to cleanly shutdown the database and open the same with startup command, to open in OPEN mode

SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area  805306368 bytes

Fixed Size                  1289996 bytes

Variable Size             218104052 bytes

Database Buffers          583008256 bytes

Redo Buffers                2904064 bytes

Database mounted.

Database opened.

SQL>

We can see database is open, now I bought database in mount stage as its requirement to convert database archive log from noarchivelog and then I fired command to convert database in archivelog mode and finally opened database using alter database command.

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area  805306368 bytes

Fixed Size                  1289996 bytes

Variable Size             218104052 bytes

Database Buffers          583008256 bytes

Redo Buffers                2904064 bytes

Database mounted.

SQL>

SQL> alter database archivelog;

Database altered.

SQL>

SQL> alter database open;

Database altered.

SQL>

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     35

Next log sequence to archive   36

Current log sequence           36

SQL>

So the Solution to error ORA-00265 is cleanly shutdown the database and open it in open mode and bring it in mount state normally and fire alter database archivelog.

[Also read : Switching between ARCHIVELOG Mode and NOARCHIVELOG.]

Read More

Oracle : RMAN Introduction

In this article we will discuss RMAN - Recovery Manager in detail with its architecture, uses and different scenarios of RMAN backup and recovery.
This is tool provided by Oracle to handle backup and recovery of databases, this is always introduced as Superman for DBA's. 
First we will understand Why RMAN?

• RMAN provides a single backup standard for entire enterprise
• RMAN is supplied with database software and so for using it does not need any additional license
• Backups can be easily automated using RMAN scripts
• RMAN allows you to generate number of reports for backup management as listing of available backups, their status, availability and listing the files which need to be backed up through simple commands such as LIST, REPORT etc.
• RMAN enables us to secure incremental backup i.e. taking backup of only modified blocks rather than entire database every time.
• RMAN can use multiple server session to complete backup based on our uses of parallelism.
• RMAN has inbuilt compression technique to reduce size of backup
• RMAN backups can be used to do cross platform conversion of database
• Only RMAN offers detection and recovery of corrupted blocks without asking for any downtime.
• RMAN is completely supported by Exadata
• RMAN is RAC aware and allows full support to backup and recovery of clustered databases
• RMAN can be used along with third party solutions such as Tivoli and Netbackups as well.

Connecting with RMAN
As discussed RMAN is inbuilt functionality in oracle we can start it with just invoking RMAN client, you just need set environment variable like oracle home, path properly.

$ RMAN

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jun 3 07:05:16 2018

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

RMAN>

Now connect with user having privileges SYSDBA or SYSBACKUP.

RMAN> connect target "sys/oracle"
or 
RMAN> connect target "sys/oracle@db_name"
or
$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Jun 3 07:05:09 2018

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

connected to target database: TECH_DB(DBID=1900906862)

RMAN>

You will see couple of things once you connect using RMAN
-DBID - database identifier- very important in certain recovery scenarios, assigned at the time of database creation
-Output of RMAN session is printed on screen itself or in in-memory view V$RMAN_OUTPUT (only 32768 rows)
-Logfile can be generated using option log='logfile name'

RMAN Architecture
There are key entities for RMAN to start with, some of them are mandatory and other are optional
Mandatory

• Target Database
• Target Controlfile
• RMAN Channels

Optional

• Recovery Catalog
• Oracle Secure Backup
• Media Management Library
• Auxiliary Database
• Cloud Control 12c

Lets discuss this entities one by one

Target Database

• This is database for which RMAN is supposed to perform all the tasks of backup and if needed recovery as well.
• The database to which RMAN would be connected and working for is the target database.

Target Controlfile

• By default RMAN maintains its entire backup history or metadata in controlfile of target database. Duration is controlled by parameter control_file_record_keep_time in parameter file (Default is 7 days).

RMAN Channels

• Communication interface between target database and auxiliary database
• By keeping multiple channels, it gives benefit like parallelism and load balancing
• A server session is allocated in target database for the channel

Auxiliary Database

• Database which is used when doing tasks such as creating a duplicate database , doing taplespace point in time recovery (TSPITR) and database conversion to different platform or creating standby database, RMAN requires connection to this database which is getting executed

Recovery Catalog

• As we know backup information is stored in target controlfile, but it will impact the storage space consumption of disk, to tide over this shortcoming; there is optional metadata storage that can be configured, called Recovery Catalog
• Recovery Catalog is actually schema that we have to create it and then within it configure recovery catalog
• This stores all the information related to backup and recovery.
• This is can be configured on same target database but this is not recommended as any incident with target database can be dangerous to loose this information as well.
• Can be stored information of multiple databases.
• RMAN first stores information in target controlfile and then transfers it to recovery catalog.

RMAN Client

• This is executable file that gets invoked by DBA. Using this we issues the statement which are executed within target database.

12c Cloud Control

• This is latest offering in family of Enterprise Manager (EM), This is optional in RMAN, but it is still worth being used

Media Management Library (MML)

• In order to take backup on tapes instead of disk, RMAN needs to interact with underlying tapes location and for that it requires drivers related to that tape driver, provided its manufacturer
• MML is API that oracle provides to its vendors who write an interface between API and their products

Oracle Secure Backup

• This is add on feature for RMAN
• Oracle Secure Backup contains an in built MML for RMAN and offers a unified management interface for not just the database but also the underlying mount points

Memory requirements for RMAN

• RMAN uses shared pool memory structure by default in order to keep metadata within instance before taking it to target controlfile.
• Specifically talking, large pool (defined by LARGE_POOL_SIZE) is configured for RMAN activities, it will minimize the burden on shared pool as it is already occupied of large number of pools.

RMAN Backup Formats

There are two formats of backup supported in RMAN

1. Backup Set
2. Image Copy

Backup Sets

• Backup set is logical structure, contains physical RMAN-specified file, which is considered as real backup of its source files i.e. datafile.
• We can also say that metadata related to the physical RMAN-specified backup is contained within a backup set
• By default RMAN perform backup in backup set format only
• There are number of files generated during backup, each file is called backup piece
• We can say backup piece is actual physical file, so in order to control its size we can use MAXPIECESIZE option during backup.

Image Copy

• This is similar to normal copy that we take using OS copy command with benefit that it will be logged in controlfile and managed by RMAN
• The difference between image copy and backup sets is that image copy backup will always contain all the blocks within file whether they are used or not.

Types of RMAN Backup

There are mainly two types of RMAN backup

1. Full Backup
2. Incremental Backup
1. Cumulative Incremental Backup
2. Differential Incremental Backup

Full Backup

• This is entire database backup, can be done at mount stage or open stage
• This backup includes datafiles, controlfiles and binary parameter file (spfile)
• RMAN uses only used blocks to be included in this backup
• For huge databases, it consumes large amount of storage, time and resources, so to avoid this we have incremental backups

Incremental Backup

• This type of backup is used to minimize time, resource and storage space, it will be secure backup of blocks those are modified since last backup done
• Its important to remember that a full database backup is not considered as valid level incremental backup

There are two levels used for incremental backups

• Level 0 (base level backup)
• Level 1 (incremental level backup)

Level 0

• Signifies parent backup in an incremental backup strategy and consider it as base of all subsequent incremental backups.
• This includes all blocks of source file in backup file at the time of backup being done

Level 1

• This type of backup is child of level 0 backup and would contain only blocks which are modified since last level 0 backup.

Types of Level 1 Backups

1. Differential 
2. Cumulative 

Differential Incremental Backup

• The main motive behind incremental backup is to save time, and it true for this differential backup as well
• This backup contains all the changed blocks from either level 0 or level 1
• Suppose we are taken level 0 backup on Sunday and we are taking differential incremental backup on subsequent days i.e. Monday to Saturday
• Some cons with differential backup is that, as there will large number of backups, it would take long time to to finish restore and recovery

Cumulative Incremental Backup

• Cumulative backups includes all the modified blocks since the last backup level 0 from the current level 1
• Storage occupied will be larger than differential backup but number of backup copies will be less so will be the restore and recovery time

Incremental backups are fast but we can make them faster using couple of ways

• Block change tracking  (10g and above)
• Multi-Section incremental Backup (12c)

This options will be discussed in later articles.
Different Scenarios and examples will be discussed in subsequent posts

[Also read Oracle : User Managed Backups, RMAN Disk Backup, RMAN single datafile backup and list backups]

Read More

Data Types in MongoDB

MongoDB supports a wide range of data types as values in documents, we will discuss those below

Basic Data Types

Documents in MongoDB can be thought of as "JSON-like" in that they are conceptually similar to objects in JavaScript. JSON expressive capability is limited as there are null, Boolean, numeric, string, array, and object.

Although these data types allow for an impressive amount of expressiveness. There are a couple of additional types which are crucial for most applications.

NULL

Null can be used to represent both a null value and nonexistent field

{"x" : null}

BOOLEAN

This is used to represent values 'true' and 'false'

{"x" : true}

64 Bit floating point number :

all numbers are represented with this data type, thus this will be a floating point number

{"x" : 3.14}

as this will be

{"x" : 3}

String

any string characters can be represented with this data type

{"x" : "header"}

Symbol

This is not supported in the MongoDB shell, it will be converted to a string

object_id

object id is 12 bytes unique ID for documents.

{"x" ObjectId()}

DATE

Dates are stored as milliseconds since the epoch, timezone is not stored.

{"x" : new Date()}

Regular Expression

Documents can contain regular expressions, using java scripts regular expression

{"x" : /header/i}

Code

Documents can also contain JavaScipt code

{"x" : function () {/*......*/}}

Binary Data

Binary data is a string of arbitrary bytes, It can not be manipulated from the shell.

Maximum Value

BSON contains a special type representing the largest possible value. MongoDB does not have this type

Minimum Value

BSON contains a special type representing the smallest possible value. MongoDB does not have this type

Undefined

Undefined can be used in documents as well

{"x" : undefined}

Array

sets or lists  of values can be represented as arrays

{"x" : ["a", "b", "c"]}

Embedded Document

Documents can contain entire documents

{"x" : {"y": "z"}}

If you like it, please share and comment below if any additional information is required.

Read More