Converting RAC DB to Non-RAC on Normal File System

To convert RAC to Non-RAC DB on normal file system we need to first convert it into ASM storage

Converting RAC db to Non-RAC db

In this article we will see conversion database from ASM to Normal File System :

Step 1 : Check details of ASM database and create pfile from spfile :

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 23 22:52:52 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 23 22:52:52 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
TEST

SQL>
SQL>
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/test/datafile/system.311.970676549
+DATA/test/datafile/sysaux.310.970676581
+DATA/test/datafile/undotbs1.319.970676607
+DATA/test/datafile/users.320.970676613

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfiletest.ora
SQL> create pfile from spfile;

File created.

SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step 2 : Change control_file parameter to destination you want on your normal FS.

Step 3 : Create spfile from pfile and start database in nomount stage :

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 23 23:30:25 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 392495104 bytes
Fixed Size 2253584 bytes
Variable Size 188746992 bytes
Database Buffers 197132288 bytes
Redo Buffers 4362240 bytes
SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/test/c
ontrol01.ctl, /u01/app/oracle/
oradata/test/control02.ctl
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Here we can see control_file parameter is changed to normal FS destination.

Step 4 : connect to RMAN and restore controlfile from controlfile located at ASM storage.

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 23 23:31:22 2018

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

connected to target database: TEST (not mounted)

RMAN> restore controlfile from '+DATA/test/controlfile/current.323.970676681';

Starting restore at 23-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/test/control01.ctl
output file name=/u01/app/oracle/oradata/test/control02.ctl
Finished restore at 23-MAR-18

Step 5 : Alter database to mount stage :

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Step 5 : Take RMAN database backup as copy backup :

RMAN> backup as copy database format '/u01/app/oracle/oradata/test/%U.dbf';

Starting backup at 23-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/test/datafile/system.311.970676549
output file name=/u01/app/oracle/oradata/test/data_D-TEST_I-2268489675_TS-SYSTEM_FNO-1_0csuhr9a.dbf tag=TAG20180323T233258 RECID=9 STAMP=971566421
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/test/datafile/sysaux.310.970676581
output file name=/u01/app/oracle/oradata/test/data_D-TEST_I-2268489675_TS-SYSAUX_FNO-2_0dsuhrao.dbf tag=TAG20180323T233258 RECID=10 STAMP=971566465
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/test/datafile/undotbs1.319.970676607
output file name=/u01/app/oracle/oradata/test/data_D-TEST_I-2268489675_TS-UNDOTBS1_FNO-3_0esuhrc6.dbf tag=TAG20180323T233258 RECID=11 STAMP=971566474
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/test/datafile/users.320.970676613
output file name=/u01/app/oracle/oradata/test/data_D-TEST_I-2268489675_TS-USERS_FNO-4_0fsuhrcd.dbf tag=TAG20180323T233258 RECID=12 STAMP=971566477
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-MAR-18

Starting Control File and SPFILE Autobackup at 23-MAR-18
piece handle=+DATA/test/autobackup/2018_03_23/s_971565717.329.971566481 comment=NONE
Finished Control File and SPFILE Autobackup at 23-MAR-18

RMAN>
RMAN>

Step 6 : With RMAN session switch database to copy backup we have taken.

RMAN> switch database to copy;

datafile 1 switched to datafile copy "/u01/app/oracle/oradata/test/data_D-TEST_I-2268489675_TS-SYSTEM_FNO-1_0csuhr9a.dbf"
datafile 2 switched to datafile copy "/u01/app/oracle/oradata/test/data_D-TEST_I-2268489675_TS-SYSAUX_FNO-2_0dsuhrao.dbf"
datafile 3 switched to datafile copy "/u01/app/oracle/oradata/test/data_D-TEST_I-2268489675_TS-UNDOTBS1_FNO-3_0esuhrc6.dbf"
datafile 4 switched to datafile copy "/u01/app/oracle/oradata/test/data_D-TEST_I-2268489675_TS-USERS_FNO-4_0fsuhrcd.dbf"

RMAN>

Step 7 : Set newname for tempfile using following script :

RMAN> run {
2> set newname for tempfile 1 to '/u01/app/oracle/oradata/test/tempfile';
3> switch tempfile all;
4> }

executing command: SET NEWNAME

RMAN>

Step 8 : Open database from sqlplus session :

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 23 23:39:07 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
TEST MOUNTED

SQL> alter database open;

Database altered.

SQL>

Now , lets work on redo logfiles :

Step 9 : Add redo logs to new destination :

SQL> set lines 1000
SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---
1 ONLINE +DATA/test/onlinelog/group_1.331.970676801 NO
1 ONLINE +DATA/test/onlinelog/group_1.332.970676801 YES
2 ONLINE +DATA/test/onlinelog/group_2.333.970676803 NO
2 ONLINE +DATA/test/onlinelog/group_2.334.970676803 YES
3 ONLINE +DATA/test/onlinelog/group_3.335.970676805 NO
3 ONLINE +DATA/test/onlinelog/group_3.336.970676805 YES

6 rows selected.
SQL> alter database add logfile group 4 '/u01/app/oracle/oradata/test/redo04.log' size 250m;

Database altered.

SQL> alter database add logfile group 5 '/u01/app/oracle/oradata/test/redo05.log' size 250m;

Database altered.

SQL> alter database add logfile group 6 '/u01/app/oracle/oradata/test/redo06.log' size 250m;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---
1 ONLINE +DATA/test/onlinelog/group_1.331.970676801 NO
1 ONLINE +DATA/test/onlinelog/group_1.332.970676801 YES
2 ONLINE +DATA/test/onlinelog/group_2.333.970676803 NO
4 ONLINE /u01/app/oracle/oradata/test/redo04.log NO
5 ONLINE /u01/app/oracle/oradata/test/redo05.log NO
6 ONLINE /u01/app/oracle/oradata/test/redo06.log NO
2 ONLINE +DATA/test/onlinelog/group_2.334.970676803 YES
3 ONLINE +DATA/test/onlinelog/group_3.335.970676805 NO
3 ONLINE +DATA/test/onlinelog/group_3.336.970676805 YES

9 rows selected.

Step 10 : Check status of Redo logs if it is not in current status drop it .

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 44 52428800 512 2 YES INACTIVE 1663747 22-MAR-18 1689826 23-MAR-18
2 1 45 52428800 512 2 NO CURRENT 1689826 23-MAR-18 2.8147E+14
3 1 43 52428800 512 2 YES INACTIVE 1663666 22-MAR-18 1663747 22-MAR-18
4 1 0 262144000 512 1 YES UNUSED 0 0
5 1 0 262144000 512 1 YES UNUSED 0 0
6 1 0 262144000 512 1 YES UNUSED 0 0

6 rows selected.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
2 1 45 52428800 512 2 NO CURRENT 1689826 23-MAR-18 2.8147E+14
4 1 0 262144000 512 1 YES UNUSED 0 0
5 1 0 262144000 512 1 YES UNUSED 0 0
6 1 0 262144000 512 1 YES UNUSED 0 0

Step 11 : We can see here group 2 is in current status , so we need to do checkpointing and archiving process.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
2 1 45 52428800 512 2 YES INACTIVE 1689826 23-MAR-18 1696689 23-MAR-18
4 1 46 262144000 512 1 NO CURRENT 1696689 23-MAR-18 2.8147E+14
5 1 0 262144000 512 1 YES UNUSED 0 0
6 1 0 262144000 512 1 YES UNUSED 0 0

Step 12 : Drop redo log of group 2 :

SQL> alter database drop logfile group 2;

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---
4 ONLINE /u01/app/oracle/oradata/test/redo04.log NO
5 ONLINE /u01/app/oracle/oradata/test/redo05.log NO
6 ONLINE /u01/app/oracle/oradata/test/redo06.log NO

SQL>

We can see here , database is fully converted to Normal File System .

Stay tuned for More articles on Oracle RAC 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Tagged:

About The Author

I am 11g OCP and OCE(RAC and GRID Infrastructure). Currently working as Oracle Database Administrator at _VOIS and Blogger (Freelancer) at Oracle-help . Previously I worked with Acute Informatics pvt ltd. and Mobile Tornado where I handled Banking Data Migration , created DR using log shipping , Golden Gate and Data Guard. I have experience of handling planned and unplanned DB role switchover and failovers and monitoring and troubleshooting Database Performance. Key skills : RMAN , GG , DG , RAC , Virtualization in OVMM.I have experience in Banking and Telecom domain. Reach me at : https://www.linkedin.com/in/jagruti-jasleniya

Comments

  1. Pingback: Restore RAC to no-RAC | ivannexus

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.