Remote Oracle Database Support : 2017

RMAN-00600: internal error, arguments [7038] [rec_cl_delarc]

Error:

{

recover

clone database

noredo

delete archivelog

;

}

executing Memory Script

Starting recover at 03-DEC-2017 21:20:54

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

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

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

RMAN-03002: failure of Duplicate Db command at 12/03/2017 21:21:05

RMAN-05501: aborting duplication of target database

RMAN-00600: internal error, arguments [7038] [rec_cl_delarc][] [] []

Solution:

Let's start with the dictionary meaning of a blog ,

noun

a webpage regularly updated and maintained by an individual or small group, written in an conversational style to make more impressive.

interview questions on concurrent manager oracle apps with solution

Q1. Why concurrent request is in pending standby mode for long time ?

Answer:

Cause

Phase code Pending and Status code Standby means concurrent request has been processed by ICM (Internal Concurrent Manager) , however it is held by CRM(conflict resolution manager) due to incompatibilities with other programs that are currently running. One more possibility of pending standby is CRM itself is not available / not running.

Solution

i. Through SYSADMIN login go to System Administrator responsibility -> concurrent manager -> CRM And check if it is running state if not then activate the same

ii. In above scenario if CRM is running and still concurrent request is in pending standby state then click on verify button and resubmit the request.

iii. if above two steps doesn't help then application developer intervention is required to re-validate the incompatibilities in application developer responsibility -> concurrent -> program -> Incompatibilities

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:

common mistakes by DBA

Listing down common mistakes that every DBA makes at least once in his career , there are few which are common for beginner but more often for "good experienced DBA's" too.

Here i highlighted experience DBA's are always good ,even though they make mistake but keeps courage to recover and learn from it :)

1 : Attempt to start the DB which is already running !

yeah ! i knew it you have done that , so even i.

This mostly happens during planned activity , OS admin asked to startup DB but its already up through cluster through init services.

CRS-4013: This command is not supported in a single-node configuration

Issue: Getting CRS-4013 , CRS-4000 error while starting up cluster services

CRS-4013: This command is not supported in a single-node configuration.
CRS-4000: Command Start failed, or completed with errors.

One of the cluster node is reporting the CRS-4013 , CRS-4000 error while other node is up in 2 node RAC cluster

Getting Google AdSense approval for your website or blogging site requires consistent effort with discipline And has no shortcuts or tricks

Following steps will guide you to know the prerequisites for creating a good website and will increase chances to get your website approved by AdSense

1. Know your subject

Congratulations ! For making your mind agree to create your own website which puts you in blogger/writer category.

iv. Fix-up errors , missing OS packages

iii. Client re-installation

Cleanup up old binaries

[oracle@testlaba /]$ cd /rdbms
[oracle@testlaba rdbms]$ ls -lrt
total 28
drwx------. 2 oracle root 16384 Jul 14 17:47 lost+found
drwx------. 3 oracle oracle 4096 Jul 19 12:47 client_dump
drwxr-xr-x. 2 oracle oracle 4096 Jul 19 12:59 oraInventory
drwx------. 6 oracle oracle 4096 Jul 19 13:49 app

Error:

Post Client installation receiving error as

[oracle@testlaba ~]$ sqlplus "/ as sysdba"

sqlplus: error while loading shared libraries: libclntshcore.so.12.1: cannot open shared object file: No such file or directory

Possible Cause:

5. Sqlnet

The "sqlnet.ora" file contains client side network configuration parameters located at "$ORACLE_HOME/network/admin" or "$ORACLE_HOME/net80/admin" directory on the client.

This file will also be present on the server if client style connections are used on the server itself.

Here is an example of an "sqlnet.ora" file.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH set to specify the order of the naming methods used for client name resolution lookups

Tnsnames Set to resolve a net service name through the tnsnames.ora file on the client

Ldap Set to resolve a database service name, net service name, or net service alias through a directory server.

ezconnect or hostname

 Select to enable clients to use a TCP/IP connect identifier, consisting of a host name and optional port and service name

cds Set to resolve an Oracle database name in a Distributed Computing Environment (DCE) environment.

Nis Set to resolve an Oracle database name in a Distributed Computing Environment (DCE) environment.

SQLNET.AUTHENTICATION_SERVICES used to enable one or more authentication services.

If authentication has been installed, it is recommended that this parameter be set to either none or to one of the authentication methods.

NONE for no authentication methods. A valid username and password can be used to access the database.

ALL for all authentication methods

NTS for Windows NT native authentication

6. Control file

Control file is a small binary file that records the physical structure of the database. The control file also includes:

2. Password

If the DBA wants to startup a stopped oracle instance whose database dictionary is not accessible how oracle would authenticate the DBA?

With the Help of password file & parameter remote_login_passwordfile or using OS authentication

This will allow authentication of the DBA to happen outside of the database either using password file or through the operating system (sqlplus “/ as sysdba”) from oracle owner.

The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.

The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.

Password files creation:

DB Files:

1. Parameter
2. Password
3. tnsnames
4. Listener
5. Sqlnet
6. Control file
7. Data files
8. Temp file
9. Online Redo Log File
10. Archive Log

1. Parameter

Parameter is a mandatory file required to start database instance in nomount stage by reading instance characteristics

Parameter files hold DB parameter & its respective values

Below Query can be used to find parameter file being used during db startup

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM v$parameter WHERE name = 'spfile';

Parameter files located in default location $ORACLE_HOME/dbs (UNIX) & %ORACLE_HOME%database (Windows)

PFILE:

6. ARCn

ARCn is background process spawn in ARCHIVELOG DB mode, where n ranges from 0-9

ARCn copies online redo log files to a designated storage device after

- A log switch has occurred

- alter database archivelog current; is executed

- No more online redo log file is available & to be overwritten

An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. The alert file keeps a record of when LGWR starts a new ARCn process.

Dynamic LOG_ARCHIVE_MAX_PROCESSES can be set to max 10.

Starting from one ARCn process oracle automatically starts up more ARCn processes till LOG_ARCHIVE_MAX_PROCESSES when the database workload requires more.

Arch can be stopped and started dynamically with alter system archive log stop|start or using below commands (deprecated 10g onwards)

archive log list

archive log stop

archive log start

archive log next

archive log all

archive log n

Query v$archive_processes to find out how many arch processes are running

7. RECO

In a distributed database, the recoverer process (RECO) automatically resolves failures in distributed transactions.

The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When RECO re-establishes a connection between the databases, it automatically resolves all in-doubt transactions, removing from each database's pending transaction table any rows that correspond to the resolved transactions

8. RVWR

The Recovery Writer process is responsible for writing to the flashback logs in the flash recovery area.

These logs are required to restore the database to a previous point in time by using the "flashback" option for Oracle databases (Oracle 10g and later)

Background Processes:

    1.   PMON
    2.   SMON
    3.   DBWn
    4.   LGWR
    5.   CKPT
    6.   ARCn
    7.   RECO
    8.   RVWR
    9.   MMON
10.   MMNL
  11.   MMAN
12.   CJQn

1. PMON

PMON is an oracle background process called process monitor.

PMON responsible for releasing Locks & cleaning up cache post abrupt termination of user process holding resources

PMON also communicates workload advisory to listener in real application cluster environment which helps listener to redirect connection to least loaded instance

PMON restarts dispatcher & server process if it fails

PMON is responsible for service registration to listener which avoids manual configuration of service in listener.ora file by supplying information like database/instance name & its current/maximum load, dispatcher/server mode (shared/dedicated) info.

2. SMON

SMON is system monitor process.

SMON is responsible for performing instance recovery during startup of DB post abrupt shutdown/termination of instance.

SMON performs two operation while performing instance recovery i.e. Roll forward & Roll Back during which it refers UNDO & Online Redo log file

SMON coalesces adjacent free extents into large free extents in dictionary managed tablespace

It wakes up every 5 min to perform Housekeeping activity, killing SMON background process terminates instance

In RAC, SMON process of one instance can perform instance recovery of other instance that has failed

SMON cleans up temporary segments that are no longer in use and recovers dead transactions which were skipped during system failure/instance recovery because of file-read errors or offline file status. Such transactions are recovered by SMON when the tablespace or data file is brought back online.

3. Large Pool

Large pool was brought in to optimize shared pool by isolating heavy IO servers operation like Backup/restoration using parameter LARGE_POOL_SIZE.

Large Pool allocates memory required during disk IO server process operations of Backup & recovery.

Also parallel query buffers & Oracle XA memory allocation taken care by large pool

select pool, name, bytes from v$sgastat where pool like '%large_pool%' order by pool, name;

4. Java Pool

Java pool memory is used for all session-specific Java code and data within the JVM.

The Java Pool Advisor statistics provide information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate. The Java Pool Advisor is internally turned on when statistics_level is set to TYPICAL or higher. These statistics resets when the advisor is turned off.

select pool, name, bytes from v$sgastat where pool like '%java%' order by pool, name;

Will walk through the SGA memory components in detail below

1. Database Buffer

Database buffer plays significant role in SGA by keeping blocks/granules read from datafile & allowing user processes to read/write the block concurrently. DB cache value is driven by parameter DB_CACHE_SIZE

Database buffer/Cache manages the blocks by an internal algorithm using write & LRU (Least Recently Used) lists

Write List holds all the dirty blocks, means the blocks which are modified by user processes in memory & should be written to DB file back again to retain consistent image of a row/data.

LRU list contains two ends, MRU (Most Recently Used) & LRU (Least Recently Used).

LRU end holds dirty, pined, free buffer/blocks. Pinned buffer means blocks which are currently in use & free is free J

Dirty blocks held by LRU end of LRU list are being moved to write list.

An Oracle Database is a Relational Database management System used to store & retrieve the related information.

An Oracle Database server/instance consists of shared memory structure, background processes & storage which handles the functional requirement to manage concurrent & shared data access by users.

Oracle Database product has evolve though its 8i, 9i, 10g & 11g version

Oracle Basic Architecture:

Overview:

Oracle database server is a part of multitier architecture includes Client Machine/Webserver, Middleware Application & Database Server.

Client Machine/Webserver:

ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted]

Issue:

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted
], [], [], [], [], [], [], [], [], [], [], []
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 2 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted

Cause:

The issue occurs since 11g as we are now using /dev/shm when using native PLSQL compilation.

Tabs

Life of DBA

Getting Google AdSense approval for your website or blogging site requires consistent effort with discipline And has no shortcuts or tricks

Following steps will guide you to know the prerequisites for creating a good website and will increase chances to get your website approved by AdSense

1. Know your subject

(adsbygoogle = window.adsbygoogle || []).push({}); iv. Fix-up errors , missing OS packages

(adsbygoogle = window.adsbygoogle || []).push({}); iii. Client re-installation

Cleanup up old binaries

Possible Cause:

Database Errors

How To

Technology

iv. Fix-up errors , missing OS packages

iii. Client re-installation