SA-DBA

1

July 14, 2009  1:52 PM

TWO_TASK… an oldie but a goodie…

Richard Evans Richard Evans Profile: Richard Evans

TWO_TASK is a very useful but often forgotten environment variable. We actually just used it again the other day and we’re always scratching our head saying “ok, I remember there is this variable we can set to do that… umm… it’s… hmm, let me google and I’ll get right back to you.”

Because it’s an obscure name — maybe someone could explain to me why it’s named TWO_TASK? — it’s often hard to locate.

From the same document I referenced in my last post, the SQL*Plus User’s Guide and Reference, TWO_TASK is:

UNIX environment variable to specify a connection string. Connections that do not specify a database will connect to the database specified in TWO_TASK.

Example

TWO_TASK=MYDB
export TWO_TASK
sqlplus hr
is the same as:

sqlplus hr@MYDB

We use this a lot when we separate an application from the DB. We try to keep applications on their own servers (in their own zones) and our DBs on their own servers (in their own zones as well). Often times, by default, vendor software doesn’t want to play nice when it isn’t on the same machine as the DB. TWO_TASK is often a clever way to get around that!

In this scenario, I’m logged on to a Solaris Zone with Oracle 10g Client software installed.

[oracle: /oracle] echo $TWO_TASK

[oracle: /oracle] sqlplus system

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 14 08:48:16 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:
ERROR:
ORA-12545: Connect failed because target host or object does not exist

Enter user-name: ^C
[oracle: /oracle] export TWO_TASK=DDSC1
[oracle: /oracle] sqlplus system

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 14 08:48:27 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Now my application can be tricked into thinking the database is local. You don’t need to try to muck with any “@DBNAME” connection string.

July 14, 2009  1:40 PM

Environmental consistency .. is that even a real phrase?

Richard Evans Richard Evans Profile: Richard Evans

Whether it is or isn’t doesn’t really matter… what I mean by environmental consistency is basically just making your life easier by installing, configuring, testing, developing, etc, the same way. This is one of the reasons why I love Response Files in Oracle. Another thing we do pretty well around here is configuring our environment variables the same.

I have a .kshrc and a .profile that I copy to every Oracle DB Zone and the only parameter I edit is the ORACLE_SID. I’m sure there is still room for improvement here — off the topic of my head, I guess I could grep the /var/opt/oracle/oratab to find the SID and put that in for ORACLE_SID — but here is the way we set things up.

[oracle: /oracle] cat .profile
stty istrip
stty erase

# Oracle Variables...
ORACLE_BASE=/oracle
ORACLE_SID=CTM1
ORACLE_HOME=/oracle/${ORACLE_SID}/102_64
NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS'
NLS_LANG=american_america.US7ASCII
SQLPATH=/group/oracle/sql
PATH=/group/oracle/sh:$ORACLE_HOME/bin:/usr/bin:/usr/sbin:/etc:/usr/ccs/bin:/usr/ucb:/etc:$ORACLE_BASE/$ORACLE_SID/admin/scripts/sh:/usr/openwin/bin:$ORACLE_HOME/OPatch:/usr/local/bin:.

export ORACLE_BASE ORACLE_SID ORACLE_HOME SQLPATH NLS_LANG NLS_DATE_FORMAT PATH

# tell us what zone we're in when we login.
ZN=`uname -n`
echo "Zone name is ${ZN}"

# Display user, SID, and current working directory as the command prompt.
export PS1='[$LOGNAME: $PWD] '
. ./.kshrc

And we do the same thing with our .kshrc. We try to references environment variables whenever possible. The less we hardcode, the less we have to change when we setup a new machine!

