11g_RAC_ASM增加删除控制文件

 

/**********************
一:准备工作
**********************/
1、查看当前控制文件数量及位置、名称
方法1:
SQL> show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————————–
control_files string +DATA/test/controlfile/current.284.829354161

SQL>
方法2:
[grid@rac1 ~]$ asmcmd
ASMCMD> cd DATA/TEST/CONTROLFILE
ASMCMD> ls
Current.284.829354161
ASMCMD>
2、备份参数文件
节点1:生成最新pfile文件,关闭数据库
SQL> create pfile=’/tmp/initTEST11021.ora’ from spfile;

File created.

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

节点2:生成最新pfile文件,关闭数据库
SQL> create pfile=’/tmp/initTEST11021.ora’ from spfile;

File created.

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

/**********************
二:创建控制文件
**********************/
节点1:启动到nomount,RMAN复制控制文件(注意,由于是OMF方式管理,所以生成的控制文件的名称会有所变化)
restore controlfile to ‘+DATA/test/controlfile/current.285.829354161′ from ‘+DATA/test/controlfile/current.284.829354161′;
restore controlfile to ‘+DATA/test/controlfile/current.286.829354161′ from ‘+DATA/test/controlfile/current.284.829354161′;

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Oct 21 06:22:47 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: TEST (not mounted)

RMAN> restore controlfile to ‘+DATA/test/controlfile/current.285.829354161′ from ‘+DATA/test/controlfile/current.284.829354161′;

Starting restore at 21-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 instance=TEST1 device type=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 21-OCT-13

RMAN> restore controlfile to ‘+DATA/test/controlfile/current.286.829354161′ from ‘+DATA/test/controlfile/current.284.829354161′;

Starting restore at 21-OCT-13
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 21-OCT-13

RMAN> exit

Recovery Manager complete.
[oracle@rac1 ~]$
–查看生成的控制文件(哪个节点都可以,利用asmcmd查看)
ASMCMD> ls
Current.284.829354161
current.292.829376851
current.293.829376861
ASMCMD>

/**********************
三:修改参数文件,使新增控制文件生效
**********************/
方法(1):利用命令修改(此时节点1的状态为nomount,如果不是,请将节点1启动到nomount)
SQL> select INST_ID,HOST_NAME,STATUS from gv$instance;

INST_ID HOST_NAM STATUS
———- ——– ————
1 rac1 STARTED

SQL>
SQL> alter system set control_files=’+DATA/test/controlfile/current.284.829354161′,’+DATA/test/controlfile/current.292.829376851′,’+DATA/test/controlfile/current.293.829376861′ scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 627732480 bytes
Fixed Size 1338336 bytes
Variable Size 247464992 bytes
Database Buffers 373293056 bytes
Redo Buffers 5636096 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string +DATA/test/controlfile/current
.284.829354161, +DATA/test/con
trolfile/current.292.829376851
, +DATA/test/controlfile/curre
nt.293.829376861
SQL>

节点2:由于节点1修改参数使用了全局修改,所以,节点2直接启动就可以。当然也可以先启动到nomount验证一下。
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 627732480 bytes
Fixed Size 1338336 bytes
Variable Size 247464992 bytes
Database Buffers 373293056 bytes
Redo Buffers 5636096 bytes
SQL>
SQL> show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string +DATA/test/controlfile/current
.284.829354161, +DATA/test/con
trolfile/current.292.829376851
, +DATA/test/controlfile/curre
nt.293.829376861
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL>
SQL> select INST_ID,HOST_NAME,STATUS from gv$instance;

INST_ID HOST_NAME STATUS
———- ———- ————
2 rac2 OPEN
1 rac1 OPEN

SQL>

方法(2):修改pfile文件(用这种方法删除一个控制文件)

将备份的pfile文件打开,修改control_files,然后再通过命令create spfile from pfile重新创建spfile。这种方式两个节点都要执行。

1)查看数据库的状态,此时数据库为open
SQL> select inst_id,name,open_mode from gv$database;

INST_ID NAME OPEN_MODE
———- ——— ——————–
1 TEST READ WRITE
2 TEST READ WRITE

SQL> select inst_id,host_name,status from gv$instance;

INST_ID HOST_NAME STATUS
———- ———- ————
2 rac2 OPEN
1 rac1 OPEN

SQL>

2)查看控制文件数量及位置(两个节点都要验证一下)
SQL> show parameter control_files

NAME TYPE VALUE
———————————— ———– ——————————
control_files string +DATA/test/controlfile/current
.284.829354161, +DATA/test/con
trolfile/current.292.829376851
, +DATA/test/controlfile/curre
nt.293.829376861
SQL>

3)创建pfile文件(两个节点全部创建)
–节点1:
SQL> create pfile=’/tmp/initTEST1.ora’ from spfile;

File created.

SQL>
–节点2:
SQL> create pfile=’/tmp/initTEST2.ora’ from spfile;

File created.

SQL>

4)修改pfile文件(注:节点1、节点2的pfile文件全部修改)
–节点1
[root@rac1 ~]# vi /tmp/initTEST1.ora
–将下面内容
*.control_files=’+DATA/test/controlfile/current.284.829354161′,’+DATA/test/controlfile/current.292.829376851′,’+DATA/test/controlfile/current.293.829376861′
–改为
*.control_files=’+DATA/test/controlfile/current.284.829354161′,’+DATA/test/controlfile/current.292.829376851′

–节点2
[root@rac1 ~]# vi /tmp/initTEST2.ora
*.control_files=’+DATA/test/controlfile/current.284.829354161′,’+DATA/test/controlfile/current.292.829376851′,’+DATA/test/controlfile/current.293.829376861′
–改为
*.control_files=’+DATA/test/controlfile/current.284.829354161′,’+DATA/test/controlfile/current.292.829376851′

5)将两个节点的数据库shutdown,通过pfile生成spfile
–节点1
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> create spfile from pfile=’/tmp/initTEST1.ora';

File created.

SQL>
–节点2
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> create spfile from pfile=’/tmp/initTEST2.ora';

File created.

SQL>
6)将两个节点startup
SQL> startup
ORACLE instance started.

Total System Global Area 627732480 bytes
Fixed Size 1338336 bytes
Variable Size 247464992 bytes
Database Buffers 373293056 bytes
Redo Buffers 5636096 bytes
Database mounted.
Database opened.
SQL>