NAVEEN

Tuesday, December 29, 2009

Renaming the database Manually

take a controlfile backup:
----------------------------

SQL>alter database backup controlfile to trace;


modify the initfile:
----------------------

copy the following code and save as inittest.ora in 'E:\oracle_home\database\inittest.ora'


*.background_dump_dest='E:\oracle\product\10.1.0\admin\test\bdump'
*.compatible='10.1.0.2.0'
*.control_files='E:\oracle\product\10.1.0\oradata\test\control01.ctl','E:\oracle\product\10.1.0\oradata\test\control02.ctl','E:\oracle\product\10.1.0\oradata\test\control03.ctl'
*.core_dump_dest='E:\oracle\product\10.1.0\admin\test\cdump'
*.db_block_size=8192
*.db_cache_size=33554432
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.java_pool_size=50331648
*.job_queue_processes=10
*.large_pool_size=8388608
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=83886080
*.sort_area_size=65536
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\product\10.1.0\admin\test\udump'
*.log_archive_format='arch_%r_%t_%s.arc'
*.log_archive_dest='E:\oracle\product\10.1.0\db_2\database\archive'



Creating a controlfile
-----------------------

copy the following code and save as ctrl.sql in "c:\ctrl.sql"

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\REDO01.LOG' SIZE 10M,
GROUP 2 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\REDO02.LOG' SIZE 10M,
GROUP 3 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSTEM01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\UNDOTBS01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\SYSAUX01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\USERS01.DBF',
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;


Stutdown the database:
----------------------

SQL> shu immediate;



Stop the Oracle services for old instance in services.msc
----------------------------------------------------------


run --> services.msc

Stop all the services related to old oracle instance through the following command

run --> cmd --> sc delete service_name


rename the folders for
------------------------

1.oradata
2.bdump


create a password file:
------------------------

c:\orapwd file='E:\oracle_home\database\pwdtest.ora' password=test entries=30


start a service for new database
--------------------------------


run --> cmd

c:\set ORACLE_SID=test

c:\oradim -new -sid test -startmode auto -maxusers 100 -pfile E:\oracle\product\10.1.0\db_2\database\inittest.ora

c:\sqlplus

username: sys as sysdba
password: test

connected to idle instance

SQL> startup nomount pfile='E:\oracle_home\database\inittest.ora';

SQL> create spfile from pfile;

SQL> @c:\ctrl.sql

SQL>alter database open resetlogs;

check the database name:
--------------------------

SQL> select instance_name ,status from v$instance;


set the globalname to instance;
-------------------------------

SQL> alter database rename global_name to test;

1 comment: