EM

Recovery from corrupted rollback segments

I had a hard day from yesterday as we had a hardisk crash in one of our servers.It was in a QA environment so the impact was not great.

No Backups for QA except weekly Export backup but it failed for last two weeks,so realistically no backup availble.Instance terminated leaving a message in alert.log saying that UNDO tablespace segments got corrupted.

Oracle 9i and 10g on a windows 2003 server.

The behaviour was different between 9i and 10g

First 9i

The lost hard disk had 9i and 10g on it.

I tried to start the DB but after mounting the undo datafile didnt come up with a messgae Corrupted block

I tried to offline drop and it worked and then i tried to drop the UNDO tablespace it didnt allow with Recurvis sql, and ora 600 error.

I managed to find out the rollback segments which needed recovery ,SMON was throwng errors in the Alert log saying the Rollback segament corrupted

Then i decided to go for UNSUPPORTED parameter in the INIT.ora

shutdown the database
edit the init and added the following
_OFFLINE_ROLLBACK_SEGEMNTS=(...)
UNDO_management=MANUAL
commented the UNDO_TABLESPACE in the init.ora

Started the database and opened it

now it tried to drop the UNDO tablespace and it worked
Created a new undo tablespace
Shutdwon the DB
Edit the init.ora again to comment the _OFFLINE_ROLLBACK_SEGEMNTS and set the UNDO_TABLESPACE value

Database opened and no more errors and did a full backup of the DB with Export.

The scenario was slightly is different in 10g
once i offline drop the undo tablespace datafile i opened the database and created and new undoablespace--This i couldnt so it in 9i
Now found the ROLLABCKSEGEMNT and edited the init.ora _OFFLINE_ROLLBACK_SEGEMENTS and changed the UNDO_TABLESPACE to the new UNDO

Opened the DB dropped the OLD UNDO

Database was UP

Export full backups
:)
Hard day.

DBlink and TNS could not resolve connect identifier specified

I had a strange problem yesterday

We were upgrading Oracle Servers and migrating and back and forth .We need to establish a DB link to move data from one db to one another

Oracle 9.2.0.6 on Solaris 10 =DBNAME=PRD
Oracle 10.2.0.3 on solaris 10=DBNAME=UAT

Steps we followed

