Ao executar a procedure START_PLAN da packages DBMS_ROLLING, caso algum requisito não seja atendido adequadamente, o Oracle irá retornar o seguinte erro:
SQL> EXEC DBMS_ROLLING.START_PLAN;
BEGIN DBMS_ROLLING.START_PLAN; END;
*
ERROR at line 1:
ORA-45415: instruction execution failure
ORA-06512: at "SYS.DBMS_ROLLING", line 80
ORA-06512: at line 1
A mensagem de erro “ORA-45415: instruction execution failure” indica que uma da instruções listadas na view DBA_ROLLING_PLAN não foi atendida. Porém, a mensagem não indica qual instrução especificamente. Para determinar qual instrução gerou o erro, precisamos recoverr ao alert log.
A mensagem de erro é gravada no alert log com o texto “failed on instruction”, então podemos filtrar com o comando grep:
$ grep -B1 "failed on instruction" alert_prod.log
Tue Jan 26 21:36:15 2021
RTS(11519): failed on instruction 7 from plan revision 1
--
Tue Jan 26 21:39:48 2021
RTS(11519): failed on instruction 7 from plan revision 1
Dica: A opção -B1 indica no comando grep inclui a linha que antecede aquela que estamos filtrando.
Neste exemplo, a mensagem indica que o erro ocorreu na instrução 7.
Agora basta validar na view DBA_ROLLING_PLAN:
SQL> SELECT instid,
target,
phase,
description
FROM DBA_ROLLING_PLAN
WHERE instid = 7;
INSTID TARGET PHASE DESCRIPTION
------ ------------ ------------ ----------------------------------------------------------------------
7 PROD START Verify Data Guard Broker configuration is disabled
Neste exemplo, ele verificou se o DG Broker estava desativado. Precisamos desativar a configuração do Dataguard no Broker, e setar o parâmetro dg_broker_start para false no primary e no standby.
Para referência, lista completa de instruções executadas pela DBMS_ROLLING (versão 12.1.0.2):
set pagesize 500
set linesize 300
col instid format 9999
col target format a12
col phase format a12
col description format a70
SELECT instid, target, phase, description FROM DBA_ROLLING_PLAN;
INSTID TARGET PHASE DESCRIPTION
------ ------------ ------------ ----------------------------------------------------------------------
1 PROD START Verify database is a primary
2 PROD START Verify MAXIMUM PROTECTION is disabled
3 stdb START Verify database is a physical standby
4 stdb START Verify physical standby is mounted
5 PROD START Verify server parameter file exists and is modifiable
6 stdb START Verify server parameter file exists and is modifiable
7 PROD START Verify Data Guard Broker configuration is disabled
8 stdb START Verify Data Guard Broker configuration is disabled
9 PROD START Verify flashback database is enabled
10 PROD START Verify available flashback restore points
11 stdb START Verify flashback database is enabled
12 stdb START Verify available flashback restore points
13 stdb START Stop media recovery
14 stdb START Drop guaranteed restore point DBMSRU_INITIAL
15 stdb START Create guaranteed restore point DBMSRU_INITIAL
16 PROD START Drop guaranteed restore point DBMSRU_INITIAL
17 PROD START Create guaranteed restore point DBMSRU_INITIAL
18 stdb START Start media recovery
19 stdb START Verify media recovery is running
20 PROD START Verify user_dump_dest has been specified
21 PROD START Backup control file to rolling_change_backup.f
22 stdb START Verify user_dump_dest has been specified
23 stdb START Backup control file to rolling_change_backup.f
24 PROD START Get current redo branch of the primary database
25 stdb START Wait until recovery is active on the primary's redo branch
26 stdb START Stop media recovery
27 PROD START Execute dbms_logstdby.build
28 stdb START Convert into a transient logical standby
29 stdb START Open database
30 stdb START Get redo branch of transient logical standby
31 stdb START Get reset scn of transient logical redo branch
32 stdb START Configure logical standby parameters
33 stdb START Start logical standby apply
34 stdb START Enable compatibility advance despite presence of GRPs
35 PROD START Log pre-switchover instructions to events table
36 stdb START Record start of user upgrade of stdb
37 stdb SWITCH Verify database is in OPENRW mode
38 stdb SWITCH Record completion of user upgrade of stdb
39 stdb SWITCH Scan LADs for presence of PROD destination
40 stdb SWITCH Test if PROD is reachable using configured TNS service
41 PROD SWITCH Enable log file archival to stdb
42 stdb SWITCH Start logical standby apply
43 stdb SWITCH Archive all current online redo logs
44 stdb SWITCH Wait until apply lag has fallen below 600 seconds
45 PROD SWITCH Log post-switchover instructions to events table
46 PROD SWITCH Switch database to a logical standby
47 stdb SWITCH Wait until end-of-redo has been applied
48 PROD SWITCH Archive all current online redo logs
49 stdb SWITCH Switch database to a primary
50 PROD SWITCH Enable compatibility advance despite presence of GRPs
51 PROD SWITCH Synchronize plan with new primary
52 PROD FINISH Verify only a single instance is active
53 PROD FINISH Verify database is mounted
54 PROD FINISH Flashback database
55 PROD FINISH Convert into a physical standby
56 stdb FINISH Verify database is open
57 stdb FINISH Save the DBID of the new primary
58 stdb FINISH Save the logminer session start scn
59 PROD FINISH Wait until transient logical redo branch has been registered
60 PROD FINISH Start media recovery
61 PROD FINISH Wait until apply/recovery has started on the transient branch
62 PROD FINISH Wait until upgrade redo has been fully recovered
63 PROD FINISH Prevent compatibility advance if GRPs are present
64 stdb FINISH Prevent compatibility advance if GRPs are present
65 PROD FINISH Drop guaranteed restore point DBMSRU_INITIAL
66 stdb FINISH Drop guaranteed restore point DBMSRU_INITIAL
66 rows selected.