Feed aggregator

I HAVE A DOUBT REGARDING CALCULATION OF AGE. WHY DO WE DIVIDE BY 365 WHILE FINDING AGE.IN MY OPINION IT HAS TO BE ONLY SYSDATE -DATEOFBIRTH

Hello AskTom team, Please correct me if I am wrong, as I understand, "extended statistics" is useful for multi-predicate/non indexed columns for achieving realistic cardinality estimates. Is there any DB level setting where Oracle generates the...

We are upgrading software that executes against an Oracle database. The vendor modified code to add a Select statement before issuing an existing Delete statement. The criteria of both commands were the same except the Select included a table that o...

Sir...i was laughed when i was seen one answer from you...That is..). How to add two numbers without using + and sum functions... Actually that is an interview question.When i was attended an interview at TCS then were asked me these type of quest...

Hi AskTom, First of all thanks for your contribution to oracle Community! :-) I have this problem where i want to parse a json file stored in clob column and after parsing i have to store it in separate table , i am able to parse this throu...

Hello. I have a quick question about Oracle 11g SQL command. I need to get only HH24:MI:SS data from SYSDATE; for example, select TO_DATE(SYSDATE, 'HH24:MI:SS') from dual; I tried it then error is like below. ORA-01861: literal does no...

hi tom, i have an insert statement which inserts select in specific table, the ids in this table are inserted by trigger and sequence and it works fine. it inserts 8600 row in about second which is fine. the question is, if something is viola...

my question is...if name of procedure along with procedure name and parameters are given in package specification but body of "procedure" is not given in package body will the procedure is going to work ..? if not ...why..? if yes then how..?

1)? How do you find the size of the data for an application? 2). How to add two numbers without using + and sum functions.

Rather than go through a full install of the Corente VPN configuration I thought I would post references to where I pulled tutorials. The obvious place to start is Corente Documentation. I would not start with this but would start with tutorials and workshops. The documentation is confusing and could lead you to locking your account and getting frustrated (trust me). Yesterday we talked about starting the install process. Normally we would go through a full install but rather than doing that I am going to reverence three documents that I found internal to Oracle.

• Corente Cloud Services Cookbook
• Corente VPN for PaaS and IaaS
• Corente VPN Workshop

My favorite of the three is the Workshop because it goes through pictures on how to install and configure with step by step instructions. It starts out by installing Linux in the Oracle Cloud and configuring it to be the App Manager console then gets the Orchestration up and running to start the gateway in the cloud. The single change that I would make with this configuration is to configure the on premise system using a Linux image in VirtualBox locally rather than doing it from Firefox on your desktop. If you follow the same steps that you did to spin up a Linux image and install the packages in the cloud, you can do the same steps in VirtualBox. This deviation starts on page 26 with an install of Linux on a local VirtualBox and goes back to the Workshop without skipping a beat. I was able to follow the 52 page Workshop easier than the 76 page Cookbook. On the flip side, I do like the focus on network configuration in the Cookbook document for the cloud Linux instance.

The net of all this discussion is that there are various ways to configure Corente. It is a time consuming project and you can't just click a button and make it work. If you want to integrate your Cisco of Juniper router in your data center there are online instructions on configuring your router. We will not go through this because we don't have access to hardware to configure and play with.

In summary, this should be the last posting on Corente. It is a very powerful tool that allows you to create a virtual private network between computers in your home, office, or data center and computers in the cloud. It allows you to configure a typical two tier configuration in the Oracle Cloud and hide the database from the public internet while giving your DBAs and developers direct connection to the database. It also allows you to replicate your production systems that are running in your data center and create a high availability site in the Oracle Cloud. This can be done using NFS or SMB file shares and rsync to keep files synchronized or DataGuard to replicate database data between a two servers. Corente VPN allows you to create a trusted and secure communication link with your data center and the Oracle Cloud.

For a long time, I was using *.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1551))’ without specifying the host name since it makes the configuration more dynamic.

Then one day, I ran into issues which I don’t remember and started to use host name for local_listener.

Today, I ran into issues after cloning VM and host name changed causing database not to start and I am now back to using local_listener without host name.

oracle@arrow1:DB1:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 29 06:29:26 2016

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

Connected to an idle instance.

SYS@DB1> startup;
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=arrow.localdomain)(PORT=1551))'
SYS@DB1> exit
Disconnected

++++++++++

