DBA Blogs

PL/SQL how to quote variables?

Tom Kyte - Sat, 2016-09-17 02:06
Hi team askTom: This is not an export/import question, more of a pl/sql and 'quoting' question, and I always have trouble quoting my variables, especially those that get evaluated at run time. Here I am trying to export 2 schemas, so I wrote a ...
Categories: DBA Blogs

Will data returned from "select for update" once row lock is obtained always contain the latest committed values?

Tom Kyte - Sat, 2016-09-17 02:06
Hello Hopefully a quick question to confirm my understanding of "select for update" and data consistency. I have a primary JDBC thread with autocommit false that begins a transaction, selects a batch of row "for update skip locked", and recurse...
Categories: DBA Blogs

Wait event

Tom Kyte - Fri, 2016-09-16 07:46
Hi team, Can you pls brief me regarding this wait event because i have continuously and every time getting this event. So pls let me know what is the meaning of this event and how to resolve this what is the solution of this event. EVENT ...
Categories: DBA Blogs

how to recover table with out backup

Tom Kyte - Fri, 2016-09-16 07:46
Hi Tom, one table is dropped, there is no backup and no flashback enable, then how can we restore the table. thanks.
Categories: DBA Blogs

Audit commit delay

Tom Kyte - Fri, 2016-09-16 07:46
Hi team, Every day we got some alert like - AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail Could you pls let us know what is this and how to resolve this ? thanks
Categories: DBA Blogs

Partner Webcast – Oracle Streams Analytics platform - Is it time to reverse traditional analytics?

Real Time, Live, Immediate or Instant are all big keywords in everyday business. Oracle Stream Analytics (OSA) is a new tool, provided as a part of Oracle Event Processing technology platform. OSA is...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Purpose of Unique constraint

Tom Kyte - Thu, 2016-09-15 13:26
Hi Tom, I am totally confused about unique constraint. I was reading Oracle Database Concepts 11g Release 2 (11.2), where its written about unique constraint that: <i> A unique key constraint requires that every value in a column or set of co...
Categories: DBA Blogs

divide data in database with specific login id

Tom Kyte - Thu, 2016-09-15 13:26
Hi we have five projects that their all data saved in some tables in database, is there any way so that each project end user should only be able to access only their own project data, and must not able able access any other projects data.
Categories: DBA Blogs

difference of update with rownum without rownum

Tom Kyte - Thu, 2016-09-15 13:26
Hi tom, I have some doubts with below update statement <code>1. UPDATE emp SET empno = rownum; 2. UPDATE emp SET empno = 1;</code> question no 1-- first one executing with out errors and populating column with unique values while s...
Categories: DBA Blogs

ORA-00904 Invalid Identifier. Query works in 12c but not in 11g

Tom Kyte - Wed, 2016-09-14 19:06
Hi Team, I have a correlated query, trying to execute it on oracle 11g. The same query executes perfectly fine on 12c but it gives ORA-00904 Invalid Identifier u.col_1 on 11g. Can someone please explain what's causing the issue in 11g? SELEC...
Categories: DBA Blogs

MView refresh failing because of ORA-01858: a non-numeric character was found where a numeric was expected

Tom Kyte - Wed, 2016-09-14 19:06
Hi Tom, We have one view with DDL CREATE OR REPLACE FORCE VIEW FX_RATE AS SELECT QUOTED_CURR ,TO_DATE(UPDATE_TIME,'DD/MM/RR') AS UPDATE_TIME ,PRICE FROM RATES; RATES DDL is: CREATE TABLE RATES ( QUOTED_CURR VARCHAR2(3 CHAR), ...
Categories: DBA Blogs

Counting repeating values

Tom Kyte - Wed, 2016-09-14 19:06
Hello Sir I have a result set with 3 columns ( INCIDENT_DATE , unit & occurrence): say I have these values in the result set INC_DT UNIT OCCURRENCE 1/8/2015 17:00 24 144 1/14/2015 17:00 24 1489 3/17/2015 18:00 24 500 9/17/201...
Categories: DBA Blogs

Install OEM 13c Silent mode

Surachart Opun - Wed, 2016-09-14 09:15
How to install Oracle EM 13c? It's the first time that I have a chance to install Oracle EM 13c. I remember last time I installed Oracle EM 12c. I have to install weblogic first. On OEM 13c, I don't need to do something like that. It installs WLS -12.1.0.3 , JDK – 1.7.0_80.
In this post, I installed Oracle EM 13c Silent mode with "SMALL" deployment. I assume, I installed Oracle Database Software 12.1.0.2 (Software Only), downloaded "12.1.0.2 DB Template for EM 13.1.0.0 on Linux x86-64" template  and uncompressed it in $ORACLE_HOME/assistants/dbca/templates folder.
OS:
ip_local_port_range parameter is set between 11000 to 65000
soft nofiles = 30000
file max = 65536
Database:
optimizer_adaptive_features = FALSE
Then, created database by using template.
[oracle@em13c ~]$ diff /u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/dbca.rsp  dbca-emrep.rsp
78c78
< GDBNAME = "orcl12c.us.oracle.com"
---
> GDBNAME = "emrep"
205c205
< SID = "orcl12c"
---
> SID = "emrep"
265c265
< TEMPLATENAME = "General_Purpose.dbc"
---
> TEMPLATENAME = "12.1.0.2.0_Database_Template_for_EM13_1_0_0_0_Small_deployment.dbc"
286c286
< #SYSPASSWORD = "password"
---
> SYSPASSWORD = "password1"
296c296
< #SYSTEMPASSWORD = "password"
---
> SYSTEMPASSWORD = "password1"
467c467
< #DATAFILEDESTINATION =
---
> DATAFILEDESTINATION =  /u02/oradata
487c487
< #STORAGETYPE=FS
---
> STORAGETYPE=FS
525c525
< #CHARACTERSET = "US7ASCII"
---
> CHARACTERSET = "AL32UTF8"
[oracle@em13c ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@em13c ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@em13c ~]$ /u01/app/oracle/product/12.1.0/dbhome_1/bin/dbca -silent -responseFile dbca-emrep.rsp
Copying database files
1% complete
3% complete
10% complete
16% complete
 23% complete
30% complete
33% complete
Creating and starting Oracle instance
35% complete
40% complete
44% complete
49% complete
50% complete
53% complete
55% complete
Completing Database Creation
58% complete
62% complete
65% complete
75% complete
85% complete
88% complete
Running Custom Scripts
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/emrep/emrep.log" for further details.
Checked database after installing.
[oracle@em13c ~]$ . oraenv
ORACLE_SID = [oracle] ? emrep
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@em13c ~]$ sqlplus  / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 14 14:39:32 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> select instance_name from v$instance;
INSTANCE_NAME
----------------
emrep
SQL>  select name from v$datafile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u02/oradata/emrep/system01.dbf
/u02/oradata/emrep/sysaux01.dbf
/u02/oradata/emrep/undotbs01.dbf
/u02/oradata/emrep/users01.dbf
/u02/oradata/emrep/mgmt_depot.dbf
/u02/oradata/emrep/mgmt.dbf
/u02/oradata/emrep/mgmt_ad4j.dbf
7 rows selected.
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 /dbhome_1/dbs/spfileemrep.ora
SQL> show parameter optimizer_adaptive_features
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_features          boolean     FALSE
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/12.1.0/dbhome_1/dbs/arch
Oldest online log sequence     1
Current log sequence           1
SQL> show parameter control_files
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u02/oradata/emrep/control01.c
                                                 tl, /u02/oradata/emrep/control02.ctlConfigured listener.
[oracle@em13c ~]$ diff /u01/app/oracle/product/12.1.0/dbhome_1/assistants/netca/netca.rsp  /home/oracle/netca.rsp
46c46
< #SHOW_GUI=false
---
> SHOW_GUI=false
60c60
< #LOG_FILE=""/oracle12cHome/network/tools/log/netca.log""
---
> LOG_FILE=""/u01/app/oracle/product/12.1.0/dbhome_1/network/tools/log/netca.log""
113c113
< NSN_NUMBER=1
---
> #NSN_NUMBER=1
117c117
< NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
---
> #NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
121c121
< NSN_SERVICE={"PLSExtProc"}
---
> #NSN_SERVICE={"PLSExtProc"}
127c127
< NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}
---
> #NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}
[oracle@em13c ~]$ netca -silent -orahome /u01/app/oracle/product/12.1.0/dbhome_1 -responsefile /home/oracle/netca.rsp
Wed Sep 14 15:26:52 ICT 2016 Oracle Net Configuration Assistant
Parsing command line arguments:
    Parameter "silent" = true
    Parameter "orahome" = /u01/app/oracle/product/12.1.0/dbhome_1
    Parameter "responsefile" = /home/oracle/netca.rsp
    Parameter "log" = /u01/app/oracle/product/12.1.0/dbhome_1/network/tools/log/netca.log
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
    Running Listener Control:
      /u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl start LISTENER
    Listener Control complete.
    Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0
