Reset/Change the ias_admin password

While upgrading the grid control host, I ran into a bug, when I say bug in this instance I mean a user problem, not the usual Oracle bug.

Apparently the ias_admin password had been changed and no one remembered to record the new password.

Oracle as usual makes life easy for us by not having any documented procedure for changing the password if forgotten.

There are two ways to change the password but no "official" way to change it if you forgot/lost the password.

After a huge amount of troubleshooting and research (15 minutes). I found this hack. The only problem is that it stores your password in clear text. Proceed at your peril:

Change ias_admin password directly in configuration file
–Backup $ORACLE_HOME/sysman/j2ee/config/jazn-data.xml
–Search for entry like below

  <user>
    <name>ias_admin</name>
      <credentials>{903}8QkQ/crno3lX0f3+67dj6WxW9KJMXaCu</credentials>
  </user>

change password like below 

  <user>
    <name>ias_admin</name>
      <credentials>Password</credentials>
  </user>

Temp tablespace recreation

For any number of reasons i.e. lack of space on ASM or disks, improperly sized segments, space will need to be reclaimed from the TEMP tablespace.

In my case it was due to a shortage of space, I looked around and there staring at me was over 30Gb of space, from whence I know not. Anyway in recent versions of Oracle (11G onwards), you have the ability to shrink the temp space, unfortunately I was running a version of 10g. So here we go:

Disclaimer: Try out all procedures on a test environment before "going production" with it.

Create temp2 tablespace

Create another temporary tablespace and make it the default tablespace using either sql prompt or grid control/enterprise manager

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘+DATA/test/tempfile/TEMP2 ′ SIZE 2000M, ‘‘+DATA/prod/tempfile/TEMP2 ′ SIZE 2000M'; 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Important: Kill inactive sessions on both nodes in RAC using the old temp tablespace:

Ensure there are no active sessions utilizing the previous temp tablespace:

SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

USERNAME                                                                                   SESSION_NUM SESSION_ADDR

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

tommy                                                                                            147 070000020CE379D8

tommy                                                                                             33 070000020FE41DF0

tommy                                                                                          55165 070000020FE1B150

SQL> SELECT SID, SERIAL#, STATUS, LOGON_TIME FROM V$SESSION WHERE SERIAL#=147;

       SID    SERIAL# STATUS                   LOGON_TIME

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

      1600        147 INACTIVE                 18-JUL-12

SQL> SELECT SID, SERIAL#, STATUS, LOGON_TIME FROM V$SESSION WHERE SERIAL#=33;

       SID    SERIAL# STATUS                   LOGON_TIME

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

      1587         33 INACTIVE                 18-JUL-12

SQL> SELECT SID, SERIAL#, STATUS, LOGON_TIME FROM V$SESSION WHERE SERIAL#=55165;

       SID    SERIAL# STATUS                   LOGON_TIME

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

      1527      55165 INACTIVE                 25-JUL-12

Kill the inactive sessions 

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

Drop temp tablespace

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Recreate Tablespace Temp

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE ‘+DATA/prod/tempfile TEMP.263.789587649′ SIZE 2000M’;

Make TEMP Tablespace default temporary tablespace

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Drop temporary tablespace temp2

At this point if you run a very busy database you might need to kill inactive sessions and wait till the active sessions on temp2 are done before dropping it.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Grid control agent 11G on 10G grid control

I have being busy keeping my nose to the grindstone with nothing interesting to blog about, that is until I came across this head- scratcher of a problem. For a little history: I have a very enthusiastic developer who's as willing as me to try new (relatively) technology.

In a bid to move along with the times, we are testing out SOA suite on an 11G database. Now the easy bit was installing an 11G database and pointing the new SOA at it and creating all the relevant schemas and tablespaces.

As a DBA who prefers working smart to hard, I decided to point the database at the grid control host. This is were it begins to get interesting, as you all know, there are various methods of installing the grid agent;

1. A push method from grid control. I ruled this out because I wanted an 11g agent. 
2. An installation using OUI. This is not supported in 11G 
3. A silent installation. Eureka, just what I wanted (not really), but I had no choice.

So I go along with the silent install:

./runInstaller -silent -responseFile /location of response file

For the less than observant this is after downloading the installables and unzipping the files(if you are as unlucky as me you will need to get the unzip utility from the IBM download page and install it. fun days).

Someone mentioned on some forum that Oracle enjoys making life difficult for people still running their RDBMSs on AIX. I am an example of this. Moving on...

So everything seems to be working (considering), until I hit some errors: 

WARN  http: nmehl_connect_internal: connect failed to (dbnew-test.3872): Connection refused (error = 79)

2012-07-04 13:32:01,083 Thread-1 ERROR main: nmectla_agentctl: Error connecting to https://dbnew-test:3872/emd/main/. Returning status code 1

Now at this point I did what any experienced DBA would do (hello Google). I got a few hits which after reviewing had nothing to do with my errors (or so i thought).

I ploughed ahead regardless. 

Trying to reconfigure, stop and start the agent, secure the agent, upload the agent:

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

./emctl start agent

./emctl stop agent

./emctl status agent

./emctl secure agent 

