Wednesday, October 16, 2019

Data Guard Broker Support for DBMS_ROLLING Upgrades

Data guard broker can remain on during a DBMS_ROLLING upgrade and no need to disable it. In oracle 19c there are many enhancements for Data Guard Broker Support for DBMS_ROLLING Upgrades

• Data Guard broker support is enabled by using the DBMS_ROLLING.BUILD_PLAN procedure
• Before starting a DBMS_ROLLING upgrade the fast-start failover feature must be disabled.
• Role changes are permissible during when rolling upgrade is in progress.
• During rolling upgrade any attempt to enable fast-start failover is rejected.
• Broker support is enabled by default during execution of the DBMS_ROLLING.BUILD_PLAN procedure
• The broker prevents a role change to a standby which is not protecting the current primary. The role changes to the Trailing Group Standby are allowed before the switchover phase. After the switchover phase, role changes are only allowed to the Leading Group Standbys.
• The broker will notifies Global Data Services and Oracle Clusterware as appropriate during the rolling upgrade.
• If the upgrade target is an Oracle RAC database, then the broker automatically reduces the target standby to one instance during the start of the upgrade process and allows the upgrade to proceed. Without the broker, the start of the upgrade is rejected if target has multiple instances running.
• The switchover step during a rolling upgrade should be performed using the DBMS_ROLLING.SWITCHOVER procedure.
• The status of a rolling upgrade being done using the PL/SQL package DBMS_ROLLING and the information is displayed in the broker commands SHOW CONFIGURATION and SHOW DATABASE output.

Please see the output of SHOW CONFIGURATION, where you can see configuration status as ROLLING DATABASE MAINTENANCE IN PROGRESS

Configuration - DRSystem
Protection Mode: MaxPerformance
North_Sales - Primary database
South_Sales - Transient logical standby database
Fast-Start Failover: DISABLED
Configuration Status:

Please see the output of SHOW Database , where you can see Database status as WARNING.

Database – DRNode1
Role: Physical standby database
Intended State: APPLY-ON
Transport Lag: ***
Apply Lag: ***
Average Apply Rate: ***
Real Time Query: OFF
Database Warning(s):
ORA-16866: database converted to transient logical standby database for rolling
database maintenance
Database Status:

Three are specific Requirements DBMS_ROLLING Upgrades on a CDB

• To use DBMS_ROLLING the database compatibility should be set to 12.2 or higher

• Different character sets pluggable databases in a CDB supported for upgrade using DBMS_ROLLING

• The TNS services referenced in the LOG_ARCHIVE_DEST_n parameters must be services that resolve to the root container of the destination database.

• The process assisting DBMS_ROLLING can only execute from the root container.

• While a DBMS_ROLLING upgrade is in progress, if any DDL is executed to start the install, upgrade, or patching of an container then you will get an error.

• When user attempt to start a DBMS_ROLLING upgrade when upgrade to an application container is in progress user will get an error. Before calling DBMS_ROLLING.SWITCHOVER all container databases on the transient logical standby must be plugged in and opened, this helps eliminating logical standby apply engine halt because it cannot apply to a given PDB.

Satishbabu Gunukula, Oracle ACE

Tuesday, October 1, 2019

ORA-01033: ORACLE initialization or shutdown in progress

You will see this error very often during data guard setup, the main reason for this error is the password files are not copied to from primary to standby.

ORA-01033: ORACLE initialization or shutdown in progress.
Cause: An attempt was made to log on while Oracle is being started up or shutdown.
Action: Wait a few minutes. Then retry the operation 

If you don’t have any password file then create one using below command on primary and copy to standby node.

$orapwd file=orapwDG password=xxxxxx

In case of RAC, the password file might be on Primary cluster DB which was stored in ASM, but on standby you have copied the password file to $ORACLE_HOME/dbs

Run below commands to on both primary and standby

For ex:- On Primary
$ srvctl config database -d DG 
Database unique name: DG
Database name: DG
Oracle home: /home/oracle/product/
Oracle user: oracle
Spfile: +DISK1/DG/ parameterfile/spfileDG.ora
Password file: +DISK1/DG/ parameterfile/pwdDG

For ex:- On standby
$ srvctl config database -d DG_std
Database unique name: DG_std
Database name: DG
Oracle home: /home/oracle/product/
Oracle user: oracle
Spfile: +DISK1/DG/ parameterfile/spfileDG.ora
Password file: /home/oracle/product/

Also users see this error on normal database. There are many possible reasons for this error.
1. You may get this error if Database might not opened
2. Startup/shutdown is in the middle and hung up.
3. If you are trying to connect to wrong database
4. Database might be in down for recovery

Please make sure you check below

1. If the database is not opened then use command “alter database open” .
2. Check target DB is in the middle of startup or shutdown as system is very busy or has some issues. You need to verity alert.log to find out the real issue.
3. Verify all environment variable are set correct and In case if DB is down startup the database
4. In case if user doing recovery and not successful and below steps will help to recover the DB
SQL> startup mount
ORACLE Instance started
SQL> recover database
Media recovery complete
SQL> alter database open;
Database altered

Satishbabu Gunukula, Oracle ACE