OraFAQ Forum: Backup & Recovery

Home » RDBMS Server » Backup & Recovery » ORA-01195 (Oracle 10g R2, AIX 5.3)

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

ORA-01195 [message #482140]

Wed, 10 November 2010 00:30

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

I tried the ff. to recover our production DB...

"SQL> startup mount
ORACLE instance started.

Total System Global Area 9110028288 bytes
Fixed Size 2085976 bytes
Variable Size 4563405736 bytes
Database Buffers 4529848320 bytes
Redo Buffers 14688256 bytes
Database mounted.
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
ORA-00279: change 73037821 generated at 11/10/2010 13:14:08 needed for thread 1
ORA-00289: suggestion : /oracle/PRD/oraarch/PRDarch1_5543_659925240.dbf
ORA-00280: change 73037821 for thread 1 is in sequence #5543

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00328: archived log ends at change 72931009, need later change 73037821
ORA-00334: archived log: '/oracle/PRD/oraarch/PRDarch1_5543_659925240.dbf'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 11 needs more recovery to be consistent
ORA-01110: data file 11: '/oracle/PRD/sapdata2/sr3_8/sr3.data8'

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01195: online backup of file 11 needs more recovery to be consistent
ORA-01110: data file 11: '/oracle/PRD/sapdata2/sr3_8/sr3.data8'

SQL>"

... and I don't know what to do next.

Pls. assist me, thank you.

Report message to a moderator

Re: ORA-01195 [message #482141 is a reply to message #482140]

Wed, 10 November 2010 00:37

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Provide the log Oracle asks you.

Regards
Michel

Report message to a moderator

Re: ORA-01195 [message #482143 is a reply to message #482141]

Wed, 10 November 2010 00:58

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Starting ORACLE instance (normal)
Wed Nov 10 13:12:22 2010
Specified value of sga_max_size is too small, bumping to 9110028288
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.2.0.
System parameters with non-default values:
processes = 80
sessions = 96
event = 10191 trace name context forever, level 1
sga_max_size = 9110028288
shared_pool_size = 4529848320
shared_pool_reserved_size= 452984832
filesystemio_options = setall
control_files = /oracle/PRD/origlogA/cntrl/cntlrPRD.dbf, /oracle/PRD/origlogB/cntrl/cntrlPRD.dbf, /oracle/PRD/sapdata1/cntrl/cntrlPRD.dbf
control_file_record_keep_time= 30
db_block_size = 8192
db_cache_size = 4529848320
compatible = 10.2.0
log_archive_dest = /oracle/PRD/oraarch/PRDarch
log_buffer = 14242816
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 = /oracle/PRD/saptrace/background
user_dump_dest = /oracle/PRD/saptrace/usertrace
core_dump_dest = /oracle/PRD/saptrace/background
optimizer_features_enable= 10.2.0.1
sort_area_size = 2097152
sort_area_retained_size = 0
db_name = PRD
open_cursors = 800
_optim_peek_user_binds = FALSE
pga_aggregate_target = 6039797760
workarea_size_policy = AUTO
statistics_level = typical
PMON started with pid=2, OS id=639224
PSP0 started with pid=3, OS id=745580
MMAN started with pid=4, OS id=651294
DBW0 started with pid=5, OS id=557164
LGWR started with pid=6, OS id=217312
CKPT started with pid=7, OS id=634984
SMON started with pid=8, OS id=495748
RECO started with pid=9, OS id=245768
CJQ0 started with pid=10, OS id=569382
MMON started with pid=11, OS id=630834
MMNL started with pid=12, OS id=581668
Wed Nov 10 13:12:22 2010
ALTER DATABASE MOUNT
Wed Nov 10 13:12:27 2010
Setting recovery target incarnation to 1
Wed Nov 10 13:12:27 2010
Successful mount of redo thread 1, with mount id 1798375222
Wed Nov 10 13:12:27 2010
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Nov 10 13:12:27 2010
ALTER DATABASE OPEN
Wed Nov 10 13:12:27 2010
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=14, OS id=241770
Wed Nov 10 13:12:27 2010
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=15, OS id=659498
Wed Nov 10 13:12:27 2010
Thread 1 opened at log sequence 5543
Current log# 3 seq# 5543 mem# 0: /oracle/PRD/origlogA/log_g13m1.dbf
Current log# 3 seq# 5543 mem# 1: /oracle/PRD/mirrlogA/log_g13m2.dbf
Successful open of redo thread 1
Wed Nov 10 13:12:27 2010
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Nov 10 13:12:27 2010
ARC1: STARTING ARCH PROCESSES
Wed Nov 10 13:12:27 2010
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Wed Nov 10 13:12:27 2010
SMON: enabling cache recovery
Wed Nov 10 13:12:27 2010
ARC2: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
ARC1: Becoming the heartbeat ARCH
ARC2 started with pid=16, OS id=356586
Wed Nov 10 13:12:27 2010
Successfully onlined Undo Tablespace 1.
Wed Nov 10 13:12:27 2010
SMON: enabling tx recovery
Wed Nov 10 13:12:27 2010
Database Characterset is UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Wed Nov 10 13:12:28 2010
Incremental checkpoint up to RBA [0x15a7.6580.0], current log tail at RBA [0x15a7.65ab.0]
Wed Nov 10 13:12:28 2010
Starting background process QMNC
QMNC started with pid=18, OS id=528564
Wed Nov 10 13:12:29 2010
Completed: ALTER DATABASE OPEN
Wed Nov 10 13:13:27 2010
Shutting down archive processes
Wed Nov 10 13:13:32 2010
ARCH shutting down
ARC2: Archival stopped
Wed Nov 10 13:13:41 2010
alter database datafile '/oracle/PRD/sapdata2/sr3_8/sr3.data8' online
Wed Nov 10 13:13:41 2010
ORA-1113 signalled during: alter database datafile '/oracle/PRD/sapdata2/sr3_8/sr3.data8' online...
Wed Nov 10 13:14:03 2010
Shutting down instance: further logons disabled
Wed Nov 10 13:14:03 2010
Stopping background process QMNC
Wed Nov 10 13:14:03 2010
Stopping background process CJQ0
Wed Nov 10 13:14:05 2010
Stopping background process MMNL
Wed Nov 10 13:14:06 2010
Stopping background process MMON
Wed Nov 10 13:14:07 2010
Shutting down instance (normal)
License high water mark = 1
Wed Nov 10 13:14:07 2010
Stopping Job queue slave processes
Wed Nov 10 13:14:07 2010
Job queue slave processes stopped
Wed Nov 10 13:14:07 2010
ALTER DATABASE CLOSE NORMAL
Wed Nov 10 13:14:08 2010
SMON: disabling tx recovery
SMON: disabling cache recovery
Wed Nov 10 13:14:08 2010
Shutting down archive processes
Archiving is disabled
Wed Nov 10 13:14:13 2010
ARCH shutting down
ARC1: Archival stopped
Wed Nov 10 13:14:18 2010
ARCH shutting down
ARC0: Archival stopped
Wed Nov 10 13:14:20 2010
Thread 1 closed at log sequence 5543
Successful close of redo thread 1
Wed Nov 10 13:14:20 2010
Completed: ALTER DATABASE CLOSE NORMAL
Wed Nov 10 13:14:20 2010
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1090
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1090
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Wed Nov 10 13:15:52 2010
Starting ORACLE instance (normal)
Wed Nov 10 13:15:52 2010
Specified value of sga_max_size is too small, bumping to 9110028288
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.2.0.
System parameters with non-default values:
processes = 80
sessions = 96
event = 10191 trace name context forever, level 1
sga_max_size = 9110028288
shared_pool_size = 4529848320
shared_pool_reserved_size= 452984832
filesystemio_options = setall
control_files = /oracle/PRD/origlogA/cntrl/cntlrPRD.dbf, /oracle/PRD/origlogB/cntrl/cntrlPRD.dbf, /oracle/PRD/sapdata1/cntrl/cntrlPRD.dbf
control_file_record_keep_time= 30
db_block_size = 8192
db_cache_size = 4529848320
compatible = 10.2.0
log_archive_dest = /oracle/PRD/oraarch/PRDarch
log_buffer = 14242816
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 = /oracle/PRD/saptrace/background
user_dump_dest = /oracle/PRD/saptrace/usertrace
core_dump_dest = /oracle/PRD/saptrace/background
optimizer_features_enable= 10.2.0.1
sort_area_size = 2097152
sort_area_retained_size = 0
db_name = PRD
open_cursors = 800
_optim_peek_user_binds = FALSE
pga_aggregate_target = 6039797760
workarea_size_policy = AUTO
statistics_level = typical
PMON started with pid=2, OS id=651300
PSP0 started with pid=3, OS id=495750
MMAN started with pid=4, OS id=245770
DBW0 started with pid=5, OS id=217314
LGWR started with pid=6, OS id=639226
CKPT started with pid=7, OS id=659500
SMON started with pid=8, OS id=745590
RECO started with pid=9, OS id=241772
CJQ0 started with pid=10, OS id=737466
MMON started with pid=11, OS id=630836
MMNL started with pid=12, OS id=581670
Wed Nov 10 13:15:53 2010
ALTER DATABASE MOUNT
Wed Nov 10 13:15:57 2010
Setting recovery target incarnation to 1
Wed Nov 10 13:15:57 2010
Successful mount of redo thread 1, with mount id 1798330889
Wed Nov 10 13:15:57 2010
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Nov 10 13:18:00 2010
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
Wed Nov 10 13:18:00 2010
Media Recovery Start
parallel recovery started with 7 processes
ORA-279 signalled during: ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE ...
Wed Nov 10 13:18:58 2010
ALTER DATABASE RECOVER CONTINUE DEFAULT
Wed Nov 10 13:18:58 2010
Media Recovery Log /oracle/PRD/oraarch/PRDarch1_5543_659925240.dbf
Errors with log /oracle/PRD/oraarch/PRDarch1_5543_659925240.dbf
ORA-328 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Wed Nov 10 13:18:58 2010
ALTER DATABASE RECOVER CANCEL
ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL ...
Wed Nov 10 13:21:15 2010
ALTER DATABASE OPEN RESETLOGS
Wed Nov 10 13:21:15 2010
ORA-1195 signalled during: ALTER DATABASE OPEN RESETLOGS...
Wed Nov 10 13:21:44 2010
ALTER DATABASE OPEN RESETLOGS
Wed Nov 10 13:21:44 2010
ORA-1195 signalled during: ALTER DATABASE OPEN RESETLOGS...
Wed Nov 10 13:57:33 2010
Adjusting the default value of parameter parallel_max_servers
from 160 to 65 due to the value of parameter processes (80)
Wed Nov 10 13:57:33 2010
Starting ORACLE instance (normal)
Shutting down instance: further logons disabled
Wed Nov 10 13:57:40 2010
Stopping background process CJQ0
Wed Nov 10 13:57:40 2010
Stopping background process MMNL
Wed Nov 10 13:57:41 2010
Stopping background process MMON
Wed Nov 10 13:57:42 2010
Shutting down instance (immediate)
License high water mark = 8
Wed Nov 10 13:57:42 2010
Stopping Job queue slave processes
Wed Nov 10 13:57:42 2010
Job queue slave processes stopped
Wed Nov 10 13:57:47 2010
Process OS id : 680132 alive after kill
Errors in file
Wed Nov 10 13:57:47 2010
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Wed Nov 10 13:57:47 2010
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Wed Nov 10 13:57:54 2010
Starting ORACLE instance (normal)
Wed Nov 10 13:57:54 2010
Specified value of sga_max_size is too small, bumping to 9110028288
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.2.0.
System parameters with non-default values:
processes = 80
sessions = 96
event = 10191 trace name context forever, level 1
sga_max_size = 9110028288
shared_pool_size = 4529848320
shared_pool_reserved_size= 452984832
filesystemio_options = setall
control_files = /oracle/PRD/origlogA/cntrl/cntlrPRD.dbf, /oracle/PRD/origlogB/cntrl/cntrlPRD.dbf, /oracle/PRD/sapdata1/cntrl/cntrlPRD.dbf
control_file_record_keep_time= 30
db_block_size = 8192
db_cache_size = 4529848320
compatible = 10.2.0
log_archive_dest = /oracle/PRD/oraarch/PRDarch
log_buffer = 14242816
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 = /oracle/PRD/saptrace/background
user_dump_dest = /oracle/PRD/saptrace/usertrace
core_dump_dest = /oracle/PRD/saptrace/background
optimizer_features_enable= 10.2.0.1
sort_area_size = 2097152
sort_area_retained_size = 0
db_name = PRD
open_cursors = 800
_optim_peek_user_binds = FALSE
pga_aggregate_target = 6039797760
workarea_size_policy = AUTO
statistics_level = typical
PMON started with pid=2, OS id=495752
PSP0 started with pid=3, OS id=245772
MMAN started with pid=4, OS id=217316
DBW0 started with pid=5, OS id=651302
LGWR started with pid=6, OS id=737468
CKPT started with pid=7, OS id=680134
SMON started with pid=8, OS id=630838
RECO started with pid=9, OS id=581672
CJQ0 started with pid=10, OS id=327922
MMON started with pid=11, OS id=557244
MMNL started with pid=12, OS id=520320
Wed Nov 10 13:57:55 2010
ALTER DATABASE MOUNT
Wed Nov 10 13:57:59 2010
Setting recovery target incarnation to 1
Wed Nov 10 13:57:59 2010
Successful mount of redo thread 1, with mount id 1798327267
Wed Nov 10 13:57:59 2010
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Wed Nov 10 13:57:59 2010
ALTER DATABASE OPEN
ORA-1589 signalled during: ALTER DATABASE OPEN...
Wed Nov 10 13:59:07 2010
alter database open resetlogs
Wed Nov 10 13:59:07 2010
ORA-1195 signalled during: alter database open resetlogs...
Wed Nov 10 13:59:22 2010
Shutting down instance: further logons disabled
Wed Nov 10 13:59:22 2010
Stopping background process MMNL
Wed Nov 10 13:59:22 2010
Stopping background process CJQ0
Wed Nov 10 13:59:24 2010
Stopping background process MMON
Wed Nov 10 13:59:25 2010
Shutting down instance (normal)
License high water mark = 1
Wed Nov 10 13:59:25 2010
Stopping Job queue slave processes
Wed Nov 10 13:59:25 2010
Job queue slave processes stopped
Wed Nov 10 13:59:25 2010
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Wed Nov 10 13:59:25 2010
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1090
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1090
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active

Report message to a moderator

Re: ORA-01195 [message #482147 is a reply to message #482143]

Wed, 10 November 2010 01:12

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You didn't understand my answer.

Quote:

ORA-00279: change 73037821 generated at 11/10/2010 13:14:08 needed for thread 1
ORA-00289: suggestion : /oracle/PRD/oraarch/PRDarch1_5543_659925240.dbf
ORA-00280: change 73037821 for thread 1 is in sequence #5543

Provide this file to Oracle recovery session (not here).

Regards
Michel

Report message to a moderator

Previous Topic:	ORA-01200 resolution
Next Topic:	RMAN-06023: no backup or copy of datafile 4 found to restore

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 13:01:26 CST 2024