Here I am presenting the simple steps to demonstrate how to take incremental level backup,perform restore and recovery using RMAN.One can learn Rman incremental level backup easily by going through this post. I used oracle 10g express edition.
Difference between differential and cumulative incremental backup will be clear by going through this simple demo.
Simple scenarios has been taken.
Demonstrating DIFFERENTIAL INCREMENTAL BACKUP
1. Database is in NOARCHIVELOG mode.
2. Not using recovery catalog.
3. RMAN configuration setting that I used is following. I am posting the output of
RMAN> SHOW ALL;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 400 M FORMAT '/usr/lib/oracle/xe/backup/df_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/snapcf_XE.f'; # default
4. Since database is in NOARCHIVELOG mode, it is necessary to shut down the database cleanly using immediate,normal or transactional options.
RMAN> shutdown
5. To take incremental 0 level backup,mount the database. This step is required even in non-incremental backups.
RMAN> startup mount
6. Issue
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
This takes the full backup of the database and also includes controlfile as well as spfile.
7. Issue
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0
2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1084366
8. RMAN> ALTER DATABASE OPEN;
9. Now, perform some DML operations. For example,
SQL> CREATE TABLE T1(C1 NUMBER);
SQL> INSERT INTO T1 VALUES(10);
SQL> /
SQL> /
SQL> COMMIT;
10. Again, shutdown the database to take incremental level backup.
RMAN>SHUTDOWN
RMAN>STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';
It backups only the changes made since the last incremental level n or lower backup.
11. Open the database again to perform some insertions.
RMAN> ALTER DATABASE OPEN;
12. SQL> INSERT INTO T1 VALUES(1);
SQL> /
SQL> /
SQL> /
SQL> COMMIT;
13. Shutdown the database again to take incremental level 2 backup.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 2 DATABASE TAG 'INC_1';
14. Open database again to made some insertions.
RMAN> ALTER DATABASE OPEN;
SQL> INSERT INTO T1 VALUES(9);
SQL> /
SQL> /
SQL> COMMI;
15. select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0
2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1084663
Here all the changes since the last backup has been stored in the redo logs.
16. Now, delete controlfile.
$ mv oradata/XE/controlfile cf.bak
17. RMAN> SHUTDOWN ABORT;
18. RMAN> STARTUP
Database will not open. Database will only go upto the NOMOUNT state since controlfile has been lost.
19. RMAN> Now following steps are needed to recover the database.
20. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
If controlfile autobackup is located in default location, then we can skip this step.
21. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP
22. RMAN> ALTER DATABASE MOUNT;
23. RMAN> RESTORE DATABASE;
It will restore the database using the 0 level backup.
24. RMAN> RECOVER DATABASE
Watch the output carefully. You can recognise various backups that are being applied. Look for the tags that you have given to backupsets. It will applies all the incrementals one by one. First it will apply level 1 incremental, and then level 2. Then it will search for appropriate log sequence and applies the same if found. If log switching has not been taken place after the last incremental backup, then we get all the data without any data loss. And database will restore upto the current point in time. In our case no log switching has taken place and all the data since the last backup exist in the redologs. NOREDO option is needed when log switching has taken place. NOREDO option is not needed if log switch has not taken place.
25.RMAN> ALTER DATABASE OPEN RESETLOGS;
26. Now, view the table t1 and you will find the table restored upto the latest point in time if all the redos has been applied.
27. Always take full backup of the database after opening the database in resetlogs mode.
Demonstrating CUMULATIVE INCREMENTAL BACKUP
Here we will utilize same scenario as above,i.e. no recovery catalog mode,no archivelog mode.
We will start here by taking incremental level 0 backup.
1. RMAN> SHUTDOWN
2. RMAN> STARTUP MOUNT
3. RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
4. RMAN> ALTER DATABASE OPEN;
5. SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;
6. Make some insertion.
SQL> INSERT INTO T1 VALUES(2);
SQL> /
SQL> /
SQL> COMMIT;
7. Again shutdown the database to take incremental level 1 database which will copies only the changed blocks since the last incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';
8.ALTER DATABASE OPEN;
9. SQL> INSERT INTO T1 VALUES(3);
SQL> /
SQL> /
SQL> COMMIT;
10. Again shutdown the database to take cumulative incremental level 1 backup, this time. This backups all the changes made after the last n-1 or lower backup,here it will backup all the changes since the incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'CUM_1';
11. RMAN> ALTER DATABASE OPEN;
12. Perform some DML again.
SQL> INSERT INTO T1 VALUES(9);
SQL>/
SQL> /
SQL> COMMIT;
13. Now, we will shutdown the database to take incremental level 1 backup this time.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1_1';
14. RMAN> ALTER DATABASE OPEN;
15. SQL> INSERT INTO T1 VALUES(0);
SQL> /
SQL> COMMIT;
16. Delete controlfile.
$ mv oradata/XE/controlfile cf.bak
17. RMAN> SHUTDOWN ABORT
18. RMAN> STARTUP
Database will not open.It will go upto only NOMOUNT state.
19. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/XE/backup/cf_%F';
20. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
21. RMAN> ALTER DATABASE MOUNT;
22. RMAN> RESTORE DATABASE;,
It will restore the database from the last incremental level 0 database.
23. RMAN> RECOVER DATABASE;
Here NOREDO option is not necessary as it is assumed that redo logs has not been switched and complete recovery is possible. Check the same by looking at the contents of v$log and comparing with the checkpoint_change# that have noted in step 5. If all the changes since that value exist in the redo logs then NOREDO option is not needed. Else it is required.
Here first of all, cumulative incremental level 1 backup would restore as there is no need for the backup having tag 'INC_1' as cumulative incremental level 1 backup take backup of all the changes since the last level 0 backup. Thus our first incremental level 1 backup is not applied. Then it applies backup having tag 'INC_1_1' which we take after cumulative incremental level backup. After that it will apply all the changes recorded in the online redologs.
Thus , only two incremental level backups needed to apply here. But in the first scenario all the incremental backups had been applied. Thus we can say that using cumulative incremental backup in your incremental strategy provides faster recovery as number of incremental backups to be applied is less.
24. RMAN> ALTER DATBASE OPEN RESETLOGS;
25. Take whole database backup after opening database in resetlogs mode. It is a good practice to perform th same.
26. Now check your table. It must have all the changes that we made, keeping all the scenario same
Difference between differential and cumulative incremental backup will be clear by going through this simple demo.
Simple scenarios has been taken.
Demonstrating DIFFERENTIAL INCREMENTAL BACKUP
1. Database is in NOARCHIVELOG mode.
2. Not using recovery catalog.
3. RMAN configuration setting that I used is following. I am posting the output of
RMAN> SHOW ALL;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 400 M FORMAT '/usr/lib/oracle/xe/backup/df_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs/snapcf_XE.f'; # default
4. Since database is in NOARCHIVELOG mode, it is necessary to shut down the database cleanly using immediate,normal or transactional options.
RMAN> shutdown
5. To take incremental 0 level backup,mount the database. This step is required even in non-incremental backups.
RMAN> startup mount
6. Issue
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
This takes the full backup of the database and also includes controlfile as well as spfile.
7. Issue
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0
2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1084366
8. RMAN> ALTER DATABASE OPEN;
9. Now, perform some DML operations. For example,
SQL> CREATE TABLE T1(C1 NUMBER);
SQL> INSERT INTO T1 VALUES(10);
SQL> /
SQL> /
SQL> COMMIT;
10. Again, shutdown the database to take incremental level backup.
RMAN>SHUTDOWN
RMAN>STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';
It backups only the changes made since the last incremental level n or lower backup.
11. Open the database again to perform some insertions.
RMAN> ALTER DATABASE OPEN;
12. SQL> INSERT INTO T1 VALUES(1);
SQL> /
SQL> /
SQL> /
SQL> COMMIT;
13. Shutdown the database again to take incremental level 2 backup.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 2 DATABASE TAG 'INC_1';
14. Open database again to made some insertions.
RMAN> ALTER DATABASE OPEN;
SQL> INSERT INTO T1 VALUES(9);
SQL> /
SQL> /
SQL> COMMI;
15. select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 0 52428800 1 YES UNUSED
0
2 1 1 52428800 1 NO CURRENT
1077758 08-MAR-11
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1084663
Here all the changes since the last backup has been stored in the redo logs.
16. Now, delete controlfile.
$ mv oradata/XE/controlfile cf.bak
17. RMAN> SHUTDOWN ABORT;
18. RMAN> STARTUP
Database will not open. Database will only go upto the NOMOUNT state since controlfile has been lost.
19. RMAN> Now following steps are needed to recover the database.
20. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/xe/backup/cf_%F';
If controlfile autobackup is located in default location, then we can skip this step.
21. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP
22. RMAN> ALTER DATABASE MOUNT;
23. RMAN> RESTORE DATABASE;
It will restore the database using the 0 level backup.
24. RMAN> RECOVER DATABASE
Watch the output carefully. You can recognise various backups that are being applied. Look for the tags that you have given to backupsets. It will applies all the incrementals one by one. First it will apply level 1 incremental, and then level 2. Then it will search for appropriate log sequence and applies the same if found. If log switching has not been taken place after the last incremental backup, then we get all the data without any data loss. And database will restore upto the current point in time. In our case no log switching has taken place and all the data since the last backup exist in the redologs. NOREDO option is needed when log switching has taken place. NOREDO option is not needed if log switch has not taken place.
25.RMAN> ALTER DATABASE OPEN RESETLOGS;
26. Now, view the table t1 and you will find the table restored upto the latest point in time if all the redos has been applied.
27. Always take full backup of the database after opening the database in resetlogs mode.
Demonstrating CUMULATIVE INCREMENTAL BACKUP
Here we will utilize same scenario as above,i.e. no recovery catalog mode,no archivelog mode.
We will start here by taking incremental level 0 backup.
1. RMAN> SHUTDOWN
2. RMAN> STARTUP MOUNT
3. RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'FULL_INC';
4. RMAN> ALTER DATABASE OPEN;
5. SQL> SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;
6. Make some insertion.
SQL> INSERT INTO T1 VALUES(2);
SQL> /
SQL> /
SQL> COMMIT;
7. Again shutdown the database to take incremental level 1 database which will copies only the changed blocks since the last incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1';
8.ALTER DATABASE OPEN;
9. SQL> INSERT INTO T1 VALUES(3);
SQL> /
SQL> /
SQL> COMMIT;
10. Again shutdown the database to take cumulative incremental level 1 backup, this time. This backups all the changes made after the last n-1 or lower backup,here it will backup all the changes since the incremental level 0 backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE TAG 'CUM_1';
11. RMAN> ALTER DATABASE OPEN;
12. Perform some DML again.
SQL> INSERT INTO T1 VALUES(9);
SQL>/
SQL> /
SQL> COMMIT;
13. Now, we will shutdown the database to take incremental level 1 backup this time.
RMAN> SHUTDOWN
RMAN> STARTUP MOUNT
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'INC_1_1';
14. RMAN> ALTER DATABASE OPEN;
15. SQL> INSERT INTO T1 VALUES(0);
SQL> /
SQL> COMMIT;
16. Delete controlfile.
$ mv oradata/XE/controlfile cf.bak
17. RMAN> SHUTDOWN ABORT
18. RMAN> STARTUP
Database will not open.It will go upto only NOMOUNT state.
19. RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/usr/lib/oracle/XE/backup/cf_%F';
20. RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
21. RMAN> ALTER DATABASE MOUNT;
22. RMAN> RESTORE DATABASE;,
It will restore the database from the last incremental level 0 database.
23. RMAN> RECOVER DATABASE;
Here NOREDO option is not necessary as it is assumed that redo logs has not been switched and complete recovery is possible. Check the same by looking at the contents of v$log and comparing with the checkpoint_change# that have noted in step 5. If all the changes since that value exist in the redo logs then NOREDO option is not needed. Else it is required.
Here first of all, cumulative incremental level 1 backup would restore as there is no need for the backup having tag 'INC_1' as cumulative incremental level 1 backup take backup of all the changes since the last level 0 backup. Thus our first incremental level 1 backup is not applied. Then it applies backup having tag 'INC_1_1' which we take after cumulative incremental level backup. After that it will apply all the changes recorded in the online redologs.
Thus , only two incremental level backups needed to apply here. But in the first scenario all the incremental backups had been applied. Thus we can say that using cumulative incremental backup in your incremental strategy provides faster recovery as number of incremental backups to be applied is less.
24. RMAN> ALTER DATBASE OPEN RESETLOGS;
25. Take whole database backup after opening database in resetlogs mode. It is a good practice to perform th same.
26. Now check your table. It must have all the changes that we made, keeping all the scenario same
Second Scenario -
How to use RMAN incremental backup to synchronize out of sync Disaster Recovery [DR] site or Standby Database
In many ways your standby database can become out of sync with primary database. It may happen due to following reasons.
If unfortunately you may have deleted the archive log from primary [or like in our case 500 GB archive log needs to be transferred to DR site, which would have taken lots of time], you need to use RMAN incremental backup to sync the DR site.
The following is a step wise document. Also I tried to show the issues that we have faced during recovery of DR site.
Step 1
On the standby database, stop the managed recovery process (MRP) by using following command.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step 2
On the standby database, find the SCN which will be used for the incremental backup at the primary database.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
Step 3
In the primary database RMAN, start to take an incremental backup from the SCN that we have found from previous step 2.
$ rman target /
RMAN> BACKUP INCREMENTAL FROM SCNDATABASE FORMAT '/backup/Standby_%U' tag 'STANDBY' FILESPERSET 1;
or
spool msglog to '/home/oraprod/rman_2sep13.log'
crosscheck archivelog all;
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
BACKUP INCREMENTAL FROM SCN 5989520905001 DATABASE FORMAT '/backup/Standby_%U' tag 'FORSTANDBY' FILESPERSET 1;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
}
Step 4
Transfer the backup to the standby server. You may use scp or rsync command. Also you can compress the backup using "gzip" command to minimize the transfer time. Say in our case we have moved all the backups to /d01/backup/ directory
Step 5
Connect to the standby database as the RMAN target, and catalog all incremental backup pieces.
RMAN> CATALOG START WITH '/d01/backup/Standby';
Step 6
Recover the standby database with the incremental backup.
RMAN> RECOVER DATABASE NOREDO;
Step 7
On the Primary database create a standby control file backup.
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/StandbyCTRL.bck';
Copy the standby control file backup to the standby system.
Step 8
In the standby database, restore the standby control file as following way.
RMAN> SHUTDOWN;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/d01/backup/StandbyCTRL.bck';
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
Step 9
If the primary and standby database data file directories are identical, skip to step10.
In RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just cataloged data files. For example.
RMAN> CATALOG START WITH '+DATA_1/LONDAN/DATAFILE/';
RMAN> SWITCH DATABASE TO COPY;
Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths. For example, LOG_FILE_NAME_CONVERT=’/PARIS/’,’/LONDAN/’.
Step 10
[Optional] On the standby database, clear all standby redo log groups.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
Step 11
[Optional] On the standby database, restart Flashback Database.
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
Step 12
On the standby database, restart MRP services.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
or
restart the services with following command.
SQL> startup nomount pfile=initLONDAN.ora;
SQL> alter database mount standby database ;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
After this check whether the archive is coming and applying correctly or not using following command.
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
===========================
But, after this we faced some problem as some datafiles have been added in the primary database. So, archival process has not been started. To resolve this issue we need to take separate backup of the new datafiles and add it to the standby database using RMAN.
first backup the missing datafile using RMAN in primary database.
RMAN> backup datafile 42 FORMAT '/backup/Fordatafile_%U' tag 'FORDATAFILE' FILESPERSET 1;
then transfer this backup in standby. And then add the backup in standby using RMAN.
RMAN> CATALOG START WITH '/d01/backup/Fordatafile';
RMAN> RESTORE DATAFILE 42;
After this start recovering the standby database.
SQL> startup nomount;
SQL> recover standby database;
Once asked give the location of asked archive log file location. You can use "AUTO" mode to apply the archive log files automatically. The log will be applied till to the point to which archives are available.
After this point we have started the automatic archive applying process using following commands and database came into sync with primary in some times.
SQL> startup nomount pfile=initLONDAN.ora;
SQL> alter database mount standby database ;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
In many ways your standby database can become out of sync with primary database. It may happen due to following reasons.
- Due to network outage or slow network
- Sudden high archive log generation in primary site etc
If unfortunately you may have deleted the archive log from primary [or like in our case 500 GB archive log needs to be transferred to DR site, which would have taken lots of time], you need to use RMAN incremental backup to sync the DR site.
The following is a step wise document. Also I tried to show the issues that we have faced during recovery of DR site.
Step 1
On the standby database, stop the managed recovery process (MRP) by using following command.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step 2
On the standby database, find the SCN which will be used for the incremental backup at the primary database.
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
Step 3
In the primary database RMAN, start to take an incremental backup from the SCN that we have found from previous step 2.
$ rman target /
RMAN> BACKUP INCREMENTAL FROM SCN
or
spool msglog to '/home/oraprod/rman_2sep13.log'
crosscheck archivelog all;
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
BACKUP INCREMENTAL FROM SCN 5989520905001 DATABASE FORMAT '/backup/Standby_%U' tag 'FORSTANDBY' FILESPERSET 1;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
}
Step 4
Transfer the backup to the standby server. You may use scp or rsync command. Also you can compress the backup using "gzip" command to minimize the transfer time. Say in our case we have moved all the backups to /d01/backup/ directory
Step 5
Connect to the standby database as the RMAN target, and catalog all incremental backup pieces.
RMAN> CATALOG START WITH '/d01/backup/Standby';
Step 6
Recover the standby database with the incremental backup.
RMAN> RECOVER DATABASE NOREDO;
Step 7
On the Primary database create a standby control file backup.
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/backup/StandbyCTRL.bck';
Copy the standby control file backup to the standby system.
Step 8
In the standby database, restore the standby control file as following way.
RMAN> SHUTDOWN;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/d01/backup/StandbyCTRL.bck';
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
Step 9
If the primary and standby database data file directories are identical, skip to step10.
In RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just cataloged data files. For example.
RMAN> CATALOG START WITH '+DATA_1/LONDAN/DATAFILE/';
RMAN> SWITCH DATABASE TO COPY;
Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths. For example, LOG_FILE_NAME_CONVERT=’/PARIS/’,’/LONDAN/’.
Step 10
[Optional] On the standby database, clear all standby redo log groups.
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
Step 11
[Optional] On the standby database, restart Flashback Database.
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;
Step 12
On the standby database, restart MRP services.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
or
restart the services with following command.
SQL> startup nomount pfile=initLONDAN.ora;
SQL> alter database mount standby database ;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
After this check whether the archive is coming and applying correctly or not using following command.
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
===========================
But, after this we faced some problem as some datafiles have been added in the primary database. So, archival process has not been started. To resolve this issue we need to take separate backup of the new datafiles and add it to the standby database using RMAN.
first backup the missing datafile using RMAN in primary database.
RMAN> backup datafile 42 FORMAT '/backup/Fordatafile_%U' tag 'FORDATAFILE' FILESPERSET 1;
then transfer this backup in standby. And then add the backup in standby using RMAN.
RMAN> CATALOG START WITH '/d01/backup/Fordatafile';
RMAN> RESTORE DATAFILE 42;
After this start recovering the standby database.
SQL> startup nomount;
SQL> recover standby database;
Once asked give the location of asked archive log file location. You can use "AUTO" mode to apply the archive log files automatically. The log will be applied till to the point to which archives are available.
After this point we have started the automatic archive applying process using following commands and database came into sync with primary in some times.
SQL> startup nomount pfile=initLONDAN.ora;
SQL> alter database mount standby database ;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
http://oracle-latest-technology.blogspot.in/2013/09/how-to-use-rman-incremental-backup-to.html
No comments:
Post a Comment