Tuesday, June 14, 2016

Oracle utl_file create file sample 

SQL> CREATE DIRECTORY dump_dir AS '/u01/app/oracle/dump';

 
SQL>
DECLARE
  f1 UTL_FILE.FILE_TYPE;
BEGIN
  f1:= UTL_FILE.FOPEN('test_dir', 'test_file.txt', 'W');
  UTL_FILE.PUTF(f1, 'Writing');
  UTL_FILE.FCLOSE(f1);
END;
/
Posted by at No comments:

Sunday, May 8, 2016

Oracle Tablespace Growth


SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
  SELECT a.days, a.tsname, used_size_mb
  , used_size_mb - LAG (used_size_mb,1)  OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM (
      SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
       ,ts.tsname
       ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
      FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts
       ,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
      WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
       AND ts.tsname = dt.tablespace_name  AND sp.begin_interval_time > sysdate-7
      GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
      ORDER BY ts.tsname, days
  ) A
) b GROUP BY b.tsname ORDER BY b.tsname
/
Posted by at No comments:

Tuesday, February 2, 2016

Configure Oracle Wallet at Client location

SHAREDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = consdb121-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sharedb)
    )
  )


SQL> conn system/oracle@SHAREDB
Ú‘±‚³‚ê‚Ü‚µ‚½B

1.Create wallet on client

[oracle@consdb121n1 tmp]$
/u01/app/oracle/product/12.1.0/dbhome1/bin/mkstore -wrl /tmp -create
Oracle Secret Store Tool: ƒo[ƒWƒ‡ƒ“12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights
reserved.

password:Oracle123#
re-enter password :Oracle123#

2.Check that the file was created

[oracle@consdb121n1 admin]$ cd /tmp
[oracle@consdb121n1 tmp]$ ls -lrt *wallet*
-rw-rw-rw- 1 oracle oinstall   0  8ŒŽ  6 16:06 2015 ewallet.p12.lck
-rw------- 1 oracle oinstall  75  8ŒŽ  6 16:06 2015 ewallet.p12
-rw-rw-rw- 1 oracle oinstall   0  8ŒŽ  6 16:06 2015 cwallet.sso.lck
-rw------- 1 oracle oinstall 120  8ŒŽ  6 16:06 2015 cwallet.sso

3.

[oracle@consdb121n1 tmp]$ mkstore -wrl /tmp -createCredential
SHAREDB_TMP system
Oracle Secret Store Tool: ƒo[ƒWƒ‡ƒ“12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights
reserved.

secret/password is missing
secret/password:oracle
re-enter secret/password:oracle
oracle wallet password:Oracle123#
Create credential oracle.security.client.connect_string1

4.Check that the filesize has increased

[oracle@consdb121n1 tmp]$ ls -lrt *wallet*
-rw-rw-rw- 1 oracle oinstall   0  8ŒŽ  6 16:06 2015 ewallet.p12.lck
-rw-rw-rw- 1 oracle oinstall   0  8ŒŽ  6 16:06 2015 cwallet.sso.lck
-rw------- 1 oracle oinstall 536  8ŒŽ  6 16:10 2015 ewallet.p12
-rw------- 1 oracle oinstall 581  8ŒŽ  6 16:10 2015 cwallet.sso

5.Copy connectivity related files to /tmp on client

[oracle@consdb121n1 tmp]$ cat  /tmp/sqlnet.ora
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = /tmp)
  )
 )


cp /u01/app/oracle/product/12.1.0/dbhome1/network/admin/tnsnames.ora
/tmp/tnsnames.ora

[oracle@consdb121n1 tmp]$ cat /tmp/tnsnames.ora
# tnsnames.ora Network Configuration File:
/u01/app/oracle/product/12.1.0/dbhome1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SHAREDB_TMP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = consdb121-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sharedb)
    )
  )


6.connection

export TNS_ADMIN=/tmp
sqlplus system/oracle@SHAREDB_TMP

7.connection

export TNS_ADMIN=/tmp
sqlplus /@SHAREDB_TMP

SQL*Plus: Release 12.1.0.2.0 Production on –Ø 8ŒŽ 6 16:40:38 2015

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

