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

User Managed Backups in Oracle

Definition :Backup is real and consistent copy of data from database that could be used to reconstruct the data after and incident.

There are two types of backup

1. Physical Backup

2. Logical Backup

Physical Backup

Copy of all the physical datafiles that are required to perform the restore and recovery of database,

This type of backup includes copy of below files

• Datafiles
• Control files
• Parameter files
• Archived log files

Different Options to take physical backup are 

• User managed techniques
• RMAN (Recovery Manager)

Logical Backup

Oracle uses oracle datapump to allow us to generate a logical backup that can be used to migrate data even do partial or full recovery of database.Utilities available are 

• exp/expdp
• imp/impdp

Difference between restore and recovery

Restore: Act that involves the restoration pf all files that will be required to recover your database to a consistent state. for example copying all backup files from backup location such as TAPE or DISK

Recover: It is process to apply all transaction recorder in archivelog, rolling forward your database to point in time. 

User Managed Backups

COLD BACKUP

This is only possible way for DBA to make consistent backup of database independent of mode of database (archivelog or noarchivelog). Below are steps explained

1. If database is running, bring it down completely in a consistent mode (using shutdown ( NORMAL/ IMMEDIATE/ TRANSACTIONAL only). This will make sure all database files header is updated to same SCN
2. Backup of all datafiles ,control files, parameter files using OS copy command
3. Start the database
4. Archive all unarchived redo logs using below command and copy those to backup location

              alter system archive log current

OFFLINE BACKUP

Assume you want to take offline backup of one or more tablespace, offline backup method will help you to achieve the same, note that SYSTEM tablespace and UNDO tablespace with active undo cannot be taken offline.Steps are mentioned below

assume tablespace is TECHON_TBS

1. Identify all datafiles associated with tablespace using dba_data_files view

select tablespace_name,file_name from dba-data_files from dba_data_files where tablespace_name='TECHON_TBS';

2. Take tablepspace offline using normal priority (do not use temporary and immediate, it will require recovery at the time of bringing tablespace online)

alter tablespace TECHON_TBS offline normal;

3. Backup all datafiles related to tablespace using OS copy command

4. Bring tablespace online

alter tablespace TECHON_TBS online;

5.archive all unarchived redo logs and copy archived log to backup location

alter system archive log current;

HOT BACKUPS

This has been introduced back in version of 6 of oracle, but initially it was bound to  tablespace only , from version 10 they included whole database as well. This allows us to take hot backup of database or tablespace without need to shutdown the database.

When taking hot backup, oracle stops recording checkpoints to all the associated datafiles.

Below are two things happens internally while database is put in BEGIN BACKUP mode

• A hot backup flag in datafile header is set
• A checkpoint occur, flashing of all dirty blocks from memory to disk, synchronizing all the datafile headers to same SCN & freezing the headers for consistency, protection and recoverability.

Hot backups can be made to the whole database, tablespace or even at container level, this requires recovery process after backup is restored so always take backup of all required archived log files.

Hot Backup of Whole Database

For taking this type of backup, we need to put our database in backup mode using 'alter database begin backup;'

This is most common type of user managed backup being used by DBA across the world. 

Below are the steps

1. Place the database in backup mode

alter database begin backup;

2. Backup all datafiles and parameter files using OS copy command

3. Pull database out of backup mode

alter database end backup;

4. Archive all unarchived logs and copy required as backup

alter system archivelog current;

5. create copy of control file as trace

alter database backup controlfile to '/control_file.trc;

Hot Backup of Tablespaces
Now we will learn taking hot backup of one or more tablespaces i.e. backing up your tablespace while database is running. while we put tablespace in backup mode, the database copies all the changed blocks into redo stream to make all the data consistent until. Below are step explained.

1. Identify all datafiles associated with tablespace using dba_data_files view

select tablespace_name,file_name from dba-data_files from dba_data_files where tablespace_name='TECHON_TBS';

2. place the tablespace in backup mode using begin backup command

alter tablespace TECHON_TBS begin backup;

3. Backup all datafiles related to tablespace using OS copy command

4. pull tablespace out of backup mode

alter tablespace TECHON_TBS end backup;

5.archive all unarchived redo logs and copy archived log to backup location

alter system archive log current;

Hot Backup of Container Database

As introduced in Oracle 12c, you can perform user managed backup of whole container database or the only root or and individual PDB.

Whole Container Database:

Below are the steps mentioned 

1. login to database using user having sysdba or sysbackup privileges.

sqlplus /nolog

connect system@container1

2.Place your database in backup mode

alter database begin backup;

3. Identify all the databases related to container database using below command

select file_name from dba_data_files;

4. Copy all the database using OS copy command

5. Pull database out of backup mode

alter database end backup;

6. Archive all unarchived logs and copy required as backup

alter system archivelog current;

7. create copy of control file as trace

alter database backup controlfile to '/control_file.trc;

ROOT only or individual PDB

Below are the simple steps to complete hot backup of root 

container or individual pluggable database

1. connect to root or individual pluggable database with user having sysdba or sysbackup privileges.

sqlplus /nolog

connect system

alter session set container= tech_pdb;

2. Identify all the datafiles part of PDB using dba_data_files view

select file_name from dba_data_files;

3. Place pluggable database in backup mode

alter pluggable database tech_pdb begin backup;

4. Copy all the datafiles using OS copy command.

5. Pull PDB out of begin backup mode

alter pluggable database tech_pbd end backup;

6. Archive all unarchived logs and copy required as backup

alter system archivelog current;

7. create copy of control file as trace

alter database backup controlfile to '/control_file.trc;

Control File Backup

There are two ways to take backup of control 

1. Binary backup
2. Text file Backup

Having valid backup of control file of a database is crucial for successful recovery of database.

Binary backup

One can take binary copy of control file via SQL statement. This copy contains additional information such as the archived redo log history , offline range for read-only and offline tablespaces, tempfile entries and RMAN backup sets and copies of data

command to take binary backup

alter database backup controlfile to '';

Text file backup

This type of backup contains a create controlfile statement based on current control file in use. This type of backup can be taken while database is in mount or open mode. This is generated as trace file and can be easily modified.

Below are few statements used to created text based backup of control file

1. alter database backup controlfile to trace;

2. alter database backup controlfile to trace resetlogs;

3. alter database backup controlfile to trace noresetlogs;

4. alter database backup controlfile to trace as '';

5. alter database backup controlfile to trace as '' reuse;

6. alter database backup controlfile to trace as '' resetlogs;

7. alter database backup controlfile to trace as '' noresetlogs;

Please comment and share if you feel this is helpful.

Read More

List Databases, Tables, schemas and other basic commands in PostgreSQL

In previous articles we have learned about basics of PostgreSQL, architecture and installation of PostgreSQL on windows
(Read here Introduction to PostgreSQL , Installation of PostgreSQL )
Now we will learn basic commands to administrates the PostgreSQL databases

Login to PostgreSQL
we can login to postgres database using psql with few options like 
database name and postgres user, below we logged in to tech_pg database using user postgreu

$ psql -d tech_pg -U postgreu

psql.bin (9.6.2.7)

Type "help" for help.

tech_pg=# 

Listing the database available
A single PostgreSQL server instance can have multiple databases, each and every database is stored as separate file under its own directory, to list database we can use \l or we can use pg_database table

tech_pg=# \l

                                             List of databases

     Name     |   Owner    | Encoding |   Collate   |    Ctype    | ICU |        Access privileges

--------------+------------+----------+-------------+-------------+-----+----------------------------------

 tech_pg      | postgreu | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |

 postgres     | postgreu | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |

template0     | postgreu | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/postgreu                   +

              |            |          |             |             |     | postgreu=CTc/postgreu

 template1    | postgreu | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/ postgreu                   +

              |            |          |             |             |     | postgreu=CTc/postgreu

(4 rows)

tech_pg=#

tech_pg=# select datname from pg_database;

   datname

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

 postgres

 tech_pg

 template1

 template0

 (4 rows)

tech_pg=#

Connect to database (Switch Database)

By default PostgreSQL server instance has 3 databases i.e. postgres, template1 and template0

one can connect to database from postgres command line tool and switch to other database using \connect or \c

tech_pg=# \c postgres

You are now connected to database "postgres" as user "postgreu".

postgres=#

postgres=# \connect tech_pg

You are now connected to database "tech_pg" as user "postgreu".

tech_pg=#

Checking Current Database

We can check the name of the database in which we are currently logged in using different methods, i.e. using \c, \conninfo, select * from current_catalog and current_database() function. it has been illustrated below 

tech_pg-# \c tech_pg

You are now connected to database "tech_pg" as user "postgreu".

tech_pg-#

tech_pg=# \conninfo

You are connected to database "tech_pg" as user "postgreu" via socket in "/tmp" at port "5444".

tech_pg=#

tech_pg=# \c

You are now connected to database "tech_pg" as user "postgreu".

tech_pg=#

tech_pg=# select * from current_catalog;

 current_database

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

 tech_pg

(1 row)

tech_pg=# select current_database() ;

 current_database

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

 tech_pg

(1 row)

tech_pg=#

Listing the Tables
one can simply list the tables created under specific database using \dt command, in Below example we can see there is no table present in database, if any present then it will list out with details.

tech_pg=# select current_database() ;

 current_database

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

 tech_pg

(1 row)

tech_pg=# \dt

No relations found.

tech_pg=#

Get list of all Schema
using command \dn, one can simply list out all the schema in postgres

tech_pg=# \dn

         List of schemas

        Name        |   Owner

--------------------+------------

 postgreu           | postgreu

 dbms_job_procedure | postgreu

 pgagent            | postgreu

 public             | postgreu

(4 rows)

One can use information_schema.schemata view to list out all the schemas in current database 

tech_pg=# select catalog_name,schema_name,schema_owner from information_schema.schemata;

 catalog_name| schema_name    | schema_owner

-------------+--------------------+--------------

 tech_pg | pg_toast           | postgreu

 tech_pg | pg_temp_1          | postgreu

 tech_pg | pg_toast_temp_1    | postgreu

 tech_pg | pg_catalog         | postgreu

 tech_pg | public             | postgreu

 tech_pg | information_schema | postgreu

 tech_pg | sys                | postgreu

 tech_pg | dbo                | postgreu

(8 rows)

Comment for feedback or in case need additional information, share the post if you like it.

Read More