oracle修改sid数据库名,oracle 11g修改数据库sid和dbname --linux

参考：

https://blog.csdn.net/iteye_14608/article/details/82162620

--操作环境

redhat 7.5

oracle 11.2.0.4

--数据库名称

原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

#######完成修改#######