Thursday, December 15, 2016

Srvctl is not able to start database (ORA-12547: TNS:lost contact)

Srvctl is not able to start database

[oracle@orcldb-n2 ~]$ srvctl start database -d orcl
PRCR-1079 : Failed to start resource ora.orcl.db
ORA-12547: TNS:lost contact
CRS-5017: The resource action "ora.orcl.db start" encountered the following error:
ORA-12547: TNS:lost contact
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/orcldb-n1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.orcl.db' on 'orcldb-n1' failed
ORA-12547: TNS:lost contact
CRS-5017: The resource action "ora.orcl.db start" encountered the following error:
ORA-12547: TNS:lost contact
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/orcldb-n2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.orcl.db' on 'orcldb-n2' failed
ORA-12547: TNS:lost contact
CRS-2632: There are no more servers to try to place resource 'ora.orcl.db' on that would satisfy its placement policy
ORA-12547: TNS:lost contact
[oracle@orcldb-n2 ~]$


Below document can help you. It can happen if the it is a cloned env.

Local SQL*Plus Connection and DBCA Fails With: ORA-12547: TNS:Lost Contact (Doc ID 422173.1)

1. Check kernel parameters settings
2. Check permission of permissions on the ORACLE executable, and config.o
[oracle@orcldb-n2 orcl]$ ls -lrt $ORACLE_HOME/rdbms/lib/config.o
-rw-r--r-- 1 oracle dba 1328 Dec 14 13:06 /u01/app/oracle/product/12.1.0.2/orcl/rdbms/lib/config.o
[oracle@orcldb-n2 orcl]$ ls -lrt $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle dba 324021455 Dec 14 13:06 /u01/app/oracle/product/12.1.0.2/orcl/bin/oracle
[oracle@orcldb-n2 orcl]$
3. Do relink all  on the database nodes
[oracle@orcldb-n2 orcl]$ relink all
writing relink log to: /u01/app/oracle/product/12.1.0.2/orcl/install/relink.log
[oracle@orcldb-n2 orcl]$ 

Tuesday, December 13, 2016

ORA-04061: existing state of has been invalidated

Getting below error.

ORA-04061: existing state of has been invalidated
ORA-04061: existing state of package "" has been invalidated
ORA-04065: not executed, altered or dropped package
ORA-06508: PL/SQL: could not find program unit being called:

Do below step to resolve this issue.

1. If it is EBS, then check workflow notification mailer , and OPMN services.
2. If it is other application or does not resolved after 1 search the search the session which is holding this ovject in cache.
3. Kill that session.

java.sql.SQLDataException: ORA-01861: literal

Oracle Concurrent programs are getting below error.

Debug Flag: N
{P_END_DATE=2016/12/02 00:00:00, P_START_DATE=2016/11/01 00:00:00, P_TYPE=AMEX}
Calling XDO Data Engine...
--SQLException
java.sql.SQLDataException: ORA-01861: literal does not match format string
 
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
        at oracle.apps.xdo.dataengine.XMLPGEN.processQuery(XMLPGEN.java:1947)
        at oracle.apps.xdo.dataengine.XMLPGEN.processSQLDataSource(XMLPGEN.java:501)
        at oracle.apps.xdo.dataengine.XMLPGEN.writeData(XMLPGEN.java:445)
        at oracle.apps.xdo.dataengine.XMLPGEN.writeGroupStructure(XMLPGEN.java:308)
        at oracle.apps.xdo.dataengine.XMLPGEN.processData(XMLPGEN.java:273)
        at oracle.apps.xdo.dataengine.XMLPGEN.processXML(XMLPGEN.java:215)
        at oracle.apps.xdo.dataengine.XMLPGEN.writeXML(XMLPGEN.java:254)
        at oracle.apps.xdo.dataengine.DataProcessor.processDataStructre(DataProcessor.java:390)
        at oracle.apps.xdo.dataengine.DataProcessor.processData(DataProcessor.java:355)
        at oracle.apps.xdo.oa.util.DataTemplate.processData(DataTemplate.java:334)
        at oracle.apps.xdo.oa.cp.JCP4XDODataEngine.runProgram(JCP4XDODataEngine.java:294)
        at oracle.apps.fnd.cp.request.Run.main(Run.java:157) 

There are below things that you can check to troubleshoot.

1. Check the template file if any date field is creating any issue.
2. Any data it is pulling are triggering this issue.
 

SMTP Error. Getting below error while trying to send email in XXXXXXX instance

Getting below error in EBS R12.1.3 in Oracle Database 

SMTP Error. Getting below error while trying to send email in XXXXXXX instance
 
Oracle Error in Send Mail prog--ORA-29277: invalid SMTP operation
ORA-06512: at "SYS.UTL_SMTP", line 82
ORA-06512: at "SYS.UTL_SMTP", line 540
ORA-06512: at "SYS.UTL_SMTP", line 525
ORA-29278: SMTP transient error: 421 Service not available 

It can be below different issues.

1. You don't have enough privileges.
2. SMTP server not given properly.
3. Check WF config to validate SMTP server.
4. Firewall ports are opened for the SMTP mail. Use command prompt to send email.

FNDLOAD gives error

