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.

Thanks,
Satishbabu Gunukula, Oracle ACE

Wednesday, August 14, 2019

How to find Oracle Database and Oracle WebLogic Server Version?

There are several ways to find Oracle Database and Oracle WebLogic Version

Oracle Database
1. Select v$instance for only Database version

SQL> select version from V$INSTANCE;
VERSION
-----------------
11.2.0.4.0

2. Select v$version table in database

SQL> Select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production

Identifying Your Oracle Database Software Release



·         10 is the major database release number
·         1 is the database maintenence release number
·         0 is the application server release number
·         1 is the component-specific release number
·         0 is the platform-specific release number


3.  Search for “Enterprise Edition” in alert_SID.log file
$cat alert_ORCL.log |grep "Enterprise Edition"
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


Oracle Weblogic:

1.Find WebLoic version from WebLogic Jar file using below command

For ex:-
$cd /u01/home/ wls1211/wlserver_12.1/server/lib/
java -cp weblogic.jar weblogic.version

2. Check registry.xml under $MW_HOME and search for earch for “component name=”WebLogic Server””

For ex: cat /u01/home/ wls1211/registry.xml | grep 'component name="WebLogic Server"'
<component name="WebLogic Server" version="12.1.1.0" InstallDir="/u01/home/oracle/Agile/wls1211/wlserver_12.1">

3. You can also find version by searching “WebLogic Server” in WebLogic Admin server logfile
$DOMAIN_HOME/servers/AdminServer/admin/AdminServer.log

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

Tuesday, May 14, 2019

Recovering Tables and Table Partitions using RMAN Backups

Oracle 12c offers new feature to recover tables and table partitions at a point-in-time using RMAN without affecting the other objects in the database. Use RECOVER TABLE command to recover tables or table partitions from an RMAN backup.

This feature is useful in the following scenarios:
  • Object has Logical corruption or dropped
  • There is no sufficient undo to perform Flashback table
  • DDL operation modified the structure and you want to recover the data (Flashback cannot rewind the structural changes)
  • Recover a small number of tables to a point-in-time
User should verify the pre-requisites before you prepare to recover the tables and table partitions. RMAN enables you to specify the point-in-time either using SCN, Time or sequence number.

The conditions must be met to perform table/ table Partition recovery:

Before you prepare to recover the tables and table partitions,
  • Database must be in ARCHIVELOG mode and read-write mode
  • At least one full backup is available along with archived logs
  • Enough disk space is available on the database server for auxiliary instance
  • If present, any dependent objects to include in recovery
  • At least 1 Gigabyte extra in memory for the auxiliary database
  • Tables and table partitions on standby databases cannot be recovered.
  • Tables with named NOT NULL constraints cannot be recovered with the REMAP option. · COMPATIBLE parameter must be set to 11.1.0 or higher to recover table partition

 
                    
RMAN enables recovery of selected tables without affecting remaining database objects. During the recovery process RMAN creates an auxiliary database, which is used to recover the tables or table partitions to a specified point-in-time. User need to specify the auxiliary database location using AUXILIARY DESTINATION clause in the RECOVERY command or SET NEWNAME command.

Please find the steps performed by RMAN during the recovery process:

1. Determine the backup which has the tables or table partitions that needs to recover to specified point-in-time
2. Create auxiliary database and recovery the tables or table partitions until specified point-in-time
3. Take a data dump export with recovered tables or table partitions
4. Import the dump into target database
5. Rename the recovered tables or table partitions in the target database

Please find an example to recovery TABLE1 table.

RECOVER TABLE TESTUSER.TABLE1
UNTIL SCN 384840289
AUXILIARY DESTINATION '/TEMP/ORCLDB/recover'
DATAPUMP DESTINATION '/TEMP/ORCLDB/dumpfiles'
DUMP FILE 'testdump.dat';

If source table exists then user can specify NOTABLEIMPORT or REMAP TABLE. Also user can use UNTIL TIME or UNTIL SEQUENCE clause to specify point-in-time recovery.

Please find an example to recovery TABLE1 table as TABLE1_REC to the state that it was 2 days before the current date.

RECOVER TABLE TESTUSER.TABLE1
UNTIL TIME ‘SYSDATE-2’
AUXILIARY DESTINATION '/TEMP/ORCLDB/recover'
REMAP TABLE 'TESTUSER'.'TABLE1':'TABLE1_REC';

There are some limitations recovering tables and table partitions:
  • We cannot recover table and table partitions belongs of SYS schema
  • We cannot recover table or table partitions from SYSAUX,SYSTEM tablespace Tables
  • We cannot recover tables with named NOT NULL constraint using REMAP option.
  • We cannot recover Table/Table partitions ON STANDBY database
  • We cannot recovery table partitions if version is prior Oracle Database 11g R1
Note that there are other methods available to recover tables to a point-in-time such as Oracle Flashback and Tablespace Point-in-Time Recovery.

Refer below link for

Flashback and Database Point-in-Time Recovery
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com


Monday, May 6, 2019

Oracle Database Upgrade to 12c and Available Methods

Oracle 12c offers many new features and upgrading database can bring many advantages to organization. There are various upgrade and migration methods available and the best method to use for your upgrade/migration scenario depends on the source database version, the source and destination operating systems, your downtime requirements, and the personal preference of the DBA. Based upon factors there is a method available to best fit your organization needs.

We have several upgrade methods available

• DBUA
• command line
• Full Transportable Export/Import
• Transportable Tablespaces
• Data Pump Export/Import
• Original Export/Import

For complete Article Please check below link

https://www.linkedin.com/pulse/oracle-database-upgrade-12c-available-methods-gunukula-oracle-ace

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

Sunday, May 5, 2019

Restoring and Recovering Files over Network in Oracle

Oracle 12c offers many new features and restoring, recovering files over the network is one of the new feature. Using RMAN you can restore and recover a database, datafile, controlfile, tablespace or spfile over the network from a physical standby database. To restore the database over the network, use the RESTORE… FROM SERVICE command and use the RECOVER…FROM SERVICE command to recover the database over the network. The FROM SERVICE clause specifies the service name of the physical standby.

You can also use multisection, compression and encryption to improve backup and restore performance.

  • Use SECTION SIZE with RESTORE command to perform multisection restore
  • Use SET ENCRYPTION clause before the RESTORE command to specify the encryption
  • Use USING COMPRESSED BACKUPSET clause to compress backup sets
This feature is useful to synchronize primary and standby database. Here are the few scenarios
Roll-forward a physical standby database to sync with the primary database
Restore the primary database using physical standby database.
Restore physical standby database using the primary database.

In the following example restoring data file over the network from physical standby to primary database

Connected to primary database implicitly
RMAN> CONNECT TARGET /

Restoring the datafile on the primary using datafile on physical database with service “standby_db”
RMAN> RESTORE DATAFILE '/oradata1/orcl/users.dbf' FROM SERVICE standby_db SECTION SIZE 1024M;

Prior to 12c , to restore and recover files over the network, you need to use RMAN BACKUP INCREMENTAL FROM SCN command to create a backup on the primary database that started at the current SCN of the standby, and was then used to roll the standby database forward in time. That manual, multi-step method is not necessary as of Oracle Database 12c.

Regards
Satishbabu Gunukula, Oracle ACE