oracle@arrow1:DB1:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ vi initDB1.ora
oracle@arrow1:DB1:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ cat initDB1.ora
*._dbms_sql_security_level=384
*.control_files='/oradata/DB1A/controlfile/o1_mf_cwj6476c_.ctl'#Oracle managed file
*.db_create_file_dest='/oradata'
*.db_name='DB1'
*.db_securefile='PREFERRED'
*.db_unique_name='DB1A'
*.global_names=TRUE
*.instance_name='DB1'
*.java_pool_size=64M
*.job_queue_processes=0
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1551))'
*.pga_aggregate_target=128M
*.sga_max_size=512M
*.streams_pool_size=64M

++++++++++

oracle@arrow1:DB1:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 29 06:30:17 2016

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

Connected to an idle instance.

SYS@DB1> startup;
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  2926472 bytes
Variable Size             478152824 bytes
Database Buffers           50331648 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.
SYS@DB1> 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@arrow1:DB1:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$

++++++++++

oracle@arrow1:DB1:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ lsnrctl start listener_12c

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 29-SEP-2016 06:36:16

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

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow1.localdomain)(PORT=1551)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     listener_12c
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                29-SEP-2016 06:36:16
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow1.localdomain)(PORT=1551)))
Services Summary...
Service "DB1" has 1 instance(s).
  Instance "DB1", status UNKNOWN, has 1 handler(s) for this service...
Service "test2" has 1 instance(s).
  Instance "test2", status UNKNOWN, has 1 handler(s) for this service...
Service "tmnt" has 1 instance(s).
  Instance "tmnt", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

++++++++++

oracle@arrow1:DB1:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 29 06:36:26 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

DB1:(SYS@DB1):PRIMARY> alter system register;

System altered.

DB1:(SYS@DB1):PRIMARY> 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@arrow1:DB1:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ lsnrctl status listener_12c

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 29-SEP-2016 06:36:46

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     listener_12c
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                29-SEP-2016 06:36:16
Uptime                    0 days 0 hr. 0 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow1.localdomain)(PORT=1551)))
Services Summary...
Service "DB1" has 1 instance(s).
  Instance "DB1", status UNKNOWN, has 1 handler(s) for this service...
Service "DB1A" has 1 instance(s).
  Instance "DB1", status READY, has 1 handler(s) for this service...
Service "test2" has 1 instance(s).
  Instance "test2", status UNKNOWN, has 1 handler(s) for this service...
Service "tmnt" has 1 instance(s).
  Instance "tmnt", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle@arrow1:DB1:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$

hello Sir, Could you pls. explain about this parameter consistent=y in exp. What is cross table consistency. Is it parent child relationship. Thanks, Js

Hi Tom, What would be the cause sudden SELECT query slowness in a Production Environment after database gets upgraded from Oracle 11.2.0.4 to Oracle 12c? Earlier these 2 SQL queries were ran less than 1 sec. Not its taking 30 to 40 sec's to exec...

Hey guys Wondering if you could please help with an issue I am having. I would like to output all weekdays (monday-friday) of the current week. For example if I run it anytime between September 26 and September 30 then it would output the we...

Hi Tom, I have a table in PCA database(11g R2) with 30 columns.It has POS data.Data volume is very high in first year,near about 5 TB.Next 3 yrs It will be 6TB approx.This table will be use for huge insert(Informatica will be used for load) like 2...

Hi tom, AS i have seen we are getting buffer busy waits and insertion is very slow then i increase the table ini_trans from 1 to 50 and continuous showing index lock for that i have increase index ini trans from 2 to 10 Now i have seen the inser...

Hi Tom, I have doubt regarding one of the scenarios in which boolean is used. For example, v_bool boolean:=TRUE; If I am doing in an expression: v_bool = TRUE then its fine but if I am writing v_bool is TRUE--- its throwing error ...

I have some hierarchical data I'd like to look at as if it were a tree. The core of the query looks like following. <code> SELECT CONNECT_BY_ROOT output_unit_id AS START_UNIT_ID, units.*, FROM units CONNECT BY PRIOR input_un...

I want to enable full SYS Context for tables in the Flashback Data Archive but I am concerned about space usage in the SYSTEM tablespace <code>exec dbms_flashback_archive.set_context_level(level => 'ALL'); </code> Can I move SYS_FBA_CONTEXT_AU...

My question is about the records kept in the controlfile. Here is a general backgroud for this question: I have a Primary database and a physical standby database both in 12C version. The redo log files on the primary database were so undersized (5...