Oracle Data Guard Protection Modes

There are three types of data guard protection modes in oracle

1. Maximum Protection
2. Maximum Availability
3. Maximum Performance (default)

Before proceeding with next explaining these three modes, you can visit Standby database introduction to understand the purpose of standby database

All of these protection modes provides high data protection, but they differ in terms of availability and performance of primary database

Maximum Protection Mode

• Maximum protection mode guarantees that no data loss will occur if primary database fails by any means
• To provide this level of protection, redo data needs to recover a transaction must be written to both the online redo log and to at least one synchronized standby database before transaction commits.
• To ensure that data loss can not occur, the primary database shutdown rather than continue processing transactions if it can not write to at lease one sync standby database
• Recommendation
• As this protection mode prioritizes data protection over primary database availability, oracle recommends to have minimum two standby database

Maximum availability Mode

• This protection mode provides the highest level of data protection that is possible without affecting availability of the primary database.
• Under normal operations, transactions to not commitment until all redo data needed to recover those transactions has been written to online redo log and based on user configuration one of the following is true
• Redo has been received at standby database and acknowledge sent to primary
• redo has been received and written to standby redo log and acknowledgement sent to primary
• In case primary does not receive acknowledgement from at least one synchronized standby database then it operates as if it were maximum performance mode to preserve primary database availability until it is again able to write it to redo stream o synchronized standby database
• To fully benefit from complete oracle data guard validation at the standby database,be sure to operate in real time apply mode so that redo changes are applied to standby database as fast as they are received.

Maximum Performance Mode

• This protection mode provides highest level of data protection that is possible without affecting the performance of primary database.
• This achieved by allowing transactions to commit as soon as all redo data generated by transactions has been written to the online log
• Redo data is also written to one or mode standby databases but this is due to asynchronously with respect to transactions commitment, so primary database performance is unaffected by time required to transmit redo data and receive acknowledgement from a standby database.
• This protection mode offers slightly less data protection than maximum availability  and has minimal impact on primary performance
• This is default protection mode in oracle.

Setting protection mode of primary database

Protection mode can be modified at any time as long as configuration meets requirements of mode.
Below are the steps for the same

• Decide the protection among the above mentioned three modes
• Verify the protection mode requirements with at lease one configuration standby database. Redo transport required for protection modes are

Availability	AFFIRM/NOAFFIRM	SYNC	DB_UNIQUE_NAME
Performance	NOAFFIRM	ASYNC	DB_UNIQUE_NAME
Protection	AFFIRM	SYNC	DB_UNIQUE_NAME

• Verify if DB_UNIQUE_NAME is set as this is mandatory parameter for data guard configuration
• Verify LOG_ARCHIVE_CONFIG parameter is set, its value includes a DG_CONFIG which includes DB_UNIQUE_NAME of primary and standby database for example 

       LOG_ARCHIVE_LOG='DG_CONFIG=('PRIM','STDBY')'

• Set data protection modes using below command

alter database set standby database to maximum {availability|performance|protection};

• Verify on primary using below command

select protection_mode from v$database;

Read More

Standby database Introduction

Standby database is an important aspect in oracle, its useful in disaster recovery scenarios and to carry out testing on production data without hurting production database performance. We will discuss more about standby database.

• Standby database is database replica created from backup of primary database
• By applying archived redo logs from primary database to standby database, once can keep two databases in synchronized
• Purpose of having standby database 
• Disaster protection
• Data corruption protection
• Supplemental reporting or testing

Configuration Options

We can setup a standby database in several different ways depending on method for

• Transferring archived redo logs
• Applying archived redo logs

Example: 

Managed standby environment allows primary database to automatically archives redo logs to standby database site so long as standby instance is started

Non-managed standby environment makes it compulsory to transfer archived redo logs manually

Managed standby mode - it automatically applies logs received from primary database

manual recovery mode - apply logs manually

Pros and Cons of standby database

Pros

• A standby database is powerful configuration for both disaster recovery and supplementary reporting and testing 
• We can maintain several standby database in geographically diverse locations
• Maintain primary and standby database on same machine on different drive/file system
• We can make standby database the new primary database with minimal loss of time and data
• Standby database provides protection against
• erroneous batch jobs
• user errors
• applying corruption on primary by not applying corrupt data on standby site

Cons

• It requires additional machine if you want to maximize disaster protection by keeping standby database on separate host
• Implementation and maintenance of Net8
• Additional system resource and cost
• Extra efforts for administration of standby site. 

Types of standby databases

In oracle standby database can be one of these types

1. Physical standby database
2. Logical standby database
3. Snapshot standby database

Physical standby database

• Physical standby database is an exact block for block copy of primary database
• This physical standby database uses process called redo apply i.e. redo received from primary database gets applied to standby database using recovery process
• This can be opened for read only access to execute number of reporting and testing queries
• With license of active data guard redo can be applied during database is open mode
• Benefits:
• Disaster recovery and high availability
• Data protection
• Reduction in primary database workload performance

Logical standby database

• Initially logical standby database is identical copy of primary database but later can be altered to have different structure
• This type of standby database is updated or brought up in sync by executing SQL statements.
• Oracle data guard automatically applies data from archived redo logs on standby database by transforming data into SQL statements and then executing SQL statements on logical standby database
• Database must remain open as it uses SQL statements to update logical standby database
• Although this database remain in read/write mode its target table will be only available for read only operations
• Benefits: 
• Ideal for High Availability
• Minimizes downtime on software update
• support for reporting and decision support requirements

Snapshot standby database

• Snapshot standby database is type of updatable standby database has full data protection for primary database
• A snapshot standby database receives and archives but does not apply redo data from its primary database
• Redo data received from primary database is applied when snapshot standby database is converted back into physical standby database.
• The data of primary database is fully protected as snapshot standby database can be converted to physical standby database any time and redo is then applied
• Benefits
• It provides exact replica of production database for development and testing purpose while data protection at all time
• It can be easily refreshed to contain current production data by converting to physical standby database and re synchronizing

In next article we are going to discuss about different protection modes in oracle for data guard configuration.

Read More

Configure and Use of Flash Recovery Area

Flash recovery area i.e. FRA available since oracle 10g edition and is identified as unified storage location for all recovery related files in oracle database. We will discuss more the about flash recovery area

• The flash recovery area cane reside in single file system or as an ASM diskgroup
• The following permanent items are stores in flash recovery area

Control file :

• Oracle stores one copy of the control file in flash recovery area during installation

Online redo logfile

• You can store one mirrored copy from each redo log file group in flash recovery area

 Following transient items are stored in flash recovery area

• Archived redo log files
• Flashback logs
• Controlfile automatic backup
• Datafile copies (during RMAN backup/cloning as ‘backup as copy’ command)
• RMAN backupsets
• RMAN files

There are three initialization parameters controls location of new control file, online redo logs and datafile are DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST

• The DB_CREATE_FILE_DEST specifies the default location for oracle managed datafiles (OMF) if we do not explicitly specify destination
• The DB_CREATE_ONLINE_LOG_DEST specifies upto five location for online redo logs files, if this parameter is not specified the oracle uses DB_CREATE_FILE_DEST as destination for online redo log files
• The DB_RECOVERY_FILE_DEST specify the default location for flash recovery are
• Recommended size of flash recovery are is sum of database size , size of incremental backup and size of all archived logs that have not been moved to tape or any other location.

Setting Flash Recovery Area

Setting flash recovery area for use requires below two parameters to be set

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

Make sure you enough space under server file system or ASM diskgroup

Setting parameters

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string  

db_recovery_file_dest_size           big integer

set recovery location
alter system set DB_RECOVERY_FILE_DEST = '+RECO01' scope = both;

set the size of FRA
alter system set DB_RECOVERY_FILE_DEST_SIZE = 4G scope = both;

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      +RECO01

db_recovery_file_dest_size           big integer  4G

Usage of Flash Recovery Area

The database alert log will indicate, if database FRA limit is full though you have enough space on file system or diskgroup.

Usage of FRA can be determined using below queries

Check location and size using v$recovery_file_dest

SQL>  select * from  v$recovery_file_dest;

NAME  SPACE_LIMIT  SPACE_U SPACE_REC NUMBER_OF_FILES       CON_ID
----------------- -------- --------- --------------- ---------------+FLASH 107374182400  2097152       0              2              0

Check usage for areas and purpose

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE    PERCENT_S_USED PERCENT_S_RECLAIMABLE NUMBER  CON_ID
----------- -------------- ------------------- --------- ---------CONTROL FILE            0               0             0           0

REDO LOG                0               0             0           0
ARCHIVED LOG            0               0             2           0
BACKUP PIECE            0               0             0           0
IMAGE COPY              0               0             0           0
FLASHBACK LOG           0               0             0           0
FOREIGN ARCHIVED LOG    0               0             0           0
AUXILIARY DATAFILE COPY 0               0             0           0

Size of flash recovery area can be modified using alter system command. Database outage will not be required to carry out this change

alter system set db_recovery_dest_size = 6G scope=both;

Read More

Oracle : Restore points

Restore point plays important role in restore and recovery scenario as it allows to store state of the database to certain point, Lets discuss oracle restore points in details with example

• Restore point is said to be "point" in database which is associated with timestamp or an SCN of the database
• Restore point is used to flash back a table or the whole database to certain point specified as per restore point
• Various RMAN operations such as backup and archival backup also uses these restore point
• Restore point can be name up to 128 characters
• There can be 2048 restore points in database provided database has enough FRA
• Privileges required to create restore points

Normal restore point	Guaranteed restore point
SELECT ANY DICTIONARY	SYSDBA
FLASHBACK ANY TABLE	SYSBACKUP
SYSDBA	SYSDG
SYSBACKUP
SYSDG

Privileges required to view or use of restore point

      SELECT ANY DICTIONARY

      FLASHBACK ANY TABLE

      SYSDBA

      SYSBACKUP or

      SYSDG or

      SELECT_CATALOG_ROLE

Requirements:

• Database must have fast recovery area (FRA) set for guaranteed restore point
• Database is in Archive log mode (guaranteed restore point)

Types of Restore points

There are two types of restore points

1. Guaranteed restore point
2. Normal restore point

Guaranteed restore point

• A guaranteed restore point enables you to flash the database back to the restore point regardless of DB_FLASHBACK_RETENTION_TARGET initialization parameter setting
• Guaranteed restore point must be dropped explicitly by user using DROP RESTORE POINT command
• Guaranteed restore point never ages out

Normal Restore Point

• A normal restore point enables you to flash the database back to a restore point within time determined by DB_FLASHBACK_RETENTION_TARGET initialization parameter setting
• Normal restore point can be dropped explicitly
• The control files stores name of restore point and the SCN

Creating restore point

In below example, we are going to create guaranteed restore point

Check for flashback on in the database using below query

SQL>  select name,FLASHBACK_ON,open_mode from v$database;

NAME      FLASHBACK_ON     OPEN_MODE

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

TECHNO_DB  NO              READ WRITE

Turn the flashback on using below statement

SQL> alter database flashback on;

Database altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

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

YES

Check for Fast Recovery Area size and location

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      +FLASH

db_recovery_file_dest_size           big integer 100G

SQL>

Check for any restore point using v$restore_point view

set lines 200

col TIME for a40

col NAME for a40

select inst_id,NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

no rows selected

Create actual restore point using CREATE RESTORE POINT command

SQL> create restore point RESTORE_POINT_20FEB2019 guarantee flashback database;

Restore point created.

Verify if restore point is created

set lines 200

col TIME for a40

col NAME for a40

select NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

    NAME                 TIME           GUA STORAGE_SIZE/1024/1024/1024

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

RESTORE_POINT_20FEB2019 20-FEB-19 06.20.40.000000000 AM    YES        2

DROP Restore Point

Drop restore point can be performed using DROP RESTORE POINT command and we need turn off flashback_on option

SQL> DROP RESTORE POINT RESTORE_POINT_20FEB2019;

Restore point dropped.

SQL> alter database flashback on;

Database altered.

Read More

MongoDB : Basic Administration

After going through CRUD operations on MongoDB database, We will take look at some more basic MongoDB Administration

Getting MongoDB database-related information

If you want to know databases available in the system, execute "show dbs" command, it will list out all the databases

> show dbs;

admin      0.000GB

config     0.000GB

local      0.016GB

techno_db  0.001GB

> 

If you want to know in which database, you are currently execute db 

> db

techno_db

> 

if you are curious about what are the collections available under the database, just execute show collections or show tables

>show collections;

tech_large

techno_col

> 

or

> show tables;

tech_large

techno_col

> 

The command db.stats() will give you all the stats about the database 

