Configure and Use of Flash Recovery Area

      ,      No comments   

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;

Oracle : Restore points

      ,      No comments   

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.

MongoDB : Basic Administration

           No comments   

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);
}
> 

Categories

Popular Posts