Thursday 11 June 2015

Steps To Migrate a Database from Non-ASM to ASM

You can use these steps to migrate a NON-ASM database to ASM database in Oracle 11gR2.

Prerequisite - ASM instance should be created and up and running.

1) Check the database version and status

   SQL> select INSTANCE_NAME,VERSION,DATABASE_STATUS from v$instance;

    INSTANCE_NAME    VERSION           DATABASE_STATUS
    ---------------- ----------------- -----------------
    learndb          11.2.0.3.0        ACTIVE

2) We should be able to check the ASM disk available from the instance which we created earlier

   SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;


     NAME                           STATE      TOTAL_MB PATH
   ------------------------------ -------- ---------- ----------
   DISK1                          NORMAL         4777 ORCL:DISK1


Migrating the SPFILE from Non-asm to ASM


1) If you are using a spfile, take a backup of spfile using rman to restore it to ASM disk

   RMAN>BACKUP AS BACKUPSET SPFILE;

2) Now shutdown the database and start it in Mount stage
   SQL> Shutdown immediate

   SQL> STARTUP MOUNT;

3) Now restore the spfile from the backup to ASM disk as below

  RMAN>RESTORE SPFILE TO '+DATA/spfilelearndb.ora';

4) You can see, still the parameter "spfile" is pointing to file system instead of ASM disk

   SQL> show parameter spfile

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    spfile                               string      /opt/oracle/product/11.2.0/dbh
                                                        ome_1/dbs/spfilelearndb.ora
5) Shutdowm the instance

    SQL> shutdown immediate


6) Delete "spfile<sid>.ora and init<sid>.ora from the $ORACLE_HOME/dbs directory and create a new init<sid>.ora with the following line of content and start the instance,

    vi initlearndb.ora
   SPFILE='+DATA/spfilelearndb.ora'

   SQL> Startup (  first it will search for spfile<sid>.ora which we deleted and next it will look for      init<sid>.ora which we have moified with the above content  )

   SQL> show parameter spfile

    NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   spfile                               string      +DATA/spfilelearndb.ora


Migrating Control files from Non-ASM to ASM


1) These are the current control files in non-asm

SQL>show parameter contol_files
         control_files                        string      /u01/data/learndb/control01.ctl,
                                                                 /u01/data/learndb/control02.ctl


2) Start the instance in nomount state

SQL> startup nomount

3) You can move these control files to ASM using RMAN

    [oracle@coltdb04 ~]$ rman target /

    Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:04:39 2012

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

    connected to target database: LEARNDB (not mounted)


    RMAN>restore controlfile to '+DATA' from '/u01/data/learndb/control01.ctl';


4) You can check whether the control file are created

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

   +DATA/LEARNDB/CONTROLFILE/current.256.787200593

5) Update the spfile with new control file location

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

6) Now shutdown the instance and startup in mount stage and check for the control file location
  
   SQL> shutdown immediate

   SQL> startup mount

   SQL> show parameter control_files


       NAME                                 TYPE        VALUE
     ------------------------------------ ----------- ------------------------------
     control_files                        string      +DATA/learndb/controlfile/curr
                                                      ent.256.787200593


Migrating the Datafiles from Non-ASM to ASM

1) Keep the database is in mount stage

2) Use RMAN "BACKUP AS COPY" method to migrate the datafiles from filesystem to ASM

   [oracle@coltdb04 ~]$ rman target /

   Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:00:40 2012

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

   connected to target database: LEARNDB (DBID=3704858930, not open)
                                            

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

3)  Switch the database to the copy created using the following RMAN command

   RMAN> SWITCH DATABASE TO COPY;

   datafile 1 switched to datafile copy "+DATA/learndb/datafile/system.258.787201633"
   datafile 2 switched to datafile copy "+DATA/learndb/datafile/sysaux.257.787201553"
   datafile 3 switched to datafile copy "+DATA/learndb/datafile/undotbs1.259.787201713"
   datafile 4 switched to datafile copy "+DATA/learndb/datafile/users.261.787201725"

