Steps To Migrate/Move a Database From Non- Automatic Storage Management to Automatic Storage Management


Applies to:

Database Management Software Oracle Server - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]

Solution:

I assue, we have already created Automatic Storage Management instance on this machine. So start moving all files from non Automatic Storage Management to Automatic Storage Management instance.

SQL> create pfile from spfile;

File created.

Set Backup Destination to Automatic Storage Management  (+FRA): 

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/opt/oracle/dwh01/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

Change Backup destination to Automatic Storage Management

SQL> alter system set db_recovery_file_dest='+FRA';

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=300G;

System altered.

SQL> SHOW PARAMETER DB_RECOVERY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +FRA
db_recovery_file_dest_size           big integer 300G

Mount the Database instance to convert into archivelog mode & move control files to Automatic Storage Management instance 

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> ALTER DATABASE ARCHIVELOG ;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/opt/oracle/dwh01/dbs/control01.ctl
/u01/opt/oracle/dwh01/dbs/control02.ctl
/u01/opt/oracle/dwh01/dbs/control03.ctl

Move Controlfile To Automatic Storage Management: Since Database is in mount stage, take backup of control file to Automatic Storage Management +FRA and restore them to Automatic Storage Management +DATA.

[oracle@srv-gbdwdb01 ~]$ rman target sys/***

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jun 21 13:01:36 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DWH01 (DBID=1056416030)

RMAN> BACKUP CURRENT CONTROLFILE ;

Starting backup at 21-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=242 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 21-JUN-12
channel ORA_DISK_1: finished piece 1 at 21-JUN-12
piece handle=+FRA/dwh01/backupset/2012_06_21/ncnnf0_tag20120621t130356_0.289.786546237 tag=TAG20120621T130356 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12

RMAN> shutdown immediate

Make sure to start instance in nomount mode only.


RMAN> startup nomount


RMAN> restore controlfile to '+DATA' from '+FRA/dwh01/backupset/2012_06_21/ncnnf0_tag20120621t130356_0.289.786546237';

Starting restore at 21-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 21-JUN-12


[oracle@srv-gbdwdb01 ~]$ export ORACLE_HOME=/u02/app/oracle/product/11.2.0/grid

[oracle@srv-gbdwdb01 ~]$ export ORACLE_SID=+ASM

Verifiy Control files are moved to Automatic Storage Management instance
 
[oracle@srv-gbdwdb01 ~]$ asmcmd

ASMCMD> connect as sysasm
ASMCMD> ls
DATA/
FRA/

ASMCMD> find -t CONTROLFILE +DATA *
WARNING:option 't' is deprecated for 'find'
please use 'type'

+DATA/DWH01/CONTROLFILE/current.256.786548801
ASMCMD>

Now, change database current control files to +DATA/DWH01/CONTROLFILE/current.256.786548801

SQL> alter system set control_files='+DATA/DWH01/CONTROLFILE/current.256.786548801' scope=spfile;

System altered.

SQL> SHUTDOWN IMMEDIATE

ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT

SQL> SHOW PARAMETER CONTROL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +DATA/dwh01/controlfile/current.256.786548801
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>

Using RMAN, copy the datafile from NON-Automatic Storage Management to Automatic Storage Management: As contorl files are moved to ASM, now let's move datafiles to Automatic Storage Management using RMAN. Database is in mont stage. Backup database files to +DATA and switch datafiles to backed up datafiles.

[oracle@srv-gbdwdb01 ~]$ rman target sys/***

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jun 21 14:02:19 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DWH01 (DBID=1056416030, not open)

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 21-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 device type=DISK
channel ORA_DISK_1: starting datafile copy
----------------------
----------------------
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 21-JUN-12
channel ORA_DISK_1: finished piece 1 at 21-JUN-12
piece handle=+DATA/dwh01/backupset/2012_06_21/nnsnf0_tag20120621t140225_0.264.786549861 tag=TAG20120621T140225 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/dwh01/datafile/system.258.786549841"
datafile 2 switched to datafile copy "+DATA/dwh01/datafile/sysaux.259.786549847"
datafile 3 switched to datafile copy "+DATA/dwh01/datafile/undotbs1.257.786549745"
datafile 4 switched to datafile copy "+DATA/dwh01/datafile/users.261.786549859"
datafile 5 switched to datafile copy "+DATA/dwh01/datafile/indx.262.786549859"
datafile 6 switched to datafile copy "+DATA/dwh01/datafile/tools.263.786549861"

RMAN> run {
set newname for tempfile 1 to '+DATA';
switch tempfile all;
}

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

RMAN> ALTER DATABASE OPEN;

database opened


Here are all the datafiles moved to Automatic Storage Management.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/dwh01/datafile/system.258.786549841
+DATA/dwh01/datafile/sysaux.259.786549847
+DATA/dwh01/datafile/undotbs1.257.786549745
+DATA/dwh01/datafile/users.261.786549859
+DATA/dwh01/datafile/indx.262.786549859
+DATA/dwh01/datafile/tools.263.786549861

6 rows selected.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/dwh01/tempfile/temp.265.786550089

Do the following maintenance for Redo Log Files: Since, we have migrated spfile, controle file and data files to Automatic Storage Management. Now needs to migrate redo log files. In case of redo migration, we will simply add new redo log files to existing groups but in Automatic Storage Management file system and will delete existing one. below are the steps.

SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------
         1
/u01/opt/oracle/dwh01/dbs/redo01.log
INACTIVE

         2
/u01/opt/oracle/dwh01/dbs/redo02.log
INACTIVE

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
STATUS
----------------

         3
/u01/opt/oracle/dwh01/dbs/redo03.log
CURRENT

Since, we have three redo log group, add one redo in each group.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;

Database altered.

Redo log gruop 3 is currnet log group, so switch to the next group and delete Non Automatic Storage Management redo log file from the group and follow the same untill all groups are clean from non Automatic Storage Management redo log files.


SQL> alter system switch logfile;

System altered.

SQL> alter database clear logfile group 3;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/opt/oracle/dwh01/dbs/redo03.log';

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL> alter database clear logfile group 2;

Database altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/opt/oracle/dwh01/dbs/redo02.log';

Database altered.

SQL> alter database clear logfile group 1;


Database altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/opt/oracle/dwh01/dbs/redo01.log';

Database altered.

Shutdown the DB and open in Non Archivelog mode if needed.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> alter database noarchivelog;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

Now database is up and running with Automatic Storage Management instance.

No comments:

Post a Comment