Sunday, 30 August 2020

ORA-20005: object statistics are locked (stattype = ALL)

 ACTIVITY:

Gather statistics on table levels:

SQL> EXEC DBMS_STATS.gather_table_stats('HR', 'CUSTOMER', estimate_percent => 15);

ERROR:

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 23829

ORA-06512: at "SYS.DBMS_STATS", line 23880

ORA-06512: at line 1

SOLUTIONS:

SQL>select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='CUSTOMER' and owner='HR';

OWNER        TABLE_NAME                     STATT

------------ ------------------------------ -----

HR     CUSTOMER ALL

Elapsed: 00:00:00.15

+++++++++ To unlock_table_stats  +++++++++++++

SQL>EXEC DBMS_STATS.unlock_table_stats('HR','CUSTOMER');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11

SQL>select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='CUSTOMER' and owner='HR';

OWNER        TABLE_NAME                     STATT

------------ ------------------------------ -----

HR     CUSTOMER

Elapsed: 00:00:00.01

SQL>EXEC DBMS_STATS.gather_table_stats('HR', 'CUSTOMER', estimate_percent => 15);

PL/SQL procedure successfully completed.

NodeManager is not coming up after SSL Setup

ERROR:

 After SSL implementation NodeManager is not startup.

java.lang.RuntimeException: Cannot convert identity certificate

  at com.certicom.tls.interfaceimpl.CertificateSupport.addAuthChain(Unknown Source)

  at com.certicom.net.ssl.SSLContext.addAuthChain(Unknown Source)

  at com.bea.sslplus.CerticomSSLContext.addIdentity(Unknown Source)

  at weblogic.security.utils.SSLContextWrapper.addIdentity(SSLContextWrapper.java:144)

  at weblogic.nodemanager.server.SSLListener.init(SSLListener.java:53)

  at weblogic.nodemanager.server.NMServer.start(NMServer.java:206)

  at weblogic.nodemanager.server.NMServer.main(NMServer.java:377)

  at weblogic.NodeManager.main(NodeManager.java:31)

 weblogic.nodemanager.server.NMServer main

SEVERE: Fatal error in node manager server

java.lang.RuntimeException: Cannot convert identity certificate

  at com.certicom.tls.interfaceimpl.CertificateSupport.addAuthChain(Unknown Source)

  at com.certicom.net.ssl.SSLContext.addAuthChain(Unknown Source)

  at com.bea.sslplus.CerticomSSLContext.addIdentity(Unknown Source)

  at weblogic.security.utils.SSLContextWrapper.addIdentity(SSLContextWrapper.java:144)

  at weblogic.nodemanager.server.SSLListener.init(SSLListener.java:53)

  at weblogic.nodemanager.server.NMServer.start(NMServer.java:206)

  at weblogic.nodemanager.server.NMServer.main(NMServer.java:377)

  at weblogic.NodeManager.main(NodeManager.java:31)

+ set +x

SOLUTIONS:

Configure Custom keystore in nodemanager.properties and enable JSSE for nodemanager.

1. Navigate to WL_HOME\server\bin

2. Take back-up of nodemanager.properties

3. Append below lines in nodemanager.properites.

----------------------------------------

KeyStores=CustomIdentityAndCustomTrust

CustomIdentityKeyStoreFileName=Identity_Keystore EX:/ofm/Oracle/Middleware/keystores/keystore.jks

CustomIdentityKeyStorePassPhrase=Identity_Keystore_Password

CustomIdentityAlias=Identity_Keystore_Alias EX: server_cert

CustomIdentityPrivateKeyPassPhrase=Private_Key_Used_When_Creating_Certificate EX: /ofm/Oracle/Middleware/keystores/keystore.jks

-------------------------------------------

4. Take back-up of startNodeManager.sh and edit the startNodeManager.sh with below JAVA_OPTIONS to enable JSSE.

-----------------------------

JAVA_OPTIONS="${JAVA_OPTIONS} -Dweblogic.security.SSL.enableJSSE=true"

export JAVA_OPTIONS

----------------------------------

5. Restart the Nodemanager and Admin server.

6. Once the nodemanager become Reachable in Admin console, start the Forms and Reports server.

SSL (HTTPS) Implementation in WebLogic and Managed servers.

 All the commands below reference $MIDDLEWARE_HOME for FMW 11g. If using FMW 12c, replace $MIDDLEWARE_HOME with $ORACLE_HOME.

1. Create a directory, for example: $MIDDLEWARE_HOME/keystores

cd /oracle/ofm/Oracle/Middleware ---> (MW_HOME)

$mkdir keystores

2. Run the following to set the environment on UNIX:

ofm11g@host:/ofm/Oracle/Middleware$ cd /ofm/Oracle/Middleware/user_projects/domains/PWCUAT/bin

$./setDomainEnv.sh

3. Create a keystore and private key, by executing the following command:

Syntax: CN=host.DOMAIN.com (Common Name), OU=organization  (Organization Unit), O=Organization (Organization), L=organization  Street (Locality Unit), ST=Doha, (State Province) C=QA (Country)"

ofm11g@host:/ofm/Oracle/Middleware/keystores$ keytool -genkey -alias server_cert -keyalg RSA -keysize 2048 -sigalg SHA256withRSA -dname "CN=host.DOMAIN.com, OU=organization , O=Organization, L=organization  Street, ST=Doha, C=QA" -keypass password -keystore keystore.jks -storepass password

ofm11g@host:/ofm/Oracle/Middleware/keystores$ ls

keystore.jks

4. At this point take a backup of the keystore e.g: keystore.jks

ofm11g@host:/ofm/Oracle/Middleware/keystores$ cp -pr keystore.jks keystore.jks-org

5. To view the contents of the keystore created, execute the following command:

ofm11g@host:/ofm/Oracle/Middleware/keystores$ keytool -list -v -keystore keystore.jks -storepass password

6. Create a Certificate Signing Request (CSR) using the following command:

keytool -certreq -v -alias server_cert -file hpsuaterver.csr -sigalg SHA256withRSA -keypass password -storepass password -keystore keystore.jks

ofm11g@host:/ofm/Oracle/Middleware/keystores$ keytool -certreq -v -alias server_cert -file hpsuaterver.csr -sigalg SHA256withRSA -keypass password -storepass password -keystore keystore.jks

Certification request stored in file <hpsuaterver.csr>

Submit this to your CA

ofm11g@host:/ofm/Oracle/Middleware/keystores$ ls

hpsuaterver.csr   keystore.jks      keystore.jks-org

Make sure you use the same -alias, -storepass and -keypass passwords from Step 3.

The CSR (server.csr) created looks like this:

-----BEGIN NEW CERTIFICATE REQUEST-----

MIIxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

-----END NEW CERTIFICATE REQUEST-----

7. Send this CSR to a Certificate Authority (CA) of your choice. They will provide two certificate server certificate and root certificate.

8. Once you have received the Certificate back you will need to import this along with the Trusted Root CA certificate(s) that signed it, into your keystore.

Take the server certificate and save it a file called server.cer. Take the Certificate Authority's root certificate and save to a file called rootCA.cer in your keystore directory e.g $MIDDLEWARE_HOME/keystores. Repeat this step for any more Root CA certificates in the chain e.g rootCA2.cer etc..

ofm11g@host:/ofm/Oracle/Middleware/keystores$ mv certnew.cer server.cer

ofm11g@host:/ofm/Oracle/Middleware/keystores$ mv UATroot.cer rootCA.cer

ofm11g@host:/ofm/Oracle/Middleware/keystores$ ls -lrt

total 26

-rw-r--r--   1 ofm11g   hps         2240 Jul 26 14:28 keystore.jks-org

-rw-r--r--   1 ofm11g   hps         2240 Jul 26 14:28 keystore.jks

-rw-r--r--   1 ofm11g   hps         1035 Jul 26 14:39 hpsuaterver.csr

-rw-r--r--   1 ofm11g   hps         2090 Jul 27 13:40 server.cer

-rw-r--r--   1 ofm11g   hps         1328 Jul 27 13:40 rootCA.cer

ofm11g@host:/ofm/Oracle/Middleware/keystores$

9. Import the CA's root certificate into your keystore using the following command:

Syntax: keytool -import -v -noprompt -trustcacerts -alias <alias> -file <rootca_file> -keystore <keystore> -storepass <password>

ofm11g@host:/ofm/Oracle/Middleware/keystores$ keytool -import -v -noprompt -trustcacerts -alias rootcacert -file rootCA.cer -keystore keystore.jks -storepass password

Certificate was added to keystore

[Storing keystore.jks]

