分类目录归档:工作

oracle easy connect ORA-12504

[oracle@edbjr4p5 admin]$ sqlplus sys@127.0.0.1:1521/pdb2_1

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 14 12:04:39 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Enter user-name: ^C

[oracle@edbjr4p5 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 14-JUN-2016 12:04:01

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=edbjr4p5.us.oracle.com)(PORT=1521)))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date                08-JUN-2016 18:35:16
Uptime                    5 days 17 hr. 28 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/edbjr4p5/listener/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr4p5.us.oracle.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=edbjr4p5.us.oracle.com)(PORT=5502))(Presentation=HTTP)(Session=RAW))
Services Summary…
Service “cdb1” has 1 instance(s).
  Instance “cdb1”, status READY, has 1 handler(s) for this service…
Service “cdb1XDB” has 1 instance(s).
  Instance “cdb1”, status READY, has 1 handler(s) for this service…
Service “cdb2” has 1 instance(s).
  Instance “cdb2”, status READY, has 1 handler(s) for this service…
Service “cdb2XDB” has 1 instance(s).
  Instance “cdb2”, status READY, has 1 handler(s) for this service…
Service “em12rep” has 1 instance(s).
  Instance “em12rep”, status READY, has 1 handler(s) for this service…
Service “em12repXDB” has 1 instance(s).
  Instance “em12rep”, status READY, has 1 handler(s) for this service…
Service “pdb1_1” has 1 instance(s).
  Instance “cdb1”, status READY, has 1 handler(s) for this service…
Service “pdb2_1” has 1 instance(s).
  Instance “cdb2”, status READY, has 1 handler(s) for this service…
The command completed successfully

[oracle@edbjr4p5 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 14 12:04:09 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> connect sys\@localhost:1521\/pdb2_1 as sysdba
Enter password:
Connected.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@edbjr4p5 admin]$ sqlplus sys@\”127.0.0.1:1521/pdb2_1\”

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 14 12:04:51 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Enter user-name: ^C
[oracle@edbjr4p5 admin]$ sqlplus sys@\”127.0.0.1:1521/pdb2_1\” as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 14 12:05:03 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is “SYS”
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

PL/SQL计算执行时间 DBMS_UTILITY.get_time

今天一个PL/SQL块里要计算执行时间,google一下DBMS_UTILITY.get_time;
奇怪的是,12c doc里没搜索到这个函数,倒是有一个例子,用法一目了然:

http://st-doc.us.oracle.com/12/121/appdev.121/e17622/tuning.htm#LNPLS881

Example 12-9 inserts the same collection elements into two database tables, using a FOR LOOP statement for the first table and a FORALL statement for the second table and showing how long each statement takes. (Times vary from run to run.)

LNPLS881Example 12-9 Time Difference for INSERT Statement in FOR LOOP and FORALL Statements

DROP TABLE parts1;
CREATE TABLE parts1 (
  pnum INTEGER,
  pname VARCHAR2(15)
);

DROP TABLE parts2;
CREATE TABLE parts2 (
  pnum INTEGER,
  pname VARCHAR2(15)
);

DECLARE
  TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
  TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
  pnums   NumTab;
  pnames  NameTab;
  iterations  CONSTANT PLS_INTEGER := 50000;
  t1  INTEGER;
  t2  INTEGER;
  t3  INTEGER;
BEGIN
  FOR j IN 1..iterations LOOP  -- populate collections
    pnums(j) := j;
    pnames(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;

  t1 := DBMS_UTILITY.get_time;

  FOR i IN 1..iterations LOOP
    INSERT INTO parts1 (pnum, pname)
    VALUES (pnums(i), pnames(i));
  END LOOP;

  t2 := DBMS_UTILITY.get_time;

  FORALL i IN 1..iterations
    INSERT INTO parts2 (pnum, pname)
    VALUES (pnums(i), pnames(i));

  t3 := DBMS_UTILITY.get_time;

  DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
  DBMS_OUTPUT.PUT_LINE('---------------------');
  DBMS_OUTPUT.PUT_LINE('FOR LOOP: ' || TO_CHAR((t2 - t1)/100));
  DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)/100));
  COMMIT;
END;
/

Result is similar to:

Execution Time (secs)
---------------------
FOR LOOP: 5.97
FORALL:   .07

PL/SQL procedure successfully completed.

11.2文档,说明如下:

Syntax

DBMS_UTILITY.GET_TIME 
  RETURN NUMBER;

Return Values

Time is the number of 100th’s of a second from the point in time at which the subprogram is invoked.

