重庆思庄oracle技术分享-oracle 11g修改数据库sid和dbname

数据库名称
原sid和dbname: testdbutf8
新sid和dbname: testdb2

#######步骤一：修改数据库sid#######
1、关库
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、修改数据库参数文件名称

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、启动并登陆数据库，查看实例名称
sqlplus / as sysdba

startup

SQL> select instance_name from v$instance;

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


########步骤二：修改数据库dbname #######

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

2、关库
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、复制一份备份文件
cp testdb2_ora_20847.trc testdb2.sql

5、编辑testdb2.sql
去掉多余部分，替换所有testdbutf8为testdb2
把CREATE CONTROLFILE REUSE 改为 CREATE CONTROLFILE SET  
注释掉 --RECOVER DATABASE USING BACKUP CONTROLFILE
最终结果如下：


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、修改目录
--闪回恢复区
cd /u01/app/oracle/fast_recovery_area
mv testdbutf8 testdb2
mv TESTDBUTF8 TESTDB2

--数据文件目录
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相关的参数，替换所有参数值中testdbutf8为testdb2，修改结果如下

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、登录数据库，生成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、验证数据库名称是否已改
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