Sunday, May 4, 2008

Migration from Filesystem to ASM Files.

This is continuation to my previous blog, wherein we discussed about interacting with ASM and OS database file.

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/orapw password=

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: