OraFAQ Forum: Backup & Recovery » recovering after OPEN RESETLOGS

Home » RDBMS Server » Backup & Recovery » recovering after OPEN RESETLOGS

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

recovering after OPEN RESETLOGS [message #165370]

Wed, 29 March 2006 11:05

deesto
Messages: 36
Registered: March 2005
Location: NY, USA

Member

I'm following a scenario in Oracle's 10g Performing User-Managed Media Recovery documentation called "Restoring a Backup Created Before a RESETLOGS: Scenario", in which you basically backup the database at the OS level, mount, recovery until cancel, OPEN RESETLOGS, SHUTDOWN ABORT, restore the database files via the OS, enable autorecovery, and finally, RECOVER DATABASE.

The problem I'm having is that when I get to the final step (RECOVER DATABASE), I get an Oracle not available error (ORA-01034), so I can't complete the procedure. Is this due to the fact that the database isn't started (from the SHUTDOWN ABORT step)? If so, how would I get around that to complete the procedure?

Also, if you know of a better procedure to recover a database after OPEN RESETLOGS, please let me know.

Thank you!
~John

Report message to a moderator

Re: recovering after OPEN RESETLOGS [message #165402 is a reply to message #165370]

Wed, 29 March 2006 22:02

alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com

Senior Member

Here is my test on this scenario:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE

SQL>
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle10g/oradata/orcl/system01.dbf
/opt/oracle10g/oradata/orcl/undotbs01.dbf
/opt/oracle10g/oradata/orcl/sysaux01.dbf
/opt/oracle10g/oradata/orcl/users01.dbf
/opt/oracle10g/oradata/orcl/example01.dbf

SQL> select * from v$controlfile;

STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

/opt/oracle10g/oradata/orcl/control01.ctl
NO 16384 430

/opt/oracle10g/oradata/orcl/control02.ctl
NO 16384 430

STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------

/opt/oracle10g/oradata/orcl/control03.ctl
NO 16384 430

SQL> desc v$logfile
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP# NUMBER
STATUS VARCHAR2(7)
TYPE VARCHAR2(7)
MEMBER VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)

SQL> select group#,member from v$logfile;

GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
/opt/oracle10g/oradata/orcl/redo03.log

2
/opt/oracle10g/oradata/orcl/redo02.log

1
/opt/oracle10g/oradata/orcl/redo01.log

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host

--start cold backup
[oracle@rhes29-new oracle10g]$ cp /opt/oracle10g/oradata/orcl/ /opt/oracle10g/oradata/orcl_cold -r
[oracle@rhes29-new oracle10g]$ cp /opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16 /opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16_cold -r
[oracle@rhes29-new oracle10g]$ exit
exit
--end of cold backup, THIS IS POINT A

SQL> startup
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218944 bytes
Variable Size 88082048 bytes
Database Buffers 184549376 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 rows selected.

--add some data to db
SQL> create user t identified by t default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource to t;

Grant succeeded.

SQL> conn t/t
Connected.
SQL> create table t (c1 number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

C1
----------
1

SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
--END OF ADD DATA, THIS IS POINT A1

--SIMULATE DATABASE CRASH
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

$ rm /opt/oracle10g/oradata/orcl -r

--RESTORE AND RECOVER DATABASE
--including control file and datafile, no redo files(actually, backup redo is not userful)
[oracle@rhes29-new oracle10g]$ cp -r /opt/oracle10g/oradata/orcl_cold/ /opt/oracle10g/oradata/orcl
[oracle@rhes29-new oracle10g]$ rm /opt/oracle10g/oradata/orcl/redo0*

--recover database
[oracle@rhes29-new oracle10g]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 16 14:13:36 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218944 bytes
Variable Size 88082048 bytes
Database Buffers 184549376 bytes
Redo Buffers 7168000 bytes
Database mounted.
SQL> recover database using backup controlfile;
ORA-00279: change 484035 generated at 01/16/2006 13:45:37 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_%u_.arc
ORA-00280: change 484035 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 484519 generated at 01/16/2006 14:02:06 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_6_%u_.arc
ORA-00280: change 484519 for thread 1 is in sequence #6
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_1wqvfgj
d_.arc' no longer needed for this recovery

ORA-00279: change 484522 generated at 01/16/2006 14:02:08 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_7_%u_.arc
ORA-00280: change 484522 for thread 1 is in sequence #7
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_6_1wqvfjn
2_.arc' no longer needed for this recovery

ORA-00279: change 484526 generated at 01/16/2006 14:02:12 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_8_%u_.arc
ORA-00280: change 484526 for thread 1 is in sequence #8
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_7_1wqvfn8
m_.arc' no longer needed for this recovery

ORA-00308: cannot open archived log
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_8_%u_.arc
'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 484526 generated at 01/16/2006 14:02:12 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_8_%u_.arc
ORA-00280: change 484526 for thread 1 is in sequence #8

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

--THIS IS POINT B.
SQL> conn t/t
Connected.
SQL> select * from t;

C1
----------
1
--backup controlfile
SQL> conn / as sysdba
Connected.
SQL> alter database backup controlfile to '/opt/oracle10g/oradata/b1.ctl';

Database altered.

SQL> alter database backup controlfile to trace as '/opt/oracle10g/oradata/b1_ctl.trc';

Database altered.

SQL> conn t/t
Connected.
SQL> insert into t values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

C1
----------
1
2

SQL> conn / as sysdba
Connected.
SQL> alter system archive log current;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE

--THIS IS POINT B1 WHICH AFTER B.

--SIMULATE DATABASE CRASH
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
[oracle@rhes29-new oracle10g]$ rm -r /opt/oracle10g/oradata/orcl
--THIS IS POINT C
--WE HAVE A FULL BACKUP OF POINT 'A' AND CONTROL FILE OF 'B' AND ALL ARCHIVE LOG FILES

--LET'S RECOVE DB FROM A -> C
--restore data file and control file
[oracle@rhes29-new oracle10g]$ rm -r /opt/oracle10g/oradata/orcl
[oracle@rhes29-new oracle10g]$ cp /opt/oracle10g/oradata/orcl_cold/ /opt/oracle10g/oradata/orcl -r
[oracle@rhes29-new oracle10g]$ rm /opt/oracle10g/oradata/orcl/*log

--recover db to B
[oracle@rhes29-new oracle10g]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 16 14:50:18 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218944 bytes
Variable Size 88082048 bytes
Database Buffers 184549376 bytes
Redo Buffers 7168000 bytes
Database mounted.

SQL> recover database using backup controlfile;
ORA-00279: change 484035 generated at 01/16/2006 13:45:37 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_%u_.arc
ORA-00280: change 484035 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-19906: recovery target incarnation changed during recovery

ORA-01112: media recovery not started

SQL> host ls -lt /opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16
total 24064
-rw-r----- 1 oracle dba 11776 Jan 16 14:30 o1_mf_1_3_1wqx20fk_.arc
-rw-r----- 1 oracle dba 1024 Jan 16 14:30 o1_mf_1_2_1wqx1x9f_.arc
-rw-r----- 1 oracle dba 1823232 Jan 16 14:30 o1_mf_1_1_1wqx1vnw_.arc
-rw-r----- 1 oracle dba 2560 Jan 16 14:02 o1_mf_1_7_1wqvfn8m_.arc
-rw-r----- 1 oracle dba 1024 Jan 16 14:02 o1_mf_1_6_1wqvfjn2_.arc
-rw-r----- 1 oracle dba 583680 Jan 16 14:02 o1_mf_1_5_1wqvfgjd_.arc
-rw-r----- 1 oracle dba 2560 Jan 16 13:42 o1_mf_1_4_1wqt89gs_.arc
-rw-r----- 1 oracle dba 8192 Jan 16 13:41 o1_mf_1_3_1wqt7lo6_.arc
-rw-r----- 1 oracle dba 22152704 Jan 16 13:40 o1_mf_1_2_1wqt5tkr_.arc

SQL> recover database using backup controlfile;
ORA-00279: change 484035 generated at 01/16/2006 13:45:37 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_%u_.arc
ORA-00280: change 484035 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_1wqvfgjd_.arc
ORA-00279: change 484519 generated at 01/16/2006 14:02:06 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_6_%u_.arc
ORA-00280: change 484519 for thread 1 is in sequence #6
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_5_1wqvfgj
d_.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_6_1wqvfjn2_.arc
ORA-00279: change 484522 generated at 01/16/2006 14:02:08 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_7_%u_.arc
ORA-00280: change 484522 for thread 1 is in sequence #7
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_6_1wqvfjn
2_.arc' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_7_1wqvfn8m_.arc
ORA-00279: change 484527 generated at 01/16/2006 14:17:22 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_1_%u_.arc
ORA-00280: change 484527 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

--Now is the POINT BEFORE B (BEFORE RESETLOGS)

SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /opt/oracle10g/oradata/orcl/co
ntrol01.ctl, /opt/oracle10g/or
adata/orcl/control02.ctl, /opt
/oracle10g/oradata/orcl/contro
l03.ctl

--change controlfile
SQL> alter system set control_files='/opt/oracle10g/oradata/b1.ctl' scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 1218944 bytes
Variable Size 88082048 bytes
Database Buffers 184549376 bytes
Redo Buffers 7168000 bytes
Database mounted.

SQL> recover database using backup controlfile;
ORA-00279: change 484527 generated at 01/16/2006 14:02:13 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_1_%u_.arc
ORA-00280: change 484527 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 485323 generated at 01/16/2006 14:30:03 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_2_%u_.arc
ORA-00280: change 485323 for thread 1 is in sequence #2
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_1_1wqx1vn
w_.arc' no longer needed for this recovery

ORA-00279: change 485326 generated at 01/16/2006 14:30:05 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_3_%u_.arc
ORA-00280: change 485326 for thread 1 is in sequence #3
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_2_1wqx1x9
f_.arc' no longer needed for this recovery

ORA-00279: change 485330 generated at 01/16/2006 14:30:08 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_4_%u_.arc
ORA-00280: change 485330 for thread 1 is in sequence #4
ORA-00278: log file
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_3_1wqx20f
k_.arc' no longer needed for this recovery

ORA-00308: cannot open archived log
'/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_4_%u_.arc
'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> recover database using backup controlfile;
ORA-00279: change 485330 generated at 01/16/2006 14:30:08 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_4_%u_.arc
ORA-00280: change 485330 for thread 1 is in sequence #4

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/opt/oracle10g/oradata/orcl/system01.dbf'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 485330 generated at 01/16/2006 14:30:08 needed for thread 1
ORA-00289: suggestion :
/opt/oracle10g/flash_recovery_area/ORCL/archivelog/2006_01_16/o1_mf_1_4_%u_.arc
ORA-00280: change 485330 for thread 1 is in sequence #4
--Retry， until cancel

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> conn t/t
Connected.
SQL> select * from t;

C1
----------
1
2
--Done. Recover from Point A to Point C, span the resetlogs point。

Summary: The controlfile after resetlogs is needed in this scenario

Alex zeng |Skype me: hans9zeng

Report message to a moderator

Re: recovering after OPEN RESETLOGS [message #165708 is a reply to message #165402]

Fri, 31 March 2006 14:44

deesto
Messages: 36
Registered: March 2005
Location: NY, USA

Member

Alex, thank you so much for following up with this detailed, logged scenario! It was a great help. I'm still confused about when certain steps are necessary, namely all of the 'recover database using backup controlfile;' commands that seem to fail (but perhaps these are necessary to recover some files?), but thanks to your information, I was able to follow the entire process through to completion.

Thank you,
John

Report message to a moderator

Re: recovering after OPEN RESETLOGS [message #166910 is a reply to message #165708]

Mon, 10 April 2006 04:16

alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com

Senior Member

deesto wrote on Fri, 31 March 2006 14:44

Yes, you are right and they are necessary.

When we are running 'recover database using backup controlfile;' against oracle, oracle will don't stop recover processes until it meet a error or cancel reply. We should use 'recover database using backup controlfile UNTIL CANCEL;' and then input 'cancel' to end the recover processes. Only after that, you can open the database by using 'alter database open resetlogs;'.

Alex zeng |Skype me: hans9zeng

Report message to a moderator

Re: recovering after OPEN RESETLOGS [message #169808 is a reply to message #166910]

Fri, 28 April 2006 15:31

deesto
Messages: 36
Registered: March 2005
Location: NY, USA

Member

Thanks Alex!

I was able to run through that procedure once, on a new test database.

But then someone else tried to run through it in their test environment, and they got errors when trying to execute the RECOVER DATABASE USING BACKUP CONTROLFILE command:
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database

The documentation I read said that if these errors occurred, you must restore the database and then perform an incomplete recovery. I did this, and I was able to "fix" the database and get it running again... but when I tried to perform this procedure afterwards, I got the same errors in the same place in the procedure (at RECOVER DATABASE USING BACKUP CONTROLFILE).

Flashback Database is on, but I keep getting this error. Any ideas how I can get past this?

Thank you,
John

[Updated on: Fri, 28 April 2006 15:32]

Report message to a moderator

Re: recovering after OPEN RESETLOGS [message #169894 is a reply to message #169808]

Sun, 30 April 2006 03:12

lakher
Messages: 14
Registered: April 2006

Junior Member

deesto wrote on Sat, 29 April 2006 04:31

1、shutdown immediate
2、startup mount
3、alter database flashback off；

Report message to a moderator

Re: recovering after OPEN RESETLOGS [message #169989 is a reply to message #169894]

Mon, 01 May 2006 09:35

deesto
Messages: 36
Registered: March 2005
Location: NY, USA

Member

Yes; thank you! I worked this out Friday night, and turning Flashback Database off was the key. But I should also point out that it needs to be turned off before each attempt to recover the database, since it seems persistent and I got the same error when I turned it off once and proceeded through the procedure below without turning it off just before each database recovery section.

Report message to a moderator

Previous Topic:	Recovering Database
Next Topic:	Export backup Error as sys user in Batch file execution

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 07:29:12 CDT 2024