elcarOnOsdnaH

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.

Friday, July 12, 2013

This summary is not available. Please click here to view the post.

Friday, June 21, 2013

Do You Really Need oraclehomeproperties.xml file ??!!

Recently, when we were trying to apply a SPU Apr2013 patch to our Oracle Cluster Database, albeit thru OEM Deployment Procedure, during analyze step we hit the following error. The error was indicating that oraclehomeproperties.xml file was missing from the 2, out of 3 hosts missing. while digging further we figured out following..

Info about oraclehomeproperties.xml File --

    This file contains the details about the node list, the local node name, and the CRS flag for the Oracle Home.
    In a shared Oracle Home, the local node information is not present.
    This file also contains the following information:
    GUID — Unique global ID for the Oracle Home
    ARU ID — Unique platform ID. The patching and patchset application depends on this ID.
    ARU ID DESCRIPTION — Platform description

NOTE : The information in oraclehomeproperties.xml overrides the information in inventory.xml. This file is located under $ORACLE_HOME/inventory/ContentsXML

Other folders
Folder Name    Description
Scripts     Contains the scripts used for the cloning operation
ContentsXML     Contains the details of the components and libraries installed
Templates     Contains the template files used for cloning.
oneoffs     Contains the details of the one-off patches applied

I did check the other cluster to check the existance and the contents for the same file and found that all nodes in cluster DB home has this file and each one contains different GUID. hmmm.. So somehow the cluster I intend to patch doesn't contain this file. I spend lots of time on MOS to figure out how to recreate this one and couldn't find much info.

Suddenly, thought came across the mind and I decided to copy the file from one node to other remaining two nodes of the cluster and tried to apply the patch and would you believe it. My analyze step completed successfully.

So the lesson learned here is that oraclehomeproperties.xml file contains the info about GUID and ARU ID which informs patching process about platform and ID of the node.

Wednesday, June 12, 2013

Cloning Oracle 11g Agent On AIX/LINUX

I was, recently, trying to setup agent on some old Oracle legacy servers and run into issues of packages and utilities missing like, gzip, wget not present and wget present was not supporting https.
This were the hosts were nightmare as no monitoring was available and upgrading them with latest patches and technology levels were not ventured due to the risk of breaking them.

So DBAs have to find the way round on how to install agents on them and bring them under OEM umbrella.

Normally, you can do the install of an agent using following well known methods.
1. Push
2. Pull
3. Using Setup / Silent Install
4. Clone

We decided to go for cloning as other options were not working for us.
Following is the method one can use to clone agent.

1. Identify the host where you have agent up and running, this will provide you source install.

2. Zip the Agent Oracle home that you want to clone (for example, agent.zip).
oracle@xxxxxxxx> zip -r agent11g.zip ./agent11g/

3. Perform a file transfer (ftp,scp) of this zipped Oracle home onto the destination host where you want to install the cloned Agent (for example, ftp agent.zip).

4. In the destination host, unzip the Agent Oracle home (for example, unzip agent.zip).
oracle@xxxxxxxx> unzip agent11g.zip

5.Remove all the files from the location $AGENT_HOME/sysman/emd/collection in the destination host

6. On the destination host, Go to $Agent_ORACLE_HOME/oui/bin/ directory and execute the following command:

./runInstaller -clone -forceClone ORACLE_HOME=<full path of Oracle home> ORACLE_HOME_NAME=<Oracle home name> -noconfig -silent

Where ORACLE_HOME is the unzipped Agent Oracle home on the destination host

oracle@xxxxxxx /u01/app/oracle/agent11g/agent11g/oui/bin> ./runInstaller -clone -forceClone ORACLE_HOME=/u01/app/oracle/agent11g/agent11g ORACLE_HOME_NAME=agent11ghome1 -noconfig -silent
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 17343 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-06-06_11-42-34AM. Please wait ...oracle@hofdvorc02 /u01/app/oracle/agent11g/agent11g/oui/bin> Oracle Universal Installer, Version 11.1.0.8.0 Production
Copyright (C) 1999, 2010, Oracle. All rights reserved.

You can find the log of this install session at:
/oracle/server/oraInventory/logs/cloneActions2013-06-06_11-42-34AM.log
.................................................................................................... 100% Done.

