DB migration to ASM Storage

Why ASM? It’s debatable point between DBAs and Storage experts. Industry wide Oracle users could be agreed on few points:

  1. Reduction in administration overhead.
  2. Better I/O as compared to placement of datafiles over filesystem.
  3. Online expansion and migration to faster disks.
  4. Storage management similar to RAC databases.

ASM providing placement of data in chucks (called Allocation Units) along with beauty of automatically rebalances across available disks. Today I am here to share demo of DB migration from filesystem to ASM. Normally there could be few possible ways to achieve migration activity. This is tough to say the best way for migration without actually understanding business requirement.

  • Migration of physical database
    • Take image copy of database in ASM storage and switch database to copy.
    • Take RMAN backup and restore it in ASM disks followed by switch copy.
    • Duplicate database into ASM diskgroup.
  • Migration with physical replication
    • Dataguard or Transportable tablespace
  • Migration with logical replication setup
    • Any replication method (Golden Gate, Streams, shareplex etc) to setup parallel environment and perform switchover.

Migration using physical standby could involve less downtime and easy performing. Not everyone can afford this approach as it involves extra hardware resources.

Today we will demonstrate migration from filesystem to ASM using RMAN. Apart from straight we will also try to restore spfile and control file from autobackup. This method is one the scenario where we lost our spfile or controlfile. Once controlfile would be available, so the information of backups as well. Although backups could also be registered if required.

Our approach includes total downtime if restoration to be done on same server. Incase data to be moved to other server then it will be time saving as transfer of compressed backup would be an advantage as compared to Image copy where size of backup would be equal to size of physical database.

There are so many ways to achieve the goal. Whatever approach you choose, need to plan based on business requirements and available resources.

Before proceeding for demo, let’s get an idea about placement of physical files.

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name MASK11G

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

—- ——– ——————– ——- ————————

1 680 SYSTEM *** /u01/app/oracle/oradata/mask11g/system01.dbf

2 540 SYSAUX *** /u01/app/oracle/oradata/mask11g/sysaux01.dbf

3 55 UNDOTBS1 *** /u01/app/oracle/oradata/mask11g/undotbs01.dbf

4 5 USERS *** /u01/app/oracle/oradata/mask11g/users01.dbf

5 100 EXAMPLE *** /u01/app/oracle/oradata/mask11g/example01.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

—- ——– ——————– ———– ——————–

1 20 TEMP 32767 /u01/app/oracle/oradata/mask11g/temp01.dbf

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ———————————————————————- —

3 ONLINE /u01/app/oracle/oradata/mask11g/redo03.log NO

2 ONLINE /u01/app/oracle/oradata/mask11g/redo02.log NO

1 ONLINE /u01/app/oracle/oradata/mask11g/redo01.log NO

SQL> show parameter spfile

NAME TYPE VALUE

———————————— ———– ——————————

spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilemask11g.ora

SQL> show parameter control_file

NAME TYPE VALUE

———————————— ———– ——————————

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/mask11g/control01.ctl,

                         /u01/app/oracle/flash_recovery_area/mask11g/control02.ctl

To ease the scenario of migration we are taking backup just before activity, otherwise we can also use any existing good backup plus all archives for recovery.

RMAN> backup as compressed backupset database tag ‘BKP_4_MIGRATION’;

Starting backup at 21-AUG-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/mask11g/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/mask11g/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/mask11g/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/mask11g/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/mask11g/users01.dbf

channel ORA_DISK_1: starting piece 1 at 21-AUG-12

channel ORA_DISK_1: finished piece 1 at 21-AUG-12

piece handle=/backup/flash_recover_area/MASK11G/backupset/2012_08_21/o1_mf_nnndf_BKP_4_MIGRATION_836w4dsp_.bkp tag=BKP_4_MIGRATION comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05

Finished backup at 21-AUG-12

Starting Control File and SPFILE Autobackup at 21-AUG-12

piece handle=/backup/autobkp/c-1740440609-20120821-01 comment=NONE

Finished Control File and SPFILE Autobackup at 21-AUG-12

Backup of controlfile and spfile could also be taken for extra precaution, although it’s already done as controlfile autobackup is enabled in rman configuration.

RMAN> backup current controlfile tag BKP_4_MIGRATION;

Starting backup at 21-AUG-12

using channel ORA_DISK_1

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-AUG-12

channel ORA_DISK_1: finished piece 1 at 21-AUG-12

