Oracle Clusterware 11gR2 – CRS-4639: Could not contact Oracle High Availability Services
# crsctl stat res -t
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Start failed, or completed with errors.
# crsctl start crs
CRS-4013: This command is not supported in a single-node configuration.
CRS-4000: Command Start failed, or completed with errors.
[root@b1 grid]# cd /u01/app/11.2.0/grid/crs/install
[root@b1 install]# ./roothas.pl -deconfig -force -verbose
[root@b1 install]# cd /u01/app/11.2.0/grid/
[root@b1 grid]# ./root.sh
User ignored Prerequisites during installation
OLR initialization - successful
Adding Clusterware entries to upstart
CRS-2672: Attempting to start 'ora.mdnsd' on 'rac2'
CRS-2676: Start of 'ora.mdnsd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rac2'
CRS-2676: Start of 'ora.gpnpd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac2'
CRS-2672: Attempting to start 'ora.gipcd' on 'rac2'
CRS-2676: Start of 'ora.cssdmonitor' on 'rac2' succeeded
CRS-2676: Start of 'ora.gipcd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rac2'
CRS-2672: Attempting to start 'ora.diskmon' on 'rac2'
CRS-2676: Start of 'ora.diskmon' on 'rac2' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac2' succeeded
PRKO-2190 : VIP exists for node rac2, VIP name rac2-vip
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
[oracle@rac2 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac2
ora....ER.lsnr ora....er.type ONLINE OFFLINE
ora....N1.lsnr ora....er.type ONLINE ONLINE rac2
ora....N2.lsnr ora....er.type ONLINE ONLINE rac2
ora....N3.lsnr ora....er.type ONLINE ONLINE rac2
ora.asm ora.asm.type ONLINE ONLINE rac2
ora.cvu ora.cvu.type ONLINE ONLINE rac2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac2
ora.oc4j ora.oc4j.type ONLINE ONLINE rac2
ora.ons ora.ons.type ONLINE ONLINE rac2
ora.rac1.vip ora....t1.type ONLINE ONLINE rac2
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application OFFLINE OFFLINE
ora.rac2.gsd application OFFLINE OFFLINE
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type ONLINE ONLINE rac2
ora.scan1.vip ora....ip.type ONLINE ONLINE rac2
ora.scan2.vip ora....ip.type ONLINE ONLINE rac2
ora.scan3.vip ora....ip.type ONLINE ONLINE rac2
Migrating Database from Filesystem to ASM
SQL> startup
ORACLE instance started.
Total System Global Area 2438529024 bytes
Fixed Size 2230792 bytes
Variable Size 1375733240 bytes
Database Buffers 1056964608 bytes
Redo Buffers 3600384 bytes
Database mounted.
Database opened.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/vishnu/system01.dbf
/u01/app/oracle/oradata/vishnu/sysaux01.dbf
/u01/app/oracle/oradata/vishnu/undotbs01.dbf
/u01/app/oracle/oradata/vishnu/users01.dbf
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 4 52428800 512 1 NO
CURRENT 1034168 28-JAN-18 2.8147E+14
2 1 2 52428800 512 1 YES
INACTIVE 998740 28-JAN-18 1010210 28-JAN-18
3 1 3 52428800 512 1 YES
INACTIVE 1010210 28-JAN-18 1034168 28-JAN-18
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/vishnu/control01.ctl
/u01/app/oracle/diag/vishnu/control02.ctl
SQL> ALTER SYSTEM SET control_files='+DATA' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET db_create_file_dest='+DATA' scope=spfile;
System altered.
SQL> create pfile='/u01/app/vishnu.ora' from spfile;
Need to make sure that we have sufficient space on those diskgroups to hold existing database.
Before to create ASM Diskgroups we should have done Capacity Planing. According to that panning diskgroups should be created otherwise during the conversion time it might get fail saying diskgroup space exhausted!
[oracle@rac2 oracle]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jan 28 10:22:30 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: VISHNU (DBID=2778817154)
RMAN> backup as copy database format '+DATA';
Starting backup at 28-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/vishnu/system01.dbf
output file name=+DATA/vishnu/datafile/system.256.966594181 tag=TAG20180128T102257 RECID=1 STAMP=966594187
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/vishnu/sysaux01.dbf
output file name=+DATA/vishnu/datafile/sysaux.257.966594193 tag=TAG20180128T102257 RECID=2 STAMP=966594197
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/vishnu/undotbs01.dbf
output file name=+DATA/vishnu/datafile/undotbs1.258.966594201 tag=TAG20180128T102257 RECID=3 STAMP=966594200
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/vishnu/controlfile/backup.259.966594201 tag=TAG20180128T102257 RECID=4 STAMP=966594201
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/vishnu/users01.dbf
output file name=+DATA/vishnu/datafile/users.260.966594203 tag=TAG20180128T102257 RECID=5 STAMP=966594202
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-JAN-18
channel ORA_DISK_1: finished piece 1 at 28-JAN-18
piece handle=+DATA/vishnu/backupset/2018_01_28/nnsnf0_tag20180128t102257_0.261.966594203 tag=TAG20180128T102257 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JAN-18
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 2438529024 bytes
Fixed Size 2230792 bytes
Variable Size 1375733240 bytes
Database Buffers 1056964608 bytes
Redo Buffers 3600384 bytes
RMAN> restore controlfile from '/u01/app/oracle/oradata/vishnu/control01.ctl';
Starting restore at 28-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/vishnu/controlfile/current.262.966594361
Finished restore at 28-JAN-18
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/vishnu/datafile/system.256.966594181"
datafile 2 switched to datafile copy "+DATA/vishnu/datafile/sysaux.257.966594193"
datafile 3 switched to datafile copy "+DATA/vishnu/datafile/undotbs1.258.966594201"
datafile 4 switched to datafile copy "+DATA/vishnu/datafile/users.260.9665942
RMAN> recover database;
Starting recover at 28-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 28-JAN-18
RMAN> sql 'alter database open';
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
VISHNU READ WRITE
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/vishnu/datafile/system.256.966594181
+DATA/vishnu/datafile/sysaux.257.966594193
+DATA/vishnu/datafile/undotbs1.258.966594201
+DATA/vishnu/datafile/users.260.966594203
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/abcd/redo03.log
/u01/app/oracle/oradata/abcd/redo02.log
/u01/app/oracle/oradata/abcd/redo01.log
Migrating redo logs into ASM is simple way, just add more redolog groups and then drop existing redolog groups which are in Filesystem. Before to add redolog groups make sure you set DB_CREATE_ONLINE_LOG_DEST_n parameter and it should point to ASM diskgroups.
alter system set db_create_online_log_dest_1=’+DATA’ scope=spfile;
alter system set db_create_online_log_dest_2=’+DATA’ scope=spfile;
SQL> show parameter db_create_online_log_dest
NAME TYPE VALUE
------------------------------------ ---------- ------
db_create_online_log_dest_1 string +LOG1
db_create_online_log_dest_2 string +LOG2
SQL> alter database add logfile group 4;
Database altered.
SQL> alter database add logfile group 5;
Database altered.
SQL> alter database add logfile group 6;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
Manually switching logfiles because Current Group can not be dropped.
SQL> select * from v$LOg;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 1 52428800 512 1 YES INACTIVE 1046676 29-MAY-12 1047525 29-MAY-12
4 1 2 104857600 512 1 NO CURRENT 1047525 29-MAY-12 2.8147E+14
5 1 0 104857600 512 1 YES UNUSED 0 0
6 1 0 104857600 512 1 YES UNUSED 0 0
SQL> alter database drop logfile group 1;
Database altered.
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
4 1 2 104857600 512 1 NO CURRENT 1047525 29-MAY-12 2.8147E+14
5 1 0 104857600 512 1 YES UNUSED 0 0
6 1 0 104857600 512 1 YES UNUSED 0 0
SQL> select member from v$Logfile;
MEMBER
---------------------------------------------------
+DATAFILE/abcd/onlinelog/group_4.266.784542027
+DATAFILE/abcd/onlinelog/group_5.267.784542035
+DATAFILE/abcd/onlinelog/group_6.271.784542041
Now the entire database got migrated to ASM storage and opened the database WITHOUT RESETLOG
show parameter spfile
connect / as sysdba
SQL> show parameter spfile
NAME TYPE VALUE
------- ------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora';
#Or simply:
SQL> create pfile from spfile
File created.
SQL> create spfile='+RACDB_DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora';
File created.
echo "SPFILE='+DATA/Vishnu/parameterfile/spfile.268.966596385'" > /u01/app/oracle/product/11.2.0//dbhome_2/dbs/spfilevishnu.ora
ASMCMD [+DATA/Vishnu/parameterfile] > ls
spfile.268.966596385
/u01/app/oracle/product/11.2.0
/dbhome_2/dbs/spfilevishnu.ora
create spfile='+DATA/Vishnu/parameterfile\spfilevishnu.ora' from pfile='/u01/app/oracle/product/11.2.0/dbhome_2/initvishnu.ora';
# crsctl stat res -t
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Start failed, or completed with errors.
# crsctl start crs
CRS-4013: This command is not supported in a single-node configuration.
CRS-4000: Command Start failed, or completed with errors.
[root@b1 grid]# cd /u01/app/11.2.0/grid/crs/install
[root@b1 install]# ./roothas.pl -deconfig -force -verbose
[root@b1 install]# cd /u01/app/11.2.0/grid/
[root@b1 grid]# ./root.sh
User ignored Prerequisites during installation
OLR initialization - successful
Adding Clusterware entries to upstart
CRS-2672: Attempting to start 'ora.mdnsd' on 'rac2'
CRS-2676: Start of 'ora.mdnsd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rac2'
CRS-2676: Start of 'ora.gpnpd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac2'
CRS-2672: Attempting to start 'ora.gipcd' on 'rac2'
CRS-2676: Start of 'ora.cssdmonitor' on 'rac2' succeeded
CRS-2676: Start of 'ora.gipcd' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rac2'
CRS-2672: Attempting to start 'ora.diskmon' on 'rac2'
CRS-2676: Start of 'ora.diskmon' on 'rac2' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac2' succeeded
PRKO-2190 : VIP exists for node rac2, VIP name rac2-vip
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
[oracle@rac2 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE rac2
ora....ER.lsnr ora....er.type ONLINE OFFLINE
ora....N1.lsnr ora....er.type ONLINE ONLINE rac2
ora....N2.lsnr ora....er.type ONLINE ONLINE rac2
ora....N3.lsnr ora....er.type ONLINE ONLINE rac2
ora.asm ora.asm.type ONLINE ONLINE rac2
ora.cvu ora.cvu.type ONLINE ONLINE rac2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE rac2
ora.oc4j ora.oc4j.type ONLINE ONLINE rac2
ora.ons ora.ons.type ONLINE ONLINE rac2
ora.rac1.vip ora....t1.type ONLINE ONLINE rac2
ora....SM2.asm application ONLINE ONLINE rac2
ora....C2.lsnr application OFFLINE OFFLINE
ora.rac2.gsd application OFFLINE OFFLINE
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip ora....t1.type ONLINE ONLINE rac2
ora.scan1.vip ora....ip.type ONLINE ONLINE rac2
ora.scan2.vip ora....ip.type ONLINE ONLINE rac2
ora.scan3.vip ora....ip.type ONLINE ONLINE rac2
Migrating Database from Filesystem to ASM
SQL> startup
ORACLE instance started.
Total System Global Area 2438529024 bytes
Fixed Size 2230792 bytes
Variable Size 1375733240 bytes
Database Buffers 1056964608 bytes
Redo Buffers 3600384 bytes
Database mounted.
Database opened.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/vishnu/system01.dbf
/u01/app/oracle/oradata/vishnu/sysaux01.dbf
/u01/app/oracle/oradata/vishnu/undotbs01.dbf
/u01/app/oracle/oradata/vishnu/users01.dbf
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 4 52428800 512 1 NO
CURRENT 1034168 28-JAN-18 2.8147E+14
2 1 2 52428800 512 1 YES
INACTIVE 998740 28-JAN-18 1010210 28-JAN-18
3 1 3 52428800 512 1 YES
INACTIVE 1010210 28-JAN-18 1034168 28-JAN-18
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/vishnu/control01.ctl
/u01/app/oracle/diag/vishnu/control02.ctl
SQL> ALTER SYSTEM SET control_files='+DATA' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET db_create_file_dest='+DATA' scope=spfile;
System altered.
SQL> create pfile='/u01/app/vishnu.ora' from spfile;
Need to make sure that we have sufficient space on those diskgroups to hold existing database.
Before to create ASM Diskgroups we should have done Capacity Planing. According to that panning diskgroups should be created otherwise during the conversion time it might get fail saying diskgroup space exhausted!
[oracle@rac2 oracle]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jan 28 10:22:30 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: VISHNU (DBID=2778817154)
RMAN> backup as copy database format '+DATA';
Starting backup at 28-JAN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/vishnu/system01.dbf
output file name=+DATA/vishnu/datafile/system.256.966594181 tag=TAG20180128T102257 RECID=1 STAMP=966594187
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/vishnu/sysaux01.dbf
output file name=+DATA/vishnu/datafile/sysaux.257.966594193 tag=TAG20180128T102257 RECID=2 STAMP=966594197
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/vishnu/undotbs01.dbf
output file name=+DATA/vishnu/datafile/undotbs1.258.966594201 tag=TAG20180128T102257 RECID=3 STAMP=966594200
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/vishnu/controlfile/backup.259.966594201 tag=TAG20180128T102257 RECID=4 STAMP=966594201
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/vishnu/users01.dbf
output file name=+DATA/vishnu/datafile/users.260.966594203 tag=TAG20180128T102257 RECID=5 STAMP=966594202
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 28-JAN-18
channel ORA_DISK_1: finished piece 1 at 28-JAN-18
piece handle=+DATA/vishnu/backupset/2018_01_28/nnsnf0_tag20180128t102257_0.261.966594203 tag=TAG20180128T102257 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JAN-18
RMAN> shutdown immediate
database closed
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 2438529024 bytes
Fixed Size 2230792 bytes
Variable Size 1375733240 bytes
Database Buffers 1056964608 bytes
Redo Buffers 3600384 bytes
RMAN> restore controlfile from '/u01/app/oracle/oradata/vishnu/control01.ctl';
Starting restore at 28-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/vishnu/controlfile/current.262.966594361
Finished restore at 28-JAN-18
RMAN> switch database to copy;
using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DATA/vishnu/datafile/system.256.966594181"
datafile 2 switched to datafile copy "+DATA/vishnu/datafile/sysaux.257.966594193"
datafile 3 switched to datafile copy "+DATA/vishnu/datafile/undotbs1.258.966594201"
datafile 4 switched to datafile copy "+DATA/vishnu/datafile/users.260.9665942
RMAN> recover database;
Starting recover at 28-JAN-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 28-JAN-18
RMAN> sql 'alter database open';
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
VISHNU READ WRITE
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/vishnu/datafile/system.256.966594181
+DATA/vishnu/datafile/sysaux.257.966594193
+DATA/vishnu/datafile/undotbs1.258.966594201
+DATA/vishnu/datafile/users.260.966594203
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/abcd/redo03.log
/u01/app/oracle/oradata/abcd/redo02.log
/u01/app/oracle/oradata/abcd/redo01.log
Migrating redo logs into ASM is simple way, just add more redolog groups and then drop existing redolog groups which are in Filesystem. Before to add redolog groups make sure you set DB_CREATE_ONLINE_LOG_DEST_n parameter and it should point to ASM diskgroups.
alter system set db_create_online_log_dest_1=’+DATA’ scope=spfile;
alter system set db_create_online_log_dest_2=’+DATA’ scope=spfile;
SQL> show parameter db_create_online_log_dest
NAME TYPE VALUE
------------------------------------ ---------- ------
db_create_online_log_dest_1 string +LOG1
db_create_online_log_dest_2 string +LOG2
SQL> alter database add logfile group 4;
Database altered.
SQL> alter database add logfile group 5;
Database altered.
SQL> alter database add logfile group 6;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
Manually switching logfiles because Current Group can not be dropped.
SQL> select * from v$LOg;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 1 52428800 512 1 YES INACTIVE 1046676 29-MAY-12 1047525 29-MAY-12
4 1 2 104857600 512 1 NO CURRENT 1047525 29-MAY-12 2.8147E+14
5 1 0 104857600 512 1 YES UNUSED 0 0
6 1 0 104857600 512 1 YES UNUSED 0 0
SQL> alter database drop logfile group 1;
Database altered.
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
4 1 2 104857600 512 1 NO CURRENT 1047525 29-MAY-12 2.8147E+14
5 1 0 104857600 512 1 YES UNUSED 0 0
6 1 0 104857600 512 1 YES UNUSED 0 0
SQL> select member from v$Logfile;
MEMBER
---------------------------------------------------
+DATAFILE/abcd/onlinelog/group_4.266.784542027
+DATAFILE/abcd/onlinelog/group_5.267.784542035
+DATAFILE/abcd/onlinelog/group_6.271.784542041
Now the entire database got migrated to ASM storage and opened the database WITHOUT RESETLOG
show parameter spfile
connect / as sysdba
SQL> show parameter spfile
NAME TYPE VALUE
------- ------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdb11.ora';
#Or simply:
SQL> create pfile from spfile
File created.
SQL> create spfile='+RACDB_DATA' from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initracdb11.ora';
File created.
echo "SPFILE='+DATA/Vishnu/parameterfile/spfile.268.966596385'" > /u01/app/oracle/product/11.2.0//dbhome_2/dbs/spfilevishnu.ora
ASMCMD [+DATA/Vishnu/parameterfile] > ls
spfile.268.966596385
/u01/app/oracle/product/11.2.0
/dbhome_2/dbs/spfilevishnu.ora
create spfile='+DATA/Vishnu/parameterfile\spfilevishnu.ora' from pfile='/u01/app/oracle/product/11.2.0/dbhome_2/initvishnu.ora';
