Home » Server Options » Data Guard » dataguard database is out of sync - help required (11g release 2, Centos 5.5)
dataguard database is out of sync - help required [message #484115] Sat, 27 November 2010 08:33 Go to next message
mateenaslam
Messages: 21
Registered: November 2010
Location: Islamabad, Pakistan
Junior Member
I have recently configured data guard. When ever i switch log on primary, standby database have the recent redo log files. Is means log transition service is working fine.

See same results on both primary and standby

------------------------------------------------
SQL> select max (sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
85
------------------------------------------------

Following command give me errors on standby.

-------------------------------------------------------
SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active
-------------------------------------------------------

I think my standby database is out of sync and i found my logs are not being applied on standby.

-------------------------------------------------------
SQL> select sequence#, applied from v$archived_log;

SEQUENCE# APPLIED
---------- ---------
54 NO
52 NO
53 NO
56 NO
55 NO
57 NO
58 NO
59 NO
60 NO
68 NO
66 NO

SEQUENCE# APPLIED
---------- ---------
65 NO
62 NO
67 NO
64 NO
63 NO
69 NO
61 NO
70 NO
72 NO
74 NO
73 NO

SEQUENCE# APPLIED
---------- ---------
71 NO
75 NO
76 NO
77 NO
78 NO
81 NO
79 NO
80 NO
82 NO
83 NO
84 NO

SEQUENCE# APPLIED
---------- ---------
85 NO
-------------------------------------------------------


can you guys tell me whats wrong ?
Re: dataguard database is out of sync - help required [message #484116 is a reply to message #484115] Sat, 27 November 2010 08:50 Go to previous messageGo to next message
mateenaslam
Messages: 21
Registered: November 2010
Location: Islamabad, Pakistan
Junior Member
i am getting following in standby alert logs

MRP0: Background Managed Standby Recovery process started (DGREPL)
MRP0: Background Media Recovery terminated with error 1110
Re: dataguard database is out of sync - help required [message #484118 is a reply to message #484116] Sat, 27 November 2010 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Verify correctness of parameters DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT.
If they are correct, post the abstract of alert.log between message
MRP0: Background Managed Standby Recovery process started
and message
MRP0: Background Media Recovery process shutdown

Regards
Michel

[Updated on: Sat, 27 November 2010 09:15]

Report message to a moderator

Re: dataguard database is out of sync - help required [message #484124 is a reply to message #484118] Sat, 27 November 2010 13:01 Go to previous messageGo to next message
mateenaslam
Messages: 21
Registered: November 2010
Location: Islamabad, Pakistan
Junior Member
Here are these parameter on primary and standby

Primary
--------------------------------
SQL> show parameters convert;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string DGREPL/DGREPL, repl/repl
log_file_name_convert string /u01/app/oracle/oradata/DGREPL
/, /u01/app/oracle/oradata/rep
l/, /u01/app/oracle/flash_reco
very_area/DGREPL/archivelog/,
/u01/app/oracle/flash_recovery
_area/repl/archivelog/


Standby
--------------------------------
SQL> show parameters convert

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string repl/repl, DGREPL/DGREPL
log_file_name_convert string /u01/app/oracle/oradata/repl/,
/u01/app/oracle/oradata/DGREP
L/, /u01/app/oracle/flash_reco
very_area/repl/archivelog/, /u
01/app/oracle/flash_recovery_a
rea/DGREPL/archivelog/

and i am getting following in trace log

-------------------------------------------------------------
MRP0: Background Media Recovery terminated with error 1110
Errors in file /u01/app/oracle/diag/rdbms/dgrepl/DGREPL/trace/DGREPL_mrp0_5625.trc:
ORA-01110: data file 1: '/u01/app/oracle/oradata/repl/system01.dbf'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/repl/system01.dbf'
Errors in file /u01/app/oracle/diag/rdbms/dgrepl/DGREPL/trace/DGREPL_mrp0_5625.trc:
ORA-01110: data file 1: '/u01/app/oracle/oradata/repl/system01.dbf'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/repl/system01.dbf'
-------------------------------------------------------------


are my parameters need modifications ?
Re: dataguard database is out of sync - help required [message #484125 is a reply to message #484124] Sat, 27 November 2010 13:10 Go to previous messageGo to next message
mateenaslam
Messages: 21
Registered: November 2010
Location: Islamabad, Pakistan
Junior Member
i am also giving following errors if i dont manually create '/u01/app/oracle/oradata/repl/' on standby, though this directory is supposed to be on primary

Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/dgrepl/DGREPL/trace/DGREPL_dbw0_5291.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/repl/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/dgrepl/DGREPL/trace/DGREPL_dbw0_5291.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/repl/users01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Re: dataguard database is out of sync - help required [message #484126 is a reply to message #484125] Sat, 27 November 2010 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
db_file_name_convert string repl/repl, DGREPL/DGREPL

Quote:
'/u01/app/oracle/oradata/repl/system01.dbf'

There is no "repl/repl" in your primary path so conversion does not apply.

Regards
Michel
Re: dataguard database is out of sync - help required [message #484128 is a reply to message #484126] Sat, 27 November 2010 13:49 Go to previous messageGo to next message
mateenaslam
Messages: 21
Registered: November 2010
Location: Islamabad, Pakistan
Junior Member
Here are paths on both primary and standby, i am little bit confused

Primary

$ ls -lh /u01/app/oracle/oradata/repl/
total 2.2G
....
-rw-r----- 1 oracle oinstall 9.4M Nov 28 00:46 control01.ctl
-rw-r----- 1 oracle oinstall 51M Nov 28 00:46 redo01a.log
-rw-r----- 1 oracle oinstall 51M Nov 28 00:46 redo01b.log
-rw-r----- 1 oracle oinstall 51M Nov 27 23:32 redo02a.log
-rw-r----- 1 oracle oinstall 51M Nov 27 23:32 redo02b.log
-rw-r----- 1 oracle oinstall 51M Nov 27 23:54 redo03a.log
-rw-r----- 1 oracle oinstall 51M Nov 27 23:54 redo03b.log
-rw-r----- 1 oracle oinstall 601M Nov 28 00:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 701M Nov 28 00:42 system01.dbf
-rw-r----- 1 oracle oinstall 69M Nov 28 00:00 temp01.dbf
-rw-r----- 1 oracle oinstall 341M Nov 28 00:42 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5.1M Nov 27 23:59 users01.dbf

Standby

$ ls -lh /u01/app/oracle/oradata/DGREPL/
total 2.0G
-rw-r----- 1 oracle oinstall 155K Nov 27 23:27 1_101_735159929.arc
-rw-r----- 1 oracle oinstall 48K Nov 27 23:28 1_102_735159929.arc
-rw-r----- 1 oracle oinstall 118K Nov 27 23:30 1_103_735159929.arc
-rw-r----- 1 oracle oinstall 72K Nov 27 23:32 1_104_735159929.arc
-rw-r----- 1 oracle oinstall 1.6M Nov 27 23:54 1_105_735159929.arc
-rw-r----- 1 oracle oinstall 9.4M Nov 28 00:45 control01.ctl
-rw-r----- 1 oracle oinstall 51M Nov 27 22:36 redo01a.log
-rw-r----- 1 oracle oinstall 51M Nov 27 22:36 redo01b.log
-rw-r----- 1 oracle oinstall 51M Nov 27 22:36 redo02a.log
-rw-r----- 1 oracle oinstall 51M Nov 27 22:36 redo02b.log
-rw-r----- 1 oracle oinstall 51M Nov 27 22:37 redo03a.log
-rw-r----- 1 oracle oinstall 51M Nov 27 22:37 redo03b.log
-rw-r----- 1 oracle oinstall 601M Nov 27 22:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 701M Nov 27 22:37 system01.dbf
-rw-r----- 1 oracle oinstall 69M Nov 27 21:55 temp01.dbf
-rw-r----- 1 oracle oinstall 341M Nov 27 22:37 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5.1M Nov 27 22:37 users01.dbf


I am little confused for DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT values
Re: dataguard database is out of sync - help required [message #484129 is a reply to message #484128] Sat, 27 November 2010 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Values must match with file name conversion to pass from the primary to the standby path.
That is "/u01/app/oracle/oradata/repl" to "/u01/app/oracle/oradata/DGREPL".

Regards
Michel
Re: dataguard database is out of sync - help required [message #484130 is a reply to message #484129] Sat, 27 November 2010 15:50 Go to previous messageGo to next message
mateenaslam
Messages: 21
Registered: November 2010
Location: Islamabad, Pakistan
Junior Member
DB_FILE_NAME_CONVERT

Specify the path name and filename location of the primary database datafiles followed by the standby location. This parameter converts the path names of the primary database datafiles to the standby datafile path names. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site, then this parameter is required. Note that this parameter is used only to convert path names for physical standby databases. Multiple pairs of paths may be specified by this parameter.

LOG_FILE_NAME_CONVERT

Specify the location of the primary database online redo log files followed by the standby location. This parameter converts the path names of the primary
database log files to the path names on the standby database. If the standby database is on the same system as the primary database or if the directory
structure where the log files are located on the standby system is different from the primary system, then this parameter is required. Multiple pairs of paths may be specified by this parameter.

Thanks my data guard is working fine now after setting correct variable values.
Re: dataguard database is out of sync - help required [message #484149 is a reply to message #484130] Sun, 28 November 2010 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67369
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quoted from Oracle® Database Reference.
Always post the source of your quotes.

Regards
Michel

Re: dataguard database is out of sync - help required [message #484154 is a reply to message #484149] Sun, 28 November 2010 07:34 Go to previous message
mateenaslam
Messages: 21
Registered: November 2010
Location: Islamabad, Pakistan
Junior Member
yes, quote is from Oracle database reference. Thanks i will post the source for next time.
Previous Topic: Unable to create physical standby database using RMAN
Next Topic: ALL_LOGFILES and ALL_ROLES
Goto Forum:
  


Current Time: Fri Sep 25 19:06:29 CDT 2020