piece handle=/backup/flash_recover_area/MASK11G/backupset/2012_08_21/o1_mf_ncnnf_BKP_4_MIGRATION_836w8tjv_.bkp tag=BKP_4_MIGRATION comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 21-AUG-12

Starting Control File and SPFILE Autobackup at 21-AUG-12

piece handle=/backup/autobkp/c-1740440609-20120821-02 comment=NONE

Finished Control File and SPFILE Autobackup at 21-AUG-12

Now proceeding to shutdown the existing database.

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Moving spfile as well to mimic loss of spfile. It will give us chance to test recovery capability for spfile.

[oracle@mask11g dbs]$ mv spfilemask11g.ora spfilemask11g.ora_21082012

Now we will replicate the case where we lost all disks containing the spfile, datafiles etc. and forced to use ASM as its already available as on this server.

we need dbid to restore spfile because we are restoring it from autobackup. If we don’t have one. Few places to find it

  1. RMAN catalog stored that for you; If you don’t have one then go for options 2, 3, 4 etc
  2. RMAN backup logs where rman connected to database
  3. Incase autobackup is on then backup file name contains dbid for you
  4. If you have ever took dbid then search for it

We would still have other ways to get or re-create spfile. I actually got DBID from two places

[oracle@mask11g dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Aug 21 16:53:47 2012

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

connected to target database: MASK11G (DBID=1740440609)

[oracle@mask11g autobkp]$ pwd

/backup/autobkp

[oracle@mask11g autobkp]$ ls -lrt

total 57696

-rw-r—– 1 oracle oinstall 9830400 Aug 21 16:58 c-1740440609-20120821-01

-rw-r—– 1 oracle oinstall 9830400 Aug 21 16:59 c-1740440609-20120821-02

Now restoring spfile from autobackup:

[oracle@mask11g ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Tue Aug 21 22:59:15 2012

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

connected to target database (not started)

RMAN> SET DBID 1740440609

executing command: SET DBID

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initmask11g.ora’

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 158662656 bytes

Fixed Size 2211448 bytes

Variable Size 92275080 bytes

Database Buffers 58720256 bytes

Redo Buffers 5455872 bytes

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/autobkp/%F’;

4> RESTORE SPFILE FROM AUTOBACKUP;

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 21-AUG-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=97 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120821

channel ORA_DISK_1: AUTOBACKUP found: /backup/autobkp/c-1740440609-20120821-02

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/autobkp/c-1740440609-20120821-02

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 21-AUG-12

Verifying the spfile and yes it’s restored

[oracle@mask11g dbs]$ ls -lrt spf*

-rw-r—– 1 oracle oinstall 2560 Aug 21 16:47 spfilemask11g.ora_21082012

-rw-r—– 1 oracle oinstall 2560 Aug 21 23:07 spfilemask11g.ora

Now Shutting down instance, because old instance was started with default settings required to restore spfile from autobackup.

SQL> shu immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

Startup again in nomount mode to modify spfile parameters

SQL> startup nomount;

ORACLE instance started.

. . . . . . . . . . .

SQL> show parameter spfile

NAME TYPE VALUE

———————————— ———– ——————————

spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilemask11g.ora

SQL> show parameter control_file

NAME TYPE VALUE

———————————— ———– ——————————

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/mask11g/control01.ctl,

/u01/app/oracle/flash_recovery_area/mask11g/control02.ctl

SQL> show parameter recover

NAME TYPE VALUE

———————————— ———– ——————————

db_recovery_file_dest string /backup/flash_recover_area

db_recovery_file_dest_size big integer 1G

recovery_parallelism integer 0

Let’s modify control parameter & recovery area parameter to point it to ASM

SQL> alter system set db_recovery_file_dest=’+FRA’ scope=spfile sid=’*’;

System altered.

SQL> alter system set control_files=’+DATA’,’+FRA’ scope=spfile sid=’*’;

System altered.

Let’s restart database in nomount mode to get modified parameters

SQL> shu immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

. . . . . . . . . . .

SQL> show parameter control_file

NAME TYPE VALUE

———————————— ———– ——————————

control_file_record_keep_time integer 7

control_files string +DATA, +FRA

SQL> show parameter recover

NAME TYPE VALUE

———————————— ———– ——————————

db_recovery_file_dest string +FRA

db_recovery_file_dest_size big integer 1G

recovery_parallelism integer 0

Now restore the control files into ASM diskgroups

RMAN> SET DBID 1740440609

executing command: SET DBID

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/autobkp/%F’;

4> RESTORE CONTROLFILE FROM AUTOBACKUP;

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 22-AUG-12

using channel ORA_DISK_1

recovery area destination: +FRA

database name (or database unique name) used for search: MASK11G

channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120822

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20120821

channel ORA_DISK_1: AUTOBACKUP found: /backup/autobkp/c-1740440609-20120821-02

channel ORA_DISK_1: restoring control file from AUTOBACKUP /backup/autobkp/c-1740440609-20120821-02

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=+DATA/mask11g/controlfile/current.256.791945099

output file name=+FRA/mask11g/controlfile/current.256.791945103

Finished restore at 22-AUG-12

Controlfiles are now restored inside ASM

ASMCMD> ls -l +DATA/MASK11G/CONTROLFILE

Type Redund Striped Time Sys Name

CONTROLFILE UNPROT FINE AUG 22 00:00:00 Y current.256.791945099

ASMCMD> ls -l +FRA/MASK11G/CONTROLFILE

Type Redund Striped Time Sys Name

CONTROLFILE UNPROT FINE AUG 22 00:00:00 Y current.256.791945103

Mounting the database

RMAN> sql ‘alter database mount’;

sql statement: alter database mount

released channel: ORA_DISK_1

In case you got a chance to see Alert log contents then you find error message showing failed dependency between database and diskgroups. It will be resolved once we register database with grid using srvctl.

Wed Aug 22 00:45:01 2012

ERROR: failed to establish dependency between database mask11g and diskgroup resource ora.DATA.dg

Wed Aug 22 00:59:18 2012

alter database mount

Wed Aug 22 00:59:18 2012

ERROR: failed to establish dependency between database mask11g and diskgroup resource ora.FRA.dg

Wed Aug 22 00:59:23 2012

Now let’s see if we can find backup information from control file. In case backup pieces are not registered on same location where it was taken then you need to re-register available backups to allow RMAN to restore.

RMAN> list backup summary;

List of Backups

===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag

——- — — – ———– ————— ——- ——- ———- —

14 B F A DISK 20-AUG-12 1 1 NO TAG20120820T175545

16 B F A DISK 20-AUG-12 1 1 NO TAG20120820T175825

17 B F A DISK 20-AUG-12 1 1 NO TAG20120820T201846

18 B F A DISK 21-AUG-12 1 1 NO TAG20120821T165708

19 B F A DISK 21-AUG-12 1 1 YES BKP_4_MIGRATION

20 B F A DISK 21-AUG-12 1 1 NO TAG20120821T165814

21 B F A DISK 21-AUG-12 1 1 NO BKP_4_MIGRATION

We are now commencing restoration of database. Although in older versions we have to use set newname for individual datafiles but now with 11g we can use newname for full database as well.

Another point I want to highlight that before restoration RMAN actually tries to implicit cross check the available source for restoration.

RMAN> RUN

2> {

3> SET NEWNAME FOR DATABASE TO ‘+DATA’;

4> RESTORE DATABASE;

5> }

executing command: SET NEWNAME

Starting restore at 22-AUG-12

Starting implicit crosscheck backup at 22-AUG-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=136 device type=DISK

Crosschecked 7 objects

Finished implicit crosscheck backup at 22-AUG-12

Starting implicit crosscheck copy at 22-AUG-12

using channel ORA_DISK_1

Finished implicit crosscheck copy at 22-AUG-12

searching for all files in the recovery area

cataloging files…

no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DATA

channel ORA_DISK_1: restoring datafile 00002 to +DATA

channel ORA_DISK_1: restoring datafile 00003 to +DATA

channel ORA_DISK_1: restoring datafile 00004 to +DATA

channel ORA_DISK_1: restoring datafile 00005 to +DATA

channel ORA_DISK_1: reading from backup piece /backup/flash_recover_area/MASK11G/backupset/2012_08_21/o1_mf_nnndf_BKP_4_MIGRATION_836w4dsp_.bkp

channel ORA_DISK_1: piece handle=/backup/flash_recover_area/MASK11G/backupset/2012_08_21/o1_mf_nnndf_BKP_4_MIGRATION_836w4dsp_.bkp tag=BKP_4_MIGRATION

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:15

Finished restore at 22-AUG-12

Best practice, ‘tail –f’ alert log to see the progress of restoration as well. Couples of other views are also available to see the real time progress with I/O counts as well J

Wed Aug 22 01:09:10 2012

Full restore complete of datafile 4 to datafile copy +DATA/mask11g/datafile/users.261.791946549. Elapsed time: 0:00:02

checkpoint is 1383645

Full restore complete of datafile 3 to datafile copy +DATA/mask11g/datafile/undotbs1.260.791946547. Elapsed time: 0:00:04

checkpoint is 1383645

last deallocation scn is 1378549

Undo Optimization current scn is 1380216

Wed Aug 22 01:09:22 2012

Full restore complete of datafile 5 to datafile copy +DATA/mask11g/datafile/example.259.791946547. Elapsed time: 0:00:15

checkpoint is 1383645

last deallocation scn is 1317961

Wed Aug 22 01:10:07 2012

Full restore complete of datafile 2 to datafile copy +DATA/mask11g/datafile/sysaux.258.791946547. Elapsed time: 0:01:01

checkpoint is 1383645

last deallocation scn is 1329758

Full restore complete of datafile 1 to datafile copy +DATA/mask11g/datafile/system.257.791946547. Elapsed time: 0:01:10

checkpoint is 1383645

last deallocation scn is 1186368

Undo Optimization current scn is 1380216

Let’s see the placement of data files in ASM

ASMCMD> ls -l +DATA/MASK11G/DATAFILE

Type Redund Striped Time Sys Name

DATAFILE UNPROT COARSE AUG 22 01:00:00 Y EXAMPLE.259.791946547

DATAFILE UNPROT COARSE AUG 22 01:00:00 Y SYSAUX.258.791946547

DATAFILE UNPROT COARSE AUG 22 01:00:00 Y SYSTEM.257.791946547

DATAFILE UNPROT COARSE AUG 22 01:00:00 Y UNDOTBS1.260.791946547

DATAFILE UNPROT COARSE AUG 22 01:00:00 Y USERS.261.791946549

Controlfile still pointing to old file

RMAN> report schema;

using target database control file instead of recovery catalog

Report of database schema for database with db_unique_name MASK11G

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

—- ——– ——————– ——- ————————

1 680 SYSTEM *** /u01/app/oracle/oradata/mask11g/system01.dbf

2 540 SYSAUX *** /u01/app/oracle/oradata/mask11g/sysaux01.dbf

3 55 UNDOTBS1 *** /u01/app/oracle/oradata/mask11g/undotbs01.dbf

4 5 USERS *** /u01/app/oracle/oradata/mask11g/users01.dbf

5 100 EXAMPLE *** /u01/app/oracle/oradata/mask11g/example01.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

—- ——– ——————– ———– ——————–

1 20 TEMP 32767 /u01/app/oracle/oradata/mask11g/temp01.dbf

So time to use switch command. Actually this command will perform rename file operation for you. Life become so easy with RMAN J

RMAN> switch database to copy;

using target database control file instead of recovery catalog

datafile 1 switched to datafile copy “+DATA/mask11g/datafile/system.257.791946547”

datafile 2 switched to datafile copy “+DATA/mask11g/datafile/sysaux.258.791946547”

datafile 3 switched to datafile copy “+DATA/mask11g/datafile/undotbs1.260.791946547”

datafile 4 switched to datafile copy “+DATA/mask11g/datafile/users.261.791946549”

datafile 5 switched to datafile copy “+DATA/mask11g/datafile/example.259.791946547”

Last hurdle is tempfile which need to be fixed manually. We will do it later.

RMAN> report schema;

Report of database schema for database with db_unique_name MASK11G

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

—- ——– ——————– ——- ————————

1 680 SYSTEM *** +DATA/mask11g/datafile/system.257.791946547

2 540 SYSAUX *** +DATA/mask11g/datafile/sysaux.258.791946547

3 55 UNDOTBS1 *** +DATA/mask11g/datafile/undotbs1.260.791946547

4 5 USERS *** +DATA/mask11g/datafile/users.261.791946549

5 100 EXAMPLE *** +DATA/mask11g/datafile/example.259.791946547

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

—- ——– ——————– ———– ——————–

1 20 TEMP 32767 /u01/app/oracle/oradata/mask11g/temp01.dbf

As redo logs were available so RMAN did auto recovery after picking changes from redos otherwise incomplete recovery would be the last option.

RMAN> recover database;

Starting recover at 22-AUG-12

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/oradata/mask11g/redo02.log

archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/oradata/mask11g/redo03.log

archived log file name=/u01/app/oracle/oradata/mask11g/redo02.log thread=1 sequence=29

archived log file name=/u01/app/oracle/oradata/mask11g/redo03.log thread=1 sequence=30

media recovery complete, elapsed time: 00:00:01

Finished recover at 22-AUG-12

Although it’s complete recovery yet control file was used from backup so resetlogs option must be used.

RMAN> sql ‘alter database open resetlogs’;

sql statement: alter database open resetlogs

Contents from alert log file.

Wed Aug 22 01:29:08 2012

alter database open noresetlogs

ORA-1588 signalled during: alter database open noresetlogs…

Wed Aug 22 01:29:20 2012

alter database open resetlogs

Archived Log entry 24 added for thread 1 sequence 28 ID 0x67bcba21 dest 1:

Archived Log entry 25 added for thread 1 sequence 29 ID 0x67bcba21 dest 1:

Archived Log entry 26 added for thread 1 sequence 30 ID 0x67bcba21 dest 1:

RESETLOGS after complete recovery through change 1405672

Resetting resetlogs activation ID 1740421665 (0x67bcba21)

Wed Aug 22 01:29:28 2012

Setting recovery target incarnation to 3

Wed Aug 22 01:29:28 2012

Assigning activation ID 1751315622 (0x6862f4a6)

. . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . .

Completed: alter database open resetlogs

Time to put tempfile in ASM as temp files don’t have anything meaningful so better to add new tempfile in ASM and drop the old from filesystem.

SQL> alter tablespace temp add tempfile ‘+DATA’ size 200M;

Tablespace altered.

SQL> alter tablespace temp drop tempfile ‘/u01/app/oracle/oradata/mask11g/temp01.dbf’;

Tablespace altered.

Now it’s turn for redo logs. Recommended option is to add mirror and drop the second. Although new log groups could be added followed by deletion of old groups as well.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———

1 1 1 52428800 512 1 NO CURRENT 1405673 22-AUG-12 2.8147E+14

2 1 0 52428800 512 1 YES UNUSED 0 0

3 1 0 52428800 512 1 YES UNUSED 0 0

SQL> col member for a60

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ———————————————————— —

3 ONLINE /u01/app/oracle/oradata/mask11g/redo03.log NO

2 ONLINE /u01/app/oracle/oradata/mask11g/redo02.log NO

1 ONLINE /u01/app/oracle/oradata/mask11g/redo01.log NO

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.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ———————————————————— —

3 ONLINE /u01/app/oracle/oradata/mask11g/redo03.log NO

2 ONLINE /u01/app/oracle/oradata/mask11g/redo02.log NO

1 ONLINE /u01/app/oracle/oradata/mask11g/redo01.log NO

1 INVALID ONLINE +DATA/mask11g/onlinelog/group_1.263.791949135 NO

2 INVALID ONLINE +DATA/mask11g/onlinelog/group_2.264.791949149 NO

3 INVALID ONLINE +DATA/mask11g/onlinelog/group_3.265.791949159 NO

6 rows selected.

Now all new log members are listed with INVALID status, which will be cleared once redo will be used very first time. We will get error if we try to drop a valid member when other members are invalid.

SQL> alter database drop logfile member ‘/u01/app/oracle/oradata/mask11g/redo03.log’;

alter database drop logfile member ‘/u01/app/oracle/oradata/mask11g/redo03.log’

*

ERROR at line 1:

ORA-00362: member is required to form a valid logfile in group 3

ORA-01517: log member: ‘/u01/app/oracle/oradata/mask11g/redo03.log’

So better to perform logswitch and let new member take responsibility of valid member.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ———————————————————— —

3 ONLINE /u01/app/oracle/oradata/mask11g/redo03.log NO

2 ONLINE /u01/app/oracle/oradata/mask11g/redo02.log NO

1 ONLINE /u01/app/oracle/oradata/mask11g/redo01.log NO

1 ONLINE +DATA/mask11g/onlinelog/group_1.263.791949135 NO

2 ONLINE +DATA/mask11g/onlinelog/group_2.264.791949149 NO

3 ONLINE +DATA/mask11g/onlinelog/group_3.265.791949159 NO

6 rows selected.

Now you can drop mirror copy except from current redo log. That’s for security purpose in case remaining redo member got corrupted.

SQL> alter database drop logfile member ‘/u01/app/oracle/oradata/mask11g/redo03.log’;

Database altered.

SQL> alter database drop logfile member ‘/u01/app/oracle/oradata/mask11g/redo02.log’;

Database altered.

SQL> alter database drop logfile member ‘/u01/app/oracle/oradata/mask11g/redo01.log’;

alter database drop logfile member ‘/u01/app/oracle/oradata/mask11g/redo01.log’

*

ERROR at line 1:

ORA-01609: log 1 is the current log for thread 1 – cannot drop members

ORA-00312: online log 1 thread 1: ‘/u01/app/oracle/oradata/mask11g/redo01.log’

ORA-00312: online log 1 thread 1: ‘+DATA/mask11g/onlinelog/group_1.263.791949135’

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile member ‘/u01/app/oracle/oradata/mask11g/redo01.log’;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

———- ——- ——- ———————————————————— —

1 ONLINE +DATA/mask11g/onlinelog/group_1.263.791949135 NO

2 ONLINE +DATA/mask11g/onlinelog/group_2.264.791949149 NO

3 ONLINE +DATA/mask11g/onlinelog/group_3.265.791949159 NO

Now we are proceeding to move spfile to ASM. During this step oracle will automatically create pfile pointing to spfile stored in ASM

RMAN> restore spfile to ‘+DATA/spfilemask11g.ora’;

Starting restore at 22-AUG-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: restoring SPFILE

output file name=+DATA/spfilemask11g.ora

channel ORA_DISK_1: reading from backup piece /backup/autobkp/c-1740440609-20120822-01

channel ORA_DISK_1: piece handle=/backup/autobkp/c-1740440609-20120822-01 tag=TAG20120822T015807

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 22-AUG-12

Now add database to grid.

[oracle@mask11g.lgk.nmk] srvctl add database -d mask11g -o /u01/app/oracle/product/11.2.0/dbhome_1 -t immediate -a “DATA,FRA” -p +DATA/spfilemask11g.ora

[oracle@mask11g.lgk.nmk] srvctl config database

mask11g

[oracle@mask11g.lgk.nmk] srvctl config database -d mask11g

Database unique name: mask11g

Database name:

Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1

Oracle user: oracle

Spfile: +DATA/spfilemask11g.ora

Domain:

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Disk Groups: DATA,FRA

Services:

After registering database with CRS, we need to start it from srvctl to complete the configuration.

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@mask11g.lgk.nmk] srvctl start database -d mask11g

[oracle@mask11g.lgk.nmk] crsctl status resource -t

——————————————————————————–

NAME TARGET STATE SERVER STATE_DETAILS

——————————————————————————–

Local Resources

——————————————————————————–

ora.DATA.dg

ONLINE ONLINE mask11g

ora.FRA.dg

ONLINE ONLINE mask11g

ora.LISTENER.lsnr

ONLINE ONLINE mask11g

ora.asm

ONLINE ONLINE mask11g Started

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.cssd

1 ONLINE ONLINE mask11g

ora.diskmon

1 ONLINE ONLINE mask11g

ora.mask11g.db

1 ONLINE ONLINE mask11g Open

While checking alert log I noticed that dependency is now established J

SUCCESS: diskgroup DATA was mounted

SUCCESS: diskgroup FRA was mounted

NOTE: dependency between database mask11g and diskgroup resource ora.DATA.dg is established

NOTE: dependency between database mask11g and diskgroup resource ora.FRA.dg is established

RMAN> list archivelog all;

using target database control file instead of recovery catalog

List of Archived Log Copies for database with db_unique_name MASK11G

=====================================================================

Key Thrd Seq S Low Time

——- —- ——- – ———

27 1 1 A 22-AUG-12

Name: +FRA/mask11g/archivelog/2012_08_22/thread_1_seq_1.260.791949249

28 1 2 A 22-AUG-12

Name: +FRA/mask11g/archivelog/2012_08_22/thread_1_seq_2.261.791949251

One key feature of ASMCMD with 11gR2 is showing that oracle is now working towards setting up similar command set as OS. So we can copy in and out b/w filesystem and ASM.

ASMCMD> cp current.256.791945099 /tmp/control.bkp

copying +DATA/MASK11G/CONTROLFILE/current.256.791945099 -> /tmp/control.bkps

If you don’t have luxury to afford so many databases on your test machine then no need to use manual time consuming process of removing file by file from different locations. Better use ‘drop database;’ command.

But don’t forget to remove database from grid before droping otherwise next time on server start you may got few errors in logs J

[oracle@mask11g.lgk.nmk] srvctl remove database -d mask11g

Remove the database mask11g? (y/[n]) y

SQL> startup mount exclusive restrict;

ORACLE instance started.

. . . . . . . . . . .

Database mounted.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Advertisements

Share Your Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Pierre blog

Pierre Forstmann Oracle Database blog

flashdba

Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

%d bloggers like this: