topic Re: ALTER DATABASE OPEN requires RESETLOGS in Operating System - HP-UX

ALTER DATABASE OPEN requires RESETLOGS

Enrico Venturi — Mon, 13 Oct 2003 07:56:40 GMT

Hello colleagues,
I perform an export online of a database, then I perform the corresponding import; see the procedures enclosed.
I startup the database then I try to create a new controlfile by the command:
CREATE CONTROLFILE REUSE SET DATABASE "DBSNML1" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 899
LOGFILE
GROUP 1 (
'/usr/Systems/1354RM_1/databases/dbsnml/data/redo01.log',
'/alcatel/MirrorArea/1354RM_1/snml/mirror/redo01.log'
) SIZE 500K,
GROUP 2 (
'/usr/Systems/1354RM_1/databases/dbsnml/data/redo02.log',
'/alcatel/MirrorArea/1354RM_1/snml/mirror/redo02.log'
) SIZE 500K,
GROUP 3 (
'/usr/Systems/1354RM_1/databases/dbsnml/data/redo03.log',
'/alcatel/MirrorArea/1354RM_1/snml/mirror/redo03.log'
) SIZE 500K
DATAFILE
'/usr/Systems/1354RM_1/databases/dbsnml/data/system01.dbf',
'/usr/Systems/1354RM_1/databases/dbsnml/data/rbs01.dbf',
'/usr/Systems/1354RM_1/databases/dbsnml/data/temp01.dbf',
'/usr/Systems/1354RM_1/databases/dbsnml/data/data01.dbf',
'/usr/Systems/1354RM_1/databases/dbsnml/data/index_t01.dbf',
'/usr/Systems/1354RM_1/databases/dbsnml/data/pm_hist_data01.dbf',
'/usr/Systems/1354RM_1/databases/dbsnml/data/pm_hist_index01.dbf'
CHARACTER SET WE8ISO8859P1
;
When I executethe next command:
ALTER DATABASE OPEN;

the errorcode is:
SVRMGR> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

If I enter the command with "RESETLOGS" option:
SVRMGR> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/usr/Systems/1354RM_1/databases/dbsnml/data/system01.dbf'

otherwise:
SVRMGR> ALTER DATABASE OPEN NORESETLOGS;
ALTER DATABASE OPEN NORESETLOGS
*
ORA-01588: must use RESETLOGS option for database open

Can anyone help me?

thanks a lot
Enrico

Re: ALTER DATABASE OPEN requires RESETLOGS

Massimo Bianchi — Mon, 13 Oct 2003 08:06:05 GMT

You have to use the RESETLOGS because you created the controlfiles with the RESETLOG option.

CREATE CONTROLFILE REUSE SET DATABASE "DBSNML1" RESETLOGS

with this sintax you resetted the information, and you have to re-open the DB.

BTW, why did you re-created the control-file

So,
ALTER DATABASE OPEN NORESETLOGS;

Finally: probably you messed up something, the re-creation being not needed.

If you only closed/opened the instance, you have to start an online recovery, applying the online redo log. Try:

svrmgrl> alter database recover automatic until cancel using backup controlfile;

this should read the online and apply them.
If it fails,
svrmgrl> alter database recover using backup controlfile until cancel;

this will prompt you with the location of files, give it the location on the online.

Have you a good recent backup ?

Massimo

Re: ALTER DATABASE OPEN requires RESETLOGS

Enrico Venturi — Mon, 13 Oct 2003 08:15:46 GMT

Caro compatriota,
ho fatto il giochino di ricreare il controlfile perchÃ© i datafiles che ho portato sulla macchina appartengono ad un'istanza che ha un ORACLE_SID differente ...
in pratica: sto tentando di cambiare nome al DB

ciao
graz

Re: ALTER DATABASE OPEN requires RESETLOGS

Massimo Bianchi — Mon, 13 Oct 2003 08:21:24 GMT

Ciao,
what is making me think is (in english so other people can follow):

ORA-01195: online backup of file 1 needs more recovery to be consistent

looks like the file was taken when in backup mode, but no logs were applied, to change the state.

You cannot do both recovery from online and changing the SID at the same time.

First the recovery
Then the change of the SID.

Did you applied the last online logs ?

Massimo

Re: ALTER DATABASE OPEN requires RESETLOGS

Enrico Venturi — Mon, 13 Oct 2003 08:31:16 GMT

I didn't apply the redo logs
because I cannot start the DB instance after the restore: actually I import tha datafiles on an environment having a different ORACLE_SID ...

