elcarOnOsdnaH

Tuesday, September 17, 2013

ADRCI Purge Command Fails With DIA-48322

When running adrci from Grid home for removal or purge of the old data when we ran purge command, we hit following.

adrci> purge
DIA-48322: Relation [INCIDENT] of ADR V[2] incompatible with V[2] tool
DIA-48210: Relation Not Found
DIA-48166: error with opening ADR block file because file does not exist

This happened when trying to purge the listener Home.
The reason being is that this home does not contain any activity to be purged:

adrci> show incident
0 rows fetched

However this is equally applicable to other homes as well and only restricted to listener home.

These errors are raised when you run a purge command in the listener/other ADR Home without incidents to purge. So if may seem like bug but actually not a bug as this is an expected behavior in this situation as there is nothing to purge.

Thursday, August 29, 2013

Instance Caging

Recently on one of our production cluster running 8 nodes and having shared environment with multiple applications, ran into high CPU usage. Since this is shared environment, running few databases, its always difficult to pinpoint which is culprit. Hence after more than couple of incidents we were able to point out which one is actually causing mayhem. Also the pattern of problem is not predictable as application is used across globe. But since hang was not momentarily it really affects all other candidates as well. This is serious problem in shared environment as one wants to save cost on hardware and software, which is quite under stable. However, this also ensures the optimum usage of the capacity as not all the databases are always used up to the capacity (again, case to case).

So the question is how do we avoid such an instance and still provide the optimum usage of resources ?

11g, introduces one new feature called "instance caging", where you can have more than one instance on the same server, and you can share the CPUs reported by the operating system

-- To find out total cpu and cores available to your system

SQL> select cpu_count_current,cpu_core_count_current from v$license;

System with 12 physical cpu cores

CPU_COUNT_CURRENT CPU_CORE_COUNT_CURRENT

----------------- ----------------------

12 12

System with 6 physical cpu and 12 cores, Hyper Threaded

CPU_COUNT_CURRENT CPU_CORE_COUNT_CURRENT

----------------- ----------------------

12 6

One can use following query to find out the CPU SOCKET and CPU CORES available to database

select * from v$osstat where stat_name like '%CPU%'

In Hyper threaded Servers, the output will be like following.

Now one needs to determine how the database instances on your server will share the CPU. With Instance Caging, each instance's cpu_count specifies the maximum number of CPUs you want it to use at any time for that particular instance. The sum of the cpu_counts across all database instances determines the amount of isolation between the database instances and the efficiency of the server.

For maximum isolation one can use "partition" approach. With the partition approach, the sum of the cpu_counts is less than or equal to the number of CPUs.

For example, suppose the total number of CPUs (i.e. CPU threads/cores) is 16. Using the partition approach, we could set cpu_count=8 for database A, cpu_count=4 for database B, and cpu_count=4 for database C. The sum of the cpu_counts is 16, which equals the number of CPUs.

The disadvantage of the partition approach is that any CPU unused by one database instance cannot be used by another. Therefore, for non-critical databases where you also want to achieve better CPU utilization efficiency, use the "over-subscribe" approach. With the over-subscribe approach, the sum of the cpu_counts is less than or equal to 3x the number of CPUs.

For example, for a server with 16 CPUs, you could use the over-subscribe approach and set cpu_count=8 for database A, cpu_count=8 for database B, and cpu_count=8 for database C. The sum of the cpu_counts is 24, which is greater than the number of CPUs. Therefore, if all databases are using their full CPU allocation, there will be some CPU contention.

Enable Instance Caging –

-- Set the cpu_count initialization parameter.
-- This is a dynamic parameter, and can be set with the following statement:

SQL> ALTER SYSTEM SET CPU_COUNT = 4;

To verify that Instance Caging is enabled, check that "instance_caging" equals "ON" and that "cpu_count" is set appropriately.

SQL> select instance_caging from v$rsrc_plan where is_top_plan = 'TRUE';

INS

---