If there are other intermediate trust certificates, repeat this for each trust certificate using a different alias each time.

10. Import the Server Certificate into your keystore using the following command:

Syntax: keytool -import -v -noprompt -trustcacerts -alias <alias> -file <rootca_file> -keystore <keystore> -storepass <password>

ofm11g@host:/ofm/Oracle/Middleware/keystores$ keytool -import -v -alias server_cert -file server.cer -keystore keystore.jks -keypass password -storepass password

Certificate reply was installed in keystore

[Storing keystore.jks]

Make sure you use the same -alias from Step 3.

11. To view the contents of the keystore, execute the following command:

Syntax: keytool -list -v -keystore keystore.jks -storepass <PASSWORD>

ofm11g@host:/ofm/Oracle/Middleware/keystores$ keytool -list -v -keystore keystore.jks -storepass password

12. At this point the keystore is now ready for use. To use this Keystore with WLS please refer back to the Master Note for your version:

Configuring Oracle WebLogic Server (10.3.x - 12.1.x) to Use SSL in Fusion Middleware 11g/12c (Doc ID 1235653.1)

Step II: Configure WebLogic Server for SSL

The steps below take you through configuring SSL for a Managed Server.

The steps assumes the reader understands how to start the Admin Server and Managed Server.

1. Start the Admin Server in the Domain

2. Login to the WLS console e.g: http://10.0.00.11:7001/console

3. Select 'Environment' -> 'Servers' and click on the server you want to configure

4. Select the 'Keystores' tab

5. Select 'Keystore -> 'Change'

6.Select 'Custom Identity and Custome Trust'from the drop down list and click 'Save'

7. Enter the relevant information in the Keystores page:

'Custom Identity Keystore' : /ofm/Oracle/Middleware/keystores/keystore.jks

'Custom Identity Keystore' : JKS

'Custom Identity Keystore Passphrase' : password

'Confirm Custom Identity Keystore Passphrase' : password

'Custom Trust Keystore' : /ofm/Oracle/Middleware/keystores/keystore.jks

'Custom Trust Keystore Type' : JKS 

'Custom Trust Keystore Passphrase' : password

'Confirm Custom Trust Keystore Passphrase' : password

Click 'Save'

13. Select the 'SSL' tab and enter the relevant information:

'Private Key Alias' : server_cert

'Private Key Password' : password

'Confirm Private Key Password': password

Click 'Save'

14. Select 'Environment' -> 'Servers' and click on the Managed Server configured

 In the 'General' tab:

Check 'SSL Listen Port Enabled'

'SSL Listen Port' : <port> e.g 7012 (make sure this is not used by another process)

Click Save

And Click on adnvace and check the Use JSSE SSL and save the changes.

15. Implement the SSL for managed servers. Follow the steps from 12 to 15 for remaining servers.

16 . Click on Environment->Servers-> AdminServer or Managed Server-> Click on SSL -> Click on Advance --> HostnameVerification "Node" --> Click on save -> Activate the changes.

17. Add SSL parameter in nodemanager.

# Added following parameters in nodemanager.properties

KeyStores=CustomIdentityAndCustomTrust

CustomIdentityKeyStoreFileName=/ofm/Oracle/Middleware/keystores/keystore.jks

CustomIdentityKeyStorePassPhrase=password

CustomIdentityAlias=server_cert

CustomIdentityPrivateKeyPassPhrase=password


# added this parameter in startNodeManager.sh

JAVA_OPTIONS="-Dweblogic.security.SSL.enableJSSE=true ${JAVA_OPTIONS}"

export JAVA_OPTIONS

# Validate your certificate.

cd $DOMAIN_HOME/bin

-bash-3.2$ . ./setDomainEnv.sh

-bash-3.2$ java  utils.ValidateCertChain -jks server_cert /ofm/Oracle/Middleware/keystores/keystore.jks

Cert[0]: CN=host.DOMAIN.com,OU=organization ,O=Organization,L=organization  Street,ST=Doha,C=QA

Cert[1]: CN=Organization-BBSFADS001-CA,dc=Organization,dc=local

Certificate chain appears valid

17. Ask your network/security team to allow new SSL ports (7012/9011/9012) between your machine and server.

18. Restart all the weblogic server services.

19. Test the below new SSL URL should be open.

https://10.0.00.11:7012/console

20. Disable http port and restart the services.

