Many of you out there will be using dataguard and i guess most of you will have some variation of the startup trigger below
CREATE OR REPLACE NONEDITIONABLE TRIGGER "SYS"."MANAGE_SERVICE" after startup
on database
declare
role varchar2(40);
begin
select database_role into role from v$database;
if role = 'PRIMARY' then
dbms_service.start_service('NYNAME_DG');
else
dbms_service.stop_service('MYNAME_DG');
end if;
end;
/
So basically if i startup and i'm the primary start the service MYNAME_DG - all clients reference this service name in the tnsnames entry so can only ever connect to the primary as the service will never be active anywhere else - really simple HA and works great.
This week however we had an odd quirk, the primary database was bounced and the service did not come up - as a short term fix one of the team just manually set activated the service name to get users connected again - but what had gone wrong?
When we could get an outage i had a look to see what had gone wrong.
The steps i ran are as follows:
SQL shutdown immediate;
SQL> startup
ORACLE instance started.
Total System Global Area 4429185024 bytes
Fixed Size 2933016 bytes
Variable Size 1140854504 bytes
Database Buffers 3271557120 bytes
Redo Buffers 13840384 bytes
Database mounted.
ORA-28365: wallet is not open
Now this is strange - in all previous attempts at restarts this message has never come up - the wallet isn't open sure enough we will open that with the next command - but why did that form any part of this startup process?
Even though we didn't get the database open message there , the database is open and works fine (whether i open the wallet or not).
However looking at the listener status and the NYNAME_DG service did not start - the database did not run all of the startup steps - it has errored out with the wallet issue and not run them all but still left the database in an open state.
A look in the alert log reveals whats going on
Errors in file /oracle/product/diag/rdbms/host/DB/trace/DB_smon_34005.trc:
ORA-01595: error freeing extent (3) of rollback segment (2))
ORA-28365: wallet is not open
and inside the trace similarly
*** 2016-12-14 20:26:47.038
SMON: following errors trapped and ignored:
ORA-01595: error freeing extent (3) of rollback segment (2))
ORA-28365: wallet is not open
So smon is rolling back an active transaction that we killed with the shutdown immediate - however it can't do that as the undo is encrypted so it errors out and the startup trigger never fires.......
So to get a clean startup with the service activated we have 2 methods.
1) always do a clean shutdown (never normally practical)
2) when we bring the database up, only mount the database then open the wallet then open the database so smon can recover anything cleanly and the startup trigger fires.
Quite interesting - i'm just glad i 'got lucky' and had a transaction that needed rolling back during my testing - if it hadn't then i may have been puzzling over this for weeks....
Comments
Post a Comment