Problem opening database after successfull recover...

Former Member · ‎10-12-2008

Hi All,

Can any one please help me with below error.

I am doing system copy from Source_SID to Target_SID taking online backup and generated offline redo log files during start of backup to end.

We are on Windows 2003 server, DB is Oracle 10g and system is SRM 5.0. I was trying system copy backup/restore for DB and export/import for java. I have got online consistent backup along with offline redo logs of source system. Also have taken CI export.

Here the file system directories in TARGET_SID and SOURCE_SID are different. First i have restored the online backup of Source_SID in Target_SID system (RYV). Took the trace file of Source_SID system and modified accordingly the filesystem structure directories of the TARGET SySTEM. Also renamed the TARGET_SID to SOURCE_SID.

Now started it in nomount state with below command.

SQL> STARTUP NOMOUNT PFILE=E:\ORACLE\RYV\102\database\initRYV.ora

ORACLE instance started.

Total System Global Area 2634022912 bytes

Fixed Size 2203184 bytes

Variable Size 1337811408 bytes

Database Buffers 1291845632 bytes

Redo Buffers 2162688 bytes

Generated the modifiled trace file script (renamed it as controlfile.sql).

SQL> @Controlfile.sql

Control file created.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

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

ORA-01110: data file 1: 'K:\ORACLE\RYV\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

Before executing the command i have places all the offline redo log files which were generated in source system during the online backup. During online backup, offline redo logs 04729 to 04732 were generated. Now i have issued below command.

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

alter database recover automatic using backup controlfile until cancel

*

ERROR at line 1:

ORA-00279: change 112926870 generated at 09/29/2008 17:26:55 needed for thread

1

ORA-00289: suggestion : F:\ORACLE\RYV\ORAARCH\RYVARCHARC04733_0649429988.001

ORA-00280: change 112926870 for thread 1 is in sequence #4733

ORA-00278: log file 'F:\ORACLE\RYV\ORAARCH\RYVARCHARC04733_0649429988.001' no

longer needed for this recovery

ORA-00308: cannot open archived log

'F:\ORACLE\RYV\ORAARCH\RYVARCHARC04733_0649429988.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

Offline Redo log files from 04729 to 04732 were applied and it was trying for 04733 which was not there. Now issued below command.

SQL> recover cancel;

Media recovery complete.

Finally tried to open database with resetlogs and here i am getting problem.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL>

Please suggest whether is there different procedure for system copy using backup/restore if file structure is different for Source_SID and Target_SID.

Thanking you for your help.

Regards

Ravi

Former Member · ‎10-14-2008

Hi All,

Thanks for your valuable inputs . I have checked the oracle version & patch level and there is difference in patch level. I will be patching the target_sid to same level that of source_sid.

Thank you sebastien. I have rewarded the points.

Former Member · ‎10-12-2008

Alert Log Details:

Sun Oct 12 08:42:37 2008

Starting ORACLE instance (normal)

Sun Oct 12 08:42:37 2008

Specified value of sga_max_size is too small, bumping to 2634022912

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

IMODE=BR

ILAT =10

LICENSE_MAX_USERS = 0

SYS auditing is disabled

ksdpec: called for event 13740 prior to event group initialization

Starting up ORACLE RDBMS Version: 10.2.0.1.0.

System parameters with non-default values:

processes = 80

sessions = 96

event = 10191 trace name context forever, level 1

sga_max_size = 2634022912

shared_pool_size = 1291845632

shared_pool_reserved_size= 127512084

filesystemio_options = setall

control_files = G:\ORACLE\RYV\ORIGLOGA\CNTRL\CNTLRRYV.DBF, F:\ORACLE\RYV\ORAARCH\CNTRL\CNTRLRYV.DBF, K:\ORACLE\RYV\SAPDATA1\CNTRL\CNTRLRYV.DBF

control_file_record_keep_time= 30

db_block_size = 8192

db_cache_size = 1291845632

compatible = 10.2.0

log_archive_dest = F:\oracle\RYV\oraarch\RYVarch

