In this
article, We will learn step by step moving a database (datafiles, control
file and online redo log files) from file system to ASM storage with
RMAN. .
1- If Block Change Tracking is enabled then you must
disable it.
#
sqlplus / as sysdba
SQL>
alter database disable block change tracking;
2- Change default location of datafiles and control files
as +DATA disk group.
SQL>
alter system set db_create_file_dest=’+DATA’ scope=spfile;
Change also default location of online redo log files as
+RECO disk group.
SQL>
alter system set db_create_online_log_dest_1=’+RECO’ scope=spfile;
3- Reset control_files parameter in the spfile parameter
file.
SQL>
alter system reset control_files scope=spfile sid=’*';
4- Now, you can move datafiles to ASM disk group with
RMAN. First, open the database on NOMOUNT state and restore control file. The
control file will locate on the +DATA disk group.
$ rman
target /
RMAN>
startup nomount
RMAN>
restore controlfile from ‘/data_TALIPDB/control01.ctl’;
5- Change status of the database to MOUNT state and take
image copy backup to +DATA disk group.
RMAN>
alter database mount;
RMAN>
backup as copy database format ‘+DATA’;
6- After finished backup switch database to copy.
RMAN>
switch database to copy;
7- Open the database.
RMAN>
alter database open;
8- Now you can enable block change tracking.
SQL>
alter database enable block change tracking using file
‘/oracle/ora11g/bct_file.log’;
9- You must add new tempfile to create tempfile on the
+DATA disk group.
#
sqlplus / as sysdba
SQL>
alter tablespace temp add tempfile size 500M;
SQL>
alter tablespace temp drop tempfile ‘/data_TALIPDB/temp01.dbf’
10- Let’s move online redo log files to ASM storage.
#
sqlplus / as sysdba
SQL>select
b.group# , b.status , a.member from v$logfile a , v$log b where a.group# =
b.group# order by 1;
GROUP#
STATUS
MEMBER
————
————
—————————
1
INACTIVE
/data1/redo01.log
2 INACTIVE /data1/redo02.log
3 CURRENT /data1/redo03.log
You can drop redo log files which are INACTIVE or
UNUSED. You can not drop a redo log file which status is CURRENT
or ACTIVE. Let’s drop online redo log groups and create on the +RECO disk
group.
SQL>
alter database drop logfile group 1;
Our new online redo log files will be created on
the +RECO disk group because I set db_create_online_log_dest_1 parameter to
‘+RECO’ disk group.
SQL>
alter database add logfile group 1 size 50M;
Drop/create second redo log group in the same way.
SQL>
alter database drop logfile group 2;
SQL>
alter database add logfile group 2 size 50M;
11- Rerun query to see status of online redo log files.
SQL>
select b.group# , b.status , a.member from v$logfile a , v$log b where a.group#
= b.group# order by 1;
GROUP#
STATUS
MEMBER
————
————
—————————
1
INACTIVE +RECO/talipdb/online
log/group_1.257.7782 52603
2
INACTIVE
+RECO/talipdb/online log/group_2.258.7782 52715
3
CURRENT
/data1/redo03.log
We can not drop 3th redo log group because it is
CURRENT log file. In this situation, switch log file.
SQL>
alter system switch logfile;
Rerun above query. You will see its status as
ACTIVE because of the redo log file is needed for crash recovery.
SQL>
select b.group# , b.status , a.member from v$logfile a , v$log b where a.group#
= b.group# order by 1;
GROUP#
STATUS
MEMBER
————
————
—————————
1
CURRENT
+RECO/talipdb/online log/group_1.257.7782 52603
2 UNUSED
+RECO/talipdb/online log/group_2.258.7782 52715
3
ACTIVE
/data1/redo03.log
To change status to INACTIVE, you must checkpoint.
SQL>
alter system checkpoint;
SQL>
select b.group# , b.status , a.member from v$logfile a , v$log b where a.group#
= b.group# order by 1;
GROUP#
STATUS
MEMBER
————
————
—————————
1
CURRENT
+RECO/talipdb/online log/group_1.257.7782 52603
2
UNUSED
+RECO/talipdb/online log/group_2.258.7782 52715
3
INACTIVE
/data1/redo03.log
Now, you can drop/create 3th redo log group.
SQL>
alter database drop logfile group 3;
SQL>
alter database add logfile group 3 size 50M;
Let’s verify our online redo log files.
SQL>
set lines 50
SQL>
select member from v$logfile;
MEMBER
————————————————–
+RECO/talipdb/onlinelog/group_3.259.778253083
+RECO/talipdb/onlinelog/group_2.258.778252715
+RECO/talipdb/onlinelog/group_1.257.778252603
Let’s verify our datafiles.
SQL>
select name from v$datafile;
NAME
——————————————————————————–
+DATA/talipdb/datafile/system.256.778251403
+DATA/talipdb/datafile/sysaux.257.778251487
+DATA/talipdb/datafile/undotbs1.258.778251553
+DATA/talipdb/datafile/users.260.778251563
SQL>
select name from v$tempfile;
NAME
——————————————————————————–
+DATA/talipdb/tempfile/temp.262.778252097
We moved our datafiles and online redo log files
to ASM. Hope to see you happy to be working with ASM databases …
No comments:
Post a Comment