> db.stats();

{

        "db" : "techno_db",

        "collections" : 2,

        "views" : 0,

        "objects" : 17000,

        "avgObjSize" : 37.64705882352941,

        "dataSize" : 640000,

        "storageSize" : 335872,

        "numExtents" : 0,

        "indexes" : 2,

        "indexSize" : 208896,

        "fsUsedSize" : 587198464,

        "fsTotalSize" : 10693378048,

        "ok" : 1,

        "operationTime" : Timestamp(1549877990, 1),

        "$clusterTime" : {

                "clusterTime" : Timestamp(1549877990, 1),

                "signature" : {

                        "hash" : BinData(0,"OFOjmV2iLyqywB6fMf4bdiUZ5CU="),

                        "keyId" : NumberLong("6636938165653340162")

                }

        }

}

> 

If you want to know stats about only particular collections then that is also possible, execute db..stats() command

  

> db.techno_col.stats();

{

        "ns" : "techno_db.techno_col",

        "size" : 70000,

        "count" : 2000,

        "avgObjSize" : 35,

        "storageSize" : 86016,

        "capped" : false,

        "wiredTiger" : {

                "metadata" : {

                        "formatVersion" : 1

                },

                "creationString" :

…

…

…

},

        "nindexes" : 1,

        "totalIndexSize" : 61440,

        "indexSizes" : {

                "_id_" : 61440

        },

        "ok" : 1,

        "operationTime" : Timestamp(1549878100, 1),

        "$clusterTime" : {

                "clusterTime" : Timestamp(1549878100, 1),

                "signature" : {

                        "hash" : BinData(0,"eBwJSS4K5vUHyZJKT54h2pduSEc="),

                        "keyId" : NumberLong("6636938165653340162")

                }

        }

}

> 

Getting Help

MongoDB provides by default build command called db.help(), it lists out commonly used methods for operating database

we can get the type command on any collections using db..help()

> db.help();

DB methods:

        db.adminCommand(nameOrDocument) - switches to 'admin' db, and runs command [just calls db.runCommand(...)]

        db.aggregate([pipeline], {options}) - performs a collectionless aggregation on this database; returns a cursor

        db.auth(username, password)

        db.cloneDatabase(fromhost) - deprecated

        db.commandHelp(name) returns the help for the command

        db.copyDatabase(fromdb, todb, fromhost) - deprecated

        db.createCollection(name, {size: ..., capped: ..., max: ...})

        db.createView(name, viewOn, [{$operator: {...}}, ...], {viewOptions})

        db.createUser(userDocument)

        db.currentOp() displays currently executing operations in the db

...

...

...

        db.shutdownServer()

        db.stats()

        db.version() current version of the server

> 

> db.techno_col.help();

DBCollection help

       db.techno_col.find().help() - show DBCursor help

       db.techno_col.bulkWrite( operations, )- ulk execute write operations, optional parameters are: w, wtimeout, j

        db.techno_col.count( query = {}, ) - count the number of documents that matches the query, optional parameters are: limit, skip, hint, maxTimeMS

...

...

...

        db.techno_col.unsetWriteConcern( ) - unsets the write concern for writes to the collection

        db.techno_col.latencyStats() - display operation latency histograms for this collection

>  

There is one method to increase the speed of your administration by simply using the tab key on the keyboard

Just type function or procedure name and press tab, it will all available functions under it

> db.techno_col.get

db.techno_col.getCollection(        db.techno_col.getIndexSpecs(    db.techno_col.getName(             db.techno_col.getShardVersion(

db.techno_col.getDB(                db.techno_col.getIndexes(        db.techno_col.getPlanCache(         db.techno_col.getSlaveOk(

db.techno_col.getFullName(          db.techno_col.getIndices(        db.techno_col.getQueryOptions(  db.techno_col.getSplitKeysForChunks(

db.techno_col.getIndexKeys(           db.techno_col.getMongo(        db.techno_col.getShardDistribution(   db.techno_col.getWriteConcern(

To get the definition of any method can be found using not providing brackets at the time of invocation of the method

for example

> db.techno_col.getIndexes

function (filter) {

    var res = this._getIndexesCommand(filter);

    if (res) {

        return res;

    }

    return this._getIndexesSystemIndexes(filter);

}

> 

Read More