| Incomplete Recovery [message #159863] |
Tue, 21 February 2006 23:51  |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
|
Can anyone explain me Why a full database backup should be performed after Incomplete Recovery?
|
|
|
|
|
|
|
|
| Re: Incomplete Recovery [message #160720 is a reply to message #160707] |
Tue, 28 February 2006 03:24  |
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
|
Senior Member |
|
|
Normally, you cannot. Oracle don't recommend that kind of recovery which pass the resetlog point.
But if you have the controlfile, redo logs and archived logs before and after the resetlog point, you can recover the db actually. I have ever done such a test under Linux Oracle9.2.0.6.
Alex zeng
[Updated on: Tue, 28 February 2006 03:25] Report message to a moderator |
|
|
|
| Re: Incomplete Recovery [message #160730 is a reply to message #160720] |
Tue, 28 February 2006 03:42 |
zoro
Messages: 4
Registered: February 2006
|
Junior Member |
|
|
I used this code to back up archived log file:
#get the redo sequence number of archived file is being archived
ARCSEQ=`${ORACLE_HOME}/bin/sqlplus -s as sysdba <<EOF
set heading off feedback off
select min(sequence#) from v\\$log
where archived='NO';
exit
EOF`
ARCLOG_FILES=`ls $log_arch_dest/|grep -v $ARCSEQ`
for arclog_file in $ARCLOG_FILES
do
cp $arclog_file /backup_dir
done
and I do backup control file and data files, no backup redo files.
I delete all those files and use my backup files to recover.
I always receive error :
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/app/oracle/oradata/celmopedb01/system01.dbf'
But if I don't remove the archived files ( the archived log is being archived).
It can recover.
What is the problem?
Maybe I remove the archived log file is being archived?
[Updated on: Tue, 28 February 2006 04:06] Report message to a moderator |
|
|
|
| Re: Incomplete Recovery [message #160738 is a reply to message #160730] |
Tue, 28 February 2006 04:00 |
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
|
Senior Member |
|
|
If you made a cold backup, you should backup the redo logs or you will get error msgs. When there are unarchived logs needed by recovery processes, the inconsistent error will be invoked.
Alex zeng
|
|
|
|
| Re: Incomplete Recovery [message #160757 is a reply to message #160738] |
Tue, 28 February 2006 04:39 |
zoro
Messages: 4
Registered: February 2006
|
Junior Member |
|
|
I make a backup in hot backup.
I mean that if the archived log file is being archived that is crashed,
we have no way to recover database from backup files although we backed up other archived log files?
|
|
|
|
| Re: Incomplete Recovery [message #160773 is a reply to message #160757] |
Tue, 28 February 2006 05:45 |
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
|
Senior Member |
|
|
Yes. In that case, we cannot use the archived logs which generated after the bad archived log but we can use the archived logs older than this bad archived log.
Alex zeng
|
|
|
|
| Re: Incomplete Recovery [message #160875 is a reply to message #160757] |
Tue, 28 February 2006 20:22 |
zoro
Messages: 4
Registered: February 2006
|
Junior Member |
|
|
I wonder my code is correct to backup archived log file:
#get the redo sequence number of archived file is being archived
ARCSEQ=`${ORACLE_HOME}/bin/sqlplus -s as sysdba <<EOF
set heading off feedback off
select min(sequence#) from v\\$log
where archived='NO';
exit
EOF`
#get the list of archived log files except the file name has ARCSEQ
ARCLOG_FILES=`ls $log_arch_dest/|grep -v $ARCSEQ`
for arclog_file in $ARCLOG_FILES
do
cp $arclog_file /backup_dir
done
Is there any way to backup archived log files?
|
|
|
|
|
|
| Re: Incomplete Recovery [message #160881 is a reply to message #160545] |
Tue, 28 February 2006 21:03 |
nguyenduong
Messages: 4
Registered: February 2006
|
Junior Member |
|
|
After doing incomplete recovery we reset the database by doing open database resetlogs i.e the sequence no. starts from 1.2....n. So the backup taken before is no longer helpful . so we must take again full db backup.
A full db backup here is the cold backup?
Is there any trouble if we use hot backup?
For Ex :
I use hot backup to backup controlfile,datafiles,archived log files. When database crash, I use these files to restore database.
After restoring, I start database (resetlogs), and use this hot backup again to back up database ( the second).
So files of the second can be used to restored database?
|
|
|
|
|
|
| Re: Incomplete Recovery [message #160926 is a reply to message #160923] |
Wed, 01 March 2006 01:01 |
nguyenduong
Messages: 4
Registered: February 2006
|
Junior Member |
|
|
I tested and saw that, when we delete all archived log files and copy archived log files that we backed up, there is always an error:
SQL> recover database until cancel using backup controlfile
ORA-00279: change 543825 generated at 03/01/2006 11:15:38 needed for thread 1
ORA-00289: suggestion :
/opt/app/oracle/flash_recovery_area/CELMTEST/archivelog/2006_03_01/o1_mf_1_4_%u_
.arc
ORA-00280: change 543825 for thread 1 is in sequence #4
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/app/oracle/flash_recovery_area/CELMTEST/archivelog/2006_03_01/o1_mf_1_3_%u_.arc
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/app/oracle/oradata/celmtest/system01.dbf'
How can we solve that?
|
|
|
|
| Re: Incomplete Recovery [message #160930 is a reply to message #160926] |
Wed, 01 March 2006 01:10 |
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
|
Senior Member |
|
|
That mean oracle need to more archived logs to reach a consistent status.
do you have file /opt/app/oracle/flash_recovery_area/CELMTEST/archivelog/2006_03_01/o1_mf_1_4_%u?
do you backup ALL archived logs after backup the data files?
Alex zeng
|
|
|
|
| Re: Incomplete Recovery [message #160935 is a reply to message #160930] |
Wed, 01 March 2006 01:22 |
nguyenduong
Messages: 4
Registered: February 2006
|
Junior Member |
|
|
I backed up all archived log except the archived is being archived.
I mean I use
alter system switch logfile;
alter system archive log stop;
and then
select min(sequence#) from v\\$log where archived='NO';
To avoid backing up the archive file that is currently being written, we find the least sequence number that is to be archived from the V$LOG view, and then backup all the archive files before that sequence number.
alter system archive log start;
I think because I didnt back up that file so it is error.
How do you think?
|
|
|
|
| Re: Incomplete Recovery [message #160944 is a reply to message #160935] |
Wed, 01 March 2006 01:53 |
alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com
|
Senior Member |
|
|
Yes, you are right.
Actually, oracle recommend you using 'alter system archive log current;' to archive one more archival after the backup and then backup it too.
In one word, be sure that you have get enough archived logs.
|
|
|
|
| Re: Incomplete Recovery [message #160988 is a reply to message #159863] |
Wed, 01 March 2006 04:53 |
frank.svs
Messages: 162
Registered: February 2006
|
Senior Member |
|
|
This might help u out gajini.
The reason why we take a fresh full Database physical bkp
because assume that u lost some online redo logs / control file /
or datafile then oracle will be updating the headers of all the
three type of files by initiaing an SCN(system change no).
Not only this but after incomplete recovery when u say
>recover cancel then
u r database does'nt open. Compulsorily u need to the database
by saying
> alter database open RESETLOGS;
and once u reset the logs. the log seq# starts from 1.
and u must complusoryily take a full database backup and the old bkp will become invalidated.
|
|
|
|
| Re: Incomplete Recovery [message #161073 is a reply to message #160944] |
Wed, 01 March 2006 20:26 |
nguyenduong
Messages: 4
Registered: February 2006
|
Junior Member |
|
|
| alexzeng wrote on Wed, 01 March 2006 01:53 |
Yes, you are right.
Actually, oracle recommend you using 'alter system archive log current;' to archive one more archival after the backup and then backup it too.
In one word, be sure that you have get enough archived logs.
|
You mean I will use:
alter system switch logfile;
alter system archive log current;
alter system archive log stop;
and copy all archived log files to back up.
We dont need :
select min(sequence#) from v\\$log where archived='NO';
To avoid backing up the archive file that is currently being written, we find the least sequence number that is to be archived from the V$LOG view, and then backup all the archive files before that sequence number.
Is that right?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