1.Connect admin/*******@UAT
Connected

2.create database link PRD connect to admin identified by **** using 'PRD'

where PRD is the service name..

Database link created;


select * from hrm_employee@prd

ora-12514 TNS could not resolve connect identifier specified

PRD service entry is present in the TNSNAMES.ora in the client side..

connect admin/*******@PRD

connectes:

so we tried all the option including replace the 'servicename' in the create DB link stmt with the

'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = *********)(PORT = 1521)) ) (CONNECT_DATA = (SID = PRD) ) )'

But still it didnt work out

Then i realised that DBlink uses the Server TNSNAMES.ora but not the Cleint TNSNAMES.ora which i am connecting from

We added the PRD entry in the TNSNAMES.ora and the DBlink worked


Pretty hard day...

New things are learned everyday..

Cheers maran

More Over

If the GLOBAL_NAMES=TRUE then the DBlink name should be same as the SID name..Or else you will hit the ora-2085 error.

Oracle Installation and Patching in AIX for 9i R2

I had a strnage expereince today..We are setting up a Dataguard for one of our clientIt is on AIX 5.3 with Oracle 9.2.0.6Software provided by the clients.

I started the installation after doing all the prerwquisites checkIt started but it was prompting for JDK directory...i was confused for a while where the JDK is but somehow i managed to find the instalation under /user/java14

but what happened after that was even more childishOracle installation was successful but the relinkimg failed witht the error "unable to invoke target ctx....ins_rdbms.mk

I tried installing 3 times but failed then got the doubt about the Version of Oracle Software...as expected it was the Oracle9i for AIX 4.3 version....Now the client is downloading again...What to do...but new thing learned today

Day 2
Just to add on yesterday inputswhile i was patching the 9.2.0.6 around 99% of installation a windows was appearing saying that "cannot open or remove a file LIBNJNI.so which is currntly runnng ,"i tried 3-4 times

but at the end ..the reason i missed out step in the README.txt i checked the os process ps-ef...but no oracle process were runnningtheni executed /usr/sbin/slibclean which removes the libraires from Kernel and Memory if accessed bu the OSTHen the installation went smoothOne more new thing i learned today.

.in UNIX i cloned the production to a new server which is going to act as new production..I tried to mount the databasse but i was keep on getting the error cannot open the password file...but alter database open command opens the DB

but when i startup the DB again i had to issue alter database openthen i realised in UNIX the password file is case case sensisitve ORACLE_SID should match the password file like orapwSIDSilly stuff but i forgot ....Metalink helped

Recovering from Hotbackup

I am just posting the thing i have learned today while cloning the DB from Hotbackup

We had a Hotbackup which is a USER-MANAGED Backup from the production

The Database files were residing in E:drive and log files/Controlfile were placed D:Drive and E:Drive

We copied the Archived log generated after the backup was complted

I cloned in a different machine in E:Drive but i didnt had the D:Drive where the logfiles and controlfile members are.

3 log grouls
redo11.log(E:Drive)
redo12.log(E:Drive)
redo13.log(D:Drive)
control3.ctl(D:Drive)

The set is like this for all three groups

Now i started recovering from the hotbackup.

I started and mounted the Database
Since there is no D:Drive i had to drop the members in D:Drive
I dropped the Group1 memeber and Group 2 Member but when i tried to drop the group 3 memeber Oracle didnt allow to drop the group 3 memeber

So i decided to a recovery using abckup controlfile until cancel option
It was prompting the Archive log and i have successfully completed the Incomplete Media Recovery

now i try to open with Alter database open resetlogs

Since resetings the logs creating the redologs it tried to create the Group 3 member in D:Drive

I tried to clear the D:Drive log memeber but couldnt it
i treid clearing UNARCHINING option still of no use

Suddenly it thought of renaming the D:Drive log memeber to E:Drive and did successfuly

now i tried to open the resetlogs option

It opened.

Regards
Maran

Patching Oracle Software

Time and again and again we see people asking help in patching Oracle software

I will just briefly explain the procedure to Patch and Upgrade the database.

Patching invloves 2 steps sometimes it is a one step process only if there is no DB running on the version

1.Assume you have a Oracle software Installed(9.2.0.1)
2.No database has been created in this version so far.

Patching
Patching is nothing but Fixes and upgrading the binary with the latest bug fixes released by Oracle

1.Download the patchset from Metalink for the Oracle/Os Versions
2.Lauch the Oracel Universtll installer from the downloaded patch
3.It selects the Oracle Home and display the Oracle Home which will be Upgraded
4.If you have multiple homes Select the appropriate Software which needs to be upgraded.
5.IF no patch is required it will prompt a message that "No Patches will be installed"
6.If the Prequisties are over It will start installing/upgrading the ORacle binaries
7.On completion of installation the Software will be upgraded to the latest patchset.

The Database created after the PAtching will all be in the latest patch version(9.2.0.8)

On the Other hand if you want to the Patch on a software on which many databases are already running

This is 2 step process
1.Upgrading the binary
2.Upgrading the database

1.Upgrading the binary
a.Shutdown all the ORacel databases on that version(9.2.0.1)
b.Stop the services related to it.
c.Follow the steps mentioned in the above steps patching


Now the Oracle binary is upgraded and now we need to upgrade the DB
1.Start the DB in the STARTUp UPGRADE/START MIGRATE
2.This will upgrade the Oracle Database.
Look for errors if any.

Comments are welcome

Insufficeint privileges for SYS user

Time and again and again we see so many questions about INSUFFICIENT PRIVILEGES for the SYS when trying to login. I throw some tips that i have learned so far

1.Never use Oracle 8 Binary to connect as SYSDBA you will encounter INSUFFICIENT PRIVILEGES issue
I try to seggregate things here
2.a Connecting Localy in the server
2.b Connecting from remote

2.a Connecting Localy in the server :
Windows Connecting locally within the server When you set the value SQLNET_AUTHENTICATION_SERVICES=(NONE) in the sqlnet.ora you have to use passwordfile for authenticating .If passwordfile not available you have create and use it.you cant connect without a password file as simple as that. Even the REMOTE_LOGIN_PASSWORDFILE =NONE is set it does not allow without password

When you set the value SQLNET_AUTHENTICATION_SERVICES=(NTS)It doesnt care whether it has a passwordfile or not.But it check whether the system user is a member of a ORA_DBA group if not it will bounce with insufficient privileges .you should be a memeber of DBA to access the DB without password.

Connecting From Remote:

If you want to connect to the DB from a remote machine you should set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE whereby which uses passwordfile to authntication to connect o the DB.Setting NONE will prevent user from connecting from the remote machine

BUt one strange i encnountered last week that i was not able to connect to DB as sys from remote machine with all variable set

Solaris 9 with Oracle 9.2.0.7.The issue was fixd with remote_os_authen=TRUE parameter.

I think it may be a bug

I will try in windows ..

Note:
1.Always set ORACLE_SID before trying to connect to the DB which fixes most of the issues
2.Unix-Check the ORacle user as a member fo DBA or OINSTALL group.
Most of the time we will be connecting to the wrong DB and gets bounced because of ORACLE_SID
S
Comments are welcome i might have

OraDim utility

Hi Friends

Just to share my experience

Last week we had a OS crash so we did a reinstall upon the installation of the OS

But what happend our system admin has changed the Drive lable for all the other drives..

Even i could identify the difference immedaitle on the Drive Label

I tried to recreate the services one by one..But on 9i Oradim utiliy does not create the services with pfile with autostartu option

I think if the Pfile entries have wrong drive lables you will not be able to create a service for he DB


After renaming the labels everything was perfect but 8/10g did not have this issue