MOVING ASM DATABASE FILES FROM ONE DISKGROUP TO ANOTHER:
SOLUTION
The preferred way of doing the file movement amoung ASM DISKGROUPS is using RMAN. RMAN is critical to Automatic Storage Management and is responsible for tracking the ASM filenames and for deleting obsolete ASM files. Since ASM files cannot be accessed through normal operating system interfaces, RMAN is the preferred means of copying ASM file.
Note: From 12c, you can use the ALTER DATABASE MOVE DATAFILE SQL statement to rename or relocate online data files.
Following example moves the data file from one Oracle ASM location to another Oracle ASM location.
ALTER DATABASE MOVE DATAFILE '+dgroup_01/data/orcl/datafile/user1.dbf' TO '+dgroup_02/data/orcl/datafile/user1.dbf';
Managing Data Files and Temp Files
The steps involved in moving a datafile from a diskgroup to another is as given below.
1) Identify the data file to be moved.
2) Identify the diskgroup on to which the file has to be moved.
3) Take the file offline.
4) Copy the file to new diskgroup using Either RMAN or DBMS_FILE_TRANSFER.
5) Rename the file to point to new location.
6) Recover the file.
7) Bring the file online.
8) Verify the new file locations.
9) Delete the file from its original location.
1) Identify the data file to be moved.
----------------------------------------
In database instance
SQL:ORCL> SELECT FILE_NAME FROM DBA_DATA_FILES:
+ASMDSK2/orcl/datafile/users.256.565313879 <======= Move this to ASMDSK1.
+ASMDSK1/orcl/sysaux01.dbf
+ASMDSK1/orcl/undotbs01.dbf
+ASMDSK1/orcl/system01.dbf
2) Identify the diskgroup on to which the file has to be moved.
--------------------------------------------------------------
In ASM instance
SQL:ASM> SELECT GROUP_NUMBER, NAME FROM V$ASM_DISKGROUP;
GROUP_NUMBER NAME
------------ ---------
1 ASMDSK1
2 ASMDSK2
3) Take the file offline.
--------------------------
SQL:ORCL> ALTER DATABASE DATAFILE '+ASMDSK2/orcl/datafile/users.256.565313879' OFFLINE;
4)Now Copy the file from Source diskgroup ASMDSK1 to target Diskgroup ASMDSK2.
--------------------------------------------------------------------------------------------
Either
4. a) DBMS_FILE_TRANSFER package or
4. b) RMAN
can be used for this step.
( The step 5 to step 8 is based on the filenames from method b).
4.a).Using DBMS_FILE_TRANSFER package
SQL:ORCL>create or replace directory orcl1 as '+asmdsk1/orcl/datafile';
SQL:ASM> Alter disgroup asmdsk2 add directory '+asmdsk2/test';
SQL:ORCL> create or replace directory orcl2 as '+asmdsk2/test';
SQL:ORCL>
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'ORCL1',
source_file_name => 'users.259.565359071',
destination_directory_object => 'ORCL2',
destination_file_name => 'USERS01.DBF');
END; Database altered.
4 b).Using RMAN copy the file to new diskgroup.
$ rman target system@orcl10
target database Password:
connected to target database: ORCL (DBID=1089529226)
RMAN>
RMAN> COPY DATAFILE '+ASMDSK2/orcl/datafile/users.256.565313879' TO '+ASMDSK1';
Starting backup at 03-AUG-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=+ASMDSK2/orcl/datafile/users.256.565313879
output filename=+ASMDSK1/orcl/datafile/users.259.565359071 tag=TAG20050803T12110
9 recid=2 stamp=565359071
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-AUG-05
5) Rename the file to point to new location.
-------------------------------------------
If you have used DBMS_FILE_TRANSFER (method 4 a)) use the following command to rename:
SQL:ORCL> ALTER DATABASE RENAME FILE '+ASMDSK2/orcl/datafile/users.256.565313879' TO
'+ASMDSK1/orcl/datafile/users.259.565359071'
Database altered.
If you have used RMAN (method 4 b) use the following option of RMAN
RMAN run {
set newname for datafile '+ASMDSK2/orcl/datafile/users.256.565313879'
to '+ASMDSK1/orcl/datafile/users.259.565359071' ;
switch datafile all;
}
6) Recover the file.
-------------------
SQL:ORCL> RECOVER DATAFILE '+ASMDSK1/orcl/datafile/users.259.565359071'
Media recovery complete.
7) Bring the file online.
-----------------------
SQL:ORCL>ALTER DATABASE DATAFILE '+ASMDSK1/orcl/datafile/users.259.565359071' ONLINE
Database altered.
8) Verify the new file location.
---------------------------------
SQL:ORCL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
-------------------------------------------------------------------------------
+ASMDSK1/orcl/datafile/users.259.565359071
+ASMDSK1/orcl/sysaux01.dbf
+ASMDSK1/orcl/undotbs01.dbf
+ASMDSK1/orcl/system01.dbf
9) Delete the file from its original location either per SQLPLUS or per ASMCMD:
e.g.: SQL:ASM> ALTER DISKGROUP ASMDSK2 DROP FILE users.256.565313879;
or: ASMCMD> rm -rf <filename>
Note:
Most Automatic Storage Management files do not need to be manually deleted because, as Oracle managed files, they are removed automatically when they are no longer needed.
However, if you need to drop an Oracle Managed File (OMF) manually you should use the fully qualified filename if you reference the file. Otherwise you will get an error (e.g. ORA-15177).
e.g.: SQL:ASM> ALTER DISKGROUP ASMDSK2 DROP FILE '+ASMDSK2/orcl/datafile/users.256.565313879';
Note: The steps provided above assume that the database is open and in Archivelog mode.
Besides these steps are not appropriated for system or sysaux datafiles. For System and Sysaux an approach similar to the one given below can be used:
1. Create a Copy of datafile in target Diskgroup:
RMAN> backup as copy tablespace system format '<New DG>';
RMAN> backup as copy tablespace sysaux format '<New DG>';
2. Then shutdown the database and restart to a mounted state
RMAN> shutdown immediate;
RMAN> startup mount;
3. switch the datafiles to the copy
RMAN> switch tablespace system to copy;
RMAN> switch tablespace sysaux to copy;
4. Recover the changes made to these tablespaces;
RMAN> recover database;
-------------
Also make sure you run RMAN crosscheck copy and delete expired copy to update the controlfile and catalog for your backups to run without issues.
2------------------------------------------------------
GOAL
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 to '<new_diskgroup i.e +DG1>' from '+DG2\ORCL10G\CONTROLFILE\mycontrol.ctl'; (specify the original (old) location of controlfile here)
(Check MOS Doc ID 345180.1 to multiplex the controlfile, when the move is done)
Mount the database and validate the controlfiles from v$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> recover database ;
This will recover the backup controlfile taken and restored before to be in sync with database/datafiles
RMAN> alter database open resetlogs;
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 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> create pfile='d:\initsid.ora' from spfile;
SQL> alter database backup controlfile to '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>startup nomount
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 > restore controlfile from 'd:\control.ctl';
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
unable to find archive log
archive log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/05/2007 18:24:32
RMAN-06054: media recovery requesting unknown log: thread 1 seq 4 lowscn
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> alter database open resetlogs;
-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)
Case 3: Convert NORMAL or HIGH redundancy to FLEX redundancy diskgroup
1) Prerequisite:
- Databases and ASM must be running on 12.2 software.
- The disk group must be NORMAL or HIGH redundancy.
- The disk group must have a minimum of 3 failure groups.
- The disk group must have a compatible.asm value of at least 12.2 and a compatible.rdbms value of at least 12.2.
- Prior to follow the rest of the steps, ensure we have valid backups
2) Stop the associated databases and Dismount the diskgroup from all nodes
ALTER DISKGROUP DG1 DISMOUNT;
3) Mount the diskgroup from any one node in restricted mode
ALTER DISKGROUP DG1 MOUNT RESTRICTED;
4) Convert to FLEX
ALTER DISKGROUP DG1 CONVERT TO FLEX;
5) Dismount the diskgroup
ALTER DISKGROUP DG1 DISMOUNT;
6) Mount the diskgroup from all nodes and startup the associated database instances
ALTER DISKGROUP DG1 MOUNT;
Additional Resources
Community Discussions: Storage Management
Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.
--------------------
GOAL
How to relocate all database files - datafiles, online redo logs, and controlfiles. This requires the database to be in mounted mode.
Non-system datafiles can be relocate with minimal outage - see solution 2.
SOLUTION
Database Name: TEST
New location: /u02/oradata/TEST
****************
Solution 1 - How to relocate all database files
This solution requires the database to be in mounted mode. We will relocate all database files, controlfiles and redo logs.
The examples below use /u02/oradata/TEST as the new destination. You can use any destination you like, including new ASM disk groups.
1) restart database in mount mode
SQL> shutdown immediate;
SQL> startup mount;
2) copy all datafiles to the new location
a) allow RMAN to generate the new name:
RMAN> backup as copy database format '/u02/oradata/TEST/%U';
or
RMAN> backup as copy database format '+DGROUP4';
OR
b) To keep the same names you can use db_file_name convert as follows: ASM不支持名字
RMAN> BACKUP AS COPY DB_FILE_NAME_CONVERT ('/u01/oradata/TEST','/u02/oradata/TEST') database;
3) switch to the datafile copies
RMAN> switch database to copy;
4) relocate the online redo logs
As online redo logs are not backed up by RMAN, you will need to relocate them outside of RMAN:
a) identify the list of online redo logs:
SQL> select * from v$logfile;
b) make an o/s copy of the line redo logs to the new location:
$ cp /tmp/redo01.log /u02/oradata/TEST/redo01.rdo
$ cp /tmp/redo02.log /u02/oradata/TEST/redo02.rdo
$ cp /tmp/redo03.log /u02/oradata/TEST/redo03.rdo
$ cp /tmp/redo04.log /u02/oradata/TEST/redo04.rdo
Note: as of 11g, you can also cp to an ASM diskgroup
c) now rename the log files, do this for each of the redo log files:
SQL> alter database rename file '/tmp/redo01.log' to '/u02/oradata/TEST/redo01.rdo';
SQL> alter database rename file '/tmp/redo02.log' to '/u02/oradata/TEST/redo02.rdo';
SQL> alter database rename file '/tmp/redo03.log' to '/u02/oradata/TEST/redo03.rdo';
SQL> alter database rename file '/tmp/redo04.log' to '/u02/oradata/TEST/redo04.rdo';
5) relocate the controlfiles
a) backup current controlfile to new location:
RMAN> backup as copy current controlfile format '/u02/oradata/TEST/control001.ctl';
b) duplicate the controlfile copy:
RMAN> backup as copy controlfilecopy '/u02/oradata/TEST/control01.ctl' format '/u02/oradata/TEST/control02.ctl';
c) change the controlfile locations:
SQL> startup nomount;
SQL> show parameter control
SQL> alter system set control_files='/u02/oradata/TEST/control01.ctl','/u02/oradata/TEST/control02.ctl'
scope=spfile;
SQL> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;;
6) if you need to relocate temp then simply drop and recreate it in SQL*Plus:
SQL> alter database drop temporary tablespace temp;---不如drop tempfile啦
SQL> create temporary tablespace temp datafile '/tmp/temp01.dbf' size 100m;
===============================================================
Solution 2 - How to relocate a non-system datafile
The following can be done when the database is open. You can only do this for non-system datafiles. It only needs minimal outage during the rename...
1) backup the datafile to the new location:
RMAN> report schema;
RMAN> backup as copy datafile 5 format '/u02/oradata/TEST/users01.bck';
RMAN> list copy of datafile 5;
2) take the datafile offline and rename it by using the SWITCH command:
SQL> alter database datafile 5 offline;
RMAN> switch datafile 5 to copy;
RMAN> recover datafile 5;
3) put it online and confirm its new location:
SQL> alter database datafile 5 online;------期间还有有不可用的
RMAN> report schema;
--------------------
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 10.2.0.4 [Release 8.1.7 to 10.2]
Information in this document applies to any platform.
GOAL
How to use RMAN to move one, several or all datafiles from one raw device/filesystem to another and parallelise the process where >1 file is being moved.
The examples in this note look at moving files from one raw device to another but the same method is used regardless of where you are moving the file to; if moving to a new filesystem, simply specify a filename instead of a raw device name.
SOLUTION
Using RMAN to move files either from a filesystem to a raw device or from one raw device to
another is simple because RMAN will handle the 'red tape' on the raw devices so you do not need to worry about how many blocks to skip when copying.
The move is achieved by COPYING the files to the new location using the rman BACKUP AS COPY command which creates a useable datafilecopy of the datafile which is in effect a backup - a switch is then made to the dataflecopy (the equvilant of 'alter database rename file') after which it must be recovered before it can be used.
To move individual files the database can remain open but first, offline the affected tablespace(s)
cleanly:
SQL>alter tablespace X offline;
To move a single file:
RMAN>backup as copy datafile 15 format '/dev/raw1';
RMAN>switch datafile 15 to copy;
RMAN>recover datafile 15;
RMAN>sql "alter tablespace X online";
To move ALL files , the database should be shutdown cleanly first (shutdown immediate) and remounted. To move several files use a single backup command to allow the copy to be parallelised across a number of channels
RMAN>run {
allocate channel d1 type disk format '/dev/raw1';
allocate channel d2 type disk format '/dev/raw2';
backup as copy (datafile 3 channel=d1) (datafile 7 channel=d2);
switch datafile 3, 7 to copy;
recover datafile 3,7;
}
If you have moved ALL files in the database to a new location then you can use this command to
switch the files:
RMAN>switch database to copy;---------------少了一步吧，怎么move的？
RMAN>recover database;
RMAN>alter database open;
The datafiles for the database will now be the ones in the NEW location.
The files in the old location will now be DATAFILECOPIES - you can view them like this:
RMAN>list copy of database;
To remove the old files
RMAN>delete copy of database;
If you have other datafilecopies created before this exercise that you wish to keep then use this
syntax to delete only those datafilecopies created within say, the last 12 hours (adjust the syntax as
necessary):
RMAN>delete copy of database completed after 'sysdate -1/2';
------------------------------这步 database 换成pluggable database 就可以通用 -------------------
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"
GOAL
How to collect the full path name of the files in ASM diskgroups
SOLUTION
Set your ORACLE_SID to the ASM instance name.
Connect to the ASM instance:
in 10g: sqlplus / as sysdba
in 11g: sqlplus / as sysasm
Then perform the following query:
SELECT gnum, filnum, concat('+'||gname,sys_connect_by_path(aname, '/'))
FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.group_number gnum,a.file_number filnum
FROM v$asm_alias a,v$asm_diskgroup g
WHERE a.group_number = g.group_number)
START WITH (mod(pindex, power(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex;
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 to '<new_diskgroup i.e +DG1>' from '+DG2\ORCL10G\CONTROLFILE\mycontrol.ctl'; (specify the original (old) location of controlfile here)
(Check MOS Doc ID 345180.1 to multiplex the controlfile, when the move is done)
Mount the database and validate the controlfiles from v$controlfile
RMAN > alter database mount;
RMAN> backup as copy database format '+DG1';
backup as copy pluggable database XXX 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; --pluggable database XXX
A SWITCH is equivalent to using the PL/SQL "alter database rename file" statement.
RMAN> recover database ; --pluggable database XXX
This will recover the backup controlfile taken and restored before to be in sync with database/datafiles
RMAN> alter database open resetlogs; --pluggable database XXX open 就可以不需要resetlogs
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 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> create pfile='d:\initsid.ora' from spfile;
SQL> alter database backup controlfile to '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>startup nomount
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 > restore controlfile from 'd:\control.ctl';
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
unable to find archive log
archive log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/05/2007 18:24:32
RMAN-06054: media recovery requesting unknown log: thread 1 seq 4 lowscn
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> alter database open resetlogs;
-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)
Case 3: Convert NORMAL or HIGH redundancy to FLEX redundancy diskgroup
1) Prerequisite:
- Databases and ASM must be running on 12.2 software.
- The disk group must be NORMAL or HIGH redundancy.
- The disk group must have a minimum of 3 failure groups.
- The disk group must have a compatible.asm value of at least 12.2 and a compatible.rdbms value of at least 12.2.
- Prior to follow the rest of the steps, ensure we have valid backups
2) Stop the associated databases and Dismount the diskgroup from all nodes
ALTER DISKGROUP DG1 DISMOUNT;
3) Mount the diskgroup from any one node in restricted mode
ALTER DISKGROUP DG1 MOUNT RESTRICTED;
4) Convert to FLEX
ALTER DISKGROUP DG1 CONVERT TO FLEX;
5) Dismount the diskgroup
ALTER DISKGROUP DG1 DISMOUNT;
6) Mount the diskgroup from all nodes and startup the associated database instances
ALTER DISKGROUP DG1 MOUNT;
9631
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