ÅI³íƒƒOƒCƒ“ŽžŠÔ: –Ø 8ŒŽ  06 2015 16:39:22 +09:00


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Advanced Analytics and Real Application Testing options
‚ɐڑ±‚³‚ê‚Ü‚µ‚½B


8. Check if whether commenting-out SSL_CLIENT_AUTHENTICATION and SSL_VERSION is okay

[oracle@consdb121n1 tmp]$ cat /tmp/sqlnet.ora
SQLNET.WALLET_OVERRIDE = TRUE
#SSL_CLIENT_AUTHENTICATION = FALSE
#SSL_VERSION = 0

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = /tmp)
  )
 )

SQL*Plus: Release 12.1.0.2.0 Production on –Ø 8ŒŽ 6 16:42:29 2015

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

ÅI³íƒƒOƒCƒ“ŽžŠÔ: –Ø 8ŒŽ  06 2015 16:40:38 +09:00


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
With the Partitioning, Real Application Clusters, Automatic Storage
Management, OLAP,
Advanced Analytics and Real Application Testing options
‚ɐڑ±‚³‚ê‚Ü‚µ‚½B

Posted by at No comments:

Cascaded Standby Databases in Oracle 12c

Cascaded Standby Databases in Oracle 12c
There are new Possibilities for cascading Standby Databases in Oracle 12c. The main Differents between Oracle 12c and the previous Releases are:

  1. Real-Time Cascading
  2. Far Sync Standby Database
  3. Data Guard Broker now supports cascaded Standby Database
we can only cascade a Standby Database from a Physical Standby Database.

Real-Time Cascading


It is now possible to forward Redo in Real-Time Mode from the first to the cascaded Standby Database. So the Redo Record is forwarded to the cascaded Standby Database once written into a Standby RedoLog of the first Standby Database.
Non Real-Time Cascading means that the whole Log Sequence is transferred to the terminal Standby Database(s) after a Log Switch on the Primary Database.

First of all setup a Data Guard Environment as usual to the cascading Standby Database. The Log Transport Method should be ‘SYNC’ and Standby RedoLogs must be configured on the cascading Standby Database. Once you created the cascaded Standby Database you can now setup the cascading Log Transport Services. Here are some Hints for correct Setup:

  • Primary, Cascading and Cascaded Standby Database db_unique_name must be present in the dg_config of log_archive_config on all the Databases
  • Setup log_archive_dest_n on the cascading Standby Database to serve the cascaded (terminal) Standby Databases using the Attribute ‘valid_for=(STANDBY_LOGFILES,STANDBY_ROLE)’
  • You can toggle between Real-Time and Non Real-Time Cascading using the Log Transport Method.
ASYNC = Real-Time Cascading
SYNC = Non Real-Time Cascading
  • You can only use log_archive_dest_1 until log_archive_dest_10 for Non Real-Time Cascading Destinations where all log_archive_dest_n’s can be used for Real-Time Cascading on the Cascading Standby Database
  • The Cascading Standby Standby can be in any Protection Mode
  • A Cascading Standby Database can serve one or multiple terminal Standby Databases
  • FAL_SERVER on the cascading Standby Database should be set to the Primary or any other Standby Database served by the Primary Database directly
  • FAL_SERVER on the terminal Standby Database should be set to the cascading Standby Database or the Primary Database
Posted by at No comments:

Wednesday, December 16, 2015

ORA-3136: WARNING Inbound Connection Timed Out

The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete  the  authentication process within the period of time specified by the parameter SQLNET.INBOUND_CONNECT_TIMEOUT. 

You might also see the errors ORA-12170 or TNS-12535 in the sqlnet.log that is generated on the server.
Check $ORACLE_HOME/network/log for this file.  This entry should contain client address from which the timeout originated and may be helpful in determining how to troubleshoot the issue.  Some applications or JDBC thin driver applications may not have these details.  The sqlnet.log file is not generated  by default in 11g and newer.  

