ORACLE 11g将数据库移动到不同的ASM磁盘组/修改ASM磁盘组的冗余属性

原创 2013年12月05日 17:12:44

ORACLE 11g将数据库移动到不同的ASM磁盘组

(修改ASM磁盘组的冗余属性)


ORACLE使用ASM存储,建库时磁盘组的冗余属性使用了EXTERN,现在想将磁盘组改为NORMAL,以下是具体步骤:

1.  新建一个期望属性的新磁盘组

[root@Oracle-LAB~]# su - grid

[grid@Oracle-LAB ~]$ asmca

或者用命令:

[grid@Oracle-LAB~]$ sqlplus / as sysasm

SQL >  CREATE DISKGROUP DATA NORMAL REDUNDANCY DISK '/dev/raw/raw1'SIZE 5120 M DISK '/dev/raw/raw2'SIZE 5120 M DISK '/dev/raw/raw3' SIZE 5120 M;

2.  检查磁盘组

[grid@Oracle-LAB~]$ sqlplus / as sysasm

SQL> select state,name,type from v$asm_diskgroup;

STATE      NAME                                              TYPE

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

MOUNTED     DATA                                      EXTERN

MOUNTED     FRA                                       EXTERN

MOUNTED    DATA01                                  NORMAL

3.  备份现有的数据库

[oracle@Oracle-LAB ~]$ sqlplus /nolog

SQL> conn /as sysdba

SQL> show parameter db_name

NAME     TYPE              VALUE

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

db_name   string  ORCL

查看当前控制文件的Value

SQL> show parameter control

NAME                                                                          TYPE                     VALUE

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

control_file_record_keep_time                           integer  7

control_files                                                             string  +DATA/orcl/controlfile/current

                                                                                                                 .260.833734379

control_management_pack_access           string     DIAGNOSTIC+TUNING

在新磁盘组生成新控制文件有两种方法(推荐方法二):

方法一:通过备份现有控制文件来生成:

备份控制文件到新磁盘组

SQL> alter database backup controlfile to '+DATA01';

Database altered.

       查看备份后的控制文件:

[root@Oracle-LABsoftware]# su - grid

[grid@Oracle-LAB ~]$ asmcmd

ASMCMD> ls +DATA01/ORCL/CONTROLFILE/

Backup.256.833381229

       设定初始化参数:

SQL> alter system setcontrol_files='+DATA01/ORCL/CONTROLFILE/Backup.256.833381229' scope=spfile;

System altered.