log_buffer = 2097152

log_checkpoint_interval = 0

db_files = 254

log_checkpoints_to_alert = TRUE

dml_locks = 4000

undo_management = AUTO

undo_tablespace = PSAPUNDO

undo_retention = 43200

recyclebin = off

remote_os_authent = TRUE

remote_login_passwordfile= EXCLUSIVE

job_queue_processes = 1

background_dump_dest = K:\ORACLE\RYV\SAPTRACE\BACKGROUND

user_dump_dest = K:\ORACLE\RYV\SAPTRACE\USERTRACE

core_dump_dest = K:\ORACLE\RYV\SAPTRACE\BACKGROUND

optimizer_features_enable= 10.2.0.1

sort_area_size = 2097152

sort_area_retained_size = 0

db_name = RYV

open_cursors = 800

optimpeek_user_binds = FALSE

pga_aggregate_target = 629145600

workarea_size_policy = AUTO

statistics_level = typical

PMON started with pid=2, OS id=4600

PSP0 started with pid=6, OS id=5228

MMAN started with pid=10, OS id=5664

DBW0 started with pid=14, OS id=4648

DBW1 started with pid=18, OS id=5588

DBW2 started with pid=22, OS id=4280

DBW3 started with pid=26, OS id=648

LGWR started with pid=30, OS id=4780

CKPT started with pid=34, OS id=1348

SMON started with pid=38, OS id=6024

RECO started with pid=42, OS id=5208

CJQ0 started with pid=46, OS id=1332

MMON started with pid=50, OS id=1304

MMNL started with pid=54, OS id=2736

Sun Oct 12 08:42:44 2008

CREATE CONTROLFILE REUSE SET DATABASE "RYV" RESETLOGS ARCHIVELOG

MAXLOGFILES 255

MAXLOGMEMBERS 5

MAXDATAFILES 5000

MAXINSTANCES 50

MAXLOGHISTORY 2629

LOGFILE

GROUP 1 (

'G:\ORACLE\RYV\ORIGLOGA\LOG_G11M1.DBF',

'H:\ORACLE\RYV\MIRRLOGA\LOG_G11M2.DBF'

) SIZE 90M,

GROUP 2 (

'G:\ORACLE\RYV\ORIGLOGB\LOG_G12M1.DBF',

'H:\ORACLE\RYV\MIRRLOGB\LOG_G12M2.DBF'

) SIZE 90M,

GROUP 3 (

'G:\ORACLE\RYV\ORIGLOGA\LOG_G13M1.DBF',

'H:\ORACLE\RYV\MIRRLOGA\LOG_G13M2.DBF'

) SIZE 90M,

GROUP 4 (

'G:\ORACLE\RYV\ORIGLOGB\LOG_G14M1.DBF',

'H:\ORACLE\RYV\MIRRLOGB\LOG_G14M2.DBF'

) SIZE 90M,

GROUP 5 (

'G:\ORACLE\RYV\ORIGLOGA\LOG_G15M1.DBF',

'H:\ORACLE\RYV\MIRRLOGA\LOG_G15M2.DBF'

) SIZE 90M,

GROUP 6 (

'G:\ORACLE\RYV\ORIGLOGB\LOG_G16M1.DBF',

'H:\ORACLE\RYV\MIRRLOGB\LOG_G16M2.DBF'

) SIZE 90M,

GROUP 7 (

'G:\ORACLE\RYV\ORIGLOGA\LOG_G17M1.DBF',

'H:\ORACLE\RYV\MIRRLOGA\LOG_G17M2.DBF'

) SIZE 90M,

GROUP 8 (

'G:\ORACLE\RYV\ORIGLOGB\LOG_G18M1.DBF',

'H:\ORACLE\RYV\MIRRLOGB\LOG_G18M2.DBF'

) SIZE 90M

-- STANDBY LOGFILE

DATAFILE

'K:\ORACLE\RYV\SAPDATA1\SYSTEM_1\SYSTEM.DATA1',

'J:\ORACLE\RYV\SAPDATA3\UNDO_1\UNDO.DATA1',