FNDLOAD apps/password Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct CISTON.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE
Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.
Attempt to connect to the database using dbcfile .dbc and Applications User apps/password has failed. The dbcfile may be configured incorrectly or the Applications Username and Password may not match.

It can be related to the below incidents.

1. Wrong password
2. Wrong dbc file, try download to test it.
3. syntax error of FNDLOAD


Sunday, May 15, 2016

In-Database Row Archiving

In-Database Row Archiving

In-database archiving is a feature provided by Oracle in 12c which allow to archive rows withing a table. It is done by making those rows invisible. Application will not be able to get those data from the table, they will compressed and kept secure. It will be accessed only when we set session parameter.

So, with this feature you don't have to purge the data, but same time it will not impact the application performance. We can compressed these archived data to reduce backup time. We can make the update deferred so that the application upgrades time can be minimized.

To manage in-database archiving for a table, you must enable ROW ARCHIVAL for the table, manipulate the ORA_ARCHIVE_STATE hidden column of the table, and specify either ACTIVE or ALL for the ROW ARCHIVAL VISIBILITY session parameter.

1. Connect to the database
2. Create the table.
Create table order_arch as select order_id, amount from order where rownum <= 4;

3. Enable row archival on the table.
alter table order_arch row archival;

4. Set the archival visibility to ALL for the session. This will allow this session to view all rows, archived or not.
alter session set row archival visibility = all;

5. Issue the same query as before, but now you should see all 4 rows.
select order_id, amount, ora_archive_state from order_arch;

6. Set the archival visibility to ACTIVE for the session. This will allow this session to view only active (unarchived) rows.

alter session set row archival visibility = active;

What are the new features of Oracle 12C.

What are the new features of Oracle 12C.

As per my view, below are the main new features of the Oracle Database 12c.

1. Pluggable Database
2. Reduction Policy
3. Top N Query and Fetch and offset Replacement to Rownum
4. Adaptive Query Optimization and Online Stats Gathering
5. Restore a Table Easily through RMAN
6. Some Datatype Size has been increased (Varchar2, NBarchar2, Raw Data)
7. Multiple indexes on single column.
8. Temporary Undo Feature
9. In database Archiving

Thursday, May 5, 2016

Free stock trading.

I've been using Robinhood to trade stocks for free. Check it out on the App Store! http://share.robinhood.com/koushim

Friday, January 15, 2016

False Alerts For Concurrent Processing In OEM 12c


Concurrent Processing - Creating a Conflict Domain for a Concurrent Program

As SYSADMIN in Systems Administrator Responsibility

1) In CONCURRENT > CONFLICT DOMAIN Form

DOMAIN SHORTNAME DESCRIPTION
458 458
204 204

SAVE

NB: 458 = Vision Services org id
       204 = Vision Project Mfg org id

2) CONCURRENT > PROGRAM > DEFINE

Query PRC: Interface Revenue to General Ledger
Select Incompatiblities

Set the "Type" field value to Domain for PATTGL and save.


3) PROFILE > SYSTEM OPTION > Concurrent:Conflicts Domain

PROFILE > SYSTEM Query " CONCURRENT: CONFLICTS DOMAIN"
at the responsibility level:

Projects, Vision Services (USA) -- set the profile to 458
Projects, Vision Project Mfg (MRC) -- set the profile to 204

SAVE


4) CONCURRENT >PROGRAM > DEFINE

Query PRC: Interface Revenue to General Ledger

INCOMPATIBILITIES TAB > Remove Program "PRC: Interface Revenue to General

SAVE
As the conflict domain has been setup the incompatibility that PRC: Interface Revenue to General Ledger has with itself is then removed. 

For more Use the Concurrent Processing - Creating a Conflict Domain for a Concurrent Program (267167.1)

There are many dead processes for WFMGSMS Concurrent Manager

The Internal Concurrent Manager log file reports many dead processes referencing WFMGSMS (Workflow Summary Mailer Concurrent Manager) Concurrent Manager.

For example:

Found dead process: spid=(14546), cpid=(506393), Service Instance=(1064)
Starting WFMGSMS Concurrent Manager : 12-MAR-2009 05:32:23
Process monitor session ended : 12-MAR-2009 05:32:25

Solution:

To implement the solution, please execute the following steps:

1. Login as System Administrator.
2. Navigate to Concurrent / Manager / Define.
3. Query the "Workflow Mailer" and disable it.
4. In the same screen, query the "Workflow Summary Mailer", and disable it.
5. Bounce the Concurrent Managers.

Problem: Concurrent Manager Request Status Remains Terminating Indefinitely

Concurrent Manager Request Status Remains Terminating Indefinitely


Symptom: Concurrent request status Remains Terminating Indefinitely

Cause: It is be because concurrent manager table need a cleanup

Solution:

Run the below commands from apps user.

update fnd_concurrent_queues Set running_processes=0, max_processes=0;

delete from fnd_concurrent_processes;

Update fnd_concurrent_requests set status_code = 'X', phase_code = 'C' where status_code = 'T';

commit;

Command to do active duplicate for Oracle Database

1. First login to target server 2. Validate tns connectivity between Source DB and Target DB 3. Prepare and validate space availability 4. S...