From 10.2.0.1 onwards the default setting for the parameter SQLNET.INBOUND_CONNECT_TIMEOUT is 60 seconds.  If the client is not able to authenticate within 60 seconds, the warning would appear in the alert log and the client connection will be terminated.
The following are the most likely reasons for this error -
  1. Server gets a connection request from a malicious client which is not supposed to connect to the database.  In this case the error thrown would be the expected and desirable behavior. You can get the client address for which the error was thrown in the sqlnet.log file that is local to the database.
  2. The server receives a valid client connection request but the client takes a long time to authenticate more than the default 60 seconds.
  3. The DB server is heavily loaded due to which it cannot finish the client logon within the timeout specified.
To understand what is causing this issue, following checks can be done 
The default value of 60 seconds is good enough in most conditions for the database server to authenticate a client connection. If it is taking longer, then it's worth checking the following items before implementing the workaround:

1. Check whether local connection on the database server is successful & quick.
2. If local connections are quick ,then check for underlying network delay with the help of your network administrator.
3. Check whether your Database performance has degraded in anyway.
4. Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them  resolved first.
These critical errors might have triggered the slowness of the database server.

It is often necessary to increase the values for INBOUND CONNECT TIMEOUT at  both the listener and the database in order to resolve this issue.    It is usually advisable to set the database (sqlnet.ora) value slightly higher than the listener (listener.ora).    The authentication process is more demanding for the database than the listener.


To set these parameters to use values higher than the default of 60 seconds, follow these instructions and restart the listener.  There is no need to restart Oracle:

Edit the server side sqlnet.ora file and add this parameter:

SQLNET.INBOUND_CONNECT_TIMEOUT=<n>  Where <n> is the value in seconds.

E.g.:
SQLNET.INBOUND_CONNECT_TIMEOUT = 120

Edit the listener.ora file and add this parameter: 
INBOUND_CONNECT_TIMEOUT_<listenername> = <n>  Again, where <n> is the timeout value in seconds. 

For example if the listener name is LISTENER then use:
INBOUND_CONNECT_TIMEOUT_LISTENER = 110

From Oracle version 10.2.0.1 onwards the default value of INBOUND_CONNECT_TIMEOUT_<listenername> is 60 seconds. For previous releases it is zero or OFF by default.

How to check whether inbound timeout is active for the listener and database server:

For example,  INBOUND_CONNECT_TIMEOUT_<listener_name> =110

You can check whether the parameter is active or not by simply doing telnet to the listener port.
$ telnet <database server IP> <listener port>
for eg.
$ telnet 123.23.23.23 1521

The telnet session should disconnect after 110 seconds which indicates that the inbound connection timeout for the listener is active.

Alternatively, check at the LSNRCTL prompt using:

LSNRCTL>set current_listener <listener_name>
LSNRCTL>show inbound_connect_timeout

To check whether database server SQLNET.INBOUND_CONNECT_TIMEOUT is active:
Eg.
SQLNET.INBOUND_CONNECT_TIMEOUT=120


a. For Dedicated server setup, enable the support level sqlnet server tracing will show the timeout value as below:
niotns: Enabling CTO, value=120000 (milliseconds) <== 120 seconds
niotns: Not enabling dead connection detection.
niotns: listener bequeathed shadow coming to life...


b. For shared Server setup,
$ telnet <database server IP> <dispatcher port>
Example.
$ telnet 123.23.23.23  51658

The telnet session should disconnect after 120 seconds which indicates that the sqlnet.inbound_connect_timeout is active.
Posted by at No comments:

Sunday, July 26, 2015

Oracle E-Business Suite upgrade to 12.1.3



Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64 (Doc ID 761566.1)
Oracle E-Business Suite Release 12.1.3 Readme (Doc ID 1080973.1)

 export ORACLE_SID=PROD
  export ORACLE_HOME=/scratch/EBS12.1.1/db/tech_st/11.1.0
  export PATH=$PATH:$ORACLE_HOME/bin
   . /scratch/EBS12.1.1/apps/apps_st/appl/APPSPROD_ofss2311720.env


To apply Oracle E-Business Suite Release 12.1.3, follow these steps:
1.    Use AutoPatch to apply R12.AD.B.DELTA.3 Patch 9239089, before you complete any of the other steps in this section. Do not merge this patch with any other patch. R12.AD.B.Delta.3 must be applied separately. Refer to My Oracle Support Knowledge Document 1077769.1, Oracle E-Business Suite Applications DBA Readme, Release 12.1.3 for more information about R12.AD.B.Delta.3.

2.    Use AutoPatch to apply Oracle E-Business Suite Release 12.1.3 Patch 9239090 and follow the instructions in the patch readme file.

3.    Use AutoPatch to apply the latest consolidated online help Patch 9239095 and follow the instructions in the patch readme file.

2. Installation packs

* Patch 9239089(R12.AD.B.DELTA.3)
* Patch 9239090 12.1.3 RELEASE UPDATE PACK
* Patch 9239095 Oracle E-Business Suite Online Help for 12.1.3 Release Update Pack
* Patch 9817770 POST-R12.ATG_PF.B.DELTA.3 CONSOLIDATED PATCH
* Patch 9966055 1OFF:12.1.3:TRANSLATED VERSION OF FNDSCSGN NOT LAUNCHED





Step1. Run utility adadmin utility and change maintenance mode to enable
Steps 2: Apply patch 9239089  (R12.AD.B.DELTA.3)
Patch R12.AD.B.delta.3: Oracle Applications DBA 12.1.3 Product Release Update Pack
Oracle E-Business Suite Applications DBA Readme, Release 12.1.3 (R12.AD.B.DELTA.3, Patch 9239089) (Doc ID 1077769.1)

https://updates.oracle.com/Orion/Services/download?type=readme&aru=12062459

Pre-install Tasks
You must shut down all Application tier services before performing the tasks in this section.
Instructions for running AD Grants
Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profiler objects.

  Usage:

  1. Create $ORACLE_HOME/appsutil/admin on the database server.

  2. Copy adgrants.sql (UNIX) from this patch directory to
     $ORACLE_HOME/appsutil/admin.
     Or, copy adgrants_nt.sql (Windows) from this patch directory to
     %ORACLE_HOME%\appsutil\admin.

  3. Set the environment to point to ORACLE_HOME on the database server.

  4. Use SQL*Plus to run the script:

     UNIX:
     $ sqlplus /nolog
     SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPS schema name>
Apply The Patch

-- Run ADAMIN utility and enable maintenance mode

 Apply patch 9239089
This patch contains the following unified driver file to be applied with
AutoPatch:
    u9239089.drv (This is the unified driver)
1.  change to patch directory
2.  run utility adpatch and give above drv filename when ask.


Apply patch 9239090
Patch 12.1.3: ORACLE E-BUSINESS SUITE 12.1.3 RELEASE UPDATE PACK


https://updates.oracle.com/Orion/Services/download?type=readme&aru=12054750
Apply patch
This patch contains the following unified driver file to be applied with
AutoPatch:
    u9239090.drv (This is the unified driver)


Apply patch 9239095
Patch 9239095: Oracle E-Business Suite Online Help for 12.1.3 Release Update Pack



https://updates.oracle.com/Orion/Services/download?type=readme&aru=12062250
Apply patch
This patch contains the following unified driver file to be applied with
AutoPatch:
    u9239095.drv (This is the unified driver)



Steps 5: Apply   Patch 9817770



Steps 6: Apply Patch 9966055


Steps 7: Apply patch 8919491

Step 7: Change maintenance mode

Step8: start application processes

  Post-Update Steps

1.    Apply post-install Oracle E-Business Suite Applications Technology patches. (Required)
o    Apply mandatory Patch 9817770:R12.ATG_PF.B (POST-R12.ATG_PF.B.DELTA.3 CONSOLIDATED PATCH).
o    Apply mandatory Patch 9966055:R12.FND.B (TRANSLATED VERSION OF FNDSCSGN NOT LAUNCHED).

 

Patch 9817770: POST-R12.ATG_PF.B.DELTA.3 CONSOLIDATED PATCH

The following should be installed in the order shown before installing this patch.
1
R12.AD.B.delta.3
Oracle Applications DBA 12.1.3 Product Release Update Pack
(Install on Oracle Applications DBA)
2
R12.ATG_PF.B.delta.3
Oracle Applications Technology 12.1.3 Product Family Release Update Pack


Patch 9966055: 1OFF:12.1.3:TRANSLATED VERSION OF FNDSCSGN NOT LAUNCHED

Prerequisite Patches
The following should be installed in the order shown before installing this patch.
1
R12.AD.B.delta.3
Oracle Applications DBA 12.1.3 Product Release Update Pack
(Install on Oracle Applications DBA)
2
R12.ATG_PF.B.delta.3
Oracle Applications Technology 12.1.3 Product Family Release Update Pack
(Install on Oracle Applications Technology Family)


Patch R12.ATG_PF.B.delta.3: Oracle Applications Technology 12.1.3 Product Family Release Update Pack
 Prerequisite Patches
R12.AD.B.delta.3
Oracle Applications DBA 12.1.3 Product Release Update Pack
(Install on Oracle Applications DBA)




-- Run ADAMIN utility and disable maintenance mode
--Start the EBS processes
--Login and check the page


SQL> select release_name from apps.fnd_product_groups;

RELEASE_NAME
--------------------------------------------------
12.1.3

SQL>


SQL> select bug_number from ad_bugs where bug_number in ('9239089','9239090','9817770','9966055');

BUG_NUMBER
------------------------------
9239089
9239090
9817770
9966055

SQL>




   
   
   col PATCH_NAME format a10
col PATCH_TYPE format a10
col DRIVER_FILE_NAME format a15
col PLATFORM format a10
select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME, AD.CREATION_DATE,  AD.PLATFORM,AL.LANGUAGE
from AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL
where AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID
and AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID


PATCH_NAME PATCH_TYPE DRIVER_FILE_NAM CREATION_DATE      PLATFORM   LANG
---------- ---------- --------------- ------------------ ---------- ----

9239089    PATCH-SET  u9239089.drv    12-JUN-15          LINUX      US
9239090    MAINTENANC u9239090.drv    12-JUN-15          LINUX      US
           E-PACK

9817770    ONE-OFF    u9817770.drv    12-JUN-15          LINUX      US
9966055    ONE-OFF    u9966055.drv    12-JUN-15          GENERIC    US
8919491    PATCH-SET  u8919491.drv    12-JUN-15          LINUX      US
9239095    ONE-OFF    u9239095.drv    12-JUN-15          GENERIC    US

-----Troubleshooting
  

  Application Tier Startup/Shutdown Scripts
Depending on your AD Version these will be in

OAD_TOP/admin/scripts/ SID_hostname

adalnctl.sh Apps Listener Control Script
adapcctl.sh Apache/Web Server Control Script
adcmctl.sh Concurrent Manager Control Script
addisctl.sh Discoverer Control Script
adfrmctl.sh Forms server Control Script
adrepctl.sh Report Server Control Script
adstpall.sh Stop All Middle/Application Tier
adstrtall.sh Start All Middle/Application Tier





Login page
http://ofss2311690.in.oracle.com:8005/OA_HTML/
http://ofss2311720.in.oracle.com:8001/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE

Username/Password: SYSADMIN/sysadmin






[debaranw@ssssss scripts]$  ./adapcctl.sh status

You are running adapcctl.sh version 120.7.12010000.2

Checking status of OPMN managed Oracle HTTP Server (OHS) instance ...

Processes in Instance: VIS_localhost.localhost.in.oracle.com
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:oafm          |    9813 | Alive
OC4JGroup:default_group          | OC4J:forms         |    9740 | Alive
OC4JGroup:default_group          | OC4J:oacore        |    9654 | Alive
HTTP_Server                      | HTTP_Server        |    9595 | Alive


adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /scratch/EBS12.1.1/inst/apps/VIS_localhost/logs/appl/admin/log/adapcctl.txt for more information ...

[debaranw@xxxxxx scripts]$





Posted by at No comments:

Tuesday, March 10, 2015

Resize datafiles to minimum



column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*8)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/
Posted by at No comments:
Subscribe to: Posts (Atom)