Archive for the ‘Backup and Recovery’ Category

It is backup and recovery

RMAN Part-2

Posted by Sanjay on December 3, 2011

========
Rman can back up the following types of files:
=========

• Archived redo log files
• Backup pieces
• Control files
• Datafiles Spfile
• spfile.

Note: It is ABCDS

RMAN will back up full backups, incremental backups, control file autobackups, and archive redo
log files. Keep in mind that flashback logs, online redo log files, and the current control file are not backed up.

Note: Always enable the autobackup for spfile (only backed up if db starting using spfile) and control file feature.
=====

RMAN> configure controlfile autobackup on;

RMAN> configure spfile autobackup on;

========

How to configure RMAN to write to specific locations on disk.

RMAN> configure channel 1 device type disk format ‘/ora01/rman/rman1_%U.bk’;

Note: CONFIGURE command must be executed before you run the BACKUP command.

=========
Backup Database.
=========

The term “RMAN level 0 incremental backup” doesn’t exactly describe itself very well, either. A level
0 incremental backup is backing up the exact same blocks as a full backup. In other words, the following
two commands back up the exact same blocks in a database:

RMAN> backup as backupset full database;

RMAN> backup as backupset incremental level=0 database;

RMAN> backup database not backed up since time=’sysdate-1′;

RMAN> backup database skip readonly skip offline skip inaccessible; backup database skip offline;

RMAN> backup database skip readonly;

RMAN> backup database skip inaccessible;

————-

Backup Sets versus Image Copies

The default backup mode of RMAN instructs it to only back up blocks that have been used in a datafile;
these are known as backup sets. RMAN can also make byte-for-byte copies of the datafiles; these are
known as image copies. Creating a backup set is the default type of backup that RMAN creates. The next
command creates a backup set backup of the database:

RMAN> backup database; backup as backupset database;

You can instruct RMAN to create image copies by using the AS COPY command. The following
creates image copies of every datafile in the database:

RMAN> backup as copy database; backup incremental level=0 database plus archivelog;

=======
Backup tablespace.
======

RMAN> backup tablespace system, sysaux;

RMAN> configure exclude for tablespace users; configure exclude for tablespace users clear; <== to clear the excluded tablespace.

=======
Backup datafile.
======

RMAN> backup datafile ‘/app/sanjay/11R2/system01.dbf’;

RMAN> backup datafile 1,5;

RMAN> backup as copy datafile 3;

RMAN> backup incremental level 1 datafile 4;

RMAN> backup database not backed up; <== To backup the datafile not backed up.

=======
Backup controlfile.
======

RMAN> backup current controlfile;

RMAN> configure controlfile autobackup format for device type disk to ‘/app/sanjay/rman/rman_ctl_%F.bk’;

=======
Backup spfile.
======

RMAN> backup spfile;

Note: RMAN can only back up the spfile if the instance was started using a spfile.

=========
Backup Archive logs.
=========

RMAN> backup archivelog all;

RMAN> backup archivelog all delete input;

RMAN> backup archivelog sequence 300;

RMAN> backup archivelog sequence between 300 and 400 thread 1;

RMAN> backup archivelog from time “sysdate-7″ until time “sysdate-1″;

Posted in Backup and Recovery | Leave a Comment »

RMAN Part 1

Posted by Sanjay on January 10, 2011

HOW TO ENABLE ARCHIVE LOG.
========

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 41
Current log sequence 43

=========
Create pfile from spfile ;
Note: In case any issue you can start the database using pfile.
Example startup pfile=’full_path’
=========

SQL> create pfile from spfile ;

File created.

=======

SQL> alter system set log_archive_start=TRUE SCOPE=SPFILE ;

System altered.

SQL> alter system set log_archive_dest=’C:\app\Sanjay.Kumar\oradata\ARCH’ SCOPE=SPFILE ;

System altered.

SQL> SHOW PARAMETER LOG_ARCHIVE_FORMAT

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_format string ARC%S_%R.%T
SQL> SHUTDOWN IMMEDIATE ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount ;
ORA-32004: obsolete and/or deprecated parameter(s) specified <=== To decide the format of archive logs.
ORACLE instance started.

Total System Global Area 1686925312 bytes
Fixed Size 2130080 bytes
Variable Size 1107300192 bytes
Database Buffers 570425344 bytes
Redo Buffers 7069696 bytes
Database mounted.
SQL> alter database archivelog ;

Database altered.

SQL> alter database open ;

Database altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> alter system switch logfile ;

System altered.

==========
BELOW IS THE FAST AND EASIEST WAY TO BACKUP DATABASE USING RMAN.
=========
C:\Users\Sanjay.Kumar>rman target /

Recovery Manager: Release 11.1.0.7.0 – Production on Fri Dec 2 22:07:41 2011

connected to target database: SANJAY (DBID=1705348440)

RMAN> backup database ;

