A remote host or same host will be used as destination host.
there are alternate ways like using rman ; read backup and recovery guide for further information;
I prefer this method for databases using filesystems as datafile storage area; because if rman is used a backup and restore operation will be required.
For Changing SID a tool NID can be prefered.
startup mount ;
nid TARGET=sys "/ as sysdba " DBNAME=SID9 SETNAME=YES
SETNAME=YES parameter keeps db id not changed, so if a dataguard was set it can continue applying .
it is simple and easy to use but a few times I needed deep dive :)
========================================================
CURRENT SID= SID8
new SID= SID9
10 backup control file to trace (user dump destination ); we will re-create it;
a new create control file script will be created in path "user_dump_dest"
SQL> alter database backup controlfile to trace;
SQL> show parameter user_dump_dest;
[oracle@tabya ~]$ cd /oracle/admin/SID8/udump
-rw-r----- 1 oracle dba 6.5K 2009-01-31 13:44 sid8_ora_4059.trc --> this is the file created ..!!
[oracle@tabya udump]$ cp sid8_ora_4059.trc /home/oracle/createControl_pre.txt
20 cold copy; shutdown listener and instance.
copy all datafiles;tempfiles;redolog files,
to get the list of files;
select name from v$datafile; --copy all of them
select member from v$logfile; -- recommended to copy all of them
select name from v$controlfile; -- do not copy them we will cretae them.
select name from v$tempfile; -- do not copy them we will create them
cp -R /oracle/oradata/SID8 /oracle/oradata/SID9
or
mv /oracle/oradata/SID8 /oracle/oradata/SID9
30 Change , edit user enviorement;
some evn variables need to be set like ORACLE_HOME,ORACLE_BASE,ORACLE_SID
you can modify .profile or create a new profile let'say .profile.SID2
cp .bash_profile .profile.SID9
vi .profile.SID9
ORACLE_SID=SID8; export ORACLE_SID ------>> ORACLE_SID=SID9; export ORACLE_SID
40 init file spfile or pfile.
parameter file must be valid before re-creating control file..
create a pfile from source spfile; change the SID to new name.
note: you can use
cd $ORACLE_HOME/dbs
cat spfileSID8.ora > pfileEditSID9.ora
vi pfileEditSID9.ora
#### DEFAULT FILE
*.audit_file_dest='/oracle/admin/SID8/adump'
*.background_dump_dest='/oracle/admin/SID8/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle/oradata/SID8/control01.ctl','/oracle/oradata/SID8/control02.ctl','/oracle/oradata/SID8/control03.ctl'
*.core_dump_dest='/oracle/admin/SID8/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='SID8'
*.db_recovery_file_dest='/oracle/fra'
*.db_recovery_file_dest_size=21118320640
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SID8XDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=83886080
*.processes=70
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=82
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/SID8/udump'
#### CHANGE THESE PARAMETER
*.db_name='SID9'
*.audit_file_dest='/oracle/admin/SID9/adump'
*.background_dump_dest='/oracle/admin/SID9/bdump'
*.core_dump_dest='/oracle/admin/SID9/cdump'
*.user_dump_dest='/oracle/admin/SID9/udump'
*.control_files='/oracle/oradata/SID9/control01.ctl','/oracle/oradata/SID9/control02.ctl','/oracle/oradata/SID9/control03.ctl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SID9XDB)'
##
*.compatible='10.2.0.3.0'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest='/oracle/fra'
*.db_recovery_file_dest_size=21118320640
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=83886080
*.processes=70
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=82
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
50 I will edit file created in step 10 by changing and setting same parameters.
This file will include 2 sets ;
Set #1. NORESETLOGS case
Set #2. RESETLOGS case
I will use RESETLOGS case because we will have clean shutdown and no need to recovery.
set SID to new in enviorement;
change datafile paths no newly copied destination
cp /home/oracle/createControl_pre.txt /home/oracle/createControl_ok.txt
vi /home/oracle/createControl_ok.txt
60 listener.ora ; tnsnames.ora
optional you can configure listener to register it or leave it to database itself.
70 create abcu dump destinations change in pfileEdit
create dump destinations; you must have changed them in pfile.. important.
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump
## CREATE DUMP FOLDERS
mkdir -p /oracle/admin/SID9/adump
mkdir -p /oracle/admin/SID9/bdump
mkdir -p /oracle/admin/SID9/cdump
mkdir -p /oracle/admin/SID9/udump
80 create passwd file for new instance;
after correctly setting env variables you can just create a password file with the command as oracle user.
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=10
90 After editing "/home/oracle/createControl_ok.txt" you will open db in nomount mode;
create controlfile
cd $ORACLE_HOME/dbs
sqlplus / as sysdba
startup nomount pfile=pfileEditSID9.ora;
@/home/oracle/createControl_ok.txt -->> failed.
@/home/oracle/createControl_ok2.txt -->> succeeded
CREATE CONTROLFILE REUSE DATABASE "SID8" RESETLOGS ARCHIVELOG -->> createControl_ok.txt ..!! WARNING
CREATE CONTROLFILE REUSE SET DATABASE "SID9" RESETLOGS ARCHIVELOG -->> createControl_ok2.txt --> Control file created.
..!! WARNING
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name SID8 in file header does not match given name of SID9
ORA-01110: data file 1: '/oracle/oradata/SID9/system01.dbf'
====================================
in create controlfile script there is a "reuse" statement set it to "set" ; then db name is changed ;SID8 to SID9
datafile has its SID embeded but can be changed during create controlfile. with SET
====================================
CREATE CONTROLFILE REUSE SET DATABASE "SID9" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/SID9/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/SID9/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/SID9/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/SID9/system01.dbf',
'/oracle/oradata/SID9/undotbs01.dbf',
'/oracle/oradata/SID9/sysaux01.dbf',
'/oracle/oradata/SID9/users01.dbf',
'/oracle/oradata/SID9/example01.dbf'
CHARACTER SET WE8ISO8859P9
;
100 add tempfile to temp tablespace
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/SID9/temp01.dbf'SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
110 create spfile and rebounce db
create spfile from pfile='
create spfile from pfile='/oracle/product/db/10.2.0/dbs/pfileEditSID9.ora';
shutdown immediate;
exit
rm pfileEditSID9.ora
sqlplus / as sysdba
startup;
That was all;
Thank You
Tamer ÖNEM
