From 10g there is a change in No_Invalidate parameter of dbms_stats. Default value of No_Invalidate in 10g is dbms_stats.Auto_Invalidate. Means the cursor will remain valid for 5 hrs after the stats gathering. This is to avoid spike in cpu and othe resources due to hard parsing. 5 hrs time limit is governed by underscore parameter “_optimizer_invalidation_period”
convert this post to pdf. It’s common missing the arch log file needed for standby recovery, there by standby going out of sync. One way before 10g is rebuilding the standby from scratch.
From 10g, you can create a incremental backup from primary and apply the incremental to standby site.
For Source and Target in ASM, use metalink note
Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary and standby are in ASM filesystem [ID 836986.1]
BACKUP FROM SOURCE:
~~~~~~~~~~~~~~~~~~~
get the SCN from checkpoint_change#@v$datafile_header of standby database. You can also take the current_scn@v$database. But v$database value comes from controlfile and if the controlfile value is recent than datafile, you may not be successful.
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 329412309 DATABASE FORMAT ‘/local/eastus/orabackup00/rman/incr1_%U’;
if you want compressed backup
BACKUP AS COMPRESSED backupset DEVICE TYPE DISK INCREMENTAL FROM SCN 8977054477894Â DATABASE FORMATÂ ‘/local/eastus/orabackup00/rman/incr1_%U’;
Starting backup at 06-MAY-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=422 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00015 name=/local/eastus/oradata01/eastusp1/eastusp1rdgdbo_data01.dbf
input datafile fno=00002 name=/local/eastus/oradata03/eastusp1/eastusp1undotbs01.dbf
input datafile fno=00010 name=/local/eastus/oradata05/eastusp1/eastusp1audit_data01.dbf
input datafile fno=00014 name=/local/eastus/oradata05/eastusp1/eastusp1_sysaux_01.dbf
input datafile fno=00013 name=/local/eastus/oradata05/eastusp1/eastusp1statspack_data.dbf
input datafile fno=00008 name=/local/eastus/oradata05/eastusp1/eastusp1xdb01.dbf
input datafile fno=00004 name=/local/eastus/oradata05/eastusp1/eastusp1drsys01.dbf
input datafile fno=00009 name=/local/eastus/oradata05/eastusp1/netiq_data01.dbf
input datafile fno=00003 name=/local/eastus/oradata05/eastusp1/eastusp1cwmlite01.dbf
input datafile fno=00005 name=/local/eastus/oradata05/eastusp1/eastusp1odm01.dbf
input datafile fno=00001 name=/local/eastus/oradata00/eastusp1/eastusp1system01.dbf
input datafile fno=00006 name=/local/eastus/oradata00/eastusp1/eastusp1tools01.dbf
input datafile fno=00007 name=/local/eastus/oradata00/eastusp1/eastusp1users01.dbf
input datafile fno=00012 name=/local/eastus/oradata02/eastusp1/eastusp1east4_idx01.dbf
input datafile fno=00017 name=/local/eastus/oradata02/eastusp1/eastusp1rdgdbo_idx01.dbf
input datafile fno=00011 name=/local/eastus/oradata01/eastusp1/eastusp1east4_data01.dbf
input datafile fno=00016 name=/local/eastus/oradata01/eastusp1/eastusp1ASI_Data01.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-09
channel ORA_DISK_1: finished piece 1 at 06-MAY-09
piece handle=/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1 tag=TAG20090506T160952 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:07:05
Finished backup at 06-MAY-09
TRANSFER THE INCREMENTAL BACKUP TO TARGET
Catalog the incremental backup. This help rman to indentify the new backup.
RMAN> CATALOG START WITH ‘/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1′;
searching for all files that match the pattern /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1
List of Files Unknown to the Database
=====================================
File Name: /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1
Recover with redo clause
RMAN> RECOVER DATABASE NOREDO;
Starting recover at 06-MAY-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /local/eastus/oradata00/eastusp1/eastusp1system01.dbf
destination for restore of datafile 00002: /local/eastus/oradata03/eastusp1/eastusp1undotbs01.dbf
destination for restore of datafile 00003: /local/eastus/oradata05/eastusp1/eastusp1cwmlite01.dbf
destination for restore of datafile 00004: /local/eastus/oradata05/eastusp1/eastusp1drsys01.dbf
destination for restore of datafile 00005: /local/eastus/oradata05/eastusp1/eastusp1odm01.dbf
destination for restore of datafile 00006: /local/eastus/oradata00/eastusp1/eastusp1tools01.dbf
destination for restore of datafile 00007: /local/eastus/oradata00/eastusp1/eastusp1users01.dbf
destination for restore of datafile 00008: /local/eastus/oradata05/eastusp1/eastusp1xdb01.dbf
destination for restore of datafile 00009: /local/eastus/oradata05/eastusp1/netiq_data01.dbf
destination for restore of datafile 00010: /local/eastus/oradata05/eastusp1/eastusp1audit_data01.dbf
destination for restore of datafile 00011: /local/eastus/oradata01/eastusp1/eastusp1east4_data01.dbf
destination for restore of datafile 00012: /local/eastus/oradata02/eastusp1/eastusp1east4_idx01.dbf
destination for restore of datafile 00013: /local/eastus/oradata05/eastusp1/eastusp1statspack_data.dbf
destination for restore of datafile 00014: /local/eastus/oradata05/eastusp1/eastusp1_sysaux_01.dbf
destination for restore of datafile 00015: /local/eastus/oradata01/eastusp1/eastusp1rdgdbo_data01.dbf
destination for restore of datafile 00016: /local/eastus/oradata01/eastusp1/eastusp1ASI_Data01.dbf
destination for restore of datafile 00017: /local/eastus/oradata02/eastusp1/eastusp1rdgdbo_idx01.dbf
channel ORA_DISK_1: reading from backup piece /local/eastus/orabackup00/rman/incr1_tvkebumh_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/eastus/orabackup00/rman/incr1_tvkebumh_1_1 tag=TAG20090506T160952
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished recover at 06-MAY-09
RMAN>
You can check the checkpoint_change#@datafile_header, it should have increased and should be equal to the point you started the incremental backup.
Controlfile of standby may not be updated after this excercise. Recreate the standby controlfile from primary again.
convert this post to pdf.