Problem: I created Stand By Database To Primary DB
But I Have Huge archive Gap On Standby DB Approx(2000 Archives)
Solution:
1. Cross Check Primary DB SCN Number And Standby SCN Number
Ex:
A. Primary SCN: 9446442315
B. Standby SCN: 9112654684
SQL> select current_scn from v$database;
2. So There Is Huge Difference For That Purpose I Took Incremental Backup On Primary From SCN:9112654684
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 9112654684 DATABASE
FORMAT '/tmp/incr_for_standby/bkup_%U';
3. Now Restore This Incremental Backup On Standby DB
RMAN> catalog start with '/tmp/incr_for_standby';
4. Now recover the standby database using the noredo option to apply only incremental backups.
RMAN> recover database noredo;
5. RMAN will apply only incremental backups to the standby database.
6. Then switch the redo log file on the primary database and apply it on the standby side.
7. On the primary side
SQL>alter system switch logfile;
System altered.
8. On the standby side
SQL>recover standby database;
9. Resolving Archived Redo Log Gaps Using Incremental Backups
Imagine that due to the network failure, some archived redo log fileswere not shipped to the standby database and were deleted from the primary database according to the defined RMAN retention policy. Since the next generated archived redo log file cannot be jumped into before successfully applying all of them one by one in subsequent order, either create a standby database from scratch, or ?
Sure, there is another option. By applying the necessary incremental backup taken from the primary database, take the database forward and bypass applying missing archived redo log files. The following scenario will show the steps of recovering the standby database without having archived redo log files.
At first, there needs to be a standby database with missing redo log files. For this:
1.Change the log_archive_dest_2 parameter on the primary site
2. Manually switch log files to create some archived redo log files
3. Delete generated archived redo log files from the primary site
First of all, check the v$archived_log file and get the last generated archived redo log sequence value on both databases, then change the log_archive_dest_2 initialization parameter on the primary site to block archived redo log shipping to the standby site as follows:
10. Run the following code on the primary database
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
54
11. Run the following code on the standby database:
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
54
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
--------------------------- ----------- ----------------------------
log_archive_dest_2 string service=test optional reopen=15
SQL> alter system set log_archive_dest_2='service=noservice';
System altered.
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
---------------------------- ----------- ---------------------------
log_archive_dest_2 string service=noservice
12.Now make some manual redo log switches and check the
v$archived_log view on both sites again:
13. Run the following codes on the primary database
SQL> alter system switch logfile;
System altered.
SQL>
/
System altered.
SQL>
/
System altered.
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
57
SQL>
14. Run the following code on the standby database
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
54
15. This shows that the last three archived redo log files were not shipped to the standby site.
Go to the primary site and delete the last generated three archived redo log files with sequence 55, 56 and 57:
This shows that the last three archived redo log files were not shipped to the standby site. Go to the primary site and delete the last generated three archived redo log files with sequence 55, 56 and 57:
[oracle@localhost 2010_06_06]$ rm -rf o1_mf_1_5[5-7]_60rp*
Now cancel the managed recovery operation on the standby site, take the last sequence value and shut down the standby database:
SQL>alter database recover managed standby database cancel;
Database altered.
SQL>select current_scn from v$database;
CURRENT_SCN
-----------
507189
SQL>shutdown immediate;
Switch to the primary site and take an incremental backup of the database starting from the scn value that has been taken from the standby database:
RMAN> backup incremental from scn 507189 database format='/u02/rman_backup/incremental/incr_backup_%U';
Create the standby controlfile, change the log_archive_dest_2 initialization parameter and switch the current redo log file:
SQL>alter database create standby controlfile as '/u02/rman_backup/standby_control.ctl';
SQL>alter system set log_archive_dest_2='service=test optional reopen=15';
System altered.
SQL>alter system switch logfile;
Copy the standby controlfile and incremental backup file to the standby side, start up the standby database in nomount modeand change the parameter file to make the instance use the standby controlfile:
SQL>alter system set control_files='/u02/rman_backup/standby_control.ctl' scope=spfile;
System altered.
SQL>shutdown immediate
SQL>startup nomount
Mount the standby database and catalog the incremental backup to its repository:
SQL>alterdatabase mount standby database;
RMAN> catalog ackuppiece'/u02/rman_backup/incr_backup_1mlfj8pq_1_1';
Now, recover the database using incremental backup:
RMAN> recover database;
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: reading from backup piece /u02/rman_backup/
incr_backup_1mlfj8pq_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/rman_backup/incr_backup_1mlfj8pq_1_1
tag=TAG20100606T224202
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
starting media recovery
archive log thread 1 sequence 58 is already on disk as file
/u01/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/
2010_06_07/o1_mf_1_58_60s4sjbc_.arc
archive log
filename=/u01/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/
rchivelog/2010_06_07/o1_mf_1_58_60s4sjbc_.arc
thread=1 sequence=58
unable to find archive log
archive log thread=1 sequence=59
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/07/2010 02:38:36
RMAN-06054: media recovery requesting unknown log: thread 1 seq 59 lowscn 508306
RMAN> exit
As can be seen, RMAN tries to apply archived redo log with sequence 59 which was not generated on the primary database yet. Perform a manual redo log switch on the primary database. The new archived redo log file will, with sequence 59, be created and shipped to the standby server. Take the current scn on the primary database to compare it with the standby database:
SQL>alter system switch logfile;
System altered.
SQL>select current_scn from v$database;
CURRENT_SCN
-----------
511308
Now switch to the standby database and run the recover standby database command. RMAN will look for the next archived redo log file and apply it automatically:
SQL>recover standby database;
Then query the current scn value on the standby database:
SQL>select current_scn from v$database;
CURRENT_SCN
-----------
511301
Conclusion
In this chapter, the method of cloning a database using the duplicate database command of RMAN has been shown. The database has been cloned to the remote and localhost with the same and different directory structures. Then the new feature of Oracle 11g a network enabled database clone without having any backups was examined where the database was cloned using live datafiles.
################################### All The Best ###################################
But I Have Huge archive Gap On Standby DB Approx(2000 Archives)
Solution:
1. Cross Check Primary DB SCN Number And Standby SCN Number
Ex:
A. Primary SCN: 9446442315
B. Standby SCN: 9112654684
SQL> select current_scn from v$database;
2. So There Is Huge Difference For That Purpose I Took Incremental Backup On Primary From SCN:9112654684
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 9112654684 DATABASE
FORMAT '/tmp/incr_for_standby/bkup_%U';
3. Now Restore This Incremental Backup On Standby DB
RMAN> catalog start with '/tmp/incr_for_standby';
4. Now recover the standby database using the noredo option to apply only incremental backups.
RMAN> recover database noredo;
5. RMAN will apply only incremental backups to the standby database.
6. Then switch the redo log file on the primary database and apply it on the standby side.
7. On the primary side
SQL>alter system switch logfile;
System altered.
8. On the standby side
SQL>recover standby database;
9. Resolving Archived Redo Log Gaps Using Incremental Backups
Imagine that due to the network failure, some archived redo log fileswere not shipped to the standby database and were deleted from the primary database according to the defined RMAN retention policy. Since the next generated archived redo log file cannot be jumped into before successfully applying all of them one by one in subsequent order, either create a standby database from scratch, or ?
Sure, there is another option. By applying the necessary incremental backup taken from the primary database, take the database forward and bypass applying missing archived redo log files. The following scenario will show the steps of recovering the standby database without having archived redo log files.
At first, there needs to be a standby database with missing redo log files. For this:
1.Change the log_archive_dest_2 parameter on the primary site
2. Manually switch log files to create some archived redo log files
3. Delete generated archived redo log files from the primary site
First of all, check the v$archived_log file and get the last generated archived redo log sequence value on both databases, then change the log_archive_dest_2 initialization parameter on the primary site to block archived redo log shipping to the standby site as follows:
10. Run the following code on the primary database
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
54
11. Run the following code on the standby database:
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
54
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
--------------------------- ----------- ----------------------------
log_archive_dest_2 string service=test optional reopen=15
SQL> alter system set log_archive_dest_2='service=noservice';
System altered.
SQL> show parameter log_archive_dest_2;
NAME TYPE VALUE
---------------------------- ----------- ---------------------------
log_archive_dest_2 string service=noservice
12.Now make some manual redo log switches and check the
v$archived_log view on both sites again:
13. Run the following codes on the primary database
SQL> alter system switch logfile;
System altered.
SQL>
/
System altered.
SQL>
/
System altered.
SQL>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
57
SQL>
14. Run the following code on the standby database
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
54
15. This shows that the last three archived redo log files were not shipped to the standby site.
Go to the primary site and delete the last generated three archived redo log files with sequence 55, 56 and 57:
This shows that the last three archived redo log files were not shipped to the standby site. Go to the primary site and delete the last generated three archived redo log files with sequence 55, 56 and 57:
[oracle@localhost 2010_06_06]$ rm -rf o1_mf_1_5[5-7]_60rp*
Now cancel the managed recovery operation on the standby site, take the last sequence value and shut down the standby database:
SQL>alter database recover managed standby database cancel;
Database altered.
SQL>select current_scn from v$database;
CURRENT_SCN
-----------
507189
SQL>shutdown immediate;
Switch to the primary site and take an incremental backup of the database starting from the scn value that has been taken from the standby database:
RMAN> backup incremental from scn 507189 database format='/u02/rman_backup/incremental/incr_backup_%U';
Create the standby controlfile, change the log_archive_dest_2 initialization parameter and switch the current redo log file:
SQL>alter database create standby controlfile as '/u02/rman_backup/standby_control.ctl';
SQL>alter system set log_archive_dest_2='service=test optional reopen=15';
System altered.
SQL>alter system switch logfile;
Copy the standby controlfile and incremental backup file to the standby side, start up the standby database in nomount modeand change the parameter file to make the instance use the standby controlfile:
SQL>alter system set control_files='/u02/rman_backup/standby_control.ctl' scope=spfile;
System altered.
SQL>shutdown immediate
SQL>startup nomount
Mount the standby database and catalog the incremental backup to its repository:
SQL>alterdatabase mount standby database;
RMAN> catalog ackuppiece'/u02/rman_backup/incr_backup_1mlfj8pq_1_1';
Now, recover the database using incremental backup:
RMAN> recover database;
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: reading from backup piece /u02/rman_backup/
incr_backup_1mlfj8pq_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/rman_backup/incr_backup_1mlfj8pq_1_1
tag=TAG20100606T224202
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
starting media recovery
archive log thread 1 sequence 58 is already on disk as file
/u01/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/
2010_06_07/o1_mf_1_58_60s4sjbc_.arc
archive log
filename=/u01/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/
rchivelog/2010_06_07/o1_mf_1_58_60s4sjbc_.arc
thread=1 sequence=58
unable to find archive log
archive log thread=1 sequence=59
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/07/2010 02:38:36
RMAN-06054: media recovery requesting unknown log: thread 1 seq 59 lowscn 508306
RMAN> exit
As can be seen, RMAN tries to apply archived redo log with sequence 59 which was not generated on the primary database yet. Perform a manual redo log switch on the primary database. The new archived redo log file will, with sequence 59, be created and shipped to the standby server. Take the current scn on the primary database to compare it with the standby database:
SQL>alter system switch logfile;
System altered.
SQL>select current_scn from v$database;
CURRENT_SCN
-----------
511308
Now switch to the standby database and run the recover standby database command. RMAN will look for the next archived redo log file and apply it automatically:
SQL>recover standby database;
Then query the current scn value on the standby database:
SQL>select current_scn from v$database;
CURRENT_SCN
-----------
511301
Conclusion
In this chapter, the method of cloning a database using the duplicate database command of RMAN has been shown. The database has been cloned to the remote and localhost with the same and different directory structures. Then the new feature of Oracle 11g a network enabled database clone without having any backups was examined where the database was cloned using live datafiles.
################################### All The Best ###################################
No comments:
Post a Comment