【修改数据库实例名】

最新推荐文章于 2024-05-09 14:32:20 发布

祈福0363

最新推荐文章于 2024-05-09 14:32:20 发布

阅读量724

点赞数

文章标签：数据库 oracle dba

本文链接：https://blog.csdn.net/m0_46535790/article/details/128194687

版权

修改Oracle实例名

环境介绍
- 变更准备
- 重点

环境介绍

Oracle版本RAC+ASM：11.2.0.4.0
linux版本：Red Hat Enterprise Linux Server release 6.5

1.停业务
2.将Oracle RAC关闭集群模式，同时关闭其中一个节点

变更准备

1.找到spfile参数文件路径

[root@db02 ~]# su - oracle
[oracle@db02:/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 24 04:18:46 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> show parameters pfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/app/oracle/product/11.
                                                 2.0/dbs/spfiledb01.ora

2.复制一份参数文件，将其中实例名相关参数进行修改

SQL> create pfile='/home/oracle/pfile1205.ora' from spfile='/oracle/app/oracle/product/11.2.0/dbs/spfiledb01.ora';
File created.

源参数文件

db01.__db_cache_size=411041792
db01.__java_pool_size=4194304
db01.__large_pool_size=4194304
db01.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
db01.__pga_aggregate_target=314572800
db01.__sga_target=629145600
db01.__shared_io_pool_size=0
db01.__shared_pool_size=192937984
db01.__streams_pool_size=4194304
*.audit_file_dest='/oracle/app/oracle/admin/db01/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/oradata/db01/control01.ctl','/oracle/oradata/db01/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='db01'
*.diagnostic_dest='/oracle/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=314572800
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=629145600
*.undo_tablespace='UNDOTBS1'

修改后参数文件

db01.__db_cache_size=411041792
db01.__java_pool_size=4194304
db01.__large_pool_size=4194304
db01.__oracle_base='/oracle/app/oracle'#ORACLE_BASE set from environment
db01.__pga_aggregate_target=314572800
db01.__sga_target=629145600
db01.__shared_io_pool_size=0
db01.__shared_pool_size=192937984
db01.__streams_pool_size=4194304
*.audit_file_dest='/oracle/app/oracle/admin/db02/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/oradata/db02/control01.ctl','/oracle/oradata/db02/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='db02'
*.diagnostic_dest='/oracle/app/oracle'
*.open_cursors=300
*.pga_aggregate_target=314572800
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=629145600
*.undo_tablespace='UNDOTBS1'

3.备份控制文件，并修改相关参数

SQL> alter database backup controlfile to trace as '/home/oracle/controlfile123.ora';
Database altered.

修改控制文件中实例名

CREATE CONTROLFILE REUSE SET DATABASE "db02" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 2048
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/oradata/db02/redo01.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/oracle/oradata/db02/redo02.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 3 '/oracle/oradata/db02/redo03.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 4 '/oracle/oradata/db02/redo04.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 5 '/oracle/oradata/db02/redo05.log'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/db02/system01.dbf',
  '/oracle/oradata/db02/sysaux01.dbf',
  '/oracle/oradata/db02/undotbs01.dbf',
  '/oracle/oradata/db02/users01.dbf',
  '/oracle/oradata/db02/itpux.dbf'
CHARACTER SET ZHS16GBK
;

4.关闭数据库实例

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

5.修改参数文件中涉及的目录结构

####01修改数据文件、日志文件目录
[oracle@db02:/home/oracle]$cd /oracle/oradata/
[oracle@db02:/oracle/oradata]$ll
total 4
drwxr-x---. 2 oracle oinstall 4096 Nov 24 02:15 db01
[oracle@db02:/oracle/oradata]$mv db01 db02
[oracle@db02:/oracle/oradata]$ll
total 4
drwxr-x---. 2 oracle oinstall 4096 Nov 24 02:15 db02

####02修改dump日志文件目录
[oracle@db02:/home/oracle]$cd /oracle/app/oracle/admin/
[oracle@db02:/oracle/app/oracle/admin]$ll
total 4
drwxr-x---. 6 oracle oinstall 4096 Nov 24 01:24 db01
[oracle@db02:/oracle/app/oracle/admin]$mv db01 db02
[oracle@db02:/oracle/app/oracle/admin]$ll
total 4
drwxr-x---. 6 oracle oinstall 4096 Nov 24 01:24 db02
[oracle@db02:/oracle/app/oracle/admin]$cd /home/oracle

6.使用修改的参数文件启动数据库实例到nomount模式，并替换库中spfile文件

SQL> startup nomount pfile='/home/oracle/pfile1205.ora';
ORACLE instance started.
Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             205521960 bytes
Database Buffers          411041792 bytes
Redo Buffers                7507968 bytes

替换库中原spfile文件

SQL> create spfile='/oracle/app/oracle/product/11.2.0/dbs/spfiledb02.ora' from pfile='/home/oracle/pfile1205.ora';
File created.

关闭数据库实例

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

7.修改实例的环境变量

[oracle@db02:/home/oracle]$vi ~/.bash_profile
# Oracle Settings oracle
PS1="[`whoami`@`hostname`:"'$PWD]$'
export PS1
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
export ORACLE_UNQNAME=db02
export LANG=en_US
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0; export ORACLE_HOME
ORACLE_SID=db02; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

生效环境变量

[oracle@db02:/home/oracle]$source ~/.bash_profile
[oracle@db02:/home/oracle]$echo $ORACLE_SID
db02

8.启动数据库实例到nomount模式，并创建控制文件

[oracle@db02:/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 24 05:14:49 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area  626327552 bytes
Fixed Size                  2255832 bytes
Variable Size             180356136 bytes
Database Buffers          436207616 bytes
Redo Buffers                7507968 bytes

SQL> CREATE CONTROLFILE REUSE SET DATABASE "db02" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
  2    3      MAXLOGMEMBERS 3
    MAXDATAFILES 2048
    MAXINSTANCES 8
  4    5    6      MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle/oradata/db02/redo01.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/oracle/oradata/db02/redo02.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 3 '/oracle/oradata/db02/redo03.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 4 '/oracle/oradata/db02/redo04.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 5 '/oracle/oradata/db02/redo05.log'  SIZE 100M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oracle/oradata/db02/system01.dbf',
  '/oracle/oradata/db02/sysaux01.dbf',
  '/oracle/oradata/db02/undotbs01.dbf',
  '/oracle/oradata/db02/users01.dbf',
  '/oracle/oradata/db02/itpux.dbf'
CHARACTER SET ZHS16GBK
;  7    8    9   10   11   12   13   14   15   16   17   18   19   20   21

Control file created.

9.启动数据库到open模式

SQL> alter database open resetlogs;

Database altered.

检查数据库状态和参数文件位置

SQL> select instance_name,status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
db02             OPEN
SQL>
SQL> show parameters pfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/app/oracle/product/11.
                                                 2.0/dbs/spfiledb02.ora

10.将RAC集群模式打开
11.启动另一个节点，观察集群状态

重点

备份的控制文件，修改数据库实例名后，再次创建会遇到以下报错

ORA-01503: CREATE CONTROLFILE failed
ORA-01161: database name DB01 in file header does not match given name of DB02
ORA-01110: data file 1: '/oracle/oradata/db02/system01.dbf'

解决方法：

####REUSE后面加SET，最后使用NOARCHIVELOG，即：
SQL> CREATE CONTROLFILE REUSE SET DATABASE "db02" RESETLOGS  NOARCHIVELOG

祈福0363

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
2
评论
【修改数据库实例名】

修改Oracle数据库实例名
复制链接

扫一扫