Once this is enabled, The CPU spikes will be limited to only specified number of cpu's and will be not be hampering other databases.

Wednesday, August 28, 2013

Identify The Top Temp Consumers In DataBase

First of all one needs to identify the sessions using temp space. The view reflecting this info is v$tempseg_usage.

One imp note is that v$tempseg_usage is same as v_$sort_area_usage view

SELECT s.sid, s.username, u.tablespace , s.sql_hash_value, u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM gv$session s, gv$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;

The main segment type one needs to check is SORT segment as that is what we are after.
Now we have session that is causing strain on our database sort area we need to check the statement causing all the mayhem. If you are in luck there might be only open cursor against this session. However, in heavily used production databases there will be multiple open cursors against one session.
So the view you need to consult is v$open_cursor. We also need to identify the number of child cursor for this parent cursor as well as text. Following is the way to do it..

select sql_id, sorts , sql_text from gv$sql
where hash_value in
(select hash_value from gv$open_cursor where sid=1920)
order by 2 desc;

As you can see there are possible two possible sql that are using temp at the moment. Now you have identified the possible culprits you can investigate further.

Following Script will tell you how much free space you have available in Temp Tablespace.
SQL> select b.tablespace_name, sum(b.bytes_used/1024/1024) used_mb,
     sum(bytes_free/1024/1024) free_mb
     from v$temp_space_header b
     group by b.tablespace_name;

Thursday, August 1, 2013

NET-BACKUP Configuration For Oracle

If the oracle_link script is executed, it will display a message similar to the following and backups of the Oracle instance will not utilize the NetBackup implementation of the Oracle SBT API.

Following is the work around to fix the issue. This is linux/Unix specific. For AIX one has to do modifications as per environment.

On the client host, manually create the needed symbolic link between the Oracle installation and the NetBackup installation.

1) Confirm that the NetBackup shared libraries are present on the client host and of the correct version.

$ cd /usr/openv/netbackup/

$ ./bin/goodies/support/versioninfo –f bin/libobk.so

======= bin/libobk.so =======

@(#) NetBackup_7.1 1234567890

2) Check for an existing libobk being used by Oracle and rename if present.

$ cd $ORACLE_HOME/lib

$ ls libobk.so

libobk.so

$ mv libobk.so libobk.so.orig

3) Create the symbolic link to the NetBackup shared library.

$ ln –s /usr/openv/netbackup/bin/libobk.so libobk.so

An updated oracle_link script is included in NetBackup release update 7.1.0.4 and release 7.5.

Error During OEM Agent Install On RAC with XML-20108: (Fatal Error) Start of root element expected. Completed with Status=102

During install of an agent on RAC environment we hit following error.

ERROR: <Line 1, Column 1>: XML-20108: (Fatal Error) Start of root element expected.

Completed with Status=102

Looked at MOS but that didn’t gave up any clues and has to look around a bit. Eventually when trying to look at installed products on one of the nodes in cluster, I found that entire inventory.xml file is missing.

So that might the cause of problem here.

Though I was not sure whether the problem is due to missing file so decided to go step by step. First thing first, I need an inventory.xml.

Now the standard way is to create inventory is to run runInstaller executable with attachHome option. However, I decided to try another work around, much simpler than earlier one. The reason being that, this is functional node and cannot do any changes to current configuration without change approval. So I decided to copy the inventory.xml from another node to the current node(assuming that both have ideal ORACLE products installed as they are part of same cluster).

Following is the snippet of the XML file copied.

<?xml version="1.0" standalone="yes" ?>

<COMPOSITEHOME_LIST>

</COMPOSITEHOME_LIST>

<VERSION_INFO>

<SAVED_WITH>11.1.0.8.0</SAVED_WITH>

<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>

</VERSION_INFO>

<HOME_LIST>

<NODE_LIST>

</NODE_LIST>

</HOME>

<NODE_LIST>

</NODE_LIST>

</HOME>

</HOME_LIST>

</INVENTORY>

Now with inventory.xml file in place when I tried installation, it worked.