[oracle: /oracle] cat .kshrc
export EDITOR=vi
alias l='ls -ltr'
alias bdump='cd /oracle/$ORACLE_SID/oratrace/bdump'
alias arch='cd /oracle/$ORACLE_SID/oraarch'
alias udump='cd /oracle/$ORACLE_SID/oratrace/udump'
alias scrp='cd /group/oracle/sh'
alias sql='cd /group/oracle/sql'
alias par='cd $HOME/scripts/parms'
alias out='cd /RMAN/scripts/logs/$ORACLE_SID'
alias dbs='cd $ORACLE_HOME/dbs'
alias net='cd $ORACLE_HOME/network/admin'
alias alert='vi /oracle/$ORACLE_SID/oratrace/bdump/alert_$ORACLE_SID.log'
alias oraenv='. $ORACLE_HOME/bin/oraenv'
alias ohome='cd $ORACLE_HOME'
alias tail_alert='tail -f /oracle/$ORACLE_SID/oratrace/bdump/alert_${ORACLE_SID}.log'
alias alert500='tail -500 /oracle/$ORACLE_SID/oratrace/bdump/alert_${ORACLE_SID}.log | more'
alias rdbms='cd $ORACLE_HOME/rdbms/admin'
alias sw='cd /group/software/oracle/stage/102_64'

My favorite part out of this whole mess is the SQLPATH variable. This is a great variable that lets you execute SQL scripts from a different location by simply using “@” as if it were in your current working directory.

According to the SQL*Plus User’s Guide and Reference, SQLPATH is:

Environment variable or Windows registry entry to specify the location of SQL scripts. SQL*Plus searches for SQL scripts, including login.sql, in the current directory and then in the directories specified by SQLPATH, and in the subdirectories of SQLPATH directories. SQLPATH is a colon separated list of directories. There is no default value set in UNIX installations.

In Windows, SQLPATH is defined in a registry entry during installation. For more information about the SQLPATH registry entry, see SQLPATH Registry Entry.

What I do is put my most-commonly used scripts into the directory defined in SQLPATH and then I can call it from anywhere. Obviously this has to be a directory that is accessible across zones (servers) :-)

Keep in mind Oracle will FIRST look at your current working directory and then query the SQLPATH. So if you have a script by the same name in each directory you’ll drive yourself nuts when it isn’t working as you’d expect! I speak from experience. ;-)

So here is an example of how I check current sessions:

[oracle: /oracle] ls -al get_session.sql
get_session.sql: No such file or directory
[oracle: /oracle] sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 14 08:18:15 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> @get_session
Sessions on database AD1
PID SID SER# BOX USERNAME OS_USER PROGRAM SQL_ADDRESS SQL_HASH_VALUE
-------- -------- -------- ------------- --------------- ------------ ----------------------------------- ---------------- --------------
12140 196 5604 oraad1 SYSMAN oracle emagent@oraad1 (TNS V1-V3) 00 0
12261 182 12004 omsapp DBSNMP OMS 00 0
12726 424 9074 oraad1 DBSNMP oracle emagent@oraad1 (TNS V1-V3) 00 0
13101 431 54 oraad1 DBSNMP oracle emagent@oraad1 (TNS V1-V3) 00 0
16456 396 29816 oraad1 SYS oracle rman@oraad1 (TNS V1-V3) 00 0

I’m sorry, the output isn’t quite as pretty as I’d like but I think it conveys my point. Using a couple quick variables and commands I can maintain a consistent environment.

The get_session.sql script is something I’ve “acquired” from years of DBA’ing via Google ;-)

Here it is:

SQL> l
1 select substr(a.spid,1,9) pid
2 , substr(b.sid,1,5) sid
3 , substr(b.serial#,1,5) ser#
4 , substr(b.machine,1,9) box
5 , substr(b.username,1,10) username
6 , substr(b.osuser,1,9) os_user
7 , substr(b.program,1,30) program
8 , b.sql_address sql_address
9 , b.sql_hash_value sql_hash_value
10 from v$session b, v$process a --, v$sqlarea c
11 where b.paddr = a.addr
12 and type='USER'
13* order by spid

I do the same thing with a user.sql script:

[oracle: /oracle] ls -al user.sql
user.sql: No such file or directory
[oracle: /oracle] sqlplus / as sysdba
SQL> @user.sql
Enter value for 1:
old 3: where username like upper('%&1%')
new 3: where username like upper('%%')

USERNAME PROFILE ACCOUNT_STATUS CREATED
------------ -------------- ------------------ --------------------
DBSNMP DEFAULT OPEN 23-Oct-2005 08:47:09
PERFSTAT DEFAULT OPEN 26-Sep-2008 16:32:34
RMAN DEFAULT OPEN 23-Oct-2005 08:47:10
SYS DEFAULT OPEN 15-Dec-2003 09:43:18
SYSMAN DEFAULT OPEN 11-Feb-2009 10:37:41
SYSTEM DEFAULT OPEN 15-Dec-2003 09:43:18
ORACLE_OCM DEFAULT EXPIRED & LOCKED 01-Jul-2009 14:06:09
OUTLN DEFAULT EXPIRED & LOCKED 23-Oct-2005 08:47:09
TSMSYS DEFAULT EXPIRED & LOCKED 28-Jun-2008 00:52:53
WMSYS DEFAULT EXPIRED & LOCKED 23-Oct-2005 08:47:10

SQL> l
1 select username, profile, account_status, created
2 from dba_users
3* where username like upper('%&1%')

What I like about this script is if you do not enter a username then it’ll display all of the users. The results are as narrow or as broad as you want. I have more columns in this script, like password hash, but that’s not information I care to give out. I think you understand ;-)

I hope this gives you an idea of what you can do with such a simple variable. The more simple and cleaner you can make your environment the better off you’ll be.

What do you do to simplify your environment? Are there variables and aliases that you setup that I’ve missed? I’d like to hear about them!


July 10, 2009  7:17 PM

Grid Control OS Command Job and a problem…

Richard Evans Richard Evans Profile: Richard Evans

If you’re an avid user of Grid Control, like me, then you’ve probably fallen in love with the ability to run OS commands on multiple machines from GC.  It’s a great tool!

For example, I often want to know how much space my Archive Logs are taking up – easy!  Since we have a very uniform environment, I’m able to query the /oracle/$ORACLE_SID/oraarch mount point and get the results ASAP.

df -h | grep arch

Easy enough right? Here is the problem… as you get more advanced, Oracle has hardcoded the shell the Agent uses!! It DOES NOT take the shell of the process owner, in our case the owner is the OS user Oracle and his shell is /bin/ksh. 
 
This may not affect you on “df -h | grep arch” but it most certainly will affect you if you you get into special characters!  Let’s say you want to use Grid Control to see if all of your /var/opt/oracle/oratab files have the autostart parameter set to “Y.” This is a fair test, I think.

From the command line, it’s as simple as…


[oracle: /oracle] grep -v ^# /var/opt/oracle/oratab
 
agt:/oracle/OracleHomes/agent10g:Y

Let’s create an OS Command job that is exactly what we did from the command line…

Here is the output if you do the same thing in Grid Control… what gives??


 
With the help of Oracle support, we’ve determined this is an issue with Grid Control on Solaris SPARC, specifically. According to Support, it seems to work fine in Linux and Windows.  Their answer?

 
CAUSE DETERMINATION

====================

The interpreter is hardcoded a /bin/sh

On Solaris this is the Bourne Shell. On Linux this is the BASH Shell

CAUSE JUSTIFICATION

====================

Verified that the interpreter is /bin/sh and that the differences between Linux and Solaris

You can verify that a couple different ways… as you can see, the second item works as this shell requires double quotes to accept special characters. If you copy that code into an OS Command Job it’ll work just fine.

[oracle:<AD1> /oracle] echo $SHELL
/bin/ksh

[oracle:<AD1> /oracle] sh
[$LOGNAME:<$ORACLE_SID> $PWD] grep -v ^# /var/opt/oracle/oratab

> ^C
[$LOGNAME:<$ORACLE_SID> $PWD] grep -v "^#" /var/opt/oracle/oratab
agt:/oracle/OracleHomes/agent10g:Y
AD1:/oracle/AD1/102_64:Y

 

Or, I was able to create an OS Command Job (to run “ps -ef | grep rman”) and quickly run the same command “ps -ef | grep rman” on the host I told it to execute against to see if it’s executing.


