更改数据库SID
在oracle9i中测试通过
案例: 旧数据库([color=blue:f74257d0fd]OLDDB[/color:f74257d0fd])
文件存放(data files,redo files,control files,temp files, undo files)目录是([color=blue:f74257d0fd]/u01/OLDDB[/color:f74257d0fd])
dump文件目录是[color=blue:f74257d0fd](/u01/dump[/color:f74257d0fd])
新数据库([color=red:f74257d0fd]NEWDB[/color:f74257d0fd])
文件存放(data files,redo files,control files,temp files, undo files)目录是([color=red:f74257d0fd]/u02/NEWDB[/color:f74257d0fd])
dump文件目录是([color=red:f74257d0fd]/u02/dump[/color:f74257d0fd])
1. 生成旧数据库初始化参数文件(init{SID}.ora)和文本格式的控制文件(control.txt)
alter database backup controlfile to trace as '/tmp/control.txt'
create pfile from spfile
2. 关闭旧数据库
3. 将旧数据库存放文件和dump的目录移动到新数据库存放文件和dump的目录(如果不更改存放的目录可以不执行该步骤)
mv [color=blue:f74257d0fd]/u01/OLDDB[/color:f74257d0fd] [color=red:f74257d0fd]/u02/NEWDB[/color:f74257d0fd]
mv [color=blue:f74257d0fd]/u01/dump[/color:f74257d0fd] [color=red:f74257d0fd]/u02/dump[/color:f74257d0fd]
4. 进入$ORACLE_HOME/dbs目录,拷贝OLDDB的pfile成NEWDB的pfile,同时修改NEWDB的pfile文件.
cd $ORACLE_HOME/dbs
cp initOLDDB.ora initNEWDB.ora
修改initNEWDB.ora文件
a. db_name和instance_name参数更改成新数据库的名称(NEWDB)
b. background_dump_desc,core_dump_desc和user_dump_desc参数所指定存放新数据库存放dump信息的目录(/u02/dump)
c. control_files参数所指定存放新数据库的控制文件目录及名称('/u02/NEWDB/control01.ctl','/u02/NEWDB/control02.ctl')
5. 编辑control.txt文件,新生成newdb.sql和newdb1.sql文件
拷贝control.txt文件中以下行到/tmp/newdb.sql文件中
---------------
CREATE CONTROLFILE [color=blue:f74257d0fd]REUSE[/color:f74257d0fd] DATABASE "[color=blue:f74257d0fd]OLDDB[/color:f74257d0fd]" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/redo01.log' SIZE 10M,
GROUP 2 '/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/redo02.log' SIZE 10M,
...
GROUP n '/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/redo0n.log' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/system01.dbf',
'/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/undotbs01.dbf',
'/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/indx01.dbf',
'/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/tools01.dbf',
'/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/users01.dbf',
'/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/xdb01.dbf',
...
'/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/filename.dbf'
CHARACTER SET ZHS16GBK
;
-------------
并修改/tmp/newdb.sql文件
a. REUSE -> SET
b. OLDDB -> NEWDB
c. 存放目录 /u01/OLDDB -> /u02/NEWDB(如果不更改存放的目录可以不执行该步骤)
更改后/tmp/newdb.sql文件内容如下
---------------
CREATE CONTROLFILE [color=red:f74257d0fd]SET[/color:f74257d0fd] DATABASE "[color=red:f74257d0fd]NEWDB[/color:f74257d0fd]" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/redo01.log' SIZE 10M,
GROUP 2 '/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/redo02.log' SIZE 10M,
...
GROUP n '/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/redo0n.log' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/system01.dbf',
'/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/undotbs01.dbf',
'/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/indx01.dbf',
'/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/tools01.dbf',
'/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/users01.dbf',
'/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/xdb01.dbf',
...
'/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/filename.dbf'
CHARACTER SET ZHS16GBK
;
-------------
拷贝control.txt文件中以下行到/tmp/newdb1.sql文件中
-------------
ALTER TABLESPACE TEMP ADD TEMPFILE '/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/temp01.dbf'
SIZE 524288000 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;
...
ALTER TABLESPACE TEMPn ADD TEMPFILE '/[color=blue:f74257d0fd]u01/OLDDB[/color:f74257d0fd]/tempn.dbf'
SIZE 524288000 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;
-------------
并修改/tmp/newdb.sql文件
a. 存放目录 /u01/OLDDB -> /u02/NEWDB(如果不更改存放的目录可以不执行该步骤)
更改后/tmp/newdb1.sql文件内容如下
-------------
ALTER TABLESPACE TEMP ADD TEMPFILE '/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/temp01.dbf'
SIZE 524288000 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;
...
ALTER TABLESPACE TEMPn ADD TEMPFILE '/[color=red:f74257d0fd]u02/NEWDB[/color:f74257d0fd]/tempn.dbf'
SIZE 524288000 REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M;
-------------
6. 删除新数据库pfile文件(initNEWDB.ora)中control_files参数所指定的控制文件.
rm /u02/NEWDB/*.ctl
7. 启动新数据库(NEWDB)到nomount状态,使用initNEWDB.ora参数文件
startup nomount pfile=?/dbs/initNEWDB.ora
8. 执行/tmp/newdb.sql文件,重建控制文件
@/tmp/newdb.sql
9. 使用resetlogs选项打开新数据库(NEWDB)
alter database open resetlogs
10. 执行/tmp/newdb1.sql文件,重建临时表空间
@/tmp/newdb2.sql
11. 查看v$database和v$instance视图,是不是数据库名称已更改