详细修改数据库名、实例名文档

JiekeXu

1817

8页

13次

2020-07-02

5墨值下载

***************一步步教你修改数据库名、实例名***************

墨天轮：https://www.modb.pro/u/4347

公众号：JiekeXu 之路

**********************************************

注：生产环境中进行如下操作之前，务必备份数据库且确保备份完整可用。

一、测试环境信息

操作系统版本：

SQL> !uname -a

Linux db10g 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64

x86_64 GNU/Linux

数据库版本：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

当前实例名：

SQL> select instance from v$thread;

INSTANCE

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

orcl

当前数据库名：

SQL> select name from v$database;

NAME

---------

ORCL

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string orcl

二、修改数据库实例(orcl-->orcl1)

修改实例名为 orcl1：

1、干净的关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2、查看 spfileorcl.ora 中的内容创建 pfile initorcl.ora

oracle@db10g:orcl$cd $ORACLE_HOME/dbs

oracle@db10g:orcl$pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

oracle@db10g:orcl$mv spfileorcl.ora spfileorcl.ora.bak

oracle@db10g:orcl$strings spfileorcl.ora.bak

oracle@db10g:orcl$ls

alert_orcl.log hc_orcl.dat initdw.ora init.ora initorcl.ora lkORCL

orapworcl snapcf_orcl.f spfileorcl.ora.bak

oracle@db10g:orcl$cat initorcl.ora

orcl.__db_cache_size=150994944

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=109051904

orcl.__streams_pool_size=8388608

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

*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'

*.compatible='10.2.0.5.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/or

adata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

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

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=283115520

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

3、确认数据库使用 pfile 文件启动

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 2095704 bytes

Variable Size 125830568 bytes

Database Buffers 150994944 bytes

Redo Buffers 6291456 bytes

Database mounted.

Database opened.

SQL> show parameter pfile

NAME TYPE VALUE

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

spfile string

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string

4、干净的关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

5、修改参数文件名为 init_orcl1.ora

oracle@db10g:orcl$mv initorcl.ora initorcl1.ora

6、修改 oracle 用户环境变量 ORACLE_SID=orcl1

oracle@db10g:orcl1$pwd

/home/oracle

oracle@db10g:orcl$echo $ORACLE_SID

orcl

oracle@db10g:orcl$source .bash_profile

oracle@db10g:orcl1$echo $ORACLE_SID

orcl1

7、创建新实例使用的密码文件 orapworcl1

oracle@db10g:orcl1$orapwd file=orapworcl1 password=oracle entries=10

oracle@db10g:orcl1$ls

hc_orcl.dat initdw.ora init.ora initorcl1.ora lkORCL orapworcl orapworcl1

snapcf_orcl.f spfileorcl.ora.bak

8、启动数据库验证当前实例名已经修改为 orcl1

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 2095704 bytes

Variable Size 88081832 bytes

***************一步步教你修改数据库名、实例名***************

墨天轮：https://www.modb.pro/u/4347

公众号：JiekeXu 之路

**********************************************

注：生产环境中进行如下操作之前，务必备份数据库且确保备份完整可用。

一、测试环境信息

操作系统版本：

SQL> !uname -a

Linux db10g 2.6.18-194.el5 #1 SMP Tue Mar 16 21:52:39 EDT 2010 x86_64 x86_64

x86_64 GNU/Linux

数据库版本：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

当前实例名：

SQL> select instance from v$thread;

INSTANCE

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

orcl

当前数据库名：

SQL> select name from v$database;

NAME

---------

ORCL

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string orcl

二、修改数据库实例(orcl-->orcl1)

修改实例名为 orcl1：

1、干净的关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

2、查看 spfileorcl.ora 中的内容创建 pfile initorcl.ora

oracle@db10g:orcl$cd $ORACLE_HOME/dbs

oracle@db10g:orcl$pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

oracle@db10g:orcl$mv spfileorcl.ora spfileorcl.ora.bak

oracle@db10g:orcl$strings spfileorcl.ora.bak

oracle@db10g:orcl$ls

alert_orcl.log hc_orcl.dat initdw.ora init.ora initorcl.ora lkORCL

orapworcl snapcf_orcl.f spfileorcl.ora.bak

oracle@db10g:orcl$cat initorcl.ora

orcl.__db_cache_size=150994944

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=109051904

orcl.__streams_pool_size=8388608

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

*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'

*.compatible='10.2.0.5.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/or

adata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

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

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=283115520

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

3、确认数据库使用 pfile 文件启动

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 2095704 bytes

Variable Size 125830568 bytes

Database Buffers 150994944 bytes

Redo Buffers 6291456 bytes

Database mounted.

Database opened.

SQL> show parameter pfile

NAME TYPE VALUE

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

spfile string

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string

4、干净的关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

5、修改参数文件名为 init_orcl1.ora

oracle@db10g:orcl$mv initorcl.ora initorcl1.ora

6、修改 oracle 用户环境变量 ORACLE_SID=orcl1

oracle@db10g:orcl1$pwd

/home/oracle

oracle@db10g:orcl$echo $ORACLE_SID

orcl

oracle@db10g:orcl$source .bash_profile

oracle@db10g:orcl1$echo $ORACLE_SID

orcl1

7、创建新实例使用的密码文件 orapworcl1

oracle@db10g:orcl1$orapwd file=orapworcl1 password=oracle entries=10

oracle@db10g:orcl1$ls

hc_orcl.dat initdw.ora init.ora initorcl1.ora lkORCL orapworcl orapworcl1

snapcf_orcl.f spfileorcl.ora.bak

8、启动数据库验证当前实例名已经修改为 orcl1

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 2095704 bytes

Variable Size 88081832 bytes

of 8

5墨值下载

oracle

关注

评论