Home » Server Options » Data Guard » show database command showing ORA-16766 (10.2.0.3)
show database command showing ORA-16766 [message #420892] Thu, 03 September 2009 02:01 Go to next message
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
Member
Operating system:windows
Database version:10.2.0.3
Primary database=test2
Physical standby database=test2stand

I have create data broker for my physical standby database but when I am trying confirm the successful activation of the primary and standby databases via the SHOW DATABASE command Then it is giving this error.

DGMGRL> show database test2stand

Database
Name: test2stand
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
test2stand

Current status for "test2stand":
Error: ORA-16766: Redo Apply unexpectedly offline


When I am trying to switch over to physical standby database it is giving this error.

DGMGRL> switchover to test2stand
Performing switchover NOW, please wait...
Error: ORA-16608: one or more databases have warnings

Failed.
Unable to switchover, primary database is still "test2"

This is the warning that is in alert log file of standby database 'test2stand'

db_unique_name = test2stand
open_cursors = 300
pga_aggregate_target = 148897792
dg_broker_start = TRUE
Thu Sep 03 11:26:59 2009
WARNING:Shared I/O Pool created with size=0 set size=0
PMON started with pid=2, OS id=2756
PSP0 started with pid=3, OS id=436
MMAN started with pid=4, OS id=3776
DBW0 started with pid=5, OS id=2120
LGWR started with pid=6, OS id=640
CKPT started with pid=7, OS id=3036
SMON started with pid=8, OS id=3332
RECO started with pid=9, OS id=3188
CJQ0 started with pid=10, OS id=3408
MMON started with pid=11, OS id=1204
Thu Sep 03 11:26:59 2009
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=720

This is error in alert log file:

MRP0: Background Media Recovery terminated with error 1110
Thu Sep 03 11:28:25 2009
Errors in file e:\oracle\product\10.2.0\admin\test2stand\bdump\test2stand_mrp0_2204.trc:
ORA-01110: data file 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST2\SYSTEM01.DBF'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST2\SYSTEM01.DBF'

Thu Sep 03 11:28:25 2009
Errors in file e:\oracle\product\10.2.0\admin\test2stand\bdump\test2stand_mrp0_2204.trc:
ORA-01110: data file 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST2\SYSTEM01.DBF'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST2\SYSTEM01.DBF'

Thu Sep 03 11:28:25 2009
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT NODELAY
Thu Sep 03 11:28:43 2009
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 3932
RFS[1]: Identified database type as 'physical standby'
Thu Sep 03 11:28:43 2009
RFS LogMiner: Client disabled from further notification
RFS[1]: No standby redo logfiles created
db_recovery_file_dest_size of 2048 MB is 1.81% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
RFS[1]: Archived Log: 'E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST2STA\ARCHIVELOG\2009_09_03\O1_MF_1_30_59YPRM5B_.ARC'


This is the error in drctest2stand log file:

-11:40:46 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:40:46 0 2 696597391 Operation MON_VERIFY cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:40:46 0 2 696597391 DMON: Entered rfm_release_chief_lock for MON_VERIFY
DG 2009-09-03-11:41:38 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:41:38 0 2 696597397 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:42:38 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:42:38 0 2 696597400 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:43:39 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:43:39 0 2 696597403 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:44:39 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:44:39 0 2 696597406 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:45:39 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:45:39 0 2 696597409 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:46:40 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:46:40 0 2 696597412 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:47:40 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:47:40 0 2 696597415 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:48:40 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:48:40 0 2 696597418 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:49:40 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:49:40 0 2 696597421 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:50:40 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:50:40 0 2 696597424 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:51:40 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:51:40 0 2 696597427 Operation CTL_GET_STATUS cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:52:12 0 2 696597432 DMON: Entered rfm_get_chief_lock() for MON_VERIFY, reason 0
DG 2009-09-03-11:52:12 0 2 696597432 DMON: chief lock convert for client healthcheck
DG 2009-09-03-11:52:12 0 2 0 INSV: Received message for inter-instance publication
DG 2009-09-03-11:52:12 0 2 0 req_id 1.1.696597432, opcode MON_VERIFY, phase BEGIN, flags 5
DG 2009-09-03-11:52:12 0 2 0 INSV: All instances have replied for message
DG 2009-09-03-11:52:12 0 2 0 req_id 1.1.696597432, opcode MON_VERIFY, phase BEGIN
DG 2009-09-03-11:52:12 0 2 0 DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply unexpectedly offline
DG 2009-09-03-11:52:12 0 2 696597432 Operation MON_VERIFY cancelled during phase 1, error = ORA-16766
DG 2009-09-03-11:52:12 0 2 696597432 DMON: Entered rfm_release_chief_lock for MON_VERIFY


Please tell me where i am wrong.Thanks in advance.
Re: show database command showing ORA-16766 [message #420983 is a reply to message #420892] Thu, 03 September 2009 09:57 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

ORA-16766: Redo Apply unexpectedly offline
Cause: 	Redo Apply was offline when it should be online.
Action: 	If necessary, start Redo Apply.
Re: show database command showing ORA-16766 [message #421054 is a reply to message #420983] Fri, 04 September 2009 00:05 Go to previous messageGo to next message
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
Member
Please tell me at the time of redo apply on standby database primary database is in open state or on what state.

How to resolve this Warning

WARNING:Shared I/O Pool created with size=0 set size=0

because when i am trying to switchover to physical standby it is giving this error

DGMGRL> switchover to test2stand
Performing switchover NOW, please wait...
Error: ORA-16608: one or more databases have warnings

Failed.
Unable to switchover, primary database is still "test2"
Re: show database command showing ORA-16766 [message #421070 is a reply to message #421054] Fri, 04 September 2009 02:15 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

>>Please tell me at the time of redo apply on standby database primary database is in open state or on what state.

Primary should be open state & standby should be mount state

Thanks
Re: show database command showing ORA-16766 [message #421093 is a reply to message #421070] Fri, 04 September 2009 03:50 Go to previous messageGo to next message
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
Member
I have passed this command to apply redo on standby database.

alter database recover managed standby database disconnect from session;

and to check standby database performing managed recovery or not i passed this command

SQL> select process, status from v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
RFS IDLE

but again it is showing the same error

DGMGRL> show database test2stand

Database
Name: test2stand
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
test2stand

Current status for "test2stand":
Error: ORA-16766: Redo Apply unexpectedly offline
Re: show database command showing ORA-16766 [message #421113 is a reply to message #421093] Fri, 04 September 2009 05:37 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Okay. Try the below link for troubleshoot your problem.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/troubleshooting.htm#i1008260
Re: show database command showing ORA-16766 [message #421268 is a reply to message #421113] Mon, 07 September 2009 02:24 Go to previous messageGo to next message
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
Member
I gone through the link but most of the solution provided through Enterprise manager but i am using CLI and the problem is still same.

I have done some investigation..

1-To check configuration

DGMGRL> show configuration

Configuration
Name: mybroker
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
test2 - Primary database
test2stand - Physical standby database

Current status for "mybroker":
Warning: ORA-16607: one or more databases have failed

I checked both database primary and standby and both are using spfile.so there is no issue with spfile as it is suggested in some forum that this warning is due to spfile.

2-
DGMGRL> show database verbose test2stand

Database
Name: test2stand
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
test2stand

Properties:
InitialConnectIdentifier = 'test2stand'
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'E:\oracle\product\10.2.0\oradata\test2stand, E:\oracle\product\10.2.0\oradata\test2'
LogFileNameConvert = 'E:\oracle\product\10.2.0\flash_recovery_area\TEST2STAND\ARCHIVELOG, E:\oracle\product\10.2.0\fla
sh_recovery_area\TEST2\ARCHIVELOG'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'SSIPL-LAPTP-052'
SidName = 'test2stand'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=SSIPL-LAPTP-052)(PORT=1521))'
StandbyArchiveLocation = 'E:\test2stand'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'ARC%S_%R.%T'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'


I think this will help you to understand the issue.
Re: show database command showing ORA-16766 [message #421379 is a reply to message #421268] Tue, 08 September 2009 00:32 Go to previous messageGo to next message
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
Member
To verify my standby database environment.i have done some steps

1-To check Level of synchronization

SQL> select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
0 0 0 0
1 73 1 66

11 rows selected.

2- To Show received logs

SQL> select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;

REGISTR CREATOR THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
------- ------- ---------- ---------- ------------- ------------
RFS ARCH 1 67 877379 877498
RFS ARCH 1 68 877498 877506
RFS ARCH 1 69 877506 877509
RFS ARCH 1 70 877509 877511
RFS ARCH 1 74 902193 902312
RFS ARCH 1 71 877511 902043
RFS ARCH 1 72 902043 902153
RFS ARCH 1 73 902153 902193


3-To verify the archived redo log files were applied.


SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP
---------- ---
67 NO
68 NO
69 NO
70 NO
71 NO
72 NO
73 NO
74 NO

8 rows selected.


When i am trying to To start real-time apply,it is giving error
.
SQL> alter database recover managed standby database using current logfile;
alter database recover managed standby database using current logfile
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST2\SYSTEM01.DBF'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST2\SYSTEM01.DBF'



Is standby database performing managed recovery

SQL> select process, status from v$managed_standby;

PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
RFS IDLE





Re: show database command showing ORA-16766 [message #421472 is a reply to message #420892] Tue, 08 September 2009 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST2\SYSTEM01.DBF'
Does it exist by correct user & correct permissions?
Re: show database command showing ORA-16766 [message #421515 is a reply to message #420892] Wed, 09 September 2009 00:03 Go to previous messageGo to next message
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
Member
Thanks for your reply

Yes it exist by correct user and permission.
Re: show database command showing ORA-16766 [message #421518 is a reply to message #420892] Wed, 09 September 2009 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Yes it exist by correct user and permission.
Therefore Oracle is in error & you need to submit bug report.
Re: show database command showing ORA-16766 [message #421520 is a reply to message #420892] Wed, 09 September 2009 00:22 Go to previous message
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
Member
Do you think some parameter in standby database i have to set to resolve this issue.

I found one parameter lock_name_space that we have to set in standby database.Do you think this parameter will make any effect on this issue

I have done one thing. when i mount the primary database and when i am trying to apply redo on standby database then it is working fine but primary database startup creates problem.
Previous Topic: concepts on dataguard
Next Topic: Failover from Primary to Secondary
Goto Forum:
  


Current Time: Thu Mar 28 14:42:25 CDT 2024