Migrating Database to Use ASM
1. Create a pfile from the spfile.
SQL> create pfile='/u01/app/oracle/oradata/tomig/inittomig.ora' from spfile;
File created.
2. Shutdown the database cleanly.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
3. Edit the below parameter in pfile
db_create_file_dest='+DATA'
db_recovery_file_dest='+FLASHBACK'
and remove the controlfile parameter from pfile.
Note :- The Data and the Flashback are the diskgroup names.
[oracle@rhel ~]$ vi /u01/app/oracle/oradata/tomig/inittomig.ora
[oracle@rhel ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 24 08:54:06 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount pfile = '/u01/app/oracle/oradata/tomig/inittomig.ora';
Oracle instance started
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
--Restore the controlfile from the current controlfile.. on issuing this
new controlfiles will be created in Data and Flashback disgroups.
RMAN> restore controlfile from '/u01/app/oracle/oradata/tomig/control01.ctl';
Starting restore at 24-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/tomig/controlfile/backup.256.735900923
output filename=+FLASHBACK/tomig/controlfile/backup.256.735900925
Finished restore at 24-NOV-10
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
--Use the backup as copy rman command to backup the database and give the destination
as the DATA diskgroup.
RMAN> backup as copy database format '+DATA';
Starting backup at 24-NOV-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/tomig/system01.dbf
output filename=+DATA/tomig/datafile/system.257.735901005 tag=TAG20101124T085645 recid=1 stamp=735901106
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/tomig/sysaux01.dbf
output filename=+DATA/tomig/datafile/sysaux.258.735901113 tag=TAG20101124T085645 recid=2 stamp=735901221
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:56
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/tomig/undo01.dbf
output filename=+DATA/tomig/datafile/undotbs1.259.735901227 tag=TAG20101124T085645 recid=3 stamp=735901243
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/tomig/users01.dbf
output filename=+DATA/tomig/datafile/users.260.735901251 tag=TAG20101124T085645 recid=4 stamp=735901268
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/tomig/test01.dbf
output filename=+DATA/tomig/datafile/test.261.735901277 tag=TAG20101124T085645 recid=5 stamp=735901281
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/tomig/controlfile/backup.262.735901283 tag=TAG20101124T085645 recid=6 stamp=735901291
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 24-NOV-10
--Using the Switch command in rman switch the database to the copy of database
which we have created in the earlier step.
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/tomig/datafile/system.257.735901005"
datafile 2 switched to datafile copy "+DATA/tomig/datafile/undotbs1.259.735901227"
datafile 3 switched to datafile copy "+DATA/tomig/datafile/sysaux.258.735901113"
datafile 4 switched to datafile copy "+DATA/tomig/datafile/users.260.735901251"
datafile 5 switched to datafile copy "+DATA/tomig/datafile/test.261.735901277"
--Do an recovery for the database.
RMAN> run
{
set until logseq 0 thread 1;
recover database;
}
executing command: SET until clause
Starting recover at 24-NOV-10
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-NOV-10
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@rhel ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 24 09:05:08 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/tomig/controlfile/backup
.256.735900923, +FLASHBACK/tom
ig/controlfile/backup.256.7359
00925
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
--The database is now running using pfile. Hence create a spfile from the pfile.
Note that the controlfile parameter will not exists in new spfile.
So we need to add it in spfile.
SQL> create spfile from pfile ='/u01/app/oracle/oradata/tomig/inittomig.ora';
File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
-- We need to issue startup nomount because the controlfile parameter is currently
not exists in spfile.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfiletomig.ora
Modify the control_file parameter to point out the controlfiles which exists in
the diskgroups of DATA and Flashback.
SQL> alter system set
control_files='+DATA/tomig/controlfile/backup.256.735900923','+FLASHBACK/tomig /controlfile/backup.256.735900925' scope=spfile;
System altered.
--Bounce the Database
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/tomig/datafile/system.257.735901005
+DATA/tomig/datafile/undotbs1.259.735901227
+DATA/tomig/datafile/sysaux.258.735901113
+DATA/tomig/datafile/users.260.735901251
+DATA/tomig/datafile/test.261.735901277
Now create online redologfiles in Diskgroups.
SQL> select member from V$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/tomig/redo01.rdo
/u01/app/oracle/oradata/tomig/redo02.rdo
/u01/app/oracle/oradata/tomig/redo03.rdo
/u01/app/oracle/oradata/tomig/redo04.rdo
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 20971520 2 YES UNUSED 0
2 1 1 20971520 2 NO CURRENT 1027524 24-NOV-10
SQL> alter database add logfile group 3 ('+DATA','+DATA') size 20m;
Database altered.
SQL> alter database add logfile group 4 ('+DATA','+DATA') size 20m;
Database altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
2 1 1 20971520 2 NO CURRENT 1027524 24-NOV-10
3 1 0 20971520 2 YES UNUSED 0
4 1 0 20971520 2 YES UNUSED 0
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
2 1 1 20971520 2 YES INACTIVE 1027524 24-NOV-10
3 1 2 20971520 2 YES INACTIVE 1028015 24-NOV-10
4 1 3 20971520 2 NO CURRENT 1028021 24-NOV-10
SQL> alter database drop logfile group 2;
Database altered.
SQL> select * from V$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
3 1 2 20971520 2 YES INACTIVE 1028015 24-NOV-10
4 1 3 20971520 2 NO CURRENT 1028021 24-NOV-10
SQL> select member from V$logfile;
MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/tomig/onlinelog/group_3.263.735902317
+DATA/tomig/onlinelog/group_3.264.735902329
+DATA/tomig/onlinelog/group_4.265.735902361
+DATA/tomig/onlinelog/group_4.266.735902373
SQL> select name from V$controlfile;
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/tomig/controlfile/backup.256.735900923
+FLASHBACK/tomig/controlfile/backup.256.735900925
--All the file has been migrated to ASM, now we have only two controlfiles,
we can multiplex is to 4 using below.
Alter the controlfile parameter with existing 2 files plus 2 more locations
as +DATA AND +FLASHBACK as below.
SQL> alter system set control_files='+DATA/tomig/controlfile/backup.256.735900923','+FLASHBACK/tomig/controlfile/backup.256.735900925','+DATA','+FLASHBACK'
2 scope=spfile;
System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
--When we use ASM the only option available for backup is rman, so connect to
rman to multiplex the files.
[oracle@rhel ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Nov 24 09:25:01 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
-execute the below command so that rman will restore this controlfile to
all the locations that been mentioned in the control_parameter.
RMAN> restore controlfile from '+DATA/tomig/controlfile/backup.256.735900923';
Starting restore at 24-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/tomig/controlfile/backup.256.735900923
output filename=+FLASHBACK/tomig/controlfile/backup.256.735900925
output filename=+DATA/tomig/controlfile/backup.267.735902747
output filename=+FLASHBACK/tomig/controlfile/backup.260.735902749
Finished restore at 24-NOV-10
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@rhel ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 24 09:26:24 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from V$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/tomig/controlfile/backup.256.735900923
+FLASHBACK/tomig/controlfile/backup.256.735900925
+DATA/tomig/controlfile/backup.267.735902747
+FLASHBACK/tomig/controlfile/backup.260.735902749
----End of Migrating a database to Use ASM--------------------------------------
No comments:
Post a Comment