Home » Server Options » Data Guard » Not able to apply archive log to standby database
Not able to apply archive log to standby database [message #253906] Wed, 25 July 2007 05:17 Go to next message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member
Hi,

I am trying to create a test env on my local system-with BOTH PRIMARY AND STANDBY DATABASE on the same host
1. I was able to mount standby databse
2. Log sequence no of primary and standby was identical

But when i issued the command
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
I noticed that the log sequence number was resetted to zero and futher changes in primary were not being applied
Here's my parameter files of primary and stnadby.
Please please help me and let me know where is the mistake

Primary parameter file
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=54525952
orcl.__streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='D:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\product\10.2.0\oradata\orcl\control01.ctl','D:\oracle\product\10.2.0\oradata\orcl\control02.ctl','D:\oracl e\product\10.2.0\oradata\orcl\control03.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='D:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.instance_name='ORCL'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=D:\oracle\product\10.2.0\flash_recovery_area\ORCL\ARCHIVELOG'
*.log_archive_dest_2='LOCATION=D:\oracle\product\10.2.0\flash_recovery_area\STBY\ARCHIVELOG'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=83886080
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0/admin/orcl/udump'

Stnadby parameter file
orcl.__db_cache_size=16777216
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=54525952
orcl.__streams_pool_size=0
*.audit_file_dest='D:\oracle\product\10.2.0/admin/stby/adump'
*.background_dump_dest='D:\oracle\product\10.2.0/admin/stby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\product\10.2.0\oradata\stby\control_stby.ctl'
*.core_dump_dest='D:\oracle\product\10.2.0/admin/stby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=83886080
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\product\10.2.0/admin/stby/udump'
*.standby_archive_dest='LOCATION=D:\oracle\product\10.2.0\flash_recovery_area\STBY\ARCHIVELOG'
*.log_archive_dest_1='LOCATION=D:\oracle\product\10.2.0\flash_recovery_area\STBY\ARCHIVELOG'
*.db_file_name_convert=('D:\oracle\product\10.2.0\oradata\orcl','D:\oracle\product\10.2.0\oradata\stby')
*.log_file_name_convert=('D:\oracle\product\10.2.0\oradata\orcl','D:\oracle\product\10.2.0\oradata\stby')
*.standby_file_management='AUTO'
*.remote_archive_enable=TRUE
*.lock_name_space='STBY'
*.instance_name='STBY'

Regards
Ram
Re: Not able to apply archive log to standby database [message #254031 is a reply to message #253906] Wed, 25 July 2007 10:22 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
pot the O/P of these querries.

from primary
•	SQL> Select Status, Error 
from v$Archive_dest 
where dest_id=2;


from standby

•	SQL> Select Name, Applied, Archived
from v$Archived_log;


from both
archive log list.
Re: Not able to apply archive log to standby database [message #254149 is a reply to message #254031] Wed, 25 July 2007 23:23 Go to previous messageGo to next message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member
Hi please fine the ouput of the queries

Primary
SQL> Select Status, Error
2 from v$Archive_dest
3 where dest_id=2;

STATUS ERROR
--------- -----------------------------------------------------------------
VALID

SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\product\10.2.0\flash_recovery_area\STBY
\ARCHIVELOG
Oldest online log sequence 77
Next log sequence to archive 79
Current log sequence 79
SQL>

Standby
SQL> Select Name, Applied, Archived
2 from v$Archived_log;

no rows selected

SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\product\10.2.0\flash_recovery_area\STBY
\ARCHIVELOG
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL>
Re: Not able to apply archive log to standby database [message #254381 is a reply to message #254149] Thu, 26 July 2007 11:16 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
did you start LOG APPLY SERVICE??

START PHYSICAL LOG APPLY SERVICE.

On the STANDBY database execute the following command to start Managed Recovery Process (MRP). This command is executed on Mount stage.

•	SQL> Alter Database Recover Managed Standby Database;

Database Altered.

By executing the above command the current session will become hanged because MRP is a foreground recovery process. 
It waits for the logs to come and apply them.
 To avoid this hanging, you can execute the following command with DISCONNECT option.

•SQL> Alter Database Recover Managed Standby Database Disconnect;

Database Altered.

Now the session will be available to you and MRP will work as a background process and apply the redo logs.

You can check whether the log is applied or not by querying V$ARCHIVED_LOG.

•SQL> Select Name, Applied, Archived
from v$Archived_log;

This query will return the name of archived files and their status of being archived and applied.

[Updated on: Thu, 26 July 2007 11:16]

Report message to a moderator

Re: Not able to apply archive log to standby database [message #254384 is a reply to message #254381] Thu, 26 July 2007 11:22 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
When you switch logfile does that create on standby as well??

Your Primary
============
SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\product\10.2.0\flash_recovery_area\STBY
\ARCHIVELOG
Oldest online log sequence 77
Next log sequence to archive 79
Current log sequence 79
SQL>


Your Standby
============

SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\product\10.2.0\flash_recovery_area\STBY
\ARCHIVELOG
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 0
SQL>



Some problem in restoring your database and recovering that it should be same like PRIMARY.

If this is your test envirmoent then try to open standby database and notice the ERROR MESSAGES.

THESE commands will you apply before opening the standby database in read only mode.

     SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
     SQL>ALTER DATABASE OPEN READ ONLY;


And after validation put back STANDBY in recover mode by this command.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
              LOGFILE DISCONNECT;
Re: Not able to apply archive log to standby database [message #254624 is a reply to message #253906] Fri, 27 July 2007 05:17 Go to previous messageGo to next message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member
Hi,
Thanks so much for ur time.But i already tried all theat you mentioned.I would give the currentstate maybe u can help me??

1.The standby databse is already created as exact reply.Log sequence no in both matching
2.But not applying logs manually or in managed recovery mode
3.In manual mode it says it is apllying the archived logs but on querying V$log - i find the log sequence no not changing.
4 Checked the datafiles and logfiles in standby which is of standby itself.
5.Both are in same host .Is this a problem

Primary
SQL> select * from V$log ;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 38 52428800 1 YES INACTIVE
555849 27-JUL-07

2 1 39 52428800 1 NO CURRENT
555852 27-JUL-07

3 1 37 52428800 1 YES INACTIVE
555847 27-JUL-07

Standby
SQL> recover standby database
ORA-00279: change 552678 generated at 07/27/2007 14:27:55 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00029_0629032552.001
ORA-00280: change 552678 for thread 1 is in sequence #29


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 554061 generated at 07/27/2007 15:10:43 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00030_0629032552.001
ORA-00280: change 554061 for thread 1 is in sequence #30
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00029_0629032552.001' no
longer needed for this recovery


ORA-00279: change 554063 generated at 07/27/2007 15:10:46 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00031_0629032552.001
ORA-00280: change 554063 for thread 1 is in sequence #31
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00030_0629032552.001' no
longer needed for this recovery


ORA-00279: change 554788 generated at 07/27/2007 15:22:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00032_0629032552.001
ORA-00280: change 554788 for thread 1 is in sequence #32
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00031_0629032552.001' no
longer needed for this recovery


ORA-00279: change 555758 generated at 07/27/2007 15:41:42 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00033_0629032552.001
ORA-00280: change 555758 for thread 1 is in sequence #33
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00032_0629032552.001' no
longer needed for this recovery


ORA-00279: change 555760 generated at 07/27/2007 15:41:43 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00034_0629032552.001
ORA-00280: change 555760 for thread 1 is in sequence #34
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00033_0629032552.001' no
longer needed for this recovery


ORA-00279: change 555763 generated at 07/27/2007 15:41:49 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00035_0629032552.001
ORA-00280: change 555763 for thread 1 is in sequence #35
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00034_0629032552.001' no
longer needed for this recovery


ORA-00279: change 555766 generated at 07/27/2007 15:41:53 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00036_0629032552.001
ORA-00280: change 555766 for thread 1 is in sequence #36
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00035_0629032552.001' no
longer needed for this recovery


ORA-00279: change 555847 generated at 07/27/2007 15:45:04 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00037_0629032552.001
ORA-00280: change 555847 for thread 1 is in sequence #37
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00036_0629032552.001' no
longer needed for this recovery


ORA-00279: change 555849 generated at 07/27/2007 15:45:05 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00038_0629032552.001
ORA-00280: change 555849 for thread 1 is in sequence #38
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00037_0629032552.001' no
longer needed for this recovery


ORA-00279: change 555852 generated at 07/27/2007 15:45:08 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00039_0629032552.001
ORA-00280: change 555852 for thread 1 is in sequence #39
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00038_0629032552.001' no
longer needed for this recovery


ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STBY\ARC00039_0629032552.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.


SQL> select * from V$log ;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 23 52428800 1 YES INACTIVE
549886 27-JUL-07

3 1 25 52428800 1 NO CURRENT
552585 27-JUL-07

2 1 24 52428800 1 YES ACTIVE
549889 27-JUL-07


SQL>

Pls help me solve this issue.Yo are all experienced people and might have come across this issue.I created the standby database again but give same error,so it is not issue with standby db creation but log apply.Pls have a look at the init.ora and suggest if any mistake.

Awaiting Response
Regards
Ram

[Updated on: Fri, 27 July 2007 05:25]

Report message to a moderator

Re: Not able to apply archive log to standby database [message #254699 is a reply to message #254624] Fri, 27 July 2007 10:13 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
verify you datagaurd configuration by creating a table on primary insert few records then manullay switch logfile and check it on standby.


PRIMARY
=======
SQL>  create table test
  2  (d date);

Table created.

SQL> insert into test values (sysdate);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

D
---------
27-JUL-07
27-JUL-07
27-JUL-07
27-JUL-07
27-JUL-07

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u40/oracle/flps/oraarc
Oldest online log sequence     2460
Next log sequence to archive   2469
Current log sequence           2469
SQL>

SQL>



STANDBY
=======
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> select * from test;

D
---------
27-JUL-07
27-JUL-07
27-JUL-07
27-JUL-07
27-JUL-07

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
              LOGFILE DISCONNECT;
  2
Database altered.

SQL>

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u40/oracle/flps/oraarc
Oldest online log sequence     2460
Next log sequence to archive   0
Current log sequence           2469
SQL>


Re: Not able to apply archive log to standby database [message #254700 is a reply to message #254699] Fri, 27 July 2007 10:15 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Check this parameter both on PRIMARY AND STANDBY side.
Log_archive_config=’DG_CONFIG=(primary,standby)’


Check this tutorial for REAL TIME DATAGUARD/DATABORKER/DATAOBSERVER.
Previous Topic: Physical Standby giving ora-1609
Next Topic: Archive LOGS SHIPPED TO WRONG DESTINATION - FLASH
Goto Forum:
  


Current Time: Thu Mar 28 10:11:07 CDT 2024