[oracle:<AD1> /oracle> ps -ef  | grep rman
oracle 8167 8163 0 11:05:52 ? 0:00 grep rman
oracle 8163 8161 0 11:05:52 ? 0:00 /bin/sh -c ps -ef | grep rman

 

You can see how the Agent is executing the command.

 Lucky for me, I was executing something simple and harmless, but think if I was trying to edit a configuration file across my environment? When I found this error, I was doing just that! Fortunately, it was just my crontabs and I could easily (relatively) go back and fix them. I was very very very lucky it wasn’t something more serious.

 I’ve been working with a great Oracle Support Rep, Peter, but unfortunately he gave me some bad news on Wednesday regarding this SR:

Hi Rich,

The enhancement request has been rejected. It was suggested that script version of host command be used instead and specify which shell interpreter you want to use in the “Interpreter” field.

Don’t think there is anything more I can do on this.

You OK for me to close this SR now? 

But I don’t go down without a fight. I asked for an escalation of this issue :-) 

I think this is a very important issue and I think Oracle development needs to recognize it and FIX IT. It’s easy to put a note in the Solaris SPARC README saying “oh, remember that this OS uses the Bourne Shell while Linux defaults to the BASH Shell.” But that would be a horrible remedy, in my humble opinion. That’s a band-aid, not a solution. This is a problem that needs a solution.


July 10, 2009  4:45 PM

A win for us and the customers…

Richard Evans Richard Evans Profile: Richard Evans

Today we demonstrated the capabilities of Oracle Data Guard. I’ve implemented it at a couple other locations and absolutely love it. I’d still consider myself a DG newbie, but it’s so straight forward and easy to manage at this point (10gR2) you don’t have to be an expert. I’ve read that it gets even better with 11g! I can’t wait for some of the new features they’ve integrated into RMAN for Data Guard.

Even if you’re not using RAC, check out page 33 of the Data Guard 11g Installation and Configuration On Oracle RAC Systems

And Uwe Hesse, an Oracle Instructor, has put together a nice document demonstrating 11g’s Data Guard Features: Oracle 11g Data Guard in Action

Back on topic now, we have Business Analysts that want to run reports against the data from our production server. Since we don’t really want them bogging down the production server, we’ve been using a Korn shell script to copy the Level 0 of the production DB from NetBackup to a reporting Zone. This has worked pretty well for a couple years now, but when I came on board I asked why we hadn’t considered Data Guard. Mostly it was a lack of experience with the product and when you’re a small team with a large workload, it’s hard to tackle products that are out of arms reach. I completely understand that, been there! Still am there!!

So I put together a scenario using one of their development DBs. I had permission to start/stop that DB as necessary and used it as my primary DB. I duplicated this DB to a separate Solaris Zone to, eventually, be my logical standby database.

I’ll post the steps I took on here sometime soon — first I want to make sure I have the bumps smoothed over.

The big thing was our demonstration today. I put together a few (~7) PowerPoint slides to give them an idea of what I want to do:

  1. What is Data Guard?
  2. How does Data Guard work?
  3. Why use Data Guard instead?
  4. What’s the catch?
  5. How would we implement?
  6. How long will this take??

Generally our customers don’t care about the details, they just want to know what the catch is, how long it’ll take to implement, and how much downtime they’ll experience. Since I’m a technical guy, I like throwing in some technical information to see exactly how long it takes for their eyes to glaze over! :-D

If you aren’t or haven’t looked at creating a Logical Database for reporting purposes you really might check it out. You can tell DG to duplicate only specific schemas, to have a lag-time, and you can create users that only exist on the reporting DB as well as index the **** out of it so their queries perform better. Yes, I know indexes come with an overhead expense, but this is on our reporting DB specifically — not production — so I really don’t care if it takes a little extra space or a few extra CPU cycles to insert, update, or delete data from the indexes. The benefits outweigh the costs in such a scenario.

It’s always a great feeling when you can alleviate some of the monotonous administrative tasks — i.e. weekly duplications on a Friday afternoon when you really want to be at the bar instead. This way, their data is always current; now they won’t complain that we duplicated it two days before the month ended and they really needed the whole month’s data available! They will have near-real time data at their finger tips.

The next thing is to get them out of fat client reporting tools and to take a good hard look at Application Express ;-)


July 10, 2009  4:06 PM

Piggybacking on VI’s importance…

Richard Evans Richard Evans Profile: Richard Evans

As a guy who uses Solaris for 99% of his administration I’ve gone from fearing vi to loving and respecting it and seriously considering buying it a valentine’s day present every year. That’s probably best reserved for a different blog all together:-)

This is a good write-up on why VI is such a powerful too even today.
Why, oh WHY, do those #?@! nutheads use vi?

I’m by no means an expert. I have learned enough vi to make my daily life so much easier though. As a lazy computer guy, VI is your best friend!!

Some examples of how I use vi on a regular basis.. creating bulk insert statements, generating quick shell scripts to execute similar commands repeatedly, and other countless items…

Yesterday, I was playing with NLS_SORT and I wanted to generate a table with a lot of first & last names. This would take forever if I did it by hand. Thank god there is the Internet!! I downloaded a random .CSV with distinct first name and last name columns, removed the extranious data, and then pasted that data into vi.


[oracle: /oracle] more insert_first_last_names.sql
Ralph Lauren
Piper Laurie
D.H. Lawrence
Martin Lawrence
Steve Lawrence
Thomas Lawrence
Vickie Lawrence
Bill Lee

A quick table creation…

create table rich_test (first_name varchar2(30), last_name varchar2(40));

And now comes the vi fun! Since I know I have only first and last names at this point, I can assume the space between them delineates first from last.

## This guy will take the space and replace it with tick comma tick…
:%s/ /','/g

Ralph','Lauren
Piper','Laurie
D.H.','Lawrence
Martin','Lawrence
Steve','Lawrence
Thomas','Lawrence
Vickie','Lawrence
Bill','Lee

Now let’s get the insert statement taken care of…

## make sure you don’t forget the tick at the beginning of the first name!

:%s/^/insert into rich_test values('/g

insert into rich_test values('Ralph','Lauren
insert into rich_test values('Piper','Laurie
insert into rich_test values('D.H.','Lawrence
insert into rich_test values('Martin','Lawrence
insert into rich_test values('Steve','Lawrence
insert into rich_test values('Thomas','Lawrence
insert into rich_test values('Vickie','Lawrence
insert into rich_test values('Bill','Lee

Last, we can finish it off by closing out the last_name tick, the parenthesis and do NOT forget the semicolon! Otherwise you’ll be left with a bunch of insert statements that are waiting for you to send them!

:%s/$/');/g

insert into rich_test values('Ralph','Lauren');
insert into rich_test values('Piper','Laurie');
insert into rich_test values('D.H.','Lawrence');
insert into rich_test values('Martin','Lawrence');
insert into rich_test values('Steve','Lawrence');
insert into rich_test values('Thomas','Lawrence');
insert into rich_test values('Vickie','Lawrence');
insert into rich_test values('Bill','Lee');

Now it’s as simple as running our SQL statement and then committing the data!

SQL> @"insert_first_last_names.sql"

1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

I’ve only given you an example with a few entries, but this is beautiful if you wanted to generate a table with a few thousand, tens of thousands, or hundreds of thousands of rows. It’s really just that quick and easy.

Until you’ve memorized the commands, you should keep a cheat sheet close at hand. This one looks pretty good! vi Editor “Cheat Sheet”

And one final note, keep your hands on the home keys. The sooner you learn to use h, j,k, & l to navigate the better off you’ll be.


July 10, 2009  12:04 AM

Grid Control Job to Excel Worksheet

Richard Evans Richard Evans Profile: Richard Evans

Often times I’m asked to run a report that requires querying multiple databases. I suppose I could just setup DB Links and use a UNION ALL but why, when I have Grid Control? :-)

First thing I do is setup a SQL Script job in Grid Control that looks like this. In addition to the user information, this query will get the DB Name so I know the user-DB relationship.

WHENEVER SQLERROR EXIT FAILURE;
set head off
set lines 210
column output format a140
select (select name from v$database)||’,’||username||’,’||account_status||’,’||created||’,’||profile as output from dba_users;

Make sure you run this job as SYSDBA so you have the correct privileges to query dba_users

Now that you have the results you can query them from SYSMAN’s table…

select output from MGMT$JOB_STEP_HISTORY where job_name = ‘TEST111′;

Paste that output into Excel and you’ll end up with something like this:

Now use the Text to Columns function in Excel to migrate the data to individual columns.

The final output!

This is a great way to produce quick reports that span multiple databases.


July 9, 2009  9:21 PM

Preventing MS Word from formatting your code…

Richard Evans Richard Evans Profile: Richard Evans

This is probably a problem that was solved years ago but I figured it out recently. Hopefully it helps someone!

The Problem

When I’m in Word and I’m creating or modifying documentation that involves code, Word wants to automatically replace certain characters with characters it thinks looks better.

Often times, I don’t notice it did that until I go to use the code. Here you’ll see the “smart quotes” ( ` ) that Word automatically replaced my straight quote ( ‘ ) with.


SQL> select job_id, job_name, job_owner, job_status
from mgmt_job
where job_status != 0
and job_name like ‘%LEVEL%0%’
order by job_name

It doesn’t look like much of difference, but it shows up in SSH as a period ( . ) and then errors.


SQL> select job_id, job_name, job_owner, job_status
from mgmt_job
where job_status != 0
and job_name like .%LEVEL%0%.
order by job_name
/
and job_name like .%LEVEL%0%.
*
ERROR at line 4:
ORA-00936: missing expression

The Fix

When you set options on the AutoFormat As You Type tab (Tools menu, AutoCorrect Options command), Word can automatically format text as you’re typing your document. For example, if you type a number followed by a period or hyphen, followed by a space or tab, followed by text, Word makes the text a numbered list.

When Word applies automatic formatting, you can use the AutoCorrect Options button to undo the action or change AutoFormat settings. Depending on the situation, the entire button may appear or the button may first appear as a small, blue box that changes to a button icon when you point to it.

I unchecked the first option, straight quotes vs. smart quotes as well as the hyphens and the bold/italic options.

 

 

If the automatic borders, tables or bullet lists annoy you there are options there to fix them as well ;-)


July 9, 2009  8:47 PM

Inaccessible UNIX filenames…

Richard Evans Richard Evans Profile: Richard Evans

I goofed up earlier and somehow managed to give my file a name. To fix this I was able to rename it by using ctl+v followed by ctl+h to generate the ^H character.

The Problem

When I saved it in vi I somehow messed up my :wq! And I it saved it with this name:


"^H" [New file] 167 lines, 3054 characters

You can see below that there is a file with no filename.


[oracle: /oracle/DB/102_64/network/admin] ls -al
total 38
-rw-r--r-- 1 oracle dba 3054 Jun 17 11:12
drwxr-x--- 3 oracle dba 1024 Jun 17 11:12 .
drwxr-x--- 13 oracle dba 1024 Feb 20 15:48 ..
-rw-r--r-- 1 oracle dba 695 Mar 5 14:32 listener.ora
drwxr-x--- 2 oracle dba 96 Feb 20 15:45 samples
-rw-r----- 1 oracle dba 172 Dec 26 2003 shrept.lst
-rw-r--r-- 1 oracle dba 172 May 27 13:57 sqlnet.ora
-rw-r--r-- 1 oracle dba 4069 Jun 17 11:11 tnsnames.ora
-rw-r----- 1 oracle dba 6759 May 1 11:16 tnsnames.ora.20090319

[oracle: /oracle/DB/102_64/network/admin] find ./ -ls
373 1 drwxr-x--- 3 oracle dba 1024 Jun 17 11:12 ./
374 0 drwxr-x--- 2 oracle dba 96 Feb 20 15:45 ./samples
2101 4 -rw-r----- 1 oracle dba 3867 Sep 9 1997 ./samples/listener.ora
4652 31 -rw-r----- 1 oracle dba 31023 Sep 9 2003 ./samples/sqlnet.ora
4653 3 -rw-r----- 1 oracle dba 2939 May 16 2000 ./samples/tnsnames.ora
10346 1 -rw-r----- 1 oracle dba 172 Dec 26 2003 ./shrept.lst
2002 1 -rw-r--r-- 1 oracle dba 695 Mar 5 14:32 ./listener.ora
2003 4 -rw-r--r-- 1 oracle dba 4069 Jun 17 11:11 ./tnsnames.ora
18456 7 -rw-r----- 1 oracle dba 6759 May 1 11:16 ./tnsnames.ora.20090319
28266 1 -rw-r--r-- 1 oracle dba 172 May 27 13:57 ./sqlnet.ora
28342 3 -rw-r--r-- 1 oracle dba 3054 Jun 17 11:12 ./

[oracle: /oracle/DB/102_64/network/admin] find ./ -type f -ls
2101 4 -rw-r----- 1 oracle dba 3867 Sep 9 1997 ./samples/listener.ora
4652 31 -rw-r----- 1 oracle dba 31023 Sep 9 2003 ./samples/sqlnet.ora
4653 3 -rw-r----- 1 oracle dba 2939 May 16 2000 ./samples/tnsnames.ora
10346 1 -rw-r----- 1 oracle dba 172 Dec 26 2003 ./shrept.lst
2002 1 -rw-r--r-- 1 oracle dba 695 Mar 5 14:32 ./listener.ora
2003 4 -rw-r--r-- 1 oracle dba 4069 Jun 17 11:11 ./tnsnames.ora
18456 7 -rw-r----- 1 oracle dba 6759 May 1 11:16 ./tnsnames.ora.20090319
28266 1 -rw-r--r-- 1 oracle dba 172 May 27 13:57 ./sqlnet.ora
28342 3 -rw-r--r-- 1 oracle dba 3054 Jun 17 11:12 ./

The Fix

By using the ctl+v followed by ctl+h I was able to reproduce the ^H character and get the file information.


[oracle: /oracle/DB/102_64/network/admin] ls -al ^H
-rw-r--r-- 1 oracle dba 3054 Jun 17 11:12

Now I move it to a file with a legit name…


[oracle: /oracle/DB/102_64/network/admin] mv ^H test

Now I can move it to something useful :-)


July 9, 2009  4:31 PM

Silent Install of Oracle EM Agent 10.2.0.4

Richard Evans Richard Evans Profile: Richard Evans

I install a lot of Oracle EM Agents… it’s time consuming and I never seem to do it quite the same way. So I decided to do what any lazy computer guy would do and spend a day configuring various response files so I don’t ever have to work again! Well, at least so I don’t have to manually install EM Agent 10.2.0.4 again :-0

I changed was the base directory and the OMSConnectInfo variables. I think that was it… double check the file though.

[oracle: /oracle] vi /group/software/oracle/stage/102_64/Agents/10.2.0.4/solaris/response/additional_agent.rsp

BASEDIR=/oracle/OracleHomes
sl_OMSConnectInfo={“omsapp”,”1159″}

cd /group/software/oracle/stage/102_64/Agents/10.2.0.4/solaris/agent

./runInstaller -silent -responseFile /group/software/oracle/stage/102_64/Agents/10.2.0.4/solaris/response/additional_agent.rsp -force

Next, what I like to do is setup the Agent environment so we can use it’s $ORACLE_HOME variables. I put the agent information in /var/opt/oracle/oratab so I can easily assume it’s environment:

echo “agt:/oracle/OracleHomes/agent10g:Y” >> /var/opt/oracle/oratab

[oracle: /oracle] grep agt /var/opt/oracle/oratab
agt:/oracle/OracleHomes/agent10g:Y

[oracle: /oracle] oraenv
ORACLE_SID = [DDSC1] ? agt

Check if the agent is running, if so, shut it down!

[oracle: /oracle/OracleHomes/agent10g] ps -ef | grep ora
oracle 19566 17696 0 16:26:42 pts/8 0:00 /oracle/OracleHomes/agent10g/perl/bin/perl /oracle/OracleHomes/agent10g/bin/emw
oracle 20102 12101 0 16:27:39 pts/8 0:00 ps -ef
oracle 7585 7207 0 16:10:39 pts/1 0:00 -ksh
oracle 20103 12101 0 16:27:39 pts/8 0:00 grep ora
oracle 19574 19566 0 16:26:43 pts/8 0:03 /oracle/OracleHomes/agent10g/bin/emagent
root 18306 17696 0 14:39:11 ? 0:00 /usr/lib/dmi/snmpXdmid -s orastc1
oracle 12101 12088 0 16:16:15 pts/8 0:00 -ksh
oracle 15290 17696 0 16:19:18 ? 0:00 /oracle/STC1/102_64/bin/tnslsnr LISTENER -inherit

[oracle: /oracle/OracleHomes/agent10g] emctl stop agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.

To secure an Agent when the OMS has already been secured. Reference Note 283091.1 on Metalink.

vi /oracle/OracleHomes/agent10g/sysman/config/emd.properties

Make sure you change the following lines to read https not http.

EMD_URL=https://oradtc1:3872/emd/main/
REPOSITORY_URL=https://omsapp:1159/em/upload/
emdWalletSrcUrl=https://omsapp:1159/em/wallets/emd[/code]

Now secure the agent!!

[oracle: /oracle] emctl secure agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Enter Agent Registration password :
Agent is already stopped... Done.
Securing agent... Started.
Requesting an HTTPS Upload URL from the OMS... Done.
Requesting an Oracle Wallet and Agent Key from the OMS... Done.
Check if HTTPS Upload URL is accessible from the agent... Done.
Configuring Agent for HTTPS in CENTRAL_AGENT mode... Done.
EMD_URL set in /oracle/OracleHomes/agent10g/sysman/config/emd.properties
publicKeyFile = /oracle/OracleHomes/agent10g/sysman/config/nmosudo.props
Generating RSA keys.

Saving private key in wallet.
SaveKeyWallet: Cannot open wallet (error=28759): file:/oracle/OracleHomes/agent10g/sysman/config
SaveKeyWallet: Re-creating wallet.

Reading private key from wallet.

Saving public key into file.

Reading public key from file.

Encrypting clear-text: gensudoprops pass.

Decrypting encrypted-text.
Decrypted Text: gensudoprops pass
Securing agent... Successful.

Fire the bad boy back up!

[oracle: /oracle] emctl start agent

Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ...... started.

If this happens...

[oracle: /oracle] emctl start agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Starting agent ..... failed.
Failed to start HTTP listener.
Consult the log files in: /oracle/OracleHomes/agent10g/sysman/log

Re-run the secure command...

[oracle: /oracle/OracleHomes/agent10g/sysman/config] emctl secure agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
Enter Agent Registration password :
Agent is already stopped... Done.
Securing agent... Started.
Requesting an HTTPS Upload URL from the OMS... Done.
Requesting an Oracle Wallet and Agent Key from the OMS... Done.
Check if HTTPS Upload URL is accessible from the agent... Done.
Configuring Agent for HTTPS in CENTRAL_AGENT mode... Done.
EMD_URL set in /oracle/OracleHomes/agent10g/sysman/config/emd.properties
publicKeyFile = /oracle/OracleHomes/agent10g/sysman/config/nmosudo.props
Generating RSA keys.

Saving private key in wallet.

Reading private key from wallet.

Saving public key into file.

Reading public key from file.

Encrypting clear-text: gensudoprops pass.

Decrypting encrypted-text.
Decrypted Text: gensudoprops pass
Securing agent... Successful.


July 9, 2009  12:00 AM

select user from dual…

ITKE ITKE Profile: ITKE


SQL> select user from dual;

USER
------------------------------
Rich

:-)

 
Hi, My name is Rich and I’ve started yet another Oracle blog for you to keep track of (use Google Reader, it rocks!). Will this one be better than the rest? Different from them? How about at least above average? Probably not. But this a good avenue for me to share my experiences and stories as an Oracle DBA.

As a DBA, I’ve used and abused so many forums, blogs, and websites over the years it should be criminal. I feel that it’s now my turn to be the abusee (is that even a word?). As I learn new things I’ll try to post them here so others have the ability to learn from my mistakes and/or accomplishments.

Use whatever cliche you want: don’t reinvent the wheel, work smarter not harder, etc, etc. No matter what you call it, it’s a practice I try to follow :-)

I’ve always said the best computer guys are the laziest computer guys. How many times have you tried to find a way to automate even the simplest of processes? If you can’t count that high you’re probably pretty good at what you do! :-)

If you have any questions for me, about my posts, or on items you’d like to see on here please let me know.


1

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: