ASM is an integrated volume manager and file system for Oracle Database files. It simplifies, automates storage management, increase storage utilization and delivers predictable performance. Due to this many of us wants to migrate filesystem database to ASM. If the database is very large and critical, we may not be able to afford the migration downtime. In this blog, I would discuss the procedure to dramitically reduce downtime during the process of migration from raw/OS filesystem to ASM using Oracle Data Guard and RMAN. This blog will also be helpful to create standby database. This procedure explains how data guard can help to reduce the downtime of migration.
Following is the summarised procedure
1. Create standby (dataguard) of the production database.
2. Migrate the standby database to ASM
3. Do the switchover to migate the database to ASM.
As mentioned above, it seems, migration is very simple and yes it is. Below mentioned is the detailed procedure.
PREPARE FOR STANDBY DATABASE
In this demonstration, I have named primary instance as “ORA10G” and standby database as “ORA10GSTD”
Create a backup of the database using RMAN.
RMAN> connect target /
connected to target database: ORA10G (DBID=3970225046)
RMAN> backup database include current controlfile for standby;
Starting backup at 08-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\DATAFILES\ORA10G\SYSTEM01.DBF
input datafile fno=00002 name=D:\ORACLE\DATAFILES\ORA10G\UNDOTBS01.DBF
input datafile fno=00003 name=D:\ORACLE\DATAFILES\ORA10G\SYSAUX01.DBF
input datafile fno=00004 name=D:\ORACLE\DATAFILES\ORA10G\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 08-APR-08
channel ORA_DISK_1: finished piece 1 at 08-APR-08
piece handle=D:\ORACLE\ORA10GHOME\DATABASE\01JDBCU5_1_1 tag=TAG20080408T230957 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 08-APR-08
channel ORA_DISK_1: finished piece 1 at 08-APR-08
piece handle=D:\ORACLE\ORA10GHOME\DATABASE\02JDBCVT_1_1 tag=TAG20080408T230957 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 08-APR-08
Make the tnsnames.ora changes in both (production and standby) servers to make connections between both the servers for communication.
Create and copy the parameter file and make appropriate changes for standby database.
SQL> create pfile from spfile;
We assume that, ASM instance is already created, mounted and ready for use. We are using diskgroup, named as ORADG. Also make sure this diskgroup should have sufficient space available.
Following are the standby parameters, which we have to modify.
Edit control_files parameter to appropriate location. This location should be ASM diskgroup.
control_files = ‘+ORADG\ORA10g\controlfiles\control01.ctl’
Add or edit DB_UNIQUE_NAME parameter to define the database uniqueness.
db_unique_name = ORA10GSTD ---- Instance name of Standby.
Add or edit LOG_ARCHIVE_CONFIG parameter to identify primary and standby database using unique name. The parameter value should be primary and standby instance name.
log_archive_config = ’dg_config=(ORA10G,ORA10GSTD)’
Set following parameters to point to ASM diskgroup.
db_create_file_dest = '+ORADG'
db_create_online_log_dest_1 = '+ORADG'
The other parameters that needs to change are –
instance_name = ORA10gSTD
fal_server = ora10g --- TNSNAMES entry for Primary
fal_client = ora10gSTD --- TNSNAMES entry for Standby
Create Password file
$ orapwd file=$ORACLE_HOME/dbs/orapwpassword=
INITIATE THE STANDBY DATABASE IN ASM
After confirmation ASM instance up and parametter, password file is prepared, now we initiate standby database.
SQL> startup force nomount
ORACLE instance started.
Total System Global Area 301989888 bytes
Fixed Size 1248672 bytes
Variable Size 96469600 bytes
Database Buffers 201326592 bytes
Redo Buffers 2945024 bytes
RMAN provides single command that initiates the standby database using source database information. Connect to the production database using RMAN and initiate standby database.
C:\> rman target sys/oracle@ora10g
Recovery Manager: Release 10.2.0.1.0 - Production
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10G (DBID=3970225046)
RMAN> connect auxiliary /
connected to auxiliary database: ORA10G (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate Db at 08-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=157 devtype=DISK
contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 08-APR-08
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\ORA10GHOME\DATABASE\02JDBCVT_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\ORA10GHOME\DATABASE\02JDBCVT_1_1 tag=TAG20080408T230957
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
output filename=+ORADG/ora10g/controlfiles/control01.ctl
Finished restore at 08-APR-08
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to +ORADG in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 08-APR-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=157 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +ORADG
restoring datafile 00002 to +ORADG
restoring datafile 00003 to +ORADG
restoring datafile 00004 to +ORADG
channel ORA_AUX_DISK_1: reading from backup piece D:\ORACLE\ORA10GHOME\DATABASE\01JDBCU5_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\ORA10GHOME\DATABASE\01JDBCU5_1_1 tag=TAG20080408T230957
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:46
Finished restore at 08-APR-08
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=651539654 filename=+ORADG/ora10gstd/datafile/system.257.651539549
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=651539654 filename=+ORADG/ora10gstd/datafile/undotbs1.258.651539549
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=651539654 filename=+ORADG/ora10gstd/datafile/sysaux.259.651539551
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=651539654 filename=+ORADG/ora10gstd/datafile/users.260.651539551
Finished Duplicate Db at 08-APR-08
We have successfully transferred files from production to standby database in ASM datagroup. Modify log_archive_dest_2 parameter so that archive logs file will transfer from primary to standby database. Then place the standby database recovery mode so archive logs can be applied automatically.
SQL> recover managed standby database disconnect;
DO SWITCHOVER TO MIGRATE DATABASE TO ASM
This is final step of the migration. We will do switchover to migrate the database to ASM. Check that, both primary and standby must be in sync.
On Primary Database –
Select max(sequence#) “MAXSEQ” from v$log_history;
MAXSEQ
---------
124
On Standby Database –
Select max(sequence#) “MAXSEQ” from v$log_history;
MAXSEQ
---------
124
As both primary and standby database, we can do switchover.
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 301989888 bytes
Fixed Size 1248672 bytes
Variable Size 96469600 bytes
Database Buffers 201326592 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
We have completed ASM migration.
No comments:
Post a Comment