'K:\ORACLE\RYV\SAPDATA4\SYSAUX_1\SYSAUX.DATA1',

'K:\ORACLE\RYV\SAPDATA1\SR3_1\SR3.DATA1',

'K:\ORACLE\RYV\SAPDATA1\SR3_2\SR3.DATA2',

'K:\ORACLE\RYV\SAPDATA1\SR3_3\SR3.DATA3',

'K:\ORACLE\RYV\SAPDATA1\SR3_4\SR3.DATA4',

'K:\ORACLE\RYV\SAPDATA1\SR3_5\SR3.DATA5',

'K:\ORACLE\RYV\SAPDATA1\SR3_6\SR3.DATA6',

'K:\ORACLE\RYV\SAPDATA1\SR3700_1\SR3700.DATA1',

'K:\ORACLE\RYV\SAPDATA1\SR3700_2\SR3700.DATA2',

'K:\ORACLE\RYV\SAPDATA1\SR3700_3\SR3700.DATA3',

'K:\ORACLE\RYV\SAPDATA1\SR3700_4\SR3700.DATA4',

'K:\ORACLE\RYV\SAPDATA1\SR3USR_1\SR3USR.DATA1',

'K:\ORACLE\RYV\SAPDATA1\SR3DB_1\SR3DB.DATA1',

'K:\ORACLE\RYV\SAPDATA1\SR3DB_2\SR3DB.DATA2',

'K:\ORACLE\RYV\SAPDATA1\SR3DB_3\SR3DB.DATA3'

CHARACTER SET UTF8

Sun Oct 12 08:42:44 2008

WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command

Default Temporary Tablespace will be necessary for a locally managed database in future release

Setting recovery target incarnation to 1

Sun Oct 12 08:42:45 2008

Successful mount of redo thread 1, with mount id 157697668

Sun Oct 12 08:42:45 2008

Completed: CREATE CONTROLFILE REUSE SET DATABASE "RYV" RESETLOGS ARCHIVELOG

MAXLOGFILES 255

MAXLOGMEMBERS 5

MAXDATAFILES 5000

MAXINSTANCES 50

MAXLOGHISTORY 2629

LOGFILE

GROUP 1 (

'G:\ORACLE\RYV\ORIGLOGA\LOG_G11M1.DBF',

'H:\ORACLE\RYV\MIRRLOGA\LOG_G11M2.DBF'

) SIZE 90M,

GROUP 2 (

'G:\ORACLE\RYV\ORIGLOGB\LOG_G12M1.DBF',

'H:\ORACLE\RYV\MIRRLOGB\LOG_G12M2.DBF'

) SIZE 90M,

GROUP 3 (

'G:\ORACLE\RYV\ORIGLOGA\LOG_G13M1.DBF',

'H:\ORACLE\RYV\MIRRLOGA\LOG_G13M2.DBF'

) SIZE 90M,

GROUP 4 (

'G:\ORACLE\RYV\ORIGLOGB\LOG_G14M1.DBF',

'H:\ORACLE\RYV\MIRRLOGB\LOG_G14M2.DBF'

) SIZE 90M,

GROUP 5 (

'G:\ORACLE\RYV\ORIGLOGA\LOG_G15M1.DBF',

'H:\ORACLE\RYV\MIRRLOGA\LOG_G15M2.DBF'

) SIZE 90M,

GROUP 6 (

'G:\ORACLE\RYV\ORIGLOGB\LOG_G16M1.DBF',

'H:\ORACLE\RYV\MIRRLOGB\LOG_G16M2.DBF'

) SIZE 90M,

GROUP 7 (

'G:\ORACLE\RYV\ORIGLOGA\LOG_G17M1.DBF',

'H:\ORACLE\RYV\MIRRLOGA\LOG_G17M2.DBF'

) SIZE 90M,

GROUP 8 (

'G:\ORACLE\RYV\ORIGLOGB\LOG_G18M1.DBF',

'H:\ORACLE\RYV\MIRRLOGB\LOG_G18M2.DBF'

) SIZE 90M

