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

Undo Tablespace recovery

Undo tablespaceFrom my understanding i amjust elobrating howards outstanding contribution

Day:FridayBackup the UNDO tablespace on Friday
2:PMSTMT:update table After 2: PM
Action 1: Generates UNDO for the OLD VALUES and inserts into UNDO TABLESPACE…Action 2: Generates REDO for the update stmt and stored in REDOLOG files which will be archived later…Need for Recovery…This will have committed and uncommitted data where committed data will have SCN to it where as uncommitted redo will not have SCN…

Changes happening for next days…Undo Tablespace corrupted…
Scenario starts here
We restore the Friday Undo Backup here…
Recover the UNDO Tablespace to bring the DB upApplying Archive logs starts here which will have commited and uncommitted data hereSince the log has the SCN for every commit

1. it starts inserting all the data Here is the thing which may be crucial as far as I have understoodIt has both commited and uncommitted data…

The Undo will be generated for those statements and corresponding redo will be generated but the internal transaction table will be updated for every committed transaction with the help of SCN from the archived logs so here the inserted stmts which have the SCn will be commited which is roll forward and the internal transaction tables gets updated with the respective SCN…

Now all the statement were executed and UNDO will have some uncommitted transactions after recovery in fact during the recovery. Those are the one which will not have an entry n the internal transaction table.

Now SMON starts rollback with the help of internal transaction table.That statement which does not have commit entry in the Internal Transactions table will all be rolled back.


So the Internal transaction tables uncommitted entry and the undo generated during the recovery will help in rolling back.This is what I have understood …

Hope this helps

Undo tablespace Concepts

Hi ,

I would like to share my experience about the UNDO tablespace problem i faced while running a customised masking scripts

I was involved in resynching the UAT with Production.

The Production was restored on UAT database after recreating the control file.We have a script file which masks the sensitive data on the UAT before bringing to the office.

Its has serious of update statements which updates the column values to zero but unfortunately we had only one commit statement which will be fired at the end of the script

Since the number of records were very high i started hitting the undo tablespace reaching more 6gigs and almost we ran out of space in the system.

se we changed the script to have commit after every DML which resulted in reusing the UNDOTBS

Now i have clearly understood what undo serves for

Regards
Elamaran

Redo Log Thread Number

I was just wondering for few days why the thread number is always constant in the archive log but i am working with the single instance database so i was unaware of that ..

Now in RAC database each instance has its own thread so if we two have nodes we will have 2 threads numbers representing each instances..