Friday, December 13, 2019

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

 The warning is clear that archive log not deleted and the below are the possible reasons. This post will help you how to manage the archive logs on standby.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

1. The archive log destination on Standby is full
2. Standby destination not accepting any logs due to issues
3. RMAN archive log configuration is set to none/default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

First run below command to identify the archive log gap

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 185685 185669 16

If there is an archive gap then the issue is either standby archive destination is full or standby destination has some issues and not accepting any more logs, based up on the error take appropriate action. As long as you archive logs backed upon on Primary you can proceed deleting the archive logs on standby

In order to manage the archive logs you have 2 options

1. Create a shell script to check applied logs on standby and delete rest on periodic basis
2. Configure FRA for archive logs and set the RMAN archive log deletion policy as per below

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=800GBG;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/FRA';

In case if you are using ASM specify disk group
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA_DISK';
You need to set LOG_ARCHIVE_DEST_1 parameter DB_RECOVERY_FILE_DEST so that archived logfiles will be created at Flash recovery area.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

Set the RMAN archived log deletion policy as follows.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

With above setting the applied archived logs will be deleted automatically when there is a space constraint in flash recovery area.

It’s different for the database where you do the backup, because you want to be sure that the backup is done before an archive log is deleted:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

Regards
Satishababu Gunukula, Oracle ACE

Thursday, December 12, 2019

Why Oracle DataGuard? New Features in Oracle 18c,19c


This Webinar helps you to understand the benefits of Oracle Data Guard, available methods, protection modes and new features in Oracle 18c and 19c.

Date and time: Jan 3th 2020 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Introduction to Oracle Data Guard
• Oracle Data Guard Benefits
• Standby Database types
• Data Guard Protection Modes
• What's new in Oracle 18c
• What's new in Oracle 19c
• References
• Contact Info
• Q&A

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited and first come and first serve basis.
You will receive an email confirmation with meeting session link.

For presentation link Click here

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

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
Members:
North_Sales - Primary database
South_Sales - Transient logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ROLLING DATABASE MAINTENANCE IN PROGRESS


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
Instance(s):
South
Database Warning(s):
ORA-16866: database converted to transient logical standby database for rolling
database maintenance
Database Status:
WARNING


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.

Regards
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/12.2.0.1/dbhome_1
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/12.2.0.1/dbhome_1
Oracle user: oracle
Spfile: +DISK1/DG/ parameterfile/spfileDG.ora
Password file: /home/oracle/product/12.2.0.1/dbhome_1/dbs/orapwDG

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

Regards
Satishbabu Gunukula, Oracle ACE

Friday, September 20, 2019

weblogic.jdbc.extensions.PoolDisabledSQLException

We have come across content pool suspended issue in WebLogic as pool got exhausted. All new connections failings and existing connections are running very slow. Which intern created issue in Agile PLM as it is using WebLogic.

weblogic.jdbc.extensions.PoolDisabledSQLException: weblogic.common.resourcepool.ResourceDisabledException: Pool AgileContentPool is Suspended, cannot allocate resources to applications..
        at weblogic.jdbc.common.internal.JDBCUtil.wrapAndThrowResourceException(JDBCUtil.java:265)
        at weblogic.jdbc.jts.Driver.newConnection(Driver.java:900)
        at weblogic.jdbc.jts.Driver.createLocalConnection(Driver.java:220)
        at weblogic.jdbc.jts.Driver.connect(Driver.java:170)
        at weblogic.jdbc.common.internal.RmiDataSource.getConnectionInternal(RmiDataSource.java:523)
        at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:516)
        at weblogic.jdbc.common.internal.RmiDataSource.getConnection(RmiDataSource.java:512)
<2019-09-10 10:38:56,366> <PCMHelperSessionBean_9xz6y2_Impl:ERROR> Session terminated...

To find the root causes examine the thread dumps while the error is occurring and pay attention to stuck thread.

If there are long running queries find out what it is running and users may see ORA-00020: Maximum number of processes reached.

Possible reasons :

1. Database running some custom or un-optimized long running queries
2. Application forgot to release the connection back to pool due to recent config changes, which can result connection leak.
3. Inactive connection timeout value may need to change to support system growth/load.

We have verified at DB level and everything working fine except more connections hanging around and not releasing. After investigation we found that most of the connections going though one server due to recent patch and didn’t update the Pcclient.jnlp, Jndiurl.properties..etc

After updating the config we see that user connections going trough all servers and connection load balance happening as expected and issue has been resolved.

Hope this post helps to resolve Agile Content pool suspended issue.

Thanks,
Satishbabu Gunukula, Oracle ACE