Home » Server Options » Data Guard » standby - ASYNC and NOAFFIRM options seems not work :/
standby - ASYNC and NOAFFIRM options seems not work :/ [message #516898] Thu, 21 July 2011 06:35 Go to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
hi.

i have another issue with my standby installation.
it is made using SYNC and AFFIRM option for maximum protection - and it works. when standby db is down, primary database waits for some moment and after some moment it shutdowns.

but my intention is to make:
1. max protection installation - to be sure that everyting is proper configured and it works.
2. change this maxprotection to maxavailability mode

the secound point doesn't work.

see my what i am doing:

SQL> alter system set log_archive_dest_2='SERVICE="(description=(address_list=(address=(protocol=TCP)(host=r00979)(port=1521)))(connect_data=(service_name=desk_std)))", LGWR SYNC AFFIRM' scope=both;

System altered.

SQL> alter database open;

Database altered.

SQL> show parameter  log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE="(description=(address
                                                 _list=(address=(protocol=TCP)(
                                                 host=r00979)(port=1521)))(conn
                                                 ect_data=(service_name=desk_st
                                                 d)))", LGWR SYNC AFFIRM


so as above maxprotection (SYNC AFIRM) is working properly.

now i am changing this option to (ASYNC NOAFFIRM)

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  107552472 bytes
Fixed Size                   454360 bytes
Variable Size              79691776 bytes
Database Buffers           25165824 bytes
Redo Buffers                2240512 bytes
Database mounted.
SQL> alter system set log_archive_dest_2='SERVICE="(description=(address_list=(address=(protocol=TCP)(host=r00979)(port=1521)))(connect_data=(service_name=desk_std)))", LGWR ASYNC NOAFFIRM' scope=both;

System altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  107552472 bytes
Fixed Size                   454360 bytes
Variable Size              79691776 bytes
Database Buffers           25165824 bytes
Redo Buffers                2240512 bytes
Database mounted.
ORA-03113: end-of-file on communication channel



and log says

ALTER SYSTEM SET log_archive_dest_2='SERVICE="(description=(address_list=(address=(protocol=TCP)(host=r00979)(port=1521)))(connect_data=(service_name=desk_std)))", LGWR ASYNC NOAFFIRM' SCOPE=BOTH;
Thu Jul 21 13:22:13 2011
alter database open
Thu Jul 21 13:22:13 2011
LGWR: Primary database is in CLUSTER CONSISTENT mode
LGWR: Primary database is in MAXIMUM PROTECTION mode
LGWR: Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I/O
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Thu Jul 21 13:22:13 2011
Errors in file c:\oracle\ora92\admin\desk\bdump\desk_lgwr_896.trc:
ORA-16072: a minimum of one standby database destination is required

---------lgwr log -------------



*** SESSION ID:(4.1) 2011-07-21 13:36:51.276
Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I/O
Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
Standby database verification failed:16072
error 16072 detected in background process
ORA-16072: a minimum of one standby database destination is required




there is even information that "LGWR: Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I/O" so i assume that synchronization mode changed.

so i am changing this mode back to maxprotection and everything seems to work.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  107552472 bytes
Fixed Size                   454360 bytes
Variable Size              79691776 bytes
Database Buffers           25165824 bytes
Redo Buffers                2240512 bytes
Database mounted.
SQL> alter system set log_archive_dest_2='SERVICE="(description=(address_list=(address=(protocol=TCP)(host=r00979)(port=1521)))(connect_data=(service_name=desk_std)))", LGWR SYNC AFFIRM' scope=both;

System altered.

SQL> alter database open;

Database altered.

SQL>


what i am doing wrong?

[Updated on: Thu, 21 July 2011 06:37]

Report message to a moderator

Re: standby - ASYNC and NOAFFIRM options seems not work :/ [message #516900 is a reply to message #516898] Thu, 21 July 2011 06:42 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
maybe this is answer for my question?

SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;


When this statement is used, the primary database is protected against data divergence. If connection between the primary and standby database is lost, the primary database shuts down.


so i need unprotect my primary database?

but no mater if primary database is protected or not - for MaxProtection mode it works, but for MaxAvailability mode primary database claims that standby is not accessible/operable.

[Updated on: Thu, 21 July 2011 07:05] by Moderator

Report message to a moderator

Re: standby - ASYNC and NOAFFIRM options seems not work :/ [message #516905 is a reply to message #516900] Thu, 21 July 2011 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Always put your version number, with 4 decimals, in all your topics.

Regards
Michel
Re: standby - ASYNC and NOAFFIRM options seems not work :/ [message #516915 is a reply to message #516905] Thu, 21 July 2011 07:34 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
ok. i will do this from now, because i can't modify my previous posts. didn't know.

the problem which i was describing concerns 9.2.0.7.0

[Updated on: Thu, 21 July 2011 07:36]

Report message to a moderator

Re: standby - ASYNC and NOAFFIRM options seems not work :/ [message #516978 is a reply to message #516915] Thu, 21 July 2011 10:29 Go to previous message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
i would like to confirm that ASYNC NOAFFIRM option (for MaxAvailability standby mode) does work after unprotecting my primary database with command:

SQL> ALTER DATABASE SET STANDBY DATABASE UNPROTECTED;


if primary db is protected i constantly get information that

ORA-16072: a minimum of one standby database destination is required


and using this protection command makes sense only for MaxProtection mode - do you agree with me? i read some documentation about OracleDataguard (9i) and for each document which i read this option wasn't described this way - i mean: it should be used only for MaxProtection mode.


[Updated on: Thu, 21 July 2011 11:52]

Report message to a moderator

Previous Topic: Does standby redologs are applied online to datafile?
Next Topic: Standby using RMAN
Goto Forum:
  


Current Time: Thu Mar 28 10:56:14 CDT 2024