如何修改oracle数据库的db_name,instace_name,sid_name

这篇文章主要介绍了如何修改oracle数据库的db_name,instace_name,sid_name，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

概念：db_name 被记录在数据文件，日志文件和控制文件中,可以不同于instance_name.

sid_name 1.主要是区别连接的数据库

2. 在操作系统层面上进行区分，当Oracle实例启动时，操作系统上fork的进程必须通过这个SID将实例与其他实例区分开来，后台进程通过该sid表示数据库 eg: ps-ef|grep smon

3.instance_name是数据库中的一个重要参数，一个主机中可以有相同instance_name的多个数据库。

Oracle的实例（instance）是由一块共享内存区域（SGA）和一组后台进程（background processes）共同组成；而后台进程正是数据库和操作系统进行交互的通道，这些进程的名称就是通过ORACLE_SID决定的

数据库名称db_name >实例名称 instance_name > oracle_sid

修改实例名称

原来的实例名称

SQL> select dbid,name from v$database;

DBID NAME

---------- ---------

1475786014 ORCL

SQL> select instance from v$thread ;

INSTANCE

--------------------------------------------------------------------------------

orcl

修改过程：

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[root@node ~]# vi /etc/oratab

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

[oracle@node ~]$ vi .bash_profile

export ORACLE_SID=test

[oracle@node ~]$ source .bash_profile

[oracle@node ~]$ env|grep ORA

ORACLE_SID=test

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

[oracle@node ~]$ cd $ORACLE_HOME/dbs

[oracle@node dbs]$ ll

total 9544

-rw-rw----. 1 oracle oinstall 1544 Jun 30 21:04 hc_orcl.dat

-rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r--r--. 1 oracle oinstall 935 Jun 30 18:04 initorcl.ora

-rw-r-----. 1 oracle oinstall 24 Jun 30 06:47 lkORCL

-rw-r-----. 1 oracle oinstall 1536 Jun 30 06:50 orapworcl

-rw-r-----. 1 oracle oinstall 9748480 Jun 30 18:05 snapcf_orcl.f

-rw-r-----. 1 oracle oinstall 2560 Jun 30 13:09 spfileorcl.ora

[oracle@node dbs]$ mv initorcl.ora inittest.ora

[oracle@node dbs]$ mv hc_orcl.dat hc_test.dat

[oracle@node dbs]$ mv lkORCL lkTEST

[oracle@node dbs]$ mv snapcf_orcl.f snapcf_test.f

[oracle@node dbs]$ mv spfileorcl.ora spfiletest.ora

[oracle@node dbs]$ rm -rf orapworcl

[oracle@node dbs]$ orapwd file=orapwtest password=system

SQL> startup;

ORACLE instance started.

Total System Global Area 1152450560 bytes

Fixed Size 2252584 bytes

Variable Size 738197720 bytes

Database Buffers 402653184 bytes

Redo Buffers 9347072 bytes

Database mounted.

Database opened.

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

test OPEN

SQL> select instance from v$thread;

INSTANCE

--------------------------------------------------------------------------------

test

SQL> select dbid,name from v$database;

DBID NAME

---------- ---------

1475786014 ORCL

修改数据库名称

备份控制文件

SQL> alter database backup controlfile to trace ;

Database altered.

more /u01/app/oracle/diag/rdbms/orcl/test/trace/test_ora_119661.trc

修改后如下

CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS noARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/oradata/test/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/oradata/test/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/oradata/test/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/oradata/test/system01.dbf',

'/oradata/test/sysaux01.dbf',

'/oradata/test/undotbs01.dbf',

'/oradata/test/users01.dbf',

'/oradata/test/fan.dbf'

CHARACTER SET AL32UTF8

;

(查找所有的orcl修改为test，所有的ORCL修改为test,找到CREATE CONTROLFILE REUSE DATABASE...语句，将其中的REUSE修改为SET

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create pfile from spfile;

修改inittest.ora

*.audit_file_dest='/u01/app/oracle/admin/test/adump'

*.audit_trail='NONE'

*.compatible='11.2.0.4.0'

*.control_files='/oradata/test/control01.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='test'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=1150287872

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

更换目录

/u01/app/oracle/admin/orcl/adump

/oradata/orcl/

将orcl更改为test (控制文件中的目录和操作系统保持一致)

sql>create spfile from pfile;

sql>startup nomount;

sql>

SQL> CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS noARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '/oradata/test/redo01.log' SIZE 50M BLOCKSIZE 512,

9 GROUP 2 '/oradata/test/redo02.log' SIZE 50M BLOCKSIZE 512,

10 GROUP 3 '/oradata/test/redo03.log' SIZE 50M BLOCKSIZE 512

11 -- STANDBY LOGFILE

12 DATAFILE

13 '/oradata/test/system01.dbf',

14 '/oradata/test/sysaux01.dbf',

15 '/oradata/test/undotbs01.dbf',

16 '/oradata/test/users01.dbf',

17 '/oradata/test/fan.dbf'

18 CHARACTER SET AL32UTF8

19 ;

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select dbid,name from v$database;

DBID NAME

---------- ---------

1475786014 TEST

SQL> select instance from v$thread;

INSTANCE

--------------------------------------------------------------------------------

test

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

---------------- ------------

test OPEN

感谢你能够认真阅读完这篇文章，希望小编分享的"如何修改oracle数据库的db_name,instace_name,sid_name"这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!