Search this Blog

Thursday, January 20, 2011

Copying Archived Logfile from ASM to a file system

Following OTN tread http://forums.oracle.com/forums/thread.jspa?threadID=2154801&tstart=0 about copying archived logfiles from ASM to a UNIX filesystem, I am posting a script we used in the past.
Nowadays we use RMAN to get the files from ASM directly into a TSM diskpool, but back then we used another method for putting the archived logfiles on backup, which resulted in the need for this script:


# Script to copy archived logfiles from ASM to a file system on UNIX
# In this case all archived logfiles found in v$archived_log are copied to a staging mount point /opt/asmprd/ar01
# The username/password used to access the database need to be set before use of this script.
# For sake of security I removed these lines from this script
#
# The script needs to be run as owner of the ASM instance
#
#
export ORACLE_SID=WCSPRD
export FSFULL=FALSE
export ORACLE_BASE=/opt/$LOGNAME/ora
export CRS_HOME=/opt/crsprd/ora/11.1.0
export ORA_CRS_HOME=$CRS_HOME
export ASM_HOME=/opt/asmprd/ora/11.1.0
export ORA_ASM_HOME=$ASM_HOME
export ORACLE_HOME=$ORACLE_BASE/11.1.0
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$CRS_HOME/lib:$ORACLE_HOME/lib32:$CRS_HOME/lib32
export LIBPATH=$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$CRS_HOME/bin:$PATH
export TNS_ADMIN=/opt/oraadmin/network/admin
export ORACLE_SID=+ASM
export HOST=`hostname`
export LOG_FILE=/var/adm/log/StageArchives_${HOST}.log
export A_FILE_COPIED=FALSE
#
# Optionally turn Debug on to see what the script does
export Debug=false
#
# Functions
#
Log() {
echo "`date +%Y/%m/%d` (`date +%H:%M:%S`) : $1" | tee -a $LOG_FILE
} #End Log
#
#
Continue()
{
echo "Continue....? [n]\c"; read ans
case $ans in
y|Y) echo "\n\nContinuing...."
break;;
*) exit 0
break;;
esac
unset ans
}
#
#
GetArchiveList()
{
echo "set termout off heading off pages 9999 lines 70 feedback off" > /tmp/getArchives.$$.sql
echo "column name format A70;" >> /tmp/getArchives.$$.sql
echo "spool /tmp/archivelist.lst;" >> /tmp/getArchives.$$.sql
echo "select trim(name) from v\$archived_log where deleted='NO' and archived='YES';" >> /tmp/getArchives.$$.sql
echo "quit;" >> /tmp/getArchives.$$.sql
$ORACLE_HOME/bin/sqlplus -S ${WCSPRD_SYS}@WCSPRD as sysdba @/tmp/getArchives.$$.sql

if [ "$Debug" = "true" ]
then
cat /tmp/getArchives.$$.sql
Continue
fi
rm -f /tmp/getArchives.$$.sql
}


StoreArchivesInFS()
{
ArchName=$1
ArchShortName=`basename $1`
ArchDest=/opt/asmprd/ar01/${ArchShortName}
AvailFS=`df -gv /opt/asmprd/ar01 | grep -v Free| awk '{print $5}' | cut -d'%' -f1`
Log "Archiving $ArchDest"

if [ "$Debug" = "true" ]
then
echo "AvailFS = $AvailFS"
echo "Commando = $ASM_HOME/bin/asmcmd cp $ArchName $ArchDest"
Continue
fi
if [ $AvailFS -lt 90 ]
then
echo "$ASM_HOME/bin/asmcmd cp $ArchName $ArchDest"
$ASM_HOME/bin/asmcmd cp $ArchName $ArchDest >/dev/null 2>&1

if [ -f "$ArchDest" ]
then
export A_FILE_COPIED=TRUE
$ASM_HOME/bin/asmcmd rm $ArchName >/dev/null 2>&1
Log "$ArchName has been deleted from ASM."
else
echo "ArchDest=$ArchDest"
ls -l $ArchDest
fi
else
export FSFULL=TRUE
fi
}