Usage Notes

Numbers are returned in the range -2147483648 to 2147483647 depending on platform and machine, and your application must take the sign of the number into account in determining the interval. For instance, in the case of two negative numbers, application logic must allow that the first (earlier) number will be larger than the second (later) number which is closer to zero. By the same token, your application should also allow that the first (earlier) number be negative and the second (later) number be positive.

 

 

convert goldengate to integrated mode

init.ora
*.compatible='11.2.0.3'
*.streams_pool_size=4096m

ext01.prm
--@@TRANLOGOPTIONS ASMUSER SYS@+ASM1, ASMPASSWORD welcome1
--@@TRANLOGOPTIONS DBLOGREADER, DBLOGREADERBUFSIZE 4194304, BUFSIZE 8388608
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 2048, _LOGMINER_READ_BUFFERS 128, parallelism 16)

create.obey
--ADD EXTRACT ext01, TRANLOG, THREAD 1,  BEGIN NOW
ADD EXTRACT ext01, INTEGRATED TRANLOG, BEGIN NOW

but there is errors when start extract 继续阅读

Cisco Anyconnect 2.5.6005 + Windows 8

after installed cisco anyconnect 2.5.6005, when connect,
“Checking for product updates…”.
failed

modified following key value by regedit
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\vpnva]
“DisplayName”=”Cisco AnyConnect VPN Virtual Miniport Adapter for Windows x64”

the displayname is sth just before “Cisco AnyConnect VPN Virtual Miniport Adapter for Windows x64”, delete them , and only remain “=”Cisco AnyConnect VPN Virtual Miniport Adapter for Windows x64”

retry, everything is ok
🙂

Failed Component with handler ID “55” does not exist

I have some EBS R12 load testing scripts , run ok in dev enviroment. 
and We decided to move it to the testing env, with much powerful hardware. 

After all R12 moving is done, all ebs form scripts show followng error. 
<strong>Failed Component with handler ID "55" does not exist.</strong>
on following line of code
nca.genericClient(101, "COMPONENT_ID_55").sendGenericClientMessage("", "12");
this code is generated by openscript after you clicked an url that point to a form and before the form shows.

I tried to record a new scripts on new env, the code run's ok in new env , and got the same error in the old env.

It must be some config not fixed for the new env.
I compared the 2 codes line by line, finally found that I don't make the DB service name as a parameter of the openscript scripts. 
the old DB service name is VIS and the new one is VISXL.

just replace all 'VIS' in old scripts to 'VISXL' and It worked again.
of course the better method is to make the service a parameter , and It will be easier to work with new env.

adoacorectl.sh, adformsctl.sh, adoafmctl.sh exiting with status 150

 

after adstart all, show following report

[Service Control Execution Report]
The report format is:
  <Service Group>            <Service>                                  <Script>         <Status>

  Root Service                                                                           Enabled
  Root Service               Oracle Process Manager for VIS_slcc02db02  adopmnctl.sh     Started
  Web Entry Point Services                                                               Enabled
  Web Entry Point Services   Oracle HTTP Server VIS_slcc02db02          adapcctl.sh      Started
  Web Entry Point Services   OracleTNSListenerAPPS_VIS_slcc02db02       adalnctl.sh      Started
  Web Application Services                                                               Enabled
  Web Application Services   OACORE OC4J Instance VIS_slcc02db02        adoacorectl.sh   Failed
  Web Application Services   FORMS OC4J Instance VIS_slcc02db02         adformsctl.sh    Failed
  Web Application Services   OAFM OC4J Instance VIS_slcc02db02          adoafmctl.sh     Failed
  Batch Processing Services                                                              Enabled
  Batch Processing Services  OracleConcMgrVIS_slcc02db02                adcmctl.sh       Started
  Batch Processing Services  Oracle Fulfillment Server VIS_slcc02db02   jtffmctl.sh      Started
  Other Services                                                                         Enabled

adformsctl.sh: exiting with status 150

check detail logs:
09/27/12-22:44:20 :: adformsctl.sh version 120.16.12010000.3
09/27/12-22:44:20 :: adformsctl.sh: starting OPMN if it is not running

opmnctl: opmn is already running.

09/27/12-22:44:21 :: adformsctl.sh: Starting OPMN managed FORMS OC4J instance

opmnctl: starting opmn managed processes...
================================================================================
opmn id=slcc02db02.us.oracle.com:6200
    no processes or applications matched this request

09/27/12-22:44:21 :: adformsctl.sh: exiting with status 150


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

stop all R12 procs

rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence/*
rm -rf $ORA_CONFIG_HOME/10.1.3/j2ee/forms/persistence/*

start all service again

all is ok.

EBS R12, after rapid clone, FRM92101

no response from runtime process

check log
[apps@slcc02db02 ~]$ tail -f   $LOG_HOME/ora/10.1.3/opmn/forms_default_group_1/formsstd.out

12/09/27 21:29:07 Forms session <2> aborted: runtime process failed during startup with errors /scratch/apps/appmgr/VIS/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXm.so.2: cannot open shared object file: No such file or directory

12/09/27 21:45:29 Forms session <3> aborted: runtime process failed during startup with errors /scratch/apps/appmgr/VIS/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXm.so.2: cannot open shared object file: No such file or directo

reinstall all needed rpms

12/09/27 21:45:47 Shutting down…
12/09/27 21:49:01 FormsServlet init():
configFileName:     /scratch/apps/appmgr/VIS/inst/apps/VIS_slcc02db02/ora/10.1.2/forms/server/appsweb.cfg
testMode:           false
12/09/27 21:49:01 Oracle Containers for J2EE 10g (10.1.3.5.0)  initialized
12/09/27 21:50:54 ListenerServlet init()

How to determine bind variable value and type from trace file

from
http://oradbatips.blogspot.jp/2007/11/tip-64-how-to-determine-bind-variable.html
For finding more information about bind variable, user should be able to locate BINDS keyword in trace file.
This part is something like this :

BINDS #2:
bind 0: dty=1 mxl=32(30) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0
bfp=030a79ec bln=32 avl=01 flg=05
value="A"

dty determines bind variable type and value shows bind variable value at the time of execution.
Different values can be assigned to dty which presents different data type.

Typical dty value is :

1 VARCHAR2 or NVARCHAR2
2 NUMBER
8 LONG
11 ROWID
12 DATE
23 RAW
24 LONG RAW
96 CHAR
112 CLOB or NCLOB
113 BLOB
114 BFILE

To demosnstrate, I ran some queries.

SQL> alter session set statistics_level=ALL;

Session altered.

SQL> alter session set max_dump_file_size=UNLIMITED;

Session altered.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> variable b number;
SQL> declare
2 cnt number;
3 begin
4 :b:=1;
5 select count(*) into cnt from dba_objects where object_id=:b;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> alter session set events '10046 trace name context off';

Session altered.

Checking trace file ……

PARSING IN CURSOR #2 len=52 dep=1 uid=0 oct=3 lid=0 tim=2797155817 hv=1220784193 ad='130a9d4c'
SELECT count(*) from dba_objects where object_id=:b1
END OF STMT
PARSE #2:c=15625,e=24270,p=0,cr=5,cu=0,mis=1,r=0,dep=1,og=0,tim=2797155809
BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=24 offset=0
bfp=030a7b2c bln=22 avl=02 flg=05
value=1
EXEC #2:c=0,e=2062,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=2797159524
FETCH #2:c=0,e=37,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=2797159885

 

—— Query #2 : Varhchar2 Bind variable

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> variable b varchar2(30);
SQL> declare
2 cnt number;
3 begin
4 :b:='A';
5 select count(*) into cnt from dba_objects where object_name=:b;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> alter session set events '10046 trace name context off';

Checking trace file .....

PARSING IN CURSOR #2 len=54 dep=1 uid=0 oct=3 lid=0 tim=3007778755 hv=2029951970 ad='12ff2c80'
SELECT count(*) from dba_objects where object_name=:b1
END OF STMT
PARSE #2:c=15625,e=12991,p=0,cr=2,cu=0,mis=1,r=0,dep=1,og=0,tim=3007778748
BINDS #2:
bind 0: dty=1 mxl=32(30) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0
bfp=030a79ec bln=32 avl=01 flg=05
value="A"
EXEC #2:c=0,e=2167,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=3007782524
WAIT #2: nam='db file sequential read' ela= 9458 p1=1 p2=89 p3=1
WAIT #2: nam='db file sequential read' ela= 6606 p1=1 p2=26791 p3=1

 

 

test code highlighter

<pre lang="php"> <div id="foo"> <?php   function foo() {     echo "Hello World!\\n";   } ?> </div> </pre>

public class Hello {
  public static void main(String[] args) {
    System.out.println("Hello World!");
  }
}
public class Hello {
  public static void main(String[] args) {
    System.out.println("Hello World!");
  }
}
class Example
  def example(arg1)
    return "Hello: " + arg1.to_s
  end
end