In previous Articles we have seen conversion from Single Instance DB to RAC db using various methods.In this Article we will see conversion of RAC to Normal DB[Single Instance].
Prerequisites :
| RAC Instance Node | RAC Node Name | RAC DB Name | RAC Instance Name | Single Instance DB name |
| Node 1 | RAC1 | test | db1 | test |
| Node 2 | RAC2 | test | db2 |
Step 1 : Check Status of Database
[oracle@rac1 ~]$ srvctl status database -d test Instance db1 is running on node rac1 Instance db2 is running on node rac2 [oracle@rac1 ~]$
Step 2 : Stop Database using srvctl
[oracle@rac1 ~]$ srvctl stop database -d test [oracle@rac1 ~]$ srvctl status database -d test Instance db1 is not running on node rac1 Instance db2 is not running on node rac2 [oracle@rac1 ~]$
Step 3: Remove Instance from database :
[oracle@rac1 ~]$ srvctl remove instance -d test -i db1 Remove instance from the database test? (y/[n]) y [oracle@rac1 ~]$ srvctl remove instance -d test -i db2 -f
Step 4: Start database on One Instance :
[oracle@rac1 ~]$ export ORACLE_SID=db1 [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 22 00:34:25 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 392495104 bytes Fixed Size 2253584 bytes Variable Size 239078640 bytes Database Buffers 146800640 bytes Redo Buffers 4362240 bytes Database mounted. Database opened. SQL>
Step 5 : Disable cluster specific parameters :
SQL> alter system set cluster_database=false scope=spfile; System altered. SQL> SQL> show parameter cluster_Database NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE cluster_database_instances integer 2 SQL> SQL> SQL> show parameter cluster_database_instances NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database_instances integer 2 SQL> SQL> alter system set cluster_database_instances=1 scope=spfile; System altered.
Step 6 : Disable thread 2 :
SQL> alter database disable thread 2; Database altered.
Step 7 : Drop redo logs associated with thread 2 :
SQL> select thread#, group# from v$log order by 1; THREAD# GROUP# ---------- ---------- 1 1 1 2 1 3 2 4 2 5 2 6 6 rows selected. SQL> alter database drop logfile group 4; Database altered. SQL> alter database drop logfile group 5; Database altered. SQL> alter database drop logfile group 6; Database altered. SQL> select thread#, group# from v$log order by 1; THREAD# GROUP# ---------- ---------- 1 1 1 3 1 2 SQL> select thread#, group# from v$log order by 1; THREAD# GROUP# ---------- ---------- 1 1 1 3 1 2 SQL>
Step 8 : Drop undo tablespace asscoiated with thread 2 :
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> SQL> drop tablespace UNDOTBS2 including contents and datafiles; Tablespace dropped.
Step 9 : Create pfile from spfile and shutdown database :
SQL> create pfile from spfile; File created. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down.
Step 10 : Remove all parameters related to db2 from pfile :
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs [oracle@rac1 dbs]$ [oracle@rac1 dbs]$ cat initdb1.ora test.__db_cache_size=192937984 db1.__db_cache_size=205520896 test.__java_pool_size=4194304 db1.__java_pool_size=4194304 test.__large_pool_size=71303168 db1.__large_pool_size=8388608 test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment test.__pga_aggregate_target=134217728 db1.__pga_aggregate_target=134217728 test.__sga_target=394264576 db1.__sga_target=394264576 test.__shared_io_pool_size=0 db1.__shared_io_pool_size=0 test.__shared_pool_size=117440512 db1.__shared_pool_size=167772160 test.__streams_pool_size=0 db1.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/test/adump' *.audit_trail='db' *.cluster_database=FALSE *.cluster_database_instances=1 *.compatible='11.2.0.4.0' *.control_files='+DATA/test/controlfile/current.323.970676681','+DATA/test/controlfile/current.324.970676683'#Restore Controlfile *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_name='test' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=10737418240 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)' db1.instance_number=1 *.log_archive_dest_1='LOCATION=/u01/arc/test' *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=300 *.pga_aggregate_target=131072000 *.processes=150 *.recyclebin='ON' *.remote_listener='rac-scan:1521' *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=393216000 db1.thread=1 db1.undo_tablespace='UNDOTBS1'
Here we can see that I have removed all parameters related to db2.
Step 11 : Create spfile from pfile and start database :
[oracle@rac1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 22 00:49:55 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup 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 Database mounted. Database opened. SQL>
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
