Ways2Solution: January 2022

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

Step by Step Migration a Oracle Database From Non ASM to ASM

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

Description:-

In this blog we are going to see How to migrate the database from normal Filesystem to Asm Storage.

There are several methods is there to move database from filesystem to Asm diskgroup like

1-RMAN

2-DBMS_FILE_TRANSFER

3-ASMCMD and 12c online Move datafile .

We are using here the RMAN Method to convert database from local filesystem to ASM storage .

First we need to install the 12c Grid Binary on the same server .here we are using for Grid insttalation ,through clone method .

Example for 12c:-

Step 1

[oracle@farukh01 Oracle]$ cd /Oracle/Grid/Ora12c/Sw/clone/bin/

[oracle@farukh01 bin]$ ls -ltr

total 24

-rwxr-x--- 1 oracle oinstall 838 Feb 26 2009 prepare_clone.pl

-rwxr-x--- 1 oracle oinstall 19091 Jan 26 2017 clone.pl

./clone.pl ORACLE_HOME=/Oracle/Grid/Ora12c/Sw ORACLE_HOME_NAME=GI12c ORACLE_BASE=/Oracle/Grid/Ora12c/

[oracle@farukh01 bin]$

[oracle@farukh01 bin]$ pwd

/Oracle/Grid/Ora12c/Sw/clone/bin

[oracle@farukh01 bin]$ ^C

[oracle@farukh01 bin]$ ./clone.pl ORACLE_HOME=/Oracle/Grid/Ora12c/Sw ORACLE_HOME_NAME=GI12c ORACLE_BASE=/Oracle/Grid/Ora12c/

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 5077 MB Passed

Checking swap space: must be greater than 500 MB. Actual 20476 MB Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-01-10_06-06-44PM. Please wait ...Error in CreateOUIProcess(): 13

Solution :

export TMP=$HOME/tmp,

and re run

[oracle@farukh01 bin]$ ./clone.pl ORACLE_HOME=/Oracle/Grid/Ora12c/Sw ORACLE_HOME_NAME=GI12c ORACLE_BASE=/Oracle/Grid/Ora12c/

[oracle@farukh01 bin]$

[oracle@farukh01 bin]$ ./clone.pl ORACLE_HOME=/Oracle/Grid/Ora12c/Sw ORACLE_HOME_NAME=GI12c ORACLE_BASE=/Oracle/Grid/Ora12c/

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB. Actual 5072 MB Passed

Checking swap space: must be greater than 500 MB. Actual 20476 MB Passed

Preparing to launch Oracle Universal Installer from /home/oracle/tmp,/OraInstall2022-01-10_06-28-09PM. Please wait ...[WARNING] [INS-32029] The Installer has detected that the Oracle Base location is not empty.

ACTION: Oracle recommends that the Oracle Base location is empty.

You can find the log of this install session at:

/Oracle/app/oraInventory/logs/cloneActions2022-01-10_06-28-09PM.log

.................................................. 5% Done.

.................................................. 10% Done.

.................................................. 15% Done.

.................................................. 20% Done.

.................................................. 25% Done.

.................................................. 30% Done.

.................................................. 35% Done.

.................................................. 40% Done.

.................................................. 45% Done.

.................................................. 50% Done.

.................................................. 55% Done.

.................................................. 60% Done.

.................................................. 65% Done.

.................................................. 70% Done.

.................................................. 75% Done.

.................................................. 80% Done.

.................................................. 85% Done.

..........

Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.

The cloning of GI12c was successful.

Please check '/Oracle/app/oraInventory/logs/cloneActions2022-01-10_06-28-09PM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.

.................................................. 95% Done.

As a root user, execute the following script(s):

1. /Oracle/app/oraInventory/orainstRoot.sh

2. /Oracle/Grid/Ora12c/Sw/root.sh

.................................................. 100% Done.

[oracle@farukh01 bin]$

[root@farukh01 ~]#

[root@farukh01 ~]# /Oracle/app/oraInventory/orainstRoot.sh

Changing permissions of /Oracle/app/oraInventory.

Adding read,write permissions for group.

Removing read,write,execute permissions for world.

Changing groupname of /Oracle/app/oraInventory to oinstall.

The execution of the script is complete.

[root@farukh01 ~]# /Oracle/Grid/Ora12c/Sw/root.sh

Check /Oracle/Grid/Ora12c/Sw/install/root_farukh01_2022-01-10_18-34-32-460410600.log for the output of root script

[root@farukh01 ~]# more /Oracle/Grid/Ora12c/Sw/install/root_farukh01_2022-01-10_18-34-32-460410600.log

Performing root user operation.

The following environment variables are set as:

ORACLE_OWNER= oracle

ORACLE_HOME= /Oracle/Grid/Ora12c/Sw

Copying dbhome to /usr/local/bin ...

Copying oraenv to /usr/local/bin ...

Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

To configure Grid Infrastructure for a Cluster or Grid Infrastructure for a Stand-Alone Server execute the following command as oracle user:

/Oracle/Grid/Ora12c/Sw/gridSetup.sh

This command launches the Grid Infrastructure Setup Wizard. The wizard also supports silent operation, and the parameters can be passed through the response file that i

s available in the installation media.

[root@farukh01 ~]#

Step 2

[root@farukh01 ~]# /Oracle/Grid/Ora12c/Sw/perl/bin/perl -I /Oracle/Grid/Ora12c/Sw/perl/lib -I /Oracle/Grid/Ora12c/Sw/crs/install /Oracle/Grid/Ora12c/Sw/crs/install/roothas.pl

Using configuration parameter file: /Oracle/Grid/Ora12c/Sw/crs/install/crsconfig_params

The log of current session can be found at:

/Oracle/Grid/Ora12c/crsdata/farukh01/crsconfig/roothas_2022-01-10_06-36-58PM.log

LOCAL ADD MODE

Creating OCR keys for user 'oracle', privgrp 'oinstall'..

Operation successful.

LOCAL ONLY MODE

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

CRS-4664: Node farukh01 successfully pinned.

2022/01/10 18:37:13 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'farukh01'

CRS-2673: Attempting to stop 'ora.evmd' on 'farukh01'

CRS-2677: Stop of 'ora.evmd' on 'farukh01' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'farukh01' has completed

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

farukh01 2022/01/10 18:41:16 /Oracle/Grid/Ora12c/Sw/cdata/farukh01/backup_20220110_184116.olr 3518739597

2022/01/10 18:41:17 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

[root@farukh01 ~]#

cd /Oracle/Grid/Ora12c/Sw/oui/bin

[oracle@farukh01 bin]$ ./runInstaller -updateNodeList ORACLE_HOME=/Oracle/Grid/Ora12c/Sw -defaultHomeName CLUSTER_NODES= CRS=TRUE

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 20476 MB Passed

The inventory pointer is located at /etc/oraInst.loc

'UpdateNodeList' was successful.

cd /Oracle/Grid/Ora12c/Sw/bin

./asmca -silent -configureASM -diskString '/dev/oracleasm/disks/*' -sysAsmPassword Grid#123 -asmsnmpPassword Grid#123 -diskGroupName DATA -diskList /dev/oracleasm/disks/DATA1 -redundancy EXTERNAL

[oracle@farukh01 ~]$ .oraenv

bash: .oraenv: command not found...

[oracle@farukh01 ~]$ . oraenv

ORACLE_SID = [RECON] ? +ASM

The Oracle base has been changed from /Oracle/Ora12c to /Oracle/Grid/Ora12c

[oracle@farukh01 ~]$