-- STANDBY LOGFILE

DATAFILE

'K:\ORACLE\RYV\SAPDATA1\SYSTEM_1\SYSTEM.DATA1',

'J:\ORACLE\RYV\SAPDATA3\UNDO_1\UNDO.DATA1',

'K:\ORACLE\RYV\SAPDATA4\SYSAUX_1\SYSAUX.DATA1',

'K:\ORACLE\RYV\SAPDATA1\SR3_1\SR3.DATA1',

'K:\ORACLE\RYV\SAPDATA1\SR3_2\SR3.DATA2',

'K:\ORACLE\RYV\SAPDATA1\SR3_3\SR3.DATA3',

'K:\ORACLE\RYV\SAPDATA1\SR3_4\SR3.DATA4',

'K:\ORACLE\RYV\SAPDATA1\SR3_5\SR3.DATA5',

'K:\ORACLE\RYV\SAPDATA1\SR3_6\SR3.DATA6',

'K:\ORACLE\RYV\SAPDATA1\SR3700_1\SR3700.DATA1',

'K:\ORACLE\RYV\SAPDATA1\SR3700_2\SR3700.DATA2',

'K:\ORACLE\RYV\SAPDATA1\SR3700_3\SR3700.DATA3',

'K:\ORACLE\RYV\SAPDATA1\SR3700_4\SR3700.DATA4',

'K:\ORACLE\RYV\SAPDATA1\SR3USR_1\SR3USR.DATA1',

'K:\ORACLE\RYV\SAPDATA1\SR3DB_1\SR3DB.DATA1',

'K:\ORACLE\RYV\SAPDATA1\SR3DB_2\SR3DB.DATA2',

'K:\ORACLE\RYV\SAPDATA1\SR3DB_3\SR3DB.DATA3'

CHARACTER SET UTF8

Sun Oct 12 08:43:43 2008

alter database open

Sun Oct 12 08:43:43 2008

ORA-1589 signalled during: alter database open...

Sun Oct 12 08:44:03 2008

alter database open resetlogs

Sun Oct 12 08:44:03 2008

ORA-1195 signalled during: alter database open resetlogs...

Sun Oct 12 08:44:42 2008

alter database recover automatic using backup controlfile until cancel

Sun Oct 12 08:44:42 2008

Media Recovery Start

parallel recovery started with 3 processes

Sun Oct 12 08:44:43 2008

Media Recovery Log F:\ORACLE\RYV\ORAARCH\RYVARCHARC04729_0649429988.001

Sun Oct 12 08:44:45 2008

Media Recovery Log F:\ORACLE\RYV\ORAARCH\RYVARCHARC04730_0649429988.001

Sun Oct 12 08:44:46 2008

Media Recovery Log F:\ORACLE\RYV\ORAARCH\RYVARCHARC04731_0649429988.001

Sun Oct 12 08:44:46 2008

Media Recovery Log F:\ORACLE\RYV\ORAARCH\RYVARCHARC04732_0649429988.001

Sun Oct 12 08:44:46 2008

Media Recovery Log F:\ORACLE\RYV\ORAARCH\RYVARCHARC04733_0649429988.001

Errors with log F:\ORACLE\RYV\ORAARCH\RYVARCHARC04733_0649429988.001

ORA-279 signalled during: alter database recover automatic using backup controlfile until cancel...

Sun Oct 12 08:45:08 2008

ALTER DATABASE RECOVER cancel

Sun Oct 12 08:45:11 2008

Media Recovery Canceled

Completed: ALTER DATABASE RECOVER cancel

Sun Oct 12 08:45:32 2008

alter database open resetlogs

Sun Oct 12 08:45:33 2008

ARCH: Logfile 8 is wrong incarnation (0:112926871:10/10/2008 14:52:35 vs 0:1:03/15/2008 13:13:08)

RESETLOGS after incomplete recovery UNTIL CHANGE 112926870

Resetting resetlogs activation ID 2278245220 (0x87cb4364)