Starting backup at 02-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=127 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\CJ_DATA.DBF
input datafile file number=00007 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\CJ_INDEX.DBF
input datafile file number=00008 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\LOC_DATA1.DBF
input datafile file number=00009 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\LOC_INDEX1.DBF
input datafile file number=00010 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\VYGR_DATA1.DBF
input datafile file number=00011 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\VYGR_DATA_A.DBF
input datafile file number=00012 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\VYGR_DATA_B.DBF
input datafile file number=00013 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\VYGR_DATA_C.DBF
input datafile file number=00014 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\VYGR_DATA_D.DBF
input datafile file number=00001 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\SYSTEM01.DBF
input datafile file number=00002 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\SYSAUX01.DBF
input datafile file number=00005 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\EXAMPLE01.DBF
input datafile file number=00003 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\UNDOTBS01.DBF
input datafile file number=00004 name=C:\APP\SANJAY.KUMAR\ORADATA\SANJAY\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 02-DEC-11
channel ORA_DISK_1: finished piece 1 at 02-DEC-11
piece handle=C:\APP\SANJAY.KUMAR\FLASH_RECOVERY_AREA\SANJAY\BACKUPSET\2011_12_02\O1_MF_NNNDF_TAG20111202T220802_7FLM277K_.BKP tag=TAG20111202T220802 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 02-DEC-11
channel ORA_DISK_1: finished piece 1 at 02-DEC-11
piece handle=C:\APP\SANJAY.KUMAR\FLASH_RECOVERY_AREA\SANJAY\BACKUPSET\2011_12_02\O1_MF_NCSNF_TAG20111202T220802_7FLM70C6_.BKP tag=TAG20111202T220802 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-DEC-11

=====================================
Check the progress of rman backup.
=====================================

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
2 ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE”
3 FROM V$SESSION_LONGOPS
4 WHERE OPNAME LIKE ‘RMAN%’
5 AND OPNAME NOT LIKE ‘%aggregate%’
6 AND TOTALWORK != 0
7 AND SOFAR TOTALWORK ;

SID SERIAL# CONTEXT SOFAR TOTALWORK %_COMPLETE
———- ———- ———- ———- ———- ———-
127 24 1 3867698 5961968 64.87

==========
Command to validate if database can be restore from backup.
==========

RMAN> restore database validate;

Starting restore at 03-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece C:\APP\SANJAY.KUMAR\FLASH_RECOVERY_AREA\SANJAY\BACKUPSET\2011_12_02\O1_MF_NNNDF_TAG20111202T220802_7FLM277K_.BKP
channel ORA_DISK_1: piece handle=C:\APP\SANJAY.KUMAR\FLASH_RECOVERY_AREA\SANJAY\BACKUPSET\2011_12_02\O1_MF_NNNDF_TAG20111202T220802_7FLM277K_.BKP tag=TAG20111202T220802
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:26
Finished restore at 03-DEC-11

RMAN> restore archive logs validate ;

Notes: Restore database validate ; will validate only datafiles. You need to check for archive logs, controlfile, spfile and recovery area.

===============
Command to validate if archive logs can be used to restore the database.
===============

RMAN> restore archivelog from time “sysdate-1″ validate;

Note: In unix use single ‘ ‘ for sysdate.

Starting restore at 03-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archived log C:\APP\SANJAY.KUMAR\ORADATA\ARCH\ARC00001_0768865380.001
channel ORA_DISK_1: scanning archived log C:\APP\SANJAY.KUMAR\FLASH_RECOVERY_AREA\SANJAY\ARCHIVELOG\2011_12_02\O1_MF_1_2_7FLMHSB6_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\SANJAY.KUMAR\FLASH_RECOVERY_AREA\SANJAY\ARCHIVELOG\2011_12_02\O1_MF_1_3_7FLMHWDT_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\SANJAY.KUMAR\FLASH_RECOVERY_AREA\SANJAY\ARCHIVELOG\2011_12_02\O1_MF_1_4_7FLMJ3BT_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\SANJAY.KUMAR\FLASH_RECOVERY_AREA\SANJAY\ARCHIVELOG\2011_12_02\O1_MF_1_5_7FLMJ6X2_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\SANJAY.KUMAR\FLASH_RECOVERY_AREA\SANJAY\ARCHIVELOG\2011_12_02\O1_MF_1_6_7FLMJ785_.ARC
channel ORA_DISK_1: scanning archived log C:\APP\SANJAY.KUMAR\FLASH_RECOVERY_AREA\SANJAY\ARCHIVELOG\2011_12_02\O1_MF_1_7_7FLMJD6W_.ARC
Finished restore at 03-DEC-11

RMAN>

RMAN> restore database validate;
RMAN> restore controlfile validate;
RMAN> restore spfile validate;
RMAN> restore tablespace users validate;
RMAN> validate recovery area;

Posted in Backup and Recovery | Tagged: Installation | Leave a Comment »

Passion for technology

Challenging the challenges over a decade. Place for Oracle DBAs, Oracle Apps DBAs, Unix Administrators and Developers

It's me

Subscribe

Categories

Admin

Top Rated

Top Clicks

Blogroll

Archive for the ‘Backup and Recovery’ Category

RMAN Part-2

RMAN Part 1

Follow “Passion for technology”