`
hunxiejun
  • 浏览: 1137695 次
文章分类
社区版块
存档分类
最新评论

How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy)

 
阅读更多

Automatic Storage Management (ASM) is an integrated file system and volume manager expressly built for Oracle database files.

This note is applicable :
1. If you wish to move to different ASM storage / Hardware.
2. If you wish to change the redundancy of the diskgroup


When the diskgroups are created with some redundancy say External,Normal or High then its redundancy cannot be changed. Need to change redundancy can arise if :

- DBA's want to switch from Normal/High Redundancy to External Redundancy due to disk space constraints or due to plans of using External methods of maintaining redundancy (like RAID 10 , etc) .

- Switch to ASM based Redundancy i.e converting from External redundancy to Normal/High Redundancy


This note discusses the steps to change the redundancy of existing diskgroups in ASM.

Note : - Please note that this steps have been tried and tested internally. But we would suggest users to test the steps in a Test Environment before executing them in Production. Also having a Full Cold Backup for the database is recommended.

Solution

To collect the list of files in ASM with their full path, use the Note 888943.1 named "How to collect the full path name of the files in ASM diskgroups"

There are two ways to perform this:
1. Create a new Diskgroup with desired redundancy and move the existing data to newly created Diskgroup.
2. Drop the existing Diskgroup after backing up data and create a new Diskgroup with desired redundancy.



CASE 1: Create a new diskgroup with desired redundancy and move the existing data to newly created diskgroup.

1) If we have extra disk space available,then we can create a new diskgroup and move the files from old diskgroup to it.

-- Initially we have two diskgroup with external redundancy as:

SQL> select state,name from v$asm_diskgroup;

STATE NAME
----------- --------------------
MOUNTED DG2
MOUNTED DG3


2) Create a new diskgroup with normal redundancy as :

SQL > create diskgroup DG1 normal redundancy failgroup <failgroup1_name> disk 'disk1_name' failgroup <failgroup2_name> disk 'disk2_name';


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

STATE NAME TYPE
----------- ------------------- ------
MOUNTED DG2 EXTERN
MOUNTED DG3 EXTERN
MOUNTED DG1 NORMAL

3) Backup the current database as follows:

SQL> show parameter db_name

NAME TYPE VALUE
---------------- ----------- ----------------------------
db_name string orcl10g

SQL> create pfile='d:/initsid.ora' from spfile;

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

SQL> alter system set control_files='+DG1/ORCL10G/CONTROLFILE/<system generated control file name from diskgroup DG1>' SCOPE=SPFILE;

-- Connect to rman
$ rman target /
RMAN > shutdown immediate;
RMAN > startup nomount;
RMAN> restore controlfile from '+DG2/ORCL10G/CONTROLFILE/mycontrol.ctl' (specify the original (old) location of controlfile here) to '<new_diskgroup i.e +DG1>'

Mountthedatabaseandvalidatethecontrolfilesfromv$controlfile

RMAN > alter database mount;

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

With "BACKUP AS COPY", RMAN copies the files as image copies, bit-for-bit copies of database files created on disk.These are identical to copies of the same files that you can create with operating system commands like cp on Unix or COPY on Windows.However, using BACKUP AS COPY will be recorded in the RMAN repository and RMAN can use them in restore operations.


4) Switch the database to copy. At this moment we are switching to the new Diskgroup

RMAN> switch database to copy;

A SWITCH is equivalent to using the PL/SQL "alter database rename file" statement.

RMAN > alter database open;



5) Add new tempfile to newly created database.

SQL> alter tablespace TEMP add tempfile '+DG1' SIZE 10M;


Drop any existing tempfile on the old diskgroup

SQL> alter database tempfile '+DG2/orcl10g/tempfile/temp.265.626631119' drop;


6) Find out how many members we have have in redolog groups, make sure that we have only one member in each log group.(drop other members).

Suppose we have 3 log groups, then add one member to each log group as following:

SQL> alter database add logfile member '+DG1' to group 1;
SQL> alter database add logfile member '+DG1' to group 2;
SQL> alter database add logfile member '+DG1' to group 3;


Then we can drop the old logfile member from earlier diskgroups as:

SQL> alter database drop logfile member 'complete_name';



7) Use the following query to verify that all the files are moved to new diskgroup with desired redundancy:


SQL> select name from v$controlfile
union
select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile
union
select filename from v$block_change_tracking


8) Enable block change tracking using ALTER DATABASE command.

SQL> alter database enable block change tracking using file ‘<FILE_NAME>’;



Case 2:

Drop the existing diskgroup after database backup and create a new diskgroup with desired redundancy.

1. Shutdown(immediate) the database and then startup mount. Take a valid RMAN backup of existing database as:

RMAN> backup device type disk format 'd:/backup/%U' database ;

RMAN> backup device type disk format 'd:/backup/%U'archivelog all;


2. Make copy of spfile to accessible location:


SQL>createpfile='d:/initsid.ora'fromspfile;

SQL>alterdatabasebackupcontrolfileto'd:/control.ctl';


3. Shutdown the RDBMS instance

SQL> shutdown immediate



4. Connect to ASM Instance and Drop the existing Diskgroups

SQL> drop diskgroup DG1 including contents;



5. Shutdown ASM Instance;

6.Startup the ASM instance in nomount state and Create the new ASM diskgroup

SQL>startupnomount
SQL> create diskgroup dg1 external redundancy disk'disk_name';


The new diskgroups name should be same as of previous diskgroup, it will facilitate the RMAN restore process.

7. Connect to the RDBMS instance and startup in nomount state using pfile

startup nomount pfile='d:/initsid.ora'
SQL> create spfile from pfile='d:/initsid.ora'


8. Now restore the controlfile and backup's using RMAN

RMAN>restorecontrolfilefrom'd:/control.ctl';
RMAN>alterdatabasemount;
RMAN>restoredatabase;
RMAN>recoverdatabase;

unabletofindarchivelog
archivelogthread=1sequence=4
RMAN-00571:===========================================================
RMAN-00569:===============ERRORMESSAGESTACKFOLLOWS===============
RMAN-00571:===========================================================
RMAN-03002:failureofrecovercommandat07/05/200718:24:32
RMAN-06054:mediarecoveryrequestingunknownlog:thread1seq4lowscn
570820

While recovery it will give an error for archive log missing, this is expected we need to open the database with resetlogs as:

RMAN>alterdatabaseopenresetlogs;


-We also need to change Flash Recovery Area to newly created diskgroup location.

SQL> alter system set db_recovery_file_dest='+DG1' scope=both;



-We must now disable and re-enable Flashback Database so that the flashback log files are recreated in the +DG1 disk group and this can be done in mount state only.

SQL> alter database flashback off ;
SQL> alter database flashback on ;



- In case you want to use new name for diskgroup,in step 8 after mounting the database , you can use :

RMAN> run{
set newname for datafile 1 to '+DG2';
set newname for datafile 2 to '+DG2';
set newname for datafile 3 to '+DG2';
set newname for datafile 4 to '+DG2';
set newname for datafile 5 to '+DG2';
restore database;
switch datafile all;
recover database;
}

(assuming that we have 5 datafiles in our database)

From Oracle

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

Blog http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群:62697716(); DBA2 群:62697977() DBA3 群:62697850()

DBA 超级群:63306533(); DBA4 群: 83829929 DBA5群: 142216823

聊天 群:40132017 聊天2群:69087192

--加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请

分享到:
评论

相关推荐

    oracle rac to rac adg 生产案例,某三甲医院部署场景

    (database managefile) ASM diskgroup (recovery manage) 生产库集群 +OCR_OVTE +DATA +ARCH 容灾库集群 +OCR_OVTE +DGDATA +DGARCH 集群名称 数据存放路径 重做日志存放路径 归档日志存放路径 生产库集群 +...

    【故障】ASM diskgroup dismount with \"Waited 15 secs for write IO to PST\"

    NULL 博文链接:https://askerain.iteye.com/blog/2295744

    dg迁移存储和数据dg修改normal冗余.txt

    asm diskgroup迁移存储和数据diskgroup修改normal冗余

    Create 10gRAC on AIX5L

    AIX创建Oracle10gRAC的redbook

    Oracle 12.2 RAC on Linux Best Practice Documentation.docx

    Oracle 12.2 RAC on RedHat 7.3最佳实践,项目需求建多套RAC,为减低管理成本,故建立一个统一的 12.2 RAC的模板。 帮助 Oracle 12.2 RAC on RedHat 7.3 这个应用场景的朋友,文档已经通过业务验证,可放心使用。

    ASM实例+ASM数据库安装(Win8+Ora10)

    3.6 检查asm disk的状态以及diskgroup的使用率 14 3.7 把asm diskgroup加入到初始化文件中 14 第四篇 创建使用ASM存储管理的数据库 16 4.1 在存储机制选择中选择ASM 16 4.2 ASM磁盘组信息 17 4.3 指定数据库区 18 ...

    jansu-dev#Oracle-Learning-Notes#ASM的connect的和mounted的区别1

    title: ASM的connect的和mounted的区别解释问题:为什么查看v$asm_diskgroup的state有时是mounted有时connect

    oracle rac日常基本维护命令

    from v$asm_diskgroup where name = 'ORCL_DATA1'); PATH ---------------------------------- ORCL:VOL1 ORCL:VOL2  二: 启动/停止RAC集群 确保是以 oracle UNIX 用户登录的。我们将从rac1节点运行所有命令...

    Oracle数据库恢复工具软件DUL

    当你的数据库因为ORA-00600/ORA-07445或其他ORA-报错,或丢失关键的system表空间数据文件,或ASM diskgroup损坏时均可以考虑采用PRM-DUL来做恢复。PRM-DUL采用独创的DataBridge恢复技术,直接从数据文件中抽取数据后...

    OCP\shangh--11G\11g R2 题库

    ASM_DISKGROUP 58 optimize 115 flashback_version 38 image copy_catalog_VPC 50 job_window_schedule_program 57 partition 11 ResourcePlan 16 RMAN_backup_recover 209 sqlloader_Pump 3 TSPITR 11 Other 108 ...

    Oracle数据恢复工具软件DUL

    当你的数据库因为ORA-00600/ORA-07445或其他ORA-报错,或丢失关键的system表空间数据文件,或ASM diskgroup损坏时均可以考虑采用PRM-DUL来做恢复。PRM-DUL采用独创的DataBridge恢复技术,直接从数据文件中抽取数据后...

    异常恢复-amdu抽取ASM文件

    2. 将ASM文件的内容抽取出来并写入到OS文件系统,Diskgroup是否mount均可 3. 打印出块的元数据,以块中C语言结构或16进制的形式 本文将详细说明AMDU工具打印的各种信息,并讲解如何在ASM instance不可用的情况下配合...

    bms工具包

    bms工具包 用于在Bare Metal Solution上安装和创建初始Oracle数据库的工具包。 快速开始 创建一个Google Cloud VM作为;... " diskgroup " : " DATA " , " disks " : [ { " name " : " DATA_1 " , " blk_device " :

    DBRECOVER for Oracle:dul数据卸载程序,恢复损坏的oracle数据库,删除,取消运行-开源

    PRM支持从9i,10g,11g到19c的Oracle数据库PRM可以在损坏的文件系统,ASM DiskGroup和数据文件上工作。 。 即使Oracle数据字典丢失,PRM也可以基于不一致的SYSTEM.DBF表空间备份来扫描和恢复字典。 PRM可以支持...

Global site tag (gtag.js) - Google Analytics