[oracle@em13c ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 14-SEP-2016 15:27:35
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=em13c.surachartopun.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                14-SEP-2016 15:26:52
Uptime                    0 days 0 hr. 0 min. 42 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/em13c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=em13c.surachartopun.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "emrep" has 1 instance(s).
  Instance "emrep", status READY, has 1 handler(s) for this service...
Service "emrepXDB" has 1 instance(s).
  Instance "emrep", status READY, has 1 handler(s) for this service...
The command completed successfully
It was time to install Oracle EM 13c Silent mode. 
  • Uncompressed software and created response file.
[oracle@em13c scratch]$ ls em13100*
em13100_linux64-2.zip  em13100_linux64-3.zip  em13100_linux64-4.zip  em13100_linux64-5.zip  em13100_linux64.bin
[oracle@em13c scratch]$ ./em13100_linux64.bin -getResponseFileTemplates -outputLoc /home/oracle/RSP
0%...............................................................100%
Launcher log file is /tmp/OraInstall2016-09-14_03-31-39PM/launcher2016-09-14_03-31-39PM.log.
Copying response file template(s)
to /home/oracle/RSP ...
  Copying response file template upgrade.rsp
  Copying response file template software_only.rsp
  Copying response file template new_install.rsp
Finished copying response file template(s)
  • Modified response file and installed OEM13c. (I chose to use new install)
[oracle@em13c scratch]$ diff /home/oracle/RSP/new_install.rsp  /home/oracle/RSP/my-new_install.rsp
38c38
< UNIX_GROUP_NAME=<string>
---
> UNIX_GROUP_NAME=dba
46c46
< INVENTORY_LOCATION=<string>
---
> INVENTORY_LOCATION=/u01/app/oraInventory
60c60
< DECLINE_SECURITY_UPDATES=false
---
> DECLINE_SECURITY_UPDATES=true
67c67
< MYORACLESUPPORT_USERNAME=<string>
---
> #MYORACLESUPPORT_USERNAME=<string>
74c74
< MYORACLESUPPORT_PASSWORD=<string>
---
> #MYORACLESUPPORT_PASSWORD=<string>
92c92
< STAGE_LOCATION=<string>
---
> #STAGE_LOCATION=<string>
101c101
< MYORACLESUPPORT_USERNAME_FOR_SOFTWAREUPDATES=<string>
---
> #MYORACLESUPPORT_USERNAME_FOR_SOFTWAREUPDATES=<string>
110c110
< MYORACLESUPPORT_PASSWORD_FOR_SOFTWAREUPDATES=<string>
---
> #MYORACLESUPPORT_PASSWORD_FOR_SOFTWAREUPDATES=<string>
117c117
< PROXY_USER=<string>
---
> #PROXY_USER=<string>
124c124
< PROXY_PWD=<string>
---
> #PROXY_PWD=<string>
131c131
< PROXY_HOST=<string>
---
> #PROXY_HOST=<string>
138c138
< PROXY_PORT=<string>
---
> #PROXY_PORT=<string>
145c145
< ORACLE_MIDDLEWARE_HOME_LOCATION=<string>
---
> ORACLE_MIDDLEWARE_HOME_LOCATION=/u01/app/oracle/mw13c
153c153
< ORACLE_HOSTNAME=<string>
---
> ORACLE_HOSTNAME=em13c.surachartopun.com
160c160
< AGENT_BASE_DIR=<string>
---
> AGENT_BASE_DIR=/u01/app/oracle/agent13c
167c167
< WLS_ADMIN_SERVER_USERNAME=<string>
---
> WLS_ADMIN_SERVER_USERNAME=weblogic
174c174
< WLS_ADMIN_SERVER_PASSWORD=<string>
---
> WLS_ADMIN_SERVER_PASSWORD=password1
181c181
< WLS_ADMIN_SERVER_CONFIRM_PASSWORD=<string>
---
> WLS_ADMIN_SERVER_CONFIRM_PASSWORD=password1
188c188
< NODE_MANAGER_PASSWORD=<string>
---
> NODE_MANAGER_PASSWORD=password1
195c195
< NODE_MANAGER_CONFIRM_PASSWORD=<string>
---
> NODE_MANAGER_CONFIRM_PASSWORD=password1
202c202
< ORACLE_INSTANCE_HOME_LOCATION=<string>
---
> ORACLE_INSTANCE_HOME_LOCATION=/u01/app/oracle/gc_inst
221c221
< SOFTWARE_LIBRARY_LOCATION=<string>
---
> SOFTWARE_LIBRARY_LOCATION=/u01/app/oracle/swlib
229c229
< DATABASE_HOSTNAME=<string>
---
> DATABASE_HOSTNAME=em13c.surachartopun.com
237c237
< LISTENER_PORT=<string>
---
> LISTENER_PORT=1521
245c245
< SERVICENAME_OR_SID=<string>
---
> SERVICENAME_OR_SID=emrep
253c253
< SYS_PASSWORD=<string>
---
> SYS_PASSWORD=password1
261c261
< SYSMAN_PASSWORD=<string>
---
> SYSMAN_PASSWORD=password1
268c268
< SYSMAN_CONFIRM_PASSWORD=<string>
---
> SYSMAN_CONFIRM_PASSWORD=password1
275c275
< DEPLOYMENT_SIZE=MEDIUM
---
> DEPLOYMENT_SIZE=SMALL
283c283
< MANAGEMENT_TABLESPACE_LOCATION=<string>
---
> #MANAGEMENT_TABLESPACE_LOCATION=<string>
291c291
< CONFIGURATION_DATA_TABLESPACE_LOCATION=<string>
---
> #CONFIGURATION_DATA_TABLESPACE_LOCATION=<string>
299c299
< JVM_DIAGNOSTICS_TABLESPACE_LOCATION=<string>
---
> #JVM_DIAGNOSTICS_TABLESPACE_LOCATION=<string>
306c306
< AGENT_REGISTRATION_PASSWORD=<string>
---
> AGENT_REGISTRATION_PASSWORD=password1
313c313
< AGENT_REGISTRATION_CONFIRM_PASSWORD=<string>
---
> AGENT_REGISTRATION_CONFIRM_PASSWORD=password1
320c320
< STATIC_PORTS_FILE=<string>
---
> #STATIC_PORTS_FILE=/u01/app/oracle/mw13c/install/portlist.ini
368c368
< CONFIG_LOCATION=<string>
---
> #CONFIG_LOCATION=<string>
376c376
< CLUSTER_LOCATION=<string>
---
> #CLUSTER_LOCATION=<string>Note: As I created database from template. So. I didn't set *TABLESPACE_LOCATION variables. For variables, that didn't define. we should comment it.
[oracle@em13c scratch]$ ./em13100_linux64.bin -silent -ignoreSysPrereqs  -responseFile /home/oracle/RSP/my-new_install.rsp
0%...............................................................100%
Launcher log file is /tmp/OraInstall2016-09-14_05-56-59PM/launcher2016-09-14_05-56-59PM.log.
Starting Oracle Universal Installer
Checking if CPU speed is above 300 MHz.   Actual 2300.094 MHz    Passed
Checking swap space: must be greater than 512 MB.   Actual 0 MB    Failed <<<<
Checking if this platform requires a 64-bit JVM.   Actual 64    Passed (64-bit not required)
Checking temp space: must be greater than 300 MB.   Actual 32844 MB    Passed
>>> Ignoring failure(s) of required prerequisite checks.  Continuing...

Preparing to launch the Oracle Universal Installer from /tmp/OraInstall2016-09-14_05-56-59PM
====Prereq Config Location main===
/tmp/OraInstall2016-09-14_05-56-59PM/stage/prereq
EMGCInstaller args -scratchPath
EMGCInstaller args /tmp/OraInstall2016-09-14_05-56-59PM
EMGCInstaller args -sourceType
EMGCInstaller args network
EMGCInstaller args -timestamp
EMGCInstaller args 2016-09-14_05-56-59PM
EMGCInstaller args -paramFile
EMGCInstaller args /tmp/sfx_g7ViFx/Disk1/install/linux64/oraparam.ini
EMGCInstaller args -silent
EMGCInstaller args -ignoreSysPrereqs
EMGCInstaller args -responseFile
EMGCInstaller args /home/oracle/RSP/my-new_install.rsp
EMGCInstaller args -nocleanUpOnExit
DiskLoc inside SourceLoc/u02/SRC/EM13c/scratch
EMFileLoc:/tmp/OraInstall2016-09-14_05-56-59PM/oui/em/
ScratchPathValue :/tmp/OraInstall2016-09-14_05-56-59PM
EMGCInstallUpdatesInfoOnNext:: calling actionOnClickofNext
Now in EMGCInstallUpdatesInfoOnNext.actionsOnClickofNext
EMGCInstallUpdatesInfoOnNext:: End of actionOnClickofNext
Session log file is /tmp/OraInstall2016-09-14_05-56-59PM/install2016-09-14_05-56-59PM.log
.
.
.
Setup in progress (Wednesday, September 14, 2016 6:03:38 PM ICT)
Setup successful
Saving inventory (Wednesday, September 14, 2016 6:03:38 PM ICT)
Saving inventory complete
End of install phases.(Wednesday, September 14, 2016 6:03:39 PM ICT)
&Decline License Agreement/u01/app/oracle/mw13c/sysman/install/plugins/oracle.sysman.si/13.1.1.0.0/oracle.sysman.si.discovery.plugin-13.1.1.0.0.farb
Session log file is /tmp/OraInstall2016-09-14_05-56-59PM/install2016-09-14_05-56-59PM.log
........................................................................
Installation in progress (Wednesday, September 14, 2016 6:03:40 PM ICT)
                                                        72% Done.
Install successful
Linking in progress (Wednesday, September 14, 2016 6:03:40 PM ICT)
Link successful
Setup in progress (Wednesday, September 14, 2016 6:03:40 PM ICT)
Setup successful
Saving inventory (Wednesday, September 14, 2016 6:03:40 PM ICT)
Saving inventory complete
End of install phases.(Wednesday, September 14, 2016 6:03:41 PM ICT)
OMS OracleHome :/u01/app/oracle/mw13c
Applying the required one-off patches.
13NGCHEKAGGREGATE  : oracle.sysman.top.agent
13NGCHEKAGGREGATE  : oracle.sysman.top.oms
13NGCHEKAGGREGATE  : encap_oms
13NGCHEKAGGREGATE  : OuiConfigVariables
13NGCHEKAGGREGATE  : OuiConfigVariables
2016-09-14_06-07-13PM: Configuration Assistant "Plugins Prerequisites Check" is in progress.
2016-09-14_06-07-32PM: Configuration Assistant "Plugins Prerequisites Check" has Succeeded.
2016-09-14_06-07-32PM: Configuration Assistant "Repository Out Of Box Configuration" is in progress.
2016-09-14_06-42-39PM: Configuration Assistant "Repository Out Of Box Configuration" has Succeeded.
2016-09-14_06-42-39PM: Configuration Assistant "OMS Configuration" is in progress.

 Executing the OMSCA command...
 Check the log files of the OMS Configuration Assistant at: /u01/app/oracle/mw13c/cfgtoollogs/omsca


 OMS Configuration Assistant completed successfully.

2016-09-14_06-51-03PM: Configuration Assistant "OMS Configuration" has Succeeded.
2016-09-14_06-51-03PM: Configuration Assistant "Plugins Deployment and Configuration" is in progress.

2016-09-14_06-59-48PM: Configuration Assistant "Plugins Deployment and Configuration" has Succeeded.
2016-09-14_06-59-48PM: Configuration Assistant "BI Publisher Configuration" is in progress.
2016-09-14_07-05-20PM: Configuration Assistant "BI Publisher Configuration" has Succeeded.
2016-09-14_07-05-20PM: Configuration Assistant "Start Oracle Management Service" is in progress.
Starting OMS ...
Executing the command: /u01/app/oracle/mw13c/bin/emctl start oms
Starting of OMS is successful.
Starting export oms config...
Executing command: /u01/app/oracle/mw13c/bin/emctl exportconfig oms -dir  /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/backup
Export config of OMS is successful.
2016-09-14_07-09-43PM: Configuration Assistant "Start Oracle Management Service" has Succeeded.
2016-09-14_07-09-43PM: Configuration Assistant "Agent Configuration Assistant" is in progress.
Getting Inet Addresses for host em13c.surachartopun.com
** Agent Port Check completed successfully.**
AgentConfiguration:agent configuration has been started
Validating OMS_HOST and EM_UPLOAD_PORT
Performing free port detection..
AgentConfiguration: Executing emctl deploy agent command...
AgentConfiguration: Executing emctl config agent command...
AgentConfiguration:agent configuration finished with status = true
2016-09-14_07-12-12PM: Configuration Assistant "Agent Configuration Assistant" has Succeeded.
*** The Installation was Successful. ***

This information is also available at:
        /u01/app/oracle/mw13c/install/setupinfo.txt
See the following for information pertaining to your Enterprise Manager installation:

Use the following URL to access:
        1. Enterprise Manager Cloud Control URL: https://em13c.surachartopun.com:7803/em
        2. Admin Server URL: https://em13c.surachartopun.com:7102/console
        3. BI Publisher URL: https://em13c.surachartopun.com:9803/xmlpserver
The following details need to be provided while installing an additional OMS:
        1. Admin Server Host Name: em13c.surachartopun.com
        2. Admin Server Port: 7102
You can find the details on ports used by this deployment at : /u01/app/oracle/mw13c/install/portlist.ini


 NOTE:
 An encryption key has been generated to encrypt sensitive data in the Management Repository. If this key is lost, all encrypted data in the Repository becomes unusable.
 A backup of the OMS configuration is available in /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/backup on host em13c.surachartopun.com. See Cloud Control Administrators Guide for details on how to back up and recover an OMS.
 NOTE: This backup is valid only for the initial OMS configuration. For example, it will not reflect plug-ins installed later, topology changes like the addition of a load balancer, or changes to other properties made using emctl or emcli. Backups should be created on a regular basis to ensure they capture the current OMS configuration. Use the following command to backup the OMS configuration:
/u01/app/oracle/mw13c/bin/emctl exportconfig oms -dir <backup dir>
Logs successfully copied to /u01/app/oraInventory/logs.
Note: su to "root" and run /u01/app/oracle/mw13c/allroot.sh script.
Starting to execute allroot.sh .........
Starting to execute /u01/app/oracle/mw13c/root.sh ......
/etc exist
Creating /etc/oragchomelist file...
/u01/app/oracle/mw13c
Finished product-specific root actions.
/etc exist
Finished execution of  /u01/app/oracle/mw13c/root.sh ......

Starting to execute /u01/app/oracle/agent13c/agent_13.1.0.0.0/root.sh ......
Finished product-specific root actions.
/etc exist
Finished execution of  /u01/app/oracle/agent13c/agent_13.1.0.0.0/root.sh ......

[root@em13c ~]# cat /etc/oragchomelist
/u01/app/oracle/mw13c
/u01/app/oracle/agent13c/agent_13.1.0.0.0:/u01/app/oracle/agent13c/agent_inst
[root@em13c ~]# find /etc/ -name ???gcstartup
/etc/rc.d/rc2.d/S98gcstartup
/etc/rc.d/rc2.d/K19gcstartup
/etc/rc.d/rc3.d/S98gcstartup
/etc/rc.d/rc3.d/K19gcstartup
/etc/rc.d/rc5.d/S98gcstartup
/etc/rc.d/rc5.d/K19gcstartup
[root@em13c ~]# cat /etc/oragchomelist
/u01/app/oracle/mw13c
/u01/app/oracle/agent13c/agent_13.1.0.0.0:/u01/app/oracle/agent13c/agent_inst
[root@em13c ~]# find /etc/ -name gcstartup
/etc/rc.d/init.d/gcstartup
[root@em13c ~]# find /etc/ -name ???gcstartup
/etc/rc.d/rc2.d/S98gcstartup
/etc/rc.d/rc2.d/K19gcstartup
/etc/rc.d/rc3.d/S98gcstartup
/etc/rc.d/rc3.d/K19gcstartup
/etc/rc.d/rc5.d/S98gcstartup
/etc/rc.d/rc5.d/K19gcstartup
  • After installed, I was able to check OMS and agent status.
[oracle@em13c scratch]$  /u01/app/oracle/mw13c/bin/emctl status oms -details
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
Console Server Host        : em13c.surachartopun.com
HTTP Console Port          : 7788
HTTPS Console Port         : 7803
HTTP Upload Port           : 4889
HTTPS Upload Port          : 4903
EM Instance Home           : /u01/app/oracle/gc_inst/em/EMGC_OMS1
OMS Log Directory Location : /u01/app/oracle/gc_inst/em/EMGC_OMS1/sysman/log
OMS is not configured with SLB or virtual hostname
Agent Upload is locked.
OMS Console is locked.
Active CA ID: 1
Console URL: https://em13c.surachartopun.com:7803/em
Upload URL: https://em13c.surachartopun.com:4903/empbs/upload
WLS Domain Information
Domain Name            : GCDomain
Admin Server Host      : em13c.surachartopun.com
Admin Server HTTPS Port: 7102
Admin Server is RUNNING
Oracle Management Server Information
Managed Server Instance Name: EMGC_OMS1
Oracle Management Server Instance Host: em13c.surachartopun.com
WebTier is Up
Oracle Management Server is Up
JVMD Engine is Up
BI Publisher Server Information
BI Publisher Managed Server Name: BIP
BI Publisher Server is Up
BI Publisher HTTP Managed Server Port   : 9701
BI Publisher HTTPS Managed Server Port  : 9803
BI Publisher HTTP OHS Port              : 9788
BI Publisher HTTPS OHS Port             : 9851
BI Publisher is locked.
BI Publisher Server named 'BIP' running at URL: https://em13c.surachartopun.com:9851/xmlpserver
BI Publisher Server Logs: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
BI Publisher Log        : /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/bipublisher/bipublisher.log
[oracle@em13c scratch]$  /u01/app/oracle/agent13c/agent_13.1.0.0.0/bin/emctl status agent
[Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 13.1.0.0.0
OMS Version            : 13.1.0.0.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/agent13c/agent_inst
Agent Log Directory    : /u01/app/oracle/agent13c/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/agent13c/agent_13.1.0.0.0
Core JAR Location      : /u01/app/oracle/agent13c/agent_13.1.0.0.0/jlib
Agent Process ID       : 26632
Parent Process ID      : 26568
Agent URL              : https://em13c.surachartopun.com:3872/emd/main/
Local Agent URL in NAT : https://em13c.surachartopun.com:3872/emd/main/
Repository URL         : https://em13c.surachartopun.com:4903/empbs/upload
Started at             : 2016-09-14 19:11:49
Started by user        : oracle
Operating System       : Linux version 3.10.0-327.28.3.el7.x86_64 (amd64)
Number of Targets      : 32
Last Reload            : (none)
Last successful upload                       : 2016-09-14 20:03:22
Last attempted upload                        : 2016-09-14 20:03:22
Total Megabytes of XML files uploaded so far : 1.56
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 40.54%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2016-09-14 20:05:00
Last successful heartbeat to OMS             : 2016-09-14 20:05:00
Next scheduled heartbeat to OMS              : 2016-09-14 20:06:00
---------------------------------------------------------------
Agent is Running and Ready
  • Connected OEM 13c by browser.




Reference:
http://www.oracle.com/technetwork/oem/install-upgrade/pr-install-em13-2835293.pdf


Categories: DBA Blogs

Links for 2016-09-13 [del.icio.us]

Categories: DBA Blogs

Disable flashback archive

Tom Kyte - Wed, 2016-09-14 00:46
For lower environment , we obfuscate data by updating. Before obfuscation, I want to turn off flashback archive so that it tablespace would not run out of space. How do I turn off flashback archive and turn it back again? Thanks.
Categories: DBA Blogs

Query session show inactive but having long operation running

Tom Kyte - Wed, 2016-09-14 00:46
Why the query session showing inactive but the long operation still in progress and running ?
Categories: DBA Blogs

Before Investing in Data Archiving, Ask These Questions

Kubilay Çilkara - Tue, 2016-09-13 15:39
Data has a way of growing so quickly that it can appear unmanageable, but that is where data archiving shines through.  The tool of archiving helps users maintain a clear environment on their primary storage drives.  It is also an excellent way to keep backup and recovery spheres running smoothly.  Mistakes and disasters can and will happen, but with successful archiving, data can be easily recovered so as to successfully avert potential problems.  Another benefit to archiving data is that companies are actually saving themselves the very real possibility of a financial headache!  In the end, this tool will help to cut costs in relation to effectively storing and protecting your data.  Who doesn’t want that?  It’s a win-win situation for everyone involved!

When It Comes to Archiving Data for Storage, Where Do I Begin?
It may feel stressful at first to implement a data archiving plan into your backup and storage sphere, but don’t be worried!  To break it down, data simply needs to go from one place into another.  In other words, you are just moving data!  Sounds easy enough, right?  If things get sticky however, software is available on the market that can help users make better decisions and wise choices when it comes to moving your data.  In your preparation to incorporate archiving, you must ask yourself some important questions before you start.  This way, companies will be guaranteed to be making choices that meet the demands of their particular IT environment.  So, don’t stress-just be well prepared and ask the right questions!  Right now, you may be wondering what those questions are.  Below you will see a few of them that are most necessary to ask in order to guarantee your archiving success. 

What You Should Be Asking: 

1.      With respect to your data, what type is that you need to store?  “Cold” data may not be something you are familiar with, but this is a term used regarding data that has been left untouched for over 6 months.  If this is the type of data you are trying to store, good for you!  This particular type of data must be kept secure in the event that it is needed for compliance audits or even for a possible legal situation that could come up down the road.  At any rate, all data -regardless of why it is being stored- must be taken care of appropriately so as to ensure security and availability over time.  You can be assured you have invested in a good archiving system if it is able to show you who viewed the data and at what time the data was viewed. 

2.     Are you aware of the particular technology you are using?  It is essential to realize that data storage is highly dependent upon two factors:  the hardware and the media you are using.  Keep in mind that interfaces are a very important factor as well, and they must be upgraded from time to time.  A point to consider when storing data is that tape has an incredibly long life, and with low usage and proper storage it could potentially last for 100 years!  Wow!  However, there are arguments over which has the potential to last longer...tape or hard disk.  Opponents towards the long life of tape argue that with proper powering down of drives, hard disk will actually outlive tape.  Here is where the rubber meets the road to this theory.  It has shown to be problematic over time, and can leave users with DUDL (Data Unavailable Data Loss).  This problem IS as terrible as it sounds.  Regardless of the fact that SSD lack mechanical parts, their electronic functions results in cell degradation, ultimately causing burnout.  It is unknown, even to vendors, what the SSD life is, but most say 3 years is what they are guaranteed for.  Bear in mind, as man-made creations, these tools will over time die.  Whatever your choice in technology, you must be sure to PLAN and TEST.  These two things are the most essential tasks to do in keeping data secure over time!

3.     Do you know the format of your data?  It is important to acknowledge that “bits” have to be well-kept in order to ensure that your stored data will be usable over the long run.  Investing in appropriate hardware to accurately read data as well as to interpret it is essential.  It is safe to say that investing in such a tool is an absolute MUST in order to ensure successful long-term storage!

4.     Is the archive located on the cloud or in your actual location?  Did you know this era is one in which storing your data on the cloud is a viable means of long-term archiving?  Crazy, isn’t it!  Truth be told, access to the cloud has been an incredibly helpful advancement when it comes to storage, but as all man-made inventions prove, problems are unavoidable.  Regarding cloud storage, at this point in time, the problems arise with respect to long-term retention.  However, many enjoy the simplicity of pay-as-you-go storage options available with the cloud.  Users also like relying upon particular providers to assist them with their cloud storage management.  In looking to their providers, people are given information such as what the type, age, and interface of their storage devices are.  You might be asking yourself why this is so appealing.  The answer is simple.  Users ultimately gain the comfort of knowing that they can access their data at anytime, and that it will always be accessible.   At this point, many are wondering what the downsides are of using the cloud for storage.  Obviously, data will grow over time, inevitably causing your cloud to grow which in turn will raise your cloud storage costs.  Users will be happy to know however, that cloud storage is actually a more frugal choice than opting to store data in a data center.  In the end, the most common compliment regarding cloud storage is the TIME involved in trying to access data in the event of a necessary recovery, restore, or a compliance issue.  Often times, these issues are quite sensitive with respect to timing, therefore data must be available quickly in case of a potential disaster.   

5.     What facts do you know about your particular data?  There is often sufficient knowledge of storage capacity, but many are not able to bring to mind how much data is stored per application.  Also, many do not know who the owner of particular data is, as well as what age the data may be.  The good news is that there is software on the market that helps administrators quickly figure these factors out.  This software produces reports with the above information by scanning environments.  With the help of these reports, it can be quickly understood what data is needed and in turn, will efficiently archive that data.  All it takes is a push of a button to get these clear-cut reports with all the appropriate information on them.  What could be better than that?
In Closing, Companies MUST Archive!


In a world that can be otherwise confusing, companies must consider the importance of archiving in order to make their data and storage management easier to grasp.  Maybe if users understood that archiving is essential in order to preserve the environment of their IT world, more people would quickly jump on board.  The best news is that archiving can occur automatically, taking the guesswork out of the process.  Archiving is a sure step to keeping systems performing well, budgets in line, and data available and accessible.  Be sure to prepare yourself before investing in data archiving by asking the vital questions laid out above.  This way, you can be certain you have chosen a system which will fully meet your department needs!

Writer Jason Zhang is the product marketing person for Rocket Software's Backup, Storage, and Cloud solutions.
Categories: DBA Blogs

Finding the Right Tools to Manage Data in Today’s Modern World

Kubilay Çilkara - Tue, 2016-09-13 15:37
A common concern among companies is if their resources are being used wisely.   Never before has this been more pressing than when considering data storage options.  Teams question the inherent worth of their data, and often fall into the trap of viewing it as an expense that weighs too heavily on the budget.  This is where wisdom is needed with respect to efficient resource use and the task of successful data storage.   Companies must ask themselves how storing particular data will benefit their business overall.  Incorporating a data storage plan into a business budget has certainly proven to be easier said than done.  Many companies fail at carrying out their desires to store data once they recognize the cost associated with the tools that are needed.  You may be wondering why the failure to follow through on these plans is so common.  After all, who wouldn’t want to budget in such an important part of company security?  The truth of the matter is that it can all be very overwhelming once the VAST amount of data that actually exists is considered, and it can be even more stressful to attempt to manage it all.

When considering what tools to use for management of one’s data, many administrators think about using either the cloud or virtualization.  Often times during their research, teams question the ability of these two places to successfully house their data.  Truth be told, both the cloud and virtualization are very reliable, but are equally limited with respect to actually managing data well.  What companies really need is a system that can effectively manage huge amounts of data, and place it into the right categories.  In doing so, the data becomes much more valuable. 

Companies must change their mindsets regarding their stored data.  It is time to put away the thoughts that data being stored is creating a money pit.  Many times, people adopt an “out with the old, in with the new” philosophy, and this is no different with respect to storing old data.  The truth is, teams eventually want new projects to work on, especially since some of the old data storage projects can appear to be high maintenance.  After all, old data generally needs lots of backups and archiving, and is actually quite needy.  These negative thoughts need to fade away however, especially in light of the fact that this data being stored and protected is a staple to company health and security.  Did you know that old and tired data actually holds the very heart of a company?  It is probable that many forget that fact, since they are so quick to criticize it!  Also notable is the fact that this data is the source of a chunk of any given business’s income.  Good to know, isn’t it?!  Poor management of data would very likely be eradicated if more teams would keep the value of this data on the forefront of their minds.  It is clear that the task of managing huge amounts of company data can be intimidating and overwhelming, but with the right attitude and proper focus, it is not impossible. 

The best news yet is that there are excellent TOOLS that exist in order to help companies take on the magnanimous task of managing company data.  In other words, administrators don’t have to go at it alone!  The resources out there will assist users with classifying and organizing data, which is a huge relief NOT to have to do manually.  When teams readily combine these tools with their understanding of business, they have a sure recipe for success when it comes to management.  To break it down even further, let’s use a more concrete example.  Imagine you are attempting to find out information about all the chemical engineers in the world.  You must ask yourself how you would go about doing this in the most efficient manner.  After all, you would clearly need to narrow down your search since there are more than 7 billion individuals that exist on this planet.  Obviously, you wouldn’t need to gather information on every single human being, as this would be a huge time waster.  On the contrary, to make things more streamlined, you would likely scope out and filter through various groups of people and categorize them by profession.  Maybe you would consider a simple search in engineering graduates.  The above example in organizing information is probably the method most people use to organize simple things in their life on a daily basis.  Keep in mind, having these tools to help streamline data is one thing, but one must also possess a good understanding of business plans, as this will assist for a better grasp on corporate data.  With these things in line, businesses can be assured that their data management systems will be in good hands and running smoothly. 

What a great gift to be alive during this time, with the easy access to modern tools which help make management of data much more understandable to those in charge.  These valuable resources also help to create confidence in administrators so that they feel well-equipped to navigate the sometimes harsh IT world.  For example, during the changes with data types and volumes, these tools assist in helping to lower the storage capacity needs, lower costs on backup and recovery, and aid in the passing of compliance with flying colors.  In fact, many in charge can rest their heads at night knowing their companies are totally in line with modern-day regulatory compliance rules. 


In the end, the value of wisdom with respect to making various decisions in an IT department is incredibly important.  Arguably one of the most areas which this applies to is in the sphere of data management.  The truth is, dependence upon tools alone to navigate the sometimes rough waters of the IT world will not be enough to get teams through.  As stated above, wisdom and the right attitude regarding data and its importance to company health and security are vital to proper management.  In addition, clients ought to be looking for resources to assist them with organizing and classifying their systems of files.  The point is clear:  intelligence paired with the proper tools will give companies exactly what they need for efficient and effective data management.  At the end of the day, users can rest easy with the knowledge that their data -which is the bread and butter of their companies- is in good hands.  What more could you ask for?

Writer Jason Zhang is the product marketing person for Rocket Software's Backup, Storage, and Cloud solutions.
Categories: DBA Blogs

Calculate compound interest using SQL

Tom Kyte - Tue, 2016-09-13 06:26
Hi, I am trying to create a logic. Please help me out. Suppose i have brought a fund in Jan 2015 which is going to mature in year 2018.I am receiving the interest Semi-Annually. I want to check the interest received From OCT 16 to SEP 17. Bas...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs