BACKUP & RECOVERY

How to enable flash recovery area in oracle database

The flash recovery area(FRA) is an Oracle-managed destination( either FILE SYSTEM or ASM ) for centralized backup and recovery files. It simplifies the backup management. The following recovery-related files are stored in the flash recovery area: — Current control file — Online redo logs — Archived redo logs — Flashback logs — Control file auto […]

How to restore lost spfile in oracle

What if you lost both spfile and pfile of the instance when it is up and running. Suppose you don’t have any type of rman or autobackup also. How can you restore that. Let’s see the below example. We will recreate the scenario by deleting the spfile: SQL> show parameter pfile; NAME TYPE VALUE ———————————— […]

Rman backup of archives between two sequences

Below is the script for taking backup of archive log between sequence number 1000 to 1050 For standalone database: RMAN> backup format ‘/archive/%d_%s_%p_%c_%t.arc.bkp’ archivelog from sequence 1000 until sequence 1050; For RAC database: For RAC database, we need to mention the thread number( like 1 for node 1 and 2 for node 2) RMAN> backup […]

ORA-19809: limit exceeded for recovery files

PROBLEM: While taking rman backup, it failed with below error ( ORA-19809 & ORA-19804) RMAN> backup archivelog all; Starting backup at 26-MAY-17 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=1942 RECID=1750 STAMP=944758808 input archived log thread=1 sequence=1943 […]

Database cloning using hot backup

In this article We will explain the steps for cloning a database using hot backup method without using RMAN utility. During hot backup, the source database will be up and running. NOTE – SOURCE DATABASE SHOULD BE IN ARCHIVELOG MODE FOR HOT BACKUP Note- Both the source and target db server should be on same […]

Database cloning using cold backup

Cold database backup means while taking backup or doing cloning, we need to shutdown the source database. This method is usually used for test database when database is in noarchivelog mode. Note- Both the source and target db server should be on same platform and the target db version will be that of the source […]

RMAN active cloning from RAC TO RAC

Below are the steps for doing RMAN from RAC to RAC. Steps will be same from NON RAC TO RAC . For standalone database  – Rman active cloning for standalone 12c database SOURCE DB RAC NODE- prodb1-host,prodb2-host DBNAME – PRODB : Oracle 12.1.0.2 INSTANCE NAMES – PRODB1,PRODB2 Oracle 12.1.0.2 TARGET DB RAC NODE – clonedb1-host,clonedb2-host DBNAME […]

ORA-01145: offline immediate disallowed unless media recovery enabled

PROBLEM: While making a datafile offline, got below error.   SQL> ALTER database datafile ‘/export/home/oracle/user02.dbf’ offline; ALTER database datafile ‘/export/home/oracle/user02.dbf’ offline * ERROR at line 1: ORA-01145: offline immediate disallowed unless media recovery enabled   SOLUTION: To make a datafile offline, archive mode need to be enabled.   SQL> archive log list Database log mode […]

ORA-00245: control file backup failed; target is likely on a local file system

PROBLEM: While taking RMAN backup in oracle RAC database, getting below error. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of resync command on default channel at 12/10/2016 22:16:52 ORA-00245: control file backup failed; target is likely on a local file system Recovery Manager complete. No lines match the search […]

How to recover a dropped user using flashback oracle

                Using flashback method, we can restore an accidentally dropped users in oracle. Basically, we will flashback the database to past, when the user was available, Then take an export dump of the schema, and restore the database to same current state. Once db is up, we can import […]