mannual coning of oracle 10g database in window machine.

######################################################################3

-Mannually Creating one database from another database in windows machine
Assume the Old database:- orcl
Assume the new database:-orclnew

NOTE:- Do not user any special character for the new database name.

1)OLD database:-
-login as sys user
U:\>sqlplus system@orcl

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Jan 18 11:28:56 2010

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>archive log list;


2)SQL>create pfile from spfile;
File created.
check the contol file path
SQL>show parameter pfile;
Result:-
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:\ORACLE\PRODUCT\10.1.0\DB_1\
DATABASE\SPFILEORCL.ORA

3)SQL>alter database backup controlfile to trace;
Result:-Database altered

This creates a file in the trace file directory.
The file usually has the extension '.trc' and will be located either in the directory defined by the parameter 'user_dump_dest',
or if this parameter is undefined it will be in $ORACLE_HOME/rdbms/log. Edit this file with your favourite editor and remove the crud.
Then rename it as "ctrlorclnew.sql," where orclnew will be the ORACLE_SID of the copied database.Save the file D:\orclnew\ctrlorclnew.sql

Modify The Script Created In The Previous Step.This may look like below.This would create the control file script.


CREATE CONTROLFILE SET DATABASE orclnew RESETLOGS ARCHIVELOGMAXLOGFILES 16

MAXLOG

MEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\orclnew\REDO01.LOG’ SIZE 50M,

GROUP 2 ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\orclnew\REDO02.LOG’ SIZE 50M,

GROUP 3 ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\orclnew\REDO03.LOG’ SIZE 50M

– STANDBY LOGFILE

DATAFILE

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\orclnew\SYSTEM01.DBF’,

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\orclnew\UNDOTBS01.DBF’,

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\orclnew\SYSAUX01.DBF’,

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\orclnew\USERS01.DBF’,

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\orclnew\EXAMPLE01.DBF’

CHARACTER SET TR8MSWIN1254

;

4)Create the pfile. This file may look like this. Change the oracle SID name and make create the folder structure according to the pfile
D:\oracle\product\10.1.0\db_1\database\INITorclnew.ORA
background_dump_dest='D:\oracle\product\10.1.0\admin\orclnew\bdump'
compatible='10.1.0.2.0'
control_files="D:\oracle\product\10.1.0\oradata\orclnew\control01.ctl", "D:\oracle\product\10.1.0\oradata\orclnew\control02.ctl", "D:\oracle\product\10.1.0\oradata\orclnew\control03.ctl"
core_dump_dest='D:\oracle\product\10.1.0\admin\orclnew\cdump'
db_block_size=8192
db_cache_size=25165824
db_domain=''
db_file_multiblock_read_count=16
db_name='orclnew'
db_recovery_file_dest='D:\oracle\product\10.1.0\flash_recovery_area'
db_recovery_file_dest_size=2147483648
dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
java_pool_size=50331648
job_queue_processes=10
large_pool_size=8388608
open_cursors=300
pga_aggregate_target=25165824
processes=150
remote_login_passwordfile='EXCLUSIVE'
shared_pool_size=83886080
sort_area_size=65536
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
user_dump_dest='D:\oracle\product\10.1.0\admin\orclnew\udump'

5)Connect to the old database as super user and copy shutdown the database.
SQL>shu immediate;
6)copy the datafile and logfiles according to the mentioned controfile script.

create the oracle service for the new database.So that it can be connected.
cd D:\oracle\product\10.1.0\oradata\orclnew\
oradim new -sid orclnew -intpwd oracle -startmode -M

7) set ORACLE_SID=orclnew
sqlplus '/nologs'

8)SQL>conn sys/oracle@orclnew
SQL>STARTUP NOMOUNT PFILE='D:\oracle\product\10.1.0\db_1\database\INITorclnew.ORA'

9)Run the ctrlorclnew.sql file
SQL>@D:\orclnew\ctrlorclnew.sql

10) Recover the database untill cancel.
SQL>RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;

SQL>ALTER DATABASE OPEN RESETLOGS;

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\orclnew\TEMP01.DBF’

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;