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

JiekeXu

1873

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

Database Buffers 188743680 bytes

Redo Buffers 6291456 bytes

Database mounted.

Database opened.

SQL> select instance from v$thread;

INSTANCE

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

orcl1

SQL> select name from v$database;

NAME

---------

ORCL

SQL> show parameter instance_name

NAME TYPE VALUE

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

instance_name string orcl1

SQL> show parameter db_name

NAME TYPE VALUE

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

db_name string orcl

三、修改数据库名为 orcl1

1、切换归档日志，备份控制文件

SQL> alter system switch logfile;

System altered.

SQL> alter database backup controlfile to trace resetlogs;

Database altered.

备份控制文件生成的 trace 文件内容如下：

oracle@db10g:orcl1$pwd

/u01/app/oracle/admin/orcl/udump

oracle@db10g:orcl1$cat orcl1_ora_7189.trc

/u01/app/oracle/admin/orcl/udump/orcl1_ora_7189.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

System name: Linux

Node name: db10g

Release: 2.6.18-194.el5

Version: #1 SMP Tue Mar 16 21:52:39 EDT 2010

Machine: x86_64

Instance name: orcl1

Redo thread mounted by this instance: 1

Oracle process number: 15

Unix process pid: 7189, image: oracle@db10g (TNS V1-V3)

*** ACTION NAME:() 2016-05-24 11:20:52.560

*** MODULE NAME:(sqlplus@db10g (TNS V1-V3)) 2016-05-24 11:20:52.560

*** SERVICE NAME:(SYS$USERS) 2016-05-24 11:20:52.560

*** SESSION ID:(159.3) 2016-05-24 11:20:52.560

DBRM(kskinitrm) cpu_count (fudge enabled): old(0) -> new(1)

kwqmnich: current time:: 3: 20: 52

kwqmnich: instance no 0 check_only flag 1

kwqmnich: initialized job cache structure

*** 2016-05-24 11:26:40.398

-- The following are current System-scope REDO Log Archival related

-- parameters and can be included in the database initialization file.

-- LOG_ARCHIVE_DEST=''

-- LOG_ARCHIVE_DUPLEX_DEST=''

of 8

5墨值下载

oracle

相关文档

评论