关闭数据库并启动至nomount状态(用SQLRAMAN

[oracle@Oracle-LAB ~]$ rman target /

RMAN> shutdown immediate    #如果是RAC,需要到另外的节点执行SHUTDOWN命令

using targetdatabase control file instead of recovery catalog

database closed

databasedismounted

Oracle instance shut down

RMAN> startup nomount

connected totarget database (not started)

Oracle instancestarted

Total SystemGlobal Area    1653518336 bytes

Fixed Size                     2228904 bytes

VariableSize                973081944 bytes

DatabaseBuffers             671088640 bytes

Redo Buffers                   7118848 bytes

从原控制文件生成现有控制文件:

RMAN> restore controlfile from'+DATA/orcl/controlfile/current.259.833372337';

Starting restoreat 05-DEC-13

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=13 device type=DISK

channelORA_DISK_1: copied control file copy

output file name=+DATA01/orcl/controlfile/backup.256.833381229

Finished restore at 05-DEC-13

方法二:使用添加控制文件的方法:

SQL> alter system set control_files='+DATA/orcl/controlfile/current.260.833734379','+DATA01'scope=spfile;

       System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instanceshut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size                               2228904 bytes

Variable Size                        973081944 bytes

Database Buffers               671088640 bytes

Redo Buffers                        7118848 bytes

SQL> quit

[oracle@Oracle-LAB~]$ rman target/

RMAN> restore controlfile from '+DATA/orcl/controlfile/current.260.833734379';

Starting restore at 09-DEC-13

using target database control file instead of recoverycatalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=135 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATA/orcl/controlfile/current.260.833734379

output filename=+DATA01/orcl/controlfile/current.256.833744103

Finished restore at 09-DEC-13

RMAN> quit       

Recovery Manager complete.

[oracle@Oracle-LAB~]$ sqlplus /nolog

SQL> conn /as sysdba

Connected.

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter control;

NAME                                                      TYPE           VALUE

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

control_file_record_keep_time        integer       7

control_files                                           string          +DATA/orcl/controlfile/current

                                                                                      .260.833734379,+DATA01/orcl/c

                                                                                      ontrolfile/current.256.833744103

control_management_pack_access                string          DIAGNOSTIC+TUNING

SQL> alter system setcontrol_files='+DATA01/orcl/controlfile/current.256.833744103' scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size                               2228904 bytes

Variable Size                        973081944 bytes

Database Buffers               671088640 bytes

Redo Buffers                         7118848 bytes

Database mounted.

Database opened.

SQL> show parameter control;

NAME                                                      TYPE           VALUE

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

control_file_record_keep_time        integer       7

control_files                                           string          +DATA01/orcl/controlfile/curre

                                                                                     nt.256.833744103

control_management_pack_access   string          DIAGNOSTIC+TUNING

 

将数据库启动到mount状态:

RMAN> shutdown immediate 

RMAN> startup nomount

RMAN> alter database mount ;

database mounted

released channel: ORA_DISK_1

启用RAMN工具,将数据库镜像备份到新磁盘组:

RMAN>backup as copy database format '+DATA01';

Starting backupat 05-DEC-13

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=13 device type=DISK

channelORA_DISK_1: starting datafile copy

input datafilefile number=00001 name=+DATA/orcl/datafile/system.264.833372265

output filename=+DATA01/orcl/datafile/system.257.833384045 tag=TAG20131205T153405 RECID=3STAMP=833384056

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channelORA_DISK_1: starting datafile copy

input datafilefile number=00002 name=+DATA/orcl/datafile/sysaux.263.833372265

output filename=+DATA01/orcl/datafile/sysaux.258.833384061 tag=TAG20131205T153405 RECID=4STAMP=833384069

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channelORA_DISK_1: starting datafile copy

input datafilefile number=00005 name=+DATA/orcl/datafile/example.268.833372347

output filename=+DATA01/orcl/datafile/example.259.833384075 tag=TAG20131205T153405 RECID=5STAMP=833384080

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

channelORA_DISK_1: starting datafile copy

input datafilefile number=00003 name=+DATA/orcl/datafile/undotbs1.267.833372265

output filename=+DATA01/orcl/datafile/undotbs1.260.833384083 tag=TAG20131205T153405RECID=6 STAMP=833384084

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channelORA_DISK_1: starting datafile copy

copying currentcontrol file

output filename=+DATA01/orcl/controlfile/backup.261.833384087 tag=TAG20131205T153405RECID=7 STAMP=833384086

channel ORA_DISK_1:datafile copy complete, elapsed time: 00:00:01

channelORA_DISK_1: starting datafile copy

input datafilefile number=00004 name=+DATA/orcl/datafile/users.269.833372265

output filename=+DATA01/orcl/datafile/users.262.833384087 tag=TAG20131205T153405 RECID=8STAMP=833384087

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

channelORA_DISK_1: starting full datafile backup set

channelORA_DISK_1: specifying datafile(s) in backup set

including currentSPFILE in backup set

channel ORA_DISK_1:starting piece 1 at 05-DEC-13

channelORA_DISK_1: finished piece 1 at 05-DEC-13

piecehandle=+DATA01/orcl/backupset/2013_12_05/nnsnf0_tag20131205t153405_0.263.833384089tag=TAG20131205T153405 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 05-DEC-13

                检查备份的数据库镜像

RMAN> list copy of database;

               

4.  切换到备份的数据库

RMAN> switch database to copy;

datafile 1switched to datafile copy"+DATA01/orcl/datafile/system.257.833384045"

datafile 2switched to datafile copy"+DATA01/orcl/datafile/sysaux.258.833384061"

datafile 3switched to datafile copy"+DATA01/orcl/datafile/undotbs1.260.833384083"

datafile 4switched to datafile copy "+DATA01/orcl/datafile/users.262.833384087"

datafile 5switched to datafile copy"+DATA01/orcl/datafile/example.259.833384075"

 

RMAN> alter database open;

database opened

                修改数据库新建文件的目标磁盘组

[oracle@Oracle-LAB ~]$ sqlplus "/assysdba"

SQL> alter system set db_create_file_dest='+DATA01';

System altered.

 

5.  在新数据库内建立新的TEMP表空间(TEMP表空间不会从旧的磁盘组中移到新磁盘组)

SQL> select name from v$tempfile;                    #查看现有的temp文件名

SQL> create bigfile temporary tablespace temp01 tempfile size 2M; 

Tablespacecreated.

SQL> alter database default temporary tablespace temp01;

Database altered.

SQL> drop tablespace temp;

Tablespace dropped.

 

6.  修改重做日志组

SQL> select * from v$log;                                  #查看有几个组

或:SQL>select group#,status from v$log;

  GROUP#       STATUS

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

1                  CURRENT

2                  INACTIVE

3                  INACTIVE

SQL> alter database add logfile member '+data01' to group 1;

Database altered.

SQL> alter database add logfile member '+data01' to group 2;

Database altered.

SQL> alter database add logfile member '+data01' to group 3;

Database altered.

SQL> select member from v$logfile;                   #查找logfile文件:

MEMBER

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

+DATA/orcl/onlinelog/group_3.256.833372341

+DATA/orcl/onlinelog/group_2.257.833372341

+DATA/orcl/onlinelog/group_1.258.833372341

+DATA01/orcl/onlinelog/group_1.265.833386551

+DATA01/orcl/onlinelog/group_2.266.833386565

+DATA01/orcl/onlinelog/group_3.267.833386569

6 rows selected.

SQL> alter system switch logfile;      #多用几次切换命令,切换一个循环

SQL> select group#,status from v$log;                                             #查看结果,找到INACTIVE的组。

  GROUP#       STATUS

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

1                  INACTIVE

2                  INACTIVE

3                 CURRENT

SQL> alter database drop logfile member'+DATA/orcl/onlinelog/group_1.258.833372341';

Database altered.

SQL> alter database drop logfile member'+DATA/orcl/onlinelog/group_2.257.833372341';

Database altered.

SQL> select group#,status from v$log;                                            

再次切换,释放Group_3,然后删除:

SQL> alter system switch logfile;

SQL> alter database drop logfile member'+DATA/orcl/onlinelog/group_3.256.833372341';

Database altered.

SQL> select * from v$logfile;                                 #检查结果

 

7.  迁移参数配置

迁移数据库配置:

[oracle@Oracle-LAB ~]$ sqlplus /nolog

SQL> conn /assysdba

Connected.

SQL> create pfile='$ORACLE_HOME/dbs/init_ORCL.ora' from spfile;

File created.

SQL> shutdown immediate;                              

Database closed.

Databasedismounted.

ORACLE instanceshut down.

SQL> startup pfile='$ORACLE_HOME/dbs/init_ORCL.ora';

ORACLE instancestarted.

Total SystemGlobal Area 1653518336 bytes

Fixed Size                               2228904 bytes

Variable Size                        989859160 bytes

Database Buffers              654311424 bytes

Redo Buffers                         7118848 bytes

Database mounted.

Database opened.

SQL> create spfile='+DATA01/orcl/spfileORCL.ora' frompfile='$ORACLE_HOME/dbs/init_ORCL.ora';

File created.

SQL> shutdown immediate;                              

Database closed.

Databasedismounted.

ORACLE instanceshut down.

[oracle@Oracle-LABdbs]$vi initORCL.ora

SPFILE='+DATA/ORCL/spfileORCL.ora'改为SPFILE='+DATA01/ORCL/spfileORCL.ora',并保存退出。

SQL> startup

迁移ASM配置:

[grid@Oracle-LAB ~]$ sqlplus / assysasm

SQL> create pfile='$ORACLE_HOME/dbs/init_ORCL.ora' from spfile;

File created.

SQL> shutdown immediate;

SQL> startup pfile='$ORACLE_HOME/dbs/init_ORCL.ora';

ASM instancestarted

Total SystemGlobal Area  283930624 bytes

Fixed Size                               2227664 bytes

Variable Size                        256537136 bytes

ASM Cache                           25165824 bytes

ASM diskgroupsmounted

SQL> create spfile='+DATA01' from pfile='$ORACLE_HOME/dbs/init_ORCL.ora';

`File created.

SQL> shutdown immediate;

SQL> startup

 

8.  全面检查

 

SQL> show parameter pfile;(分别在GRIDORACLE帐号下查看)

SQL> select name from v$controlfile

SQL> select name from v$datafile

SQL> select name from v$tempfile

SQL> select member from v$logfile

SQL> select filename from v$block_change_tracking

SQL> select name from v$flashback_database_logfile;

 


版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

oracle 11g R2 单实例 ASM 数据库数据磁盘组迁移

oracle 11g R2 单实例 ASM 数据库数据磁盘组迁移      在使用ASM作为数据库存储的时候,可能往往我们会因为某些原因,会考虑将数据的存储数据迁移到一个新的磁盘组。 例如:创建数据...

Oracle 11g在ASM磁盘组上添加控制文件

控制文件(Control File)是Oracle的物理文件之一,它记录了数据库的名字、数据文件的位置等信息。控制文件的重要性在于,一旦控制文件损坏,数据库将会宕机。如果没有数据库的备份和归档日志文件...

oracle 11g RAC ASM磁盘被强制下线抢修一例

又有一段时间没写有关oracle的文章了,恐怕这也是不能成为高手的原因之一,偶有典型性拖延症和懒癌。 今天要讲的一个栗子又一个现场case,中午吃饭的时候看到来自同事的未接,处于职业敏感感觉是数据中...

Oracle 10g手工建立ASM磁盘组错误之--ORA-15201

本文来自我的老师原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://tiany.blog.51cto.com/513694/141...

Oracle 11g asm中不同au size下datafile的au分布初探

今天有朋友问11g中asm 的au size不为1m的情况下,datafile的au 分布是怎么样的?通过10g的方式去kfed read, 发现不对了,原帖地址:~~【高手请进】在oracle11...

Oracle 11g RAC 更换磁盘组

作者 阿九【转载时请务必以超链接形式标明文章原始出处和作者信息】 新安装的ORACLE 11G R2 RAC 由于创建ASM磁盘的时候,选择了Normal模式,导致磁盘大小只有原来的一半。 ...

LINUX 11G RAC ASM磁盘组在线增加磁盘扩容

LINUX 11G RAC ASM磁盘组在线增加磁盘扩容      1.操作系统版本 OEL 6.1   [root@cqltjcpt1 ~]# more ...
  • lmocm
  • lmocm
  • 2015-01-22 11:50
  • 351

oracle12c R2 asmca创建ASM磁盘组ORA-15031解决

APPLIES TO:Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1] Inform...

关于oracle 11.2.0.4 使用asm磁盘组条件下对应用数据表空间文件的rename alias操作

在运维操作过程中会出现一些失误,针对在使用ASM磁盘管理下,给表空间添加数据文件,添加的数据文件不符合创建规则,因此需要对数据文件进行rename操作,关于使用文件系统的rename操作网上已经有很多...

RAC数据库迁移(ASM磁盘组迁移)

RAC 数据库 更换存储 数据迁移磁盘盘组 rename file 方式 无数据库丢失
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)