RMAN Backup policy

RMAN Backup policy
---------------------

requirement is as follows.

1)Database running is noarchivelog mode. As I wish to take backup online so database need to run in archivelog mode.

2)I will use flash recovery area to take backup for better management of backup data.

3)Take full database backup or level 0 incremental backup in a week and each day will take incremental backup cumulative.

4)As a repository I will use recovery catalog in order to store backup information.

5)I need to setup rman configuration like,
-autobackup on(in order to back up the control file and spfile whenever the database structure metadata in the control file changes and whenever a backup record is added) ,

-backup optimization on (in order to skip logs that it has already backed up to the specified device). and,

-recovery window to 7 days. (to ensure that you can recover the database to any point within the last week)

6)recovery catalog database name is test, listener port 1522 and serice name test1

Below is the step by step solution.


01: Connect to source database as sysdba.
=============================================
oracle@neptune ~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 23:22:04 2008


Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

02: See the log mode of database.
====================================

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

03: Shutdown the source database.
================================
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

04: Start the database in mount state.
======================================
SQL> startup mount
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 109055528 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
Database mounted.

05: Enable archive log.
=========================
SQL> alter database archivelog;
Database altered.

06: Open the database.
=========================

SQL> alter database open;
Database altered.

07: Check the archived destination and flash recovery area size and location.
=============================================
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 83
Next log sequence to archive 85
Current log sequence 85

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- -------
db_recovery_file_dest string /oracle/app/oracle/product/10.
2.0/db_1/flash_recovery_area
db_recovery_file_dest_size big integer 50G
If you see after giving archive log list Archive destination is not shown as USE_DB_RECOVERY_FILE_DEST then set it by,
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

08: Connect to rman of source database.
=========================================
SQL> host rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:24:16 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: host (DBID=259530685)

09: Change some configuration settings of source database.
======================================================
RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> configure backup optimization on;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored

10: Connect to the recovery catalog database.
====================================================
oracle@neptune ~$ sqlplus sys/sistem@test:1522/test1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 23:35:21 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

11: Create recovery catalog user and tablespace and assign permission.
====================================================
SQL> create user rman identified by rman;

User created.

SQL> create tablespace catalog_spc datafile '/oradata1/catalog.dbf' size 10M autoextend on maxsize unlimited;

Tablespace created.

SQL> alter user rman default tablespace catalog_spc temporary tablespace temp quota unlimited on catalog_spc;

User altered.

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

12: Connect to recovery catalog database.
=================================================

bash-3.00$ rman catalog rman/rman@test:1522/test1

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:39:43 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to recovery catalog database

13: Create recovery catalog schema objects.
============================================

RMAN> create catalog tablespace catalog_spc;

recovery catalog created

14: Be sure whether recovery catalog objects successfully created.
===================================================

SQL> select table_name from tabs;

15: Connect to rman on source and recovery catalog database.
==========================================================

bash-3.00$ rman target / catalog rman/rman@test:1522/test1

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:41:53 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: host (DBID=259530685)
connected to recovery catalog database

16: Register database with recovery catalog.
============================================

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete


17: Check whether registration was successful.
==============================================

RMAN> report schema;

If this return database structure then registration is successful.

18: Create scripts for incremental database backups.
=========================================================

This script is for full database backup level 0. This one will be run on weekly basis.
--------------------------

CREATE GLOBAL SCRIPT global_full_backup
{
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
This script is for incremental cumulative database backup level 0. This one will be run on daily basis.
CREATE GLOBAL SCRIPT global_incr_backup
{
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}


This one is for RMAN incrementally updated backups. I just made this and will not use it. For huge database we may consider it.
--------------------------------

CREATE GLOBAL SCRIPT global_incr_updated_backup
{
RECOVER COPY OF DATABASE WITH TAG 'inc_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'inc_update'
DATABASE;
}

A sample output after creating global_full_backup,
RMAN> CREATE GLOBAL SCRIPT global_full_backup;
{
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}

starting full resync of recovery catalog
full resync complete
created global script global_full_backup

19: Run these scripts and take backups.
======================================

Weekly,

RMAN>RUN{
EXECUTE GLOBAL SCRIPT global_full_backup;
}

Daily,

RMAN>RUN{
EXECUTE GLOBAL SCRIPT global_incr_backup;
}


You may consider also inside script like,

RMAN> backup database plus archivelog delete all input;

in order to delete archived log from all locations that already taken backup.

Or, you may issue,

RMAN> delete archivelog all backed up 1 times to disk;


in order to delete archive log that have at least 1 times backed up to disk.