This content has been marked as final. Show 63 replies
Right now seems gap are only one, which means are in sync.
Odd thought perhaps on my part, You are running this from the Database in the Primary mode right?
select open_mode, switchover_status from v$database;
Because if you run from the Standby mode you get odd results.
SQL> select open_mode, switchover_status from v$database; OPEN_MODE SWITCHOVER_STATUS -------------------- -------------------- MOUNTED NOT ALLOWED SQL>
SQL> show parameter
The problem is I tried to switchover to standby, then switchover back to primary, it always failed with exact same error:
Redo transport problem detected: redo transport for database prds has the following error:
ORA-16146: control file enqueue unavailable
Something is wrong.
I have a concern of my multiplexed logfiles.
It used to be only one logfile member, I just recently added another logfile member on FRA in addition to DAT.
WIll that be the cause of this issue?
Maybe. Your log_archive_max_processes are set to Default ( 4 ). You may be pushing it
Just finished review of INIT's
The main thing I noticed in the parameter check is "log_archive_dest_2" has no LGWR on the standby. Cannot remember if is will cause issue or not, looking up.....
Only other thing is on the Standby log_archive_dest_state_2 is ENABLE when it should be DEFER ( but that is NOT the issue )
h2. log_archive_dest_1 - NOT AS EXPECTED
Will post full INIT review here as EDIT in a moment
PRIMARY Checked : compatible 220.127.116.11.0 ( Same as standby ) db_name prd ( Correct ) db_unique_name prd ( Correct ) fal_server prds ( Correct ) log_archive_dest_1 LOCATION=use_db_recovery_file_dest ?? Was expecting more like log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prd' log_archive_dest_2 string service="prds", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="prds" net_ timeout=30, valid_for=(all_logfiles,primary_role) log_archive_dest_state_1 ENABLE ( Correct ) log_archive_dest_state_2 ENABLE ( Correct ) log_archive_max_processes 4 ( default ) might bump up some. remote_login_passwordfile EXCLUSIVE ( Correct ) standby_file_management AUTO ( correct )
STANDBY Checked : compatible 18.104.22.168.0 ( Same as primary ) db_name PRD ( Correct ) case different - Hmmm db_unique_name prds ( Correct ) fal_server prd ( Correct ) log_archive_dest_1 LOCATION=use_db_recovery_file_dest ( Not as expected ) log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prds' log_archive_dest_2 service=prd ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prd ( no LGWR would add to match setup with Primary ) log_archive_dest_state_1 ENABLE ( correct ) log_archive_dest_state_2 ENABLE ( should be DEFER ) But not the issue log_archive_max_processes 4 ( default ) might bump up some. log_file_name_convert +DAT, +DAT ( matches Primary ) remote_login_passwordfile EXCLUSIVE ( Correct ) standby_file_management AUTO ( Correct )
Edited by: mseberg on Jul 22, 2012 4:28 PM
h2. Action plan
Would remove broker completely and leave it out until we resolve. That way it won't make parameter changes on us.
1. Fix both log_archive_dest_1 settings
2. log_archive_max_processes bump both from 4 to 8
3. Fix log_archive_dest_2 on standby, add missing LGWR
great. I will follow your action plan. I am cooking dinner now, will update the results soon later.
I changed parameters accordingly. and now it still unable to switchback from standby to primary.
First switchover to prds worked but drcSID.log has this error:
SQL Execution error=604, sql=[alter system set log_archive_dest_1='']. See error stack below.
ORA-00604: error occurred at recursive SQL level 1
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16028: new LOG_ARCHIVE_DEST_1 causes less destinations than LOG_ARCHIVE_MIN_SUCCEED_DEST requires
Notifying Oracle Clusterware to buildup standby database after SWITCHOVER
Then switchover to prd
on drcSID.log got following error:
Site prds returned ORA-16570.
Redo transport problem detected: redo transport for database remeprds has the following error:
ORA-16456: switchover to standby in progress or completed
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration prd_dg Warning ORA-16607
Primary Database prd Error ORA-16810
Physical Standby Database prds Error ORA-16570
Redo transport problem detected: redo transport for database prds has the following error.
Please help me to get this fixed.
Here is dgmgrl command:
DGMGRL> show configuration
Configuration - prd_dg
Protection Mode: MaxPerformance
prd - Primary database
prds - Physical standby database
Fast-Start Failover: DISABLED
DGMGRL> switchover to prds
Performing switchover NOW, please wait...
New primary database "prds" is opening...
Operation requires shutdown of instance "PRD1" on database "prd"
Shutting down instance "PRD1"...
ORACLE instance shut down.
Operation requires startup of instance "PRD1" on database "prd"
Starting instance "PRD1"...
ORACLE instance started.
Switchover succeeded, new primary is "prds"
DGMGRL> switchover to prd
Performing switchover NOW, please wait...
Error: ORA-16552: error occurred while generating directives for client
Error: ORA-16501: the Data Guard broker operation failed
Unable to switchover, primary database is still "prds"
You did not follow the action plan. The idea was to remove Broker until issue was resolved so we would not have Broker changing our INIT settings.
Remove broker completely.
Double check your INIT's ( By creating new Pfiles from Spfiles )
SQL> select switchover_status from v$database;
Can should also consider at what point despite your best efforts you need more help than a forum can give you and open an SR with Oracle.
Sorry, don't mean to be harsh or anything but after 50 some posts you have to wonder it this is working.
Edited by: mseberg on Jul 23, 2012 7:48 AM
I know. It is just very frustrating. I saw some other posts had same problem, but they never updated what fixes they did. I will close this out as answered.
If you figure it out please post.
Thank you very much