[oracle@farukh01 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 10 19:29:09 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

set lines 200

col PATH for a30

select mount_status,header_status,mode_status,state,total_mb/1024,free_mb/1024,name,path,label from v$asm_disk;

MOUNT_S HEADER_STATU MODE_ST STATE TOTAL_MB/1024 FREE_MB/1024 NAME PATH LABEL

------- ------------ ------- -------- ------------- ------------ ------------------------------ ------------------------------ -------------------------------

CLOSED PROVISIONED ONLINE NORMAL 0 0 /dev/oracleasm/disks/DATA3

CLOSED PROVISIONED ONLINE NORMAL 0 0 /dev/oracleasm/disks/CONTROL

CLOSED PROVISIONED ONLINE NORMAL 0 0 /dev/oracleasm/disks/REDO

CLOSED PROVISIONED ONLINE NORMAL 0 0 /dev/oracleasm/disks/DATA4

CLOSED PROVISIONED ONLINE NORMAL 0 0 /dev/oracleasm/disks/DATA2

CACHED MEMBER ONLINE NORMAL 199.999023 199.935547 DATA_0000 /dev/oracleasm/disks/DATA1 DATA1

6 rows selected.

alter diskgroup DATA add disk '/dev/oracleasm/disks/DATA2' ;

./asmca -silent -configureASM -diskString '/dev/oracleasm/disks/*' -diskGroupName CONTROL -diskList /dev/oracleasm/disks/CONTROL -redundancy EXTERNAL

./asmca -silent -configureASM -diskString '/dev/oracleasm/disks/*' -sysAsmPassword Grid#123 -asmsnmpPassword Grid#123 -diskGroupName REDO -diskList /dev/oracleasm/disks/REDO -redundancy EXTERNAL

SQL> select group_number,name from v$asm_diskgroup;

GROUP_NUMBER NAME

------------ ------------------------------

1 DATA

2 CONTROL

3 REDO

We are going to migrate the below database "RECON" and Disk Group Name is "+DATA"

ASM Instance is already Created and Ready to Use see the below link.

Step by Step configuration ASM for a Standalone Database Here

Let’s Start the Activity :-

Pre-Steps

Step -1

SQL> select instance_name,version,database_status from v$instance ;

INSTANCE_NAME VERSION DATABASE_STATUS

---------------- ----------------- -----------------

RECON 12.2.0.1.0 ACTIVE

SQL> select file#,name from v$datafile ;

FILE# NAME

---------- -------------------------------------------------------

1 /u01/datafile/Recon/system01.dbf

2 /u02/datafile/Recon/RECON_TS_02.dbf

3 /u01/datafile/Recon/sysaux01.dbf

4 /u01/datafile/Recon/undotbs01.dbf

5 /u02/datafile/Recon/RECON_TS_01.dbf

7 /u01/datafile/Recon/users01.dbf

8 /u03/datafile/Recon/RECON_TS_03.dbf

9 /u03/datafile/Recon/RECON_TS_04.dbf

10 /u02/datafile/Recon/RECON_INDX_01.dbf

11 /u03/datafile/Recon/RECON_INDX_01.dbf

12 /u01/datafile/Recon/DEDUP_SMALL_01.dbf

13 /u01/datafile/Recon/DEDUP_IND_SMALL_01.dbf

14 /u01/datafile/Recon/DEDUP_IND_LARGE_01.dbf

15 /u01/datafile/Recon/DEDUP_LARGE_01.dbf

16 /u03/datafile/Recon/undo05042018.dbf

17 /u03/datafile/Recon/RECON_INDX_02.dbf

18 /u02/datafile/Recon/recon_ts12042018.dbf

19 /u03/datafile/Recon/RECON_TS_05.dbf

20 /u01/datafile/Recon/RECON_TS_11062018.dbf

21 /u02/datafile/Recon/RECON_INDX_03.dbf

22 /u03/datafile/Recon/RECON_INDX_04.dbf

23 /u01/datafile/Recon/RECON_TS_07.dbf

24 /u01/datafile/Recon/RECON_INDX_001.dbf

25 /u02/datafile/Recon/recon_ts14112018.dbf

24 rows selected.

SQL> select name,state,total_mb/1024,path from v$asm_disk ;

NAME STATE TOTAL_MB/1024 PATH

------------------------------------------------------- -------- ------------- ----------------------------------------

DATA_0003 NORMAL 199.999023 /dev/oracleasm/disks/DATA4

REDO_0000 NORMAL 49.9990234 /dev/oracleasm/disks/REDO

CONTROL_0000 NORMAL 49.9990234 /dev/oracleasm/disks/CONTROL

DATA_0002 NORMAL 199.999023 /dev/oracleasm/disks/DATA3

DATA_0001 NORMAL 199.999023 /dev/oracleasm/disks/DATA2

DATA_0000 NORMAL 199.999023 /dev/oracleasm/disks/DATA1

6 rows selected.

SQL> select name from v$controlfile;

NAME

-------------------------------------------------------

/u01/controlfile/control01.ctl

/redo/controlfile/control02.ctl

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/redo/Redologs/redo03a.log

/redo/Redologs/redo03b.log

/redo/Redologs/redo02a.log

/redo/Redologs/redo02b.log

/redo/Redologs/redo01a.log

/redo/Redologs/redo01b.log

14 rows selected.

Migration steps:

Step1:-Login in to "RECON" Database change the below parameters

SQL> ALTER SYSTEM SET control_files='+CONTROL' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET db_create_file_dest='+DATA' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest='+DATA' scope=spfile;

System altered.

SQL>

SQL> alter system set db_recovery_file_dest_size=50G scope=both sid='*';

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Step-2:- Startup the "RECON" database in NOMOUNT mode

sqlplus / as sysdba

sql> startup nomount

Step -3:- Connect to Rman Session to copy the controlfile from local filesystem to ASM Diskgroup "+DATA"

RMAN> RESTORE CONTROLFILE FROM '/u01/controlfile/control01.ctl';

Starting restore at 11-JAN-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=272 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+CONTROL/RECONN/CONTROLFILE/current.256.1093709339

Finished restore at 11-JAN-22

Step -4:- Connect to SQL*Plus and mount the database.

[oracle@farukh01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 11 16:10:52 2022

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

+CONTROL/RECONN/CONTROLFILE/current.256.1093709339

SQL>

Step -5:- Again connect to RMAN session to copy the database files from the local filesystem to ASM Diskgroup "+DATA"

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

Starting backup at 11-JAN-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=276 device type=DISK