Online log G:\ORACLE\RYV\ORIGLOGA\LOG_G11M1.DBF: Thread 1 Group 1 was previously cleared

Online log H:\ORACLE\RYV\MIRRLOGA\LOG_G11M2.DBF: Thread 1 Group 1 was previously cleared

Online log G:\ORACLE\RYV\ORIGLOGB\LOG_G12M1.DBF: Thread 1 Group 2 was previously cleared

Online log H:\ORACLE\RYV\MIRRLOGB\LOG_G12M2.DBF: Thread 1 Group 2 was previously cleared

Online log G:\ORACLE\RYV\ORIGLOGA\LOG_G13M1.DBF: Thread 1 Group 3 was previously cleared

Online log H:\ORACLE\RYV\MIRRLOGA\LOG_G13M2.DBF: Thread 1 Group 3 was previously cleared

Online log G:\ORACLE\RYV\ORIGLOGB\LOG_G14M1.DBF: Thread 1 Group 4 was previously cleared

Online log H:\ORACLE\RYV\MIRRLOGB\LOG_G14M2.DBF: Thread 1 Group 4 was previously cleared

Online log G:\ORACLE\RYV\ORIGLOGA\LOG_G15M1.DBF: Thread 1 Group 5 was previously cleared

Online log H:\ORACLE\RYV\MIRRLOGA\LOG_G15M2.DBF: Thread 1 Group 5 was previously cleared

Online log G:\ORACLE\RYV\ORIGLOGB\LOG_G16M1.DBF: Thread 1 Group 6 was previously cleared

Online log H:\ORACLE\RYV\MIRRLOGB\LOG_G16M2.DBF: Thread 1 Group 6 was previously cleared

Online log G:\ORACLE\RYV\ORIGLOGA\LOG_G17M1.DBF: Thread 1 Group 7 was previously cleared

Online log H:\ORACLE\RYV\MIRRLOGA\LOG_G17M2.DBF: Thread 1 Group 7 was previously cleared

Sun Oct 12 08:45:34 2008

Setting recovery target incarnation to 2

Sun Oct 12 08:45:34 2008

Assigning activation ID 157697668 (0x9664684)

LGWR: STARTING ARCH PROCESSES

ARC0 started with pid=74, OS id=2036

Sun Oct 12 08:45:35 2008

ARC0: Archival started

Sun Oct 12 08:45:35 2008

ARC1: Archival started

LGWR: STARTING ARCH PROCESSES COMPLETE

Thread 1 opened at log sequence 1

Current log# 8 seq# 1 mem# 0: G:\ORACLE\RYV\ORIGLOGB\LOG_G18M1.DBF

Current log# 8 seq# 1 mem# 1: H:\ORACLE\RYV\MIRRLOGB\LOG_G18M2.DBF

Sun Oct 12 08:45:35 2008

Successful open of redo thread 1

Sun Oct 12 08:45:35 2008

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

ARC1 started with pid=3, OS id=1640

Sun Oct 12 08:45:36 2008

ARC0: STARTING ARCH PROCESSES

Sun Oct 12 08:45:36 2008

SMON: enabling cache recovery

Sun Oct 12 08:45:36 2008

ARC1: Becoming the 'no FAL' ARCH

ARC1: Becoming the 'no SRL' ARCH

Sun Oct 12 08:45:36 2008

ARC2: Archival started

Sun Oct 12 08:45:36 2008

ARC0: STARTING ARCH PROCESSES COMPLETE

ARC0: Becoming the heartbeat ARCH

Sun Oct 12 08:45:36 2008

Errors in file k:\oracle\ryv\saptrace\usertrace\ryv_ora_868.trc:

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Sun Oct 12 08:45:36 2008

Error 704 happened during db open, shutting down database

USER: terminating instance due to error 704

Sun Oct 12 08:45:36 2008

Errors in file k:\oracle\ryv\saptrace\background\ryv_pmon_4600.trc:

ORA-00704: bootstrap process failure

ARC2 started with pid=4, OS id=5996

Sun Oct 12 08:45:37 2008