Installation in progress (Thursday, June 6, 2013 11:42:40 AM CDT)
............................................................ 60% Done.
Install successful

Linking in progress (Thursday, June 6, 2013 11:42:43 AM CDT)
. 61% Done.
Link successful

Setup in progress (Thursday, June 6, 2013 11:43:01 AM CDT)
....................... 100% Done.
Setup successful

End of install phases.(Thursday, June 6, 2013 11:43:03 AM CDT)
Starting to execute configuration assistants
The following configuration assistants have not been run. This can happen because Oracle Universal Installer was invoked with the -noConfig option.
--------------------------------------
The "/u01/app/oracle/agent11g/agent11g/cfgtoollogs/configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
The "/u01/app/oracle/agent11g/agent11g/cfgtoollogs/configToolAllCommands" script contains all commands to be executed by the configuration assistants. This file may be used to run the configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.

--------------------------------------
WARNING:
The following configuration scripts need to be executed as the "root" user.
#!/bin/sh
#Root script to run
/u01/app/oracle/agent11g/agent11g/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts

The cloning of agent11ghome1 was successful.

7. Once install is done, Execute the following script to run the Agent Configuration Assistant (agentca):

$Agent_ORACLE_HOME/bin/agentca -f

oracle@xxxxxxx /u01/app/oracle/agent11g/agent11g/bin> ./agentca -f

Stopping the agent using /u01/app/oracle/agent11g/agent11g/bin/emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Running agentca using /u01/app/oracle/agent11g/agent11g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/agent11g/agent11g ACTION=Configure MODE=Perform RESPONSE_FILE=/u01/app/oracle/agent11g/agent11g/response_file RERUN=TRUE INV_PTR_LOC=/u01/app/oracle/agent11g/agent11g/oraInst.loc COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}
Perform - mode is starting for action: Configure

Perform - mode finished for action: Configure

You can see the log file: /u01/app/oracle/agent11g/agent11g/cfgtoollogs/oui/configActions2013-06-06_11-45-26-AM.log

Stopping the agent using /u01/app/oracle/agent11g/agent11g/bin/emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.

Running Agent Addon Configuration using /u01/app/oracle/agent11g/agent11g/perl/bin/perl /u01/app/oracle/agent11g/agent11g/sysman/install/AddonConfig.pl
Arguments passed

Configuring Addon from xml : oracle.sysman.plugin.virtualization.agent.11_1_0_1_0.xml

Running Command : /u01/app/oracle/agent11g/agent11g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/agent11g/agent11g ACTION=configure MODE=perform RERUN=true RESPONSE_FILE=/u01/app/oracle/agent11g/agent11g/vt_responsefile COMPONENT_XML={oracle.sysman.plugin.virtualization.agent.11_1_0_1_0.xml}
Setting the invPtrLoc to /u01/app/oracle/agent11g/agent11g/oraInst.loc

perform - mode is starting for action: configure

perform - mode finished for action: configure

You can see the log file: /u01/app/oracle/agent11g/agent11g/cfgtoollogs/oui/configActions2013-06-06_11-46-22-AM.log

Agent Addon Configuration done

Starting the agent using /u01/app/oracle/agent11g/agent11g/bin/emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Agent is already running

oracle@xxxxxxx /u01/app/oracle/agent11g/agent11g/bin> ./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /u01/app/oracle/agent11g/agent11g
Agent binaries : /u01/app/oracle/agent11g/agent11g
Agent Process ID : 21163
Parent Process ID : 21118
Agent URL : http://xxxxxxxxxxxxxxxxxxxxxx:3872/emd/main/
Repository URL : https://xxxxxxxxxxxxxxxxxxxxxxxx:4900/em/upload/
Started at : 2013-06-06 11:46:24
Started by user : oracle
Last Reload : 2013-06-06 11:46:24
Last successful upload : (none)
Last attempted upload : (none)
Total Megabytes of XML files uploaded so far : 0.00
Number of XML files pending upload : 20
Size of XML files pending upload(MB) : 20.04
Available disk space on upload filesystem : 65.34%
Last attempted heartbeat to OMS : 2013-06-06 11:46:27
Last successful heartbeat to OMS : unknown
---------------------------------------------------------------
Agent is Running and Ready

8. Run the newly cloned Agents root.sh script

$Agent_ORACLE_HOME/root.sh as root user
root@xxxxxxx /u01/app/oracle/agent11g/agent11g> root.sh

Now one needs to try and test a upload from agent home to OEM.

oracle@xxxxxxxxxxx /u01/app/oracle/agent11g/agent11g/bin> ./emctl upload agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

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

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors.

If you come across above errors, one has to clear the previous state of the agent. This happens when you have upload files pending in collections, upload or state directory. Also when you check status of the agent , try to look at lines highlighted in RED, that will tell you that something is not right. If the agent is configured correctly, it will show some data.

Now lets try to fix the issue on hand.

oracle@xxxxx /u01/app/oracle/agent11g/agent11g/bin> ./emctl stop agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Stopping agent ... stopped.

oracle@xxxxxxx /u01/app/oracle/agent11g/agent11g/bin> ./emctl clearstate agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

EMD clearstate completed successfully

oracle@xxxxx /u01/app/oracle/agent11g/agent11g/bin> ./emctl secure agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Agent is already stopped... Done.

Securing agent... Started.

Enter Agent Registration Password :

Securing agent... Successful.

oracle@xxxxxxx /u01/app/oracle/agent11g/agent11g/bin> ./emctl start agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Starting agent ..... started.

oracle@xxxxxx /u01/app/oracle/agent11g/agent11g/bin> ./emctl upload agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

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

EMD upload completed successfully

Now if you check on OEM targets page, you should see the newly configured host available for monitoring.

Friday, May 24, 2013

Changing CRS/Database timezone in 11.2.0.2 post install

Recently we need to modify our prod RAC cluster timezone from CST to EST. This is 8 node cluster hence we got to make sure the changes are reflected on all nodes properly.

First thing is to make sure your OS is reflecting the correct TZ as per your need. Usually this is been taken care by SAs.
So one needs to get confirmation from SAs that OS is ready with new Time zone values. Post that DBAs need to perform following changes on Grid

The timezone info in Grid home is stored in the following file ....
$GRID_HOME/crs/install/s_config_(hostname).txt

#cd /u01/app/grid/11.2.0.2/crs/install

-- file which contains the values is following, where xxxxx is my host name.

# cat s_crsconfig_xxxxxxxx.txt
TZ=UTC
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
TNS_ADMIN=
ORACLE_BASE=

To resolve the issue we need to change TZ to EST on all nodes and restart clusterware. So entry would be like
===
From TZ=UTC ==> TZ=America/New_York

On Restarting clusteware , database and clusteware starts with correct timezone.

Remember - The time zone of the system is defined by the contents of /etc/localtime

Monday, May 20, 2013

ORA-00020 on DB Instance / HUNDREDS OF ORAAGENT.BIN@HOSTNAME SESSSIONS IN 11.2.0.2 DATABASE

The issue happen on one of our production env running 8 node cluster. The DB having issue was running on two nodes of the cluster. One one node, say node A, it was running fine. However, on another node B, it was not allowing connection to DB. Later on we figured out the process parameter seems exhausted and we increased the "PROCESSES" parameter from 100 to 500.

Time went by and again after few weeks we started seeing the same problem. Some thing is not right as ASM instance doesn't eat that many processes. Oracle Best Practices for the ASM instance suggests to keep 100 Processes for medium to high load DB.

Checking went on, one surprising issue that I saw during the event is that there were lots of ORAAGENT processes spawned. Total I saw was around 700+. Not sure why. So we decided to clear everything and start the instance afresh. We shut down the instance and on the startup surprise was waiting for us. ASM Instance was able to start and to our surprise the process from oraagent.bin were still in place.

SQL> startup
ORA-03113: end-of-file on communication channel

Once it happens, other instance may fail to restart as no new connection can be made to the ORA-00020 instance

To double check my suspicion I checked the log files and trace files. And what I found is following.
[grid@xxxx cdmp_20130405075132]$ cat /u01/app/grid/diag/asm/+asm/+ASM7/trace/+ASM7_diag_30046.trc | grep oraagent.bin@tryprorarac2g.intra.searshc.com | wc -l
734

