环境介绍
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