User managed Backup & Recovery

User managed backup & recovery consists of a user copying all the files to the backup destination (without using RMAN)

We have to copy tablespaces (datafiles), archived redo log file and network and password files by using OS system utility to copy them and initialization parameter file (spfile or pfile) and control file using SQL*PLUS utility. One could also export logical objects (tables, indexes, PL/SQL units) using Oracle Export Utility.

Backup control file

If the control file is multiplexed (it should be) it is enough to make a backup of only one file.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO cf1.bak

Backup of spfile (or pfile)

Checking whether we are using PFILE or SPFILE

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';

Backup pfile from spfile

SQL> CREATE PFILE='/backup/pfileORCL.bak' from SPFILE='/oradata/spfile.ora';

Backup spfile from pfile

SQL> CREATE SPFILE='/backup/spfileORCL.bak' from PFILE='/oradata/pfile.ora';

Offline COLD backup

when backing up datafiles it makes a difference in how we do it, whether the DB is running or is shut down.
Cold backup will mean that the DB is shutdown, which we can do with the following command

SQL> shutdown;

and then we copy like any other file in the operating system all the datafiles, archived redo log files.

QUESTION: how to backup network and password files? Just regular copying also?

Online HOT backup

we have to freeze every tablespace for the purpose of copying and unfreeze it after we have done the copying.

SQL> aler tablespace users begin backup;

now we copy the datafile using OCOPY command for copying. More about it on this LINK

d:\app\Administrator\oradata\orcl>ocopy users01.dbf e:\users01bk.dbf

SQL> alter tablespace users end backup;

We have to repeat this procedure for each datafile that we want to backup, so it make sense to automate the process with some scripts.

QUESTION: How do we backup the archived redo log files? I know that the ARCHIVELOGMODE has to be turned on, but how is it done?

Backup files verification

E:\>dbv file = users01bk.dbf feedback = 100

Feedback 100 just makes a dot after each 100 files o that we know that the system is working.

User-managed Recovery

If You Lose…

Then…

Data files in the SYSTEM tablespace or data files with active undo segments

The database automatically shuts down. If the hardware problem is temporary, then fix it and restart the database. Usually, crash recovery recovers lost transactions. If the hardware problem is permanent, then restore the data files from backups and recover the database as described in “Performing Closed Database Recovery”. Database will be started in mount mode

SQL> startup mount;

and then we have to restore all the datafiles and later issue the command to restore them all. If there are too many of them, we can issue the command to restore the entire database

SQL> recover database;

Data files not in the SYSTEM tablespace or data files that do not contain active rollback or undo segments

Affected data files are taken offline, but the database stays open. If the unaffected portions of the database must remain available, then do not shut down the database. Take tablespaces containing problem data files offline using the temporary option,

SQL> alter database datafile 'd:\app\administrator\oradata\orcl\users01.dbf' offline;

then recover them as described in “Performing Open Database Recovery”.

SQL> recover datafile 'd:\app\administrator\oradata\orcl\users01.dbf';


and at the end we have to bring the datafile back online by issuing the command

SQL> alter database datafile 'd:\app\administrator\oradata\orcl\users01.dbf' online;

All copies of the current control file

You must restore a backup control file and then open the database with the RESETLOGS option.

If you do not have a backup, then you can attempt to re-create the control file. If possible, use the script included in the ALTER DATABASE BACKUP CONTROLFILE TO TRACE output. Additional work may be required to match the control file structure with the current database structure.

One copy of a multiplexed control file

Copy an intact multiplexed control file into the location of the damaged or missing control file and open the database. If you cannot copy the control file to its original location, then edit the initialization parameter file to reflect a new location or remove the damaged control file.

SQL> alter system set control_files = 'e:\um2\control01.ctl' scope = spfile;

Then, open the database.

One or more archived logs required for media recovery

You must restore backups of these archived logs for recovery to proceed. You can restore either to the default or nondefault location. If you do not have backups, then you must perform incomplete recovery up to an SCN before the first missing redo log and open RESETLOGS.

The server parameter file (SPFILE)

If you have a backup of the server parameter file, then restore it. Alternatively, if you have a backup of the client-side initialization parameter file, then you can restore a backup of this file, start the instance, and then re-create the server parameter file.

COMPLETE User-Managed Database Recovery

1. mount DB

2. restore DF

3. recover file(s)

4. open the DB

INCOMPLETE User-Managed Database Recovery

1. mount DB

2. restore DF, control file.

3. recover DB UNTIL TIME (datestamp in format ‘2012-03-16:14:30:00’) OR change (SCN) or CANCEL

4. open the DB with RESETLOGS

SQL> alter database open resetlogs;