You will go crazy if you haven’t configured as required #DBMS_ROLLING
This blog post based on my experience while performing upgrade of Data Guard environment from 12.1.0.1 to 12.1.0.2 using DBMS_ROLLING technique of 12c new feature. In fact we expect the safe upgrade in case of critical environments upgrades, now let’s consider you stuck somewhere in the middle with new errors which are not familiar or not listed in MOS then for sure you will end up with lot of efforts and also time, probably there can be many issues either from OS level, Networking, Database and so on but we will discuss few errors what we faced in each phase of DBMS_ROLLING execution.
As you know there are 5-6 steps to upgrade the Data Guard using DBMS_ROLLING such as initialization, build plan, start phase, switchover and then finally finish the plan.Here in any phase if you do not have expected communication between sites then the commands of DBMS_ROLLING will not work, the issues can be because of any network communication and most probably even the misconfiguration. In this practice i have configured Data Guard using broker and later i have disabled broker prior to start the upgrade of Data Guard setup, but still am facing the misconfiguration issues? it’s for sure weird.
I will try to explain what can be the various possible reasons that can stuck your safe upgrade plan 🙂
Now consider we are into the build plan step of DBMS_ROLLING executed from primary database and failed with below error.
SQL> exec dbms_rolling.build_plan;
BEGIN dbms_rolling.build_plan; END;
*
ERROR at line 1:
ORA-45414: could not connect to a remote database
ORA-06512: at "SYS.DBMS_ROLLING", line 16
ORA-06512: at line 1
So the first thought is primary unable to connect to standby as per the error “Could not connect to a remote database” and hence of course we will try to connect really we are able to reach primary to standby and vice versa, right? let’s see.
SQL> connect sys/oracle@inddb as sysdba
Connected.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ORA-U2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 17 19:42:13 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> exec dbms_rolling.build_plan;
BEGIN dbms_rolling.build_plan; END;
*
ERROR at line 1:
ORA-45414: could not connect to a remote database
ORA-06512: at "SYS.DBMS_ROLLING", line 16
ORA-06512: at line 1
Well, am able to connect and i tried give the same command from remote database this time but resulted the same error. Now we need some idea what the error code mean to say.
SQL> !oerr ora 45414 45414, 00000, "could not connect to a remote database" // *Document: YES // *Cause: Connections to one or more databases failed. // *Action: Consult the DBA_ROLLING_PLAN and DBA_ROLLING_EVENTS views to // identify the involved databases and the specific error codes.
Here you won’t get much information specific to the problem, but you can get where you stuck if you consult the views DBA_ROLLING_PLAN or any other. Probably this error is first time and i thought configuration is perfect because it is handled by the Data Guard broker and now we will look at various sources if there is any known issue? like wise i checked MOS but no information found on this error.
Now i have taken decision to go ahead to recheck the configuration whether my primary and standby configuration is perfect? In this process i came to know 2 issues, they are
- LOG_ARCHIVE_CONFIG was not configured on standby
- The redo transport from standby to primary is disabled.
These are the only two issues i have seen but expected to have many more reasons, but am writing here what i have experienced so far and i will update the blog post if any more information on this practices.
SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string nydb SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(nydb,inddb) SQL> show parameter db_uniq NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string inddb SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(nydb) SQL> alter system set log_archive_config='dg_config=(nydb,inddb)' scope=both; System altered. SQL>
Now we have updated the values and then proceeded with the build plan execution using DBMS_ROLLING method and we will see the results.
SQL> exec dbms_rolling.build_plan; PL/SQL procedure successfully completed. SQL>
Finally the DBMS_ROLLING able to execute successfully, like i said earlier i have faced with same error with some other misconfiguration and we will see output of that as well.
SQL> exec dbms_rolling.switchover;
BEGIN dbms_rolling.switchover; END;
*
ERROR at line 1:
ORA-45414: could not connect to a remote database
ORA-06512: at "SYS.DBMS_ROLLING", line 89
ORA-06512: at line 1
SQL> !oerr ora 45414
45414, 00000, "could not connect to a remote database"
// *Document: YES
// *Cause: Connections to one or more databases failed.
// *Action: Consult the DBA_ROLLING_PLAN and DBA_ROLLING_EVENTS views to
// identify the involved databases and the specific error codes.
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string defer
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
SQL>
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL>
After enabling redo transport and now am able to execute the commands, here the question may be why i have to enable redo transport from standby to primary? the answer is whenever swhichover occurs End of the Redo sentence will be sent to the remote sites and thus need the communication between the two sites.
I hope you enjoyed reading, again… if you encountered the same issue. 🙂
