Year: 2020

Script to check free space and used space in Oracle Database tablespaces

select a.tablespace_name,
       a.bytes_alloc/(1024*1024) “TOTAL ALLOC (MB)”,
 a.physical_bytes/(1024*1024) “TOTAL PHYS ALLOC (MB)”,
       nvl(b.tot_used,0)/(1024*1024) “USED (MB)”,
       (nvl(b.tot_used,0)/a.bytes_alloc)*100 “% USED”
from ( select tablespace_name,
       sum(bytes) physical_bytes,
sum(decode(autoextensible,’NO’,bytes,’YES’,maxbytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name ) a,
     ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
–and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and   a.tablespace_name not like ‘UNDO%’
order by 1
 –order by 5
/ 

RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel



This is an error during Database Clone using RMAN. I was trying to restore database using RMAN Backup 


RMAN-00571: ===========================================================


RMAN-00569: ===============
ERROR MESSAGE STACK FOLLOWS ===============


RMAN-00571: ===========================================================


RMAN-03002: failure of Duplicate Db command
RMAN-05501: aborting duplication of target database


RMAN-03009: failure of allocate command on ORA_AUX_SBT_TAPE_1 channel 
ORA-19554: error allocating device, device type: SBT_TAPE, device name:


ORA-27211: Failed to load Media Management Library


Additional information: 


Cause: Backup Configuration was modified.


Solution:
Use the below command to Resolve the error and continue with Database Restore


rman auxiliary / 
run
{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate database to ‘DEV’ backup location ‘/Daily_Backup/Backup_16042018’;
}
exit;
EOF

adcfgclone.pl script failed during instantiate OHS Stage

While cloning 12.2.6 environment, adcfgclone.pl script failed during instantiate OHS Stage.


Issue is because opmn components were unable to start up with below error messages.


globalInitNLS: NLS boot file not found or invalid
 — default linked-in boot block used
XML parser init: error 201.
globalInitNLS: NLS boot file not found or invalid
 — default linked-in boot block used


Further analysis on the clone logfiles for OHS creation shown that ORA_NLS10 parameter is unset before cloning.


Resolution:


$unset ORA_NLS10


+Restart the failed script

Concurrent Manager Troubleshooting : Multiple Concurrent Requests Struck without Processing


Scenario: Concurrent Requests are in Running Normal state for longer time than normal duration of the Program.

Analysis: 

Step 1: Navigate to System Administrator Responsibility : Concurrent Manager –> Administer

Identify the Concurrent Requests which are in Running Status.

Get the Database Session Details of the currently running concurrent requests using the below Query:

SELECT DISTINCT  a.request_id,C.INST_ID, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = ‘R’ and a.status_coDe=’R’;

Step 2: Verify if the database sessions are active/Inactive at the database level using the below query.

select inst_id,sid,serial#,program,module,status,last_call_et,sql_id from gv$session where sid=&sid;

— sid value to be taken from output of Sql Query in Step 1

If Database session is INACTIVE And Running no sql for more than an Hour, we can Terminate the Concurrent Requests.
If the Database session is ACTIVE and has an SQL_ID attached with it, Need to check on tuning the sql being run by the database session.

I will cover more details about SQL Tuning in another post.

How to add space in ACFS filesystem?

The below post explains the step-by-step process to add storage to a mountpoint in ACFS filesystem


1.Before extending the mountpoint storage we need to add an LVM of required Storage Capacity on the server.


For checking the LUN’s available on the server, click here


For now, assume the LUN added is /dev/sdf1


2. As root User, check the available disks by running the below command


$ /usr/sbin/oracleasm listdisks
DATA01
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03


3. Create a new disk using the below command


/usr/sbin/oracleasm createdisk DATA02 /dev/sdf1 


Where DATA02 is the name of new disk added
/dev/sdf1 is the LUN added for storage extension.


4. Verify the disk is added by running the below command.


 $/etc/init.d/oracleasm  listdisks
DATA01
DATA02
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03


5. Navigate to the other cluster nodes and run the scandisks command to reflect the newly added disk on all the nodes of cluster.


$/etc/init.d/oracleasm  scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]


 $/etc/init.d/oracleasm  listdisks
DATA01
DATA02
OCR_VOTE01
OCR_VOTE02
OCR_VOTE03


6. Now login to the database server as GRID user (Where ASM instance is running) to add space to ACFS.


Set the environment as required




export ORACLE_HOME=/u01/app/12.1.0/grid
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=+ASM1


$sqlplus / as sysasm


SQL> alter diskgroup DATA add disk ‘/dev/oracleasm/disks/DATA02’;


Diskgroup altered.




7. Increase the space on filesystem


As root user,


$acfsutil size +1G /oradata
acfsutil size: new file system size: 591833071616 (564416MB)

How to Resolve FRM-92101 error while launching forms in Oracle EBS 12.2.x versions?

In EBS 12.2.6 Environment,
Launching Forms fails with Error FRM-92101 intermittently.
FRM-92101: There was a
failure in the Forms Server during startup. This could happen due to invalid
configuration. Please look into the web-server log file for details
 
Solution
1.      
Quick workaround for this issue is to clear the
browser cache, restart the browser and launch the forms session again.
OR
2.      
Check all the required lib files are installed
or not and try relinking the forms executables to resolve the issue.
       OR
3.      
This is a bug with Internet Explorer and
R12.2.X. Contact Oracle support to provide a fix.

How to Enable Database Vault in Oracle database Version 11gR1?



Steps to enable Database Vault in Oracle Database 11gR1:


1. In the command prompt, use DVCA utility to enable Oracle Database Vault.


e.g.


dvca -action enable 
  -oh /u01/app/oracle/product/11.1.0/db_1
  -service conn_alias 
  -instance orcl 
  -dbname orcl 
  -owner_account lbrown_dvowner 
  -logfile dvcalog.txt 


Enter SYS password: sys_password
Enter owner password: owner_password


Parameter Description:
======================


-service is the name of the database specifier. The specifier can be a connect descriptor or net service name. 


-instance is the name of the database instance.


-dbname is the database name.


-sys_passwd is the SYS password. If you use a cleartext password on the command line, you must include the nodecrypt option. If you omit the password, DVCA prompts you for it. Preferably, omit the password and then enter it interactively when prompted.


-owner_account is the Oracle Database Vault Owner account name.


-owner_passwd is the Oracle Database Vault Owner account password. If you use a cleartext password on the command line, you must include the nodecrypt option. If you omit the password, DVCA prompts you for it. Preferably, omit the password and then enter it interactively when prompted.


-logfile is an optional flag to specify a log file name and location. You can enter an absolute path, or enter a path that is relative to the location of the $ORACLE_HOME/bin directory.


-silent is the option to run in command line mode. This option is required if you are not running DVCA in an xterm window.


-nodecrypt is the option to read plaintext passwords.


-lockout is the flag to use to disable SYSDBA operating system authentication.


2. Stop the database, Database Control console process, and listener.


Enable the Oracle Database Vault option as follows:
 The make command enables both Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on).
 You must enable Oracle Label Security before you can use Database Vault.


cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_on lbac_on


cd $ORACLE_HOME/bin
relink all


3.Restart the database, Database Control console process, and listener.


4. Verify Oracle Database Vault and Oracle Label Security are enabled.


SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Label Security’;


SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;

EBS 12.2.6 login Page is taking long time to load

EBS Login Performance Issues:


After providing username and password in the login page of EBS 12.2.6, loading home page is taking very long time to load.


weblogic console url shows that one of the oacore servers, oacore_server2 is in WARNING state due to Max number of Stuck Threads reached.


Error Message from oacore log
=============================


<Error> <WebLogicServer> <BEA-000337> <[STUCK] ExecuteThread: ’16’ for queue: ‘weblogic.kernel.Default (self-tuning)’ has been busy for “605” seconds working on the request “Workmanager: default, Version: 0, Scheduled=true, Started=true, Started time: 605929 ms
[
GET /OA_HTML/OA.jsp?page=/oracle/apps/per/perimage/webui/PerImgViewPG&transactionid=82152123&language_code=US&pCalledFrom=PERWSIMG&OAMC=N&oas=Wnrjc-sK-Ns6nz5o7H7HIg..&pPersonId=16520&payLegislationCode=AE&CallFromForm=’Y’ HTTP/1.1
Accept: */*
Accept-Language: en-US
User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 10.0; WOW64; Trident/7.0; .NET4.0C; .NET4.0E)
Accept-Encoding: gzip, deflate
Cookie: oracle.uix=0^^GMT+4:00^p; treemenu1=0; JSESSIONID=tg0hURm_tL89fEex9hDrVrvC1rLBjpTn3fc2pZ89o7Hpb7jR7gVi!222603901; JsessionIDForms=Lh0hURqNRRpfmiTouQ_0kF2MiYYn5x8j8cW5AhjSBMEyOEfvp1oa!-851768; DPRPROD=l6hjdVQuX3p1xvWrRtPjEW8SAj
ECID-Context: 1.005NsDH9rp8FCC0_zxo2yW0002km000083;kXjE
Connection: Keep-Alive
X-Forwarded-For: 10.11.32.120
Proxy-Client-IP: 10.11.32.120
X-WebLogic-KeepAliveSecs: 30
X-WebLogic-Force-JVMID: 222603901


]”, which is more than the configured time (StuckThreadMaxTime) of “600” seconds. Stack trace:
        java.net.SocketInputStream.socketRead0(Native Method)
        java.net.SocketInputStream.read(SocketInputStream.java:152)
        java.net.SocketInputStream.read(SocketInputStream.java:122)
        oracle.net.nt.MetricsEnabledInputStream.read(TcpNTAdapter.java:730)






Solution1
=====


Temporary workaround is to Restart the oacore servers to release the stuck threads


Solution2
========


1. Increase the value of paramter “StuckThreadMaxTime” TO 3600 Seconds from the weblogic Console.


Log in to WebLogic console 

Click on the Admin server >> Click on the Configuration tab >> Tuning tab and set the below values