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.
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