Monday, December 6, 2010

DB Recovery

incomplete recovery of database until CANCLE after reaching some time from hot snap backup and archives



SQL>startup mount

SQL> recover database using backup controlfile until CANCEL
ORA-00279: change 5965295809608 generated at 12/01/2010 17:54:21 needed for
thread 1
ORA-00289: suggestion :
/oraarchive/vis/abcd001k_VIS_0001_0000006134_697765707.arc
ORA-00280: change 5965295809608 for thread 1 is in sequence #6134


Specify log: {=suggested | filename | AUTO | CANCEL}
/oraarchive/vis/abcd001k_VIS_0001_0000006134_697765707.arc
ORA-00279: change 5965295965197 generated at 12/02/2010 00:00:16 needed for
thread 1
ORA-00289: suggestion :
/oraarchive/vis/abcd001k_VIS_0001_0000006135_697765707.arc
ORA-00280: change 5965295965197 for thread 1 is in sequence #6135
ORA-00278: log file
'/oraarchive/vis/abcd001k_VIS_0001_0000006134_697765707.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/oraarchive/vis/abcd001k_VIS_0001_0000006135_697765707.arc
ORA-00279: change 5965295965276 generated at 12/02/2010 00:00:36 needed for
thread 1
ORA-00289: suggestion :
/oraarchive/vis/abcd001k_VIS_0001_0000006136_697765707.arc
ORA-00280: change 5965295965276 for thread 1 is in sequence #6136
ORA-00278: log file
'/oraarchive/vis/abcd001k_VIS_0001_0000006135_697765707.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/oraarchive/vis/abcd001k_VIS_0001_0000006136_697765707.arc


ORA-00279: change 5965296032263 generated at 12/02/2010 04:00:15 needed for
thread 1
ORA-00289: suggestion :
/oraarchive/vis/abcd001k_VIS_0001_0000006137_697765707.arc
ORA-00280: change 5965296032263 for thread 1 is in sequence #6137
ORA-00278: log file
'/oraarchive/vis/abcd001k_VIS_0001_0000006136_697765707.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}


/oraarchive/vis/abcd001k_VIS_0001_0000006137_697765707.arc
ORA-00279: change 5965296032275 generated at 12/02/2010 04:00:19 needed for
thread 1
ORA-00289: suggestion :
/oraarchive/vis/abcd001k_VIS_0001_0000006138_697765707.arc
ORA-00280: change 5965296032275 for thread 1 is in sequence #6138
ORA-00278: log file
'/oraarchive/vis/abcd001k_VIS_0001_0000006137_697765707.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

/oraarchive/vis/abcd001k_VIS_0001_0000006138_697765707.arc
ORA-00279: change 5965296081988 generated at 12/02/2010 08:00:15 needed for
thread 1
ORA-00289: suggestion :
/oraarchive/vis/abcd001k_VIS_0001_0000006139_697765707.arc
ORA-00280: change 5965296081988 for thread 1 is in sequence #6139
ORA-00278: log file
'/oraarchive/vis/abcd001k_VIS_0001_0000006138_697765707.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/oraarchive/vis/abcd001k_VIS_0001_0000006139_697765707.arc
ORA-00279: change 5965296081997 generated at 12/02/2010 08:00:19 needed for
thread 1
ORA-00289: suggestion :
/oraarchive/vis/abcd001k_VIS_0001_0000006140_697765707.arc
ORA-00280: change 5965296081997 for thread 1 is in sequence #6140
ORA-00278: log file
'/oraarchive/vis/abcd001k_VIS_0001_0000006139_697765707.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/oraarchive/vis/abcd001k_VIS_0001_0000006140_697765707.arc
ORA-00279: change 5965296141979 generated at 12/02/2010 12:00:15 needed for
thread 1
ORA-00289: suggestion :
/oraarchive/vis/abcd001k_VIS_0001_0000006141_697765707.arc
ORA-00280: change 5965296141979 for thread 1 is in sequence #6141
ORA-00278: log file
'/oraarchive/vis/abcd001k_VIS_0001_0000006140_697765707.arc' no longer
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/d03/oraVIS/vis/db/apps_st/data/system01.dbf'


To open the database with foruce, do below:

1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) Alter database open resetlogs.
4) reset undo_management to “manual” in init.ora file.
5) startup database
6) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
7) Bounce database.