RmanCrosscheck()
{
# Crosscheck and clean repository
#Log "Crosscheck and clean repository"
$ORACLE_HOME/bin/rman target ${WCSPRD_SYS}@WCSPRD rcvcat ${TSMORA_TSMCAT}@tsmora cmdfile StageArchives.cmd
d
}


# Main Program
PROG_PID=$$
RUNNING=`ps -ef| grep StageArchives.sh | grep -v ${PROG_PID} | grep -v grep | awk '{print $2}'`
if [ -n "$RUNNING" ]
then
Log "Old Running session(s) found with PID(s): $RUNNING "
ps -ef| grep ${RUNNING} | grep -v grep >> $LOG_FILE
for tpid in $RUNNING
{
kill $tpid
}
fi
GetArchiveList
cat /tmp/archivelist.lst | while read ARCHFILE
do
if [ ! "$ARCHFILE" = "" ]
then
StoreArchivesInFS $ARCHFILE
if [ "$FSFULL" = "TRUE" ]
then
#
# Insert some action here to clean up the destination file system
fi
fi
done
#
#
#

#
# Use Rman to update the recovery catalog
RmanCrosscheck

unset FSFULL ARCHFILE
#rm -f /tmp/archivelist.lst


The script contains a call to 'rman' using the command file 'StageArchives.cmd'
This file looks like:

allocate channel for maintenance type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
quit;


Please note that I am not able to give any support to anyone who wants to use this script.
You are allowed to copy and use it, but for the remainder you're on your own.


Success!

Tuesday, January 11, 2011

Installing Oracle Agent11g

In this thread the installation of Oracle's Management Agent 11g for Grid Control 11g is explained.

The agent installation is not a manual install anymore. Oracle has decided to only provide in silent installs.
The available options are:

- Push install from the OMS server ( requires ssh to be configured on the client )
- Pull install from the client ( requires wget to be installed )
- Silent install from an Agent download.

Unfortunately the first two options are not suitable for AIX servers. With a push install from the OMS server, the push installer tries to start a ssh-session on the server to be installed, using the ssh binary in a Linux style path ( /usr/local/bin/ssh ).

The second installation type is also unfortunately more Linux minded. Wget, the Linux source download tool, is by default not available on AIX.

The last method is the easiest, because no extra software ( ssh, wget) needs to be installed or configured.
You can download the Agent11g installer from:


After downloading, FTP the agent 11g install file to the client to be installed.
Unzip it into a temporary directory.


Create an Installation response file, with the contents like below. A sample configuration file can be found in /aix/response.
The to be changed values are marked in bold-white.

####################################################################
## copyright (c) 1999, 2010 Oracle. All rights reserved. ##
## ##
## Specify values for the variables listed below to customize ##
## your installation. ##
## ##
## Each variable is associated with a comment. The comment ##
## identifies the variable type. ##
## ##
## Please specify the values in the following format: ##
## ##
## Type Example ##
## String "Sample Value" ##
## Boolean True or False ##
## Number 1000 ##
## StringList {"String value 1","String Value 2"} ##
## ##
## The values that are given as need to be ##
## specified for a silent installation to be successful. ##
## ##
## ##
## This response file is generated by Oracle Software ##
## Packager. ##
###################################################################

RESPONSEFILE_VERSION=2.2.1.0.0

#################################################################################
#Inputs for Oracle Configuration Manager
#################################################################################
#-------------------------------------------------------------------------------
#SECURITY_UPDATES_VIA_MYORACLESUPPORT: Whether security updates are reqi-
# uired via My Oracle Support
#DECLINE_SECURITY_UPDATES: Whether security updates should be declined.
#MYORACLESUPPORT_USERNAME & MYORACLESUPPORT_PASSWORD: User name and passw-
# ord for My Oracle Support access, these will be effected only when SECU-
# RITY_UPDATES_VIA_MYORACLESUPPORT=true
#COLLECTOR_SUPPORTHUB_URL: If there is not direct connection and Support
# hub is deployed. Need to specify that URL.
#-------------------------------------------------------------------------------
SECURITY_UPDATES_VIA_MYORACLESUPPORT=False
DECLINE_SECURITY_UPDATES=True

################################################################################
#PARAMETERS FOR SOFTWARE UPDATES
################################################################################
#-------------------------------------------------------------------------------
#INSTALL_UPDATES_SELECTION:
# Option 1. If you want to skip the software updates, provide
# INSTALL_UPDATES_SELECTION="skip"
# Option 2. If you have already downloaded the updates then provide
# INSTALL_UPDATES_SELECTION="staged"
# If you choose the Option 2 then make sure you also provide STAGE_LOCATION.
#STAGE_LOCATION: Stage location for software updates. It will be effected
# only when INSTALL_UPDATES_SELECTION is set to "staged"
#-------------------------------------------------------------------------------
INSTALL_UPDATES_SELECTION="skip"
STAGE_LOCATION=

################################################################################
#PROXY DETAILS FOR SECURITY UPDATES/ SOFTWARE UPDATES
################################################################################
#-------------------------------------------------------------------------------
#PROXY_USER: User name for proxy access.
#PROXY_PWD: Password for proxy access.
#PROXY_HOST: Server providing proxy access.
#PROXY_PORT: Port for proxy access.
#------------------------------------------------------------------------------
PROXY_USER=
PROXY_PWD=
PROXY_HOST=
PROXY_PORT=

################################################################################
#Various inputs required for Installation and Configuration
################################################################################
#-------------------------------------------------------------------------------
#ORACLE_AGENT_HOME_LOCATION: The location of oracle agent home.
#Agent home "agent11g" would be created under the directory specified by
#ORACLE_AGENT_HOME_LOCATION.
#-------------------------------------------------------------------------------
ORACLE_AGENT_HOME_LOCATION=/opt/wcst01/ora/

#-------------------------------------------------------------------------------
#DEINSTALL_LIST: List of components to be deinstalled during a
# deinstall session.The following choices are available. The value should
# contain only one of these choices.The choices are of the form
# Internal Name, Version : External name. Please use the internal name and
# version while specifying the value.
# oracle.sysman.top.agent, 10.2.0.0.0 : Oracle Management Agent 10.2.0.0.0
# oracle.sysman.top.em_seed, 10.2.0.0.0 : Oracle Enterprise Manager Repository Database 10.2.0.0.0
# oracle.sysman.top.oms, 10.2.0.0.0 : Oracle Enterprise Manager Grid Console 10.2.0.0.0
#-------------------------------------------------------------------------------
DEINSTALL_LIST={"oracle.sysman.top.agent","11.1.0.1.0"}

#-------------------------------------------------------------------------------
#b_silentInstall: The user should not change this value
#CLUSTER_NODES: This variable represents the cluster node names
# selected by the user for installation.
#-------------------------------------------------------------------------------
b_silentInstall=true
CLUSTER_NODES=vtl1h121ps,vtl1h123ps

#-------------------------------------------------------------------------------
#TOPLEVEL_COMPONENT: The top level component to be installed in the
# current session.Value should contain only one of the following available
# choices.The choices are of the form Internal Name, Version : External name.
# Please use the internal name and version while specifying the value.
# oracle.sysman.top.agent, 10.3.0.0.0 : Oracle Management Agent 10.3.0.0.0
# Example: TOPLEVEL_COMPONENT = {"oracle.sysman.top.agent","10.3.0.0.0"}
#-------------------------------------------------------------------------------
TOPLEVEL_COMPONENT={"oracle.sysman.top.agent","11.1.0.1.0"}

#-------------------------------------------------------------------------------
#SELECTED_LANGUAGES: Languages in which the components will be installed.
# The following choices are available. The value should contain only one of
# these choices.The choices are of the form Internal Name : External name.
# Please use the internal name while specifying the value.
# en, : English
# de, : German
# es, : Latin American Spanish
# es_MX, : Mexican Spanish
# es_ES, : Spanish
# fr, : French
# fr_CA, : Canadian French
# it, : Italian
# iw, : Hebrew
# ja, : Japanese
# ko, : Korean
# pt_BR, : Brazilian Portuguese
# zh_CN, : Simplified Chinese
# zh_TW, : Traditional Chinese
#Example: SELECTED_LANGUAGES = {"en"}
#-------------------------------------------------------------------------------
SELECTED_LANGUAGES={"en"}

