Please go to tahiti.oracle.com and select your version. Search for the word "restore." There you will find both all the options and examples.

There are so many options it is difficult to know what you want to do. You need to understand how the software works. If you use rman, it is very simple, but you haven't told us which version of Oracle you are using exactly (9.2.0.5 is a version, 9i isn't), what commands you used to back up, and so on.

And it all begs the question, if you are recovering with archived logs, why aren't you using hot backups?

Is the below step is correct, if anything is wrong please correct it:-

RESTORE of DATABASE:-

Copy the database files to the destination server.

1)Edit the init.ora file to the actually location.
2)Set oracle_sid= sidname
3)Sqlplus /NOLOG
4)Startup nomount pfile=’path of the inti.ora’.
5)Take control file backup by “alter database backup controlfile to trace; “(Check udump directory for text based controlfile.)
6)Open control file backup from udump file in text editor. And edit it.
Copy the contained of the controlfile and paste it in CMD.
Now new control files get created.
7)Alter database open resetlog

Now you have restored the database. Below are the steps to recovery it.

8)Shutdown your Database
9)Startup mount
10)“Recover database” and select auto from the option.
It will apply the archive log automatically.

Is the below step is correct, if anything is wrong please correct it:-

RESTORE of DATABASE:-

Copy the database files to the destination server.

1)Edit the init.ora file to the actually location.
2)Set oracle_sid= sidname
3)Sqlplus /NOLOG
4)Startup nomount pfile=’path of the inti.ora’.
5)Take control file backup by “alter database backup controlfile to trace; “(Check udump directory for text based controlfile.)
6)Open control file backup from udump file in text editor. And edit it.
Copy the contained of the controlfile and paste it in CMD.
Now new control files get created.
7)Alter database open resetlog

Once you have open the database then why you are going for recovery process. There is no need of recovery .Now your database will work fine now.

Mariappan wrote:
Cold backup taken 2 days back.
for that i have to apply the archive log file to bring the database till date.

Here archivelog related info is not stored in the control file. Secondly you have already specify the resetlogs option which is used after the incomplete recovery. Once you specify the resetlogs ,you have advanced the incarnation value . So no need of any recovery and go through the oracle manual for more details .

But i am still confused, this is first time for me as i said previously.

Can you please edit my steps give below and give me the correct step by step. So i can follow in my new database server.

RESTORE of DATABASE:-

Copy the database files to the destination server.

1)Edit the init.ora file to the actually location.
2)Set oracle_sid= sidname
3)Sqlplus /NOLOG
4)Startup nomount pfile=’path of the inti.ora’.
5)Take control file backup by “alter database backup controlfile to trace; “(Check udump directory for text based controlfile.)
6)Open control file backup from udump file in text editor. And edit it.
Copy the contained of the controlfile and paste it in CMD.
Now new control files get created.
7)Alter database open resetlog

Now you have restored the database. Below are the steps to recovery it.

8)Shutdown your Database
9)Startup mount
10)“Recover database” and select auto from the option.
It will apply the archive log automatically.

Cold backup taken 2 days back.
for that i have to apply the archive log file to bring the database till date.

As Fran has advised you earlier, you must use

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

The "Using Backup Controlfile" indicates to Oracle that there are potentially ArchiveLogs (which are not recorded in the controlfile) that have to be applied. The Recovery will generate an expected file name, based on log_archive_dest and log_archive_format, and prompt you for confirmation. If the file name is correct, you can confirm it, else enter the name of the first ArchiveLog that was generated after the Cold backup. After having applied this ArchiveLog, Oracle will prompt you to confirm the next ArchiveLog file name. It will continue prompting you (you can set AUTORECOVERY if you know that all the ArchiveLogs are of the expected filenames) until it comes to a point in time when the next "expected" ArchiveLog is not available. This is the point where you have applied the last available ArchiveLog.
You will then CANCEL the recovery (the filename prompt allows you to enter "CANCEL") if you have used interactive prompts without AUTORECOVERY.

If all the datafiles have been recovered to a consistent point in time, Oracle will return a message like "media recovery complete". If they are not yet consistent you will get a warning that some datafiles still need recovery.
Assuming that your cold backup was a proper COLD Backup, and the ArchiveLogs are in the right sequence, you will have a consistent recovery.

You will then have to

ALTER DATABASE OPEN RESETLOGS;

at which point Oracle will overwrite and/or create the Online Redo Logs and reset the Log Sequence to 1 (and create a new database incarnation).

Note : If you restore the controlfile from a backup , you do NOT need to CREATE CONTROLFILE. There are risks if you make mistakes in manually running a CREATE CONTROLFILE.