notes about SQLPLUS & database migration

Start Oracle using SQLPLUS on Windiws NT platform:

As we all know, post-oracle-9i-installation on Windows NT needs some manual operations to startup the database from SQLPLUS.
Here are the steps to notice me long after.

1.Set the ORACLE_SID enviroment.
2.Make sure key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\ORACLE_SID in register table has correct value.
3.Services OracleService and OracleOraHome92TNSListener are of automatic startup.
4.Use oradim to change oracle start type.

ORADIM -STARTUP -SID feelfall -STARTTYPE srvc

Database files migration:

I made a mistake in oracle 9i installation yesterday on my notebook. The datafiles, control files and online logs had been stored at path of oracle\ora92\feelfall, which i was intent to exist at oracle\oraDATA. The problem is not as simple as drag the whole 'feelfall'(coincides my sid) directory into oracle\ then rename it. Here i need a little bit more operations to make the database running okay.
We know that oracle database is conceptually divided into two parts, instance and databse. The parameter file contains part of the inforamtion about oracle instance and has records of the path of database control files. Well, control file contains the whole information about the database part, such like every datafile's location, as well as every online log file's, recovery information and so on. For safe consideration, there are mutiple controfile mirrors, and each one can be used to open a specified database.

open F:\oracle\admin\feelfall\pfile\init.ora.223200716132

###########################################
# File Configuration
###########################################
control_files=("F:\oracle\ora92\feelfall\control01.ctl", "F:\oracle\ora92\feelfall\control02.ctl", "F:\oracle\ora92\feelfall\control03.ctl")

create a file init.ora similar to init.ora.223200716132, then change control_files's value like this:
control_files=("F:\oracle\oraDATA\feelfall\control01.ctl", "F:\oracle\oraDATA\feelfall\control02.ctl", "F:\oracle\oraDATA\feelfall\control03.ctl")


SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='F:\ORACLE\ADMIN\feelfall\PFILE\INIT.ORA'
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying controlfile, check alert log for more info

This error message indicates oracle instance can not locate my control files.
Move control files to the path asigned above.

SQL> alter database mount;

Database altered.

Now i should rename the datafiles and log files, ortherwise i could not accomplish my initial intention.

SQL> alter database rename file 'F:\ORACLE\ORA92\feelfall\SYSTEM01.DBF'
2 to 'F:\ORACLE\ORADATA\feelfall\SYSTEM01.DBF'

Database altered.


...rename others...


I should put a little emphasize on temporary file, cause you cann't rename them.

SQL> alter database tempfile 'F:\ORACLE\ORA92\feelfall\TEMP01.DBF' drop;

Database altered.

...now i openned the datavbase...

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\feelfall\SYSTEM01.DBF'

The open stage failed because i need the rest files.
Copy the files needed to the correct new folder.

SQL> alter database open;

Database altered.

SQL> alter tablespace temp
2 add tempfile 'F:\ORACLE\ORADATA\feelfall\TEMP01.DBF' reuse;

Tablespace altered.

Now, create a new spfile so that not more actions taken when startup.

SQL> create spfile from pfile='F:\ORACLE\ADMIN\feelfall\PFILE\INIT.ORA';

File created.



Okay!