1. In WebLogic Administration Console:

2. Click Lock and Edit.

3. Select Environment, Clusters, and select cluster_forms.

4. Select Configuration, and the Replication tab.

5. Select secure replication enabled.

6. Click Save.

7. Click Activate Changes.

Please do the above steps for cluster_reports also.

Note: Please take config.xml back-up before doing any changes in weblogic console.

++++++++++++++Start the Application  services. +++++++++++++====

echo "Starting up the AdminServer ..."

nohup $DOMAIN_HOME/bin/startWebLogic.sh  &

sleep 60

echo "Starting Node Manager ..."

nohup $WL_HOME/server/bin/startNodeManager.sh &

sleep 5

echo "Starting Forms Server 11G................."

nohup $DOMAIN_HOME/bin/startManagedWebLogic.sh WLS_FORMS https://10.0.00.12:7012 &

sleep 60

echo "Starting Reports Server 11G................."

nohup sh $DOMAIN_HOME/bin/startManagedWebLogic.sh WLS_REPORTS https://10.0.00.12:7012 &

sleep 90

echo "Starting OPMN ALL ............................"

opmnctl startall

=====================

Oracle Reference Documents:

=====================

Configuring Oracle WebLogic Server (10.3.x - 12.1.x) to Use SSL in Fusion Middleware 11g/12c (Doc ID 1235653.1)

How To Create a Java Keystore via Keytool in FMW 11g/12c (Doc ID 1230333.1)

After SSL Implementation in WebLogic Not listening for SSL, java.io.IOException: Cannot convert identity certificate

 ERROR:

<Jul 28, 2020 10:06:39 AM GMT+03:00> <Notice> <Security> <BEA-090171> <Loading the identity certificate and private key stored under the alias server_cert from the JKS keystore file /ofm/Oracle/Middleware/keystores/keystore.jks.>

<Jul 28, 2020 10:06:39 AM GMT+03:00> <Error> <WebLogicServer> <BEA-000297> <Inconsistent security configuration, java.lang.RuntimeException: Cannot convert identity certificate>

<Jul 28, 2020 10:06:39 AM GMT+03:00> <Emergency> <Security> <BEA-090034> <Not listening for SSL, java.io.IOException: Cannot convert identity certificate.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Error> <WebLogicServer> <BEA-000297> <Inconsistent security configuration, java.lang.RuntimeException: Cannot convert identity certificate>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Emergency> <Security> <BEA-090034> <Not listening for SSL, java.io.IOException: Cannot convert identity certificate.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Error> <WebLogicServer> <BEA-000297> <Inconsistent security configuration, java.lang.RuntimeException: Cannot convert identity certificate>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Emergency> <Security> <BEA-090034> <Not listening for SSL, java.io.IOException: Cannot convert identity certificate.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Error> <WebLogicServer> <BEA-000297> <Inconsistent security configuration, java.lang.RuntimeException: Cannot convert identity certificate>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Emergency> <Security> <BEA-090034> <Not listening for SSL, java.io.IOException: Cannot convert identity certificate.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Error> <WebLogicServer> <BEA-000297> <Inconsistent security configuration, java.lang.RuntimeException: Cannot convert identity certificate>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Emergency> <Security> <BEA-090034> <Not listening for SSL, java.io.IOException: Cannot convert identity certificate.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Error> <WebLogicServer> <BEA-000297> <Inconsistent security configuration, java.lang.RuntimeException: Cannot convert identity certificate>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Emergency> <Security> <BEA-090034> <Not listening for SSL, java.io.IOException: Cannot convert identity certificate.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Notice> <Server> <BEA-002613> <Channel "Default[3]" is now listening on 172.30.0.130:7001 for protocols iiop, t3, ldap, snmp, http.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Notice> <Server> <BEA-002613> <Channel "Default[1]" is now listening on 10.0.11.146:7001 for protocols iiop, t3, ldap, snmp, http.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Notice> <Server> <BEA-002613> <Channel "Default[2]" is now listening on 172.30.0.66:7001 for protocols iiop, t3, ldap, snmp, http.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Notice> <Server> <BEA-002613> <Channel "Default" is now listening on 10.0.11.144:7001 for protocols iiop, t3, ldap, snmp, http.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Notice> <Server> <BEA-002613> <Channel "Default[5]" is now listening on 127.0.0.1:7001 for protocols iiop, t3, ldap, snmp, http.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Notice> <Server> <BEA-002613> <Channel "Default[4]" is now listening on 172.30.2.2:7001 for protocols iiop, t3, ldap, snmp, http.>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Notice> <WebLogicServer> <BEA-000329> <Started WebLogic Admin Server "AdminServer" for domain "PWCUAT" running in Production Mode>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to RUNNING>