It perfectly fits the figure of 700 odd connections that I saw. Eventually ORA-00020 will happen to DB instance as oraagent.bin keeps making new connections without closing them. Not sure why they were abundance, I checked MOS and I came across following two bug notes and they fits the bill. I also checked that we have not applied PSU3 , in which they say, they have fixed it.

Bug 11877079 : HUNDREDS OF ORAAGENT.BIN@HOSTNAME SESSSIONS IN 11.2.0.2 DATABASE
Bug 10299006 : AFTER 11.2.0.2 UPGRADE, ORAAGENT.BIN CONNECTS TO DATABASE WITH TOO MANY SESSIONS

So, we ultimately decided to reboot the box to kill all the ORAAGENT.BIN processes. Post refresh ASM came up clean and DB started successfully.

Hope this will help you to trouble shoot your ASM bug.

On a side note, this is also fixed in 11.2.0.3 and we recently applied 11.2.0.3 Grid Upgrade to fix this. DBs are still running with 11.2.0.2 Version.

Monday, April 22, 2013

Cluster Agent Install fails with OUI-35000 & PRKC-1044

While installing the cluster OEM agent on RAC, we hit following error.

[oracle@rac1a bin]$agentDownload.linux_x64 -b /u01/app/oracle/product -m oemgc1 -r 7799 -n XXXPRD -c "rac1a,rac1b" -y

ERROR: OUI-35000: Fatal cluster error encountered (PRKC-1044 : Failed to check remote command execution setup for node rac1b using shells /usr/bin/ssh and /usr/bin/rsh

rac1b: Connection refusedPRKC-1044 : Failed to check remote command execution setup for node rac1a using shells /usr/bin/ssh and /usr/bin/rsh

rac1a: Connection refused). Correct the problem and try the operation again.

Completed with Status=1

Primarily the issue seems to be with rsh setup on cluster nodes. Initially it was not setup hence we got it done with help of SA's. So RSH was in place but again it failed with same error.

So I decided to run it from another node in cluster and After running from it, hit the following err at end...

ERROR: Remote 'AttachHome' failed on nodes: 'rac1a'. Refer to '/u01/app/oraInventory/logs/installActions2013-04-12_05-47-02AM.log' for details.

You can manually re-run the following command on the failed nodes after the installation:

/u01/app/oracle/product/agent11g/oui/bin/runInstaller -attachHome -noClusterEnabled ORACLE_HOME=/u01/app/oracle/product/agent11g ORACLE_HOME_NAME=agent11g1 CLUSTER_NODES=rac1a,rac1b "INVENTORY_LOCATION=/u01/app/oraInventory" LOCAL_NODE=<node on which command is to be run>.

That looks like install went fine but agents were not configured at all. Also when you check inventory the agent home was not registered with it, hence above message made some sense.

So I went ahead and ran following command on both nodes one by one.

runInstaller -attachHome -noClusterEnabled ORACLE_HOME=/u01/app/oracle/product/agent11g ORACLE_HOME_NAME=agent11g1 CLUSTER_NODES=rac1a,rac1b "INVENTORY_LOCATION=/u01/app/oracle/oraInventory" LOCAL_NODE=rac1a

On Node1 -

[oracle@rac1a bin]$ ./runInstaller -attachHome -noClusterEnabled ORACLE_HOME=/u01/app/oracle/product/agent11g ORACLE_HOME_NAME=agent11g1 CLUSTER_NODES=rac1a,rac1b "INVENTORY_LOCATION=/u01/app/oraInventory" LOCAL_NODE=rac1a

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 19077 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-04-12_06-46-16AM. Please wait ...[oracle@rac1a bin]$ The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oraInventory

Please execute the 'null' script at the end of the session.

'AttachHome' was successful.

[oracle@rac1a bin]$ ./agentca -f -n XXXPRD -c rac1a,rac1b

CLUSTER_NAME environment variable is set to XXXPRD

The ORACLE_HOME=/u01/app/oracle/product/agent11g doesn't exist in the oraInventory specified in /oracletemp/PS1/oraInventory, Please specify the correct oraInventory location using -i option

Since there are multiple ORACLE_HOME on this host, the inventory pointer was wrong in /etc/oraInst.loc.

[oracle@rac1a bin]$ cat /etc/oraInst.loc

#inventory_loc=/oracletemp/oraInventory

inst_group=dba

So inventory was wrong hence needs to fix it to point at right location after adding following entry

inventory_loc=/u01/app/oraInventory

[oracle@rac1a bin]$ cat /etc/oraInst.loc

#inventory_loc=/oracletemp/oraInventory

inventory_loc=/u01/app/oraInventory

inst_group=dba

[oracle@rac1a bin]$ ./agentca -f -n XXXPRD -c rac1a,rac1b -i /etc/oraInst.loc

CLUSTER_NAME environment variable is set to XXXPRD

Stopping the agent using /u01/app/oracle/product/agent11g/bin/emctl stop agent

EM Configuration issue. /u01/app/oracle/product/agent11g/rac1a not found.

Running agentca using /u01/app/oracle/product/agent11g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/product/agent11g ACTION=Configure MODE=Perform RESPONSE_FILE=/u01/app/oracle/product/agent11g/response_file RERUN=TRUE INV_PTR_LOC=/etc/oraInst.loc COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}

Perform - mode is starting for action: Configure

Perform - mode finished for action: Configure

You can see the log file: /u01/app/oracle/product/agent11g/cfgtoollogs/oui/configActions2013-04-12_07-14-18-AM.log

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 19077 MB Passed

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oraInventory

'UpdateNodeList' was successful.

Starting the agent using /u01/app/oracle/product/agent11g/bin/emctl start agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Starting agent ......... started.

Stopping the agent using /u01/app/oracle/product/agent11g/bin/emctl stop agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Stopping agent ... stopped.

Running Agent Addon Configuration using /u01/app/oracle/product/agent11g/perl/bin/perl /u01/app/oracle/product/agent11g/sysman/install/AddonConfig.pl

Arguments passed

Configuring Addon from xml : oracle.sysman.plugin.virtualization.agent.11_1_0_1_0.xml

Running Command : /u01/app/oracle/product/agent11g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/product/agent11g ACTION=configure MODE=perform RERUN=true RESPONSE_FILE=/u01/app/oracle/product/agent11g/vt_responsefile COMPONENT_XML={oracle.sysman.plugin.virtualization.agent.11_1_0_1_0.xml}

Setting the invPtrLoc to /u01/app/oracle/product/agent11g/oraInst.loc

perform - mode is starting for action: configure

perform - mode finished for action: configure

You can see the log file: /u01/app/oracle/product/agent11g/cfgtoollogs/oui/configActions2013-04-12_07-15-12-AM.log

Agent Addon Configuration done

Starting the agent using /u01/app/oracle/product/agent11g/bin/emctl start agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Agent is already running

On Node2 -

oracle@rac1b /u01/app/oracle/product/agent11g/bin> ./agentca -f -n XXXPRD -c rac1a,rac1b

CLUSTER_NAME environment variable is set to XXXPRD

Stopping the agent using /u01/app/oracle/product/agent11g/bin/emctl stop agent

EM Configuration issue. /u01/app/oracle/product/agent11g/rac1b not found.

Running agentca using /u01/app/oracle/product/agent11g/oui/bin/runConfig.sh ORACLE_HOME=/u01/app/oracle/product/agent11g ACTION=Configure MODE=Perform RESPONSE_FILE=/u01/app/oracle/product/agent11g/response_file RERUN=TRUE INV_PTR_LOC=/u01/app/oracle/product/agent11g/oraInst.loc COMPONENT_XML={oracle.sysman.top.agent.10_2_0_1_0.xml}

Perform - mode is starting for action: Configure

Running Agent Addon Configuration using /u01/app/oracle/product/agent11g/perl/bin/perl /u01/app/oracle/product/agent11g/sysman/install/AddonConfig.pl

Arguments passed

Configuring Addon from xml : oracle.sysman.plugin.virtualization.agent.11_1_0_1_0.xml

Setting the invPtrLoc to /u01/app/oracle/product/agent11g/oraInst.loc

perform - mode is starting for action: configure

perform - mode finished for action: configure

You can see the log file: /u01/app/oracle/product/agent11g/cfgtoollogs/oui/configActions2013-04-12_07-21-35-AM.log

Agent Addon Configuration done

Starting the agent using /u01/app/oracle/product/agent11g/bin/emctl start agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Agent is already running

Once done, cluster was properly discovered by OEM GC and agent was able to upload data successfully....