#-------------------------------------------------------------------------------
#COMPONENT_LANGUAGES: Languages in which the components will be installed.
# The following choices are available. The value should contain only one of
# these choices.The choices are of the form Internal Name : External name.
# Please use the internal name while specifying the value.
# en, : English
# de, : German
# es, : Latin American Spanish
# es_MX, : Mexican Spanish
# es_ES, : Spanish
# fr, : French
# fr_CA, : Canadian French
# it, : Italian
# iw, : Hebrew
# ja, : Japanese
# ko, : Korean
# pt_BR, : Brazilian Portuguese
# zh_CN, : Simplified Chinese
# zh_TW, : Traditional Chinese
#Example: COMPONENT_LANGUAGES = {"en"}
#-------------------------------------------------------------------------------
COMPONENT_LANGUAGES={"en"}

#-------------------------------------------------------------------------------
#OMS_HOST: OMS host info required to connect to OMS
#OMS_PORT: OMS port info required to connect to OMS
#AGENT_REGISTRATION_PASSWORD: Agent Registration Password needed to
# establish a secure connection to the OMS.
#-------------------------------------------------------------------------------
OMS_HOST=[Your FQN OMS server]
OMS_PORT=1159
#OMS_PORT=4889
AGENT_REGISTRATION_PASSWORD=[Agent registration password entered during OMS installation]

#-------------------------------------------------------------------------------
#s_agentSrvcName: Sets the agent Service Name and this variable can be
# used to overrite the agent service name calculated by the install. This is
# required for only Windows.
# Example:
# s_agentSrvcName = "Oracleagent11gAgent" ; default value
# s_agentSrvcName = "GridAgent" ; User specified value
#-------------------------------------------------------------------------------
#s_agentSrvcName=""

####################################################################################
#Please Don't change the values of these variables
####################################################################################
#-------------------------------------------------------------------------------
#FROM_LOCATION: Complete path to the products.xml.
#b_upgrade: "whether it is Upgrade or not"
#EM_INSTALL_TYPE: install type
#-------------------------------------------------------------------------------
FROM_LOCATION="[Absolute path to products.xml]"
b_upgrade=false
EM_INSTALL_TYPE="AGENT"


Note that the above response file is from an installation where the to be installed server was a two-node RAC. When provided with the cluster names, the installer is able to install all nodes in one time.
When installing a RAC cluster, these options are mandatory. When not, comment out the option "CLUSTER_NODES"

Also note that in the above example there are two entries for OMS_PORT. One is commented out. Use port 1159 for SSL communication between the agent and the OMS server. The agent is in that case automatically secured. Use port 4889 for non-SSL communication.
The to be used ports here are the defaults. During the OMS installation, the port list of the OMS server is shown. Make sure you use the ports that match your OMS server.

The last entry that needs some attention is the one at the bottom of the file. However in the example a relative path has been given, the installer needs an absolute path instead.



When ready, start the installer using the command:

cp aix/response/additionalAgent.rsp aix/agent
cd aix/agent
./runInstaller [-CLUSTER_NAME=] -silent -responseFile -noconfig


The is the name of the RAC-cluster.
This name can be obtained by looking into an OCR-dump file and look for the entry:

[SYSTEM.css.clustername]
ORATEXT :

How to generate an OCR dump file can be found here .

When the installation finishes, run the given 'root.sh' script.


After that the agents need to be configured on each host ( in a cluster installation do this host by host )
Go to the AGENT_HOME/bin directory and execute:

./agentca -f -n -c -i /etc/oraInst.loc

When installing a non-RAC system, the -n and -c options can be left out.

This finishes the installation of the Oracle Agent 11g on AIX.
It might be necessary to 'upload' the first configuration manually, before the agent will appear in the OMS console. Use the following command to do so:

./emctl upload agent ( for a RAC do this on each node )





Service XDB is down alert in Grid Control

Problem:
GridControl is reporting the XDB service as being down for RAC database.
In this thread it is investigated why this alert is thrown. We are using a RAC database called RACTST to show all settings and SQL queries.
It must be said that we haven't seen this alert being thrown for single-server databases. The Metric isn't even available in Grid Control there.