Is there any way to backup the datafiles without requiring recovery during the import phase? (I don't want to use the exp/imp utilities!)

regards
Enrico

Re: ALTER DATABASE OPEN requires RESETLOGS

Massimo Bianchi — Mon, 13 Oct 2003 08:38:53 GMT

Normally online logs are applied automatically, but in this case they looks invalid, because you chaged the SID.

Now, what can we do ?

First: aplly manually the logs, like if they were archived log. I did it a couple of time.

Check this:

* goal: How to roll forward the database using a old controlfile, with archivfiles?
* fact: Oracle Server - Enterprise Edition 8.1
* fact: Oracle Server - Enterprise Edition 8

fix:

This test scenario demonstrates how to apply archivelog files to a clean but
old backup.

Let's first shut the database down in it's current situation:

SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

We make a clean cold backup of the database; namely, all files,
controlfiles, datafiles, etc. Then, start the database up again.

Checking the Archive_Dest directory, we see that the last archivelog file for
this test is 'arc84.1'. We'll come back to that later.

SVRMGR> connect internal/password
Connected.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 11907072 bytes
Fixed Size 49152 bytes
Variable Size 10657792 bytes
Database Buffers 1126400 bytes
Redo Buffers 73728 bytes
Database mounted.
Database opened.

As a test we create a new table, insert some rows, and then make some
logswitches:

SVRMGR> create table testtable(a number(2));
Statement processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> insert into testtable values(2);
1 row processed.
SVRMGR> commit;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> alter system switch logfile;
Statement processed.
SVRMGR> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

Again checking the Archive_Dest directory, we now see the last archivelog file
is arc87.1. (Three onwards from the arc84.1 we had previously).

We now intend to restore the backup we made earlier.

The plan is to apply the three new archivelog files, so first we start the
database up again:

SVRMGR> connect internal/password
Connected.
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 11907072 bytes
Fixed Size 49152 bytes
Variable Size 10657792 bytes
Database Buffers 1126400 bytes
Redo Buffers 73728 bytes
Database mounted.

And now we issue the command to start the recovery and we apply all the
archivelog files that are newer than any of the control or datafiles that we
restored.

SVRMGR> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ORA-00279: change 216252 generated at 04/09/99 16:39:56 needed for thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC85.1
ORA-00280: change 216252 for thread 1 is in sequence #85
SVRMGR> alter database recover continue default;
alter database recover continue default
*
ORA-00279: change 216265 generated at 04/09/99 16:45:34 needed for thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC86.1
ORA-00280: change 216265 for thread 1 is in sequence #86
ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC85.1' no longer needed for
this recovery
SVRMGR> alter database recover continue default;
alter database recover continue default
*
ORA-00279: change 216266 generated at 04/09/99 16:45:40 needed for thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC87.1
ORA-00280: change 216266 for thread 1 is in sequence #87
ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC86.1' no longer needed for
this recovery
SVRMGR> alter database recover continue default;
alter database recover continue default
*
ORA-00279: change 216267 generated at 04/09/99 16:46:00 needed for thread 1
ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC88.1
ORA-0028.
0: change 216267 for thread 1 is in sequence #88
ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC87.1' no longer needed for
this recovery
SVRMGR> alter database recover continue default;
alter database recover continue default
*
ORA-00308: cannot open archived log 'D:\ORA80\DATABASE\ARCHIVE\ARC88.1'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

We could have stopped after 87 but there is no harm in continuing until the
system cannot find the required file.

Now we stop the recovery and open the database:

SVRMGR> alter database recover cancel;
alter database recover cancel
Statement processed.
SVRMGR> alter database open resetlogs;
Statement processed.

And, finally, we prove that the new information that was not in the backup
and was only stored in the archivelog files is now recovered:

SVRMGR> select * from testtable;
A
----------
2
2
2
2
2
5 rows selected.
SVRMGR>.

Re: ALTER DATABASE OPEN requires RESETLOGS

Enrico Venturi — Mon, 13 Oct 2003 08:45:42 GMT

Massimo,
my question is:
can I set the source DB in some state so that no recovery is needed during the restore?
I have no problems to shutdown the source DB before starting the on line export ....

Re: ALTER DATABASE OPEN requires RESETLOGS

Massimo Bianchi — Mon, 13 Oct 2003 09:10:58 GMT

I think that i am missing something...

Your spurce DB looks like to be in a "begin backup state".

You should issue the command

alter tablespace end backup;

for each tablespace, to stop the backup process.

The question is, why tablespace are in begin backup mode, may be a running backup or a crashed backup.

With that set of files, you can open the db without the resetlog option, import your data and after change the SID.

Or, change the SID and after import the data.

Are yuo copying the source files with oracle open ? In these second case, you have to do the folowing:

- db MUST be in archivelog mode. necessary
- take a note of the current online line log number

- set each tablespace in begin backup mode
alter tablespace begin backup

- copy the datafiles
for in in $(cat lista_data_files.txt)
do
rcp $i dest_host:/dest_dir/$i
done

- end the backup mode
alter tablespace end backup;
- take note of the current online log.
- copy all the archive log between, and included, the one i made you note down.

- issue a recover with that logs, you will be able to open the destination DB with the RESETLOGS options (needed because we are arriving from a partial recover)

- create a new controfile script
- run the crea_controfile script to change the SID
- do the import

####################### OR #################Ã

if you can close the source db, take an offline copy of it, and then issue the create controlfile script.

#######################################

if in doubt, check

select FILE#, status from v$backup;

If any file is in the "ACTIVE" state, then those files are in begin backup. but why ??

Massim

Re: ALTER DATABASE OPEN requires RESETLOGS

Enrico Venturi — Mon, 13 Oct 2003 09:43:36 GMT

Ok Massimo,
If I just mount the database (no OPEN!) and I copy the datafiles on the new machine, do I still need to recover them?

I understood you suggestions but I'd like to understand if there's a simpler way to achieve the same result

thanks
enrico

Re: ALTER DATABASE OPEN requires RESETLOGS

Massimo Bianchi — Mon, 13 Oct 2003 09:56:25 GMT

"If I just mount the database (no OPEN!) and I copy the datafiles on the new machine, do I still need to recover them?
"

I think yes, because when the db is in mount state the control file are accessed, and the datafile are accessed, only in read-only mode.

If you can keep the db in state mount, why don't you keep it closed and copy them offline ?

Massimo

Re: ALTER DATABASE OPEN requires RESETLOGS

Stan_17 — Mon, 13 Oct 2003 13:47:18 GMT

Hi Enrico,

Massimo has covered pretty much everything. If you planned to mount the database and copy datafiles is almost equivalent of taking a offline (provided you had done a clean shutdown before mounting the database) or cold backup. so you could do it either way. In fact, rman does mount the database for taking offline backups.

-
Stan