RMAN Incremental database backup in Oracle

I will demonstrate RMAN incremental database backup in this post.
My 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)My recovery catalog database name is neptune, listener port 1522 and serice name SHAIK.MOON.SHAIKDB.COM

Below is the step by step solution.

Step 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

Step 02: See the log mode of database.
SQL> select log_mode from v$database;

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

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

Step 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.

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

Step 06: Open the database.
SQL> alter database open;
Database altered.

Step 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';

Step 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: SHAIK (DBID=259530685)

Step 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

Step 10: Connect to the recovery catalog database.
oracle@neptune ~$ sqlplus sys/sistem@moon:1522/SHAIK.MOON.SHAIKDB.COM 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

Step 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.

Step 12: Connect to recovery catalog database.
bash-3.00$ rman catalog rman/rman@moon:1522/SHAIK.MOON.SHAIKDB.COM

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

Step 13: Create recovery catalog schema objects.
RMAN> create catalog tablespace catalog_spc;

recovery catalog created

Step 14: Be sure whether recovery catalog objects successfully created.
SQL> select table_name from tabs;

Step 15: Connect to rman on source and recovery catalog database.
bash-3.00$ rman target / catalog rman/rman@moon:1522/SHAIK.MOON.SHAIKDB.COM

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: SHAIK(DBID=259530685)
connected to recovery catalog database

Step 16: Register database with recovery catalog.
RMAN> register database;

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

Step 17: Check whether registration was successful.
RMAN> report schema;

If this return database structure then registration is successful.

Step 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

Step 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.

Comments

Post a Comment

Popular posts from this blog

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq
Read more

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned. ORA-00939: too many arguments for function But surprisingly on Oracle 9.2 database the same code does not return any error. Cause of the Problem As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255. Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767. Solution of the Problem Opt
Read more

ORA-31655: no data or metadata objects selected for job

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining
Read more