<Jul 28, 2020 10:06:40 AM GMT+03:00> <Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode>

https port is not coming up after SSL implementation in WebLogic.

SOLUTIONS:

1. Navigate to [managed server or Admin server ] > Configuration > SSL > Advanced.

2. Check "Use JSSE SSL".

3. Save and Activate changes.

No replication server channel for WLS_FORMS java.lang.AssertionError: No replication server channel for WLS_FORMS

 ERROR:

<Aug 23, 2020 11:56:43 AM GMT+03:00> <Critical> <WebLogicServer> <BEA-000386> <Server subsystem failed. Reason: java.lang.AssertionError: No replication server channel for WLS_FORMS

java.lang.AssertionError: No replication server channel for WLS_FORMS

at weblogic.cluster.replication.ReplicationManagerServerRef.initialize(ReplicationManagerServerRef.java:128)

at weblogic.cluster.replication.ReplicationManagerServerRef.<clinit>(ReplicationManagerServerRef.java:84)

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:170)

at weblogic.rmi.internal.BasicRuntimeDescriptor.getServerReferenceClass(BasicRuntimeDescriptor.java:469)

Truncated. see log file for complete stacktrace

SOLUTIONS:

1. In WebLogic Administration Console:

2. Click Lock and Edit.

3. Select Environment, Clusters, and select cluster_forms.

4. Select Configuration, and the Replication tab.

5. Select secure replication enabled.

6. Click Save.

7. Click Activate Changes.

Please do the above steps for cluster_reports also.

Note: Please take config.xml back-up before doing any changes in weblogic console.

ORA-32017: failure in updating SPFILE ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

 ERROR:

SQL> alter system set log_archive_dest_1='/oradata/archive' scope=spfile;
alter system set log_archive_dest_1='/oradata/archive' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

Solutions:

SQL> alter system set log_archive_dest_1='location=/oradata/archive' scope=both;

System altered.

Convert Oracle Database from No Archive Mode to Archive Mode

 Step:1 Check the current archive status.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     210

Current log sequence           212

Step:2 Change the archive log location.

SQL> alter system set log_archive_dest_1='location=/oradata/archive' scope=both;

System altered.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /oradata/archive

Oldest online log sequence     210

Current log sequence           212

Step:3 Stop the database and start in Mount mode.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 6157238272 bytes

Fixed Size                  8615744 bytes

Variable Size            1291847872 bytes

Database Buffers         4848615424 bytes

Redo Buffers                8159232 bytes

Database mounted.

Step:4 Change database from no archivelog to archivelog .

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /oradata/archive

Oldest online log sequence     210

Next log sequence to archive   212

Current log sequence           212

Sunday, 16 August 2020

ORA-01591: lock held by in-doubt distributed transaction 167.3.155988

 ERROR:

ORA-02354: error in exporting/importing data

ORA-01591: lock held by in-doubt distributed transaction 167.3.155988

SOLUTIONS:

SQL> SELECT LOCAL_TRAN_ID, STATE FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID          STATE

---------------------- ----------------

167.3.155988           prepared

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('167.3.155988');

BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('167.3.155988'); END;

*

ERROR at line 1:

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at "SYS.DBMS_TRANSACTION", line 105

ORA-06512: at line 1

SQL> SELECT local_tran_id FROM dba_2pc_pending;

LOCAL_TRAN_ID

----------------------

167.3.155988

SQL> rollback force '167.3.155988';

Rollback complete.

SQL> commit;

Commit complete.

SQL> begin dbms_transaction.purge_lost_db_entry('167.3.155988');

  2  end;

  3  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM dba_2pc_pending;

no rows selected

SQL> commit;

Commit complete.

SQL> SELECT * FROM dba_2pc_pending;

no rows selected

Re-Initiate your process.


OS Watcher Installation in RAC

 Step:1 Download and untar the oswbb812.tar under the grid user in RAC on the both nodes. Follow the OS Watcher User's Guide (Doc ID 153...