4) Migrate the tempfile to ASM using RMAN

   RMAN> run
   2> {
   3> set newname for tempfile '/u01/data/learndb/temp01.dbf' to '+DATA';
   4> switch tempfile all;
   5> }

   executing command: SET NEWNAME

   using target database control file instead of recovery catalog

   renamed tempfile 1 to +DATA in control file

5) Now open the Database

   RMAN> ALTER DATABASE OPEN;

   database opened

6) You can now check the datafiles created in ASM
  
   SQL> select FILE_NAME from dba_data_files;

   FILE_NAME
   --------------------------------------------------------------------------------
   +DATA/learndb/datafile/users.261.787201725
   +DATA/learndb/datafile/undotbs1.259.787201713
   +DATA/learndb/datafile/sysaux.257.787201553
   +DATA/learndb/datafile/system.258.787201633


Migrating the Redo log files from Non-ASM to ASM

1) Identify the currently available redo log files using the following command

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

    GROUP#           MEMBER                STATUS
   ----------       ---------              -----------
  
        3    /u01/data/learndb/redo03.log INACTIVE

        2    /u01/data/learndb/redo02.log CURRENT

        1    /u01/data/learndb/redo01.log INACTIVE

2) Add the new logfiles to ASM using following command

   ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1; 
   ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;
   ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;

3) Now drop the old logfiles, A redo log member can only be dropped after being archived and being in INACTIVE mode. If needed, switch logfile multiple times until the logfile is ready for dropping.

   ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo01.log';
   ALTER SYSTEM SWITCH LOGFILE;
   ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo02.log';
   ALTER SYSTEM SWITCH LOGFILE;
   ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo03.log';

4) You can check the logfiles created now in ASM

     GROUP#           MEMBER                STATUS
   ----------       ---------              -----------
         1    +DATA/learndb/onlinelog/group_1.264.787205393   CURRENT

         2    +DATA/learndb/onlinelog/group_2.265.787205405   INACTIVE

         3    +DATA/learndb/onlinelog/group_3.266.787205417   INACTIVE

Now your database is migrated to ASM







I hope this article helped you. Your suggestions/feedbacks are most welcome.
Keep learning... Have a great day!!!

Monday 1 June 2015

Recovery of database using RMAN backup when system datafile is lost

DB Name: TESTDB

[oracle@test dbf]$   ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@test dbf]$  rm system01.dbf

SQL> select * from sys.aud$;
select * from sys.aud$
                  *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


Note: Since the normal shutdown is not possible in this case we shall use shut abort

SQL> shut abort
ORACLE instance shut down
[oracle@test dbf]$ sqlplus / as sysdba
SQL> startup mount


[oracle@test dbf]$ rman target/

RMAN> restore datafile 1;

Starting restore at 25-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK

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 /u01/app/oracle/TESTDB/oradata/dbf/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck
channel ORA_DISK_1: piece handle=/u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck tag=FULL_OPEN_BACKUP_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 25-SEP-14



RMAN> recover datafile 1;

Starting recover at 25-SEP-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc
archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_25_859142042.arc
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_26_859142042.arc
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_27_859142042.arc
archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_28_859142042.arc
archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_29_859142042.arc
archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_30_859142042.arc
archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_31_859142042.arc
archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_32_859142042.arc
archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_33_859142042.arc
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc thread=1 sequence=22
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc thread=1 sequence=24
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_25_859142042.arc thread=1 sequence=25
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_26_859142042.arc thread=1 sequence=26
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_27_859142042.arc thread=1 sequence=27
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_28_859142042.arc thread=1 sequence=28
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_29_859142042.arc thread=1 sequence=29
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_30_859142042.arc thread=1 sequence=30
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_31_859142042.arc thread=1 sequence=31
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-SEP-14


sql>alter database open;



It is suggested to take RMAN backup immediately after the activity is over




I hope this article helped you. Your suggestions/feedbacks are most welcome.

Keep learning... Have a great day!!!