數(shù)據(jù)庫名稱
原sid和dbname: testdbutf8
新sid和dbname: testdb2

#######步驟一：修改數(shù)據(jù)庫sid#######
1、關(guān)庫
sqlplus / as sysdba

shutdown immediate;
exit

2、修改/etc/oratab文件,替換所有sid為testdb2

vi /etc/oratab

testdb2:/u01/app/oracle/product/11.2.0/db_1:N

3、修改bash_profile,替換所有sid為testdb2
vi /home/oracle/.bash_profile

export ORACLE_SID=testdb2

source .bash_profile

4、修改數(shù)據(jù)庫參數(shù)文件名稱

cd??$ORACLE_HOME/dbs

mv hc_testdbutf8.dat hc_testdb2
mv inittestdbutf8.ora inittestdb2.ora
mv lkTESTDBUTF8 lkTESTDB2
mv orapwtestdbutf8 orapwtestdb2
mv spfiletestdbutf8.ora spfiletestdb2.ora

[oracle@testdb-utf8 dbs]$ ll
total 28
-rw-rw---- 1 oracle oinstall 1544 May 22 14:03 hc_testdb2.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15??2009 init.ora
-rw-r--r-- 1 oracle oinstall 1316 May 22 13:54 inittestdb2.ora
-rw-r----- 1 oracle oinstall? ?24 May 22 09:09 lkTESTDB2
-rw-r----- 1 oracle oinstall 2048 May 22 11:27 orapwtestdb2
-rw-r----- 1 oracle oinstall 3584 May 26 04:00 spfiletestdb2.ora


5、重新生成密碼文件
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=sys entries=5 force=y

6、啟動并登陸數(shù)據(jù)庫，查看實例名稱
sqlplus / as sysdba

startup

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
testdb2


########步驟二：修改數(shù)據(jù)庫dbname #######

1、接上一步驟，備份控制文件
alter database backup controlfile to trace resetlogs;

2、關(guān)庫
shutdown immediate;
exit

3、進入控制文件備份目錄
cd /u01/app/oracle/diag/rdbms/testdb2/testdb2/trace/
tail -200 alert_testdb2.log

找到下面這句話：
alter database backup controlfile to trace resetlogs
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/testdbutf8/testdb2/trace/testdb2_ora_20847.trc
Completed: alter database backup controlfile to trace resetlogs

4、復(fù)制一份備份文件
cp testdb2_ora_20847.trc testdb2.sql

5、編輯testdb2.sql
去掉多余部分，替換所有testdbutf8為testdb2
把CREATE CONTROLFILE REUSE 改為 CREATE CONTROLFILE SET??
注釋掉 --RECOVER DATABASE USING BACKUP CONTROLFILE
最終結(jié)果如下：


vi testdb2.sql

STARTUP NOMOUNT
CREATE CONTROLFILE SET??DATABASE "TESTDB2" RESETLOGS??NOARCHIVELOG
? ? MAXLOGFILES 16
? ? MAXLOGMEMBERS 3
? ? MAXDATAFILES 100
? ? MAXINSTANCES 8
? ? MAXLOGHISTORY 292
LOGFILE
??GROUP 1 '/oradata/datafile/testdb2/redo01.log'??SIZE 500M BLOCKSIZE 512,
??GROUP 2 '/oradata/datafile/testdb2/redo02.log'??SIZE 500M BLOCKSIZE 512,
??GROUP 3 '/oradata/datafile/testdb2/redo03.log'??SIZE 500M BLOCKSIZE 512,
??GROUP 4 '/oradata/datafile/testdb2/redo04.log'??SIZE 500M BLOCKSIZE 512,
??GROUP 5 '/oradata/datafile/testdb2/redo05.log'??SIZE 500M BLOCKSIZE 512
DATAFILE
??'/oradata/datafile/testdb2/system01.dbf',
??'/oradata/datafile/testdb2/sysaux01.dbf',
??'/oradata/datafile/testdb2/undotbs01.dbf',
??'/oradata/datafile/testdb2/users01.dbf'
CHARACTER SET AL32UTF8
;
--RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/datafile/testdb2/temp01.dbf'
? ???SIZE 61865984??REUSE AUTOEXTEND ON NEXT 655360??MAXSIZE 32767M;

6、生成pfile

sqlplus / as sysdba

create pfile='?/dbs/inittestdb2.ora' from spfile;


7、修改目錄
--閃回恢復(fù)區(qū)
cd /u01/app/oracle/fast_recovery_area
mv testdbutf8 testdb2
mv TESTDBUTF8 TESTDB2

--數(shù)據(jù)文件目錄
cd /oradata/datafile
mv testdbutf8 testdb2

--日志文件目錄
cd /u01/app/oracle/diag/rdbms
mv testdbutf8 testdb2

cd /u01/app/oracle/admin
mv testdbutf8 testdb2


8、備份老控制文件
cd /oradata/datafile/testdb2
mv control01.ctl control01.ctl.bak
cd /u01/app/oracle/fast_recovery_area/testdb2
mv control02.ctl control02.ctl.bak

9、修改pfile,刪除testdbutf8相關(guān)的參數(shù)，替換所有參數(shù)值中testdbutf8為testdb2，修改結(jié)果如下

cd $ORACLE_HOME/dbs
vi inittestdb2.ora

testdb2.__db_cache_size=9026142208
testdb2.__java_pool_size=33554432
testdb2.__large_pool_size=67108864
testdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
testdb2.__pga_aggregate_target=2147483648
testdb2.__sga_target=10737418240
testdb2.__shared_io_pool_size=0
testdb2.__shared_pool_size=1543503872
testdb2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/testdb2/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/datafile/testdb2/control01.ctl','/u01/app/oracle/fast_recovery_area/testdb2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_files=500
*.db_name='testdb2'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.db_unique_name='testdb2'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers=''
*.fast_start_mttr_target=300
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=1000
*.optimizer_index_caching=5
*.optimizer_index_cost_adj=90
*.parallel_max_servers=256
*.parallel_servers_target=64
*.pga_aggregate_target=2147483648
*.processes=3000
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.session_cached_cursors=500
*.sessions=3305
*.sga_target=10737418240
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'


10、登錄數(shù)據(jù)庫，生成spfile
sqlplus / as sysdba
create spfile from pfile='?/dbs/inittestdb2.ora';

11、運行之前的testdb2.sql
@/u01/app/oracle/diag/rdbms/testdb2/testdb2/trace/testdb2.sql


SQL> @/u01/app/oracle/diag/rdbms/testdb2/testdb2/trace/testdb2.sql
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size? ?? ?? ?? ?? ?? ?2262656 bytes
Variable Size? ?? ?? ?? ?1644169600 bytes
Database Buffers? ?? ?? ?9026142208 bytes
Redo Buffers? ?? ?? ?? ?? ?16900096 bytes

Control file created.


Database altered.


Tablespace altered.


12、驗證數(shù)據(jù)庫名稱是否已改
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> show parameter name

NAME? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?TYPE? ?? ???VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name? ?? ?? ?? ?? ?string
db_file_name_convert? ?? ?? ?? ?? ???string
db_name? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?string? ?? ?testdb2
db_unique_name? ?? ?? ?? ?? ?? ?? ???string? ?? ?testdb2
global_names? ?? ?? ?? ?? ?? ?? ?? ? boolean? ???FALSE
instance_name? ?? ?? ?? ?? ?? ?? ?? ?string? ?? ?testdb2
lock_name_space? ?? ?? ?? ?? ?? ?? ? string
log_file_name_convert? ?? ?? ?? ?? ? string
processor_group_name? ?? ?? ?? ?? ???string
service_names? ?? ?? ?? ?? ?? ?? ?? ?string? ?? ?testdb2
SQL> select name from v$database;

NAME
---------
TESTDB2

SQL> exit