Background:
When creating a database with 'dbca' one can choose whether to use shared server setup. Even when shared server is disabled during database creation, afterwards the parameter 'dispatchers' is still set.
For anyone who is unfamiliar with the shared server term, consult the Oracle Database Administrators Guide at: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/manproc003.htm#i1010000


What do we see:
The default value for the dispatchers parameter looks like:
dispatchers='(PROTOCOL=TCP) (SERVICE=RACTSTXDB)'

In this parameter the service 'RACTSTXDB' is configured. This service then is automatically made available. The service availability can be checked by querying the gv$services table:

SQL> select inst_id,name from gv$services where name='RACTSTXDB';
INST_ID NAME
---------- ----------------------------------------------------------------
1 RACTSTXDB
2 RACTSTXDB

Because this is a 2-node RAC it is clearly shown that the service is available on both nodes.


When does the alert occur ?
Most DBA's disable the 'dispatchers' parameter whenever they choose to not use shared servers. Disabling the dispatcher is easily done by resetting the parameter from the spfile:

SQL> alter system reset dispatchers scope=spfile sid='*';

System altered.

It is also possible to turn of the dispatchers dynamically afterwards, by per using:

SQL> alter system reset dispatchers scope=memory sid='';

One can also restart the instance(s) to turn of the parameter after the spfile change.


Now wait for approx. 5 minutes, and voila, there is the alert:






Clicking on the alert shows a bit more detail, about when the alert occured:












A check in gv$services confirms the service being 'down' ( in fact it is gone):

SQL> select inst_id,name from gv$services where name='RACTSTXDB';

no rows selected.


The service is however still available in the database. A select from dba_services, still shows is as being known:

SQL> select name from dba_services where name='RACTSTXDB';
NAME
----------------------------------------------------------------
RACTSTXDB
1 row selected.


Solution:
Disabling the alert in GridControl would be a permanent "solution".
But that's more like a non-DBA workaround. You wouldn't be warned about any other real service being down.

There are three solutions to get rid of the alert:
- Either re-enable the dispatcher, or
- Remove the service from the database, or
- Learn to live with the alert being there.

The first is easy. Put back the 'dispatchers' parameter in the spfile with 'scope=both', and you're done. But stubborn as we are, DBA's never give up.

Well the second step, as said in the 'Problem' section, the alert is only thrown for RAC databases. For single instances, the Metric isn't even available in Grid Control.
In the RAC-FAQ on Oracle Support it says:

When I look at ALL_SERVICES view in my database I see services I did not create, what are they for?
You will always see a default database service that is the same name as your database. This service is available on all instances in the cluster. You will also see two services used by the database SYS$BACKGROUND (for background processes) and SYS$USERS (users who connect via BEQ or without using a service_name). You may also see services that end with XDB which are created for the XML DB feature and you will not be able to manage these services.


The meaning of the last phrase is that this service is not manageable with srvctl. It is unknown as a cluster resource. Is also says that belongs to the XML DB feature, so before proceeding with this thread consult MOS Note 416312.1 and the Oracle Forums thread: http://forums.oracle.com/forums/thread.jspa?threadID=410714&tstart=0


If you're sure you're not using XML-Db features, then you are OK to drop the XDB service.
Use the following command to do so:

SQL> exec dbms_service.delete_service('RACTSTXDB');

PL/SQL procedure successfully completed.

This will drop the service. When done the alert in Grid Control will subsequently be cleared.
And ofcourse as a real DBA we first test this on our TEST systems, don't we ??


The last option 'Learn to live with it' is valid for those users that do use the XML-Db feature and are not able to drop the service. For them I would almost recommend to follow solution 1. It is easier to cope with a running but unused dispatcher, then with an Grid Control alert that sits in your face every day.



Disclaimer:
I nowhere found a reason why we should not be able to drop this service if we are not using XML-Db features. Therefore the real impact is not completely known. If anyone can elaborate about this subject, you're most welcome to do so.
And eh... if you mess up your production system by following the actions in this thread, start blaming yourself, not me. All above is done on a TEST server.