Errors in file k:\oracle\ryv\saptrace\background\ryv_reco_5208.trc:

ORA-00704: bootstrap process failure

Sun Oct 12 08:45:37 2008

Errors in file k:\oracle\ryv\saptrace\background\ryv_ckpt_1348.trc:

ORA-00704: bootstrap process failure

Sun Oct 12 08:45:37 2008

Errors in file k:\oracle\ryv\saptrace\background\ryv_lgwr_4780.trc:

ORA-00704: bootstrap process failure

Sun Oct 12 08:45:40 2008

Errors in file k:\oracle\ryv\saptrace\background\ryv_dbw2_4280.trc:

ORA-00704: bootstrap process failure

Sun Oct 12 08:45:40 2008

Errors in file k:\oracle\ryv\saptrace\background\ryv_dbw3_648.trc:

ORA-00704: bootstrap process failure

Sun Oct 12 08:45:40 2008

Errors in file k:\oracle\ryv\saptrace\background\ryv_dbw0_4648.trc:

ORA-00704: bootstrap process failure

Sun Oct 12 08:45:40 2008

Errors in file k:\oracle\ryv\saptrace\background\ryv_dbw1_5588.trc:

ORA-00704: bootstrap process failure

Sun Oct 12 08:45:40 2008

Errors in file k:\oracle\ryv\saptrace\background\ryv_psp0_5228.trc:

ORA-00704: bootstrap process failure

Sun Oct 12 08:45:41 2008

Errors in file k:\oracle\ryv\saptrace\background\ryv_mman_5664.trc:

ORA-00704: bootstrap process failure

Instance terminated by USER, pid = 868

ORA-1092 signalled during: alter database open resetlogs...

Former Member · ‎10-12-2008

Hi Debasis,

Thanks for the response.

I have restored the online backup. I mean i have copied all the datafiles manually in there respective locations in Target_SID and then started the database in nomount. After that created the control file

Set #2 RESETLOGS case'.

-Changed all values from Source_SID to TARGET_SID.

-Changed the create command to 'CREATE CONTROLFILE REUSE SET DATABASE TARGET_SID RESETLOGS ARCHIVELOG'

-Checked that directory structure matches that available on the target system

-comment out the command 'RECOVER DATABASE USING BACKUP CONTROLFILE'"

-Comment out the command ALTER TABLESPACE PSAPTEMP

Controlfile is created succesfully.

Now i have moved all the archive files to TARGET_SID and issued the recovery command and recovery was done.

When tried to open the database with resetlogs it is still throwing same error

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced.

I tried what you mentioned, but it didn't work out.

debasissahoo · ‎10-12-2008

Hi Ravi,

I believe you've only used brrestore to restore the online backup.

But online backup needs a database recovery after restore. you've not done that, hence you're not able to open the database.

- first you have to restore the offline redo logs to the target system from source database backup. (for successful recovery, at least you need to have redo logs that were generated during the course of online backup, this information you can get from arch<tagertsid>.log)

- once these are available, you can do

SQL> connect / as sysdba
SQL> startup mount
SQL> recover database using backup controlfile until time '2008-MM-DD:hh:mm:ss';

when asked give AUTO.

MM, DD, hh mm ss replace accordingly.

it will automatically read from the restored offline redo logs and do the recovery.

Once recovery is done successfully, then only you can run

SQL> alter database open resetlogs;

Regards,

Debasis.

Edited by: Debasis Sahoo on Oct 12, 2008 4:18 PM

Former Member · ‎10-12-2008

Ravi,

Can you check the controlfile.sql which contains the line create control file..... put norestlog noarchive logs... I think the archieve log is on..

Problem opening database after successfull recovery

Accepted Solutions (0)

Answers (5)

Answers (5)

Re: Lookup logic in Integration suite

Re: CF Deployment Error: Error getting tenant t0

Re: Encountering a script error on the Crystal Rep...

Re: LLM, RAG and Cloud Foundry: No space left on d...

Sending Mail From UI5 Application upon Button clic...