星期五 六月 14, 2013

如何在ASM磁盘组中复制一个新的控制文件

     在一些情况下,可能需要增加控制文件的镜像文件,或者复制一个控制文件来替换原有的控制文件。比如一个控制文件损坏了,另外一个镜像的控制文件是好的,这种情况下可以按照下面的方法复制一个控制文件来替换原有的损坏的控制文件。

     在下面的例子中,假设控制文件+DATA/rac11g2/controlfile/current.260.815940667上发生了坏块,我们需要用没有问题的控制文件+RECO/rac11g2/controlfile/current.256.815940675来复制一个新的控制文件来替换已损坏的这个控制文件。

Wed May 23 14:11:50 2013
Read from controlfile member '+DATA/rac11g2/controlfile/current.260.815940667' has found a corrupted block (blk# 4, seq# 0) Hex
dump of (file 0, block 4) in trace file /u01/app/oracle/admin/racdb/bdump/racdb1_lmon_332342.trc
Corrupt block relative dba: 0x00000004 (file 0, block 4)
Bad check value found during control file block read
Data in bad block:
type: 21 format: 2 rdba: 0x00000004
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x00001501
check value in block header: 0xdcca
computed block checksum: 0x2

   下面是针对控制文件在ASM磁盘组中的情况,演示如何在ASM磁盘组上复制一个控制文件来替换原来的一个控制文件。主要用到了rman 的命令 restore controlfile to '...' from '...' 。

   下面的大部分命令都是连接到数据库实例上执行,但是asmcmd命令需要设置环境变量ORACLE_SID和ORACLE_HOME为ASM对应的值来执行。针对11.2,需要注意是用grid用户还是oracle用户来执行。

1. 首先备份一下数据库实例的spfile(不是ASM实例):
SQL>create pfile='/tmp/pfile' from spfile;

如果没有使用spfile,直接备份pfile即可。

2.确定数据库实例当前的controlfile的文件名:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/rac11g2/controlfile/current.260.815940667
+RECO/rac11g2/controlfile/current.256.815940675

3. 停止使用这个控制文件的数据库实例(ASM 要保持启动)。如果是RAC,需要停止所有节点上使用这个控制文件的数据库实例

4. 启动数据库实例到nomount。对于RAC系统,只需启动任何一个实例,比如实例1。注意连接的是数据库实例,不是ASM实例。一般使用oracle用户登录到sqlplus。

su - oracle
sqlplus / as sysdba
SQL>startup nomount;

5. 使用RMAN来复制一个controlfile,源文件是'+RECO/rac11g2/controlfile/current.256.815940675',目标是建立到磁盘组'+DATA'中。


su - oracle
$ rman target /
RMAN> restore controlfile to '+DATA' from '+RECO/rac11g2/controlfile/current.256.815940675';

Starting restore at 06-JUN-13
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
Finished restore at 06-JUN-13

如果需要复制多个控制文件,重复这步即可。

注意,如果实例没有停止,那么执行上面的命令会报错:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/13/2013 20:53:12
ORA-19607: +RECO/racdb/controlfile/current.256.783272801 is an active control file
ORA-19600: input file is control file  (+RECO/racdb/controlfile/current.256.783272801)
ORA-19601: output file is control file  (+DATA)

6. 利用asmcmd 找到新建的controlfile的名字:
如果是11.2,一般会使用grid用户来执行asmcmd,如果是10g, 11.1,一般还是使用oracle用户。
但是不论是哪个用户,都要确保ORACLE_SID设置为了ASM实例,并且保证ORACLE_HOME设置为了ASM实例所在的ORACLE_HOME。

$export ORACLE_SID=+ASM1 <==设置为ASM实例
$export ORACLE_HOME=...

$asmcmd
ASMCMD> cd +DATA/rac11g2/controlfile
ASMCMD> ls
ASM
Current.260.815940667 <==旧的
current.268.817385067 <==新的

7. 设置数据库实例的初始化参数来使用新的控制文件:
export ORACLE_SID=RAC11G22 <==要设置为数据库实例
sqlplus / as sysdba
SQL>alter system set control_files='+DATA/RAC11G2/CONTROLFILE/Current.268.817385067','RECO/RAC11G2/CONTROLFILE/Current.256.815940675' scope=spfile id='*';

注意,如果有多个控制文件,它们要分别用''括起来,否则执行上面的命令会报错。

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/rac11g2/controlfile/curr <===这时还是旧的值
                                                ent.260.815940667, +RECO/rac11
                                                g2/controlfile/current.256.815
                                                940675
8.  重启实例:
SQL>shutdown immediate
SQL>startup
SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/rac11g2/controlfile/curr
                                                ent.268.817385067, +RECO/rac11 <==已经设置为了新的
                                                g2/controlfile/current.256.815
                                                940675

SQL> select name from gv$controlfile;

NAME
--------------------------------------------------------------------------------
+DATA/rac11g2/controlfile/current.268.817385067 <==用了新的
+RECO/rac11g2/controlfile/current.256.815940675

9. 对于RAC,启动其它节点的数据库实例。

参考MOS文档:
How To Move Controlfile To ASM [ID 468458.1]
How to duplicate a controlfile when ASM is involved [ID 345180.1]


参与此主题的后续讨论,可以访问我们的中文社区,跟帖“分享: 如何在ASM磁盘组中复制一个新的控制文件? "

星期二 一月 29, 2013

如何重建RAC的控制文件?

在下面的一些情况下,可能需要重建控制文件:

o 所有控制文件都已损坏或丢失
o 没有针对控制文件的备份或者备份已损坏
o 或者在一些情况下,Oracle Support建议这么做

下面是针对RAC环境下重建控制文件的具体过程, 包括两个例子。一个是以noresetlogs模式来重建控制文件,一个是以resetlogs模式来重建控制文件。如果redo logs都存在而且没有被损坏,那么可以采用noresetlogs。 使用resetlogs会将所有redo log清空而且重置log sequence为1.

在RAC上重建控制文件与单实例有一些小区别: 在重建控制文件前必须设置cluster_database=false,而且只启动一个实例来执行操作,否则会报错
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode


重建完控制文件后,需要再将cluster_database设为true.


TESTCASE1
---------------------------
用noresetlog模式重建控制文件


过程:

1.首先生成
重建控制文件的脚本:
SQL> alter database backup controlfile to trace;

Database altered.

2. 所生成的控制文件的脚本会在udump下:

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace

数据库的Alert log中也会详细输出这个文件的路径和名字:
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/RACDB1_ora_10076.trc


(注意: 例子中使用到的具体脚本,比如创建控制文件和添加临时数据文件的命令都在上面生成的trace文件中,其它步骤和命令也基本都在这个trace中)。

3. 停止所有数据库实例:
[oracle@rac1 trace]$ srvctl stop database -d RACDB


4. 用noresetlog重建控制文件:
[oracle@rac1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 8 11:23:44 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount; <==启动一个实例
ORACLE instance started.

Total System Global Area  739065856 bytes
Fixed Size                  2232032 bytes
Variable Size             549454112 bytes
Database Buffers          184549376 bytes
Redo Buffers                2830336 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS  ARCHIVELOG
 2      MAXLOGFILES 192
 3      MAXLOGMEMBERS 3
 4      MAXDATAFILES 1024
 5      MAXINSTANCES 32
 6      MAXLOGHISTORY 292
 7  LOGFILE
 8    GROUP 1 (
 9      '+DATA/racdb/onlinelog/group_1.261.783272805',
10      '+RECO/racdb/onlinelog/group_1.257.783272807'
11    ) SIZE 50M BLOCKSIZE 512,
12    GROUP 2 (
13      '+DATA/racdb/onlinelog/group_2.262.783272807',
14      '+RECO/racdb/onlinelog/group_2.258.783272809'
15    ) SIZE 50M BLOCKSIZE 512,
16    GROUP 3 (
17      '+DATA/racdb/onlinelog/group_3.269.804115405',
18      '+RECO/racdb/onlinelog/group_3.261.804115405'
19    ) SIZE 50M BLOCKSIZE 512,
20    GROUP 4 (
21      '+DATA/racdb/onlinelog/group_4.270.804115405',
22      '+RECO/racdb/onlinelog/group_4.263.804115407'
23    ) SIZE 50M BLOCKSIZE 512
24  -- STANDBY LOGFILE
25  DATAFILE
26    '+DATA/racdb/datafile/system.256.783272707',
27    '+DATA/racdb/datafile/sysaux.257.783272707',
28    '+DATA/racdb/datafile/undotbs1.258.783272707',
29    '+DATA/racdb/datafile/users.259.783272707',
30    '+DATA/racdb/datafile/example.264.783272831',
31    '+DATA/racdb/datafile/undotbs2.265.783273081'
32  CHARACTER SET AL32UTF8
33  ;
CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode  <============由于没有设置cluster_database=false,所以报错

需要将设置cluster_database=false:
SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  739065856 bytes
Fixed Size                  2232032 bytes
Variable Size             549454112 bytes
Database Buffers          184549376 bytes
Redo Buffers                2830336 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS  ARCHIVELOG
 2      MAXLOGFILES 192
 3      MAXLOGMEMBERS 3
 4      MAXDATAFILES 1024
 5      MAXINSTANCES 32
 6      MAXLOGHISTORY 292
 7  LOGFILE
 8    GROUP 1 (
 9      '+DATA/racdb/onlinelog/group_1.261.783272805',
10      '+RECO/racdb/onlinelog/group_1.257.783272807'
11    ) SIZE 50M BLOCKSIZE 512,
12    GROUP 2 (
13      '+DATA/racdb/onlinelog/group_2.262.783272807',
14      '+RECO/racdb/onlinelog/group_2.258.783272809'
15    ) SIZE 50M BLOCKSIZE 512,
16    GROUP 3 (
17      '+DATA/racdb/onlinelog/group_3.269.804115405',
18      '+RECO/racdb/onlinelog/group_3.261.804115405'
19    ) SIZE 50M BLOCKSIZE 512,
20    GROUP 4 (
21      '+DATA/racdb/onlinelog/group_4.270.804115405',
22      '+RECO/racdb/onlinelog/group_4.263.804115407'
23    ) SIZE 50M BLOCKSIZE 512
24  -- STANDBY LOGFILE
25  DATAFILE
26    '+DATA/racdb/datafile/system.256.783272707',
27    '+DATA/racdb/datafile/sysaux.257.783272707',
28    '+DATA/racdb/datafile/undotbs1.258.783272707',
29    '+DATA/racdb/datafile/users.259.783272707',
30    '+DATA/racdb/datafile/example.264.783272831',
31    '+DATA/racdb/datafile/undotbs2.265.783273081'
32  CHARACTER SET AL32UTF8
33  ;

Control file created.

SQL> select * from v$log; <===确认redo log,thread#是正确的

   GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
        1          1         11   52428800        512          2 NO
CURRENT                6876599 08-JAN-13   2.8147E+14

        2          1        10   52428800        512          2 NO
INACTIVE               6825446 07-JAN-13      6876599 08-JAN-13

        3          2          7   52428800        512          2 NO
INACTIVE               6877338 08-JAN-13      6960724 08-JAN-13


   GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
        4          2        6   52428800        512          2 NO
INACTIVE               6815353 07-JAN-13      6877338 08-JAN-13

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> alter system  archive log all;

System altered.

SQL> alter database open;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/racdb/tempfile/temp.263.783272821'
 2       SIZE 39845888  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M; 《==别忘了添加原来的临时文件到临时表空间中

Tablespace altered.

6.将cluster_database设为true :
SQL> alter system set cluster_database=true scope=spfile;

System altered.

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

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

7. 启动所有实例:
[oracle@rac1 trace]$ srvctl start database -d RACDB
[oracle@rac1 trace]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2

TESTCASE2
---------------------------
用resetlogs模式重建控制文件


Test Process:

1.首先生成
重建控制文件的脚本:
SQL> alter database backup controlfile to trace;

Database altered.

2. 数据库的Alert log中也会详细输出这个文件的路径和名字:
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/RACDB1_ora_5649.trc

3. 设置cluster_database=false:
SQL> alter system set cluster_database=false scope=spfile;
System altered.

否则,在重建控制文件的时候会报下面的错误:

CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

4. 停止所有数据库实例:
[oracle@rac1 trace]$ srvctl stop database -d RACDB
[oracle@rac1 trace]$ srvctl status database -d RACDB
Instance RACDB1 is not running on node rac1
Instance RACDB2 is not running on node rac2

5. 用resetlogs模式重建控制文件:

[oracle@rac1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 8 12:45:25 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  739065856 bytes
Fixed Size                  2232032 bytes
Variable Size             549454112 bytes
Database Buffers          184549376 bytes
Redo Buffers                2830336 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG
 2      MAXLOGFILES 192
 3      MAXLOGMEMBERS 3
 4      MAXDATAFILES 1024
 5      MAXINSTANCES 32
 6      MAXLOGHISTORY 292
 7  LOGFILE
 8    GROUP 1 (
 9      '+DATA/racdb/onlinelog/group_1.261.783272805',
10      '+RECO/racdb/onlinelog/group_1.257.783272807'
11    ) SIZE 50M BLOCKSIZE 512,
12    GROUP 2 (
13      '+DATA/racdb/onlinelog/group_2.262.783272807',
14      '+RECO/racdb/onlinelog/group_2.258.783272809'
15    ) SIZE 50M BLOCKSIZE 512
16  -- STANDBY LOGFILE
17  DATAFILE
18    '+DATA/racdb/datafile/system.256.783272707',
19    '+DATA/racdb/datafile/sysaux.257.783272707',
20    '+DATA/racdb/datafile/undotbs1.258.783272707',
21    '+DATA/racdb/datafile/users.259.783272707',
22    '+DATA/racdb/datafile/example.264.783272831',
23    '+DATA/racdb/datafile/undotbs2.265.783273081'
24  CHARACTER SET AL32UTF8
25  ;

Control file created.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;  <=========必须使用UNTIL CANCEL,否则数据库无法open
ORA-00279: change 6976933 generated at 01/08/2013 12:45:12 needed for thread 1
ORA-00289: suggestion : +RECO
ORA-00280: change 6976933 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL 
Media recovery cancelled.


接下来需要��加其他thread,因为用resetlogs重建controlfile只是增加了thread为1的redo log:


SQL> ALTER DATABASE ADD LOGFILE THREAD 2
 2    GROUP 3 (
 3      '+DATA/racdb/onlinelog/group_3.269.804115405',
 4      '+RECO/racdb/onlinelog/group_3.261.804115405'
 5    ) SIZE 50M BLOCKSIZE 512 REUSE,
 6    GROUP 4 (
 7      '+DATA/racdb/onlinelog/group_4.270.804115405',
 8      '+RECO/racdb/onlinelog/group_4.263.804115407'
 9    ) SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD LOGFILE THREAD 2
*
ERROR at line 1:
ORA-01276: Cannot add file +DATA/racdb/onlinelog/group_3.269.804115405.  File
has an Oracle Managed Files file name. 


对于ASM,使用了OMF命名规则时不能指定具体的文件名,只需要指定diskgroup名即可:

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
 2    GROUP 3 (
 3      '+DATA',
 4      '+RECO'
 5    ) SIZE 50M BLOCKSIZE 512 REUSE,
 6    GROUP 4 (
 7      '+DATA',
 8      '+RECO'
 9    ) SIZE 50M BLOCKSIZE 512 REUSE;

Database altered.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/racdb/tempfile/temp.263.783272821'
 2           SIZE 39845888  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.

6. 设置cluster_database=true :
SQL> alter system set cluster_database=true scope=spfile;

System altered.

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

7. 启动所有实例:

[oracle@rac1 trace]$ srvctl start database -d RACDB
[oracle@rac1 trace]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2 

在My Oracle Support中有针对重建控制文件的一些说明:
How to Recreate a Controlfile (Doc ID 735106.1)

About

本博客由Oracle全球技术支持中国区的工程师维护。为中文用户提供数据库相关的技术支持信息,包括常用的诊断工具、诊断方法、产品新特性、案例分析等。此外,MOS也陆续推出各类中文内容:技术通讯统一发布在Note 1529795.1 中,中文文档列表更新在Note 1533057.1 中,网上讲座请查看MOS文档 1456176.1,在"Archived"中可以下载历史的录音和文档。

Search

Archives
« 四月 2014
星期日星期一星期二星期三星期四星期五星期六
  
1
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
今天