Remote Oracle Database Support : November 2017

concurrent manager workflow in oracle apps

Concurrent Manager has executes job defined by program executable as per schedule specified during concurrent request submission.

Concurrent manager has below managers helps to processes the concurrent request based on their defined functions

ICM - Internal concurrent manager
SM - Standard manager
CRM - Conflict resolution manager

Functioning:

1> ICM - Internal Concurrent manager

- TO start/stop/reset other concurrent managers
- Should be running prior to other managers

oracle network error solution

Listing down oracle common network TNS error and solution faced mostly during database administration

You may click on individual ORA- error string to find more details in for of error details , cause and possible solution to fix in your environment.

#1 ORA-12545: Connect failed because target host or object does not exist

#2 ORA-12547: TNS: lost contact

Life of DBA

Database administration considered as a formidable role in IT industry , it takes huge efforts to get noticed after background work performed by every individual DBA through qualification , certification and innovation.

Day to day production issue handling across the clock 24/7 , keeping database up and running , abandon work-life balance , performing optimal to application queries without a single human error or common mistakes is backbreaking.

Database health check
Backup / Recovery
Performance Tuning
Patching / Upgrades
User queries
Security
Root cause analysis
Client process

Launch of autonomous database cloud 18c

Self- Driving
Self- Scale
Self- Repair

what is oracle 18c autonomous database

Oracle unveils worlds first autonomous database cloud on Oct 2, 2017 by oracle chairman of board and CTO Larry Ellison.

Features:

Ground Breaking Machine learning technique to automate and eliminate human labor , reducing human errors, no more manual tuning leading improved availability with high performance and security that is also at very lower cost which reflect as next generation industry-leading databases.

Oracle autonomous database cloud to attain availability of 99.95% with only 30 min of planned/Unplanned downtime.

ASM DiskGroup Migration

Step Wise Disk-Group Migration from DG01 to DG02

Scenario:

DBTST is an Oracle11g single instance database on ASM which holds complete database in Disk Group named DG01 spread across 4*50 GB of Disks. Objective is to migrate complete databases from DG01 to DG02 Disk Group. Here DG02 is a new Disk Group on 500 GB disk (/dev/mapper/mpathe)

col NAME for a20
col PATH for a40
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH from v$asm_disk;

GROUP_NUMBER DISK_NUMBER MOUNT_S STATE OS_MB   TOTAL_MB FREE_MB NAME       PATH
------- -------- ------- -------- ---------- ---------- -------- --------------- ----------------------------------------
           1           2 CACHED NORMAL     51200    51200         43 DG01_0002    /dev/mapper/mpathc
           1           1 CACHED NORMAL                 51200    51200         41 DG01_0001    /dev/mapper/mpathb
           1           3 CACHED NORMAL                 51200    51200         45 DG01_0003    /dev/mapper/mpathd
           1           0 CACHED NORMAL                 51200    51200         46 DG01_0000    /dev/mapper/mpatha
           0           0 CLOSED NORMAL                 512000          0          0                      /dev/mapper/mpathe

Migration Steps:

preparing for oracle certification

Scope of this document is to highlight the benefits of certification and its preparation , it does not target to specific exam certification code or syllabus but to provide hints to prepare them all.it can be referred by all technology aspiring certified professionals and not just oracle

Oracle certification is an educational program by oracle university to increase the oracle user base in the form of profiles like dba , developers , architects , business analysts which has following benefits

1.Self pride
"I am an oracle certified professional/expert/master" these few words itself has so much of weight & efforts behind it. Certification adds on the self pride for sure if done in proper way else it is just an another piece of paper (will explain it why , soon..)

Oracle Performance Tuning II

Oracle Diagnostic Pack:

The Oracle Diagnostic Pack provides automatic performance diagnostic and advanced system monitoring functionality. The Diagnostic Pack includes the following features:

1.Automatic Workload Repository

The AWR collects and stores database statistics relating to problem detection and tuning. AWR is a replacement for the stats pack utility which helps gather database performance statistics. AWR generates snapshots of key performance data, such as system and session statistics, segment-usage statistics, time-model statistics, high-load statistics and stores it in the sysaux tablespace. The MMON process is responsible for collecting the statistics in the SGA and saving them to the sysaux tablespace.

AWR will collect data on the following

Base statistics that are also part of the V$SYSSTAT and V$SESSTAT views
SQL statistics
Database object-usage statistics
Time-model statistics

Enabling Oracle Performance Pack

Enabling Performance Tuning & Diagnostic pack:

Method 1: Granting Management Pack Access through OEM

Home Page>setup>Management Pack Access <Grant Access> <Apply>

Method 2:

ü Step 1

ORA-01078 ORA-29701 GRID Home Cloning

This blog explains cloning steps of Grid Home/ASM Home applicable for 10g & 11g (on ASM) particularly , applicable for scenarios like..

Host Name Changes
Binary Corruption without any valid backup to restore
Cloning of DB across the servers
Attaching new GRID Home to existing DB Home

It has been observed that non-ASM DB cloning is simpler task without much activity related to DB Home post cloning.

Just need to keep same uid/gid of os osuser as source db software, copy as it is, relink it on destination & few changes in listener.ora, password file, tnsnames.ora and new ORACLE_HOME is ready for use

Considering Single instance approach here as RAC to Non RAC copy has different approach & need to play with few lib files

If you try to copy GRID Home of some other server & try to bring up the ASM instance you will come across below errors for sure although you set proper profile or being up the LISTENER etc.

ORA-01078: failure in processing system parameters

ORA-29701: unable to connect to Cluster Synchronization Service

ORA-1652: unable to extend temp segment

There could be only two ways to resolve this error ..

I. Add More Space in Temporary Table-space temp-file
II. Identify the Problematic Query & Tune it !

Today i came across this error , a developer reach out to me saying a package is executing for almost 30 minutes & terminating with "ORA-1652: unable to extend temp segment by 128 in tablespace TEMP" error.

ORA-02049: timeout: distributed transaction waiting for lock

Issue: ORA-02049: timeout: distributed transaction waiting for lock

Background:

Application has reported the error ORA-02049 during transaction workflow involving DB link to Oracle9i two nodes RAC. Just before this error starts appearing the new code was deployed on production database at source which is 11g two node RAC.

ASMCMD-8016 unable to copy controlfile in ASM

During DB cloning or duplication , we may encounter below error while backing up control file or any other ASM db file within ASM disk group or on local file system

Error:

ASMCMD> cd +RECO/testdb/controlfile/
ASMCMD> cp control02.dbf /u01/oneoffpatches
copying +RECO/testdb/controlfile/control02.dbf -> /u01/oneoffpatches/control02.dbf
ASMCMD-8016: copy source '+RECO/testdb/controlfile/control02.dbf' and target '/u01/oracle/control02.dbf' failed
ORA-06550: line 3, column 9:

Remote Oracle Database Support

Tabs