Saturday 20 February 2016

Steps To Migrate a Database From Non-ASM to ASM ( Rman & Rconfig)

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. Please refer my previous article to create a ASM instance

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

#################################################



Convert Single Instance to RAC using RCONFIG


The following oracle supported methods are available to convert a single-instance database to a RAC database as long as the RAC and the standalone environments are running on the same OS and using the same oracle release:

1. RCONFIG
2. DBCA
3. Oracle Enterprise Manager (grid control)
4. Manual method

Here we will see how to convert single intance database to RAC using RCONFIG.

During the conversion, rconfig performs the following steps automatically:
• Migrating the database to ASM, if specified
• Creating RAC database instances on all specified nodes in the cluster
• Configuring the Listener and NetService entries
• Registering services with CRS
• Starting up the instances and listener on all nodes

In Oracle 11g R2, a single-instance database can either be converted to an administrator-managed cluster database or a policy-managed cluster database.

The difference between administrator managed and policy managed cluster database is given below in Blue,

Server pools are logical divisions of a cluster into pools of servers, which are allocated to host databases or other applications. Server pools are managed using crsctl and srvctl commands.
Caution:

By default, any named user may create a server pool. To restrict the operating system users that have this privilege, Oracle strongly recommends that you add specific users to the CRS Administrators list.
Each server pool name must be unique within the cluster. Two server pools cannot use the same name.
There are two types of server pool management:

Administrator-managed: Database administrators define the servers on which databases resource run, and place resources manually as needed. This is the management strategy used in previous releases.

Policy managed: Database administrators specify in which server pool (excluding generic or free) the database resource will run. Oracle Clusterware is responsible for placing the database resource on a server.
The server pool name is a required attribute. You can also provide values for the following attributes; if you do not specify them, then they are set to the default value:
MIN_SIZE: Minimum number of servers on which you want a resource to run. The default is 0.
MAX_SIZE: Maximum number of servers on which you want a resource to run. The default is -1, which indicates that resources can run on all available nodes in the cluster.
IMPORTANCE: The relative importance of the resource pool, used to determine how to reconfigure servers when a node joins or leaves the cluster. The default is 0.

Note: Clients using Oracle Database 11g release 2 and later databases using policy-managed server pools must access the database using the Single Client Access Name (SCAN). This is required because policy-managed databases can run on different servers at different times, so connecting to a particular node virtual IP address for a policy-managed database is not possible.

When you navigate through the $ORACLE_HOME/assistants/rconfig/sampleXMLS, you will find two sample XML input files.

- ConvertToRAC_AdminManaged.xml
- ConvertToRAC_PolicyManaged.xml

While converting a single-instance database, with filesystem storage, to an RAC database with Automatic Storage Management (ASM), rconfig invokes RMAN internally to back up the database to proceed with converting non-ASM to ASM.

CURRENT SCENARIO:-

2 node RAC setup
- Names of nodes : egtodb01, egtodb02

- Name of single instance database with filesystem storage : ebsuat

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

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

OBJECTIVE
- convert ebsuat to a Admin managed RAC database running on two nodes egtodb01 and egtodb02.

- change storage to ASM with
   . Datafiles on +DATA diskgroup
   . Flash recovery area on +FRA diskgroup

IMPLEMENTATION:– copy ConvertToRAC_AdminManaged.xml to another file my.xml

egtodb01$ cd $ORACLE_HOME/assistants/rconfig/sampleXMLs
egtodb01$ cp ConvertToRAC_AdminManaged.xml my.xml

– Edit my.xml and make following changes :
   . Specify current OracleHome of non-rac database for SourceDBHome
   . Specify OracleHome where the rac database should be configured. It can be same as SourceDBHome
   . Specify SID of non-rac database and credential. User with sysdba role is required to perform conversion
   . Specify the list of nodes that should have rac instances running for the Admin Managed Cluster Database
   . LocalNode should be the first node in this nodelist.
   . Instance Prefix tag is optional starting with 11.2. If left empty, it is derived from db_unique_name
   . Specify the type of storage to be used by rac database. Allowable values are CFS|ASM
   . Specify Database Area Location to be configured for rac database.
   . Specify Flash Recovery Area to be configured for rac database.

– Run rconfig to convert ebsuat from single instance database to 2 instance RAC database

egtodb01$ rconfig my.xml

– Check the log file for rconfig while conversion is going on
oracle@egtodb01$ ls -lrt  $ORACLE_BASE/cfgtoollogs/rconfig/*.log

– check that the database has been converted successfully

egtodb01$srvctl status database -d ebsuat
Instance ebsuat1 is running on node egtodb01
Instance ebsuat2 is running on node egtodb02

– Note that rconfig adds password file to all the nodes but  entry to tnsnames.ora needs to be modified (to reflect scan name instead of host-ip) on the local node and added to rest of the nodes.

– For all other nodes, copy the entry for the database ebsuat from tnsnames.ora on local node to tnsnames.ora on remote nodes.

– Following is the entry I modified on the local node and copied to rest of the nodes :
ebsuat =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ebsdb-scan.mydomain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ebsuat)
    )
  )

– check that database can be connected remotely from remote node.
egtodb02$sqlplus system/manager@ebsuat

– check that datafiles have converted to ASM

SQL>select name from v$datafile;

NAME
——————————————————————————–
+DATA/ebsuat/datafile/system.326.79483827
+DATA/ebsuat/datafile/sysaux.325.79483834
+DATA/ebsuat/datafile/undotbs1.305.79483805
+DATA/ebsuat/datafile/users.342.79483841
+DATA/ebsuat/datafile/undotbs2.348.79483

No comments:

Post a Comment