./emctl unsecure agent-----When I unsecured the agent the agent started but no connection to the repository.
./emctl upload agent

All along I had been barely reviewing one or two of the logs that were generated while I was going through this routine of mine over and over again. At some point I even re-installed the agent. No luck, same errors

I finally decided to take a closer look at the logs, and hey presto:

:Property 'agentTZRegion' is missing from /u022/location.

So while I was busy running mental, all I had to do was take a better look at my logs and the solution would have being staring me in the face.

So I:

./emctl config agent getTZ

realized the timezone was exactly what was in the emd.properties. 

./emctl resetTZ agent

On the grid/EM repository run the following(depending on your TZ settings)

SQL> exec mgmt_target.set_agent_tzrgn('':'','Etc/GMT+1')
SQL> commit

Now go on and multiply (start the agent).

This worked like a charm for me.

Good luck with the fix fellas. I'll be happy as always to answer any questions.

Changing an IP address SCAN listener and VIP in Oracle 11G

This is a quick and dirty way to modify the SCAN listener, Public IP and Virtual IP for an Oracle 11G

database

This change was carried out on Linux OS but is applicable to most *nix

As Grid owner

 . oraenv

/oifcfg getif 

crsctl stat res -t

srvctl stop instance -d testdb -i testdb1

srvctl stop instance -d testdb -i testdb2

* If CRS is on ASM disks shutdown CRS first as root on both nodes -- crsctl stop crs

srvctl stop asm -n plrac2 -f

crsctl stat res -t

srvctl disable database -d testdb

srvctl disable nodeapps

Modify /etc/hosts

ifdown eth0

modify ip address: vi /etc/sysconfig/network-scripts/ifcfg-eth0

ifup eth0

confirm changes: ifconfig -a

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

SCAN Listener:

AS grid owner

$ ./srvctl stop scan_listener

$ ./srvctl stop scan

$ ./srvctl config scan

$ ./srvctl remove scan_listener -f

$ exit

exit

#./srvctl remove scan -f

#./srvctl add scan -n plrac_cluster

#./srvctl config scan

#su oracle

$ ./srvctl add scan_listener -p 1521

$ ./srvctl config scan

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

As root

srvctl modify nodeapps -n plrac1 -A plrac1/255.255.255.0

srvctl modify nodeapps -n plrac2 -A plrac2/255.255.255.0

./srvctl config scan

verify the changes:

 srvctl config nodeapps -a

./srvctl config scan

as root:

srvctl enable nodeapps

srvctl enable database -d testdb

as grid owner:

srvctl start vip -n plrac1

srvctl start vip -n plrac2

srvctl start instance -d testdb -n plrac1

srvctl start instance -d testdb -n plrac2

Migrate Oracle 10.2.0.3 to Oracle 10.2.0.5

The following are the steps performed to migrate a database from 10.2.0.3 to 10.2.0.5

  

•      Download oracle installables (Software, 10.2.0.5 Patchset)
•      Install new oracle home. 
•      Migrate ASM instance and database to new Oracle home.
•      Upgrade database to 10.2.0.5

       *** Please note. ASM is started in a non-open state and consequently does not need to be upgraded after    moving the necessary files.

Download oracle installable files (Software, 10.2.0.5 Patchset)

Download the files to a location with enough space to contain the zipped files .

After the download the installables should be unzipped and renamed (It’s a personal thing, but I like to do this to know what the folders contain).

Install and upgrade new oracle home

The new oracle home can be installed following the usual steps using Oracle’s runInstaller utility after performing the pre-installation tasks.

Following the successful installation, the 10.2.0.5 patch was applied to the home using the runInstaller utility located in the patch folder and selecting the home to upgrade.

Migrate ASM and database to new Oracle home.

·         shutdown all database instances and afterwards ASM

·         stop listener(s)

·         change the entries in oratab

·         Since this is ASM the files(control and data) are located in a centralized location and do not need to be moved.

·         copy and modify init.ora for relevant databases and ASM

·         copy/move password files to new home

·         copy/move tnsnames and sqlnet.ora to new ORACLE_HOME location

·         startup ASM and migrated database to confirm that the change took effect.

·         Shutdown database

Upgrade Database to 10.2.0.5

Usually the Oracle Database Upgrade Assistant (DBUA) will be run from the new oracle home to upgrade the selected database. However sometimes  due to network issues, the upgrade can be run manually. See below:

1. Start the database in the UPGRADE mode:

SQL> STARTUP UPGRADE

2. Set the system to spool results to a log file for later analysis:

SQL> SPOOL upgrade_info.log

3. Run the Pre-Upgrade Information Tool:

SQL> @/rdbms/admin/utlu102i.sql

4. Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

5.       Enter the following SQL*Plus commands:

SQL> SPOOL patch.log

SQL> @?/rdbms/admin/catupgrd.sql

This was run in nohup mode i.e.

SQL> nohup sqlplus "/ as sysdba" @?/rdbms/admin/catupgrd.sql &

SQL> SPOOL OFF

6.       Restart the database:

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

7.       Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.

SQL> @?/rdbms/admin/utlrp.sql

8.       Run the following command to check the status of all the components after the upgrade:

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;