Monday, November 9, 2020

Command to do active duplicate for Oracle Database

1. First login to target server
2. Validate tns connectivity between Source DB and Target DB
3. Prepare and validate space availability
4. Startup mount the target and source database ( If not done already)
5. Use the below command to do active duplicate

rman auxiliary sys/PASS@target_DB target sys/PASS@source_DB log=/tmp/active_duplicate.log <<EOF
run {
allocate channel ch1 type disk;
allocate auxiliary channel stby1 type disk;
DUPLICATE TARGET DATABASE TO 'newdb' FROM ACTIVE DATABASE nofilenamecheck noredo;
}
EOF

How to change ASM rebalance Power in Oracle Database

 How to change ASM rebalance Power in Oracle Database?

1. Login to ASM 

2. Use the below query to find ASM Rebalance power for all current operations.

select * from v$asm_operation;

3. Use the below query to change ASM rebalance power.

alter diskgroup DATA rebalance power 8; 

4. Use the below query to find changed ASM Rebalance power for all current operations.

select * from v$asm_operation;

Thursday, July 26, 2018

Unable to mount NFS or access in linux

Unable to mount NFS or access in linux


Problem: Mountpoint /nfsdemo/nfs2 is mounted but not able to access its directories.

df -Ph
Filesystem                                   Size  Used Avail Use% Mounted on
demooracle:/root_vrdm_1/nfsdemo               18G  4.3G   13G  26% /nfsdemo/nfs2

cd /nfsdemo/nfs2/testdir1/tdir2 -- Failed

Troubleshooting:
1. /proc/mounts does not have the mountpoint entry.
cat /proc/mounts -- It does not have the entry
2. /etc/fstab has the entry
3. Try to umount
umount /nfsdemo/nfs2 -- fails with timeout
4. Try to do force mount
mount -f /nfsdemo/nfs2  -- Completes but does not resolve the issue
5. Reboot the OS to clean the memory and it resolves the issue

Friday, September 15, 2017

Single resource deadlock: blocked by granted enqueue, f 0

We are noticing "Single resource deadlock" in alert log of the database.

Error Message
*** 2017-09-14 15:51:27.949
*** SESSION ID:(2218.11151) 2017-09-14 15:51:27.949
*** CLIENT ID:(TESTUSER) 2017-09-14 15:51:27.949
*** SERVICE NAME:(VISEBS) 2017-09-14 15:51:27.949
*** MODULE NAME:(e:PO:bes:oracle.apps.fnd.wf.ds.user.updated) 2017-09-14 15:51:27.949
*** CLIENT DRIVER:() 2017-09-14 15:51:27.949
*** ACTION NAME:(PO/PO_BUYER_CCRP) 2017-09-14 15:51:27.949

Single resource deadlock: blocked by granted enqueue, f 0
Granted global enqueue 0xf215c1260

Reasons:
It can be due to the PRAGMA AUTONOMOUS_TRANSACTION given inside the package or procedure it is calling.
Due to autonomous transaction any insert , update or delete is there on same row in parent and child can create deadlock.

Wednesday, August 30, 2017

After modifying Workflow Package getting below error ORA-00054: resource busy and acquire with NOWAIT

After modifying Workflow Package getting below error ORA-00054: resource busy and acquire with NOWAIT


After modifying any workflow package, while approving or acting on the notification getting below error.

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 
ORA-06512: at "APPS.WF_NOTIFICATION", line 5137 ORA-06512: at line 1

It is due to the old state of the existing code creating this error.

Resolution:

1. Clear the old session locking up the old objects.

a. Search the session
select s.*
from gv$access a, gv$session s
where object IN ('AP_WORKFLOW_PKG')
and a.inst_id=s.inst_id
and a.sid=s.sid
order by logon_time;
b. Kill the session ( Restart workflow and OPMN, will clear most of them )
2. Bounce the database

Check which file rsync is accessing

Check current file rsync is accessing


It is quiet troublesome to find which folder or file rsync is currently scanning. Sometimes it is required check it when it is running for a long time and you are not able to find the directory where it is consuming more time.

There are several approach to find this.
1. lsof command to find the current file it is accessing.
2. Doing the rsync in vervose mode and printing it to a file

I prefer the first one as it is easier to do.

$ lsof -ad3-999 -c rsync
COMMAND   PID    USER   FD   TYPE             DEVICE SIZE/OFF      NODE NAME
rsync   15070 root    3r   DIR               0,20 20235264   2829984 /app/testing/documents (loaclhost:/root_vdm_1/app)
rsync   15070 root    4u  unix 0xffff880c260033c0      0t0 827481535 socket
rsync   15070 root    5u  unix 0xffff88074fb9f080      0t0 827481536 socket
rsync   15071 root    3u  unix 0xffff880b011e9480      0t0 827481546 socket
rsync   15072 root    4u  unix 0xffff880b011e9100      0t0 827481547 socket

If you want to watch it periodically(every 5 sec), then use the watch command to do it.

$ watch -n 5 "lsof -ad3-999 -c rsync"

Error at line 536: ORA-04021: timeout occurred while waiting to lock object while executing datapatch for PSU

Error at line 536: ORA-04021: timeout occurred while waiting to lock object while executing datapatch for PSU


Getting below error while executing below error for database PSU ( datapatch )

 Error at line 536: ORA-04021: timeout occurred while waiting to lock object

================================
./datapatch -verbose
SQL Patching tool version 12.1.0.2.0 Production on Tue Aug 29 14:55:58 2017
Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: ......

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 20204035 ():
  Installed in the binary registry and the SQL registry
Patch 22139226 (Database PSU 12.1.0.2.160119, Oracle JavaVM Component (Jan2016)):
  Not installed in the binary or the SQL registry
Patch 24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)):
  Installed in the binary registry and the SQL registry
Bundle series PSU:
  ID 170718 in the binary registry and ID 161018 in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    25755742 (DATABASE PATCH SET UPDATE 12.1.0.2.170718)

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 25755742 apply: WITH ERRORS
  logfile: ....._....log (errors)
    Error at line 536: ORA-04021: timeout occurred while waiting to lock object
=============================================

Log file details 

===================================
Session altered.


Library created.

CREATE OR REPLACE PACKAGE BODY dbms_audit_mgmt wrapped
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock objectPackage body created.


Grant succeeded.
==================================================

Resolution:
 Some session might be locking this object. Below are the solutions for this problem.
1. Search the session blocking the object and kill it if possible.
2. Shutdown the database and do a startup restrict

Command to do active duplicate for Oracle Database

1. First login to target server 2. Validate tns connectivity between Source DB and Target DB 3. Prepare and validate space availability 4. S...