Lesson learned –

Inventory.xml file is needed as any new product installed will add an entry in that file hence missing file may cause failures…

Wednesday, July 24, 2013

Replication Failure Due to Missing Archived Logs /

Issue During Restore of Archived Logs

During replication from our Source DB to target DB failed with following error…

a) The SCN portion of the log position 8940427876.0.0.0.0.0 is invalid. b) The SCN associated with log position 8940427876.0.0.0.0.0 does not correspond to a current on-line redo log file and ARCHIVELOG mode is not enabled. c) The redo log file(s) corresponding to log position 8940427876.0.0.0.0.0 does not currently exist, exists but is inaccessible or exists and is corrupt. d) The control file contains invalid information

Initially it looked like the archivelog was deleted from disk post backup hence we decided to restore the archivelogs as per the timeline given by Apps team.

We used following script to restore the archived logs from backup
run
{
ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t2 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t3 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t4 TYPE 'SBT_TAPE';
restore archivelog
from time = "to_date('JUL 13 2013 00:00:00','MON DD YYYY HH24:MI:SS')"
until time = "to_date('JUL 16 2013 05:00:00','MON DD YYYY HH24:MI:SS')";
RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
RELEASE CHANNEL t3;
RELEASE CHANNEL t4;
}

#select name, first_time, thread# from v$archived_log where 8583608519 between first_change# and next_change#

The logs were restored but when we execute we get following list blank name
NAME FIRST_TIM THREAD#
---------------------------------------- --------- -----------
01-JUL-13 1
01-JUL-13 2

The name column of the view v$archive_log will be blank after RMAN has deleted the archive log.

From the reference guide:

NAME VARCHAR2(513) Archived log file name. If set to NULL, either the log file was cleared before it was archived or an RMAN backup command with the "delete input" option was executed to back up archivelog all (RMAN> backup archivelog all delete input;).

We ran the following query to figure out the archive logs containing those changes…

set echo on feedback on time on timing on pagesize 100 linesize 80
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where 8940427876 between first_change# and next_change#
order by first_change#;

NAME
--------------------------------------------------------------------------------
THREAD# SEQUENCE# S FIRST_TIM NEXT_TIME FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- - --------- --------- ------------- ------------

1 16231 D 17-JUL-13 17-JUL-13 8939762971 8940683008

2 20020 D 17-JUL-13 17-JUL-13 8940108930 8940777999

Name was still blank, hence decided to check the backup pieces.

RMAN> list copy of archivelog sequence=16231 thread=1;
specification does not match any archived log in the repository

RMAN> list backup of archivelog sequence=16231 thread=1;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
19997 1.49G SBT_TAPE 00:00:51 18-JUL-2013 00:36:57
BP Key: 19997 Status: AVAILABLE Compressed: NO Tag: HOT_ARCH_BK
Handle: XXXX_ARC_ifof0pnm_1_1_821061366 Media: @aaaaj

List of Archived Logs in backup set 19997
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 16231 8939762971 17-JUL-2013 15:28:53 8940683008 17-JUL-2013 18:05:31

RMAN> list copy of archivelog sequence=20020 thread=2;
specification does not match any archived log in the repository

RMAN> list backup of archivelog sequence=20020 thread=2;
List of Backup Sets
==================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
19997 1.49G SBT_TAPE 00:00:51 18-JUL-2013 00:36:57
BP Key: 19997 Status: AVAILABLE Compressed: NO Tag: HOT_ARCH_BK
Handle: XXXX_ARC_ifof0pnm_1_1_821061366 Media: @aaaaj

List of Archived Logs in backup set 19997
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
2 20020 8940108930 17-JUL-2013 15:56:06 8940777999 17-JUL-2013 18:08:41

First, run the following SQL statement to determine the minimum and maximum sequence# for each thread/node that are still available on disk.

This will gives us the list of archive logs still on disk.

select thread#, min(sequence#), max(sequence#) from v$archived_log
where status = 'A'
group by thread#;

THREAD# MIN(SEQUENCE#) MAX(SEQUENCE#)
---------- -------------- --------------
1 16122 16256
2 19734 20063

To determine their FIRST_CHANGE# and FIRST_TIME, please run the following SQL statement, as shown here.

set echo on feedback on time on timing on pagesize 100 linesize 80
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where
(thread# = 1 and sequence# = 16122) or
(thread# = 2 and sequence# = 19734)
order by first_change#;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
THREAD# SEQUENCE# S FIRST_TIME NEXT_TIME FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- - -------------------- -------------------- ------------- ------------

2 19734 D 15-JUL-2013 23:46:55 16-JUL-2013 00:10:58 8899005572 8899582829 ç Original entry , deleted post RMAN backup

+PSFT_HR_PROD_DATA/XXXX/archivelog/2013_07_18/thread_2_seq_19734.1649.821074773
2 19734 A 15-JUL-2013 23:46:55 16-JUL-2013 00:10:58 8899005572 8899582829 ç restored archivelog backup

+PSFT_HR_PROD_DATA/XXXX/archivelog/2013_07_18/thread_1_seq_16122.1646.821074773
1 16122 A 15-JUL-2013 23:47:18 16-JUL-2013 00:00:19 8899012060 8899374054 ç restored archivelog backup

1 16122 D 15-JUL-2013 23:47:18 16-JUL-2013 00:00:19 8899012060 8899374054 ç Original entry , deleted post RMAN backup

I suggest to run the following RMAN script.

run
{
ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t2 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t3 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t4 TYPE 'SBT_TAPE';
restore archivelog from time = "to_date('JUL 14 2013 00:00:00','MON DD YYYY HH24:MI:SS')"
until time = "to_date('JUL 16 2013 00:10:58','MON DD YYYY HH24:MI:SS')";
RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
RELEASE CHANNEL t3;
RELEASE CHANNEL t4;
}

archived log for thread 1 with sequence 16122 is already on disk as file +PSFT_HR_PROD_DATA/XXXX/archivelog/2013_07_18/thread_1_seq_16122.1646.821074773
archived log for thread 1 with sequence 16123 is already on disk as file +PSFT_HR_PROD_DATA/XXXX/archivelog/2013_07_18/thread_1_seq_16123.1644.821074773
archived log for thread 1 with sequence 16124 is already on disk as file +PSFT_HR_PROD_DATA/XXXX/archivelog/2013_07_18/thread_1_seq_16124.1648.821074773
archived log for thread 2 with sequence 19734 is already on disk as file +PSFT_HR_PROD_DATA/XXXX/archivelog/2013_07_18/thread_2_seq_19734.1649.821074773
archived log for thread 2 with sequence 19735 is already on disk as file +PSFT_HR_PROD_DATA/XXXX/archivelog/2013_07_18/thread_2_seq_19735.1650.821074773
released channel: t1
released channel: t2
released channel: t3
released channel: t4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/18/2013 20:45:45
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 2 with sequence 19733 and starting SCN of 8897710936 found to restore

So if you see, the sequence its showing for thread 2 is the one before what is available on disk. So now we have to check the backup piece containing this particular log sequence.

RMAN archive log backup shows following…

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
19842 1.30G SBT_TAPE 00:00:35 16-JUL-2013 04:58:32
BP Key: 19842 Status: EXPIRED Compressed: NO Tag: HOT_ARCH_BK
Handle: XXXX_ARC_dioes0al_1_1_820904277 Media: @aaaaj

List of Archived Logs in backup set 19842
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 16120 8898316623 15-JUL-2013 23:16:07 8898774568 15-JUL-2013 23:31:54
1 16121 8898774568 15-JUL-2013 23:31:54 8899012060 15-JUL-2013 23:47:18
2 19733 8897710936 15-JUL-2013 22:58:36 8899005572 15-JUL-2013 23:46:55 ç thread 2 with sequence 19733 and starting SCN of 8897710936
2 19734 8899005572 15-JUL-2013 23:46:55 8899582829 16-JUL-2013 00:10:58

According to the output, the RMAN backup piece 'XXXX_ARC_dioes0al_1_1_820904277' contain the archivelog sequence# 19733 of thread# 2, but the RMAN backup status is EXPIRED. That explains why you are getting the following error, as shown here, because the RMAN backup is EXPIRED, which contains the archivelog sequence# 19733 of thread# 2

RMAN-06025: no backup of archived log for thread 2 with sequence 19733 and starting SCN of 8897710936 found to restore

To change the status from EXPIRED to AVAILABLE, run the following RMAN script.

spool log to rman_archivelog_crosscheck01.log
set echo on
run
{
ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t2 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t3 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t4 TYPE 'SBT_TAPE';
crosscheck backup;
RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
RELEASE CHANNEL t3;
RELEASE CHANNEL t4;
}
spool log off

Afterward, run the following RMAN restore script

spool log to rman_archivelog_restore02.log
set echo on
run
{
ALLOCATE CHANNEL t1 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t2 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t3 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL t4 TYPE 'SBT_TAPE';
restore archivelog from time = "to_date('JUL 14 2013 00:00:00','MON DD YYYY HH24:MI:SS')"
until time = "to_date('JUL 16 2013 00:10:58','MON DD YYYY HH24:MI:SS')";
RELEASE CHANNEL t1;
RELEASE CHANNEL t2;
RELEASE CHANNEL t3;
RELEASE CHANNEL t4;
}
spool log off

set echo on feedback on time on timing on pagesize 100 linesize 80
select name, thread#, sequence#, status, first_time, next_time, first_change#, next_change#
from v$archived_log
where 8940427876 between first_change# and next_change#
order by first_change#;

Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 15955 8848106302 13-JUL-2013 20:08:40 8848165443 13-JUL-2013 20:09:05
1 15956 8848165443 13-JUL-2013 20:09:05 8849372833 13-JUL-2013 22:00:25
2 19508 8849012683 13-JUL-2013 20:38:13 8849372813 13-JUL-2013 22:00:24
2 19509 8849372813 13-JUL-2013 22:00:24 8849672629 14-JUL-2013 00:26:26

List of Archived Logs in backup set 19842
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 16120 8898316623 15-JUL-2013 23:16:07 8898774568 15-JUL-2013 23:31:54
1 16121 8898774568 15-JUL-2013 23:31:54 8899012060 15-JUL-2013 23:47:18
2 19733 8897710936 15-JUL-2013 22:58:36 8899005572 15-JUL-2013 23:46:55
2 19734 8899005572 15-JUL-2013 23:46:55 8899582829 16-JUL-2013 00:10:58

List of Archived Logs in backup set 19595
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 15957 8849372833 13-JUL-2013 22:00:25 8849499795 13-JUL-2013 23:19:50
1 15958 8849499795 13-JUL-2013 23:19:50 8850111952 14-JUL-2013 01:15:30
2 19510 8849672629 14-JUL-2013 00:26:26 8849927816 14-JUL-2013 01:13:23
2 19511 8849927816 14-JUL-2013 01:13:23 8850109483 14-JUL-2013 01:15:29

List of Archived Logs in backup set 19843
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 16122 8899012060 15-JUL-2013 23:47:18 8899374054 16-JUL-2013 00:00:19
1 16123 8899374054 16-JUL-2013 00:00:19 8899582716 16-JUL-2013 00:10:56
1 16124 8899582716 16-JUL-2013 00:10:56 8899757027 16-JUL-2013 00:24:31
2 19735 8899582829 16-JUL-2013 00:10:58 8900326154 16-JUL-2013 01:10:19

So given the time frame from Jul 14, 00:00 to Jul 16, 00:00 following are the sequence numbers for corresponding threads.

run
{
restore archive from sequence 19509 thread 2 until sequence 19735 thread 2;
restore archive from sequence 15958 thread 1 until sequence 16124 thread 1;
}

Once restored, the CDC started successfully and issue was resolved.